jt
2021-06-10 5d0d028456874576560552f5a5c4e8b801786f11
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
using HanHe.Utility.Data;
using HH.WMS.Common;
using HH.WMS.Entitys;
using HH.WMS.Entitys.Basic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace HH.WMS.DAL.Basic
{
    public class TN_WM_B_TRAY_INFODAL : DapperBaseDAL
    {
        #region GET
 
        public OperateResult GetForPrint(string trayCode, string trayType, string spec, int pageIndex, int pageSize)
        {
            string sqlWhere = " where 1=1 ";
            if (!string.IsNullOrEmpty(trayCode))
            {
                sqlWhere += " and CN_S_TRAY_CODE like '%" + trayCode + "%' ";
            }
            if (!string.IsNullOrEmpty(trayType))
            {
                sqlWhere += " and CN_S_TRAY_TYPE like '%" + trayType + "%'";
            }
            if (!string.IsNullOrEmpty(spec))
            {
                sqlWhere += " and CN_S_SPEC like '%" + spec + "%'";
            }
            sqlWhere += " and CN_S_TRAY_CODE not like 'VWT%' ";
            string sql = @"(select * from TN_WM_TRAY_INFO " + sqlWhere + ") AS AA ";
            return ExecutePagingResult(sql, pageIndex, pageSize);
        }
 
        #endregion
 
        #region 更新托盘实际重量
        /// <summary>
        /// 更新托盘实际重量
        /// </summary>
        /// <param name="trayCode">托盘 编码</param>
        /// <param name="weight">本次入托实际重量</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult updateTrayCodeWeight(string trayCode, decimal weight, IDbTransaction trans)
        {
            string sq1 = "UPDATE TN_WM_TRAY_INFO SET CN_S_STATE=@CN_S_STATE,CN_F_WEIGHT=CN_F_WEIGHT+@CN_F_WEIGHT,CN_S_USE_STATE=case when CN_F_WEIGHT+@CN_F_WEIGHT>=CN_F_LOADBEARING then '满' else '不满' end  WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
            return ExecuteTranSql(sq1, new
            {
                CN_S_STATE = Constants.TrayState_InUse,
                CN_F_WEIGHT = weight,
                CN_S_TRAY_CODE = trayCode
            }, trans);
        }
        #endregion
 
        #region 查询托盘是否已使用
        /// <summary>
        /// 查询托盘是否已使用
        /// </summary>
        /// <param name="trayCode">托盘号</param>
        /// <returns></returns>
        /// <History>[HANHE(DBS)] CREATED BY 2018-12-5</History>
        public bool GetUseState(string trayCode)
        {
            string sql = "select count(1) from tn_wm_b_tray_location where CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
 
            DataTable dt = ExecuteDataTable(sql, new
            {
                CN_S_TRAY_CODE = trayCode
            });
            if (Convert.ToInt16(dt.Rows[0][0]) == 0)
            {
                sql = "select COUNT(1) from TN_WM_B_TRAY_ITEM_MST where CN_S_TRAY_CODE=@CN_S_TRAY_CODE"; //tn_wm_b_tray_item_rel
                dt = ExecuteDataTable(sql, new
                {
                    CN_S_TRAY_CODE = trayCode
                });
            }
            return Convert.ToInt16(dt.Rows[0][0]) > 0;
        }
        #endregion
 
        #region  新增容器表
        /// <summary>
        /// 新增容器表
        /// </summary>
        /// <returns></returns>
        /// <History>[HANHE(LT)] CREATED BY 2018-12-27</History>
        public OperateResult Add(List<TN_WM_B_TRAY_INFOEntity> list, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
            foreach (TN_WM_B_TRAY_INFOEntity model in list)
            {
                StringBuilder strSql = new StringBuilder();
                //strSql.Append(" if not exists(select * from tn_wm_tray_info  " + model.sqlWhere + " )");
 
                strSql.Append(" IF NOT EXISTS(SELECT * FROM dbo.TN_WM_B_TRAY_ITEM_MST  " + model.sqlWhere + ") ");
                strSql.Append("insert into tn_wm_tray_info(");
                strSql.Append("CN_S_TRAY_CODE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_S_TRAY_CODE_EXT,CN_S_TRAY_TYPE,CN_T_MODIFY,CN_S_CREATOR,CN_S_TRAY_DIMENSION,CN_S_CREATOR_BY,CN_S_TRAY_COLOR,CN_S_DESC,CN_T_CREATE,CN_S_STATE,CN_S_SPEC,CN_S_SORTING_CODE,CN_S_COLLECT_CODE,CN_S_COLLECT_LOCATION,CN_N_LATTICE_NUM,CN_S_USE_STATE,CN_N_ROW_NUM,CN_N_COLUMN_NUM,CN_F_LOADBEARING,CN_F_GROSSWEIGHT,CN_F_WEIGHT)");
                strSql.Append(" values (");
                strSql.Append("@CN_S_TRAY_CODE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_S_TRAY_CODE_EXT,@CN_S_TRAY_TYPE,@CN_T_MODIFY,@CN_S_CREATOR,@CN_S_TRAY_DIMENSION,@CN_S_CREATOR_BY,@CN_S_TRAY_COLOR,@CN_S_DESC,@CN_T_CREATE,@CN_S_STATE,@CN_S_SPEC,@CN_S_SORTING_CODE,@CN_S_COLLECT_CODE,@CN_S_COLLECT_LOCATION,@CN_N_LATTICE_NUM,@CN_S_USE_STATE,@CN_N_ROW_NUM,@CN_N_COLUMN_NUM,@CN_F_LOADBEARING,@CN_F_GROSSWEIGHT,@CN_F_WEIGHT)");
 
                result = ExecuteTranSql(strSql.ToString(), new
                {
                    CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
                    CN_S_MODIFY = model.CN_S_MODIFY,
                    CN_S_MODIFY_BY = model.CN_S_MODIFY_BY,
                    CN_S_TRAY_CODE_EXT = model.CN_S_TRAY_CODE_EXT,
                    CN_S_TRAY_TYPE = model.CN_S_TRAY_TYPE,
                    CN_T_MODIFY = model.CN_T_MODIFY,
                    CN_S_CREATOR = model.CN_S_CREATOR,
                    CN_S_TRAY_DIMENSION = model.CN_S_TRAY_DIMENSION,
                    CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
                    CN_S_TRAY_COLOR = model.CN_S_TRAY_COLOR,
                    CN_S_DESC = model.CN_S_DESC,
                    CN_T_CREATE = model.CN_T_CREATE,
                    CN_S_STATE = model.CN_S_STATE,
                    CN_S_SPEC = model.CN_S_SPEC,
                    CN_S_SORTING_CODE = model.CN_S_SORTING_CODE,
                    CN_S_COLLECT_CODE = model.CN_S_COLLECT_CODE,
                    CN_S_COLLECT_LOCATION = model.CN_S_COLLECT_LOCATION,
                    CN_N_LATTICE_NUM = model.CN_N_LATTICE_NUM,
                    CN_S_USE_STATE = model.CN_S_USE_STATE,
                    CN_N_ROW_NUM = model.CN_N_ROW_NUM,
                    CN_N_COLUMN_NUM = model.CN_N_COLUMN_NUM,
                    CN_F_LOADBEARING = model.CN_F_LOADBEARING,
                    CN_F_GROSSWEIGHT = model.CN_F_GROSSWEIGHT,
                    CN_F_WEIGHT = model.CN_F_WEIGHT,
                }, trans);
                if (result.Status == ResultStatus.Error)
                    return result;
            }
            return result;
 
        }
        #endregion
 
        #region  新增容器表 单个
        /// <summary>
        /// 新增容器表
        /// </summary>
        /// <returns></returns>
        /// <History>[HANHE(LT)] CREATED BY 2018-12-27</History>
        public OperateResult Add(TN_WM_B_TRAY_INFOEntity model, IDbTransaction trans)
        {
            OperateResult result = new OperateResult();
 
            StringBuilder strSql = new StringBuilder();
            //strSql.Append(" if not exists(select * from tn_wm_tray_info  " + model.sqlWhere + " )");
            strSql.Append("insert into tn_wm_tray_info(");
            strSql.Append("CN_S_TRAY_CODE,CN_S_MODIFY,CN_S_MODIFY_BY,CN_S_TRAY_CODE_EXT,CN_S_TRAY_TYPE,CN_T_MODIFY,CN_S_CREATOR,CN_S_TRAY_DIMENSION,CN_S_CREATOR_BY,CN_S_TRAY_COLOR,CN_S_DESC,CN_T_CREATE,CN_S_STATE,CN_S_SPEC,CN_S_SORTING_CODE,CN_S_COLLECT_CODE,CN_S_COLLECT_LOCATION,CN_N_LATTICE_NUM,CN_S_USE_STATE,CN_N_ROW_NUM,CN_N_COLUMN_NUM,CN_F_LOADBEARING,CN_F_GROSSWEIGHT,CN_F_WEIGHT)");
            strSql.Append(" values (");
            strSql.Append("@CN_S_TRAY_CODE,@CN_S_MODIFY,@CN_S_MODIFY_BY,@CN_S_TRAY_CODE_EXT,@CN_S_TRAY_TYPE,@CN_T_MODIFY,@CN_S_CREATOR,@CN_S_TRAY_DIMENSION,@CN_S_CREATOR_BY,@CN_S_TRAY_COLOR,@CN_S_DESC,@CN_T_CREATE,@CN_S_STATE,@CN_S_SPEC,@CN_S_SORTING_CODE,@CN_S_COLLECT_CODE,@CN_S_COLLECT_LOCATION,@CN_N_LATTICE_NUM,@CN_S_USE_STATE,@CN_N_ROW_NUM,@CN_N_COLUMN_NUM,@CN_F_LOADBEARING,@CN_F_GROSSWEIGHT,@CN_F_WEIGHT)");
 
            result = ExecuteTranSql(strSql.ToString(), new
            {
                CN_S_TRAY_CODE = model.CN_S_TRAY_CODE,
                CN_S_MODIFY = model.CN_S_MODIFY,
                CN_S_MODIFY_BY = model.CN_S_MODIFY_BY,
                CN_S_TRAY_CODE_EXT = model.CN_S_TRAY_CODE_EXT,
                CN_S_TRAY_TYPE = model.CN_S_TRAY_TYPE,
                CN_T_MODIFY = model.CN_T_MODIFY,
                CN_S_CREATOR = model.CN_S_CREATOR,
                CN_S_TRAY_DIMENSION = model.CN_S_TRAY_DIMENSION,
                CN_S_CREATOR_BY = model.CN_S_CREATOR_BY,
                CN_S_TRAY_COLOR = model.CN_S_TRAY_COLOR,
                CN_S_DESC = model.CN_S_DESC,
                CN_T_CREATE = model.CN_T_CREATE,
                CN_S_STATE = model.CN_S_STATE,
                CN_S_SPEC = model.CN_S_SPEC,
                CN_S_SORTING_CODE = model.CN_S_SORTING_CODE,
                CN_S_COLLECT_CODE = model.CN_S_COLLECT_CODE,
                CN_S_COLLECT_LOCATION = model.CN_S_COLLECT_LOCATION,
                CN_N_LATTICE_NUM = model.CN_N_LATTICE_NUM,
                CN_S_USE_STATE = model.CN_S_USE_STATE,
                CN_N_ROW_NUM = model.CN_N_ROW_NUM,
                CN_N_COLUMN_NUM = model.CN_N_COLUMN_NUM,
                CN_F_LOADBEARING = model.CN_F_LOADBEARING,
                CN_F_GROSSWEIGHT = model.CN_F_GROSSWEIGHT,
                CN_F_WEIGHT = model.CN_F_WEIGHT,
            }, trans);
            if (result.Status == ResultStatus.Error)
                return result;
 
            return result;
 
        }
        #endregion
 
        #region 更新托盘实际重量及容器规格
        /// <summary>
        /// 更新托盘实际重量
        /// </summary>
        /// <param name="trayCode">托盘 编码</param>
        /// <param name="weight">本次入托实际重量</param>
        /// <param name="trans"></param>
        /// <returns></returns>
        /// <History>[HANHE(XDL)] CREATED BY 2018-11-14</History>
        public OperateResult updateTrayInfo(string trayCode, decimal? weight, string useState, string traySpec, IDbTransaction trans)
        {
            string sq1 = "UPDATE TN_WM_TRAY_INFO SET CN_S_SPEC=@CN_S_SPEC,CN_F_GROSSWEIGHT=@CN_F_GROSSWEIGHT,CN_S_USE_STATE=@CN_S_USE_STATE WHERE CN_S_TRAY_CODE=@CN_S_TRAY_CODE";
            return ExecuteTranSql(sq1, new
            {
                CN_F_GROSSWEIGHT = weight,
                CN_S_SPEC = traySpec,
                CN_S_USE_STATE = useState,
                CN_S_TRAY_CODE = trayCode
            }, trans);
        }
        #endregion
        #region 根据托盘号获取托盘信息
        public TN_WM_B_TRAY_INFOEntity GetModel(string trayCode)
        {
            string sql = @"SELECT * FROM tn_wm_tray_info WHERE CN_S_TRAY_CODE='" + trayCode + "'";
 
            return ExecuteSingleEntity<TN_WM_B_TRAY_INFOEntity>(sql, null);
        }
        /// <summary>
        /// 设置实体的各列
        /// <summary>
        private void SetEntity(TN_WM_B_TRAY_INFOEntity entity, IDataReader reader)
        {
            SetEntityUti(entity, "CN_GUID", "CN_GUID", reader);
            SetEntityUti(entity, "CN_S_TRAY_CODE", "CN_S_TRAY_CODE", reader);
            SetEntityUti(entity, "CN_S_TRAY_TYPE", "CN_S_TRAY_TYPE", reader);
            SetEntityUti(entity, "CN_S_USE_STATE", "CN_S_USE_STATE", reader);
            SetEntityUti(entity, "CN_S_SPEC", "CN_S_SPEC", reader);
        }
        #endregion
    }
}