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