public override void UpdateEntity(WhWarehouseMonthStockHistory history)
{
Context.Update("WhWarehouseMonthStockHistory", history).AutoMap(p => p.SysNo).Where(p => p.SysNo).Execute();
}
public override int InsertEntity(WhWarehouseMonthStockHistory history)
{
return(Context.Insert("WhWarehouseMonthStockHistory", history).AutoMap(p => p.SysNo).ExecuteReturnLastId <int>("SysNo"));
}
public abstract void UpdateEntity(WhWarehouseMonthStockHistory history);
public override void InsertOrUpdateStockHistory(int year, int month, int day, int warehouseSysNo)
{
string InWareGoodsSql = @" select PdProductStockInDetail.PdProductSysNo as ProductSysNo, SUM(PdProductStockInDetail.DoStorageQuantity) as TotalQuantity
from
PdProductStockIn inner join PdProductStockInDetail on PdProductStockIn.SysNo=PdProductStockInDetail.ProductStockInSysNo
where Status=50 and WarehouseSysNo=" + warehouseSysNo +
@" and PdProductStockIn.StorageTime<='" + year + "-" + month + "-" + day + @" 23:59:59'
group by PdProductStockInDetail.PdProductSysNo";
List <WarehouseMod> InWareList = Context.Sql(InWareGoodsSql).QueryMany <WarehouseMod>();
string SalesGoodsSql = @" select SoOrderItem.ProductSysNo ,sum(SoOrderItem.Quantity) as TotalQuantity from SoOrder inner join SoOrderItem on SoOrderItem.OrderSysNo=SoOrder.SysNo
where Status>=30 and SoOrder.DefaultWarehouseSysNo=" + warehouseSysNo +
@" and SoOrder.CreateDate<='" + year + "-" + month + "-" + day + @" 23:59:59'
group by SoOrderItem.ProductSysNo";
List <WarehouseMod> SalesList = Context.Sql(SalesGoodsSql).QueryMany <WarehouseMod>();
string RetGoodsSql = @" select RcReturnItem.ProductSysNo,sum(RcReturnItem.RmaQuantity) as TotalQuantity
from RcReturn inner join RcReturnItem on RcReturn.SysNo=RcReturnItem.ReturnSysNo inner join SoOrder on RcReturn.OrderSysNo=SoOrder.SysNo
where RcReturn.RmaType=20 and RcReturn.Status=50 and SoOrder.DefaultWarehouseSysNo=" + warehouseSysNo +
@" and RcReturn.CreateDate<='" + year + "-" + month + "-" + day + @" 23:59:59'
group by RcReturnItem.ProductSysNo";
List <WarehouseMod> RetList = Context.Sql(RetGoodsSql).QueryMany <WarehouseMod>();
///商品损耗列表
string LossGoodsSql = @"select sum(PdProductLossItem.Quantity) as TotalQuantity,PdProductLossItem.ProSysNo as ProductSysNo from PdProductLoss inner join PdProductLossItem on PdProductLoss.sysNo=PdProductLossItem.PSysNo
where PdProductLoss.ComeByWareSysNo=" + warehouseSysNo + @"
and PdProductLoss.CreateTime<='" + year + "-" + month + "-" + day + @" 23:59:59'
group by PdProductLossItem.ProSysNo";
List <WarehouseMod> LossList = Context.Sql(LossGoodsSql).QueryMany <WarehouseMod>();
List <Model.Procurement.WhWarehouseMonthStockHistory> haveList =
GetWhWarehouseMonthStockHistory(year, month, warehouseSysNo);
foreach (WarehouseMod mod in InWareList)
{
WhWarehouseMonthStockHistory history = haveList.Find(p => p.ProductSysNo == mod.ProductSysNo);
WarehouseMod inWareMod = InWareList.Find(p => p.ProductSysNo == mod.ProductSysNo);
WarehouseMod salesMod = SalesList.Find(p => p.ProductSysNo == mod.ProductSysNo);
WarehouseMod retMod = RetList.Find(p => p.ProductSysNo == mod.ProductSysNo);
WarehouseMod lossMod = LossList.Find(p => p.ProductSysNo == mod.ProductSysNo);
if (history == null)
{
WhWarehouseMonthStockHistory tempHistory = new WhWarehouseMonthStockHistory()
{
WarehouseSysNo = warehouseSysNo,
WhYear = year,
WhMonth = month,
InWareQuantity = (inWareMod == null ? 0 : inWareMod.TotalQuantity),
ProductSysNo = mod.ProductSysNo,
SalesQuantity = (salesMod == null ? 0 : salesMod.TotalQuantity),
RetQuantity = (retMod == null ? 0 : retMod.TotalQuantity),
TotalQuantity = (
(inWareMod == null ? 0 : inWareMod.TotalQuantity) +
(retMod == null ? 0 : retMod.TotalQuantity) -
(salesMod == null ? 0 : salesMod.TotalQuantity)
),
LossQuantity = (lossMod == null?0:lossMod.TotalQuantity)
};
InsertEntity(tempHistory);
}
else
{
history.InWareQuantity = (inWareMod == null ? 0 : inWareMod.TotalQuantity);
history.ProductSysNo = mod.ProductSysNo;
history.SalesQuantity = (salesMod == null ? 0 : salesMod.TotalQuantity);
history.RetQuantity = (retMod == null ? 0 : retMod.TotalQuantity);
history.TotalQuantity = (
(inWareMod == null ? 0 : inWareMod.TotalQuantity) +
(retMod == null ? 0 : retMod.TotalQuantity) -
(salesMod == null ? 0 : salesMod.TotalQuantity)
);
history.LossQuantity = (lossMod == null ? 0 : lossMod.TotalQuantity);
UpdateEntity(history);
}
}
}
public abstract int InsertEntity(WhWarehouseMonthStockHistory history);
