HGSRM/WMS-BS/NFine.Application/WMS/WeiWaiProductionIssueApp.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;

namespace NFine.Application.WMS
{
   public class WeiWaiProductionIssueApp : RepositoryFactory<ICSVendor>
    {
        #region MyRegion

       
        public static DataTable WWInvmes = new DataTable();

        /// <summary>
        /// 委外退料
        /// </summary>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetICSOApplyNeg(ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"	select a.ID,a.OApplyNegCode,a.WHCode,b.WarehouseName,a.Status,a.Type,a.Memo,C.F_RealName AS 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	from ICSOApplyNeg 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.CreatePerson
where a.Type='1'";
            sql = string.Format(sql);
            DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        //子表查询
        public DataTable GetICSOApplyNegDetail(string OApplyNegCode, 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.OApplyNegCode,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
from ICSOApplyNegDetail a
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
                            WHERE a.OApplyNegCode='" + OApplyNegCode + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetICSReturnTemporary(string rfqno)
        {
            string sql = @"SELECT
                        a.OApplyNegCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        a.SourceDetailID as ZJID,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,
                        a.IssueNegQuantity,
a.ExtensionID,
'' as tuinum
,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
	                        ICSOApplyNegDetail 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.OApplyNegCode='" + 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 (WWInvmes.Rows.Count > 0)
            {
                dtCloned.Merge(WWInvmes, false);
            }
            return dtCloned;
        }

        public string GetNewid()
        {
            string sql = "select newid() AS ID";
            return Repository().FindTableBySql(sql, null).Rows[0]["ID"].ToString();

        }

        public DataTable GetINV(string invcode,string DocNo, ref Pagination jqgridparam)
        {
            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(DocNo))
            {
                wherestr += " and c.OOCode like '%" + DocNo + "%'";
            }
            string sql = @"SELECT DISTINCT
    a.PickID AS ZJID,
	a.Sequence,
	c.OOCode,
	c.Sequence AS MuHang,
	a.InvCode,
	a.Quantity,
	a.Amount,
	b.InvName,
	b.InvStd,
	b.InvUnit,
	a.IssueQuantity,
	a.ExtensionID,
	isnull(d.Quantity, 0) as tuinum
,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 ICSOOPick a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode AND a.WorkPoint = b.WorkPoint
LEFT JOIN ICSOutsourcingOrder c ON a.OODetailID = c.OODetailID AND a.WorkPoint = c.WorkPoint
LEFT JOIN (select SourceDetailID, WorkPoint, sum(Quantity) as Quantity FROM ICSOApplyNegDetail GROUP BY SourceDetailID, WorkPoint) d ON a.PickID = d.SourceDetailID AND a.WorkPoint = d.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
WHERE

    a.IssueQuantity > 0 and a.WorkPoint = '{0}' ";
            sql = string.Format(sql, WorkPoint);
            sql = sql + wherestr;
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        public void UpdateMOApplyNegTemp(string json)
        {
            var data = json.ToJObject();
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            var info = WWInvmes.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]["tuinum"] = data["tuinum"];
                info[0]["Quantity"] = 0;
            }
            else
            {
                DataRow newrow = WWInvmes.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["tuinum"] = data["tuinum"];
                newrow["Quantity"] = 0;
                newrow["ID"] = data["ID"];
                newrow["TLZID"] = data["TLZID"];
                WWInvmes.Rows.Add(newrow);
            }
        }

        public void AddMOApplyNegTemp(string json)
        {
            var data = json.ToJObject();
            if (WWInvmes.Columns.Count <= 0)
            {
                WWInvmes.Columns.Add("ZJID", typeof(string));
                WWInvmes.Columns.Add("InvCode", typeof(string));
                WWInvmes.Columns.Add("INVNAME", typeof(string));
                WWInvmes.Columns.Add("InvStd", typeof(string));
                WWInvmes.Columns.Add("InvUnit", typeof(string));
                WWInvmes.Columns.Add("Quantity", typeof(string));
                WWInvmes.Columns.Add("Amount", typeof(string));
                WWInvmes.Columns.Add("IssueNegQuantity", typeof(string));
                WWInvmes.Columns.Add("ExtensionID", typeof(string));
                WWInvmes.Columns.Add("ID", typeof(string));
                WWInvmes.Columns.Add("TLZID", typeof(string));
                WWInvmes.Columns.Add("tuinum", typeof(string));
                
            }
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            DataRow newrow = WWInvmes.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"];
            newrow["tuinum"] = data["tuinum"];
            WWInvmes.Rows.Add(newrow);

        }

        public DataTable GetICSMOApplyNegDetailTemp(string OApplyNegCode)
        {
            string sql = @"	SELECT
                        a.ID,
                        a.ID as TLZID,
                        a.OApplyNegCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        a.SourceDetailID as ZJID,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,
a.IssueNegQuantity,
a.ExtensionID,
isnull(d.Quantity,0) as tuinum
,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
	                        ICSOApplyNegDetail a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode and a.WorkPoint=b.WorkPoint
LEFT JOIN (select OApplyNegCode,WorkPoint,sum(Quantity) as Quantity FROM ICSOApplyNegDetail GROUP BY OApplyNegCode,WorkPoint) d ON a.OApplyNegCode = d.OApplyNegCode AND a.WorkPoint = d.WorkPoint
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where a.OApplyNegCode='" + OApplyNegCode + "'";
            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
            if (WWInvmes == null || WWInvmes.Columns.Count <= 0)
                WWInvmes = 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 (WWInvmes.Rows.Count > 0)
            {
                //dtCloned.Merge(WWInvmes, false);
                foreach (DataRow data in WWInvmes.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]["Quantity"] = 0;
                        info[0]["IssueNegQuantity"] = data["IssueNegQuantity"];
                        info[0]["ExtensionID"] = data["ExtensionID"];
                        info[0]["tuinum"] = data["tuinum"];
                    }
                    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"];
                        newrow["tuinum"] = data["tuinum"];
                        dtCloned.Rows.Add(newrow);
                    }
                }
            }
            return dtCloned;
        }

        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;
         
            sql += string.Format(@"DELETE FROM dbo.ICSOApplyNeg  WHERE OApplyNegCode IN ({0}) and WorkPoint ='{1}'", keyValue.TrimEnd(','), WorkPoint);
            sql += string.Format(@"DELETE FROM dbo.ICSOApplyNegDetail  WHERE OApplyNegCode 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 string SaveICSMOApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "OutsourcingIssueDoNegativeApply/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 void ClearTemp()
        {
            WWInvmes.Rows.Clear();
        }
        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);
            }
        }


        public string UpdateICSMOApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "OutsourcingIssueDoNegativeApply/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 string AuditICSMOApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "OutsourcingIssueDoNegativeApply/Approve";
            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;
        }


        #endregion
    }
}