Estsh-Yiri/Estsh.Core.Services/Repositories/ROSerchPrintRepository.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 ROSerchPrintRepository : BaseRepository<SysStock>, IROSerchPrintRepository
    {
        public ROSerchPrintRepository(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(Hashtable requestHt, int PageSize, int PageIndex, string direction, string sort)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                String strWhere = "a.enabled = 'Y' AND a.carton_no <> '0' ";
                //if (requestHt["orderNo"] != null && !"".Equals(requestHt["orderNo"].ToString().Trim()))
                //{
                //    strWhere += " and " + " a.order_no = '" + requestHt["orderNo"].ToString().Trim() + "'";
                //}
                if (requestHt["status"] != null && !"".Equals(requestHt["status"].ToString().Trim()))
                {
                    strWhere += " and " + " a.status = '" + requestHt["status"].ToString().Trim() + "'";
                }
                if (requestHt["locateName"] != null && !"".Equals(requestHt["locateName"].ToString().Trim()))
                {
                    strWhere += " and " + " a.locate_id = '" + requestHt["locateName"].ToString().Trim() + "'";
                }

                if (requestHt["startDate"] != null && !"".Equals(requestHt["startDate"].ToString().Trim())
                    && requestHt["endDate"] != null && !"".Equals(requestHt["endDate"].ToString().Trim()))
                {
                    strWhere += " and " + "a.create_time between Convert(DateTime,'"
                        + requestHt["startDate"].ToString().Trim() + "') and Convert(DateTime,'"
                        + requestHt["endDate"].ToString().Trim() + "')";
                }

                if (requestHt["startCartonNo"] != null && !"".Equals(requestHt["startCartonNo"].ToString().Trim())
                    && requestHt["endCartonNo"] != null && !"".Equals(requestHt["endCartonNo"].ToString().Trim()))
                {
                    strWhere += " and " + "a.carton_no between '" + requestHt["startCartonNo"].ToString().Trim()
                        + "' and '" + requestHt["endCartonNo"].ToString().Trim() + "'";
                }
  
                String orderBy = "";
                if (sort != null && !"".Equals(sort.Trim()))
                {
                    orderBy += " a." + typeof(SysStock).GetEntityColumnName(sort.Trim()) + " " + direction;
                }
                else
                {
                    orderBy += " a."+typeof(SysStock).GetEntityColumnName("CreateYmd") + " " + direction;
                }

                Hashtable result = new Hashtable();
                StringBuilder column = new StringBuilder(1024);
                column.Append("a.ruid, a.carton_no, d.enum_desc as status, a.qty, a.lot_no, ");
                column.Append(" a.create_time, b.part_no, b.part_spec, c.vendor_name");

                StringBuilder table = new StringBuilder(1024);
                table.Append("dbo.sys_stock a ");
                table.Append("LEFT JOIN dbo.sys_part b ON a.part_id = b.part_id ");
                table.Append("LEFT JOIN dbo.sys_vendor c ON a.vendor_id = c.vendor_id ");
                table.Append("LEFT JOIN dbo.sys_enum d ON a.status = d.enum_value AND d.enum_type = 'sys_stock_status' ");

                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
                parameters.Add("@Table", table.ToString());
                parameters.Add("@Column", column.ToString());
                parameters.Add("@OrderColumn", orderBy);
                parameters.Add("@GroupColumn", "");
                parameters.Add("@PageSize", PageSize);
                parameters.Add("@CurrentPage", PageIndex);
                parameters.Add("@Group", 0);
                parameters.Add("@Condition", strWhere);

                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;
            }
        }

        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 ");
                SqlStringBuilder.Append("	LEFT JOIN dbo.sys_part b 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
    }
}