public void ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
}
else
{
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
}
using (OleDbConnection con = new OleDbConnection(conn))
{
//try
//{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
int h = 1;
//for (int u = 1; u < dtexcel.Columns.Count; u++)
for (int u = 1; u < 53; u++)
{
Console.WriteLine("NEW WEEK :" + h);
DateTime startWeek = Helper.FirstDateOfWeek(Convert.ToInt32(Helper.CurrentYear), h - 1);
foreach (DataRow row in dtexcel.Rows)
{
//MessageBox.Show();
if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString()))
{
if (categoryTxt.Text.Contains("COGS"))
{
if (row[0].ToString().Contains("Beginning"))
{
begs = row[u].ToString();
}
if (row[0].ToString().Contains("Ending"))
{
ends = row[u].ToString();
}
if (row[0].ToString().Contains("Cost") || row[0].ToString().Contains("Total"))
{
cogs = row[u].ToString();
}
Console.WriteLine("this is the line " + row[0].ToString() + " Value" + row[u].ToString());
try
{
existingID = Cogs.List("SELECT * from cogs WHERE category='" + subcategory.Text + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from cogs WHERE id ='" + existingID + "'";
DBConnect.save(Query);
Console.WriteLine("DELETE: " + row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
}
catch
{
existingID = "";
}
if (!string.IsNullOrEmpty(cogs) && cogs != "0")
{
string ID = Guid.NewGuid().ToString();
Cogs i = new Cogs(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), subcategory.Text, Convert.ToDouble(begs), Convert.ToDouble(ends), Convert.ToDouble(cogs), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
}
// Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
if (!row[0].ToString().Contains("Total") && !row[0].ToString().Contains("Week") && !row[0].ToString().Contains("Week:"))
{
if (categoryTxt.Text.Contains("Complimentary"))
{
existingID = "";
try
{
existingID = Comp.List("SELECT * from comp WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from comp WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Comp i = new Comp(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Purchases"))
{
existingID = "";
try
{
existingID = Inventory.List("SELECT * from inventory WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from inventory WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Inventory i = new Inventory(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), subcategory.Text, Convert.ToDouble(row[u].ToString()), 0, 0, 0, startWeek.AddDays(7).ToString("MMMM"), startWeek.AddDays(7).ToString("dd-MM-yyyy"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Sales"))
{
existingID = "";
try
{
existingID = Sale.List("SELECT * from sale WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from sale WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Sale i = new Sale(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Labor"))
{
existingID = "";
try
{
existingID = Labor.List("SELECT * from labor WHERE department='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from labor WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Labor i = new Labor(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Payroll"))
{
existingID = "";
try
{
existingID = Taxes.List("SELECT * from taxes WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from taxes WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Taxes i = new Taxes(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Supplies"))
{
existingID = "";
try
{
existingID = Supplies.List("SELECT * from supplies WHERE supplier='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from supplies WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Supplies i = new Supplies(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Repairs"))
{
existingID = "";
try
{
existingID = Repair.List("SELECT * from repair WHERE supplier='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from repair WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Repair i = new Repair(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
if (categoryTxt.Text.Contains("Equipment"))
{
existingID = "";
try
{
existingID = Equipment.List("SELECT * from equipment WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from equipment WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString()))
{
Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
string ID = Guid.NewGuid().ToString();
try
{
Equipment i = new Equipment(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
catch { }
}
}
if (categoryTxt.Text.Contains("Expenses"))
{
existingID = "";
try
{
existingID = Expense.List("SELECT * from expense WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from expense WHERE id ='" + existingID + "'";
DBConnect.save(Query);
}
catch
{
existingID = "";
}
if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString()))
{
Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
string ID = Guid.NewGuid().ToString();
try
{
Expense i = new Expense(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), subcategory.Text, Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM"));
DBConnect.Insert(i);
}
catch { }
}
}
}
}
}
h++;
}
//}
//catch { }
}
}
public void ReadExcel(string fileName, string fileExt)
{
string conn = string.Empty;
DataTable dtexcel = new DataTable();
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007
}
else
{
conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007
}
using (OleDbConnection con = new OleDbConnection(conn))
{
//try
//{
OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
oleAdpt.Fill(dtexcel); //fill excel data into dataTable
int h = 1;
//for (int u = 1; u < dtexcel.Columns.Count; u++)
for (int u = 1; u < 60; u++)
{
Console.WriteLine("NEW WEEK :" + h);
DateTime startWeek = Helper.FirstDateOfWeek(Convert.ToInt32(Helper.CurrentYear), h - 1);
foreach (DataRow row in dtexcel.Rows)
{
try
{
//MessageBox.Show();
if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString()))
{
// Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
if (!row[0].ToString().Contains("Inventory") && !row[0].ToString().Contains("Total") && !row[0].ToString().Contains("Week"))
{
existingID = "";
try
{
existingID = Inventory.List("SELECT * from inventory WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
string Query = "DELETE from inventory WHERE id ='" + existingID + "'";
DBConnect.save(Query);
Console.WriteLine("DELETE: " + row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
}
catch
{
existingID = "";
}
string ID = Guid.NewGuid().ToString();
Inventory i = new Inventory(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), categoryTxt.Text, Convert.ToDouble(row[u].ToString()), 0, 0, 0, startWeek.AddDays(7).ToString("MMMM"), Convert.ToDateTime(dateTxt.Text).ToString("dd-MM-yyyy"));
DBConnect.Insert(i);
Console.WriteLine("INSERT: " + row[0].ToString() + " " + row[u].ToString().Replace("'", @"\'") + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy"));
}
}
}
catch { }
}
h++;
}
//}
//catch { }
}
}
