public static List <BonusSambilanDetailModel> GetBonusSambilanDetailData(int month, int year)
{
ApplicationDbContext db = new ApplicationDbContext();
List <HR_BONUS_SAMBILAN_DETAIL> tableList = HR_BONUS_SAMBILAN_DETAIL.GetBonusSambilanDetailData(month, year);
List <BonusSambilanDetailModel> outputList = new List <BonusSambilanDetailModel>();
foreach (HR_BONUS_SAMBILAN_DETAIL y in tableList)
{
BonusSambilanDetailModel d = new BonusSambilanDetailModel();
HR_MAKLUMAT_PERIBADI maklumat = db.HR_MAKLUMAT_PERIBADI
.Where(m => m.HR_NO_PEKERJA == y.HR_NO_PEKERJA).FirstOrDefault();
HR_MAKLUMAT_PEKERJAAN kerja = db.HR_MAKLUMAT_PEKERJAAN
.Where(m => m.HR_NO_PEKERJA == y.HR_NO_PEKERJA).FirstOrDefault();
d.BulanBonus = y.HR_BULAN_BONUS;
d.TahunBonus = y.HR_TAHUN_BONUS;
d.Nama = maklumat.HR_NAMA_PEKERJA;
d.NoPekerja = y.HR_NO_PEKERJA;
d.NoKadPengenalan = y.HR_NO_KPBARU;
d.NoAkaunBank = kerja.HR_NO_AKAUN_BANK;
d.NoKWSP = kerja.HR_NO_KWSP;
d.TarikhLantikan = kerja.HR_TARIKH_LANTIKAN;
d.Jan = y.HR_JANUARI;
d.Feb = y.HR_FEBRUARI;
d.Mac = y.HR_MAC;
d.April = y.HR_APRIL;
d.Mei = y.HR_MEI;
d.Jun = y.HR_JUN;
d.Julai = y.HR_JULAI;
d.Ogos = y.HR_OGOS;
d.September = y.HR_SEPTEMBER;
d.Oktober = y.HR_OKTOBER;
d.November = y.HR_NOVEMBER;
d.Disember = y.HR_DISEMBER;
d.JumlahGaji = y.HR_JUMLAH_GAJI;
d.GajiPurata = y.HR_GAJI_PURATA;
d.BonusDiterima = y.HR_BONUS_DITERIMA;
d.BonusLayak = y.HR_BONUS_LAYAK;
d.Catatan = y.HR_CATATAN;
d.MinBulan = y.HR_BULAN_START;
d.MaxBulan = y.HR_BULAN_END;
if (y.HR_MUKTAMAD > 0)
{
d.IsMuktamad = true;
}
else
{
d.IsMuktamad = false;
}
outputList.Add(d);
}
//sort by nama
outputList = outputList.OrderBy(x => x.Nama).ToList();
return(outputList);
}
private static bool IsNoData(BonusSambilanDetailModel det)
{
bool retbool = false;
if (det.Jan == 0 && det.Feb == 0 && det.Mac == 0 && det.April == 0 && det.Mei == 0 && det.Jun == 0 &&
det.Julai == 0 && det.Ogos == 0 && det.September == 0 && det.Oktober == 0 &&
det.November == 0 && det.Disember == 0)
{
retbool = true;
}
return(retbool);
}
/// <summary>
/// Output BONUS_SAMBILAN_DETAIL data from HR_TRANSAKSI_SAMBILAN_DETAIL
/// </summary>
/// <param name="startMonth">start of month (if not Jan)</param>
/// <param name="month">bulan bonus dibayar</param>
/// <param name="year">tahun bonus dibayar</param>
/// <returns>List BonusSambilanDetailModel</returns>
public static List <BonusSambilanDetailModel> GetDetailsFromTransaksi
(int startMonth, int month, int year, int endMonth)
{
ApplicationDbContext db = new ApplicationDbContext();
//get list of pekerja tidak aktif
List <string> listHR_TidakAktif = db.HR_MAKLUMAT_PERIBADI
.Where(s => s.HR_AKTIF_IND == "T" &&
s.HR_ALASAN != "A17").Select(s => s.HR_NO_PEKERJA).Distinct().ToList();
//exclude bulan dibayar (month) and filter out tidak aktif
List <string> listHR_PEKERJA = db.HR_TRANSAKSI_SAMBILAN_DETAIL
.Where(s => s.HR_BULAN_BEKERJA >= startMonth &&
s.HR_BULAN_BEKERJA <= endMonth &&
s.HR_TAHUN == year &&
!listHR_TidakAktif.Contains(s.HR_NO_PEKERJA))
.Select(s => s.HR_NO_PEKERJA).Distinct().ToList();
List <BonusSambilanDetailModel> outputList = new List <BonusSambilanDetailModel>();
foreach (string hr_pekerja in listHR_PEKERJA)
{
BonusSambilanDetailModel det = new BonusSambilanDetailModel();
List <HR_TRANSAKSI_SAMBILAN_DETAIL> elaunlain =
db.HR_TRANSAKSI_SAMBILAN_DETAIL.
Where(x => x.HR_NO_PEKERJA == hr_pekerja &&
x.HR_TAHUN == year &&
x.HR_BULAN_BEKERJA >= startMonth &&
x.HR_BULAN_BEKERJA <= endMonth
//&& x.HR_BULAN_DIBAYAR == month
).ToList();
HR_MAKLUMAT_PERIBADI maklumat = db.HR_MAKLUMAT_PERIBADI
.Where(m => m.HR_NO_PEKERJA == hr_pekerja).FirstOrDefault();
HR_MAKLUMAT_PEKERJAAN kerja = db.HR_MAKLUMAT_PEKERJAAN
.Where(m => m.HR_NO_PEKERJA == hr_pekerja).FirstOrDefault();
det.BulanBonus = month;
det.TahunBonus = year;
det.Nama = maklumat.HR_NAMA_PEKERJA;
det.NoPekerja = hr_pekerja;
det.NoKadPengenalan = maklumat.HR_NO_KPBARU;
det.NoAkaunBank = kerja.HR_NO_AKAUN_BANK;
det.NoKWSP = kerja.HR_NO_KWSP;
det.TarikhLantikan = kerja.HR_TARIKH_LANTIKAN;
int totalBulanBerkhidmat = 0;
det.Jan = GetGajiPokok(db, elaunlain, 1);
if (det.Jan > 0)
{
totalBulanBerkhidmat++;
}
det.Feb = GetGajiPokok(db, elaunlain, 2);
if (det.Feb > 0)
{
totalBulanBerkhidmat++;
}
det.Mac = GetGajiPokok(db, elaunlain, 3);
if (det.Mac > 0)
{
totalBulanBerkhidmat++;
}
det.April = GetGajiPokok(db, elaunlain, 4);
if (det.April > 0)
{
totalBulanBerkhidmat++;
}
det.Mei = GetGajiPokok(db, elaunlain, 5);
if (det.Mei > 0)
{
totalBulanBerkhidmat++;
}
det.Jun = GetGajiPokok(db, elaunlain, 6);
if (det.Jun > 0)
{
totalBulanBerkhidmat++;
}
det.Julai = GetGajiPokok(db, elaunlain, 7);
if (det.Julai > 0)
{
totalBulanBerkhidmat++;
}
det.Ogos = GetGajiPokok(db, elaunlain, 8);
if (det.Ogos > 0)
{
totalBulanBerkhidmat++;
}
det.September = GetGajiPokok(db, elaunlain, 9);
if (det.September > 0)
{
totalBulanBerkhidmat++;
}
det.Oktober = GetGajiPokok(db, elaunlain, 10);
if (det.Oktober > 0)
{
totalBulanBerkhidmat++;
}
det.November = GetGajiPokok(db, elaunlain, 11);
if (det.November > 0)
{
totalBulanBerkhidmat++;
}
det.Disember = GetGajiPokok(db, elaunlain, 12);
if (det.Disember > 0)
{
totalBulanBerkhidmat++;
}
det.JumlahGaji = GetSumGajiPokok(elaunlain);
det.MinBulan = startMonth;
det.MaxBulan = endMonth;
int totalBulanDikira = endMonth - startMonth + 1;
if (totalBulanBerkhidmat > 0)
{
det.GajiPurata = det.JumlahGaji == null ?
0 : decimal.Round(Convert.ToDecimal(det.JumlahGaji) / totalBulanBerkhidmat, 3);
}
//new changes:
decimal totalGajiPokok = GetSumGajiPokok(elaunlain);
decimal bonusLayak = totalGajiPokok / totalBulanDikira;
det.BonusLayak = bonusLayak;
det.BonusDiterima = bonusLayak * (decimal)1.0; //temp start with decimal 1.0
det.IsMuktamad = false;
if (!IsNoData(det))
{
outputList.Add(det);
}
}
//sort by Nama
outputList = outputList.OrderBy(x => x.Nama).ToList();
return(outputList);
}
/// <summary>
/// Output BONUS_SAMBILAN_DETAIL data from HR_TRANSAKSI_SAMBILAN_DETAIL
/// </summary>
/// <param name="startMonth">start of month (if not Jan)</param>
/// <param name="month">bulan bonus dibayar</param>
/// <param name="year">tahun bonus dibayar</param>
/// <returns>List BonusSambilanDetailModel</returns>
public static List <BonusSambilanDetailModel> GetDetailsFromTransaksi
(int startMonth, int month, int year, int endMonth)
{
ApplicationDbContext db = new ApplicationDbContext();
List <string> listHR_PEKERJA = db.HR_TRANSAKSI_SAMBILAN_DETAIL
.Where(s => s.HR_BULAN_DIBAYAR == month &&
s.HR_BULAN_BEKERJA >= startMonth &&
s.HR_BULAN_BEKERJA <= endMonth &&
s.HR_TAHUN == year).Select(s => s.HR_NO_PEKERJA).Distinct().ToList();
List <BonusSambilanDetailModel> outputList = new List <BonusSambilanDetailModel>();
foreach (string hr_pekerja in listHR_PEKERJA)
{
BonusSambilanDetailModel det = new BonusSambilanDetailModel();
List <HR_TRANSAKSI_SAMBILAN_DETAIL> elaunlain =
db.HR_TRANSAKSI_SAMBILAN_DETAIL.
Where(x => x.HR_NO_PEKERJA == hr_pekerja &&
x.HR_TAHUN == year &&
x.HR_BULAN_BEKERJA >= startMonth &&
x.HR_BULAN_BEKERJA <= endMonth &&
x.HR_BULAN_DIBAYAR == month).ToList();
HR_MAKLUMAT_PERIBADI maklumat = db.HR_MAKLUMAT_PERIBADI
.Where(m => m.HR_NO_PEKERJA == hr_pekerja).FirstOrDefault();
HR_MAKLUMAT_PEKERJAAN kerja = db.HR_MAKLUMAT_PEKERJAAN
.Where(m => m.HR_NO_PEKERJA == hr_pekerja).FirstOrDefault();
det.BulanBonus = month;
det.TahunBonus = year;
det.Nama = maklumat.HR_NAMA_PEKERJA;
det.NoPekerja = hr_pekerja;
det.NoKadPengenalan = maklumat.HR_NO_KPBARU;
det.NoAkaunBank = kerja.HR_NO_AKAUN_BANK;
det.NoKWSP = kerja.HR_NO_KWSP;
det.TarikhLantikan = kerja.HR_TARIKH_LANTIKAN;
det.Jan = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 1).Sum(c => c.HR_JUMLAH);
det.Feb = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 2).Sum(c => c.HR_JUMLAH);
det.Mac = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 3).Sum(c => c.HR_JUMLAH);
det.April = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 4).Sum(c => c.HR_JUMLAH);
det.Mei = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 5).Sum(c => c.HR_JUMLAH);
det.Jun = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 6).Sum(c => c.HR_JUMLAH);
det.Julai = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 7).Sum(c => c.HR_JUMLAH);
det.Ogos = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 8).Sum(c => c.HR_JUMLAH);
det.September = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 9).Sum(c => c.HR_JUMLAH);
det.Oktober = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 10).Sum(c => c.HR_JUMLAH);
det.November = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 11).Sum(c => c.HR_JUMLAH);
det.Disember = elaunlain
.Where(c => c.HR_BULAN_BEKERJA == 12).Sum(c => c.HR_JUMLAH);
det.JumlahGaji = elaunlain.Sum(c => c.HR_JUMLAH);
det.MinBulan = startMonth;
det.MaxBulan = endMonth;
int totalBulan = endMonth - startMonth + 1;
if (totalBulan > 0)
{
det.GajiPurata = det.JumlahGaji == null ?
0 : decimal.Round(Convert.ToDecimal(det.JumlahGaji) / totalBulan, 3);
}
det.IsMuktamad = false;
outputList.Add(det);
}
//sort by Nama
outputList = outputList.OrderBy(x => x.Nama).ToList();
return(outputList);
}