using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
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.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Net;
using NFine.Code;
using System.Data.Common;
using System.Data.SqlClient;

namespace NFine.Application.WMS
{
    /// <summary>
    /// 委外发料申请
    /// </summary>
    public class ICSOApplyApp : RepositoryFactory<ICSVendor>
    {

        public static DataTable Invmes = new DataTable();

        /// <summary>
        /// 委外发料申请删除
        /// </summary>
        /// <param name="keyValue"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public string DeleteOApplyApplyNeg(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.ICSOApply  WHERE ApplyCode 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;
        }

        /// <summary>
        /// 委外申请单号查询
        /// </summary>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetOApplyApplyNeg(ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"select a.ApplyCode, a.CreatePerson,CONVERT(NVARCHAR(20), a.CreateDateTime,20) as CreateDateTime from ICSOApply a 
                                group by  a.ApplyCode,a.CreatePerson,a.CreateDateTime";
            sql = string.Format(sql);
            DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        /// <summary>
        /// 委外订单明细查询
        /// </summary>
        /// <param name="jqgridparam"></param>
        /// <returns></returns>
        public DataTable GetOApplyApplyNegDetail(string ApplyNegCode, ref Pagination jqgridparam)
        {
            DataTable dt = new DataTable();
            object Figure = GetDecimalDigits();
            List<DbParameter> parameter = new List<DbParameter>();
            string sql = @"SELECT a.ID,a.ApplyCode,a.Sequence,a.SourceCode,a.SourceSequence,a.InvCode,a.Quantity,a.Amount,a.IssueQuantity,a.WHCode,e.WarehouseName as WHName,a.Type,a.Status,a.CreatePerson,a.CreateDateTime
,a.ApplyID,a.ApplyDetailID,a.ExtensionID,a.MUSER,a.MUSERName,a.MTIME,a.WorkPoint,b.InvName
            ,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.EATTRIBUTE as EATTRIBUTE1,a.EATTRIBUTE2,a.EATTRIBUTE3,a.EATTRIBUTE4,a.EATTRIBUTE5,a.EATTRIBUTE6,a.EATTRIBUTE7,a.EATTRIBUTE8,
            a.EATTRIBUTE9,a.EATTRIBUTE10
from ICSOApply 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 e on a.WHCode = e.WarehouseCode and a.WorkPoint=e.WorkPoint
            where a.ApplyCode = '" + ApplyNegCode + "' ";
            sql = string.Format(sql, Figure);
            DataTable dttest = Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
            return Repository().FindTablePageBySql(sql.ToString(), parameter.ToArray(), ref jqgridparam);
        }

        public object GetDecimalDigits()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location.TrimEnd(',');
            try
            {
                string sql = string.Empty;
                sql = @"select Figure from ICSConfiguration where Code='Figure001' and Enable='1' and  WorkPoint='" + WorkPoint + "'";
                object Figure = SqlHelper.ExecuteScalar(sql);
                return Figure;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message.ToString());
            }
        }
        public void ClearTemp()
        {
            Invmes.Rows.Clear();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="ICSASN"></param>
        /// <returns></returns>
        public string SaveICSOApplyApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "OApply/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;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="ICSASN"></param>
        /// <returns></returns>
        public string UpdateICSOApplyApplyNeg(string ICSASN)
        {
            string msg = "";
            string APIURL = ConfigurationManager.ConnectionStrings["APIURL"].ConnectionString + "OApply/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;
        }
        /// <summary>
        /// 获取单号
        /// </summary>
        /// <param name="WorkPoint"></param>
        /// <returns></returns>
        public string GetOOCode(string WorkPoint)
        {
            WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string OOCode = string.Empty;
            if (!string.IsNullOrEmpty(WorkPoint))
            {

                OOCode = GetSerialCode(WorkPoint, "ICSOApply", "ApplyCode", "OA", 8);
            }
            if (!string.IsNullOrWhiteSpace(OOCode))
            {
                string sqlISHave = @"SELECT ApplyCode FROM ICSMOApply a 
                              WHERE a.ApplyCode = '{0}'";
                sqlISHave = string.Format(sqlISHave, OOCode);
                DataTable dtIsHave = SqlHelper.GetDataTableBySql(sqlISHave);
                if (dtIsHave.Rows.Count > 0)
                {
                    throw new Exception("单号已存在！");
                }
            }
            return OOCode;
        }

        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();
        }
        public void UpdateOutsourcingOrderApplyNegTemp(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"];
            }
            else
            {
                DataRow newrow = Invmes.NewRow();
                newrow["ZJID"] = data["ZJID"];
                Invmes.Rows.Add(newrow);
            }
        }
        public DataTable GetICSOApplyReturnTemporary(string ApplyCode)
        {
            string sql = @"SELECT a.ID,a.ApplyCode,a.Sequence,a.SourceCode,a.SourceSequence,a.InvCode,a.Quantity,a.Amount,a.IssueQuantity
				            ,a.WHCode,a.Type,a.Status,a.CreatePerson,a.CreateDateTime,b.InvName
				            ,a.ApplyID,a.ApplyDetailID,a.ExtensionID,a.MUSER,a.MUSERName,a.MTIME,a.WorkPoint
                       from ICSOApply a 
					   left join ICSInventory b on a.InvCode = b.InvCode and a.WorkPoint = b.WorkPoint
                        where a.ApplyCode ='" + ApplyCode + "' ";
            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;
        }

        /// <summary>
        /// 接口api解析
        /// </summary>
        /// <param name="url"></param>
        /// <param name="body"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        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 SetData_PR(String savePath)
        {
            //数据获取
            try
            {
                int index = 1;
                string msg = "";
                string MUSER = NFine.Code.OperatorProvider.Provider.GetCurrent().UserCode;
                string MUSERNAME = NFine.Code.OperatorProvider.Provider.GetCurrent().UserName;
                string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
                SqlConnection conn = SqlHelper.GetDataCenterConn();
                string sql = "";
                string sqlss = "";
                int count = 0;
                DataTable data = FileToExcel.ExcelToTable(savePath);

                var groupedData = from row in data.AsEnumerable()
                                  group row by row.Field<string>("委外申请单号") into g
                                  select new { Category = g.Key, Values = g.ToList() };

                // 输出结果
                foreach (var group in groupedData)
                {

                    string CreateDateTime = DateTime.Now.ToString();
                    Console.WriteLine($"Category: {group.Category}");
                    foreach (DataRow row in group.Values)
                    {
                      
                        string IDD = string.Empty;
                        string ApplyCode = row["委外申请单号"].ToString();
                        string Sequence = row["申请单行号"].ToString();
                        string InvCode = row["助记码"].ToString();
                        string Quantity = row["数量"].ToString();
                        string WHCode = row["仓库代码"].ToString();
                        string EATTRIBUTE3 = row["物料代码"].ToString();
                        string EATTRIBUTE4 = row["组件"].ToString();
                        if (ApplyCode == "" || ApplyCode == null)
                        {
                            throw new Exception("第 " + index + " 行委外申请单号不能为空！");
                        }
                        if (Sequence == "" || Sequence == null)
                        {
                            throw new Exception("第 " + index + " 行申请单行号不能为空！");
                        }
                        if (InvCode == "" || InvCode == null)
                        {
                            throw new Exception("第 " + index + " 行助记码不能为空！");
                        }
                        if (Quantity == "" || Quantity == null)
                        {
                            throw new Exception("第 " + index + " 行数量不能为空！");
                        }
                        if (WHCode == "" || WHCode == null)
                        {
                            throw new Exception("第 " + index + " 行仓库代码不能为空！");
                        }
                        if (EATTRIBUTE3 == "" || EATTRIBUTE3 == null)
                        {
                            throw new Exception("第 " + index + " 行物料代码不能为空！");
                        }
                        
                        string sqls = @"select ApplyCode,Sequence from ICSOApply where ApplyCode='{0}' and Sequence='{2}' and WorkPoint='{1}'";
                        sqls = string.Format(sqls, ApplyCode, WorkPoint, Sequence);
                        DataTable dnum = SqlHelper.CmdExecuteDataTable(sqls);
                        if (dnum != null && dnum.Rows.Count > 0)
                        {
                            throw new Exception("委外领料单: " + ApplyCode + "，行号: " + Sequence + " 已存在！");
                        }
                        else
                        {

                            //检验自由项
                            string Colspan = row["批次"].ToString() + "~" + row["自由项1"].ToString()
                                    + "~" + row["自由项2"].ToString() + "~" + row["自由项3"].ToString() + "~" + row["自由项4"].ToString()
                                    + "~" + row["自由项5"].ToString() + "~" + row["自由项6"].ToString() + "~" + row["自由项7"].ToString()
                                    + "~" + row["自由项8"].ToString() + "~" + row["自由项9"].ToString() + "~" + row["自由项10"].ToString();
                            sql = @"select ID,Colspan from ICSExtension a
                                   where Colspan='{0}' and WorkPoint='{1}'";
                            sql = string.Format(sql, Colspan, WorkPoint);
                            var dtt = SqlHelper.CmdExecuteDataTable(sql);
                            if (dtt.Rows.Count == 0)
                            {
                                IDD = Guid.NewGuid().ToString();
                                sql = @"Insert into ICSExtension(ID, Colspan, BatchCode, cFree1, cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10, MTIME, MUSER, MUSERName, WorkPoint) 
                            select '{14}','{0}','{1}',
                            '{2}','{3}','{4}','{5}',
                '{6}','{7}','{8}','{9}','{10}','{11}',GETDATE(),'{12}',f.F_RealName,'{13}'from Sys_SRM_User f where f.F_Account='{12}' and f.F_Location='{13}'";
                                sql = string.Format(sql, Colspan, row["批次"].ToString(), row["自由项1"].ToString(), row["自由项2"].ToString(), row["自由项3"].ToString(), row["自由项4"].ToString(), row["自由项5"].ToString(), row["自由项6"].ToString(), row["自由项7"].ToString(), row["自由项8"].ToString(), row["自由项9"].ToString(), row["自由项10"].ToString(), MUSER, WorkPoint, IDD);
                                if (SqlHelper.CmdExecuteNonQueryLi(sql) < 0)
                                {
                                    throw new Exception("自由项添加失败");
                                }
                            }
                            else
                            {
                                IDD = dtt.Rows[0]["ID"].ToString();
                            }
                            sqlss += @"Insert INto  ICSOApply 
                                ( ID,ApplyCode,Sequence,SourceCode,SourceSequence,InvCode,Quantity,Amount,   IssueQuantity,WHCode,Type,Status,CreatePerson,CreateDateTime,ApplyID,ApplyDetailID,ExtensionID,MUSER,MUSERName,MTIME,WorkPoint,EATTRIBUTE3,EATTRIBUTE4)
                                Values (newid(),'{0}','{1}','{2}','{3}','{4}',{5},{6},
                            {7},'{8}','{9}','{10}','{11}','{17}','{12}','{13}','{14}',
                               '{11}','{15}',GETDATE(),'{16}','{17}','{18}' )";
                        }
                        sqlss = string.Format(sqlss, ApplyCode, Sequence, row["来源单据号"].ToString(), row["来源单据行号"].ToString(), InvCode, Quantity, row["辅计量数量"].ToString() == "" ? 0 : Convert.ToDecimal(row["辅计量数量"].ToString()), row["已发数量"].ToString() == "" ? 0 : Convert.ToDecimal(row["已发数量"].ToString()), WHCode, "1", "2", MUSER, "", "", IDD, MUSERNAME, WorkPoint, CreateDateTime, EATTRIBUTE3, EATTRIBUTE4);
                    }
                }

                //foreach (DataRow dr in data.Rows)
                //{
                //    index++;
                //    var StackCode = "";
                //    var StackName = "";
                //    //string GUID = Guid.NewGuid().ToString();
                //    string KuFang = dr["库房编码"].ToString().Trim().ToUpper();
                //    string Qu = dr["区"].ToString().Trim().ToUpper();
                //    string Pai = dr["排"].ToString().Trim().ToUpper();
                //    string Jia = dr["货架"].ToString().Trim().ToUpper();
                //    string Ceng = dr["层"].ToString().Trim().ToUpper();
                //    string Ge = dr["格"].ToString().Trim().ToUpper();
                //    if (KuFang == "" || KuFang == null)
                //    {
                //        throw new Exception("第 " + index + " 行库房编码不能为空！");
                //    }
                //    if (Qu == "" && Pai == "" && Jia == "" && Ceng == "" && Ge == "")
                //    {
                //        throw new Exception("第 " + index + " 区、排、货架、层、格至少填写一项！");
                //    }
                //    string sqlWH = @"select ID from ICSWarehouse where WarehouseCode='{0}' and WorkPoint='{1}'";
                //    sqlWH = string.Format(sqlWH, KuFang, WorkPoint);
                //    DataTable dd = SqlHelper.CmdExecuteDataTable(sqlWH);
                //    if (dd == null || dd.Rows.Count <= 0)
                //    {
                //        throw new Exception("库房编码: " + KuFang + " 不存在！");
                //    }
                //    string WHID = dd.Rows[0]["ID"].ToString();
                //    StackCode = KuFang;
                //    StackName = KuFang + "仓库";
                //    if (Qu != "")
                //    {
                //        StackCode += "-" + Qu;
                //        StackName += Qu + "区";
                //    }
                //    if (Pai != "")
                //    {
                //        StackCode += "-" + Pai;
                //        StackName += Pai + "排";
                //    }
                //    if (Jia != "")
                //    {
                //        StackCode += "-" + Jia;
                //        StackName += Jia + "货架";
                //    }
                //    if (Ceng != "")
                //    {
                //        StackCode += "-" + Ceng;
                //        StackName += Ceng + "层";
                //    }
                //    if (Ge != "")
                //    {
                //        StackCode += "-" + Ge;
                //        StackName += Ge + "格";
                //    }
                //    string sqls = @"select LocationCode from ICSLocation where LocationCode='{0}' and WorkPoint='{1}'";
                //    sqls = string.Format(sqls, StackCode, WorkPoint);
                //    DataTable dnum = SqlHelper.CmdExecuteDataTable(sqls);
                //    if (dnum != null && dnum.Rows.Count > 0)
                //    {
                //        throw new Exception("库位: " + StackCode + " 已存在！");
                //    }
                //    else
                //    {
                //        sql += @"Insert INto  ICSLocation 
                //                ( ID,   LocationCode,   LocationName,   WHID,   MUSER,   MUSERName,   MTIME,   WorkPoint,   EATTRIBUTE1,   Region,   Row,   GoodsShelf,   Tier,   Grid)
                //                Values (newid(),'{0}','{1}','{2}','{3}','{4}',getdate(),'{5}','','{6}','{7}','{8}','{9}','{10}')";
                //    }
                //    sql = string.Format(sql, StackCode, StackName, WHID, MUSER, MUSERNAME, WorkPoint, Qu, Pai, Jia, Ceng, Ge);

                //}
                count = SqlHelper.CmdExecuteNonQueryLi(sqlss);
                if (count > 0)
                {
                    msg = "导入成功";
                }
                else
                {
                    return "无有效的导入数据。";

                }
                return msg;

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

            }
        }


        /// <summary>
        /// 全部导出
        /// <returns></returns>
        public DataTable GetASNListExport()
        {
            string sql = @"SELECT a.ApplyCode AS 委外申请单号
                        ,a.Sequence AS 委外申请单行号
                        ,a.SourceCode AS 来源单据号
                        ,a.SourceSequence AS 来源单据行号
                        ,a.IssueQuantity AS 已发数量
                        ,a.Quantity AS 数量
                        ,a.WHCode AS 仓库代码
                        ,e.WarehouseName as 仓库名称
                        ,a.Type AS 采购类型
                        ,a.InvCode AS 物料代码
                        ,b.InvName AS 物料名称
                        ,a.Amount AS 辅计量
                        ,a.Status AS 状态
                        ,a.CreatePerson AS 创建人
                        ,a.CreateDateTime AS 创建时间
                        ,a.MUSER AS 操作人
                        ,a.MUSERName AS 操作人名称
                        ,a.MTIME AS 操作时间
                        ,a.WorkPoint AS 站点
                        from ICSOApply 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 e on a.WHCode = e.WarehouseCode and a.WorkPoint=e.WorkPoint";
            
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }
    }
    }