private void button2_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(annualTxt.Text))
{
MessageBox.Show("", "Please input the annual projection value !", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
string test = "";
try
{
test = Annual.List("SELECT * from budget WHERE year='" + yearCbx.Text + "' AND weekly = '" + weeklyTxt.Text + "'").First().Annuals.ToString();
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
if (!string.IsNullOrEmpty(test))
{
MessageBox.Show("Value inserted !");
return;
}
string ID = Guid.NewGuid().ToString();
Annual i = new Annual(ID, yearCbx.Text, Convert.ToDouble(annualTxt.Text), Convert.ToDouble(weeklyTxt.Text));
DBConnect.Insert(i);
MessageBox.Show("Information Saved");
annualTxt.Text = "";
weeklyTxt.Text = "";
}
private void button6_Click(object sender, EventArgs e)
{
if (nameTxt.Text == "")
{
nameTxt.BackColor = Color.Red;
return;
}
MemoryStream stream = ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg);
string fullimage = ImageToBase64(stream);
if (!string.IsNullOrEmpty(existingID))
{
if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
Staff j = new Staff(existingID, nameTxt.Text, contactTxt.Text, departmentCbx.Text, fullimage, emailTxt.Text);
DBConnect.Update(j, existingID);
existingID = "";
return;
}
}
existingID = "";
string id = Guid.NewGuid().ToString();
Staff _user = new Staff(id, nameTxt.Text, contactTxt.Text, departmentCbx.Text, fullimage, emailTxt.Text);
if (DBConnect.Insert(_user) != "")
{
MessageBox.Show("Information Saved");
this.DialogResult = DialogResult.OK;
this.Dispose();
}
}
private void button4_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(amountTxt.Text))
{
amountTxt.BackColor = Color.Red;
return;
}
if (string.IsNullOrEmpty(itemTxt.Text))
{
itemTxt.BackColor = Color.Red;
return;
}
if (!string.IsNullOrEmpty(existingID))
{
if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
Expense j = new Expense(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, categoryTxt.Text, Convert.ToDouble(amountTxt.Text), month);
DBConnect.Update(j, existingID);
existingID = "";
}
return;
}
existingID = "";
string ID = Guid.NewGuid().ToString();
Expense i = new Expense(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, categoryTxt.Text, Convert.ToDouble(amountTxt.Text), month);
DBConnect.Insert(i);
MessageBox.Show("Information Saved ");
itemTxt.Text = "";
amountTxt.Text = "";
autocompleteCategory();
autocomplete();
}
private void button4_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(endTxt.Text))
{
endTxt.BackColor = Color.Red;
return;
}
if (string.IsNullOrEmpty(categoryTxt.Text))
{
categoryTxt.BackColor = Color.Red;
return;
}
if (!string.IsNullOrEmpty(existingID))
{
if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
Cogs j = new Cogs(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, categoryTxt.Text, Convert.ToDouble(begTxt.Text), Convert.ToDouble(endTxt.Text), Convert.ToDouble(cogsTxt.Text), month);
DBConnect.Update(j, existingID);
existingID = "";
return;
}
else
{
return;
}
}
existingID = "";
string ID = Guid.NewGuid().ToString();
Cogs i = new Cogs(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, categoryTxt.Text, Convert.ToDouble(begTxt.Text), Convert.ToDouble(endTxt.Text), Convert.ToDouble(cogsTxt.Text), month);
DBConnect.Insert(i);
MessageBox.Show("Information Saved ");
categoryTxt.Text = "";
endTxt.Text = "";
}
private void button4_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(amountTxt.Text))
{
amountTxt.BackColor = Color.Red;
return;
}
if (string.IsNullOrEmpty(itemTxt.Text))
{
itemTxt.BackColor = Color.Red;
return;
}
if (!string.IsNullOrEmpty(existingID))
{
if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
Sale j = new Sale(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, Convert.ToDouble(amountTxt.Text), itemTxt.Text, month);
DBConnect.Update(j, existingID);
existingID = "";
return;
}
else
{
return;
}
}
else
{
string test = "";
try
{
test = Sale.List("SELECT * from sale WHERE item='" + itemTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Amount.ToString();
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
if (!string.IsNullOrEmpty(test))
{
MessageBox.Show("Value seems to be inserted already");
return;
}
Helper.CurrentWeek = Convert.ToInt32(weekLbl.Text);
Helper.CurrentStarting = startLbl.Text;
Helper.CurrentEnding = endLbl.Text;
existingID = "";
string ID = Guid.NewGuid().ToString();
Sale i = new Sale(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, Convert.ToDouble(amountTxt.Text), itemTxt.Text, month);
DBConnect.Insert(i);
MessageBox.Show("Information Saved ");
itemTxt.Text = "";
amountTxt.Text = "";
}
}
private void button4_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(annualTxt.Text))
{
annualTxt.BackColor = Color.Red;
return;
}
string ID = Guid.NewGuid().ToString();
Salary i = new Salary(ID, staffTxt.Text, departmentCbx.Text, categoryCbx.Text, Convert.ToDouble(annualTxt.Text), Convert.ToDouble(weeklyTxt.Text), Convert.ToDouble(biweeklyTxt.Text), Convert.ToDouble(0), Convert.ToDouble(ourTxt.Text));
DBConnect.Insert(i);
MessageBox.Show("Information Saved ");
annualTxt.Text = "";
autocompleteDepartment();
}
private void button7_Click(object sender, EventArgs e)
{
if (Helper.UserLevel < 3)
{
MessageBox.Show("Access Denied !");
return;
}
if (nameTxt.Text == "")
{
nameTxt.BackColor = Color.Red;
return;
}
if (levelCbx.Text == "")
{
levelCbx.BackColor = Color.Red;
return;
}
if (contactTxt.Text == "")
{
contactTxt.BackColor = Color.Red;
return;
}
if (pass2Txt.Text != passTxt.Text)
{
MessageBox.Show("Passwords do not match");
passTxt.BackColor = Color.Red;
return;
}
if (String.IsNullOrEmpty(passTxt.Text))
{
passTxt.BackColor = Color.Red;
return;
}
MemoryStream stream = Helper.ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg);
string fullimage = Helper.ImageToBase64(stream);
string id = Guid.NewGuid().ToString();
User _user = new User(id, nameTxt.Text, contactTxt.Text, Helper.MD5Hash(pass2Txt.Text), fullimage, Convert.ToInt32(levelCbx.Text));
if (DBConnect.Insert(_user) != "")
{
MessageBox.Show("Information Saved");
this.DialogResult = DialogResult.OK;
this.Dispose();
}
}
private void button2_Click_1(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(pctTxt.Text))
{
pctTxt.BackColor = Color.Red;
return;
}
if (string.IsNullOrEmpty(itemTxt.Text))
{
itemTxt.BackColor = Color.Red;
return;
}
string ID = Guid.NewGuid().ToString();
Budget i = new Budget(ID, itemTxt.Text, CategoryCbx.Text, CategoryCbx.Text, Convert.ToDouble(pctTxt.Text), 0, yearTxt.Text);
DBConnect.Insert(i);
MessageBox.Show("Information Saved ");
pctTxt.Text = "";
}
private void button2_Click(object sender, EventArgs e)
{
if (nameTxt.Text == "")
{
nameTxt.BackColor = Color.Red;
return;
}
MemoryStream stream = Helper.ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg);
string fullimage = Helper.ImageToBase64(stream);
string id = Guid.NewGuid().ToString();
Company _company = new Company(id, nameTxt.Text, addressTxt.Text, fullimage, currentTxt.Text);
if (DBConnect.Insert(_company) != "")
{
MessageBox.Show("Information Saved");
this.DialogResult = DialogResult.OK;
this.Dispose();
}
}
private void button5_Click(object sender, EventArgs e)
{
if (startHrTxt.Text == "" || endHrTxt.Text == "")
{
MessageBox.Show("Please input the start time and end time for the meeting /schedule ");
return;
}
string ID = Guid.NewGuid().ToString();
var start = Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd") + "T" + this.startHrTxt.Text + ":" + startMinTxt.Text + ":00";
var end = Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd") + "T" + this.endHrTxt.Text + ":" + endMinTxt.Text + ":00";
string priority = "Medium";
if (!String.IsNullOrEmpty(priorityCbx.Text))
{
priority = priorityCbx.Text;
}
Events _event = new Events(ID, Helper.CleanString(this.detailsTxt.Text), start, end, "", "", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "", "due", "", Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd"), "", priority, DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "f", "", "", "", "", "0", "");
DBConnect.Insert(_event);
MessageBox.Show("Information saved" + start + " to" + end);
}
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 { }
}
}