/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public bool InsertInfo(InboundTypeInfo info)
{
int cnt = dal.GetCounts("[ITEM_NAME] = N'" + info.ItemName + "' and [CODE_FID] = N'" + codeFid + "'");
if (cnt > 0)
{
throw new Exception("MC:0x00000119");///进仓类型不能重复
}
string itemValue = new SeqDefineDAL().GetCurrentCode("INBOUND_TYPE_VALUE");
cnt = dal.GetCounts("[ITEM_VALUE] = " + itemValue + " and [CODE_FID] = N'" + codeFid + "'");
if (cnt > 0 || string.IsNullOrEmpty(itemValue))
{
throw new Exception("MC:0x00000119");///进仓类型不能重复
}
CodeItemInfo codeItemInfo = new CodeItemInfo();
codeItemInfo.Fid = Guid.NewGuid();
codeItemInfo.ItemValue = int.Parse(itemValue);
codeItemInfo.ItemName = info.ItemName;
codeItemInfo.Comments = info.Comments;
codeItemInfo.CodeFid = Guid.Parse(codeFid);
codeItemInfo.ValidFlag = true;
codeItemInfo.CreateUser = info.CreateUser;
codeItemInfo.CreateDate = info.CreateDate;
return(dal.Add(codeItemInfo) > 0 ? true : false);
}
/// <summary>
/// IFM-004系统接口-接收
/// </summary>
/// <param name="sourceSystem"></param>
/// <param name="logFid"></param>
/// <param name="methodCode"></param>
/// <param name="keyValue"></param>
/// <param name="msgContent"></param>
/// <param name="errorCode"></param>
/// <param name="errorMsg"></param>
/// <param name="loginUser"></param>
/// <param name="executeStartTime"></param>
/// <returns></returns>
public static string GetCreateInboundLogSql(string sourceSystem, Guid logFid, string methodCode, string keyValue, string msgContent, string errorCode, string errorMsg, string loginUser, DateTime executeStartTime, int executeResult = (int)ExecuteResultConstants.Success)
{
///交易编号
string transNo = new SeqDefineDAL().GetCurrentCode("INTERFACE_TRANS_NO", sourceSystem.ToUpper(), ownerSystem);
return("insert into [LES].[TI_IFM_" + sourceSystem.ToUpper() + "_INBOUND_LOG] "
+ "(FID, TRANS_NO, SOURCE_SYSTEM, TARGET_SYSTEM, METHOD_CODE, EXECUTE_START_TIME, EXECUTE_END_TIME, EXECUTE_RESULT, EXECUTE_TIMES, KEY_VALUE, MSG_CONTENT, ERROR_CODE, ERROR_MSG, VALID_FLAG, CREATE_USER, CREATE_DATE) "
+ "values (N'" + logFid + "', N'" + transNo + "', N'" + sourceSystem + "', N'" + ownerSystem + "', N'" + methodCode + "', N'" + executeStartTime + "', GETDATE(), " + executeResult + ", 1, N'" + keyValue + "', N'" + msgContent.Replace("'", "''") + "', N'" + errorCode + "', N'" + errorMsg + "', 1, N'" + loginUser + "', GETDATE());");
}
/// <summary>
/// 完成
/// </summary>
/// <param name="rowsKeyValues"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool CompleteInfos(List <string> rowsKeyValues, string loginUser)
{
if (dal.GetList("[ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ") and [STATUS] = " + (int)PackageBarcodeStatusConstants.GroupSupport, "[ID]").Count != rowsKeyValues.Count)
{
throw new Exception("MC:0x00000462");///状态为已组托才能进行完成操作
}
List <PackageBarcodeInfo> packageBarcodes = dal.GetList("[ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ")", "[ID]");
if (packageBarcodes == null || packageBarcodes.Count() == 0)
{
throw new Exception("MC:0x00000084");///数据错误
}
DataTable dt = CommonDAL.ExecuteDataTableBySql("select [SUPPLIER_NUM],[DOCK],[WM_NO],[ZONE_NO] from [LES].[TT_PCM_PACKAGE_BARCODE] where [ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ") group by [SUPPLIER_NUM],[DOCK],[WM_NO],[ZONE_NO]");
List <PackageBarcodeDetailInfo> packageBarcodeDetails = new PackageBarcodeDetailDAL().GetList("[PACKAGE_BARCODE_FID] in ('" + string.Join("','", packageBarcodes.Select(d => d.Fid.GetValueOrDefault()).ToArray()) + "')", string.Empty);
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < dt.Rows.Count; i++)
{
List <PackageBarcodeInfo> barcodeInfos = packageBarcodes.Where(d => d.Dock == dt.Rows[i]["DOCK"].ToString() && d.SupplierNum == dt.Rows[i]["SUPPLIER_NUM"].ToString() && d.WmNo == dt.Rows[i]["WM_NO"].ToString() && d.ZoneNo == dt.Rows[i]["ZONE_NO"].ToString()).ToList();
if (barcodeInfos == null)
{
throw new Exception("MC:0x00000084");///数据错误
}
Guid guid = Guid.NewGuid();
string OrderNo = new SeqDefineDAL().GetCurrentCode("POUT_ORDER_NO");
stringBuilder.Append("insert into [LES].[TT_PCM_PACKAGE_OUTBOUND]([FID],[ORDER_NO],[S_WM_NO],[S_ZONE_NO],[SUPPLIER_NUM],[T_DOCK],[STATUS],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])values(N'" + guid + "',N'" + OrderNo + "',N'" + barcodeInfos.FirstOrDefault().WmNo + "',N'" + barcodeInfos.FirstOrDefault().ZoneNo + "',N'" + barcodeInfos.FirstOrDefault().SupplierNum + "',N'" + barcodeInfos.FirstOrDefault().Dock + "'," + (int)WmmOrderStatusConstants.Published + ",1,GETDATE(),N'" + loginUser + "')\n");
stringBuilder.Append("update [LES].[TT_PCM_PACKAGE_BARCODE] set [PACKAGE_ORDER_NO] = N'" + OrderNo + "',[PACKAGE_ORDER_TYPE] = 001,[PACKAGE_ORDER_FID] = N'" + guid + "',[MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] in ('" + string.Join("','", barcodeInfos.Select(d => d.Id).ToArray()) + "')\n");
foreach (var items in barcodeInfos)
{
List <PackageBarcodeDetailInfo> infos = packageBarcodeDetails.Where(d => d.PackageBarcodeFid == items.Fid).ToList();
foreach (var info in infos)
{
Guid fid = new Guid();
stringBuilder.Append("insert into [LES].[TT_PCM_PACKAGE_OUTBOUND_DETAIL] ([FID],[ORDER_FID],[ORDER_NO],[S_WM_NO],[S_ZONE_NO],[S_DLOC],[SUPPLIER_NUM],[PACKAGE_MODEL],[PACKAGE_CNAME],[PACKAGE_TYPE],[PACKAGE_QTY],[PACKAGE_BARCODE],[PACKAGE_BARCODE_FID],[VALID_FLAG],[CREATE_DATE],[CREATE_USER])values(");
stringBuilder.Append("N'" + fid + "',N'" + guid + "',N'" + OrderNo + "',N'" + items.WmNo + "',N'" + items.ZoneNo + "',N'" + items.Dloc + "',N'" + items.SupplierNum + "',N'" + info.PackageModel + "',N'" + info.PackageCname + "',N'" + info.PackageType + "',N'" + info.PackageQty + "',N'" + items.BarcodeNo + "',N'" + items.Fid + "',1,GETDATE(),N'" + loginUser + "'");
stringBuilder.Append(")\n");
}
}
}
stringBuilder.Append("update [LES].[TT_PCM_PACKAGE_BARCODE] set [STATUS] = N'" + (int)PackageBarcodeStatusConstants.GroupSingle + "', [MODIFY_DATE] = GETDATE(),[MODIFY_USER] = N'" + loginUser + "' where [ID] in (" + string.Join(",", rowsKeyValues.ToArray()) + ")\n");
using (TransactionScope trans = new TransactionScope())
{
if (!string.IsNullOrEmpty(stringBuilder.ToString()))
{
CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
}
trans.Complete();
}
return(true);
}
/// <summary>
/// IFM-005系统接口-发送
/// </summary>
/// <param name="targetSystem"></param>
/// <param name="logFid"></param>
/// <param name="methodCode"></param>
/// <param name="keyValue"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public static string GetCreateOutboundLogSql(string targetSystem, Guid logFid, string methodCode, string keyValue, string loginUser)
{
///交易编号
string transNo = new SeqDefineDAL().GetCurrentCode("INTERFACE_TRANS_NO", ownerSystem, targetSystem.ToUpper());
return("insert into [LES].[TI_IFM_" + targetSystem.ToUpper() + "_OUTBOUND_LOG] "
+ "(FID, TRANS_NO, SOURCE_SYSTEM, TARGET_SYSTEM, METHOD_CODE, KEY_VALUE, EXECUTE_RESULT, VALID_FLAG, CREATE_USER, CREATE_DATE) "
+ "values (N'" + logFid + "', N'" + transNo + "', N'" + ownerSystem + "', N'" + targetSystem.ToUpper() + "', N'" + methodCode + "', N'" + keyValue + "', " + (int)ExecuteResultConstants.Submit + ", 1, N'" + loginUser + "', GETDATE());"
+ "insert into [LES].[TI_IFM_" + targetSystem.ToUpper() + "_OUTBOUND_DETAIL_LOG] "
+ "(FID, LOG_FID, TRANS_NO, METHOD_CODE, EXECUTE_RESULT, VALID_FLAG, CREATE_USER, CREATE_DATE,EXECUTE_START_TIME) "
+ "values (NEWID(), N'" + logFid + "', N'" + transNo + "', N'" + methodCode + "', " + (int)ExecuteResultConstants.Submit + ", 1, N'" + loginUser + "', GETDATE(),GETDATE());");
}
/// <summary>
/// InsertInfo
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public long InsertInfo(MessageInfo info)
{
int cnt = dal.GetCounts("[MESSAGE_CN] = N'" + info.MessageCn + "' and [MESSAGE_TYPE] = " + info.MessageType.GetValueOrDefault() + "");
if (cnt > 0)
{
throw new Exception("MC:1x00000019");///同一类型下提示消息不能重复
}
if (!string.IsNullOrEmpty(info.MessageEn))
{
cnt = dal.GetCounts("[MESSAGE_EN] = N'" + info.MessageEn + "' and [MESSAGE_TYPE] = " + info.MessageType.GetValueOrDefault() + "");
if (cnt > 0)
{
throw new Exception("MC:1x00000019");///同一类型下提示消息不能重复
}
}
///消息代码
string messageType = "0";
switch (info.MessageType.GetValueOrDefault())
{
case (int)MessageTypeConstants.SysMsg: messageType = "1"; break;
case (int)MessageTypeConstants.ProgramMsg: messageType = "7"; break;
case (int)MessageTypeConstants.ClientMsg: messageType = "3"; break;
default: messageType = "0"; break;
}
///
string messageCode = new SeqDefineDAL().GetCurrentCode("MESSAGE_CODE", messageType);
cnt = dal.GetCounts("[MESSAGE_CODE] = N'" + messageCode + "'");
if (cnt > 0)
{
throw new Exception("MC:1x00000000");///消息代码重复，请确认后重新保存
}
info.MessageCode = messageCode;
return(dal.Add(info));
}
/// <summary>
/// 看板生成拉动单
/// </summary>
/// <param name="cardNos"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool CreateKanbanPullOrder(List <String> cardNos, string loginUser, bool isFilterLoginUser = false)
{
if (cardNos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
#region 初始化SQL脚本
///主表字符串
string sqlKanbanPullOrder = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER] (
FID,
ORDER_CODE,
PART_BOX_CODE,
PART_BOX_NAME,
ROUTE_CODE,
ROUTE_NAME,
SOURCE_ZONE_NO,
SOURCE_WM_NO,
TARGET_ZONE_NO,
TARGET_WM_NO,
PICK_UP_FINISH_TIME,
PICK_UP_USER,
DELIVERY_FINISH_TIME,
DELIVERY_USER,
PRINT_CNT,
PRINT_TIME,
PRINT_USER,
STATUS,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
///明细表字符串
string sqlKanbanPullOrderDetail = @"insert into [LES].[TT_MPM_KANBAN_PULL_ORDER_DETAIL] (
FID,
ORDER_FID,
ORDER_CODE,
ORDER_STATUS,
CARD_NO,
PART_NO,
PART_NAME,
SUPPLIER_CODE,
SUPPLIER_NAME,
PART_QTY,
PACKAGE_CODE,
PACKAGE_QTY,
VALID_FLAG,
CREATE_DATE,
CREATE_USER) VALUES ({0});" ;
#endregion
///获取所有看板卡信息
List <KanbanCardInfo> kanbanCardInfos = GetKanBanCardsListIsByLoginUser(cardNos, loginUser, isFilterLoginUser);
if (kanbanCardInfos.Count == 0)
{
throw new Exception("MC:3x00000011");///看板卡数据不能为空
}
if (cardNos.Count != kanbanCardInfos.Count)
{
throw new Exception("MC:0x00000257");///所提交的看板卡状态必须为已启用且不能为已扫描
}
///根据看板卡信息中的零件类代码②对物料需求进行分组
var groupbyPartboxcode = kanbanCardInfos.GroupBy(ml => new { ml.PartBoxCode }).Select(w => w.Key).ToList();
#region 根据零件类获取看板拉动物料拉动对应拉动零件
List <MaintainInhouseLogisticStandardInfo> maintainInhouseLogisticStandardInfos
= new MaintainInhouseLogisticStandardBLL().GetList(
"[INHOUSE_PART_CLASS] in ('"
+ string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))
+ "') and [STATUS] = " + (int)BasicDataStatusConstants.Enable
+ " AND [INHOUSE_SYSTEM_MODE]=N'"
+ (int)PullModeConstants.Kanban
+ "'", string.Empty);
if (maintainInhouseLogisticStandardInfos.Count == 0)
{
throw new Exception("0x00000233");///没有已启用的物料拉动信息
}
#endregion
#region 获取所有看板零件类以分组方式去重
List <KanbanPartBoxInfo> kanbanPartBoxInfos = new KanbanPartBoxDAL().GetList(
string.Format("[STATUS] = "
+ (int)BasicDataStatusConstants.Enable
+ " and [PART_BOX_CODE] in ('{0}') ", string.Join("','", groupbyPartboxcode.Select(w => w.PartBoxCode))), string.Empty);
if (kanbanPartBoxInfos.Count == 0)
{
throw new Exception("MC:3x00000014");///看板零件类数据错误
}
#endregion
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine("update [LES].[TM_MPM_KANBAN_CARD] " +
"set [USED_STATUS] = " + (int)KanbanCardUseStatusConstants.Scaned + "," +
"[MODIFY_USER] = N'" + loginUser + "'," +
"[MODIFY_DATE] = GETDATE() " +
"where [ID] in (" + string.Join(",", kanbanCardInfos.Select(d => d.Id).ToArray()) + ");");
#region 遍历看板卡信息
foreach (var partboxcode in groupbyPartboxcode)
{
///获取看板零件类信息
KanbanPartBoxInfo kanbanPartBoxInfo = kanbanPartBoxInfos.FirstOrDefault(d => d.PartBoxCode == partboxcode.PartBoxCode);
///TT_MPM_KANBAN_PULL_ORDER.FID
Guid pullOrderFid = Guid.NewGuid();
///看板拉动单号②依据序列号规则中的KANBAN_PULL_ORDER_CODE进行生成，明细中拉动单号③相同于主表
string orderCode = new SeqDefineDAL().GetCurrentCode("KANBAN_PULL_ORDER_CODE", "KB");///TODO:看板拉动单号生成
///看板拉动单外键①为NEW.GUID，同时作为看板拉动单明细的主表外键②，明细中外键①为NEW.GUID
///物流路线代码⑤名称⑥、来源仓库⑧存储区⑦、目标仓库⑩存储区⑨从零件类代码③对应的看板零件类TM_MPM_KANBAN_PART_BOX信息中获取
///创建时间获取数据库服务器时间，创建人取自于参数操作用户
///创建后看板拉动单状态⑱为20.已发布，数据有效标记⑲为true，看板拉动单其它字段在生成时留空
#region TT_MPM_KANBAN_PULL_ORDER
string sqlKanbanPullOrderValue = "N'" + pullOrderFid.ToString() + "'" ///FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxCode + "'" ///PART_BOX_CODE
+ ",N'" + kanbanPartBoxInfo.PartBoxName + "'" ///PART_BOX_NAME
+ ",N'" + kanbanPartBoxInfo.RouteCode + "'" ///ROUTE_CODE
+ ",N'" + kanbanPartBoxInfo.RouteName + "'" ///ROUTE_NAME
+ ",N'" + kanbanPartBoxInfo.SourceZoneNo + "'" ///SOURCE_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.SourceWmNo + "'" ///SOURCE_WM_NO
+ ",N'" + kanbanPartBoxInfo.TargetZoneNo + "'" ///TARGET_ZONE_NO
+ ",N'" + kanbanPartBoxInfo.TargetWmNo + "'" ///TARGET_WM_NO
+ ",NULL" ///PICK_UP_FINISH_TIME
+ ",NULL" ///PICK_UP_USER
+ ",NULL" ///DELIVERY_FINISH_TIME
+ ",NULL" ///DELIVERY_USER
+ ",NULL" ///PRINT_CNT
+ ",NULL" ///PRINT_TIME
+ ",NULL" ///PRINT_USER
+ "," + (int)PullOrderStatusConstants.Released ///STATUS
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrder, sqlKanbanPullOrderValue));
List <KanbanCardInfo> kanbanCards = kanbanCardInfos.Where(d => d.PartBoxCode == partboxcode.PartBoxCode).ToList();
#region 根据看板卡信息获取所有零件物料拉动信息
var maintainInhouseLogisticStandardInfosparts = from a in maintainInhouseLogisticStandardInfos
join b in kanbanCards.Where(w => w.PartBoxCode == partboxcode.PartBoxCode).GroupBy(w => new { w.PartBoxCode, w.PartNo }).Select(w => new { w.Key.PartBoxCode, w.Key.PartNo })
on new { a.InhousePartClass, a.PartNo } equals new { InhousePartClass = b.PartBoxCode, b.PartNo }
select a;
#endregion
foreach (var kanbanCard in kanbanCards)
{
///看板卡号①=④、物料号④=⑤、物料名称⑤=⑥、物料数量⑧=⑨、包装型号⑨=⑩
///包装数量⑪默认为1，数据有效标记⑫为true
///若看板卡信息中指定了供应商代码⑥名称⑦，则看板拉动单明细中供应商代码⑦名称⑧从看板卡信息中获取
#region TT_MPM_KANBAN_PULL_ORDER_DETAIL
string sqlKanbanPullOrderDetailValue = "NEWID()" ///FID
+ ",N'" + pullOrderFid.ToString() + "'" ///ORDER_FID
+ ",N'" + orderCode + "'" ///ORDER_CODE
+ "," + (int)PullOrderStatusConstants.Released ///ORDER_STATUS
+ ",N'" + kanbanCard.CardNo + "'" ///CARD_NO
+ ",N'" + kanbanCard.PartNo + "'" ///PART_NO
+ ",N'" + kanbanCard.PartName + "'" ///PART_NAME
+ ",N'" + kanbanCard.SupplierCode + "'" ///SUPPLIER_CODE
+ ",N'" + kanbanCard.SupplierName + "'" ///SUPPLIER_NAME
+ "," + kanbanCard.PartQty.GetValueOrDefault() ///PART_QTY
+ ",N'" + kanbanCard.PackageCode + "'" ///PACKAGE_CODE
+ ",1" ///PACKAGE_QTY
+ ",1" ///VALID_FLAG
+ ",GETDATE()" ///CREATE_DATE
+ ",N'" + loginUser + "'"; ///CREATE_USER
#endregion
stringBuilder.AppendLine(string.Format(sqlKanbanPullOrderDetail, sqlKanbanPullOrderDetailValue));
}
#region 单据衔接
MaterialPullingOrderInfo mpOrder = new MaterialPullingOrderInfo();
mpOrder.OrderNo = orderCode;
mpOrder.PartBoxCode = kanbanPartBoxInfo.PartBoxCode; ///零件类2
mpOrder.PartBoxName = kanbanPartBoxInfo.PartBoxName; ///零件类名称3
mpOrder.Plant = string.Empty; ///工厂4
mpOrder.Workshop = string.Empty; ///车间5
mpOrder.AssemblyLine = string.Empty; ///流水线6
mpOrder.SupplierNum = string.Empty; ///供应商代码7
mpOrder.SupplierName = string.Empty; ///供应商名称
mpOrder.SourceZoneNo = kanbanPartBoxInfo.SourceZoneNo; ///来源存储区8
mpOrder.SourceWmNo = kanbanPartBoxInfo.SourceWmNo; ///来源仓库9
mpOrder.TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo; ///目标存储区10
mpOrder.TargetWmNo = kanbanPartBoxInfo.TargetWmNo; ///目标仓库11
mpOrder.TargetDock = string.Empty; ///道口12
mpOrder.PlanShippingTime = DateTime.Now.AddMinutes(kanbanPartBoxInfo.PickUpTime.GetValueOrDefault()); ///建议交货时间
mpOrder.PlanDeliveryTime = mpOrder.PlanShippingTime.GetValueOrDefault().AddMinutes(kanbanPartBoxInfo.DeliveryTime.GetValueOrDefault()); ///预计到厂时间14 = 建议交货时间 – 送货时间
mpOrder.PublishTime = DateTime.Now;
mpOrder.AsnFlag = false;
mpOrder.OrderType = 0;///TODO:对于看板拉动单应该的值，如果后续用到则需要定义枚举
mpOrder.MaterialPullingOrderDetailInfos = (from m in maintainInhouseLogisticStandardInfosparts
select new MaterialPullingOrderDetailInfo
{
OrderNo = orderCode, ///拉动单号1
SupplierNum = m.SupplierNum, ///供应商2
PartNo = m.PartNo, ///物料号3
PartCname = m.PartCname, ///物料号中文名称4
PartEname = m.PartEname, ///物料号英文名称5
//Uom = m.PartUnits,///计量单位6
PackageQty = m.InboundPackage.GetValueOrDefault(), ///入库单包装数量7
PackageModel = m.InboundPackageModel, ///入库包装编号8
RequirePackageQty = kanbanCards.Where(w => w.PartNo == m.PartNo).Count(), ///需求包装数量9
RequirePartQty = (kanbanCards.Where(w => w.PartNo == m.PartNo).Sum(w => w.PartQty) ?? 0), ///需求物料数量10
TargetZoneNo = kanbanPartBoxInfo.TargetZoneNo,
TargetWmNo = kanbanPartBoxInfo.TargetWmNo
}).ToList();
///执行单据衔接
stringBuilder.AppendLine(MaterialPullingCommonBLL.Handler(mpOrder, loginUser));
#endregion
}
#endregion
///数据保存时使用SQL拼接多条insert语句方式一次提交执行，执行失败需要同步返回至客户端
using (TransactionScope trans = new TransactionScope())
{
CommonDAL.ExecuteNonQueryBySql(stringBuilder.ToString());
trans.Complete();
}
return(true);
}
/// <summary>
/// 入库数据导入
/// </summary>
/// <param name="dataTable"></param>
/// <param name="fieldNames"></param>
/// <param name="loginUser"></param>
/// <returns></returns>
public bool ImportDataByExcel(DataTable dataTable, Dictionary <string, string> fieldNames, string loginUser)
{
List <ReceiveDetailInfo> receiveDetailInfos = CommonDAL.DatatableConvertToList <ReceiveDetailInfo>(dataTable).ToList();
if (receiveDetailInfos.Count == 0)
{
throw new Exception("MC:1x00000043");///数据格式不符合导入规范
}
List <MaintainPartsInfo> maintainPartsInfos = new MaintainPartsDAL().GetListForInterfaceDataSync(receiveDetailInfos.Select(d => d.PartNo).ToList());
///SUPPLIER_NUM.60、BOOK_KEEPER、RECEIVE_TYPE.70、TRAN_TIME.80、ORGANIZATION_FID.90、WM_NO.30、COST_CENTER.100、CONTRACT_NO.110合并
var groupReceives = receiveDetailInfos
.GroupBy(b => new { b.SupplierNum, b.BookKeeper, b.ReceiveType, b.TranTime, b.OrganizationFid, b.WmNo, b.CostCenter, b.ContractNo })
.Select(p => new { p.Key }).ToList();
string sql = string.Empty;
foreach (var groupReceive in groupReceives)
{
string receiveNo = new SeqDefineDAL().GetCurrentCode("RECEIVE_NO");
Guid receiveFid = Guid.NewGuid();
List <ReceiveDetailInfo> receiveDetailList = receiveDetailInfos.Where(d =>
d.SupplierNum == groupReceive.Key.SupplierNum &&
d.BookKeeper == groupReceive.Key.BookKeeper &&
d.ReceiveType == groupReceive.Key.ReceiveType &&
d.TranTime == groupReceive.Key.TranTime &&
d.OrganizationFid == groupReceive.Key.OrganizationFid &&
d.WmNo == groupReceive.Key.WmNo &&
d.CostCenter == groupReceive.Key.CostCenter &&
d.ContractNo == groupReceive.Key.ContractNo).ToList();
int rowNo = 1;
foreach (var receiveDetail in receiveDetailList)
{
///PART_NO.10、PART_ENAME.60、ZONE_NO.40、DLOC.50、ACTUAL_QTY.20
MaintainPartsInfo maintainPartsInfo = maintainPartsInfos.FirstOrDefault(d => d.PartNo == receiveDetail.PartNo);
if (maintainPartsInfo == null)
{
if (string.IsNullOrEmpty(receiveDetail.PartNo))
{
throw new Exception("MC:0x00000227");///器材编号不能为空
}
sql += "insert into [LES].[TM_BAS_MAINTAIN_PARTS] "
+ "(FID, PART_NO, PART_CNAME, PART_ENAME, PART_UNITS, PART_CLS, PART_GROUP, PURCHASE_UNIT_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(NEWID(), "
+ "N'" + receiveDetail.PartNo + "', "
+ "N'" + receiveDetail.PartCname + "', "
+ "N'" + receiveDetail.PartEname + "', "
+ "N'" + receiveDetail.PartUnits + "', "
+ "N'" + receiveDetail.PartCls + "', "
+ "N'" + receiveDetail.PartGroup + "', "
+ "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
+ "1, N'" + loginUser + "', GETDATE());";
}
else
{
///物料描述
receiveDetail.PartCname = maintainPartsInfo.PartCname;
///规格型号
receiveDetail.PartCls = maintainPartsInfo.PartCls;
///产地
// receiveDetail.OriginPlace = maintainPartsInfo.OriginPlace;
///计量单位
receiveDetail.MeasuringUnitNo = maintainPartsInfo.PartUnits;
///物料采购单价
//receiveDetail.PurchaseUnitPrice = maintainPartsInfo.PurchaseUnitPrice;
}
receiveDetail.PartPrice = receiveDetail.PurchaseUnitPrice.GetValueOrDefault() * receiveDetail.ActualQty.GetValueOrDefault();
sql += "insert into [LES].[TT_WMM_RECEIVE_DETAIL] "
+ "(FID, RECEIVE_FID, SUPPLIER_NUM, TARGET_WM, TARGET_ZONE, TARGET_DLOC, PART_NO, PART_CNAME, PART_ENAME, MEASURING_UNIT_NO, ACTUAL_QTY, TRAN_NO, RUNSHEET_NO, ROW_NO, ORIGIN_PLACE, PURCHASE_UNIT_PRICE, PART_PRICE, PART_CLS, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(NEWID(), "
+ "N'" + receiveFid + "', "
+ "N'" + groupReceive.Key.SupplierNum + "', "
+ "N'" + receiveDetail.WmNo + "', "
+ "N'" + receiveDetail.ZoneNo + "', "
+ "N'" + receiveDetail.Dloc + "', "
+ "N'" + receiveDetail.PartNo + "', "
+ "N'" + receiveDetail.PartCname + "', "
+ "N'" + receiveDetail.PartEname + "', "
+ "N'" + receiveDetail.PartUnits + "', "
+ "" + receiveDetail.ActualQty.GetValueOrDefault() + ", "
+ "N'" + receiveNo + "', "
+ "N'" + groupReceive.Key.ContractNo + "', "
+ "" + rowNo++ + ", "
+ "N'" + receiveDetail.OriginPlace + "', "
+ "" + receiveDetail.PurchaseUnitPrice.GetValueOrDefault() + ", "
+ "" + receiveDetail.PartPrice.GetValueOrDefault() + ", "
+ "N'" + receiveDetail.PartCls + "', "
+ "1, N'" + loginUser + "', GETDATE());";
}
///物料价格合计
decimal sumOfPrice = receiveDetailList.Sum(d => d.PartPrice.GetValueOrDefault());
///物料合计数量
decimal sumPartQty = receiveDetailList.Sum(d => d.ActualQty.GetValueOrDefault());
if (!DateTime.TryParse(groupReceive.Key.TranTime.ToString("yyyy-MM-dd HH:mm:ss"), out DateTime tranTime))
{
throw new Exception("MC:0x00000328");///入库时间不能为空
}
sql += "insert into [LES].[TT_WMM_RECEIVE] "
+ "(FID, RECEIVE_NO, SUPPLIER_NUM, WM_NO, RECEIVE_TYPE, TRAN_TIME, BOOK_KEEPER, STATUS, RUNSHEET_NO, ORGANIZATION_FID, COST_CENTER, SUM_PART_QTY, SUM_OF_PRICE, VALID_FLAG, CREATE_USER, CREATE_DATE) values "
+ "(N'" + receiveFid + "', "
+ "N'" + receiveNo + "', "
+ "N'" + groupReceive.Key.SupplierNum + "', "
+ "N'" + groupReceive.Key.WmNo + "', "
+ "" + groupReceive.Key.ReceiveType + ", "
+ "N'" + tranTime.ToString("yyyy-MM-dd HH:mm:ss") + "', "
+ "N'" + groupReceive.Key.BookKeeper + "', "
+ "" + (int)WmmOrderStatusConstants.Created + ", "
+ "N'" + groupReceive.Key.ContractNo + "', "
+ "N'" + groupReceive.Key.OrganizationFid + "', "
+ "N'" + groupReceive.Key.CostCenter + "', "
+ "" + sumPartQty + ", "
+ "" + sumOfPrice + ", "
+ "1, N'" + loginUser + "', GETDATE());";
}
if (string.IsNullOrEmpty(sql))
{
return(false);
}
return(CommonDAL.ExecuteNonQueryBySql(sql));
}
