static public void FuelTank(ArrayList salesLabel) { Label fuelTankPetrol = (salesLabel[0] as Label); Label fuelTankDiesel = (salesLabel[1] as Label); double totalPetrolinTank = 0; double totalDIeselinTank = 0; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "Select LTRUsable from ddPetrol WHERE LTRUsable > 0; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalPetrolinTank = totalPetrolinTank + (reader.GetDouble(0)); } fuelTankPetrol.Content = totalPetrolinTank.ToString(); reader.Close(); sqlite_cmd.CommandText = "Select LTRUsable from ddDiesel WHERE LTRUsable > 0;"; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalDIeselinTank = totalDIeselinTank + reader.GetDouble(0); } fuelTankDiesel.Content = totalDIeselinTank.ToString(); reader.Close(); GlobalFunctions.CloseConnection(); }
static public void OwnerTransactions(ArrayList salesLabel, ArrayList selectedDateRange) { string startRange = selectedDateRange[0].ToString(); string endRange = selectedDateRange[1].ToString(); Label depositLabel = (salesLabel[0] as Label); Label withdrawLabel = (salesLabel[1] as Label); double totalDepost = 0; double totalwithdraw = 0; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "SELECT deposit, withdrawal FROM ownerAmount where date >" + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalDepost = totalDepost + reader.GetDouble(0); totalwithdraw = totalwithdraw + reader.GetDouble(1); } reader.Close(); GlobalFunctions.CloseConnection(); depositLabel.Content = totalDepost.ToString(); withdrawLabel.Content = totalwithdraw.ToString(); }
static private void fuelTankUpdate(string fuelRef, double fuelInTank, string table) { SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "Update " + table + " set LTRUsable = '" + fuelInTank + "' where Reference = '" + fuelRef + "'"; sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); }
static public void creditorUpdate(string table, string name, string amount, DatePicker datepicker) { DateTime dateTime = datepicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "Update " + table + " set amount = '" + amount + "', date = '" + date + "', humanDate = '" + humanDate + "' where creditorName = '" + name + "'"; sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); }
static public void TotalProfit(ArrayList profitLabels, ArrayList dateRange) { string startRange = dateRange[0].ToString(); string endRange = dateRange[1].ToString(); Label totalProfitPetrolLabel = (profitLabels[0] as Label); Label totalProfitDieselLabel = (profitLabels[1] as Label); Label totalExpensesLabel = (profitLabels[2] as Label); Label netProfitLabel = (profitLabels[3] as Label); double PetrolProfit = 0; double DieselProfit = 0; double netProfit = 0; double totalExpenses = 0; double grossProfit = 0; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "SELECT totalPKR, totalcost from petrol where date > " + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { PetrolProfit = PetrolProfit + (reader.GetDouble(0) - reader.GetDouble(1)); } reader.Close(); sqlite_cmd.CommandText = "SELECT totalPKR, totalcost from diesel where date > " + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { DieselProfit = DieselProfit + (reader.GetDouble(0) - reader.GetDouble(1)); } reader.Close(); sqlite_cmd.CommandText = "SELECT total FROM expenses where date > " + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalExpenses = totalExpenses + reader.GetDouble(0); } reader.Close(); grossProfit = PetrolProfit + DieselProfit; netProfit = grossProfit - totalExpenses; totalProfitPetrolLabel.Content = PetrolProfit; totalProfitDieselLabel.Content = DieselProfit; totalExpensesLabel.Content = totalExpenses; netProfitLabel.Content = netProfit; GlobalFunctions.CloseConnection(); }
static public void creditorinsert(string table, string name, string amount, DatePicker datepicker) { DateTime dateTime = datepicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); SqlDataAdapter adapter; string query; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "insert into " + table + " (creditorName,amount,date,humanDate) values ('" + name + "','" + amount + "','" + date + "','" + humanDate + "')"; sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); }
static public void saveExpenseData(object sender, EventArgs e, ArrayList list, DatePicker datepicker) { DateTime dateTime = datepicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); if ((list[list.Count - 1] as TextBox).Text.Length < 1 || (list[list.Count - 1] as TextBox).Text == "0") { foreach (TextBox textbox in list) { textbox.Background = Brushes.Red; } return; } string query; string textboxesValues = ""; foreach (TextBox textBox in list) { double value = 0; if (textBox.Text.Length > 0) { value = double.Parse(textBox.Text); } textboxesValues = textboxesValues + "'" + value + "',"; } SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "insert into expenses (salaries,electricity,maintenance,other,withdraw,total,date,humanDate) values (" + textboxesValues + "" + date + ",'" + humanDate + "')"; sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); TextBox withdrawBox = (list[list.Count - 2] as TextBox); if (withdrawBox.Text.Length > 0) { ownerDepositWithdrawCalculator(withdrawBox, "withdraw", date); } foreach (TextBox textBox in list) { textBox.Text = ""; } }
static public void SalesOverview(ArrayList salesLabel, ArrayList selectedDateRange) { string startRange = selectedDateRange[0].ToString(); string endRange = selectedDateRange[1].ToString(); Label LtrPetrol = (salesLabel[0] as Label); Label PkrPetrol = (salesLabel[1] as Label); Label LtrDiesel = (salesLabel[2] as Label); Label PkrDiesel = (salesLabel[3] as Label); double totalLtrPetrol = 0; double totalPkrPetrol = 0; double totalLtrDiesel = 0; double totalPkrDiesel = 0; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "Select totalPKR, totalLTR from petrol where date >" + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalLtrPetrol = totalLtrPetrol + reader.GetDouble(0); totalPkrPetrol = totalPkrPetrol + reader.GetDouble(1); } reader.Close(); sqlite_cmd.CommandText = "Select totalLTR, totalPKR from diesel where date >" + startRange + " and date < " + endRange + "; "; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalLtrDiesel = totalLtrDiesel + reader.GetDouble(0); totalPkrDiesel = totalPkrDiesel + reader.GetDouble(1); } reader.Close(); GlobalFunctions.CloseConnection(); LtrPetrol.Content = totalLtrPetrol.ToString(); PkrPetrol.Content = totalPkrPetrol.ToString(); LtrDiesel.Content = totalLtrDiesel.ToString(); PkrDiesel.Content = totalPkrDiesel.ToString(); }
static public string getLastEntry(string table, string column) { string date = "0"; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "SELECT " + column + " FROM " + table + " ORDER BY id DESC limit 1"; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { date = reader.GetValue(0).ToString(); } GlobalFunctions.CloseConnection(); return(date); }
static public int InsertEntry(object sender, EventArgs e, string table, Dictionary <string, double> dict, DatePicker datePicker) { DateTime dateTime = datePicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); double dailyProfit = dict["totalPkrs"] - dict["usedFuelCost"]; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "insert into " + table + " (opening1,closing1,opening2,closing2,rate,testing,discount,totalPKR,totalLTR,totalcost,profit,date,humanDate) " + "values ('" + dict["n1opening"] + "','" + dict["n1closing"] + "','" + dict["n2opening"] + "','" + dict["n2closing"] + "'," + "'" + dict["rate"] + "','" + dict["testing"] + "','" + dict["discount"] + "','" + dict["totalPkrs"] + "','" + dict["totalLtrs"] + "','" + dict["usedFuelCost"] + "','" + dailyProfit + "','" + date + "','" + humanDate + "')"; int commandstatus = sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); return(commandstatus); }
static public void FuelDemandDraftEntry(object sender, EventArgs e, ArrayList textBoxes, DatePicker datepicker, string table) { DateTime dateTime = datepicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); foreach (TextBox textBox in textBoxes) { if (textBox.Text.Length < 1 || textBox.Text == "0") { textBox.Background = Brushes.Red; return; } } string Reference = (textBoxes[0] as TextBox).Text; double priceCalculated = 0; double totalPKR = double.Parse((textBoxes[1] as TextBox).Text); double totalLTR = double.Parse((textBoxes[2] as TextBox).Text); priceCalculated = totalPKR / totalLTR; priceCalculated = Math.Round(priceCalculated, 2, MidpointRounding.AwayFromZero); if (priceCalculated < 40) { (textBoxes[1] as TextBox).Background = Brushes.Red; (textBoxes[2] as TextBox).Background = Brushes.Red; return; } SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "insert into " + table + " (Reference,TotalPKR,LTRUsable,TotalLTR,PerLTRPrice,date,humanDate) values ('" + Reference + "','" + totalPKR + "','" + totalLTR + "','" + totalLTR + "','" + priceCalculated + "','" + date + "','" + humanDate + "');"; sqlite_cmd.ExecuteNonQuery(); (textBoxes[0] as TextBox).Text = ""; (textBoxes[1] as TextBox).Text = ""; (textBoxes[2] as TextBox).Text = ""; GlobalFunctions.CloseConnection(); }
static public Dictionary <string, string> creditorGetData(string table) { Dictionary <string, string> d = new Dictionary <string, string>(); SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "SELECT * FROM " + table; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { d.Add(reader.GetValue(1).ToString(), reader.GetValue(2).ToString()); } GlobalFunctions.CloseConnection(); return(d); }
static public Boolean dateFound(DatePicker datePicker, string table) { DateTime dateTime = datePicker.SelectedDate.Value; string date = GlobalFunctions.epochTimeParam(dateTime); SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "select date from " + table + " where date =" + date; reader = sqlite_cmd.ExecuteReader(); if (reader.HasRows) { GlobalFunctions.CloseConnection(); return(true); } GlobalFunctions.CloseConnection(); return(false); }
static private void ownerDepositWithdrawCalculator(TextBox textBox, string status, string date) { string humanDate = GlobalFunctions.epochToDateTime(long.Parse(date)); double total = 0; double deposit = 0; double withdraw = 0; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "Select (total) from ownerAmount"; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { total = reader.GetDouble(0); } if (status == "deposit") { deposit = double.Parse(textBox.Text); } else { withdraw = double.Parse(textBox.Text); } total = (total + deposit) - withdraw; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "insert into ownerAmount (deposit,withdrawal,total,date,humanDate) values ('" + deposit + "','" + withdraw + "','" + total + "','" + date + "','" + humanDate + "')"; sqlite_cmd.ExecuteNonQuery(); GlobalFunctions.CloseConnection(); }
static public double FuelDeduction(double fuelLtr, string table) { double usedFuelPerLTRPrice = 0; double fuelToDeduct = fuelLtr; double fuelInTank = 0; double totalUsableFuel = 0; string fuelRef = ""; SQLiteDataReader reader; SQLiteCommand sqlite_cmd; sqlite_cmd = GlobalFunctions.Connect().CreateCommand(); sqlite_cmd.CommandText = "SELECT LTRUsable FROM " + table + " WHERE LTRUsable > 0;"; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { totalUsableFuel = totalUsableFuel + reader.GetDouble(0); } if (fuelToDeduct > totalUsableFuel) { MessageBox.Show("There is not enough Fuel in Tank"); return(0); } reader.Close(); sqlite_cmd.CommandText = "SELECT Reference,LTRUsable, PerLTRPRice FROM " + table + " WHERE LTRUsable > 0 limit 1;"; reader = sqlite_cmd.ExecuteReader(); while (reader.Read()) { fuelRef = reader.GetValue(0).ToString(); fuelInTank = reader.GetDouble(1); usedFuelPerLTRPrice = reader.GetDouble(2); break; } reader.Close(); if (fuelInTank <= 0) { MessageBox.Show("The Fuel tank is empty!"); return(0); } if (fuelInTank >= fuelToDeduct) { fuelInTank = fuelInTank - fuelToDeduct; fuelTankUpdate(fuelRef, fuelInTank, table); usedFuelTotalCost = usedFuelTotalCost + (usedFuelPerLTRPrice * fuelToDeduct); } else { double difference = fuelInTank - fuelToDeduct; difference = difference * -1; usedFuelTotalCost = usedFuelTotalCost + (usedFuelPerLTRPrice * fuelInTank); fuelInTank = 0; fuelTankUpdate(fuelRef, fuelInTank, table); FuelDeduction(difference, table); } GlobalFunctions.CloseConnection(); reader.Close(); return(usedFuelTotalCost); }