using Bro.Common.Helper;
using Bro.M071.Model;
using Bro.M071.Model.Model;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Math;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Linq;
using System.Text;

namespace Bro.M071.DBManager
{
    public class MeasurementUnitResultManager : ModelManager<MeasurementUnitResult>
    {
        //DbProviderFactory Dbfactory = SQLiteFactory.Instance;
        protected override void ActionBeforeNewModel(DBModel context, MeasurementUnitResult config)
        {
            CheckProductionCodeDuplicate(context, config);
        }

        protected override void ActionBeforeUpdateModel(DBModel context, MeasurementUnitResult config)
        {
            CheckProductionCodeDuplicate(context, config);
        }

        private void CheckProductionCodeDuplicate(DBModel context, MeasurementUnitResult config)
        {
        }

        public List<MeasurementUnitResult_DTO> GetMeasurementUnitResult(MeasurementUnitResultRequest request)
        {
            using (DBModel context = new DBModel())
            {
                var mList = context.MeasurementUnitResult.Where(u => u.IS_DELETED == 0);
                var pList = context.ProductionMeasurementRecords.Where(u => u.IS_DELETED == 0);

                if (!string.IsNullOrWhiteSpace(request.ProductionBarcode))
                {
                    pList = pList.Where(u => u.ProductionBarcode.Contains(request.ProductionBarcode));
                }
                if (!string.IsNullOrWhiteSpace(request.ProductionCode))
                {
                    pList = pList.Where(u => u.ProductionCode.Contains(request.ProductionCode));
                }
                if (request.StartTime != null)
                {
                    pList = pList.Where(u => u.OperationStartTime >= request.StartTime);
                }
                if (request.EndTime != null)
                {
                    pList = pList.Where(u => u.OperationStartTime <= request.EndTime);
                }
                //mList
                if (!string.IsNullOrWhiteSpace(request.MeasurementName))
                {
                    mList = mList.Where(u => u.MeasurementName.Contains(request.MeasurementName));
                }
                if (!string.IsNullOrWhiteSpace(request.MeasurementResult))
                {
                    mList = mList.Where(u => u.MeasurementResult == request.MeasurementResult);
                }
                if (!string.IsNullOrWhiteSpace(request.MeasurementType))
                {
                    mList = mList.Where(u => u.MeasurementType == request.MeasurementType);
                }

                var query = from productionMeasurementRecords in pList
                            join measurementUnitResult in mList
                            on productionMeasurementRecords.ID equals measurementUnitResult.ProductionMeasurementRecordsId
                            //into pmList
                            select new MeasurementUnitResult_DTO
                            {
                                ProductionCode = productionMeasurementRecords.ProductionCode,
                                ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
                                ProductionMeasurementRecordsId = productionMeasurementRecords.ID,
                                OperationStartTime = productionMeasurementRecords.OperationStartTime,
                                OperationEndTime = productionMeasurementRecords.OperationEndTime,

                                Id = measurementUnitResult.ID,
                                MeasurementName = measurementUnitResult.MeasurementName,
                                MeasurementType = measurementUnitResult.MeasurementType,
                                MeasurementValue = measurementUnitResult.MeasurementValue,
                                MeasurementResult = measurementUnitResult.MeasurementResult
                            };
                var pageList = query.OrderBy(u => u.OperationStartTime).ToPagedList(request);
                request.TotalNum = mList.Count();
                return pageList;
            }
        }


        public ProductionMeasurementUnitResultExportDataSet GetProductionMeasurementUnitResultExportDataSet(MeasurementUnitResultRequest request)
        {
            ProductionMeasurementUnitResultExportDataSet productionMeasurementUnitResultExportDataSet = new ProductionMeasurementUnitResultExportDataSet();
            using (DBModel context = new DBModel())
            {
                var mList = context.MeasurementUnitResult.Where(u => u.IS_DELETED == 0);
                var pList = context.ProductionMeasurementRecords.Where(u => u.IS_DELETED == 0);
                var kList = context.KeyUnitData.Where(u => u.IS_DELETED == 0);

                if (!string.IsNullOrWhiteSpace(request.ProductionBarcode))
                {
                    pList = pList.Where(u => u.ProductionBarcode.Contains(request.ProductionBarcode));
                }
                if (!string.IsNullOrWhiteSpace(request.ProductionCode))
                {
                    pList = pList.Where(u => u.ProductionCode.Contains(request.ProductionCode));
                }
                if (request.StartTime != null)
                {
                    pList = pList.Where(u => u.OperationStartTime >= request.StartTime);
                }
                if (request.EndTime != null)
                {
                    pList = pList.Where(u => u.OperationStartTime <= request.EndTime);
                }

                //mList
                if (!string.IsNullOrWhiteSpace(request.MeasurementName))
                {
                    mList = mList.Where(u => u.MeasurementName.Contains(request.MeasurementName));
                }
                if (!string.IsNullOrWhiteSpace(request.MeasurementResult))
                {
                    mList = mList.Where(u => u.MeasurementResult == request.MeasurementResult);
                }
                if (!string.IsNullOrWhiteSpace(request.MeasurementType))
                {
                    mList = mList.Where(u => u.MeasurementType == request.MeasurementType);
                }

                productionMeasurementUnitResultExportDataSet.ProductionMeasurementRecordList = pList.ToList();
                var measurementUnitResults = from productionMeasurementRecords in pList
                                             join measurementUnitResult in mList on productionMeasurementRecords.ID equals measurementUnitResult.ProductionMeasurementRecordsId
                                             select new MeasurementUnitResult_DTO
                                             {
                                                 ProductionCode = productionMeasurementRecords.ProductionCode,
                                                 ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
                                                 ProductionMeasurementRecordsId = productionMeasurementRecords.ID,
                                                 OperationStartTime = productionMeasurementRecords.OperationStartTime,
                                                 OperationEndTime = productionMeasurementRecords.OperationEndTime,

                                                 Id = measurementUnitResult.ID,
                                                 MeasurementName = measurementUnitResult.MeasurementName,
                                                 MeasurementType = measurementUnitResult.MeasurementType,
                                                 MeasurementValue = measurementUnitResult.MeasurementValue,
                                                 MeasurementResult = measurementUnitResult.MeasurementResult
                                             };

                productionMeasurementUnitResultExportDataSet.MeasurementUnitResultList = measurementUnitResults.ToList();

                var keyUnitDataQuery = from productionMeasurementRecords in pList
                                       join keyUnitData in kList on productionMeasurementRecords.ID equals
                                       keyUnitData.ProductionMeasurementRecordsId
                                       select new KeyUnitData_DTO
                                       {
                                           ProductionCode = productionMeasurementRecords.ProductionCode,
                                           ProductionBarcode = productionMeasurementRecords.ProductionBarcode,
                                           ProductionMeasurementRecordsId = productionMeasurementRecords.ID,

                                           Id = keyUnitData.ID,
                                           Key = keyUnitData.Key,
                                           MeasurementItem = keyUnitData.MeasurementItem,
                                           ItemValue = keyUnitData.ItemValue,
                                       };
                productionMeasurementUnitResultExportDataSet.KeyUnitDataList = keyUnitDataQuery.ToList();

                return productionMeasurementUnitResultExportDataSet;
            }
        }
        public void BatchAddMeasurementUnitResult(List<MeasurementUnitResult> records)
        {
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
                {
                    using (SQLiteCommand insertRngCmd = (SQLiteCommand)conn.CreateCommand())
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into MeasurementUnitResult (");
                        strSql.Append("ID,ProductionMeasurementRecordsId,MeasurementName,MeasurementType,MeasurementValue,MeasurementResult,IS_DISABLED,IS_DELETED,CREATE_USER,CREATE_TIME, UPDATE_USER,UPDATE_TIME");
                        strSql.Append(")values (");
                        strSql.Append("@ID,@ProductionMeasurementRecordsId,@MeasurementName,@MeasurementType,@MeasurementValue,@MeasurementResult,@IS_DISABLED,@IS_DELETED,@CREATE_USER,@CREATE_TIME,@UPDATE_USER,@UPDATE_TIME)");

                        insertRngCmd.CommandText = strSql.ToString();
                        conn.Open();
                        var transaction = conn.BeginTransaction();

                        foreach (var record in records)
                        {
                            insertRngCmd.Parameters.AddWithValue("@ID", record.ID);
                            insertRngCmd.Parameters.AddWithValue("@ProductionMeasurementRecordsId", record.ProductionMeasurementRecordsId);
                            insertRngCmd.Parameters.AddWithValue("@MeasurementName", record.MeasurementName);
                            insertRngCmd.Parameters.AddWithValue("@MeasurementType", record.MeasurementType);
                            insertRngCmd.Parameters.AddWithValue("@MeasurementValue", record.MeasurementValue);
                            insertRngCmd.Parameters.AddWithValue("@MeasurementResult", record.MeasurementResult);
                            insertRngCmd.Parameters.AddWithValue("@IS_DISABLED", record.IS_DISABLED);
                            insertRngCmd.Parameters.AddWithValue("@IS_DELETED", record.IS_DELETED);
                            insertRngCmd.Parameters.AddWithValue("@CREATE_USER", record.CREATE_USER);
                            insertRngCmd.Parameters.AddWithValue("@CREATE_TIME", record.CREATE_TIME);
                            insertRngCmd.Parameters.AddWithValue("@UPDATE_USER", record.UPDATE_USER);
                            insertRngCmd.Parameters.AddWithValue("@UPDATE_TIME", record.UPDATE_TIME);
                            insertRngCmd.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
                throw new ProcessException(ex.Message, null);
            }
        }
    }
}