private void button2_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE test;");
int rates = 0;
string[] filebox = new string[] { @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-5K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-8K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-10K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-12K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-15K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-18K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-20K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-25K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-24-30K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-5K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-8K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-10K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-12K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-15K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-18K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-20K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-25K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-36-30K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-5K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-8K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-10K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-12K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-15K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-18K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-20K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-25K.csv", @"R:\Daily Work Folders\Ratebooks\Arval\Current\3-48-30K.csv" };
foreach (string fil in filebox)
{
var dt = GetArvalTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\arval_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\arval_temp.csv", "test");
rates += dt.Rows.Count;
}
}
System.Windows.Forms.MessageBox.Show("The Ratebooks were uploaded successfully with " + rates + " lines.");
Arval f2 = new Arval();
f2.ShowDialog();
this.Hide();
}
private void button3_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE test_lex;");
string[] filebox = new string[] { @"R:\Daily Work Folders\Ratebooks\Lex\Current\CH 24.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CH 36.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CH 48.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CH 60.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CHNM 24.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CHNM 36.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CHNM 48.csv", @"R:\Daily Work Folders\Ratebooks\Lex\Current\CHNM 60.csv" };
int rates = 0;
foreach (string fil in filebox)
{
var dt = GetLexTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\lex_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\lex_temp.csv", "test_lex");
rates += dt.Rows.Count;
}
label1.Text = rates + " vehicles added.";
}
System.Windows.Forms.MessageBox.Show("The Ratebooks were uploaded successfully with " + rates + " lines.");
this.Hide();
Form2 f2 = new Form2();
f2.ShowDialog();
}
private void button1_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE test;");
int rates = 0;
Form1 f1 = new Form1();
string drive = f1.GetDriveLocation();
string[] filebox = new string[] { @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 8k nm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 8k wm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 5k nm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 5k wm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 24 nm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 24 wm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 36 nm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 36 wm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 48 nm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD 48 wm.csv", @"R:\Daily Work Folders\Ratebooks\ALD\Current\ALD LCV wm.csv" };
foreach (string fil in filebox)
{
var dt = GetALDTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\ald_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\ald_temp.csv", "test");
rates += dt.Rows.Count;
}
}
System.Windows.Forms.MessageBox.Show("The Ratebooks were uploaded successfully with " + rates + " lines.");
this.Hide();
ALD f2 = new ALD();
f2.ShowDialog();
}
private void button4_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE test;");
//, @"R:\Daily Work Folders\Ratebooks\Leaseplan\Current\Contract Hire_8k.csv"
string[] filebox = new string[] { @"R:\Daily Work Folders\Ratebooks\Leaseplan\Current\Contract Hire.csv", @"R:\Daily Work Folders\Ratebooks\Leaseplan\Current\Contract Hire_8k.csv" };
int rates = 0;
foreach (string fil in filebox)
{
var dt = GetLeaseplanTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\leaseplan_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\leaseplan_temp.csv", "test");
rates += dt.Rows.Count;
}
}
System.Windows.Forms.MessageBox.Show("The Ratebooks were uploaded successfully with " + rates + " lines.");
this.Hide();
Leaseplan l1 = new Leaseplan();
l1.ShowDialog();
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
string[] queries = new string[] { "DELETE FROM leases WHERE lease_funder_id = 5;", "INSERT INTO leases (`lease_cap_id`, `lease_term`, `lease_mileage_annual`, `lease_monthly_cost`, `lease_maintenance`, `lease_ppm`, `lease_ppmm`, `lease_funder_id`, `lease_vat`, `lease_datetime`) SELECT t.`test_cap_id`, t.`test_term`, t.`test_mileage_annual`/(t.`test_term`/12), t.`test_monthly_cost`, case when t.`test_maintenance` > 0 then t.`test_maintenance` else 0 end as test_maintenance, case when t.`test_maintenance` > 0 then 0 else 0.01 * t.`test_ppm` end as test_ppm, case when t.`test_maintenance` > 0 then 0.01 * t.`test_ppm` else 0 end as test_ppmm, t.`test_funder`, 0, now() FROM test t WHERE t.`test_cap_id` > 0 and t.test_funder = 5; ", "UPDATE leases SET lease_funder_id = 25 WHERE lease_funder_id = 5;", "INSERT INTO leases (`lease_funder_id`,`lease_cap_id`,`lease_term`, `lease_mileage_annual`, `lease_initial_rental`, `lease_monthly_cost`, `lease_maintenance`, `lease_ppm`, `lease_ppmm`, `lease_datetime`) select 5, l.`lease_cap_id`, l.`lease_term`, l.`lease_mileage_annual`, l.`lease_initial_rental`, max(l.`lease_monthly_cost`), max(l.`lease_maintenance`), max(l.`lease_ppm`), max(l.`lease_ppmm`), l.`lease_datetime` from leases l where l.`lease_funder_id` = 25 GROUP BY l.`lease_cap_id`, l.`lease_term`, l.`lease_mileage_annual`;", "DELETE FROM leases WHERE lease_funder_id = 25;" };
SynergyUtilities syn = new SynergyUtilities();
for (int i = 0; i < queries.Length; i++)
{
syn.UploadToDatabase(queries[i]);
backgroundWorker1.ReportProgress(100 * (i + 1) / queries.Length);
}
}
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
string[] queries = new string[] { "DELETE FROM leases WHERE lease_funder_id = 9;", "INSERT INTO leases (`lease_cap_id`, `lease_term`, `lease_mileage_annual`, `lease_monthly_cost`, `lease_maintenance`, `lease_ppm`, `lease_ppmm`, `lease_funder_id`, `lease_vat`, `lease_datetime`) select convert(mid(tl.`test_cap_id`,locate('/',tl.`test_cap_id`)-5,5), signed integer) as test_cap_id,tl.`test_term`, tl.`test_mileage_annual`, round(tl.`test_monthly_cost`*(tl.`test_term`+5)/(tl.`test_term`+2),2), case when tl.`test_maintenance` > 0 then tl.`test_maintenance` else 0 end as test_maintenance, case when tl.`test_maintenance` > 0 then 0 else 0 end as test_ppm, case when tl.`test_maintenance` > 0 then 0 else 0 end as test_ppmm, tl.`test_funder`, 0, now() from test_lex tl WHERE tl.test_funder = 9;", "DROP TABLE IF EXISTS lex5k_10k;", "CREATE TABLE lex5k_10k AS SELECT l.`lease_cap_id`,l.`lease_term`, max(case when l.`lease_mileage_annual` = 5000 then l.`lease_monthly_cost` end) as 5k_price, max(case when l.`lease_mileage_annual` = 10000 then l.`lease_monthly_cost` end) as 10k_price, case when l.`lease_maintenance` > 0 then 1 else 0 end as maintenance_binary, max(case when l.`lease_mileage_annual` = 5000 then l.`lease_maintenance` end) as 5k_main, max(case when l.`lease_mileage_annual` = 10000 then l.`lease_maintenance` end) as 10k_main, l.`lease_ppm`, l.`lease_ppmm` FROM leases l WHERE l.`lease_funder_id` = 9 and (l.`lease_mileage_annual` = 5000 OR l.`lease_mileage_annual` = 10000) GROUP BY l.`lease_cap_id`, l.`lease_term`, `maintenance_binary`;", "INSERT INTO leases (`lease_funder_id`,`lease_cap_id`,`lease_term`, `lease_mileage_annual`, `lease_initial_rental`, `lease_monthly_cost`, `lease_maintenance`, `lease_ppm`, `lease_ppmm`, `lease_datetime`) select 9 as lease_funder_id, l5.`lease_cap_id`, l5.`lease_term`, 8000 as lease_annual_mileage, 3 as lease_initial_rental, round((l5.`10k_price`-l5.`5k_price`)/5000*3000 + l5.`5k_price`,2) as lease_monthly_cost, round((l5.`10k_main`-l5.`5k_main`)/5000*3000 + l5.`5k_main`,2) as lease_maintenance, l5.`lease_ppm`, l5.`lease_ppmm`, NOW() from `lex5k_10k` l5 WHERE l5.`5k_price` IS NOT NULL and l5.`10k_price` IS NOT NULL and l5.`5k_main` IS NOT NULL and l5.`10k_main` IS NOT NULL;", "UPDATE leases SET `lease_funder_id` = 29 WHERE `lease_funder_id` = 9;", "INSERT INTO leases (`lease_funder_id`,`lease_cap_id`,`lease_term`, `lease_mileage_annual`, `lease_initial_rental`, `lease_monthly_cost`, `lease_maintenance`, `lease_ppm`, `lease_ppmm`, `lease_datetime`) select 9, l.`lease_cap_id`, l.`lease_term`, l.`lease_mileage_annual`, l.`lease_initial_rental`, max(l.`lease_monthly_cost`), max(l.`lease_maintenance`), max(l.`lease_ppm`), max(l.`lease_ppmm`), l.`lease_datetime` from leases l where l.`lease_funder_id` = 29 GROUP BY l.`lease_cap_id`, l.`lease_term`, l.`lease_mileage_annual`;", "DELETE FROM leases WHERE lease_funder_id = 29;" };
SynergyUtilities syn = new SynergyUtilities();
for (int i = 0; i < queries.Length; i++)
{
syn.UploadToDatabase(queries[i]);
backgroundWorker1.ReportProgress(100 * (i + 1) / queries.Length);
}
}
private void button6_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE lives;");
string[] filebox = new string[] { @"R:\Daily Work Folders\Website Uploads\WebsiteUpload.csv" };
foreach (string fil in filebox)
{
var dt = GetWebsite(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\website_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\website_temp.csv", "lives");
System.Windows.Forms.MessageBox.Show("The Ratebook " + fil + " was uploaded successfully with " + dt.Rows.Count + " lines.");
}
}
}
private void button5_Click(object sender, EventArgs e)
{
SynergyUtilities syn = new SynergyUtilities();
syn.UploadToDatabase("TRUNCATE test;");
string[] single_nm = new string[] { @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-30k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-30k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-8k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-8k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-30k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-30k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-8k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-8k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-12k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-12k-nmV.csv" };
string[] double_nm = new string[] { @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-1015k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-1015k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-2025k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-2025k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-1015k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-1015k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-2025k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-2025k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-1015k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-1015k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-2025k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-2025k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-1015k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-1015k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-2025k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-2025k-nmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-8k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-30k-nm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-30k-nmV.csv" };
string[] single_wm = new string[] { @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-30k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-30k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-8k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-8k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-30k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-30k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-8k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-8k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-12k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-12k-wmV.csv" };
string[] double_wm = new string[] { @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-1015k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-1015k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-2025k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-24-2025k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-1015k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-1015k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-2025k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-36-2025k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-1015k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-1015k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-2025k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-48-2025k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-1015k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-1015k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-2025k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-60-2025k-wmV.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-8k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-30k-wm.csv", @"R:\Daily Work Folders\Ratebooks\Hitachi\Current\Hit-2436-30k-wmV.csv" };
int rates = 0;
foreach (string fil in single_nm)
{
var dt = GetHitachiTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", "test");
rates += dt.Rows.Count;
}
}
foreach (string fil in double_nm)
{
var dt = GetHitachiTable_2(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", "test");
rates += dt.Rows.Count;
}
}
foreach (string fil in single_wm)
{
var dt = GetHitachiMaintainedTable(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", "test");
rates += dt.Rows.Count;
}
}
foreach (string fil in double_wm)
{
var dt = GetHitachiMaintainedTable_2(fil);
if (dt.Rows.Count > 0)
{
var MyCsv = ToCsv(dt);
System.IO.File.WriteAllText(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", MyCsv);
MyMySQLConnector(@"R:\Daily Work Folders\Website Uploads\hitachi_temp.csv", "test");
rates += dt.Rows.Count;
}
}
System.Windows.Forms.MessageBox.Show("The Ratebooks were uploaded successfully with " + rates + " lines.");
this.Hide();
Hitachi f2 = new Hitachi();
f2.ShowDialog();
}