HGSRM/WMS-BS/NFine.Application/WMS/WMSCreateMOApplyApp.cs

using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
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.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Threading;
using System.Reflection.Emit;
using NFine.Application.WMS;
using System.Net;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;

namespace NFine.Application.WMS
{
    public class WMSCreateMOApplyApp : RepositoryFactory<ICSVendor>
    {
        public static DataTable Invmes = new DataTable();

        PickMaterialApp App = new PickMaterialApp();



        #region 删除辅料材料出库单
        public string DeleteICSMOApply(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;

            sql += string.Format(@"DELETE FROM dbo.ICSMOApply  WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return msg;
        }
        #endregion

        #region 保存发料信息
        public string SaveICSMOApply(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            List<JsonData> parsedData = JsonConvert.DeserializeObject<List<JsonData>>(ICSASN);
            string sql = "";
            string Colspan = "";
            string str1 = "";
            List<string> ExtensionIDList = new List<string>();
            // 获取解析后的数据
            JsonData data = parsedData[0];
            foreach (var detail in data.Detail)
            {
                Colspan ="~" + detail.Batch + "~~~~~~~~~~~~";
                string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                bool flag = true;
                foreach (var item in ExtensionIDList)
                {
                    if (item == Colspan + WorkPoint)
                    {
                        flag = false;
                    }
                }
                if (ExtensionID == null && flag == true)
                {
                    str1 = Guid.NewGuid().ToString();
                    sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
                        str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
                        data.User, UserName, WorkPoint);
                }
                else if (ExtensionID != null)
                {
                    str1 = ExtensionID.ToString();
                }
                ExtensionIDList.Add(Colspan + WorkPoint);

                sql += "INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode,EATTRIBUTE11)" +
                   "                          select  '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','1','{23}','{24}',0,'{25}','{26}'";
                sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, data.MTIME, WorkPoint, '1', str1, data.User, UserName, data.MTIME, data.Dept, data.CodeType, data.ApplyNegCode, data.Custmer, data.Remarks, detail.DetailReamrk, detail.SYproject, "杂发", detail.SourceCode, data.Remarks2, detail.DetailReamrk2, data.LYDept,data.Remarks3);
            }
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
               msg=ex.Message;
            }
            return msg;
        }
        #region 辅料材料出库单主表信息查询
        public DataTable GetICSMOApply(ref Pagination jqgridparam, string queryJson)
        {
            string ParentId = "";
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            var queryParam = queryJson.ToJObject();
            string sql = @"	SELECT DISTINCT
                            a.ApplyCode
                            ,a.Status
                            ,a.CreatePerson
                            ,a.CreateDateTime
                            ,a.MUSER
                            ,a.MUSERName 
                            ,a.WHCode 
							,case when c.Quantity=Isnull(c.IssueQuantity,0)	then '1'  else '2' END as FLStatus
							from dbo.ICSMOApply a 
							INNER JOIN (SELECT Sum(a.Quantity)	AS Quantity,
														Sum(Isnull(a.IssueQuantity,0)) AS IssueQuantity,a.ApplyCode,a.WorkPoint FROM ICSMOApply a GROUP BY a.ApplyCode,a.WorkPoint ) c ON a.ApplyCode=c.ApplyCode AND a.WorkPoint=c.workpoint
							left join  ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint 

                           where 1=1 ";

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
                {
                    sql += " and a.CreatePerson like '%" + queryParam["CreatedBy"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["Status"].ToString()))
                {
                    if (queryParam["Status"].ToString()=="1") 
                    { 
                        sql += " and c.Quantity=Isnull(c.IssueQuantity,0) ";
                    } 
                    else if(queryParam["Status"].ToString() == "2") 
                    {
                        sql += " and c.Quantity!=Isnull(c.IssueQuantity,0) and Status='1' ";
                    }
                    else if (queryParam["Status"].ToString() == "3")
                    {
                        sql += " and c.Quantity!=Isnull(c.IssueQuantity,0) and Status='2' ";
                    }

                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["WHCode"].ToString()))
                {
                    sql += " and a.WHCode like '%" + queryParam["WHCode"].ToString() + "%' ";
                }
            }
            sql = string.Format(sql);
            ParentId = SqlHelper.Organize_F_ParentId(NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode);
            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            if (!string.IsNullOrWhiteSpace(ParentId) && ParentId != "0" && NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "Vendor")
            {
                return SqlHelper.FindTablePageBySql_OtherTempByRole(sql.ToString(), sql, ParentId, UserCode, parameter.ToArray(),"", ref jqgridparam);
            }
            else
            {
                //if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode!="admin")
                //{
                //    sql += " and a.CreatePerson like '%" + NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode + "%' ";
                //}
                return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            }
     
        }
        #endregion

        #region 辅料材料出库单主表信息查询
        public DataTable GetICSMOApplyByApplyCode(string ApplyCode)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	SELECT DISTINCT 
                            a.ApplyCode
                            ,a.Status
                            ,a.CreatePerson
                            ,a.CreateDateTime
                            ,a.MUSER
                            ,a.MUSERName 
                            ,a.SourceCode
							from dbo.ICSMOApply a 
							left join  ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
                            WHERE  a.ApplyCode='{0}'";
            sql = string.Format(sql, ApplyCode);
            return Repository().FindTableBySql(sql.ToString());
        }
        #endregion
        #region 主页面子表信息查询
        public DataTable GetICSMOApplyDetail(string ApplyCode, string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	select  a.ID
                            ,a.ApplyCode
                            ,a.Sequence
                            ,a.InvCode
							,b.InvName
							,b.InvStd
                            ,a.Quantity
                            ,a.Amount
                            ,a.ExtensionID
                            ,a.MUSER
                            ,a.MUSERName
                            ,a.MTIME 
                            ,a.WHCode
                            ,c.WarehouseName as WHName
                            ,a.SourceCode
                            ,IssueQuantity
                            ,f.Colspan
                            ,f.ProjectCode	
                            ,f.BatchCode	
                            ,f.Version	
                            ,f.Brand
                            ,f.cFree1	
                            ,f.cFree2	
                            ,f.cFree3	
                            ,f.cFree4	
                            ,f.cFree5
                            ,f.cFree6	
                            ,f.cFree7
                            ,f.cFree8	
                            ,f.cFree9
                            ,f.cFree10
                            from ICSMOApply a
							LEFT JOIN ICSInventory b ON a.InvCode=b.InvCode AND  a.WorkPoint=b.WorkPoint
	                        left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
                            left join  ICSWarehouse c On c.WarehouseCode= a.WHCode AND c.WorkPoint=a.WorkPoint
                            WHERE a.ApplyCode='" + ApplyCode + "' ";
            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["POCode"].ToString()))
                {
                    sql += " and a.ApplyCode like '%" + queryParam["POCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["InvCode"].ToString()))
                {
                    sql += " and a.InvCode like '%" + queryParam["InvCode"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["CreatedBy"].ToString()))
                {
                    sql += " and a.CreatePerson like '%" + queryParam["CreatedBy"].ToString() + "%' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeFrom"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) >= '" + queryParam["TimeFrom"].ToString() + "' ";
                }
                if (!string.IsNullOrWhiteSpace(queryParam["TimeArrive"].ToString()))
                {
                    sql += " and convert(nvarchar(20),a.CreateDateTime,23) <= '" + queryParam["TimeArrive"].ToString() + "' ";
                }
            }

            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        #endregion

        public string DeleteICSMOApplyByCode(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;

            sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
                                BEGIN
                                 RAISERROR('该单据已发料，不能删除！',16,1);
                                    RETURN
                                END;
                     IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND Status='2')
                                BEGIN
                                 RAISERROR('该单据已审核，不能删除！',16,1);
                                    RETURN
                                END;
                   DELETE FROM dbo.ICSMOApply  WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
           
            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        /// <summary>
        /// 审核单据
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string ApproveICSMOApplyByCode(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;

            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;

            sql += string.Format(@"IF  EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND status!='1')
                                BEGIN
                                 RAISERROR('该单据的状态不是开立状态，不能审核！',16,1);
                                    RETURN
                                END
                             UPDATE dbo.ICSMOApply SET Status='2', EATTRIBUTE12='{2}',EATTRIBUTE13='{3}',EATTRIBUTE14='{4}'  WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint,UserCode,UserName,DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }

            }
            catch (Exception ex)
            {
                msg=ex.Message;

            }  
            return msg; 
        }

        /// <summary>
        /// 弃审单据
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string AbandonICSMOApplyByCode(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;

            sql += string.Format(@"IF  EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND status!='2')
                                BEGIN
                                 RAISERROR('该单据的状态不是审核状态，不能弃审!',16,1);
                                    RETURN
                                END
                                  IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
                                BEGIN
                                 RAISERROR('该单据已发料，不能删除！',16,1);
                                    RETURN
                                END
                             UPDATE dbo.ICSMOApply SET Status='1' WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);

            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }

            }
            catch (Exception ex)
            {
                msg = ex.Message;

            }
            return msg;
        }
        /// <summary>
        /// 发料
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string ICSMOApplyByCodeFL(string ID, string Type, string Mechanism, string InvCode)
        {
            string msg = string.Empty;
            try
            {
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
                string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                string UserCode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                List<LOTStockModel> lOTStockModels = new List<LOTStockModel>();
                ID = ID.TrimEnd(',');
                //单据先进行拣料
                DataTable dateTable = App.GetICSMOPickMergeTemp(ID, Type, Mechanism, InvCode);
                DataRow[] dateRows = dateTable?.Select($@"LotNo <> '' And LotNo is not null");
                decimal ToltalCount = 0.0M;
                string ZDsql = $@" SELECT  Sum(Quantity) AS Quantity  FROM ICSMOApply WHERE ApplyCode in({ID}) and WorkPoint='{WorkPoint}' ";
                 var dt=SqlHelper.CmdExecuteDataTable(ZDsql);
                if (dateRows == null || dateRows.Length <= 0)
                {
                    msg = "库存不足，请先检查库存";
                }
                else
                {
                    string docCode = string.Empty;
                    string docCodeAfter = string.Empty;
                    LOTStockModel lOTStockModel = null;

                    // 使用 LINQ 根据 Code 字段对 DataRow[] 进行分组  
                    var groups = dateRows.Cast<DataRow>().GroupBy(row => new {
                        Code = row.Field<string>("Code"),
                        Sequence = row.Field<string>("Sequence"),
                        iQuantity = row.Field<string>("iQuantity")
                    });

                    foreach (var group in groups)
                    {
                        var firstRow = group.First();
                        var code = firstRow["Code"];
                        decimal count = 0.0M;
                        lOTStockModel = new LOTStockModel
                        {
                            TransCode = firstRow["Code"].ToString(),
                            TransSequence = firstRow["Sequence"].ToString(),
                            Amount = "0",
                            User = UserCode,
                            MTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
                            WorkPoint = WorkPoint,
                            Detail = new List<LOTStockModelList>(),
                        };

                        foreach (var row in group)
                        {
                            count += row["QTY"].ToDecimal();
                            LOTStockModelList lOTStockModelList = new LOTStockModelList
                            {

                                TransSequence = firstRow["Sequence"].ToString(),
                                CurrentQuantity = row["QTY"].ToString(),
                                LotNo = row["LotNo"].ToString(),
                                CurrentAmount = "0",
                                Sequence = row["Sequence"].ToString(),
                                WarehouseCode = row["WarehouseCode"].ToString(),
                                LocationCode = row["LocationCode"].ToString()
                            };
                            lOTStockModel.Detail.Add(lOTStockModelList);
                        }
                        lOTStockModel.Quantity = count.ToString();
                        lOTStockModels.Add(lOTStockModel);
                        ToltalCount += count;
                    }
                    if (ToltalCount != dt.Rows[0]["Quantity"].ToDecimal()) 
                    {
                        msg = "库存不足，请先检查库存";
                        return msg;
                    }
                   
                    string Inputstr = JsonConvert.SerializeObject(lOTStockModels);

                    string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "LOTStockDown/Create";
                    string result = HttpPost(APIURL, Inputstr);
                    JObject Obj = (JObject)JsonConvert.DeserializeObject(result);//或者JObject jo = JObject.Parse(jsonText);
                    string MessAge = Obj["Message"].ToString();
                    string Success = Obj["Success"].ToString();

                    if (Success.ToUpper() == "FALSE")
                    {
                        msg = MessAge;
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;

            }
            return msg;
        }
        #region 获取物料信息
        public DataTable GetItemList(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd

 FROM ICSInventory a 
 LEFT JOIN
(SELECT Sum(ISnull(Quantity,0)) as Quantity,WorkPoint,INVCode FROM (SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut GROUP BY WorkPoint,INVCode

UNION ALL

SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOApply GROUP BY WorkPoint,INVCode

UNION ALL

SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN where EATTRIBUTE10='销售出库' GROUP BY WorkPoint,INVCode 

UNION ALL
SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue
			GROUP BY WorkPoint,INVCode)d
GROUP BY d.INVCode,d.WorkPoint) b
 ON a.InvCode=b.InvCode  AND a.WorkPoint=b.WorkPoint 
left JOIN( SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM  ICSWareHouseLotInfo where  Freeze<>1 GROUP BY InvCode, WorkPoint ) c ON c.InvCode=a.INVCode AND c.WorkPoint=a.WorkPoint
where 1=1";
            if (!string.IsNullOrEmpty(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["INVCODE"].ToString()))
                    sql += " and  a.INVCODE like '%" + queryParam["INVCODE"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["INVNAME"].ToString()))
                    sql += " and  a.INVNAME like '%" + queryParam["INVNAME"].ToString() + "%'";
            }
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            sql += " and  a.WorkPoint=('" + WorkPoint + "') ";

            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }
        #endregion

        public DataTable GetItemListCount(string queryJson, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.INVCODE,a.INVNAME,ISnull(c.Quantity,0)-ISnull(b.Quantity,0) + ISnull(d.Quantity,0) AS Quantity,a.EATTRIBUTE2,a.InvStd

 FROM ICSInventory a 
 LEFT JOIN
(SELECT Sum(ISnull(Quantity,0)) as Quantity,WorkPoint,INVCode FROM (SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(OutQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSOtherOut GROUP BY WorkPoint,INVCode

UNION ALL

SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSMOIssue GROUP BY WorkPoint,INVCode

UNION ALL

SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(SDNQuantity, 0)),0) as Quantity,WorkPoint,INVCode FROM ICSSDN GROUP BY WorkPoint,INVCode )d
GROUP BY d.INVCode,d.WorkPoint
UNION ALL

SELECT ISNUll(sum(ISNULL(Quantity,0))-sum(ISNULL(IssueQuantity, 0)),0) as Quantity,WorkPoint,INVCode from ICSMOIssue
			GROUP BY WorkPoint,INVCode ) b
 ON a.InvCode=b.InvCode  AND a.WorkPoint=b.WorkPoint 
left JOIN( SELECT Sum(ISnull(Quantity,0)) AS Quantity,InvCode, WorkPoint FROM  ICSWareHouseLotInfo where  Freeze<>1 GROUP BY InvCode, WorkPoint) c ON c.InvCode=a.INVCode AND c.WorkPoint=a.WorkPoint
left join ICSMOIssue d ON d.InvCode=a.InvCode AND a.WorkPoint=d.WorkPoint  
where 1=1";
            if (!string.IsNullOrEmpty(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["INVCODE"].ToString()))
                    sql += " and  a.INVCODE like '%" + queryParam["INVCODE"].ToString() + "%'";
                if (!string.IsNullOrWhiteSpace(queryParam["ApplyCode"].ToString()))
                    sql += " and  d.ApplyCode like '%" + queryParam["ApplyCode"].ToString() + "%'";
            }
            //string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            sql += " and  a.WorkPoint=('" + WorkPoint + "') ";

            return SqlHelper.CmdExecuteDataTable(sql);
        }
        #endregion

        #region
        public decimal GetInvCodeCount(string invCode,string whCode,string batchCode)
        {

            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = $@"EXEC Proc_GetLotStorageQty '{WorkPoint}','{invCode}','{whCode}','{batchCode}'";
            var dt= SqlHelper.CmdExecuteDataTable(sql);
            return dt.Rows[0]["Quantity"].ToDecimal();
        }
        #endregion
        #region 获取领料部门
        public DataTable GetLYDep()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string sql = @"SELECT b.F_ItemName as Name,b.F_ItemCode as Code FROM [dbo].[Sys_SRM_Items] a 
                              INNER JOIN Sys_SRM_ItemsDetail b ON b.F_ItemId = a.F_Id
                              WHERE F_EnCode = 'LYDep'";
            return SqlHelper.CmdExecuteDataTable(sql);

        }
        #endregion
        #region 获取单据类型
        /// <summary>
        /// 获取U9单据类型
        /// </summary>
        /// <returns></returns>
        public string GetU9CodeType(string type)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string result=String.Empty;
            try
            {
                TypeModel model = new TypeModel
                {
                    TypeName = type,
                    OrgCode = WorkPoint
                };
                string Inputstr = JsonConvert.SerializeObject(model);

                string APIURL = ConfigurationManager.ConnectionStrings["ERPAPIURL"].ConnectionString + "GetU9DocType ";
                result = HttpPost(APIURL, Inputstr); 
            }
            catch (Exception ex)
            {
                result = ex.Message;

            }
            return result;
        }
        #endregion
        /// <summary>
        /// 关闭单据
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        public string CloseICSMOApplyByCode(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;

            sql += string.Format(@"IF EXISTS( select ID from ICSMOApply where ApplyCode IN ({0}) and WorkPoint ='{1}' AND IssueQuantity>0)
                                BEGIN
                                 RAISERROR('该单据已发料，不能关闭！',16,1);
                                    RETURN
                                END
UPDATE dbo.ICSMOApply SET Status='3' WHERE ApplyCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);

            try
            {
                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                msg=ex.Message;
            }
            return msg;
        }
        /// <summary>
        /// 获取单号
        /// </summary>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public string GetBidCode(string WorkPoint)
        {
            WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string BidCide = string.Empty;
            if (!string.IsNullOrEmpty(WorkPoint))
            {
                string DateString = DateTime.Now.ToString("yyyyMMdd");
               
                string Pre = "CLCK" + DateString;
                BidCide = GetSerialCode(WorkPoint, "ICSMOApply", "ApplyCode", Pre, 4);
            }
            if (!string.IsNullOrWhiteSpace(BidCide))
            {
                string sqlISHave = @"SELECT ApplyCode FROM ICSMOApply a 
                              WHERE a.ApplyCode = '{0}'";
                sqlISHave = string.Format(sqlISHave, BidCide);
                DataTable dtIsHave = SqlHelper.GetDataTableBySql(sqlISHave);
                if (dtIsHave.Rows.Count > 0)
                {
                    throw new Exception("单号已存在！");
                }
            }
            return BidCide;
        }
        public string GetSerialCode(string workPointCode, string tbName, string colName, string Pre, int numLen)
        {
            string sql = "EXEC Addins_GetSerialCode '{0}','{1}','{2}','{3}',{4}";
            sql = string.Format(sql, new object[] { workPointCode, tbName, colName, Pre, numLen });
            return SqlHelper.ExecuteScalar(sql).ToString();
        }

        /// <summary>
        /// 修改单据
        /// </summary>
        /// <param name="ICSASN"></param>
        /// <returns></returns>
        public string UpdateICSMOApply(string ICSASN)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            string UserName = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            try
            {
                // 获取解析后的数据
                JsonData data = JsonConvert.DeserializeObject<JsonData>(ICSASN);
                string sql = $@"IF EXISTS( select ID from ICSMOApply where  ApplyCode = '{data.Code}' and WorkPoint ='{WorkPoint}' AND Status='2')
                                BEGIN
                                 RAISERROR('该单据已审核，不能修改！',16,1);
                                    RETURN
                                END;
                     DECLARE  @CreatedCode VARCHAR(50);
                     DECLARE  @CreatedName VARCHAR(50);
                     SET @CreatedCode =(SELECT top 1 MUSER from ICSMOApply where  ApplyCode = '{data.Code}' and  WorkPoint ='{WorkPoint}');
                     SET @CreatedName=(SELECT top 1 MUSERName from ICSMOApply where  ApplyCode = '{data.Code}'  and WorkPoint ='{WorkPoint}') ;               
                  delete from ICSMOApply where ApplyCode = '{data.Code}'; ";
                string str1 = "";
                List<string> ExtensionIDList = new List<string>();

                foreach (var detail in data.Detail)
                {
                    string Colspan = "~" + detail.Batch + "~~~~~~~~~~~~";
                    string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                    object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                    bool flag = true;
                    foreach (var item in ExtensionIDList)
                    {
                        if (item == Colspan + WorkPoint)
                        {
                            flag = false;
                        }
                    }
                    if (ExtensionID == null && flag == true)
                    {
                        str1 = Guid.NewGuid().ToString();
                        sql += string.Format(@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),'{16}','{17}','{18}' )",
                            str1, Colspan, "", detail.Batch, "", "", "", "", "", "", "", "", "", "", "", "",
                            data.User, UserName, WorkPoint);
                    }
                    else if (ExtensionID != null)
                    {
                        str1 = ExtensionID.ToString();
                    }
                    ExtensionIDList.Add(Colspan + WorkPoint);

                    sql += "INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode,EATTRIBUTE11)" +
                    "                          select  '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}',newID(),newID(),'{10}','{11}',{12},{13},'{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','1','{23}','{24}',0,'{25}','{26}'";
                    sql = string.Format(sql, detail.ID, data.Code, detail.WHCode, detail.Sequence, detail.InvCode, detail.Quantity, data.User, data.MTIME, WorkPoint, '1', str1, "@CreatedCode", "@CreatedName", data.MTIME, data.Dept, data.CodeType, data.ApplyNegCode, data.Custmer, data.Remarks, detail.DetailReamrk, detail.SYproject, "杂发", detail.SourceCode, data.Remarks2, detail.DetailReamrk2, data.LYDept, data.Remarks3);
                }

                if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
                {

                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            return msg;
        }
        public DataTable GetICSReturnTemporary(string rfqno)
        {
            string sql = @"SELECT
                        a.ID,
                        a.ApplyCode,
                        a.InvCode,
                        b.InvName as INVNAME,
                        b.InvStd,
                        a.Quantity,
                        a.SourceCode,
                        a.WHCode,
                        a.WHCode as WHCodeHHH,
                        a.EATTRIBUTE6,
                        a.EATTRIBUTE9,
                        a.SourceCode, 
                        a.Quantity
            	        ,f.BatchCode as Batch	
                        ,c.WarehouseName as WHCodeName	
                        ,a.EATTRIBUTE7 as SYproject,
                        a.EATTRIBUTE11 ,
                        a.DepCode
                        FROM ICSMOApply a
                        LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode 
	                    left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
                        LEFT JOIN dbo.ICSWarehouse c ON a.WHCode=c.WarehouseCode AND a.WorkPoint=c.WorkPoint
                        where a.ApplyCode='" + rfqno + "'";
            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
            DataTable dtCloned = table.Clone();
            foreach (DataColumn col in dtCloned.Columns)
            {
                col.DataType = typeof(string);
            }
            foreach (DataRow row in table.Rows)
            {
                DataRow newrow = dtCloned.NewRow();
                foreach (DataColumn column in dtCloned.Columns)
                {
                    newrow[column.ColumnName] = row[column.ColumnName].ToString();

                }
                dtCloned.Rows.Add(newrow);
            }
            if (Invmes.Rows.Count > 0)
            {
                dtCloned.Merge(Invmes, false);
            }
            return dtCloned;
        }

        //拣料
        public DataTable GetICSMOPickMergeTemp(string ID, string Type, string Mechanism, string InvCode)
        {

            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            ID = string.IsNullOrWhiteSpace(ID) ? "''" : ID.TrimEnd(',');

            string sqls = "";
            string wheresql = "";
            #region 各单据sql
            string Code = SqlHelper.GetItemsDetailEnabledMark("MtimeControl");
            if (Type == "2")
            {
                if (!string.IsNullOrWhiteSpace(InvCode))
                {
                    wheresql = " and a.InvCode = '" + InvCode + "' ";
                }
                sqls = @"select
	                        a.ApplyCode as Code,a.Sequence, a.InvCode, b.InvName as INVNAME, b.InvStd, b.InvUnit,a.Quantity, (SUM(ISNULL(a.Quantity, 0))-SUM(ISNULL(a.IssueQuantity, 0))) AS iQuantity, a.WhCode,a.ExtensionID,
				f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10
                        FROM
	                        ICSMOApply a
                        LEFT JOIN ICSINVENTORY b ON a.InvCode = b.InvCode AND a.WorkPoint=b.WorkPoint
 LEFT JOIN  ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint
                        WHERE a.ApplyCode in ({0}) AND a.WorkPoint = '{1}' AND ISNULL(a.Quantity, 0)>ISNULL(a.IssueQuantity, 0)
                        GROUP BY a.ApplyCode,a.InvCode,b.InvName,b.InvStd,b.InvUnit,a.WhCode,a.ExtensionID,a.Sequence,f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10";
            }
          

            #endregion

            sqls = string.Format(sqls, ID, WorkPoint);

            string sql = $@"SELECT row_number() over ( order by c.Code,c.InvCode,d.MTIME,d.LotNO) AS rowNo,
	                        c.Code,
                            c.Sequence,
	                        c.InvCode,
	                        c.InvName as INVNAME,
	                        c.InvStd,
                            c.InvUnit,
	                        c.iQuantity AS iQuantity,
	                        c.WHCode AS WarehouseCode, 
	                        d.LotNO,
                            ISNULL(d.Quantity, 0) AS QTY,
                            ISNULL(d.Quantity, 0) AS QTYLeft,
                            CONVERT(decimal(18,6),0) AS SendQTY,
                            d.LocationCode AS LocationCode,
	                        CONVERT(varchar(100),d.MTIME, 23) MTIME,
	                        f.QTYTotal QTYTotal,c.ExtensionID
                        FROM
                        ({sqls}) c
                        left join (select d.WarehouseCode,d.Quantity-d.LockQuantity as Quantity,d.INVCode, m.LotNo, d.MTIME,d.inDate,d.LocationCode,m.ExtensionID ,m.ProductDate,m.ExpirationDate,
	f.ProjectCode	,f.BatchCode	,f.Version	,f.Brand	,f.cFree1	,f.cFree2	,f.cFree3	,f.cFree4	
,f.cFree5	,f.cFree6	,f.cFree7	,f.cFree8	,f.cFree9	,f.cFree10
                                     from ICSWareHouseLotInfo d ,ICSInventoryLot m 
	LEFT JOIN  ICSExtension f on m.ExtensionID=f.ID and m.WorkPoint=f.WorkPoint
					                where d.LotNo=m.LotNo and d.WorkPoint=m.WorkPoint
								      and d.WorkPoint='{WorkPoint}' AND d.Quantity-d.LockQuantity>0 AND d.Quantity>0
                                   
                             ) d 
                               on c.InvCode=d.INVCode 
	                    AND (LEN(ISNULL(c.WHCode,''))<=0 OR (LEN(ISNULL(c.WHCode,''))>0 AND c.WHCode=d.WarehouseCode))
						AND (LEN(ISNULL(c.ProjectCode,''))<=0 OR (LEN(ISNULL(c.ProjectCode,''))>0 AND c.ProjectCode=d.ProjectCode))
						AND (LEN(ISNULL(c.BatchCode,''))<=0 OR (LEN(ISNULL(c.BatchCode,''))>0 AND c.BatchCode=d.BatchCode))
						AND (LEN(ISNULL(c.Version,''))<=0 OR (LEN(ISNULL(c.Version,''))>0 AND c.Version=d.Version))
						AND (LEN(ISNULL(c.Brand,''))<=0 OR (LEN(ISNULL(c.Brand,''))>0 AND c.Brand=d.Brand))
						AND (LEN(ISNULL(c.cFree1,''))<=0 OR (LEN(ISNULL(c.cFree1,''))>0 AND c.cFree1=d.cFree1))
						AND (LEN(ISNULL(c.cFree2,''))<=0 OR (LEN(ISNULL(c.cFree2,''))>0 AND c.cFree2=d.cFree2))
						AND (LEN(ISNULL(c.cFree3,''))<=0 OR (LEN(ISNULL(c.cFree3,''))>0 AND c.cFree3=d.cFree3))
						AND (LEN(ISNULL(c.cFree4,''))<=0 OR (LEN(ISNULL(c.cFree4,''))>0 AND c.cFree4=d.cFree4))
						AND (LEN(ISNULL(c.cFree5,''))<=0 OR (LEN(ISNULL(c.cFree5,''))>0 AND c.cFree5=d.cFree5))
						AND (LEN(ISNULL(c.cFree6,''))<=0 OR (LEN(ISNULL(c.cFree6,''))>0 AND c.cFree6=d.cFree6))
						AND (LEN(ISNULL(c.cFree7,''))<=0 OR (LEN(ISNULL(c.cFree7,''))>0 AND c.cFree7=d.cFree7))
						AND (LEN(ISNULL(c.cFree8,''))<=0 OR (LEN(ISNULL(c.cFree8,''))>0 AND c.cFree8=d.cFree8))
						AND (LEN(ISNULL(c.cFree9,''))<=0 OR (LEN(ISNULL(c.cFree9,''))>0 AND c.cFree9=d.cFree9))
						AND (LEN(ISNULL(c.cFree10,''))<=0 OR (LEN(ISNULL(c.cFree10,''))>0 AND c.cFree10=d.cFree10))
                         LEFT JOIN (SELECT INVCode,WarehouseCode,SUM(Quantity-LockQuantity) AS QTYTotal FROM ICSWareHouseLotInfo  WHERE WorkPoint='{WorkPoint}' GROUP BY INVCode,WarehouseCode) f ON c.InvCode=f.INVCode AND c.WhCode=f.WarehouseCode
                     ";
            // sql = string.Format(sql, sqls)1;
            if (Code == "MC00002")
            {
                sql += "order by d.inDate";
            }
            else if (Code == "MC00001")
            {
                sql += "   order BY ISNULL(d.ProductDate,d.ExpirationDate)";
            }
            else
            {
                sql += "   order BY ISNULL(d.ExpirationDate,d.ProductDate)";
            }
            var dataset = Repository().FindDataSetBySql(sql);
            if (dataset.Tables[0].Rows.Count == 0)
                return null;
            DataTable table = dataset.Tables[0];
            decimal qtyCount = 0;
            bool remove = false;
            List<int> removeList = new List<int>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                if (i != 0
                 && (!table.Rows[i]["InvCode"].ToString().Equals(table.Rows[i - 1]["InvCode"].ToString())
                  || !table.Rows[i]["Sequence"].ToString().Equals(table.Rows[i - 1]["Sequence"].ToString())
                  || !table.Rows[i]["Code"].ToString().Equals(table.Rows[i - 1]["Code"].ToString())
                  || !table.Rows[i]["ExtensionID"].ToString().Equals(table.Rows[i - 1]["ExtensionID"].ToString()))
                 )
                {
                    qtyCount = 0;
                    remove = false;
                }
                if (table.Rows[i]["QTYLeft"].ToString().ToDecimal() == 0 && table.Rows[i]["LotNO"].ToString() != "")
                {
                    table.Rows[i]["LotNO"] = "";
                    string a = "0.000000";
                    table.Rows[i]["QTY"] = Convert.ToDecimal(a);
                    //removeList.Add(i);
                    continue;
                }
                if (remove && table.Rows[i]["LotNO"].ToString() != "")
                {
                    removeList.Add(i);
                }
                else
                {
                    var lotQty = table.Rows[i]["QTYLeft"].ToString().ToDecimal();
                    var orderQty = table.Rows[i]["iQuantity"].ToString().ToDecimal();
                    qtyCount += lotQty;
                    foreach (DataRow dr in table.Rows)
                    {
                        if (dr["LotNO"].ToString() == table.Rows[i]["LotNO"].ToString())
                        {
                            if (qtyCount > orderQty)
                            {
                                dr["QTYLeft"] = Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty);
                            }
                            else
                            {
                                dr["QTYLeft"] = 0;
                            }
                        }
                    }
                    if (qtyCount >= orderQty)
                    {
                        table.Rows[i]["SendQTY"] = Convert.ToDecimal(lotQty) - (Convert.ToDecimal(qtyCount) - Convert.ToDecimal(orderQty));
                        remove = true;
                    }
                    else
                    {
                        table.Rows[i]["SendQTY"] = lotQty;
                    }
                }
            }
            if (removeList.Count > 0)
            {
                removeList.Reverse();
                foreach (var item in removeList)
                {
                    table.Rows.RemoveAt(item);
                }
            }
            try
            {
                #region 旧卡控
                
                #endregion
                DataRow[] dss = table.Select("LotNO=''");
                foreach (var item in dss)
                {
                    DataRow[] dsss = table.Select("Code='" + item["Code"].ToString() + "' and Sequence='" + item["Sequence"].ToString() + "' and LotNO <> '' ");
                    if (dsss != null && dsss.Length > 0)
                    {
                        table.Rows.Remove(item);
                    }
                }
                var result = App.ConvertCellToString(table);
                if (Invmes.Rows.Count > 0)
                {
                    result.Merge(Invmes, false);
                }
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        //接口api解析
        public static string HttpPost(string url, string body)

        {
            try
            {
                Encoding encoding = Encoding.UTF8;
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
                request.Method = "POST";
                request.Accept = "application/json, text/javascript, */*"; //"text/html, application/xhtml+xml, */*";
                request.ContentType = "application/json; charset=utf-8";

                byte[] buffer = encoding.GetBytes(body);
                request.ContentLength = buffer.Length;
                request.GetRequestStream().Write(buffer, 0, buffer.Length);
                HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                using (StreamReader reader = new StreamReader(response.GetResponseStream(), encoding))
                {
                    return reader.ReadToEnd();
                }
            }
            catch (WebException ex)
            {
                throw new Exception(ex.Message);
            }
        }

        //深拷贝
        #region
        public static T DeepCopyByReflect<T>(T obj)
        {
            if (obj == null)
                return obj;

            var type = obj.GetType();
            //如果是字符串或值类型则直接返回
            if (obj is string || type.IsValueType) return obj;
            if(type.IsArray)
            {
                var elementType = Type.GetType(type.FullName.Replace("[]", null));
                var array = obj as Array;
                var copied = Array.CreateInstance(elementType, array.Length);
                for (int idx = 0; idx < copied.Length; idx++)
                {
                    copied.SetValue(DeepCopyByReflect(array.GetValue(idx)), idx);
                }
                return (T)Convert.ChangeType(copied,type);
            }


            object retval = Activator.CreateInstance(obj.GetType());
            FieldInfo[] fields = obj.GetType().GetFields(BindingFlags.Public | BindingFlags.NonPublic |BindingFlags.Instance | BindingFlags.Static);
            foreach (FieldInfo field in fields)
            {
                try { field.SetValue(retval, DeepCopyByReflect(field.GetValue(obj))); }
                catch { }
            }
            return (T)retval;
        }
        #endregion

        #region

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="savePath"></param>
        /// <param name="Year"></param>
        /// <returns></returns>
        public string SetData_PR(String savePath, string Year)
        {
            //数据获取
            try
            {
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                SqlConnection conn = SqlHelper.GetDataCenterConn();
                DataTable data = FileToExcel.ExcelToTable(savePath);

                int index = 1;
                string Tday = DateTime.Now.ToString("yyyyMMdd");
                string sql = string.Empty;
                string[] nameString = {"单据类型名称","受益部门名称","料品编码","数量"};
                //获取单据类型
                string jsonstring=GetU9CodeType("杂发");
                //获取项目信息
                string jsonProject = GetU9CodeType("项目");
                //获取部门信息
                string jsonDep = GetU9CodeType("部门");
                var docType= JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonstring);
                var projectType = JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonProject);
                var depType = JsonConvert.DeserializeObject<List<DocTypeModel>>(jsonDep);
                var LYDepType = JsonConvert.DeserializeObject <List<DocTypeModel>>(GetLYDep().ToJson());
                string applyCode = GetBidCode(WorkPoint);
                Pagination pagination=new Pagination();
                pagination.rows = 20;
                pagination.sidx = "INVCODE";
                pagination.sord = "desc";
                pagination.page = 1;
                if (data != null && data.Rows.Count > 0)
                {
                    
                    foreach (DataRow dr in data.Rows)
                    {
                        index++;
                        for(int i=0;i< nameString.Length;i++)
                        {
                            if (string.IsNullOrWhiteSpace(dr[i].ToString())) 
                            {
                                throw new Exception($"第{index}行，{nameString[i]}不能为空！");
                            }
                        }
                        string queryJson="{\"INVCODE\":\""+ dr["料品编码"].ToString() + "\",\"ApplyCode\":\"\"}";
                        DataTable ListData = GetItemListCount(queryJson, ref pagination);
                        decimal count = ListData.Rows[0]["Quantity"].ToDecimal();
                        string DocTypeName = dr["单据类型名称"].ToString();
                        string SYDep=string.Empty;
                        string SYDepName = dr["受益部门名称"].ToString();
                        string InvCode = dr["料品编码"].ToString();
                        string Count = dr["数量"].ToString();
                        string ZJProject = dr["在建工程项目"].ToString();
                        string CusCode = dr["客户编码"].ToString();
                        string RDCode = dr["RD编码"].ToString();
                        string ProjectCode = dr["项目编码"].ToString();
                        string LYDep = string.Empty;
                        string LYDepName = dr["领用部门名称"].ToString();
                        string WHCode = dr["仓库编码"].ToString();
                        string BatchCode = dr["批号"].ToString();
                        string SYProject = string.Empty;
                        string SYProjectName = dr["受益项目名称"].ToString();
                        string MOCode = dr["工单"].ToString();
                        string ReturnCount = dr["回收数量"].ToString();
                        string Ramark = dr["备注"].ToString();
                        string str1=string.Empty;
                        //单据类型
                        List<DocTypeModel> DocTypeModel = docType.Where(e => e.Name == DocTypeName).ToList();
                        if (DocTypeModel.IsEmpty())
                        {
                            throw new Exception($"第{index}行，单据类型不存在！");
                        } 
                        string DocType = DocTypeModel[0].Code;
                        //受益部门
                        if (!string.IsNullOrEmpty(SYDepName))
                        {
                            List<DocTypeModel> depTypeModel = depType.Where(e => e.Name == SYDepName).ToList();
                            if (depTypeModel.IsEmpty())
                            {
                                throw new Exception($"第{index}行，受益部门不存在！");
                            }
                             SYDep = depTypeModel[0].Code;
                        }
                        //领用部门
                        if (!string.IsNullOrEmpty(LYDepName))
                        {
                            List<DocTypeModel> LYDepModel = LYDepType.Where(e => e.Name == LYDepName).ToList();
                            if (LYDepModel.IsEmpty())
                            {
                                throw new Exception($"第{index}行，领用部门不存在！");
                            }
                            LYDep = LYDepModel[0].Code;
                        }
                        //受益项目
                        if (!string.IsNullOrEmpty(SYProjectName))
                        {
                            List<DocTypeModel> SYprojectModel = projectType.Where(e => e.Name == SYProjectName).ToList();
                            if (SYprojectModel.IsEmpty())
                            {
                                throw new Exception($"第{index}行，受益项目不存在！");
                            }
                            SYProject = SYprojectModel[0].Code;
                        }

                        if (count< Count.ToDecimal()) 
                        {
                            throw new Exception($"第{index}行，物料编码：{InvCode}，创建单据数量不能超过库存数量！");
                        }
                        
                        if (!string.IsNullOrWhiteSpace(dr["批号"].ToString()))
                        {
                            var Colspan = "~" + dr["批号"].ToString() + "~~~~~~~~~~~~";
                            string sqls = string.Format(@"select ID from ICSExtension where Colspan='{0}' and WorkPoint='{1}'", Colspan, WorkPoint);
                            object ExtensionID = SqlHelper.ExecuteScalar(sqls);
                            if (ExtensionID == null)
                            {
                                str1 = Guid.NewGuid().ToString();
                                sql += $@"Insert into ICSExtension(ID, Colspan, ProjectCode, BatchCode, Version, Brand, MTIME, MUSER, MUSERName, WorkPoint) 
                              Values('{str1}','{Colspan}','','{dr["批号"]}','','',getdate(),'{MUSER}','{MUSERNAME}','{WorkPoint}')";
                                   
                            }
                            else
                            {
                                str1 = ExtensionID.ToString();
                            }
                        }

                        sql += $@"IF not EXISTS( SELECT ID FROM ICSInventory WHERE InvCode='{InvCode}' AND WorkPoint='{WorkPoint}')
                                BEGIN
                                 RAISERROR('第{index}行物料不存在，导入失败！',16,1);
                                    RETURN
                                END;
                                 INSERT INTO ICSMOApply(ID,ApplyCode,WHCode,Sequence,InvCode,Quantity,CreatePerson,CreateDateTime,WorkPoint,Status,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,EATTRIBUTE,EATTRIBUTE2,EATTRIBUTE3,EATTRIBUTE4,EATTRIBUTE5,EATTRIBUTE6,EATTRIBUTE7,EATTRIBUTE10,SourceCode,Type,EATTRIBUTE8,EATTRIBUTE9,IssueQuantity,DepCode)  Values(newID(),'{applyCode}','{WHCode}','{index}','{InvCode}','{Count}','{MUSER}','{Tday}','{WorkPoint}','1',newID(),newID(),'{str1}','{MUSER}','{MUSERNAME}','{Tday}','{SYDep}','{DocType}','{ZJProject}','{CusCode}','{RDCode}','{ReturnCount}','{SYProject}','杂发','{MOCode}','1','{ProjectCode}','{Ramark}',0,'{LYDep}');";



                    }

                    if (SqlHelper.CmdExecuteNonQueryLi(sql) <= 0)
                    {
                        return "导入失败，新增数据错误！";
                    }

                }
                else
                {
                    return "无有效的导入数据。";

                }
                return "true";

            }
            catch (Exception ex)
            {
                return ex.Message;

            }

        }
        #endregion

        // 定义一个类来表示 JSON 数据的结构
        public class JsonData
        {
            public string Dept { get; set; }
            public string LYDept { get; set; }
            public string CodeType { get; set; }
            public string ApplyNegCode { get; set; }
            public string Custmer { get; set; }
            public string Remarks { get; set; }
            public string Remarks2 { get; set; }
            public string Remarks3 { get; set; }
            public string Code { get; set; }
            public string MTIME { get; set; }
            public string User { get; set; }
          
            public List<JsonDetail> Detail { get; set; }
        }

        public class JsonDetail
        {
            public string ID;
            public int Sequence { get; set; }
            public string InvCode { get; set; }
            public decimal Quantity { get; set; }
            public string SYproject { get; set; }
            public string WHCode { get; set; }
            /// <summary>
            /// 工单号
            /// </summary>
            public string SourceCode { get; set; }
            public string DetailReamrk { get; set; }
            public string DetailReamrk2 { get; set; }
            public string Batch { get; set; }
        }
   
        /// <summary>
        /// 材料出库
        /// </summary>
        public class LOTStockModel
        {
            /// <summary>
            /// 源头单据号
            /// </summary>
            public string TransCode { get; set; }

            /// <summary>
            /// 源头单据行号
            /// </summary>
            public string TransSequence { get; set; }
            /// <summary>
            /// 数量
            /// </summary>
            public string Quantity { get; set; }
            /// <summary>
            /// 辅计量数量
            /// </summary>
            public string Amount { get; set; }
            /// <summary>
            /// 操作类型
            /// </summary>
            public string TransType { get; set; } = "生产发料-领料申请单";
            /// <summary>
            /// 操作人
            /// </summary>
            public string User { get; set; }
            /// <summary>
            /// 操作时间
            /// </summary>
            public string MTime { get; set; }


            public string WorkPoint { get; set; }
            public List<LOTStockModelList> Detail { get; set; }

        }
        public class LOTStockModelList
        {
            /// <summary>
            /// 源头单据行号
            /// </summary>
            public string TransSequence { get; set; }
            /// <summary>
            /// 条码
            /// </summary>
            public string LotNo { get; set; }
            /// <summary>
            /// 数量
            /// </summary>
            public string CurrentQuantity { get; set; }
            /// <summary>
            /// 辅计量数量
            /// </summary>
            public string CurrentAmount { get; set; }

            public string Sequence { get; set; }

            /// <summary>
            /// 仓库代码
            /// </summary>
            public string WarehouseCode { get; set; }
            /// <summary>
            /// 库位代码
            /// </summary>
            public string LocationCode { get; set; }
            /// <summary>
            /// LogID
            /// </summary>
            public string LogID { get; set; }
        }

        public class TypeModel
        {
            /// <summary>
            /// 名称
            /// </summary>
            public string TypeName { get; set; }
            /// <summary>
            /// 组织
            /// </summary>
            public string OrgCode { get; set; }
            
        }

        public class DocTypeModel
        {
            /// <summary>
            /// 名称
            /// </summary>
            public string Name { get; set; }
            /// <summary>
            /// 编码
            /// </summary>
            public string Code { get; set; }

        }

    }
}