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

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

namespace NFine.Application.WMS
{
   public  class ICSDisassemblyDocApp : RepositoryFactory<ICSVendor>
    {
        public DataTable GetGridJson(string queryJson, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            #region [SQL]
            string sql = @"
						select a.DABDOCType,
                        a.ExtensionID as ExtensionID,
                         a.DABDOCCode as Code,
                         a.Sequence as Seq,
                         a.Quantity as Qty,
                         a.InvCode,
                         a.WHCode,
                         b.InvName,
			             CASE WHEN c.TransSequence IS NOT NULL THEN '已选' ELSE '未选' END AS isInput,
						  CASE WHEN f.TransCode IS NOT NULL THEN '已生成' ELSE '未生成' END AS isCreate,e.*
                     from ICSDisassemblyDoc a 
                     left join ICSInventory  b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
                     left join (select TransCode,TransSequence,WorkPoint FROM ICSWareHouseLotInfoLog WHERE  BusinessCode='37' group by  TransCode,TransSequence,WorkPoint)c 
                     ON a.DABDOCCode=c.TransCode AND a.Sequence=c.TransSequence AND a.WorkPoint=c.WorkPoint
					 left join (select TransCode,WorkPoint FROM ICSInventoryLotDetail  group by  TransCode,WorkPoint)f 
                     ON a.DABDOCCode=f.TransCode AND a.WorkPoint=f.WorkPoint
                     LEFT JOIN ICSWarehouse d ON a.WHCode=d.WarehouseCode AND a.WorkPoint=d.WorkPoint
                     left join ICSExtension e on a.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
                     where a.DABDOCType='1'";
            
            sql = string.Format(sql, DbHelper.GetErpIp(), DbHelper.GetErpName());
            #endregion

            if (!string.IsNullOrWhiteSpace(queryJson))
            {
                if (!string.IsNullOrWhiteSpace(queryParam["DABDOCCode"].ToString()))
                {
                    sql += " and a.DABDOCCode like '%" + queryParam["DABDOCCode"].ToString() + "%' ";
                }
            }
            if (NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode != "admin")
            {
                sql += " and a.WorkPoint='" + NFine.Code.OperatorProvider.Provider.GetCurrent().Location + "'";
            }
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetSubGridJson(string Code)
        {
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            string sql = string.Empty;
            List<DbParameter> parameter = new List<DbParameter>();
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            sql = @"select 
				    a.Sequence,
				    a.DABDOCType,
				    a.DABDOCCode as Code,
				    a.Quantity as Qty,
				    a.InvCode,
				    a.WHCode,
				    e.BatchCode,
				    b.InvName,
				    f.LotNo
			    from ICSDisassemblyDoc a 
		        left join ICSInventory  b on a.InvCode=b.InvCode and a.WorkPoint=b.WorkPoint
			    left join (select TransCode,TransSequence,WorkPoint,LotNo FROM ICSInventoryLotDetail  group by  TransCode,TransSequence,WorkPoint,LotNo)f 
			    ON a.DABDOCCode=f.TransCode AND a.Sequence=f.TransSequence AND a.WorkPoint=f.WorkPoint
			    LEFT JOIN ICSWarehouse d ON a.WHCode=d.WarehouseCode AND a.WorkPoint=d.WorkPoint
			    left join ICSExtension e on a.ExtensionID=e.ID and a.WorkPoint=e.WorkPoint
			    where a.DABDOCType='2' and a.DABDOCCode='{0}'
			    order by a.Sequence";
            sql = string.Format(sql, Code, WorkPoint);
            return Repository().FindTableBySql(sql.ToString());
        }

        //查询符合套件的在库条码

        public DataTable GetLotNoByKit(string InvCode, string WHCode, string ExtensionID, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            DataTable table = new DataTable();
            //验证是否需要匹配批次信息
            string checksql = $"SELECT ID FROM ICSInventoryBatchEnable WHERE WorkPoint='{3}' AND BatchEnable='1' AND InvCode='{InvCode}' AND WHCode='{WorkPoint}'";
            var checkdt = SqlHelper.CmdExecuteDataTable(checksql);
            string sql = string.Empty;
            if (checkdt.Rows.Count > 0)
            {
                sql = @" select a.LotNo,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,a.WarehouseCode,a.LocationCode,c.BatchCode,a.Quantity from dbo.ICSWareHouseLotInfo a
								  left join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
								  left join ICSExtension c on b.ExtensionID=c.ID and b.WorkPoint=c.WorkPoint
                                  left join ICSInventory d ON a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
								  where a.InvCode='{0}' and a.WarehouseCode='{1}' and b.ExtensionID='{2}' AND a.WorkPoint='{3}' ";
            }
            else 
            {
                sql = @" select a.LotNo,a.InvCode,d.InvName,d.InvDesc,d.InvStd,d.InvUnit,a.WarehouseCode,a.LocationCode,'' BatchCode,a.Quantity from dbo.ICSWareHouseLotInfo a
								  left join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
                                  left join ICSInventory d ON a.InvCode=d.InvCode AND a.WorkPoint=d.WorkPoint
								  where a.InvCode='{0}' and a.WarehouseCode='{1}' AND a.WorkPoint='{3}' ";
            }
          
            sql = string.Format(sql, InvCode, WHCode, ExtensionID, WorkPoint);
            //return Repository().FindTableBySql(sql.ToString());
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        //绑定套件
        public string CreateLogByKit(string Code, string Parameter)
        {
            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 sql = string.Empty;
            JArray res = (JArray)JsonConvert.DeserializeObject(Parameter);
            string Identification = Guid.NewGuid().ToString();
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                sql += @"update ICSWareHouseLotInfo set LockQuantity='{6}' where LotNo='{2}' and WorkPoint='{9}'";
                sql += @"INSERT INTO ICSWareHouseLotInfoLog(ID,Identification,TransCode,TransSequence,LotNo,InvCode,FromWarehouseCode,FromLocationCode,
												            ToWarehouseCode,ToLocationCode,Quantity,Memo,Lock,TransType,BusinessCode,ERPUpload,
												            ERPID,ERPDetailID,ERPCode,ERPSequence,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE1)
						Values(NEWID(),'{0}','{1}','1','{2}','{3}','{4}','{5}','','','{6}','',1,'11','37',0,'','','','','{7}','{8}',getdate(),'{9}','')";
                sql = string.Format(sql, Identification, Code, jo["LotNo"].ToString(), jo["InvCode"].ToString(), jo["WHCode"].ToString(), jo["LocationCode"].ToString(), jo["Quantity"].ToString(), MUSER, MUSERNAME, WorkPoint);
            }

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

                }
                else
                {
                    msg = "绑定失败";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return msg;
        }

        //批量生成条码
        public string CreateInventoryLot(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 sql = string.Empty;
            string sqls= string.Empty;
            string sqlLot = string.Empty;
            sqls = @"select DABDOCCode,Sequence,InvCode,Quantity,Amount,ExtensionID from ICSDisassemblyDoc where  DABDOCType='2' and DABDOCCode='{0}'";
            sqls = string.Format(sqls, Code);
            var parent = Repository().FindTableBySql(sqls.ToString());
            sqlLot = string.Format(@"SELECT A.LotNO AS LOTNO FROM ICSInventoryLot A WHERE A.LotNO LIKE '{0}%'", Code);
            var parentLot = Repository().FindTableBySql(sqlLot.ToString());
            foreach (DataRow dts in parent.Rows)
            {
                string LotNo = "";
                string DABDOCCode = dts["DABDOCCode"].ToString();
                string Sequence = dts["Sequence"].ToString();
                var drs=parentLot.Select("LOTNO", "LOTNO DESC");
                if (drs.Length==0)
                {
                    LotNo = DABDOCCode + Sequence + "00001";
                }
                else
                {
                    var OldLotNo = drs[0]["LOTNO"].ToString();
                    LotNo = DABDOCCode + Sequence + (Convert.ToInt64(OldLotNo.Substring(OldLotNo.Length - 5))+1).ToString().PadLeft(5,'0');
                }

                sql += @"INSERT INTO ICSInventoryLot(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,
						PrintTimes,LastPrintUser,LastPrintTime,MUSER,MUSERName,MTIME,WorkPoint)
						Values(NEWID(),'{0}','{1}',getdate(),'2999-12-31','{2}','{3}','{4}','100','','','','{5}','{6}',getdate(),'{7}')";

                sql+= @"INSERT INTO ICSInventoryLotDetail( LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
						Values('{0}','{8}','{9}','{5}','{6}',getdate(),'{7}')";
                sql = string.Format(sql, LotNo, dts["InvCode"].ToString(), dts["Quantity"].ToString(), dts["Amount"].ToString(), dts["ExtensionID"].ToString(), MUSER, MUSERNAME, WorkPoint, dts["DABDOCCode"].ToString(), dts["Sequence"].ToString());
            }

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

                }
                else
                {
                    msg = "生成失败";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return msg;
        }

        //批量删除条码
        public string DeleteInventoryLot(string Code)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = string.Empty;
            string sql = string.Format(@"select * from dbo.ICSInventoryLot a 
								 inner join dbo.ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint
								 inner join dbo.ICSWareHouseLotInfo c on a.LotNo=c.LotNo and a.WorkPoint=c.WorkPoint
								 where b.TransCode='{0}' and a.WorkPoint='{1}'", Code, WorkPoint);
            DataTable dtASN = SqlHelper.GetDataTableBySql(sql);
            if (dtASN != null && dtASN.Rows.Count > 0)
            {
                msg = "该单据下的条码已入库,无法删除！";
            }

            if (string.IsNullOrEmpty(msg))
            {

                string sqls = string.Format("delete a from  ICSInventoryLot a inner join ICSInventoryLotDetail b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint where b.TransCode='{0}' and a.WorkPoint='{1}'", Code, WorkPoint);
                sqls += string.Format("delete from ICSInventoryLotDetail where TransCode='{0}' and WorkPoint='{1}'", Code, WorkPoint);
                SqlHelper.CmdExecuteNonQueryLi(sqls);
            }
            return msg;
        }


        /// <summary>
        /// 零件一键入库
        /// </summary>
        /// <param name="Codes"></param>
        /// <returns></returns>
        public string PostInWareHouse(string Codes)
        {
            string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
            string msg = "";
            DateTime TimeNow = DateTime.Now;
            string sqls = string.Empty;
            string sql = string.Empty;
            try
            {
                Codes = Codes.Substring(1, Codes.Length - 2);
                //1.获取需要散件入库的仓库

                string whereSql2 = @"
                  	SELECT F_Define1,F_Define2 FROM Sys_SRM_ItemsDetail WHERE F_ItemId=(SELECT F_Id FROM Sys_SRM_Items  WHERE F_EnCode='LocationCode' ) AND F_ItemCode='ICSDisassemblyDoc' ";

                DataTable dataTable2 = SqlHelper.CmdExecuteDataTable(whereSql2);

                if (dataTable2.Rows.Count <= 0)
                {
                    throw new Exception("请先再自定义档案配置需要零件入库的仓库信息！");
                }
                //判断是否全部生成条码
                string isCreate = $@" IF EXISTS ( select a.Sequence,a.DABDOCType,a.DABDOCCode as Code,a.Quantity ,a.InvCode,a.DABDOCQuantity
			    from ICSDisassemblyDoc a 
			    where a.DABDOCType='2' and a.DABDOCCode in ({Codes}) AND Isnull(a.DABDOCQuantity,0)!=0 and a.WorkPoint='{WorkPoint}')
                        BEGIN
                         RAISERROR('该单据散件已入库',16,1);
                            RETURN
                        END;
                       select a.Sequence,a.DABDOCType,a.DABDOCCode ,a.Quantity ,a.InvCode,a.DABDOCQuantity,a.Amount,a.ExtensionID,b.LotNO 
			    from ICSDisassemblyDoc a 
                  left JOIN ICSInventoryLotDetail b  ON a.DABDOCCode=b.TransCode AND a.Sequence=b.TransSequence and a.WorkPoint=b.WorkPoint
			    where a.DABDOCType='2' and a.DABDOCCode in ({Codes}) and a.WorkPoint='{WorkPoint}' ";
                DataTable isCreatedt = SqlHelper.CmdExecuteDataTable(isCreate);
                if (isCreatedt.Rows.Count <= 0)
                {
                    throw new Exception("请先确认拆卸单是否绑定套件单信息！");
                }
                else
                {
                    List<LOTStockUpCreateIModel> models = new List<LOTStockUpCreateIModel>();
                    LOTStockUpCreateIModel model = new LOTStockUpCreateIModel
                            {
                                User = MUSER,
                                MTime = TimeNow.ToString("yyyy-MM-dd HH:mm:ss"),
                                WorkPoint = WorkPoint,
                                TransType = "拆卸单"
                    };
                            model.detail = new List<LOTStockUpCreateIModelList>();
                    for (int i = 0; i < isCreatedt.Rows.Count; i++)
                    {
                        //查询需要入库的库位信息

                        //sql = $@" SELECT top 1 LocationCode,LotNo FROM ICSWareHouseLotInfo WHERE WarehouseCode='{dataTable2.Rows[0]["F_Define1"]}' AND WorkPoint='{WorkPoint}'  AND InvCode='{isCreatedt.Rows[i]["InvCode"]}' ORDER BY MTIME    ";
                        //var getSql = SqlHelper.CmdExecuteDataTable(sql);
                        ////仓库有库存，任选一个库位
                        //if (getSql.Rows.Count > 0)
                        //{
                        //        LOTStockUpCreateIModelList detail = new LOTStockUpCreateIModelList
                        //        {
                        //            LotNo = getSql.Rows[0]["LotNo"].ToString(),
                        //            WarehouseCode = dataTable2.Rows[0]["F_Define1"].ToString(),
                        //            LocationCode = getSql.Rows[0]["LocationCode"].ToString(),
                        //            TransCode = isCreatedt.Rows[i]["DABDOCCode"].ToString(),
                        //            TransSequence = isCreatedt.Rows[i]["Sequence"].ToString(),
                        //            Quantity = isCreatedt.Rows[i]["Quantity"].ToString(),
                        //            InvCode = isCreatedt.Rows[i]["InvCode"].ToString()
                        //        };
                        //        model.detail.Add(detail);
                            
                        //}
                        
                        //else
                        //{
                            //查询该单据是否已经生成条码信息


                            //创建新的条码入库
                            string LotNo = "";
                            if (string.IsNullOrEmpty(isCreatedt.Rows[i]["LotNO"].ToString()))
                            {
                                string DABDOCCode = isCreatedt.Rows[i]["DABDOCCode"].ToString();
                                string Sequence = isCreatedt.Rows[i]["Sequence"].ToString();
                                var sqlLot = string.Format(@"SELECT A.LotNO AS LOTNO FROM ICSInventoryLot A WHERE A.LotNO LIKE '{0}%'", DABDOCCode);
                                var parentLot = Repository().FindTableBySql(sqlLot.ToString());
                                var drs = parentLot.Select("LOTNO", "LOTNO DESC");
                                if (drs.Length == 0)
                                {
                                    LotNo = DABDOCCode + Sequence + "00001";
                                }
                                else
                                {
                                    var OldLotNo = drs[0]["LOTNO"].ToString();
                                    LotNo = DABDOCCode + Sequence + (Convert.ToInt64(OldLotNo.Substring(OldLotNo.Length - 5)) + 1).ToString().PadLeft(5, '0');
                                }

                                sqls += @"INSERT INTO ICSInventoryLot(ID,LotNo,InvCode,ProductDate,ExpirationDate,Quantity,Amount,ExtensionID,Type,
						PrintTimes,LastPrintUser,LastPrintTime,MUSER,MUSERName,MTIME,WorkPoint)
						Values(NEWID(),'{0}','{1}',getdate(),'2999-12-31','{2}','{3}','{4}','100','','','','{5}','{6}',getdate(),'{7}')";

                                sqls += @"INSERT INTO ICSInventoryLotDetail( LotNo,TransCode,TransSequence,MUSER,MUSERName,MTIME,WorkPoint)
						Values('{0}','{8}','{9}','{5}','{6}',getdate(),'{7}')";
                                sqls = string.Format(sqls, LotNo, isCreatedt.Rows[i]["InvCode"].ToString(), isCreatedt.Rows[i]["Quantity"].ToString(), isCreatedt.Rows[i]["Amount"].ToString(), isCreatedt.Rows[i]["ExtensionID"].ToString(), MUSER, MUSERNAME, WorkPoint, DABDOCCode, Sequence);
                            }
                            else 
                            {
                                LotNo = isCreatedt.Rows[i]["LotNO"].ToString();
                            }
                            LOTStockUpCreateIModelList detail = new LOTStockUpCreateIModelList
                            {
                                LotNo = LotNo,
                                WarehouseCode = dataTable2.Rows[0]["F_Define1"].ToString(),
                                LocationCode = dataTable2.Rows[0]["F_Define2"].ToString(),
                                TransCode = isCreatedt.Rows[i]["DABDOCCode"].ToString(),
                                TransSequence = isCreatedt.Rows[i]["Sequence"].ToString(),
                                Quantity = isCreatedt.Rows[i]["Quantity"].ToString(),
                                InvCode = isCreatedt.Rows[i]["InvCode"].ToString()
                            };
                            model.detail.Add(detail);


                        //}

                    }
                    if (!String.IsNullOrEmpty(sqls)) 
                    {
                        SqlHelper.CmdExecuteNonQueryLi(sqls);
                    }
                    models.Add(model);
                    var input = models.ToJson();
                    string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "LOTStockUp/Create";
                    string result = HttpPost(APIURL, input);
                    Result res = result.ToObject<Result>();
                    if (!res.Success)
                    {
                        throw new Exception(res.Message);
                    }
                    msg = "";
                }

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

        }
        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);
            }
        }

    }
}