private void selectFileButton_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.Filter = Variables.excelFilter;
if (of.ShowDialog() == DialogResult.OK)
{
DataSet ds = Util.openExcel(of.FileName);
if (ds.Tables.Count == 0)
{
return;
}
loadForm.ShowSplashScreen();
DataTable dt = ds.Tables[0];
dt.Rows.RemoveAt(0);
dt.Rows.RemoveAt(0);
dt.Rows.RemoveAt(0);
DataRow[] toRemove = dt.Select("Column2 IS NULL AND Column3 IS NULL");
foreach (var rem in toRemove)
{
dt.Rows.Remove(rem);
}
foreach (DataRow drOutput in dt.Rows)
{
drOutput[2] = drOutput[2].ToString().Replace("\'", "");
drOutput[4] = drOutput[4].ToString().Replace("\'", "");
drOutput[1] = drOutput[1].ToString().Replace(" 12:00:00 AM", "");
//your remaining code
}
Console.WriteLine(dt.Rows[4][1].GetType());
dataGridView1.DataSource = dt;
bool tempFormat = false;
if (dt.Columns.Count > 6)
{
tempFormat = true;
}
var allAbacas = (from x in db.Abacas
select x).ToList();
List <Abaca> toInput = new List <Abaca>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.WriteLine("i = " + i);
if (dt.Rows[i][0].ToString() == "Transaction Date")
{
String kodeNasabah = (String)dt.Rows[i + 2][2];
String namaNasabah = (String)dt.Rows[i + 1][2];
int counter = 4;
while (!dt.Rows[i + counter][0].ToString().Contains("Total Amount"))
{
if (tempFormat)
{
if (dt.Rows[i + counter][6].ToString().ToLower().Contains("delivery"))
{
counter++;
continue;
}
}
Console.WriteLine("Tanggal [" + (i + counter) + "]: " + dt.Rows[i + counter][1].ToString());
DateTime tanggal = DateTime.ParseExact(dt.Rows[i + counter][1].ToString(), "d/M/yyyy", CultureInfo.InvariantCulture);
String vendorCardNo = dt.Rows[i + counter][2].ToString();
String vendorName = dt.Rows[i + counter][3].ToString();
String jobId = dt.Rows[i + counter][4].ToString();
Int64 totalAmount = 0;
if (String.IsNullOrEmpty(dt.Rows[i + counter][5].ToString()) || dt.Rows[i + counter][5].ToString().Trim() == "-")
{
totalAmount = 0;
}
else
{
totalAmount = (Int64)Double.Parse(dt.Rows[i + counter][5].ToString());
}
var checkData = allAbacas.Where(x => x.jobId == jobId && x.tanggal == tanggal).FirstOrDefault();
if (checkData != null)
{
counter++;
continue;
}
Abaca newItem = new Abaca()
{
jobId = jobId,
tanggal = tanggal,
vendorCardNo = vendorCardNo,
vendorName = vendorName,
totalAmount = totalAmount,
CustomerCode = kodeNasabah.TrimStart(':').TrimStart(' ').ToUpper(),
CustomerName = namaNasabah.TrimStart(':').TrimStart(' '),
TotalUangBesar = 0
};
toInput.Add(newItem);
counter++;
}
i += counter;
}
}
db.Abacas.AddRange(toInput);
db.SaveChanges();
loadForm.CloseForm();
MessageBox.Show("Done!");
}
}
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();
}