数据查询功能

wells.liu

2020-07-08 ee940e0f7c7f00471976bc151319e2e2baf1062b

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
using Bro.Common.Helper;
using Bro.M071.Model;
using Bro.M071.Model.Model;
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
                            {
                                ProductionMeasurementRecord= productionMeasurementRecords,
                                MeasurementUnitResult= measurementUnitResult
 
                            };
                var pageList = query.OrderBy(u => u.ProductionMeasurementRecord.OperationStartTime).ToPagedList(request);
                request.TotalNum = mList.Count();
                return pageList;
            }
        }
 
        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);
            }
        }
    }
}