void loadKanwilComboBox()
{
Database1Entities db = new Database1Entities();
var q = (from x in db.Pkts
select x.kanwil).OrderBy(x => x).Distinct().ToList();
q.Add("ALL VENDOR");
kanwilComboBox.DataSource = q;
}
void loadPktCombo()
{
Database1Entities db = new Database1Entities();
List <String> listPkt = (from x in db.Pkts
where x.kanwil.ToLower().Contains("jabo") && x.kodePktCabang.Length > 1
select x.kodePktCabang.Contains("CCAS") ? "CCAS" : x.kodePktCabang).Distinct().ToList();
pktComboBox.DataSource = listPkt;
pktComboBox.SelectedIndex = 0;
}
private void SelectFilesButton_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.Multiselect = true;
of.Filter = "Microsoft Excel | *.xls; *xlsx; *xlsm";
files = new List <String>();
if (of.ShowDialog() == DialogResult.OK)
{
collectionTransaksiPkt = new List <List <transaksiPkt> >();
loadForm.ShowSplashScreen();
String [] tempFileNames = of.FileNames;
foreach (var temp in tempFileNames)
{
files.Add(temp);
}
filesList.Items.Clear();
foreach (String temp in files)
{
filesList.Items.Add(temp.Substring(temp.LastIndexOf("\\") + 1, temp.LastIndexOf(".xls") - temp.LastIndexOf("\\") - 1));
}
GC.Collect();
db = new Database1Entities();
var q = (from x in db.Pkts select x).ToList();
int counter = 0;
List <Pkt> tempP = new List <Pkt>();
foreach (string temp in filesList.Items)
{
Console.WriteLine(counter++);
foreach (var temp2 in q)
{
if (temp2.kodePkt == temp)
{
tempP.Add(temp2);
}
}
}
foreach (var temp in tempP)
{
q.Remove(temp);
}
checkSheetDates();
inputIntoCollection();
loadForm.CloseForm();
MessageBox.Show("Done!");
if (MessageBox.Show("Input File?", "Reminder", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
inputDataToDB();
loadListDataYangSudahAda();
loadDataYangBelumAda();
}
}
}
void loadGroupingCombo()
{
Database1Entities db = new Database1Entities();
List <String> listGrouping = new List <String>();
listGrouping.Add("Nasional");
listGrouping.Add("Non Jabo");
listGrouping.AddRange(db.Pkts.Select(x => x.kanwil).OrderBy(x => x).Distinct().ToList());
groupingComboBox.DataSource = listGrouping;
groupingComboBox.SelectedIndex = 0;
}
void loadBulanCombo()
{
Database1Entities db = new Database1Entities();
var q = db.TransaksiAtms.Where(x => ((DateTime)x.tanggal).Year == year).Select(x => ((DateTime)x.tanggal).Month).Distinct().OrderByDescending(x => x).ToList();
foreach (var temp in q)
{
bulanCombo.Items.Add(temp.ToString());
}
bulanCombo.SelectedIndex = 0;
month = Int32.Parse(bulanCombo.SelectedItem.ToString());
}
private void refreshButton_Click(object sender, EventArgs e)
{
int buf;
if (Int32.TryParse(bulanCombo.SelectedText, out buf))
{
month = buf;
}
if (Int32.TryParse(tahunCombo.SelectedText, out buf))
{
year = buf;
}
Database1Entities db = new Database1Entities();
var q = (from x in db.TransaksiAtms
where x.kodePkt == kodePkt && ((DateTime)x.tanggal).Month == month && ((DateTime)x.tanggal).Year == year
select new
{ Rasio = (x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50) == 0 ? 0 : (Double)(x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20) / (x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50) }
).ToList();
rasioChart.Series.Clear();
ChartValues <Double> values = new ChartValues <Double>();
int counter = 1;
List <String> tgl = new List <String>();
foreach (var temp in q)
{
tgl.Add(counter++.ToString());
values.Add(Math.Round((Double)temp.Rasio, 2));
}
for (int a = 0; a < rasioChart.AxisX.Count; a++)
{
rasioChart.AxisX.RemoveAt(a);
}
rasioChart.AxisX.Add(new Axis()
{
Title = "Tanggal",
Labels = tgl,
Separator = new Separator()
{
Step = 1
},
});
rasioChart.Series.Add(
new LineSeries()
{
Values = values,
Title = "Rasio " + month + " - " + year,
DataLabels = true
});
rasioChart.LegendLocation = LegendLocation.Right;
}
void loadTahunCombo()
{
Database1Entities db = new Database1Entities();
var q = db.TransaksiAtms.Select(x => ((DateTime)x.tanggal).Year).Distinct().OrderByDescending(x => x).ToList();
foreach (var temp in q)
{
Console.WriteLine("Tahun: " + temp);
tahunCombo.Items.Add(temp.ToString());
}
tahunCombo.SelectedIndex = 0;
Console.WriteLine("Tahun");
Console.WriteLine(tahunCombo.SelectedItem.ToString());
year = Int32.Parse(tahunCombo.SelectedItem.ToString());
}
private void inputDataCashpointToolStripMenuItem1_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.Filter = Variables.excelFilter;
if (of.ShowDialog() == DialogResult.OK)
{
loadForm.ShowSplashScreen();
Database1Entities db = new Database1Entities();
List <Cashpoint> fromDb = db.Cashpoints.ToList();
DataSet ds = Util.openExcel(of.FileName);
DataTable dt = ds.Tables[0];
List <Cashpoint> fromExcel = new List <Cashpoint>();
foreach (DataRow row in dt.Rows)
{
if (row[0].ToString().Length != 5)
{
continue;
}
fromExcel.Add(new Cashpoint()
{
idCashpoint = row[0].ToString(),
kodePkt = row[1].ToString()
});
}
//
List <Cashpoint> toInput = new List <Cashpoint>();
foreach (var row in fromExcel)
{
var q = (from x in fromDb
where x.idCashpoint == row.idCashpoint
select x).FirstOrDefault();
if (q != null)
{
q.kodePkt = row.kodePkt;
db.SaveChanges();
}
else
{
toInput.Add(row);
}
}
db.Cashpoints.AddRange(toInput);
db.SaveChanges();
loadForm.CloseForm();
}
}
private void gantiPasswordToolStripMenuItem_Click(object sender, EventArgs e)
{
Database1Entities db = new Database1Entities();
if (String.IsNullOrEmpty(db.Passwords.Select(x => x.password1).FirstOrDefault()))
{
var password = db.Passwords.FirstOrDefault();
InputPromptForm ipf2 = new InputPromptForm("New Password", "Input");
if (ipf2.ShowDialog() == DialogResult.OK)
{
if (password != null)
{
password.password1 = BCrypt.Net.BCrypt.HashPassword(ipf2.value);
}
else
{
db.Passwords.Add(new Password()
{
password1 = BCrypt.Net.BCrypt.HashPassword(ipf2.value)
});
}
db.SaveChanges();
MessageBox.Show("Password Changed!");
}
}
else
{
InputPromptForm ipf = new InputPromptForm("Old Password", "Input");
if (ipf.ShowDialog() == DialogResult.OK)
{
String oldPassword = ipf.value;
String hashed = BCrypt.Net.BCrypt.HashPassword(oldPassword);
Console.WriteLine(hashed);
var password = db.Passwords.FirstOrDefault();
if (BCrypt.Net.BCrypt.Verify(oldPassword, password.password1))
{
InputPromptForm ipf2 = new InputPromptForm("New Password", "Input");
if (ipf2.ShowDialog() == DialogResult.OK)
{
password.password1 = BCrypt.Net.BCrypt.HashPassword(ipf2.value);
db.SaveChanges();
MessageBox.Show("Password Changed!");
}
}
}
}
}
private void loadDataVaultToolStripMenuItem_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
of.Filter = Variables.excelFilter;
if (of.ShowDialog() == DialogResult.OK)
{
loadForm.ShowSplashScreen();
String path = of.FileName;
DataSet ds = Util.openExcel(path);
if (ds.Tables.Count == 0)
{
loadForm.CloseForm();
return;
}
DataTable dt = ds.Tables[0];
dt.Rows.RemoveAt(0);
var data = (from DataRow dr in dt.Rows
select new
{
namaPkt = dr[0].ToString(),
bankLain = dr[1].ToString()
}).ToList();
data = data.GroupBy(x => x.namaPkt).Select(x => new { namaPkt = x.Key, bankLain = String.Join(",", x.Select(y => y.bankLain)) }).ToList();
Database1Entities db = new Database1Entities();
foreach (var temp in data)
{
Console.WriteLine(temp.namaPkt + " " + temp.bankLain);
var toEdit = db.Pkts.Where(x => x.namaPkt.Contains(temp.namaPkt)).FirstOrDefault();
if (toEdit != null)
{
Console.WriteLine("TO EDIT: " + toEdit.namaPkt + " " + toEdit.kodePkt);
toEdit.vaultBankLain = temp.bankLain;
}
else
{
Console.WriteLine(temp.namaPkt);
}
}
db.SaveChanges();
loadForm.CloseForm();
}
}
List <StoreClass> loadNewNote50()
{
Database1Entities db = new Database1Entities();
List <StoreClass> result = new List <StoreClass>();
var query = (from x in db.StokPosisis
select x).ToList();
var q2 = (from x in query
where x.tanggal == Variables.todayDate &&
x.denom == "50000"
select x).ToList();
result = q2.Select(x => new StoreClass()
{
kodePkt = x.namaPkt,
tanggal = (DateTime)x.tanggal,
val = (Int64)x.newBaru
}).ToList();
return(result);
}
public PembagianSaldoForm(List <StoreClass> morningBalance100, List <StoreClass> morningBalance50, List <StoreClass> newNote100, List <StoreClass> newNote50, Double persenUnprocessed, List <ApprovalPembagianSaldo> deliveryCabang, List <ApprovalPembagianSaldo> adhocCabang, List <DataPermintaanDanSumber> listDataTambahanSumber, List <DataPermintaanDanSumber> listDataTambahanPermintaan)
{
InitializeComponent();
this.morningbalance100 = morningBalance100;
this.morningbalance50 = morningBalance50;
this.persenUnprocessed = persenUnprocessed;
this.deliveryCabang = deliveryCabang;
this.adhocCabang = adhocCabang;
this.listDataTambahanPermintaan = new ReadOnlyCollection <DataPermintaanDanSumber>(listDataTambahanPermintaan);
this.listDataTambahanSumber = new ReadOnlyCollection <DataPermintaanDanSumber>(listDataTambahanSumber);
Database1Entities db = new Database1Entities();
DateTime maxDateDetailApproval = (DateTime)db.DetailApprovals.Where(x => x.bon100 != -1).Max(x => x.tanggal);
this.morningbalance100 = this.morningbalance100.Where(x => x.tanggal <= maxDateDetailApproval).ToList();
this.morningbalance50 = this.morningbalance50.Where(x => x.tanggal <= maxDateDetailApproval).ToList();
if (newNote100.Any())
{
foreach (var temp in newNote100)
{
var toedit = morningbalance100.Where(x => x.kodePkt == temp.kodePkt && x.tanggal == temp.tanggal).FirstOrDefault();
toedit.val = toedit.val - temp.val;
}
foreach (var temp in newNote50)
{
var toedit = morningbalance50.Where(x => x.kodePkt == temp.kodePkt && x.tanggal == temp.tanggal).FirstOrDefault();
toedit.val = toedit.val - temp.val;
}
this.newNote100 = newNote100;
this.newNote50 = newNote50;
}
else
{
this.newNote100 = loadNewNote100();
this.newNote50 = loadNewNote50();
}
initPembagianGridView();
initListSisaPermintaan();
initListSumber();
}
private void saveBtn_Click(object sender, EventArgs e)
{
Database1Entities db = new Database1Entities();
db.ApprovalPembagianSaldoes.AddRange(deliveryCabang);
db.ApprovalPembagianSaldoes.AddRange(adhocCabang);
var dataInputanUser = LoadDataInputanUser();
var dataInputanUserToInsert = (from x in dataInputanUser
select new ApprovalPembagianSaldo()
{
JenisTransaksi = "Reguler",
D100 = x.d100,
D50 = x.d50,
JenisUang = x.jenisUang,
PktSumber = x.namaPktSumber,
PktTujuan = x.namaPktTujuan,
Tanggal = x.tanggal
}).ToList();
db.ApprovalPembagianSaldoes.AddRange(dataInputanUserToInsert);
db.SaveChanges();
MessageBox.Show("Done!");
}
private void button1_Click(object sender, EventArgs e)
{
en = new Database1Entities();
OpenFileDialog of = new OpenFileDialog();
of.Filter = Variables.excelFilter;
if (of.ShowDialog() == DialogResult.OK)
{
loadForm.ShowSplashScreen();
String filename = of.FileName;
DataSet ds = Util.openExcel(filename);
DataTable dt = ds.Tables[0];
Console.WriteLine(ds.Tables.Count);
var query = (from x in en.OrderTrackings.AsEnumerable()
where ((DateTime)x.timestamp).Date == dateTimePicker1.Value.Date
select x).ToList();
Console.WriteLine("Query Count: " + query.Count);
if (query.Any())
{
var Result = MessageBox.Show("Tanggal " + dateTimePicker1.Value.Date.ToShortDateString() + " sudah ada di database, update?", "", MessageBoxButtons.YesNo);
if (Result == DialogResult.Yes)
{
en.OrderTrackings.RemoveRange(query);
en.SaveChanges();
for (int i = 1; i < dt.Rows.Count; i++)
{
Console.WriteLine(i);
Console.WriteLine(dt.Rows[i][1].ToString());
Console.WriteLine(dt.Rows[i][4].ToString());
String kodePkt = "";
String kodeCabang = "";
DateTime tanggal = new DateTime(1, 1, 1);
Int64 nominalDispute = 0;
DateTime dueDate, timestamp;
String reference_master = "",
sched_id = "",
actn_id = "",
state_blog = "";
var rows = dt.Select("");
if (dt.Rows[i][13].ToString() == null || dt.Rows[i][13].ToString() == "")
{
continue;
}
else
{
if (dt.Rows[i][19].ToString() == null || dt.Rows[i][19].ToString() == "")
{
continue;
}
else
{
if (Convert.ToDateTime(dt.Rows[i][10].ToString()).Date == dateTimePicker1.Value.Date && dt.Rows[i][13].ToString() == "CONFIRMED" && dt.Rows[i][9].ToString() == "2")
{
//Console.WriteLine("cus");
Console.WriteLine("PROCESS ROW [" + i + "]");
Console.WriteLine("nominalDispute: " + dt.Rows[i][19].ToString().Split(':')[1]);
kodePkt = dt.Rows[i][0].ToString();
kodeCabang = dt.Rows[i][2].ToString();
timestamp = Convert.ToDateTime(dt.Rows[i][10].ToString());
DateTime timeLimit = new DateTime(timestamp.Year, timestamp.Month, timestamp.Day, 21, 0, 0);
if (timestamp > timeLimit)
{
tanggal = timestamp.AddDays(1).Date;
}
else
{
tanggal = timestamp;
}
Int64 buf;
if (Int64.TryParse(dt.Rows[i][19].ToString().Split(':')[1].Trim('\"'), out buf))
{
nominalDispute = buf;
}
else
{
MessageBox.Show("Data ROW " + i + " Nominal dispute tidak bisa di parse, akan diskip!");
continue;
}
dueDate = Convert.ToDateTime(dt.Rows[i][4].ToString());
reference_master = dt.Rows[i][7].ToString();
sched_id = dt.Rows[i][8].ToString();
actn_id = dt.Rows[i][9].ToString();
state_blog = dt.Rows[i][14].ToString();
if (state_blog.Contains("|"))
{
state_blog = state_blog.Split('|')[1];
}
}
else
{
Console.WriteLine("sor");
continue;
}
en.OrderTrackings.Add(new OrderTracking()
{
kodePkt = kodePkt,
kodeCabang = kodeCabang,
tanggal = tanggal,
nominalDispute = nominalDispute,
dueDate = dueDate,
timestamp = timestamp,
reference_master = reference_master,
sched_id = sched_id,
actn_id = actn_id,
state_blog = state_blog
});
en.SaveChanges();
}
}
}
MessageBox.Show("data berhasil diupdate");
}
else /*(DialogResult ==DialogResult.No)*/
{
MessageBox.Show("data tidak diupdate");
}
}
else
{
Console.WriteLine("ADD NEW");
for (int i = 1; i < dt.Rows.Count; i++)
{
Console.WriteLine(i);
Console.WriteLine(dt.Rows[i][1].ToString());
Console.WriteLine(dt.Rows[i][4].ToString());
String kodePkt = "";
String kodeCabang = "";
DateTime tanggal = new DateTime(1, 1, 1);
Int64 nominalDispute = 0;
DateTime dueDate, timestamp;
String reference_master = "",
sched_id = "",
actn_id = "",
state_blog = "";
var rows = dt.Select("");
if (dt.Rows[i][13].ToString() == null || dt.Rows[i][13].ToString() == "")
{
Console.WriteLine("ROW [" + i + "][13] IS NULL OR EMPTY");
continue;
}
else
{
if (dt.Rows[i][19].ToString() == null || dt.Rows[i][19].ToString() == "")
{
Console.WriteLine("ROW [" + i + "][19] IS NULL OR EMPTY");
continue;
}
else
{
Console.WriteLine(Convert.ToDateTime(dt.Rows[i][10].ToString()).Date);
Console.WriteLine(dateTimePicker1.Value.Date);
if (Convert.ToDateTime(dt.Rows[i][10].ToString()).Date == dateTimePicker1.Value.Date && dt.Rows[i][13].ToString() == "CONFIRMED" && dt.Rows[i][9].ToString() == "2")
{
Console.WriteLine("PROCESS ROW [" + i + "]");
Console.WriteLine("nominalDispute: " + dt.Rows[i][19].ToString().Split(':')[1]);
kodePkt = dt.Rows[i][0].ToString();
kodeCabang = dt.Rows[i][2].ToString();
timestamp = Convert.ToDateTime(dt.Rows[i][10].ToString());
DateTime timeLimit = new DateTime(timestamp.Year, timestamp.Month, timestamp.Day, 21, 0, 0);
if (timestamp > timeLimit)
{
tanggal = timestamp.AddDays(1).Date;
}
else
{
tanggal = timestamp.Date;
}
Int64 buf;
if (Int64.TryParse(dt.Rows[i][19].ToString().Split(':')[1].Trim('\"'), out buf))
{
nominalDispute = buf;
}
else
{
MessageBox.Show("Data ROW " + i + " Nominal dispute tidak bisa di parse, akan diskip!");
continue;
}
dueDate = Convert.ToDateTime(dt.Rows[i][4].ToString());
reference_master = dt.Rows[i][7].ToString();
sched_id = dt.Rows[i][8].ToString();
actn_id = dt.Rows[i][9].ToString();
state_blog = dt.Rows[i][14].ToString();
if (state_blog.Contains("|"))
{
state_blog = state_blog.Split('|')[1];
}
}
else
{
Console.WriteLine("DATE ROW [" + i + "][10] Tidak Sama");
continue;
}
en.OrderTrackings.Add(new OrderTracking()
{
kodePkt = kodePkt,
kodeCabang = kodeCabang,
tanggal = tanggal,
nominalDispute = nominalDispute,
dueDate = dueDate,
timestamp = timestamp,
reference_master = reference_master,
sched_id = sched_id,
actn_id = actn_id,
state_blog = state_blog
});
en.SaveChanges();
}
}
}
}
loadForm.CloseForm();
}
}
private void getDataButtonClick(object sender, EventArgs e)
{
Database1Entities db = new Database1Entities();
List <String> pkts = (from x in db.Pkts select x.kodePkt).ToList();
pktComboBox.DataSource = pkts;
/**
* load semua file pkt
**/
loadForm lf = new loadForm();
lf.Show();
Application.DoEvents();
FolderBrowserDialog fdb = new FolderBrowserDialog();
String folderPath;
if (fdb.ShowDialog() == DialogResult.OK)
{
folderPath = fdb.SelectedPath;
foreach (String temp in pkts)
{
String filePath = folderPath + "\\" + temp + ".xlsx";
//Console.WriteLine(filePath);
//filePath = Variables.parentFolder + Variables.todayYear + "\\" + Variables.todayMonth + "\\" + Variables.todayDay + "\\" + temp + ".xlsx";
//Console.WriteLine(filePath);
bool found = false;
try
{
Util.openExcel(filePath);
//Reinisialisasi
sheetIndex = 0;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
DataSet data = Util.openExcel(filePath);
ds.Add(data);
List <transaksiPkt> pktSheets = loadDataIntoClass(data);
collectionTransaksiPkt.Add(pktSheets);
List <String> sheetnames = new List <String>();
for (int i = 0; i < data.Tables.Count; i++)
{
sheetnames.Add(data.Tables[i].TableName.ToString());
}
listSheetNames.Add(sheetnames);
//comboBox1.DataSource = sheetnames;
//loadGridView(data);
Util.closeExcel();
found = true;
}
catch (Exception err)
{
}
if (!found)
{
try
{
filePath = folderPath + "\\" + temp + ".xls";
//Console.WriteLine(filePath);
//filePath = Variables.parentFolder + Variables.todayYear + "\\" + Variables.todayMonth + "\\" + Variables.todayDay + "\\" + temp + ".xls";
//Console.WriteLine(filePath);
//Reinisialisasi
sheetIndex = 0;
dataGridView1.Rows.Clear();
dataGridView1.Refresh();
DataSet data = Util.openExcel(filePath);
List <String> sheetnames = new List <String>();
for (int i = 0; i < data.Tables.Count; i++)
{
sheetnames.Add(data.Tables[i].TableName.ToString());
//Console.WriteLine(data.Tables[i].TableName.ToString());
}
listSheetNames.Add(sheetnames);
ds.Add(data);
List <transaksiPkt> pktSheets = loadDataIntoClass(data);
collectionTransaksiPkt.Add(pktSheets);
Util.closeExcel();
found = true;
}
catch (Exception err2)
{
//MessageBox.Show("File " + temp + " tidak ada");
}
}
if (!found)
{
//MessageBox.Show("File " + temp + " tidak ada");
}
}
}
lf.Close();
MessageBox.Show("Done");
comboBox1.DataSource = listSheetNames[0];
loadGridView(ds[0]);
}
public void initListSisaPermintaan()
{
listDataPermintaan = new List <DataPermintaanDanSumber>();
Database1Entities db = new Database1Entities();
var q = (from x in db.Approvals
join y in db.DetailApprovals on x.idApproval equals y.idApproval
join z in db.Pkts on x.kodePkt equals z.kodePkt
where x.tanggal == Variables.todayDate && z.kanwil.ToUpper().Contains("JABO") &&
y.bon100 != -1
select new { z.namaPkt, tanggal = (DateTime)y.tanggal, y.bon100, y.bon50, y.bon20, y.adhoc100, y.adhoc50, y.adhoc20 }).ToList();
//Adhoc
listDataPermintaan.AddRange(
q.Where(x => (DateTime)x.tanggal == Variables.todayDate)
.Select(x => new DataPermintaanDanSumber()
{
tanggal = x.tanggal,
namaPkt = x.namaPkt,
d100 = (Int64)x.adhoc100,
d50 = (Int64)x.adhoc50,
d20 = (Int64)x.adhoc20,
}).ToList());
//Bon Reguler
listDataPermintaan.AddRange(
q.Where(x => (DateTime)x.tanggal > Variables.todayDate)
.Select(x => new DataPermintaanDanSumber()
{
tanggal = x.tanggal,
namaPkt = x.namaPkt,
d100 = (Int64)x.bon100,
d50 = (Int64)x.bon50,
d20 = (Int64)x.bon20
}).ToList());
Console.WriteLine("List Data Sisa Permintaan");
Console.WriteLine("==========================");
List <DataPermintaanDanSumber> temp = new List <DataPermintaanDanSumber>();
foreach (var t in listDataTambahanPermintaan)
{
temp.Add(new DataPermintaanDanSumber()
{
namaPkt = t.namaPkt,
d100 = t.d100,
d50 = t.d50,
d20 = t.d20,
jenisUang = t.jenisUang,
tanggal = t.tanggal
});
}
foreach (var t in temp)
{
Console.WriteLine(t.namaPkt + " " + t.d100 + " " + t.d50 + " " + t.d20);
}
listDataPermintaan.AddRange(temp);
sisaGridView.DataSource = listDataPermintaan.Where(x => x.d100 + x.d50 != 0).OrderBy(x => x.namaPkt).OrderBy(x => x.tanggal).ToList();
sisaGridView.Columns["tanggal"].Width = 70;
sisaGridView.Columns["namaPkt"].Width = 200;
sisaGridView.Columns["d100"].DefaultCellStyle.Format = "N0";
sisaGridView.Columns["d50"].DefaultCellStyle.Format = "N0";
sisaGridView.Columns["d20"].DefaultCellStyle.Format = "N0";
}
public List <String> cekKapasitas()
{
Database1Entities db = new Database1Entities();
Double persenKapasitas = (Double)kapasitasNumeric.Value / 100;
var pcsDeliveryCabang = (from x in deliveryCabang
group x by new { x.Tanggal, x.PktSumber } into g
where g.Key.Tanggal == Variables.todayDate.AddDays(1)
select new {
NamaPkt = g.Key.PktSumber,
value = g.Sum(x => (x.D100 / 100000) + (x.D50 / 50000)),
}).ToList();
var qfit = (from x in db.StokPosisis
group x by new { x.namaPkt, x.tanggal, x.denom } into g
where g.Key.tanggal == Variables.todayDate &&
(g.Key.denom == "100000" || g.Key.denom == "50000")
select new
{
g.Key.namaPkt,
g.Key.tanggal,
g.Key.denom,
FIT = g.Sum(x => x.fitBaru + x.fitLama + x.fitNKRI)
}).ToList();
var fit = (from x in qfit
group x by x.namaPkt into g
select new
{
namaPkt = g.Key,
FIT = g.Sum(x => x.FIT / Int32.Parse(x.denom))
}).ToList();
var qunprocessed = (from x in db.StokPosisis
group x by new { x.namaPkt, x.tanggal, x.denom } into g
where g.Key.tanggal == Variables.todayDate &&
(g.Key.denom == "100000" || g.Key.denom == "50000")
select new
{
g.Key.namaPkt,
g.Key.tanggal,
g.Key.denom,
unprocessed = g.Sum(x => x.unprocessed)
}).ToList();
var unprocessed = (from x in qunprocessed
group x by x.namaPkt into g
select new
{
namaPkt = g.Key,
unprocessed = g.Sum(x => x.unprocessed / Int32.Parse(x.denom)) * persenUnprocessed
}).ToList();
var listDataInputanUser = LoadDataInputanUser();
var dataDariTabelPembagian = (from x in listDataInputanUser
group x by new { x.jenisUang, x.namaPktSumber, x.tanggal } into g
where g.Key.tanggal == Variables.todayDate.AddDays(1) &&
g.Key.jenisUang.ToUpper() == "FIT"
select new {
g.Key.namaPktSumber,
value = g.Sum(x => (x.d100 / 100000) + (x.d50 / 50000))
}).ToList();
var valuePerbandingan = (from x in unprocessed
join y in dataDariTabelPembagian on x.namaPkt equals y.namaPktSumber into xy
from y in xy.DefaultIfEmpty()
join z in fit on x.namaPkt equals z.namaPkt into xz
from z in xz.DefaultIfEmpty()
join zz in pcsDeliveryCabang on x.namaPkt equals zz.NamaPkt into xzz
from zz in xzz.DefaultIfEmpty()
select new {
x.namaPkt,
value = (y == null? 0 :y.value) + (zz == null? 0 : zz.value) - z.FIT - x.unprocessed
}).ToList();
var yangKuning = (from x in valuePerbandingan
join y in db.Pkts on x.namaPkt equals y.namaPkt
where x.value > (y.kapasitasCPC * persenKapasitas)
select x.namaPkt).ToList();
return(yangKuning);
}
public void initPembagianGridView()
{
Database1Entities db = new Database1Entities();
//Nama PKT yang ada di ComboBox
//Nanti ganti data dari master bank tukab
List <String> bankTukabTujuan = listDataTambahanPermintaan.Select(x => x.namaPkt).ToList();
var listNamaPkt = db.Pkts.Where(x => x.kanwil.ToUpper().Contains("JABO") && !x.namaPkt.Contains("Alam Sutera")).Select(x => x.namaPkt).ToList();
listNamaPkt.AddRange(bankTukabTujuan);
DataGridViewComboBoxColumn pktTujuan = new DataGridViewComboBoxColumn()
{
DataSource = listNamaPkt,
HeaderText = "Nama Pkt Tujuan",
ValueType = typeof(String)
};
var listNamaPktSumber = morningbalance100.Select(x => x.kodePkt).Distinct().OrderBy(x => x).ToList();
List <String> bankTukabSumber = listDataTambahanSumber.Select(x => x.namaPkt).ToList();
listNamaPktSumber.AddRange(bankTukabSumber);
DataGridViewComboBoxColumn pktSumber = new DataGridViewComboBoxColumn()
{
DataSource = listNamaPktSumber,
HeaderText = "Nama Pkt Sumber",
ValueType = typeof(String)
};
var listTanggal = db.DetailApprovals.AsEnumerable().Where(x => x.bon100 != -1 && x.tanggal >= Variables.todayDate).Select(x => ((DateTime)x.tanggal).ToShortDateString()).Distinct().ToList();
DataGridViewComboBoxColumn tgl = new DataGridViewComboBoxColumn()
{
DataSource = listTanggal,
HeaderText = "Tanggal",
ValueType = typeof(String)
};
var listJenisUang = new List <String>()
{
"FIT", "NEW NOTE"
};
DataGridViewComboBoxColumn jenisUang = new DataGridViewComboBoxColumn()
{
DataSource = listJenisUang,
HeaderText = "Jenis Uang",
ValueType = typeof(String)
};
this.listNamaPktSumber = listNamaPktSumber;
this.listNamaPkt = listNamaPkt;
pembagianGridView.Columns.Add(tgl);
pembagianGridView.Columns[0].Width = 100;
pembagianGridView.Columns.Add(pktSumber);
pembagianGridView.Columns[1].Width = 200;
pembagianGridView.Columns.Add(pktTujuan);
pembagianGridView.Columns[2].Width = 200;
pembagianGridView.Columns.Add(jenisUang);
pembagianGridView.Columns[3].Width = 70;
pembagianGridView.Columns.Add("100000", "100000");
pembagianGridView.Columns.Add("50000", "50000");
pembagianGridView.Columns.Add("20000", "20000");
pembagianGridView.Columns["100000"].ValueType = typeof(Int64);
pembagianGridView.Columns["100000"].DefaultCellStyle.Format = "N0";
pembagianGridView.Columns["100000"].Width = 120;
pembagianGridView.Columns["50000"].ValueType = typeof(Int64);
pembagianGridView.Columns["50000"].DefaultCellStyle.Format = "N0";
pembagianGridView.Columns["50000"].Width = 120;
pembagianGridView.Columns["20000"].ValueType = typeof(Int64);
pembagianGridView.Columns["20000"].DefaultCellStyle.Format = "N0";
pembagianGridView.Columns["20000"].Width = 120;
}
public initTransaksi()
{
InitializeComponent();
Console.Write(alphabetToIndex('n'));
Database1Entities db = new Database1Entities();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog of = new OpenFileDialog();
if (of.ShowDialog() == DialogResult.OK)
{
Database1Entities db = new Database1Entities();
loadForm.ShowSplashScreen();
DataSet ds = Util.openExcel(of.FileName);
DataTable dt = ds.Tables[0];
DataRow[] rowsToDelete = dt.Select("Column0 is NULL");
foreach (var row in rowsToDelete)
{
dt.Rows.Remove(row);
}
dt.Rows.RemoveAt(0);
int COLINDEX_TANGGAL = 'A' - 'A',
COLINDEX_WSID = 'B' - 'A',
COLINDEX_TOTALTARIKAN = 'C' - 'A';
List <TabelTarikan> dataExcel = new List <TabelTarikan>();
foreach (DataRow row in dt.Rows)
{
dataExcel.Add(new TabelTarikan()
{
tanggal = DateTime.Parse(row[COLINDEX_TANGGAL].ToString()),
wsid = row[COLINDEX_WSID].ToString(),
totalTarikan = Int64.Parse(row[COLINDEX_TOTALTARIKAN].ToString())
});
}
Console.WriteLine("Masuk ke db");
//var toUpdate = (from x in db.TabelTarikans.AsEnumerable()
// join y in dataExcel on new { x.tanggal.Date, x.wsid } equals new { y.tanggal.Date, y.wsid }
// select new TabelTarikan() {
// wsid = x.wsid,
// tanggal = x.tanggal,
// totalTarikan = y.totalTarikan
// }).ToList();
//var datasToDelete = new List<TabelTarikan>(toUpdate);
//foreach(var row in toUpdate)
//{
// Console.WriteLine("Update");
// var temp = db.TabelTarikans.AsEnumerable().Where(x => x.tanggal.Date == row.tanggal.Date && x.wsid == row.wsid).FirstOrDefault();
// if(temp!= null)
// {
// temp.totalTarikan = row.totalTarikan;
// db.SaveChanges();
// }
//}
//foreach (var row in datasToDelete)
//{
// Console.WriteLine("Delete");
// dataExcel.Remove(row);
//}
using (SqlBulkCopy sbq = new SqlBulkCopy(Variables.connectionString))
{
using (var reader = ObjectReader.Create(dataExcel, "tanggal", "wsid", "totalTarikan"))
{
sbq.DestinationTableName = "dbo.TabelTarikan";
sbq.WriteToServer(reader);
}
}
}
loadForm.CloseForm();
}
private void viewButton_Click(object sender, EventArgs e)
{
loadForm.ShowSplashScreen();
DateTime startDate = startDatePicker.Value.Date, endDate = endDatePicker.Value.Date;
String kodePkt = pktComboBox.SelectedItem.ToString();
Console.WriteLine(kodePkt);
GC.Collect();
db = new Database1Entities();
var list = (from x in db.TransaksiAtms.AsEnumerable()
where ((DateTime)x.tanggal).Date >= startDate && ((DateTime)x.tanggal).Date <= endDate &&
x.kodePkt == kodePkt
orderby x.tanggal
select new {
x.tanggal,
x.kodePkt,
x.saldoAwal100,
x.saldoAwal50,
x.saldoAwal20,
x.sislokATM100,
x.sislokATM50,
x.sislokATM20,
x.sislokCRM100,
x.sislokCRM50,
x.sislokCRM20,
x.sislokCDM100,
x.sislokCDM50,
x.sislokCDM20,
x.isiATM100,
x.isiATM50,
x.isiATM20,
x.isiCRM100,
x.isiCRM50,
x.isiCRM20,
x.bon100,
x.bon50,
x.bon20,
x.adhoc100,
x.adhoc50,
x.adhoc20,
x.setor100,
x.setor50,
x.setor20,
x.saldoAkhir100,
x.saldoAkhir50,
x.saldoAkhir20,
rasio100 = x.isiATM100 == 0 ? 0 : (Double)x.saldoAwal100 / (Double)(x.isiATM100 + x.isiCRM100),
rasio50 = x.isiATM50 == 0 ? 0 : (Double)x.saldoAwal50 / (Double)(x.isiATM50 + x.isiCRM50),
rasio20 = x.isiATM20 == 0 ? 0 : (Double)x.saldoAwal20 / (Double)(x.isiATM20 + x.isiCRM20),
rasioGabungan = x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50 == 0 ? 0 : (Double)(x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20) / (Double)(x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50),
}).ToList();
if (kodePkt == "All Vendor")
{
list = (from x in db.TransaksiAtms.AsEnumerable()
where ((DateTime)x.tanggal).Date >= startDate && ((DateTime)x.tanggal).Date <= endDate
orderby x.tanggal ascending
orderby x.kodePkt ascending
select new
{
x.tanggal,
x.kodePkt,
x.saldoAwal100,
x.saldoAwal50,
x.saldoAwal20,
x.sislokATM100,
x.sislokATM50,
x.sislokATM20,
x.sislokCRM100,
x.sislokCRM50,
x.sislokCRM20,
x.sislokCDM100,
x.sislokCDM50,
x.sislokCDM20,
x.isiATM100,
x.isiATM50,
x.isiATM20,
x.isiCRM100,
x.isiCRM50,
x.isiCRM20,
x.bon100,
x.bon50,
x.bon20,
x.adhoc100,
x.adhoc50,
x.adhoc20,
x.setor100,
x.setor50,
x.setor20,
x.saldoAkhir100,
x.saldoAkhir50,
x.saldoAkhir20,
rasio100 = x.isiATM100 == 0 ? 0 : (Double)x.saldoAwal100 / (Double)(x.isiATM100 + x.isiCRM100),
rasio50 = x.isiATM50 == 0 ? 0 : (Double)x.saldoAwal50 / (Double)(x.isiATM50 + x.isiCRM50),
rasio20 = x.isiATM20 == 0 ? 0 : (Double)x.saldoAwal20 / (Double)(x.isiATM20 + x.isiCRM20),
rasioGabungan = x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50 == 0 ? 0 : (Double)(x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20) / (Double)(x.isiATM100 + x.isiATM20 + x.isiATM50 + x.isiCRM100 + x.isiCRM20 + x.isiCRM50),
}).ToList();
}
dataGridView1.DataSource = list;
for (int a = 2; a < dataGridView1.ColumnCount; a++)
{
dataGridView1.Columns[a].DefaultCellStyle.Format = "N0";
}
dataGridView1.Columns[dataGridView1.ColumnCount - 4].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[dataGridView1.ColumnCount - 3].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[dataGridView1.ColumnCount - 2].DefaultCellStyle.Format = "N2";
dataGridView1.Columns[dataGridView1.ColumnCount - 1].DefaultCellStyle.Format = "N2";
loadForm.CloseForm();
}
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");
}
}
List <tanggalValue> loadListRealisasi()
{
List <tanggalValue> res = new List <tanggalValue>();
Database1Entities db = new Database1Entities();
DateTime startDate = calendarSelect.SelectionStart,
endDate = calendarSelect.SelectionEnd;
if (groupingComboBox.SelectedItem.ToString() == "Nasional")
{
res = (from x in db.TransaksiAtms
group x by x.tanggal into g
where g.Key <= endDate && g.Key >= startDate
select new tanggalValue()
{
tanggal = g.Key,
value = (Int64)g.Sum(x => x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20)
}).ToList();
}
else if (groupingComboBox.SelectedItem.ToString() == "Non Jabo")
{
var q = (from x in db.TransaksiAtms
join y in db.Pkts on x.kodePkt equals y.kodePkt
where !y.kanwil.ToUpper().Contains("JABO")
select x
).ToList();
res = (from x in q
group x by x.tanggal into g
where g.Key <= endDate && g.Key >= startDate
select new tanggalValue()
{
tanggal = g.Key,
value = (Int64)g.Sum(x => x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20)
}).ToList();
}
else
{
String kanwil = groupingComboBox.SelectedItem.ToString();
var q = (from x in db.TransaksiAtms
join y in db.Pkts on x.kodePkt equals y.kodePkt
where y.kanwil.ToUpper().Contains(kanwil.ToUpper())
select x
).ToList();
res = (from x in q
group x by x.tanggal into g
where g.Key <= endDate && g.Key >= startDate
select new tanggalValue()
{
tanggal = g.Key,
value = (Int64)g.Sum(x => x.saldoAwal100 + x.saldoAwal50 + x.saldoAwal20)
}).ToList();
}
for (int a = 0; a < res.Count; a++)
{
res[a].tanggal = Variables.todayDate.AddDays(a);
}
Console.WriteLine("List Realisasi\n=================");
foreach (var temp in res)
{
Console.WriteLine(temp.tanggal.ToShortDateString() + " " + temp.value);
}
return(res);
}
bool processTable(DataTable table)
{
Console.WriteLine("Ganti Table: " + table.TableName);
String namaPkt;
String tanggalS;
Console.WriteLine("Column Count: " + table.Columns.Count);
try
{
namaPkt = table.Rows[5][alphabetToNumber('L')].ToString();
tanggalS = table.Rows[8][alphabetToNumber('B')].ToString();
}
catch
{
return(false);
}
DateTime testDate;
if (!DateTime.TryParse(tanggalS, out testDate) || String.IsNullOrEmpty(namaPkt))
{
return(false);
}
DateTime buf, tanggal = new DateTime(1, 1, 1);
if (DateTime.TryParse(tanggalS, out buf))
{
tanggal = buf;
}
//Skip Tanggal
//if (first)
//{
// lastDate = buf;
// first = false;
//}
//else
//{
// if ((buf - lastDate).TotalDays > 1)
// {
// MessageBox.Show(table.TableName + " skip 1 tanggal");
// return false;
// }
// else
// lastDate = buf;
//}
using (Database1Entities db = new Database1Entities())
{
List <StokPosisi> dataDb = (from x in db.StokPosisis.AsEnumerable()
where x.namaPkt == namaPkt &&
((DateTime)x.tanggal).Date == tanggal.Date
select x).ToList();
if (dataDb.Any())
{
return(true);
}
else
{
List <StokPosisi> toInsert = new List <StokPosisi>();
DataRowCollection rows = table.Rows;
for (int a = ROWSTARTKERTAS; a <= ROWENDKERTAS; a++)
{
String unprocessedS = rows[a][COL_UNPROCESSED].ToString(),
newBaruS = rows[a][COL_NEW_BARU].ToString(),
newLamaS = rows[a][COL_NEW_LAMA].ToString(),
fitBaruS = rows[a][COL_FIT_BARU].ToString(),
fitNKRIS = rows[a][COL_FIT_NKRI].ToString(),
fitLamaS = rows[a][COL_FIT_LAMA].ToString(),
passThroughS = rows[a][COL_PASSTROUGH].ToString(),
unfitBaruS = rows[a][COL_UNFIT_BARU].ToString(),
unfitNKRIS = rows[a][COL_UNFIT_NKRI].ToString(),
unfitLamaS = rows[a][COL_UNFIT_LAMA].ToString(),
RRMBaruS = rows[a][COL_RRM_BARU].ToString(),
RRMNKRIS = rows[a][COL_RRM_NKRI].ToString(),
RRMLamaS = rows[a][COL_RRM_LAMA].ToString(),
RupiahRusakMayorS = rows[a][COL_RUPIAH_RUSAK_MAYOR].ToString(),
cekLaporanS = rows[a][COL_CEK_LAPORAN].ToString(),
jenisS = "Kertas",
denomS = rows[a][COL_DENOM].ToString(),
openBalanceS = rows[a][COL_OPEN_BALANCE].ToString(),
inCabangS = rows[a][COL_IN_CABANG].ToString(),
inRetailS = rows[a][COL_IN_RETAIL].ToString(),
inBIS = rows[a][COL_IN_BI].ToString(),
inTUKABS = rows[a][COL_IN_TUKAB].ToString(),
inOtherCPCS = rows[a][COL_IN_OTHER_CPC].ToString(),
inVaultATMS = rows[a][COL_IN_VAULT_ATM].ToString(),
inTukaranS = rows[a][COL_IN_TUKARAN].ToString(),
outCabangS = rows[a][COL_OUT_CABANG].ToString(),
outRetailS = rows[a][COL_OUT_RETAIL].ToString(),
outBIULES = rows[a][COL_OUT_BI_ULE].ToString(),
outBIUTLES = rows[a][COL_OUT_BI_UTLE].ToString(),
outTUKABS = rows[a][COL_OUT_TUKAB].ToString(),
outOtherCPCS = rows[a][COL_OUT_OTHER_CPC].ToString(),
outVaultATMS = rows[a][COL_OUT_VAULT_ATM].ToString(),
outTukaranS = rows[a][COL_OUT_TUKARAN].ToString();
Int64 buff,
unprocessed = 0,
newBaru = 0,
newLama = 0,
fitBaru = 0,
fitNKRI = 0,
fitLama = 0,
passThrough = 0,
unfitBaru = 0,
unfitNKRI = 0,
unfitLama = 0,
RRMBaru = 0,
RRMNKRI = 0,
RRMLama = 0,
RupiahRusakMayor = 0,
cekLaporan = 0,
openBalance = 0,
inCabang = 0,
inRetail = 0,
inBI = 0,
inTUKAB = 0,
inOtherCPC = 0,
inVaultATM = 0,
inTukaran = 0,
outCabang = 0,
outRetail = 0,
outBIULE = 0,
outBIUTLE = 0,
outTUKAB = 0,
outOtherCPC = 0,
outVaultATM = 0,
outTukaran = 0;
if (!String.IsNullOrEmpty(unprocessedS))
{
if (Int64.TryParse(unprocessedS, out buff))
{
unprocessed = buff;
}
}
if (!String.IsNullOrEmpty(newBaruS))
{
if (Int64.TryParse(newBaruS, out buff))
{
newBaru = buff;
}
}
if (!String.IsNullOrEmpty(newLamaS))
{
if (Int64.TryParse(newLamaS, out buff))
{
newLama = buff;
}
}
if (!String.IsNullOrEmpty(fitBaruS))
{
if (Int64.TryParse(fitBaruS, out buff))
{
fitBaru = buff;
}
}
if (!String.IsNullOrEmpty(fitNKRIS))
{
if (Int64.TryParse(fitNKRIS, out buff))
{
fitNKRI = buff;
}
}
if (!String.IsNullOrEmpty(fitLamaS))
{
if (Int64.TryParse(fitLamaS, out buff))
{
fitLama = buff;
}
}
if (!String.IsNullOrEmpty(passThroughS))
{
if (Int64.TryParse(passThroughS, out buff))
{
passThrough = buff;
}
}
if (!String.IsNullOrEmpty(unfitBaruS))
{
if (Int64.TryParse(unfitBaruS, out buff))
{
unfitBaru = buff;
}
}
if (!String.IsNullOrEmpty(unfitNKRIS))
{
if (Int64.TryParse(unfitNKRIS, out buff))
{
unfitNKRI = buff;
}
}
if (!String.IsNullOrEmpty(unfitLamaS))
{
if (Int64.TryParse(unfitLamaS, out buff))
{
unfitLama = buff;
}
}
if (!String.IsNullOrEmpty(RRMBaruS))
{
if (Int64.TryParse(RRMBaruS, out buff))
{
RRMBaru = buff;
}
}
if (!String.IsNullOrEmpty(RRMNKRIS))
{
if (Int64.TryParse(RRMNKRIS, out buff))
{
RRMNKRI = buff;
}
}
if (!String.IsNullOrEmpty(RRMLamaS))
{
if (Int64.TryParse(RRMLamaS, out buff))
{
RRMLama = buff;
}
}
if (!String.IsNullOrEmpty(RupiahRusakMayorS))
{
if (Int64.TryParse(RupiahRusakMayorS, out buff))
{
RupiahRusakMayor = buff;
}
}
if (!String.IsNullOrEmpty(cekLaporanS))
{
if (Int64.TryParse(cekLaporanS, out buff))
{
cekLaporan = buff;
}
}
if (!String.IsNullOrEmpty(openBalanceS))
{
if (Int64.TryParse(openBalanceS, out buff))
{
openBalance = buff;
}
}
if (!String.IsNullOrEmpty(inCabangS))
{
if (Int64.TryParse(inCabangS, out buff))
{
inCabang = buff;
}
}
if (!String.IsNullOrEmpty(inRetailS))
{
if (Int64.TryParse(inRetailS, out buff))
{
inRetail = buff;
}
}
if (!String.IsNullOrEmpty(inBIS))
{
if (Int64.TryParse(inBIS, out buff))
{
inBI = buff;
}
}
if (!String.IsNullOrEmpty(inTUKABS))
{
if (Int64.TryParse(inTUKABS, out buff))
{
inTUKAB = buff;
}
}
if (!String.IsNullOrEmpty(inOtherCPCS))
{
if (Int64.TryParse(inOtherCPCS, out buff))
{
inOtherCPC = buff;
}
}
if (!String.IsNullOrEmpty(inVaultATMS))
{
if (Int64.TryParse(inVaultATMS, out buff))
{
inVaultATM = buff;
}
}
if (!String.IsNullOrEmpty(inTukaranS))
{
if (Int64.TryParse(inTukaranS, out buff))
{
inTukaran = buff;
}
}
if (!String.IsNullOrEmpty(outCabangS))
{
if (Int64.TryParse(outCabangS, out buff))
{
outCabang = buff;
}
}
if (!String.IsNullOrEmpty(outRetailS))
{
if (Int64.TryParse(outRetailS, out buff))
{
outRetail = buff;
}
}
if (!String.IsNullOrEmpty(outBIULES))
{
if (Int64.TryParse(outBIULES, out buff))
{
outBIULE = buff;
}
}
if (!String.IsNullOrEmpty(outBIUTLES))
{
if (Int64.TryParse(outBIUTLES, out buff))
{
outBIUTLE = buff;
}
}
if (!String.IsNullOrEmpty(outTUKABS))
{
if (Int64.TryParse(outTUKABS, out buff))
{
outTUKAB = buff;
}
}
if (!String.IsNullOrEmpty(outOtherCPCS))
{
if (Int64.TryParse(outOtherCPCS, out buff))
{
outOtherCPC = buff;
}
}
if (!String.IsNullOrEmpty(outVaultATMS))
{
if (Int64.TryParse(outVaultATMS, out buff))
{
outVaultATM = buff;
}
}
if (!String.IsNullOrEmpty(outTukaranS))
{
if (Int64.TryParse(outTukaranS, out buff))
{
outTukaran = buff;
}
}
toInsert.Add(new StokPosisi()
{
namaPkt = namaPkt,
tanggal = tanggal,
denom = denomS,
unprocessed = unprocessed,
newBaru = newBaru,
newLama = newLama,
fitBaru = fitBaru,
fitNKRI = fitNKRI,
fitLama = fitLama,
passThrough = passThrough,
unfitBaru = unfitBaru,
unfitNKRI = unfitNKRI,
unfitLama = unfitLama,
RRMBaru = RRMBaru,
RRMNKRI = RRMNKRI,
RRMLama = RRMLama,
RupiahRusakMayor = RupiahRusakMayor,
cekLaporan = cekLaporan,
jenis = jenisS,
openBalance = 0,
inCabang = 0,
inRetail = 0,
inBI = 0,
inTUKAB = 0,
inOtherCPC = 0,
inVaultATM = 0,
inTukaran = 0,
outCabang = 0,
outRetail = 0,
outBIULE = 0,
outBIUTLE = 0,
outOtherCPC = 0,
outTUKAB = 0,
outVaultATM = 0,
outTukaran = 0,
});
var harisebelom = tanggal.AddDays(-1);
var datasebelom = (from x in db.StokPosisis.AsEnumerable()
where x.namaPkt == namaPkt &&
((DateTime)x.tanggal).Date == harisebelom.Date &&
x.denom == denomS &&
x.jenis == jenisS
select x).FirstOrDefault();
if (datasebelom != null)
{
datasebelom.openBalance = openBalance;
//in
datasebelom.inCabang = inCabang;
datasebelom.inRetail = inRetail;
datasebelom.inBI = inBI;
datasebelom.inTUKAB = inTUKAB;
datasebelom.inOtherCPC = inOtherCPC;
datasebelom.inVaultATM = inVaultATM;
datasebelom.inTukaran = inTukaran;
//out
datasebelom.outCabang = outCabang;
datasebelom.outRetail = outRetail;
datasebelom.outBIULE = outBIULE;
datasebelom.outBIUTLE = outBIUTLE;
datasebelom.outTUKAB = outTUKAB;
datasebelom.outOtherCPC = outOtherCPC;
datasebelom.outVaultATM = outVaultATM;
datasebelom.outTukaran = outTukaran;
db.SaveChanges();
}
}
for (int a = ROWSTARTKOIN; a <= ROWENDKOIN; a++)
{
String unprocessedS = rows[a][COL_UNPROCESSED].ToString(),
newBaruS = rows[a][COL_NEW_BARU].ToString(),
newLamaS = rows[a][COL_NEW_LAMA].ToString(),
fitBaruS = rows[a][COL_FIT_BARU].ToString(),
fitNKRIS = rows[a][COL_FIT_NKRI].ToString(),
fitLamaS = rows[a][COL_FIT_LAMA].ToString(),
passThroughS = rows[a][COL_PASSTROUGH].ToString(),
unfitBaruS = rows[a][COL_UNFIT_BARU].ToString(),
unfitNKRIS = rows[a][COL_UNFIT_NKRI].ToString(),
unfitLamaS = rows[a][COL_UNFIT_LAMA].ToString(),
RRMBaruS = rows[a][COL_RRM_BARU].ToString(),
RRMNKRIS = rows[a][COL_RRM_NKRI].ToString(),
RRMLamaS = rows[a][COL_RRM_LAMA].ToString(),
RupiahRusakMayorS = rows[a][COL_RUPIAH_RUSAK_MAYOR].ToString(),
cekLaporanS = rows[a][COL_CEK_LAPORAN].ToString(),
jenisS = "Koin",
denomS = rows[a][COL_DENOM].ToString(),
openBalanceS = rows[a][COL_OPEN_BALANCE].ToString(),
inCabangS = rows[a][COL_IN_CABANG].ToString(),
inRetailS = rows[a][COL_IN_RETAIL].ToString(),
inBIS = rows[a][COL_IN_BI].ToString(),
inTUKABS = rows[a][COL_IN_TUKAB].ToString(),
inOtherCPCS = rows[a][COL_IN_OTHER_CPC].ToString(),
inVaultATMS = rows[a][COL_IN_VAULT_ATM].ToString(),
inTukaranS = rows[a][COL_IN_TUKARAN].ToString(),
outCabangS = rows[a][COL_OUT_CABANG].ToString(),
outRetailS = rows[a][COL_OUT_RETAIL].ToString(),
outBIULES = rows[a][COL_OUT_BI_ULE].ToString(),
outBIUTLES = rows[a][COL_OUT_BI_UTLE].ToString(),
outTUKABS = rows[a][COL_OUT_TUKAB].ToString(),
outOtherCPCS = rows[a][COL_OUT_OTHER_CPC].ToString(),
outVaultATMS = rows[a][COL_OUT_VAULT_ATM].ToString(),
outTukaranS = rows[a][COL_OUT_TUKARAN].ToString();
Int64 buff,
unprocessed = 0,
newBaru = 0,
newLama = 0,
fitBaru = 0,
fitNKRI = 0,
fitLama = 0,
passThrough = 0,
unfitBaru = 0,
unfitNKRI = 0,
unfitLama = 0,
RRMBaru = 0,
RRMNKRI = 0,
RRMLama = 0,
RupiahRusakMayor = 0,
cekLaporan = 0,
openBalance = 0,
inCabang = 0,
inRetail = 0,
inBI = 0,
inTUKAB = 0,
inOtherCPC = 0,
inVaultATM = 0,
inTukaran = 0,
outCabang = 0,
outRetail = 0,
outBIULE = 0,
outBIUTLE = 0,
outTUKAB = 0,
outOtherCPC = 0,
outVaultATM = 0,
outTukaran = 0;
if (!String.IsNullOrEmpty(unprocessedS))
{
if (Int64.TryParse(unprocessedS, out buff))
{
unprocessed = buff;
}
}
if (!String.IsNullOrEmpty(newBaruS))
{
if (Int64.TryParse(newBaruS, out buff))
{
newBaru = buff;
}
}
if (!String.IsNullOrEmpty(newLamaS))
{
if (Int64.TryParse(newLamaS, out buff))
{
newLama = buff;
}
}
if (!String.IsNullOrEmpty(fitBaruS))
{
if (Int64.TryParse(fitBaruS, out buff))
{
fitBaru = buff;
}
}
if (!String.IsNullOrEmpty(fitNKRIS))
{
if (Int64.TryParse(fitNKRIS, out buff))
{
fitNKRI = buff;
}
}
if (!String.IsNullOrEmpty(fitLamaS))
{
if (Int64.TryParse(fitLamaS, out buff))
{
fitLama = buff;
}
}
if (!String.IsNullOrEmpty(passThroughS))
{
if (Int64.TryParse(passThroughS, out buff))
{
passThrough = buff;
}
}
if (!String.IsNullOrEmpty(unfitBaruS))
{
if (Int64.TryParse(unfitBaruS, out buff))
{
unfitBaru = buff;
}
}
if (!String.IsNullOrEmpty(unfitNKRIS))
{
if (Int64.TryParse(unfitNKRIS, out buff))
{
unfitNKRI = buff;
}
}
if (!String.IsNullOrEmpty(unfitLamaS))
{
if (Int64.TryParse(unfitLamaS, out buff))
{
unfitLama = buff;
}
}
if (!String.IsNullOrEmpty(RRMBaruS))
{
if (Int64.TryParse(RRMBaruS, out buff))
{
RRMBaru = buff;
}
}
if (!String.IsNullOrEmpty(RRMNKRIS))
{
if (Int64.TryParse(RRMNKRIS, out buff))
{
RRMNKRI = buff;
}
}
if (!String.IsNullOrEmpty(RRMLamaS))
{
if (Int64.TryParse(RRMLamaS, out buff))
{
RRMLama = buff;
}
}
if (!String.IsNullOrEmpty(RupiahRusakMayorS))
{
if (Int64.TryParse(RupiahRusakMayorS, out buff))
{
RupiahRusakMayor = buff;
}
}
if (!String.IsNullOrEmpty(cekLaporanS))
{
if (Int64.TryParse(cekLaporanS, out buff))
{
cekLaporan = buff;
}
}
if (!String.IsNullOrEmpty(openBalanceS))
{
if (Int64.TryParse(openBalanceS, out buff))
{
openBalance = buff;
}
}
if (!String.IsNullOrEmpty(inCabangS))
{
if (Int64.TryParse(inCabangS, out buff))
{
inCabang = buff;
}
}
if (!String.IsNullOrEmpty(inRetailS))
{
if (Int64.TryParse(inRetailS, out buff))
{
inRetail = buff;
}
}
if (!String.IsNullOrEmpty(inBIS))
{
if (Int64.TryParse(inBIS, out buff))
{
inBI = buff;
}
}
if (!String.IsNullOrEmpty(inTUKABS))
{
if (Int64.TryParse(inTUKABS, out buff))
{
inTUKAB = buff;
}
}
if (!String.IsNullOrEmpty(inOtherCPCS))
{
if (Int64.TryParse(inOtherCPCS, out buff))
{
inOtherCPC = buff;
}
}
if (!String.IsNullOrEmpty(inVaultATMS))
{
if (Int64.TryParse(inVaultATMS, out buff))
{
inVaultATM = buff;
}
}
if (!String.IsNullOrEmpty(inTukaranS))
{
if (Int64.TryParse(inTukaranS, out buff))
{
inTukaran = buff;
}
}
if (!String.IsNullOrEmpty(outCabangS))
{
if (Int64.TryParse(outCabangS, out buff))
{
outCabang = buff;
}
}
if (!String.IsNullOrEmpty(outRetailS))
{
if (Int64.TryParse(outRetailS, out buff))
{
outRetail = buff;
}
}
if (!String.IsNullOrEmpty(outBIULES))
{
if (Int64.TryParse(outBIULES, out buff))
{
outBIULE = buff;
}
}
if (!String.IsNullOrEmpty(outBIUTLES))
{
if (Int64.TryParse(outBIUTLES, out buff))
{
outBIUTLE = buff;
}
}
if (!String.IsNullOrEmpty(outTUKABS))
{
if (Int64.TryParse(outTUKABS, out buff))
{
outTUKAB = buff;
}
}
if (!String.IsNullOrEmpty(outOtherCPCS))
{
if (Int64.TryParse(outOtherCPCS, out buff))
{
outOtherCPC = buff;
}
}
if (!String.IsNullOrEmpty(outVaultATMS))
{
if (Int64.TryParse(outVaultATMS, out buff))
{
outVaultATM = buff;
}
}
if (!String.IsNullOrEmpty(outTukaranS))
{
if (Int64.TryParse(outTukaranS, out buff))
{
outTukaran = buff;
}
}
toInsert.Add(new StokPosisi()
{
namaPkt = namaPkt,
tanggal = tanggal,
denom = denomS,
unprocessed = unprocessed,
newBaru = newBaru,
newLama = newLama,
fitBaru = fitBaru,
fitNKRI = fitNKRI,
fitLama = fitLama,
passThrough = passThrough,
unfitBaru = unfitBaru,
unfitNKRI = unfitNKRI,
unfitLama = unfitLama,
RRMBaru = RRMBaru,
RRMNKRI = RRMNKRI,
RRMLama = RRMLama,
RupiahRusakMayor = RupiahRusakMayor,
cekLaporan = cekLaporan,
jenis = jenisS,
openBalance = 0,
inCabang = 0,
inRetail = 0,
inBI = 0,
inTUKAB = 0,
inOtherCPC = 0,
inVaultATM = 0,
inTukaran = 0,
outCabang = 0,
outRetail = 0,
outBIULE = 0,
outBIUTLE = 0,
outOtherCPC = 0,
outTUKAB = 0,
outVaultATM = 0,
outTukaran = 0,
});
var harisebelom = tanggal.AddDays(-1);
var datasebelom = (from x in db.StokPosisis.AsEnumerable()
where x.namaPkt == namaPkt &&
((DateTime)x.tanggal).Date == harisebelom.Date &&
x.denom == denomS &&
x.jenis == jenisS
select x).FirstOrDefault();
if (datasebelom != null)
{
datasebelom.openBalance = openBalance;
//in
datasebelom.inCabang = inCabang;
datasebelom.inRetail = inRetail;
datasebelom.inBI = inBI;
datasebelom.inTUKAB = inTUKAB;
datasebelom.inOtherCPC = inOtherCPC;
datasebelom.inVaultATM = inVaultATM;
datasebelom.inTukaran = inTukaran;
//out
datasebelom.outCabang = outCabang;
datasebelom.outRetail = outRetail;
datasebelom.outBIULE = outBIULE;
datasebelom.outBIUTLE = outBIUTLE;
datasebelom.outTUKAB = outTUKAB;
datasebelom.outOtherCPC = outOtherCPC;
datasebelom.outVaultATM = outVaultATM;
datasebelom.outTukaran = outTukaran;
db.SaveChanges();
}
}
db.StokPosisis.AddRange(toInsert);
db.SaveChanges();
toInsert.Clear();
toInsert = new List <StokPosisi>();
GC.Collect();
}
}
return(true);
}
private void button1_Click(object sender, EventArgs e)
{
Database1Entities db = new Database1Entities();
DataSet ds = new DataSet();
OpenFileDialog of = new OpenFileDialog();
if (of.ShowDialog() == DialogResult.OK)
{
ds = Util.openExcel(@of.FileName);
}
if (ds.Tables.Count != 0)
{
int counter = 0;
List <Pkt> listPkt = (from x in db.Pkts select x).ToList();
//Pkt newpkt = new Pkt();
//newpkt.kodePkt = ds.Tables[0].Rows[41][1].ToString();
//newpkt.namaPkt= ds.Tables[0].Rows[41][2].ToString();
//newpkt.tipee2e = ds.Tables[0].Rows[41][3].ToString();
//newpkt.koordinator = ds.Tables[0].Rows[41][4].ToString();
//newpkt.kanwil = ds.Tables[0].Rows[41][5].ToString().Replace(" - ", " ");
//newpkt.sentralisasi = ds.Tables[0].Rows[41][6].ToString().Replace(" - ", " ");
//Console.WriteLine(newpkt.kodePkt + " " + newpkt.namaPkt);
//db.Pkts.Add(newpkt);
//db.SaveChanges();
foreach (DataRow row in ds.Tables[0].Rows)
{
Console.WriteLine(counter);
if (counter == 0)
{
counter++; continue; //Skip Header
}
Pkt toEdit = listPkt.Where(x => x.kodePkt == row[1].ToString()).FirstOrDefault();
if (toEdit != null)
{
toEdit.kodePkt = row[1].ToString();
Console.Write(row[1].ToString() + " ");
toEdit.namaPkt = row[4].ToString();
Console.Write(row[4].ToString());
toEdit.e2e = row[5].ToString();
Console.Write(row[5].ToString());
toEdit.koordinator = row[6].ToString();
Console.Write(row[6].ToString());
toEdit.kanwil = row[7].ToString().Replace(" -", "");
Console.Write(row[7].ToString());
toEdit.sentralisasi = row[8].ToString().Replace(" -", "");
Console.WriteLine(row[8].ToString());
toEdit.vendor = row[10].ToString();
Int64 buf, kapasitasCPC = 0;
if (Int64.TryParse(row[11].ToString(), out buf))
{
kapasitasCPC = buf;
}
toEdit.kapasitasCPC = kapasitasCPC;
if (String.IsNullOrEmpty(row[3].ToString()))
{
toEdit.kodePktCabang = "";
}
else
{
toEdit.kodePktCabang = row[3].ToString();
}
if (String.IsNullOrEmpty(row[2].ToString()))
{
toEdit.kodePktATM = "";
}
else
{
toEdit.kodePktATM = row[2].ToString();
}
toEdit.kodeOpti = row[12] == null || String.IsNullOrWhiteSpace(row[12].ToString()) ? "" : row[12].ToString();
counter++;
}
else
{
Pkt newPkt = new Pkt();
newPkt.kodePkt = row[1].ToString();
Console.Write(row[1].ToString() + " ");
newPkt.namaPkt = row[4].ToString();
Console.Write(row[4].ToString());
newPkt.e2e = row[5].ToString();
Console.Write(row[5].ToString());
newPkt.koordinator = row[6].ToString();
Console.Write(row[6].ToString());
newPkt.kanwil = row[7].ToString().Replace(" -", "");
Console.Write(row[7].ToString());
newPkt.sentralisasi = row[8].ToString().Replace(" -", "");
Console.WriteLine(row[8].ToString());
newPkt.vendor = row[10].ToString();
Int64 buf, kapasitasCPC = 0;
if (Int64.TryParse(row[11].ToString(), out buf) && !String.IsNullOrWhiteSpace(row[11].ToString()))
{
kapasitasCPC = buf;
}
newPkt.kapasitasCPC = kapasitasCPC;
if (String.IsNullOrEmpty(row[3].ToString()))
{
newPkt.kodePktCabang = "";
}
else
{
newPkt.kodePktCabang = row[3].ToString();
}
if (String.IsNullOrEmpty(row[2].ToString()))
{
newPkt.kodePktATM = "";
}
else
{
newPkt.kodePktATM = row[2].ToString();
}
newPkt.kodeOpti = row[12] == null || String.IsNullOrWhiteSpace(row[12].ToString())? "" : row[12].ToString();
db.Pkts.Add(newPkt);
counter++;
}
}
db.SaveChanges();
this.Close();
}
}
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();
}