HGSRM/WMS-BS/NFine.Application/DHAY/MaterialsDeliveredRTMApp.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.Domain._03_Entity.WMS;
using System.Net;
using System.IO;
using NFine.Application.SystemManage;
using NFine.Application.Entity;

namespace NFine.Application.DHAY
{
    public class MaterialsDeliveredRTMApp : RepositoryFactory<ICSVendor>
    {
        public static DataTable MDInvmes = new DataTable();
        DataActionApp actionapp = new DataActionApp();
        public DataTable GetICSMOApplyNeg(string MenuID, ref Pagination jqgridparam)
        {
            string DataActionsql = actionapp.DataActionSqlGet(MenuID);
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"  select a.ID,a.ApplyNegCode,a.WHCode,b.WarehouseName,a.Status,a.Type,a.Memo,d.CreatePerson AS MOCreater
                            ,g.F_RealName CreatePerson,a.CreateDateTime,a.MUSER,a.MUSERName 
                            ,a.EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
                            a.EATTRIBUTE9,a.EATTRIBUTE10,c.F_RealName AS ApproveBy
                            from ICSMOApplyNeg a 
                            left join  ICSWarehouse b on a.WHCode=b.WarehouseCode and a.WorkPoint=b.WorkPoint
                            LEFT JOIN Sys_SRM_User c ON c.F_Account=a.EATTRIBUTE1
							LEFT JOIN (select distinct f.CreatePerson,d.ApplyNegCode,d.WorkPoint from ICSMOApplyNegDetail d
                            left join ICSMOIssue e on e.IssueDetailID=d.SourceDetailID and e.WorkPoint=d.WorkPoint
                            left join ICSMO f on f.MOCode=e.SourceCode and f.WorkPoint=e.WorkPoint) d
							on d.ApplyNegCode=a.ApplyNegCode and d.WorkPoint=a.WorkPoint
                            LEFT JOIN Sys_SRM_User g ON g.F_Account=a.CreatePerson
                            where a.Type='3'";
            sql = string.Format(sql);
            if (DataActionsql != "")
            {
                sql += " " + DataActionsql;
            }
            DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public DataTable GetICSMOApplyNegDetail(string ApplyNegCode, ref Pagination jqgridparam)
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            DataTable dt = new DataTable();
            //var queryParam = queryJson.ToJObject();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select  a.ID
                            ,a.ApplyNegCode
                            ,a.Sequence
                            ,a.SourceDetailID
                            ,a.InvCode
                            ,a.Quantity
                            ,a.Amount
                            ,a.IssueNegQuantity
                            ,a.ExtensionID
                            ,a.MUSER
                            ,a.MUSERName
                            ,a.MTIME 
                            ,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
                            ,a.EATTRIBUTE1
                            ,a.EATTRIBUTE2
                            ,a.EATTRIBUTE3
                            ,a.EATTRIBUTE4
                            ,a.EATTRIBUTE5
                            ,a.EATTRIBUTE6
                            ,a.EATTRIBUTE7
                            ,a.EATTRIBUTE8
                            ,a.EATTRIBUTE9
                            ,a.EATTRIBUTE10
                            ,b.SourceCode
							,c.CreatePerson AS MOCreater
                            from ICSMOApplyNegDetail a
					        LEFT JOIN ICSMOIssue b ON a.SourceDetailID=issueDetailID AND a.WorkPoint=b.WorkPoint
							LEFT JOIN ICSMO c ON c.MOCode=b.SourceCode AND c.WorkPoint=b.WorkPoint
							left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint                           
                            WHERE a.ApplyNegCode='" + ApplyNegCode + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public string DeleteICSMOApplyNeg(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            string sqlSeach = string.Format(@"select a.LotNo from dbo.ICSInventoryLotDetail a
                                inner join dbo.ICSInventoryLot b on a.LotNo=b.LotNo and a.WorkPoint=b.WorkPoint and b.Type='13'
                                where a.TransCode in ({0})", keyValue.TrimEnd(','));
            DataTable dt = SqlHelper.GetDataTableBySql(sqlSeach);
            if (dt.Rows.Count > 0)
            {
                msg = "该退料单已生成条码,请先删除条码!";
                return msg;
            }

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

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return msg;
        }
        public DataTable GetICSReturnTemporary(string rfqno)
        {
            string sql = @"SELECT
                        a.ApplyNegCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        a.SourceDetailID as ZJID,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,
a.IssueNegQuantity,
a.ExtensionID,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
	                        ICSMOApplyNegDetail 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.ApplyNegCode='" + 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 (MDInvmes.Rows.Count > 0)
            {
                dtCloned.Merge(MDInvmes, false);
            }
            return dtCloned;
        }

        public void ClearTemp()
        {
            MDInvmes.Rows.Clear();
        }

        public DataTable GetINV(string invcode, string SourceCode, string SourceSequence, string MenuID, ref Pagination jqgridparam)
        {
            string DataActionsql = actionapp.DataActionSqlGetSecondery(MenuID);
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            List<DbParameter> parameter = new List<DbParameter>();
            DataTable table = new DataTable();
            string wherestr = "";
            if (!string.IsNullOrEmpty(invcode))
            {
                wherestr += " and a.InvCode like '%" + invcode + "%'";
            }
            if (!string.IsNullOrEmpty(SourceCode))
            {
                wherestr += " and a.SourceCode like '%" + SourceCode + "%'";
            }
            if (!string.IsNullOrEmpty(SourceSequence))
            {
                wherestr += " and a.SourceSequence like '%" + SourceSequence + "%'";
            }
            string sql = @"SELECT DISTINCT a.IssueDetailID as ZJID,a.IssueCode,a.Sequence as MuHang,a.SourceCode,a.SourceSequence,ISNULL(g.DepCode,'') AS DepCode,   a.InvCode,a.Quantity,a.Amount,b.InvName,b.InvStd,b.InvUnit,a.IssueQuantity as IssueNegQuantity,a.ExtensionID 
,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,g.CreatePerson
FROM ICSMOIssue 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 ICSMO g on g.MOCode=a.SourceCode and g.WorkPoint=a.WorkPoint
where a.IssueQuantity>0 and g.ERPStatus!='3' and isnull(a.EATTRIBUTE10,'')!='超额领料' and a.WorkPoint = '" + WorkPoint + "'" + wherestr;
            sql += " " + DataActionsql;
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        public void AddMOApplyNegTemp(string json)
        {
            var data = json.ToJObject();
            if (MDInvmes.Columns.Count <= 0)
            {
                MDInvmes.Columns.Add("ZJID", typeof(string));
                MDInvmes.Columns.Add("InvCode", typeof(string));
                MDInvmes.Columns.Add("INVNAME", typeof(string));
                MDInvmes.Columns.Add("InvStd", typeof(string));
                MDInvmes.Columns.Add("InvUnit", typeof(string));
                MDInvmes.Columns.Add("Quantity", typeof(string));
                MDInvmes.Columns.Add("IssueNegQuantity", typeof(string));
                MDInvmes.Columns.Add("ExtensionID", typeof(string));
                MDInvmes.Columns.Add("Amount", typeof(string));
                MDInvmes.Columns.Add("ID", typeof(string));
            }
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            DataRow newrow = MDInvmes.NewRow();
            newrow["ZJID"] = data["ZJID"];
            newrow["InvName"] = data["InvName"];
            newrow["InvCode"] = data["InvCode"];
            newrow["InvStd"] = data["InvStd"];
            newrow["InvUnit"] = data["InvUnit"];
            newrow["Amount"] = data["Amount"];
            newrow["IssueNegQuantity"] = data["IssueNegQuantity"];
            newrow["ExtensionID"] = data["ExtensionID"];
            newrow["Quantity"] = "";
            newrow["ID"] = data["ID"];
            MDInvmes.Rows.Add(newrow);

        }

        public void UpdateMOApplyNegTemp(string json)
        {
            var data = json.ToJObject();
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            var info = MDInvmes.Select(string.Format("TLZID='{0}'", data["TLZID"]));
            if (info != null && info.Length > 0)
            {
                info[0]["ZJID"] = data["ZJID"];
                info[0]["InvName"] = data["InvName"];
                info[0]["InvCode"] = data["InvCode"];
                info[0]["InvStd"] = data["InvStd"];
                info[0]["InvUnit"] = data["InvUnit"];
                info[0]["Amount"] = data["Amount"];
                info[0]["IssueNegQuantity"] = data["IssueNegQuantity"];
                info[0]["ExtensionID"] = data["ExtensionID"];
                info[0]["Quantity"] = 0;
            }
            else
            {
                DataRow newrow = MDInvmes.NewRow();
                newrow["ZJID"] = data["ZJID"];
                newrow["InvName"] = data["InvName"];
                newrow["InvCode"] = data["InvCode"];
                newrow["InvStd"] = data["InvStd"];
                newrow["InvUnit"] = data["InvUnit"];
                newrow["Amount"] = data["Amount"];
                newrow["IssueNegQuantity"] = data["IssueNegQuantity"];
                newrow["ExtensionID"] = data["ExtensionID"];
                newrow["Quantity"] = 0;
                newrow["ID"] = data["ID"];
                newrow["TLZID"] = data["TLZID"];
                MDInvmes.Rows.Add(newrow);
            }
        }

        public string SaveICSMOApplyNeg(string ICSASN)
        {
            string msg = "";
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sourceDetailID = string.Empty;
            //判断工单是否同一个人创建的，不是同一个创建人的工单不允许做在同一张退货单
            List<ICSMOApplyNeg> model = JsonConvert.DeserializeObject<List<ICSMOApplyNeg>>(ICSASN.ToString());
            foreach (ICSMOApplyNeg moa in model)
            {
                foreach (ICSMOApplyNegDetail moaD in moa.Detail)
                {
                    sourceDetailID += moaD.SourceDetailID + ",";
                }
            }
            //查询工单行是否同一人创建


            string sqlSeach = string.Format(@"select distinct  f.CreatePerson
FROM ICSMOIssue e
left join ICSMO f on f.MOCode = e.SourceCode and f.WorkPoint = e.WorkPoint
WHERE e.IssueDetailID in('{0}') and e.WorkPoint='{1}'", sourceDetailID.TrimEnd(',').Replace(",", "','"), WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sqlSeach);
            if (dt.Rows.Count > 1)
            {
                msg = "选择的工单行非同一人创建，请重新选择!";
                return msg;
            }


            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "MOIssueDocNegativeApply/Create";
            string result = HttpPost(APIURL, ICSASN);
            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;
            }
            return msg;
        }

        public DataTable GetICSMOApplyNegDetailTemp(string ApplyNegCode)
        {
            string sql = @"SELECT
                        a.ID,
                        a.ID as TLZID,
                        a.ApplyNegCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        a.SourceDetailID as ZJID,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,
						ISNULL(l.Quantity, 0) as Quantitys,
                        ISNULL(l.Amount, 0) as Amounts,
a.IssueNegQuantity,
a.ExtensionID,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
	                        ICSMOApplyNegDetail 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 ICSMOIssue l ON l.InvCode=a.InvCode AND l.WorkPoint=a.WorkPoint AND l.ExtensionID=a.ExtensionID AND l.IssueDetailID=a.SourceDetailID  
where a.ApplyNegCode='" + ApplyNegCode + "'";
            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
            if (MDInvmes == null || MDInvmes.Columns.Count <= 0)
                MDInvmes = table.Clone();
            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 (MDInvmes.Rows.Count > 0)
            {
                //dtCloned.Merge(Invmes, false);
                foreach (DataRow data in MDInvmes.Rows)
                {

                    var info = dtCloned.Select(string.Format("TLZID='{0}'", data["TLZID"]));
                    if (info != null && info.Length > 0)
                    {
                        info[0]["ZJID"] = data["ZJID"];
                        info[0]["InvName"] = data["InvName"];
                        info[0]["InvCode"] = data["InvCode"];
                        info[0]["InvStd"] = data["InvStd"];
                        info[0]["InvUnit"] = data["InvUnit"];
                        info[0]["Amount"] = data["Amount"];
                        info[0]["IssueNegQuantity"] = data["IssueNegQuantity"];
                        info[0]["ExtensionID"] = data["ExtensionID"];
                        info[0]["Quantity"] = 0;
                    }
                    else
                    {
                        DataRow newrow = dtCloned.NewRow();
                        newrow["ZJID"] = data["ZJID"];
                        newrow["InvName"] = data["InvName"];
                        newrow["InvCode"] = data["InvCode"];
                        newrow["InvStd"] = data["InvStd"];
                        newrow["InvUnit"] = data["InvUnit"];
                        newrow["Amount"] = data["Amount"];
                        newrow["Quantity"] = 0;
                        newrow["ID"] = data["ID"];
                        newrow["TLZID"] = data["TLZID"];
                        newrow["IssueNegQuantity"] = data["IssueNegQuantity"];
                        newrow["ExtensionID"] = data["ExtensionID"];
                        dtCloned.Rows.Add(newrow);
                    }
                }
            }
            return dtCloned;
        }




        public string UpdateICSMOApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "MOIssueDocNegativeApply/Update";
            string result = HttpPost(APIURL, ICSASN);
            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;
            }
            return msg;
        }

        public DataTable StatementExportAll(string MenuID)
        {

            string DataActionsql = actionapp.DataActionSqlGet(MenuID);
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"  SELECT
	a.ApplyNegCode '退料申请单号',
	d.Sequence '退料申请单行号',
	f.CreatePerson  '工单创建人',
	a.WHCode '仓库代码',
	b.WarehouseName '仓库名称',
CASE
		
		WHEN a.Status= '1' THEN
		'开立' 
		WHEN a.Status= '1' THEN
		'审核' ELSE '关闭' 
	END AS '状态',
CASE
		
		WHEN a.Type= '1' THEN
		'生产订单备料退料' 
		WHEN a.Type= '2' THEN
		'领料申请单退料' ELSE '材料出库退料' 
	END AS '类型',
	a.Memo '备注',
	a.CreatePerson '创建人',
	a.CreateDateTime '创建时间',
	a.EATTRIBUTE1 '审核人编码',
	a.EATTRIBUTE2 '审核时间',
	c.F_RealName AS  '审核人',
	e.SourceCode '工单',
	d.SourceDetailID '来源表体ID',
	d.InvCode '料品编码',
	d.Quantity '数量',
	d.Amount '辅计量数量',
	d.IssueNegQuantity '已退数量',
	d.MUSERName '操作人名称',
	d.MTIME '操作时间',
	g.BatchCode '批次',
	d.EATTRIBUTE1 '单据自定义项1',
	h.EATTRIBUTE3 '图号' 
FROM
	ICSMOApplyNeg a
	LEFT JOIN ICSWarehouse b ON a.WHCode= b.WarehouseCode 
	AND a.WorkPoint= b.WorkPoint
	LEFT JOIN Sys_SRM_User c ON c.F_Account= a.EATTRIBUTE1
	LEFT JOIN ICSMOApplyNegDetail d ON d.ApplyNegCode= a.ApplyNegCode 
	AND d.WorkPoint= a.WorkPoint
	LEFT JOIN ICSMOIssue e ON e.IssueDetailID= d.SourceDetailID 
	AND e.WorkPoint= d.WorkPoint
	LEFT JOIN ICSMO f ON f.MOCode= e.SourceCode 
	AND f.WorkPoint= e.WorkPoint
	LEFT JOIN ICSExtension g ON d.ExtensionID= g.ID 
	AND d.WorkPoint= g.WorkPoint
	LEFT JOIN ICSInventory h ON d.InvCode= h.InvCode 
	AND d.WorkPoint= h.WorkPoint 
WHERE
	a.Type= '3'  ";
            sql = string.Format(sql);
            if (DataActionsql != "")
            {
                sql += " " + DataActionsql;
            }
            sql += " order by a.ApplyNegCode desc,d.Sequence ";


            return SqlHelper.CmdExecuteDataTable(sql.ToString());
        }



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




















    }
}