public DataTable GetReportRevenueForShop(DateTime dateFrom, DateTime dateTo, string orderNumber, string UserLogin, bool isCreateTemplate) { using (var context = new VnrHrmDataContext()) { string status = string.Empty; DataTable table = GetSchemaReportVenueForShop(UserLogin); if (isCreateTemplate) { return table.ConfigTable(); } var orgServices = new Cat_OrgStructureServices(); var lstObjOrg = new List<object>(); lstObjOrg.Add(orderNumber); var lstOrgID = orgServices.GetData<Cat_OrgStructureEntity>(lstObjOrg, ConstantSql.hrm_cat_sp_get_OrgStructureByOrderNumber, UserLogin, ref status).Select(s => s.ID).ToList(); var shopServices = new Cat_ShopServices(); var lstObjShop = new List<object>(); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(null); lstObjShop.Add(1); lstObjShop.Add(int.MaxValue - 1); var lstShop = shopServices.GetData<Cat_ShopEntity>(lstObjShop, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList(); var lstShopID = new List<Guid>(); if (lstOrgID != null) { lstShop = lstShop.Where(s => lstOrgID.Contains(s.OrgStructureID.Value)).ToList(); } var kpiBonusService = new Cat_KPIBonusServices(); var lstObjKPIBonus = new List<object>(); lstObjKPIBonus.Add(null); lstObjKPIBonus.Add(null); lstObjKPIBonus.Add(1); lstObjKPIBonus.Add(int.MaxValue - 1); var lstKPIBonus = kpiBonusService.GetData<Cat_KPIBonusEntity>(lstObjKPIBonus, ConstantSql.hrm_cat_sp_get_KPIBonus, UserLogin, ref status).ToList(); var kpiBonusItemServices = new Cat_KPIBonusItemServices(); var lstObjKPIBonusItem = new List<object>(); lstObjKPIBonusItem.Add(null); lstObjKPIBonusItem.Add(null); lstObjKPIBonusItem.Add(1); lstObjKPIBonusItem.Add(int.MaxValue - 1); var lstKPIBonusItem = kpiBonusItemServices.GetData<Cat_KPIBonusItemEntity>(lstObjKPIBonusItem, ConstantSql.hrm_cat_sp_get_KPIBonusItem, UserLogin, ref status).ToList(); var revenueForRecordServices = new Sal_RevenueRecordService(); var lstObjRevenueRecord = new List<object>(); lstObjRevenueRecord.Add(null); lstObjRevenueRecord.Add(null); lstObjRevenueRecord.Add(1); lstObjRevenueRecord.Add(int.MaxValue - 1); var lstRevenueRecord = revenueForRecordServices.GetData<Sal_RevenueRecordEntity>(lstObjRevenueRecord, ConstantSql.hrm_sal_sp_get_RevenueRecord, UserLogin, ref status).Where(s => s.Month.Value.Month == dateFrom.Month && s.Month.Value.Year == dateFrom.Year && s.Month.Value.Month == dateFrom.Month && s.Month.Value.Year == dateFrom.Year).ToList(); var revenueForShopServices = new Sal_RevenueForShopServices(); var lstObjRevenueForShop = new List<object>(); lstObjRevenueForShop.Add(null); lstObjRevenueForShop.Add(null); lstObjRevenueForShop.Add(null); lstObjRevenueForShop.Add(null); lstObjRevenueForShop.Add(1); lstObjRevenueForShop.Add(int.MaxValue - 1); var lstRevenueForShop = revenueForRecordServices.GetData<Sal_RevenueForShopEntity>(lstObjRevenueForShop, ConstantSql.hrm_sal_sp_get_RevenueForShop, UserLogin, ref status).ToList(); List<object> listModel = new List<object>(); listModel.AddRange(new object[18]); listModel[16] = 1; listModel[17] = int.MaxValue - 1; List<Hre_ProfileEntity> listProfile = GetData<Hre_ProfileEntity>(listModel, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList(); List<ElementFormula> listElementFormula = new List<ElementFormula>(); foreach (var item in lstShop) { #region Lấy các phần tử là Enum Sal_RevenueForShopEntity RevenueForShopItem = new Sal_RevenueForShopEntity(); RevenueForShopItem = lstRevenueForShop.Where(m => m.ShopID == item.ID && m.KPIBonusID == lstKPIBonus.Where(t => t.IsTotalRevenue == true).FirstOrDefault().ID && m.DateFrom <= dateTo && m.DateTo >= dateFrom).FirstOrDefault(); if (RevenueForShopItem != null) { listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_TAGET_SHOP.ToString(), RevenueForShopItem.Target, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_ACTUAL_SHOP.ToString(), RevenueForShopItem.Actual, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_PRECENT_REVENUE.ToString(), RevenueForShopItem.Actual / RevenueForShopItem.Target, 0)); } else { listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_TAGET_SHOP.ToString(), 0, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_ACTUAL_SHOP.ToString(), 0, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_PRECENT_REVENUE.ToString(), 0, 0)); } listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SHOPMEMBER.ToString(), listProfile.Where(m => m.ShopID == item.ID).Count(), 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SL.ToString(), lstShop.Where(m => m.ID == item.ID).FirstOrDefault().NoShiftLeader, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_RANK.ToString(), lstShop.Where(m => m.ID == item.ID).FirstOrDefault().Rank, 0)); #endregion DataRow dr = table.NewRow(); var lstRevenueForShopByID = lstRevenueForShop.Where(s => s.ShopID == item.ID && lstKPIBonus.Where(m => m.IsTotalRevenue == true).FirstOrDefault().ID == s.KPIBonusID).FirstOrDefault(); dr[Sal_ReportRevenueForShopsEntity.FieldNames.ShopCode] = item != null ? item.Code : string.Empty; dr[Sal_ReportRevenueForShopsEntity.FieldNames.ShopName] = item != null ? item.ShopName : string.Empty; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Rank] = item != null ? item.Rank : string.Empty; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Target] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Target : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Actual] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Actual : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.CompletionRate] = lstRevenueForShopByID != null ? lstRevenueForShopByID.Actual.Value / lstRevenueForShopByID.Target.Value : 0; double totalPrice = 0; foreach (var kpi in lstKPIBonus) { var CommissionName = Sal_ReportRevenueForShopsEntity.FieldNames.Commission + kpi.KPIBonusName; var lstKpiBonusByID = lstKPIBonusItem.Where(s => s.ShopID == item.ID && kpi.ID == s.KPIBonusID).FirstOrDefault(); var lstRevenueRecordByID = lstRevenueRecord.Where(s => s.ShopID.Value == item.ID && s.KPIBonusID == kpi.ID).FirstOrDefault(); if (table.Columns.Contains(kpi.KPIBonusName)) { dr[kpi.KPIBonusName] = lstKpiBonusByID != null ? lstKpiBonusByID.Value : 0; } if (table.Columns.Contains(CommissionName)) { dr[CommissionName] = lstRevenueRecordByID != null ? lstRevenueRecordByID.Amount * 1 : 0; totalPrice += lstRevenueRecordByID != null ? (double)lstRevenueRecordByID.Amount : 0; } } var lstRevenueRecordByItem = lstRevenueRecord.Where(s => s.KPIBonusID == null && s.Type == EnumDropDown.SalesType.E_ITEM_MAJOR.ToString() && s.ShopID == item.ID).FirstOrDefault(); var lstRevenueRecordByLineItem = lstRevenueRecord.Where(s => s.KPIBonusID == null && s.Type == EnumDropDown.SalesType.E_LINEITEM_MAJOR.ToString() && s.ShopID == item.ID).FirstOrDefault(); dr[Sal_ReportRevenueForShopsEntity.FieldNames.TopLine5Forcus] = item != null ? item.MainLineProduct : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.CommissionTopLine5Forcus] = lstRevenueRecordByItem != null ? lstRevenueRecordByItem.Amount : 0; totalPrice += lstRevenueRecordByItem != null ? (double)lstRevenueRecordByItem.Amount : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.ProductForcus] = item != null ? item.PromoteProduct : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.CommissionProductForcus] = lstRevenueRecordByLineItem != null ? lstRevenueRecordByLineItem.Amount : 0; totalPrice += lstRevenueRecordByLineItem != null ? (double)lstRevenueRecordByLineItem.Amount : 0; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Commission] = totalPrice; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Incentive] = totalPrice; double FriceSL = (double)GetObjectValue(new List<Cat_ElementEntity>(), listElementFormula, item.Formular1).Value; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Shiftleader] = FriceSL; dr[Sal_ReportRevenueForShopsEntity.FieldNames.Total] = totalPrice + FriceSL; table.Rows.Add(dr); } return table.ConfigTable(true); } }
public void ComputeComissionService(Guid? ShopID, DateTime Month, string UserLogin) { using (var context = new VnrHrmDataContext()) { string status = string.Empty; var unitOfWork = (IUnitOfWork)new UnitOfWork(context); var repoShop = new CustomBaseRepository<Cat_Shop>(unitOfWork); var repoRevenueForShop = new CustomBaseRepository<Sal_RevenueForShopEntity>(unitOfWork); var repoItemForShopEntity = new CustomBaseRepository<Sal_ItemForShopEntity>(unitOfWork); var repoLineItemForShopEntity = new CustomBaseRepository<Sal_LineItemForShopEntity>(unitOfWork); var repoRevenueRecord = new CustomBaseRepository<Sal_RevenueRecord>(unitOfWork); #region Get Data List<object> listModel = new List<object>(); listModel.AddRange(new object[5]); listModel[3] = 1; listModel[4] = Int32.MaxValue - 1; List<Cat_Shop> listShop = GetData<Cat_Shop>(listModel, ConstantSql.hrm_cat_sp_get_Shop, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[4]); listModel[2] = 1; listModel[3] = Int32.MaxValue - 1; List<Cat_ShopGroupEntity> listGroupShop = GetData<Cat_ShopGroupEntity>(listModel, ConstantSql.hrm_cat_sp_get_ShopGroup, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[6]); listModel[4] = 1; listModel[5] = Int32.MaxValue - 1; List<Sal_RevenueForShopEntity> listRevenueForShop = GetData<Sal_RevenueForShopEntity>(listModel, ConstantSql.hrm_sal_sp_get_RevenueForShop, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[5]); listModel[3] = 1; listModel[4] = Int32.MaxValue - 1; List<Sal_ItemForShopEntity> listItemForShop = GetData<Sal_ItemForShopEntity>(listModel, ConstantSql.hrm_sal_sp_get_ItemForShop, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[5]); listModel[3] = 1; listModel[4] = Int32.MaxValue - 1; List<Sal_LineItemForShopEntity> listLineItemForShop = GetData<Sal_LineItemForShopEntity>(listModel, ConstantSql.hrm_sal_sp_get_LineItemForShop, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[4]); listModel[0] = Month; listModel[2] = 1; listModel[3] = Int32.MaxValue - 1; List<Sal_RevenueRecord> listRevenueRecord = GetData<Sal_RevenueRecord>(listModel, ConstantSql.hrm_sal_sp_get_RevenueRecord, UserLogin, ref status).ToList(); listModel = new List<object>(); listModel.AddRange(new object[18]); listModel[16] = 1; listModel[17] = int.MaxValue - 1; List<Hre_ProfileEntity> listProfile = GetData<Hre_ProfileEntity>(listModel, ConstantSql.hrm_hr_sp_get_Profile, UserLogin, ref status).ToList(); List<Cat_KPIBonusEntity> listKPIBonus = new List<Cat_KPIBonusEntity>(); listModel = new List<object>(); listModel.AddRange(new object[4]); listModel[2] = 1; listModel[3] = Int32.MaxValue - 1; listKPIBonus = GetData<Cat_KPIBonusEntity>(listModel, ConstantSql.hrm_cat_sp_get_KPIBonus, UserLogin, ref status).ToList(); List<Cat_KPIBonusItemEntity> listKPIBonusItem = new List<Cat_KPIBonusItemEntity>(); listModel = new List<object>(); listModel.AddRange(new object[4]); listModel[2] = 1; listModel[3] = Int32.MaxValue - 1; listKPIBonusItem = GetData<Cat_KPIBonusItemEntity>(listModel, ConstantSql.hrm_cat_sp_get_KPIBonusItem, UserLogin, ref status).ToList(); #endregion List<Guid> ListShopID = new List<Guid>(); if (ShopID == null) { ListShopID = listShop.Select(m => m.ID).ToList(); } else { if (!listShop.Any(m => m.ID == ShopID)) { if (!listGroupShop.Any(m => m.ID == ShopID)) { ListShopID = listShop.Select(m => m.ID).ToList(); } else { ListShopID = listShop.Where(m => m.ShopGroupID == ShopID).Select(m => m.ID).ToList(); } } else { ListShopID.Add((Guid)ShopID); } } List<Sal_RevenueRecord> _tmpRevenueRecord; List<ElementFormula> listElementFormula = new List<ElementFormula>(); foreach (Guid Guid in ListShopID) { #region Xóa tháng đã tính trước đó nếu có _tmpRevenueRecord = new List<Sal_RevenueRecord>(); _tmpRevenueRecord = listRevenueRecord.Where(m => m.ShopID == Guid && m.Month.Value.Month == Month.Month && m.Month.Value.Year == Month.Year).ToList(); if (_tmpRevenueRecord.Count > 0) { foreach (var j in _tmpRevenueRecord) { repoRevenueRecord.Remove(j); } } #endregion #region Lấy các phần tử là Enum Sal_RevenueForShopEntity RevenueForShopItem = new Sal_RevenueForShopEntity(); RevenueForShopItem = listRevenueForShop.Where(m => m.ShopID == Guid && m.KPIBonusID == listKPIBonus.Where(t => t.IsTotalRevenue == true).FirstOrDefault().ID && (m.DateFrom.Value.Month == Month.Month && m.DateFrom.Value.Year == Month.Year) && (m.DateTo.Value.Month == Month.Month && m.DateTo.Value.Year == Month.Year)).FirstOrDefault(); if (RevenueForShopItem != null) { listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_TAGET_SHOP.ToString(), RevenueForShopItem.Target, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_ACTUAL_SHOP.ToString(), RevenueForShopItem.Actual, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_PRECENT_REVENUE.ToString(), RevenueForShopItem.Actual / RevenueForShopItem.Target, 0)); } else { continue; } listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SHOPMEMBER.ToString(), listProfile.Where(m => m.ShopID == Guid).Count(), 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_COUNT_SL.ToString(), listShop.Where(m => m.ID == Guid).FirstOrDefault().NoShiftLeader, 0)); listElementFormula.Add(new ElementFormula(PayrollElement.SAL_COM_RANK.ToString(), listShop.Where(m => m.ID == Guid).FirstOrDefault().Rank, 0)); #endregion #region Lấy giá trị cho các loại phần tử là Hoa Hồng double TotalMoney_RevenueForShop = 0;//biến lưu tổng doanh thu cửa hàng sau khi đã trừ tiền SL if (listKPIBonus != null && listKPIBonus.Count > 0) { #region tính số tổng doanh thu sau khi trừ tiền SL RevenueForShopItem = new Sal_RevenueForShopEntity(); RevenueForShopItem = listRevenueForShop.Where(m => m.ShopID == Guid && m.KPIBonusID == listKPIBonus.Where(t => t.IsTotalRevenue == true).FirstOrDefault().ID && (m.DateFrom.Value.Month == Month.Month && m.DateFrom.Value.Year == Month.Year) && (m.DateTo.Value.Month == Month.Month && m.DateTo.Value.Year == Month.Year)).FirstOrDefault(); double Money_SL = 0; if (RevenueForShopItem != null) { Money_SL = (double)GetObjectValue(new List<Cat_ElementEntity>(), listElementFormula, listShop.Single(m => m.ID == Guid).Formular1).Value; double Money_Revenue = (double)RevenueForShopItem.Actual * ((double)GetObjectValue(new List<Cat_ElementEntity>(), listElementFormula, listShop.Single(m => m.ID == Guid).Formular).Value / 100); TotalMoney_RevenueForShop = Money_Revenue - Money_SL; } #endregion Sal_RevenueRecord RecordItem = new Sal_RevenueRecord(); foreach (var j in listKPIBonus) { RevenueForShopItem = listRevenueForShop.Where(m => m.ShopID == Guid && m.KPIBonusID == j.ID && (m.DateFrom.Value.Month == Month.Month && m.DateFrom.Value.Year == Month.Year) && (m.DateTo.Value.Month == Month.Month && m.DateTo.Value.Year == Month.Year)).FirstOrDefault(); RecordItem = new Sal_RevenueRecord(); RecordItem.ShopID = Guid; RecordItem.Month = Month; RecordItem.KPIBonusID = j.ID; RecordItem.Type = EnumDropDown.SalesType.E_SALE.ToString(); RecordItem.Note = string.Format("{0:n0}", Money_SL); if ((RevenueForShopItem != null && RevenueForShopItem.Target <= RevenueForShopItem.Actual) || (RevenueForShopItem != null && RevenueForShopItem.IsPass != null && RevenueForShopItem.IsPass == true)) { Cat_KPIBonusItemEntity bonusItem = listKPIBonusItem.Where(m => m.ShopID == Guid && m.KPIBonusID == j.ID).FirstOrDefault(); if (bonusItem != null) { RecordItem.Amount = TotalMoney_RevenueForShop * (bonusItem.Value / 100); } else { RecordItem.Amount = 0; } } else { RecordItem.Amount = 0; } repoRevenueRecord.Add(RecordItem); } #region Lấy giá trị cho 2 enum là dòng sản phẩm và sản phẩm //SAL_COM_PERCENT_SHOP_5 List<Sal_LineItemForShopEntity> listLineItem = listLineItemForShop.Where(m => m.ShopID == Guid && (m.DateFrom.Value.Month == Month.Month && m.DateFrom.Value.Year == Month.Year) && (m.DateTo.Value.Month == Month.Month && m.DateTo.Value.Year == Month.Year)).ToList(); RecordItem = new Sal_RevenueRecord(); RecordItem.ShopID = Guid; RecordItem.Month = Month; RecordItem.Type = EnumDropDown.SalesType.E_LINEITEM_MAJOR.ToString(); if (listLineItem != null && listLineItem.Count > 0 && listLineItem.Any(m => m.Actual < m.Target && m.IsPass != true)) { int NumberPass = 0; foreach (var lineItem in listLineItem) { if ((lineItem.IsPass != null && lineItem.IsPass == true) || lineItem.Actual >= lineItem.Target) { NumberPass++; } } if (NumberPass >= 3) { RecordItem.Amount = TotalMoney_RevenueForShop * (listShop.Single(m => m.ID == Guid).MainLineProduct / 100); } else { RecordItem.Amount = 0; } } else { RecordItem.Amount = TotalMoney_RevenueForShop * (listShop.Single(m => m.ID == Guid).MainLineProduct / 100); } repoRevenueRecord.Add(RecordItem); //SAL_COM_PERCENT_SHOP_6 List<Sal_ItemForShopEntity> listItem = listItemForShop.Where(m => m.ShopID == Guid && (m.DateFrom.Value.Month == Month.Month && m.DateFrom.Value.Year == Month.Year) && (m.DateTo.Value.Month == Month.Month && m.DateTo.Value.Year == Month.Year)).ToList(); RecordItem = new Sal_RevenueRecord(); RecordItem.ShopID = Guid; RecordItem.Month = Month; RecordItem.Type = EnumDropDown.SalesType.E_ITEM_MAJOR.ToString(); if (listItem != null && listItem.Count > 0 && listItem.Any(m => m.Actual < m.Target && m.IsPass != true)) { RecordItem.Amount = 0; } else { RecordItem.Amount = TotalMoney_RevenueForShop * (listShop.Single(m => m.ID == Guid).PromoteProduct / 100); } repoRevenueRecord.Add(RecordItem); #endregion } #endregion } repoRevenueRecord.SaveChanges(); } }