public int ConfirmLockState(WMS_BN_LockMaterial info, out string msg)
{
msg = "";
db.UseTransaction(true);
var rowsAffected = 0;
rowsAffected = db.Update <WMS_BN_LockMaterial>("WMS_BN_LockMaterial", info).AutoMap(x => x.ID).Where(x => x.ID).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
var sql = String.Format(@"select InventoryCode from SYS_Part where InventoryCode = '{0}'", info.InventoryCode);
sql = String.Format(@"select * from WMS_BN_RealStock where InventoryCode = '{0}' and WarehouseCode='{1}'", info.InventoryCode, info.WarehouseCode);
WMS_BN_RealStock product = db.Sql(sql).QuerySingle <WMS_BN_RealStock>();
if (product == null)
{
msg = "该仓库物料不存在！";
return(0);
}
if (info.LockState == 1)
{
product.RealStock = product.RealStock - info.LockQuantity;
product.LockStock = product.LockStock + info.LockQuantity;
}
else if (info.LockState == 2)
{
product.RealStock = product.RealStock + info.LockQuantity;
product.LockStock = product.LockStock - info.LockQuantity;
}
product.ModifyTime = DateTime.Now;
rowsAffected = db.Update <WMS_BN_RealStock>("WMS_BN_RealStock", product).AutoMap(x => x.ID).Where(x => x.ID).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
db.Commit();
return(rowsAffected);
}
public int AuditBillCode(string BillCode, out string msg)
{
msg = string.Empty;
//var rowsAffected = 0;
string sql = String.Format(@"
SELECT t1.*,
t1.QualifiedQuntity,
t3.WarehouseID,
t3.WarehouseName,
t2.CheckQuantity
,t4.QuantityUnit
,t4.Model
, t4.InventoryCode
,t4.InventoryName
,t3.WorkshopID
,t3.WorkshopName
FROM dbo.QMS_ProcessInspection t1
LEFT JOIN MES_ProcessInspectionRequest t2
ON t1.PBillCode = t2.BillCode
AND t1.IsEnable = 1
AND t2.IsEnable = 1
AND t2.BillState = 2
LEFT JOIN dbo.MES_BN_ProductProcessRoute t3
ON t2.ProductProcessRouteID = t3.ID
AND t3.IsEnable = 1
inner JOIN dbo.SYS_Part t4 ON t1.PartCode=t4.PartCode
AND t4.IsEnable=1 where t1.BillCode='{0}'" , BillCode);
dynamic stateList = db.Sql(sql).QueryMany <dynamic>();
if (stateList[0].BillState == 2)
{
msg = "单据已审核";
return(0);
}
else
{
sql = string.Format(@"update QMS_ProcessInspection set BillState=2 where BillCode='{0}'", BillCode);
//string insertSql=string.Format(@"")
using (db.UseTransaction(true))
{
int countMain = db.Sql(sql).Execute();
string documentNo = "";
documentNo = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "ZXRK", "", "");
//billstate = 2;
#region WMS_BN_BillMain表
string sqlA = string.Format(@"
INSERT INTO dbo.WMS_BN_BillMain
(
ID,
BillCode,
BillType,
ContractCode,
ProjectName,
SalesmanCode,
Salesman,
DepartmentID,
DepartmentName,
SupplierCode,
SupplierName,
WarehouseCode,
WarehouseName,
PickPersonCode,
PickPerson,
ApproveState,
ApprovePerson,
ApproveDate,
ApproveRemark,
Remark,
CreatePerson,
CreateTime,
ModifyPerson,
ModifyTime,
IsEnable
)
VALUES
( {0}, -- ID - int
'{1}', -- BillCode - varchar(50)
{2}, -- BillType - int
'{3}', -- ContractCode - varchar(50)
'{4}', -- ProjectName - varchar(50)
'', -- SalesmanCode - varchar(50)
'', -- Salesman - varchar(50)
'', -- DepartmentID - varchar(50)
'', -- DepartmentName - varchar(50)
'', -- SupplierCode - varchar(50)
'', -- SupplierName - varchar(50)
'{5}', -- WarehouseCode - varchar(50)
'{6}', -- WarehouseName - varchar(50)
'', -- PickPersonCode - varchar(50)
'', -- PickPerson - varchar(50)
'1', -- ApproveState - varchar(50)
'', -- ApprovePerson - varchar(50)
GETDATE(), -- ApproveDate - datetime
'', -- ApproveRemark - varchar(500)
'', -- Remark - varchar(500)
'{7}', -- CreatePerson - varchar(50)
GETDATE(), -- CreateTime - datetime
'{7}', -- ModifyPerson - varchar(50)
GETDATE(), -- ModifyTime - datetime
1 -- IsEnable - int
)
", Convert.ToInt32(new WMS_BN_BillMainService().GetNewKey("ID", "Maxplus")), documentNo, 8, stateList[0].ContractCode,
stateList[0].ProjectName, stateList[0].WarehouseID, stateList[0].WarehouseName, MmsHelper.GetUserName());
#endregion
int countA = db.Sql(sqlA).Execute();
#region WMS_BN_BillDetail表
string sqlB = string.Format(@"
INSERT INTO dbo.WMS_BN_BillDetail
(
BillCode,
OrderBillCode,
InventoryCode,
InventoryName,
Specs,
Unit,
MateNum,
ConfirmNum,
UnitPrice,
TotalPrice,
PackageCode,
BatchCode,
PBillCode,
AccountabilityCode,
Remark,
CreatePerson,
CreateTime,
ModifyPerson,
ModifyTime,
IsEnable
)
VALUES
( '{0}', -- BillCode - varchar(50)
'', -- OrderBillCode - varchar(50)
N'{1}', -- InventoryCode - nvarchar(50)
N'{2}', -- InventoryName - nvarchar(50)
'{3}', -- Specs - varchar(50)
N'{4}', -- Unit - nvarchar(10)
{5}, -- MateNum - float
{6}, -- ConfirmNum - float
0.0, -- UnitPrice - float
0.0, -- TotalPrice - float
'', -- PackageCode - varchar(50)
'', -- BatchCode - varchar(50)
'', -- PBillCode - varchar(50)
'', -- AccountabilityCode - varchar(50)
N'', -- Remark - nvarchar(500)
N'{7}', -- CreatePerson - nvarchar(50)
GETDATE(), -- CreateTime - datetime
N'{7}', -- ModifyPerson - nvarchar(50)
GETDATE(), -- ModifyTime - datetime
1 -- IsEnable - int
)
", documentNo, stateList[0].InventoryCode, stateList[0].InventoryName, stateList[0].Model,
stateList[0].Unit, stateList[0].QualifiedQuntity, stateList[0].QualifiedQuntity, MmsHelper.GetUserName());
#endregion
int countB = db.Sql(sqlB).Execute();
int countC = 0;
bool aaa = true;
bool ccc = true;
//更新库存表
//库存表
List <WMS_BN_RealStock> realStockData = db.Sql("SELECT * FROM WMS_BN_RealStock WHERE IsEnable = 1").QueryMany <WMS_BN_RealStock>();
var realStocks = realStockData.Where(s =>
{
bool a = s.WarehouseCode == null ? false : s.WarehouseCode.Equals(stateList[0].WarehouseID);
bool b = s.InventoryCode == null ? false : s.InventoryCode.Equals(stateList[0].InventoryCode);
bool c = s.BatchCode == null ? false : s.BatchCode.Equals(stateList[0].BatchCode);
return(a && b && c);
}).ToList();
string updateStockSal = "";
if (stateList[0].WarehouseName == stateList[0].WorkshopName)
{
#region
//没有库存则新增
if (realStocks.Count <= 0)
{
WMS_BN_RealStock realStockModel = new WMS_BN_RealStock()
{
InventoryCode = stateList[0].InventoryCode,
InventoryName = stateList[0].InventoryName,
Model = stateList[0].Model,
RealStock = (double)stateList[0].QualifiedQuntity,
TotalStock = (double)stateList[0].QualifiedQuntity,
WarehouseCode = stateList[0].WarehouseID,
WarehouseName = stateList[0].WarehouseName,
BatchCode = stateList[0].BatchCode,
Unit = stateList[0].Unit,
CreatePerson = MmsHelper.GetUserName(),
CreateTime = DateTime.Now,
ModifyPerson = MmsHelper.GetUserName(),
ModifyTime = DateTime.Now,
IsEnable = 1
};
countC = db.Insert("WMS_BN_RealStock", realStockModel).AutoMap(x => x.ID).Execute();
updateStockSal += String.Format(@" update WMS_BN_BillMain set ApproveState=2 where BillCode = '{0}'", documentNo);
}
//有则更新数量
else if (realStocks.Count.Equals(1))
{
updateStockSal = string.Format("UPDATE WMS_BN_RealStock SET RealStock = {0},TotalStock = {1},ModifyPerson = '{2}',ModifyTime = '{3}' WHERE ID = {4} ;\r\n",
realStocks[0].RealStock + stateList[0].QualifiedQuntity, realStocks[0].TotalStock + stateList[0].QualifiedQuntity, MmsHelper.GetUserName(), DateTime.Now, realStocks[0].ID);
updateStockSal += String.Format(@" update WMS_BN_BillMain set ApproveState=2 where BillCode = '{0}'", documentNo);
}
else
{
return(0);
msg = @"仓库物料种类繁多，可惜不知取哪一种！";
}
#endregion
ccc = db.Sql(updateStockSal).Execute() > 0;
}
if (aaa && countMain > 0 && countA > 0 && countB > 0 && ccc && countC > 0)
{
db.Commit();
msg = "审核单据成功";
return(1);
}
else
{
db.Rollback();
msg = "审核单据失败，请确认数据";
return(1);
}
}
}
}
/// <summary>
/// 物料扫码出库
/// </summary>
/// <returns></returns>
public ResultModel MaterialOutput(dynamic data)
{
try
{
DateTime newDT = DateTime.Now;
string documentNo = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "QTCK", "", "");
WMS_BN_BillMain bMainModel = new WMS_BN_BillMain()
{
ID = Convert.ToInt32(new WMS_BN_BillMainService().GetNewKey("ID", "Maxplus")),
BillCode = documentNo,
BillType = 7,
IsEnable = 1,
ContractCode = data["mainData"]["ContractCode"],
ProjectName = data["mainData"]["ProjectName"],
WarehouseCode = data["mainData"]["WarehouseCode"],
WarehouseName = data["mainData"]["WarehouseName"],
ApproveState = data["mainData"]["ApproveState"],
ApprovePerson = data["mainData"]["UserName"],
ApproveDate = newDT,
CreatePerson = data["userData"]["UserName"],
CreateTime = newDT,
ModifyPerson = data["userData"]["UserName"],
ModifyTime = newDT
};
WMS_BN_BillDetail bDetailModel = new WMS_BN_BillDetail()
{
BillCode = documentNo,
IsEnable = 1,
InventoryCode = data["detailData"]["InventoryCode"],
InventoryName = data["detailData"]["InventoryName"],
Specs = data["detailData"]["Specs"],
Unit = data["detailData"]["Unit"],
MateNum = data["detailData"]["MateNum"] == null ? null : Convert.ToDouble(data["detailData"]["MateNum"]),
ConfirmNum = data["detailData"]["ConfirmNum"] == null ? null : Convert.ToDouble(data["detailData"]["ConfirmNum"]),
CreatePerson = data["userData"]["UserName"],
CreateTime = newDT,
ModifyPerson = data["userData"]["UserName"],
ModifyTime = newDT,
PBillCode = data["detailData"]["PBillCode"]
};
db.UseTransaction(true);
int tempA = db.Sql(GetInsertSQL(bMainModel)).Execute();
var realStocks = db.Sql(string.Format(@"SELECT * FROM dbo.WMS_BN_RealStock WHERE IsEnable = 1 AND WarehouseCode = '{0}' AND InventoryCode = '{1}' ORDER BY BatchCode", bMainModel.WarehouseCode, bDetailModel.InventoryCode)).QueryMany <WMS_BN_RealStock>();
WMS_BN_RealStock realStock = null;
if (realStocks.Count <= 0)
{
throw new Exception(@"仓库没有物料！");
}
else
{
var num = Convert.ToInt32(bDetailModel.ConfirmNum ?? 1);
foreach (var item in realStocks)
{
if (item.RealStock == null)
{
throw new Exception(@"仓库物料数量异常！");
}
else
{
if ((item.RealStock ?? 0) < num)
{
}
else
{
realStock = item;
bDetailModel.BatchCode = item.BatchCode;
break;
}
}
}
if (string.IsNullOrEmpty(bDetailModel.BatchCode))
{
throw new Exception(@"仓库物料数量不够，无法出库！");
}
}
int tempB = db.Sql(GetInsertSQL(bDetailModel)).Execute();
string sql = GetUpdateSQL(nameof(WMS_BN_RealStock), new KeyValuePair <string, object>("ID", realStock.ID), new
{
RealStock = realStock.RealStock - bDetailModel.ConfirmNum,
TotalStock = realStock.TotalStock - bDetailModel.ConfirmNum,
ModifyPerson = bDetailModel.ModifyPerson,
ModifyTime = bDetailModel.ModifyTime,
});
int tempC = db.Sql(sql).Execute();
bool result = tempA > 0 && tempB > 0 && tempC > 0;
if (result)
{
db.Commit();
}
else
{
db.Rollback();
}
return(new ResultModel()
{
Result = result,
Msg = result ? @"成功！" : "失败！"
});
}
catch (Exception ex)
{
db.Rollback();
return(new ResultModel()
{
Result = false,
Msg = ex.Message
});
}
}
public dynamic NewAuditBillCode(string billCode)
{
try
{
if (string.IsNullOrEmpty(billCode))
{
throw new Exception(@"参数错误！");
}
string sqlA = string.Format(@"SELECT * FROM dbo.MES_MaterialReturnMain WHERE IsEnable = 1 AND BillCode = '{0}';", billCode);
List <MES_MaterialReturnMain> listA = db.Sql(sqlA).QueryMany <MES_MaterialReturnMain>();
DateTime newDT = DateTime.Now;
if (listA.Count <= 0)
{
throw new Exception(@"请保存数据后审核！");
}
if (listA[0].BillState != null && listA[0].BillState.Equals(2))
{
throw new Exception(@"单据已审核！");
}
int billType = 0;
using (var dbA = Db.Context("Sys"))
{
var sysCodeModel = dbA.Sql(@"SELECT * FROM HBHC_Sys.dbo.sys_code WHERE Text='退料入库';").QuerySingle <sys_code>();
if (sysCodeModel == null)
{
throw new Exception(@"字典表查不到【退料入库】数据信息！");
}
else
{
billType = Convert.ToInt32(sysCodeModel.Value);
}
}
using (var dbB = Db.Context("Mms"))
{
try
{
dbB.UseTransaction(true);
string sqlB = string.Format(@"UPDATE dbo.MES_MaterialReturnMain SET BillState = 2 WHERE IsEnable = 1 AND BillCode = '{0}';", billCode);
int tempA = dbB.Sql(sqlB).Execute();
var dno = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", "TLRK", "", "");
string sqlC = WinFormClientService.GetInsertSQL(new WMS_BN_BillMain()
{
ID = dbB.Sql("SELECT ISNULL(MAX(ID),1)+1 FROM dbo.WMS_BN_BillMain;").QuerySingle <int>(),
BillCode = dno,
BillType = billType,
ContractCode = listA[0].ContractCode,
ProjectName = listA[0].ProjectName,
DepartmentID = listA[0].DepartmentID == null ? null : listA[0].DepartmentID.ToString(),
DepartmentName = listA[0].DepartmentName,
WarehouseCode = listA[0].WarehouseCode,
WarehouseName = listA[0].WarehouseName,
ApproveState = "2",
Remark = "",
CreatePerson = MmsHelper.GetUserName(),
CreateTime = newDT,
ModifyPerson = MmsHelper.GetUserName(),
ModifyTime = newDT,
IsEnable = 1
});
int tempB = dbB.Sql(sqlC).Execute();
var listB = dbB.Sql(string.Format(@"SELECT * FROM dbo.MES_MaterialReturnDetail WHERE IsEnable = 1 AND MainID = {0};", listA[0].ID)).QueryMany <MES_MaterialReturnDetail>();
StringBuilder sb = new StringBuilder();
foreach (var item in listB)
{
WMS_BN_BillDetail bDetailModel = new WMS_BN_BillDetail()
{
BillCode = dno,
InventoryCode = item.InventoryCode,
InventoryName = item.InventoryName,
Specs = item.Model,
Unit = item.Unit,
MateNum = item.ReturnQuantity,
ConfirmNum = item.ReturnQuantity,
PBillCode = billCode,
Remark = "",
CreatePerson = MmsHelper.GetUserName(),
CreateTime = newDT,
ModifyPerson = MmsHelper.GetUserName(),
ModifyTime = newDT,
IsEnable = 1
};
var realStocks = db.Sql(string.Format(@"SELECT * FROM dbo.WMS_BN_RealStock WHERE IsEnable = 1 AND WarehouseCode = '{0}' AND InventoryCode = '{1}' ORDER BY BatchCode", listA[0].WarehouseCode, item.InventoryCode)).QueryMany <WMS_BN_RealStock>();
WMS_BN_RealStock realStock = null;
if (realStocks.Count <= 0)
{
throw new Exception(@"仓库没有物料！");
}
else
{
var num = Convert.ToInt32(bDetailModel.ConfirmNum);
foreach (var i in realStocks)
{
if (i.RealStock == null)
{
throw new Exception(@"仓库物料数量异常！");
}
else
{
if ((i.RealStock ?? 0) < num)
{
}
else
{
realStock = i;
bDetailModel.BatchCode = i.BatchCode;
break;
}
}
}
if (string.IsNullOrEmpty(bDetailModel.BatchCode))
{
throw new Exception(@"仓库物料数量不够，无法出库！");
}
}
sb.Append(WinFormClientService.GetInsertSQL(bDetailModel));
sb.Append(WinFormClientService.GetUpdateSQL(nameof(WMS_BN_RealStock), new KeyValuePair <string, object>("ID", realStock.ID), new
{
RealStock = realStock.RealStock - bDetailModel.ConfirmNum,
TotalStock = realStock.TotalStock - bDetailModel.ConfirmNum,
ModifyPerson = bDetailModel.ModifyPerson,
ModifyTime = bDetailModel.ModifyTime,
}));
}
string sqlD = sb.ToString();
int tempC = dbB.Sql(sqlD).Execute();
bool result = tempA > 0 && tempB > 0 && tempC > 0;
if (result)
{
dbB.Commit();
}
else
{
dbB.Rollback();
}
return(new ResultModel()
{
Result = result,
Msg = result ? @"审核成功！" : "审核失败！"
});
}
catch (Exception ex)
{
dbB.Rollback();
throw new Exception(ex.Message);
}
}
}
catch (Exception ex)
{
return(new ResultModel()
{
Result = false,
Msg = ex.Message
});
}
}
