private void btnConfirm_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txtYear.Text) || string.IsNullOrEmpty(cbMonth.Text))
{
MessageBox.Show("One or more fields are left empty. Please fill them up!", "Error");
return;
}
int year;
bool yearValid = Int32.TryParse(txtYear.Text, out year);
if (yearValid == false || year < 0)
{
MessageBox.Show("Please enter a valid year!", "Error");
return;
}
int month = 1;
if (cbMonth.Text == "February")
{
month = 2;
}
else if (cbMonth.Text == "March")
{
month = 3;
}
else if (cbMonth.Text == "April")
{
month = 4;
}
else if (cbMonth.Text == "May")
{
month = 5;
}
else if (cbMonth.Text == "June")
{
month = 6;
}
else if (cbMonth.Text == "July")
{
month = 7;
}
else if (cbMonth.Text == "August")
{
month = 8;
}
else if (cbMonth.Text == "September")
{
month = 9;
}
else if (cbMonth.Text == "October")
{
month = 10;
}
else if (cbMonth.Text == "November")
{
month = 11;
}
else if (cbMonth.Text == "December")
{
month = 12;
}
string dateForMonth = "1/" + month.ToString() + "/" + year.ToString();
double totalSpent = 0.0;
double totalEarned = 0.0;
OleDbCommand cmd = new OleDbCommand();
OleDbConnection cn = new OleDbConnection();
OleDbDataReader dr;
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Restaurant.accdb; Persist Security Info=False;";
cmd.Connection = cn;
bool reportFound = false;
int reportID = -1;
string q = "SELECT reportID, reportDate FROM Report WHERE reportTitle = 'Monthly';";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
reportID = Int32.Parse(dr["reportID"].ToString());
DateTime dtObtainedDate = DateTime.Parse(dr["reportDate"].ToString());
string obtainedDate = dtObtainedDate.ToShortDateString();
if (obtainedDate == dateForMonth)
{
reportFound = true;
break;
}
}
dr.Close();
cn.Close();
bool generateNewReport = false;
//If a report was generated previously, ask user if he want to generate a new one or see old one
if (reportFound == true)
{
DialogResult dialogResult = MessageBox.Show("You have previously generated a report for this month and year. Click 'Yes' to generate a new report or 'No' to see the old report. If you generate a new report, the old report will be updated automatically.", "Selection", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
generateNewReport = true;
}
else if (dialogResult == DialogResult.No)
{
frmReport newForm = new frmReport(reportID);
newForm.Show();
this.Hide();
return;
}
}
if (reportFound == false)
{
q = "SELECT totalPrice FROM Bill WHERE Year([billDate]) = " + year + " AND Month([billDate]) = " + month + ";";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalEarned = totalEarned + double.Parse(dr["totalPrice"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for earnings in the chosen month and year! Please select another month or year!", "Empty");
return;
}
q = "SELECT totalCost FROM Cost WHERE Year([costDate]) = " + year + " AND Month([costDate]) = " + month + ";";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalSpent = totalSpent + double.Parse(dr["totalCost"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for expenditure in the chosen month and year! Please select another month or year!", "Empty");
return;
}
double totalProfit = totalEarned - totalSpent;
q = "SELECT Max(reportID) FROM Report";
cmd.CommandText = q;
cn.Open();
int maxId = 1;
if (cmd.ExecuteScalar() != DBNull.Value)
{
maxId = Convert.ToInt32(cmd.ExecuteScalar());
maxId = maxId + 1;
}
cn.Close();
q = "INSERT INTO Report(reportID, reportTitle, totalCost, totalSales, totalProfit, reportDate) VALUES(" +
maxId + ", 'Monthly'," + totalSpent + "," + totalEarned + "," + totalProfit + ", ?);";
cmd.CommandText = q;
cmd.Parameters.AddWithValue("?", new DateTime(year, month, 1));
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
frmReport newForm = new frmReport(maxId);
newForm.Show();
this.Hide();
return;
}
if (generateNewReport == true)
{
q = "SELECT totalPrice FROM Bill WHERE Year([billDate]) = " + year + " AND Month([billDate]) = " + month + ";";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalEarned = totalEarned + double.Parse(dr["totalPrice"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for earnings in the chosen month and year! Please select another month or year!", "Empty");
return;
}
q = "SELECT totalCost FROM Cost WHERE Year([costDate]) = " + year + " AND Month([costDate]) = " + month + ";";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalSpent = totalSpent + double.Parse(dr["totalCost"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for expenditure in the chosen month and year! Please select another month or year!", "Empty");
return;
}
double totalProfit = totalEarned - totalSpent;
q = "UPDATE Report SET totalCost=" + totalSpent + ", totalSales=" + totalEarned + ", totalProfit=" +
totalProfit + " WHERE reportID=" + reportID;
cmd.CommandText = q;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
frmReport newForm = new frmReport(reportID);
newForm.Show();
this.Hide();
return;
}
}
private void btnConfirm_Click(object sender, EventArgs e)
{
DateTime chosenDate = calDate.SelectionRange.Start;
DateTime endDate = chosenDate.AddDays(6);
if (DateTime.Today < endDate)
{
MessageBox.Show("The week you have chosen has not ended. Please choose another Sunday!", "Error");
return;
}
OleDbCommand cmd = new OleDbCommand();
OleDbConnection cn = new OleDbConnection();
OleDbDataReader dr;
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Restaurant.accdb; Persist Security Info=False;";
cmd.Connection = cn;
string chosenDateStr = chosenDate.ToShortDateString();
string endDateStr = endDate.ToShortDateString();
double totalSpent = 0.0;
double totalEarned = 0.0;
bool reportFound = false;
int reportID = -1;
string q = "SELECT reportID, reportDate FROM Report WHERE reportTitle = 'Weekly';";
cmd.CommandText = q;
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
reportID = Int32.Parse(dr["reportID"].ToString());
DateTime dtObtainedDate = DateTime.Parse(dr["reportDate"].ToString());
string obtainedDate = dtObtainedDate.ToShortDateString();
if (obtainedDate == chosenDateStr)
{
reportFound = true;
break;
}
}
dr.Close();
cn.Close();
bool generateNewReport = false;
//If a report was generated previously, ask user if he want to generate a new one or see old one
if (reportFound == true)
{
DialogResult dialogResult = MessageBox.Show("You have previously generated a report for the chosen week. Click 'Yes' to generate a new report or 'No' to see the old report. If you generate a new report, the old report will be updated automatically.", "Selection", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
generateNewReport = true;
}
else if (dialogResult == DialogResult.No)
{
frmReport newForm = new frmReport(reportID);
newForm.Show();
this.Hide();
return;
}
}
if (reportFound == false)
{
q = "SELECT [totalPrice] FROM [Bill] WHERE [billDate] BETWEEN ? AND ?;";
cmd.CommandText = q;
cmd.Parameters.AddWithValue("?", new DateTime(chosenDate.Year, chosenDate.Month, chosenDate.Day));
cmd.Parameters.AddWithValue("?", new DateTime(endDate.Year, endDate.Month, endDate.Day));
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalEarned = totalEarned + double.Parse(dr["totalPrice"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for earnings for the chosen week! Please select another week!", "Empty");
return;
}
q = "SELECT totalCost FROM Cost WHERE costDate BETWEEN ? AND ?;";
cmd.CommandText = q;
cmd.Parameters.AddWithValue("?", new DateTime(chosenDate.Year, chosenDate.Month, chosenDate.Day));
cmd.Parameters.AddWithValue("?", new DateTime(endDate.Year, endDate.Month, endDate.Day));
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalSpent = totalSpent + double.Parse(dr["totalCost"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for expenditure for the chosen week! Please select another week!", "Empty");
return;
}
double totalProfit = totalEarned - totalSpent;
q = "SELECT Max(reportID) FROM Report";
cmd.CommandText = q;
cn.Open();
int maxId = 1;
if (cmd.ExecuteScalar() != DBNull.Value)
{
maxId = Convert.ToInt32(cmd.ExecuteScalar());
maxId = maxId + 1;
}
cn.Close();
q = "INSERT INTO Report(reportID, reportTitle, totalCost, totalSales, totalProfit, reportDate) VALUES(" +
maxId + ", 'Weekly'," + totalSpent + "," + totalEarned + "," + totalProfit + ",'" + chosenDateStr + "');";
cmd.CommandText = q;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
frmReport newForm = new frmReport(maxId);
newForm.Show();
this.Hide();
return;
}
if (generateNewReport == true)
{
q = "SELECT [totalPrice] FROM [Bill] WHERE [billDate] BETWEEN ? AND ?;";
cmd.CommandText = q;
cmd.Parameters.AddWithValue("?", new DateTime(chosenDate.Year, chosenDate.Month, chosenDate.Day));
cmd.Parameters.AddWithValue("?", new DateTime(endDate.Year, endDate.Month, endDate.Day));
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalEarned = totalEarned + double.Parse(dr["totalPrice"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for earnings for the chosen week! Please select another week!", "Empty");
return;
}
q = "SELECT totalCost FROM Cost WHERE costDate BETWEEN ? AND ?;";
cmd.CommandText = q;
cmd.Parameters.AddWithValue("?", new DateTime(chosenDate.Year, chosenDate.Month, chosenDate.Day));
cmd.Parameters.AddWithValue("?", new DateTime(endDate.Year, endDate.Month, endDate.Day));
cn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
totalSpent = totalSpent + double.Parse(dr["totalCost"].ToString());
}
dr.Close();
cn.Close();
if (totalEarned == 0.0)
{
MessageBox.Show("There are no records for expenditure for the chosen week! Please select another week!", "Empty");
return;
}
double totalProfit = totalEarned - totalSpent;
q = "UPDATE Report SET totalCost=" + totalSpent + ", totalSales=" + totalEarned + ", totalProfit=" +
totalProfit + " WHERE reportID=" + reportID;
cmd.CommandText = q;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
frmReport newForm = new frmReport(reportID);
newForm.Show();
this.Hide();
return;
}
}
