SpeedNumCube/sncprojects/lib/SNCDataBase/zxshdb.cpp

#include "zxshdb.h"
#include "sncdatabase.h"
#include <QtSql>
ZxshDB::ZxshDB()
{
}

int ZxshDB::addShkjZxshTask(QString ssid, QString userCode, QString province, QString city,\
                            QString inserttime,QString finishtime, int updatedays, QString isp,\
                            QString slttype, QString packnolist,QString seglist, int neednum, int rpnum)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("insert into user.SHKJ_ZXSH_TASK(ssid,USERCODE,province,\
                  city,inserttime,finishtime,updatedays,isp,slttype,packnolist,seg,neednum,rpnum,dealstatus,isdel)\
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,'0','N')");
    QVariantList vars;
    vars<<ssid<<userCode<<province<<city<<inserttime<<finishtime;
    vars<<updatedays<<isp<<slttype<<packnolist<<seglist<<neednum<<rpnum;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    query.clear();
    QStringList list = packnolist.split(",");
    QVariantList v1,v2,v3,v4,v5;
    QString time = QDateTime::currentDateTime().toString("yyyy-MM-dd HH:mm:ss");
    for(int i=0;i<list.size();i++)
    {
        if(list.at(i)!="end")
        {
            v1<<province;
            v2<<city;
            v3<<userCode;
            v4<<list.at(i);
            v5<<time;

            query.prepare("delete from user.SHKJ_ZXSH_TASK_packno where usercode=? and packno=?");
            query.addBindValue(userCode);
            query.addBindValue(list.at(i));
            query.exec();
        }
    }
    query.clear();
    query.prepare("insert into user.SHKJ_ZXSH_TASK_packno(province,city,usercode,packno,inserttime) values(?,?,?,?,?)");
    query.addBindValue(v1);
    query.addBindValue(v2);
    query.addBindValue(v3);
    query.addBindValue(v4);
    query.addBindValue(v5);
    query.execBatch();
    QSqlDatabase::database().commit();
    return 0;
}

int ZxshDB::updateZxshDealStatus(QString id, QString state)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("update user.SHKJ_ZXSH_TASK set dealstatus=?  where ssid=? ");
    QVariantList vars;
    vars<<state<<id;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();

    return 0;
}

QMultiMap<QString, QString> ZxshDB::getShkjZxshTasksByUser(QString userCode, QString isdown="0")
{
    QString where = "";

    if(isdown=="0") //全部
        ;
    else if(isdown=="1")    //已下载
    {
        where += " and dealstatus = 3 ";
    }
    else if(isdown=="2")    //未下载
    {
        where += " and dealstatus <= 2 ";
    }
    else
        ;

    QMultiMap<QString ,QString> values;
    QSqlQuery query;

    query.prepare("SELECT TASKID,SSID,PROVINCE,CITY,INSERTTIME,FINISHTIME,UPDATEDAYS,ISP ,\
                  SLTTYPE,PACKNOLIST,SEG,NEEDNUM ,RPNUM,USERCODE,DEALSTATUS,ISDEL \
                  from user.SHKJ_ZXSH_TASK where usercode=? and (isdel='N' or isdel is null or isdel='')" + where);
    query.addBindValue(userCode);

    query.exec();

    while(query.next())
    {
        int i=0;
        values.insert("taskid",query.value(i++).toString());
        values.insert("ssid",query.value(i++).toString());
        values.insert("province",query.value(i++).toString());
        values.insert("city",query.value(i++).toString());
        values.insert("inserttime",query.value(i++).toString());
        values.insert("finishtime",query.value(i++).toString());
        values.insert("updatedays",query.value(i++).toString());
        values.insert("isp",query.value(i++).toString());
        values.insert("slttype",query.value(i++).toString());
        values.insert("packnolist",query.value(i++).toString());
        values.insert("seglist",query.value(i++).toString());
        values.insert("neednum",query.value(i++).toString());
        values.insert("rpnum",query.value(i++).toString());
        values.insert("usercode",query.value(i++).toString());
        values.insert("dealstatus",query.value(i++).toString());
        values.insert("isdel",query.value(i++).toString());
    }
    return values;
}

int ZxshDB::updateShkjZxshTaskInfos(QMultiMap<QString, QString> maps, QString userCode)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    int cols = maps.uniqueKeys().size();
    int rows = 0;
    if(cols!=0)
    {
        rows = maps.size()/cols;
    }

    for(int i=0;i<rows;i++)
    {
        QString ssid = maps.values("ssid").at(i);
        QString dealstatus = maps.values("dealstatus").at(i);
        QString province = maps.values("province").at(i);
        QString city = maps.values("city").at(i);
        QString updatedays = maps.values("updatedays").at(i);
        QString isp = maps.values("isp").at(i);
        QString neednum = maps.values("neednum").at(i);
        QString rpnum = maps.values("rpnum").at(i);
        QString inserttime = maps.values("inserttime").at(i);
        QString finishtime = maps.values("finishtime").at(i);

        query.clear();
        query.prepare("select ssid from user.SHKJ_ZXSH_TASK where usercode = ? and ssid = ?");
        query.addBindValue(userCode);
        query.addBindValue(ssid);
        query.exec();

        if(!query.next())
        {
            query.clear();
            query.prepare("insert into user.SHKJ_ZXSH_TASK( \
                          ssid,dealstatus,province,city,updatedays,isp ,\
                          neednum,rpnum ,inserttime,finishtime,USERCODE,isdel) \
                    VALUES(?,?,?,?,?,?,?,?,?,?,?,'N')");
            QVariantList vars;
            vars<<ssid<<dealstatus<<province<<city<<updatedays<<isp<<neednum;
            vars<<rpnum<<inserttime<<finishtime<<userCode;
            for(int i=0;i<vars.size();i++)
            {
                query.addBindValue(vars.at(i));
            }
            query.exec();
        }
        else
        {
            query.clear();
            query.prepare("update user.SHKJ_ZXSH_TASK \
                          set dealstatus=?,rpnum = ? ,inserttime=?,finishtime=?\
                         where USERCODE = ? and ssid = ?") ;
            query.addBindValue(dealstatus);
            query.addBindValue(rpnum);
            query.addBindValue(inserttime);
            query.addBindValue(finishtime);
            query.addBindValue(userCode);
            query.addBindValue(ssid);
            query.exec();
        }


    }
    QSqlDatabase::database().commit();
    return 0;
}

int ZxshDB::updateShkjZxshSltPacknos(QMultiMap<QString, QString> maps, QString userCode)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    int cols = maps.uniqueKeys().size();
    int rows = 0;
    if(cols!=0)
    {
        rows = maps.size()/cols;
    }
    QVariantList v1,v2,v3,v4,v5;

    for(int i=0;i<rows;i++)
    {
        v1<<maps.values("province").at(i);
        v2<<maps.values("city").at(i);
        v3<<userCode;
        v4<<maps.values("packno").at(i);
        v5<<maps.values("inserttime").at(i);
        query.prepare("delete from user.SHKJ_ZXSH_TASK_packno where usercode=? and packno=?");
        query.addBindValue(userCode);
        query.addBindValue(maps.values("packno").at(i));
        query.exec();
    }
    query.clear();
    query.prepare("insert into user.SHKJ_ZXSH_TASK_packno(province,city,usercode,packno,inserttime)\
                  values(?,?,?,?,?)");
    query.addBindValue(v1);
    query.addBindValue(v2);
    query.addBindValue(v3);
    query.addBindValue(v4);
    query.addBindValue(v5);
    query.execBatch();

    QSqlDatabase::database().commit();
    return 0;
}

int ZxshDB::delShkjZxshTaskByID(QString ssid)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("update user.SHKJ_ZXSH_TASK set isdel='Y' where ssid = ?") ;
    query.addBindValue(ssid);
    query.exec();
    QSqlDatabase::database().commit();
}

void ZxshDB::updateSegRealPnum(QString upday, QString areacode, QString filename)
{
    QSqlQuery query;
    QFile file2(filename);
    file2.open(QIODevice::ReadOnly);
    QTextStream in2(&file2);
    QSqlDatabase::database().transaction();
    QVariantList seg2,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10;
    while(!in2.atEnd())
    {
        QString line=in2.readLine();
        QStringList fields = line.split(",");
        int i=0;
        seg2 << fields.at(i++);
        s1 << fields.at(i++);
        s2 << fields.at(i++);
        s3 << fields.at(i++);
        s4 << fields.at(i++);
        s5 << fields.at(i++);
        s6 << fields.at(i++);
        s7 << fields.at(i++);
        s8 << fields.at(i++);
        s9 << fields.at(i++);
        s10 << fields.at(i++);
    }
    file2.close();
    QFile::remove(filename);
    query.exec("drop table SEG_RealNum_tmp");
    query.exec("CREATE TABLE SEG_RealNum_tmp (seg char(7),\
               S1 numberic,\
               S2 numberic,\
               S3 numberic,\
               S4 numberic,\
               S5 numberic,\
               S6 numberic,\
               S7 numberic,\
               S8 numberic,\
               S9 numberic,\
               S10 numberic)");
    query.finish();
    query.prepare("insert into SEG_RealNum_tmp(seg,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10) values(?,?,?,?,?,?,?,?,?,?,?)");
    query.addBindValue(seg2);

    query.addBindValue(s1);
    query.addBindValue(s2);
    query.addBindValue(s3);
    query.addBindValue(s4);

    query.addBindValue(s5);
    query.addBindValue(s6);
    query.addBindValue(s7);
    query.addBindValue(s8);
    query.addBindValue(s9);
    query.addBindValue(s10);
    query.execBatch();
    QSqlDatabase::database().commit();
}

void ZxshDB::updatePackRealPnum(QString upday, QString areacode, QString filename)
{
    QSqlQuery query;
    QFile file2(filename);
    file2.open(QIODevice::ReadOnly);
    QTextStream in2(&file2);
    QSqlDatabase::database().transaction();
    QVariantList s1,s2,s3,s4,s5;
    while(!in2.atEnd())
    {
        QString line=in2.readLine();
        QStringList fields = line.split(",");
        int i=0;
        s1 << fields.at(i++);
        s2 << fields.at(i++);
        s3 << fields.at(i++);
        s4 << fields.at(i++);
        QString dlcount = fields.at(i++);
        if(dlcount=="9+")//注：下载次数>=9时，接口方法中会返回 9+
            dlcount = 10;
        s5 << dlcount;

    }
    file2.close();
    QFile::remove(filename);
    query.exec("drop table Pack_RealNum_tmp");
    query.exec("CREATE TABLE Pack_RealNum_tmp (packid varchar(50),\
               packnum numberic,\
               isp char(1),\
               updatedays numberic,dlcount numberic)");
    query.finish();
    query.prepare("insert into Pack_RealNum_tmp(packid,packnum,isp,updatedays,dlcount) values(?,?,?,?,?)");
    query.addBindValue(s1);
    query.addBindValue(s2);
    query.addBindValue(s3);
    query.addBindValue(s4);
    query.addBindValue(s5);
    query.execBatch();

    QSqlDatabase::database().commit();

}




QStringList ZxshDB::getShkjZxshSegRealPnum(int updatedays, QString corp, QString city)
{
    QString cmcc,cutc,ctcc,isp;
    cmcc = SNCDataBase::getCMCC();
    cutc = SNCDataBase::getCUTC();
    ctcc = SNCDataBase::getCTCC();
    if(corp=="1")
        isp = cmcc;
    else if(corp=="2")
        isp = cutc;
    else if(corp=="3")
        isp = ctcc;
    else
        isp = cmcc+","+cutc+","+ctcc;
    QSqlQuery query;
    QString sql = "select a.seg,b.s1,b.s2,b.s3,b.s4,b.s5,b.s6,b.s7,b.s8,b.s9,b.s10 \
            from num.mseg a LEFT JOIN SEG_RealNum_tmp b \
            on a.seg = b.seg where a.city = ? and  substr(b.seg,1,3) in ("+isp+")";

    if(updatedays==1)
        sql += " and b.s1>0";
    else if(updatedays==2)
        sql += " and b.s2>0";
    else if(updatedays==3)
        sql += " and b.s3>0";
    else if(updatedays==4)
        sql += " and b.s4>0";
    else if(updatedays==5)
        sql += " and b.s5>0";
    else if(updatedays==6)
        sql += " and b.s6>0";
    else if(updatedays==7)
        sql += " and b.s7>0";
    else if(updatedays==30)
        sql += " and b.s8>0";
    else if(updatedays==90)
        sql += " and b.s9>0";
    else if(updatedays==180)
        sql += " and b.s10>0";
    query.prepare(sql);
    query.addBindValue(city);
    query.exec();

    QStringList list;
    QSqlRecord rec = query.record();
    int col = rec.count();
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            QString data;
            if(!query.value(i).isNull())
            {
                data = query.value(i).toString();
            }
            if(i==col-1)
            {
                info += data;
            }
            else
            {
                info += data + ",";
            }
        }
        list<<info;
    }
    return list;
}
QStringList ZxshDB::getShkjZxshPackRealPnum(int updatedays, QString corp, QString city,QString usercode,QString isdown)
{
    QString where;
    if(updatedays==7)
        where =" where updatedays<=7 ";
    else if(updatedays==30)
        where =" where updatedays<=30 and updatedays>7 ";
    else if(updatedays==90)
        where =" where updatedays<=90 and updatedays>30 ";
    else if(updatedays==180)
        where =" where  updatedays>90 ";
    else
        where =" where updatedays<=7 and updatedays>=1";

    if(corp=="1")
        where +=" and isp = '1'";
    else if(corp=="2")
        where +=" and isp = '2'";
    else if(corp=="3")
        where +=" and isp = '3'";
    else if(corp=="6")
        where +=" and isp = '6'";
    else
        where +=" ";
    if(!city.isEmpty())
        where + " and city='"+city+"' ";
    //判断用户是否下载的标准，改为不再区分用户，只要在本机上下载过，就认为是下载过
    //modify by xxw on 2016-5-23
    if(isdown=="0")
        ;
    else if(isdown=="1")//已下载
    {
        if(usercode.isEmpty())
            where += " and b.usercode='never exists' ";
        else
            where += " and b.usercode is not null ";
    }
    else if(isdown=="2")//未下载
    {
        if(usercode.isEmpty())
            ;
        else
            where += " and ( b.usercode='' or b.usercode is null)  ";
    }

    else
        ;
    qDebug()<<where;//2017-7-2 by xxw
//    if(isdown=="0")
//        ;
//    else if(isdown=="1")
//        where += " and b.usercode =? ";
//    else if(isdown=="2")
//        where += " and (b.usercode != ? or b.usercode='' or b.usercode is null)  ";
//    else
//        ;
    QSqlQuery query;
    QString sql = "select a.packid,a.packnum,a.isp,a.updatedays,a.dlcount,b.packno \
            from pack_realnum_tmp a left join USER.SHKJ_ZXSH_TASK_PACKNO b on a.packid=b.packno "\
            + where + " order by a.updatedays asc ,random()";
//    if(isdown=="1"||isdown=="2")
//    {
//        query.prepare(sql);
//        query.addBindValue(usercode);
//        query.exec();
//    }
//    else
//    {
//        query.exec(sql);
//    }

    query.exec(sql);

    QStringList list;
    QSqlRecord rec = query.record();
    int col = rec.count();
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            QString data;
            if(!query.value(i).isNull())
            {
                data = query.value(i).toString();
            }
            if(i==col-1)
            {
                info += data;
            }
            else
            {
                info += data + ",";
            }
        }
        list<<info;
    }

    return list;
}

bool ZxshDB::validatePacknoSelected(QString packno, QString usercode)
{
    if (usercode.isEmpty())
        return false;
    QSqlQuery query;
    //判断用户是否下载的标准，改为不再区分用户，只要在本机上下载过，就认为是下载过
    //modify by xxw on 2016-5-23
    query.prepare("SELECT TASKID from USER.SHKJ_ZXSH_TASK_PACKNO where packno = ?");
    //query.prepare("SELECT TASKID from USER.SHKJ_ZXSH_TASK_PACKNO where usercode=? and packno = ?");
    //query.addBindValue(usercode);
    query.addBindValue(packno);
    query.exec();

    if(query.next())
    {
        return true;
    }
    else
    {
        return false;
    }
}

bool ZxshDB::validateExistUserPackno(QString usercode)
{
    QSqlQuery query;
    query.prepare("SELECT TASKID from USER.SHKJ_ZXSH_TASK_PACKNO where usercode=?");
    query.addBindValue(usercode);
    query.exec();

    if(query.next())
    {
        return true;
    }
    else
    {
        return false;
    }
}

bool ZxshDB::deletePackRealPnum()
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from Pack_RealNum_tmp");
    QSqlDatabase::database().commit();
}