public static bool UpdatePOStatus(SqlConnection connection, PO_Header_Model model) { bool returnValue = true; var GUID = SQL_Transact.GenerateGUID(); SQL_Transact.BeginTransaction(connection, GUID); try { StringBuilder sQuery = new StringBuilder(); sQuery.Append(@"UPDATE a_PO_Header SET POStatus = @POStatus WHERE idPOHeader = @idPOHeader "); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; SqlParameter parm2 = new SqlParameter { ParameterName = "@idPOHeader", SqlDbType = SqlDbType.Int, Value = model.idPOHeader }; cmd.Parameters.Add(parm2); SqlParameter parm17 = new SqlParameter { ParameterName = "@POStatus", SqlDbType = SqlDbType.NVarChar, Value = model.POStatus }; cmd.Parameters.Add(parm17); if (cmd.ExecuteNonQuery() >= 1) { returnValue = true; } cmd.Dispose(); cmd.Parameters.Clear(); } SQL_Transact.CommitTransaction(connection, GUID); } catch { SQL_Transact.RollbackTransaction(connection, GUID); } return(returnValue); }
protected void btnSave_Click(object sender, EventArgs e) { if (Convert.ToBoolean(Session["ComputeClick"]) == true) { foreach (GridViewRow row in gvItems.Rows) { var objPODetail = new PO_Detail_Model { idPODetail = int.Parse(row.Cells[0].Text), idPOHeader = int.Parse(row.Cells[1].Text), idItem = int.Parse(row.Cells[2].Text), Quantity = decimal.Parse(row.Cells[5].Text), Price = decimal.Parse(row.Cells[6].Text), Tax = decimal.Parse(row.Cells[7].Text), Amount = decimal.Parse(row.Cells[8].Text), Final_Cost = decimal.Parse(row.Cells[9].Text), Unit_Comp = row.Cells[10].Text, isReceived = "No", Partial_Remaining = 0 }; PO_Detail.Update(oCon, objPODetail); } var pO_Charges_Model = new PO_Charges_Model { idPOHeader = Convert.ToInt32(Session["idPOHeader"].ToString()), Brokerage = Convert.ToDecimal(txtBrokerage.Text), CEDEC = Convert.ToDecimal(txtCEDEC.Text), CustomsStamps = Convert.ToDecimal(txtCustomsStamps.Text), DeliveryCharges = Convert.ToDecimal(txtDeliveryCharges.Text), DocumentaryStamps = Convert.ToDecimal(txtDocumentaryStamps.Text), DocumentationCharges = Convert.ToDecimal(txtDocumentationCharges.Text), ForkliftCost = Convert.ToDecimal(txtForkliftCost.Text), FreightCharges = Convert.ToDecimal(txtFreightCharges.Text), HandlingFee = Convert.ToDecimal(txtHandlingFee.Text), ImportationInsurance = Convert.ToDecimal(txtImportationInsurance.Text), ImportDuties = Convert.ToDecimal(txtImportDuties.Text), ImportProcessingFee = Convert.ToDecimal(txtImportProcessingFee.Text), Miscellaneous = Convert.ToDecimal(txtMiscellaneous.Text), NotarialFee = Convert.ToDecimal(txtNotarialFee.Text), OtherCharges = Convert.ToDecimal(txtOtherCharges.Text), ProcessingFee = Convert.ToDecimal(txtProcessingFee.Text), WarehouseStorageCharges = Convert.ToDecimal(txtWarehouseStorageCharges.Text), Xerox = Convert.ToDecimal(txtXerox.Text) }; PO_Charges.Save(oCon, pO_Charges_Model); var poheader = new PO_Header_Model { idPOHeader = Convert.ToInt32(Session["idPOHeader"].ToString()), POStatus = "Announced", Total_Charges = Convert.ToDecimal(txtTotalCharges.Text), ImportShipmentNumber = txtImpShpNum.Text }; PO_Header.UpdatePOStatus(oCon, poheader); PO_Header.UpdateTotalCharges(oCon, poheader); defaultSettings(); GetPO(); } else { HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "Cost not yet computed!" + ControlChars.Quote + ");</script>"); } }
public static bool Update(SqlConnection connection, PO_Header_Model model) { bool returnValue = true; var GUID = SQL_Transact.GenerateGUID(); SQL_Transact.BeginTransaction(connection, GUID); StringBuilder sQuery = new StringBuilder(); sQuery.Append(@"UPDATE a_PO_Header SET PO_Number = @PO_Number ,Terms = @Terms ,FOB_Point = @FOB_Point ,Shipping_Via = @Shipping_Via ,Currency = @Currency ,idSupplier = @idSupplier ,PO_Total = @PO_Total ,PO_Quantity = @PO_Quantity ,Total_Charges = @Total_Charges ,Forex_Rate = @Forex_Rate ,Created_By = @Created_By ,Remarks = @Remarks ,POStatus = @POStatus ,ImportShipmentNumber = @ImportShipmentNumber WHERE idPOHeader = @idPOHeader "); using (SqlCommand cmd = new SqlCommand()) { try { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; SqlParameter parm1 = new SqlParameter { ParameterName = "@PO_Number", SqlDbType = SqlDbType.NVarChar, Value = model.PO_Number }; cmd.Parameters.Add(parm1); SqlParameter parm2 = new SqlParameter { ParameterName = "@idPOHeader", SqlDbType = SqlDbType.Int, Value = model.idPOHeader }; cmd.Parameters.Add(parm2); SqlParameter parm3 = new SqlParameter { ParameterName = "@idSupplier", SqlDbType = SqlDbType.NVarChar, Value = model.idSupplier }; cmd.Parameters.Add(parm3); //SqlParameter parm4 = new SqlParameter //{ // ParameterName = "@PO_Date", // SqlDbType = SqlDbType.DateTime, // Value = model.PO_Date //}; //cmd.Parameters.Add(parm4); //SqlParameter parm5 = new SqlParameter //{ // ParameterName = "@Delivery_Date", // SqlDbType = SqlDbType.DateTime, // Value = model.Delivery_Date //}; //cmd.Parameters.Add(parm5); SqlParameter parm6 = new SqlParameter { ParameterName = "@Terms", SqlDbType = SqlDbType.NVarChar, Value = model.Terms }; cmd.Parameters.Add(parm6); SqlParameter parm7 = new SqlParameter { ParameterName = "@FOB_Point", SqlDbType = SqlDbType.NVarChar, Value = model.FOB_Point }; cmd.Parameters.Add(parm7); SqlParameter parm8 = new SqlParameter { ParameterName = "@Shipping_Via", SqlDbType = SqlDbType.NVarChar, Value = model.Shipping_Via }; cmd.Parameters.Add(parm8); SqlParameter parm9 = new SqlParameter { ParameterName = "@Currency", SqlDbType = SqlDbType.NVarChar, Value = model.Currency }; cmd.Parameters.Add(parm9); SqlParameter parm10 = new SqlParameter { ParameterName = "@PO_Total", SqlDbType = SqlDbType.Decimal, Value = model.PO_Total }; cmd.Parameters.Add(parm10); SqlParameter parm11 = new SqlParameter { ParameterName = "@PO_Quantity", SqlDbType = SqlDbType.Int, Value = model.PO_Quantity }; cmd.Parameters.Add(parm11); SqlParameter parm12 = new SqlParameter { ParameterName = "@Total_Charges", SqlDbType = SqlDbType.Decimal, Value = model.Total_Charges }; cmd.Parameters.Add(parm12); SqlParameter parm13 = new SqlParameter { ParameterName = "@Forex_Rate", SqlDbType = SqlDbType.Decimal, Value = model.Forex_Rate }; cmd.Parameters.Add(parm13); //SqlParameter parm14 = new SqlParameter //{ // ParameterName = "@PR_Number", // SqlDbType = SqlDbType.NVarChar, // Value = model.PR_Number //}; //cmd.Parameters.Add(parm14); SqlParameter parm15 = new SqlParameter { ParameterName = "@Created_By", SqlDbType = SqlDbType.NVarChar, Value = model.Created_By }; cmd.Parameters.Add(parm15); SqlParameter parm16 = new SqlParameter { ParameterName = "@Remarks", SqlDbType = SqlDbType.NVarChar, Value = model.Remarks }; cmd.Parameters.Add(parm16); SqlParameter parm17 = new SqlParameter { ParameterName = "@POStatus", SqlDbType = SqlDbType.NVarChar, Value = model.POStatus }; cmd.Parameters.Add(parm17); SqlParameter parm18 = new SqlParameter { ParameterName = "@ImportShipmentNumber", SqlDbType = SqlDbType.NVarChar, Value = model.ImportShipmentNumber }; cmd.Parameters.Add(parm18); if (cmd.ExecuteNonQuery() >= 1) { returnValue = true; cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.CommitTransaction(connection, GUID); } } catch { cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.RollbackTransaction(connection, GUID); } } return(returnValue); }
public static List <PO_Header_Model> RetrieveData(SqlConnection connection, string PO_Number) { StringBuilder sQuery = new StringBuilder(); sQuery.Append(@"SELECT idPOHeader ,PO_Number ,PO_Date ,Delivery_Date ,Terms ,FOB_Point ,Shipping_Via ,Currency ,idSupplier ,PO_Quantity ,PO_Total ,Total_Charges ,Forex_Rate ,PR_Number ,Created_By ,Remarks ,POStatus ,ImportShipmentNumber FROM a_PO_Header "); if (PO_Number != "") { sQuery.Append(" WHERE PO_Number LIKE '%' + @PO_Number + '%'"); } sQuery.Append(" ORDER BY PO_Number DESC "); var lmodel = new List <PO_Header_Model>(); connection.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; if (PO_Number != "") { SqlParameter parm2 = new SqlParameter { ParameterName = "@PO_Number", SqlDbType = SqlDbType.NVarChar, Value = PO_Number }; cmd.Parameters.Add(parm2); } var oreader = cmd.ExecuteReader(); while (oreader.Read()) { PO_Header_Model oModel = new PO_Header_Model { idPOHeader = (int)oreader["idPOHeader"], PO_Number = (string)oreader["PO_Number"], PO_Date = (DateTime)oreader["PO_Date"], Delivery_Date = (DateTime)oreader["Delivery_Date"], Terms = (string)oreader["Terms"], FOB_Point = (string)oreader["FOB_Point"], Shipping_Via = (string)oreader["Shipping_Via"], Currency = (string)oreader["Currency"], PO_Total = (decimal)oreader["PO_Total"], idSupplier = (int)oreader["idSupplier"], PO_Quantity = (int)oreader["PO_Quantity"], Total_Charges = (decimal)oreader["Total_Charges"], Forex_Rate = (decimal)oreader["Forex_Rate"], PR_Number = (string)oreader["PR_Number"], Created_By = (string)oreader["Created_By"], Remarks = (string)oreader["Remarks"], POStatus = (string)oreader["POStatus"], ImportShipmentNumber = (string)oreader["ImportShipmentNumber"] }; lmodel.Add(oModel); } oreader.Close(); cmd.Dispose(); } connection.Close(); return(lmodel); }
public static int Save(SqlConnection connection, PO_Header_Model model) { int returnValue = 0; var GUID = SQL_Transact.GenerateGUID(); SQL_Transact.BeginTransaction(connection, GUID); StringBuilder sQuery = new StringBuilder(); sQuery.Append(@"INSERT INTO a_PO_Header (PO_Number ,PO_Date ,Delivery_Date ,Terms ,FOB_Point ,Shipping_Via ,Currency ,idSupplier ,PO_Total ,PO_Quantity ,Total_Charges ,Forex_Rate ,PR_Number ,Created_By ,Remarks ,POStatus ,ImportShipmentNumber) VALUES (@PO_Number ,@PO_Date ,@Delivery_Date ,@Terms ,@FOB_Point ,@Shipping_Via ,@Currency ,@idSupplier ,@PO_Total ,@PO_Quantity ,@Total_Charges ,@Forex_Rate ,@PR_Number ,@Created_By ,@Remarks ,@POStatus ,@ImportShipmentNumber) SELECT SCOPE_IDENTITY() as 'ID'"); using (SqlCommand cmd = new SqlCommand()) { cmd.Connection = connection; cmd.CommandText = sQuery.ToString(); cmd.CommandType = CommandType.Text; SqlParameter parm2 = new SqlParameter { ParameterName = "@PO_Number", SqlDbType = SqlDbType.NVarChar, Value = model.PO_Number }; cmd.Parameters.Add(parm2); SqlParameter parm3 = new SqlParameter { ParameterName = "@idSupplier", SqlDbType = SqlDbType.Int, Value = model.idSupplier }; cmd.Parameters.Add(parm3); SqlParameter parm4 = new SqlParameter { ParameterName = "@PO_Date", SqlDbType = SqlDbType.DateTime, Value = model.PO_Date }; cmd.Parameters.Add(parm4); SqlParameter parm5 = new SqlParameter { ParameterName = "@Delivery_Date", SqlDbType = SqlDbType.DateTime, Value = model.Delivery_Date }; cmd.Parameters.Add(parm5); SqlParameter parm6 = new SqlParameter { ParameterName = "@Terms", SqlDbType = SqlDbType.NVarChar, Value = model.Terms }; cmd.Parameters.Add(parm6); SqlParameter parm7 = new SqlParameter { ParameterName = "@FOB_Point", SqlDbType = SqlDbType.NVarChar, Value = model.FOB_Point }; cmd.Parameters.Add(parm7); SqlParameter parm8 = new SqlParameter { ParameterName = "@Shipping_Via", SqlDbType = SqlDbType.NVarChar, Value = model.Shipping_Via }; cmd.Parameters.Add(parm8); SqlParameter parm9 = new SqlParameter { ParameterName = "@Currency", SqlDbType = SqlDbType.NVarChar, Value = model.Currency }; cmd.Parameters.Add(parm9); SqlParameter parm10 = new SqlParameter { ParameterName = "@PO_Total", SqlDbType = SqlDbType.Decimal, Value = model.PO_Total }; cmd.Parameters.Add(parm10); SqlParameter parm11 = new SqlParameter { ParameterName = "@PO_Quantity", SqlDbType = SqlDbType.Int, Value = model.PO_Quantity }; cmd.Parameters.Add(parm11); SqlParameter parm12 = new SqlParameter { ParameterName = "@Total_Charges", SqlDbType = SqlDbType.Decimal, Value = model.Total_Charges }; cmd.Parameters.Add(parm12); SqlParameter parm13 = new SqlParameter { ParameterName = "@Forex_Rate", SqlDbType = SqlDbType.Decimal, Value = model.Forex_Rate }; cmd.Parameters.Add(parm13); SqlParameter parm14 = new SqlParameter { ParameterName = "@PR_Number", SqlDbType = SqlDbType.NVarChar, Value = model.PR_Number }; cmd.Parameters.Add(parm14); SqlParameter parm15 = new SqlParameter { ParameterName = "@Created_By", SqlDbType = SqlDbType.NVarChar, Value = model.Created_By }; cmd.Parameters.Add(parm15); SqlParameter parm16 = new SqlParameter { ParameterName = "@Remarks", SqlDbType = SqlDbType.NVarChar, Value = model.Remarks }; cmd.Parameters.Add(parm16); SqlParameter parm17 = new SqlParameter { ParameterName = "@POStatus", SqlDbType = SqlDbType.NVarChar, Value = model.POStatus }; cmd.Parameters.Add(parm17); SqlParameter parm18 = new SqlParameter { ParameterName = "@ImportShipmentNumber", SqlDbType = SqlDbType.NVarChar, Value = model.ImportShipmentNumber }; cmd.Parameters.Add(parm18); var oreader = cmd.ExecuteReader(); try { while (oreader.Read()) { returnValue = Convert.ToInt32(oreader["ID"].ToString()); } oreader.Close(); cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.CommitTransaction(connection, GUID); } catch { oreader.Close(); cmd.Dispose(); cmd.Parameters.Clear(); SQL_Transact.RollbackTransaction(connection, GUID); } } return(returnValue); }
protected void btnSave_Click(object sender, EventArgs e) { if (Session["Mode"].ToString() == "Add") { string PONum = PO_Header.GetLastPONumber(oCon); if (PONum == null) { PONum = "PJ000000"; } PONum = PONum.Substring(2); int PONum_ = int.Parse(PONum) + 1; string po_number = "PJ" + PONum_.ToString("000000"); var poHeader = new PO_Header_Model { PO_Number = po_number, PO_Date = DateTime.Now, Delivery_Date = DateTime.Now, Terms = txtTerms.Text, FOB_Point = "", Shipping_Via = "", Currency = "USD", idSupplier = 0, PO_Total = Convert.ToDecimal(txtPOAmount.Text), PO_Quantity = Convert.ToInt32(txtPOQuantity.Text), Total_Charges = 0, Forex_Rate = Convert.ToDecimal(txtForexRate.Text), PR_Number = "", Created_By = Session["User_Domain"].ToString(), Remarks = txtRemarks.Text, POStatus = "Created", ImportShipmentNumber = "" }; int idPOHeader = PO_Header.Save(oCon, poHeader); foreach (GridViewRow row in gvItems.Rows) { int idItem = int.Parse(row.Cells[1].Text); decimal Quantity = decimal.Parse(row.Cells[4].Text); decimal Price = decimal.Parse(row.Cells[5].Text); decimal Extended = decimal.Parse(row.Cells[6].Text); var objPODetail = new PO_Detail_Model { idPOHeader = idPOHeader, idItem = idItem, Quantity = Quantity, Price = Price, Amount = Extended, Tax = 0, Final_Cost = 0, Unit_Comp = "", isReceived = "No", Partial_Remaining = 0 }; PO_Detail.Save(oCon, objPODetail); } POUpload(po_number, idPOHeader); } else if (Session["Mode"].ToString() == "Edit") { int idPOHeader = Convert.ToInt32(Session["idPOHeader"].ToString()); var poHeader = new PO_Header_Model { PO_Number = lblPONumber.Text, Terms = txtTerms.Text, FOB_Point = "", Shipping_Via = "", Currency = "USD", idSupplier = 0, PO_Total = Convert.ToDecimal(txtPOAmount.Text), PO_Quantity = Convert.ToInt32(txtPOQuantity.Text), Total_Charges = 0, Forex_Rate = Convert.ToDecimal(txtForexRate.Text), Created_By = Session["User_Domain"].ToString(), Remarks = txtRemarks.Text, POStatus = "Created", idPOHeader = idPOHeader, ImportShipmentNumber = "" }; PO_Header.Update(oCon, poHeader); bool result = PO_Detail.DeleteAllDetail(oCon, idPOHeader); if (result == true) { foreach (GridViewRow row in gvItems.Rows) { int idItem = int.Parse(row.Cells[1].Text); decimal Quantity = decimal.Parse(row.Cells[4].Text); decimal Price = decimal.Parse(row.Cells[5].Text); decimal Extended = decimal.Parse(row.Cells[6].Text); var objPODetail = new PO_Detail_Model { idPOHeader = idPOHeader, idItem = idItem, Quantity = Quantity, Price = Price, Amount = Extended, Tax = 0, Final_Cost = 0, Unit_Comp = "", isReceived = "No", Partial_Remaining = 0 }; PO_Detail.Save(oCon, objPODetail); } POUpload(lblPONumber.Text, idPOHeader); } else { HttpContext.Current.Response.Write("<script>alert(" + ControlChars.Quote + "Error in deleting po detail of " + lblPONumber.Text + ControlChars.Quote + ");</script>"); } } defaultSettings(); GetPO(); }