public static bool addReorderRecord(PendingAOCPortOrder order)
{
bool success = false;
OracleConnection ORA = new OracleConnection();
ORA.ConnectionString = ConfigurationManager.ConnectionStrings["aocport"].ToString();
OracleCommand OCMD = ORA.CreateCommand();
#region SQL Statement
string[] sql = new string[order.LINES.Length];
for (int L = 0; L < order.LINES.Length; L++)
{
sql[L] = "insert into AOCPORT_PENDING_ORDERS (" +
"PENDING_ORDER_HEADER_ID, " +
"AOC_SITE_NUMBER, " +
"CUSTOMER_GROUP_CODE, " +
"CUSTOMER_ID, " +
"CUSTOMER_NAME, " +
"CUSTOMER_PO_NUMBER, " +
"LINE_SPECIAL_INSTRUCTIONS, " +
"LOCATION_NAME, " +
//"ORDERED_CONTAINERS, " +
"ORDERED_ITEM, " +
"ORDER_QUANTITY, " +
"ORDER_QUANTITY_UOM, " +
"ORDER_SPECIAL_INSTRUCTIONS, " +
//"PACKAGE_CODE, " +
"SUBMISSION_STATUS, " +
"REQUESTED_DELIVERY_DATE, " +
"SUBMITTED_BY_EMAIL_ADDRESS, " +
"SUBMITTED_BY_NAME, " +
"SUBMIT_DATE) values ( " +
"'" + order.PENDING_ORDER_HEADER_ID + "', " +
"'" + order.AOC_SITE_NUMBER + "', " +
order.CUSTOMER_GROUP_CODE + ", " +
order.CUSTOMER_ID + ", " +
"'" + order.CUSTOMER_NAME + "', " +
"'" + order.CUSTOMER_PO_NUMBER + "', " +
"'" + order.LINES[L].SPECIAL_INSTRUCTIONS + "', " +
"'" + order.LOCATION_NAME + "', " +
//order.LINES[0].ORDERED_CONTAINERS + ", " +
"'" + order.ORDERED_ITEM + "', " +
order.LINES[L].ORDERED_QUANTITY + ", " +
"'" + order.LINES[L].ORDER_QUANTITY_UOM + "', " +
"'" + order.ORDER_SPECIAL_INSTRUCTIONS + "', " +
//"'" + order.PACKAGE_CODE + "', " +
"'" + order.SUBMISSION_STATUS + "', " +
"TO_DATE('" + order.LINES[L].DELIVERY_DATE.ToString("dd-MMM-yyyy") + "'), " +
"'" + order.SUBMITTED_BY_EMAIL_ADDRESS + "', " +
"'" + order.SUBMITTED_BY_NAME + "', " +
"TO_DATE('" + order.SUBMIT_DATE.ToString("dd-MMM-yyyy") + "') " +
") ";
}
#endregion
ORA.Open();
OCMD = ORA.CreateCommand();
OracleTransaction txn = ORA.BeginTransaction();
#region Inser the new reorder record
try
{
OCMD.Transaction = txn;
for (int L = 0; L < order.LINES.Length; L++)
{
OCMD.CommandText = sql[L];
int recs = OCMD.ExecuteNonQuery();
}
txn.Commit();
success = true;
}
catch (Exception e)
{
txn.Rollback();
success = false;
}
finally
{
ORA.Dispose();
OCMD.Dispose();
}
#endregion
int meaningless = 0;
return(success);
}
public static bool updateReorderRecord(string pohi, PendingAOCPortOrder order)
{
bool success = false;
success = (deleteReorderRecord(pohi) && addReorderRecord(order));
#region old code
/*OracleConnection ORA = new OracleConnection();
* ORA.ConnectionString = ConfigurationManager.ConnectionStrings["aocport"].ToString();
* OracleCommand OCMD = ORA.CreateCommand();
*
*
#region SQL Statement
* string sql = "UPDATE AOCPORT_PENDING_ORDERS SET " +
* "AOC_SITE_NUMBER = " + "'" + order.AOC_SITE_NUMBER + "', " +
* "CUSTOMER_GROUP_CODE = " + order.CUSTOMER_GROUP_CODE + ", " +
* "CUSTOMER_ID = " + order.CUSTOMER_ID + ", " +
* "CUSTOMER_NAME = " + "'" + order.CUSTOMER_NAME + "', " +
* "CUSTOMER_PO_NUMBER = " + "'" + order.CUSTOMER_PO_NUMBER + "', " +
* "LINE_SPECIAL_INSTRUCTIONS = " + "'" + order.LINE_SPECIAL_INSTRUCTIONS + "', " +
* "LOCATION_NAME = " + "'" + order.LOCATION_NAME + "', " +
* "ORDERED_CONTAINERS = " + order.ORDERED_CONTAINERS + ", " +
* "ORDERED_ITEM = " + "'" + order.ORDERED_ITEM + "', " +
* "ORDER_QUANTITY = " + order.ORDER_QUANTITY + ", " +
* "ORDER_QUANTITY_UOM = " + "'" + order.ORDER_QUANTITY_UOM + "', " +
* "ORDER_SPECIAL_INSTRUCTIONS = " + "'" + order.ORDER_SPECIAL_INSTRUCTIONS + "', " +
* "PACKAGE_CODE = " + "'" + order.PACKAGE_CODE + "', " +
* "SUBMISSION_STATUS = " + "'" + order.SUBMISSION_STATUS + "', " +
* "REQUESTED_DELIVERY_DATE = " + "TO_DATE('" + order.REQUESTED_DELIVERY_DATE.ToString("dd-MMM-yyyy") + "'), " +
* "SUBMITTED_BY_EMAIL_ADDRESS = " + "'" + order.SUBMITTED_BY_EMAIL_ADDRESS + "', " +
* "SUBMITTED_BY_NAME = " + "'" + order.SUBMITTED_BY_NAME + "', " +
* "SUBMIT_DATE = TO_DATE('" + order.SUBMIT_DATE.ToString("dd-MMM-yyyy") + "') " +
* "WHERE PENDING_ORDER_HEADER_ID = '" + order.PENDING_ORDER_HEADER_ID + "' AND PENDING_ORDER_ID = " + order.PENDING_ORDER_ID;
*
*
*
*
*
*
*
#endregion
*
*
* ORA.Open();
* OCMD = ORA.CreateCommand();
*
* OracleTransaction txn = ORA.BeginTransaction();
* OCMD.Transaction = txn;
*
#region Inser the new reorder record
* try
* {
* OCMD.CommandText = sql;
*
* int recs = OCMD.ExecuteNonQuery();
*
* txn.Commit();
*
* success = true;
*
*
* }
* catch (Exception e)
* {
* txn.Rollback();
* success = false;
* }
* finally
* {
* ORA.Dispose();
* OCMD.Dispose();
* }*/
#endregion
int meaningless = 0;
return(success);
}
public static IEnumerable <PendingAOCPortOrder> getPendingAOCPortOrders(int user_id)
{
List <PendingAOCPortOrder> data = new List <PendingAOCPortOrder>();
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["aocport"].ToString());
OracleCommand cmd = conn.CreateCommand();
string sql = "SELECT * " +
" FROM AOCPORT_PENDING_ORDERS " +
" WHERE 1 = 1 " +
//" AND user_id = " + user_id + " " +
" ORDER BY PENDING_ORDER_HEADER_ID, REQUESTED_DELIVERY_DATE";
int counter = 0;
try
{
conn.Open();
cmd.CommandText = sql;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables.Count > 0)
{
DataTable dt = ds.Tables[0];
DataView view = new DataView(dt);
DataTable dtOrderHeaderId = view.ToTable(true, "PENDING_ORDER_HEADER_ID");
foreach (DataRow dr in dtOrderHeaderId.Rows)
{
//Filter to rows that match the current PENDING_ORDER_HEADER_ID
DataRow[] rows = dt.Select("PENDING_ORDER_HEADER_ID = '" + dr["PENDING_ORDER_HEADER_ID"].ToString() + "' ");
PendingAOCPortOrder order = new PendingAOCPortOrder();
order.PENDING_ORDER_HEADER_ID = dr["PENDING_ORDER_HEADER_ID"].ToString();
//order.PENDING_ORDER_ID = rows[0]["PENDING_ORDER_ID"].ToString();
order.AOC_SITE_NUMBER = rows[0]["AOC_SITE_NUMBER"].ToString();
order.CUSTOMER_GROUP_CODE = rows[0]["CUSTOMER_GROUP_CODE"].ToString();
order.CUSTOMER_ID = rows[0]["CUSTOMER_ID"].ToString();
order.CUSTOMER_NAME = rows[0]["CUSTOMER_NAME"].ToString();
order.CUSTOMER_PO_NUMBER = rows[0]["CUSTOMER_PO_NUMBER"].ToString();
order.LOCATION_NAME = rows[0]["LOCATION_NAME"].ToString();
order.ORDERED_ITEM = rows[0]["ORDERED_ITEM"].ToString();
order.ORDER_QUANTITY_UOM = rows[0]["ORDER_QUANTITY_UOM"].ToString();
order.ORDER_SPECIAL_INSTRUCTIONS = rows[0]["ORDER_SPECIAL_INSTRUCTIONS"].ToString();
order.SUBMIT_DATE = (DateTime)rows[0]["SUBMIT_DATE"];
order.SUBMITTED_BY_EMAIL_ADDRESS = rows[0]["SUBMITTED_BY_EMAIL_ADDRESS"].ToString();
order.SUBMITTED_BY_NAME = rows[0]["SUBMITTED_BY_NAME"].ToString();
order.SUBMISSION_STATUS = rows[0]["SUBMISSION_STATUS"].ToString();
order.LINES = new PendingAOCPortOrderLine[rows.Length];
for (int l = 0; l < rows.Length; l++)
{
PendingAOCPortOrderLine line = new PendingAOCPortOrderLine();
line.DELIVERY_DATE = (DateTime)rows[l]["REQUESTED_DELIVERY_DATE"];
line.ORDERED_ITEM = rows[l]["ORDERED_ITEM"].ToString();
line.ORDERED_QUANTITY = rows[l]["ORDER_QUANTITY"].ToString();
line.ORDER_QUANTITY_UOM = rows[l]["ORDER_QUANTITY_UOM"].ToString();
line.SPECIAL_INSTRUCTIONS = rows[l]["LINE_SPECIAL_INSTRUCTIONS"].ToString();
order.LINES[l] = line;
}
/*order.ORDERED_CONTAINERS = dr["ORDERED_CONTAINERS"].ToString();
* order.PACKAGE_CODE = dr["PACKAGE_CODE"].ToString();*/
data.Add(order);
}
/*foreach (DataRow dr in dt.Rows)
* {
*
*
* order.PENDING_ORDER_HEADER_ID = dr["PENDING_ORDER_HEADER_ID"].ToString();
* order.PENDING_ORDER_ID = dr["PENDING_ORDER_ID"].ToString();
* order.AOC_SITE_NUMBER = dr["AOC_SITE_NUMBER"].ToString();
* order.CUSTOMER_GROUP_CODE = dr["CUSTOMER_GROUP_CODE"].ToString();
* order.CUSTOMER_ID = dr["CUSTOMER_ID"].ToString();
* order.CUSTOMER_NAME = dr["CUSTOMER_NAME"].ToString();
* order.CUSTOMER_PO_NUMBER = dr["CUSTOMER_PO_NUMBER"].ToString();
* order.LINE_SPECIAL_INSTRUCTIONS = dr["LINE_SPECIAL_INSTRUCTIONS"].ToString();
* order.LOCATION_NAME = dr["LOCATION_NAME"].ToString();
* order.ORDER_QUANTITY = dr["ORDER_QUANTITY"].ToString();
* order.ORDER_QUANTITY_UOM = dr["ORDER_QUANTITY_UOM"].ToString();
* order.ORDER_SPECIAL_INSTRUCTIONS = dr["ORDER_SPECIAL_INSTRUCTIONS"].ToString();
* order.ORDERED_ITEM = dr["ORDERED_ITEM"].ToString();
* order.ORDERED_CONTAINERS = dr["ORDERED_CONTAINERS"].ToString();
* order.PACKAGE_CODE = dr["PACKAGE_CODE"].ToString();
* order.SUBMISSION_STATUS = dr["SUBMISSION_STATUS"].ToString();
* order.REQUESTED_DELIVERY_DATE = (DateTime) dr["REQUESTED_DELIVERY_DATE"];
* order.SUBMIT_DATE = (DateTime) dr["SUBMIT_DATE"];
* order.SUBMITTED_BY_EMAIL_ADDRESS = dr["SUBMITTED_BY_EMAIL_ADDRESS"].ToString();
* order.SUBMITTED_BY_NAME = dr["SUBMITTED_BY_NAME"].ToString();
*
*
*
* counter++;
*
* }*/
}
}
catch (Exception e)
{
Debug.WriteLine("ERROR: " + e.Message);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
return(data);
}