HGSRM/WMS-BS/NFine.Application/KBSWMS/ICSUpdateMoPickApp.cs


using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using NFine.Code;
using NFine.Data.Extensions;
using NFine.Domain._03_Entity.SRM;
using NFine.Repository;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace NFine.Application.KBSWMS
{
   public class ICSUpdateMoPickApp : RepositoryFactory<ICSVendor>
    {

        public DataTable GetICSMOPickInfo(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"		select b.ID,a.MOCode,b.Sequence,b.InvCode,c.InvName,b.Quantity,b.IssueQuantity from dbo.ICSMO a
                                left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
                                left join dbo.ICSInventory c on b.InvCode=c.InvCode and b.WorkPoint=c.WorkPoint
                                where a.MOCode='" + queryParam["Code"].ToString() + "'";

            //if (!string.IsNullOrWhiteSpace(queryJson))
            //{
            //    if (!string.IsNullOrWhiteSpace(queryParam["Code"].ToString()))
            //    {
            //        sql += " and a.ApplyNegCode like '%" + queryParam["Code"].ToString() + "%' ";
            //    }
            //}
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }



        public string UpdateMopickInfoByCode(string Code)
        {
            string msg = "";
            try
            {
                //检验输入的工单号是否存在
                string sqlSeach = @"select * from dbo.ICSMO where MOCode='" + Code + "'";
                DataTable dtMoInfo = SqlHelper.GetDataTableBySql(sqlSeach);
                if (dtMoInfo == null )
                {
                    throw new Exception("所输入的工单号未查询到相关单据信息！");
                }
                //调用同步方法更新子件数据
              msg= ExecuteMoPick(Code);
            }
            catch (Exception ex)
            {
                msg=ex.Message;
            }
            return msg;
        }




        public string  ExecuteMoPick(string Code)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg=string.Empty;
            string sqlSeachMopickInfo = @"select b.PickID from dbo.ICSMO a 
                                        left join dbo.ICSMOPick b on a.MODetailID=b.MODetailID and a.WorkPoint=b.WorkPoint
                                        where a.MOCode='" + Code + "'";
            DataTable dtMoPickInfo = SqlHelper.GetDataTableBySql(sqlSeachMopickInfo);


            string ERPUrl = ConfigurationManager.ConnectionStrings["MopickERPMUrl"].ConnectionString;
            string Namespace = this.GetType().Namespace;
            string Class = this.GetType().Name + WorkPoint;
            StringBuilder sql = new StringBuilder();
            QueryModel input = new QueryModel();
            input.time = "2000-01-01";
            input.pageSize = 2000;
            input.workorder = Code;
            //获取token
            string token = GetToken.GetTokenInfermation();
            string Inputstr = JsonConvert.SerializeObject(input);
            string resultStr = HTTPHelper.HttpPost("生产备料", ERPUrl + "KBS_erp_wms_sfq", Inputstr, token);
            ResultModel result = new ResultModel();
            result = JsonConvert.DeserializeObject<ResultModel>(resultStr);
            if (result.success && result.count > 0)
            {
                try
                {
                    string extensionID = string.Empty;
                    //获取ICSExtensionID
                    string IDString = $@"SELECT ID from ICSExtension WHERE WorkPoint='{WorkPoint}' AND Colspan='~~~~~~~~~~~~~'";
                    var IDdt = SqlHelper.ExecuteTable(IDString);
                    if (IDdt.Rows.Count > 0)
                    {
                        extensionID = IDdt.Rows[0]["ID"].ToString();
                    }
                    else
                    {
                        extensionID = SqlHelper.GetNewid();
                        Dictionary<string, string> extensionvalue = new Dictionary<string, string>
                    {
                        { "ID", $"'{extensionID}'" },
                        { "BatchCode", "''" },
                        { "version", "''" },
                        { "Brand", "''" },
                        { "ProjectCode", "''" },
                        { "cFree1", "''" },
                        { "cFree2", "''" },
                        { "cFree3", "''" },
                        { "cFree4", "''" },
                        { "cFree5", "''" },
                        { "cFree6", "''" },
                        { "cFree7", "''" },
                        { "cFree8", "''" },
                        { "cFree9", "''" },
                        { "cFree10", "''" },
                        { "Colspan", "'" + "~~~~~~~~~~~~~" + "'" },
                        { "MTIME", "GETDATE()" },
                        { "MUSER", "'" + MUSER + "'" },
                        { "MUSERName", "'" + MUSERNAME + "'" },
                        { "WorkPoint", "'" + WorkPoint + "'" }
                    };
                        sql.Append(SqlHelper.InsertSQLKingDeeEx("ICSExtension", extensionvalue, WorkPoint));
                    }
                     
                        var res = (List<SFAModel>)JsonConvert.DeserializeObject(result.data.list.ToString(), typeof(List<SFAModel>));

                        var sw1 = new Stopwatch();
                        sw1.Start();
                        //获取数据库的委外备料子件数据
                        string moPickString = $@"SELECT max(Cast(Sequence as int)) as Sequence, MODetailID,WorkPoint from ICSMOPick 
                                               GROUP BY MODetailID,WorkPoint";
                        var moPickdt = SqlHelper.ExecuteTable( moPickString);
                        //自由项
                        Dictionary<string, string> value = new Dictionary<string, string>();
                     
                        var groups = res.GroupBy(o => o.SFB01).ToList();
                        sw1.Stop();
                      //  Debug.WriteLine("查询时间" + sw1.Elapsed.TotalSeconds);
                        var sw = new Stopwatch();
                        sw.Restart();
                        foreach (var item in groups)
                        {

                            var data = item.OrderBy(o => o.RNUM).ToList();
                            for (int idx = 0; idx < data.Count; idx++)
                            {
                            //处理查询到的工单子件信息
                            string currentPickID = $"{data[idx].SFB01}~1~{data[idx].SFA27}";
                            for (int i = dtMoPickInfo.Rows.Count - 1; i >= 0; i--)
                            {
                                if (dtMoPickInfo.Rows[i]["PickID"].ToString() == currentPickID)
                                {
                                    dtMoPickInfo.Rows.RemoveAt(i);
                                    break;
                                }
                            }


                            Dictionary<string, string> values = new Dictionary<string, string>();
                                //委外备料
                                var MODetailID = $"'{data[idx].SFB01}~1'";

                                //查询数据库中有没有备料信息，获取行号信息
                                var Seqence = 0;
                                if (moPickdt != null && moPickdt.Rows.Count > 0)
                                {
                                    var tmp = moPickdt.Select($"MODetailID = {MODetailID} and WorkPoint = '{data[idx].LEGAL}'");
                                    if (tmp.Length > 0)
                                    {
                                        Seqence = int.Parse(tmp[0]["Sequence"].ToString()) + idx + 1;
                                    }
                                    else
                                    {
                                        Seqence = idx + 1;
                                    }
                                }
                                else
                                {
                                    Seqence = idx + 1;
                                }
                                values.Add("Sequence", $"'{Seqence}'");
                                values.Add("MODetailID", MODetailID);
                                values.Add("PickID", $"'{data[idx].SFB01}~1~{data[idx].SFA27}'");
                                values.Add("InvCode", $"'{data[idx].SFA27}'");
                                values.Add("Quantity", $"(SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA05})");
                                values.Add("ParentQuantity", "'0'");
                                values.Add("ParentAmount", "'0'");
                                values.Add("Amount", $"'{data[idx].SFA05}'");
                                values.Add("WHCode", "''");
                                values.Add("SupplyType", "'3'");
                                values.Add("ExtensionID", $"'{extensionID}'");
                                values.Add("MTIME", $"'{data[idx].SFBDATE}'");
                                values.Add("WorkPoint", $"'{data[idx].LEGAL}'");
                                values.Add("ID", "NEWID()");
                                //values.Add("IssueQuantity", $" CASE WHEN IssueQuantity>0 THEN IssueQuantity ELSE (SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA06}) END");
                                values.Add("IssueQuantity", $" (SELECT ISnull((SELECT top 1 InvRate FROM ICSInventory WHERE InvCode='{data[idx].SFA27}' AND workPoint='{data[idx].LEGAL}'),1)* {data[idx].SFA06})");

                                values.Add("MUSER", $"'{data[idx].SFBMODU}'");
                                values.Add("MUSERName", $"'{data[idx].GEN02}'");
                                values.Add("EATTRIBUTE1", $"'0'");
                                sql.Append(SqlHelper.InsertSQLKingDee("ICSMOPick", values, $"MODetailID = {MODetailID} and InvCode= '{data[idx].SFA27}' and WorkPoint='{data[idx].LEGAL}' "));

                            }

                        }

                    // 构建删除 SQL 语句
                    if (dtMoPickInfo.Rows.Count > 0)
                    {
                        StringBuilder deleteSql = new StringBuilder();
                        deleteSql.Append("DELETE FROM ICSMOPick WHERE PickID IN (");
                        for (int i = 0; i < dtMoPickInfo.Rows.Count; i++)
                        {
                            if (i > 0)
                            {
                                deleteSql.Append(",");
                            }
                            deleteSql.Append($"'{dtMoPickInfo.Rows[i]["PickID"]}'");
                        }
                        deleteSql.Append(")");
                        SqlHelper.ExecuteDate(deleteSql.ToString());
                    }


                    SqlHelper.ExecuteDate( $"{sql}");
                        sql = new StringBuilder();
                        sw.Stop();
                  
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                }
            }


            return msg;


        }

        public string UpdateMoInfoByCode(string Code)
        {
            string msg = "";
            try
            {
                //检验输入的工单号是否存在
                string sqlSeach = @"select * from dbo.ICSMO where MOCode='" + Code + "'";
                DataTable dtMoInfo = SqlHelper.GetDataTableBySql(sqlSeach);
                if (dtMoInfo == null)
                {
                    throw new Exception("所输入的工单号未查询到相关单据信息！");
                }
                //调用同步方法更新工单数据
                msg = ExecuteMo(Code);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }


        public string  ExecuteMo(string Code)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string ERPUrl = ConfigurationManager.ConnectionStrings["MopickERPMUrl"].ConnectionString;
            string msg = string.Empty;
            string Namespace = this.GetType().Namespace;
            string Class = this.GetType().Name + WorkPoint;

            StringBuilder sql = new StringBuilder();
            QueryModel input = new QueryModel();
            input.time =  "2000-01-01";
            input.workorder = Code;
            //获取token
            string token = GetToken.GetTokenInfermation();
            string Inputstr = JsonConvert.SerializeObject(input);
            string resultStr = HTTPHelper.HttpPost("生产订单", ERPUrl + "KBS_erp_wms_sfb", Inputstr, token);
            ResultModel result = new ResultModel();
            result = JsonConvert.DeserializeObject<ResultModel>(resultStr);
        
            if (result.success && result.count > 0)
            {
                try
                {
                    JArray res = (JArray)JsonConvert.DeserializeObject(result.data.list.ToString());
                    var sw = new Stopwatch();
                    sw.Start();
                    //自由项
                    Dictionary<string, string> value = new Dictionary<string, string>();
                    value.Add("ID", "newid()");
                    value.Add("BatchCode", "''");
                    value.Add("version", "''");
                    value.Add("Brand", "''");
                    value.Add("ProjectCode", "''");
                    value.Add("cFree1", "''");
                    value.Add("cFree2", "''");
                    value.Add("cFree3", "''");
                    value.Add("cFree4", "''");
                    value.Add("cFree5", "''");
                    value.Add("cFree6", "''");
                    value.Add("cFree7", "''");
                    value.Add("cFree8", "''");
                    value.Add("cFree9", "''");
                    value.Add("cFree10", "''");
                    value.Add("Colspan", "'" + "~~~~~~~~~~~~~" + "'");
                    value.Add("MTIME", "GETDATE()");
                    value.Add("MUSER", "'" + MUSER + "'");
                    value.Add("MUSERName", "'" + MUSERNAME + "'");
                    value.Add("WorkPoint", "'" + WorkPoint + "'");
                    sql.Append(SqlHelper.InsertSQLKingDeeEx("ICSExtension", value,WorkPoint));
                    int i = 1;
                    foreach (var item in res)
                    {
                        JObject det = (JObject)item;

                        if (i % 1000 == 0)
                        {
                            SqlHelper.ExecuteDate( $"{sql}");
                            sql = new StringBuilder();

                        }

                        //采购订单
                        Dictionary<string, string> values = new Dictionary<string, string>();
                        values.Add("MOCODE", "'" + det["SFB01"].ToString().Trim() + "'");//工单号
                        values.Add("Sequence", "'1'");//行号（鼎捷不返回）
                        values.Add("InvCode", "'" + det["SFB05"].ToString().Trim() + "'");//物料代码
                        values.Add("Quantity", "'" + det["SFB08"].ToString().Trim() + "'");//数量
                        values.Add("Amount", "'0'");//辅计量
                        values.Add("DepCode", "'" + det["SFB82"].ToString().Trim() + "'");//部门代码
                        values.Add("WHCode", "'" + det["SFB30"].ToString().Trim() + "'");//仓库代码
                        values.Add("StartDate", "'" + det["SFB25"].ToString().Trim() + "'");//开工日期
                        values.Add("DueDate", "'" + det["SFB18"].ToString().Trim() + "'");//完工日期
                        values.Add("MOSTATUS", "'1'");//工单状态
                        values.Add("MOMemo", "''");//备注
                        values.Add("CreatePerson", "'" + det["SFBORIU"].ToString() + "'");//创建人
                        values.Add("CreateDateTime", "'" + det["SFB81"].ToString().Trim() + "'");//创建时间

                        values.Add("ERPStatus", "'2'");//生产订单状态（？？？怎么判断的）
                        values.Add("MOID", $"'{det["SFB01"]}'");
                        values.Add("MODetailID", $"'{det["SFB01"]}~1'");
                        values.Add("ExtensionID", $"(select id from ICSExtension where Colspan = '~~~~~~~~~~~~~' and WorkPoint = '{det["LEGAL"]}') ");//自由项需要最后update
                        values.Add("ID", "newid()");
                        values.Add("MUSER", "'" + det["SFBMODU"].ToString().Trim() + "'");
                        values.Add("MUSERName", "'" + det["GEN02"].ToString().Trim() + "'");
                        values.Add("WorkPoint", "'" + det["LEGAL"].ToString().Trim() + "'");
                        values.Add("MTIME", "'" + det["SFBDATE"].ToString().Trim() + "'");

                        sql.Append(SqlHelper.InsertSQLKingDee("ICSMO", values, "MOCODE ='" + det["SFB01"].ToString().Trim() + $"' and MODetailID='{det["SFB01"]}~{1}' and WorkPoint='{det["LEGAL"]}'"));

                        i++;

                    }
                    SqlHelper.ExecuteDate($"{sql}");
                    sw.Stop();
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                }
            }
            return msg;
        }
























        /// <summary>
        /// 查询接口Model
        /// </summary>
        public class QueryModel
        {
            /// <summary>
            /// 时间
            /// </summary>
            public string time { get; set; }
            /// <summary>
            /// 页码
            /// </summary>
            public int pageIndex { get; set; } = 1;
            /// <summary>
            /// 页数
            /// </summary>
            public int pageSize { get; set; } = int.MaxValue;

            public string workorder { get; set; }
        }
        public class TokenModel
        {
            public string token { get; set; }
        }

        public class ResultModel
        {

            public bool success { get; set; }

            public string message { get; set; }

            public int code { get; set; }
            public int count { get; set; }
            public DateTime? maxTime { get; set; }


            public DataList data { get; set; }
        }

        public class DataList
        {
            public object count { get; set; }
            public object list { get; set; }
        }


        public class SFAModel
        {
            /// <summary>
            /// 
            /// </summary>
            public string SFB01 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string SFA27 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public decimal SFA05 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public decimal SFA06 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string SFBMODU { get; set; }
            /// <summary>
            /// 李晓粉
            /// </summary>
            public string GEN02 { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public DateTime SFBDATE { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public string LEGAL { get; set; }
            /// <summary>
            /// 
            /// </summary>
            public int RNUM { get; set; }
        }



        public class GetToken
        {
           // private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            public static string GetTokenInfermation()
            {
                try
                {
                    string resultStr = HTTPHelper.QueryPostparamsService("获取token", $@"https://weapp.lexy.cn/lexyapi/getToken?ent_id=qy202307311690791200&ent_secret=66cc4211eaa37f79f3608353e4e28c36");
                    var tokenString = JsonConvert.DeserializeObject<TokenModel>(resultStr);
                    if (string.IsNullOrEmpty(tokenString.token))
                    {
                        throw new Exception("获取token失败");
                    }
                    return tokenString.token;

                }
                catch (System.Net.WebException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }



        public class HTTPHelper
        {
           // private static log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            public static string HttpPost(string apiName, string url, string body)
            {
                try
                {
                    //log.Debug(url + Environment.NewLine + body);
                    Encoding encoding = Encoding.UTF8;
                    System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
                    request.Method = "POST";
                    request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                    request.ContentType = "application/json; charset=utf-8";
                    // request.ContentType = "text/html, application/xhtml+xml";
                    byte[] buffer = encoding.GetBytes(body);
                    request.ContentLength = buffer.Length;
                    request.GetRequestStream().Write(buffer, 0, buffer.Length);
                    System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
                    using (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
                    {
                        return reader.ReadToEnd();
                    }
                }
                catch (System.Net.WebException ex)
                {
                   // log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine + body);
                    throw new Exception(apiName + "调用失败," + ex.Message);
                }
            }

            public static string HttpPost(string apiName, string url, string body, string headers = "")
            {
                try
                {
                    //log.Debug(url + Environment.NewLine + body);
                    Encoding encoding = Encoding.UTF8;
                    System.Net.HttpWebRequest request = (System.Net.HttpWebRequest)System.Net.WebRequest.Create(url);
                    request.Method = "POST";
                    request.Headers.Add("token", headers);
                    request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                    request.ContentType = "application/json; charset=utf-8";
                    // request.ContentType = "text/html, application/xhtml+xml";
                    byte[] buffer = encoding.GetBytes(body);
                    request.ContentLength = buffer.Length;
                    request.GetRequestStream().Write(buffer, 0, buffer.Length);
                    System.Net.HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
                    using (System.IO.StreamReader reader = new System.IO.StreamReader(response.GetResponseStream(), encoding))
                    {
                        return reader.ReadToEnd();
                    }
                }
                catch (System.Net.WebException ex)
                {
                   // log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine + body);
                    throw new Exception(apiName + "调用失败," + ex.Message);
                }
            }

            public static string QueryPostparamsService(string apiName, string url)
            {
                string result = "";
                HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
                req.Method = "Get";
                //req.Headers.Add()
                try
                {
                    HttpWebResponse resp = (HttpWebResponse)req.GetResponse();
                    Stream stream = resp.GetResponseStream();
                    //获取内容
                    using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
                    {
                        result = reader.ReadToEnd();
                    }
                }
                catch (Exception ex)
                {
                   // log.Error(ex.ToString() + Environment.NewLine + url + Environment.NewLine);
                    throw new Exception(apiName + "调用失败," + ex.Message);
                }
                return result;
            }
        }














    }
}