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);
}