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();
}
}
