HEGII.ss.mes.wcf/DK.Service/SAPDataService/SAPDataLogicPartial.cs


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Net;
using System.Reflection;
using System.Text;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Dongke.IBOSS.PRD.Basics.DataAccess;
using Dongke.IBOSS.PRD.Basics.Library;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Newtonsoft.Json.Linq;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Service.SAPHegiiDataService
{
    public partial class SAPDataLogic
    {
        #region 跨车间作业
        /// <summary>
        /// 同步SAP数据（自动）
        /// </summary>
        /// <param name="date"></param>
        public static void CrossWorkshopToSAP(DateTime date, DateTime ndate)
        {
            IDBTransaction oracleConn = null;
            ServiceResultEntity sre = new ServiceResultEntity();
            int logid = 0;
            string message = string.Empty;
            string sqlString = string.Empty;
            try
            {
                #region 生成日志
                OracleParameter[] paras = new OracleParameter[]
                    {
                        new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input),
                        new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output),
                        new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output)
                    };

                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                DataSet ds = oracleConn.ExecStoredProcedure("pro_sap_hegii_workdata_kcjzy", paras);
                int.TryParse(paras[1].Value + "", out logid);
                message = paras[2].Value + "";
                oracleConn.Commit();
                #endregion

                #region 同步SAP
                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                //sqlString = "select workcode from tp_mst_account where rownum = 1";
                //string workcode = oracleConn.GetSqlResultToStr(sqlString);
                //workcode = "5000";

                sqlString = "SELECT\n" +
                            "   to_char(B.EXECUTEDATEBEGIN,'yyyymmddhh24miss') AS ZYWKS,\n" +
                            "	to_char(B.EXECUTEDATEEND,'yyyymmddhh24miss') AS ZYWJS,\n" +
                            "	to_char(SYSDATE,'yyyymmddhh24miss') AS ZMONT,\n" +
                            "	A.WORKCODE AS WERKS,\n" +
                            "	A.SAPCODE AS MATNR,\n" +
                            "	A.GOODSCODE AS GROES,\n" +
                            "	A.WORKSHOP AS ZSCCJ,\n" +
                            "	A.WORKSHOP AS ZSSCJ,\n" +
                            "	A.DATACODE AS ZJDNU,\n" +
                            "	A.ITEM AS ZZYLX,\n" +
                            "	A.NUM AS MENGE,\n" +
                            //"	A.ZSCS,\n" +
                            "	'T' AS ZSCS,\n" +
                            "   CASE WHEN A.TESTMOULDFLAG = 1 THEN 'Y' ELSE 'C' END AS ZSCMS, \n" +
                            "	'' AS ZTYPE1, \n" +
                            "	'' AS ZMSG1 \n" +
                            "FROM\n" +
                            "	TSAP_HEGII_WORKDATA_KCJZY A\n" +
                            "	INNER JOIN TSAP_HEGII_DATALOG_KCJZY B ON B.LOGID = A.LOGID\n" +
                            "WHERE\n" +
                            "	A.LOGID = :logid";
                paras = new OracleParameter[]
                {
                    new OracleParameter(":logid", OracleDbType.Int32, logid, ParameterDirection.Input),
                };
                DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras);

                sqlString = "select SAP_INI_BG from TP_SYS_SAPCONFIG";
                string SAP_ING_NEW = oracleConn.GetSqlResultToStr(sqlString);

                if (workData != null && workData.Rows.Count > 0 && SAP_ING_NEW == "1")
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<CrossWorkShopToSAP>.ConvertToModel(workData)) + "}}";
                    INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
                    string url033 = ini.ReadIniData("SAP_NEW_INFO", "Url033");
                    //url033 = "Url033=http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM033"
                    string result = PostData(url033, postString, "POST");
                    string ztype = JObject.Parse(result)["ZTYPE"].ToString();
                    string msg = JObject.Parse(result)["ZMSG"].ToString();
                    sqlString = "update TSAP_HEGII_DATALOG_KCJZY t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid";
                    paras = new OracleParameter[]
                    {
                        new OracleParameter(":logid", OracleDbType.Varchar2, logid, ParameterDirection.Input),
                        new OracleParameter(":DataStuts", OracleDbType.Varchar2, ztype, ParameterDirection.Input),
                        new OracleParameter(":msg", OracleDbType.Varchar2, msg, ParameterDirection.Input),
                    };
                    oracleConn.ExecuteNonQuery(sqlString, paras);
                    oracleConn.Commit();
                }
                #endregion
            }
            catch (Exception ex)
            {
                OutputLog.TraceLog(LogPriority.Error,
                        "CrossWorkshopToSAP",
                        "跨车间作业量" + date.ToString("yyyy-MM-dd HH:mm:ss"),
                        ex.ToString(),
                        LocalPath.LogExePath + "SAP_HEGII\\Error_");
            }
        }

        public static void CrossWorkshopToSAP_test(DateTime date, DateTime ndate)
        {
            IDBTransaction oracleConn = null;
            ServiceResultEntity sre = new ServiceResultEntity();
            int logid = 0;
            string message = string.Empty;
            string sqlString = string.Empty;
            try
            {
                #region 同步SAP
                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                //sqlString = "select workcode from tp_mst_account where rownum = 1";
                //string workcode = oracleConn.GetSqlResultToStr(sqlString);
                //workcode = "5000";
                sqlString = @"SELECT
                    to_char(:v_datebegin, 'yyyymmddhh24miss') AS ZYWKS,
                    to_char(:in_dateend, 'yyyymmddhh24miss') AS ZYWJS,
                    to_char(SYSDATE,'yyyymmddhh24miss') AS ZMONT,
					'5000' AS WERKS,
                    MATERIALCODE AS MATNR,
					GOODSCODE AS GROES,
					to_char(WORKSHOP) AS ZSCCJ,
					to_char(DATACODE) AS ZJDNU,
                    to_char(ITEM)  AS ZZYLX,
                    to_char(count( * )) AS MENGE,
                    'T' AS ZSCS,
					 CASE WHEN TESTMOULDFLAG = 1 THEN 'Y' ELSE 'C' END AS ZSCMS,
				    '' AS ZTYPE1,
                    '' AS ZMSG1
                FROM
                    (--产量

                            SELECT

                                GDD.MATERIALCODE,
                                gdd.goodscode,
                                HGDI.WORKSHOP,
                                HGDI.DATACODE,
                                1 AS ITEM,
                                GDD.TESTMOULDFLAG,
                              G.GOODS_LINE_CODE AS ZSCS

                            FROM

                                TP_PM_PRODUCTIONDATA PD

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON PD.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 1

                                AND HGDI.ITEMID = PD.PROCEDUREID

                                AND(HGDI.ITEMID <> 104 OR(PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1'))

                            WHERE

                                PD.VALUEFLAG = 1

                                AND PD.CREATETIME >= :v_datebegin

                                AND PD.CREATETIME < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
                                )

                                AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1

                        UNION ALL

                          --产量撤销

                            SELECT

                                GDD.MATERIALCODE,
								GDD.goodscode,
								HGDI.WORKSHOP,
								HGDI.DATACODE AS DATACODE,
								2 AS ITEM,
                                GDD.TESTMOULDFLAG,
								G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_PRODUCTIONDATA PD

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON PD.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMID = PD.PROCEDUREID

                                AND(HGDI.ITEMID <> 104 OR(PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1'))

                            WHERE

                                PD.VALUEFLAG = 0

                                AND PD.BACKOUTTIME >= :v_datebegin

                                AND PD.BACKOUTTIME < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1-- 工序报损
                          UNION ALL
                          SELECT

                                GDD.MATERIALCODE,
								GDD.goodscode,
								HGDI.WORKSHOP,
								HGDI.DATACODE AS DATACODE,
								3 AS ITEM,
                                GDD.TESTMOULDFLAG,
							  G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_SCRAPPRODUCT SP

                                INNER JOIN tp_pm_productiondata pd ON pd.productiondataid = sp.productiondataid

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON PD.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 2

                                AND HGDI.ITEMID = PD.PROCEDUREID

                                AND(HGDI.ITEMID <> 104 OR(PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1'))

                            WHERE

                                SP.AUDITSTATUS = 1

                                AND SP.AUDITDATE >= :v_datebegin

                                AND SP.AUDITDATE < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1-- 工序报损撤销
                          UNION ALL
                          SELECT

                                GDD.MATERIALCODE,
								GDD.goodscode,
								HGDI.WORKSHOP,
								HGDI.DATACODE AS DATACODE,
								4 AS ITEM,
                                GDD.TESTMOULDFLAG,
							  G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_SCRAPPRODUCT SP

                                INNER JOIN tp_pm_productiondata pd ON pd.productiondataid = sp.productiondataid

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON PD.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 2

                                AND HGDI.ITEMID = PD.PROCEDUREID

                                AND(HGDI.ITEMID <> 104 OR(PD.ISREFIRE = '0' AND PD.CHECKFLAG = '1'))

                            WHERE

                                SP.AUDITSTATUS = 1

                                AND SP.VALUEFLAG = '0'

                                AND SP.BACKOUTTIME >= :v_datebegin

                                AND SP.BACKOUTTIME < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1-- 盘点清除
                          UNION ALL
                          SELECT

                                GDD.MATERIALCODE,
								GDD.GOODSCODE,
								HGDI.WORKSHOP,
								HGDI.DATACODE,
								5 AS ITEM,
                                GDD.TESTMOULDFLAG ,
								G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_GOODSCHANGEHISTORY GH

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = GH.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON GH.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 2

                                AND HGDI.ITEMID = GH.OTHERID

                            WHERE

                                GH.CREATETIME >= :v_datebegin

                                AND GH.CREATETIME < :in_dateend

                                AND GH.DATATYPE IN( 11, 12 ) 
								AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1-- 干补
                          UNION ALL
                          SELECT

                                GDD.MATERIALCODE,
								GDD.GOODSCODE,
								HGDI.WORKSHOP,
								HGDI.DATACODE,
								6 AS ITEM,
                                GDD.TESTMOULDFLAG,
							  G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_SCRAPPRODUCT SP

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON SP.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 2

                                AND HGDI.ITEMID = SP.PROCEDUREID

                            WHERE

                                SP.AUDITSTATUS = 1

                                AND SP.VALUEFLAG = '1'

                                AND SP.GOODSLEVELTYPEID = 9

                                AND SP.SPECIALREPAIRTIME >= :v_datebegin

                                AND SP.SPECIALREPAIRTIME < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1-- 回收
                          UNION ALL
                          SELECT

                                GDD.MATERIALCODE,
								GDD.GOODSCODE,
								HGDI.WORKSHOP,
								HGDI.DATACODE,
								7 AS ITEM,
                                GDD.TESTMOULDFLAG,
							  G.GOODS_LINE_CODE AS ZSCS
                            FROM

                                TP_PM_SCRAPPRODUCT SP

                                INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = SP.GROUTINGDAILYDETAILID

                                INNER JOIN TP_MST_GOODS G ON SP.GOODSID = G.GOODSID

                                INNER JOIN TP_MST_GOODSTYPE GT ON GT.GOODSTYPEID = G.GOODSTYPEID

                                INNER JOIN TSAP_HEGII_DATAITEM HGDI ON(HGDI.DATACODE = '30' OR HGDI.DATACODE = '40' OR HGDI.DATACODE = '50')

                                AND HGDI.ITEMTYPE = 2

                                AND HGDI.ITEMID = SP.PROCEDUREID

                            WHERE

                                SP.AUDITSTATUS = 1

                                AND SP.VALUEFLAG = '1'

                                AND SP.RECYCLINGFLAG = '1'

                                AND SP.RECYCLINGTIME >= :v_datebegin

                                AND SP.RECYCLINGTIME < :in_dateend

                                AND(
                                    (HGDI.WORKSHOP = 2 AND INSTR(GDD.GROUTINGLINECODE, 'C') = 1)

                                    OR(
                                        HGDI.WORKSHOP = 3

                                        AND(INSTR(GDD.GROUTINGLINECODE, 'B') = 1 OR INSTR(GDD.GROUTINGLINECODE, 'D') = 1)
                                    )
								) 
								AND INSTR(GT.GOODSTYPECODE, '001001' ) = 1
							) 
						GROUP BY

                            MATERIALCODE,
							GOODSCODE,
							WORKSHOP,
							DATACODE,
							ITEM,
							TESTMOULDFLAG,
							ZSCS
                        ORDER BY
                            DATACODE,
                            ITEM,
                            WORKSHOP";
                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":v_datebegin", OracleDbType.Date, date, ParameterDirection.Input),
                    new OracleParameter(":in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input),
                };
                DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras);

                sqlString = "select SAP_INI_BG from TP_SYS_SAPCONFIG";
                string SAP_ING_NEW = oracleConn.GetSqlResultToStr(sqlString);

                if (workData != null && workData.Rows.Count > 0 && SAP_ING_NEW == "0")
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<CrossWorkShopToSAP>.ConvertToModel(workData)) + "}}";
                    //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
                    //string url033 = ini.ReadIniData("SAP_NEW_INFO", "Url033");
                    string url033 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM033";
                    string result = PostData(url033, postString, "POST");
                    string ztype = JObject.Parse(result)["ZTYPE"].ToString();
                    string msg = JObject.Parse(result)["ZMSG"].ToString();
                    //sqlString = "update TSAP_HEGII_DATALOG_KCJZY t set t.EndTime = sysdate, DataStuts = :DataStuts, DataMSG =:msg where logid = :logid";
                    //paras = new OracleParameter[]
                    //{
                    //    new OracleParameter(":logid", OracleDbType.Varchar2, logid, ParameterDirection.Input),
                    //    new OracleParameter(":DataStuts", OracleDbType.Varchar2, ztype, ParameterDirection.Input),
                    //    new OracleParameter(":msg", OracleDbType.Varchar2, msg, ParameterDirection.Input),
                    //};
                    //oracleConn.ExecuteNonQuery(sqlString, paras);
                    oracleConn.Commit();
                }
                #endregion
            }
            catch (Exception ex)
            {
                OutputLog.TraceLog(LogPriority.Error,
                        "CrossWorkshopToSAP",
                        "跨车间作业量" + date.ToString("yyyy-MM-dd HH:mm:ss"),
                        ex.ToString(),
                        LocalPath.LogExePath + "SAP_HEGII\\Error_");
            }
        }

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

            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString = "SELECT\n" +
                        "	dl.logid,\n" +
                        "	dl.begintime,\n" +
                        "	dl.endtime,\n" +
                        "	dl.yyyymmdd,\n" +
                        "	dl.workcode,\n" +
                        "	dl.datastuts,\n" +
                        "	dl.datamsg,\n" +
                        "	dl.executedatebegin,\n" +
                        "	dl.executedateend,\n" +
                        "	u.usercode synusercode\n" +
                        "FROM\n" +
                        "	tsap_hegii_datalog_kcjzy dl\n" +
                        "	LEFT JOIN tp_mst_user u ON u.userid = dl.createuserid \n" +
                        "WHERE\n" +
                        "	dl.yyyymmdd >= :datebegin \n" +
                        "	AND dl.yyyymmdd <= :dateend \n";

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

                string datastuts = cre.Properties["datastuts"] + "";
                if (!string.IsNullOrEmpty(datastuts))
                {
                    sqlString += "   and dl.datastuts in (" + datastuts + ")\n";
                }
                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_kczzy(int logid)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);

            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString = "\n" +
                "select wd.workshop\n" +
                "	   ,case when wd.workshop = 2 then '二车间' when wd.workshop = 3 then '三车间' else '-' end workshopname\n " +
                "	   ,case when wd.item = 1 then '产量' when wd.item = 2 then '产量撤销' when wd.item = 3 then '工序报损' when wd.item = 4 then '工序报损撤销' \n" +
                "       when wd.item = 5 then '盘点清除' when wd.item = 6 then '干补' when wd.item = 7 then '回收' else '-' end as itemname\n" +
                "      ,item\n" +
                "      ,wd.datacode\n" +
                "      ,dc.datacodename\n" +
                "      ,wd.goodscode\n" +
                "      ,wd.sapcode\n" +
                "      ,wd.num\n" +
                "      ,wd.createtime\n" +
                "      ,wd.testmouldflag\n" +
                "      ,wd.zscs\n" +
                "      ,case when wd.zscs = 'L' then '立浇【L】' when wd.zscs = 'G' then '高压【G】' when wd.zscs = 'M' then '粘接高压（三水厂）【M】' when wd.zscs = 'Q' then '吊装线【Q】' else '-' end as zscsname\n" +
                "      ,wd.logid\n" +
                "  from tsap_hegii_workdata_kcjzy wd\n" +
                " inner join tsap_hegii_datacode dc\n" +
                "    on dc.datacode = wd.datacode\n" +
                " where wd.logid = :logid \n" +
                " order by wd.datacode,wd.item,wd.workshop \n";
                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":logid",OracleDbType.Int32, logid, ParameterDirection.Input),
                };

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

                return sre;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 报工

        /// <summary>
        /// 同步SAP数据（自动）
        /// </summary>
        /// <param name="date"></param>
        public static void AutoWorkDataToSAP5000(DateTime date, string funCode)
        {
            if (string.IsNullOrWhiteSpace(funCode))
            {
                //return;
                funCode = "ALL";
            }
            funCode = "," + funCode + ",";
            ServiceResultEntity sre = null;
            // 10 模具
            if (funCode == ",ALL," || funCode.Contains(",10,"))
            {
                try
                {
                    sre = SetWorkData10_50(date, "10", 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "10 模具 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "10 模具 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 20 湿坯
            if (funCode == ",ALL," || funCode.Contains(",20,"))
            {
                try
                {
                    sre = SetWorkData10_50(date, "20", 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "20 湿坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "20 湿坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 30 精坯
            if (funCode == ",ALL," || funCode.Contains(",30,"))
            {
                try
                {
                    sre = SetWorkData10_50(date, "30", 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "30 精坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "30 精坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 40 釉坯
            if (funCode == ",ALL," || funCode.Contains(",40,"))
            {
                try
                {
                    sre = SetWorkData10_50(date, "40", 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 50 烧成
            if (funCode == ",ALL," || funCode.Contains(",50,"))
            {
                try
                {
                    sre = SetWorkData10_50(date, "50", 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "50 烧成 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "50 烧成 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 6001 成品明细
            if (funCode == ",ALL," || funCode.Contains(",6001,"))
            {
                try
                {
                    sre = SetFP6001(date, 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 6001 成品明细(小时)-20分钟
            if (funCode == ",6002,")
            {
                try
                {
                    sre = SetFP6002(date, 0);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP",
                                "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP",
                            "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
        }

        /// <summary>
        /// 同步SAP数据（自动）(重载)
        /// </summary>
        /// <param name="date">当前时间</param>
        /// <param name="funCode">工序码</param>
        /// <param name="ndate">本次要执行到的时间</param>
        public static void AutoWorkDataToSAP5000(DateTime date, string funCode, DateTime ndate)
        {
            if (string.IsNullOrWhiteSpace(funCode))
            {
                //return;
                funCode = "ALL";
            }
            funCode = "," + funCode + ",";
            ServiceResultEntity sre = null;
            // 10 模具
            if (funCode == ",ALL," || funCode.Contains(",10,"))
            {
                try
                {
                    sre = SetWorkData10_50_5000("10", 0, ndate);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP5000",
                                "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP5000",
                            "10 模具 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 20 湿坯
            if (funCode == ",ALL," || funCode.Contains(",20,"))
            {
                try
                {
                    sre = SetWorkData10_50_5000("20", 0, ndate);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP5000",
                                "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP5000",
                            "20 湿坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 30 精坯
            if (funCode == ",ALL," || funCode.Contains(",30,"))
            {
                try
                {
                    sre = SetWorkData10_50_5000("30", 0, ndate);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP5000",
                                "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP5000",
                            "30 精坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 40 釉坯
            if (funCode == ",ALL," || funCode.Contains(",40,"))
            {
                try
                {
                    sre = SetWorkData10_50_5000("40", 0, ndate);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP5000",
                                "40 釉坯 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP5000",
                            "40 釉坯 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 50 烧成
            if (funCode == ",ALL," || funCode.Contains(",50,"))
            {
                try
                {
                    sre = SetWorkData10_50_5000("50", 0, ndate);
                    if (sre.Status != Constant.ServiceResultStatus.Success ||
                        "S" != sre.Result + "")
                    {
                        OutputLog.TraceLog(LogPriority.Warning,
                                "AutoWorkDataToSAP5000",
                                "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                                JsonHelper.ToJson(sre),
                                LocalPath.LogExePath + "SAP_HEGII\\Warn_");
                    }
                }
                catch (Exception ex)
                {
                    OutputLog.TraceLog(LogPriority.Error,
                            "AutoWorkDataToSAP5000",
                            "50 烧成 " + ndate.ToString("yyyy-MM-dd HH:mm:ss"),
                            ex.ToString(),
                            LocalPath.LogExePath + "SAP_HEGII\\Error_");
                }
            }
            // 6001 成品明细
            //if (funCode == ",ALL," || funCode.Contains(",6001,"))
            //{
            //    try
            //    {
            //        sre = SetFP6001(date, 0);
            //        if (sre.Status != Constant.ServiceResultStatus.Success ||
            //            "S" != sre.Result + "")
            //        {
            //            OutputLog.TraceLog(LogPriority.Warning,
            //                    "AutoWorkDataToSAP5000",
            //                    "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
            //                    JsonHelper.ToJson(sre),
            //                    LocalPath.LogExePath + "SAP_HEGII\\Warn_");
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        OutputLog.TraceLog(LogPriority.Error,
            //                "AutoWorkDataToSAP5000",
            //                "6001 成品明细 " + date.ToString("yyyy-MM-dd HH:mm:ss"),
            //                ex.ToString(),
            //                LocalPath.LogExePath + "SAP_HEGII\\Error_");
            //    }
            //}
            //// 6001 成品明细(小时)-20分钟
            //if (funCode == ",6002,")
            //{
            //    try
            //    {
            //        sre = SetFP6002(date, 0);
            //        if (sre.Status != Constant.ServiceResultStatus.Success ||
            //            "S" != sre.Result + "")
            //        {
            //            OutputLog.TraceLog(LogPriority.Warning,
            //                    "AutoWorkDataToSAP5000",
            //                    "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"),
            //                    JsonHelper.ToJson(sre),
            //                    LocalPath.LogExePath + "SAP_HEGII\\Warn_");
            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        OutputLog.TraceLog(LogPriority.Error,
            //                "AutoWorkDataToSAP5000",
            //                "6002 成品明细(小时) " + date.ToString("yyyy-MM-dd HH:mm:ss"),
            //                ex.ToString(),
            //                LocalPath.LogExePath + "SAP_HEGII\\Error_");
            //    }
            //}
        }

        /// <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";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":DATEBEGIN",OracleDbType.Varchar2, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Varchar2, cre.Properties["dateend"], 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(int logid)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                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_datacode dc\n" +
                "    on dc.datacode = wd.datacode\n" +
                " where wd.logid = :logid \n" +
                " 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),
                };

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

        #region 报工移库
        /// <summary>
        /// 报工移库_同步SAP数据（自动）
        /// </summary>
        /// <param name="date"></param>
        public static void BGYKToSAP(DateTime date, DateTime ndate)
        {
          IDBTransaction oracleConn = null;
            ServiceResultEntity sre = new ServiceResultEntity();
            int logid = 0;
            string message = string.Empty;
            string sqlString = string.Empty;
            try
            {
                #region 生成日志
                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter("in_dateend", OracleDbType.Date, ndate, ParameterDirection.Input),
                    new OracleParameter("out_logid", OracleDbType.Int32, null, ParameterDirection.Output),
                    new OracleParameter("out_msg", OracleDbType.NVarchar2, 500, null, ParameterDirection.Output)
                };

                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                DataSet ds = oracleConn.ExecStoredProcedure("PRO_SAP_HEGII_WORKDATA_BGYK", paras);
                int.TryParse(paras[1].Value + "", out logid);
                message = paras[2].Value + "";
                oracleConn.Commit();
                #endregion

                #region 同步SAP
                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

                //2022年9月8日11:38:51 更改 by fy
                //sqlString = @"
                //SELECT WERKS,
                //	   MATNR,
                //	   ZJDNU,
                //	   ZSCS,
                //	   ZSCCJ,
                //	   ZSCMS,
                //	   CHARG,
                //	   MENGE,
                //	   ZMLID
                //  FROM TSAP_HEGII_WORKDATA_BGYK
                // WHERE LOGID = :LOGID ";
                sqlString = @"SELECT
                                A.WERKS,
	                            A.MATNR,
	                            A.ZJDNU,
	                            -- A.ZSCS,
	                            'T' AS ZSCS,
	                            A.ZSCCJ,
	                            A.ZSCMS,
	                            A.CHARG,
	                            A.MENGE,
	                            A.ZMLID,
	                            to_char(B.EXECUTEDATEBEGIN, 'yyyymmddhh24miss') AS ZYWKS,
                                to_char(B.EXECUTEDATEEND, 'yyyymmddhh24miss' ) AS ZYWJS,
                                to_char(SYSDATE, 'yyyymmdd' ) AS ZBUDAT,
                                to_char(SYSDATE, 'yyyymmddhh24miss' ) AS ZMONT,
                                '' AS ZTYPE1,
                                '' AS ZMSG1
                            FROM
                                TSAP_HEGII_WORKDATA_BGYK A
                                INNER JOIN TSAP_HEGII_DATALOG_BGYK B ON B.LOGID = A.LOGID
                            WHERE
                                A.LOGID = :LOGID";

                paras = new OracleParameter[]
                {
                    new OracleParameter(":LOGID", OracleDbType.Int32, logid, ParameterDirection.Input),
                };

                DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras);

                //获取报工SAP接口是否开启
                sqlString = "select SAP_INI_BG from TP_SYS_SAPCONFIG";
                string SAP_ING_NEW = oracleConn.GetSqlResultToStr(sqlString);

                if (workData != null && workData.Rows.Count > 0 && SAP_ING_NEW == "1")
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<BGYKToSAP>.ConvertToModel(workData)) + "}}";
                    INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
                    string url034 = ini.ReadIniData("SAP_NEW_INFO", "Url034");
                    //url034 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM034";
                    string result = PostData(url034, postString, "POST");
                    string ztype = JObject.Parse(result)["ZTYPE"].ToString();
                    string zmsg = JObject.Parse(result)["ZMSG"].ToString();
                    sqlString = "update TSAP_HEGII_DATALOG_BGYK t set t.EndTime = sysdate, ZTYPE = :ZTYPE, ZMSG =:ZMSG where logid = :logid";
                    paras = new OracleParameter[]
                    {
                        new OracleParameter(":logid", OracleDbType.Varchar2, logid, ParameterDirection.Input),
                        new OracleParameter(":ZTYPE", OracleDbType.Varchar2, ztype, ParameterDirection.Input),
                        new OracleParameter(":ZMSG", OracleDbType.Varchar2, zmsg, ParameterDirection.Input),
                    };
                    oracleConn.ExecuteNonQuery(sqlString, paras);
                    oracleConn.Commit();
                }
                #endregion
            }
            catch (Exception ex)
            {
                OutputLog.TraceLog(LogPriority.Error,
                        "BGYKToSAP",
                        "报工移库" + date.ToString("yyyy-MM-dd HH:mm:ss"),
                        ex.ToString(),
                        LocalPath.LogExePath + "SAP_HEGII\\Error_");
            }
        }

        public static void BGYKToSAP_TEST(DateTime date, DateTime ndate)
        {
            IDBTransaction oracleConn = null;
            ServiceResultEntity sre = new ServiceResultEntity();
            int logid = 0;
            string message = string.Empty;
            string sqlString = string.Empty;
            try
            {
                #region 同步SAP
                oracleConn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);

                //2022年9月8日11:38:51 更改 by fy
                //sqlString = @"
                //SELECT WERKS,
                //	   MATNR,
                //	   ZJDNU,
                //	   ZSCS,
                //	   ZSCCJ,
                //	   ZSCMS,
                //	   CHARG,
                //	   MENGE,
                //	   ZMLID
                //  FROM TSAP_HEGII_WORKDATA_BGYK
                // WHERE LOGID = :LOGID ";

                sqlString = @"SELECT '5000' WERKS,MATNR,to_char(ZJDNU) ZJDNU,'T' AS ZSCS,to_char(ZSCCJ) ZSCCJ,to_char(ZSCMS) ZSCMS,to_char(CHARG)CHARG,
                                to_char(MENGE) MENGE,to_char(ZMLID) ZMLID,
                                to_char(:V_DATEBEGIN, 'yyyymmddhh24miss') AS ZYWKS,
                                to_char(:IN_DATEEND, 'yyyymmddhh24miss' ) AS ZYWJS,
                                to_char(SYSDATE, 'yyyymmdd' ) AS ZBUDAT,
                                to_char(SYSDATE, 'yyyymmddhh24miss' ) AS ZMONT FROM (
                        SELECT
	                        T.MATNR,
	                        '30' AS ZJDNU,
	                        G.GOODS_LINE_CODE AS ZSCS,
	                        '2' AS ZSCCJ,
	                        T.ZSCMS,
	                        TO_CHAR( SYSDATE, 'yyyymm' ) AS CHARG,
	                        SUM( T.MENGE ) AS MENGE,
	                        '3' AS ZMLID 
                        FROM
	                        (-- 3-3线上施釉（3）到3#刮登（99）
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.CREATETIME >= :V_DATEBEGIN 
		                        AND PD1.CREATETIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 99 
		                        AND PD1.VALUEFLAG = '1' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 3 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG UNION ALL-- 3-3线上施釉（3）到3#刮登（99）撤销了的
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        - COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.BACKOUTTIME >= :V_DATEBEGIN 
		                        AND PD1.BACKOUTTIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 99 
		                        AND PD1.VALUEFLAG = '0' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 3 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG 
	                        ) T
	                        LEFT JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID 
                        GROUP BY
	                        T.MATNR,
	                        T.ZSCMS,
	                        G.GOODS_LINE_CODE UNION ALL
                        SELECT
	                        T.MATNR,
	                        '40' AS ZJDNU,
	                        G.GOODS_LINE_CODE AS ZSCS,
	                        '2' AS ZSCCJ,
	                        T.ZSCMS,
	                        TO_CHAR( SYSDATE, 'yyyymm' ) AS CHARG,
	                        SUM( T.MENGE ) AS MENGE,
	                        '3' AS ZMLID 
                        FROM
	                        (-- 3#卸窑（103）到7-1成检出窑交接（11）
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.CREATETIME >= :V_DATEBEGIN 
		                        AND PD1.CREATETIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 11 
		                        AND PD1.VALUEFLAG = '1' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 103 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG UNION ALL-- 3-3线上施釉（3）到3#刮登（99）撤销了的
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        - COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.BACKOUTTIME >= :V_DATEBEGIN 
		                        AND PD1.BACKOUTTIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 11 
		                        AND PD1.VALUEFLAG = '0' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 103 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG 
	                        ) T
	                        LEFT JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID 
                        GROUP BY
	                        T.MATNR,
	                        T.ZSCMS,
	                        G.GOODS_LINE_CODE UNION ALL
                        SELECT
	                        T.MATNR,
	                        '40' AS ZJDNU,
	                        G.GOODS_LINE_CODE AS ZSCS,
	                        '3' AS ZSCCJ,
	                        T.ZSCMS,
	                        TO_CHAR( SYSDATE, 'yyyymm' ) AS CHARG,
	                        SUM( T.MENGE ) AS MENGE,
	                        '2' AS ZMLID 
                        FROM
	                        (-- 6-1卸窑（10）到3#成检交接（104）
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.CREATETIME >= :V_DATEBEGIN 
		                        AND PD1.CREATETIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 104 
		                        AND PD1.VALUEFLAG = '1' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 10 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG UNION ALL-- 6-1卸窑（10）到3#成检交接（104）撤销了的
	                        SELECT
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE AS MATNR,
		                        DECODE( GDD.TESTMOULDFLAG, '1', 'Y', 'C' ) AS ZSCMS,
		                        - COUNT( 1 ) AS MENGE 
	                        FROM
		                        TP_PM_PRODUCTIONDATA PD1
		                        INNER JOIN TP_PM_GROUTINGDAILYDETAIL GDD ON GDD.GROUTINGDAILYDETAILID = PD1.GROUTINGDAILYDETAILID 
	                        WHERE
		                        PD1.BACKOUTTIME >= :V_DATEBEGIN 
		                        AND PD1.BACKOUTTIME < :IN_DATEEND 
		                        AND PD1.PROCEDUREID = 104 
		                        AND PD1.VALUEFLAG = '0' 
		                        AND EXISTS ( SELECT 1 FROM TP_PM_PRODUCTIONDATA PD2 WHERE PD2.BARCODE = PD1.BARCODE AND PD2.PROCEDUREID = 10 ) 
	                        GROUP BY
		                        GDD.GOODSID,
		                        GDD.MATERIALCODE,
		                        GDD.TESTMOULDFLAG 
	                        ) T
	                        LEFT JOIN TP_MST_GOODS G ON G.GOODSID = T.GOODSID 
                        GROUP BY
	                        T.MATNR,
	                        T.ZSCMS,
	                        G.GOODS_LINE_CODE
                        ) WHERE MENGE > 0";

                OracleParameter[] paras = new OracleParameter[]
                {
                    new OracleParameter(":V_DATEBEGIN",OracleDbType.Date, date, ParameterDirection.Input),
                    new OracleParameter(":IN_DATEEND",OracleDbType.Date, ndate, ParameterDirection.Input),
                };

                DataTable workData = oracleConn.GetSqlResultToDt(sqlString, paras);

                //获取报工SAP接口是否开启
                sqlString = "select SAP_INI_BG from TP_SYS_SAPCONFIG";
                string SAP_ING_NEW = oracleConn.GetSqlResultToStr(sqlString);

                if (workData != null && workData.Rows.Count > 0 && SAP_ING_NEW == "0")
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<BGYKToSAP>.ConvertToModel(workData)) + "}}";
                    //INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
                    //string url034 = ini.ReadIniData("SAP_NEW_INFO", "Url034");
                    string url034 = "http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM034";
                    string result = PostData(url034, postString, "POST");
                    string ztype = JObject.Parse(result)["ZTYPE"].ToString();
                    string zmsg = JObject.Parse(result)["ZMSG"].ToString();
                    //sqlString = "update TSAP_HEGII_DATALOG_BGYK t set t.EndTime = sysdate, ZTYPE = :ZTYPE, ZMSG =:ZMSG where logid = :logid";
                    //paras = new OracleParameter[]
                    //{
                    //    new OracleParameter(":logid", OracleDbType.Varchar2, logid, ParameterDirection.Input),
                    //    new OracleParameter(":ZTYPE", OracleDbType.Varchar2, ztype, ParameterDirection.Input),
                    //    new OracleParameter(":ZMSG", OracleDbType.Varchar2, zmsg, ParameterDirection.Input),
                    //};
                    //oracleConn.ExecuteNonQuery(sqlString, paras);
                    oracleConn.Commit();
                }
                #endregion
            }
            catch (Exception ex)
            {
                OutputLog.TraceLog(LogPriority.Error,
                        "BGYKToSAP",
                        "报工移库" + date.ToString("yyyy-MM-dd HH:mm:ss"),
                        ex.ToString(),
                        LocalPath.LogExePath + "SAP_HEGII\\Error_");
            }
        }

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

            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString = @"
                SELECT DL.LOGID,
                       DL.BEGINTIME,
                       DL.ENDTIME,
                       DL.YYYYMMDD,
                       DL.ZTYPE,
                       DL.ZMSG,
                       U.USERCODE SYNUSERCODE
                  FROM TSAP_HEGII_DATALOG_BGYK DL
                  LEFT JOIN TP_MST_USER U
                    ON U.USERID = DL.CREATEUSERID
                 WHERE DL.YYYYMMDD >= :DATEBEGIN
                   AND DL.YYYYMMDD <= :DATEEND ";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":DATEBEGIN",OracleDbType.Varchar2, cre.Properties["datebegin"], ParameterDirection.Input),
                    new OracleParameter(":DATEEND",OracleDbType.Varchar2, cre.Properties["dateend"], 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_BGYK(int logid)
        {
            IDBConnection oracleConn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
            ServiceResultEntity sre = new ServiceResultEntity();
            try
            {
                string sqlString = @" 
                SELECT WERKS,
                       MATNR,
                       ZJDNU,
                       ZSCS,
                       ZSCCJ,
                       ZSCMS,
                       CHARG,
                       MENGE,
                       ZMLID
                  FROM TSAP_HEGII_WORKDATA_BGYK
                 WHERE LOGID = :LOGID ";

                OracleParameter[] oracleParameter = new OracleParameter[]
                {
                    new OracleParameter(":LOGID",OracleDbType.Int32, logid, ParameterDirection.Input),
                };

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

        #region PostData 请求
        public static string PostData(string url, string data, string method)
        {
            //将单引号转义成双引号
            data = data.Replace("'", "\"");
            //创建Web访问对象
            HttpWebRequest myRequest = (HttpWebRequest)WebRequest.Create(url);
            //把用户传过来的数据转成“UTF-8”的字节流
            byte[] buf = System.Text.Encoding.GetEncoding("UTF-8").GetBytes(data);

            myRequest.Method = method;
            myRequest.ContentLength = buf.Length;
            myRequest.ContentType = "application/json;charset=UTF-8";
            //myRequest.MaximumAutomaticRedirections = 1;
            myRequest.AllowAutoRedirect = true;

            //UTF8标准转码加密
            INIUtility ini = INIUtility.Instance(INIUtility.IniFile.SAP_HEGII);
            // 配置文件
            string userName = ini.ReadIniData("SAP_NEW_INFO", "userName");
            
            // 测试
            //string userName = "hgsapdk:Sapdk#240";
            
            // 正式
            //string userName = "PODKMES:Sapdk#800";
            
            
            string base64Header = Convert.ToBase64String(Encoding.UTF8.GetBytes(userName));
            myRequest.Headers.Add("Authorization", "Basic " + base64Header);

            //发送请求
            Stream stream = myRequest.GetRequestStream();
            stream.Write(buf, 0, buf.Length);
            stream.Close();

            //获取接口返回值
            //通过Web访问对象获取响应内容
            HttpWebResponse myResponse = (HttpWebResponse)myRequest.GetResponse();
            //通过响应内容流创建StreamReader对象，因为StreamReader更高级更快
            StreamReader reader = new StreamReader(myResponse.GetResponseStream(), Encoding.UTF8);
            //string returnXml = HttpUtility.UrlDecode(reader.ReadToEnd());//如果有编码问题就用这个方法
            string returnXml = reader.ReadToEnd();//利用StreamReader就可以从响应内容从头读到尾
            reader.Close();
            myResponse.Close();

            // 结果
            OutputLog.TraceLog(LogPriority.Information,
                    "报工030", method, data,
                    LocalPath.LogExePath + "SAP_HEGII\\Info_030");

            return returnXml;
        }
        #endregion

        #region 转换
        public class ModelConvertHelper<T> where T : new()
        {
            public static List<T> ConvertToModel(DataTable dt)
            {
                // 定义集合
                List<T> ts = new List<T>();
                // 获得此模型的类型
                Type type = typeof(T);
                string tempName = "";
                foreach (DataRow dr in dt.Rows)
                {
                    T t = new T();
                    // 获得此模型的公共属性
                    PropertyInfo[] propertys = t.GetType().GetProperties();

                    foreach (PropertyInfo pi in propertys)
                    {
                        tempName = pi.Name;
                        // 检查DataTable是否包含此列
                        if (dt.Columns.Contains(tempName))
                        {
                            // 判断此属性是否有Setter
                            if (!pi.CanWrite) continue;
                            object value = dr[tempName];
                            if (value != DBNull.Value)
                                pi.SetValue(t, value, null);
                        }
                    }
                    ts.Add(t);
                }
                return ts;
            }
        }
        #endregion
    }
}