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