SpeedNumCube/sncprojects/app/snc_db_tool/mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QtSql>
MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

}

MainWindow::~MainWindow()
{
    delete ui;
}
void MainWindow::on_createSegPB_clicked()
{

    /*QFileDialog::Options options;
    //options |= QFileDialog::DontUseNativeDialog;
    QString selectedFilter;
    QString fileName = QFileDialog::getOpenFileName(this,
                                tr("Select"),"",
                                tr("CSV Files (*.csv)"),
                                &selectedFilter,
                                options);
    if (fileName.isEmpty())
        return ;
    QFile file(fileName);
    file.open(QIODevice::ReadOnly);
    QTextStream in(&file);
        QVariantList seg,province,city,corp,areacode;
    while(!in.atEnd())
    {
        QString line=in.readLine();
        QStringList fields = line.split(",");
        seg << fields.at(0);
        province << fields.at(1);
        city << fields.at(2);
        //corp << fields.at(3);
        //areacode << fields.at(4);
    }*/
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setPassword("123*asdf");
    QFile::remove("mseg.db");
    db.setDatabaseName("mseg.db");
    if (!db.open()) {
        return ;
    }
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    //query.exec("CREATE TABLE MSEG (seg char(7) primary key,province varchar(20),city varchar(50),corp varchar(4),areacode varchar(4))");
    //query.prepare("INSERT INTO MSEG values(?,?,?,?,?)");
     query.exec("create table CONFIG (NAME varchar(100),VALUE varchar(1000))");
     query.exec("insert into  CONFIG values ('seg_version','201201')");
    query.exec("CREATE TABLE MSEG (seg char(7)  primary key,province varchar(20),city varchar(20),areacode varchar(4))");
    //query.prepare("INSERT INTO MSEG values(?,?,?)");
    //query.addBindValue(seg);
    //query.addBindValue(province);
    //query.addBindValue(city);
    //query.addBindValue(corp);
    //query.addBindValue(areacode);
    //query.execBatch();
    query.exec("CREATE TABLE CITY  as  select province,city,areacode,count(*) as total from mseg group by province,city,areacode");
    //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)");
    //query.exec("insert into SEG_DYNAMIC VALUES('1300281',100,10,20,30,40,50,60,70,80)");
    //query.exec("insert into SEG_DYNAMIC VALUES('1303770',200,20,20,60,40,50,60,70,80)");
    QSqlDatabase::database().commit();
    db.close();
    QMessageBox::information(this,"","finished",QMessageBox::Ok);
}

void MainWindow::on_createSysPB_clicked()
{
    QFile::remove("sys.db");
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setPassword("123*asdf");
    db.setDatabaseName("sys.db");
    if (!db.open()) {
        return ;
    }
    QSqlDatabase::database().transaction();
    QSqlQuery query;

    query.exec("create table srv_param(srv_id varchar(100),srv_param_name varchar(100),srv_param_value varchar(100)) ");


    query.exec("create table AD(ADURL varchar(100),ADTEXT varchar(100)) ");
    query.exec("insert into  AD values ('www.kh8.cc','瞬时二代空号检测，八种状态智能检测')");
    query.exec("insert into  AD values ('www.smsjc.com','名远智能空号检测，实时检测八种状态')");
    query.exec("create table page_ad(AD_ID VARCHAR(100),AD_URL varchar(1000)) ");
    query.exec("insert into  page_ad values ('ad_app2_index_up','http://www.iyoogo.com:86/app2/adup/index.htm')");
    query.exec("insert into  page_ad values ('ad_app2_index_bottom','http://www.iyoogo.com:86/app2/adbottom/index.htm')");
    query.exec("insert into  page_ad values ('ad_app2_help','http://www.iyoogo.com:86/app2/bang/index.htm')");
    query.exec("insert into  page_ad values ('ad_app2_riddle','http://www.iyoogo.com:86/app2/unlog_shkj/index.htm')");
    query.exec("create table CONFIG (NAME varchar(100),VALUE varchar(1000))");
    query.exec("insert into  CONFIG values ('appupdate_name','sncupdate.exe')");
    query.exec("insert into  CONFIG values ('splashtext_color','#ffffff')");
    query.exec("insert into  CONFIG values ('app_name','极速号码魔方')");
    query.exec("insert into  CONFIG values ('splashimage_name',':/splash.jpg')");
    query.exec("insert into  CONFIG values ('resource_name','res/default.rcc')");
    query.exec("insert into  CONFIG values ('qss_name',':/main.qss')");
    query.exec("insert into  CONFIG values ('authcode_seg','*hope*2012!')");
    query.exec("insert into  CONFIG values ('authcode_syspara','*LUck*2013!')");
    query.exec("insert into  CONFIG values ('authcode_url','mf_*LUck%1_*2013!')");
    query.exec("insert into  CONFIG values ('authcode_encrypt','Encrypt*LUck*2013!')");
    query.exec("insert into  CONFIG values ('authcode_srvtime','*LUck*2013!')");
    //query.exec("insert into  CONFIG values ('seg_version','201201')");
    //query.exec("insert into  CONFIG values ('config_url','http://jshmmf.9966.org:9696/mofang/config.xml')");
    //query.exec("insert into  CONFIG values ('config_url','http://61.156.8.201:9696/mofang/config.xml')");
    query.exec("insert into  CONFIG values ('banner_ad','http://www.iyoogo.com:86/ad/ad.html')");
    query.exec("insert into  CONFIG values ('appconfig_url','http://app.iyoogo.com:86/product/app.xml')");
    query.exec("insert into  CONFIG values ('dataconfig_url','http://app.iyoogo.com:86/product/data.xml')");
    query.exec("insert into  CONFIG values ('helpme_url','http://www.iyoogo.com:86/app/bang/index.htm')");
    query.exec("insert into  CONFIG values ('website_title','IYOOGO官方网站')");
    query.exec("insert into  CONFIG values ('website_url','http://www.iyoogo.com')");
    query.exec("create table client_config(id varchar(100) primary key,name varchar(100),notice_url varchar(1000),host varchar(1000),port varchar(10)) ");
    query.exec("insert into  client_config values ('default','default','','ws1.iyoogo.com','86')");
    query.exec("insert into  client_config values ('sskh','瞬时空号检测','http://www.iyoogo.com:86/app/post/index.htm','ws1.iyoogo.com','86')");
    query.exec("insert into  client_config values ('mykh','名远空号检测','http://www.iyoogo.com:86/app/post2/index.htm','ws2.iyoogo.com','86')");

    QVariant cmcc = "'134','135','136','137','138','139','147','150','151','152','154','157','158','159','182','183','187','188'";
    QVariant cutc = "'130','131','132','145','155','156','185','186'";
    QVariant ctcc = "'133','153','180','189'";
    QVariantList corps,segs;
    corps<<"CMCC"<<"CUTC"<<"CTCC";
    segs<<cmcc<<cutc<<ctcc;
    query.exec("create table corp_prefix(corp varchar(100),prefix varchar(200)) ");
    query.prepare("insert into  corp_prefix values (?,?)");
    query.addBindValue(corps);
    query.addBindValue(segs);
    query.execBatch();
    query.finish();

    query.exec("CREATE TABLE NUMBER_RULES(RULE_ID INTEGER PRIMARY KEY ,NAME VARCHAR(100),REGEX VARCHAR(500))");
    query.exec("CREATE TABLE NUMBER_RULES_SYSTEM(SLN_ID INTEGER PRIMARY KEY,NAME VARCHAR(100))");
    query.exec("CREATE TABLE NUMBER_RULES_SYSTEM_MAP(SLN_ID INTEGER,RULE_ID INTEGER)");
    query.exec("insert into NUMBER_RULES_SYSTEM(name) values('系统内置号码方案1')");
    query.exec("insert into NUMBER_RULES_SYSTEM(name) values('系统内置号码方案2')");
    query.exec("insert into NUMBER_RULES_SYSTEM(name) values('系统内置号码方案3')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位AAAA    ','(\\d)\\1\\1\\1')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位AABB    ','(\\d)\\1(\\d)\\2')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位ABAB    ','(\\d\\d)\\1')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位ABCD    ','([0][1][2][3]|[1][2][3][4]|[3][4][5]6|[4][5][6][7]|[5][6][7][8]|[6][7][8][9])')");
    //query.exec("insert into NUMBER_RULES(name,REGEX) values('号码不含4  ','1[3|5|8][0-35-9]{9}')");
    //query.exec("insert into NUMBER_RULES(name,REGEX) values('号码包含4  ','1[3|4|5|8][0-9]*[4]+[0-9]*')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位不含4    ','[0-35-9]{4}')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位包含4    ','[0-9]*[4]+[0-9]*')");
    //query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位不包含6、7、8、9、0','1[3|4|5|8][0-9]{5}[1-5]{4}')");
    //query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位包含6、7、8、9、0','1[3|4|5|8][0-9]{5}[0-9]*[06-9]+[0-9]*')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位2连号  ','[0-9]*([0-9])\\1[0-9]*')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位3连号  ','[0-9]*([0-9])\\1\\1[0-9]*')");
    query.exec("insert into NUMBER_RULES(name,REGEX) values('末尾四位包含7  ','[0-9]*[7]+[0-9]*')");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(1,1)");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(1,2)");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(2,2)");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(2,3)");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(3,3)");
    query.exec("insert into NUMBER_RULES_SYSTEM_MAP(SLN_ID,RULE_ID) values(3,4)");
    QSqlDatabase::database().commit();
    db.close();
    QMessageBox::information(this,"","finished",QMessageBox::Ok);
}

void MainWindow::on_pushButton_clicked()
{
    QFile::remove("../data/user.db");
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setPassword("123*asdf");
    db.setDatabaseName("../data/user.db");
    if (!db.open()) {
        return ;
    }
    QSqlDatabase::database().transaction();
    QSqlQuery query;

    query.exec("CREATE TABLE NUMBER_RULES_USER(SLN_ID INTEGER PRIMARY KEY,NAME VARCHAR(100))");
    query.exec("CREATE TABLE NUMBER_RULES_USER_MAP(SLN_ID INTEGER,RULE_ID INTEGER)");
    query.exec("CREATE TABLE NUMBER_BUILD_HISTORY(BUILD_ID INTEGER PRIMARY KEY,BUILD_DATE TEXT,REGION VARCHAR(100),TOTAL NUMERIC,SEGMENTS TEXT,RULE_IDS TEXT)");
    query.exec("CREATE TABLE 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("CREATE TABLE 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("CREATE TABLE 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("CREATE TABLE 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("CREATE TABLE 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)\
               )");

    /*query.exec("insert into NUMBER_RULES_USER(name) values('用户号码方案1')");
    query.exec("insert into NUMBER_RULES_USER(name) values('用户内置号码方案2')");
    query.exec("insert into NUMBER_RULES_USER(name) values('用户内置号码方案3')");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(1,1)");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(1,2)");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(2,2)");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(2,3)");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(3,3)");
    query.exec("insert into NUMBER_RULES_USER_MAP(SLN_ID,RULE_ID) values(3,4)");

    query.exec("INSERT INTO NUMBER_BUILD_HISTORY(BUILD_DATE,REGION,TOTAL,SEGMENTS,RULE_IDS) VALUES('2012-3-7','济南等',100000,'1301234,1311334,1334567','1,2')");
    query.exec("INSERT INTO NUMBER_BUILD_HISTORY(BUILD_DATE,REGION,TOTAL,SEGMENTS,RULE_IDS) VALUES('2012-3-8','青岛等',200000,'1341234,1351334,1364567','2,3')");
    query.exec("INSERT INTO NUMBER_BUILD_HISTORY(BUILD_DATE,REGION,TOTAL,SEGMENTS,RULE_IDS) VALUES('2012-3-8','上海等',300000,'1371234,1381334,1394567','3,4')");*/
    QSqlDatabase::database().commit();
    db.close();
    QMessageBox::information(this,"","finished",QMessageBox::Ok);
}
void MainWindow::loadDict()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(":memory:");
    db.setPassword("123*asdf");
    if (!db.open()) {
        return ;
    }
    QSqlQuery query;
    query.exec("ATTACH DATABASE 'mseg.db' AS mseg");
    query.exec("ATTACH DATABASE 'sys.db' AS sys");
    query.exec("ATTACH DATABASE '../data/user.db' AS user");
    QStandardItemModel *model = new QStandardItemModel() ;
    QStandardItem *sys = new QStandardItem("sys");
    query.exec("select name from sys.sqlite_master where type='table'");

    while(query.next())
    {
        QStandardItem *item = new QStandardItem(query.value(0).toString());
        sys->appendRow(item);
    }
    model->appendRow(sys);
    QStandardItem *mseg = new QStandardItem("mseg");
    query.exec("select name from mseg.sqlite_master where type='table'");
    while(query.next())
    {
        QStandardItem *item = new QStandardItem(query.value(0).toString());
        mseg->appendRow(item);
    }
    model->appendRow(mseg);
    QStandardItem *user = new QStandardItem("user");
    query.exec("select name from user.sqlite_master where type='table'");
    while(query.next())
    {
        QStandardItem *item = new QStandardItem(query.value(0).toString());
        user->appendRow(item);
    }
    model->appendRow(user);
    this->ui->treeView->setModel(model);

}

void MainWindow::on_queryPB_clicked()
{
    loadDict();
}

void MainWindow::on_treeView_clicked(const QModelIndex &index)
{
    if(index.parent().isValid())
    {
        QString db = index.parent().data().toString();

        QString table = index.data().toString();
        QSqlTableModel *model = new QSqlTableModel();
        model->setTable(db+"."+table);
        model->setEditStrategy(QSqlTableModel::OnManualSubmit);
        model->setSort(0,Qt::AscendingOrder);
        model->select(); //选取整个表的所有行
        // model->removeColumn(1); //不显示name属性列,如果这时添加记录，则该属性的值添加不上
        ui->tableView->setModel(model);
        QSqlQueryModel *model2 = new QSqlQueryModel();
        model2->setQuery("select count(*) as total from "+table);
        this->ui->tableView_2->setModel(model2);
    }
}

void MainWindow::on_pushButtonBuildNumber_clicked()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setPassword("123*asdf");
    db.setDatabaseName("mseg.db");
    if (!db.open()) {
        return ;
    }
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.exec("select seg from mseg");
    QFile file("data.txt");
    int count=0;
    if(file.open(QIODevice::WriteOnly))
    {
        QTextStream out(&file);
        while(query.next())
        {
            QString seg = query.value(0).toString();
            QString num;
            for(int i=0;i<40;i++){
                num += seg+"0000\r\n";
                count++;
            }
            if(count>1000*10000)
                break;
            out<<num;

        }
        file.close();
    }
}