using Dapper;
using Estsh.Core.Dapper;
using Estsh.Core.IRepositories;
using Estsh.Core.Model.ExcelModel;
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
{
    /// <summary>
    /// 处理菜单模块的业务数据
    /// </summary>
    public class ProduceStockRepository : BaseRepository<GWorkorder>, IProduceStockRepository
    {
        public ProduceStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }
        #region  成员方法

        /// <summary>
        /// 根据传入条件获得菜单列表数据
        /// </summary>
        public List<SysPartCustOrder> getList(string strWhere, string filedOrder)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select  * from dbo.sys_part_cust_order ");
                if (strWhere != null && !strWhere.Trim().Equals(""))
                {
                    strSql.Append(" where " + strWhere);
                }
                if (filedOrder != null && !filedOrder.Trim().Equals(""))
                {
                    strSql.Append(" order by " + filedOrder);
                }

                List<SysPartCustOrder> result = dbConn.Query<SysPartCustOrder>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 汇总信息
        /// </summary> 
        /// <returns></returns>
        public Hashtable GetSummary(string strWhere, Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                 #if (false)
                 StringBuilder sql = new StringBuilder(1024);
                 sql.Append("SELECT  type_id , ");
                 sql.Append("        c.model_id , ");
                 sql.Append("        type_name , ");
                 sql.Append("        model_desc , ");
                 sql.Append("        e.number ");
                 sql.Append("FROM    g_workorder AS a ");
                 sql.Append("        INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                 sql.Append("        INNER JOIN sys_model AS c ON c.model_id = b.model_id ");
                 sql.Append("        INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id ");
                 sql.Append("        INNER JOIN ( SELECT c.model_id , ");
                 sql.Append("                            COUNT(c.model_id) AS number ");
                 sql.Append("                     FROM   g_workorder AS a ");
                 sql.Append("                            INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                 sql.Append("                            INNER JOIN sys_model AS c ON c.model_id = b.model_id ");
                 sql.Append("                            INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id ");
                 sql.Append("                            LEFT JOIN g_wo_relation AS e ON e.workorder_id = a.ruid ");
                 sql.Append("                            LEFT JOIN g_vw_order AS f ON f.order_id = e.order_id ");
                 sql.Append("                     WHERE 1=1  " + _where + " ");
                 sql.Append("                     GROUP BY c.model_id ");
                 sql.Append("                   ) AS e ON e.model_id = c.model_id ");
                 sql.Append("                     WHERE 1=1  " + _where + " ");
                 sql.Append("GROUP BY type_id , ");
                 sql.Append("        c.model_id , ");
                 sql.Append("        type_name , ");
                 sql.Append("        model_desc , ");
                 sql.Append("        e.number  ");
                
                 return _remotingProxy.GetDataTable(sql.ToString());
                #endif
                Hashtable result = new Hashtable();
                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(" dbo.g_workorder a  ");
                table.Append(" LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
                table.Append(" left join dbo.sys_model c on b.model_id=c.model_id ");
                parameters.Add("@Table", table.ToString());

                StringBuilder column = new StringBuilder();
                column.Append(" b.part_no , ");
                column.Append(" COUNT(1) AS qty ");
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", "b.part_no");
                parameters.Add("@GroupColumn", "b.part_no");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 1);
                string where = " a.type not in ('C','K') and " + strWhere;
                parameters.Add("@Condition", where);

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

        /// <summary>
        ///  详细
        /// </summary>
        /// <param name="_where"></param>
        /// <param name="typeID"></param>
        /// <returns></returns>
        public List<GWorkorder> GetSummaryDtail(string _where, string typeID)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT d.type_name, ");
                SqlStringBuilder.Append(" g.part_id, ");
                SqlStringBuilder.Append(" h.part_no, ");
                SqlStringBuilder.Append(" h.part_spec, ");
                SqlStringBuilder.Append(" COUNT(h.part_no) AS number, ");
                SqlStringBuilder.Append(" CASE ISNULL(NULLIF(g.type, 'N'), a.type) ");
                SqlStringBuilder.Append(" WHEN 'N' THEN '正常' ");
                SqlStringBuilder.Append(" WHEN 'P' THEN '领料' ");
                SqlStringBuilder.Append(" WHEN 'X' THEN '试制' ");
                SqlStringBuilder.Append(" WHEN 'D' THEN '加拨' ");
                SqlStringBuilder.Append(" WHEN 'S' THEN '库存' ");
                SqlStringBuilder.Append(" END AS type ");
                SqlStringBuilder.Append(" FROM   g_workorder AS a  ");
                SqlStringBuilder.Append(" INNER JOIN sys_part AS b ON a.part_id = b.part_id ");
                SqlStringBuilder.Append(" INNER JOIN sys_model AS c ON c.model_id = b.model_id ");
                SqlStringBuilder.Append(" INNER JOIN sys_model_type AS d ON c.model_type_id = d.type_id ");
                SqlStringBuilder.Append(" LEFT JOIN g_wo_relation AS e ON e.workorder_id = a.ruid ");
                SqlStringBuilder.Append(" LEFT JOIN g_vw_order AS f ON f.order_id = e.order_id ");
                SqlStringBuilder.Append(" inner join g_workorder_detail as g on a.ruid=g.workorder_id ");
                SqlStringBuilder.Append(" inner join sys_part as h on g.part_id=h.part_id ");
                SqlStringBuilder.Append(" WHERE 1=1  " + _where);
                SqlStringBuilder.Append("  GROUP BY  ");
                SqlStringBuilder.Append("  g.part_id, ");
                SqlStringBuilder.Append("  h.part_no, ");
                SqlStringBuilder.Append("  h.part_spec, ");
                SqlStringBuilder.Append("  d.type_name ,  ");
                SqlStringBuilder.Append("  ISNULL(NULLIF(g.type, 'N'), a.type)    ORDER BY  type_name asc,type asc, h.part_no  asc");

                List<GWorkorder> result = dbConn.Query<GWorkorder>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        /// <summary>
        /// 获得详细信息
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public Hashtable GetDetail(string strWhere, Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                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(" dbo.g_workorder a  ");
                table.Append(" LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
                table.Append(" left join dbo.sys_model c on b.model_id=c.model_id ");
                parameters.Add("@Table", table.ToString());

                StringBuilder column = new StringBuilder();
                column.Append(" b.part_no , ");
                column.Append(" COUNT(1) AS qty ");
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", "b.part_no");
                parameters.Add("@GroupColumn", "b.part_no");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 1);
                string where = " a.type not in ('C','K') and " + strWhere;
                parameters.Add("@Condition", where);

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

        /// <summary>
        /// 获得统计数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <param name="pager"></param>
        /// <returns></returns>
        public Hashtable GetStatisticsData(string strWhere, Pager pager)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                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(" dbo.g_workorder a  ");
                table.Append(" LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
                table.Append(" left join dbo.sys_model c on b.model_id=c.model_id ");
                parameters.Add("@Table", table.ToString());

                StringBuilder column = new StringBuilder();
                column.Append(" b.part_no , ");
                column.Append(" COUNT(1) AS qty ");
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", "b.part_no");
                parameters.Add("@GroupColumn", "b.part_no");
                parameters.Add("@PageSize", pager.pageSize);
                parameters.Add("@CurrentPage", pager.pageNo);
                parameters.Add("@Group", 1);
                string where = " a.type not in ('C','K') and " + strWhere;
                parameters.Add("@Condition", where);

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

        /// <summary>
        /// 根据分页条件获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(String type_id, int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                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_workorder  as a  inner join sys_part as b on a.part_id=b.part_id  inner join sys_model as c on c.model_id=b.model_id  inner join sys_model_type as d on c.model_type_id=d.type_id inner join ( select c.model_id,count(c.model_id) as number from dbo.g_workorder  as a inner join sys_part as b on a.part_id=b.part_id inner join sys_model as c on c.model_id=b.model_id inner join sys_model_type as d on c.model_type_id=d.type_id left join g_wo_relation as e on e.workorder_id=a.ruid  left join g_vw_order as f on f.order_id =e.order_id     ");
                parameters.Add("@Column", "type_id,c.model_id,type_name,model_desc,e.number ");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", " a.car_no  like 'A%' AND " + strWhere + " group by c.model_id   ) as e on e.model_id=c.model_id  group by type_id,c.model_id,type_name,model_desc,e.number ");

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

        /// <summary>
        /// 插入菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int saveProduceStock(SysPartCustOrder htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO sys_part_cust_order (part_id ");
                SqlStringBuilder.Append("						, cust_order ");
                SqlStringBuilder.Append("						, create_userid ");
                SqlStringBuilder.Append("						, create_time ");
                SqlStringBuilder.Append("						, enabled ");
                SqlStringBuilder.Append("						,ship_unit) ");
                SqlStringBuilder.Append("					VALUES(@partId ");
                SqlStringBuilder.Append("						, @custOrder ");
                SqlStringBuilder.Append("						, @createUserid ");
                SqlStringBuilder.Append("						, CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("						, 'Y' ");
                SqlStringBuilder.Append("						,@shipUnit)");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 更新菜单数据
        /// </summary>
        /// <param name="htParams"></param>
        /// <returns></returns>
        public int updateProduceStock(SysPartCustOrder htParams)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("update sys_part_cust_order  ");
                SqlStringBuilder.Append("SET cust_order=@custOrder ");
                SqlStringBuilder.Append(" , ship_unit=@shipUnit ");
                SqlStringBuilder.Append(" ,update_userid = @updateUserId ");
                SqlStringBuilder.Append(" ,update_time = CONVERT(varchar(50), GETDATE(), 21)");
                SqlStringBuilder.Append("WHERE part_id=@partId	 ");

                int result = dbConn.Execute(SqlStringBuilder.ToString(), htParams);
                return result;
            }
        }

        /// <summary>
        /// 删除菜单数据
        /// </summary>
        /// <param name="ruid"></param>
        /// <returns></returns>
        public int deleteProduceStock(String part_id)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters htparams = new DynamicParameters();
                htparams.Add("@part_id", part_id);
                String delStr = "update sys_part_cust_order set Enabled='N'  where part_id = @part_id";
                int result = dbConn.Execute(delStr, htparams);
                return result;
            }
        }

        /// <summary>
        /// 获取下拉框菜单数据
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select type_id as [value],type_name+':'+type_desc as [key] from dbo.sys_model_type where enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        /// <param name="PageSize">一页显示多少条数据</param>
        /// <param name="PageIndex">当前第几页</param>
        /// <param name="strWhere">条件</param>
        /// <param name="OrderBy">排序字段</param>
        /// <returns></returns>
        public Hashtable getTableListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", "dbo.sys_part_cust_order a  LEFT JOIN sys_part b ON a.part_id = b.part_id ");
                parameters.Add("@Column", "b.part_no,b.part_spec,a.*,a.part_id as part_id_a");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

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

        #endregion  成员方法
    }
}