Ejemplo n.º 1
0
 private void itemTxt_Leave(object sender, EventArgs e)
 {
     try
     {
         amountTxt.Text = Taxes.List("SELECT * from taxes WHERE name='" + 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");
     }
     try
     {
         month = Taxes.List("SELECT * from taxes WHERE name='" + itemTxt.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 = Taxes.List("SELECT * from taxes WHERE name='" + itemTxt.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");
     }
 }
Ejemplo n.º 2
0
        private void autocomplete()
        {
            AutoCompleteStringCollection AutoItem = new AutoCompleteStringCollection();

            foreach (Taxes r in Taxes.List("SELECT * from taxes").GroupBy(x => x.Name, (key, group) => group.First()))
            {
                AutoItem.Add(r.Name);
            }
            itemTxt.AutoCompleteMode         = AutoCompleteMode.Suggest;
            itemTxt.AutoCompleteSource       = AutoCompleteSource.CustomSource;
            itemTxt.AutoCompleteCustomSource = AutoItem;
        }
Ejemplo n.º 3
0
        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();
        }
Ejemplo n.º 4
0
        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 { }
            }
        }