using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Models;
using Estsh.Core.Repository.IRepositories;
using Estsh.Core.Util;
using System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：零星生产计划
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class StockOrderReleaseRepository : BaseRepository<GOrder>, IStockOrderReleaseRepository
    {
        public StockOrderReleaseRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 获取客户产线列表
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetCustPDLine()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT  cust_pdline_name as [value], ");
                SqlStringBuilder.Append("cust_pdline_name + ' : ' + cust_pdline_desc as [key] ");
                SqlStringBuilder.Append("FROM    dbo.sys_cust_pdline ");
                SqlStringBuilder.Append("WHERE   enabled = 'Y' ");
                SqlStringBuilder.Append("ORDER BY cust_pdline_name ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 根据客户产线查询车型
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> GetMode_type(string CustPDLine)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string sql = "select type_id as [value], type_name as [key] from dbo.sys_model_type WHERE enabled='Y' and cust_pdline_id= (select cust_pdline_id from sys_cust_pdline where cust_pdline_name='" + CustPDLine + "')";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
                return result;
            }
        }


        ///<summary>
        /// 调用存储过程 dbo.sys_release_stock_order 释放生产指令
        /// </summary>
        /// <returns></returns>
        public string ReleaseOrder(string cbCustPDLine, int cbShift, int Qty)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters Parameters = new DynamicParameters();
                Parameters.Add("@tray_no", "");
                Parameters.Add("@use_old_tray", 0);
                Parameters.Add("@cust_pdline", cbCustPDLine);
                Parameters.Add("@shift_code", cbShift);
                Parameters.Add("@qty", Qty);
                Parameters.Add("@pc_name", "");
                Parameters.Add("@tres", null, DbType.String, ParameterDirection.Output, 500);
                var hashtable = dbConn.Execute("sys_release_order", Parameters, commandType: CommandType.StoredProcedure);
                string result = Parameters.Get<string>("@tres");
                return result;
            }
        }

        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="aWhere"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public List<GOrder> GetOrderSummary(string cust_pdline, ref Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("( SELECT TOP 1000 ");
                SqlStringBuilder.Append("      a.order_id, ");
                SqlStringBuilder.Append("      order_seq , ");
                SqlStringBuilder.Append("      type_name , ");
                SqlStringBuilder.Append("      c.model_name , ");
                SqlStringBuilder.Append("      car_no , ");
                SqlStringBuilder.Append("      vin , ");
                SqlStringBuilder.Append("      ( CASE status ");
                SqlStringBuilder.Append("          WHEN 1 THEN '否' WHEN 3 THEN '是' ");
                SqlStringBuilder.Append("          ELSE '' ");
                SqlStringBuilder.Append("        END ) AS isEmpty, ");
                SqlStringBuilder.Append("       (CASE [error_message] WHEN '' THEN '正常' ELSE '异常' END ) AS [error_message] ");
                SqlStringBuilder.Append("FROM  dbo.g_order AS a ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_part AS b ON a.model_part_id = b.part_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_model AS c ON b.model_id = c.model_id ");
                SqlStringBuilder.Append("      LEFT JOIN dbo.sys_model_type AS d ON c.model_type_id = d.type_id ");
                SqlStringBuilder.Append("WHERE cust_pdline = '1002' ");
                SqlStringBuilder.Append("      AND a.status in ( 0,1,3) ");
                SqlStringBuilder.Append("      AND error_message = '') AS aa ");

                parameters.Add("@Table", SqlStringBuilder.ToString());
                parameters.Add("@Column", " * ");
                parameters.Add("@OrderColumn", " order_seq ");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", " 1=1 ");
                List<GOrder> result = dbConn.Query<GOrder>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                pager.totalRows = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        /// <summary>
        /// 明细
        /// </summary>
        /// <param name="aWhere">查询条件</param>
        /// <returns></returns>
        public List<GOrder> GetOrderDetail(string aWhere, ref Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("( SELECT TOP 1000 ");
                SqlStringBuilder.Append("                 order_seq , ");
                SqlStringBuilder.Append("                 type_name , ");
                SqlStringBuilder.Append("                 c.model_name , ");
                SqlStringBuilder.Append("				 f.part_no, ");
                SqlStringBuilder.Append("				 f.part_spec, ");
                SqlStringBuilder.Append("                 car_no , ");
                SqlStringBuilder.Append("                 vin , ");
                SqlStringBuilder.Append("                 ( CASE g.status ");
                SqlStringBuilder.Append("                     WHEN 0 THEN '否' ");
                SqlStringBuilder.Append("                     ELSE '' ");
                SqlStringBuilder.Append("                   END ) AS is_charge ");
                SqlStringBuilder.Append("          FROM   dbo.g_order AS a ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.g_order_detail AS e ON a.order_id = e.order_id ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.g_order_items AS g ON e.ruid = g.order_detail_id ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.sys_part AS b ON a.model_part_id = b.part_id ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.sys_model AS c ON b.model_id = c.model_id ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.sys_model_type AS d ON c.model_type_id = d.type_id ");
                SqlStringBuilder.Append("                 LEFT JOIN dbo.sys_part AS f ON f.part_no = g.part_no ");
                SqlStringBuilder.Append("          WHERE  cust_pdline = '1002' ");
                SqlStringBuilder.Append("                 AND a.status in ( 0,1,3) ");
                SqlStringBuilder.Append("                 AND error_message = '' ");
                SqlStringBuilder.Append("        ) AS aa ");

                parameters.Add("@Table", SqlStringBuilder.ToString());
                parameters.Add("@Column", " * ");
                parameters.Add("@OrderColumn", "order_seq ");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", " 1=1 ");
                List<GOrder> result = dbConn.Query<GOrder>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                pager.totalRows = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        /// <summary>
        /// 异常
        /// </summary>
        /// <param name="aWhere"></param>
        /// <returns></returns>
        public List<GWorkorder> GetOrderErrorMessage(string aWhere, ref Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                StringBuilder table = new StringBuilder();
                table.Append(" g_workorder as a ");
                table.Append(" left join sys_pdline as b on a.pdline_id=b.pdline_id ");
                table.Append(" left join sys_part as c on a.part_id=c.part_id ");
                table.Append(" left join sys_model as d on c.model_id=d.model_id ");
                table.Append(" left join g_workorder_detail as e on a.ruid=e.workorder_id ");
                table.Append(" left join g_workorder_sn as  f on e.ruid=f.wo_detail_id ");
                table.Append(" left join g_sn_status as g on f.serial_number=g.serial_number ");
                table.Append(" left join sys_part as h on h.part_id=e.part_id ");
                table.Append(" left join sys_model_type as i on i.type_id=d.model_type_id ");
                parameters.Add("@Table", table.ToString());

                StringBuilder column = new StringBuilder();
                column.Append(" a.car_no,e.seq,b.pdline_name,i.type_name,d.model_name,d.model_desc,h.part_no,h.part_spec,case e.enabled when 'N' then '不生产' when 'Y' then '生产' ELSE '' END as enabled,");
                column.Append(" CASE g.work_flag WHEN '-1' THEN '指令释放' WHEN '0' THEN '已上线' WHEN '1' THEN '已下线' WHEN '2' THEN '已发运' ELSE '' END as work_flag ,");
                column.Append(" CONVERT(varchar, g.in_pdline_time, 120) AS in_pdline_time,CONVERT(varchar, g.out_pdline_time, 120) AS out_pdline_time, ");
                column.Append(" g.create_time as create_time,  ");
                column.Append(" CONVERT(varchar,g.shipping_time,120) AS shipping_time,g.serial_number ");

                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", " e.seq ");
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", aWhere);
                List<GWorkorder> result = dbConn.Query<GWorkorder>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                pager.totalRows = parameters.Get<int>("@TotalCount");
                return result;
            }
        }

        /// <summary>
        /// 获取班次
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> Getshift()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = "SELECT shift_id as [value] ,shift_code as [key] from sys_shift where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
                return result;
            }
        }

        //获取单号
        public string getNumber()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " SELECT param_value FROM dbo.sys_base WHERE param_name='ProductionTaskNumber' ";
                object obj = dbConn.ExecuteScalar(SqlString);
                return obj == null ? "0" : obj.ToString();
            }
        }

        //变更状态
        public string changeStatus(int order_id, int status)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " UPDATE dbo.g_order SET status=" + status + " WHERE order_id=" + order_id + "  ";
                object obj = dbConn.ExecuteScalar(SqlString);
                return obj == null ? "0" : obj.ToString();
            }
        }

        /// <summary>
        /// 获取单号
        /// </summary>
        /// <param name="order_id"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public string getProductionTaskNumber()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " SELECT param_value FROM dbo.sys_base WHERE param_name='ProductionTaskNumber' ";
                object obj = dbConn.ExecuteScalar(SqlString);
                return obj == null ? "0" : obj.ToString();
            }
        }

        public string updateProductionTaskNumber()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " UPDATE dbo.sys_base SET param_value=(ISNULL(param_value,0)+1) WHERE param_name='ProductionTaskNumber' ";
                int obj = dbConn.Execute(SqlString);
                return  obj.ToString();
            }
        }

        public string updateWorkOrderPrintNumber(string ruid, string ProductionTaskNumber)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " UPDATE dbo.g_workorder SET print_number=" + ProductionTaskNumber + " WHERE ruid=" + ruid;
                int obj = dbConn.Execute(SqlString);
                return obj.ToString();
            }
        }

        /// <summary>
        /// 初始化单号
        /// </summary>
        /// <returns></returns>
        public bool InitializeNumber()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string SqlString = " UPDATE dbo.sys_base SET param_value=1 WHERE param_name='ProductionTaskNumber' ";
                int obj = dbConn.Execute(SqlString);
                return obj>0;
            }
        }

        /// <summary>
        /// 获取打印数据
        /// </summary>
        /// <returns></returns>
        public List<GWorkorder> getPrintData(string cbCustPDLine)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT DISTINCT ");
                SqlStringBuilder.Append("     a.ruid , ");
                SqlStringBuilder.Append("     b.part_no , ");
                SqlStringBuilder.Append("     b.part_spec2 , ");
                SqlStringBuilder.Append("     '1'  AS qty , ");
                SqlStringBuilder.Append("     pdline_name , ");
                SqlStringBuilder.Append("     e.order_seq , ");
                SqlStringBuilder.Append("     a.create_time AS date , ");
                SqlStringBuilder.Append("     dateadd(n,-CONVERT(INT,(SELECT TOP 1 param_value FROM dbo.sys_base WHERE param_name='LeadTimeAdvance')),CONVERT(DATETIME,g.on_line_time)) AS date1 , ");
                SqlStringBuilder.Append("     dateadd(n,-CONVERT(INT,(SELECT TOP 1 param_value FROM dbo.sys_base WHERE param_name='RecommendedOnLineTime')),CONVERT(DATETIME,g.on_line_time)) AS date2 ");
                SqlStringBuilder.Append("FROM dbo.g_workorder AS a ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.sys_part AS b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_wo_relation AS c ON c.workorder_id = ruid ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_order AS e ON c.order_id = e.order_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.sys_pdline AS f ON f.pdline_id = a.pdline_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_order_detail AS g ON g.order_id = e.order_id ");
                SqlStringBuilder.Append("WHERE   a.print_number = 0  AND a.pdline_id=100002 AND e.cust_pdline=1002  and type='N' ");
                SqlStringBuilder.Append("ORDER BY e.order_seq ");

                List<GWorkorder> result = dbConn.Query<GWorkorder>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取打印数据
        /// </summary>
        /// <returns></returns>
        public List<GWorkorder> getPrintData(string cbCustPDLine, string printDate, string printNumber)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT DISTINCT ");
                SqlStringBuilder.Append("     a.ruid , ");
                SqlStringBuilder.Append("     b.part_no , ");
                SqlStringBuilder.Append("     b.part_spec2 , ");
                SqlStringBuilder.Append("     '1'  AS qty , ");
                SqlStringBuilder.Append("     pdline_name , ");
                SqlStringBuilder.Append("     e.order_seq , ");
                SqlStringBuilder.Append("     a.print_number , ");
                SqlStringBuilder.Append("     a.create_time AS date , ");
                SqlStringBuilder.Append("     dateadd(n,-CONVERT(INT,(SELECT TOP 1 param_value FROM dbo.sys_base WHERE param_name='LeadTimeAdvance')),CONVERT(DATETIME,g.on_line_time)) AS date1 , ");
                SqlStringBuilder.Append("     dateadd(n,-CONVERT(INT,(SELECT TOP 1 param_value FROM dbo.sys_base WHERE param_name='RecommendedOnLineTime')),CONVERT(DATETIME,g.on_line_time)) AS date2 ");
                SqlStringBuilder.Append("FROM dbo.g_workorder AS a ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.sys_part AS b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_wo_relation AS c ON c.workorder_id = ruid ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_order AS e ON c.order_id = e.order_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.sys_pdline AS f ON f.pdline_id = a.pdline_id ");
                SqlStringBuilder.Append("     LEFT JOIN dbo.g_order_detail AS g ON g.order_id = e.order_id ");
                SqlStringBuilder.Append("WHERE  a.print_number = " + printNumber + " AND a.create_time='" + printDate + "'  AND a.pdline_id=100002 AND e.cust_pdline=1002 and type='N' ");
                SqlStringBuilder.Append("ORDER BY e.order_seq ");

                List<GWorkorder> result = dbConn.Query<GWorkorder>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        public string getPrintQty()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT TOP 1  ISNULL(param_value,0) FROM dbo.sys_base WHERE param_name='PagePrintQuantity'");
                object obj = dbConn.ExecuteScalar(SqlStringBuilder.ToString());
                return obj.ToString();
            }
        }
    }
}