JSBTDieBondH/View/die-bonder-ui/Src/Sql/SqlOperation.cpp

// *****************************************************************************
// 版权所有（C）2023~2099 上海骄成超声波技术有限公司
// 保留所有权利
// *****************************************************************************
// 作者 : 杨坚
// 版本 : 1.0
// 功能说明:
//          数据库操作类
// *****************************************************************************
#include "SqlOperation.h"
#include <QDir>
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QSqlError>
#include <QString>
#include <QTextCodec>
#include <iostream>
#include <string>
#include "../common/JLogAllOutput.h"
#include "../common/JMessageTip.h"
#include "CManageDB.h"

SqlOperation::SqlOperation()
{
    m_pCViewInterface.reset(ns_module::CViewInterface::GetInstance());
    if (m_pCViewInterface == nullptr)
    {
        ErrorInfo(QObject::tr("SqlOperation:Initialization failed, please check", "初始化失败,请检查"));
        return;
    }

    //注册回调
    DataNoticeCallbackFun fun = std::bind(&SqlOperation::DataChange, this, std::placeholders::_1, std::placeholders::_2);
    //模组
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, BONDHEAD_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, WAFERHEAD_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, WAFFLEHEAD_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, EJECTOR_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, WAFERTABLE_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, WAFER_CALIB_TABLE_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, WAFFLE_CALIB_TABLE_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, BOND_TABLE_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, NOZZLE_TABLE_CONFIG_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(MODULECONFIG_DATABASE_NAME, ULTRAHEAD_TABLE_NAME, fun);
    //标定
    m_pCViewInterface->RecvDataChangNotice(CALIBCONFIG_DATABASE_NAME, CALIB_CTD_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(CALIBCONFIG_DATABASE_NAME, CALIB_XY_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(CALIBCONFIG_DATABASE_NAME, CALIB_R_TABLE_NAME, fun);
    m_pCViewInterface->RecvDataChangNotice(CALIBCONFIG_DATABASE_NAME, FORCE_CONTROL_TABLE_NAME, fun);

}

SqlOperation& SqlOperation::GetInstance()
{
    static SqlOperation instance;
    return instance;
}

int SqlOperation::LoadSql()
{


    // 获取构建目录路径
    QString buildDirPath = QCoreApplication::applicationDirPath();
    QDir buildDirectory(buildDirPath);
    QString strError;

    QString destDbPath = buildDirectory.filePath("configurations.db");

    // 检查文件是否存在
    QFile databaseFile(destDbPath);
    if (!databaseFile.exists()) 
    {
        strError = QObject::tr("The database file does not exist:", "数据库文件不存在:") + destDbPath;
        ErrorInfo(strError);
    }
    else
    {
        // 创建数据库连接
        QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
        db.setDatabaseName(destDbPath);

        if (!db.open())
        {
            strError = QObject::tr("Database open failure:", "数据库打开失败:") + db.lastError().text();
            ErrorInfo(strError);
        }
        else
        {
            int rtn = loadMenu();
            if (rtn != 0) 
                return rtn;
            rtn = loadAllDirectories();
            if (rtn != 0) 
                return rtn;
            rtn = loadAllControlData();
            if (rtn != 0) 
                return rtn;
            rtn = LoadAppConfig();
            if (rtn != 0)
                return rtn;

            return 0;
        }
    }

    return -1;
}

int SqlOperation::loadMenu()
{
    QSqlDatabase db = QSqlDatabase::database();
    if (!db.isOpen())
    {
        qWarning() << "数据库未打开";
        return 0;
    }
    QSqlQuery query(db);
    QString sql = QString("SELECT Id, Name FROM %1").arg(MENU_TABLE_NAME);
    if (!query.exec(sql))
    {
        qWarning() << "查询 Mune 失败:" << query.lastQuery() << "  " << query.lastError().text();
        return 0;
    }
    int id = 0;
    QString name = "";
    while (query.next())
    {
        id = query.value("Id").toInt();
        name = query.value("Name").toString();
        m_mapMenu[id] = name;
    }
    return 0;
}

int SqlOperation::loadAllDirectories()
{
    QList<QJsonObject> dirs;
    int rtn = loadDirectories(Dir_Calibration_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_Calibration_TABLE_NAME] = dirs;


    dirs.clear();
    rtn = loadDirectories(Dir_Diagnosis_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_Diagnosis_TABLE_NAME] = dirs;


    dirs.clear();
    rtn = loadDirectories(Dir_Message_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_Message_TABLE_NAME] = dirs;

    dirs.clear();
    rtn = loadDirectories(Dir_ModuleConfig_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_ModuleConfig_TABLE_NAME] = dirs;

    dirs.clear();
    rtn = loadDirectories(Dir_ProductAssist_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_ProductAssist_TABLE_NAME] = dirs;

    dirs.clear();
    rtn = loadDirectories(Dir_Programme_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_Programme_TABLE_NAME] = dirs;

    dirs.clear();
    rtn = loadDirectories(Dir_SystemInfo_TABLE_NAME, dirs);
    if (rtn != 0)
    {
        return -1;
    }
    m_mapAllDirectories[Dir_SystemInfo_TABLE_NAME] = dirs;





    //for (auto it = m_mapMenu.begin(); it != m_mapMenu.end(); ++it)
    //{
    //    if (it.value() == "Home")
    //    {
    //        continue;
    //    }
    //    QList<QJsonObject> dir;
    //    QString tableName = "Dir_" + it.value();
    //    int rtn  = loadDirectories(tableName, dir);
    //    if (rtn != 0)
    //    {
    //        return -1;
    //    }
    //    m_mapAllDirectories[it.value()] = dir;
    //}
    return 0;
}


int SqlOperation::loadDirectories(QString tableName, QList<QJsonObject>& directories)
{
    QSqlDatabase db = QSqlDatabase::database();
    if (!db.isOpen())
    {
        qWarning() << "数据库未打开";
        return 0 ;
    }

    QSqlQuery query(db);
    QString sql = QString("SELECT Id, Name_Chs, ParentId, IsThirdLevel, Separator, UserGrade, TableName, Circle FROM %1").arg(tableName);
    //query.prepare(R"( SELECT Id, Name_Chs, ParentId, IsThirdLevel, Separator, UserGrade, Circle FROM :tableName )");
    //query.bindValue(":tableName", tableName);

    if (!query.exec(sql))
    {
        qWarning() << "查询 Directories 失败:" << query.lastQuery() << "  " << query.lastError().text();
        return -1;
    }

    // 以 QJsonObject 保存所有目录
    //QList<QJsonObject> directories;
    while (query.next())
    {
        QJsonObject obj;   
        int id = query.value("Id").toInt();
        obj["Id"] = id;
        obj["Name"] = query.value("Name_Chs").toString();
        obj["ParentId"] = query.value("ParentId").isNull() ? -1 : query.value("ParentId").toInt();
        obj["IsThirdLevel"] = query.value("IsThirdLevel").toBool();
        obj["Separator"] = query.value("Separator").toInt();
        obj["UserGrade"] = query.value("UserGrade").toString();
        obj["Circle"] = query.value("Circle").toInt();
        obj["TableName"] = query.value("TableName").toString();
        obj["Icon"]  = query.value("Icon").toString();
        obj["Enabled"] = query.value("Enabled").toBool();
        qDebug() << "loadDirectories：" << obj["Name"].toString() << " ParentId:" << obj["ParentId"].toInt() << "UserGrade:" << obj["UserGrade"] << " Separator:" << obj["Separator"].toInt();
        qDebug() << " Circle:" << obj["Circle"].toInt();

        // 权限判断：只有当记录的 UserGrade 中包含当前用户权限时才加入
        //bool ok;
        //if (hasPrivilege(obj["UserGrade"].toString().toInt(&ok, 16), userPrivilege))
            directories.append(obj);
       // else
          //  qDebug() << "目录跳过（权限不足）:" << obj["Name"].toString();
    }

    return 0;
}

int SqlOperation::loadAllControlData()
{
    QTextCodec* codec = QTextCodec::codecForName("GBK");
    if (!codec) {
        std::cerr << "不支持的编码" << std::endl;
        return -1;
    }

    m_allControlData.clear();
    QList<QString> moduleTableNameList;
    moduleTableNameList.append(BONDHEAD_CONFIG_TABLE_NAME);
    moduleTableNameList.append(WAFERHEAD_CONFIG_TABLE_NAME);
    moduleTableNameList.append(WAFFLEHEAD_CONFIG_TABLE_NAME);
    moduleTableNameList.append(EJECTOR_CONFIG_TABLE_NAME);
    moduleTableNameList.append(WAFERTABLE_CONFIG_TABLE_NAME);
    moduleTableNameList.append(WAFER_CALIB_TABLE_CONFIG_TABLE_NAME);
    moduleTableNameList.append(WAFFLE_CALIB_TABLE_CONFIG_TABLE_NAME);
    moduleTableNameList.append(BOND_TABLE_CONFIG_TABLE_NAME);
    moduleTableNameList.append(NOZZLE_TABLE_CONFIG_TABLE_NAME);
    moduleTableNameList.append(ULTRAHEAD_TABLE_NAME);

    for (QString& dbName : moduleTableNameList)
    {
        Table_Control_Data controlDatas_module;
        vector<int> iGroupId;
        vector<CONFIG_BASE_STRUCT> vecConfig;
        if (m_pCViewInterface)
        {
            m_pCViewInterface->LoadConfigDB(dbName.toStdString(), iGroupId, vecConfig);
            controlDatas_module.tableName = dbName;
            // 遍历 std::vector 并将元素添加到 QList 中
            for (CONFIG_BASE_STRUCT& element : vecConfig) {
                QString str;
                if (element.strDescribe.size() > 0)
                {
                    str = codec->toUnicode(element.strDescribe.data(), element.strDescribe.size());
                    element.strDescribe = str.toStdString();
                }
                if (element.strUnit.size() > 0)
                {
                    str = codec->toUnicode(element.strUnit.data(), element.strUnit.size());
                    element.strUnit = str.toStdString();
                }
                controlDatas_module.controlDataList.append(element);
            }
            m_allControlData.append(controlDatas_module);
        }

        /*int rtn1 = loadGivenTableControlData_D_Config(dbName, controlDatas_module);
        if (rtn1 == 0)
            m_allControlData.append(controlDatas_module);*/
    }


    Table_Control_Data controlData;
    int rtn = LoadControlDataFromModule(CALIB_XY_TABLE_NAME, controlData);  
    if (rtn == 0) { m_allControlData.append(controlData); }


    rtn = LoadControlDataFromModule(CALIB_CTD_TABLE_NAME, controlData);
    if (rtn == 0) { m_allControlData.append(controlData); }

    rtn = LoadControlDataFromModule(CALIB_R_TABLE_NAME, controlData);
    if (rtn == 0) { m_allControlData.append(controlData); }


    for (auto it = m_mapAllDirectories.begin(); it != m_mapAllDirectories.end(); ++it)
    {
        for (int i = 0; i < it.value().size(); i++)
        {
            QJsonObject controlData = it.value()[i];
            if (controlData["IsThirdLevel"].toBool())
            {
                QStringList tableNames = controlData["TableName"].toString().split(";");
                for (const QString& tableAndGroup : tableNames)
                {
                    QString name = tableAndGroup.split(",")[0];
                    if (isModuleTable(name) || name.isEmpty())
                    {
                        continue;
                    }
                    Table_Control_Data controlDatas;
                    rtn = loadGivenTableControlData(name, controlDatas);
                    if (rtn != 0)
                    {
                        qDebug() << "read table fail : " << name;
                        continue;
                    }
                    m_allControlData.append(controlDatas);
                }
                
            }
        }
    }
    //另外加载HomeProduct的控件数据
    Table_Control_Data controlDatas;
    rtn = loadGivenTableControlData(HOME_TABLE_NAME, controlDatas);
    if (rtn != 0) return rtn;
    m_allControlData.append(controlDatas);
    return 0;
}

int SqlOperation::loadGivenTableControlData(QString tableName, Table_Control_Data& controlDatas)
{
    QSqlDatabase db = QSqlDatabase::database();
    if (!db.isOpen())
    {
        qWarning() << "数据库未打开";
        return -1;
    }

    if (tableName.isEmpty())
    {
        qDebug() << "Encountered an empty string, stopping traversal.";
        return -1;
    } else
    {
        QString sql = QString("SELECT GroupId, Name, Describe,Describe_Eng, Type, Value, UpperLimit,LowerLimit,Unit, UserGrade, ChangeFlag, Icon, Enable, CallFun FROM %1 ").arg(tableName);
        QSqlQuery queryBond(db);
        queryBond.prepare(sql);
        if (queryBond.exec())
        {
            CONFIG_BASE_STRUCT value;
            QList<CONFIG_BASE_STRUCT> valueList;
            while (queryBond.next())
            {
                bool ok;
                value.iUserGrade = queryBond.value("UserGrade").toString().toInt(&ok, 16);
                value.strName = queryBond.value("Name").toString().toStdString();
                value.strDescribe = queryBond.value("Describe").toString().toStdString();
                //value.strDescribe_Eng = queryBond.value("Describe_Eng").toString().toStdString();
                value.strType = queryBond.value("Type").toString().toStdString();
                value.strValue = queryBond.value("Value").toString().toStdString();
                value.strUpperLimit = queryBond.value("UpperLimit").toString().toStdString();
                value.strLowerLimit = queryBond.value("LowerLimit").toString().toStdString();
                value.strUnit = queryBond.value("Unit").toString().toStdString();
                value.iGroupId = queryBond.value("GroupId").toInt();
                value.iChangeFlag = queryBond.value("ChangeFlag").toInt();
                value.sIcon = queryBond.value("Icon").toString().toStdString();
                value.bEnable = queryBond.value("Enable").toBool();
                value.strCallFun = queryBond.value("CallFun").toString().toStdString();
                valueList.append(value);
            }
            controlDatas.tableName = tableName;
            controlDatas.controlDataList = valueList;
        }
        else
        {
            qWarning() << "查询 BondHead 失败:" << queryBond.lastError().text();
            return -1;
        }

    }
    return 0;
}



int SqlOperation::updateDb(QString tableName, QString name, const QString currentValue)
{
    QSqlDatabase db = QSqlDatabase::database();
    if (!db.isOpen())
    {
        qWarning() << "数据库未打开";
    }
    QString sql = QString("UPDATE %1 SET currentValue = :newValue WHERE Name = :name").arg(tableName);
    QSqlQuery query;
    query.prepare(sql);
    query.bindValue(":newValue", currentValue);
    query.bindValue(":name", name);
    // 执行更新操作
    if (!query.exec())
    {
        qWarning() << "Failed to update currentValue:" << query.lastError().text();
    } else
    {
        qDebug() << "currentValue updated successfully.";
    }
    return 0;
}

int SqlOperation::LoadAppConfig()
{
    int nErr = -1;

    QSqlDatabase db = QSqlDatabase::database();
    if (!db.isOpen())
    {
        JLogAllOutput::cmd_Warning("数据库未打开");
    }
    else
    {
        QSqlQuery query(db);
        QString sql = QString("SELECT ID, SwitchWnd,GroupId_R,Index_R,GroupId_L,Index_L FROM %1").arg("AppConfig");
        if (!query.exec(sql))
        {
            QString strErr = "查询 Mune 失败:";
            strErr += query.lastQuery();
            strErr += "  ";
            strErr += query.lastError().text();
            JLogAllOutput::cmd_Warning(strErr.toStdString());
            nErr = 1;
        }
        else
        {
            while (query.next())
            {
                m_appConifg.nID = query.value("Id").toInt();
                m_appConifg.nID = query.value("SwitchWnd").toInt();
                m_appConifg.nGroupId_R = query.value("GroupId_R").toInt();
                m_appConifg.nIndex_R = query.value("Index_R").toInt();
                m_appConifg.nGroupId_L = query.value("GroupId_L").toInt();
                m_appConifg.nIndex_L = query.value("Index_L").toInt();
            }
            nErr = 0;
        }
    }

    return nErr;
}

int SqlOperation::GetDirectories(QString tableName, const int& userPrivilege, QList<QJsonObject>& directories)
{
    auto it = m_mapAllDirectories.find(tableName);
    if (it == m_mapAllDirectories.end())
    {
        qDebug() << "获取目录表名出错！";
        return false;
    }
    for (QJsonObject& dir : m_mapAllDirectories[tableName])
    {
        bool ok;
        if (dir["UserGrade"].toString().toInt(&ok, 16) == userPrivilege)
        {
            directories.append(dir);
        }
    }
    return true;
}

int SqlOperation::GetThirdDirControlData(QString multiTableName, const int& userPrivilege, QList<Table_Control_Data>& data, bool& allChangeFlag)
{
    allChangeFlag = true;
    QStringList tableNames = multiTableName.split(";");
    for (const QString& name : tableNames)
    {
        QStringList nameAndGroups = name.split(",");
        vector<int> vecGroup;
        for (int i = 1; i < nameAndGroups.size(); i++)
        {
            vecGroup.push_back(nameAndGroups[i].toInt());
        }

        Table_Control_Data needControls;
        for (Table_Control_Data& tableData : m_allControlData)
        {
            //寻找表格对应的所有控件
            if (tableData.tableName == nameAndGroups[0])
            {
                needControls.tableName = name;

                //寻找用户等级对应的控件
                for (CONFIG_BASE_STRUCT& value : tableData.controlDataList)
                {
                    if (vecGroup.size() == 0)
                    {
                        needControls.controlDataList.append(value);
                        if (value.iChangeFlag == false)
                        {
                            allChangeFlag = false;
                        }
                    } else
                    {
                        for (int& groupId : vecGroup)
                        {
                            //if (value.iUserGrade == userPrivilege)
                            if (value.iGroupId == groupId)
                            {
                                needControls.controlDataList.append(value);
                                if (value.iChangeFlag == false)
                                {
                                    allChangeFlag = false;
                                }
                            }
                        }
                    }

                }
                
            }
        }
        data.append(needControls);
    }
    return 0;
}


int SqlOperation::updateControlData(QString tableName, QString name, const QString& currentValue)
{
    vector<CONFIG_BASE_STRUCT> changeData;
    for (Table_Control_Data &tableData: m_allControlData)
    {
        if (tableData.tableName == tableName)
        {
            for (CONFIG_BASE_STRUCT& controlData : tableData.controlDataList)
            {
                if (controlData.strName == name.toStdString())
                {
                    controlData.strValue = currentValue.toStdString();
                    changeData.push_back(controlData);
                    m_pCViewInterface->SaveConfigDB(tableName.toStdString(), changeData);
                }
            }                    
        } 
    }
    return 0;
}

int SqlOperation::GetThirdDirButtons(QString multiTableName, const int& userPrivilege, QList<CONFIG_BASE_STRUCT>& buttons)
{
    QStringList tableNames = multiTableName.split(",");
    for (const QString& name : tableNames)
    {
        for (Table_Control_Data& tableData : m_allControlData)
        {
            //寻找表格对应的所有控件
            if (tableData.tableName == name)
            {
                //寻找用户等级对应的控件
                for (CONFIG_BASE_STRUCT& value : tableData.controlDataList)
                {
                    if (value.iUserGrade == userPrivilege)
                    {
                        buttons.append(value);
                    }
                }
            }
        }
    }
    return 0;
}

int SqlOperation::GetMuneName(int id, QString& name)
{
    return 0;
}

void SqlOperation::ErrorInfo(const QString& strError)
{
    JMessageTip::Message_question(strError);
    JLogAllOutput::cmd_debug(strError.toStdString());
    //exit(0); //TODO: 关闭可以交给主界面
}

bool SqlOperation::isModuleTable(QString tableName)
{
    if (tableName == BONDHEAD_CONFIG_TABLE_NAME ||
        tableName == WAFERHEAD_CONFIG_TABLE_NAME ||
        tableName == WAFFLEHEAD_CONFIG_TABLE_NAME ||
        tableName == EJECTOR_CONFIG_TABLE_NAME ||
        tableName == WAFERTABLE_CONFIG_TABLE_NAME ||
        tableName == WAFER_CALIB_TABLE_CONFIG_TABLE_NAME ||
        tableName == WAFFLE_CALIB_TABLE_CONFIG_TABLE_NAME ||
        tableName == BOND_TABLE_CONFIG_TABLE_NAME ||
        tableName == NOZZLE_TABLE_CONFIG_TABLE_NAME ||
        tableName == ULTRAHEAD_TABLE_NAME ||
        tableName == CALIB_CTD_TABLE_NAME ||
        tableName == CALIB_XY_TABLE_NAME ||
        tableName == CALIB_R_TABLE_NAME)
    {
        return true;
    }
    return false;
}

int SqlOperation::LoadControlDataFromModule(std::string tableName, Table_Control_Data& controlData)
{
    QTextCodec* codec = QTextCodec::codecForName("GBK");
    vector<int> vecCalibGroupId;
    vector<CONFIG_BASE_STRUCT> vecCalibConfig;
    int rtn = m_pCViewInterface->LoadConfigDB(tableName, vecCalibGroupId, vecCalibConfig);
    if (rtn != OK)
    {
        JLogAllOutput::cmd_debug("读取底层表格数据出错");
        return -1;
    }
    controlData.tableName = QString::fromStdString(tableName);
    controlData.controlDataList.clear();
    for (CONFIG_BASE_STRUCT& element : vecCalibConfig)
    {
        QString str;
        if (element.strDescribe.size() > 0)
        {
            str = codec->toUnicode(element.strDescribe.data(), element.strDescribe.size());
            element.strDescribe = str.toStdString();
        }
        if (element.strUnit.size() > 0)
        {
            str = codec->toUnicode(element.strUnit.data(), element.strUnit.size());
            element.strUnit = str.toStdString();
        }
        controlData.controlDataList.append(element);
    }
    return 0;
    //m_allControlData.append(controlData);
}

void SqlOperation::DataChange(string strDbName, string strTableName)
{

}