private void recruit_emp_Click(object sender, EventArgs e)
{
addBtn_Clicked = false;
changeBtn_Clicked = false;
fireBtn_Clicked = false;
recruitBtn_Clicked = true;
if (emp_details.SelectedRows.Count == 1)
{
if (emp_details.SelectedRows[0].Cells[10].Value.ToString() == "False")
{
SqlCon.con.Open();
MySqlCommand cmd = new MySqlCommand("update pharmacy.people set Working = 1 where ID ='" + emp_details.SelectedRows[0].Cells[0].Value.ToString() + "';", SqlCon.con);
cmd.ExecuteNonQuery();
SqlCon.con.Close();
SqlCon.updateDataGridView("select * from pharmacy.people;", emp_details);
cancelBtn_Click(sender, e);
MessageBox.Show("Successfully recruited!");
}
else
{
MessageBox.Show("Employee is already working. No need to recruit!");
}
}
else
{
MessageBox.Show("Please select an Employee!");
}
}
private void debitDetails_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'pharmacyDataSet1.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter.Fill(this.pharmacyDataSet1.supplierdetails);
String query = "Select * from pharmacy.debit_balances";
SqlCon.updateDataGridView(query, debitDetails_dgv);
}
private void Sales_History_Load(object sender, EventArgs e)
{
to_dtp.MaxDate = DateTime.Today;
from_dtp.MaxDate = DateTime.Today;
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions;";
SqlCon.updateDataGridView(query, dataGridView1);
}
private void ClrBtn2_Click(object sender, EventArgs e)
{
supplierID_search2.Value = 0;
supplierName_search2.ResetText();
String query = "Select * from pharmacy.credit_details";
SqlCon.updateDataGridView(query, creditDetails);
}
private void clrBtn_Click(object sender, EventArgs e)
{
debitID_search.Value = 0;
Amount_search.Value = 0;
supDetails_search.ResetText();
String query = "Select * from pharmacy.debit_balances";
SqlCon.updateDataGridView(query, debitDetails_dgv);
}
private void clrBtn_Click(object sender, EventArgs e)
{
transactionID_search1.Value = 0;
itemID_search.Value = 0;
supDetails_search1.ResetText();
String query = "Select * from pharmacy.supplier_transactions";
SqlCon.updateDataGridView(query, supplier_transactionsDetails);
}
private void emp_management_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'pharmacyDataSet2.people' table. You can move, or remove it, as needed.
//this.peopleTableAdapter.Fill(this.pharmacyDataSet2.people);
String query = "Select * from pharmacy.people";
SqlCon.updateDataGridView(query, emp_details);
groupBox1.Enabled = false;
bdayPicker.MaxDate = DateTime.Today;
}
public void refreshdatagrid()
{
String query1 = "Select * from pharmacy.supplier_transactions";
SqlCon.updateDataGridView(query1, supplier_transactionsDetails);
String query2 = "Select * from pharmacy.credit_details";
SqlCon.updateDataGridView(query2, creditDetails);
}
private void modify_inventory_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'pharmacyDataSet6.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter2.Fill(this.pharmacyDataSet6.supplierdetails);
// TODO: This line of code loads data into the 'pharmacyDataSet5.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter1.Fill(this.pharmacyDataSet5.supplierdetails);
// TODO: This line of code loads data into the 'pharmacyDataSet1.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter.Fill(this.pharmacyDataSet1.supplierdetails);
String query = "Select * from pharmacy.inventory;";
SqlCon.updateDataGridView(query, dataGridView1);
groupBox1.Enabled = false;
}
private void empID_search_ValueChanged(object sender, EventArgs e)
{
String query;
if (empID_search.Value == 0)
{
query = "Select * from pharmacy.people";
}
else
{
query = "Select * from pharmacy.people where ID like '" + empID_search.Value.ToString() + "%" + "';";
}
SqlCon.updateDataGridView(query, emp_details);
}
private void Amount_search_ValueChanged(object sender, EventArgs e)
{
String query;
if (Amount_search.Value == 0)
{
query = "Select * from pharmacy.debit_balances";
}
else
{
query = "Select * from pharmacy.debit_balances where DebitID like '" + Amount_search.Value.ToString() + "%" + "';";
}
SqlCon.updateDataGridView(query, debitDetails_dgv);
}
private void supplierID_search2_ValueChanged(object sender, EventArgs e)
{
String query;
if (supplierID_search2.Value == 0)
{
query = "Select * from pharmacy.credit_details";
}
else
{
query = "Select * from pharmacy.credit_details where SupplierID like '" + supplierID_search2.Value.ToString() + "%" + "';";
}
SqlCon.updateDataGridView(query, creditDetails);
}
private void itemID_search_ValueChanged(object sender, EventArgs e)
{
String query;
if (itemID_search.Value == 0)
{
query = "Select * from pharmacy.supplier_transactions";
}
else
{
query = "Select * from pharmacy.supplier_transactions where ItemID like '" + itemID_search.Value.ToString() + "%" + "';";
}
SqlCon.updateDataGridView(query, supplier_transactionsDetails);
}
private void numericUpDown1_ValueChanged(object sender, EventArgs e)
{
String query;
if (numericUpDown1.Value == 0)
{
query = "Select * from pharmacy.inventory;";
}
else
{
query = "Select * from pharmacy.inventory where ProductID like '" + numericUpDown1.Value.ToString() + "%" + "';";
}
SqlCon.updateDataGridView(query, dataGridView1);
}
private void supplier_transactions_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'pharmacyDataSet7.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter2.Fill(this.pharmacyDataSet7.supplierdetails);
// TODO: This line of code loads data into the 'pharmacyDataSet4.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter1.Fill(this.pharmacyDataSet4.supplierdetails);
// TODO: This line of code loads data into the 'pharmacyDataSet3.supplierdetails' table. You can move, or remove it, as needed.
this.supplierdetailsTableAdapter.Fill(this.pharmacyDataSet3.supplierdetails);
String query1 = "Select * from pharmacy.supplier_transactions";
String query2 = "Select * from pharmacy.credit_details";
SqlCon.updateDataGridView(query1, supplier_transactionsDetails);
SqlCon.updateDataGridView(query2, creditDetails);
groupBox1.Enabled = false;
}
private void numericUpDown1_ValueChanged(object sender, EventArgs e)
{
if (productID_nud.Value != 0)
{
MySqlDataAdapter sAdapter2 = new MySqlDataAdapter("Select ProductID,BrandName,MedicalName,UnitPrice,ExpiryDate,InStock from pharmacy.inventory where ProductID like '" + productID_nud.Value.ToString() + "%" + "' and InStock > 0 and Branch = '" + branch + "';", SqlCon.con);
DataTable dt2 = new DataTable();
sAdapter2.Fill(dt2);
dataGridView1.DataSource = dt2;
dataGridView1.Update();
dataGridView1.Refresh();
}
else
{
String query = "Select ProductID,BrandName,MedicalName,UnitPrice,ExpiryDate,InStock from pharmacy.inventory where InStock > 0 and Branch = '" + branch + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (date_chkbox.Checked)
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions where CreatedDate between '" + from_dtp.Value.ToString("yyyy-MM-dd") + "' and '" + to_dtp.Value.ToString("yyyy-MM-dd") + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
else if (branch_chkbox.Checked)
{
if (comboBox1.Text == "")
{
MessageBox.Show("Select an option for branch", "Error", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
if (comboBox1.Text == "All")
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions;";
SqlCon.updateDataGridView(query, dataGridView1);
}
else if (comboBox1.Text == "Mulleriyawa")
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions where Branch ='" + "Mulleriyawa" + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
else if (comboBox1.Text == "Welivita")
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions where Branch ='" + "Welivita" + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
}
}
else if (invoice_chkbox.Checked)
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions where InvoiceNo = '" + numericUpDown1.Value.ToString() + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
else
{
String query = "select ProductID,ProductName,InvoiceNo,CashierID,CreatedDate,CreatedTime,Quantity,Amount,Branch from order_transactions;";
SqlCon.updateDataGridView(query, dataGridView1);
}
}
private void resetAll()
{
groupBox1.Enabled = false;
groupBox2.Enabled = true;
groupBox3.Enabled = true;
payDebit.Enabled = true;
viewDebit.Enabled = true;
supplier_transactionsDetails.Enabled = true;
creditDetails.Enabled = true;
supplier_transactionsDetails.ClearSelection();
creditDetails.ClearSelection();
String query1 = "Select * from pharmacy.supplier_transactions";
SqlCon.updateDataGridView(query1, supplier_transactionsDetails);
String query2 = "Select * from pharmacy.credit_details";
SqlCon.updateDataGridView(query2, creditDetails);
}
private void button2_Click(object sender, EventArgs e)
{
add_clicked = false;
remove_clicked = true;
edit_clicked = false;
update_clicked = false;
if (dataGridView1.SelectedRows.Count == 1)
{
DialogResult result = MessageBox.Show("Are you sure you want to delete?", "Are You Sure?", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
SqlCon.con.Open();
MySqlCommand cmd = new MySqlCommand("delete from pharmacy.inventory where ProductID='" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "';", SqlCon.con);
cmd.ExecuteNonQuery();
SqlCon.updateDataGridView("select * from pharmacy.inventory;", dataGridView1);
SqlCon.con.Close();
}
}
else
{
MessageBox.Show("Select a product first!");
}
}
private void button2_Click_1(object sender, EventArgs e)
{
con.Open();
string connString = "Server=localhost;Database=pharmacy;Uid=root;Pwd=;";
command = new MySqlCommand("DELETE FROM pharmacy.supplierdetails WHERE SupplierID=" + supplierDetailsDgv.SelectedRows[0].Cells[0].Value, con);
try
{
mdr = command.ExecuteReader();
} catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
con.Open();
String query = "Select * from pharmacy.supplierdetails;";
SqlCon.updateDataGridView(query, supplierDetailsDgv);
con.Close();
}
private void load_dgv1()
{
String query = "Select ProductID,BrandName,MedicalName,UnitPrice,ExpiryDate,InStock from pharmacy.inventory where InStock >0 and Branch = '" + branch + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
String query = "Select * from pharmacy.inventory where MedicalName like '" + "%" + textBox2.Text + "%" + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
private void refreshDatagrid(object sender, EventArgs e)
{
String query = "Select * from pharmacy.supplierdetails;";
SqlCon.updateDataGridView(query, supplierDetailsDgv);
}
private void supDetails_search_SelectedIndexChanged(object sender, EventArgs e)
{
String query = "Select * from pharmacy.debit_balances where SupplierName like'" + supDetails_search.Text + "';";
SqlCon.updateDataGridView(query, debitDetails_dgv);
}
private void button5_Click(object sender, EventArgs e)
{
if (add_clicked)
{
if (brandName_txtbox.Text == "" || medicalname_txtbox.Text == "" || branch_combobox.SelectedItem == null)
{
MessageBox.Show("Check details again!");
}
else if (label13.Text == "Unavailable")
{
MessageBox.Show("Check productID again!");
}
else
{
decimal value = buyingPrice_nud.Value * noofitems_nud.Value;
SqlCon.con.Open();
MySqlCommand cmd_invent = new MySqlCommand(@"insert into pharmacy.inventory (ProductID,BrandName,MedicalName,Supplier
,UnitPrice,ExpiryDate,InStock,Reorderlevel,Branch) values ('" + numericUpDown2.Value.ToString() + "','" + brandName_txtbox.Text + "','" + medicalname_txtbox.Text + "','" + supplier_combobox.Text + "','" + unitprice_nud.Value.ToString() + "','" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "','" + noofitems_nud.Value.ToString() + "','" + numericUpDown5.Value.ToString() + "','" + branch_combobox.Text + "');", SqlCon.con);
MySqlCommand cmd_suptransac = new MySqlCommand("INSERT INTO supplier_transactions (`TransactionID`, `InvoiceNo`, `ItemID`, `Quantity`, `BuyingPrice`, `SupplierName`, `Date`, `Time`, `Value`) VALUES(NULL,'" + buyingInvoiceNo_nud.Value.ToString() + "','" + numericUpDown2.Value.ToString() + "','" + noofitems_nud.Value.ToString() + "','" + buyingPrice_nud.Value.ToString() + "','" + supplier_combobox.Text + "','" + DateTime.Today.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToShortTimeString() + "','" + value.ToString() + "');", SqlCon.con);
MySqlCommand cmd_creditdetails = new MySqlCommand("update credit_details set creditamount = creditamount + '" + value.ToString() + "' where suppliername = '" + supplier_combobox.Text + "';", SqlCon.con);
cmd_suptransac.ExecuteNonQuery();
cmd_invent.ExecuteNonQuery();
cmd_creditdetails.ExecuteNonQuery();
SqlCon.con.Close();
String query = "Select * from pharmacy.inventory;";
SqlCon.updateDataGridView(query, dataGridView1);
MessageBox.Show("Data inserted!");
button6_Click(sender, e);
}
}
else if (edit_clicked)
{
if (brandName_txtbox.Text == "" || medicalname_txtbox.Text == "" || branch_combobox.SelectedItem == null)
{
MessageBox.Show("Check details again!");
}
else if (label13.Text == "Unavailable")
{
MessageBox.Show("Check productID again!");
}
else
{
decimal value = buyingPrice_nud.Value * noofitems_nud.Value;
SqlCon.con.Open();
MySqlCommand locktable = new MySqlCommand("lock tables pharmacy.inventory write;", SqlCon.con);
locktable.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand("delete from pharmacy.inventory where ProductID='" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "';", SqlCon.con);
cmd.ExecuteNonQuery();
MySqlCommand cmd1 = new MySqlCommand(@"insert into pharmacy.inventory (ProductID,BrandName,MedicalName,Supplier
,UnitPrice,ExpiryDate,InStock,Reorderlevel,Branch) values ('" + numericUpDown2.Value.ToString() + "','" + brandName_txtbox.Text + "','" + medicalname_txtbox.Text + "','" + supplier_combobox.Text + "','" + unitprice_nud.Value.ToString() + "','" + dateTimePicker1.Value.ToString("yyyy-MM-dd") + "','" + noofitems_nud.Value.ToString() + "','" + numericUpDown5.Value.ToString() + "','" + branch_combobox.Text + "');", SqlCon.con);
MySqlCommand cmd_suptransac = new MySqlCommand("INSERT INTO supplier_transactions (`TransactionID`, `InvoiceNo`, `ItemID`, `Quantity`, `BuyingPrice`, `SupplierName`, `Date`, `Time`, `Value`) VALUES(NULL,'" + buyingInvoiceNo_nud.Value.ToString() + "','" + numericUpDown2.Value.ToString() + "','" + noofitems_nud.Value.ToString() + "','" + buyingPrice_nud.Value.ToString() + "','" + supplier_combobox.Text + "','" + DateTime.Today.ToString("yyyy-MM-dd") + "','" + DateTime.Now.ToShortTimeString() + "','" + value.ToString() + "');", SqlCon.con);
MySqlCommand cmd_creditdetails = new MySqlCommand("update credit_details set creditamount = creditamount + '" + value.ToString() + "' where suppliername = '" + supplier_combobox.Text + "';", SqlCon.con);
cmd_suptransac.ExecuteNonQuery();
cmd_creditdetails.ExecuteNonQuery();
cmd1.ExecuteNonQuery();
MySqlCommand unlock = new MySqlCommand("unlock tables;", SqlCon.con);
unlock.ExecuteNonQuery();
SqlCon.con.Close();
String query = "Select * from pharmacy.inventory;";
SqlCon.updateDataGridView(query, dataGridView1);
button6_Click(sender, e);
}
}
else if (update_clicked)
{
SqlCon.con.Open();
MySqlCommand cmd = new MySqlCommand("update pharmacy.inventory set UnitPrice='" + unitprice_nud.Value.ToString() + "' where ProductID ='" + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "';", SqlCon.con);
cmd.ExecuteNonQuery();
SqlCon.con.Close();
SqlCon.updateDataGridView("select * from pharmacy.inventory;", dataGridView1);
button6_Click(sender, e);
}
}
private void Inventory_details_welivita_Load(object sender, EventArgs e)
{
String query = "Select * from pharmacy.inventory where Branch = '" + branch + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
String query = "Select * from pharmacy.inventory where BrandName like '" + "%" + textBox1.Text + "%" + "' and Branch = '" + branch + "';";
SqlCon.updateDataGridView(query, dataGridView1);
}
private void emplastName_txtBox_TextChanged(object sender, EventArgs e)
{
String query = "Select * from pharmacy.people where LastName like'" + "%" + emplastName_txtBox.Text + "%" + "';";
SqlCon.updateDataGridView(query, emp_details);
}
private void saveBtn_Click(object sender, EventArgs e)
{
if (addBtn_Clicked)
{
if (availability_indicator.Text == "ID available")
{
if (bdayPicker.Value.ToString("yyyy-MM-dd") != "0000-00-00" && fName_txtBox.Text != "" && lName_txtBox.Text != "" && roleSelect_cmboBox.Text != "" && address_txtBox.Text != "" && username_txtBox.Text != "" && branchSelect.Text != "")
{
SqlCon.con.Open();
MySqlCommand cmd = new MySqlCommand(@"insert into pharmacy.people (ID,FirstName,LastName,Type,Address,DateOfBirth,Salary,
Username,Password,Branch,Working) values ('" + emp_ID.Value.ToString() + "','" + fName_txtBox.Text + "','" + lName_txtBox.Text + "','" + roleSelect_cmboBox.Text + "','" + address_txtBox.Text + "','" + bdayPicker.Value.ToString("yyyy-MM-dd") + "','" + salary_updown.Value.ToString() + "','" + username_txtBox.Text + "','" + pwd_txtBox.Text + "','" + branchSelect.Text + "','" + 1 + "');", SqlCon.con);
cmd.ExecuteNonQuery();
SqlCon.con.Close();
String query = "Select * from pharmacy.people;";
SqlCon.updateDataGridView(query, emp_details);
MessageBox.Show("Employee Successfully added!");
cancelBtn_Click(sender, e);
}
else
{
MessageBox.Show("Invalid Entry. Please fill the details!");
}
}
else
{
MessageBox.Show("Invalid ID!");
}
//cancelBtn_Click(sender, e);
}
else if (changeBtn_Clicked)
{
if (bdayPicker.Value.ToString("yyyy-MM-dd") != "0000-00-00" && fName_txtBox.Text != "" && lName_txtBox.Text != "" && roleSelect_cmboBox.Text != "" && address_txtBox.Text != "" && username_txtBox.Text != "" && branchSelect.Text != "")
{
SqlCon.con.Open();
MySqlCommand locktable = new MySqlCommand("lock tables pharmacy.people write;", SqlCon.con);
locktable.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand("delete from pharmacy.people where ID='" + emp_details.SelectedRows[0].Cells[0].Value.ToString() + "';", SqlCon.con);
cmd.ExecuteNonQuery();
MySqlCommand cmd1 = new MySqlCommand(@"insert into pharmacy.people (ID,FirstName,LastName,Type,Address,DateOfBirth,Salary,
Username,Password,Branch,Working) values ('" + emp_ID.Value.ToString() + "','" + fName_txtBox.Text + "','" + lName_txtBox.Text + "','" + roleSelect_cmboBox.Text + "','" + address_txtBox.Text + "','" + bdayPicker.Value.ToString("yyyy-MM-dd") + "','" + salary_updown.Value.ToString() + "','" + username_txtBox.Text + "','" + pwd_txtBox.Text + "','" + branchSelect.Text + "','" + 1 + "');", SqlCon.con);
cmd1.ExecuteNonQuery();
MySqlCommand unlock = new MySqlCommand("unlock tables;", SqlCon.con);
unlock.ExecuteNonQuery();
SqlCon.con.Close();
String query = "Select * from pharmacy.people;";
SqlCon.updateDataGridView(query, emp_details);
cancelBtn_Click(sender, e);
MessageBox.Show("Employee Details changed successfully!");
}
else
{
MessageBox.Show("Invalid Entry. Please fill the details!");
}
}
else
{
MessageBox.Show("Error! Recheck the entry!");
}
}
private void role_CmbBox_SelectedIndexChanged(object sender, EventArgs e)
{
String query = "Select * from pharmacy.people where Type like'" + role_CmbBox.Text + "';";
SqlCon.updateDataGridView(query, emp_details);
}
