using Hh.Mes.Common.config;
using Hh.Mes.Common.log;
using Hh.Mes.Common.Request;
using Hh.Mes.POJO.Entity;
using Hh.Mes.POJO.Response;
using Hh.Mes.Service.Repository;
using NPOI.SS.Formula.Functions;
using Org.BouncyCastle.Crypto;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hh.Mes.Service.Material
{
    public class MaterialCallService : RepositorySqlSugar<dynamic>
    {
        /// <summary>
        /// 物料呼叫，需求库位
        /// </summary>
        /// <returns></returns>
        public Task<DataSet> GetSelectData()
        {
            string sql = @" SELECT t1.code, srmCode   FROM [dbo].[base_location] t1 WHERE type = 'A' OR type = 'B'
                            select WorkshopId, WorkshopCode, LineCode, LineName, Id  from Line";

            return base.Context.Ado.GetDataSetAllAsync(sql);
        }

        /// <summary>
        /// 获取物料
        /// </summary>
        public Response GetMaterialList(PageReq pageReq, base_material entity)
        {
            var result = new Response();
            string orderBy = (pageReq == null || string.IsNullOrEmpty(pageReq.field)) ? " id desc" : $"{pageReq.field} {pageReq.order} ";
            string sqlWhere = SqlMateriaWhere(entity);
            var stringBuilder = new StringBuilder();
            //页码，页数
            //Exel ture 不分页
            if (!entity.Exel && pageReq != null)
            {
                stringBuilder.Append("declare @pageIndex int,@pageSize int,@offset int");
                stringBuilder.AppendLine($"  select @pageIndex={pageReq.page}, @pageSize={pageReq.limit}, @offset=(@pageIndex - 1) * @pageSize");
            }

            stringBuilder.AppendLine($@" select t3.quantityRate, t2.mtTypeName materialtypeName,t1.*  
                                         from base_material t1 with(nolock)
                                         left join base_material_type t2 with(nolock) on t1.MtTypeCode=t2.mtTypeCode  
                                         left join base_material_rate t3   with(nolock) on t1.materialCode=t3.materialCode  
                                         where {sqlWhere} order by {orderBy} ");

            //Exel ture 不分页
            if (!entity.Exel)
            {
                stringBuilder.AppendLine("  offset @offset row fetch next @pageSize row only ");
                stringBuilder.Append($@" select rowTotal= count(*) from base_material t1 with(nolock)
                                         left join base_material_type t2 with(nolock) on t1.MtTypeCode=t2.mtTypeCode  where {sqlWhere}");
            }

            var parameters = new List<SugarParameter>(){
                new SugarParameter("@mtTypeCode",entity.mtTypeCode),
                new SugarParameter("@materialName",entity.materialName),
                new SugarParameter("@materialCode",entity.materialCode),
                //new SugarParameter("@codeOrName",entity.codeOrName),
                new SugarParameter("@plmeId",entity.plmeId)
            };

            var ds = base.Context.Ado.GetDataSetAll(stringBuilder.ToString(), parameters);
            result.Result = ds.Tables[0];
            result.Count = entity.Exel ? (int)result.Result.Rows.Count : (int)ds.Tables[1].Rows[0]["rowTotal"];
            return result;
        }

        public string SqlMateriaWhere(base_material model)
        {
            var stringBuilder = new StringBuilder();
            stringBuilder.Append(" 1=1 ");
            if (!string.IsNullOrEmpty(model.plmeId))
            {
                stringBuilder.Append(" and t1.plmeId=@plmeId ");
            }
            if (!string.IsNullOrEmpty(model.mtTypeCode))
            {
                stringBuilder.Append(" and  t1.mtTypeCode = @mtTypeCode ");
            }
            if (!string.IsNullOrEmpty(model.materialName))
            {
                stringBuilder.Append(" and  t1.materialName like '%'+@materialName+'%' ");
            }
            if (!string.IsNullOrEmpty(model.materialCode))
            {
                stringBuilder.Append(" and  t1.materialCode like '%'+@materialCode+'%' ");
            }
            //下拉弹出搜索框
            if (!string.IsNullOrEmpty(model.codeOrName))
            {
                stringBuilder.Append($" and  (t1.materialCode like '%'+@codeOrName+'%' or  t1.materialName like '%'+@codeOrName+'%' )   ");
            }
            return stringBuilder.ToString();
        }


        public dynamic Ins(base_material entity)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                entity.keys = Guid.NewGuid();
                entity.createBy = sysWebUser.Account;
                entity.createTime = DateTime.Now;

                var rate = new base_material_rate
                {
                    materialCode = entity.materialCode,
                    materialName = entity.materialName,
                    mtClassify = entity.mtClassify,
                    unitCode = entity.unitCode,
                    specifications = entity.specifications,
                    materialKeys = entity.keys,
                    quantityRate = 1,

                    createBy = sysWebUser?.Account,
                    createTime = DateTime.Now
                };

                Context.Insertable(entity).AddQueue();
                Context.Insertable(rate).AddQueue();
                Context.SaveQueues();
                return response;
            });
        }



        /// <summary>
        /// 更新 物料
        /// </summary>
        public dynamic Upd(base_material model)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                model.updateBy = sysWebUser?.Account;
                model.updateTime = DateTime.Now;
                response.Status = Context.Updateable(model).Where(u => u.id == model.id).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "更新失败";
                return response;
            });
        }

        /// <summary>
        /// 删除 物料
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public dynamic DelByIds(Guid[] ids)
        {
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var response = new Response();
                Context.Deleteable<base_material>(t => ids.Contains(t.keys)).ExecuteCommand();
                Context.Deleteable<base_material_rate>(t => ids.Contains(t.materialKeys)).ExecuteCommand();
                return response;
            });
        }


        /// <summary>
        /// 左侧列表  
        /// </summary>
        public DataTable GetTreeList()
        {
            string sql = @"select t1.id,name=t1.mtTypeName ,  t1.mtTypeCode ,t1.keys,parentId=t1.parentTypeId		     
						   from base_material_type t1";
            var dt = base.Context.Ado.GetDataTable(sql);
            return dt == null || dt.Rows.Count == 0 ? null : dt;
        }

        /// <summary>
        /// 编辑 物料比例表数据
        /// </summary>
        public dynamic EditRate(base_material_rate model)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                model.updateBy = sysWebUser?.Account;
                model.updateTime = DateTime.Now;
                response.Status = Context.Updateable(model)
                                         .UpdateColumns(t => new { t.oprSequenceCode, t.quantityRate })
                                         .Where(u => u.id == model.id).ExecuteCommand() > 0;
                if (!response.Status) response.Message = "更新失败";
                return response;
            });
        }

        /// <summary>
        /// 查找比率数据
        /// </summary>
        /// <returns></returns>
        public dynamic GetMaterialRateByCode(Guid materialKeys)
        {
            var response = new Response();
            return ExceptionsHelp.Instance.ExecuteT(() =>
            {
                var result = base.Context.Queryable<base_material_rate>()
                                 .Where(x => x.materialKeys == materialKeys).ToList();
                response.Result = result;
                return response;
            });
        }


        public string GetMaterialUrl()
        {
            var urlDic = "UrlMaterialUpCict" + ConfigRead.GetInstance.GetAppsetConnection().AppCustomExtend1;
            var url = GetDictionaryDictValue(urlDic, "GetUrl");
            return url + "api/Upstream/Mes/V1/MaterialUpCict";
        }
    }
}