private ReportDataSource GetRecAccount(EnumAccountCatType accountCatType, string costCenterId, string recPeriodId)
{
IList <TRecAccount> dets;
TRecPeriod recPeriod = _tRecPeriodRepository.Get(recPeriodId);
if (!string.IsNullOrEmpty(costCenterId))
{
MCostCenter costCenter = _mCostCenterRepository.Get(costCenterId);
dets = _tRecAccountRepository.GetByAccountType(accountCatType.ToString(), costCenter, recPeriod);
}
else
{
dets = _tRecAccountRepository.GetByAccountType(accountCatType.ToString(), null, recPeriod);
}
var list = from det in dets
select new
{
det.RecAccountStart,
det.RecAccountEnd,
det.RecAccountDesc,
CostCenterId = det.CostCenterId.Id,
det.CostCenterId.CostCenterName,
AccountId = det.AccountId.Id,
det.AccountId.AccountName,
AccountCatId = det.AccountId.AccountCatId.Id,
det.AccountId.AccountCatId.AccountCatName,
AccountParentId = det.AccountId.AccountParentId != null ? det.AccountId.AccountParentId.Id : ""
}
;
ReportDataSource reportDataSource = new ReportDataSource("RecAccountViewModel", list.ToList());
return(reportDataSource);
}
public ActionResult Closing(ClosingViewModel viewModel, FormCollection formCollection)
{
TRecPeriod recPeriod = new TRecPeriod();
recPeriod.SetAssignedIdTo(Guid.NewGuid().ToString());
recPeriod.PeriodFrom = viewModel.DateFrom.Value;
recPeriod.PeriodTo = viewModel.DateTo.Value;
recPeriod.PeriodType = EnumPeriodType.Custom.ToString();
recPeriod.PeriodDesc = string.Format("{0:dd-MMM-yyyy} s/d {1:dd-MMM-yyyy}", viewModel.DateFrom.Value, viewModel.DateTo.Value);
recPeriod.CreatedBy = User.Identity.Name;
recPeriod.CreatedDate = DateTime.Now;
try
{
_tRecPeriodRepository.Save(recPeriod);
_tRecPeriodRepository.DbContext.CommitChanges();
_tRecAccountRepository.RunClosing(recPeriod);
TempData[EnumCommonViewData.SaveState.ToString()] = EnumSaveState.Success;
}
catch (Exception)
{
TempData[EnumCommonViewData.SaveState.ToString()] = EnumSaveState.Failed;
}
return(RedirectToAction("Closing"));
}
private void CalculateCommission(TRecPeriod recPeriod)
{
IList <TLoan> listLoan = _loanRepository.GetListByAccDate(recPeriod.PeriodFrom, recPeriod.PeriodTo);
CalculateCommissionSurveyor(recPeriod, listLoan);
CalculateCommissionTLS(recPeriod, listLoan);
CalculateCommissionSales(recPeriod, listLoan);
CalculateCommissionCollector(recPeriod, listLoan);
}
public void RunOpening(TRecPeriod recPeriod)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@" delete from TRecAccount
where RecPeriodId = :recPeriod ");
IQuery q = Session.CreateQuery(sql.ToString());
q.SetEntity("recPeriod", recPeriod);
q.ExecuteUpdate();
}
private void CalculateCommissionSurveyor(TRecPeriod recPeriod, IList <TLoan> listLoan)
{
MCommission comm = _mCommissionRepository.GetCommissionByDate(EnumDepartment.SU, recPeriod.PeriodFrom, recPeriod.PeriodTo);
if (comm == null)
{
throw new Exception("Data komisi untuk surveyor tidak tersedia.");
}
IList <MCommissionDet> listDets = comm.CommissionDets;
decimal?incentiveSurvey = 0;
decimal?incentiveApprove = 0;
//get incentive
if (listDets.Count > 0)
{
MCommissionDet det = listDets[0];
incentiveSurvey = det.DetailIncentiveSurveyOnly;
incentiveApprove = det.DetailIncentiveSurveyAcc;
}
//get survey group by surveyor
IList <TLoanSurvey> listSurvey = _tLoanSurveyRepository.GetListBySurveyDate(recPeriod.PeriodFrom, recPeriod.PeriodTo);
var recapSurvey = from survey in listSurvey
where survey.LoanId.SurveyorId != null
group survey by survey.LoanId.SurveyorId into grouped
select new
{
SurveyorId = grouped.Key,
CountSurvey = grouped.Count(x => x.LoanId.SurveyorId != null)
};
//save survey commission
foreach (var recap in recapSurvey)
{
SaveCommission(recPeriod, EnumCommissionType.IncentiveSurvey, EnumDepartment.SU, recap.SurveyorId, 1, recap.CountSurvey, incentiveSurvey);
}
//get loan group by surveyor that status is OK
var recapLoan = from loan in listLoan
where loan.LoanStatus == EnumLoanStatus.OK.ToString() && loan.SurveyorId != null
group loan by loan.SurveyorId into grouped
select new
{
SurveyorId = grouped.Key,
CountLoan = grouped.Count(x => x.SurveyorId != null)
};
//save loan commission
foreach (var recap in recapLoan)
{
SaveCommission(recPeriod, EnumCommissionType.IncentiveApprove, EnumDepartment.SU, recap.SurveyorId, 1, recap.CountLoan, incentiveApprove);
}
}
public static OpeningViewModel Create(ITRecPeriodRepository tRecPeriodRepository)
{
OpeningViewModel viewModel = new OpeningViewModel();
IList <TRecPeriod> listRecPeriod = tRecPeriodRepository.GetAll();
TRecPeriod recPeriod = new TRecPeriod();
recPeriod.PeriodDesc = "-Pilih Period-";
listRecPeriod.Insert(0, recPeriod);
viewModel.RecPeriodList = new SelectList(listRecPeriod, "Id", "PeriodDesc");
return(viewModel);
}
public void RunClosing(TRecPeriod recPeriod)
{
Session
.CreateSQLQuery(@" EXECUTE [SP_CLOSING]
@periodId = :periodId,
@periodType = :periodType,
@periodFrom = :periodFrom,
@periodTo = :periodTo")
.SetString("periodId", recPeriod.Id)
.SetString("periodType", recPeriod.PeriodType)
.SetDateTime("periodFrom", recPeriod.PeriodFrom)
.SetDateTime("periodTo", recPeriod.PeriodTo)
.UniqueResult();
}
public ActionResult Opening(OpeningViewModel viewModel, FormCollection formCollection)
{
TRecPeriod recPeriod = _tRecPeriodRepository.Get(viewModel.RecPeriodId);
try
{
_tRecAccountRepository.RunOpening(recPeriod);
_tRecPeriodRepository.Delete(recPeriod);
_tRecPeriodRepository.DbContext.CommitChanges();
TempData[EnumCommonViewData.SaveState.ToString()] = EnumSaveState.Success;
}
catch (Exception ex)
{
TempData[EnumCommonViewData.SaveState.ToString()] = EnumSaveState.Failed;
}
return(RedirectToAction("Opening"));
}
public static ReportParamViewModel CreateReportParamViewModel(IMCostCenterRepository mCostCenterRepository, IMWarehouseRepository mWarehouseRepository, IMSupplierRepository mSupplierRepository, ITRecPeriodRepository tRecPeriodRepository, IMItemRepository mItemRepository)
{
ReportParamViewModel viewModel = new ReportParamViewModel();
IList <MCostCenter> list = mCostCenterRepository.GetAll();
MCostCenter costCenter = new MCostCenter();
costCenter.CostCenterName = "-Semua Cost Center-";
list.Insert(0, costCenter);
viewModel.CostCenterList = new SelectList(list, "Id", "CostCenterName");
IList <MWarehouse> listWarehouse = mWarehouseRepository.GetAll();
MWarehouse account = new MWarehouse();
account.WarehouseName = "-Semua Gudang-";
listWarehouse.Insert(0, account);
viewModel.WarehouseList = new SelectList(listWarehouse, "Id", "WarehouseName");
IList <MSupplier> listSupplier = mSupplierRepository.GetAll();
MSupplier supplier = new MSupplier();
supplier.SupplierName = "-Semua Supplier-";
listSupplier.Insert(0, supplier);
viewModel.SupplierList = new SelectList(listSupplier, "Id", "SupplierName");
IList <TRecPeriod> listRecPeriod = tRecPeriodRepository.GetAll();
TRecPeriod recPeriod = new TRecPeriod();
recPeriod.PeriodDesc = "-Pilih Period-";
listRecPeriod.Insert(0, recPeriod);
viewModel.RecPeriodList = new SelectList(listRecPeriod, "Id", "PeriodDesc");
IList <MItem> listItem = mItemRepository.GetAll();
MItem item = new MItem();
item.ItemName = "-Semua Item-";
listItem.Insert(0, item);
viewModel.ItemList = new SelectList(listItem, "Id", "ItemName");
viewModel.DateFrom = DateTime.Today;
viewModel.DateTo = DateTime.Today;
return(viewModel);
}
private void SaveCommission(TRecPeriod recPeriod, EnumCommissionType enumCommissionType, EnumDepartment department, MEmployee mEmployee, int level, decimal?commissionFactor, decimal?commissionValue, string desc = null)
{
TCommission comm = new TCommission();
comm.SetAssignedIdTo(Guid.NewGuid().ToString());
comm.CommissionLevel = level;
comm.CommissionFactor = commissionFactor;
comm.CommissionValue = commissionValue;
comm.EmployeeId = mEmployee;
comm.CommissionType = enumCommissionType.ToString();
comm.CommissionStatus = department.ToString();
comm.RecPeriodId = recPeriod;
comm.CommissionDesc = desc;
comm.CreatedBy = User.Identity.Name;
comm.CreatedDate = DateTime.Now;
comm.DataStatus = EnumDataStatus.Updated.ToString();
_tCommissionRepository.Save(comm);
}
public ActionResult Index(ClosingViewModel viewModel, FormCollection formCollection)
{
string Message = string.Empty;
bool Success = true;
try
{
_tRecPeriodRepository.DbContext.BeginTransaction();
TRecPeriod recPeriod = new TRecPeriod();
recPeriod.SetAssignedIdTo(Guid.NewGuid().ToString());
recPeriod.PeriodFrom = Helper.CommonHelper.ConvertToDate(formCollection["StartDate"]);
recPeriod.PeriodTo = Helper.CommonHelper.ConvertToDate(formCollection["EndDate"]);
recPeriod.PeriodType = EnumPeriodType.Custom.ToString();
recPeriod.PeriodDesc = string.Format("{0} s/d {1}", formCollection["StartDate"], formCollection["EndDate"]);
recPeriod.CreatedBy = User.Identity.Name;
recPeriod.CreatedDate = DateTime.Now;
CalculateCommission(recPeriod);
_tRecPeriodRepository.Save(recPeriod);
_tRecPeriodRepository.DbContext.CommitChanges();
Success = true;
Message = "Tutup buku Berhasil Disimpan.";
}
catch (Exception ex)
{
Success = false;
Message = "Error :\n" + ex.GetBaseException().Message;
_tRecPeriodRepository.DbContext.RollbackTransaction();
}
var e = new
{
Success,
Message
};
return(Json(e, JsonRequestBehavior.AllowGet));
}
private void CalculateCommissionCollectorDetail(TRecPeriod recPeriod, decimal?target, decimal?real, MEmployee tempEmp, IList <MCommissionDet> listDets)
{
MCommissionDet det;
//if (real.HasValue)
{
decimal percentReal = Math.Ceiling(target.HasValue && real.HasValue && target != 0 ? real.Value / target.Value * 100 : 0);
//get incentive
det = (from d in listDets
where d.DetailLowTarget <= percentReal && d.DetailHighTarget >= percentReal
select d).FirstOrDefault();
//check if it has commission to calculate
if (det != null)
{
//normal commission
SaveCommission(recPeriod, EnumCommissionType.Commission, EnumDepartment.COL, tempEmp, 1, real, det.DetailValue.Value / 100);
//transport commission
SaveCommission(recPeriod, EnumCommissionType.TransportAllowance, EnumDepartment.COL, tempEmp, 2, 30, det.DetailTransportAllowance);
}
}
}
public static ReportParamViewModel Create(ITRecPeriodRepository tRecPeriodRepository, IMPartnerRepository mPartnerRepository)
{
ReportParamViewModel viewModel = new ReportParamViewModel();
viewModel.DateFrom = DateTime.Today.AddDays(-1);
viewModel.DateTo = DateTime.Today;
IList <TRecPeriod> listRecPeriod = tRecPeriodRepository.GetAll();
TRecPeriod recPeriod = new TRecPeriod();
recPeriod.PeriodDesc = "-Pilih Periode-";
listRecPeriod.Insert(0, recPeriod);
viewModel.RecPeriodList = new SelectList(listRecPeriod, "Id", "PeriodDesc");
IList <MPartner> listPartner = mPartnerRepository.GetAll();
MPartner partner = new MPartner();
partner.PartnerName = "-Pilih Toko-";
listPartner.Insert(0, partner);
viewModel.PartnerList = new SelectList(listPartner, "Id", "PartnerName");
return(viewModel);
}
private void CalculateCommissionCollector(TRecPeriod recPeriod, IList <TLoan> listLoan)
{
MCommission comm = _mCommissionRepository.GetCommissionByDate(EnumDepartment.COL, recPeriod.PeriodFrom, recPeriod.PeriodTo);
if (comm == null)
{
throw new Exception("Data komisi untuk kolektor tidak tersedia.");
}
IList <MCommissionDet> listDets = comm.CommissionDets;
IList <MZoneEmployee> zoneEmployees = _mZoneEmployeeRepository.GetListByDate(recPeriod.PeriodFrom, recPeriod.PeriodTo);
IList <MZoneEmployee> orderedZe = (from ze in zoneEmployees
orderby ze.EmployeeId.Id
select ze).ToList();
//get installment group by tls that status is OK
IList <TInstallment> listInstallment = _installmentRepository.GetListByMaturityDate(recPeriod.PeriodFrom, recPeriod.PeriodTo);
//save collector commission
MCommissionDet det;
MEmployee tempEmp = null;
decimal? target = 0;
decimal? real = 0;
foreach (MZoneEmployee ze in orderedZe)
{
//get total by employee
//if (tempEmp != null)
{
if (ze.EmployeeId == tempEmp)
{
//sum target and real by employee
//get target and real
target = target + (from ins in listInstallment
where ins.LoanId.ZoneId == ze.ZoneId
select ins.InstallmentBasic).Sum();
real = real + (from ins in listInstallment
where
ins.LoanId.ZoneId == ze.ZoneId &&
ins.InstallmentStatus == EnumInstallmentStatus.Paid.ToString() &&
ins.InstallmentPaymentDate <= ins.InstallmentMaturityDate
select ins.InstallmentBasic).Sum();
}
else
{
//calculate commission
if (tempEmp != null)
{
CalculateCommissionCollectorDetail(recPeriod, target, real, tempEmp, listDets);
}
target = 0;
real = 0;
}
}
tempEmp = ze.EmployeeId;
}
//calculate last commission
if (tempEmp != null)
{
CalculateCommissionCollectorDetail(recPeriod, target, real, tempEmp, listDets);
}
}
public IList <TRecAccount> GetByAccountType(string accountCatType, MCostCenter costCenter, TRecPeriod recPeriod)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@" select rec
from TRecAccount as rec
left outer join rec.AccountId acc, MAccountCat cat
where acc.AccountCatId = cat.Id");
if (!string.IsNullOrEmpty(accountCatType))
{
sql.AppendLine(@" and cat.AccountCatType = :accountCatType");
}
if (costCenter != null)
{
sql.AppendLine(@" and rec.CostCenterId = :costCenter");
}
if (recPeriod != null)
{
sql.AppendLine(@" and rec.RecPeriodId = :recPeriod");
}
sql.AppendLine(@" order by rec.CostCenterId, cat.Id");
IQuery q = Session.CreateQuery(sql.ToString());
if (!string.IsNullOrEmpty(accountCatType))
{
q.SetString("accountCatType", accountCatType);
}
if (costCenter != null)
{
q.SetEntity("costCenter", costCenter);
}
if (recPeriod != null)
{
q.SetEntity("recPeriod", recPeriod);
}
return(q.List <TRecAccount>());
}
private void CalculateCommissionTLS(TRecPeriod recPeriod, IList <TLoan> listLoan)
{
MCommission comm = _mCommissionRepository.GetCommissionByDate(EnumDepartment.TLS, recPeriod.PeriodFrom, recPeriod.PeriodTo);
if (comm == null)
{
throw new Exception("Data komisi untuk TLS tidak tersedia.");
}
//target mapped to commissionvalue
//next to do, changes it to target
decimal target = comm.CommissionValue.Value;
IList <MCommissionDet> listDets = comm.CommissionDets;
//get loan group by tls that status is OK
var recapLoan = from loan in listLoan
where loan.LoanStatus == EnumLoanStatus.OK.ToString() && loan.TLSId != null
group loan by loan.TLSId into grouped
select new
{
TLSId = grouped.Key,
SumLoan = grouped.Sum(x => x.LoanBasicPrice)
};
//save TLS commission
MCommissionDet det;
foreach (var recap in recapLoan)
{
decimal percentReal = Math.Ceiling(target != 0 ? recap.SumLoan.Value / target * 100 : 0);
decimal percentCalc = percentReal > 100 ? 100 : percentReal;
//get total to calculate,if realization more than 100, use target value
//if not use sumloan
decimal totalToCalc = percentReal > 100 ? target : recap.SumLoan.Value;
//get incentive
det = (from d in listDets
where d.DetailLowTarget <= percentCalc && d.DetailHighTarget >= percentCalc
select d).FirstOrDefault();
//check if it has commission to calculate
if (det != null)
{
//normal commission
SaveCommission(recPeriod, EnumCommissionType.Commission, EnumDepartment.TLS, recap.TLSId, 1, totalToCalc, det.DetailValue.Value / 100);
//if real > 100, sisa calculate new commission
if (percentReal > 100)
{
det = (from d in listDets
where d.DetailLowTarget > 100
select d).FirstOrDefault();
//check if it has commission to calculate
if (det != null)
{
//above normal commission
SaveCommission(recPeriod, EnumCommissionType.Commission, EnumDepartment.TLS, recap.TLSId, 2, recap.SumLoan.Value - target, det.DetailValue.Value / 100);
}
}
}
}
}
private void CalculateCommissionSales(TRecPeriod recPeriod, IList <TLoan> listLoan)
{
MCommission comm = _mCommissionRepository.GetCommissionByDate(EnumDepartment.SA, recPeriod.PeriodFrom, recPeriod.PeriodTo);
if (comm == null)
{
throw new Exception("Data komisi untuk SA tidak tersedia.");
}
IList <MCommissionDet> listDets = (from det in comm.CommissionDets
orderby det.DetailCustomerNumber descending
select det).ToList();
//get loan group by SA that status is OK
var recapLoan = from loan in listLoan
where loan.LoanStatus == EnumLoanStatus.OK.ToString() && loan.SalesmanId != null
group loan by loan.SalesmanId into grouped
select new
{
SalesmanId = grouped.Key,
SumLoan = grouped.Sum(x => x.LoanBasicPrice),
CountLoan = grouped.Count(x => x.SalesmanId != null)
};
IList <TLoan> listOrderedLoan = (from loan in listLoan
where loan.LoanStatus == EnumLoanStatus.OK.ToString() && loan.SalesmanId != null
orderby loan.LoanAccDate
select loan).ToList();
TLoan loanToCalc;
int startLoanNo = 0;
int endLoanNo = 0;
decimal commissionRun = 0;
int sisaLoan = 0;
bool hasInsertAddedIncentive = false;
//get commission value
foreach (var recap in recapLoan)
{
//get loan by salesman
listOrderedLoan = (from loan in listLoan
where loan.LoanStatus == EnumLoanStatus.OK.ToString() && loan.SalesmanId != null && loan.SalesmanId == recap.SalesmanId
orderby loan.LoanAccDate
select loan).ToList();
sisaLoan = recap.CountLoan;
startLoanNo = 0;
endLoanNo = 0;
hasInsertAddedIncentive = false;
//loop commission detail
foreach (MCommissionDet det in listDets)
{
//search commission
commissionRun = det.DetailCustomerNumber != 0 ? sisaLoan / det.DetailCustomerNumber : 0;
if (commissionRun >= 1)
{
//get number of last loan to calculate with current commission
endLoanNo = endLoanNo + Convert.ToInt32(Math.Ceiling(commissionRun) * det.DetailCustomerNumber);
//get sisa loan
sisaLoan = sisaLoan - endLoanNo;
//loop loan
for (int j = startLoanNo; j < endLoanNo; j++)
{
//save commission
loanToCalc = listOrderedLoan[j];
SaveCommission(recPeriod, EnumCommissionType.Commission, EnumDepartment.SA, recap.SalesmanId, j, loanToCalc.LoanBasicPrice, det.DetailValue / 100, loanToCalc.LoanCode);
}
startLoanNo = endLoanNo;
//insert other incentive
if (!hasInsertAddedIncentive)
{
SaveCommission(recPeriod, EnumCommissionType.TransportAllowance, EnumDepartment.SA, recap.SalesmanId, endLoanNo, 30, det.DetailTransportAllowance);
SaveCommission(recPeriod, EnumCommissionType.IncentiveCredit, EnumDepartment.SA, recap.SalesmanId, endLoanNo + 1, 1, det.DetailIncentive);
hasInsertAddedIncentive = true;
}
}
}
}
}
