HEGII.ft.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";
                        }

                        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.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";
                        }

                        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 = row["sapflbatchno"] + "";
                        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";
                        }

                        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 = row["sapflbatchno"] + "";
                        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");

            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"] + "";

                            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 = row["UDF2"] + "";
                            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"] + "";

                            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 = row["LPN"] + "";
                            header.UDF1 = row["UDF1"] + "";
                            header.UDF2 = row["UDF2"] + "";
                            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"] + "";

                            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 = row["LPN"] + "";
                            header.UDF1 = row["UDF1"] + "";
                            header.UDF2 = row["UDF2"] + "";
                            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
                  FROM TP_WMS_LOG WL
                 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.yyyymmdd >= :DATEBEGIN\n" +
                    "   and dl.yyyymmdd < :DATEEND\n" +
                     "   and (-1= :DATACODE or dl.datacode=:DATACODE )";//add xiacm 2022-10-12

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":DATEBEGIN",OracleDbType.Varchar2, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Varchar2, 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-17
                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 = string.Empty;
                INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);

                // 配置文件
                string SAP_NEW_INFO = ini.ReadIniData("SAP_NEW_INFO", "Factory");
                //一厂 add xiacm 2022-10-17
                if("5310".Equals(SAP_NEW_INFO))
				{
                     sqlString = "\n" +
                    "SELECT T1.DATACODE,\n" +
                    "       T1.Actualoutput,\n" +
                    "       T2.*\n" +
                    "  FROM (\n" +
                    "        -- 业务数据汇总\n" +
                    "        -- 10\n" +
                    "        SELECT '10' AS DATACODE,\n" +
                    "                SUM(DECODE(MHH.OPERATIONTYPE, 1, M.STANDARDGROUTINGSUM, 0)) AS Actualoutput\n" +
                    "          FROM (SELECT MH.GOODSID,\n" +
                    "                        MH.MOULDID,\n" +
                    "                        MH.OPERATIONTYPE,\n" +
                    "                        MH.GROUTINGNUM\n" +
                    "                   FROM TP_PC_MOULDCHANGEHISTORY MH\n" +
                    "                  WHERE MH.OPERATIONTYPE IN (1, 2, 3, 9, 11, -1)\n" +
                    "                    AND MH.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND MH.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT MH.GOODSIDAFTER,\n" +
                    "                        MH.MOULDID,\n" +
                    "                        0,\n" +
                    "                        MH.GROUTINGNUM\n" +
                    "                   FROM TP_PC_MOULDCHANGEHISTORY MH\n" +
                    "                  WHERE MH.OPERATIONTYPE = -1\n" +
                    "                    AND MH.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND MH.CREATETIME < :DATEEND) MHH\n" +
                    "         INNER JOIN TP_PC_MOULD M\n" +
                    "            ON M.MOULDID = MHH.MOULDID\n" +
                    "        UNION ALL\n" +
                    "        -- 20\n" +
                    "        SELECT '20' AS DATACODE,\n" +
                    "                COUNT(1) AS Businessoutput\n" +
                    "          FROM TP_PM_GROUTINGDAILYDETAIL GDD\n" +
                    "          LEFT JOIN TP_MST_GOODS G\n" +
                    "            ON GDD.GOODSID = G.GOODSID\n" +
                    "         WHERE GDD.CREATETIME >= :DATEBEGIN\n" +
                    "           AND GDD.CREATETIME < :DATEEND\n" +
                    "           AND GDD.GROUTINGFLAG = '1'\n" +
                    "           AND G.SCRAPSUMFLAG = '1'\n" +
                    "        UNION ALL\n" +
                    "        -- 30\n" +
                    "        SELECT '30' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (20,23,55)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (20,23,55)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 40\n" +
                    "        SELECT '40' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (4,22,45)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (4,22,45)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 50\n" +
                    "        SELECT '50' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (9)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (9)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 60\n" +
                    "        SELECT '60' AS DATACODE,\n" +
                    "                COUNT(1) AS Businessoutput\n" +
                    "          FROM TP_PM_FINISHEDPRODUCT GH\n" +
                    "         WHERE GH.FHTIME >= :DATEBEGIN\n" +
                    "           AND GH.FHTIME < :DATEEND) T1\n" +
                    "  LEFT JOIN (\n" +
                    "             -- 当日推送日志数据汇总\n" +
                    "             SELECT DATACODE AS DATACODETD,\n" +
                    "                     SUM(TO_NUMBER(OUTPUTNUM)) AS output,\n" +
                    "                     SUM(TO_NUMBER(SCRAPNUM)) AS scrapnum,\n" +
                    "                     SUM(TO_NUMBER(CLEANUPNUM)) AS cleannupnum,\n" +
                    "                     SUM(TO_NUMBER(RECOVERYNUM)) AS recoverynum,\n" +
                    "                     SUM(TO_NUMBER(REPAIRNUM)) AS repairnum\n" +
                    "               FROM TSAP_HEGII_WORKDATA_BG\n" +
                    "              WHERE LOGID IN (SELECT LOGID\n" +
                    "                                FROM TSAP_HEGII_DATALOG_BG T\n" +
                    "                               WHERE DATASTUTS = 'F'\n" +
                    "                                 AND EXECUTEDATEBEGIN >= :DATEBEGIN\n" +
                    "                                 AND EXECUTEDATEEND <= :DATEEND)\n" +
                    "              GROUP BY DATACODE\n" +
                    "              ORDER BY DATACODE) T2\n" +
                    "    ON T2.DATACODETD = T1.DATACODE\n" +
                     "  where (-1=:DATACODE OR T1.DATACODE= :DATACODE)\n" +
                    " ORDER BY T1.DATACODE";
                }
                //二厂 add xiacm 2022-10-17
                else if ("5320".Equals(SAP_NEW_INFO))
				{

                     sqlString = "\n" +
                    "SELECT T1.DATACODE,\n" +
                    "       T1.Actualoutput,\n" +
                    "       T2.*\n" +
                    "  FROM (\n" +
                    "        -- 业务数据汇总\n" +
                    "        -- 10\n" +
                    "        SELECT '10' AS DATACODE,\n" +
                    "                SUM(DECODE(MHH.OPERATIONTYPE, 1, M.STANDARDGROUTINGSUM, 0)) AS Actualoutput\n" +
                    "          FROM (SELECT MH.GOODSID,\n" +
                    "                        MH.MOULDID,\n" +
                    "                        MH.OPERATIONTYPE,\n" +
                    "                        MH.GROUTINGNUM\n" +
                    "                   FROM TP_PC_MOULDCHANGEHISTORY MH\n" +
                    "                  WHERE MH.OPERATIONTYPE IN (1, 2, 3, 9, 11, -1)\n" +
                    "                    AND MH.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND MH.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT MH.GOODSIDAFTER,\n" +
                    "                        MH.MOULDID,\n" +
                    "                        0,\n" +
                    "                        MH.GROUTINGNUM\n" +
                    "                   FROM TP_PC_MOULDCHANGEHISTORY MH\n" +
                    "                  WHERE MH.OPERATIONTYPE = -1\n" +
                    "                    AND MH.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND MH.CREATETIME < :DATEEND) MHH\n" +
                    "         INNER JOIN TP_PC_MOULD M\n" +
                    "            ON M.MOULDID = MHH.MOULDID\n" +
                    "        UNION ALL\n" +
                    "        -- 20\n" +
                    "        SELECT '20' AS DATACODE,\n" +
                    "                COUNT(1) AS Businessoutput\n" +
                    "          FROM TP_PM_GROUTINGDAILYDETAIL GDD\n" +
                    "          LEFT JOIN TP_MST_GOODS G\n" +
                    "            ON GDD.GOODSID = G.GOODSID\n" +
                    "         WHERE GDD.CREATETIME >= :DATEBEGIN\n" +
                    "           AND GDD.CREATETIME < :DATEEND\n" +
                    "           AND GDD.GROUTINGFLAG = '1'\n" +
                    "           AND G.SCRAPSUMFLAG = '1'\n" +
                    "        UNION ALL\n" +
                    "        -- 30\n" +
                    "        SELECT '30' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (34)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (34)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 40\n" +
                    "        SELECT '40' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (34)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (34)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 50\n" +
                    "        SELECT '50' AS DATACODE,\n" +
                    "                SUM(T1.CC) AS Businessoutput\n" +
                    "          FROM (SELECT 1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (12)\n" +
                    "                    AND P.CREATETIME >= :DATEBEGIN\n" +
                    "                    AND P.CREATETIME < :DATEEND\n" +
                    "                 UNION ALL\n" +
                    "                 SELECT -1 AS CC\n" +
                    "                   FROM TP_PM_PRODUCTIONDATA P\n" +
                    "                  WHERE P.PROCEDUREID IN (12)\n" +
                    "                    AND P.VALUEFLAG = '0'\n" +
                    "                    AND P.BACKOUTTIME >= :DATEBEGIN\n" +
                    "                    AND P.BACKOUTTIME < :DATEEND) T1\n" +
                    "        UNION ALL\n" +
                    "        -- 60\n" +
                    "        SELECT '60' AS DATACODE,\n" +
                    "                COUNT(1) AS Businessoutput\n" +
                    "          FROM TP_PM_FINISHEDPRODUCT GH\n" +
                    "         WHERE GH.FHTIME >= :DATEBEGIN\n" +
                    "           AND GH.FHTIME < :DATEEND) T1\n" +
                    "  LEFT JOIN (\n" +
                    "             -- 当日推送日志数据汇总\n" +
                    "             SELECT DATACODE AS DATACODETD,\n" +
                    "                     SUM(TO_NUMBER(OUTPUTNUM)) AS output,\n" +
                    "                     SUM(TO_NUMBER(SCRAPNUM)) AS scrapnum,\n" +
                    "                     SUM(TO_NUMBER(CLEANUPNUM)) AS cleannupnum,\n" +
                    "                     SUM(TO_NUMBER(RECOVERYNUM)) AS recoverynum,\n" +
                    "                     SUM(TO_NUMBER(REPAIRNUM)) AS repairnum\n" +
                    "               FROM TSAP_HEGII_WORKDATA_BG\n" +
                    "              WHERE LOGID IN (SELECT LOGID\n" +
                    "                                FROM TSAP_HEGII_DATALOG_BG T\n" +
                    "                               WHERE DATASTUTS = 'F'\n" +
                    "                                 AND EXECUTEDATEBEGIN >= :DATEBEGIN\n" +
                    "                                 AND EXECUTEDATEEND <= :DATEEND)\n" +
                    "              GROUP BY DATACODE\n" +
                    "              ORDER BY DATACODE) T2\n" +
                    "    ON T2.DATACODETD = T1.DATACODE\n" +
                    "  where (-1=:DATACODE OR T1.DATACODE= :DATACODE)\n" +
                    " 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;
            }

        }
    }
}