purchasing/inquiry/po_search.php

   1 <?php
   2
   3 $page_security = 2;
   4 $path_to_root="../..";
   5 include($path_to_root . "/includes/session.inc");
   6
   7 include($path_to_root . "/purchasing/includes/purchasing_ui.inc");
   8
   9 $js = "";
  10 if ($use_popup_windows)
  11         $js .= get_js_open_window(900, 500);
  12 page(_("Search Outstanding Purchase Orders"), false, false, "", $js);
  13
  14 if (isset($_GET['order_number']))
  15 {
  16         $_POST['order_number'] = $_GET['order_number'];
  17 }
  18
  19 //---------------------------------------------------------------------------------------------
  20
  21 start_form(false, true);
  22
  23 start_table("class='tablestyle_noborder'");
  24 start_row();
  25 ref_cells(_("#:"), 'order_number');
  26
  27 date_cells(_("from:"), 'OrdersAfterDate', null, -30);
  28 date_cells(_("to:"), 'OrdersToDate');
  29
  30 locations_list_cells(_("Location:"), 'StockLocation', null, true);
  31
  32 stock_items_list_cells(_("Item:"), 'SelectStockFromList', null, true);
  33
  34 submit_cells('SearchOrders', _("Search"));
  35 end_row();
  36 end_table();
  37
  38 end_form();
  39
  40 //---------------------------------------------------------------------------------------------
  41
  42 if (isset($_POST['order_number']) && ($_POST['order_number'] != ""))
  43 {
  44         $order_number = $_POST['order_number'];
  45 }
  46
  47 if (isset($_POST['SelectStockFromList']) && ($_POST['SelectStockFromList'] != "") &&
  48         ($_POST['SelectStockFromList'] != $all_items))
  49 {
  50         $selected_stock_item = $_POST['SelectStockFromList'];
  51 }
  52 else
  53 {
  54         unset($selected_stock_item);
  55 }
  56
  57 $today = date2sql(Today());
  58
  59 //figure out the sql required from the inputs available
  60 $sql = "SELECT ".TB_PREF."purch_orders.order_no, ".TB_PREF."suppliers.supp_name, ".TB_PREF."purch_orders.into_stock_location,
  61         ".TB_PREF."purch_orders.ord_date, ".TB_PREF."purch_orders.requisition_no, ".TB_PREF."purch_orders.reference,
  62         ".TB_PREF."suppliers.curr_code, ".TB_PREF."locations.location_name,
  63         Sum(".TB_PREF."purch_order_details.unit_price*".TB_PREF."purch_order_details.quantity_ordered) AS OrderValue,
  64         Sum(".TB_PREF."purch_order_details.delivery_date < '" . $today. "'
  65         AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received)) As OverDue
  66         FROM ".TB_PREF."purch_orders, ".TB_PREF."purch_order_details, ".TB_PREF."suppliers, ".TB_PREF."locations
  67         WHERE ".TB_PREF."purch_orders.order_no = ".TB_PREF."purch_order_details.order_no
  68         AND ".TB_PREF."purch_orders.supplier_id = ".TB_PREF."suppliers.supplier_id
  69         AND ".TB_PREF."locations.loc_code = ".TB_PREF."purch_orders.into_stock_location
  70         AND (".TB_PREF."purch_order_details.quantity_ordered > ".TB_PREF."purch_order_details.quantity_received) ";
  71
  72 if (isset($order_number) && $order_number != "")
  73 {
  74         $sql .= "AND ".TB_PREF."purch_orders.reference LIKE '%". $order_number . "%'";
  75 }
  76 else
  77 {
  78
  79         $data_after = date2sql($_POST['OrdersAfterDate']);
  80         $data_before = date2sql($_POST['OrdersToDate']);
  81
  82         $sql .= "  AND ".TB_PREF."purch_orders.ord_date >= '$data_after'";
  83         $sql .= "  AND ".TB_PREF."purch_orders.ord_date <= '$data_before'";
  84
  85         if (isset($_POST['StockLocation']) && $_POST['StockLocation'] != $all_items)
  86         {
  87                 $sql .= " AND ".TB_PREF."purch_orders.into_stock_location = '". $_POST['StockLocation'] . "' ";
  88         }
  89
  90         if (isset($selected_stock_item))
  91         {
  92                 $sql .= " AND ".TB_PREF."purch_order_details.item_code='". $selected_stock_item ."' ";
  93         }
  94 } //end not order number selected
  95
  96 $sql .= " GROUP BY ".TB_PREF."purch_orders.order_no";
  97
  98 $result = db_query($sql,"No orders were returned");
  99
 100 /*show a table of the orders returned by the sql */
 101
 102 start_table("$table_style colspan=7 width=80%");
 103
 104 if (isset($_POST['StockLocation']) && $_POST['StockLocation'] == $all_items)
 105         $th = array(_("#"), _("Reference"), _("Supplier"), _("Location"),
 106                 _("Supplier's Reference"), _("Order Date"), _("Currency"), _("Order Total"));
 107 else
 108         $th = array(_("#"), _("Reference"), _("Supplier"),
 109                 _("Supplier's Reference"), _("Order Date"), _("Currency"), _("Order Total"));
 110
 111 table_header($th);
 112
 113 $j = 1;
 114 $k = 0; //row colour counter
 115 $overdue_items = false;
 116 while ($myrow = db_fetch($result))
 117 {
 118
 119         if ($myrow["OverDue"] == 1)
 120         {
 121                  start_row("class='overduebg'");
 122                  $overdue_items = true;
 123         }
 124         else
 125         {
 126                 alt_table_row_color($k);
 127         }
 128
 129         $modify = "$path_to_root/purchasing/po_entry_items.php?" . SID . "ModifyOrderNumber=" . $myrow["order_no"];
 130         $receive = "$path_to_root/purchasing/po_receive_items.php?" . SID . "PONumber=" . $myrow["order_no"];
 131
 132         $date = sql2date($myrow["ord_date"]);
 133
 134         label_cell(get_trans_view_str(systypes::po(), $myrow["order_no"]));
 135         label_cell($myrow["reference"]);
 136         label_cell($myrow["supp_name"]);
 137         if (isset($_POST['StockLocation']) && $_POST['StockLocation'] == $all_items)
 138                 label_cell($myrow["location_name"]);
 139         label_cell($myrow["requisition_no"]);
 140         label_cell($date);
 141         label_cell($myrow["curr_code"]);
 142         amount_cell($myrow["OrderValue"]);
 143         label_cell("<a href=$modify>" . _("Edit") . "</a>");
 144         label_cell("<a href=$receive>" . _("Receive") . "</a>");
 145         end_row();
 146
 147         $j++;
 148         If ($j == 12)
 149         {
 150                 $j = 1;
 151                 table_header($th);
 152         }
 153 //end of page full new headings if
 154 }
 155 //end of while loop
 156
 157 end_table();
 158
 159 if ($overdue_items)
 160         display_note(_("Marked orders have overdue items."), 0, 1, "class='overduefg'");
 161
 162 end_page();
 163 ?>