SpeedNumCube/sncprojects/lib/SNCDataBase/shkjdb.cpp

#include "shkjdb.h"
#include <QtSql>
ShkjDB::ShkjDB()
{
}
int ShkjDB::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.shxz_shkj  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);
        QString memo = maps.values("memo").at(i);

        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.shxz_shkj where LTF_ID = ?");
        query.addBindValue(ltf_id);
        query.exec();

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


    QSqlDatabase::database().commit();
    return 0;
}
QMultiMap<QString, QString> ShkjDB::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,MEMO \
                  from user.SHXZ_SHKJ where user_code=? and (isdel='N' or isdel is null or isdel='')");
    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());
        values.insert("memo",query.value(i++).toString());
    }
    return values;
}

QMultiMap<QString, QString> ShkjDB::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.shxz_shkj 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,memo \
                      from user.SHXZ_SHKJ where user_code=? and (isdel='N' or isdel is null or isdel='')");
        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,memo \
                      from user.SHXZ_SHKJ where user_code=? and deal_status = ? and (isdel='N' or isdel is null or isdel='')");
        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());
        values.insert("memo",query.value(i++).toString());
    }
    return values;
}
int ShkjDB::delShkjByID(QString srvID)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("update user.SHXZ_SHKJ set isdel='Y' 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;
}
int ShkjDB::updateShkjCachePath(QString srvID,QString cachePath)
{
    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("update user.SHXZ_SHKJ 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 ShkjDB::getShkjCachePath(QString srvID)
{
    QSqlQuery query;
    query.prepare("select CACHE_LOCAL_PATH from user.SHXZ_SHKJ 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;
}