private void BTN_Search_Click(object sender, EventArgs e)
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
DataTable dt = new();
foreach (DataRow item in dt.Rows)
{
int n = dgv_StockView.Rows.Add();
dgv_StockView.Rows[n].Cells[0].Value = item["Stock_Number"].ToString();
dgv_StockView.Rows[n].Cells[1].Value = item["Product_ID"].ToString();
dgv_StockView.Rows[n].Cells[2].Value = item["Product_Name"].ToString();
dgv_StockView.Rows[n].Cells[3].Value = item["Quantity"].ToString();
dgv_StockView.Rows[n].Cells[5].Value = Convert.ToDateTime(item["Transaction_Date"].ToString()).ToString("dd/MM/yyyy");
}
SqlDataAdapter sda = new();
if (TXT_StockNumber.Text.Length > 0)
{
sda = new(@"SELECT * FROM [dbo].[Stocks] LEFT JOIN [dbo].[Products] ON Stocks.Product_ID = Products.Product_ID WHERE [Stock_Number] LIKE '%" + TXT_StockNumber.Text + "%'", con.Connection);
sda.Fill(dt);
}
else if (TXT_ProductCode.Text.Length > 0)
{
sda = new(@"SELECT * FROM [dbo].[Stocks] LEFT JOIN [dbo].[Products] ON Stocks.Product_ID = Products.Product_ID WHERE Stocks.Product_ID LIKE '%" + TXT_ProductCode.Text + "%'", con.Connection);
sda.Fill(dt);
}
else if (TXT_ProductName.Text.Length > 0)
{
sda = new(@"SELECT * FROM [dbo].[Stocks] LEFT JOIN [dbo].[Products] ON Stocks.Product_ID = Products.Product_ID WHERE Product_Name LIKE '%" + TXT_ProductName.Text + "%'", con.Connection);
sda.Fill(dt);
}
dgv_StockView.Rows.Clear();
foreach (DataRow item in dt.Rows)
{
int n = dgv_StockView.Rows.Add();
dgv_StockView.Rows[n].Cells[0].Value = item["Stock_Number"].ToString();
dgv_StockView.Rows[n].Cells[1].Value = item["Product_ID"].ToString();
dgv_StockView.Rows[n].Cells[2].Value = item["Product_Name"].ToString();
dgv_StockView.Rows[n].Cells[3].Value = item["Quantity"].ToString();
dgv_StockView.Rows[n].Cells[4].Value = Convert.ToDateTime(item["Transaction_Date"].ToString()).ToString("dd/MM/yyyy");
}
con.CloseCon();
}
private void BTN_delete_Click(object sender, EventArgs e)
{
DialogResult dialogResult = MessageBox.Show("Are You sure want you to delete?", "Message", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (dialogResult == DialogResult.Yes)
{
if (dgv_OrdersView.SelectedRows.Count > 0)
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
string orderNumber = dgv_OrdersView.CurrentRow.Cells[0].Value.ToString();
string sqlQuery = @"DELETE FROM [dbo].[Orders] WHERE [Order_ID] = '" + Convert.ToInt32(orderNumber) + "'";
SqlCommand cmd = new(sqlQuery, con.Connection);
cmd.ExecuteNonQuery();
con.CloseCon();
LoadData();
}
}
}
private void BTN_add_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(TXT_OrderNumber.Text))
{
FRM_Products.Show_MSG(LBL_AddMSG, "Insert order number", Color.Red);
}
else
{
selectedStockNumber.Clear();
quantityList.Clear();
productList.Clear();
foreach (DataGridViewRow rows in dgv_ProductView.Rows)
{
if (Convert.ToBoolean(rows.Cells["clm_Select"].Value))
{
selectedStockNumber.Add(Convert.ToInt32(rows.Cells["clm_StockNumber"].Value));
quantityList.Add(Convert.ToInt32(rows.Cells["clm_Quantity"].Value));
productList.Add(Convert.ToInt32(rows.Cells["clm_ProductCode"].Value));
}
}
ConnectionClass con = new ConnectionClass();
con.OpenCon();
int i = 0;
foreach (var item in selectedStockNumber)
{
string sqlQuery = @"INSERT INTO Orders (Order_Nu, Product_ID, Quantity, Stock_Number) VALUES ('" + TXT_OrderNumber.Text + "','" + productList[i] + "', '" + quantityList[i] + "','" + item + "') ";
SqlCommand cmd = new SqlCommand(sqlQuery, con.Connection);
cmd.ExecuteNonQuery();
i++;
}
int n = 0;
foreach (var item in selectedStockNumber)
{
string sqlQuery = @"UPDATE [dbo].[Stocks] SET Quantity = Quantity - '" + quantityList[n] + "' WHERE Stock_Number = '" + selectedStockNumber[n] + "'";
SqlCommand cmd = new SqlCommand(sqlQuery, con.Connection);
cmd.ExecuteNonQuery();
n++;
}
FRM_Products.Show_MSG(LBL_AddMSG, "Order added", Color.Green);
con.CloseCon();
}
}
public void LoadData()
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
SqlDataAdapter sda = new("SELECT * FROM [dbo].[Orders] LEFT JOIN [dbo].[Products] ON Orders.Product_ID = Products.Product_ID", con.Connection);
DataTable dt = new();
sda.Fill(dt);
dgv_OrdersView.Rows.Clear();
foreach (DataRow item in dt.Rows)
{
int n = dgv_OrdersView.Rows.Add();
dgv_OrdersView.Rows[n].Cells[0].Value = item["Order_ID"].ToString();
dgv_OrdersView.Rows[n].Cells[1].Value = item["Order_Nu"].ToString();
dgv_OrdersView.Rows[n].Cells[2].Value = item["Product_Name"].ToString();
dgv_OrdersView.Rows[n].Cells[3].Value = item["Quantity"].ToString();
}
con.CloseCon();
}
public void FillComboBox()
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
SqlDataAdapter sda = new SqlDataAdapter(@"SELECT Product_ID, Product_Name FROM [dbo].[Products] WHERE Product_Status = 1", con.Connection);
DataTable dt = new DataTable();
sda.Fill(dt);
DataRow row = dt.NewRow();
row[0] = DBNull.Value;
row[1] = "Please select";
dt.Rows.InsertAt(row, 0);
CBX_ProductCode.DataSource = dt;
CBX_ProductCode.ValueMember = "Product_ID";
CBX_ProductName.DataSource = dt;
CBX_ProductName.DisplayMember = "Product_Name";
}
public void LoadData()
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
SqlDataAdapter sda = new("SELECT * FROM [dbo].[Stocks] LEFT JOIN [dbo].[Products] ON Stocks.Product_ID = Products.Product_ID", con.Connection);
DataTable dt = new();
sda.Fill(dt);
dgv_StockView.Rows.Clear();
foreach (DataRow item in dt.Rows)
{
int n = dgv_StockView.Rows.Add();
dgv_StockView.Rows[n].Cells[0].Value = item["Stock_Number"].ToString();
dgv_StockView.Rows[n].Cells[1].Value = item["Product_ID"].ToString();
dgv_StockView.Rows[n].Cells[2].Value = item["Product_Name"].ToString();
dgv_StockView.Rows[n].Cells[3].Value = item["Quantity"].ToString();
dgv_StockView.Rows[n].Cells[4].Value = Convert.ToDateTime(item["Transaction_Date"].ToString()).ToString("dd/MM/yyyy");
}
con.CloseCon();
}
private void BTN_Login_Click(object sender, EventArgs e)
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
SqlDataAdapter sda = new(@"SELECT * FROM [Warehouse].[dbo].[Users]
WHERE Username = '******' AND Password = '******'", con.Connection);
DataTable dt = new();
sda.Fill(dt);
if (dt.Rows.Count == 1)
{
this.Hide();
FRM_WarehouseMain main = new();
main.Show();
}
else
{
MessageBox.Show("Invalid Username or Password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
BTN_Clear_Click(sender, e);
}
}
private void BTN_Add_Click(object sender, EventArgs e)
{
string ProductCode = TXT_ProductCode.Text;
string ProductName = TXT_ProductName.Text;
int ProductStatus = CBX_ProductStatus.SelectedIndex;
if (FRM_Products.Validation(ERP_ProductAdd, ProductCode, ProductName, ProductStatus, TXT_ProductCode, TXT_ProductName, CBX_ProductStatus))
{
ConnectionClass con = new ConnectionClass();
con.OpenCon();
bool status;
if (CBX_ProductStatus.SelectedIndex == 0)
{
status = true;
}
else
{
status = false;
}
if (FRM_Products.IfProductExists(con.Connection, TXT_ProductCode.Text))
{
FRM_Products.Show_MSG(LBL_AddMSG, "Product with this code already exists", Color.OrangeRed);
}
else
{
string sqlQuery = @"INSERT INTO [dbo].[Products]
([Product_ID],
[Product_Name],
[Product_Status])
VALUES
('" + TXT_ProductCode.Text + "','" + TXT_ProductName.Text + "','" + status + "')";
SqlCommand cmd = new(sqlQuery, con.Connection);
cmd.ExecuteNonQuery();
con.CloseCon();
FRM_Products.Show_MSG(LBL_AddMSG, "Product added to list", Color.LightGreen);
}
}
}
