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

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：打包信息查询
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class ShippingInformationDefineRepository : BaseRepository<GMisco>, IShippingInformationDefineRepository
    {
        public ShippingInformationDefineRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        /// <summary>
        /// 客户产线集合
        /// </summary>
        /// <returns>数据集</returns>
        public List<KeyValueResult> GetCustPDLineName()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("select cust_pdline_name as [value],cust_pdline_name as [key] from sys_cust_pdline where enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getShippingListByPage(int PageSize, int PageIndex, string strWhere)
        {
            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);
                parameters.Add( "@Table", @" dbo.g_misco a LEFT JOIN sys_part b ON a.part_no=b.part_no ");
                parameters.Add( "@Column", @" order_no,werks,a.part_no,b.part_spec, qty,piqty,case  when  piqty=qty   then '已完成'when  piqty<qty   then  '未完成'else '其他'   END  AS flag,a.update_time,a.a.create_time  ");
                parameters.Add( "@OrderColumn", " a.update_time desc ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add( "@Condition", "1=1" + strWhere);

                Hashtable result = new Hashtable();
                List<GMisco> depts = dbConn.Query<GMisco>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", depts);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        // <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getShippingListDetailByPage(int PageSize, int PageIndex, string strWhere)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                Hashtable values = new Hashtable(2);

               DynamicParameters parameters = new DynamicParameters();
                if (!string.IsNullOrEmpty(strWhere))
                { 
                   strWhere=strWhere.Replace("update","create");
                
                }
                   
                parameters.Add( "@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add( "@Table", "  dbo.g_misco_sn a LEFT JOIN dbo.g_misco b ON a.order_no=b.order_no AND a.part_no=b.part_no LEFT JOIN sys_part d ON a.part_no=d.part_no AND d.part_type=1 ");
                parameters.Add( "@Column", " a.order_no,a.serial_number,a.part_no,d.part_spec,emp_name,a.create_time  ");
                parameters.Add( "@OrderColumn", " a.create_time desc  ");
                parameters.Add( "@GroupColumn", "");
                parameters.Add( "@PageSize", PageSize);
                parameters.Add( "@CurrentPage", PageIndex);
                parameters.Add( "@Group", 0);
                parameters.Add( "@Condition", " 1=1 " + strWhere);

                List<GMiscoSn> depts = dbConn.Query<GMiscoSn>("Com_Pagination", parameters, commandType: CommandType.StoredProcedure).ToList();
                result.Add("dataList", depts);
                result.Add("totalCount", parameters.Get<int>("@TotalCount"));
                return result;
            }
        }

        public List<GMisco> GetCountShipping(string whereStr)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT order_no,werks,a.part_no,b.part_spec, qty,piqty,case  when  piqty=qty   then '已完成'when  piqty<qty   then  '未完成'else '其他'   END  AS flag,a.update_time,a.a.create_time   ");
                SqlStringBuilder.Append("FROM   dbo.g_misco a LEFT JOIN sys_part b ON a.part_no=b.part_no ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append("		ORDER BY  a.update_time desc  ");

                List<GMisco> result = dbConn.Query<GMisco>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        public List<ShippingInformationDefine> getTableListByPage(string whereStr)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT order_no,werks,a.part_no,b.part_spec, qty,piqty,case  when  piqty=qty   then '已完成'when  piqty<qty   then  '未完成'else '其他'   END  AS flag,a.update_time,a.a.create_time   ");
                SqlStringBuilder.Append("FROM   dbo.g_misco a LEFT JOIN sys_part b ON a.part_no=b.part_no ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append("		ORDER BY  a.update_time desc  ");

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

        public List<GMiscoSn> GetCountShippingDetail(string whereStr)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                if (!string.IsNullOrEmpty(whereStr))
                {
                    whereStr = whereStr.Replace("update", "create");

                }
                
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT   a.order_no,a.serial_number,a.part_no,d.part_spec,emp_name,a.create_time ");
                SqlStringBuilder.Append("FROM    dbo.g_misco_sn a LEFT JOIN dbo.g_misco b ON a.order_no=b.order_no AND a.part_no=b.part_no LEFT JOIN sys_part d ON a.part_no=d.part_no AND d.part_type=1  ");
                SqlStringBuilder.Append("where 1=1 " + whereStr);
                SqlStringBuilder.Append(" ORDER BY  a.create_time desc ");

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