OTS/OTSIncAReportApp/3-ServiceCenter/DataOperation/DataAccess/UserSTDDbAccess.cs

using OTSCommon.DBOperate;
using OTSIncAReportApp._1_UI.OTSReportExport.DataIntegration;
using OTSIncAReportApp.DataOperation.DataAccess;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static OTSDataType.otsdataconst;

namespace OTSIncAReportApp._2_CommonFunction.CommonClass
{
    public class UserSTDDbAccess
    {

        private SqlHelper dbHelper;
       
        public UserSTDDbAccess(string STDDbName, string resultpath)
        {
            NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
            if (!STDDbName.Contains(".db"))
            {
                STDDbName += ".db";
            }
            if (STDDbName.ToLower() == "nostddb.db")
            {
                dbHelper = null;
                log.Error("Failed to load user-defined library" + "!");
            }
            string fullPath = resultpath + STDDbName;
            string fullPath2 = System.IO.Directory.GetCurrentDirectory() + "\\Config\\SysData\\" + STDDbName;
            if (System.IO.File.Exists(fullPath))
            {
                dbHelper = new SqlHelper("data source='" + fullPath + "'");
                log.Warn("Loading the user standard library:" + fullPath + "!");
            }
            else if (System.IO.File.Exists(fullPath2))
            {
                dbHelper = new SqlHelper("data source='" + fullPath2 + "'");
                log.Warn("Loading the user standard library:" + fullPath2 + "!");
            }
            else
            {
                dbHelper = null;
                log.Error("Failed to load user-defined library" + "!");
            }
        }
        public DataTable GetSubAttributeFromDatabase()
        {
            string sqliteString = "select STDId,Hardness,Density,Electrical_conductivity from ClassifySTD";
            DataTable DT = new DataTable();
            DT = dbHelper.ExecuteQuery(sqliteString);
            return DT;
        }
        /// <summary>
        /// 获得颗粒类别
        /// </summary>

        /// <param name="StandardLibraryAddress">标准库地址</param>
        /// <returns></returns>
        public DataTable ObtainParticleCategory_start(string StandardLibraryAddress)
        {
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("GroupId");
            dataTable.Columns.Add("GroupName");
            dataTable.Columns.Add("inoId");
            dataTable.Columns.Add("display");
            DataTable get_dt = SelectStandardLibraryGrouping(StandardLibraryAddress);
            if (get_dt.Rows.Count == 0)
            {
                DataRow data = dataTable.NewRow();
                data["inoId"] = 0;
                data["GroupId"] = 0;
                data["GroupName"] = "Default";
                data["display"] = "0";
                dataTable.Rows.Add(data);
                return dataTable;
            }
            DataRow[] dataRow = get_dt.Select("", "iorder ASC");
            int shul = 0;
            string DefaultGroupId = "";
            foreach (DataRow row in dataRow)
            {
                if (row.ItemArray[1].ToString() != "Default")
                {
                    DataRow data = dataTable.NewRow();
                    data["inoId"] = shul;
                    data["GroupId"] = row.ItemArray[0].ToString();
                    data["GroupName"] = row.ItemArray[1].ToString();
                    data["display"] = "0";
                    dataTable.Rows.Add(data);
                    shul++;
                }
                else
                {
                    DefaultGroupId = row.ItemArray[0].ToString();

                }
            }
            DataRow data2 = dataTable.NewRow();
            data2["inoId"] = shul;
            data2["GroupId"] = DefaultGroupId;
            data2["GroupName"] = "Default";
            data2["display"] = "0";
            dataTable.Rows.Add(data2);
            return dataTable;
        }
        /// <summary>
        /// 获取规则数据
        /// </summary>
        /// <param name="strings">组类别</param>
        /// <param name="a_SelectedIndex">标准库地址</param>
        /// <returns></returns>
        public List<DataTable> ObtainRuleData_start(DataTable strings, string a_SelectedIndex)
        {
            List<DataTable> List_data = new List<DataTable>();
            DataTable dt_stl = new DataTable();
            SqLiteHelper sh = new SqLiteHelper("data source='" + a_SelectedIndex + "'");
            dt_stl = sh.ExecuteQuery("select * from ClassifySTD");
            dt_stl.Columns.Add("display");
            if (dt_stl == null)
            {
                List_data.Add(dt_stl);
                return List_data;
            }

            for (int i = 0; i < strings.Rows.Count; i++)
            {
                DataTable table = dt_stl.Clone();
                table.TableName = strings.Rows[i]["GroupId"].ToString();
                for (int a = 0; a < dt_stl.Rows.Count; a++)
                {
                    if (strings.Rows[i]["GroupId"].ToString() == dt_stl.Rows[a]["GroupId"].ToString())
                    {
                        dt_stl.Rows[a]["display"] = 0;
                        table.Rows.Add(dt_stl.Rows[a].ItemArray);
                    }
                }

                DataView dvs = table.DefaultView;
                dvs.Sort = "ListNum ASC";
                DataTable TemporaryDataGroup = dvs.ToTable();
                List_data.Add(TemporaryDataGroup);
            }

            return List_data;
        }
        private DataTable SelectStandardLibraryGrouping(string a_SelectedIndex)
        {
            DataTable dt_stl = new DataTable();
            SqLiteHelper sh = new SqLiteHelper("data source='" + a_SelectedIndex + "'");
            dt_stl = sh.ExecuteQuery("select * from STDGroups");
            return dt_stl;
        }

        /// <summary>
        /// 获得颗粒类别
        /// </summary>
        /// <returns></returns>
        public List<string> ObtainParticleCategory(OTSReport_Export m_otsreport_export, BasicData basicData)
        {
            List<string> ClassName = new List<string>();
            if (m_otsreport_export.m_ReportApp.m_RptConfigFile.Systype != OTS_SysType_ID.IncA)
            {
                DataTable get_dt = ReadClassification(basicData);
                DataTable getClass_dt = get_dt.Clone();
                DataRow[] dataRow = get_dt.Select("", "iorder ASC");
                foreach (DataRow row in dataRow)
                {
                    if (row.ItemArray[1].ToString() != "Default")
                    {
                        getClass_dt.ImportRow(row);
                    }
                }
                for (int i = 0; i < getClass_dt.Rows.Count; i++)
                {
                    ClassName.Add(getClass_dt.Rows[i][1].ToString());
                }
                ClassName.Add("Default");

            }
            else
            {
                DataTable getClass_dt = ReadClassification(basicData);
                for (int i = 0; i < getClass_dt.Rows.Count; i++)
                {
                    ClassName.Add(getClass_dt.Rows[i][1].ToString());

                }

                bool bl = false;
                for (int i = 0; i < getClass_dt.Rows.Count; i++)
                {
                    if (getClass_dt.Rows[i]["GroupName"].ToString() != "NOT_INCLUTION" && getClass_dt.Rows[i]["GroupName"].ToString() != "Invalid"
                    && getClass_dt.Rows[i]["GroupName"].ToString() != "Not Identified")
                        if (getClass_dt.Rows[i]["GroupName"].ToString() == "")
                        {
                            if (!bl)
                            {
                                ClassName.Add("Default");
                                bl = true;
                            }
                        }
                        else
                        {
                            bool isDefault = false;

                            for (int a = 0; a < ClassName.Count; a++)
                            {
                                if (getClass_dt.Rows[i]["GroupName"].ToString() == "Default")
                                {
                                    isDefault = true;
                                }
                            }
                            if (isDefault)
                            {
                                if (getClass_dt.Rows[i]["GroupName"].ToString() != "Default")
                                {
                                    ClassName.Add(getClass_dt.Rows[i]["GroupName"].ToString());
                                }
                            }
                            else
                            {
                                ClassName.Add(getClass_dt.Rows[i]["GroupName"].ToString());
                            }
                        }
                }
            }
            return ClassName;
        }
        public DataTable ReadClassification(BasicData basicData)
        {
            DataTable dt_stl = new DataTable();
            SqLiteHelper sh = new SqLiteHelper("data source='" + basicData.GetFilePath() + "\\" + basicData.GetResfile() + "'");
            dt_stl = sh.ExecuteQuery("select * from STDGroups");
            return dt_stl;
        }
        public SqlHelper GetSqlHelper() { return dbHelper; }
    }
}