protected void DMGridApproval_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e) { ASPxGridView grid = sender as ASPxGridView; ASPxTextBox qty = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ApprovedQty"], "ApprovedQtyDM") as ASPxTextBox; ASPxTextBox cost = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ApprovedCost"], "ApprovedCostDM") as ASPxTextBox; ASPxTextBox total = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ApprovedTotalCost"], "ApprovedTotalCostDM") as ASPxTextBox; string PK = e.Keys[0].ToString(); Double qty_float = Convert.ToDouble(qty.Value.ToString()); Double cost_float = Convert.ToDouble(cost.Value.ToString()); Double total_float = Convert.ToDouble(total.Value.ToString()); SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString()); conn.Open(); string update = "UPDATE " + MRPClass.DirectMatTable() + " SET [ApprovedQty] = @QTY, [ApprovedCost] = @COST, [ApprovedTotalCost] = @TOTAL WHERE [PK] = @PK"; SqlCommand cmd = new SqlCommand(update, conn); cmd.Parameters.AddWithValue("@PK", PK); cmd.Parameters.AddWithValue("@QTY", qty_float); cmd.Parameters.AddWithValue("@COST", cost_float); cmd.Parameters.AddWithValue("@TOTAL", total_float); cmd.CommandType = CommandType.Text; int result = cmd.ExecuteNonQuery(); if (result > 0) { MRPClass.UpdateLastModified(conn, docnumber); string remarks = MRPClass.directmaterials_logs + "-" + MRPClass.edit_logs; MRPClass.AddLogsMOPList(conn, mrp_key, remarks); } conn.Close(); e.Cancel = true; grid.CancelEdit(); bindDM = true; BindDirectMaterials(docnumber); }
protected void Create_Click(object sender, EventArgs e) { CheckCreatorKey(); SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString()); conn.Open(); if (DocNumber.Value != null) { ExpDelivery.ClientEnabled = true; Vendor.ClientEnabled = true; Currency.ClientEnabled = true; Site.ClientEnabled = true; Terms.ClientEnabled = true; WareHouse.ClientEnabled = true; Location.ClientEnabled = true; ProCategory.ClientEnabled = true; } List <object> selectedValues = POAddEditGrid.GetSelectedFieldValues(new string[] { "PK", "TableIdentifier", "MRPCategory", "Item", "Qty", "UOM", "Cost", "TotalCost", "POQty", "POCost", "POTotalCost", "TaxGroup", "TaxItemGroup" }) as List <object>; if (selectedValues.Count == 0) { ItemsEmpty.HeaderText = "Alert"; ItemsEmptyLabel.Text = "No Selected Items"; ItemsEmpty.ShowOnPageLoad = true; } foreach (object[] obj in selectedValues) { string pk = obj[0].ToString(); string identifier = obj[1].ToString(); string category = obj[2].ToString(); string item = obj[3].ToString(); int slashIndex = item.IndexOf("/"); string item_code = item.Substring(0, slashIndex); string qty = obj[4].ToString(); string uom = obj[5].ToString(); string cost = obj[6].ToString(); string total = obj[7].ToString(); string po_qty = obj[8].ToString(); string po_cost = obj[9].ToString(); string po_total = obj[10].ToString(); string taxgroup = obj[11].ToString(); string taxitem = obj[12].ToString(); if (!string.IsNullOrEmpty(pk) && !string.IsNullOrEmpty(identifier) && !string.IsNullOrEmpty(category) && !string.IsNullOrEmpty(item) && !string.IsNullOrEmpty(qty) && !string.IsNullOrEmpty(uom) && !string.IsNullOrEmpty(cost) && !string.IsNullOrEmpty(total) && !string.IsNullOrEmpty(po_qty) && !string.IsNullOrEmpty(po_cost) && !string.IsNullOrEmpty(po_total) && !string.IsNullOrEmpty(taxgroup) && !string.IsNullOrEmpty(taxitem)) { //CREATE PO NUMBER //Declare Variables string DocPref = "", strDocNum = ""; int DocNum = 0; //QUERY PO NUMBER TO START TO... string query = "SELECT [DocumentPrefix],[DocumentNum] FROM " + MRPClass.DocNumberTableName() + " where DocumentPrefix = 'PO'"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //GET DATA... DocPref = reader[0].ToString(); DocNum = Convert.ToInt32(reader[1].ToString()); } reader.Close(); //INCREASE IN DOCNUMBER INTEGER (SEE SQL DATABASE FOR CLARIFICATION) DocNum += 1; strDocNum = DocNum.ToString("00000000#"); //CREATED PO NUMBER string PONumber = DocPref + "-" + Session["EntityCode"].ToString() + "-" + strDocNum; string MRPnumber = DocNumber.Value.ToString(); //UPDATE PO NUMBER IS INCREASE (SEE SQL DATABASE FOR CLARIFICATION) string update = "UPDATE " + MRPClass.DocNumberTableName() + " SET [DocumentNum] = '" + DocNum + "' WHERE [DocumentPrefix] = 'PO'"; cmd = new SqlCommand(update, conn); int result = cmd.ExecuteNonQuery(); if (result == 0) { return; } string terms = ""; if (Terms.Value != null)//IF TERMS COMBOBOX NULL/EMPTY VALUE { terms = Terms.Value.ToString(); } //INSERT INFO IN PO CREATION TABLE string insert = "INSERT INTO " + MRPClass.POTableName() + " ([MRPNumber],[PONumber],[CreatorKey], [ExpectedDate] ,[VendorCode], [PaymentTerms], [CurrencyCode], [InventSite], [InventSiteWarehouse], [InventSiteWarehouseLocation]) VALUES (@MRPNumber, @PONumber, @CreatorKey, @expdate, @vendor, @terms, @currency, @site, @warehouse, @location)"; cmd = new SqlCommand(insert, conn); cmd.Parameters.AddWithValue("MRPNumber", MRPnumber); cmd.Parameters.AddWithValue("PONumber", PONumber); cmd.Parameters.AddWithValue("CreatorKey", Session["CreatorKey"].ToString()); cmd.Parameters.AddWithValue("@expdate", ExpDelivery.Value.ToString()); cmd.Parameters.AddWithValue("@vendor", Vendor.Value.ToString()); cmd.Parameters.AddWithValue("@terms", terms); cmd.Parameters.AddWithValue("@currency", Currency.Value.ToString()); cmd.Parameters.AddWithValue("@site", Site.Value.ToString()); cmd.Parameters.AddWithValue("@warehouse", WareHouse.Value.ToString()); cmd.Parameters.AddWithValue("@location", Location.Value.ToString()); cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); switch (identifier) { case "1": //Direct Materials string update_materials = "UPDATE " + MRPClass.DirectMatTable() + " SET [QtyPO] = '" + po_qty + "' WHERE [PK] = '" + pk + "'"; SqlCommand cmd_mat = new SqlCommand(update_materials, conn); cmd_mat.ExecuteNonQuery(); string insert_mat_po = "INSERT " + MRPClass.POCreationTableName() + " ([PONumber],[ItemCode],[TaxGroup],[TaxItemGroup],[Qty],[Cost],[TotalCost], [ItemPK], [Identifier]) VALUES (@ponumber, @code, @taxgroup, @taxitem, @poqty, @pocost, @pototal, @itempk, @identifier)"; SqlCommand cmd_mat_po = new SqlCommand(insert_mat_po, conn); cmd_mat_po.Parameters.AddWithValue("@ponumber", PONumber); cmd_mat_po.Parameters.AddWithValue("@code", item_code); cmd_mat_po.Parameters.AddWithValue("@taxgroup", taxgroup); cmd_mat_po.Parameters.AddWithValue("@taxitem", taxitem); cmd_mat_po.Parameters.AddWithValue("@poqty", Convert.ToDouble(po_qty)); cmd_mat_po.Parameters.AddWithValue("@pocost", Convert.ToDouble(po_cost)); cmd_mat_po.Parameters.AddWithValue("@pototal", Convert.ToDouble(po_total)); cmd_mat_po.Parameters.AddWithValue("@itempk", pk); cmd_mat_po.Parameters.AddWithValue("@identifier", identifier); cmd_mat_po.CommandType = CommandType.Text; cmd_mat_po.ExecuteNonQuery(); break; case "2": //Opex string update_opex = "UPDATE " + MRPClass.OpexTable() + " SET [QtyPO] = '" + po_qty + "' WHERE [PK] = '" + pk + "'"; SqlCommand cmd_opex = new SqlCommand(update_opex, conn); cmd_opex.ExecuteNonQuery(); string insert_opex_po = "INSERT " + MRPClass.POCreationTableName() + " ([PONumber],[ItemCode],[TaxGroup],[TaxItemGroup],[Qty],[Cost],[TotalCost], [ItemPK], [Identifier]) VALUES (@ponumber, @code, @taxgroup, @taxitem, @poqty, @pocost, @pototal, @itempk, @identifier)"; SqlCommand cmd_opex_po = new SqlCommand(insert_opex_po, conn); cmd_opex_po.Parameters.AddWithValue("@ponumber", PONumber); cmd_opex_po.Parameters.AddWithValue("@code", item_code); cmd_opex_po.Parameters.AddWithValue("@taxgroup", taxgroup); cmd_opex_po.Parameters.AddWithValue("@taxitem", taxitem); cmd_opex_po.Parameters.AddWithValue("@poqty", Convert.ToDouble(po_qty)); cmd_opex_po.Parameters.AddWithValue("@pocost", Convert.ToDouble(po_cost)); cmd_opex_po.Parameters.AddWithValue("@pototal", Convert.ToDouble(po_total)); cmd_opex_po.Parameters.AddWithValue("@itempk", pk); cmd_opex_po.Parameters.AddWithValue("@identifier", identifier); cmd_opex_po.CommandType = CommandType.Text; cmd_opex_po.ExecuteNonQuery(); break; } conn.Close(); Server.Transfer("mrp_pocreation.aspx"); } else { ItemsEmpty.HeaderText = "Alert"; ItemsEmptyLabel.Text = "Some selected items are empty"; ItemsEmpty.ShowOnPageLoad = true; } } conn.Close(); }
protected void POCreatedGrid_RowUpdating(object sender, DevExpress.Web.Data.ASPxDataUpdatingEventArgs e) { ASPxGridView grid = sender as ASPxGridView; string itempk = "", identifier = ""; string PK = e.Keys[0].ToString(); SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString()); conn.Open(); string query = "SELECT * FROM " + MRPClass.POCreationTableName() + " WHERE [PK] = '" + PK + "'"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { itempk = reader["ItemPK"].ToString(); identifier = reader["Identifier"].ToString(); } reader.Close(); ASPxComboBox itemcode = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ItemCode"], "ItemCode") as ASPxComboBox; ASPxComboBox taxgroup = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TaxGroup"], "TaxGroup") as ASPxComboBox; ASPxComboBox taxitemgroup = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TaxItemGroup"], "TaxItemGroup") as ASPxComboBox; ASPxTextBox qty = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["Qty"], "Qty") as ASPxTextBox; ASPxTextBox cost = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["Cost"], "Cost") as ASPxTextBox; ASPxTextBox totalcost = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TotalCost"], "TotalCost") as ASPxTextBox; string code = itemcode.Value.ToString(); string tax_group = taxgroup.Value.ToString(); string tax_itemgroup = taxitemgroup.Value.ToString(); string qtystr = qty.Value.ToString(); string coststr = cost.Value.ToString(); string totalstr = totalcost.Value.ToString(); string update = "UPDATE " + MRPClass.POCreationTableName() + " SET [ItemCode] = @ItemCode, [TaxGroup] = @TaxGroup, [TaxItemGroup] = @TaxItemGroup, [Qty] = @Qty, [Cost] = @Cost, [TotalCost] = @TotalCost WHERE [PK] = '" + PK + "'"; cmd = new SqlCommand(update, conn); cmd.Parameters.AddWithValue("@ItemCode", code); cmd.Parameters.AddWithValue("@TaxGroup", tax_group); cmd.Parameters.AddWithValue("@TaxItemGroup", tax_itemgroup); cmd.Parameters.AddWithValue("@Qty", qtystr); cmd.Parameters.AddWithValue("@Cost", coststr); cmd.Parameters.AddWithValue("@TotalCost", totalstr); cmd.CommandType = System.Data.CommandType.Text; int result = cmd.ExecuteNonQuery(); if (result > 0) { switch (identifier) { case "1": //Direct Materials string update_DM = "UPDATE " + MRPClass.DirectMatTable() + " SET [QtyPO] = @qty WHERE [PK] = '" + itempk + "'"; SqlCommand cmd_DM = new SqlCommand(update_DM, conn); cmd_DM.Parameters.AddWithValue("@qty", qtystr); cmd_DM.CommandType = System.Data.CommandType.Text; cmd_DM.ExecuteNonQuery(); break; case "2": //Opex string update_OP = "UPDATE " + MRPClass.OpexTable() + " SET [QtyPO] = @qty WHERE [PK] = '" + itempk + "'"; SqlCommand cmd_OP = new SqlCommand(update_OP, conn); cmd_OP.Parameters.AddWithValue("@qty", qtystr); cmd_OP.CommandType = System.Data.CommandType.Text; int aa = cmd_OP.ExecuteNonQuery(); if (aa > 0) { MRPClass.PrintString("successfully....."); } break; } } conn.Close(); e.Cancel = true; grid.CancelEdit(); BindGrid(); }
protected void POCreatedGrid_RowDeleting(object sender, DevExpress.Web.Data.ASPxDataDeletingEventArgs e) { ASPxGridView grid = sender as ASPxGridView; string itempk = "", identifier = "", qty = "", cost = "", total = ""; string PK = e.Keys[0].ToString(); SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString()); conn.Open(); string query = "SELECT * FROM " + MRPClass.POCreationTableName() + " WHERE [PK] = '" + PK + "'"; SqlCommand cmd = new SqlCommand(query, conn); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { itempk = reader["ItemPK"].ToString(); identifier = reader["Identifier"].ToString(); qty = reader["Qty"].ToString(); cost = reader["Cost"].ToString(); total = reader["TotalCost"].ToString(); } reader.Close(); string delete = "DELETE FROM " + MRPClass.POCreationTableName() + " WHERE [PK] = '" + PK + "'"; cmd = new SqlCommand(delete, conn); int res = cmd.ExecuteNonQuery(); if (res > 0) { Double update_qtypo = 0; switch (identifier) { case "1": //Direct Materials Double dm_qtypo = 0; string query_dm = "SELECT [QtyPO] FROM " + MRPClass.DirectMatTable() + " WHERE [PK] = '" + itempk + "'"; cmd = new SqlCommand(query_dm, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { dm_qtypo = Convert.ToDouble(reader[0].ToString()); } reader.Close(); update_qtypo = dm_qtypo - Convert.ToDouble(qty); string update_dm = "UPDATE " + MRPClass.DirectMatTable() + " SET [QtyPO] = '" + update_qtypo + "' WHERE [PK] = '" + itempk + "'"; cmd = new SqlCommand(update_dm, conn); cmd.ExecuteNonQuery(); break; case "2": //Opex Double op_qtypo = 0; string query_op = "SELECT [QtyPO] FROM " + MRPClass.OpexTable() + " WHERE [PK] = '" + itempk + "'"; cmd = new SqlCommand(query_op, conn); reader = cmd.ExecuteReader(); while (reader.Read()) { op_qtypo = Convert.ToDouble(reader[0].ToString()); } reader.Close(); update_qtypo = op_qtypo - Convert.ToDouble(qty); string update_op = "UPDATE " + MRPClass.OpexTable() + " SET [QtyPO] = '" + update_qtypo + "' WHERE [PK] = '" + itempk + "'"; cmd = new SqlCommand(update_op, conn); cmd.ExecuteNonQuery(); break; } } conn.Close(); }
protected void POCreatedGrid_RowInserting(object sender, DevExpress.Web.Data.ASPxDataInsertingEventArgs e) { ASPxGridView grid = sender as ASPxGridView; ASPxHiddenField pk_identifier = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ItemCode"], "pk_identifier_ingrid") as ASPxHiddenField; ASPxComboBox itemcode = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["ItemCode"], "ItemCode") as ASPxComboBox; ASPxComboBox taxgroup = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TaxGroup"], "TaxGroup") as ASPxComboBox; ASPxComboBox taxitemgroup = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TaxItemGroup"], "TaxItemGroup") as ASPxComboBox; ASPxTextBox qty = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["Qty"], "Qty") as ASPxTextBox; ASPxTextBox cost = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["Cost"], "Cost") as ASPxTextBox; ASPxTextBox totalcost = grid.FindEditRowCellTemplateControl((GridViewDataColumn)grid.Columns["TotalCost"], "TotalCost") as ASPxTextBox; string itempk = pk_identifier["hidden_pk"].ToString(); string identifier = pk_identifier["hidden_identifier"].ToString(); string code = itemcode.Value.ToString(); string tax_group = taxgroup.Value.ToString(); string tax_itemgroup = taxitemgroup.Value.ToString(); string qtystr = qty.Value.ToString(); string coststr = cost.Value.ToString(); string totalstr = totalcost.Value.ToString(); SqlConnection conn = new SqlConnection(GlobalClass.SQLConnString()); conn.Open(); //INSERT NEW ROW IN PO CREATION DETAILS string insert = "INSERT INTO " + MRPClass.POCreationTableName() + " ([PONumber] ,[ItemPK], [Identifier] ,[ItemCode] ,[TaxGroup] ,[TaxItemGroup] ,[Qty],[Cost] ,[TotalCost]) VALUES (@PONumber, @ItemPK, @Identifier, @ItemCode, @TaxGroup, @TaxItemGroup, @Qty, @Cost, @TotalCost)"; SqlCommand cmd = new SqlCommand(insert, conn); cmd.Parameters.AddWithValue("@PONumber", ponumber); cmd.Parameters.AddWithValue("@ItemPK", itempk); cmd.Parameters.AddWithValue("@Identifier", identifier); cmd.Parameters.AddWithValue("@ItemCode", code); cmd.Parameters.AddWithValue("@TaxGroup", tax_group); cmd.Parameters.AddWithValue("@TaxItemGroup", tax_itemgroup); cmd.Parameters.AddWithValue("@Qty", qtystr); cmd.Parameters.AddWithValue("@Cost", coststr); cmd.Parameters.AddWithValue("@TotalCost", totalstr); cmd.CommandType = System.Data.CommandType.Text; int result = cmd.ExecuteNonQuery(); MRPClass.PrintString("identifier:" + identifier); MRPClass.PrintString("identifier:" + itempk); if (result > 0) { switch (identifier) { case "1": //Direct Materials string update_DM = "UPDATE " + MRPClass.DirectMatTable() + " SET [QtyPO] = @qty WHERE [PK] = '" + itempk + "'"; SqlCommand cmd_DM = new SqlCommand(update_DM, conn); cmd_DM.Parameters.AddWithValue("@qty", qtystr); cmd_DM.CommandType = System.Data.CommandType.Text; cmd_DM.ExecuteNonQuery(); break; case "2": //Opex string update_OP = "UPDATE " + MRPClass.OpexTable() + " SET [QtyPO] = @qty WHERE [PK] = '" + itempk + "'"; SqlCommand cmd_OP = new SqlCommand(update_OP, conn); cmd_OP.Parameters.AddWithValue("@qty", qtystr); cmd_OP.CommandType = System.Data.CommandType.Text; int aa = cmd_OP.ExecuteNonQuery(); if (aa > 0) { MRPClass.PrintString("successfully....."); } break; } } conn.Close(); e.Cancel = true; grid.CancelEdit(); BindGrid(); }