#include "sncdatabase.h"
#include <QtSql>

SNCDataBase::SNCDataBase()
{
}

bool SNCDataBase::createConnection(QString password, QString path)
{
    if(!QFile::exists("sys.db") || !QFile::exists("mseg.db") || !QFile::exists(path + "/user.db") )
    {
        qDebug()<<"Cannot Find Db File";
        return false;
    }
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

    db.setDatabaseName(":memory:");
    db.setPassword(password);
    if (!db.open()) {
        qDebug()<<"DB Open Error;"<<db.lastError();
        return false;
    }
    QSqlQuery query;
    query.exec(QString("ATTACH DATABASE 'mseg.db' AS num"));
    query.exec(QString("ATTACH DATABASE 'sys.db' AS sys"));
    query.exec(QString("ATTACH DATABASE '%1/user.db' AS user").arg(path));
    query.exec("create table tmp_number (seg char(7))");
    query.exec("CREATE TABLE SEG_DYNAMIC_UPDATE_LOG (areacode VARCHAR(4),LAST_CHECK_DATE VARCHAR(20))");
    query.exec("CREATE TABLE SEG_DYNAMIC (seg char(7)  primary key,S1 numberic,S2 numberic,S3 numberic,S4 numberic,sample_index numberic,recommend_index numberic,check_day numberic)");

    query.exec("select name from sys.sqlite_master where type='table' and name='CONFIG'");
    if(!query.next())
    {
        return false;
    }
    query.exec("select name from num.sqlite_master where type='table' and name='MSEG'");
    if(!query.next())
    {
        return false;
    }
    query.exec("select name from user.sqlite_master where type='table' and name='NUMBER_RULES_USER'");
    if(!query.next())
    {
        return false;
    }
    query.exec("select name from user.sqlite_master where type='table' and name='FILE_UPLOAD'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.FILE_UPLOAD(\
                   FILE_ID INTEGER PRIMARY KEY,FILE_NAME VARCHAR(100),FILE_PATH VARCHAR(500),\
                   FILE_MEMO VARCHAR(100),FILE_TYPE CHAR(1),TASK_TYPE CHAR(1),\
                   MODULE_ID VARCHAR(100),CLIENT_ID VARCHAR(100),USER_CODE VARCHAR(100),\
                   UPLOAD_STATE INTEGER,SERVER_FID VARCHAR(100),SERVER_RTIME VARCHAR(100),\
                   ISVERIFY,OPT_STATUS CHAR(1),TF_TIME VARCHAR(100),PERCENT NUMERIC,\
                   RTN_MSG VARCHAR(1000),RTN_DETAIL TEXT\
                   )");
    }
    query.exec("select name from user.sqlite_master where type='table' and name='SHKJ_CHECK'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.SHKJ_CHECK(\
                   USER_CODE VARCHAR(100),LTF_ID VARCHAR(100) PRIMARY KEY,QUERY_CODE VARCHAR(100),DETAIL VARCHAR(500),\
                   HMZS INTEGER,LTF_TJ VARCHAR(100),LTF_LIST VARCHAR(100),FORCAST_TJ VARCHAR(500),\
                   FORCAST_LIST VARCHAR(100),INSERT_TIME VARCHAR(100),FINISH_TIME VARCHAR(100),\
                   DEAL_STATUS CHAR(1),CACHE_LOCAL_PATH VARCHAR(1000)\
                   )");
    }
    //query.exec("drop table USER.SHXZ_SHKJ");
    query.exec("select name from user.sqlite_master where type='table' and name='SHXZ_SHKJ'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.SHXZ_SHKJ(\
                   USER_CODE VARCHAR(100),LTF_ID VARCHAR(100) PRIMARY KEY,QUERY_CODE VARCHAR(100),DETAIL VARCHAR(500),\
                   HMZS INTEGER,LTF_TJ VARCHAR(100),LTF_LIST VARCHAR(100),FORCAST_TJ VARCHAR(500),\
                   FORCAST_LIST VARCHAR(100),INSERT_TIME VARCHAR(100),FINISH_TIME VARCHAR(100),\
                   DEAL_STATUS CHAR(1),CACHE_LOCAL_PATH VARCHAR(1000),MEMO VARCHAR(1000),ISDEL CHAR(1)\
                   )");
    }
    query.exec("select name from user.sqlite_master where type='table' and name='USER_LOGON'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.USER_LOGON(\
                   ID INTEGER PRIMARY KEY,USER_CODE VARCHAR(100),PASSWORD VARCHAR(100),\
                   ISAUTO CHAR(1),ISREMEMBER CHAR(1),LAST_LOGIN_TIME VARCHAR(100)\
                   )");
    }
    query.exec("select name from user.sqlite_master where type='table' and name='BZJC_CHECK_DEALING'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.BZJC_CHECK_DEALING(\
                   BATCH_ID VARCHAR(100) PRIMARY KEY,BATCH_TYPE CHAR(1),CLIENT_ID VARCHAR(100),USER_CODE VARCHAR(100),\
                   DETAIL VARHCAR(500),RESULT_TJ VARHCAR(500),SUBMIT_TIME VARCHAR(100),TF_TIME VARCHAR(100),\
                   VF_TIME VARCHAR(100),ISVERIFY CHAR(1),SETTLE_FEE NUMERIC,GIFT_FEE NUMERIC ,\
                   NUM_COUNT INTEGER,DOWNLOAD_COUNT INTEGER,FINISH_COUNT INTEGER, OPT_STATUS CHAR(1)\
                   )");
    }
    query.exec("select name from user.sqlite_master where type='table' and name='BZJC_CHECK_FINISHED'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.BZJC_CHECK_FINISHED(\
                   BATCH_ID VARCHAR(100) PRIMARY KEY,BATCH_TYPE CHAR(1),CLIENT_ID VARCHAR(100),USER_CODE VARCHAR(100),\
                   DETAIL VARHCAR(500),RESULT_TJ VARHCAR(500),SUBMIT_TIME VARCHAR(100),FINISH_TIME VARCHAR(100),\
                   SETTLE_FEE NUMERIC,GIFT_FEE NUMERIC ,\
                   NUM_COUNT INTEGER,DOWNLOAD_COUNT INTEGER,FINISH_COUNT INTEGER, OPT_STATUS CHAR(1),\
                   ISPASS CHAR(1),ISDELIVER CHAR(1),DELIVER_TIME VARCHAR(100),CACHE_LOCAL_PATH VARCHAR(1000)\
                   )");
    }

    //add by xxw on 2014-10-30 for shkj-zxsh-task
    //query.exec("drop table USER.SHKJ_ZXSH_TASK");
    query.exec("select name from user.sqlite_master where type='table' and name='SHKJ_ZXSH_TASK'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.SHKJ_ZXSH_TASK(\
                   TASKID INTEGER PRIMARY KEY,\
                   SSID VARCHAR(100),\
                   INSERTTIME VARCHAR(100),\
                   FINISHTIME VARCHAR(100),\
                   PROVINCE VARCHAR(100),\
                   CITY VARCHAR(100),\
                   USERCODE VARCHAR(100),\
                   UPDATEDAYS NUMERIC,\
                   ISP CHAR(1),\
                   SLTTYPE CHAR(1),\
                   PACKNOLIST TEXT,\
                   SEG TEXT,\
                   NEEDNUM NUMERIC,\
                   RPNUM NUMERIC,\
                   DEALSTATUS CHAR(1),\
                   ISDEL CHAR(1))");
    }
    //query.exec("drop table USER.SHKJ_ZXSH_TASK_PACKNO");
    query.exec("select name from user.sqlite_master where type='table' and name='SHKJ_ZXSH_TASK_PACKNO'");
    if(!query.next())
    {
        query.exec("CREATE TABLE USER.SHKJ_ZXSH_TASK_PACKNO(\
                   TASKID INTEGER PRIMARY KEY,\
                   province VARCHAR(100),\
                   city VARCHAR(100),\
                   PACKNO VARCHAR(100),\
                   INSERTTIME VARCHAR(100),\
                   USERCODE VARCHAR(100))");
    }
//    query.exec("select name from user.sqlite_master where type='table' and name='SHKJ_ZXSH_TASK_SERVER'");
//    if(!query.next())
//    {
//        query.exec("CREATE TABLE USER.SHKJ_ZXSH_TASK_SERVER(\
//                   TASK_ID INTEGER PRIMARY KEY,SSID VARCHAR(100),INSERTTIME VARCHAR(100),FINISHTIME VARCHAR(100),\
//                   PROVINCE VARCHAR(100),CITY VARCHAR(100),USER_CODE VARCHAR(100),\
//                   UPDATEDAYS NUMERIC,ISP CHAR(1),SEG TEXT,NEED_NUM NUMERIC,REAL_NUM NUMERIC,DEAL_STATUS CHAR(1))");
//    }
//    query.exec("insert into user.shkj_zxsh_task (ssid,optime,uptime,user_code,province,city,isp,dlnum,deal_status,isdel)\
//               values('ssid','2014-12-21 13:13:13',7,'xxw','山东','济南','1',130,'1','N')");
    return true;
}


QString SNCDataBase::getProductName()
{
    return "极速号码魔方2019";
}
QString SNCDataBase::getAppName()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='app_name' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QString SNCDataBase::getLocalBannerAd()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='banner_ad' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getLocalBannerTopAd()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='banner_topad' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getLocalBannerBottomAd()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='banner_bottomad' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QString SNCDataBase::getLocalCachePath()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='cache_path' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getClientDefaultHost()
{
    QSqlQuery query;
    query.exec("select host  from sys.client_config where id='default' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getClientDefaultPort()
{
    QSqlQuery query;
    query.exec("select port  from sys.client_config where id='default' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getSplashTextColor()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='splashtext_color' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getUpdateAppName()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='appupdate_name' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getResourceName()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='resource_name' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getSplashImageName()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='splashimage_name' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getQssName()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='qss_name' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getWebsiteUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='website_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getWebsiteTitle()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='website_title' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getHelpmeUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='helpme_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getRiddleUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='riddle_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getZxshUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.page_ad where name='ad_app2_zxsh' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAppHash()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='apphash' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getJSJCpnumMinSize()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='JSJCpnumMinSize' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    if(!QRegExp("\\d+").exactMatch(value))
        return "1000";
    return value;
}

QString SNCDataBase::getJSJCpnumMaxSize()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='JSJCpnumMaxSize' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    if(!QRegExp("\\d+").exactMatch(value))
        return "500000";
    return value;
}

QString SNCDataBase::getJSJCmonthMaxNumFree()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='JSJCmonthMaxNum_free' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    if(!QRegExp("\\d+").exactMatch(value))
        return "100000";
    return value;
}

QString SNCDataBase::getJSJCmonthMaxNumPay()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='JSJCmonthMaxNum_pay' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    if(!QRegExp("\\d+").exactMatch(value))
        return "10000000";
    return value;
}

QString SNCDataBase::getSSORegUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='sso_reg_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getSSOAccountUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='sso_account_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getSSOPayUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='sso_pay_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getDefaultNumchkCorp()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='default_numchk_corp' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

void SNCDataBase::updateSSORegUrl(QString url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='sso_reg_url'");
    query.prepare("insert into sys.config values('sso_reg_url',?)");
    QVariant value = url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateSSOAccountUrl(QString url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='sso_account_url'");
    query.prepare("insert into sys.config values('sso_account_url',?)");
    QVariant value = url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateSSOPayUrl(QString url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='sso_pay_url'");
    query.prepare("insert into sys.config values('sso_pay_url',?)");
    QVariant value = url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateAppHash(QString apphash)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='apphash'");
    query.prepare("insert into sys.config values('apphash',?)");
    QVariant value = apphash;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateDefaultNumchkCorp(QString corp)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='default_numchk_corp'");
    query.prepare("insert into sys.config values('default_numchk_corp',?)");
    QVariant value = corp;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}
QString SNCDataBase::getExportDir()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='export_dir' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAppConfigUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='appconfig_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getDataConfigUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='dataconfig_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getImportDir()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='import_dir' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QStringList SNCDataBase::getConfigServerUrls()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='config_url' ");
    QStringList values;
    while(query.next())
    {
        values << query.value(0).toString();
    }
    return values;
}
QMap<QString,QString> SNCDataBase::getLocalTextAds()
{
    QSqlQuery query;
    QMap<QString,QString> ads;
    query.exec("select adurl,adtext  from sys.ad ");
    while(query.next())
    {
        ads.insert(query.value(0).toString(),query.value(1).toString());
    }
    return ads;
}

QMap<QString, QString> SNCDataBase::getLocalPageAds()
{
    QSqlQuery query;
    QMap<QString,QString> ads;
    query.exec("select ad_id,ad_url  from sys.page_ad ");
    while(query.next())
    {
        ads.insert(query.value(0).toString(),query.value(1).toString());
    }
    return ads;
}
QMap<QString,QStringList> SNCDataBase::getLocalClientConfigs()
{
    QSqlQuery query;
    QMap<QString,QStringList> configs;
    query.exec("select id,name,notice_url,host,port from sys.client_config ");
    while(query.next())
    {
        QStringList list;
        list<<query.value(1).toString()<<query.value(2).toString()<<query.value(3).toString()<<query.value(4).toString();
        configs.insert(query.value(0).toString(),list);
    }
    return configs;
}
QString SNCDataBase::getAppVersion()
{
    return "7.6.2";//必须是7.1.0三段
}

QString SNCDataBase::getAppVersion2()
{
    return "7.6.2";
}
QString SNCDataBase::getSegVersion()
{
    QSqlQuery query;
    query.exec("select value  from num.config where name='seg_version' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
void SNCDataBase::updateConfigUrls(QStringList config_urls)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='config_url'");
    query.prepare("insert into sys.config values(?,?)");
    QVariantList names,values;
    for(int i=0;i<config_urls.size();i++)
    {
        names<<"config_url";
        values<<config_urls.at(i);
    }
    query.addBindValue(names);
    query.addBindValue(values);
    query.execBatch();
    QSqlDatabase::database().commit();
}
void SNCDataBase::updateTextAd(QMap<QString, QString> ads)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.ad");
    query.prepare("insert into sys.ad values(?,?)");
    QVariantList urls,contents;
    foreach(QString key,ads.keys())
    {
        urls<<key;
        contents<<ads.value(key);
    }
    query.addBindValue(urls);
    query.addBindValue(contents);
    query.execBatch();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updatePageAd(QMap<QString, QString> ads)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.page_ad");
    query.prepare("insert into sys.page_ad values(?,?)");
    QVariantList ad_ids,ad_urls;
    foreach(QString key,ads.keys())
    {
        ad_ids<<key;
        ad_urls<<ads.value(key);
    }
    query.addBindValue(ad_ids);
    query.addBindValue(ad_urls);
    query.execBatch();
    QSqlDatabase::database().commit();
}


void SNCDataBase::updateCorpPrefix(QMap<QString, QString> prefixes)
{
    //qDebug()<<prefixes;
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.corp_prefix");
    query.prepare("insert into sys.corp_prefix values(?,?)");
    QVariantList corps,contents;
    foreach(QString key,prefixes.keys())
    {
        corps<<key;
        contents<<prefixes.value(key);
    }
    query.addBindValue(corps);
    query.addBindValue(contents);
    query.execBatch();
    QSqlDatabase::database().commit();
}
void SNCDataBase::updateClientConfig(QMap<QString, QStringList> configs, QString default_host, QString default_port)
{
    //qDebug()<<configs;
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.client_config");
    query.prepare("insert into sys.client_config values(?,?,?,?,?)");
    QVariantList ids, names,notice_urls,hosts,ports;
    ids<<"default";
    names<<"default";
    notice_urls<<"www.iyoogo.com";
    hosts<<default_host;
    ports<<default_port;
    foreach(QString key,configs.keys())
    {
        ids<<key;
        names<<configs.value(key).at(0);
        notice_urls<<configs.value(key).at(1);
        hosts<<configs.value(key).at(2);
        ports<<configs.value(key).at(3);
    }
    query.addBindValue(ids);
    query.addBindValue(names);
    query.addBindValue(notice_urls);
    query.addBindValue(hosts);
    query.addBindValue(ports);
    query.execBatch();
    QSqlDatabase::database().commit();
}
void SNCDataBase::updateBannerAd(QString banner_ad)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='banner_ad'");
    query.prepare("insert into sys.config values('banner_ad',?)");
    QVariant value = banner_ad;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateBannerTopAd(QString banner_ad)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='banner_topad'");
    query.prepare("insert into sys.config values('banner_topad',?)");
    QVariant value = banner_ad;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateBannerBottomAd(QString banner_ad)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='banner_bottomad'");
    query.prepare("insert into sys.config values('banner_bottomad',?)");
    QVariant value = banner_ad;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateHelpmeUrl(QString helpme_url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='helpme_url'");
    query.prepare("insert into sys.config values('helpme_url',?)");
    QVariant value = helpme_url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateWebsiteUrl(QString website_url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='website_url'");
    query.prepare("insert into sys.config values('website_url',?)");
    QVariant value = website_url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateWebsiteTitle(QString website_title)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='website_title'");
    query.prepare("insert into sys.config values('website_title',?)");
    QVariant value = website_title;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateRiddleUrl(QString url)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='riddle_url'");
    query.prepare("insert into sys.config values('riddle_url',?)");
    QVariant value = url;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}
void SNCDataBase::updateExportDir(QString export_dir)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='export_dir'");
    query.prepare("insert into sys.config values('export_dir',?)");
    QVariant value = export_dir;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateImportDir(QString dir)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from sys.config where name='import_dir'");
    query.prepare("insert into sys.config values('import_dir',?)");
    QVariant value = dir;
    query.addBindValue(value);
    query.exec();
    QSqlDatabase::database().commit();
}
void SNCDataBase::updateSegInfo(QString version,QString baseSegFile,QString dynamicSegFile)
{
    QFile::copy(QCoreApplication::applicationDirPath()+"/mseg.db",QCoreApplication::applicationDirPath()+"/mseg2.db");
    //QSqlQuery query(QSqlDatabase::database("mseg2"));
    QSqlQuery query;
    query.exec("ATTACH DATABASE 'mseg2.db' AS num2");
    QFile file(baseSegFile);
    file.open(QIODevice::ReadOnly);
    QTextStream in(&file);
    QVariantList seg,province,city;
    QSqlDatabase::database().transaction();
    while(!in.atEnd())
    {
        QString line=in.readLine();
        QStringList fields = line.split(",");
        int i=0;
        seg << fields.at(i++);
        province << fields.at(i++);
        city << fields.at(i++);
    }
    file.close();
    query.exec("drop table MSEG_tmp");
    query.exec("CREATE TABLE MSEG_tmp (seg char(7),province varchar(20),city varchar(50))");
    query.prepare("INSERT INTO MSEG_tmp values(?,?,?)");
    query.addBindValue(seg);
    query.addBindValue(province);
    query.addBindValue(city);
    query.execBatch();
    query.exec("delete from num2.mseg where seg in (select seg from MSEG_tmp )");
    query.exec("insert into num2.mseg select * from MSEG_tmp");
    query.exec("delete from num2.city");
    query.exec("insert into num2.CITY  select province,city,count(*) as total from num2.mseg group by province,city");
    query.finish();
    QFile file2(dynamicSegFile);
    file2.open(QIODevice::ReadOnly);
    QTextStream in2(&file2);
    QVariantList seg2,total,s1,s2,s3,s4;
    while(!in2.atEnd())
    {
        QString line=in2.readLine();
        QStringList fields = line.split(",");
        int i=0;
        seg2 << fields.at(i++);
        total << fields.at(i++);
        s1 << fields.at(i++);
        s2 << fields.at(i++);
        s3 << fields.at(i++);
        s4 << fields.at(i++);
    }
    file2.close();
    query.exec("drop table SEG_DYNAMIC_tmp");
    query.exec("CREATE TABLE SEG_DYNAMIC_tmp (seg char(7),total int,S1 numberic,S2 numberic,S3 numberic,S4 numberic)");
    query.prepare("insert into SEG_DYNAMIC_tmp(seg,total,s1,s2,s3,s4) values(?,?,?,?,?,?)");
    query.addBindValue(seg2);
    query.addBindValue(total);
    query.addBindValue(s1);
    query.addBindValue(s2);
    query.addBindValue(s3);
    query.addBindValue(s4);
    query.execBatch();
    query.exec("delete from num2.SEG_DYNAMIC where seg in (select seg from SEG_DYNAMIC_tmp )");
    query.exec("insert into num2.seg_dynamic select * from SEG_DYNAMIC_tmp");
    query.exec("delete from num2.config where name='seg_version'");
    query.finish();
    query.prepare("insert into num2.config(name,value) values('seg_version',?)");
    query.addBindValue(version);
    query.exec();
    QSqlDatabase::database().commit();
}

void SNCDataBase::updateBaseSegInfo(QString version, QString baseSegFile)
{
    QSqlQuery query;
    QFile file(baseSegFile);
    file.open(QIODevice::ReadOnly);
    QTextStream in(&file);
    QVariantList seg,province,city,areacode;

    QTime t;
    t.start();

    while(!in.atEnd())
    {
        QString line=in.readLine();
        QStringList fields = line.split(",");
        int i=0;
        seg << fields.at(i++);
        province << fields.at(i++);
        city << fields.at(i++);
        areacode << fields.at(i++);
    }
    qDebug()<<"start1 read"<<t.elapsed();
    t.start();

    file.close();
    QSqlDatabase::database().transaction();
    query.exec("delete from num.mseg");
    query.finish();
    QSqlDatabase::database().commit();
    qDebug()<<"start2 read"<<t.elapsed();
    t.start();
    QSqlDatabase::database().transaction();
    query.prepare("INSERT INTO num.mseg values(?,?,?,?)");
    query.addBindValue(seg);
    query.addBindValue(province);
    query.addBindValue(city);
    query.addBindValue(areacode);
    query.execBatch();
    query.exec("delete from num.city");
    query.exec("insert into num.CITY  select province,city,areacode,count(*) as total from num.mseg group by province,city,areacode");
    query.finish();
    query.exec("delete from num.config where name='seg_version'");
    query.finish();
    query.prepare("insert into num.config(name,value) values('seg_version',?)");
    query.addBindValue(version);
    query.exec();
    QSqlDatabase::database().commit();
    qDebug()<<"start3 read"<<t.elapsed();
}
void SNCDataBase::updateDynamicSegInfo(QString checkdate,QString areacode,QString dynamicSegFile)
{
    QSqlQuery query;
    QFile file2(dynamicSegFile);
    file2.open(QIODevice::ReadOnly);
    QTextStream in2(&file2);
    QSqlDatabase::database().transaction();
    QVariantList seg2,s1,s2,s3,s4,index1,index2,checkday;
    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++);
        index1 << fields.at(i++);
        index2 << fields.at(i++);
        checkday << fields.at(i++);
    }
    file2.close();
    query.exec("drop table SEG_DYNAMIC_tmp");
    query.exec("CREATE TABLE SEG_DYNAMIC_tmp (seg char(7),S1 numberic,S2 numberic,S3 numberic,S4 numberic,sample_index numberic,recommend_index numberic,check_day numberic)");
    query.finish();
    query.prepare("insert into SEG_DYNAMIC_tmp(seg,s1,s2,s3,s4,sample_index,recommend_index,check_day) values(?,?,?,?,?,?,?,?)");
    query.addBindValue(seg2);

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

    query.addBindValue(index1);
    query.addBindValue(index2);
    query.addBindValue(checkday);
    query.execBatch();

    query.finish();
    query.exec("delete from SEG_DYNAMIC where seg in (select seg from SEG_DYNAMIC_tmp )");
    query.exec("insert into SEG_DYNAMIC select * from SEG_DYNAMIC_tmp");

    query.finish();
    query.prepare("delete from SEG_DYNAMIC_UPDATE_LOG where areacode=?");
    query.addBindValue(areacode);
    query.exec();
    query.finish();
    query.prepare("insert into SEG_DYNAMIC_UPDATE_LOG(areacode,LAST_CHECK_DATE) values(?,?)");
    query.addBindValue(areacode);
    query.addBindValue(checkdate);
    query.exec();
    QSqlDatabase::database().commit();
}


void SNCDataBase::updateDynamicSegInfo(QString checkdate, QStringList list)
{
    QSqlQuery query;
    QVariant seg2,s1,s2,s3,s4,index1,index2,checkday;
    int i=0;
    seg2 = list.at(i++);
    s1 = list.at(i++);
    s2 = list.at(i++);
    s3 = list.at(i++);
    s4 = list.at(i++);
    index1 = list.at(i++);
    index2 = list.at(i++);
    checkday = list.at(i++);
    query.exec("drop table SEG_DYNAMIC_tmp");
    query.exec("CREATE TABLE SEG_DYNAMIC_tmp (seg char(7),S1 numberic,S2 numberic,S3 numberic,S4 numberic,sample_index numberic,recommend_index numberic,check_day numberic)");
    query.finish();
    query.prepare("insert into SEG_DYNAMIC_tmp(seg,s1,s2,s3,s4,sample_index,recommend_index,check_day) values(?,?,?,?,?,?,?,?)");
    query.addBindValue(seg2);

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

    query.addBindValue(index1);
    query.addBindValue(index2);
    query.addBindValue(checkday);
    query.exec();

    query.finish();
    query.exec("delete from SEG_DYNAMIC where seg in (select seg from SEG_DYNAMIC_tmp )");
    query.exec("insert into SEG_DYNAMIC select * from SEG_DYNAMIC_tmp");

    query.finish();

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

void SNCDataBase::updateSegRecInfo(QString SegFile)
{

    QFile file(SegFile);
    file.open(QIODevice::ReadOnly);
    QTextStream in(&file);

    QVariantList seg,province,city,zipcode,s1,s2,s3,s4,sample_index,rec_index,check_day;
    while(!in.atEnd())
    {
        QString line=in.readLine();
        QStringList fields = line.split(",");
        int i=0;
        seg << fields.at(i++);
        province << fields.at(i++);
        city << fields.at(i++);
        zipcode << fields.at(i++);
        s1 << fields.at(i++);
        s2 << fields.at(i++);
        s3 << fields.at(i++);
        s4 << fields.at(i++);
        sample_index << fields.at(i++);
        rec_index << fields.at(i++);
        check_day << fields.at(i++);

    }
    file.close();
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from SEG_rec");
    query.finish();
    query.prepare("insert into SEG_rec(seg,province,city,zipcode,s1,s2,s3,s4,sample_index,rec_index,check_day) values(?,?,?,?,?,?,?,?,?,?,?)");
    query.addBindValue(seg);
    query.addBindValue(province);
    query.addBindValue(city);
    query.addBindValue(zipcode);
    query.addBindValue(s1);
    query.addBindValue(s2);
    query.addBindValue(s3);
    query.addBindValue(s4);
    query.addBindValue(sample_index);
    query.addBindValue(rec_index);
    query.addBindValue(check_day);

    query.execBatch();
    query.finish();
    QSqlDatabase::database().commit();
}


QString SNCDataBase::getAreaCodeByCityName(QString city)
{
    QSqlQuery query;
    query.prepare("select areacode  from num.city where city=? ");
    query.addBindValue(city);
    query.exec();
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getCitySegLastCheckDate(QString areacode)
{
    QSqlQuery query;
    query.prepare("select last_check_date  from seg_dynamic_update_log where areacode = ? ");
    query.addBindValue(areacode);
    query.exec();
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QList<QStringList> SNCDataBase::getRegionInfo()
{
    QSqlQuery query;
    QList<QStringList> maps;
    QStringList citys;
    QStringList provinces;
    query.exec("select province,city  from num.city order by province desc ");
    while(query.next())
    {
        QString province = query.value(0).toString();
        QString city = query.value(1).toString();
        citys<<city;
        provinces<<province;
    }
    maps.append(citys);
    maps.append(provinces);
    return maps;
}
QStringList SNCDataBase::getSegmentByCityAndRange(QString city, QString start, QString end)
{
    QString where = " where  city = '" + city + "' ";
    where +=" and seg>= '"  +start + "' and seg <='" + end +"'";
    QSqlQuery query;
    QStringList list;
    query.exec("select seg from num.mseg " + where + "  order by seg asc");
    //qDebug()<<"select seg from num.mseg " + where + " ";
    while(query.next())
        list<<query.value(0).toString();
    return list;
}
QStringList SNCDataBase::getSegmentByCityAll(QString city)
{
    QString where = " where  city = '"+city+"' ";
    QSqlQuery query;
    query.exec("select seg from num.mseg " + where + "  order by seg asc");
    QStringList list;
    while(query.next())
        list<<query.value(0).toString();
    return list;
}
QStringList SNCDataBase::getSegmentByCityAndCorp(QString city, QString corp)
{
    QString where = " where  city = '"+city+"' ";
    QString cmcc,cutc,ctcc;
    cmcc = getCMCC();
    cutc = getCUTC();
    ctcc = getCTCC();
    if(corp=="CMCC")
        where +=" and substr(seg,1,3) in ("+cmcc+")";
    else if(corp=="CUTC")
        where +=" and substr(seg,1,3) in ("+cutc+")";
    else if(corp=="CTCC")
        where +=" and substr(seg,1,3) in ("+ctcc+")";
    else
        where +=" and 0>1 ";
    QSqlQuery query;
    query.exec("select seg from num.mseg " + where + "  order by seg asc ");
    QStringList list;
    while(query.next())
        list<<query.value(0).toString();
    return list;
}
QString  SNCDataBase::getCMCC()
{
    QSqlQuery query;
    query.exec("select prefix  from sys.corp_prefix where corp='CMCC' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QString  SNCDataBase::getCUTC()
{
    QSqlQuery query;
    query.exec("select prefix  from sys.corp_prefix where corp='CUTC' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QString SNCDataBase::getCTCC()
{
    QSqlQuery query;
    query.exec("select prefix  from sys.corp_prefix where corp='CTCC' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}
QStringList SNCDataBase::getSegmentDynamicByCityAndRange(QString city, QString start, QString end)
{
    QString where = " where  a.city = '" + city + "' ";
    where +=" and a.seg>= '"  +start + "' and a.seg <='" + end +"'";
    QSqlQuery query;
    QStringList list;
    query.exec("select a.seg,b.s1,b.s2,b.s3,b.s4,b.sample_index,b.recommend_index,b.check_day from num.mseg a LEFT JOIN seg_dynamic b on a.seg = b.seg " + where + " ");
    QSqlRecord rec = query.record();
    int col = rec.count();
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            if(query.value(i).isNull())
            {
                if(i==col-1)
                    info += "-1,";
                else
                    info += "0,";
            }
            else
            {
                info += query.value(i).toString()+",";
            }
        }
        list<<info;
    }
    return list;
}

QStringList SNCDataBase::getSegmentDynamic(QString city, QString corp, int days)
{
    QString where = " where  a.city = '"+city+"' ";
    QString cmcc,cutc,ctcc;
    cmcc = getCMCC();
    cutc = getCUTC();
    ctcc = getCTCC();
    if(corp=="CMCC")
        where +=" and substr(a.seg,1,3) in ("+cmcc+")";
    else if(corp=="CUTC")
        where +=" and substr(a.seg,1,3) in ("+cutc+")";
    else if(corp=="CTCC")
        where +=" and substr(a.seg,1,3) in ("+ctcc+")";
    else
        where +=" and 1>0 ";
    where += " and b.check_day<" + QString::number(days);
    QSqlQuery query;
    query.exec("select a.seg,b.s1,b.s2,b.s3,b.s4,b.sample_index,b.recommend_index,b.check_day from num.mseg a LEFT JOIN seg_dynamic b on a.seg = b.seg " + where + " ");
    QSqlRecord rec = query.record();
    int col = rec.count();
    QStringList list;
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            if(query.value(i).isNull())
            {
                if(i==col-1)
                    info += "-1,";
                else
                    info += "0,";
            }
            else
            {
                info += query.value(i).toString()+",";
            }
        }
        list<<info;
    }
    return list;
}
QStringList SNCDataBase::getSegmentDynamicByCityAll(QString city)
{
    QString where = " where  city = '"+city+"' ";
    QSqlQuery query;

    query.exec("select a.seg,b.s1,b.s2,b.s3,b.s4,b.sample_index,b.recommend_index,b.check_day from ( select seg from num.mseg "+where +" ) a LEFT JOIN seg_dynamic b on a.seg = b.seg " );
    //query.exec("select a.seg,b.total,b.s1,b.s2,b.s3,b.s4 from num.mseg a LEFT JOIN seg_dynamic b on a.seg = b.seg " + where + " ");
    QStringList list;
    QSqlRecord rec = query.record();
    int col = rec.count();
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            if(query.value(i).isNull())
            {
                if(i==col-1)
                    info += "-1,";
                else
                    info += "0,";
            }
            else
            {
                info += query.value(i).toString()+",";
            }
        }
        list<<info;
    }
    return list;
}
QStringList SNCDataBase::getSegmentDynamicByCityAndCorp(QString city, QString corp)
{
    QString where = " where  a.city = '"+city+"' ";
    QString cmcc,cutc,ctcc;
    //cmcc = "'134','135','136','137','138','139','147','150','151','152','154','157','158','159','182','183','187','188'";
    //cutc = "'130','131','132','145','155','156','185','186'";
    //ctcc = "'133','153','180','189'";
    cmcc = getCMCC();
    cutc = getCUTC();
    ctcc = getCTCC();
    if(corp=="CMCC")
        where +=" and substr(a.seg,1,3) in ("+cmcc+")";
    else if(corp=="CUTC")
        where +=" and substr(a.seg,1,3) in ("+cutc+")";
    else if(corp=="CTCC")
        where +=" and substr(a.seg,1,3) in ("+ctcc+")";
    else
        where +=" and 0>1 ";
    QSqlQuery query;
    query.exec("select a.seg,b.s1,b.s2,b.s3,b.s4,b.sample_index,b.recommend_index,b.check_day from num.mseg a LEFT JOIN seg_dynamic b on a.seg = b.seg " + where + " ");
    QSqlRecord rec = query.record();
    int col = rec.count();
    QStringList list;
    while(query.next())
    {
        QString info="";
        for(int i=0;i<col;i++)
        {
            if(query.value(i).isNull())
            {
                if(i==col-1)
                    info += "-1,";
                else
                    info += "0,";
            }
            else
            {
                info += query.value(i).toString()+",";
            }
        }
        list<<info;
    }
    return list;
}
QMap<QString,QString> SNCDataBase::groupSegmentIntoCity(QStringList segments)
{
    QSqlQuery query;
    query.exec("truncate table tmp_number");
    query.prepare("insert into tmp_number values(?)");
    QVariantList varList;
    for(int i=0;i<segments.size();i++)
    {
        varList<<segments.at(i);
    }
    query.addBindValue(varList);
    QSqlDatabase::database().transaction();
    query.execBatch();
    QSqlDatabase::database().commit();
    query.exec("select a.seg,b.city from  tmp_number a inner join num.mseg b on a.seg =b.seg ");
    QString city;
    QString segment;
    QMap<QString,QString> mapSegments;
    while(query.next())
    {
        segment = query.value(0).toString();
        city = query.value(1).toString();
        if(city.isEmpty())
            city="未知地区";
        mapSegments.insert(segment,city);
    }
    return mapSegments;
}
QMap<QString,QString> SNCDataBase::groupSegmentIntoProvince(QStringList segments)
{
    QSqlQuery query;
    query.exec("truncate table tmp_number");
    query.prepare("insert into tmp_number values(?)");
    QVariantList varList;
    for(int i=0;i<segments.size();i++)
    {
        varList<<segments.at(i);
    }
    query.addBindValue(varList);
    QSqlDatabase::database().transaction();
    query.execBatch();
    QSqlDatabase::database().commit();
    query.exec("select a.seg,b.province from  tmp_number a inner join num.mseg b on a.seg =b.seg ");
    QString province;
    QString segment;
    QMap<QString,QString> mapSegments;
    while(query.next())
    {
        segment = query.value(0).toString();
        province = query.value(1).toString();
        if(province.isEmpty())
            province="未知地区";
        mapSegments.insert(segment,province);
    }
    return mapSegments;
}

QMap<int ,QString> SNCDataBase::getSysSolutions()
{
    QString where = "  ";
    QSqlQuery query;
    query.exec("select sln_id,name from sys.NUMBER_RULES_SYSTEM " + where + " ");
    QMap<int,QString> map;
    while(query.next())
    {
        map.insert(query.value(0).toInt(),query.value(1).toString());
    }
    return map;
}
QMap<int ,QString> SNCDataBase::getUserSolutions()
{
    QString where = "  ";
    QSqlQuery query;
    query.exec("select sln_id,name from USER.NUMBER_RULES_USER " + where + " ");
    QMap<int,QString> map;
    while(query.next())
    {
        map.insert(query.value(0).toInt(),query.value(1).toString());
    }
    return map;
}
QMap<int ,QString> SNCDataBase::getAllRuleNames()
{
    QString where = "  ";
    QSqlQuery query;
    query.exec("select RULE_ID,name from SYS.NUMBER_RULES " + where + " ");
    QMap<int,QString> map;
    while(query.next())
    {
        map.insert(query.value(0).toInt(),query.value(1).toString());
    }
    return map;
}
QList<int> SNCDataBase::getRuleIdBySysSlnID(int id)
{
    QString where = " where sln_id=  " + QString::number(id);
    QSqlQuery query;
    query.exec("select rule_id from sys.NUMBER_RULES_SYSTEM_MAP " + where + " ");
    QList<int> list;
    while(query.next())
    {
        list.append(query.value(0).toInt());
    }
    return list;
}
 QList<int> SNCDataBase::getRuleIdByUserSlnID(int id)
{
    QString where = " where sln_id=  " + QString::number(id);
    QSqlQuery query;
    query.exec("select rule_id from user.NUMBER_RULES_USER_MAP " + where + " ");
    QList<int> list;
    while(query.next())
    {
        list.append(query.value(0).toInt());
    }
    return list;
}
QStringList SNCDataBase::getRulesBySysSlnID(int id)
{
    QString where = " where a.sln_id =  " + QString::number(id);
    QSqlQuery query;
    query.exec("select b.regex from SYS.NUMBER_RULES_SYSTEM_MAP a left join SYS.NUMBER_RULES b on a.RULE_id = b.RULE_id " + where + " ");
    QStringList list;
    while(query.next())
    {
        list<<query.value(0).toString();
    }
    return list;
}
QStringList SNCDataBase::getRulesByUserSlnID(int id)
{
    QString where = " where a.sln_id =  " + QString::number(id);
    QSqlQuery query;
    query.exec("select b.regex from USER.NUMBER_RULES_USER_MAP a left join SYS.NUMBER_RULES b on a.RULE_id = b.RULE_id " + where + " ");
    QStringList list;
    while(query.next())
    {
        list<<query.value(0).toString();
    }
    return list;
}
void SNCDataBase::addUserSolution(QString name, QStringList ruleids)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("insert into user.NUMBER_RULES_USER(name) values(?)");
    QVariant v = name;
    query.addBindValue(v);
    query.exec();
    query.exec("select last_insert_rowid()");
    QString sln_id;
    if(query.next())
        sln_id = query.value(0).toString();
    query.prepare("insert into user.NUMBER_RULES_USER_MAP(sln_id,rule_id) values(?,?)");
    QVariantList rule_ids,sln_ids;
    for(int i=0;i<ruleids.size();i++)
    {
        rule_ids << ruleids.at(i);
        sln_ids << sln_id;
    }
    query.addBindValue(sln_ids);
    query.addBindValue(rule_ids);
    query.execBatch();
    QSqlDatabase::database().commit();
}
void SNCDataBase::delUserBuildSolution(int build_id)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from user.NUMBER_BUILD_HISTORY where BUILD_ID="+QString::number(build_id));
    QSqlDatabase::database().commit();
}
void SNCDataBase::delAllUserBuildSolution()
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from user.NUMBER_BUILD_HISTORY ");
    QSqlDatabase::database().commit();
}
void SNCDataBase::delUserSolution(int sln_id)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("delete from user.NUMBER_RULES_USER_MAP WHERE SLN_ID="+QString::number(sln_id));
    query.exec("delete from user.NUMBER_RULES_USER WHERE SLN_ID="+QString::number(sln_id));
    QSqlDatabase::database().commit();
}
void SNCDataBase::addBuildSolution(QString build_date, QString region, int total,
                                QString segments, QString rule_ids)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.exec("select count(*) as total from user.NUMBER_BUILD_HISTORY");
    if(query.next())
    {
        int total = query.value(0).toInt();
        if(total>=20)
        {
            query.exec("delete from user.NUMBER_BUILD_HISTORY where build_id in (select min(build_id) from user.NUMBER_BUILD_HISTORY )");
        }
    }
    query.prepare("insert into user.NUMBER_BUILD_HISTORY(BUILD_DATE,REGION,TOTAL,SEGMENTS,RULE_IDS) values(?,?,?,?,?)");
    QVariant v1,v2,v3,v4,v5;
    v1 = build_date;
    v2 = region;
    v3 = total;
    v4 = segments;
    v5 = rule_ids;
    query.addBindValue(v1);
    query.addBindValue(v2);
    query.addBindValue(v3);
    query.addBindValue(v4);
    query.addBindValue(v5);
    query.exec();
    QSqlDatabase::database().commit();
}
QList<QStringList> SNCDataBase::getAllBuildHistory()
{
    QSqlQuery query;
    query.exec("select build_date,region,total,build_id,segments,rule_ids from user.NUMBER_BUILD_HISTORY order by build_id desc ");
    QList<QStringList> list;
    while(query.next())
    {
        QStringList l;
        l<< query.value(0).toString();
        l<< query.value(1).toString();
        l<< query.value(2).toString();
        l<< query.value(3).toString();
        l<< query.value(4).toString();
        l<< query.value(5).toString();
        list.append(l);
    }
    return list;
}
QStringList SNCDataBase::getRuleContensByRuleIDs(QStringList ids)
{
    QSqlQuery query;
    QString where;
    for(int i=0;i<ids.size();i++)
    {
        where +=ids.at(i)+",";
    }
    where +="0";
    query.exec("select regex from sys.NUMBER_RULES where RULE_ID in ("+where+")");
    QStringList list;
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
    return list;
}

QString SNCDataBase::getSegmentsGroupCount(QVariantList vars,int len)
{
     QSqlQuery query;
     QSqlDatabase::database().transaction();
     query.exec("drop table tmp_segs_for_count");
     query.exec("create table tmp_segs_for_count (seg varchar(7))");
     query.prepare("insert into tmp_segs_for_count (seg) values(?)");
     query.addBindValue(vars);
     query.execBatch();
     QSqlDatabase::database().commit();
     query.exec(QString("select substr(seg,1,%1),count(*) as total from tmp_segs_for_count group by substr(seg,1,%1)").arg(len));
     QString str;
     int total = 0;
     while(query.next())
     {
         if(query.value(1).toInt()>500)
         {
            str += query.value(0).toString()+","+query.value(1).toString()+";";
            total += query.value(1).toInt();
         }
     }
     if(total<vars.size()*0.7)
     {
        str = "";
     }
     QSqlDatabase::database().transaction();
     query.exec("delete from tmp_segs_for_count");
     QSqlDatabase::database().commit();

     return str;

}

QStringList SNCDataBase::getCityBySegment(QString segment)
{
    QSqlQuery query;
    QVariant var = segment;
    query.prepare("select province,city,areacode  from num.mseg where seg=? ");
    query.addBindValue(var);
    query.exec();
    QStringList values;
    if(query.next())
    {
        values << query.value(0).toString();
        values << query.value(1).toString();
        values << query.value(2).toString();
    }
    return values;
}

QMap<QString, QString> SNCDataBase::getSrvParam(QString srvid)
{
    QSqlQuery query;
    query.prepare("select srv_param_name,srv_param_value  from sys.srv_param where srv_id=?");
    QVariant v = srvid;
    query.addBindValue(v);
    query.exec();
    QMap<QString,QString> para;

    while(query.next())
    {
        para.insert(query.value(0).toString(),query.value(1).toString());
    }
    return para;
}

void SNCDataBase::updateSrvParam(QString srvid, QMap<QString, QString> para)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.clear();
    query.prepare("delete from sys.srv_param where srv_id=?");
    QVariant value = srvid;
    query.addBindValue(value);
    query.exec();
    foreach(QString key ,para.keys())
    {
        if(!key.isEmpty() && !para.value(key).isEmpty())
        {
            query.clear();
            query.prepare("insert into sys.srv_param values(?,?,?)");
            QVariant value1 = srvid;
            QVariant value2 = key;
            QVariant value3 = para.value(key);
            query.addBindValue(value1);
            query.addBindValue(value2);
            query.addBindValue(value3);
            query.exec();

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

int SNCDataBase::getSegCount()
{
    QSqlQuery query;
    query.exec("select sum(total) from num.city ");
    int total = 0;
    if(query.next())
    {
        total = query.value(0).toInt();
    }
    return total;
}

QString SNCDataBase::getAuthCodeSeg()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='authcode_seg' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAuthCodeSrvtime()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='authcode_srvtime' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAuthCodeUrl()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='authcode_url' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAuthCodeSyspara()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='authcode_syspara' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

QString SNCDataBase::getAuthCodeEncrpt()
{
    QSqlQuery query;
    query.exec("select value  from sys.config where name='authcode_encrypt' ");
    QString value="";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

int SNCDataBase::addFileUpload(QString moduleID, QString clientID, QString userCode, QString fileName,QString filePath, QString fileType, QString taskType,QString fileMemo)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("insert into user.FILE_UPLOAD(MODULE_ID,CLIENT_ID,USER_CODE,File_Name,\
                  FILE_PATH,FILE_TYPE,TASK_TYPE,FILE_MEMO,UPLOAD_STATE,OPT_STATUS,ISVERIFY)\
            values(?,?,?,?,?,?,?,?,0,'0','0')");
    QVariantList vars;
    vars<<moduleID<<clientID<<userCode<<fileName<<filePath<<fileType<<taskType<<fileMemo;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::updateFileUploadState(int fileID, int state)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("update user.FILE_UPLOAD set UPLOAD_STATE=? where file_id=?");
    QVariantList vars;
    vars<<state<<fileID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}
int SNCDataBase::updateFileUploadPercent(int fileID, double percent)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("update user.FILE_UPLOAD set percent=? where file_id=?");
    QVariantList vars;
    vars<<percent<<fileID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}
int SNCDataBase::updateFileUploadSrvInfo(int fileID, QString srvID,QString srvRTime)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("update user.FILE_UPLOAD set server_fid=? ,server_rtime =? where file_id=?");
    QVariantList vars;
    vars<<srvID<<srvRTime<<fileID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::updateFileUploadState(int fileID, int state, QString errMsg,QString errDetail)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("update user.FILE_UPLOAD set UPLOAD_STATE=? ,rtn_msg = ?,rtn_detail = ? where file_id=?");
    QVariantList vars;
    vars<<state<<errMsg<<errDetail<<fileID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::clearFileUpload(QString moduleID, QString clientID, QString userCode)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("delete * from  user.FILE_UPLOAD where module_ID=? and client_ID=? and user_code = ?");
    QVariantList vars;
    vars<<moduleID<<clientID<<userCode;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::deleteFileByFileID(int fileID)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("delete  from  user.FILE_UPLOAD where file_id = ?");
    QVariantList vars;
    vars<<fileID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

QMultiMap<QString, QString> SNCDataBase::getAllUpload(QString moduleID, QString clientID, QString userCode)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.prepare("select file_id,file_name,file_path,file_type,task_type,file_memo,\
                  UPLOAD_STATE,rtn_msg ,rtn_detail,percent,server_fid,server_rtime,\
                  isverify,opt_status,tf_time from user.file_upload \
               where module_ID=? and client_id=? and user_code=?");
    QVariantList vars;
    vars<<moduleID<<clientID<<userCode;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("fileid",query.value(i++).toString());
        values.insert("filename",query.value(i++).toString());
        values.insert("filepath",query.value(i++).toString());
        values.insert("filetype",query.value(i++).toString());
        values.insert("tasktype",query.value(i++).toString());
        values.insert("memo",query.value(i++).toString());
        values.insert("uploadstate",query.value(i++).toString());
        values.insert("rtnmsg",query.value(i++).toString());
        values.insert("rtndetail",query.value(i++).toString());
        values.insert("percent",query.value(i++).toString());
        values.insert("srvfid",query.value(i++).toString());
        values.insert("srvrtime",query.value(i++).toString());
        values.insert("isverify",query.value(i++).toString());
        values.insert("optstatus",query.value(i++).toString());
        values.insert("tftime",query.value(i++).toString());
    }
    return values;
}

int SNCDataBase::updateFileUploadOutdateDataBySrvIDs(QString moduleID,QString clientID,QString userCode,QStringList ids)
{
    QString where;
    for(int i=0;i<ids.size();i++)
    {
        if(i==ids.size()-1)
            where += QString("'%1'").arg(ids.at(i));
        else
            where += QString("'%1',").arg(ids.at(i));
    }
    if(where.isEmpty())
    {
        where = " where module_id = ? and client_id = ? and user_code = ? and  upload_state = 3 ";
    }
    else
    {
        where = " where module_id = ? and client_id = ? and user_code = ? and  upload_state = 3 and \
                server_fid not in (" + where + ")";
    }

    QString sql = "update user.file_upload set opt_status = -1 " + where;
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare(sql);
    query.addBindValue(moduleID);
    query.addBindValue(clientID);
    query.addBindValue(userCode);
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::updateShkjCheckInfos(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;
    }
    //add-on-2013-9-23 for delete outdate-shhj-check-info
    query.clear();
    query.exec("delete from  user.SHKJ_CHECK  where deal_status='0' or deal_status='1'");
    //end
    for(int i=0;i<rows;i++)
    {
        QString uqrycode = maps.values("uqrycode").at(i);
        QString detail = maps.values("detail").at(i);
        QString hmzs = maps.values("hmzs").at(i);
        QString ltf_tj = maps.values("ltf_tj").at(i);
        QString ltf_list = maps.values("ltf_list").at(i);
        QString forecast_tj = maps.values("forecast_tj").at(i);
        QString forecast_list = maps.values("forecast_list").at(i);
        QString inserttime = maps.values("inserttime").at(i);
        QString finishtime = maps.values("finishtime").at(i);
        QString dealstatus = maps.values("dealstatus").at(i);
        QString ltf_id = maps.values("ltfid").at(i);

//        if(dealstatus=="2" || dealstatus == "3")//该任务如果已经处理完毕或者下载，则删除上传记录表
//        {
//            query.clear();
//            query.prepare("delete from  user.FILE_UPLOAD  where SERVER_FID = ?");
//            query.addBindValue(ltf_id);
//            query.exec();
//        }
//        else if(dealstatus=="1")//该任务处理中，则更新optstatus 为 3（保持与标准检测的一致性）
//        {
//            query.clear();
//            query.prepare("update user.FILE_UPLOAD set isverify = 1, opt_status = '3'  where SERVER_FID = ?");
//            query.addBindValue(ltf_id);
//            query.exec();

//        }
//        else
//        {
//            query.clear();
//            query.prepare("update user.FILE_UPLOAD set isverify = '1', opt_status = ?  where SERVER_FID = ?");
//            query.addBindValue(dealstatus);
//            query.addBindValue(ltf_id);
//            query.exec();
//        }
        query.clear();
        query.prepare("delete from  user.FILE_UPLOAD  where SERVER_FID = ?");
        query.addBindValue(ltf_id);
        query.exec();
        query.clear();
        query.prepare("select CACHE_LOCAL_PATH  from  user.SHKJ_CHECK where LTF_ID = ?");
        query.addBindValue(ltf_id);
        query.exec();

        if(query.next())
        {
            if(!query.value(0).toString().isEmpty())
                continue;
        }
        query.clear();
        query.prepare("delete  from  user.SHKJ_CHECK where LTF_ID = ?");
        query.addBindValue(ltf_id);
        query.exec();

        query.clear();
        query.prepare("insert into user.shkj_check( \
                      QUERY_CODE,DETAIL,HMZS,LTF_TJ,LTF_LIST,FORCAST_TJ ,\
                      FORCAST_LIST,INSERT_TIME ,FINISH_TIME,DEAL_STATUS,LTF_ID,USER_CODE) \
                      VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
        QVariantList vars;
        vars<<uqrycode<<detail<<hmzs<<ltf_tj<<ltf_list<<forecast_tj<<forecast_list;
        vars<<inserttime<<finishtime<<dealstatus<<ltf_id<<userCode;
        for(int i=0;i<vars.size();i++)
        {
            query.addBindValue(vars.at(i));
        }
        query.exec();
    }


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

int SNCDataBase::updateZxshTaskInfos(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;
    }
    //删除现有的数据信息
    query.prepare("delete from  user.SHKJ_ZXSH_TASK_SERVER WHERE USER_CODE = ?");
    query.addBindValue(userCode);
    query.exec();
    query.clear();

    //添加新的数据信息
    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.prepare("insert into user.SHKJ_ZXSH_TASK_SERVER( \
                      ssid,deal_status,province,city,updatedays,isp ,\
                      need_num,real_num ,inserttime,finishtime,USER_CODE) \
                      VALUES(?,?,?,?,?,?,?,?,?,?,?)");
        QVariantList vars;
        vars<<ssid<<dealstatus<<province<<city<<updatedays<<isp<<neednum;
        vars<<rpnum<<inserttime<<finishtime;
        for(int i=0;i<vars.size();i++)
        {
            query.addBindValue(vars.at(i));
        }
        query.exec();
    }


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

QMultiMap<QString, QString> SNCDataBase::getShkjCheckInfos(QString userCode)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.prepare("select QUERY_CODE,DETAIL,HMZS,LTF_TJ,LTF_LIST,FORCAST_TJ ,\
                  FORCAST_LIST,INSERT_TIME ,FINISH_TIME,DEAL_STATUS,LTF_ID \
                  from user.shkj_check where user_code=?");
    QVariantList vars;
    vars<<userCode;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("uqrycode",query.value(i++).toString());
        values.insert("detail",query.value(i++).toString());
        values.insert("hmzs",query.value(i++).toString());
        values.insert("ltf_tj",query.value(i++).toString());
        values.insert("ltf_list",query.value(i++).toString());
        values.insert("forecast_tj",query.value(i++).toString());
        values.insert("forecast_list",query.value(i++).toString());
        values.insert("inserttime",query.value(i++).toString());
        values.insert("finishtime",query.value(i++).toString());
        values.insert("dealstatus",query.value(i++).toString());
        values.insert("ltfid",query.value(i++).toString());
    }
    return values;
}

QMultiMap<QString, QString> SNCDataBase::getShkjCheckInfos(QString userCode, QString dealstatus)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;

    QVariantList vars;
    if(dealstatus.isEmpty())
    {
//        query.prepare("select QUERY_CODE,DETAIL,HMZS,LTF_TJ,LTF_LIST,FORCAST_TJ ,\
//                      FORCAST_LIST,INSERT_TIME ,FINISH_TIME,DEAL_STATUS,LTF_ID \
//                      from user.shkj_check where user_code=? and (deal_status = '2' or deal_status='3')");
        query.prepare("select QUERY_CODE,DETAIL,HMZS,LTF_TJ,LTF_LIST,FORCAST_TJ ,\
                      FORCAST_LIST,INSERT_TIME ,FINISH_TIME,DEAL_STATUS,LTF_ID \
                      from user.shkj_check where user_code=? ");
        vars<<userCode;
    }
    else
    {
        query.prepare("select QUERY_CODE,DETAIL,HMZS,LTF_TJ,LTF_LIST,FORCAST_TJ ,\
                      FORCAST_LIST,INSERT_TIME ,FINISH_TIME,DEAL_STATUS,LTF_ID \
                      from user.shkj_check where user_code=? and deal_status = ?");
        vars<<userCode<<dealstatus;
    }
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("uqrycode",query.value(i++).toString());
        values.insert("detail",query.value(i++).toString());
        values.insert("hmzs",query.value(i++).toString());
        values.insert("ltf_tj",query.value(i++).toString());
        values.insert("ltf_list",query.value(i++).toString());
        values.insert("forecast_tj",query.value(i++).toString());
        values.insert("forecast_list",query.value(i++).toString());
        values.insert("inserttime",query.value(i++).toString());
        values.insert("finishtime",query.value(i++).toString());
        values.insert("dealstatus",query.value(i++).toString());
        values.insert("ltfid",query.value(i++).toString());
    }
    return values;
}

int SNCDataBase::getShkjToDown(QString userCode)
{
    QSqlQuery query;
    query.prepare("select count(*) from user.shkj_check where user_code=? and deal_status='2'");
    QVariantList vars;
    vars<<userCode;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    int value = 0;
    if(query.next())
    {
        value = query.value(0).toInt();
    }
    return value;
}

int SNCDataBase::updateShkjCachePath(QString srvID,QString cachePath)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("update user.shkj_check set CACHE_LOCAL_PATH = ?,deal_status='3'  where LTF_ID=? ");
    QVariantList vars;
    vars<<cachePath<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();

    return 0;
}

QString SNCDataBase::getShkjCachePath(QString srvID)
{
    QSqlQuery query;
    query.prepare("select CACHE_LOCAL_PATH from user.shkj_check where LTF_ID=? ");
    QVariantList vars;
    vars<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QString value = "";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

int SNCDataBase::delShkjByID(QString srvID)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("delete from user.shkj_check  where LTF_ID=? ");
    QVariantList vars;
    vars<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();

    return 0;
}

QMultiMap<QString, QString> SNCDataBase::getShkjZxshTasks(QString userCode, QString dealstatus)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;

    QVariantList vars;
    if(dealstatus.isEmpty())
    {
        query.prepare("select TASK_ID,SSID,PROVINCE,CITY,INSERTTIME,UPDATEDAYS,ISP ,\
                      SEG,NEED_NUM ,USER_CODE,DEAL_STATUS,ISDEL \
                      from user.SHKJ_ZXSH_TASK where user_code=?");
        vars<<userCode;
    }
    else
    {
        query.prepare("select TASK_ID,SSID,PROVINCE,CITY,INSERTTIME,UPDATEDAYS,ISP ,\
                      SEG,NEED_NUM ,USER_CODE,DEAL_STATUS,ISDEL \
                      from user.SHKJ_ZXSH_TASK where user_code=? and deal_status = ?");
        vars<<userCode<<dealstatus;
    }
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    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("updatedays",query.value(i++).toString());
        values.insert("isp",query.value(i++).toString());
        values.insert("seg",query.value(i++).toString());
        values.insert("neednum",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;
}

QMultiMap<QString, QString> SNCDataBase::getShkjZxshTasksByUser(QString userCode)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;

    QVariantList vars;
    query.prepare("select SSID,PROVINCE,CITY,INSERTTIME,FINISHTEIME,UPDATEDAYS,ISP ,\
                  SEG,NEED_NUM ,REAL_NUM,USER_CODE,DEAL_STATUS \
                  from user.SHKJ_ZXSH_TASK_SERVER where user_code=?");
    vars<<userCode;
    query.exec();
    while(query.next())
    {
        int i=0;
        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("seg",query.value(i++).toString());
        values.insert("neednum",query.value(i++).toString());
        values.insert("realnum",query.value(i++).toString());
        values.insert("usercode",query.value(i++).toString());
        values.insert("dealstatus",query.value(i++).toString());
    }
    return values;
}

int SNCDataBase::addShkjZxshTask(QString ssid, QString userCode, QString province,QString city, \
                                  QString inserttime,QString updatedays, QString isp, QString seg, int neednum)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare("insert into user.SHKJ_ZXSH_TASK(ssid,USER_CODE,province,\
                  city,inserttime,updatedays,isp,seg,need_num)\
            values(?,?,?,?,?,?,?,?,?)");
    QVariantList vars;
    vars<<ssid<<userCode<<province<<city<<inserttime<<updatedays<<isp<<seg<<neednum;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}

int SNCDataBase::updateLoginInfo(QString userCode, QString password, QString isAuto, QString isRemember, QString lastLoginTime)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();

    query.clear();
    query.prepare("delete  from  user.USER_LOGON where USER_CODE = ?");
    query.addBindValue(userCode);
    query.exec();

    query.clear();
    query.prepare("insert into user.USER_LOGON( \
                  USER_CODE,PASSWORD,ISAUTO,ISREMEMBER,LAST_LOGIN_TIME) \
                  VALUES(?,?,?,?,?)");
    QVariantList vars;
    vars<<userCode<<password<<isAuto<<isRemember<<lastLoginTime;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();


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

QMultiMap<QString, QString> SNCDataBase::getDefaultLoginInfo()
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.exec("select USER_CODE,PASSWORD,ISAUTO,ISREMEMBER,LAST_LOGIN_TIME \
                  from user.USER_LOGON ORDER BY ID asc");
    while(query.next())
    {
        int i=0;
        values.insert("usercode",query.value(i++).toString());
        values.insert("password",query.value(i++).toString());
        values.insert("isauto",query.value(i++).toString());
        values.insert("isremember",query.value(i++).toString());
        values.insert("lastlogintime",query.value(i++).toString());
    }
return values;
}

int SNCDataBase::updateBzjcCheckDealInfos(QMultiMap<QString, QString> maps, QString userCode, QString clientID)
{
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    int cols = maps.uniqueKeys().size();
    int rows = 0;
    if(cols!=0)
    {
        rows = maps.size()/cols;
    }
    query.clear();
    query.exec("delete  from  user.bzjc_check_dealing where client_id = '" + clientID+ "'");
    for(int i=0;i<rows;i++)
    {
        QString batchid = maps.values("batchid").at(i);
        QString batchtype = maps.values("batchtype").at(i);
        QString detail = maps.values("detail").at(i);
        QString resulttj = maps.values("resulttj").at(i);
        QString submittime = maps.values("submittime").at(i);
        QString tftime = maps.values("tftime").at(i);
        QString vftime = maps.values("vftime").at(i);
        QString isverify = maps.values("isverify").at(i);
        QString settlefee = maps.values("settlefee").at(i);
        QString giftfee = maps.values("giftfee").at(i);
        int numcount = maps.values("numcount").at(i).toInt();
        int dlcount = maps.values("dlcount").at(i).toInt();
        int finishcount = maps.values("finishcount").at(i).toInt();
        QString optstatus = maps.values("optstatus").at(i);

        query.clear();
        query.prepare("delete  from  user.file_upload where server_fid = ?");
        query.addBindValue(batchid);
        query.exec();

        double percent = 0;
        if(numcount!=0)
            percent = finishcount * 100.0 / numcount;
        query.clear();
        query.prepare("insert into user.bzjc_check_dealing( \
                      BATCH_ID ,BATCH_TYPE ,CLIENT_ID,USER_CODE ,\
                      DETAIL ,RESULT_TJ ,SUBMIT_TIME,TF_TIME,\
                      VF_TIME ,ISVERIFY,SETTLE_FEE,GIFT_FEE ,\
                      NUM_COUNT ,DOWNLOAD_COUNT ,FINISH_COUNT , OPT_STATUS) \
                      VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        QVariantList vars;
        vars<<batchid<<batchtype<<clientID<<userCode<<detail<<resulttj<<submittime<<tftime<<vftime<<isverify;
        vars<<settlefee<<giftfee<<numcount<<dlcount<<finishcount<<optstatus;
        for(int i=0;i<vars.size();i++)
        {
            query.addBindValue(vars.at(i));
        }
        query.exec();
    }


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

int SNCDataBase::updateBzjcCheckFinishInfos(QMultiMap<QString, QString> maps, QString userCode, QString clientID)
{
    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 batchid = maps.values("batchid").at(i);
        QString batchtype = maps.values("batchtype").at(i);
        QString detail = maps.values("detail").at(i);
        QString resulttj = maps.values("resulttj").at(i);
        QString submittime = maps.values("submittime").at(i);
        QString finishtime = maps.values("finishtime").at(i);
        QString settlefee = maps.values("settlefee").at(i);
        QString giftfee = maps.values("giftfee").at(i);
        QString numcount = maps.values("numcount").at(i);
        QString dlcount = maps.values("dlcount").at(i);
        QString finishcount = maps.values("finishcount").at(i);
        QString optstatus = maps.values("optstatus").at(i);
        QString ispass = maps.values("ispass").at(i);
        QString isdeliver = maps.values("isdeliver").at(i);
        QString delivertime = maps.values("delivertime").at(i);

//        query.clear();
//        query.prepare("delete  from  user.file_upload where server_fid = ?");
//        query.addBindValue(batchid);
//        query.exec();

        query.clear();
        query.prepare("select CACHE_LOCAL_PATH  from  user.bzjc_check_finished where BATCH_ID = ?");
        query.addBindValue(batchid);
        query.exec();

        if(query.next())
        {
            if(!query.value(0).toString().isEmpty())
                continue;
        }
        query.clear();
        query.prepare("delete  from  user.bzjc_check_finished where BATCH_ID = ?");
        query.addBindValue(batchid);
        query.exec();
        query.clear();
        query.prepare("insert into user.bzjc_check_finished( \
                      BATCH_ID ,BATCH_TYPE ,CLIENT_ID,USER_CODE ,\
                      DETAIL ,RESULT_TJ ,SUBMIT_TIME,FINISH_TIME,\
                      SETTLE_FEE,GIFT_FEE ,\
                      NUM_COUNT ,DOWNLOAD_COUNT ,FINISH_COUNT , OPT_STATUS,\
                      ISPASS,ISDELIVER,DELIVER_TIME) \
                      VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        QVariantList vars;
        vars<<batchid<<batchtype<<clientID<<userCode<<detail<<resulttj<<submittime<<finishtime;
        vars<<settlefee<<giftfee<<numcount<<dlcount<<finishcount<<optstatus<<ispass<<isdeliver<<delivertime;
        for(int i=0;i<vars.size();i++)
        {
            query.addBindValue(vars.at(i));
        }
        query.exec();
    }
    QSqlDatabase::database().commit();
    return 0;
}

QMultiMap<QString, QString> SNCDataBase::getBzjcCheckFinishInfos(QString userCode, QString clientID, QString isdeliver)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    QVariantList vars;
    if(isdeliver.isEmpty())
    {
        query.prepare("select BATCH_ID ,BATCH_TYPE ,\
                      DETAIL ,RESULT_TJ ,SUBMIT_TIME,FINISH_TIME,\
                      SETTLE_FEE,GIFT_FEE ,\
                      NUM_COUNT ,DOWNLOAD_COUNT ,FINISH_COUNT , OPT_STATUS,\
                      ISPASS,ISDELIVER,DELIVER_TIME \
                      from user.bzjc_check_finished where user_code=? and client_id = ? ");
        vars<<userCode<<clientID;
    }
    else
    {
        query.prepare("select BATCH_ID ,BATCH_TYPE ,\
                      DETAIL ,RESULT_TJ ,SUBMIT_TIME,FINISH_TIME,\
                      SETTLE_FEE,GIFT_FEE ,\
                      NUM_COUNT ,DOWNLOAD_COUNT ,FINISH_COUNT , OPT_STATUS,\
                      ISPASS,ISDELIVER,DELIVER_TIME \
                      from user.bzjc_check_finished where user_code=? and client_id = ?  and isdeliver = ?");
        vars<<userCode<<clientID<<isdeliver;
    }
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("batchid",query.value(i++).toString());
        values.insert("batchtype",query.value(i++).toString());
        values.insert("detail",query.value(i++).toString());
        values.insert("resulttj",query.value(i++).toString());
        values.insert("submittime",query.value(i++).toString());
        values.insert("finishtime",query.value(i++).toString());
        values.insert("settlefee",query.value(i++).toString());
        values.insert("giftfee",query.value(i++).toString());
        values.insert("numcount",query.value(i++).toString());
        values.insert("dlcount",query.value(i++).toString());
        values.insert("finishcount",query.value(i++).toString());
        values.insert("optstatus",query.value(i++).toString());
        values.insert("ispass",query.value(i++).toString());
        values.insert("isdeliver",query.value(i++).toString());
        values.insert("delivertime",query.value(i++).toString());
    }
    return values;
}

QMultiMap<QString, QString> SNCDataBase::getBzjcCheckDealingInfos(QString userCode, QString clientID)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    QVariantList vars;
    query.prepare("select BATCH_ID ,BATCH_TYPE ,\
                  DETAIL ,RESULT_TJ ,SUBMIT_TIME,\
                  SETTLE_FEE,GIFT_FEE ,\
                  NUM_COUNT ,DOWNLOAD_COUNT ,FINISH_COUNT , OPT_STATUS,isverify,tf_time \
                  from user.BZJC_CHECK_DEALING where user_code=? and client_id = ? ");
    vars<<userCode<<clientID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("batchid",query.value(i++).toString());
        values.insert("batchtype",query.value(i++).toString());
        values.insert("detail",query.value(i++).toString());
        values.insert("resulttj",query.value(i++).toString());
        values.insert("submittime",query.value(i++).toString());
        values.insert("settlefee",query.value(i++).toString());
        values.insert("giftfee",query.value(i++).toString());
        values.insert("numcount",query.value(i++).toString());
        values.insert("dlcount",query.value(i++).toString());
        values.insert("finishcount",query.value(i++).toString());
        values.insert("optstatus",query.value(i++).toString());
        values.insert("isverify",query.value(i++).toString());
        values.insert("tftime",query.value(i++).toString());
    }
    return values;
}

int SNCDataBase::getBzjcToDown(QString userCode, QString clientID)
{
    QSqlQuery query;
    query.prepare("select count(*) from user.bzjc_check_finished where user_code=?  and client_id = ? and isdeliver='0'");
    QVariantList vars;
    vars<<userCode<<clientID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    int value = 0;
    if(query.next())
    {
        value = query.value(0).toInt();
    }
    return value;
}

int SNCDataBase::delBzjcByID(QString srvID)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("delete from user.BZJC_CHECK_FINISHED  where batch_ID=? ");
    QVariantList vars;
    vars<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();

    return 0;
}

int SNCDataBase::updateBzjcCachePath(QString srvID, QString cachePath)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("update user.BZJC_CHECK_FINISHED set CACHE_LOCAL_PATH = ?,ISDELIVER='1'  where batch_ID=? ");
    QVariantList vars;
    vars<<cachePath<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QSqlDatabase::database().commit();

    return 0;
}

QString SNCDataBase::getBzjcCachePath(QString srvID)
{
    QSqlQuery query;
    query.prepare("select CACHE_LOCAL_PATH from user.BZJC_CHECK_FINISHED where batch_ID=? ");
    QVariantList vars;
    vars<<srvID;
    for(int i=0;i<vars.size();i++)
    {
        query.addBindValue(vars.at(i));
    }
    query.exec();
    QString value = "";
    if(query.next())
    {
        value = query.value(0).toString();
    }
    return value;
}

int SNCDataBase::deleteBzjcFinishExceptSrvIDs(QString clientID, QString userCode, QStringList ids)
{
    QString where;
    for(int i=0;i<ids.size();i++)
    {
        if(i==ids.size()-1)
            where += QString("'%1'").arg(ids.at(i));
        else
            where += QString("'%1',").arg(ids.at(i));
    }
    if(where.isEmpty())
    {
        where = " where client_id = ? and user_code = ?  ";
    }
    else
    {
        where = " where client_id = ? and user_code = ?  and \
                BATCH_ID not in (" + where + ")";
    }

    QString sql = " delete from BZJC_CHECK_FINISHED " + where;
    QSqlQuery query;
    QSqlDatabase::database().transaction();
    query.prepare(sql);
    query.addBindValue(clientID);
    query.addBindValue(userCode);
    query.exec();
    QSqlDatabase::database().commit();
    return 0;
}
QMultiMap<QString, QString> SNCDataBase::getSegRecInfos()
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.exec("select seg,province,city,zipcode,s1,s2,s3,s4,sample_index,rec_index,check_day from SEG_rec order by random() limit 10");
    while(query.next())
    {
        int i=0;
        values.insert("seg",query.value(i++).toString());
        values.insert("province",query.value(i++).toString());
        values.insert("city",query.value(i++).toString());
        values.insert("zipcode",query.value(i++).toString());
        values.insert("shi",query.value(i++).toString());
        values.insert("kong",query.value(i++).toString());
        values.insert("ting",query.value(i++).toString());
        values.insert("guan",query.value(i++).toString());
        values.insert("sample_index",query.value(i++).toString());
        values.insert("rec_index",query.value(i++).toString());
        values.insert("check_day",query.value(i++).toString());
    }

    return values;
}
QMultiMap<QString, QString> SNCDataBase::getSegRecInfosByCity(QString city)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.prepare("select seg,province,city,zipcode,s1,s2,s3,s4,sample_index,rec_index,check_day from SEG_rec where city =?  order by random() limit 10");
    QVariant v;
    v = city;
    query.addBindValue(v);
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("seg",query.value(i++).toString());
        values.insert("province",query.value(i++).toString());
        values.insert("city",query.value(i++).toString());
        values.insert("zipcode",query.value(i++).toString());
        values.insert("shi",query.value(i++).toString());
        values.insert("kong",query.value(i++).toString());
        values.insert("ting",query.value(i++).toString());
        values.insert("guan",query.value(i++).toString());
        values.insert("sample_index",query.value(i++).toString());
        values.insert("rec_index",query.value(i++).toString());
        values.insert("check_day",query.value(i++).toString());
    }

    return values;
}
QStringList SNCDataBase::getSegAreaCodes()
{
    QStringList values;
    QSqlQuery query;
    QVariantList vars;
    query.exec("select distinct areacode from num.city");
    while(query.next())
    {
        int i=0;
        values.append(query.value(i++).toString());
    }
    return values;
}

QStringList SNCDataBase::getRecSegs()
{
    QStringList values;
    QSqlQuery query;
    QVariantList vars;
    query.exec("select seg from seg_rec");
    while(query.next())
    {
        int i=0;
        values.append(query.value(i++).toString());
    }
    return values;
}


QMultiMap<QString, QString> SNCDataBase::getSegRecInfosByProvince(QString province)
{
    QMultiMap<QString ,QString> values;
    QSqlQuery query;
    query.prepare("select seg,province,city,zipcode,s1,s2,s3,s4,sample_index,rec_index,check_day from SEG_rec where province =?  order by random() limit 10");
    QVariant v;
    v = province;
    query.addBindValue(v);
    query.exec();
    while(query.next())
    {
        int i=0;
        values.insert("seg",query.value(i++).toString());
        values.insert("province",query.value(i++).toString());
        values.insert("city",query.value(i++).toString());
        values.insert("zipcode",query.value(i++).toString());
        values.insert("shi",query.value(i++).toString());
        values.insert("kong",query.value(i++).toString());
        values.insert("ting",query.value(i++).toString());
        values.insert("guan",query.value(i++).toString());
        values.insert("sample_index",query.value(i++).toString());
        values.insert("rec_index",query.value(i++).toString());
        values.insert("check_day",query.value(i++).toString());
    }

    return values;
}