|
|
using Dapper;
|
|
|
using Estsh.Core.Dapper;
|
|
|
using Estsh.Core.Model.EnumUtil;
|
|
|
using Estsh.Core.Model.ExcelModel;
|
|
|
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 PullGroupRepository : BaseRepository<WmsPullingGroup>, IPullGroupRepository
|
|
|
{
|
|
|
public PullGroupRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
|
|
|
{
|
|
|
}
|
|
|
#region 成员方法
|
|
|
/// <summary>
|
|
|
/// 获得菜单列表数据
|
|
|
/// </summary>
|
|
|
public List<WmsPullingGroup> getList(string ruid)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * from wms_pulling_group (nolock) ");
|
|
|
if (!ruid.Trim().Equals(""))
|
|
|
{
|
|
|
strSql.Append(" where ruid=" + ruid);
|
|
|
}
|
|
|
|
|
|
List<WmsPullingGroup> result = dbConn.Query<WmsPullingGroup>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getPullGroupListByPage(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_pulling_group a (nolock) ");
|
|
|
Params.Add("@Column", "*");
|
|
|
Params.Add("@PageSize", PageSize);
|
|
|
Params.Add("@CurrentPage", PageIndex);
|
|
|
Params.Add("@Condition", strWhere);
|
|
|
Params.Add("@OrderColumn", OrderBy);
|
|
|
Params.Add("@Group", 0);
|
|
|
|
|
|
List<WmsPullingGroup> dataList = dbConn.Query<WmsPullingGroup>("Com_Pagination", Params, commandType: CommandType.StoredProcedure).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", Params.Get<int>("@TotalCount"));
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 获取分页数据列表
|
|
|
/// </summary>
|
|
|
public Hashtable getPullGroupDetailListByPage(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock) ");
|
|
|
SqlStringBuilder.Append("where " + strWhere);
|
|
|
|
|
|
List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
|
|
|
result.Add("dataList", dataList);
|
|
|
result.Add("totalCount", dataList.Count());
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<WmsPullingGroupPart> getPullGroupDetailList(string strWhere)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
Hashtable result = new Hashtable();
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("select a.* from wms_pulling_group_part a (nolock) ");
|
|
|
SqlStringBuilder.Append("where " + strWhere);
|
|
|
List<WmsPullingGroupPart> dataList = dbConn.Query<WmsPullingGroupPart>(SqlStringBuilder.ToString()).ToList();
|
|
|
return dataList;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
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 where enabled='Y' ";
|
|
|
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 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 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 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 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 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 where Enabled = 'Y'");
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(strSql.ToString()).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysVendor> getSelectVendor(string vendorId)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
StringBuilder strSql = new StringBuilder();
|
|
|
strSql.Append("select * from sys_Vendor where Enabled = 'Y' and vendor_id='" + vendorId + "'");
|
|
|
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 where enum_type='wms_PullGroup_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;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<SysPdline> GetPdlineByPdlineId(int pdlineId)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE enabled='Y' and pdline_id='" + pdlineId + "'";
|
|
|
List<SysPdline> result = dbConn.Query<SysPdline>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public SysPdline GetPdlineByPdlineCode(string pdlineCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT * FROM sys_pdline (NOLOCK) WHERE pdline_code='" + pdlineCode + "'";
|
|
|
SysPdline result = dbConn.Query<SysPdline>(sql).FirstOrDefault();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
public List<KeyValueResult> GetPdlineByKey()
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT distinct pdline_code as [value],pdline_code as [key] FROM sys_pdline (NOLOCK) WHERE enabled='Y' ORDER BY pdline_code";
|
|
|
DynamicParameters param = new DynamicParameters();
|
|
|
List<KeyValueResult> result = dbConn.Query<KeyValueResult>(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);
|
|
|
string result = list.Get<string>("@order_no");
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public bool saveHandPullGroup(string groupCode, string handPullQty)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
List<string> sqlList = new List<string>();
|
|
|
List<DynamicParameters> parametersList = new List<DynamicParameters>();
|
|
|
DynamicParameters parameters = new DynamicParameters();
|
|
|
#region
|
|
|
string checkPullingGroupSql = @"select * from [dbo].[wms_pulling_group] where group_code = '" + groupCode + "'";
|
|
|
List<WmsPullingGroup> wmsPulling = dbConn.Query<WmsPullingGroup>(checkPullingGroupSql).ToList();
|
|
|
for (int j = 0; j < wmsPulling.Count; j++)
|
|
|
{
|
|
|
string getPullingGroupCumulativeValueSql = @"select * from [dbo].[wms_pulling_group] (nolock) where group_code='" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "'";
|
|
|
List<WmsPullingGroup> wmsPullingGroup = dbConn.Query<WmsPullingGroup>(getPullingGroupCumulativeValueSql).ToList();
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append(" select * from mes_work_order_part k (nolock) where k.order_no in (select distinct top " + handPullQty + " a.order_no from mes_work_order_part a (nolock) left join mes_work_order b (nolock) on a.order_no = b.order_no and a.parent_part_no = b.part_no ");
|
|
|
SqlStringBuilder.Append(" where a.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code ");
|
|
|
SqlStringBuilder.Append(" where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') and a.pull_status = 10 and pdline_code = '" + wmsPulling[j].PdlineCode + "' order by a.order_no ) ");
|
|
|
SqlStringBuilder.Append(" and k.part_no in (select part_no from[dbo].[wms_pulling_group_part] a (nolock) left join[dbo].[wms_pulling_group] b (nolock) on a.group_code = b.group_code ");
|
|
|
SqlStringBuilder.Append(" where b.group_code = '" + wmsPulling[j].GroupCode + "' and pdline_code = '" + wmsPulling[j].PdlineCode + "' and a.enabled='Y' and b.enabled='Y') ");
|
|
|
|
|
|
List<MesWorkOrderPart> mesWorkOrderParts = dbConn.Query<MesWorkOrderPart>(SqlStringBuilder.ToString()).ToList();
|
|
|
if (mesWorkOrderParts.Count > 0)
|
|
|
{
|
|
|
List<MesWorkOrderPart> storeInfoList = mesWorkOrderParts.GroupBy(x => new { x.PartNo, x.FactoryId, x.FactoryCode })
|
|
|
.Select(g => new MesWorkOrderPart { PartNo = g.Key.PartNo, FactoryId = g.Key.FactoryId, FactoryCode = g.Key.FactoryCode, Qty = g.Sum(b => b.Qty) }).ToList();
|
|
|
|
|
|
var orderNo = GetOrderNo("ShiftProduction", "P");//生产领料
|
|
|
for (int i = 0; i < storeInfoList.Count; i++)
|
|
|
{
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail ");
|
|
|
SqlStringBuilder.Append(" (order_no,item_no,part_id,part_no,part_spec,qty,pick_qty,out_qty,rec_qty ");
|
|
|
SqlStringBuilder.Append(" ,unit,plan_date,plan_time,item_status,src_warehouse_id,src_warehouse_name,dest_warehouse_id ");
|
|
|
SqlStringBuilder.Append(" ,dest_warehouse_name,src_zone_id,src_zone_name,dest_zone_id,dest_zone_name,src_erp_warehouse,dest_erp_warehouse,factory_id ");
|
|
|
SqlStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time,guid) ");
|
|
|
SqlStringBuilder.Append(" VALUES(@orderNo,@itemNo,@partId,@partNo,@partSpec,@qty,@pickQty,@outQty,@recQty,@unit,@planDate ");
|
|
|
SqlStringBuilder.Append(" ,@planTime,@itemStatus,@srcWarehouseId,@srcWarehouseName,@destWarehouseId,@destWarehouseName ");
|
|
|
SqlStringBuilder.Append(" ,@srcZoneId,@srcZoneName,@destZoneId,@destZoneName,@srcErpWarehouse,@destErpWarehouse ");
|
|
|
SqlStringBuilder.Append(" ,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@guid) ");
|
|
|
sqlList.Add(SqlStringBuilder.ToString());
|
|
|
parameters = new DynamicParameters();
|
|
|
parameters.Add("@orderNo", orderNo);//生产领料单
|
|
|
parameters.Add("@itemNo", (i + 1) * 10);//序号
|
|
|
|
|
|
StringBuilder partNoStringBuilder = new StringBuilder(1024);
|
|
|
partNoStringBuilder.Append("select * from sys_part (nolock) where part_no='" + storeInfoList[i].PartNo + "'");//零件号ID
|
|
|
List<SysPart> sysParts = dbConn.Query<SysPart>(partNoStringBuilder.ToString()).ToList();
|
|
|
if (sysParts.Count > 0)
|
|
|
{
|
|
|
parameters.Add("@partId", sysParts[0].PartId);//
|
|
|
parameters.Add("@partNo", sysParts[0].PartNo);//零件号
|
|
|
parameters.Add("@partSpec", sysParts[0].PartSpec);//简码
|
|
|
parameters.Add("@unit", sysParts[0].Unit);//单位
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
parameters.Add("@partId", 0);//零件号ID
|
|
|
parameters.Add("@partNo", storeInfoList[i].PartNo);//零件号
|
|
|
parameters.Add("@partSpec", "");//简码
|
|
|
parameters.Add("@unit", "");//单位
|
|
|
}
|
|
|
|
|
|
parameters.Add("@qty", storeInfoList[i].Qty);//数量
|
|
|
parameters.Add("@pickQty", 0);//
|
|
|
parameters.Add("@outQty", 0);//
|
|
|
parameters.Add("@recQty", 0);//
|
|
|
|
|
|
parameters.Add("@planDate", "");//计划日期
|
|
|
parameters.Add("@planTime", "");//计划时间
|
|
|
parameters.Add("@itemStatus", "10");//状态
|
|
|
parameters.Add("@srcWarehouseId", 0);//源仓库ID
|
|
|
parameters.Add("@srcWarehouseName", "");//源仓库
|
|
|
parameters.Add("@srcZoneId", 0);//源库区ID
|
|
|
parameters.Add("@srcZoneName", "");//源库区
|
|
|
parameters.Add("@srcErpWarehouse", "");//源ERP库存地
|
|
|
|
|
|
string sqlPull = "SELECT * FROM dbo.wms_pulling_group (nolock) where group_code = '" + wmsPulling[j].GroupCode + "'";
|
|
|
WmsPullingGroup pullingGroup = dbConn.QueryFirstOrDefault<WmsPullingGroup>(sqlPull);
|
|
|
if (pullingGroup != null)
|
|
|
{
|
|
|
parameters.Add("@destWarehouseId", 0);//目标仓库ID
|
|
|
parameters.Add("@destWarehouseName", "");//目标仓库
|
|
|
parameters.Add("@destZoneId", 0);//目标库区ID
|
|
|
parameters.Add("@destZoneName", pullingGroup.GroupName);//目标库区
|
|
|
parameters.Add("@destErpWarehouse", "");//目标ERP库存地
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
parameters.Add("@destWarehouseId", 0);//目标仓库ID
|
|
|
parameters.Add("@destWarehouseName", "");//目标仓库
|
|
|
parameters.Add("@destZoneId", 0);//目标库区ID
|
|
|
parameters.Add("@destZoneName", "");//目标库区
|
|
|
parameters.Add("@destErpWarehouse", "");//目标ERP库存地
|
|
|
}
|
|
|
|
|
|
parameters.Add("@factoryId", storeInfoList[0].FactoryId);//工厂ID
|
|
|
parameters.Add("@factoryCode", storeInfoList[0].FactoryCode);//工厂代码
|
|
|
parameters.Add("@enabled", "Y");
|
|
|
parameters.Add("@createUserid", 0);
|
|
|
parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
|
|
|
parameters.Add("@guid", Guid.NewGuid().ToString());//
|
|
|
parametersList.Add(parameters);
|
|
|
}
|
|
|
|
|
|
StringBuilder stringBuilderSQL = new StringBuilder(1024);
|
|
|
stringBuilderSQL.Append("INSERT INTO dbo.wms_move_header ");
|
|
|
stringBuilderSQL.Append(" (order_no,order_type,order_status,ref_order_no,factory_id,factory_code ");
|
|
|
stringBuilderSQL.Append(" ,enabled,create_userid,create_time,guid) ");
|
|
|
stringBuilderSQL.Append(" VALUES(@orderNo,@orderType,@orderStatus,@refOrderNo ");
|
|
|
stringBuilderSQL.Append(" ,@factoryId,@factoryCode,@enabled,@createUserid ");
|
|
|
stringBuilderSQL.Append(" ,@createTime,@guid) ");
|
|
|
sqlList.Add(stringBuilderSQL.ToString());
|
|
|
|
|
|
parameters = new DynamicParameters();
|
|
|
parameters.Add("@orderNo", orderNo);//生产领料单
|
|
|
parameters.Add("@orderType", (int)WmsEnumUtil.MoveOrderType.PICK);//生产领料单
|
|
|
parameters.Add("@orderStatus", (int)WmsEnumUtil.MoveOrderStatus.CREATE);//已创建
|
|
|
parameters.Add("@refOrderNo", "成品下线大件拉动");//
|
|
|
|
|
|
parameters.Add("@factoryId", mesWorkOrderParts[0].FactoryId);//
|
|
|
parameters.Add("@factoryCode", mesWorkOrderParts[0].FactoryCode);//
|
|
|
parameters.Add("@enabled", "Y");//
|
|
|
parameters.Add("@createUserid", 0);//
|
|
|
parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
|
|
|
parameters.Add("@guid", Guid.NewGuid().ToString());//
|
|
|
parametersList.Add(parameters);
|
|
|
|
|
|
for (int i = 0; i < mesWorkOrderParts.Count; i++)
|
|
|
{
|
|
|
for (int k = 0; k < storeInfoList.Count; k++)
|
|
|
{
|
|
|
stringBuilderSQL = new StringBuilder(1024);
|
|
|
stringBuilderSQL.Append("update mes_work_order_part set pull_status =@pullStatus where order_no=@order_no and part_no=@part_no");
|
|
|
sqlList.Add(stringBuilderSQL.ToString());
|
|
|
parameters = new DynamicParameters();
|
|
|
parameters.Add("@order_no", mesWorkOrderParts[i].OrderNo);//id
|
|
|
parameters.Add("@part_no", storeInfoList[k].PartNo);//id
|
|
|
parameters.Add("@pullStatus", "20");//
|
|
|
parametersList.Add(parameters);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
return InsertData(sqlList, parametersList);
|
|
|
#endregion
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 插入菜单数据
|
|
|
/// </summary>
|
|
|
/// <param name="htParams"></param>
|
|
|
/// <returns></returns>
|
|
|
public int savePullGroup(WmsPullingGroup htParams, IList<WmsPullingGroupPart> htDetailParams)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
for (int i = 0; i < htDetailParams.Count; i++)
|
|
|
{
|
|
|
htDetailParams[i].GroupCode = htParams.GroupCode;
|
|
|
htDetailParams[i].GroupName = htParams.GroupName;
|
|
|
}
|
|
|
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_pulling_group ");
|
|
|
SqlStringBuilder.Append(" (group_code,group_name,pulling_type,pdline_code,pdline_name,kitting_qty,triggerValue,pullValue,is_auto_print,print_copies ");
|
|
|
SqlStringBuilder.Append(" ,printer,print_template,factory_id,factory_code,enabled,create_userid,create_time,guid) ");
|
|
|
SqlStringBuilder.Append(" VALUES(@groupCode,@groupName,@pullingType,@pdlineCode,@pdlineName,@kittingQty,@triggerValue,@pullValue,@isAutoPrint,@printCopies ");
|
|
|
SqlStringBuilder.Append(" ,@printer,@printTemplate,@factoryId,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21),newid()) ");
|
|
|
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
Parameters.Add(htParams);
|
|
|
|
|
|
StringBuilder SqlDetailStringBuilder = new StringBuilder(1024);
|
|
|
SqlDetailStringBuilder.Append("INSERT INTO dbo.wms_pulling_group_part ");
|
|
|
SqlDetailStringBuilder.Append(" (group_code,group_name,part_id,part_no,part_spec,pull_qty,factory_id ");
|
|
|
SqlDetailStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time,guid) ");
|
|
|
SqlDetailStringBuilder.Append(" VALUES(@groupCode,@groupName,@partId,@partNo,@partSpec,@pullQty,@factoryId ");
|
|
|
SqlDetailStringBuilder.Append(" ,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21), newid()) ");
|
|
|
|
|
|
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 updatePullGroup(WmsPullingGroup htParams, IList<WmsPullingGroupPart> htDetailParams)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
for (int i = 0; i < htDetailParams.Count; i++)
|
|
|
{
|
|
|
htDetailParams[i].GroupCode = htParams.GroupCode;
|
|
|
htDetailParams[i].GroupName = htParams.GroupName;
|
|
|
}
|
|
|
dbConn.Open();
|
|
|
List<string> SqlStrings = new List<string>();
|
|
|
List<object> Parameters = new List<object>();
|
|
|
|
|
|
StringBuilder SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("UPDATE dbo.wms_pulling_group ");
|
|
|
SqlStringBuilder.Append(" SET group_code = @groupCode,group_name = @groupName ");
|
|
|
SqlStringBuilder.Append(" ,triggerValue = @triggerValue,pullValue = @pullValue ");
|
|
|
SqlStringBuilder.Append(" ,pulling_type = @pullingType,pdline_name = @pdlineName ");
|
|
|
SqlStringBuilder.Append(" ,pdline_code = @pdlineCode,kitting_qty = @kittingQty ");
|
|
|
SqlStringBuilder.Append(" ,is_auto_print = @isAutoPrint,print_copies = @printCopies ");
|
|
|
SqlStringBuilder.Append(" ,factory_id = @factoryId,factory_code = @factoryCode ");
|
|
|
SqlStringBuilder.Append(" ,enabled = @enabled,update_userid = @updateUserid ");
|
|
|
SqlStringBuilder.Append(" ,update_time = @updateTime ");
|
|
|
SqlStringBuilder.Append(" WHERE group_code=@groupCode ");
|
|
|
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
Parameters.Add(htParams);
|
|
|
|
|
|
for (int i = 0; i < htDetailParams.Count; i++)
|
|
|
{
|
|
|
string sql = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE group_code='" + htDetailParams[i].GroupCode + "' and part_no='" + htDetailParams[i].PartNo + "' ";
|
|
|
List<WmsPullingGroupPart> pullingGroupParts = dbConn.Query<WmsPullingGroupPart>(sql).ToList();
|
|
|
if (pullingGroupParts.Count > 0)
|
|
|
{
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("UPDATE dbo.wms_pulling_group_part ");
|
|
|
SqlStringBuilder.Append(" SET group_code = @groupCode,group_name = @groupName ");
|
|
|
SqlStringBuilder.Append(" ,part_id = @partId,part_no = @partNo ");
|
|
|
SqlStringBuilder.Append(" ,part_spec = @partSpec,pull_qty = @pullQty ");
|
|
|
SqlStringBuilder.Append(" ,factory_id = @factoryId,factory_code = @factoryCode ");
|
|
|
SqlStringBuilder.Append(" ,enabled = @enabled,update_userid = @updateUserid ");
|
|
|
SqlStringBuilder.Append(" ,update_time = @updateTime ");
|
|
|
SqlStringBuilder.Append(" WHERE group_code = @groupCode and part_no=@partNo ");
|
|
|
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
Parameters.Add(htDetailParams[i]);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
SqlStringBuilder = new StringBuilder(1024);
|
|
|
SqlStringBuilder.Append("INSERT INTO dbo.wms_pulling_group_part ");
|
|
|
SqlStringBuilder.Append(" (group_code,group_name,part_id,part_no,part_spec,pull_qty,factory_id ");
|
|
|
SqlStringBuilder.Append(" ,factory_code,enabled,create_userid,create_time,guid) ");
|
|
|
SqlStringBuilder.Append(" VALUES(@groupCode,@groupName,@partId,@partNo,@partSpec,@pullQty,@factoryId ");
|
|
|
SqlStringBuilder.Append(" ,@factoryCode,@enabled,@createUserid,CONVERT(varchar(50), GETDATE(), 21), newid()) ");
|
|
|
|
|
|
SqlStrings.Add(SqlStringBuilder.ToString());
|
|
|
Parameters.Add(htDetailParams[i]);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
string sqlStr = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE group_code='" + htParams.GroupCode + "' and factory_code='" + htParams.FactoryCode + "' ";
|
|
|
List<WmsPullingGroupPart> wmsPullingGroupParts = dbConn.Query<WmsPullingGroupPart>(sqlStr).ToList();
|
|
|
|
|
|
StringBuilder stringBuilderDel = new StringBuilder(1024);
|
|
|
for (int i = 0; i < wmsPullingGroupParts.Count; i++)
|
|
|
{
|
|
|
WmsPullingGroupPart wmsPullingGroupPart = htDetailParams.Where(a => a.PartNo == wmsPullingGroupParts[i].PartNo).FirstOrDefault();
|
|
|
if (wmsPullingGroupPart == null)
|
|
|
{
|
|
|
stringBuilderDel.Append("delete from dbo.wms_pulling_group_part (nolock) where group_code=@groupCode and part_no=@partNo ");
|
|
|
|
|
|
SqlStrings.Add(stringBuilderDel.ToString());
|
|
|
DynamicParameters dynamicParameters = new DynamicParameters();
|
|
|
dynamicParameters.Add("@groupCode", htParams.GroupCode);
|
|
|
dynamicParameters.Add("@partNo", wmsPullingGroupParts[i].PartNo);
|
|
|
Parameters.Add(dynamicParameters);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
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;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <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> 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 int EnableData(String ids)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
String delStr = "update wms_pulling_group set Enabled='Y' WHERE group_code in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
String detailStr = "update wms_pulling_group_part set Enabled='Y' WHERE group_code 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_pulling_group set Enabled='N' WHERE group_code in (" + ids + ")";
|
|
|
int result = dbConn.Execute(delStr);
|
|
|
String detailStr = "update wms_pulling_group_part set Enabled='N' WHERE group_code in (" + ids + ")";
|
|
|
int datailResult = dbConn.Execute(detailStr);
|
|
|
return datailResult;
|
|
|
}
|
|
|
}
|
|
|
public PullGroup ifExistsPullGroup(string groupCode)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT * FROM wms_pulling_group (NOLOCK) WHERE group_code = '" + groupCode + "'";
|
|
|
PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public PullGroup ifExistsPullGroupPart(string groupCode, string partNo)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = "SELECT * FROM wms_pulling_group_part (NOLOCK) WHERE group_code = '" + groupCode + "' and part_no='" + partNo + "'";
|
|
|
PullGroup result = dbConn.Query<PullGroup>(sql).FirstOrDefault();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
public List<PullGroup> getExportList(string strWhere, string orderBy)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
string sql = @"select b.group_code,b.group_name,case when b.pulling_type=10 then 'JIT蓝卡队列' when b.pulling_type=20 then 'JIT指示票队列' end pulling_type
|
|
|
,b.pdline_code,b.kitting_qty,b.triggerValue,b.pullValue,a.part_id,part_no,part_spec,a.pull_qty
|
|
|
from wms_pulling_group_part a (nolock) left join wms_pulling_group b (nolock) on a.group_code=b.group_code
|
|
|
where " + strWhere + orderBy + " ";
|
|
|
List<PullGroup> result = dbConn.Query<PullGroup>(sql).ToList();
|
|
|
return result;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
//事务批量执行添加、修改
|
|
|
public bool InsertData(List<string> sqlStrings, List<DynamicParameters> parameterList)
|
|
|
{
|
|
|
using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
|
|
|
{
|
|
|
if (dbConn.State == ConnectionState.Closed)
|
|
|
{
|
|
|
dbConn.Open();
|
|
|
}
|
|
|
//执行事务
|
|
|
IDbTransaction transaction = dbConn.BeginTransaction();
|
|
|
if (parameterList == null || parameterList.Count == 0)
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(sqlStrings[i], null, transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return true;
|
|
|
}
|
|
|
catch (Exception exception)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
for (int i = 0; i < sqlStrings.Count; i++)
|
|
|
{
|
|
|
dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
|
|
|
}
|
|
|
transaction.Commit();
|
|
|
return true;
|
|
|
}
|
|
|
catch (Exception exception)
|
|
|
{
|
|
|
transaction.Rollback();
|
|
|
return false;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#endregion 成员方法
|
|
|
}
|
|
|
}
|