OMAY_WMS/WMS-BS/NFine.Application/WMS/PurOrderApp.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 PurOrderApp : RepositoryFactory<ICSVendor>
    {

        public static DataTable Invmes = new DataTable();
        // public static DataTable Supplier = new DataTable();

        /// <summary>
        /// 退料
        /// </summary>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetICSMOApplyNeg(ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"		select distinct a.POCode,a.VenCode,a.DepCode,a.PersonCode,a.Status,a.POType,a.CreatePerson,a.CreateDateTime,a.MUSER,a.MUSERName 
								from ICSPurchaseOrder a 
                                 where a.POType='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 GetICSMOApplyNegDetail(string POCode, 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.POCode,a.Sequence,a.InvCode,a.Quantity,a.Amount,a.InQuantity,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
from ICSPurchaseOrder a
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
                            WHERE a.POCode='" + POCode + "' ";
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }


        public DataTable GetINV(string invcode, 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 + "%'";
                wherestr += " and InvCode like '%" + invcode + "%'";
            }
            //            string sql = @"SELECT DISTINCT a.ID,a.PoCode,a.Sequence,a.InvCode,a.Quantity,a.InQuantity,a.Amount,b.InvName,b.InvStd,b.InvUnit,a.ExtensionID  
            //,f.Colspan,isnull(f.ProjectCode,'') AS	ProjectCode	,isnull(f.BatchCode,'') AS	BatchCode	,isnull(f.Version,'') AS	Version	,isnull(f.Brand,'') AS	Brand	,isnull(f.cFree1,'') AS	cFree1,isnull(f.cFree2,'') AS	cFree2,isnull(f.cFree3,'') AS	cFree3	,isnull(f.cFree4,'') AS	cFree4
            //,isnull(f.cFree5,'') AS	cFree5	,isnull(f.cFree6,'') AS	cFree6	,isnull(f.cFree7,'') AS	cFree7	,isnull(f.cFree8,'') AS	cFree8	,isnull(f.cFree9,'') AS	cFree9	,isnull(f.cFree10,'') AS	cFree10
            //FROM ICSPurchaseOrder 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.WorkPoint = '" + WorkPoint + "'" + wherestr;
            string sql = @"select ID, InvCode ,InvName ,InvStd ,InvUnit ,ClassCode ,ClassName from ICSInventory where WorkPoint = '" + WorkPoint + "'" + wherestr;
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        public DataTable GetICSMOPickLog(string invcode, 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 + "'%";
            //}
            string sql = @"	select distinct a.POCode,a.Sequence,a.Quantity,a.Amount,a.ID,a.InvCode 
,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 ICSPurchaseOrder a
left join ICSExtension f on a.ExtensionID=f.ID 
where a.InvCode='{0}' and a.WorkPoint='{1}'
                            ";
            sql = string.Format(sql, invcode, WorkPoint);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);

        }

        /// <summary>
        /// 获取仓库列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetInvCode()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"
select '' as InvCode,
union all
select InvCode from ICSInventory where WorkPoint='{0}'
";
            //string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            //if (role != "admin")
            //{
            //    sql += " and b.WorkPoint=" + WorkPoint.TrimEnd(',') + "";
            //}
            sql = string.Format(sql, WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }


        public DataTable GetICSReturnTemporary(string rfqno)
        {
            string sql = @"SELECT
                        a.POCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit
,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
	                        ICSPurchaseOrder a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode 
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where a.POCode='" + 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 string GetNewid()
        {
            string sql = "select newid() AS ID";
            return Repository().FindTableBySql(sql, null).Rows[0]["ID"].ToString();

        }

        public void AddPurOrderTemp(string json)
        {
            var data = json.ToJObject();
            if (Invmes.Columns.Count <= 0)
            {
                //Invmes.Columns.Add("ID", typeof(string));
                //Invmes.Columns.Add("PoCode", typeof(string));
                Invmes.Columns.Add("InvCode", typeof(string));
                Invmes.Columns.Add("InvName", typeof(string));
                Invmes.Columns.Add("InvStd", typeof(string));
                Invmes.Columns.Add("InvUnit", typeof(string));
                Invmes.Columns.Add("Quantity", typeof(string));
                Invmes.Columns.Add("IssueQuantity", typeof(string));
                Invmes.Columns.Add("ExtensionID", typeof(string));
                Invmes.Columns.Add("Amount", typeof(string));
                Invmes.Columns.Add("ID", typeof(string));
                Invmes.Columns.Add("ProjectCode", typeof(string));
                Invmes.Columns.Add("BatchCode", typeof(string));
                Invmes.Columns.Add("Version", typeof(string));
                Invmes.Columns.Add("Brand", typeof(string));
                Invmes.Columns.Add("cFree1", typeof(string));
                Invmes.Columns.Add("cFree2", typeof(string));
                Invmes.Columns.Add("cFree3", typeof(string));
                Invmes.Columns.Add("cFree4", typeof(string));
                Invmes.Columns.Add("cFree5", typeof(string));
                Invmes.Columns.Add("cFree6", typeof(string));
                Invmes.Columns.Add("cFree7", typeof(string));
                Invmes.Columns.Add("cFree8", typeof(string));
                Invmes.Columns.Add("cFree9", typeof(string));
                Invmes.Columns.Add("cFree10", typeof(string));
            }
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            DataRow newrow = Invmes.NewRow();
            //newrow["ID"] = data["ID"];
            //newrow["PoCode"] = data["PoCode"];
            newrow["InvName"] = data["InvName"];
            newrow["InvCode"] = data["InvCode"];
            newrow["InvStd"] = data["InvStd"];
            newrow["InvUnit"] = data["InvUnit"];
            newrow["Amount"] = data["Amount"];
            newrow["IssueQuantity"] = data["IssueQuantity"];
            newrow["ExtensionID"] = data["ExtensionID"];
            newrow["Quantity"] = "";
            newrow["ID"] = data["ID"];
            newrow["ProjectCode"] = data["ProjectCode"];
            newrow["BatchCode"] = data["BatchCode"];
            newrow["Version"] = data["Version"];
            newrow["Brand"] = data["Brand"];
            newrow["cFree1"] = data["cFree1"];
            newrow["cFree2"] = data["cFree2"];
            newrow["cFree3"] = data["cFree3"];
            newrow["cFree4"] = data["cFree4"];
            newrow["cFree5"] = data["cFree5"];
            newrow["cFree6"] = data["cFree6"];
            newrow["cFree7"] = data["cFree7"];
            newrow["cFree8"] = data["cFree8"];
            newrow["cFree9"] = data["cFree9"];
            newrow["cFree10"] = data["cFree10"];
            Invmes.Rows.Add(newrow);

        }


        public string DeleteMopick(string json)
        {
            try
            {
                string wheresql = "(";
                string[] id = json.Split(',');
                for (int i = 0; i < id.Length; i++)
                {
                    wheresql += "'" + id[i].Replace(']', ' ').Replace('[', ' ').Replace('"', ' ').Trim() + "',";
                }
                wheresql = wheresql.Trim(',') + ")";
                string sql = "delete  from  ICSMOApplyNeg where id in" + wheresql;
                int count = DbHelper.ExecuteNonQuery(CommandType.Text, sql);
                DataRow[] row = Invmes.Select("ID in " + wheresql + "");
                foreach (DataRow a in row)
                {
                    Invmes.Rows.Remove(a);
                }

                return "删除成功!";

            }
            catch (Exception ex)
            {

                return ex.Message + "  删除失败！";

            }

        }


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

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

        //接口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);
            }
        }


        /// <summary>
        /// 获取料品属性修改文本框值
        /// </summary>
        /// <param name="POCode"></param>
        /// <param name="PORow"></param>
        /// <returns></returns>
        public DataTable GetInputValue(string ID)
        {
            try
            {
                DataTable dt = new DataTable();
                //var queryParam = queryJson.ToJObject();
                List<DbParameter> parameter = new List<DbParameter>();
                string sql = @"select InvCode,InvName,InvDesc,InvStd,InvUnit,AmountUnit, ClassCode,ClassName,InvRate,InvVersion,InvBrand,InvIQC,
                                InvFQC,EffectiveEnable,EffectiveDays,BatchEnable,LotEnable,PrintEnable,WHUser
                                from ICSInventory 
                                WHERE 1=1 AND ID='{0}' ";
                sql = string.Format(sql, ID);
                return Repository().FindTableBySql(sql.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }

        public DataTable GetICSMOApplyNegDetailTemp(string PoCode)
        {
            string sql = @"SELECT DISTINCT
                        a.ID,
                        a.ID as TLZID,
                        a.PoCode,
                        a.InvCode,
                        a.Quantity,
                        a.Amount,
                        b.InvName,
                        b.InvStd,
                        b.InvUnit,a.VenCode,a.DepCode,
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
	                        ICSPurchaseOrder a
LEFT JOIN ICSInventory b ON a.InvCode = b.InvCode 
	left join ICSExtension f on a.ExtensionID=f.ID and a.WorkPoint=f.WorkPoint  
where a.PoCode='" + PoCode + "'";
            DataTable table = Repository().FindDataSetBySql(sql).Tables[0];
            if(Invmes==null || Invmes.Columns.Count<=0)
                Invmes = 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 (Invmes.Rows.Count > 0)
            {
                //dtCloned.Merge(Invmes, false);
                foreach (DataRow data in Invmes.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;
                        info[0]["ProjectCode"] = data["ProjectCode"];
                        info[0]["BatchCode"] = data["BatchCode"];
                        info[0]["Version"] = data["Version"];
                        info[0]["Brand"] = data["Brand"];
                        info[0]["cFree1"] = data["cFree1"];
                        info[0]["cFree2"] = data["cFree2"];
                        info[0]["cFree3"] = data["cFree3"];
                        info[0]["cFree4"] = data["cFree4"];
                        info[0]["cFree5"] = data["cFree5"];
                        info[0]["cFree6"] = data["cFree6"];
                        info[0]["cFree7"] = data["cFree7"];
                        info[0]["cFree8"] = data["cFree8"];
                        info[0]["cFree9"] = data["cFree9"];
                        info[0]["cFree10"] = data["cFree10"];
                    }
                    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["ProjectCode"] = data["ProjectCode"];
                        newrow["BatchCode"] = data["BatchCode"];
                        newrow["Version"] = data["Version"];
                        newrow["Brand"] = data["Brand"];
                        newrow["cFree1"] = data["cFree1"];
                        newrow["cFree2"] = data["cFree2"];
                        newrow["cFree3"] = data["cFree3"];
                        newrow["cFree4"] = data["cFree4"];
                        newrow["cFree5"] = data["cFree5"];
                        newrow["cFree6"] = data["cFree6"];
                        newrow["cFree7"] = data["cFree7"];
                        newrow["cFree8"] = data["cFree8"];
                        newrow["cFree9"] = data["cFree9"];
                        newrow["cFree10"] = data["cFree10"];
                        dtCloned.Rows.Add(newrow);
                    }
                }
            }
            return dtCloned;
        }

        public void UpdateMOApplyNegTemp(string json)
        {
            var data = json.ToJObject();
            string usercode = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
            var info = Invmes.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 = Invmes.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"];
                Invmes.Rows.Add(newrow);
            }
        }

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


        public string DeleteICSPurchaseOrder(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 1 FROM ICSPurchaseOrder WHERE POCode IN ({0}) and WorkPoint ='{1}' and InQuantity>0)
                                    BEGIN
	                                    RAISERROR('已经入库的采购订单不能删除！',16,1);
	                                    RETURN
                                    END
                                    ELSE
                                    BEGIN
	                                    DELETE FROM dbo.ICSPurchaseOrder  WHERE POCode IN ({0}) and WorkPoint ='{1}'
                                    END", keyValue.TrimEnd(','), WorkPoint);
            try
            {
                SqlHelper.ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            //try
            //{
            //    if (SqlHelper.CmdExecuteNonQueryLi(sql) > 0)
            //    {

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





    }
}