|
|
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
|
|
|
{
|
|
|
|
|
|
public class StockDetailQueryRepository : BaseRepository<SysStock>, IStockDetailQueryRepository
|
|
|
{
|
|
|
public StockDetailQueryRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
#region 成员方法
|
|
|
|
|
|
public Hashtable getZoneList(int PageSize, int PageIndex, string strWhere, string OrderBy)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
|
|
|
Hashtable result = new Hashtable();
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@TotalCount", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
Params.Add("@TotalPage", 0, DbType.Int32, ParameterDirection.Output);
|
|
|
Params.Add("@GroupColumn", " warehouse_id, warehouse_name,zone_id,zone_name");
|
|
|
Params.Add("@Table", " sys_stock (nolock) ");
|
|
|
Params.Add("@Column", " warehouse_id, warehouse_name,zone_id,zone_name,SUM(qty) qty ,COUNT(1) Printed ");
|
|
|
Params.Add("@PageSize", PageSize);
|
|
|
Params.Add("@CurrentPage", PageIndex);
|
|
|
Params.Add("@Condition", strWhere);
|
|
|
Params.Add("@OrderColumn", OrderBy);
|
|
|
Params.Add("@Group", 1);
|
|
|
|
|
|
List<SysStock> dataList = dbConn.Query<SysStock>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", Params.Get<int>("@TotalCount"));
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysStock> getLocateListByPage(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = string.Format(@" SELECT warehouse_id, warehouse_name,zone_id,zone_name,locate_id,locate_name,SUM(qty) qty ,COUNT(1) Printed FROM
|
|
|
sys_stock (nolock) where " + strWhere
|
|
|
+ " GROUP BY warehouse_id, warehouse_name,zone_id,zone_name,locate_id,locate_name");
|
|
|
List<SysStock> dataList = dbConn.Query<SysStock>(sql).ToList();
|
|
|
return dataList;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public List<SysStock> getListByPage(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = string.Format(@" SELECT a.*, b.enum_name as stock_status,c.part_spec2 FROM
|
|
|
sys_stock a (nolock) left join sys_enum b on a.status= b.enum_value
|
|
|
and b.enum_type = 'sys_stock_status'
|
|
|
left join sys_part c (nolock) on a.part_no=c.part_no where " + strWhere);
|
|
|
List<SysStock> dataList = dbConn.Query<SysStock>(sql).ToList();
|
|
|
return dataList;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> GetStockType()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "select enum_value as [value] ,enum_name as [key] from sys_enum where enum_type = 'sys_stock_status' order by convert(int, enum_value) ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#endregion 成员方法
|
|
|
|
|
|
/// <summary>
|
|
|
/// 判断用户输入的零件号是否存在
|
|
|
/// BY NOAH
|
|
|
/// </summary>
|
|
|
/// <param name="part_no"></param>
|
|
|
/// <returns></returns>
|
|
|
public String isExsitPart_no(String part_no)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string part_id = null;
|
|
|
string SqlString = "select top 1 part_id from sys_part (nolock) where enabled='Y' and part_no=@partNo";
|
|
|
DynamicParameters Params = new DynamicParameters();
|
|
|
Params.Add("@partNo", part_no);
|
|
|
List<SysPart> dt = dbConn.Query<SysPart>(SqlString, Params).ToList();
|
|
|
if (dt.Count < 1)
|
|
|
{
|
|
|
return part_id;
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
part_id = dt[0].PartId.ToString();
|
|
|
return part_id;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取零件号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetPartNo()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "select part_no as [value],part_no as [key] from sys_part (nolock) where enabled='Y' ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取库位名称
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetLocateName()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "select locate_name as [value],locate_name as [key] from sys_locate where enabled='Y' ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取枚举值
|
|
|
/// </summary>
|
|
|
/// <param name="enumtype">枚举类型</param>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> GetSysEnum(string enumtype)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string Sqlstring = "select enum_desc as [key],enum_value as [value] from sys_enum where enum_type=@enum_type and enabled='Y' ";
|
|
|
DynamicParameters values = new DynamicParameters();
|
|
|
values.Add("@enum_type", enumtype);
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(Sqlstring, values).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> GetWareHouseName()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string SqlString = "select warehouse_id as [value] , warehouse_name,warehouse_desc as [key] from sys_warehouse where enabled='Y' ";
|
|
|
try
|
|
|
{
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(SqlString).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
catch
|
|
|
{
|
|
|
return null;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<StockDetailQuery> getExportList(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = string.Format(@" SELECT a.ruid , a.vendor_id , a.vendor_code , a.carton_no , a.carton_type , a.part_id , a.part_no , a.part_spec , a.lot_no , a.fix_lot_no , a.status ,cast( a.qty as int ) qty , cast(a.snp_qty as int ) snp_qty , a.locate_id , a.locate_name , a.group_no , a.erp_warehouse , a.date_code , a.qms_status , a.ref_order_no , a.unit , a.dock , a.warehouse_id , a.warehouse_name , a.zone_id , a.zone_name , a.printed , a.print_time , a.remark , a.factory_id , a.factory_code , a.enabled , a.create_userid , a.create_time , a.update_userid , a.update_time , a.guid , a.timestamp , a.count_qty , a.reveice_time , a.qc_finish_time , a.tray_no , a.channel_number , a.mtoc , a.mtoc_desc , a.cartype_name , a.pick_up_tab , b.enum_name as stock_status,c.part_spec2 FROM
|
|
|
sys_stock a (nolock) left join sys_enum b on a.status= b.enum_value
|
|
|
and b.enum_type = 'sys_stock_status'
|
|
|
left join sys_part c (nolock) on a.part_no=c.part_no where " + strWhere);
|
|
|
List<StockDetailQuery> dataList = dbConn.Query<StockDetailQuery>(sql).ToList();
|
|
|
return dataList;
|
|
|
}
|
|
|
}
|
|
|
public int EnableData(string id, string empId)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update sys_stock set Enabled='Y',update_userid=" + empId + ", update_time = CONVERT(varchar(50), GETDATE(), 21) WHERE ruid in (" + id + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
}
|
|
|
}
|