HEGII.gt.mes.wcf/DK.Service/WMSDataService/WMSDataLogic.cs

using System;
using System.Collections.Generic;
using System.Data;

using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Basics.Library;
using Dongke.IBOSS.PRD.Service.DataModels;
using Dongke.IBOSS.PRD.Service.WMSDataService.WMS_putBARCODEData;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.WMSDataService
{
    public class WMSDataLogic
    {
        /// <summary>
        /// 同步WMS系统条码
        /// </summary>
        /// <param name="fpRows"></param>
        /// <returns></returns>
        public static string PushWMS(DataTable fpData, DateTime now)
        {
            INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config);
            string endPointConfigurationName = ini.ReadIniData("WMS_INFO", "EndPointConfigurationName");
            string userName = ini.ReadIniData("WMS_INFO", "UserName");
            string password = ini.ReadIniData("WMS_INFO", "Password");
            string factory = ini.ReadIniData("WMS_INFO", "Factory");

            wmsResultInfo wmsResult = null;
            try
            {
                string outcode;
                string orderitem;

                // 主数据
                using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                {
                    // 登录
                    wmsSecurityInfo info = new wmsSecurityInfo();
                    info.username = userName;
                    info.password = password;

                    wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                    List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                    wmsBarCodeHeader header = null;

                    foreach (DataRow row in fpData.Rows)
                    {
                        outcode = row["outcode"] + "";
                        orderitem = row["orderitem"] + "";
                        if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                        {
                            orderitem = "000000";
                        }
                        else
                        {
                            orderitem = orderitem.PadLeft(6, '0');
                        }

                        header = new wmsBarCodeHeader();
                        header.SPS = factory;
                        header.SKU = row["sapcode"] + "";
                        header.SerialNO = outcode;
                        header.SerialNO1 = outcode.Substring(outcode.Length - 10, 10);
                        header.SerialNO2 = now.ToString("yyyyMMdd");
                        header.AddDay = now.ToString("yyyyMMdd");
                        header.AddTime = now.ToString("HHmmss");
                        header.CODEI = row["barcode"] + "";
                        header.UDF1 = row["ordercode"] + "";
                        header.UDF2 = orderitem;
                        header.UDF8 = row["securitycode"] + "";
                        header.AddWho = "HGMESDK";
                        headers.Add(header);
                    }

                    barcodeInfo.wmsBarCodeHeader = headers.ToArray();

                    wmsParamInfo wmsParam = new wmsParamInfo();
                    wmsParam.customerid = "FLUXWMSSOAP";
                    wmsParam.messageid = "ZTBARCODE";
                    wmsParam.stdno = "ZTBARCODE";
                    wmsParam.warehouseid = "WH01";
                    string ss = JsonHelper.ToJson(barcodeInfo);

                    // 参数
                    wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                    if (wmsResult.returnCode != "0000")
                    {
                        return "同步失败:" + wmsResult.returnDesc;
                    }
                }

                // 关系数据
                using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                {
                    // 登录
                    wmsSecurityInfo info = new wmsSecurityInfo();
                    info.username = userName;
                    info.password = password;

                    wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                    List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                    wmsBarCodeHeader header = null;
                    foreach (DataRow row in fpData.Rows)
                    {
                        outcode = row["outcode"] + "";
                        orderitem = row["orderitem"] + "";
                        if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                        {
                            orderitem = "000000";
                        }
                        else
                        {
                            orderitem = orderitem.PadLeft(6, '0');
                        }

                        header = new wmsBarCodeHeader();
                        header.SPS = factory;
                        header.SKU = row["sapcode"] + "";
                        header.SerialNO = outcode;
                        header.TraceID = outcode;
                        header.SerialNO1 = outcode.Substring(outcode.Length - 10, 10);
                        header.SerialNO2 = now.ToString("yyyyMMdd");
                        header.AddDay = now.ToString("yyyyMMdd");
                        header.AddTime = now.ToString("HHmmss");
                        header.LPN = "";
                        header.UDF1 = row["ordercode"] + "";
                        header.UDF2 = orderitem;
                        header.AddWho = "HGMESDK";
                        headers.Add(header);
                    }

                    barcodeInfo.wmsBarCodeHeader = headers.ToArray();
                    wmsParamInfo wmsParam = new wmsParamInfo();

                    wmsParam.customerid = "FLUXWMSSOAP";
                    wmsParam.messageid = "ZPPCODE";
                    wmsParam.stdno = "ZPPCODE";
                    wmsParam.warehouseid = "WH01";

                    // 参数
                    wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                    if (wmsResult.returnCode != "0000")
                    {
                        return "同步失败:" + wmsResult.returnDesc;
                    }
                }
            }
            catch (Exception ex)
            {
                return "同步失败:" + ex.Message;
            }
            return "同步成功";
        }

        /// <summary>
        /// 撤销同步WMS系统的条码
        /// </summary>
        /// <param name="fpRows"></param>
        /// <returns></returns>
        public static string BackPushWMS(DataTable fpData, DateTime now)
        {
            INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config);
            string endPointConfigurationName = ini.ReadIniData("WMS_INFO", "EndPointConfigurationName");
            string userName = ini.ReadIniData("WMS_INFO", "UserName");
            string password = ini.ReadIniData("WMS_INFO", "Password");
            string factory = ini.ReadIniData("WMS_INFO", "Factory");

            wmsResultInfo wmsResult = null;
            try
            {
                string outcode;
                string orderitem;

                // 关系数据
                using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                {
                    // 登录
                    wmsSecurityInfo info = new wmsSecurityInfo();
                    info.username = userName;
                    info.password = password;

                    wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                    List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                    wmsBarCodeHeader header = null;
                    foreach (DataRow row in fpData.Rows)
                    {
                        outcode = row["outcode"] + "";
                        orderitem = row["orderitem"] + "";
                        if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                        {
                            orderitem = "000000";
                        }
                        else
                        {
                            orderitem = orderitem.PadLeft(6, '0');
                        }

                        header = new wmsBarCodeHeader();
                        header.SPS = factory;
                        header.SKU = row["sapcode"] + "";
                        header.SerialNO = outcode;
                        header.TraceID = outcode;
                        header.SerialNO1 = outcode.Substring(outcode.Length - 10, 10);
                        header.SerialNO2 = now.ToString("yyyyMMdd");
                        header.AddDay = now.ToString("yyyyMMdd");
                        header.AddTime = now.ToString("HHmmss");
                        header.LPN = "";
                        header.UDF1 = row["ordercode"] + "";
                        header.UDF2 = orderitem;
                        header.UDF5 = "X";
                        header.AddWho = "HGMESDK";
                        headers.Add(header);
                    }

                    barcodeInfo.wmsBarCodeHeader = headers.ToArray();
                    wmsParamInfo wmsParam = new wmsParamInfo();

                    wmsParam.customerid = "FLUXWMSSOAP";
                    wmsParam.messageid = "ZPPCODE";
                    wmsParam.stdno = "ZPPCODE";
                    wmsParam.warehouseid = "WH01";

                    // 参数
                    wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                    if (wmsResult.returnCode != "0000")
                    {
                        return "同步失败:" + wmsResult.returnDesc;
                    }
                }
            }
            catch (Exception ex)
            {
                return "同步失败:" + ex.Message;
            }
            return "同步成功";
        }

        /// <summary>
        /// 手动同步WMS系统条码
        /// </summary>
        /// <param name="fpRows"></param>
        /// <returns></returns>
        public static ServiceResultEntity SyncWMSDataLog(ClientRequestEntity cre, SUserInfo userInfo)
        {
            ServiceResultEntity sre = new ServiceResultEntity();
            DataTable dtBarcode = cre.Data.Tables[0];

            INIUtility ini = INIUtility.Instance(INIUtility.IniFile.Config);
            string endPointConfigurationName = ini.ReadIniData("WMS_INFO", "EndPointConfigurationName");
            string userName = ini.ReadIniData("WMS_INFO", "UserName");
            string password = ini.ReadIniData("WMS_INFO", "Password");
            string factory = ini.ReadIniData("WMS_INFO", "Factory");
            string orderitem = string.Empty;

            wmsResultInfo wmsResult = null;
            string serialNO;
            DateTime now;
            try
            {
                if ("1".Equals(cre.Properties["LogType"] + ""))
                {
                    // 主数据
                    using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                    {
                        // 登录
                        wmsSecurityInfo info = new wmsSecurityInfo();
                        info.username = userName;
                        info.password = password;

                        wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                        List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                        wmsBarCodeHeader header = null;

                        foreach (DataRow row in dtBarcode.Rows)
                        {
                            now = Convert.ToDateTime(row["CreateTime"]);
                            serialNO = row["SerialNO"] + "";

                            orderitem = row["UDF2"] + "";
                            if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                            {
                                orderitem = "000000";
                            }
                            else
                            {
                                orderitem = orderitem.PadLeft(6, '0');
                            }

                            header = new wmsBarCodeHeader();
                            header.SPS = factory;
                            header.SKU = row["SKU"] + "";
                            header.SerialNO = serialNO;
                            header.SerialNO1 = serialNO.Substring(serialNO.Length - 10, 10);
                            header.SerialNO2 = now.ToString("yyyyMMdd");
                            header.AddDay = now.ToString("yyyyMMdd");
                            header.AddTime = now.ToString("HHmmss");
                            header.CODEI = row["CODEI"] + "";
                            header.UDF1 = row["UDF1"] + "";
                            header.UDF2 = orderitem;
                            header.UDF8 = row["securitycode"] + "";
                            header.AddWho = "HGMESDK";
                            headers.Add(header);
                        }

                        barcodeInfo.wmsBarCodeHeader = headers.ToArray();

                        wmsParamInfo wmsParam = new wmsParamInfo();
                        wmsParam.customerid = "FLUXWMSSOAP";
                        wmsParam.messageid = "ZTBARCODE";
                        wmsParam.stdno = "ZTBARCODE";
                        wmsParam.warehouseid = "WH01";

                        // 参数
                        wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                        if (wmsResult.returnCode != "0000")
                        {
                            sre.OtherStatus = -1;
                            sre.Message = "同步失败:" + wmsResult.returnDesc;
                            return sre;
                        }
                    }

                    // 关系数据
                    using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                    {
                        // 登录
                        wmsSecurityInfo info = new wmsSecurityInfo();
                        info.username = userName;
                        info.password = password;

                        wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                        List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                        wmsBarCodeHeader header = null;
                        foreach (DataRow row in dtBarcode.Rows)
                        {
                            now = Convert.ToDateTime(row["CreateTime"]);
                            serialNO = row["SerialNO"] + "";

                            orderitem = row["UDF2"] + "";
                            if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                            {
                                orderitem = "000000";
                            }
                            else
                            {
                                orderitem = orderitem.PadLeft(6, '0');
                            }

                            header = new wmsBarCodeHeader();
                            header.SPS = factory;
                            header.SKU = row["SKU"] + "";
                            header.SerialNO = serialNO;
                            header.TraceID = serialNO;
                            header.SerialNO1 = serialNO.Substring(serialNO.Length - 10, 10);
                            header.SerialNO2 = now.ToString("yyyyMMdd");
                            header.AddDay = now.ToString("yyyyMMdd");
                            header.AddTime = now.ToString("HHmmss");
                            header.LPN = "";
                            header.UDF1 = row["UDF1"] + "";
                            header.UDF2 = orderitem;
                            header.AddWho = "HGMESDK";
                            headers.Add(header);
                        }

                        barcodeInfo.wmsBarCodeHeader = headers.ToArray();
                        wmsParamInfo wmsParam = new wmsParamInfo();

                        wmsParam.customerid = "FLUXWMSSOAP";
                        wmsParam.messageid = "ZPPCODE";
                        wmsParam.stdno = "ZPPCODE";
                        wmsParam.warehouseid = "WH01";

                        // 参数
                        wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                        if (wmsResult.returnCode != "0000")
                        {
                            sre.OtherStatus = -1;
                            sre.Message = "同步失败:" + wmsResult.returnDesc;
                            return sre;
                        }
                    }
                }
                else
                {
                    // 关系数据
                    using (ERPWSService2Client wmsClient = new ERPWSService2Client(endPointConfigurationName))
                    {
                        // 登录
                        wmsSecurityInfo info = new wmsSecurityInfo();
                        info.username = userName;
                        info.password = password;

                        wmsBARCODEInfo barcodeInfo = new wmsBARCODEInfo();

                        List<wmsBarCodeHeader> headers = new List<wmsBarCodeHeader>();
                        wmsBarCodeHeader header = null;
                        foreach (DataRow row in dtBarcode.Rows)
                        {
                            now = Convert.ToDateTime(row["CreateTime"]);
                            serialNO = row["SerialNO"] + "";

                            orderitem = row["UDF2"] + "";
                            if (string.IsNullOrEmpty(orderitem) || "0".Equals(orderitem))
                            {
                                orderitem = "000000";
                            }
                            else
                            {
                                orderitem = orderitem.PadLeft(6, '0');
                            }

                            header = new wmsBarCodeHeader();
                            header.SPS = factory;
                            header.SKU = row["SKU"] + "";
                            header.SerialNO = serialNO;
                            header.TraceID = serialNO;
                            header.SerialNO1 = serialNO.Substring(serialNO.Length - 10, 10);
                            header.SerialNO2 = now.ToString("yyyyMMdd");
                            header.AddDay = now.ToString("yyyyMMdd");
                            header.AddTime = now.ToString("HHmmss");
                            header.LPN = "";
                            header.UDF1 = row["UDF1"] + "";
                            header.UDF2 = orderitem;
                            header.UDF5 = "X";
                            header.AddWho = "HGMESDK";
                            headers.Add(header);
                        }

                        barcodeInfo.wmsBarCodeHeader = headers.ToArray();
                        wmsParamInfo wmsParam = new wmsParamInfo();

                        wmsParam.customerid = "FLUXWMSSOAP";
                        wmsParam.messageid = "ZPPCODE";
                        wmsParam.stdno = "ZPPCODE";
                        wmsParam.warehouseid = "WH01";

                        // 参数
                        wmsResult = wmsClient.putBARCODEData(info, barcodeInfo, wmsParam);

                        if (wmsResult.returnCode != "0000")
                        {
                            sre.OtherStatus = -1;
                            sre.Message = "同步失败:" + wmsResult.returnDesc;
                            return sre;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                sre.OtherStatus = -1;
                sre.Message = "同步失败:" + ex.Message;
                return sre;
            }

            IDBTransaction oracleTrConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
            string sql = @"
            UPDATE TP_WMS_LOG
               SET RETURNDESC   = '同步成功',
                   UPDATEUSERID = :USERID
             WHERE ACCOUNTID = :ACCOUNTID
               AND LOGTYPE = :LOGTYPE
               AND CODEI = :CODEI ";
            OracleParameter[] Paras;

            foreach (DataRow row in dtBarcode.Rows)
            {
                Paras = new OracleParameter[]
                {
                    new OracleParameter(":USERID",OracleDbType.Int32,userInfo.UserID,ParameterDirection.Input),
                    new OracleParameter(":ACCOUNTID",OracleDbType.Int32,userInfo.AccountID,ParameterDirection.Input),
                    new OracleParameter(":LOGTYPE",OracleDbType.Char, cre.Properties["LogType"] + "",ParameterDirection.Input),
                    new OracleParameter(":CODEI",OracleDbType.Varchar2, row["CODEI"], ParameterDirection.Input),
                };
                oracleTrConn.ExecuteNonQuery(sql, Paras);
            }

            oracleTrConn.Commit();
            oracleTrConn.Disconnect();

            sre.OtherStatus = 1;
            sre.Message = "同步成功";
            return sre;
        }

        /// <summary>
		/// 查询同步日志
		/// </summary>
		/// <param name="cre"></param>
		/// <param name="userInfo"></param>
		/// <returns></returns>
		public static ServiceResultEntity GetWMSDataLog(ClientRequestEntity cre, SUserInfo userInfo)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString = @"
                SELECT 0 AS SEL,
                       WL.LOGID,
                       WL.LOGTYPE,
                       DECODE(WL.LOGTYPE, '1', '交接', '撤销') AS LOGTYPENAME,
                       WL.SKU,
                       WL.SERIALNO,
                       WL.ADDDAY,
                       WL.ADDTIME,
                       WL.CODEI,
                       NVL(WL.UDF1, 'hegii') AS UDF1,
                       WL.UDF2,
                       WL.LPN,
                       WL.RETURNDESC,
                       WL.CREATETIME,
                       GDD.SECURITYCODE
                  FROM TP_WMS_LOG WL
                 INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD
                    ON GDD.BARCODE = WL.CODEI
                 WHERE WL.VALUEFLAG = '1'
                   AND WL.ACCOUNTID = :ACCOUNTID
                   AND WL.CREATETIME >= :CREATETIMEBEGIN
                   AND WL.CREATETIME < :CREATETIMEEND ";

                List<OracleParameter> paras = new List<OracleParameter>();
                paras.Add(new OracleParameter(":ACCOUNTID", OracleDbType.Int32, userInfo.AccountID, ParameterDirection.Input));
                paras.Add(new OracleParameter(":CREATETIMEBEGIN", OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input));
                paras.Add(new OracleParameter(":CREATETIMEEND", OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input));

                if (!string.IsNullOrEmpty(cre.Properties["LogType"] + ""))
                {
                    sqlString += " AND WL.LOGTYPE = :LOGTYPE\n";
                    paras.Add(new OracleParameter(":LOGTYPE", OracleDbType.NVarchar2, cre.Properties["LogType"], ParameterDirection.Input));
                }

                if (!string.IsNullOrEmpty(cre.Properties["ReturnDesc"] + ""))
                {
                    sqlString += " AND WL.RETURNDESC LIKE :RETURNDESC\n";
                    paras.Add(new OracleParameter(":RETURNDESC", OracleDbType.NVarchar2, "%" + cre.Properties["ReturnDesc"] + "%", ParameterDirection.Input));
                }

                sqlString += " order by WL.CREATETIME";

                sre.Data = oracleConn.GetSqlResultToDs(sqlString, paras.ToArray());
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询同步日志
        /// </summary>
        /// <param name="cre"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetDataLog_BG(ClientRequestEntity cre)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString =
                "select dl.logid\n" +
                "      ,dl.logtype\n" +
                "      ,dl.begintime\n" +
                "      ,dl.endtime\n" +
                "      ,dl.yyyymmdd\n" +
                "      ,dl.workcode\n" +
                "      ,dl.datacode\n" +
                "      ,dc.datacodename\n" +
                "      ,dl.datastuts\n" +
                "      ,dl.datamsg\n" +
                "      ,dl.datalogid\n" +
                "      ,dl.executedatebegin\n" +
                "      ,dl.executedateend\n" +
                "      ,u.usercode synusercode\n" +
                "  from TSAP_HEGII_DATALOG_BG dl\n" +
                " inner join tsap_hegii_datacode dc\n" +
                "    on dc.datacode = dl.datacode\n" +
                "  left join tp_mst_user u\n" +
                "    on u.userid = dl.createuserid\n" +
                " where dl.logtype IN('2','3')\n" +
                "   and dl.executedatebegin >= :DATEBEGIN\n" +
                "   and dl.executedateend < :DATEEND\n" +
                "   and (-1= :DATACODE or dl.datacode=:DATACODE )";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":DATEBEGIN",OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input),
                    new OracleParameter(":DATACODE",OracleDbType.Varchar2, cre.Properties["datacode"], ParameterDirection.Input),
                };

                sqlString += "ORDER BY dl.logid DESC\n";

                sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询同步明细
        /// </summary>
        /// <param name="logid"></param>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetWorkData_BG(ClientRequestEntity cre)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                int logid = Convert.ToInt32(cre.Request);
                string sqlString = "\n" +
                "select wd.yyyymmdd\n" +
                "      ,wd.workcode\n" +
                "      ,wd.datacode\n" +
                "      ,dc.datacodename\n" +
                "      ,wd.goodscode\n" +
                "      ,wd.sapcode\n" +
                "      ,wd.usercode\n" +
                "      ,wd.ordercode\n" +
                "      ,wd.orderitem\n" +
                "      ,to_number(wd.outputnum) outputnum\n" +
                "      ,to_number(wd.scrapnum) scrapnum\n" +
                "      ,to_number(wd.cleanupnum) cleanupnum\n" +
                "      ,to_number(wd.recoverynum) recoverynum\n" +
                "      ,to_number(wd.repairnum) repairnum\n" +
                "      ,wd.createtime\n" +
                "      ,wd.ztype\n" +
                "      ,wd.zmsg\n" +
                "      ,wd.ztime\n" +
                "      ,wd.testmouldflag\n" +
                "      ,wd.zscs\n" +
                "      ,wd.logid\n" +
                "  from tsap_hegii_workdata_BG wd\n" +
                " inner join TSAP_HEGII_DATALOG_BG dl\n" +
                "    on dl.logid = wd.logid\n" +
                " inner join tsap_hegii_datacode dc\n" +
                "    on dc.datacode = wd.datacode\n";
                //update xiacm 2022-10-12
                if (logid > 0)
                {
                    sqlString += " where wd.logid = :logid \n";
                }
                else
                {
                    sqlString += " where dl.executedatebegin >= :DATEBEGIN and dl.executedateend < :DATEEND";
                    sqlString += " and (-1= :DATACODE OR wd.datacode = :DATACODE)";
                }
                sqlString += " order by wd.goodscode,wd.sapcode,wd.usercode,wd.ordercode,wd.outputnum \n";
                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input),
                    new OracleParameter(":DATEBEGIN",OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input),
                    new OracleParameter(":DATACODE",OracleDbType.Varchar2, cre.Properties["datacode"], ParameterDirection.Input),
                };
                sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 报工汇总 add xiacm 2022-10-12
        /// </summary>
        /// <param name="cre"></param>
        /// <returns></returns>
        public static ServiceResultEntity GetDataLogTotal_BG(ClientRequestEntity cre)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            ServiceResultEntity sre = new ServiceResultEntity();

            try
            {
                string sqlString = @"
                SELECT T1.DATACODE,
                       T1.ACTUALOUTPUT,
                       T2.OUTPUT,
                       T2.SCRAPNUM,
                       T2.CLEANNUPNUM,
                       T2.RECOVERYNUM,
                       T2.REPAIRNUM,
                       T3.DRCXFDRJJDCCP,
                       T4.DRJJBQTRCXDCCP,
                       T1.ACTUALOUTPUT + NVL(T4.DRJJBQTRCXDCCP, 0) - NVL(T3.DRCXFDRJJDCCP, 0) -
                       (T2.OUTPUT + T2.RECOVERYNUM) AS DIFFER
                  FROM (
                        -- 业务数据汇总
                        -- 10
                        SELECT '10' AS DATACODE,
                                SUM(DECODE(MHH.OPERATIONTYPE, 1, M.STANDARDGROUTINGSUM, 0)) AS ACTUALOUTPUT
                          FROM (SELECT MH.GOODSID,
                                        MH.MOULDID,
                                        MH.OPERATIONTYPE,
                                        MH.GROUTINGNUM
                                   FROM TP_PC_MOULDCHANGEHISTORY MH
                                  WHERE MH.OPERATIONTYPE IN (1, 2, 3, 9, 11, -1)
                                    AND MH.CREATETIME >= :DATEBEGIN
                                    AND MH.CREATETIME < :DATEEND
                                 UNION ALL
                                 SELECT MH.GOODSIDAFTER,
                                        MH.MOULDID,
                                        0,
                                        MH.GROUTINGNUM
                                   FROM TP_PC_MOULDCHANGEHISTORY MH
                                  WHERE MH.OPERATIONTYPE = -1
                                    AND MH.CREATETIME >= :DATEBEGIN
                                    AND MH.CREATETIME < :DATEEND) MHH
                         INNER JOIN TP_PC_MOULD M
                            ON M.MOULDID = MHH.MOULDID
                        UNION ALL
                        -- 20
                        SELECT '20' AS 数据节点,
                                SUM(DECODE(GH.DATATYPE, 1, 1, -1)) 业务数据产量
                          FROM TP_PM_GOODSCHANGEHISTORY GH
                         INNER JOIN TP_MST_GOODS G
                            ON G.GOODSID = GH.GOODSID
                         WHERE GH.CREATETIME >= :DATEBEGIN
                           AND GH.CREATETIME < :DATEEND
                           AND GH.DATATYPE IN (1, 2)
                           AND G.SCRAPSUMFLAG = '1'
                        UNION ALL
                        -- 30
                        SELECT '30' AS DATACODE,
                                SUM(T1.CC) AS BUSINESSOUTPUT
                          FROM (SELECT 1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (4, 35)
                                    AND P.CREATETIME >= :DATEBEGIN
                                    AND P.CREATETIME < :DATEEND
                                 UNION ALL
                                 SELECT -1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (4, 35)
                                    AND P.VALUEFLAG = '0'
                                    AND P.BACKOUTTIME >= :DATEBEGIN
                                    AND P.BACKOUTTIME < :DATEEND) T1
                        UNION ALL
                        -- 40
                        SELECT '40' AS DATACODE,
                                SUM(T1.CC) AS BUSINESSOUTPUT
                          FROM (SELECT 1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (5, 39)
                                    AND P.CREATETIME >= :DATEBEGIN
                                    AND P.CREATETIME < :DATEEND
                                 UNION ALL
                                 SELECT -1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (5, 39)
                                    AND P.VALUEFLAG = '0'
                                    AND P.BACKOUTTIME >= :DATEBEGIN
                                    AND P.BACKOUTTIME < :DATEEND) T1
                        UNION ALL
                        -- 50
                        SELECT '50' AS DATACODE,
                                SUM(T1.CC) AS BUSINESSOUTPUT
                          FROM (SELECT 1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (9, 48)
                                    AND P.CREATETIME >= :DATEBEGIN
                                    AND P.CREATETIME < :DATEEND
                                 UNION ALL
                                 SELECT -1 AS CC
                                   FROM TP_PM_PRODUCTIONDATA P
                                  WHERE P.PROCEDUREID IN (9, 48)
                                    AND P.VALUEFLAG = '0'
                                    AND P.BACKOUTTIME >= :DATEBEGIN
                                    AND P.BACKOUTTIME < :DATEEND) T1
                        UNION ALL
                        -- 60
                        SELECT '60' AS DATACODE,
                                COUNT(1) AS BUSINESSOUTPUT
                          FROM TP_PM_FINISHEDPRODUCT GH
                         WHERE GH.FHTIME >= :DATEBEGIN
                           AND GH.FHTIME < :DATEEND) T1
                  LEFT JOIN (
                             -- 当日推送日志数据汇总
                             SELECT DATACODE AS DATACODETD,
                                     SUM(TO_NUMBER(OUTPUTNUM)) AS OUTPUT,
                                     SUM(TO_NUMBER(SCRAPNUM)) AS SCRAPNUM,
                                     SUM(TO_NUMBER(CLEANUPNUM)) AS CLEANNUPNUM,
                                     SUM(TO_NUMBER(RECOVERYNUM)) AS RECOVERYNUM,
                                     SUM(TO_NUMBER(REPAIRNUM)) AS REPAIRNUM
                               FROM TSAP_HEGII_WORKDATA_BG
                              WHERE LOGID IN (SELECT LOGID
                                                FROM TSAP_HEGII_DATALOG_BG T
                                               WHERE DATASTUTS = 'S'
                                                 AND EXECUTEDATEBEGIN >= :DATEBEGIN
                                                 AND EXECUTEDATEEND <= :DATEEND)
                              GROUP BY DATACODE
                              ORDER BY DATACODE) T2
                    ON T2.DATACODETD = T1.DATACODE

                     LEFT JOIN (
                             -- 当日撤销非当日交接的产成品
                             SELECT '60' AS DATACODE,
                                     COUNT(WL.CODEI) AS DRCXFDRJJDCCP
                               FROM TP_WMS_LOG WL
                              WHERE LOGTYPE = 2
                                AND EXISTS (SELECT 1
                                       FROM TP_WMS_LOG WL1
                                      WHERE WL1.CODEI = WL.CODEI
                                        AND WL1.LOGTYPE = 1
                                        AND TRUNC(WL1.CREATETIME) < TRUNC(WL.CREATETIME))
                                AND WL.CREATETIME >= :DATEBEGIN
                                AND WL.CREATETIME < :DATEEND) T3
                    ON T3.DATACODE = T1.DATACODE
                  LEFT JOIN (
                             -- 当日交接被其他日撤销的产成品
                             SELECT '60' AS DATACODE,
                                     COUNT(WL.CODEI) AS DRJJBQTRCXDCCP
                               FROM TP_WMS_LOG WL
                              WHERE LOGTYPE = 1
                                AND EXISTS (SELECT 1
                                       FROM TP_WMS_LOG WL1
                                      WHERE WL1.CODEI = WL.CODEI
                                        AND WL1.LOGTYPE = 2
                                        AND TRUNC(WL1.CREATETIME) > TRUNC(WL.CREATETIME))
                                AND WL.CREATETIME >= :DATEBEGIN
                                AND WL.CREATETIME < :DATEEND) T4
                    ON T4.DATACODE = T1.DATACODE
                 WHERE (-1 = :DATACODE OR T1.DATACODE = :DATACODE)
                 ORDER BY T1.DATACODE ";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":DATEBEGIN",OracleDbType.Date, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Date, cre.Properties["dateend"], ParameterDirection.Input),
                    new OracleParameter(":DATACODE",OracleDbType.Varchar2, cre.Properties["datacode"], ParameterDirection.Input),
                };

                sre.Data = oracleConn.GetSqlResultToDs(sqlString, oracleParameter);
                return sre;

            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
    }
}