private void selectFileBtn_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.Multiselect = true;
of.Filter = Variables.excelFilter;
if (of.ShowDialog() == DialogResult.OK)
{
loadForm.ShowSplashScreen();
foreach (String path in of.FileNames)
{
collectionTransaksiPkt.Add(loadSheetsIntoClassList(Util.openExcel(path)));
}
foreach (List <transaksiPkt> list in collectionTransaksiPkt)
{
foreach (transaksiPkt pkt in list)
{
db = new Database1Entities();
var q = (from x in db.TransaksiAtms where x.kodePkt == pkt.kodePkt && x.tanggal == pkt.tanggalPengajuan select x).FirstOrDefault();
if (q != null)
{
q.adhoc100 = pkt.penerimaanBonAdhoc[0];
q.adhoc50 = pkt.penerimaanBonAdhoc[1];
q.adhoc20 = pkt.penerimaanBonAdhoc[2];
q.bon100 = pkt.penerimaanBon[0];
q.bon50 = pkt.penerimaanBon[1];
q.bon20 = pkt.penerimaanBon[2];
q.isiATM100 = pkt.pengisianAtm[0];
q.isiATM50 = pkt.pengisianAtm[1];
q.isiATM20 = pkt.pengisianAtm[2];
q.isiCRM100 = pkt.pengisianCrm[0];
q.isiCRM50 = pkt.pengisianCrm[1];
q.isiCRM20 = pkt.pengisianCrm[2];
q.setor100 = pkt.setorUang[0];
q.setor50 = pkt.setorUang[1];
q.setor20 = pkt.setorUang[2];
q.sislokATM100 = pkt.bongkaranAtm[0];
q.sislokATM50 = pkt.bongkaranAtm[1];
q.sislokATM20 = pkt.bongkaranAtm[2];
q.sislokCDM100 = pkt.bongkaranCdm[0];
q.sislokCDM50 = pkt.bongkaranCdm[1];
q.sislokCDM20 = pkt.bongkaranCdm[2];
q.sislokCRM100 = pkt.bongkaranCrm[0];
q.sislokCRM50 = pkt.bongkaranCrm[1];
q.sislokCRM20 = pkt.bongkaranCrm[2];
Console.WriteLine("Saldo Akhir Hitungan 100: " + pkt.saldoAkhirHitungan[0]);
Console.WriteLine("Saldo Akhir Hitungan 50: " + pkt.saldoAkhirHitungan[1]);
Console.WriteLine("Saldo Akhir Hitungan 20: " + pkt.saldoAkhirHitungan[2]);
Int64 selisih100 = pkt.saldoAkhirHitungan[0] - (Int64)q.saldoAkhir100;
Int64 selisih50 = pkt.saldoAkhirHitungan[1] - (Int64)q.saldoAkhir50;
Int64 selisih20 = pkt.saldoAkhirHitungan[2] - (Int64)q.saldoAkhir20;
Console.WriteLine("Selisih 100: " + selisih100);
Console.WriteLine("Selisih 50: " + selisih50);
Console.WriteLine("Selisih 20: " + selisih20);
q.saldoAkhir100 += selisih100;
q.saldoAkhir50 += selisih50;
q.saldoAkhir20 += selisih20;
foreach (var temp in (from x in db.TransaksiAtms where x.kodePkt == pkt.kodePkt && x.tanggal > pkt.tanggalPengajuan select x).ToList())
{
temp.saldoAwal100 += selisih100;
temp.saldoAwal50 += selisih50;
temp.saldoAwal20 += selisih20;
temp.saldoAkhir100 += selisih100;
temp.saldoAkhir50 += selisih50;
temp.saldoAkhir20 += selisih20;
}
}
foreach (var temp2 in pkt.bonAtmYangDisetujui)
{
var query = (from x in db.laporanBons.AsEnumerable()
where ((DateTime)x.tanggal).Date == temp2.tgl.Date &&
x.kodePkt == pkt.kodePkt
select x).FirstOrDefault();
if (query != null)
{
query.C100 = temp2.d100;
query.C50 = temp2.d50;
query.C20 = temp2.d20;
}
else
{
laporanBon newL = new laporanBon();
newL.kodePkt = pkt.kodePkt;
newL.tanggal = temp2.tgl;
newL.C100 = temp2.d100;
newL.C50 = temp2.d50;
newL.C20 = temp2.d20;
db.laporanBons.Add(newL);
}
db.SaveChanges();
}
foreach (var temp2 in pkt.permintaanBon)
{
var query = (from x in db.LaporanPermintaanBons.AsEnumerable()
where ((DateTime)x.tanggal).Date == temp2.tgl &&
x.kodePkt == pkt.kodePkt
select x).FirstOrDefault();
if (query != null)
{
query.C100 = temp2.d100;
query.C50 = temp2.d50;
query.C20 = temp2.d20;
}
else
{
LaporanPermintaanBon newL = new LaporanPermintaanBon();
newL.kodePkt = pkt.kodePkt;
newL.tanggal = temp2.tgl;
newL.C100 = temp2.d100;
newL.C50 = temp2.d50;
newL.C20 = temp2.d20;
db.LaporanPermintaanBons.Add(newL);
}
db.SaveChanges();
}
var q3 = (from x in db.LaporanPermintaanAdhocs.AsEnumerable()
where x.tanggal == pkt.tanggalPengajuan.AddDays(1) && x.kodePkt == pkt.kodePkt
select x).ToList();
if (q3.Any())
{
q3[0].C100 = pkt.permintaanAdhoc[0];
q3[0].C50 = pkt.permintaanAdhoc[1];
q3[0].C20 = pkt.permintaanAdhoc[2];
db.SaveChanges();
}
bool isError = false;
String errMsg = "";
var queryApprovalLaporanBon = (from a in db.Approvals.AsEnumerable()
join da in db.DetailApprovals.AsEnumerable() on a.idApproval equals da.idApproval
join lb in pkt.bonAtmYangDisetujui.AsEnumerable() on((DateTime)da.tanggal).Date equals((DateTime)lb.tgl).Date
where a.kodePkt == pkt.kodePkt && pkt.tanggalPengajuan == ((DateTime)da.tanggal).Date &&
da.bon100 != -1
select new { Approval = a, DetailApproval = da, LaporanBon = lb }).ToList();
if (q.saldoAwal100 != pkt.saldoAwal[0] || q.saldoAwal50 != pkt.saldoAwal[1] || q.saldoAwal20 != pkt.saldoAwal[2])
{
isError = true;
errMsg += "Saldo awal " + q.kodePkt + " " + q.tanggal + "tidak sesuai \n=========================="
+ "\nSaldo Awal 100 DB: " + ((Int64)q.saldoAwal100).ToString("n0") + " Laporan Bon 100: " + ((Int64)pkt.saldoAwal[0]).ToString("n0")
+ "\nSaldo Awal 50 DB: " + ((Int64)q.saldoAwal50).ToString("n0") + " Laporan Bon 50: " + ((Int64)pkt.saldoAwal[1]).ToString("n0")
+ "\nSaldo Awal 20 DB: " + ((Int64)q.saldoAwal20).ToString("n0") + " Laporan Bon 20: " + ((Int64)pkt.saldoAwal[2]).ToString("n0");
}
foreach (var temp in queryApprovalLaporanBon)
{
if (temp.DetailApproval.bon100 != temp.LaporanBon.d100 || temp.DetailApproval.bon50 != temp.LaporanBon.d50 || temp.DetailApproval.bon20 != temp.LaporanBon.d20)
{
isError = true;
errMsg += "\nBon yang disetujui\n==========================" + temp.Approval.kodePkt + "\n"
+ "\nApproval Bon 100: " + ((Int64)temp.DetailApproval.bon100).ToString("n0") + " Laporan Bon 100: " + ((Int64)temp.LaporanBon.d100).ToString("n0")
+ "\nApproval Bon 50: " + ((Int64)temp.DetailApproval.bon50).ToString("n0") + " Laporan Bon 50: " + ((Int64)temp.LaporanBon.d50).ToString("n0")
+ "\nApproval Bon 20: " + ((Int64)temp.DetailApproval.bon20).ToString("n0") + " Laporan Bon 20: " + ((Int64)temp.LaporanBon.d20).ToString("n0");
}
}
if (isError)
{
MessageBox.Show("Ada Kesalahan:\n" + errMsg);
}
db.SaveChanges();
}
}
loadForm.CloseForm();
MessageBox.Show("Done");
}
}
public static void inputDataTransaksiATMToDB(transaksiPkt temp)
{
Database1Entities db = new Database1Entities();
//Skip data yang udah ada
var check = (from x in db.TransaksiAtms where x.kodePkt == temp.kodePkt && x.tanggal == temp.tanggalPengajuan select x).FirstOrDefault();
if (check != null)
{
return;
}
TransaksiAtm newT = new TransaksiAtm();
newT.kodePkt = temp.kodePkt;
newT.tanggal = temp.tanggalPengajuan;
newT.adhoc100 = temp.penerimaanBonAdhoc[0];
newT.adhoc50 = temp.penerimaanBonAdhoc[1];
newT.adhoc20 = temp.penerimaanBonAdhoc[2];
newT.bon100 = temp.penerimaanBon[0];
newT.bon50 = temp.penerimaanBon[1];
newT.bon20 = temp.penerimaanBon[2];
newT.isiATM100 = temp.pengisianAtm[0];
newT.isiATM50 = temp.pengisianAtm[1];
newT.isiATM20 = temp.pengisianAtm[2];
newT.isiCRM100 = temp.pengisianCrm[0];
newT.isiCRM50 = temp.pengisianCrm[1];
newT.isiCRM20 = temp.pengisianCrm[2];
newT.saldoAwal100 = temp.saldoAwalHitungan[0];
newT.saldoAwal50 = temp.saldoAwalHitungan[1];
newT.saldoAwal20 = temp.saldoAwalHitungan[2];
newT.setor100 = temp.setorUang[0];
newT.setor50 = temp.setorUang[1];
newT.setor20 = temp.setorUang[2];
newT.sislokATM100 = temp.bongkaranAtm[0];
newT.sislokATM50 = temp.bongkaranAtm[1];
newT.sislokATM20 = temp.bongkaranAtm[2];
newT.sislokCDM100 = temp.bongkaranCdm[0];
newT.sislokCDM50 = temp.bongkaranCdm[1];
newT.sislokCDM20 = temp.bongkaranCdm[2];
newT.sislokCRM100 = temp.bongkaranCrm[0];
newT.sislokCRM50 = temp.bongkaranCrm[1];
newT.sislokCRM20 = temp.bongkaranCrm[2];
newT.saldoAkhir100 = temp.saldoAkhirHitungan[0];
newT.saldoAkhir50 = temp.saldoAkhirHitungan[1];
newT.saldoAkhir20 = temp.saldoAkhirHitungan[2];
db.TransaksiAtms.Add(newT);
db.SaveChanges();
int counter = 1;
foreach (var temp2 in temp.bonAtmYangDisetujui)
{
var query = (from x in db.laporanBons.AsEnumerable()
where ((DateTime)x.tanggal).Date == temp2.tgl.Date &&
x.kodePkt == temp.kodePkt
select x).FirstOrDefault();
if (query != null)
{
query.C100 = temp2.d100;
query.C50 = temp2.d50;
query.C20 = temp2.d20;
}
else
{
laporanBon newL = new laporanBon();
newL.kodePkt = temp.kodePkt;
newL.tanggal = temp2.tgl;
newL.C100 = temp2.d100;
newL.C50 = temp2.d50;
newL.C20 = temp2.d20;
db.laporanBons.Add(newL);
}
db.SaveChanges();
}
foreach (var temp2 in temp.permintaanBon)
{
var query = (from x in db.LaporanPermintaanBons.AsEnumerable()
where ((DateTime)x.tanggal).Date == temp2.tgl.Date &&
x.kodePkt == temp.kodePkt
select x).FirstOrDefault();
if (query != null)
{
query.C100 = temp2.d100;
query.C50 = temp2.d50;
query.C20 = temp2.d20;
}
else
{
LaporanPermintaanBon newL = new LaporanPermintaanBon();
newL.kodePkt = temp.kodePkt;
newL.tanggal = temp2.tgl;
newL.C100 = temp2.d100;
newL.C50 = temp2.d50;
newL.C20 = temp2.d20;
db.LaporanPermintaanBons.Add(newL);
}
db.SaveChanges();
}
var qLapPermintaanAdhoc = (from x in db.LaporanPermintaanAdhocs.AsEnumerable()
where x.kodePkt == temp.kodePkt &&
x.tanggal == temp.tanggalPengajuan.AddDays(1)
select x).FirstOrDefault();
if (qLapPermintaanAdhoc != null)
{
qLapPermintaanAdhoc.C100 = temp.permintaanAdhoc[0];
qLapPermintaanAdhoc.C50 = temp.permintaanAdhoc[1];
qLapPermintaanAdhoc.C20 = temp.permintaanAdhoc[2];
}
else
{
LaporanPermintaanAdhoc newA = new LaporanPermintaanAdhoc();
newA.kodePkt = temp.kodePkt;
newA.tanggal = temp.tanggalPengajuan.AddDays(1);
newA.C100 = temp.permintaanAdhoc[0];
newA.C50 = temp.permintaanAdhoc[1];
newA.C20 = temp.permintaanAdhoc[2];
db.LaporanPermintaanAdhocs.Add(newA);
}
db.SaveChanges();
}