Estsh-Yiri/Estsh.Core.Wms/Repositories/StockQueryRepository.cs

using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System.Data;
using System.Text;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 库存查询
    /// </summary>
    public class StockQueryRepository : BaseRepository<BaseEntity>, IStockQueryRepository
    {
        public StockQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        /// <summary>
        /// 获取库存查询信息
        /// </summary>
        /// <param name="orderNo"></param>
        /// <returns></returns>
        public List<SysStock> GetStockQueryList(string part_spec)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sql = new StringBuilder(1024);
                //扫简码
                sql.Append(@" select a.part_no,a.part_spec,b.part_spec2,v.vendor_Name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_spec='" + part_spec + "' and locate_name <> '' and locate_name is not null and a.enabled='Y' and a.qty !=0   group by a.part_no,a.part_spec,b.part_spec2,v.vendor_Name ");
                //DynamicParameters parameters = new DynamicParameters();
                //parameters.Add("@part_spec", part_spec); 
                List<SysStock> sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                if (sysStocks.Count == 0)
                {
                    //扫零件号
                    sql.Clear();
                    sql.Append(@" select a.part_no,a.part_spec,b.part_spec2,v.vendor_Name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_no='" + part_spec + "' and locate_name <> '' and locate_name is not null and a.enabled='Y'  and a.qty !=0   group by a.part_no,a.part_spec,b.part_spec2,v.vendor_Name ");
                    sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                }
                if (sysStocks.Count == 0)
                {
                    //扫箱条码
                    sql.Clear();
                    sql.Append(@" select a.part_no,a.part_spec,b.part_spec2,v.vendor_Name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_no = (select part_no from sys_stock where carton_no='" + part_spec + "') and locate_name <> '' and locate_name is not null and a.enabled='Y'  and a.qty !=0   group by a.part_no,a.part_spec,b.part_spec2,v.vendor_Name ");
                    sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                }
                return sysStocks;
            }
        }

        public List<SysStock> GetLocateListByScan(string part_spec)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@" select locate_name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_spec='" + part_spec + "' and locate_name <> '' and locate_name is not null and a.enabled='Y'  and a.qty !=0  group by locate_name order by a.locate_name ");
                //DynamicParameters parameters = new DynamicParameters();
                //parameters.Add("@part_spec", part_spec);
                 
                List<SysStock> sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                if (sysStocks.Count == 0)
                {
                    //扫零件号
                    sql.Clear();
                    sql.Append(@" select locate_name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_no='" + part_spec + "' and locate_name <> '' and locate_name is not null and a.enabled='Y'  and a.qty !=0  group by locate_name  order by a.locate_name");
                    sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                }
                if (sysStocks.Count == 0)
                {
                    //扫箱条码
                    sql.Clear();
                    sql.Append(@" select locate_name,sum(qty) qty,count(1) carton_qty from sys_stock a (nolock) 
                           left join sys_part b (nolock)  on a.part_id=b.part_id
                           left join sys_vendor v (nolock)  on v.vendor_id=a.vendor_id
                       where a.part_no = (select part_no from sys_stock where carton_no='" + part_spec + "') and locate_name <> '' and locate_name is not null and a.enabled='Y'  and a.qty !=0  group by locate_name  order by a.locate_name");
                    sysStocks = dbConn.Query<SysStock>(sql.ToString()).ToList();
                }
                return sysStocks;
            }
        }
    }
}