private void Btn_Del_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Do you want to delete this sales record?", "Delete Sales", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
string deleteStatement = @"UPDATE Sales_Record SET sales_status = 0 WHERE sales_record_id = @salesID";
DataGridViewRow row = dataGridView_salesRecords.CurrentCell.OwningRow;
string id = row.Cells["Sales ID"].Value.ToString();
SqlCommand command;
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
try
{
conn.Open();
command = new SqlCommand(deleteStatement, conn);
command.Parameters.AddWithValue(@"salesID", id);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
dataGridView_salesRecords.Update();
GetData();
}
}
private void GetData(string cmd)
{
try
{
dataAdapter = new SqlDataAdapter(cmd, connString.getConnString());
table = new DataTable();
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Btn_Backup_Click(object sender, EventArgs e)
{
SqlCommand command;
string backupFolder = "C:\\SRPbackup\\";
try
{
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
// Creates backup query with filename containing date
string backupQuery = "BACKUP DATABASE \"" + AppDomain.CurrentDomain.BaseDirectory + database + "\" TO DISK = '" + backupFolder + database + "-" + DateTime.Now.ToString("yyyy-MM-dd.HH-mm-ss") + ".bak'";
try
{
conn.Open();
command = new SqlCommand(backupQuery, conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void Btn_Add_Click(object sender, EventArgs e)
{
SqlCommand command;
string getMaxID_CMD = "SELECT MAX(id) AS 'maxID' FROM Product";
string insertCmd = @"INSERT INTO Product (product_id, product_name, product_stock_qty, product_category, product_price, product_status)
VALUES(@product_ID, @product_name, 0, @product_category, @product_price, @product_status)";
if (txtBoxName.Text != "" && sltCategory.SelectedItem.ToString() != "")
{
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
try
{
conn.Open();
command = new SqlCommand(getMaxID_CMD, conn);
using (SqlDataReader reader = command.ExecuteReader()) {
if (reader.Read())
{
if (reader["maxID"].ToString().Equals(""))
{
maxID = 1;
}
else
{
maxID = Int32.Parse(reader["maxID"].ToString());
maxID++;
}
strMaxID = maxID.ToString();
for (int i = 0; i < (8 - strMaxID.Length); i++)
{
zeroCode += "0";
}
strMaxID = "P-" + zeroCode + maxID.ToString();
}
}
command = new SqlCommand(insertCmd, conn);
command.Parameters.AddWithValue(@"product_ID", strMaxID);
command.Parameters.AddWithValue(@"product_name", txtBoxName.Text);
command.Parameters.AddWithValue(@"product_category", sltCategory.SelectedItem.ToString());
double price = Convert.ToDouble(txtBoxPrice.Text);
command.Parameters.AddWithValue(@"product_price", price);
command.Parameters.AddWithValue(@"product_status", 1);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
this.Close();
}
else
{
MessageBox.Show("The required form must be filled.");
}
}
public SalesRecordsPie(bool useDates, DateTime startDate, DateTime endDate)
{
InitializeComponent();
connString = new ConnectionString();
chart1.DataSource = bindingSource1;
try
{
if (useDates)
{
dataAdapter = new SqlDataAdapter(selectQuery_part1 +
selectQuery_date1 + startDate.ToString("yyyy-MM-dd") +
selectQuery_date2 + endDate.ToString("yyyy-MM-dd") + selectQuery_date3 +
selectQuery_part2, connString.getConnString());
}
else
{
dataAdapter = new SqlDataAdapter(selectQuery_part1 + selectQuery_part2, connString.getConnString());
}
table = new DataTable();
dataAdapter.Fill(table);
// Prompts user if they selected a period with no data
if (table.Rows.Count == 0)
{
MessageBox.Show("No data available for the selected period", "No data", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
bindingSource1.DataSource = table;
series1.ChartArea = "ChartArea1";
series1.Name = "Pie";
series1.Font = new System.Drawing.Font("Microsoft Sans Serif", 10F);
series1.XValueMember = "Category";
series1.YValueMembers = "Sales Total Price";
legend1.Name = "Legend1";
series1.ChartArea = "ChartArea1";
series1.ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Pie;
series1.Font = new System.Drawing.Font("Microsoft Sans Serif", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
series1.Legend = "Legend1";
series1.Name = "Series1";
this.chart1.Legends.Add(legend1);
this.chart1.Series.Add(series1);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void SubmitEditProductFormButton_Click(object sender, EventArgs e)
{
// Validates manager password
if (txtEditProductPassword.Text == Decrypt((string)srpKey.GetValue("SRPvalue1")))
{
connString = new ConnectionString();
String updateProduct_SQL = @"UPDATE Product SET product_name = @productName, product_category = @productCategory, product_price = @productPrice, product_status = @productStatus WHERE product_id = @productID";;
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
try
{
conn.Open();
command = new SqlCommand(updateProduct_SQL, conn);
command.Parameters.AddWithValue(@"productName", txtEditProductName.Text);
command.Parameters.AddWithValue(@"productCategory", sltEditProductCategory.SelectedItem.ToString());
command.Parameters.AddWithValue(@"productPrice", txtEditProductUnitPrice.Value);
int idx;
idx = (sltEditPruductStatus.SelectedIndex == 0) ? 1 : 0;
command.Parameters.AddWithValue(@"productStatus", idx);
command.Parameters.AddWithValue(@"productID", txtEditProductID.Text);
command.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Product updated successfully!");
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
else
{
MessageBox.Show("Incorrect manager password.", "Wrong password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// This fires on loading the application, checks the date and if it differs from the one in the registry, performs a backup.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void DatabaseBackup(object sender, EventArgs e)
{
SqlCommand command;
string userName = Environment.UserName;
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
// Creates backup query with filename containing date
//string backupQuery = "BACKUP DATABASE SRP_SYSTEM TO DISK = 'D:\ + DateTime.Now.ToString("yyyy-MM-dd") + ".bak'";
string backupQuery = @"BACKUP DATABASE SRP_SYSTEM TO DISK = 'C:\Users\" + userName + @"\Documents\BACKUP_DATABASE_" + DateTime.Now.ToString("yyyy-MM-dd") + @".bak'";
try
{
conn.Open();
command = new SqlCommand(backupQuery, conn);
command.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void generateGraph()
{
//CLEAR SERIES IN CHART
prediction_chart1.Series.Clear();
prediction_chart2.Series.Clear();
prediction_chart3.Series.Clear();
prediction_chart4.Series.Clear();
SqlCommand command;
string[] date = generateQuery(); //generate select statement query
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
conn.Open();
command = new SqlCommand(date[0], conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var series1 = new System.Windows.Forms.DataVisualization.Charting.Series
{
Name = reader["Product Name"].ToString(),
ChartType = SeriesChartType.Bar
};
if (prediction_chart1.Series.IsUniqueName(reader["Product Name"].ToString()))
{
this.prediction_chart1.Series.Add(series1);
}
this.prediction_chart1.Series[reader["Product Name"].ToString()].Points.AddXY("First Week", reader["Quantity Order"].ToString());
}
}
command = new SqlCommand(date[1], conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var series1 = new System.Windows.Forms.DataVisualization.Charting.Series
{
Name = reader["Product Name"].ToString(),
ChartType = SeriesChartType.Bar
};
if (prediction_chart2.Series.IsUniqueName(reader["Product Name"].ToString()))
{
this.prediction_chart2.Series.Add(series1);
}
this.prediction_chart2.Series[reader["Product Name"].ToString()].Points.AddXY("Second Week", reader["Quantity Order"].ToString());
}
}
command = new SqlCommand(date[2], conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var series1 = new System.Windows.Forms.DataVisualization.Charting.Series
{
Name = reader["Product Name"].ToString(),
ChartType = SeriesChartType.Bar
};
if (prediction_chart3.Series.IsUniqueName(reader["Product Name"].ToString()))
{
this.prediction_chart3.Series.Add(series1);
}
this.prediction_chart3.Series[reader["Product Name"].ToString()].Points.AddXY("Third Week", reader["Quantity Order"].ToString());
}
}
command = new SqlCommand(date[3], conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var series1 = new System.Windows.Forms.DataVisualization.Charting.Series
{
Name = reader["Product Name"].ToString(),
ChartType = SeriesChartType.Bar
};
if (prediction_chart4.Series.IsUniqueName(reader["Product Name"].ToString()))
{
this.prediction_chart4.Series.Add(series1);
}
this.prediction_chart4.Series[reader["Product Name"].ToString()].Points.AddXY("Fourth Week", reader["Quantity Order"].ToString());
}
}
command = new SqlCommand(date[4], conn);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
prediction = reader["Product Name"].ToString();
}
}
conn.Close();
}
}
public SalesRecordsGraph(bool useDates, DateTime startDate, DateTime endDate)
{
InitializeComponent();
connString = new ConnectionString();
chart1.DataSource = bindingSource1;
// Declaration of categories
categories.Add("Pain Medication");
categories.Add("Fitness");
categories.Add("First Aid");
categories.Add("Vitamins & Supplements");
categories.Add("Digestive Health");
categories.Add("Oral Care");
categories.Add("Cough, Cold & Nasal Medication");
// Generating the graph areas based on the categories - just add any more categories above
// Count stores the number of series with data
int count = 0;
foreach (String s in categories)
{
try
{
if (useDates)
{
dataAdapter = new SqlDataAdapter(selectQuery_part1 + s +
selectQuery_date1 + startDate.ToString("yyyy-MM-dd") +
selectQuery_date2 + endDate.ToString("yyyy-MM-dd") +
selectQuery_part2, connString.getConnString());
}
else
{
dataAdapter = new SqlDataAdapter(selectQuery_part1 + s +
selectQuery_part2, connString.getConnString());
}
table = new DataTable();
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
chart1.Series.Add(s);
chart1.Series[s].ChartArea = "ChartArea1";
chart1.Series[s].Name = s;
chart1.Series[s].ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.StackedArea;
chart1.Series[s].Font = new System.Drawing.Font("Microsoft Sans Serif", 10F);
chart1.Series[s].XValueMember = "Sales Date";
chart1.Series[s].YValueMembers = "Sales Total Price";
chart1.Series[s].YValuesPerPoint = 6;
chart1.Legends.Add(s);
chart1.Legends[s].Name = s;
if (table.Rows.Count != 0)
{
count++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
// Prompts user if they selected a period with no data
if (count == 0)
{
MessageBox.Show("No data available for the selected period", "No data", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
private void Btn_Add_Click(object sender, EventArgs e)
{
// Validates manager password
if (textBox4.Text == Decrypt((string)srpKey.GetValue("SRPvalue1")))
{
SqlCommand command;
string getMaxID_CMD = "SELECT MAX(id) AS 'maxID' FROM Log_Changes";
string updateState = @"UPDATE Product SET product_stock_qty = @StockQty, product_arrival = @arrivalDate, product_expiry_date = @expiryDate WHERE product_id = @productID";
string editLogState = @"INSERT INTO Log_Changes (log_changes_id, log_changes_date, log_changes_time, log_changes_info, product_id)
VALUES (@logID, @date, @time, @info, @productID)";
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
//edit stock
try
{
conn.Open();
command = new SqlCommand(updateState, conn);
if (txtBoxEditQty.Text == "")
{
txtBoxEditQty.Text = "0";
}
int newQty = Convert.ToInt32(txtBoxCurrentQty.Text) + Convert.ToInt32(txtBoxEditQty.Text);
command.Parameters.AddWithValue(@"StockQty", newQty);
DateTime dateArrival = Convert.ToDateTime(txtBoxArrival.Text);
command.Parameters.AddWithValue(@"arrivalDate", dateArrival.ToString("yyyy-MM-dd"));
DateTime dateExpiry = Convert.ToDateTime(txtBoxExpiry.Text);
command.Parameters.AddWithValue(@"expiryDate", dateExpiry.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue(@"productID", txtBoxProductID.Text);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//log changes for edit
try
{
conn.Open();
command = new SqlCommand(getMaxID_CMD, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader["maxID"].ToString().Equals(""))
{
maxID = 1;
}
else
{
maxID = Int32.Parse(reader["maxID"].ToString());
maxID++;
}
strMaxID = maxID.ToString();
for (int i = 0; i < (7 - strMaxID.Length); i++)
{
zeroCode += "0";
}
strMaxID = "LS-" + zeroCode + maxID.ToString();
}
}
command = new SqlCommand(editLogState, conn);
command.Parameters.AddWithValue(@"logID", strMaxID);
command.Parameters.AddWithValue(@"date", DateTime.Today.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue(@"time", DateTime.Now.ToString("hh:mm:ss tt"));
string action = "";
int qty = Convert.ToInt32(txtBoxEditQty.Text);
action = (qty < 0) ? "Deduct " : "Add ";
command.Parameters.AddWithValue(@"info", action + txtBoxEditQty.Text + " Stocks");
command.Parameters.AddWithValue(@"productID", txtBoxProductID.Text);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
this.Close();
}
else
{
MessageBox.Show("Incorrect manager password.", "Wrong password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void addButton_Click_1(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Are you sure you want to refund " + txtBoxProductName.Text + " ?", "Refund Product", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
// Validates manager password
if (textBox_managerPass.Text == Decrypt((string)srpKey.GetValue("SRPvalue1")))
{
// Insert code
SqlCommand command;
string getMaxID_CMD = "SELECT MAX(id) AS 'maxID' FROM Log_Refunds";
string insertStatement = @"INSERT INTO Log_Refunds (log_refunds_id, log_refunds_date, log_refunds_time, log_refunds_description, product_id, refunds_product_qty) VALUES (@logID, @date, @time, @desc, @productID, @productQty)";
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
//log Refunds
try
{
conn.Open();
command = new SqlCommand(getMaxID_CMD, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader["maxID"].ToString().Equals(""))
{
maxID = 1;
}
else
{
maxID = Int32.Parse(reader["maxID"].ToString());
maxID++;
}
strMaxID = maxID.ToString();
for (int i = 0; i < (7 - strMaxID.Length); i++)
{
zeroCode += "0";
}
strMaxID = "LR-" + zeroCode + maxID.ToString();
}
}
command = new SqlCommand(insertStatement, conn);
command.Parameters.AddWithValue(@"logID", strMaxID);
command.Parameters.AddWithValue(@"date", DateTime.Today.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue(@"time", DateTime.Now.ToString("hh:mm:ss tt"));
command.Parameters.AddWithValue(@"desc", txtBoxDescription.Text);
command.Parameters.AddWithValue(@"productID", txtBoxProductID.Text);
command.Parameters.AddWithValue(@"productQty", txtBoxQty.Value);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
}
else
{
MessageBox.Show("Incorrect manager password.", "Wrong password", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void Btn_Add_Click(object sender, EventArgs e)
{
SqlCommand command;
string getMaxID_CMD = "SELECT MAX(id) AS 'maxID' FROM Log_Changes";
string updateState = @"UPDATE Product SET product_stock_qty = @addStockQty, product_arrival = @arrivalDate, product_expiry_date = @expiryDate WHERE product_id = @productID";
string addLogState = @"INSERT INTO Log_Changes (log_changes_id, log_changes_date, log_changes_time, log_changes_info, product_id)
VALUES (@logID, @date, @time, @info, @productID)";
using (SqlConnection conn = new SqlConnection(connString.getConnString()))
{
//add stock
try
{
conn.Open();
command = new SqlCommand(updateState, conn);
int newQty = Convert.ToInt32(txtBoxProductQty.Text) + Convert.ToInt32(txtBoxAddQty.Text);
command.Parameters.AddWithValue(@"addStockQty", newQty);
DateTime dateArrival = Convert.ToDateTime(txtBoxArrival.Text);
command.Parameters.AddWithValue(@"arrivalDate", dateArrival.ToString("yyyy-MM-dd"));
DateTime dateExpiry = Convert.ToDateTime(txtBoxExpiry.Text);
command.Parameters.AddWithValue(@"expiryDate", dateExpiry.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue(@"productID", txtBoxProductID.Text);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
//log changes
try
{
conn.Open();
command = new SqlCommand(getMaxID_CMD, conn);
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
if (reader["maxID"].ToString().Equals(""))
{
maxID = 1;
}
else
{
maxID = Int32.Parse(reader["maxID"].ToString());
maxID++;
}
strMaxID = maxID.ToString();
for (int i = 0; i < (7 - strMaxID.Length); i++)
{
zeroCode += "0";
}
strMaxID = "LS-" + zeroCode + maxID.ToString();
}
}
command = new SqlCommand(addLogState, conn);
command.Parameters.AddWithValue(@"logID", strMaxID);
command.Parameters.AddWithValue(@"date", DateTime.Today.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue(@"time", DateTime.Now.ToString("hh:mm:ss tt"));
command.Parameters.AddWithValue(@"info", "Add " + txtBoxAddQty.Text + " Stocks");
command.Parameters.AddWithValue(@"productID", txtBoxProductID.Text);
command.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
this.Close();
}
