private void currentRD_SelectedIndexChanged(object sender, EventArgs e)
{
string tempUnit = "";
string valueFlatNo = selFlatNo.Text;
string valueDate = currentRD.Text;
tempUnit = RetrieveData.Retrieve_Unit(valueFlatNo, valueDate);
currentUnit.Text = tempUnit;
}
private void subMonth_SelectedIndexChanged(object sender, EventArgs e)
{
string mnthValue = subMonth.SelectedItem.ToString();
string currentYear = DateTime.Now.Year.ToString();
RetrieveData retrieveSubsidyRates = new RetrieveData();
Double[] subRateArr = retrieveSubsidyRates.getSubSidyRates(mnthValue, currentYear);
subText.Text = subRateArr[0].ToString();
unsubText.Text = subRateArr[1].ToString();
yearText.Text = currentYear;
}
private void btnUpdate_Click(object sender, EventArgs e)
{
// We use these three SQLite objects:
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteCommand sqlite_cmd1;
SQLiteDataReader sqlite_datareader;
//SQLiteDataReader sqlite_datareader1;
DateTime tempYear;
string calcYear = "";
//string valueMonth = "";
// create a new database connection:
sqlite_conn = new SQLiteConnection("Data Source=C:\\RGRAB\\Application\\GasDB.db;Version=3;New=False;Compress=True;");
// open the connection:
sqlite_conn.Open();
// create a new SQL command:
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd1 = sqlite_conn.CreateCommand();
try
{
Cursor.Current = Cursors.WaitCursor;
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "SELECT gr.Flat_No,gr.Reading_Unit, gr.Reading_Month from Gas_reading gr INNER JOIN (SELECT max(Reading_Unit) as maxunit, Flat_No from Gas_Reading GROUP BY Flat_No) tgr on gr.Flat_No = tgr.Flat_No and gr.Reading_Unit = tgr.maxunit GROUP BY gr.Flat_No";
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
string valueFlatNo = sqlite_datareader.GetString(0);
string valueUnit = sqlite_datareader.GetString(1);
string valueMonth = sqlite_datareader.GetString(2);
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd1.CommandText = "UPDATE Resident_Detail SET Total_Units = '" + valueUnit + "' where Flat_No = '" + valueFlatNo + "';";
// Now lets execute the SQL ;D
sqlite_cmd1.ExecuteNonQuery();
//Code to decide the subsidy status of the resident
int valMonth = Convert.ToDateTime("01-" + valueMonth + "-2011").Month;
string valYear = DateTime.Now.Year.ToString();
if ((valMonth == 3) && (valYear == "2014"))
{
valMonth = 4;
}
string currentMonth = RetrieveData.getMonth(valMonth);
if ((valMonth >= 1) && (valMonth <= 3))
{
tempYear = DateTime.Today.AddYears(-1);
calcYear = tempYear.Year.ToString();
}
else if ((valMonth >= 4) && (valMonth <= 12))
{
calcYear = DateTime.Now.Year.ToString();
}
double baseUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, calcYear, "March"));
double currentUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, valYear, valueMonth));
double diffUnit = RetrieveData.calcConsumedUnit(baseUnit, currentUnit);
if (diffUnit > 64)
{
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd1.CommandText = "UPDATE Resident_Detail SET Subsidy_Status = 'UnSubsidized' where Flat_No = '" + valueFlatNo + "';";
// Now lets execute the SQL ;D
sqlite_cmd1.ExecuteNonQuery();
}
}
Cursor.Current = Cursors.Default;
MessageBox.Show("Total Consumption updated for all Residents");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
// We are ready, now lets cleanup and close our connection:
sqlite_conn.Close();
}
}
private void clkCalculate_Click(object sender, EventArgs e)
{
string valueFlatNo = selFlatNo.Text;
string valueDate = currentRD.Text;
string valueStatus = subStatus.Text;
string calcYear = "";
string rdYear = "";
DateTime tempYear;
double addUnit = 0.0;
double nonUnit = 0.0;
double calcAmount = 0.0;
double valuePenalty = Convert.ToDouble(penaltyText.Text);
double valueSub = 0.0;
double valueunSub = 0.0;
DateTime today = DateTime.Today;
string Today = today.ToString("MM/dd/yyyy"); // As String
DateTime minValue = Convert.ToDateTime(lastRD.Text).Date;
DateTime maxValue = Convert.ToDateTime(currentRD.Text).Date;
string currentYear = DateTime.Now.Year.ToString();
TimeSpan diff = maxValue - minValue;
string span = diff.TotalDays.ToString();
textUsage.Text = span;
string valueMonth = DateTime.Parse(valueDate).Month.ToString();
string valueYear = DateTime.Parse(valueDate).Year.ToString();
int tempMonth = Convert.ToInt32(valueMonth);
int prevMonth1 = 0;
string currentMonth = RetrieveData.getMonth(tempMonth);
//Get previous month from the reading Month
if (tempMonth == 1)
{
prevMonth1 = 12;
tempYear = DateTime.Today.AddYears(-1);
rdYear = tempYear.Year.ToString();
}
else
{
prevMonth1 = tempMonth - 1;
rdYear = DateTime.Now.Year.ToString();
}
string prevMonth = RetrieveData.getMonth(prevMonth1);
RetrieveData retrieveSubsidyRates = new RetrieveData();
Double[] subRateArr = retrieveSubsidyRates.getSubSidyRates(currentMonth, currentYear);
valueSub = subRateArr[0];
valueunSub = subRateArr[1];
//publish the rates on the form
textSubsidyRate.Text = subRateArr[0].ToString();
textNonSubsidyRate.Text = subRateArr[1].ToString();
if ((tempMonth >= 1) && (tempMonth <= 3))
{
tempYear = DateTime.Today.AddYears(-1);
calcYear = tempYear.Year.ToString();
}
else if ((tempMonth >= 4) && (tempMonth <= 12))
{
calcYear = DateTime.Now.Year.ToString();
}
string prevDate = RetrieveData.Retrieve_LastRD(valueFlatNo, prevMonth, rdYear);
//Get baseline units for consumption calculation
double baseUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, calcYear, "March"));
double currentUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, valueYear, currentMonth));
double prevUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, valueYear, prevMonth));
string currUnit = currentUnit.ToString("0.00");
string preUnit = prevUnit.ToString("0.00");
double diffUnit = RetrieveData.calcConsumedUnit(baseUnit, currentUnit);
double usageUnit = currentUnit - prevUnit;
textUnits.Text = usageUnit.ToString("0.00");
double maxUnit = baseUnit + 64;
if (diffUnit > 64 && prevUnit < maxUnit)
{
addUnit = (currentUnit - maxUnit);
nonsubUnits.Text = addUnit.ToString("0.00");
nonUnit = (maxUnit - prevUnit);
subUnits.Text = nonUnit.ToString("0.00");
}
else if (diffUnit > 64 && prevUnit > maxUnit)
{
addUnit = (currentUnit - prevUnit);
nonsubUnits.Text = addUnit.ToString("0.00");
nonUnit = 0.0;
subUnits.Text = nonUnit.ToString("0.00");
}
else if (diffUnit < 64)
{
addUnit = 0.0;
nonsubUnits.Text = Convert.ToString("0.00");
nonUnit = (currentUnit - prevUnit);
subUnits.Text = nonUnit.ToString("0.00");
}
if (valueStatus == "NonSubsidized")
{
nonsubUnits.Text = (addUnit + nonUnit).ToString("0.00");
subUnits.Text = "0.00";
}
calcAmount = RetrieveData.calculateAmount(valueSub, valueunSub, addUnit, nonUnit, valueStatus);
valueAmount.Text = calcAmount.ToString("0.00");
double totalAmount = calcAmount + valuePenalty;
valueTotalAmount.Text = totalAmount.ToString("0.00");
//This code has been commented. This inserted a row in Invoice Detail table for individual bill calculation.
//SQLiteConnection sqlite_conn;
//SQLiteCommand sqlite_cmd;
//// create a new database connection:
//sqlite_conn = new SQLiteConnection("Data Source=C:\\RGRAB\\Application\\GasDB.db;Version=3;New=False;Compress=True;");
//// open the connection:
//sqlite_conn.Open();
//// create a new SQL command:
//sqlite_cmd = sqlite_conn.CreateCommand();
//try
//{
// // First lets build a SQL-Query again:
// //sqlite_cmd.CommandText = "INSERT INTO Invoice_Detail (Flat_No, Reading_Year, Reading_Month, Current_Date, Current_Unit, Last_Date, Last_Unit,Subsidy_Unit, NonSubsidy_Unit, Span,Unit, Invoice_Date, Invoice_Amount) values ('" + valueFlatNo + "','" + currentYear + "', '" + currentMonth + "', '" + valueDate + "', '" + currUnit + "', '" + prevDate + "','" + preUnit + "', '" + subUnits.Text + "', '" + nonsubUnits.Text + "','" + span + "', '" + textUnits.Text + "','" + Today + "','" + valueTotalAmount.Text + "');";
// //Execute the query
// //sqlite_cmd.ExecuteNonQuery();
//}
//catch (Exception ex)
//{
// string errMessage = ex.Message;
// if (errMessage == "columns Flat_No, Reading_Year, Reading_Month are not unique")
// {
// MessageBox.Show("Bill has already been calculated for '"+ valueFlatNo +"'", "Information!", MessageBoxButtons.OK, MessageBoxIcon.Information);
// }
// else
// {
// MessageBox.Show(ex.Message.ToString(), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
// }
//}
//finally
//{
// // We are ready, now lets cleanup and close our connection:
// sqlite_conn.Close();
//}
}
private void clkBatchCalculate_Click(object sender, EventArgs e)
{
string valueMonth = subBatchMonth.Text;
string currentYear = DateTime.Now.Year.ToString();
string calcYear = "";
DateTime tempYear;
string rdYear = "";
int prevMonth = 0;
int currMonth = 0;
double addUnit = 0.0;
double nonUnit = 0.0;
double calcAmount = 0.0;
double valueSub = 0.0;
double valueunSub = 0.0;
DateTime today = DateTime.Today;
string Today = today.ToString("MM/dd/yyyy"); // As String
if (valueMonth == "")
{
MessageBox.Show("Please select the month for Batch Invoicing", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteCommand sqlite_cmd1;
SQLiteDataReader sqlite_datareader;
Cursor.Current = Cursors.WaitCursor;
// create a new database connection:
sqlite_conn = new SQLiteConnection("Data Source=C:\\RGRAB\\Application\\GasDB.db;Version=3;New=False;Compress=True;");
// open the connection:
sqlite_conn.Open();
// create a new SQL command:
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd1 = sqlite_conn.CreateCommand();
try
{
// First lets build a SQL-Query again:
sqlite_cmd.CommandText = "SELECT GR.Flat_No, GR.Reading_Date, GR.Reading_Unit, RD.Subsidy_Status FROM Gas_Reading GR,Resident_Detail RD where GR.Reading_Month = '" + valueMonth + "' and GR.Reading_Year = '" + currentYear + "' and GR.Flat_No = RD.Flat_No;";
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
//code for batch calculate
string valueFlatNo = sqlite_datareader.GetString(0);
string valueDate = sqlite_datareader.GetString(1);
string valueUnit = sqlite_datareader.GetString(2);
string valueStatus = sqlite_datareader.GetString(3);
//calculate the previous month
currMonth = DateTime.Parse(valueDate).Month;
//Get previous month from the reading Month
if (currMonth == 1)
{
prevMonth = 12;
tempYear = DateTime.Today.AddYears(-1);
rdYear = tempYear.Year.ToString();
}
else
{
prevMonth = currMonth - 1;
rdYear = DateTime.Now.Year.ToString();
}
string preMonth = RetrieveData.getMonth(prevMonth);
string currentMonth = RetrieveData.getMonth(currMonth);
if ((currMonth >= 1) && (currMonth <= 3))
{
tempYear = DateTime.Today.AddYears(-1);
calcYear = tempYear.Year.ToString();
}
else if ((currMonth >= 4) && (currMonth <= 12))
{
calcYear = DateTime.Now.Year.ToString();
}
//code to determine the previous month reading date
string prevDate = RetrieveData.Retrieve_LastRD(valueFlatNo, preMonth, rdYear);
DateTime minValue = Convert.ToDateTime(prevDate).Date;
DateTime maxValue = Convert.ToDateTime(valueDate).Date;
TimeSpan diff = maxValue - minValue;
string span = diff.TotalDays.ToString();
double baseUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, calcYear, "March"));
double currentUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, currentYear, currentMonth));
double prevUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, rdYear, preMonth));
RetrieveData retrieveSubsidyRates = new RetrieveData();
Double[] subRateArr = retrieveSubsidyRates.getSubSidyRates(currentMonth, currentYear);
valueSub = subRateArr[0];
valueunSub = subRateArr[1];
double diffUnit = RetrieveData.calcConsumedUnit(baseUnit, currentUnit);
double usageUnit = Math.Round((currentUnit - prevUnit), 2);
double maxUnit = baseUnit + 64;
if (diffUnit > 64 && prevUnit < maxUnit)
{
addUnit = Math.Round((currentUnit - maxUnit), 2);
nonUnit = Math.Round((maxUnit - prevUnit), 2);
}
else if (diffUnit > 64 && prevUnit > maxUnit)
{
addUnit = Math.Round((currentUnit - prevUnit), 2);
nonUnit = 0.0;
}
else if (diffUnit < 64)
{
addUnit = 0.0;
nonUnit = Math.Round((currentUnit - prevUnit), 2);
}
calcAmount = RetrieveData.calculateAmount(valueSub, valueunSub, addUnit, nonUnit, valueStatus);
double totalAmount = Math.Round(calcAmount, 2);
// First lets build a SQL-Query again:
sqlite_cmd1.CommandText = "INSERT INTO Invoice_Detail (Flat_No, Reading_Year, Reading_Month, Current_Date, Current_Unit, Last_Date, Last_Unit,Subsidy_Unit, NonSubsidy_Unit, Span,Unit, Invoice_Date, Invoice_Amount) values ('" + valueFlatNo + "','" + currentYear + "', '" + currentMonth + "', '" + valueDate + "', '" + currentUnit + "', '" + prevDate + "','" + prevUnit + "', '" + nonUnit + "', '" + addUnit + "','" + span + "', '" + usageUnit + "','" + Today + "','" + totalAmount + "');";
//Execute the query
sqlite_cmd1.ExecuteNonQuery();
}
Cursor.Current = Cursors.Default;
MessageBox.Show("Invoice Data calculated successfully for the month of '" + valueMonth + "'", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString(), "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// We are ready, now lets cleanup and close our connection:
sqlite_conn.Close();
}
}
public List <ConsumptionDetail> getConsumption()
{
double usage = 0.00;
string valueMonth = "";
DateTime tempYear;
string calcYear = "";
SQLiteConnection sqlite_conn;
SQLiteCommand sqlite_cmd;
SQLiteDataReader sqlite_datareader;
List <ConsumptionDetail> consumptionList = new List <ConsumptionDetail>();
// create a new database connection:
sqlite_conn = new SQLiteConnection("Data Source=C:\\RGRAB\\Application\\GasDB.db;Version=3;New=False;Compress=True;");
try
{
// open the connection:
sqlite_conn.Open();
// create a new SQL command:
sqlite_cmd = sqlite_conn.CreateCommand();
// First lets build a SQL-Query again:
sqlite_cmd.CommandText = "Select rd.Flat_No, rd.Name, rd.Total_Units, gr.Reading_Month,gr.Reading_Year from Resident_Detail rd, Gas_Reading gr where rd.Flat_No = gr.Flat_No and rd.Total_units = gr.Reading_Unit;";
// Now the SQLiteCommand object can give us a DataReader-Object:
sqlite_datareader = sqlite_cmd.ExecuteReader();
ConsumptionDetail consump = null;
// The SQLiteDataReader allows us to run through the result lines:
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
//Move the values to the corresponding fields
consump = new ConsumptionDetail();
string valueFlatNo = consump.FlatNo = sqlite_datareader.GetString(0);
consump.Name = sqlite_datareader.GetString(1);
usage = Convert.ToDouble(sqlite_datareader.GetString(2));
valueMonth = sqlite_datareader.GetString(3);
string valYear = sqlite_datareader.GetString(4);
int valMonth = Convert.ToDateTime("01-" + valueMonth + "-2011").Month;
if ((valMonth == 3) && (valYear == "2014"))
{
valMonth = 4;
}
if ((valMonth >= 1) && (valMonth <= 3))
{
tempYear = DateTime.Today.AddYears(-1);
calcYear = tempYear.Year.ToString();
}
else if ((valMonth >= 4) && (valMonth <= 12))
{
calcYear = DateTime.Now.Year.ToString();
}
double baseUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, calcYear, "March"));
double currentUnit = Convert.ToDouble(RetrieveData.getReading(valueFlatNo, valYear, valueMonth));
double diffUnit = RetrieveData.calcConsumedUnit(baseUnit, currentUnit);
double tempUnit = Math.Round(((diffUnit * 2.6) / 14), 0);
string totalConsumption = consump.Consumption = Convert.ToString(tempUnit);
consumptionList.Add(consump);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// We are ready, now lets cleanup and close our connection:
sqlite_conn.Close();
}
return(consumptionList);
}
