private void categoryTxt_SelectedIndexChanged(object sender, EventArgs e)
{
int prevWeek = Convert.ToInt32(weekLbl.Text) - 1;
try
{
begTxt.Text = Cogs.List("SELECT * from cogs WHERE category = '" + categoryTxt.Text + "'").Where(k => k.Week == prevWeek).First().EndingInventory.ToString();
}
catch
{
begTxt.Text = "0";
try
{
begTxt.Text = Cogs.List("SELECT * from cogs WHERE category='" + categoryTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().BeginningInventory.ToString();
}
catch { }
}
try
{
endTxt.Text = Cogs.List("SELECT * from cogs WHERE category='" + categoryTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().EndingInventory.ToString();
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
try
{
cogsTxt.Text = Cogs.List("SELECT * from cogs WHERE category='" + categoryTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Cost.ToString();
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
try
{
month = Cogs.List("SELECT * from cogs WHERE category='" + categoryTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Month;
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
try
{
existingID = Cogs.List("SELECT * from cogs WHERE category='" + categoryTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString();
}
catch (Exception y)
{
// Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item");
}
}
private void button21_Click(object sender, EventArgs e)
{
reportViewer7.RefreshReport();
Profit r = new Profit();
int week = Convert.ToInt32(Convert.ToInt32(maxWeeksTxt.Text));
string year = Helper.CurrentYear;
string date = "";
pr = new List <Profit>();
double totalPayRoll = 0;
double totalCogs = 0;
double totalComps = 0;
double totalSales = 0;
double totalTaxes = 0;
double totalEquip = 0;
for (int x = 1; x <= week; x++)
{
DateTime startWeek = Helper.FirstDateOfWeek(Convert.ToInt32(Helper.CurrentYear), x - 1);
string ending = startWeek.AddDays(7).ToString("dd-MM-yyyy");
string month = startWeek.AddDays(7).ToString("MMMM");
totalSales = Sale.List("SELECT * FROM sale WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "'").Sum(t => t.Amount);
totalCogs = Cogs.List("SELECT * FROM cogs WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Cost);
totalComps = Comp.List("SELECT * FROM comp WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "'").Sum(t => t.Amount);
double Gross = totalSales - (totalCogs + totalComps);
double totalLabor = Labor.List("SELECT * FROM labor WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
totalTaxes = Taxes.List("SELECT * FROM taxes WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
totalPayRoll = totalTaxes + totalLabor;
double prime = totalCogs + totalComps + totalPayRoll;
double totalSupplies = Supplies.List("SELECT * FROM supplies WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
double totalRepair = Repair.List("SELECT * FROM repair WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
double totalVariable = Expense.List("SELECT * FROM expense WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' AND category<>'Occupancy Expenses'").Sum(t => t.Amount);
double ctrl = totalPayRoll + totalVariable + totalSupplies + totalRepair;
double ctrlProfit = Gross - ctrl;
double occupancyExpense = Expense.List("SELECT * FROM expense WHERE week ='" + x + "' AND category='Occupancy Expenses' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
totalEquip = Equipment.List("SELECT * FROM equipment WHERE week ='" + x + "' AND date= '" + Helper.CurrentYear + "' ").Sum(t => t.Amount);
double restProfit = ctrlProfit - occupancyExpense - totalEquip;
r = new Profit(Helper.CurrentYear, month, x, Convert.ToDateTime(ending).ToString("dd-MMM-yy"), totalSales, totalCogs, totalComps, Gross, totalPayRoll, prime, totalVariable, totalSupplies, totalRepair, ctrl, ctrlProfit, occupancyExpense, totalEquip, restProfit);
pr.Add(r);
}
this.ProfitBindingSource.DataSource = pr;
reportViewer7.RefreshReport();
}
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 { }
}
}