//自动生成出入库单
public int PostBuild(string billCode, out string msg)
{
msg = string.Empty;
var rowsAffected = 0;
string sql = String.Format(@" select MAX(t1.BillState) as BillState,t2.WarehouseCode,max(t2.WarehouseName) as WarehouseName,t2.InventoryCode,max(t2.InventoryName) as InventoryName,sum(t2.DValue) as DValue from WMS_BN_TakeStockMain as t1
inner join WMS_BN_TakeStockDetail as t2 on t1.BillCode=t2.BillCode where t1.BillCode='{0}'
group by t2.WarehouseCode,t2.Inventorycode", billCode);
var list = db.Sql(sql).QueryMany <dynamic>();
var res = list[1].DValue;
if (list[0].BillState == 2)
{
msg = "已锁定的数据不能重复提交！";
return(0);
}
db.UseTransaction(true);
foreach (var item in list)
{
res = item.DValue;
WMS_BN_BillMain master = new WMS_BN_BillMain();
if (item.DValue > 0)
{
master.BillType = 6;
master.WarehouseCode = item.WarehouseCode;
master.WarehouseName = item.WarehouseName;
master.Remark = "盘盈入库";
}
else if (item.DValue < 0)
{
master.BillType = 7;
master.WarehouseCode = item.WarehouseCode;
master.WarehouseName = item.WarehouseName;
master.Remark = "盘亏出库";
}
else
{
continue;
}
var sc = new sys_codeService().Getsys_codeByTypeAndID("BillType", Convert.ToInt16(master.BillType));
master.BillCode = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", sc.Description, "", "");
master.CreateTime = DateTime.Now;
master.CreatePerson = MmsHelper.GetUserName();
rowsAffected = db.Sql(@"insert into WMS_BN_BillMain (BillCode,BillType,WarehouseCode,WarehouseName,Remark,
CreatePerson,CreateTime) values(@0,@1,@2,@3,@4,@5,@6)", master.BillCode, master.BillType,
master.WarehouseCode, master.WarehouseName, master.Remark, master.CreatePerson, master.CreateTime).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
var detailList = (from p in list where p.WarehouseCode == item.WarehouseCode select p).ToList();
foreach (var it in detailList)
{
WMS_BN_BillDetail detail = new WMS_BN_BillDetail();
detail.PBillCode = billCode;
detail.BillCode = master.BillCode;
detail.InventoryCode = it.InventoryCode;
detail.InventoryName = it.InventoryName;
detail.MateNum = Math.Abs(item.DValue);
detail.ConfirmNum = Math.Abs(item.DValue);
rowsAffected = db.Insert <WMS_BN_BillDetail>("WMS_BN_BillDetail", detail)
.AutoMap(x => x.ID)
.Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
}
}
sql = string.Format(@"update WMS_BN_TakeStockMain set BillState='2' where BillCode='{0}'", billCode);
rowsAffected = db.Sql(sql).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
msg = "操作成功！";
db.Commit();
return(rowsAffected);
}
public int PostBuild(string billCode, out string msg)
{
msg = string.Empty;
int rowsAffected = 0;
db.UseTransaction(true);
string sql = string.Format(@"select a.BillCode,a.Remark, a.InWarehouseCode,a.InWarehouseName,a.OutWarehouseCode,a.OutWarehouseName,
b.InventoryCode,b.InventoryName,b.ConfirmNum,b.Unit from WMS_BN_TransfersMain a
left join WMS_BN_TransfersDetail b on a.BillCode=b.BillCode where a.IsEnable=1 and
b.IsEnable=1 and b.BillCode='{0}'", billCode);
var list = db.Sql(sql).QueryMany <dynamic>();
if (list.Count <= 0)
{
msg = "没有数据！";
return(0);
}
//foreach (var item in list)
//{
WMS_BN_BillMain master = new WMS_BN_BillMain();
var sc = new sys_codeService().Getsys_codeByTypeAndID("BillType", 7);
master.BillCode = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", sc.Description, "", "");
master.BillType = 7;
master.WarehouseCode = list[0].OutWarehouseCode;
master.WarehouseName = list[0].OutWarehouseName;
master.Remark = list[0].Remark;
master.CreateTime = DateTime.Now;
master.CreatePerson = MmsHelper.GetUserName();
master.ApproveState = "1";
rowsAffected = db.Sql(@"insert into WMS_BN_BillMain (ID,BillCode,BillType,WarehouseCode,WarehouseName,Remark,ApproveState,
CreatePerson,CreateTime) values(((select MAX(id)+1 from WMS_BN_BillMain)),@0,@1,@2,@3,@4,@5,@6,@7)", master.BillCode, master.BillType,
master.WarehouseCode, master.WarehouseName, master.Remark, master.ApproveState, master.CreatePerson, master.CreateTime).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
//var detailList = (from p in list where p.OutWarehouseID == item.OutWarehouseID select p).ToList();
//var detailList = (from p in list where p.OutWarehouseID == item.OutWarehouseID select p).ToList();
foreach (var it in list)
{
WMS_BN_BillDetail detail = new WMS_BN_BillDetail();
detail.PBillCode = billCode;
detail.BillCode = master.BillCode;
detail.InventoryCode = it.InventoryCode;
detail.InventoryName = it.InventoryName;
detail.MateNum = it.ConfirmNum;
detail.ConfirmNum = it.ConfirmNum;
rowsAffected = db.Insert <WMS_BN_BillDetail>("WMS_BN_BillDetail", detail)
.AutoMap(x => x.ID)
.Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
}
sc = new sys_codeService().Getsys_codeByTypeAndID("BillType", 6);
master.BillCode = MmsHelper.GetOrderNumber("WMS_BN_BillMain", "BillCode", sc.Description, "", "");
master.BillType = 6;
master.WarehouseCode = list[0].InWarehouseCode;
master.WarehouseName = list[0].InWarehouseName;
master.Remark = list[0].Remark;
master.CreateTime = DateTime.Now;
master.CreatePerson = MmsHelper.GetUserName();
master.ApproveState = "1";
rowsAffected = db.Sql(@"insert into WMS_BN_BillMain (ID,BillCode,BillType,WarehouseCode,WarehouseName,Remark,ApproveState,
CreatePerson,CreateTime) values(((select MAX(id)+1 from WMS_BN_BillMain)),@0,@1,@2,@3,@4,@5,@6,@7)", master.BillCode, master.BillType,
master.WarehouseCode, master.WarehouseName, master.Remark, master.ApproveState, master.CreatePerson, master.CreateTime).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
//detailList = (from p in list where p.InWarehouseID == item.InWarehouseID select p).ToList();
foreach (var it in list)
{
WMS_BN_BillDetail detail = new WMS_BN_BillDetail();
detail.PBillCode = billCode;
detail.BillCode = master.BillCode;
detail.InventoryCode = it.InventoryCode;
detail.InventoryName = it.InventoryName;
detail.MateNum = it.ConfirmNum;
detail.ConfirmNum = it.ConfirmNum;
rowsAffected = db.Insert <WMS_BN_BillDetail>("WMS_BN_BillDetail", detail)
.AutoMap(x => x.ID)
.Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
}
//}
sql = string.Format(@"update WMS_BN_TransfersMain set ApproveState='2',ApprovePerson='{0}',ApproveTime='{1}' where BillCode='{2}'", MmsHelper.GetUserName(), DateTime.Now, billCode);
rowsAffected = db.Sql(sql).Execute();
if (rowsAffected < 0)
{
db.Rollback();
return(rowsAffected);
}
msg = "操作成功！";
db.Commit();
return(rowsAffected);
}
/// <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
});
}
}
