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.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,'yyyymmddhhmiss') AS ZYWKS,\n" +
                            "	to_char(B.EXECUTEDATEEND,'yyyymmddhhmiss') AS ZYWJS,\n" +
                            "	to_char(SYSDATE,'yyyymmddhhmiss') AS ZMONT,\n" +
                            "	A.WORKCODE AS WERKS,\n" +
                            "	A.SAPCODE AS MATNR,\n" +
                            "	A.GOODSCODE AS GROES,\n" +
                            "	A.WORKSHOP AS ZSCCJ,\n" +
                            "	A.DATACODE AS ZJDNU,\n" +
                            "	A.ITEM AS ZZYLX,\n" +
                            "	A.NUM AS MENGE,\n" +
                            "	A.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);

                if (workData != null && workData.Rows.Count > 0)
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<CrossWorkShopToSAP>.ConvertToModel(workData)) + "}}";
                    string result = PostData("http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM033", 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 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,
	                            A.ZSCCJ,
	                            A.ZSCMS,
	                            A.CHARG,
	                            A.MENGE,
	                            A.ZMLID,
	                            to_char(B.EXECUTEDATEBEGIN, 'yyyymmddhhmiss') AS ZYWKS,
                                to_char(B.EXECUTEDATEEND, 'yyyymmddhhmiss' ) AS ZYWJS,
                                to_char(SYSDATE, 'yyyymmdd' ) AS ZBUDAT,
                                to_char(SYSDATE, 'yyyymmddhhmiss' ) 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);

                if (workData != null && workData.Rows.Count > 0)
                {
                    string postString = "{\"IT_INPUT\":{\"item\":" + JsonHelper.ToJson(ModelConvertHelper<BGYKToSAP>.ConvertToModel(workData)) + "}}";
                    string result = PostData("http://hgs4podev.hegii.com:50200/RESTAdapter/DKMES/ZPPFM034", 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标准转码加密
            string base64Header = Convert.ToBase64String(Encoding.UTF8.GetBytes("hgsapdk:Sapdk#240"));
            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();
            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
    }
}