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); } }