public List<MonthlyReportObject> GetMonthlyReportObjects(Warehouse wh, Dealer dealer, int month, int year)
{
List<MonthlyReportObject> res = new List<MonthlyReportObject>();
string dbCode;
int bgMonth = month - 1, bgYear = year;
if (bgMonth == 0) { bgMonth = 12; bgYear--; }
// for transaction histories
DateTime dtTHBegin = new DateTime(year, month, 1);
DateTime dtTHEnd = dtTHBegin.AddMonths(1);
if (wh == null)
{
dbCode = dealer.DatabaseCode;
res.AddRange(dealer.ActiveWarehouses.Where(w => w.Type == WarehouseType.Part).Select(w => new MonthlyReportObject
{
PlaceCode = w.Code,
PlaceName = w.Address,
PlaceType = "W",
PartList = dcObj.PartInfos.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == w.WarehouseId).Count() > 0 && pi.PartType == PartType.Part)
.Join(dcObj.Parts.Where(p => p.DatabaseCode == dbCode), pi => pi.PartCode, p => p.PartCode,
(pi, p) => new MonthlyReportPart
//.Select(ps => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Part,
EnglishName = p.EnglishName,
VietnamName = p.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == w.WarehouseId).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == w.WarehouseId).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.ActualCost),
}),
AccList = dcObj.PartInfos.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == w.WarehouseId).Count() > 0 && pi.PartType == PartType.Accessory)
.Select(pi => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Accessory,
EnglishName = pi.Accessory.EnglishName,
VietnamName = pi.Accessory.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == w.WarehouseId).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == w.WarehouseId).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == w.WarehouseId).Sum(th => th.ActualCost),
}),
}).ToList());
res.AddRange(dealer.Dealers.Select(d => new MonthlyReportObject
{
PlaceCode = d.DealerCode,
PlaceName = d.DealerName,
PlaceType = "D",
PartList = dcObj.PartInfos.Where(pi => pi.DealerCode == d.DealerCode && pi.PartSafeties.Count > 0 && pi.PartType == PartType.Part)
.Join(dcObj.Parts.Where(p => p.DatabaseCode == dbCode), pi => pi.PartCode, p => p.PartCode,
(pi, p) => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Part,
EnglishName = p.EnglishName,
VietnamName = p.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == null && iv.DealerCode == d.DealerCode).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == null && iv.DealerCode == d.DealerCode).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == d.DealerCode).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == d.DealerCode).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == d.DealerCode).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == d.DealerCode).Sum(th => th.ActualCost),
}),
AccList = dcObj.PartInfos.Where(pi => pi.DealerCode == d.DealerCode && pi.PartSafeties.Count > 0 && pi.PartType == PartType.Accessory)
.Select(pi => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Accessory,
EnglishName = pi.Accessory.EnglishName,
VietnamName = pi.Accessory.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == null && iv.DealerCode == d.DealerCode).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == null && iv.DealerCode == d.DealerCode).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == d.DealerCode).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == d.DealerCode).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == d.DealerCode).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == d.DealerCode).Sum(th => th.ActualCost),
}),
}).ToList());
}
else
{
dbCode = wh.Dealer.DatabaseCode;
res.Add(
new MonthlyReportObject
{
PlaceCode = wh.Code,
PlaceName = wh.Address,
PlaceType = "W",
PartList = dcObj.PartInfos.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == wh.WarehouseId).Count() > 0 && pi.PartType == PartType.Part)
.Join(dcObj.Parts.Where(p => p.DatabaseCode == dbCode), pi => pi.PartCode, p => p.PartCode,
(pi, p) => new MonthlyReportPart
//.Select(ps => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Part,
EnglishName = p.EnglishName,
VietnamName = p.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == wh.WarehouseId).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == wh.WarehouseId).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
}),
AccList = dcObj.PartInfos.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == wh.WarehouseId).Count() > 0 && pi.PartType == PartType.Accessory)
.Select(pi => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Accessory,
EnglishName = pi.Accessory.EnglishName,
VietnamName = pi.Accessory.VietnamName,
BeginQuantity = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == wh.WarehouseId).Quantity,
Balance = dcObj.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfoId == pi.PartInfoId && iv.Month == month && iv.Year == year && iv.WarehouseId == wh.WarehouseId).Quantity,
InQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
InAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
OutAmount = dcObj.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfoId == pi.PartInfoId && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
}),
}
);
}
res.ForEach(obj =>
{
obj.BeginQuantity = obj.PartList.Sum(p => p.BeginQuantity) + obj.AccList.Sum(a => a.BeginQuantity);
obj.InQuantity = obj.PartList.Sum(p => p.InQuantity) + obj.AccList.Sum(a => a.InQuantity);
obj.InAmount = obj.PartList.Sum(p => p.InAmount) + obj.AccList.Sum(a => a.InAmount);
obj.OutQuantity = obj.PartList.Sum(p => p.OutQuantity) + obj.AccList.Sum(a => a.OutQuantity);
obj.OutAmount = obj.PartList.Sum(p => p.OutAmount) + obj.AccList.Sum(a => a.OutAmount);
obj.Balance = obj.PartList.Sum(p => p.Balance) + obj.AccList.Sum(a => a.Balance);
});
return res;
}
public List<MonthlyReportPart> GetMonthlyReportItems(Warehouse wh, Dealer dealer, int month, int year)
{
string dbCode;
//IQueryable<PartInfo> pis;
IQueryable<MonthlyReportPartInfo> pisP;
IQueryable<MonthlyReportPartInfo> pisA;
List<MonthlyReportPart> res = new List<MonthlyReportPart>();
int bgMonth = month - 1, bgYear = year;
if (bgMonth == 0) { bgMonth = 12; bgYear--; }
// for transaction histories
DateTime dtTHBegin = new DateTime(year, month, 1);
DateTime dtTHEnd = dtTHBegin.AddMonths(1);
#region get parts list
if (wh == null)
{
if (dealer == null) throw new Exception("Invalid dealer!");
//pis = dc.PartInfos.Where(pi => dealers.Contains(pi.DealerCode) || pi.DealerCode == dealer.DealerCode);
pisP = dc.PartInfos.Where(pi => pi.PartType == PartType.Part)
.Where(pi => pi.Dealer.ParentCode == dealer.DealerCode || pi.DealerCode == dealer.DealerCode)
.GroupBy(pi => pi.PartCode).Select(g => new MonthlyReportPartInfo { PartCode = g.Key });
pisA = dc.PartInfos.Where(pi => pi.PartType == PartType.Accessory)
.Where(pi => pi.Dealer.ParentCode == dealer.DealerCode || pi.DealerCode == dealer.DealerCode)
.Select(g => new MonthlyReportPartInfo { PartCode = g.PartCode, EnglishName = g.Accessory.EnglishName, VietnamName = g.Accessory.VietnamName });
dbCode = dealer.DatabaseCode;
}
else
{
//pis = dc.PartInfos.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == wh.WarehouseId).Count() > 0);
pisP = dc.PartInfos.Where(pi => pi.PartType == PartType.Part)
.Where(pi => pi.PartSafeties.Count(ps => ps.WarehouseId == wh.WarehouseId) > 0)
.GroupBy(pi => pi.PartCode).Select(g => new MonthlyReportPartInfo { PartCode = g.Key });
pisA = dc.PartInfos.Where(pi => pi.PartType == PartType.Accessory)
.Where(pi => pi.PartSafeties.Where(ps => ps.WarehouseId == wh.WarehouseId).Count() > 0)
.Select(g => new MonthlyReportPartInfo { PartCode = g.PartCode, EnglishName = g.Accessory.EnglishName, VietnamName = g.Accessory.VietnamName });
dbCode = wh.Dealer.DatabaseCode;
}
#endregion
//var pisP = pis.Where(pi => pi.PartType == PartType.Part);
//var pisA = pis.Where(pi => pi.PartType == PartType.Accessory);
if (wh == null)
{
#region Part
res = pisP.Join(dc.Parts.Where(p => p.DatabaseCode == dbCode), pi => pi.PartCode, p => p.PartCode,
(pi, p) => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Part,
EnglishName = p.EnglishName,
VietnamName = p.VietnamName,
// warehouses
ByWarehouses = dc.Warehouses.Where(w => (w.Status != WarehouseStatus.Deleted || w.Status == null) && w.DealerCode == dealer.DealerCode && w.Type == WarehouseType.Part
&& w.PartSafeties.Where(ps => ps.PartInfo.PartCode == pi.PartCode).Count() > 0).Select(pl => new MonthlyReportItem
{
PlaceCode = pl.Code,
PlaceName = pl.Address,
PlaceType = "W",
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == pl.WarehouseId).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == pl.WarehouseId).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.ActualCost),
}),
// sub dealers
ByDealers = dc.Dealers.Where(d => d.ParentCode == dealer.DealerCode
&& d.PartInfos.Where(dp => dp.PartCode == pi.PartCode).Count() > 0
).Select(pl => new MonthlyReportItem
{
PlaceCode = pl.DealerCode,
PlaceName = pl.DealerName,
PlaceType = "D",
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == null && iv.DealerCode == pl.DealerCode).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == null && iv.DealerCode == pl.DealerCode).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == pl.DealerCode).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == pl.DealerCode).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == pl.DealerCode).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == pl.DealerCode).Sum(th => th.ActualCost),
}),
}).ToList<MonthlyReportPart>();
#endregion
#region Accsessory
res.AddRange(pisA.Select(pi => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Accessory,
EnglishName = pi.EnglishName,
VietnamName = pi.VietnamName,
ByWarehouses = dc.Warehouses.Where(w => (w.Status != WarehouseStatus.Deleted || w.Status == null) && w.DealerCode == dealer.DealerCode && w.Type == WarehouseType.Part
&& w.PartSafeties.Where(ps => ps.PartInfo.PartCode == pi.PartCode).Count() > 0
).Select(pl => new MonthlyReportItem
{
PlaceCode = pl.Code,
PlaceName = pl.Address,
PlaceType = "W",
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == pl.WarehouseId).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == pl.WarehouseId).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == pl.WarehouseId).Sum(th => th.ActualCost),
}),
ByDealers = dc.Dealers.Where(d => d.ParentCode == dealer.DealerCode
&& d.PartInfos.Where(dp => dp.PartCode == pi.PartCode).Count() > 0
).Select(pl => new MonthlyReportItem
{
PlaceCode = pl.DealerCode,
PlaceName = pl.DealerName,
PlaceType = "D",
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == null && iv.DealerCode == pl.DealerCode).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == null && iv.DealerCode == pl.DealerCode).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == pl.DealerCode).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.DealerCode == pl.DealerCode).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == pl.DealerCode).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.DealerCode == pl.DealerCode).Sum(th => th.ActualCost),
}),
}).ToList<MonthlyReportPart>()
);
#endregion
}
else
{
#region Part
res = pisP.Join(dc.Parts.Where(p => p.DatabaseCode == dbCode), pi => pi.PartCode, p => p.PartCode,
(pi, p) => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Part,
EnglishName = p.EnglishName,
VietnamName = p.VietnamName,
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == wh.WarehouseId).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Part && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == wh.WarehouseId).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Part && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
}).ToList<MonthlyReportPart>();
#endregion
#region Accsessory
res.AddRange(pisA.Select(pi => new MonthlyReportPart
{
PartCode = pi.PartCode,
PartType = PartType.Accessory,
EnglishName = pi.EnglishName,
VietnamName = pi.VietnamName,
BeginQuantity = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == bgMonth && iv.Year == bgYear && iv.WarehouseId == wh.WarehouseId).Quantity,
Balance = dc.Inventories.SingleOrDefault(iv => iv.PartInfo.PartType == PartType.Accessory && iv.PartInfo.PartCode == pi.PartCode && iv.Month == month && iv.Year == year && iv.WarehouseId == wh.WarehouseId).Quantity,
InQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
InAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity > 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
OutQuantity = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.Quantity),
OutAmount = dc.TransactionHistories.Where(th => th.PartInfo.PartType == PartType.Accessory && th.PartInfo.PartCode == pi.PartCode && th.TransactionDate >= dtTHBegin && th.TransactionDate < dtTHEnd && th.Quantity < 0 && th.WarehouseId == wh.WarehouseId).Sum(th => th.ActualCost),
}).ToList<MonthlyReportPart>()
);
#endregion
}
// summary part info
if (wh == null)
{
res.ForEach(item =>
{
item.BeginQuantity = item.ByWarehouses.Sum(i => i.BeginQuantity) + item.ByDealers.Sum(i => i.BeginQuantity);
item.InAmount = item.ByWarehouses.Sum(i => i.InAmount) + item.ByDealers.Sum(i => i.InAmount);
item.InQuantity = item.ByWarehouses.Sum(i => i.InQuantity) + item.ByDealers.Sum(i => i.InQuantity);
item.OutAmount = item.ByWarehouses.Sum(i => i.OutAmount) + item.ByDealers.Sum(i => i.OutAmount);
item.OutQuantity = item.ByWarehouses.Sum(i => i.OutQuantity) + item.ByDealers.Sum(i => i.OutQuantity);
item.Balance = item.ByWarehouses.Sum(i => i.Balance) + item.ByDealers.Sum(i => i.Balance);
});
}
return res;
}
private void detach_Dealers(Dealer entity)
{
this.SendPropertyChanging();
entity.Contact = null;
}
private void attach_Dealers(Dealer entity)
{
this.SendPropertyChanging();
entity.Contact = this;
}
private void detach_Dealers(Dealer entity)
{
this.SendPropertyChanging("Dealers");
entity.Dealer1 = null;
}
private void attach_Dealers(Dealer entity)
{
this.SendPropertyChanging("Dealers");
entity.Dealer1 = this;
}