NX_WCS/NX_WcsDal/WcsBusiness/Tran_Dal.cs

using NX_CommonClassLibrary;
using NX_DbClassLibrary;
using NX_LogClassLibrary;
using NX_ModelClassLibrary.CustomEnum;
using NX_ModelClassLibrary.TranModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NX_WcsDal.WcsBusiness
{
    public class Tran_Dal
    {
        #region 单例模式
        /// <summary>
        /// 单例模式对象
        /// </summary>
        private static Tran_Dal _instance = null;
        private static readonly object lockObj = new object();
        /// <summary>
        /// 单例模式方法
        /// </summary>
        public static Tran_Dal Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (lockObj)
                    {
                        if (_instance == null)
                        {
                            _instance = new Tran_Dal();
                        }
                    }
                }
                return _instance;
            }
        }
        #endregion

        /// <summary>
        /// 加载输送线未完成的指令数据
        /// </summary>
        /// <returns></returns>
        public List<WcsTranCmdMd> LoadTranUnFinishedCmd(string regionCode)
        {
            try
            {
                string sqlQueryTranUnFinishedCmd = $@"
                                                        SELECT
	                                                        A.TRAN_CMD_ID,
	                                                        A.NEWID,
	                                                        A.TASK_NO,
	                                                        A.CMD_NO,
	                                                        A.TRAY_CODE,
	                                                        A.PALLET_CODE,
	                                                        A.TRAY_STATUS,
	                                                        A.TRAN_DEV_NO,
	                                                        A.SLOC_NO,
	                                                        A.SPLC_NO,
	                                                        A.ELOC_NO,
	                                                        A.EPLC_NO,
	                                                        A.CMD_TYPE,
	                                                        A.ACTIVE_TYPE,
	                                                        A.CMD_PRIORITY,
	                                                        A.CMD_STATUS,
	                                                        A.CMD_LOG,
	                                                        A.ERR_FLAG,
	                                                        A.ERR_LOG,
	                                                        A.CREATE_BY,
	                                                        B.USER_CODE CreateCode,
	                                                        B.USER_NAME CreateName,
	                                                        A.CREATE_TIME,
	                                                        A.EXECUTE_TIME,
	                                                        A.FINISH_TIME,
	                                                        A.UPDATE_BY,
	                                                        C.USER_CODE UpdateCode,
	                                                        C.USER_NAME UpdateName,
	                                                        A.UPDATE_TIME,
	                                                        A.DATA_VERSION,
	                                                        A.REMARKS1,
	                                                        A.REMARKS2,
	                                                        A.REMARKS3,
	                                                        A.REMARKS4,
	                                                        A.REMARKS5
                                                        FROM
	                                                        WCS_TRAN_CMD A
                                                        LEFT JOIN SYS_USER B ON A.CREATE_BY = B.USER_ID
                                                        LEFT JOIN SYS_USER C ON A.UPDATE_BY = C.USER_ID
                                                        WHERE
	                                                        A.CMD_STATUS < '{(int)TranCmdStatusEnum.完成}'
                                                        AND A.REGION_CODE = '{regionCode}'
                                                    ";
                DataTable dt = LeadDbHelperSQLServer.Query(sqlQueryTranUnFinishedCmd).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsTranCmdMd> retLst = new List<WcsTranCmdMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        WcsTranCmdMd tmpMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsTranCmdMd>(dt.Columns, item);
                        retLst.Add(tmpMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"加载输送线未完成指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
        /// <summary>
        /// 更新输送线指令状态
        /// </summary>
        /// <param name="wcsTranCmdMd">输送线指令对象</param>
        public void UpdateTranCmdStatus(WcsTranCmdMd wcsTranCmdMd)
        {
            try
            {
                string sqlUpdateTranCmdStatus = string.Empty;
                if (wcsTranCmdMd.CmdStatus == TranCmdStatusEnum.已下发PLC)
                {
                    sqlUpdateTranCmdStatus = $@"
                                                UPDATE WCS_TRAN_CMD
                                                SET 
                                                    CMD_STATUS = '{(int)wcsTranCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsTranCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE(),
                                                    EXECUTE_TIME = GETDATE()
                                                WHERE 
                                                TRAN_CMD_ID = '{wcsTranCmdMd.TranCmdId}'
                                            ";
                }
                else if (wcsTranCmdMd.CmdStatus == TranCmdStatusEnum.完成)
                {
                    sqlUpdateTranCmdStatus = $@"
                                                UPDATE WCS_TRAN_CMD
                                                SET CMD_STATUS = '{(int)wcsTranCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsTranCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE(),
                                                    FINISH_TIME = GETDATE()
                                                WHERE 
                                                TRAN_CMD_ID = '{wcsTranCmdMd.TranCmdId}'
                                            ";
                }
                else
                {
                    sqlUpdateTranCmdStatus = $@"
                                                UPDATE WCS_TRAN_CMD
                                                SET CMD_STATUS = '{(int)wcsTranCmdMd.CmdStatus}',
                                                    UPDATE_BY = '{wcsTranCmdMd.UpdateBy}',
                                                    UPDATE_TIME = GETDATE()
                                                WHERE 
                                                TRAN_CMD_ID = '{wcsTranCmdMd.TranCmdId}'
                                            ";
                }
                LeadDbHelperSQLServer.ExecuteSql(sqlUpdateTranCmdStatus);
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"更新输送线指令状态发生异常！【{ex.Message}】", LogTypeEnum.Err);
            }
        }
        public List<WcsTranCmdMd> LoadAllTranCmd(WcsTranCmdSearchMd searchMd)
        {
            try
            {
                string sqlQuery = string.Empty;
                if (!string.IsNullOrEmpty(searchMd.TaskNo))
                {
                    sqlQuery += $" AND TASK_NO = '{searchMd.TaskNo}'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdNo))
                {
                    sqlQuery += $" AND CMD_NO = '{searchMd.CmdNo}'";
                }
                if (!string.IsNullOrEmpty(searchMd.PalletCode))
                {
                    sqlQuery += $" AND PALLET_CODE like '%{searchMd.PalletCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.DevMsg))
                {
                    sqlQuery += $" AND TRAN_DEV_NO = '{searchMd.DevMsg}'";
                }
                if (!string.IsNullOrEmpty(searchMd.SlocCode))
                {
                    sqlQuery += $" AND SLOC_NO like '%{searchMd.SlocCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.ElocCode))
                {
                    sqlQuery += $" AND ELOC_NO like '%{searchMd.ElocCode}%'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdType))
                {
                    sqlQuery += $" AND CMD_TYPE = '{searchMd.CmdType}'";
                }
                if (!string.IsNullOrEmpty(searchMd.CmdStatus))
                {
                    sqlQuery += $" AND CMD_STATUS = '{searchMd.CmdStatus}'";
                }
                if (!string.IsNullOrEmpty(searchMd.TrayLoadedType))
                {
                    sqlQuery += $" AND TRAY_STATUS = '{searchMd.TrayLoadedType}'";
                }
                if (!string.IsNullOrEmpty(searchMd.StartExcuteTime))
                {
                    sqlQuery += $" AND EXECUTE_TIME >= '{searchMd.StartExcuteTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.EndExcuteTime))
                {
                    sqlQuery += $" AND EXECUTE_TIME <= '{searchMd.EndExcuteTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.StartFinishTime))
                {
                    sqlQuery += $" AND FINISH_TIME >= '{searchMd.StartFinishTime}'";
                }
                if (!string.IsNullOrEmpty(searchMd.EndFinishTime))
                {
                    sqlQuery += $" AND FINISH_TIME <= '{searchMd.EndFinishTime}'";
                }
                int pageStartIndex = (searchMd.PageNum - 1) * searchMd.EveryPageQty;
                int pageEndIndex = searchMd.PageNum * searchMd.EveryPageQty;
                string sql = $@"select * from VW_ZBK_WCS_TRAN_CMD where 1 = 1 {sqlQuery} 
                                ORDER BY A.UPDATE_TIME DESC,A.CREATE_TIME DESC
                                OFFSET {pageStartIndex} ROWS 
                                FETCH NEXT {searchMd.EveryPageQty} ROWS ONLY";
                DataTable dt = LeadDbHelperSQLServer.Query(sql).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    List<WcsTranCmdMd> retLst = new List<WcsTranCmdMd>();
                    foreach (DataRow item in dt.Rows)
                    {
                        WcsTranCmdMd retMd = ColumnToClassPropertyHelper.ColumnToClassProperty<WcsTranCmdMd>(dt.Columns, item);
                        retLst.Add(retMd);
                    }
                    return retLst;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog($"加载堆垛机所有作业指令数据发生异常！【{ex.Message}】", LogTypeEnum.Err);
                return null;
            }
        }
    }
}