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

using Dapper;
using Estsh.Core.Dapper;
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
{
    /// <summary>
    /// 收货查询补打模块的业务数据
    /// </summary>
    public class StockBarcodePrintRepository : BaseRepository<SysStock>, IStockBarcodePrintRepository
    {
        public StockBarcodePrintRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {
        }

        #region 收货查询补打

        /// <summary>
        /// 根据分页条件获取列表
        /// </summary>
        /// <param name="PageSize"></param>
        /// <param name="PageIndex"></param>
        /// <param name="strWhere"></param>
        /// <param name="OrderBy"></param>
        /// <returns></returns>
        public Hashtable getListByPage(int PageSize, int PageIndex, string strWhere, string OrderBy)
        {
            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", @"sys_stock a(nolock) left join sys_enum b on a.status=b.enum_value and enum_type = 'sys_stock_status' left join sys_vendor v on a.vendor_id=v.vendor_id ");
                parameters.Add("@Column", @" a.carton_no,a.ref_order_no,a.part_no,a.part_spec,a.unit,a.lot_no,a.status,a.qty,a.snp_qty,a.vendor_code,v.vendor_name,a.locate_name,a.zone_name,a.warehouse_name,
                                              a.erp_warehouse,a.group_no,a.dock,a.factory_code,a.create_time,a.update_time,a.enabled,b.enum_name as stockStatus  ");
                parameters.Add("@OrderColumn", OrderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);


                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append(" ");


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

        /// <summary>
        /// 获取库位列表
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getLocateList()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT locate_id as [value],locate_name as [key] from sys_locate where enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }
        /// <summary>
        /// 获取库区列表
        /// </summary>
        /// <returns></returns>
        public List<KeyValueResult> getZoneList()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT zone_id as [value],zone_name as [key] from sys_zone where enabled='Y' ");
                List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
                return result;
            }
        }
        
        public List<SysStock> getCartonListByRuid(String ruids)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("SELECT a.ruid ");
                SqlStringBuilder.Append("	 , a.carton_no ");
                //SqlStringBuilder.Append("	 , a.order_no ");
                SqlStringBuilder.Append("	 , a.qty ");
                SqlStringBuilder.Append("	 , a.lot_no ");
                SqlStringBuilder.Append("	 , a.create_time ");
                SqlStringBuilder.Append("	 , b.part_no ");
                SqlStringBuilder.Append("	 , b.part_spec ");
                SqlStringBuilder.Append("	 , c.vendor_name ");
                SqlStringBuilder.Append("FROM dbo.sys_stock a (nolock) ");
                SqlStringBuilder.Append("	LEFT JOIN dbo.sys_part b (nolock) ON a.part_id = b.part_id ");
                SqlStringBuilder.Append("	LEFT JOIN dbo.sys_vendor c ON a.vendor_id = c.vendor_id ");
                SqlStringBuilder.Append("WHERE a.ruid IN (" + ruids + ") and a.enabled='Y'  ");
                List<SysStock> result = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
                return result;
            }
        }
        #endregion
    }
}