#include "TaskDao.h"

TaskDao::TaskDao()
{

}

QList<TaskDTO> TaskDao::getTaskListPage(TaskRequest request, Page &page)
{
    QList<TaskDTO> resultList;

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);

    // 查询总数并赋值
    quint32 totalCount = getTaskTotalCount(request);
    page.totalCount = totalCount;

    // 构造查询语句
    QString sql = "SELECT blei.id AS id, blei.sample_id, eei.equipment_name, eei.model, "
                  "eei.manufacture_no, eei.manufacturer, bo.id as orderId, bo.order_no, bo.undertake_time, "
                  "bo.customer_name, sd.SIMPLE_NAME, bo.require_over_time, bo.is_urgent, blei.measure_status "
                  "FROM biz_business_lab_executive_info blei "
                  "JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id "
                  "LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID "
                  "JOIN biz_business_order_info bo ON bo.id = blei.order_id";

    sql = QString("%1 %2").arg(sql).arg(generateTaskListPageWhereClause(request));

    // 设置分页数据
    sql = QString("%1 ORDER BY bo.undertake_time DESC").arg(sql);
    sql = QString("%1 %2").arg(sql).arg("LIMIT :offset, :limit;");

    query.prepare(sql);
    bindValueTaskListPage(query, request);

    query.bindValue(":offset", page.currentPage * page.pageSize);
    query.bindValue(":limit", page.pageSize);

//    qDebug() << sql << request.measureStatusList.join(",") << request.deptId << page.currentPage << page.pageSize << totalCount;

    // 分页查询
    bool isSuccess = query.exec();
    if (isSuccess) {
        while (query.next()) {
            TaskDTO task;
            task.id = query.value("id").toString();
            task.sampleId = query.value("sample_id").toString();
            task.sampleName = query.value("equipment_name").toString();
            task.sampleModel = query.value("model").toString();
            task.manufactureNo = query.value("manufacture_no").toString();
            task.manufacturer = query.value("manufacturer").toString();
            task.customerName = query.value("customer_name").toString();
            task.measureDeptName = query.value("SIMPLE_NAME").toString();
            task.requireOverTime = query.value("require_over_time").toDate().toString("yyyy-MM-dd");
            task.isUrgent = query.value("is_urgent").toString();
            task.measureStatus = query.value("measure_status").toString();
            task.orderId = query.value("orderId").toString();
            task.orderNo = query.value("order_no").toString();

            resultList.append(task);
        }
    } else {
        LogUtil::PrintLog("ERROR", QString("查询任务单失败[%1]").arg(query.lastError().text()));
    }

    // 返回结果
    return resultList;
}

quint32 TaskDao::getTaskTotalCount(TaskRequest request)
{
    quint32 totalCount = 0;

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);

    QString sql = "SELECT COUNT( blei.id ) AS recCount "
                  "FROM biz_business_lab_executive_info blei "
                  "JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id "
                  "LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID "
                  "JOIN biz_business_order_info bo ON bo.id = blei.order_id";

    sql = QString("%1 %2").arg(sql).arg(generateTaskListPageWhereClause(request));

    query.prepare(sql);
    bindValueTaskListPage(query, request);

//    qDebug() << sql << request.measureStatusList.join(',');

    if (query.exec() && query.next()) {
        totalCount = query.value("recCount").toInt();
    } else {
        LogUtil::PrintLog("ERROR", QString("查询任务单数量失败[%1]").arg(query.lastError().text()));
    }

    return totalCount;
}
/*
quint32 TaskDao::getMyTaskCount(QString userId, QString devName, QString devModel, QString devSerial, QString customer)
{
    quint32 count = 0;

    QString sql = "SELECT COUNT(blei.id) AS RECCT FROM biz_business_lab_executive_info blei "
                  "JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id "
                  "JOIN biz_business_order_info bo ON bo.id = blei.order_id "
                  "LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID "
                  "WHERE blei.measure_person_id = "
                  "(SELECT brsi.id from biz_resource_staff_info brsi JOIN sys_user ON sys_user.ACCOUNT = brsi.account WHERE sys_user.id = :userId) "
                  "AND blei.measure_status = 3";

    if (devName.isEmpty() == false && devName != "%%") {
        sql += " AND eei.equipment_name like :name";
    }
    if (devModel.isEmpty() == false && devModel != "%%") {
        sql += " AND eei.model like :model";
    }
    if (devSerial.isEmpty() == false && devSerial != "%%") {
        sql += " AND eei.manufacture_no like :serial";
    }
    if (customer.isEmpty() == false) {
        sql += " AND bo.customer_id = :customer";
    }

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);
    query.prepare(sql);

    query.bindValue(":userId", userId);
    if (devName.isEmpty() == false && devName != "%%") {
        query.bindValue(":name", devName);
    }
    if (devModel.isEmpty() == false && devModel != "%%") {
        query.bindValue(":model", devModel);
    }
    if (devSerial.isEmpty() == false && devSerial != "%%") {
        query.bindValue(":serial", devSerial);
    }
    if (customer.isEmpty() == false) {
        query.bindValue(":customer", customer);
    }

    bool isSuccess = query.exec();
    if (isSuccess && query.next()) {
        count = query.value("RECCT").toInt();
    } else {
        qDebug() << "Query execution failed: " << query.lastError().text();
    }

    return count;
}
*/
/*
QList<MyTaskDto> TaskDao::getMyTaskListPage(QString userId, QString devName, QString devModel, QString devSerial, QString customer, qint8 limit, qint16 offset)
{
    QList<MyTaskDto> result;

    QString sql = "SELECT blei.id AS id, blei.measure_person_id, blei.require_certifications, blei.measure_status, "
                  "eei.id AS sample_id, eei.equipment_no AS sampleNo, eei.equipment_name AS sampleName, eei.model AS sampleModel, "
                  "eei.check_cycle, eei.manufacture_no AS manufactureNo, eei.manufacturer, eei.help_instruction AS helpInstruction, "
                  "eei.certificate_valid AS certificateValid, eei.RFID AS labelBind, "
                  "bo.order_no AS orderNo, bo.id AS orderId, bo.customer_id, bo.customer_name, bo.deliverer, bo.is_urgent, bo.require_over_time, bo.customer_address, "
                  "sd.ID AS measureDeptId, sd.SIMPLE_NAME AS measureDeptName "
                  "FROM biz_business_lab_executive_info blei "
                  "JOIN eqpt_equipment_info eei ON eei.id = blei.sample_id "
                  "JOIN biz_business_order_info bo ON bo.id = blei.order_id "
                  "LEFT JOIN sys_dept sd ON blei.measure_dept_id = sd.ID "
                  "WHERE blei.measure_person_id = "
                  "(SELECT brsi.id from biz_resource_staff_info brsi JOIN sys_user ON sys_user.ACCOUNT = brsi.account WHERE sys_user.id = :userId) "
                  "AND blei.measure_status = 3";

    if (devName.isEmpty() == false && devName != "%%") {
        sql += " AND eei.equipment_name like :name";
    }
    if (devModel.isEmpty() == false && devModel != "%%") {
        sql += " AND eei.model like :model";
    }
    if (devSerial.isEmpty() == false && devSerial != "%%") {
        sql += " AND eei.manufacture_no like :serial";
    }
    if (customer.isEmpty() == false) {
        sql += " AND bo.customer_id = :customer";
    }

    sql += " limit :limit offset :offset;";

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);
    query.prepare(sql);

    query.bindValue(":userId", userId);
    if (devName.isEmpty() == false && devName != "%%") {
        query.bindValue(":name", devName);
    }
    if (devModel.isEmpty() == false && devModel != "%%") {
        query.bindValue(":model", devModel);
    }
    if (devSerial.isEmpty() == false && devSerial != "%%") {
        query.bindValue(":serial", devSerial);
    }
    if (customer.isEmpty() == false) {
        query.bindValue(":customer", customer);
    }
    query.bindValue(":limit", limit);
    query.bindValue(":offset", offset);

    if (query.exec()) {
        while (query.next()) {
            MyTaskDto task;
            task.id = query.value("id").toString();
            task.sampleId = query.value("sample_id").toString();
            task.sampleNo = query.value("sampleNo").toString();
            task.sampleName = query.value("sampleName").toString();
            task.sampleModel = query.value("sampleModel").toString();
            task.checkCycle = query.value("check_cycle").toString();
            task.manufactureNo = query.value("manufactureNo").toString();
            task.manufacturer = query.value("manufacturer").toString();
            task.helpInstruction = query.value("helpInstruction").toString();
            task.orderNo = query.value("orderNo").toString();
            task.orderId = query.value("orderId").toString();
            task.customerId = query.value("customer_id").toString();
            task.customerName = query.value("customer_name").toString();
            task.deliverer = query.value("deliverer").toString();
            task.isUrgent = query.value("is_urgent").toString();
            task.requireOverTime = query.value("require_over_time").toDateTime();
            task.requireCertifications = query.value("require_certifications").toString();
            task.measureStatus = query.value("measure_status").toString();
            task.customerAddress = query.value("customer_address").toString();
            task.measurePersonId = query.value("measure_person_id").toString();
            task.labelBind = query.value("labelBind").toString();
            task.certificateValid = query.value("certificateValid").toString();
            task.measureDeptId = query.value("measureDeptId").toString();
            task.deptFullName = query.value("measureDeptName").toString();
//            loginMeasureDeptName = query.value("measureDeptName").toString();
            result.append(task);
        }
    } else {
        qDebug() << "Query execution failed: " << query.lastError().text();
    }

    return result;
}
*/
/*
quint32 TaskDao::getMyTaskEquipmentCount(EqptEquipmentRequest request)
{
    quint32 result = 0;

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);

    // 构造查询语句
    QString sql = "SELECT COUNT( eei.id ) AS RECCT FROM eqpt_equipment_info eei "
                  "LEFT JOIN eqpt_equipment_model_info eemi ON eei.model_id = eemi.id "
                  "LEFT JOIN biz_business_device_measure_item_info bbdmii ON eei.model_id = bbdmii.device_model_id "
                  "LEFT JOIN biz_business_device_measure_item_category bbdmic ON bbdmic.id = bbdmii.item_category_id ";
    sql = QString("%1 %2;").arg(sql).arg(generateEqptEqptInfoListPageWhereClause(request));
//    qDebug() << sql;

    // 绑定查询条件
    query.prepare(sql);
    bindValueEqptEqptInfoListPage(query, request);

    if (query.exec() && query.next()) {
        result = query.value("RECCT").toInt();
    } else {
        LogUtil::PrintLog("ERROR", QString("查询待检设备数量失败[%1]").arg(query.lastError().text()));
    }
    return result;
}
*/
/*
QList<MyTaskDto> TaskDao::getMyTaskEquipmentListPage(EqptEquipmentRequest request, Page &page)
{
    QList<MyTaskDto> resultList;

    // 查询总数
    page.totalCount = getMyTaskEquipmentCount(request);

    QSqlDatabase dbLocal = CDbConnectionPool::instance().openConnection("local");
    QSqlQuery query(dbLocal);

    QString sql = "SELECT eei.id AS sample_id, eei.equipment_name AS sample_name, eei.manufacturer, eei.manufacture_no, "
                  "eei.check_date, eei.check_organization, eei.quality_condition, eemi.model, "
                  "bbdmii.id AS itemid, bbdmii.item_category_name, bbdmii.belong_standard_equipment, bbdmii.approval_status "
                  "FROM eqpt_equipment_info eei "
                  "LEFT JOIN eqpt_equipment_model_info eemi ON eei.model_id = eemi.id "
                  "LEFT JOIN biz_business_device_measure_item_info bbdmii ON eei.model_id = bbdmii.device_model_id "
                  "LEFT JOIN biz_business_device_measure_item_category bbdmic ON bbdmic.id = bbdmii.item_category_id";

    sql = QString("%1 %2").arg(sql).arg(generateEqptEqptInfoListPageWhereClause(request));

    // 添加分页查询
    sql = QString("%1 ORDER BY eei.id ASC").arg(sql);
    sql = QString("%1 %2").arg(sql).arg("LIMIT :offset, :limit;");

//    qDebug() << sql << page.currentPage * page.tableRowCount << page.tableRowCount;

    query.prepare(sql);
    bindValueEqptEqptInfoListPage(query, request);
    query.bindValue(":offset", page.currentPage * page.pageSize);
    query.bindValue(":limit", page.pageSize);

    bool isSuccess = query.exec();
    if (isSuccess) {
        while (query.next()) {
            MyTaskDto task;
            task.id = query.value("sample_id").toString();
            task.sampleId = query.value("sample_id").toString();
            task.sampleName = query.value("sample_name").toString();
            task.sampleModel = query.value("model").toString();
            task.manufactureNo = query.value("manufacture_no").toString();
            task.manufacturer = query.value("manufacturer").toString();
            task.checkDate = query.value("check_date").toString();
            task.inputCheckOrganization = query.value("check_organization").toString();
            task.inputAddress = query.value("quality_condition").toString();
            task.belongStandardEquipment = query.value("belong_standard_equipment").toString();
//            task.belongStandardEquipmentName = ConstCailDeviceGroup.value(query.value("belong_standard_equipment").toString());
            task.approvalStatus = query.value("approval_status").toString();

            resultList.append(task);
        }
    } else {
        LogUtil::PrintLog("ERROR", QString("查询标准设备失败[%1]").arg(query.lastError().text()));
    }

    return resultList;
}
*/

QString TaskDao::generateTaskListPageWhereClause(TaskRequest request)
{
    QString where = "WHERE blei.measure_status != 1"; // 1=待分配 不出现在自动检定的任务清单中
    if (!request.userId.isEmpty()) {
        where = QString("%1 AND blei.measure_person_id = :userId").arg(where);
    }
    if (!request.deptId.isEmpty()) {
        where = QString("%1 AND blei.measure_dept_id = :deptId").arg(where);
    }
    if (!request.sampleName.isEmpty()) {
        where = QString("%1 AND eei.equipment_name like :eqptName").arg(where);
    }
    if (!request.sampleModel.isEmpty()) {
        where = QString("%1 AND eei.model like :model").arg(where);
    }
    if (!request.customerName.isEmpty()) {
        where = QString("%1 AND bo.customer_name like :customer").arg(where);
    }
    if (!request.requireStart.isEmpty()) {
        where = QString("%1 AND bo.require_over_time > :start").arg(where);
    }
    if (!request.requireEnd.isEmpty()) {
        where = QString("%1 AND bo.require_over_time < :end").arg(where);
    }
    if (!request.isUrgent.isEmpty()) {
        where = QString("%1 AND bo.is_urgent = :urgent").arg(where);
    }
    if (!request.measureStatusList.isEmpty()) {
        QString tmp;
        for (int i = 0; i < request.measureStatusList.size(); i++) {
            tmp = QString("%1,:status%2").arg(tmp).arg(i);
        }
        where = QString("%1 AND blei.measure_status IN ( %2 )").arg(where).arg(tmp.mid(1));
    }

    return where;
}

void TaskDao::bindValueTaskListPage(QSqlQuery query, TaskRequest request)
{
    if (!request.userId.isEmpty()) {
        query.bindValue(":userId", request.userId);
    }
    if (!request.deptId.isEmpty()) {
        query.bindValue(":deptId", request.deptId);
    }
    if (!request.sampleName.isEmpty()) {
        query.bindValue(":eqptName", "%" + request.sampleName + "%");
    }
    if (!request.sampleModel.isEmpty()) {
        query.bindValue(":model", "%" + request.sampleModel + "%");
    }
    if (!request.customerName.isEmpty()) {
        query.bindValue(":customer", "%" + request.customerName + "%");
    }
    if (!request.requireStart.isEmpty()) {
        query.bindValue(":start", request.requireStart);
    }
    if (!request.requireEnd.isEmpty()) {
        query.bindValue(":end", request.requireEnd);
    }
    if (!request.isUrgent.isEmpty()) {
        query.bindValue(":urgent", request.isUrgent);
    }
    if (!request.measureStatusList.isEmpty()) {
        for (int i = 0; i < request.measureStatusList.size(); i++) {
            query.bindValue(QString(":status%1").arg(i), request.measureStatusList.at(i));
        }
    }
}