Example #1
0
        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);
        }
Example #2
0
        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);
        }
Example #3
0
        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);
        }