#include "datebase.h"

datebase::datebase(QString flag,QObject *parent) :
 QObject(parent)
{

 //sqlite_db = QSqlDatabase::addDatabase("QSQLITE",flag);
 //sqlite_db = QSqlDatabase::database("QSQLITE");
 sqlite_db= new QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE",flag));
}

void datebase::databaseinit(QSqlDatabase *db,QString dbname)
{
 db->setDatabaseName(dbname);
 if(!QFile::exists(dbname))
 {
 if(!db->open())
 {
 qDebug()<<"Unable to open sqlite database";
 return;
 }
 QStringList create_cmd;
 create_cmd.append(CREATE_INFO_USER);
 create_cmd.append(CREATE_INFO_USER1);
 create_cmd.append(CREATE_INFO_NETINFO);
 create_cmd.append(CREATE_INFO_NETINFO1);
 create_cmd.append(CREATE_INFO_NETINFO2);
 create_cmd.append(CREATE_INFO_SECTION);
 create_cmd.append(CREATE_INFO_SECTION1);
 create_cmd.append(CREATE_INFO_PERSON);
 create_cmd.append(CREATE_INFO_PERSON1);
 create_cmd.append(CREATE_INFO_PERSON2);

 create_cmd.append(CREATE_INFO_FLOWPERSON);

 create_cmd.append(CREATE_INFO_FEEBOX);
 create_cmd.append(CREATE_INFO_FEEBOX1);
 create_cmd.append(CREATE_INFO_FEEBOX2);
 create_cmd.append(CREATE_INFO_NETPERSON);
 create_cmd.append(CREATE_INFO_NETPERSON1);

 create_cmd.append(CREATE_INFO_CLASSRECORD);
 create_cmd.append(CREATE_INFO_PERSONRECORD);
 create_cmd.append(CREATE_INFO_BOXRECORD);
 QSqlQuery *query = new QSqlQuery(*db);
 foreach(QString cmd,create_cmd)
 {
 if(!query->exec(cmd))
 {
 qDebug()<<"build sqlite error"<<query->lastError().text();
 return;
 }else{
 qDebug()<<"build sqlite success";
 }
 }
 }else{
 if(!sqlite_db->open())
 {
 qDebug()<<"Unable to open sqlite database";
 }
 else
 {
 qDebug()<<"open sqlite database sucsess";
 }
 }
}

QString datebase::GetSectionId(QSqlDatabase *db,QString name)
{
 QSqlQuery query(*db);
 QString str="";
 int ret;
 query.clear();
 query.prepare(QObject::tr("select Id from Section where Name = '%1'").arg(name));
 query.exec();
 ret = query.next();
 if(ret==1)
 {
 str=query.value(0).toString();
 query.clear();
 }
 return str;
}

bool datebase::writePerson(QSqlDatabase *db,PersonInfo *checkInfo)//Ա
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into Person(Card_Sn,Pass_No,Name,Section,Post,Photo,identity_Card,Status,End_date) values (?,?,?,?,?,?,?,?,?)");
 query.bindValue(0,checkInfo->Card_Sn);
 query.bindValue(1,checkInfo->Pass_No);
 query.bindValue(2,checkInfo->Name);
 query.bindValue(3,checkInfo->Section);
 query.bindValue(4,checkInfo->Post);
 query.bindValue(5,checkInfo->Photo);
 query.bindValue(6,checkInfo->identity_Card);
 query.bindValue(7,checkInfo->Status);
 query.bindValue(8,checkInfo->End_date);
 //query.bindValue(9,checkInfo->SectionID);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}

bool datebase::writeNet(QSqlDatabase *db,NetInfo *checkInfo)
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into NetInfo(Pass_No,NetName,Address,Phone,Person,UpSection,NetFlag) values (?,?,?,?,?,?,?)");
 query.bindValue(0,checkInfo->Pass_No);
 query.bindValue(1,checkInfo->NetName);
 query.bindValue(2,checkInfo->Address);
 query.bindValue(3,checkInfo->Phone);
 query.bindValue(4,checkInfo->Person);
 query.bindValue(5,checkInfo->UpSection);
 query.bindValue(6,checkInfo->NetFlag);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}

bool datebase::writeBox(QSqlDatabase *db,QString pass,QString card,QString address)
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into FeeBox(Pass_No,Card_Sn,Net_Address) values (?,?,?)");
 query.bindValue(0,pass);
 query.bindValue(1,card);
 query.bindValue(2,address);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}


bool datebase::writeNetPertson(QSqlDatabase *db,QString address,QString user,QString pass,QString flag)
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into NetPerson(Net_Address,User,Pass_Word,Flag) values (?,?,?,?)");
 query.bindValue(0,address);
 query.bindValue(1,user);
 query.bindValue(2,pass);
 query.bindValue(3,flag);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}

bool datebase::writePertson(QSqlDatabase *db,QString user,QString pass)
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into UserSys(User,PassWord) values (?,?)");
 query.bindValue(0,user);
 query.bindValue(1,pass);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}

bool datebase::writeSection(QSqlDatabase *db,QString name)
{
 QSqlQuery query(*db);
 query.clear();
 query.prepare("insert into Section(Name) values (?)");
 query.bindValue(0,name);
 int guet = query.exec();
 query.clear();
 if(guet==1)
 {
 qDebug()<<"insert success";
 return true;
 }else{
 qDebug()<<"insert false"<<query.lastError().text();
 return false;
 }
}

bool datebase::deleteSectionInfo(QSqlDatabase *db,int id)
{
QSqlQuery query(*db);
query.prepare(tr("delete from Section where Id=%1").arg(id));
query.exec();
query.clear();
}

bool datebase::deleteNetInfo(QSqlDatabase *db,QString name)
{
 QSqlQuery query(*db);
 query.prepare(tr("delete from NetInfo where Pass_No=%1").arg(name));
 query.exec();
 query.clear();
}

bool datebase::deleteDeeBox(QSqlDatabase *db,int id)
{
 QSqlQuery query(*db);
 query.prepare(tr("delete from FeeBox where Id=%1").arg(id));
 query.exec();
 query.clear();
}



bool datebase::deleteNetPerInfo(QSqlDatabase *db,int id)
{
 QSqlQuery query(*db);
 query.prepare(tr("delete from NetPerson where Id=%1").arg(id));
 query.exec();
 query.clear();
}

bool datebase::deleteUserInfo(QSqlDatabase *db,int id)
{
 QSqlQuery query(*db);
 query.prepare(tr("delete from UserSys where Id=%1").arg(id));
 query.exec();
 query.clear();
}


bool datebase::updateSection(QSqlDatabase *db,int id,QString name)
{
 QSqlQuery query(*db);
 QString str;
 str=QString("update Section set Name='%1' where Id=%2").arg(name).arg(id);
 query.clear();
 query.exec(str);
 return true;
}

bool datebase::updateNetInfo(QSqlDatabase *db,int id,NetInfo *checkInfo)
{
 QSqlQuery query(*db);
 QString str;
 str=QString("update NetInfo set Pass_No='%1',NetName='%2',Address='%3',Phone='%4',Person='%5',UpSection='%6',NetFlag='%7' where Id=%8").arg(checkInfo->Pass_No).arg(checkInfo->NetName).arg(checkInfo->Address).arg(checkInfo->Phone).arg(checkInfo->Person).arg(checkInfo->UpSection).arg(checkInfo->NetFlag).arg(id);
 query.exec(str);
 query.clear();
 return true;
}

bool datebase::updateBoxInfo(QSqlDatabase *db,int id,QString pass,QString card,QString address)
{
 QSqlQuery query(*db);
 QString str;
 str=QString("update FeeBox set Pass_No='%1',Card_Sn='%2',Net_Address='%3' where Id=%4").arg(pass).arg(card).arg(address).arg(id);
 query.exec(str);
 query.clear();
 return true;
}


bool datebase::updateNetPersonInfo(QSqlDatabase *db,int id,QString address,QString user,QString pass,QString flag)
{
 QSqlQuery query(*db);
 QString str;
 str=QString("update NetPerson set Net_Address='%1',User='%2',Pass_Word='%3',Flag='%4' where Id=%5").arg(address).arg(user).arg(pass).arg(flag).arg(id);
 query.exec(str);
 query.clear();
 return true;
}

bool datebase::updateUserInfo(QSqlDatabase *db,int id,QString user,QString pass)
{
 QSqlQuery query(*db);
 QString str;
 str=QString("update UserSys set User='%1',PassWord='%2' where Id=%3").arg(user).arg(pass).arg(id);
 query.exec(str);
 query.clear();
 return true;
}

QByteArray datebase::GetUserPhotoInfo(QSqlDatabase *db,int id)
{
 QSqlQuery query(*db);
 QByteArray str="";
 int ret;
 query.clear();
 query.prepare(QObject::tr("select Photo from Person where Id = '%1'").arg(id));
 query.exec();
 ret = query.next();
 if(ret==1)
 {
 str=query.value(0).toByteArray();
 query.clear();
 }
 return str;
}

bool datebase::UserCheck(QSqlDatabase *db,QString UserStr,QString passStr)
{
 QSqlQuery query(*db);
 QString pass;
 int ret;
 query.clear();
 query.prepare(QObject::tr("select PassWord from UserSys where User = '%1'").arg(UserStr));
 query.exec();
 ret = query.next();
 if(ret==1)
 {
 pass=query.value(0).toString();
 if(passStr==pass)
 {
 return true;
 }else{
 return false;
 }
 }else{
 if(UserStr=="admin" && passStr=="6269768")
 {
 return true;
 }else{
 return false;
 }
 }
}



void datebase::UserPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db,QString address)
{
 QSqlQuery query(*Sysdb);
 QString user,pass,flag;
 query.clear();
 query.prepare(QObject::tr("select User,Pass_Word,Flag from NetPerson where Net_Address = '%1'").arg(address));
 query.exec();
 while( query.next())
 {
 user=query.value(0).toString();
 pass=query.value(1).toString();
 flag=query.value(1).toString();
 writeNetPertson(db,address,user,pass,flag);
 }
}

void datebase::SectionPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db)
{
 QSqlQuery query(*Sysdb);
 QString name;
 query.clear();
 query.prepare(QObject::tr("select Name from Section order by Id asc"));
 query.exec();
 while( query.next())
 {
 name=query.value(0).toString();
 writeSection(db,name);
 }
}

void datebase::NetInfoPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db)
{
 QSqlQuery query(*Sysdb);
 NetInfo nInfo;
 query.clear();
 query.prepare(QObject::tr("select Pass_No,NetName,Address,Phone,Person,UpSection,NetFlag from NetInfo order by Id asc"));
 query.exec();
 while( query.next())
 {

 nInfo.Pass_No=query.value(0).toString();
 nInfo.NetName=query.value(1).toString();
 nInfo.Address=query.value(2).toString();
 nInfo.Phone=query.value(3).toString();
 nInfo.Person=query.value(4).toString();
 nInfo.UpSection=query.value(5).toString();
 nInfo.NetFlag=query.value(6).toString();
 writeNet(db,&nInfo);
 }
}

void datebase::PersonPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db)
{
 QSqlQuery query(*Sysdb);
 PersonInfo perInfo;
 query.clear();
 query.prepare(QObject::tr("select Card_Sn,Pass_No,Name,Section,Post,Photo,identity_Card,Status,End_date from Person order by Id asc"));
 query.exec();
 while( query.next())
 {
 perInfo.Card_Sn=query.value(0).toString();
 perInfo.Pass_No=query.value(1).toString();
 perInfo.Name=query.value(2).toString();
 perInfo.Section=query.value(3).toString();
 perInfo.Post=query.value(4).toString();
 perInfo.Photo=query.value(5).toByteArray();
 perInfo.identity_Card=query.value(6).toString();
 perInfo.Status=query.value(7).toString();
 perInfo.End_date=query.value(8).toString();
 writePerson(db,&perInfo);
 }
}

void datebase::BoxPcToLinux(QSqlDatabase *Sysdb,QSqlDatabase *db)
{
 QSqlQuery query(*Sysdb);
 QString pass,card,address;
 query.clear();
 query.prepare(QObject::tr("select Card_Sn,Pass_No,Net_Address from FeeBox order by Id asc"));
 query.exec();
 while( query.next())
 {
 pass=query.value(0).toString();
 card=query.value(1).toString();
 address=query.value(2).toString();
 writeBox(db,pass,card,address);
 }
}