using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class ReworkPartRepository : BaseRepository<BaseEntity>, IReworkPartRepository
    {
        public ReworkPartRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        /// <summary>
        /// 获取返工返修物料拉动的数据
        /// </summary>
        /// <returns></returns>
        public List<MesWmsReworkPart> GetReworkPartInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.Append("select * from MES_WMS_REWORK_PART where syn_time in (select top 1 syn_time from MES_WMS_REWORK_PART where syn_flg = '10' order by syn_time) and syn_flg = '10' ");
                SqlStringBuilder.Append("select * from MES_WMS_REWORK_PART (nolock) where serial_number in (select top 1 serial_number from MES_WMS_REWORK_PART (nolock) where syn_flg = '10' order by syn_time) and syn_flg = '10' ");
                return dbConn.Query<MesWmsReworkPart>(SqlStringBuilder.ToString()).ToList();
            }
        }

        /// <summary>
        /// 获取订单编号
        /// </summary>
        /// <returns></returns>
        public string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        /// <summary>
        /// 处理返工返修物料拉动的数据
        /// </summary>
        /// <param name="sapSu"></param>
        /// <returns></returns>
        public bool InsertReworkPartInfo(List<MesWmsReworkPart> mesWmsReworkList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsSubcontractData wmsSubcontract = new WmsSubcontractData();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                DynamicParameters parameters = new DynamicParameters();

                //List<MesWmsReworkPart> WmsRework = mesWmsReworkList.GroupBy(a => a.SerialNumber).Select(b => new MesWmsReworkPart
                //{
                //    SerialNumber = b.Key,
                //    ItemQty = b.Count()
                //}).ToList();


                //sqlList = new List<string>();
                //parametersList = new List<DynamicParameters>();
                //List<MesWmsReworkPart> mesWmsReworks = mesWmsReworkList.Where(a => a.SerialNumber == WmsRework[0].SerialNumber).ToList();
                List<MesWmsReworkPart> mesWmsReworks = mesWmsReworkList;

                StringBuilder factoryStringBuilder = new StringBuilder(1024);
                factoryStringBuilder.Append("select * from sys_factory (nolock) where factory_code='" + mesWmsReworks[0].FactoryCode + "'");//工厂代码获取工厂ID
                List<SysFactory> sysFactory = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();

                SqlStringBuilder = new StringBuilder();
                SqlStringBuilder.Append(" SELECT * from sys_pdline (nolock) WHERE pdline_code='" + mesWmsReworks[0].PdlineCode + "'");
                List<SysPdline> sysPdlines = dbConn.Query<SysPdline>(SqlStringBuilder.ToString()).ToList();

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_move_header ");
                SqlStringBuilder.Append("           (order_no,order_type,order_status,ref_order_no ");
                SqlStringBuilder.Append("           ,factory_id,factory_code,enabled,create_userid ");
                SqlStringBuilder.Append("           ,create_time,guid) ");
                SqlStringBuilder.Append("     VALUES(@orderNo,@orderType,@orderStatus,@refOrderNo  ");
                SqlStringBuilder.Append("            ,@factoryId,@factoryCode,@enabled,@createUserid ");
                SqlStringBuilder.Append("           ,@createTime,@guid) ");
                sqlList.Add(SqlStringBuilder.ToString());

                string orderNo = GetOrderNo("ShiftWarehouse", "RR");
                parameters = new DynamicParameters();
                parameters.Add("@orderNo", orderNo);//返修NC入库单
                parameters.Add("@orderType", (int)WmsEnumUtil.MoveOrderType.REWORK_NC_IN_STOCK);//NC返修NC入库单
                parameters.Add("@orderStatus", (int)WmsEnumUtil.MoveOrderStatus.CREATE);//已创建
                parameters.Add("@refOrderNo", "");//

                if (sysFactory.Count > 0)
                {
                    parameters.Add("@factoryId", sysFactory[0].FactoryId);//
                }
                else
                {
                    parameters.Add("@factoryId", 0);//
                }
                parameters.Add("@factoryCode", mesWmsReworks[0].FactoryCode);//工厂
                parameters.Add("@enabled", "Y");//
                parameters.Add("@createUserid", 0);//
                parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                parameters.Add("@guid", Guid.NewGuid().ToString());//
                parametersList.Add(parameters);

                for (int i = 0; i < mesWmsReworks.Count; i++)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail ");
                    SqlStringBuilder.Append("           ([order_no], [item_no], [part_id], [part_no], [part_spec], [qty], [pick_qty], [out_qty], [rec_qty], [unit], [plan_date] ");
                    SqlStringBuilder.Append("           , [plan_time], [item_status], [src_warehouse_id], [src_warehouse_name], [dest_warehouse_id], [dest_warehouse_name] ");
                    SqlStringBuilder.Append("           , [src_zone_id], [src_zone_name], [dest_zone_id], [dest_zone_name], [src_erp_warehouse], [dest_erp_warehouse] ");
                    SqlStringBuilder.Append("           , [factory_id], [factory_code], [enabled], [create_userid], [create_time] ");
                    SqlStringBuilder.Append("           , [update_userid], [update_time], [guid]) ");
                    SqlStringBuilder.Append("     VALUES(@orderNo,@itemNo,@partId,@partNo,@partSpec,@qty,@pickQty,@outQty,@rec_qty,@unit ");
                    SqlStringBuilder.Append("           ,@planDate,@planTime,@itemStatus,@srcWarehouseId,@srcWarehouseName,@dest_warehouse_id,@dest_warehouse_name,@srcZoneId ");
                    SqlStringBuilder.Append("           ,@srcZoneName,@dest_zone_id,@dest_zone_name,@srcErpWarehouse,@dest_erp_warehouse,@factoryId "); 
                    SqlStringBuilder.Append("           ,@factoryCode,@enabled,@createUserid,@createTime,@update_userid,@update_time,@guid) ");

                    sqlList.Add(SqlStringBuilder.ToString());
                    parameters = new DynamicParameters();
                    parameters.Add("@orderNo", orderNo);//返修NC入库单
                    parameters.Add("@itemNo", (i + 1) * 10);//序号

                    StringBuilder partNoStringBuilder = new StringBuilder(1024);
                    partNoStringBuilder.Append("select * from sys_part (nolock) where part_no='" + mesWmsReworks[i].ItemPartNo + "'");//零件号ID
                    List<SysPart> sysPart = dbConn.Query<SysPart>(partNoStringBuilder.ToString()).ToList();
                    if (sysPart.Count > 0)
                    {
                        parameters.Add("@partId", sysPart[0].PartId);//
                        parameters.Add("@partNo", sysPart[0].PartNo);//零件号
                        parameters.Add("@partSpec", sysPart[0].PartSpec);//简码
                        parameters.Add("@unit", sysPart[0].Unit);//单位
                    }
                    else
                    {
                        parameters.Add("@partId", 0);//零件号ID
                        parameters.Add("@partNo", mesWmsReworks[i].ItemPartNo);//零件号
                        parameters.Add("@partSpec", "");//简码
                        parameters.Add("@unit", "");//单位
                    }

                    parameters.Add("@qty", mesWmsReworks[i].ItemQty);//数量
                    parameters.Add("@pickQty", 0);//
                    parameters.Add("@outQty", 0);//
                    parameters.Add("@rec_qty", 0);//
                    parameters.Add("@planDate", "");//计划日期
                    parameters.Add("@planTime", "");//计划时间
                    parameters.Add("@itemStatus", "10");//状态

                    if (sysPdlines.Count > 0)
                    {
                        parameters.Add("@srcWarehouseId", sysPdlines[0].SrcWarehouseId);//源仓库ID
                        parameters.Add("@srcWarehouseName", sysPdlines[0].SrcWarehouseName);//源仓库
                        parameters.Add("@dest_warehouse_id", "");//目标源仓库ID
                        parameters.Add("@dest_warehouse_name", "");//目标源仓库
                        parameters.Add("@srcZoneId", sysPdlines[0].SrcZoneId);//源库区ID
                        parameters.Add("@srcZoneName", sysPdlines[0].SrcZoneName);//源库区 
                        parameters.Add("@dest_zone_id", "");//目标源库区ID
                        parameters.Add("@dest_zone_name", "");//目标源库区 
                        parameters.Add("@srcErpWarehouse", "");//源ERP库存地
                        parameters.Add("@dest_erp_warehouse", "");//目标源ERP库存地
                        StringBuilder stringBuilder = new StringBuilder();
                        stringBuilder.Append(" SELECT * from sys_zone (nolock) WHERE zone_name='" + sysPdlines[0].SrcZoneName + "'");
                        List<SysZone> sysZones = dbConn.Query<SysZone>(stringBuilder.ToString()).ToList();
                        if (sysZones.Count > 0)
                        {
                            parameters.Add("@srcErpWarehouse", sysZones[0].ErpWarehouse);//ERP库存地
                        }
                        else
                        {
                            parameters.Add("@srcErpWarehouse", "");//ERP库存地
                        }
                    }
                    else
                    {
                        parameters.Add("@srcWarehouseId", "");//源仓库ID
                        parameters.Add("@srcWarehouseName", "");//源仓库
                        parameters.Add("@dest_warehouse_id", "");//目标源仓库ID
                        parameters.Add("@dest_warehouse_name", "");//目标源仓库
                        parameters.Add("@srcZoneId", "");//源库区ID
                        parameters.Add("@srcZoneName", "");//源库区 
                        parameters.Add("@dest_zone_id", "");//目标源库区ID
                        parameters.Add("@dest_zone_name", "");//目标源库区 
                        parameters.Add("@srcErpWarehouse", "");//源ERP库存地
                        parameters.Add("@dest_erp_warehouse", "");//目标源ERP库存地
                    }

                    if (sysFactory.Count > 0)
                    {
                        parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                    }
                    else
                    {
                        parameters.Add("@factoryId", 0);
                    }
                    parameters.Add("@factoryCode", mesWmsReworks[i].FactoryCode);//工厂代码
                    parameters.Add("@enabled", "Y");
                    parameters.Add("@createUserid", 0);
                    parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                    parameters.Add("@update_userid", 0);
                    parameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                    parameters.Add("@guid", Guid.NewGuid().ToString());//
                    parametersList.Add(parameters);

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("update MES_WMS_REWORK_PART set syn_flg=@synFlg,syn_time=@synTime  where pdline_code=@pdlineCode and item_part_no=@itemPartNo ");//更新同步状态
                    sqlList.Add(SqlStringBuilder.ToString());

                    parameters = new DynamicParameters();
                    parameters.Add("@pdlineCode", mesWmsReworks[i].PdlineCode);//产线
                    parameters.Add("@itemPartNo", mesWmsReworks[i].ItemPartNo);//零件号
                    parameters.Add("@serialNumber", mesWmsReworks[i].SerialNumber);//条码
                    parameters.Add("@synFlg", "20");//
                    parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                    parametersList.Add(parameters);
                }

                bool staResult = ExecuteTransaction(sqlList, parametersList);
                if (staResult)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}