using NFine.Repository;
using NFine.Domain._03_Entity.SRM;
using NFine.Data.Extensions;
using System;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.Data.SqlClient;
using System.Data;
using NFine.Code;

namespace NFine.Application.PNWMS
{
   public class BasicSettingsApp : RepositoryFactory<ICSVendor>
    {

        public string DeleteStack(string keyValue)
        {
            //站点信息
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string msg = "";
            JArray res = (JArray)JsonConvert.DeserializeObject(keyValue);
            //keyValue = keyValue.Substring(1, keyValue.Length - 2);
            string sql = string.Empty;
            foreach (var item in res)
            {
                JObject jo = (JObject)item;
                sql = string.Format(@"
                            IF  EXISTS(SELECT * FROM dbo.ICSWareHouseLotInfo a
                                        LEFT JOIN dbo.ICSLocation b ON a.LocationCode=b.LocationCode and a.WorkPoint=b.WorkPoint
                                         where b.ID  = '{0}' and a.Quantity>0)
                            BEGIN
                                  RAISERROR('选中库位{2}在库存中已经有库存，请先确认!',16,1);
                                        RETURN
                            END
                            DELETE FROM dbo.ICSLocation  WHERE ID = '{0}' and WorkPoint ='{1}'", jo["ID"].ToString(), WorkPoint, jo["LocationCode"].ToString());
                try
                {
                    SqlHelper.ExecuteNonQuery(sql);
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    //throw new Exception(ex.Message);
                }
            }


            return msg;
        }

        //库位导入
        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 = "";
                int count = 0;
                DataTable data = FileToExcel.ExcelToTable(savePath);
                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(sql);
                if (count > 0)
                {
                    msg = "导入成功";
                }
                else
                {
                    return "无有效的导入数据。";

                }
                return msg;

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

            }

        }


        /// <summary>
        /// 获取库房信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetWarehouse()
        {
            string WorkPoint = NFine.Code.OperatorProvider.Provider.GetCurrent().Location;
            string sql = @"
	 select '' as WarehouseCode,'' as WarehouseName 
union all
SELECT [WarehouseCode],[WarehouseName] FROM [ICSWarehouse] WITH (NOLOCK) WHERE  1=1 ";
            //string role = NFine.Code.OperatorProvider.Provider.GetCurrent().RoleEnCode;
            //if (role != "admin")
            //{
            //    sql += " and b.WorkPoint='" + WorkPoint + "'";
            //}
            sql += " and WorkPoint='" + WorkPoint + "'";
            DataTable dt = SqlHelper.GetDataTableBySql(sql);
            return dt;
        }



        /// <summary>
        /// 修改库位
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public string UpdateStack(string keyValue)
        {
            var queryParam = keyValue.ToJObject();
            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 StackSql = @"select * from ICSWarehouse where WarehouseCode='{0}'";
            StackSql = string.Format(StackSql, queryParam["Warehouse"].ToString());
            DataTable dt = SqlHelper.GetDataTableBySql(StackSql);
            string sql = string.Empty;
            sql = @"UPDATE dbo.ICSLocation set LocationCode='{0}',LocationName='{1}',WHID='{2}',MUSER='{3}',MUSERName='{4}',MTIME=GETDATE(),WorkPoint='{5}',Region='{7}',Row='{8}',GoodsShelf ='{9}',Tier='{10}',Grid='{11}' WHERE ID='{6}'";
            sql = string.Format(sql, queryParam["StackCode"].ToString(), queryParam["StackName"].ToString(), dt.Rows[0]["ID"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["ID"].ToString(), queryParam["Qu"].ToString(), queryParam["Pai"].ToString(), queryParam["Jia"].ToString(), queryParam["Ceng"].ToString(), queryParam["Ge"].ToString());
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql) > 0)
                {

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

            return msg;
        }

        /// <summary>
        /// 新增库位
        /// </summary>
        /// <param name="queryJson"></param>
        /// <returns></returns>
        public string InsertStack(string keyValue)
        {
            var queryParam = keyValue.ToJObject();
            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 StackSql = @"select * from ICSWarehouse where WarehouseCode='{0}' and WorkPoint='{1}'";
            StackSql = string.Format(StackSql, queryParam["Warehouse"].ToString(), WorkPoint);
            DataTable dt = SqlHelper.GetDataTableBySql(StackSql);
            string sql = string.Empty;
            sql = @"IF ((SELECT 1 FROM ICSLocation WHERE LocationCode='{0}' AND LocationName='{1}' AND WHID='{2}' AND WorkPoint='{5}' ) IS NULL)
	                    BEGIN
		                    INSERT INTO dbo.ICSLocation
                                ( ID ,LocationCode ,LocationName ,WHID ,MUSER ,
                                    MUSERName ,MTIME ,WorkPoint ,Region,Row,GoodsShelf ,Tier,Grid) Values(NEWID(),'{0}','{1}','{2}','{3}','{4}',GETDATE(),'{5}','{6}','{7}','{8}','{9}','{10}')
	                    END
                    ELSE
	                    BEGIN
		                    RAISERROR('库位已存在',16,1);
	                    END	";
            sql = string.Format(sql, queryParam["StackCode"].ToString(), queryParam["StackName"].ToString(), dt.Rows[0]["ID"].ToString(), MUSER, MUSERNAME, WorkPoint, queryParam["Qu"].ToString(), queryParam["Pai"].ToString(), queryParam["Jia"].ToString(), queryParam["Ceng"].ToString(), queryParam["Ge"].ToString());
            try
            {
                if (SqlHelper.ExecuteNonQuery(sql) > 0)
                {

                }
                else
                {
                    msg = "新增失败";
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return msg;
            }

            return msg;
        }















    }
}