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