Estsh-Yiri/Estsh.Core.Services/Repositories/InventTransQueryRepository.cs

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 System.Collections;
using System.Data;
using System.Text;

/***************************************************************************************************
 *
 * 更新人：sitong.dong
 * 描述：事物查询
 * 修改时间：2022.06.22
 * 修改日志：系统迭代升级
 *
 **************************************************************************************************/
namespace Estsh.Core.Repositories
{
    public class InventTransQueryRepository : BaseRepository<SysStockTrans>, IInventTransQueryRepository
    {
        public InventTransQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }


        #region  成员方法

        /// <summary>
        /// 获取分页数据列表
        /// </summary>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                Hashtable result = new Hashtable();
                if (PageSize == 0)
                    return result;
                DynamicParameters Params = new DynamicParameters();
                StringBuilder SqlStringBuilder = new StringBuilder(2048);
                SqlStringBuilder.Append("trans_code,c.enum_desc as trans_type_desc,a.carton_no,a.part_id,a.part_no,a.part_spec,pt.part_spec2,a.unit " +
                                         ", a.src_locate_id, a.src_locate_name, a.dest_locate_id, a.dest_locate_name " +
                                         ", convert(int,a.old_qty) old_qty, convert(int,a.new_qty) new_qty, convert(int,a.trans_qty) trans_qty,a.old_status" +
                                         ", os.enum_name old_status_desc ,a.new_status,ns.enum_name new_status_desc " +
                                         ", a.old_qms_status, a.new_qms_status, a.src_erp_warehouse, a.dest_erp_warehouse " +
                                         ", a.src_warehouse_id, a.src_warehouse_name " +
                                         ", a.dest_warehouse_id, a.dest_warehouse_name, a.src_zone_id, a.src_zone_name " +
                                         ", a.dest_zone_id, a.dest_zone_name, a.enabled, a.update_userid " +
                                         ", a.update_time, a.create_userid, a.create_time, a.factory_id, a.factory_code,a.ref_order_no  ");

                Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);

                StringBuilder SqlStringBuilder1 = new StringBuilder(2048);
                SqlStringBuilder1.Append("  sys_stock_trans a (nolock) LEFT JOIN sys_part pt ON pt.part_id = a.part_id " +
                     "LEFT JOIN sys_enum c ON a.trans_code = c.enum_value AND c.enum_type = 'sys_stock_trans_trans_type' " +
                     " LEFT JOIN sys_enum os ON a.old_status = os.enum_value" +
                     " AND os.enum_type = 'sys_stock_status' LEFT JOIN sys_enum ns ON a.new_status = ns.enum_value  AND ns.enum_type = 'sys_stock_status'  ");
                Params.Add("@Column", SqlStringBuilder.ToString());
                Params.Add("@Table", SqlStringBuilder1.ToString());
                Params.Add("@PageSize", PageSize);
                Params.Add("@CurrentPage", PageIndex);
                Params.Add("@OrderColumn", " a.create_time desc ");
                Params.Add("@Group", 0);
                Params.Add("@Condition", strWhere);
                Params.Add("@GroupColumn", " ");

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

        public List<InventTransQuery> getExportList(string strWhere, string orderBy)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" select trans_code,c.enum_desc as trans_type_desc,a.carton_no,a.part_id,a.part_no,a.part_spec,pt.part_spec2,a.unit  ");
                SqlStringBuilder.Append("                                        , a.src_locate_id, a.src_locate_name, a.dest_locate_id, a.dest_locate_name  ");
                SqlStringBuilder.Append("                                        ,convert(int,a.old_qty) old_qty, convert(int,a.new_qty) new_qty, convert(int,a.trans_qty) trans_qty,a.old_status ");
                SqlStringBuilder.Append("                                        , os.enum_name old_status_desc ,a.new_status,ns.enum_name new_status_desc  ");
                SqlStringBuilder.Append("                                        , a.old_qms_status, a.new_qms_status, a.src_erp_warehouse, a.dest_erp_warehouse  ");
                SqlStringBuilder.Append("                                        , a.src_warehouse_id, a.src_warehouse_name  ");
                SqlStringBuilder.Append("                                        , a.dest_warehouse_id, a.dest_warehouse_name, a.src_zone_id, a.src_zone_name  ");
                SqlStringBuilder.Append("                                        , a.dest_zone_id, a.dest_zone_name, a.enabled, a.update_userid  ");
                SqlStringBuilder.Append("                                        , a.update_time, a.create_userid, a.create_time, a.factory_id, a.factory_code,a.ref_order_no from  sys_stock_trans a (nolock) ");
                SqlStringBuilder.Append("   LEFT JOIN sys_part pt ON pt.part_id = a.part_id ");

               SqlStringBuilder.Append("                     LEFT JOIN sys_enum c ON a.trans_code = c.enum_value AND c.enum_type = 'sys_stock_trans_trans_type'  ");
                SqlStringBuilder.Append("                     LEFT JOIN sys_enum os ON a.old_status = os.enum_value ");
                SqlStringBuilder.Append("                      AND os.enum_type = 'sys_stock_status' LEFT JOIN sys_enum ns ON a.new_status = ns.enum_value  AND ns.enum_type = 'sys_stock_status'   ");
                SqlStringBuilder.Append("                      WHERE " + strWhere + orderBy);

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

        #endregion  成员方法
        public List<KeyValueResult> GetStockTransType()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "select enum_value AS [value],enum_name as [key] from sys_enum c where c.enum_type = 'sys_stock_trans_trans_type' and c.enabled='Y' order by enum_seq ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetErpwarehouse()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "select distinct erp_warehouse as [key] , erp_warehouse as value from sys_zone where enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> GetSrcLocate()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = "select distinct src_locate_id AS [value] , b.locate_name as [key] from sys_stock_trans a (nolock) left join sys_locate b on a.src_locate_id = b.locate_id where  a.enabled='Y' ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

        public List<KeyValueResult> Getuserinfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strSql = " select distinct a.create_userid as [value] ,b.emp_name as [key] from sys_stock_trans a (nolock) left join sys_emp b on a.create_userid = b.emp_id where  a.enabled='Y'  ";
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
                return result;
            }
        }

    }
}