private void buttonDelete_Click(object sender, EventArgs e)
{
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
if (dataGridViewUsers.SelectedRows.Count > 0)
{
string command = "";
for (int i = 0; i < dataGridViewUsers.SelectedRows.Count; i++)
{
command = "delete from users where username='******'";
SqlCommand cmd = new SqlCommand(command, conn);
int affected = cmd.ExecuteNonQuery();
if (affected > 0)
{
MessageBox.Show("User succesfully deleted");
dataGridViewUsers.Rows.RemoveAt(dataGridViewUsers.SelectedRows[i].Index);
}
else
{
MessageBox.Show("Deletion not possible");
}
}
}
}
private void buttonPurchase_Click(object sender, EventArgs e)
{
DatabaseWorking windowMain = new DatabaseWorking();
SqlConnection conn = windowMain.CreateConnectionToSqlDatabase();
dataGridViewPurchase.Rows.Clear();
SqlCommand cmd;
MessageBox.Show(dataGridViewPurchase.Rows.Count.ToString());
for (int i = 0; i < dataGridViewPurchase.Rows.Count - 1; i++)
{
MessageBox.Show("Item" + dataGridViewPurchase.Rows[0].Cells[1].Value.ToString());
string command =
"select barcode , medicineName , quantity , expirayDate from stock join medicine on stock.medicneID=medicine.medicineID where barcode = '" + dataGridViewPurchase.Rows[0].Cells[0].Value + "' and medicineName = '" + dataGridViewPurchase.Rows[0].Cells[1].Value + "'" +
"if @@ROWCOUNT > 0" +
"Begin " +
"update stock set quantity = quantity + '" + dataGridViewPurchase.Rows[0].Cells[2].Value + "' , supplierID = (select supplierID from supplier where SupplierName = '" + comboBox1.SelectedItem + "') where barcode = '" + dataGridViewPurchase.Rows[0].Cells[0].Value + "'" +
" End";
cmd = new SqlCommand(command, conn);
int affected = cmd.ExecuteNonQuery();
if (affected > 0)
{
MessageBox.Show("Purcahsed successfull !");
}
else
{
MessageBox.Show("Purcahsed unsuccessfull !" + affected);
}
}
}
private void button4_Click(object sender, EventArgs e)
{
string itemid = Guid.NewGuid().ToString();
MessageBox.Show(itemid);
for (int i = 0; i < dataGridSales.Rows.Count - 1; i++)
{
string command;
//"Declare @medicneID int" +
//"Set @medicneID =1"+// (select medicineID from medicine where medicineName = ' " + dataGridSales.Rows[i].Cells[1].Value.ToString() + " ') " +
//"update sale set barcode= ' " + dataGridSales.Rows[i].Cells[0].Value.ToString() + " ' " + ", medicneID= 1 , salePrice =" + dataGridSales.Rows[i].Cells[3].Value.ToString() +
//", quantity =" + dataGridSales.Rows[i].Cells[2] + ",profit=(select" + int.Parse(dataGridSales.Rows[i].Cells[3].Value.ToString()) + "- costPrice from stock where barcode=" + dataGridSales.Rows[i].Cells[0].Value.ToString() +
//") ,soldDatewithTime='" + DateTime.Now + "',userID=1 where barcode='" + dataGridSales.Rows[i].Cells[0].Value.ToString()+"'";
//"Declare @medicneID int" +
//"Set @medicneID =1"+// (select medicineID from medicine where medicineName = ' " + dataGridSales.Rows[i].Cells[1].Value.ToString() + " ') " +
// "update sale set barcode= ' " + dataGridSales.Rows[i].Cells[0].Value.ToString() + " ' " + ", medicneID= 1 , salePrice =" + dataGridSales.Rows[i].Cells[3].Value.ToString() +
// ", quantity =" + dataGridSales.Rows[i].Cells[2] + ",profit=(select" + int.Parse(dataGridSales.Rows[i].Cells[3].Value.ToString()) + "- costPrice from stock where barcode=" + dataGridSales.Rows[i].Cells[0].Value.ToString() +
// ") ,soldDatewithTime='" + DateTime.Now + "',userID=1 where barcode='" + dataGridSales.Rows[i].Cells[0].Value.ToString() + "'";
MessageBox.Show(dataGridSales.Rows[i].Cells[0].Value.ToString());
command = "insert into saleItems values('" + itemid + " ','" + dataGridSales.Rows[i].Cells[0].Value.ToString() + "', (select medicineID from medicine where medicineName = ' " + dataGridSales.Rows[i].Cells[1].Value.ToString() + " ')," + int.Parse(dataGridSales.Rows[i].Cells[3].Value.ToString()) + " , (select " + float.Parse(dataGridSales.Rows[i].Cells[3].Value.ToString()) + " - costPrice from stock where barcode = '" + dataGridSales.Rows[i].Cells[0].Value.ToString() + "')," + int.Parse(dataGridSales.Rows[i].Cells[2].Value.ToString()) + ")";
DatabaseWorking windowMain = new DatabaseWorking();
SqlConnection conn = windowMain.CreateConnectionToSqlDatabase();
SqlCommand cmd = new SqlCommand(command, conn);
cmd.ExecuteNonQuery();
}
}
private void buttonAdd_Click(object sender, EventArgs e)
{
textBoxBarcode.Text = Stock.random;
if (textBoxBarcode.Text.Length == 12 && textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.ToString() != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "")
{
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
string command = "insert into stock values('" + textBoxBarcode.Text + "', (select medicineID from medicine where medicineName='" + textBox1.Text + "') ," + textBox2.Text + " ," + textBox3.Text + " , " + textBox4.Text + ", (select supplierID from supplier where SupplierName='" + textBox5.Text + "'),'" + textBox6.Text + "','" + textBox7.Text + "')";
SqlCommand cmd = new SqlCommand(command, conn);
int affected = cmd.ExecuteNonQuery();
if (affected > 0)
{
MessageBox.Show("item inserted into stock successfully");
textBoxBarcode.Text = "";
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
}
else
{
MessageBox.Show("Some Error caused in insertion");
}
}
else
{
MessageBox.Show("Please fill all required fields");
}
}
private void ButtonReports_Click(object sender, EventArgs e)
{
Reports reports = new Reports();
ToggleColor(ButtonReports);
ContentPanel.Controls.Clear();
CreateUserControl(reports);
DatabaseWorking database = new DatabaseWorking();
string command = "select distinct soldDate from saleOrder";
SqlConnection sqlConnection = database.CreateConnectionToSqlDatabase();
database.AddItemsToCombobox(reports.comboBoxFromdate, command, sqlConnection);
database.AddItemsToCombobox(reports.comboBoxTillDate, command, sqlConnection);
command = "select soldDate, SUM([totalPrice]) from saleOrder group by soldDate ";
SqlCommand cmd = new SqlCommand(command, sqlConnection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow dataRow in dt.Rows)
{
reports.chartSalesPerday.Series["Series1"].Points.AddXY(dataRow[0].ToString(), dataRow[1].ToString());
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
string sql = "select barcode as Barcode, medicineName as Prouct, quantity as Quantity, expirayDate as [Expiry Date] from stock join medicine on stock.medicneID = medicine.medicineID where quantity<" + textBox1.Text;
DatabaseWorking windowMain = new DatabaseWorking();
SqlConnection conn = windowMain.CreateConnectionToSqlDatabase();
DatagridStock.Rows.Clear();
windowMain.AddItemsToDataGrid(DatagridStock, sql, conn);
}
private void ButtonStock_Click(object sender, EventArgs e)
{
Stock stock = new Stock();
ToggleColor(ButtonStock);
ContentPanel.Controls.Clear();
CreateUserControl(stock);
DatabaseWorking database = new DatabaseWorking();
string command = "select barcode,medicineName,salePrice,costPrice,quantity,expirayDate from stock join medicine on stock.medicneID=medicine.medicineID;";
SqlConnection sqlConnection = database.CreateConnectionToSqlDatabase();
database.AddItemsToDataGrid(stock.dataGridViewStock, command, sqlConnection);
}
private void ButtonDashboard_Click(object sender, EventArgs e)
{
Dashboard dashboard = new Dashboard();
ContentPanel.Controls.Clear();
CreateUserControl(dashboard);
DatabaseWorking database = new DatabaseWorking();
string command = "select count(supplierID) from supplier ";
SqlConnection sqlConnection = database.CreateConnectionToSqlDatabase();
SqlCommand cmd = new SqlCommand(command, sqlConnection);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
dashboard.labelTotalProviders.Text = dt.Rows[0][0].ToString();
command = "select sum(totalPrice) from saleOrder";
cmd = new SqlCommand(command, sqlConnection);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
dashboard.labelSales.Text = "RS " + dt.Rows[0][0].ToString();
command = "select count(distinct medicneID) from stock ";
cmd = new SqlCommand(command, sqlConnection);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
dashboard.labelProducts.Text = dt.Rows[0][0].ToString();
command = "select count(orderID) from saleOrder";
cmd = new SqlCommand(command, sqlConnection);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
dashboard.labelServed.Text = dt.Rows[0][0].ToString();
command = "select count(userID) from users";
cmd = new SqlCommand(command, sqlConnection);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
dashboard.labelTotalEmployees.Text = dt.Rows[0][0].ToString();
command = "select count(CompanyName) from stock join medicine on stock.medicneID=medicine.medicineID";
cmd = new SqlCommand(command, sqlConnection);
adapter = new SqlDataAdapter(cmd);
adapter.Fill(dt);
dashboard.labelBrands.Text = dt.Rows[0][0].ToString();
}
private void buttonAdd_Click(object sender, EventArgs e)
{
int admincheck;
if (checkBox1.Checked)
{
admincheck = 1;
}
else if (checkBox2.Checked)
{
admincheck = 0;
}
else
{
admincheck = 0;
}
if (textBoxFirstName.Text != "" && textBoxLastName.Text != "" && textBoxUsername.Text != "" && textBoxPassword.Text != "" && admincheck.ToString() != "" && textBox1.Text != "" && imagePath != "" && comboBox1.SelectedItem.ToString() != "")
{
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
string command = "insert into users values('" + textBoxFirstName.Text + "','" + textBoxLastName.Text + "','" + textBoxUsername.Text + "','" + textBoxPassword.Text + "','" + admincheck.ToString() + "','" + textBox1.Text + "','" + comboBox1.SelectedItem + "','" + imagePath + "')";
SqlCommand cmd = new SqlCommand(command, conn);
int affected = cmd.ExecuteNonQuery();
if (affected > 0)
{
MessageBox.Show("User inserted successfully");
textBoxFirstName.Text = "";
textBoxLastName.Text = "";
textBoxUsername.Text = "";
textBoxPassword.Text = "";
textBox1.Text = "";
imagePath = "";
pictureBox1.ImageLocation = "";
}
else
{
MessageBox.Show("Some Error caused in insertion");
}
}
else
{
MessageBox.Show("Please fill all required fields");
}
}
private void ButtonProviders_Click(object sender, EventArgs e)
{
Providers providers = new Providers();
ToggleColor(ButtonProviders);
ContentPanel.Controls.Clear();
CreateUserControl(providers);
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
string command = "select photo,SupplierName,phone,address,dues from supplier";
SqlCommand cmd = new SqlCommand(command, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow dataRow in dt.Rows)
{
int rowIndex = providers.dataGridViewProviderInfo.Rows.Add();
Image image = Image.FromFile(dataRow[0].ToString());
image = (Image)(new Bitmap(image, new Size(150, 150)));
// providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[0].Size = new Size(50, 50);
providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[0].Value = image;
for (int i = 1; i < dt.Columns.Count; i++)
{
providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[i].Value = dataRow[i].ToString();
// providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[providers.dataGridViewProviderInfo.Rows.Count - 1].Value = "Clear Dues";
}
}
providers.dataGridViewProviderInfo.Rows[0].Selected = false;
command = "select SupplierName , count(barcode)*sum(costPrice) from stock join supplier on stock.supplierID=supplier.supplierID group by SupplierName";
cmd = new SqlCommand(command, conn);
adapter = new SqlDataAdapter(cmd);
dt = new DataTable();
adapter.Fill(dt);
providers.chartStockShares.Series["Series1"].Points.RemoveAt(0);
foreach (DataRow dataRow in dt.Rows)
{
providers.chartStockShares.Series["Series1"].Points.AddXY(dataRow[0].ToString(), dataRow[1].ToString());
}
}
private void ButtonPurchase_Click(object sender, EventArgs e)
{
DatabaseWorking working = new DatabaseWorking();
Purcahse purcahseTab = new Purcahse();
ToggleColor(ButtonPurchase);
ContentPanel.Controls.Clear();
CreateUserControl(purcahseTab);
if (purcahseTab.DatagridStock.Rows.Count != 0)
{
string[] sql = new string[2];
sql[0] = "select barcode as Barcode, medicineName as Prouct, quantity as Quantity, expirayDate as [Expiry Date] from stock join medicine on stock.medicneID = medicine.medicineID";
sql[1] = "select SupplierName from supplier";
SqlConnection conn = working.CreateConnectionToSqlDatabase();
working.AddItemsToDataGrid(purcahseTab.DatagridStock, sql[0], conn);
working.AddItemsToCombobox(purcahseTab.comboBox1, sql[1], conn);
purcahseTab.comboBox1.SelectedIndex = 0;
}
}
private void buttonEdit_Click(object sender, EventArgs e)
{
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
DataGridViewRow row = dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index];
string command = "update stock set medicneID = (select medicineID from medicine where medicineName='" + row.Cells[1].Value + "'), salePrice = '" + dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index].Cells[2].Value + "', costPrice = '" + dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index].Cells[3].Value + "', quantity = '" + dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index].Cells[4].Value + "', expirayDate = '" + dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index].Cells[5].Value + "' from stock where barcode='" + dataGridViewStock.Rows[dataGridViewStock.SelectedRows[0].Index].Cells[0].Value + "'";
if (dataGridViewStock.SelectedRows.Count == 1)
{
SqlCommand cmd = new SqlCommand(command, conn);
int affected = cmd.ExecuteNonQuery();
if (affected > 0)
{
MessageBox.Show("Item updated succesfully");
}
else
{
MessageBox.Show("Updation not possible");
}
}
}
private void button1_Click(object sender, EventArgs e)
{
string command = "select * from users where username = '******' and passsword = '" + textBox1.Text.ToString() + "'";
// string command = "insert into users values('Anus','Baig','anusbaig57','medical',2,'03343854480','Male','C:\\Users\\anasb\\Documents\\MedicalStore\\UserImages\\AnusBaig.jpg')";
DatabaseWorking db = new DatabaseWorking();
SqlConnection conn = db.CreateConnectionToSqlDatabase();
SqlCommand cmd = new SqlCommand(command, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
try
{
if (dt.Rows[0][3].ToString() == UserTextbox.Text && dt.Rows[0][4].ToString() == textBox1.Text)
{
name = dt.Rows[0][1].ToString() + " " + dt.Rows[0][2].ToString();
path = dt.Rows[0][8].ToString();
adminstatus = bool.Parse(dt.Rows[0][5].ToString());
WindowMain WindowMain = new WindowMain();
WindowMain.Show();
this.Close();
}
else
{
DialogResult dialogueResult = MessageBox.Show("Incorrect UserName or Password", "Incoorect Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
if (dialogueResult == DialogResult.Cancel)
{
this.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("You are not a member of this Software's users", "Incoorect Info", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void ButtonEmployees_Click(object sender, EventArgs e)
{
Users user = new Users();
ToggleColor(ButtonEmployees);
ContentPanel.Controls.Clear();
CreateUserControl(user);
DatabaseWorking database = new DatabaseWorking();
SqlConnection conn = database.CreateConnectionToSqlDatabase();
string command = "select photo, username,phone,passsword from users";
SqlCommand cmd = new SqlCommand(command, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach (DataRow dataRow in dt.Rows)
{
int rowIndex = user.dataGridViewUsers.Rows.Add();
Image image = Image.FromFile(dataRow[0].ToString());
image = (Image)(new Bitmap(image, new Size(150, 150)));
// providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[0].Size = new Size(50, 50);
user.dataGridViewUsers.Rows[rowIndex].Cells[0].Value = image;
for (int i = 1; i < dt.Columns.Count; i++)
{
user.dataGridViewUsers.Rows[rowIndex].Cells[i].Value = dataRow[i].ToString();
// providers.dataGridViewProviderInfo.Rows[rowIndex].Cells[providers.dataGridViewProviderInfo.Rows.Count - 1].Value = "Clear Dues";
}
}
user.dataGridViewUsers.Rows[0].Selected = false;
}
private void dataGridSales_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
if (dataGridSales.CurrentCell.ColumnIndex == 0)
{
if (dataGridSales.CurrentCell.Value.ToString().Length > 2)
{
string command = "select medicineName,quantity,salePrice,1 * salePrice from stock join medicine on stock.medicneID=medicine.medicineID where barcode =" + dataGridSales.CurrentCell.Value.ToString();
DatabaseWorking windowMain = new DatabaseWorking();
SqlConnection conn = windowMain.CreateConnectionToSqlDatabase();
// windowMain.AddItemsToDataGrid(dataGridSales, command, conn);
SqlCommand cmd = new SqlCommand(command, conn);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
for (int i = 0; i < dt.Columns.Count; i++)
{
dataGridSales.Rows[dataGridSales.CurrentRow.Index].Cells[i + 1].Value = dt.Rows[0][i].ToString();
}
dataGridSales.Rows[dataGridSales.CurrentRow.Index].Cells[2].Value = "1";
int item = 0;
for (int i = 0; i < dataGridSales.Rows.Count - 1; i++)
{
item += int.Parse(dataGridSales.Rows[i].Cells[4].Value.ToString());
}
textBox4.Text = item.ToString();
textBox1.Text = item.ToString();
}
else
{
MessageBox.Show("Invalid Barcode");
}
}
else if (dataGridSales.CurrentCell.ColumnIndex == 2)
{
if (dataGridSales.CurrentCell.Value.ToString().Length > 0)
{
dataGridSales.Rows[dataGridSales.CurrentRow.Index].Cells[4].Value = int.Parse(dataGridSales.Rows[dataGridSales.CurrentRow.Index].Cells[2].Value.ToString()) * int.Parse(dataGridSales.Rows[dataGridSales.CurrentRow.Index].Cells[3].Value.ToString());
int item = 0;
for (int i = 0; i < dataGridSales.Rows.Count - 1; i++)
{
item += int.Parse(dataGridSales.Rows[i].Cells[4].Value.ToString());
}
textBox4.Text = item.ToString();
textBox1.Text = item.ToString();
}
}
else
{
MessageBox.Show("Invalid Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
catch (NullReferenceException ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}