#ifndef DATEBASE_H
#define DATEBASE_H

#include <QObject>
#include <QtSql>
#define CREATE_INFO_CARBIND "CREATE TABLE [ProcessList] ([Id] INTEGER PRIMARY KEY, [Type] varchar(20), [Name] varchar(30), [IsUse] int)"

#define CREATE_INFO_USER  "CREATE TABLE [UserSys] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, [User] varchar(20),[PassWord] varchar(30),[Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_USER1 "CREATE UNIQUE INDEX [UserUni] ON [UserSys] ([User])"

#define CREATE_INFO_NETINFO "CREATE TABLE [NetInfo] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Pass_No] VARCHAR(20), [NetName] VARCHAR(20), [Address] VARCHAR(20), [Phone] VARCHAR(20), [Person] VARCHAR(20), [UpSection] VARCHAR(20), [NetFlag] VARCHAR(20), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_NETINFO1 "CREATE UNIQUE INDEX [Net_PassNo] ON [NetInfo] ([Pass_No])"
#define CREATE_INFO_NETINFO2 "CREATE UNIQUE INDEX [NetNameOne] ON [NetInfo] ([NetName])"

#define CREATE_INFO_SECTION "CREATE TABLE [Section] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Name] VARCHAR(20), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')));CREATE UNIQUE INDEX [Section_Name] ON [Section] ([Name])"
#define CREATE_INFO_SECTION1 "CREATE UNIQUE INDEX [Section_Name] ON [Section] ([Name])"

#define CREATE_INFO_PERSON "CREATE TABLE [Person] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Card_Sn] VARCHAR(20), [Pass_No] VARCHAR(20), [Name] VARCHAR(20), [Section] VARCHAR(20) CONSTRAINT [SectionID] REFERENCES [Section]([Name]) ON DELETE SET NULL ON UPDATE SET DEFAULT, [Post] VARCHAR(20), [Photo] BLOB(50), [identity_Card] VARCHAR(30), [Status] varchaR(10), [End_date] DATE(20), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_PERSON1 "CREATE UNIQUE INDEX [PerSon_PassNo] ON [Person] ([Pass_No])"
#define CREATE_INFO_PERSON2 "CREATE UNIQUE INDEX [PerSon_Card_Sn] ON [Person] ([Card_Sn])"

#define CREATE_INFO_FLOWPERSON  "CREATE TABLE [FlowPerson] ([Id] VARCHAR(20), [Card_Sn] VARCHAR(20), [Pass_No] VARCHAR(20), [Name] VARCHAR(20), [Section] VARCHAR(20), [Post] VARCHAR(20), [identity Card] VARCHAR(20), [Status] VARCHAR(10), [End_Date] VARCHAR(20), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')), CONSTRAINT [] PRIMARY KEY ([Id]))"

#define CREATE_INFO_FEEBOX "CREATE TABLE [FeeBox] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Card_Sn] VARCHAR(20), [Pass_No] VARCHAR(20), [Net_Address] VARCHAR(20) CONSTRAINT [NetID] REFERENCES [NetInfo]([NetName]), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_FEEBOX1 "CREATE UNIQUE INDEX [CardUni] ON [FeeBox] ([Card_Sn])"
#define CREATE_INFO_FEEBOX2 "CREATE UNIQUE INDEX [FeeBox_Pass_no] ON [FeeBox] ([Pass_No])"

#define CREATE_INFO_NETPERSON "CREATE TABLE [NetPerson] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT, [Net_Address] VARCHAR(20) CONSTRAINT [NetID] REFERENCES [NetInfo]([NetName]), [User] VARCHAR(20), [Pass_Word] VARCHAR(20), [Flag] VARCHAR(10), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_NETPERSON1 "CREATE UNIQUE INDEX [NetPersonUser] ON [NetPerson] ([User])"

#define CREATE_INFO_CLASSRECORD  "CREATE TABLE [ClassRecord] ([Id] VARCHAR(30),[Flag] VARCHAR(10), [Main_Turn] VARCHAR(20), [Common_Turn] VARCHAR(20), [Net_Address] VARCHAR(20) CONSTRAINT [NetID] REFERENCES [NetInfo]([NetName]),  [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')), CONSTRAINT [sqlite_atoindex_ClassRecord_1] PRIMARY KEY ([Id]))"
#define CREATE_INFO_PERSONRECORD "CREATE TABLE [PersonRecord] ([No] INTEGER PRIMARY KEY AUTOINCREMENT, [Id] VARCHAR(20) CONSTRAINT [ClassID] REFERENCES [ClassRecord]([Id]), [Card_Sn] VARCHAR(20) CONSTRAINT [PersonRecord_Card_Sn] REFERENCES [Person]([Card_Sn]), [Pass_No] VARCHAR(20), [Name] VARCHAR(20), [Section] VARCHAR(20), [Post] VARCHAR(20), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"
#define CREATE_INFO_BOXRECORD "CREATE TABLE [BoxRecord] ([No] INTEGER PRIMARY KEY AUTOINCREMENT, [Id] VARCHAR(20) CONSTRAINT [ClassId] REFERENCES [ClassRecord]([Id]),[Card_Sn] VARCHAR(20) CONSTRAINT [BoxRecord_Card_Sn] REFERENCES [FeeBox]([Card_Sn]), [Pass_No] VARCHAR(20), [Net_Address] VARCHAR(20) CONSTRAINT [BoxRecord_Net_Name] REFERENCES [NetInfo]([NetName]), [Create_Time] TIMESTAMP NOT NULL DEFAULT (datetime('now','localtime')))"


typedef struct
{
  QString Card_Sn;     //
  QString Pass_No;
  QString Name; //ͣʼ
  QString Section;
  //QString SectionID;
  QString Post; //
  QByteArray Photo;
  QString identity_Card;
  QString Status;
  QString End_date;
}PersonInfo;

typedef struct
{
  QString NetName;     //
  QString Pass_No;
  QString Address; //ͣʼ
  QString Phone;
  QString Person; //
  QString NetFlag;
  QString UpSection;
}NetInfo;

typedef struct
{
  QString NetName;     //
  QString Pass_No;
  QString Address; //ͣʼ
  QString Phone;
  QString Person; //
  QString NetFlag;
  QString UpSection;
}NetPersonInfo;

class datebase : public QObject
{
    Q_OBJECT
public:
    explicit datebase(QString flag,QObject *parent = 0);
    QSqlDatabase *sqlite_db;

signals:

public slots:
   void databaseinit(QSqlDatabase *db,QString dbname);
   bool writePerson(QSqlDatabase *db,PersonInfo *checkInfo);
   bool writeSection(QSqlDatabase *db,QString name);
   bool writeNetPertson(QSqlDatabase *db,QString address,QString user,QString pass,QString flag);
   bool writePertson(QSqlDatabase *db,QString user,QString pass);
   bool updateSection(QSqlDatabase *db,int id,QString name);
   bool updateNetInfo(QSqlDatabase *db,int id,NetInfo *checkInfo);
   bool updateBoxInfo(QSqlDatabase *db,int id,QString pass,QString card,QString address);
   bool updateNetPersonInfo(QSqlDatabase *db,int id,QString address,QString user,QString pass,QString flag);
   bool updateUserInfo(QSqlDatabase *db,int id,QString user,QString pass);
   bool writeNet(QSqlDatabase *db,NetInfo *checkInfo);
   bool writeBox(QSqlDatabase *db,QString pass,QString card,QString address);
   bool deleteNetInfo(QSqlDatabase *db,QString name);
   bool deleteSectionInfo(QSqlDatabase *db,int id);
   bool deleteDeeBox(QSqlDatabase *db,int id);
   bool deleteNetPerInfo(QSqlDatabase *db,int id);
   bool deleteUserInfo(QSqlDatabase *db,int id);
   QString GetSectionId(QSqlDatabase *db,QString name);
   QByteArray GetUserPhotoInfo(QSqlDatabase *db,int id);
   bool UserCheck(QSqlDatabase *db,QString UserStr,QString passStr);
   void UserPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db,QString address);
   void SectionPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db);
   void NetInfoPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db);
   void PersonPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db);
   void BoxPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db);
};

#endif // DATEBASE_H