|
|
using Dapper;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.Model.EnumUtil;
|
|
|
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 PurchaseManageRepository : BaseRepository<WmsPurchase>, IPurchaseManageRepository
|
|
|
{
|
|
|
public PurchaseManageRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
#region 成员方法
|
|
|
/// <summary>
|
|
|
/// 获得菜单列表数据
|
|
|
/// </summary>
|
|
|
public List<SysWarehouse> getList(string strWhere, string filedOrder)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select warehouse_id,warehouse_name,warehouse_desc,a.enabled,b.factory_id,b.factory_name from sys_warehouse a (nolock) LEFT JOIN sys_factory b (nolock) ON a.factory_id = b.factory_id ");
|
|
|
if (!strWhere.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" where " + strWhere);
|
|
|
}
|
|
|
if (filedOrder != null && !filedOrder.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" order by " + filedOrder);
|
|
|
}
|
|
|
List<SysWarehouse> result = dbConn.Query<SysWarehouse>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getPurchaseListByPage(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", "");
|
|
|
Params.Add("@Table", "wms_purchase a left join sys_enum b on b.enum_type='wms_purchase_order_type' and a.order_type=b.enum_value left join sys_enum c on c.enum_type='wms_purchase_order_status' and a.order_status=c.enum_value " +
|
|
|
" left join sys_vendor v on a.vendor_id=v.vendor_id ");
|
|
|
Params.Add("@Column", "a.ruid,a.order_no,v.vendor_name,a.order_type,b.enum_desc as order_type_desc,a.order_status,c.enum_desc as order_status_desc,a.vendor_id,a.vendor_code,a.se_date,a.se_time,a.dock,a.ref_order_no,a.factory_id,a.factory_code,a.create_time,a.enabled");
|
|
|
Params.Add("@PageSize", PageSize);
|
|
|
Params.Add("@CurrentPage", PageIndex);
|
|
|
Params.Add("@Condition", strWhere);
|
|
|
Params.Add("@OrderColumn", OrderBy);
|
|
|
Params.Add("@Group", 0);
|
|
|
|
|
|
List<WmsPurchase> dataList = dbConn.Query<WmsPurchase>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", Params.Get<int>("@TotalCount"));
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getPurchaseDetailListByPage(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select a.ruid,a.order_no ,a.item_no,a.part_id,a.part_no,a.part_spec,a.qty,a.rec_qty,a.box_qty,a.rec_box_qty,a.snp_qty, ");
|
|
|
SqlStringBuilder.Append("a.unit,a.erp_warehouse,a.item_status,b.enum_desc as item_status_desc,a.factory_id,a.factory_code,a.enabled from ");
|
|
|
SqlStringBuilder.Append("wms_purchase_detail a (nolock) left join sys_enum b (nolock) on enum_type='wms_purchase_detail_item_status' and a.item_status=convert(int,b.enum_value) ");
|
|
|
SqlStringBuilder.Append("where "+ strWhere);
|
|
|
|
|
|
List<WmsPurchaseDetail> dataList = dbConn.Query<WmsPurchaseDetail>(SqlStringBuilder.ToString()).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", dataList.Count());
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getStockListByPage(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select a.ruid,a.ref_order_no,a.part_id,a.part_no,a.part_spec,p.part_spec2,a.carton_no,a.qty,a.unit,e.enum_name as stock_status, ");
|
|
|
SqlStringBuilder.Append(" a.factory_id,a.factory_code,a.enabled,c.se_date,c.se_time,c.vendor_code,d.vendor_name,a.reveice_time,a.qc_finish_time from sys_stock a (nolock) ");
|
|
|
SqlStringBuilder.Append(" left join wms_purchase c (nolock) on a.ref_order_no=c.order_no ");
|
|
|
SqlStringBuilder.Append(" left join sys_vendor d (nolock) on c.vendor_id=d.vendor_id ");
|
|
|
SqlStringBuilder.Append(" left join sys_part p (nolock) on a.part_id=p.part_id ");
|
|
|
SqlStringBuilder.Append(" left join sys_enum e (nolock) on e.enum_value=a.status and e.enum_type = 'sys_stock_status' ");
|
|
|
SqlStringBuilder.Append(" where " + strWhere);
|
|
|
|
|
|
List<SysStock> dataList = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", dataList.Count());
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysStock> getStockListByPrint(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select a.ruid,a.ref_order_no,a.part_id,a.part_no,a.part_spec,p.part_spec2,a.carton_no,a.qty,a.unit,e.enum_name as stock_status, ");
|
|
|
SqlStringBuilder.Append(" a.factory_id,a.factory_code,a.enabled,c.se_date,c.se_time,c.vendor_code,d.vendor_name from sys_stock a (nolock) ");
|
|
|
SqlStringBuilder.Append(" left join wms_purchase c (nolock) on a.ref_order_no=c.order_no ");
|
|
|
SqlStringBuilder.Append(" left join sys_vendor d (nolock) on c.vendor_id=d.vendor_id ");
|
|
|
SqlStringBuilder.Append(" left join sys_part p (nolock) on a.part_id=p.part_id ");
|
|
|
SqlStringBuilder.Append(" left join sys_enum e (nolock) on e.enum_value=a.status and e.enum_type = 'sys_stock_status' ");
|
|
|
SqlStringBuilder.Append(" where " + strWhere);
|
|
|
|
|
|
List<SysStock> dataList = dbConn.Query<SysStock>(SqlStringBuilder.ToString()).ToList();
|
|
|
return dataList;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得零件信息
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<SysPart> GetPartNoInfo(string part_no)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no LIKE '%" + part_no + "%' ORDER BY part_no";
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得零件信息
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<SysPart> GetPartNoInfoByPartNo(string part_no)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no = '" + part_no + "'";
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获得零件简码信息
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<SysPart> GetPartSpecInfo(string partSpec)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_spec LIKE '%" + partSpec + "%' ORDER BY part_no";
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<SysPart> GetPartSpecInfoByPartSpec(string partSpec)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_spec = '" + partSpec + "'";
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysPart> GetPartInfo(string part_no)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id,part_no,part_spec,default_box_qty FROM sys_part (NOLOCK) WHERE enabled='Y' AND part_no LIKE '" + part_no + "%' ORDER BY part_no";
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql).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 (nolock) where enabled='Y' ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> GetOrderStatus()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String strSql = "select enum_value as [value],enum_name as [key] from sys_enum (nolock) where enum_type='wms_purchase_order_status' order by enum_value ";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取下拉框菜单数据 这里显示的是待添加的厂区信息，厂区名称
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public List<KeyValueResult> getSelectFactory()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select factory_id as [value],factory_name as [key] from sys_factory (nolock) where Enabled = 'Y'");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> getSelectWarehouse()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select warehouse_id as [value],warehouse_desc as [key] from sys_warehouse (nolock) where Enabled = 'Y'");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysWarehouse> getSelectWarehouse(string warehouseid)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * from sys_warehouse (nolock) where Enabled = 'Y' and warehouse_id='" + warehouseid + "'");
|
|
|
List<SysWarehouse> result = dbConn.Query<SysWarehouse>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<SysZone> getSelectZone(string zoneid)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * from sys_zone (nolock) where Enabled = 'Y' and zone_id='" + zoneid + "'");
|
|
|
List<SysZone> result = dbConn.Query<SysZone>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<KeyValueResult> getSelectZone()
|
|
|
{
|
|
|
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 (nolock) where Enabled = 'Y'");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> getSelectVendor()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select vendor_id as [value],vendor_name as [key] from sys_Vendor (nolock) where Enabled = 'Y'");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysVendor> getSelectVendor(string vendorName)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * from sys_Vendor (nolock) where Enabled = 'Y' and vendor_name='" + vendorName + "'");
|
|
|
List<SysVendor> result = dbConn.Query<SysVendor>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> GetPart(int type)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT part_id as [value],part_no as [key] FROM sys_part (NOLOCK) WHERE enabled='Y' ORDER BY part_no";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@part_type", type);
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql, param).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<KeyValueResult> GetOrderType()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT [enum_value] as [value],[enum_desc] as [key] FROM sys_enum (nolock) where enum_type='wms_purchase_order_type' and enabled='Y'";
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<SysPart> GetPart(int type, string PartNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT * FROM sys_part (NOLOCK) WHERE enabled='Y' and part_no='" + PartNo + "' ORDER BY part_no";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
param.Add("@part_type", type);
|
|
|
List<SysPart> result = dbConn.Query<SysPart>(sql, param).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取订单编号
|
|
|
/// </summary>
|
|
|
/// <returns></returns>
|
|
|
public string GetOrderNo(string stockOrder, string p)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
DynamicParameters list = new DynamicParameters();
|
|
|
list.Add("@order_type", stockOrder);
|
|
|
list.Add("@order_prefix", p);
|
|
|
list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);
|
|
|
|
|
|
var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);// this._remotingProxy.ExecuteSotreProcedure("dbo.sys_create_orderno", list);
|
|
|
string result = list.Get<string>("@order_no");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 插入菜单数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int savePurchaseManage(WmsPurchase htParams, IList<WmsPurchaseDetail> htDetailParams)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
string orderNo = GetOrderNo("InteriorPurchaseOrders", "I");
|
|
|
if (htParams.OrderType == (int)WmsEnumUtil.PurchaseOrderType.PO)
|
|
|
{
|
|
|
orderNo = GetOrderNo("POPurchaseOrders", "PO");
|
|
|
}
|
|
|
else if (htParams.OrderType == (int)WmsEnumUtil.PurchaseOrderType.ASN)
|
|
|
{
|
|
|
orderNo = GetOrderNo("ASNPurchaseOrders", "ASN");
|
|
|
}
|
|
|
else if (htParams.OrderType == (int)WmsEnumUtil.PurchaseOrderType.SUB)
|
|
|
{
|
|
|
orderNo = GetOrderNo("InteriorPurchaseOrders", "I");
|
|
|
}
|
|
|
htParams.OrderNo = orderNo;
|
|
|
for (int i = 0; i < htDetailParams.Count; i++)
|
|
|
{
|
|
|
htDetailParams[i].OrderNo = orderNo;
|
|
|
}
|
|
|
dbConn.Open();
|
|
|
List<string> SqlStrings = new List<string>();
|
|
|
List<object> Parameters = new List<object>();
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.wms_purchase(order_no,order_type,order_status,vendor_id,vendor_code,se_date,se_time,dock,ref_order_no,factory_id");
|
|
|
SqlStringBuilder.Append(", factory_code, enabled, create_userid, create_time, guid)");
|
|
|
SqlStringBuilder.Append(" VALUES(@orderNo, @orderType, '10', @vendorId, @vendorCode, @seDate, @seTime, @dock, @orderNo, @factoryId");
|
|
|
SqlStringBuilder.Append(", @factoryCode, @enabled, @createUserid, CONVERT(varchar(50), GETDATE(), 21), newid()) ");
|
|
|
|
|
|
StringBuilder SqlDetailStringBuilder = new StringBuilder(1024);
|
|
|
SqlDetailStringBuilder.Append("INSERT INTO dbo.wms_purchase_detail(order_no,item_no,part_id,part_no,part_spec,qty,rec_qty,box_qty,rec_box_qty,snp_qty,unit,erp_warehouse,item_status,");
|
|
|
SqlDetailStringBuilder.Append("factory_id, factory_code, enabled, create_userid, create_time, guid)");
|
|
|
SqlDetailStringBuilder.Append("VALUES(@orderNo, @itemNo, @partId, @partNo, @partSpec, @qty, @recQty, @boxQty, @recBoxQty, @snpQty, @unit, @erpWarehouse, '10',");
|
|
|
SqlDetailStringBuilder.Append("@factoryId, @factoryCode, @enabled, @createUserid, CONVERT(varchar(50), GETDATE(), 21), newid())");
|
|
|
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
Parameters.Add(htParams);
|
|
|
|
|
|
SqlStrings.Add(SqlDetailStringBuilder.ToString());
|
|
|
Parameters.Add(htDetailParams);
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < SqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(SqlStrings[i], Parameters[i], transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return 1;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return 0;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//关闭
|
|
|
public int onClose(String orderNos)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update wms_purchase set order_status='60' WHERE order_no in (" + orderNos + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
String detailStr = "update wms_purchase_detail set item_status='100' WHERE order_no in (" + orderNos + ")";
|
|
|
int datailResult = dbConn.Execute(detailStr);
|
|
|
return datailResult;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//启用
|
|
|
public int EnableData(String ids)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update wms_purchase set Enabled='Y' WHERE order_no in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
String detailStr = "update wms_purchase_detail set Enabled='Y' WHERE order_no in (" + ids + ")";
|
|
|
int datailResult = dbConn.Execute(detailStr);
|
|
|
return datailResult;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//禁用
|
|
|
public int DisableData(String ids)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update wms_purchase set Enabled='N' WHERE order_no in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
String detailStr = "update wms_purchase_detail set Enabled='N' WHERE order_no in (" + ids + ")";
|
|
|
int datailResult = dbConn.Execute(detailStr);
|
|
|
return datailResult;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public Hashtable onBarcodeGenerator(string orderNo, string userID, string factoryID, string factoryCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string detailPur = @"select a.ruid,a.order_no,a.item_no,a.part_id,a.part_no,a.part_spec,a.qty,a.Unit,a.erp_warehouse,default_box_qty,b.vendor_id,b.vendor_code,b.se_date,b.se_time,b.dock
|
|
|
from wms_purchase_detail a (nolock) left join wms_purchase b (nolock) on a.order_no=b.order_no left join sys_part c (nolock) on a.part_id = c.part_id where a.order_no = " + orderNo;
|
|
|
|
|
|
List<WmsPurchaseDetail> purchaseDetails = dbConn.Query<WmsPurchaseDetail>(detailPur).ToList();
|
|
|
List<string> sqlInsert = new List<string>();
|
|
|
List<DynamicParameters> parameInsert = new List<DynamicParameters>();
|
|
|
|
|
|
for (int i = 0; i < purchaseDetails.Count; i++)
|
|
|
{
|
|
|
int qty = Convert.ToInt32(purchaseDetails[i].Qty);
|
|
|
int defaultBoxQty = Convert.ToInt32(purchaseDetails[i].DefaultBoxQty) == 0 ? qty : Convert.ToInt32(purchaseDetails[i].DefaultBoxQty);
|
|
|
|
|
|
int qtyNum = qty / defaultBoxQty;//数量
|
|
|
int remaNum = qty % defaultBoxQty;//求余
|
|
|
|
|
|
|
|
|
int vendorId = purchaseDetails[i].VendorId;//供应商代码
|
|
|
string vendorCode = purchaseDetails[i].VendorName;//供应商代码
|
|
|
|
|
|
string sedate = purchaseDetails[i].SeDate;//交货日期
|
|
|
string setime = purchaseDetails[i].SeTime;//交货时间
|
|
|
|
|
|
int partId_I = purchaseDetails[i].PartId;
|
|
|
string partNo_I = purchaseDetails[i].PartNo;
|
|
|
string partSpec_I = purchaseDetails[i].PartSpec;
|
|
|
string unit = purchaseDetails[i].Unit;//单位
|
|
|
string dock = String.IsNullOrEmpty(purchaseDetails[i].Dock) ? "" : purchaseDetails[i].Dock;//道口
|
|
|
|
|
|
string orderNo_New = purchaseDetails[i].OrderNo;//GetOrderNo();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder();
|
|
|
|
|
|
for (int j = 0; j < qtyNum; j++)
|
|
|
{
|
|
|
string carNo_New = GetOrderNo("StockOrder", "M");
|
|
|
|
|
|
SqlStringBuilder.Remove(0, SqlStringBuilder.Length);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.sys_stock (vendor_id,vendor_code,carton_no,part_id,part_no,part_spec ");
|
|
|
SqlStringBuilder.Append(" ,lot_no,fix_lot_no,status,qty,snp_qty,locate_id,locate_name,group_no ");
|
|
|
SqlStringBuilder.Append(" ,erp_warehouse,date_code,qms_status,ref_order_no,unit,dock,warehouse_id ");
|
|
|
SqlStringBuilder.Append(" ,warehouse_name,zone_id,zone_name,printed,print_time,factory_id ");
|
|
|
SqlStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time) ");
|
|
|
SqlStringBuilder.Append(" VALUES (@vendor_id,@vendor_code,@carton_no,@part_id,@partNo,@part_spec ");
|
|
|
SqlStringBuilder.Append(" ,@lot_no,@fix_lot_no,@status,@qty,@snpQty,@locateId,@locateName ");
|
|
|
SqlStringBuilder.Append(" ,@groupNo,@erpWarehouse,@date_code,@qms_status,@refOrderNo ");
|
|
|
SqlStringBuilder.Append(" ,@unit,@dock,@warehouseId,@warehouseName,@zoneId,@zoneName ");
|
|
|
SqlStringBuilder.Append(" ,@printed,@print_time,@factory_id,@factory_code,@enabled,@create_userid ");
|
|
|
SqlStringBuilder.Append(" ,CONVERT(varchar(50), GETDATE(), 21)) ");
|
|
|
|
|
|
DynamicParameters param3 = new DynamicParameters();
|
|
|
param3.Add("@vendor_id", vendorId);
|
|
|
param3.Add("@vendor_code", vendorCode);
|
|
|
param3.Add("@carton_no", carNo_New);
|
|
|
param3.Add("@part_id", partId_I);
|
|
|
param3.Add("@partNo", partNo_I);
|
|
|
param3.Add("@part_spec", partSpec_I);
|
|
|
param3.Add("@lot_no", DateTime.Now.ToString("yyyyMMdd"));
|
|
|
param3.Add("@fix_lot_no", "");
|
|
|
param3.Add("@status", "20");
|
|
|
param3.Add("@qty", defaultBoxQty);
|
|
|
param3.Add("@snpQty", defaultBoxQty);
|
|
|
param3.Add("@locateId", 0);
|
|
|
param3.Add("@locateName", "");
|
|
|
param3.Add("@groupNo", "");
|
|
|
param3.Add("@erpWarehouse", purchaseDetails[i].ErpWarehouse);
|
|
|
param3.Add("@date_code", "");
|
|
|
param3.Add("@qms_status", "");
|
|
|
param3.Add("@refOrderNo", orderNo_New);
|
|
|
param3.Add("@unit", unit);
|
|
|
param3.Add("@dock", dock);
|
|
|
param3.Add("@warehouseId", 0);
|
|
|
param3.Add("@warehouseName", "");
|
|
|
param3.Add("@zoneId", 0);
|
|
|
param3.Add("@zoneName", "");
|
|
|
param3.Add("@printed", 0);
|
|
|
param3.Add("@print_time", "");
|
|
|
param3.Add("@factory_id", factoryID);
|
|
|
param3.Add("@factory_code", factoryCode);
|
|
|
param3.Add("@enabled", "Y");
|
|
|
param3.Add("@create_userid", userID);
|
|
|
|
|
|
|
|
|
sqlInsert.Add(SqlStringBuilder.ToString());
|
|
|
parameInsert.Add(param3);
|
|
|
}
|
|
|
if (remaNum > 0)
|
|
|
{
|
|
|
string carNo_New = GetOrderNo("StockOrder", "M");// repository.GetOrderNo();
|
|
|
SqlStringBuilder.Remove(0, SqlStringBuilder.Length);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.sys_stock (vendor_id,vendor_code,carton_no,part_id,part_no,part_spec ");
|
|
|
SqlStringBuilder.Append(" ,lot_no,fix_lot_no,status,qty,snp_qty,locate_id,locate_name,group_no ");
|
|
|
SqlStringBuilder.Append(" ,erp_warehouse,date_code,qms_status,ref_order_no,unit,dock,warehouse_id ");
|
|
|
SqlStringBuilder.Append(" ,warehouse_name,zone_id,zone_name,printed,print_time,factory_id ");
|
|
|
SqlStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time) ");
|
|
|
SqlStringBuilder.Append(" VALUES (@vendor_id,@vendor_code,@carton_no,@part_id,@partNo,@part_spec ");
|
|
|
SqlStringBuilder.Append(" ,@lot_no,@fix_lot_no,@status,@qty,@snpQty,@locateId,@locateName ");
|
|
|
SqlStringBuilder.Append(" ,@groupNo,@erpWarehouse,@date_code,@qms_status,@refOrderNo ");
|
|
|
SqlStringBuilder.Append(" ,@unit,@dock,@warehouseId,@warehouseName,@zoneId,@zoneName ");
|
|
|
SqlStringBuilder.Append(" ,@printed,@print_time,@factory_id,@factory_code,@enabled,@create_userid ");
|
|
|
SqlStringBuilder.Append(" ,CONVERT(varchar(50), GETDATE(), 21)) ");
|
|
|
|
|
|
DynamicParameters param3 = new DynamicParameters();
|
|
|
param3.Add("@vendor_id", vendorId);
|
|
|
param3.Add("@vendor_code", vendorCode);
|
|
|
param3.Add("@carton_no", carNo_New);
|
|
|
param3.Add("@part_id", partId_I);
|
|
|
param3.Add("@partNo", partNo_I);
|
|
|
param3.Add("@part_spec", partSpec_I);
|
|
|
param3.Add("@lot_no", DateTime.Now.ToString("yyyyMMdd"));
|
|
|
param3.Add("@fix_lot_no", "");
|
|
|
param3.Add("@status", "20");
|
|
|
param3.Add("@qty", remaNum);
|
|
|
param3.Add("@snpQty", defaultBoxQty);
|
|
|
param3.Add("@locateId", 0);
|
|
|
param3.Add("@locateName", "");
|
|
|
param3.Add("@groupNo", "");
|
|
|
param3.Add("@erpWarehouse", purchaseDetails[i].ErpWarehouse);
|
|
|
param3.Add("@date_code", "");
|
|
|
param3.Add("@qms_status", "");
|
|
|
param3.Add("@refOrderNo", orderNo_New);
|
|
|
param3.Add("@unit", unit);
|
|
|
param3.Add("@dock", dock);
|
|
|
param3.Add("@warehouseId", 0);
|
|
|
param3.Add("@warehouseName", "");
|
|
|
param3.Add("@zoneId", 0);
|
|
|
param3.Add("@zoneName", "");
|
|
|
param3.Add("@printed", 0);
|
|
|
param3.Add("@print_time", "");
|
|
|
param3.Add("@factory_id", factoryID);
|
|
|
param3.Add("@factory_code", factoryCode);
|
|
|
param3.Add("@enabled", "Y");
|
|
|
param3.Add("@create_userid", userID);
|
|
|
|
|
|
|
|
|
sqlInsert.Add(SqlStringBuilder.ToString());
|
|
|
parameInsert.Add(param3);
|
|
|
}
|
|
|
}
|
|
|
Hashtable result = new Hashtable();
|
|
|
if (!ExecuteSqlTransaction(sqlInsert, parameInsert))
|
|
|
{
|
|
|
result.Add("message", "生成条码失败，请重查看！");
|
|
|
result.Add("flag", "error");
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
|
|
|
string updateSql = "update wms_purchase set order_status=20 where order_no=" + orderNo;
|
|
|
int datailResult = dbConn.Execute(updateSql);
|
|
|
result.Add("message", "生成条码成功");
|
|
|
result.Add("flag", "OK");
|
|
|
}
|
|
|
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public bool ExecuteSqlTransaction(List<string> sqlStrings, List<DynamicParameters> parameters)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(sqlStrings[i], parameters[i], transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return true;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
#endregion 成员方法
|
|
|
}
|
|
|
}
|