CNAS/SyncLocalDAL/LaboratoryTestDAL.cs

using CnasLocalIDAL;
using CnasSynchronousCommon;
using CnasSynchrousModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace SyncLocalDAL
{
    public class LaboratoryTestDAL: LocalDBbase, ILaboratoryTest
    {
        //读取数据
        public List<LaboratoryTest> GetAllLaboratoryTests()
        {
            List<LaboratoryTest> laboratoryTests = new List<LaboratoryTest>();
            try
            { 
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strSql = "select * from LaboratoryTest";
                SQLiteDataReader reader = SQLiteHelper.ExecuteReader(strSql, new SQLiteParameter[] { });
                while (reader.Read())
                {
                    LaboratoryTest laboratoryTest = new LaboratoryTest();

                    AddLaboratoryTestValue(reader,laboratoryTest);

                    laboratoryTests.Add(laboratoryTest);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return laboratoryTests;
        }
        public List<LaboratoryTest> GetLaboratoryTestsByType(string strType)
        {
            List<LaboratoryTest> laboratoryTests = new List<LaboratoryTest>();
            try
            {
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strSql = $"select * from LaboratoryTest where datatype='{strType}'";
                SQLiteDataReader reader = SQLiteHelper.ExecuteReader(strSql, new SQLiteParameter[] { });
                while (reader.Read())
                {
                    LaboratoryTest laboratoryTest = new LaboratoryTest();

                    AddLaboratoryTestValue(reader, laboratoryTest);

                    laboratoryTests.Add(laboratoryTest);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return laboratoryTests;
        }

        public DataTable GetAllLaboratoryTestsToDT()
        {
            DataTable dt = new DataTable();
            try
            {
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strSql = "select * from LaboratoryTest";

                dt = SQLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[] { });
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return dt;
        }

        public DataTable GetLaboratoryTestsByTypeToDT(string strType)
        {
            DataTable dt = new DataTable();
            try
            {
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strSql = $"select * from LaboratoryTest where datatype='{strType}'";

                dt = SQLiteHelper.ExecuteQuery(strSql, new SQLiteParameter[] { });
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return dt;
        }

        //插入一行数据
        public int InsertLaboratory(LaboratoryTest test)
        {
            int iReturn = 0;
            string strSql = @"INSERT INTO LaboratoryTest (GUID
                                                        ,Sample_Number
                                                        ,Crucible_Number
                                                        ,Instrument_Number
                                                        ,Auto_Code
                                                        ,DataType
                                                        ,Empty_Crucible_Weight
                                                        ,Sample_Weight
                                                        ,AddSample_Weight
                                                        ,Drying_Weight
                                                        ,SecondDrying_Weight
                                                        ,ThirdDrying_Weight
                                                        ,BurningResidue_Weight
                                                        ,SecondBurningResidue_Weight
                                                        ,ThirdBurningResidue_Weight
                                                        ,Crucible_Location_Number
                                                        ,Cinder_Characteristics
                                                        ,Operator
                                                        ,DataMold
                                                        ,WeighingType
                                                        ,OperaDateTime
                                                        ,IfEnabled
                                                        ,Mad
                                                        ,Granularity)
                                            VALUES(@GUID
                                                        ,@Sample_Number
                                                        ,@Crucible_Number
                                                        ,@Instrument_Number
                                                        ,@Auto_Code
                                                        ,@DataType
                                                        ,@Empty_Crucible_Weight
                                                        ,@Sample_Weight
                                                        ,@AddSample_Weight
                                                        ,@Drying_Weight
                                                        ,@SecondDrying_Weight
                                                        ,@ThirdDrying_Weight
                                                        ,@BurningResidue_Weight
                                                        ,@SecondBurningResidue_Weight
                                                        ,@ThirdBurningResidue_Weight
                                                        ,@Crucible_Location_Number
                                                        ,@Cinder_Characteristics
                                                        ,@Operator
                                                        ,@DataMold
                                                        ,@WeighingType
                                                        ,@OperaDateTime
                                                        ,@IfEnabled
                                                        ,@Mad
                                                        ,@Granularity)";
            try
            {
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                int index = 0;
                SQLiteParameter[] parameters = new SQLiteParameter[test.GetType().GetProperties().Count()];
                for (int i = 0; i < test.GetType().GetProperties().Count(); i++)
                {
                    parameters[index] = new SQLiteParameter(test.GetType().GetProperties()[i].Name, test.GetType().GetProperties()[i].GetValue(test, null));
                    index++;
                }

                //执行SQL语句
                iReturn += SQLiteHelper.ExecuteNonQuery(strSql, parameters);
            }
            catch(Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return iReturn;
        }

        //更新数据
        public int UpdateLaboratory(LaboratoryTest test)
        {
            int iReturn = 0;
            string strSql = @"UPDATE LaboratoryTest set Sample_Number=@Sample_Number
                                                        ,Crucible_Number=@Crucible_Number
                                                        ,Instrument_Number=@Instrument_Number
                                                        ,Auto_Code=@Auto_Code
                                                        ,DataType=@DataType
                                                        ,Empty_Crucible_Weight=@Empty_Crucible_Weight
                                                        ,Sample_Weight=@Sample_Weight
                                                        ,AddSample_Weight=@AddSample_Weight
                                                        ,Drying_Weight=@Drying_Weight
                                                        ,SecondDrying_Weight=@SecondDrying_Weight
                                                        ,ThirdDrying_Weight=@ThirdDrying_Weight
                                                        ,BurningResidue_Weight=@BurningResidue_Weight
                                                        ,SecondBurningResidue_Weight=@SecondBurningResidue_Weight
                                                        ,ThirdBurningResidue_Weight=@ThirdBurningResidue_Weight 
                                                        ,Crucible_Location_Number=@Crucible_Location_Number
                                                        ,Cinder_Characteristics=@Cinder_Characteristics
                                                        ,Operator=@Operator
                                                        ,DataMold=@DataMold
                                                        ,WeighingType=@WeighingType
                                                        ,OperaDateTime=@OperaDateTime
                                                        ,IfEnabled=@IfEnabled
                                                        ,Mad=@Mad
                                                        ,Granularity=@Granularity
                                                          WHERE GUID=@GUID";
            try
            {
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                SQLiteParameter[] parameters = new SQLiteParameter[test.GetType().GetProperties().Count()];
                for (int i = 0; i < test.GetType().GetProperties().Count(); i++)
                {
                    parameters[i] = new SQLiteParameter(test.GetType().GetProperties()[i].Name, test.GetType().GetProperties()[i].GetValue(test, null));
                }

                //执行SQL语句
                iReturn += SQLiteHelper.ExecuteNonQuery(strSql, parameters);
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return iReturn;
        }

        //删除数据
        public int DeleteLaboratory(LaboratoryTest test)
        {
            int iReturn = 0;
            try
            {
                //删除数据时要删除记录表中相关数据，避免LaboratoryTestID重复导致无法正常增加记录。
                string strSql = $"DELETE FROM UpLoadRecord WHERE LaboratoryTestGUID='{test.GUID}';DELETE FROM LaboratoryTest WHERE GUID='{test.GUID}';";
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                //执行SQL语句
                iReturn += SQLiteHelper.ExecuteNonQuery(strSql, new SQLiteParameter[] { });
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return iReturn;
        }


        private void AddLaboratoryTestValue(SQLiteDataReader reader, LaboratoryTest laboratoryTest)
        {
            laboratoryTest.GUID = reader["GUID"].ToString();
            laboratoryTest.Sample_Number = reader["Sample_Number"].ToString();
            laboratoryTest.Crucible_Number = reader["Crucible_Number"].ToString();
            laboratoryTest.Crucible_Location_Number = reader["Crucible_Location_Number"].ToString();
            laboratoryTest.Instrument_Number = reader["Instrument_Number"].ToString();
            laboratoryTest.Auto_Code = reader["Auto_Code"].ToString();
            laboratoryTest.DataType = reader["DataType"].ToString();
            laboratoryTest.Cinder_Characteristics = reader["Cinder_Characteristics"].ToString();
            laboratoryTest.Operator = reader["Operator"].ToString();
            laboratoryTest.DataMold = reader["DataMold"].ToString();
            laboratoryTest.WeighingType = reader["WeighingType"].ToString();
            laboratoryTest.Granularity = reader["Granularity"].ToString();

            double defaultdouble = 0;
            if (double.TryParse(reader["Empty_Crucible_Weight"].ToString(), out defaultdouble))
                laboratoryTest.Empty_Crucible_Weight = defaultdouble;
            if (double.TryParse(reader["Sample_Weight"].ToString(), out defaultdouble))
                laboratoryTest.Sample_Weight = defaultdouble;
            if (double.TryParse(reader["AddSample_Weight"].ToString(), out defaultdouble))
                laboratoryTest.AddSample_Weight = defaultdouble;
            if (double.TryParse(reader["Drying_Weight"].ToString(), out defaultdouble))
                laboratoryTest.Drying_Weight = defaultdouble;
            if (double.TryParse(reader["SecondDrying_Weight"].ToString(), out defaultdouble))
                laboratoryTest.SecondDrying_Weight = defaultdouble;
            if (double.TryParse(reader["ThirdDrying_Weight"].ToString(), out defaultdouble))
                laboratoryTest.ThirdDrying_Weight = defaultdouble;

            if (double.TryParse(reader["BurningResidue_Weight"].ToString(), out defaultdouble))
                laboratoryTest.BurningResidue_Weight = defaultdouble;
            if (double.TryParse(reader["SecondBurningResidue_Weight"].ToString(), out defaultdouble))
                laboratoryTest.SecondBurningResidue_Weight = defaultdouble;
            if (double.TryParse(reader["ThirdBurningResidue_Weight"].ToString(), out defaultdouble))
                laboratoryTest.ThirdBurningResidue_Weight = defaultdouble;

            if (double.TryParse(reader["Mad"].ToString(), out defaultdouble))
                laboratoryTest.Mad = defaultdouble;

            DateTime defaultdate = DateTime.Now;
            if (DateTime.TryParse(reader["OperaDateTime"].ToString(), out defaultdate))
                laboratoryTest.OperaDateTime = defaultdate;

            laboratoryTest.IfEnabled = reader["IfEnabled"].ToString().ToLower() == "true" ? true : false;
        }

        /// <summary>
        /// 根据每种类型CNAS库里面的最大化验时间获取数据
        /// </summary>
        /// <param name="dictType"></param>
        /// <returns></returns>
        public List<LaboratoryTest> GetLaboratoryTestsByTypeAndDate(Dictionary<string,string> dictType)
        {
            List<LaboratoryTest> laboratoryTests = new List<LaboratoryTest>();
            try
            {
                //select* from laboratorytest where(datatype= 'ARB' and OperaDateTime> '2019-08-01') or datatype not in('ARB');
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strsql = "select * from LaboratoryTest where";
                string strsql_2 = "";
                foreach (var item in dictType)
                {
                    strsql += "(datatype='" + item.Key + "' and OperaDateTime>'" + item.Value + "') or";
                    strsql_2 += "'" + item.Key + "',";
                }      
                string strSql = strsql +" datatype not in(" +strsql_2.Substring(0, strsql_2.Length - 1)+")";
                SQLiteDataReader reader = SQLiteHelper.ExecuteReader(strSql, new SQLiteParameter[] { });
                while (reader.Read())
                {
                    LaboratoryTest laboratoryTest = new LaboratoryTest();

                    AddLaboratoryTestValue(reader, laboratoryTest);

                    laboratoryTests.Add(laboratoryTest);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return laboratoryTests;
        }

        /// <summary>
        /// 获取未上传的所有数据
        /// </summary>
        /// <param name="dictType"></param>
        /// <returns></returns>
        public List<LaboratoryTest> GetLaboratoryTestsByNoUpLoad()
        {
            List<LaboratoryTest> laboratoryTests = new List<LaboratoryTest>();
            try
            {
                //select* from laboratorytest where(datatype= 'ARB' and OperaDateTime> '2019-08-01') or datatype not in('ARB');
                //构建数据库连接
                SQLiteHelper.SetConnectionString(ConnectString);

                string strsql = "select * from LaboratoryTest A left join UpLoadRecord  B on A.GUID=B.LaboratoryTestGUID where B.GUID is null;";
                
                SQLiteDataReader reader = SQLiteHelper.ExecuteReader(strsql, new SQLiteParameter[] { });
                while (reader.Read())
                {
                    LaboratoryTest laboratoryTest = new LaboratoryTest();

                    AddLaboratoryTestValue(reader, laboratoryTest);

                    laboratoryTests.Add(laboratoryTest);
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                AppLog.Error(ex.Message);
                throw ex;
            }
            return laboratoryTests;
        }
    }
}