Page 1 of 1

query help

Posted: Wed Sep 12, 2007 9:22 am
by smilesmita
ihave this page which helps you find an invoice...it is basically a search for details of invoice.you can search by multiple fields on that page.
following are the feilds listed:
PRO/Air Bill Number:
PRO/Airbill Number:
Carrier's Invoice Number :
Carrier Account Number:
Purchase Order Number :
Carrier Customer Cost Object:
Payment Date Pick Date on Calendar __ THROUGH __
Invoice Receipt Date
Pick Date on Calendar __ THROUGH __
Check Number

the first field PRO/Air bill number...currently the users put a pro number and hit search and they get result.wht we want is to be able to enter tracking number in that field and hit search.
so if a pro is entered the query will search by pro number while if i enter tracking number instead ..the query shud then search by tracking number.
so either ways the query shud search...may be first by pro number and if that search comes blank then it shud search by tracking number.
we are using that field to take 2 kinds of input-PRO number or tracking number.
this is the query which we are using right now which pulls the record by different search criterias and i am confused as to where i can modify to make the search critirea get the results which i am looking for:

Code: Select all

var $browse_query = "
        SELECT  am_client.client_name,
                am_invoice_line_item.batch_id,
                am_batch_status.status_text,
                am_invoice.invoice_date_received,
                am_payment_group.payment_group_create_date,
                am_invoice_line_item.payment_id AS check_number,
                am_payment.payment_date AS check_date,
                carrier.company_name AS carrier_company,
                am_invoice_line_item.line_item_pro,
                am_invoice_line_item.line_item_amount_billed,
                am_invoice_line_item.line_item_amount_paid,
                am_invoice_line_item_billing.ilib_amount,
                (NOT am_invoice_line_item_billing.ilib_amount =
                    am_invoice_line_item.line_item_amount_paid) AS split,
                am_short_pay.short_reason,
                am_invoice.carrier_invoice_number,
                billing.company_name AS billing_company,
                coalesce(am_purchase_order.po_number,
                    am_invoice_line_item.line_item_purchase_order) AS po_number,
                am_invoice_line_item.line_item_ardmore_invoice,
                am_invoice.invoice_id,
                am_invoice_line_item.line_item_id,
                am_invoice.carrier_account_number,
                am_invoice.invoice_problem_flag
           FROM am_invoice
                LEFT OUTER JOIN am_invoice_line_item USING ( invoice_id )
                LEFT OUTER JOIN am_invoice_line_item_billing USING ( line_item_id )
                LEFT OUTER JOIN fw_company carrier ON
                    ( carrier.company_id = am_invoice.carrier_id )
                LEFT OUTER JOIN am_invoice_batch USING ( batch_id )
                LEFT OUTER JOIN am_client using ( client_id )
                LEFT OUTER JOIN am_batch_status
                    ON ( am_batch_status.status_code =
                        am_invoice_batch.batch_status )
                LEFT OUTER JOIN fw_company billing ON ( billing.company_id =
                    am_invoice_line_item_billing.ilib_company_id )
                LEFT OUTER JOIN am_short_pay ON ( am_short_pay.short_code =
                    am_invoice_line_item.line_item_short_pay_reason )
                LEFT OUTER JOIN am_purchase_order ON ( am_purchase_order.po_id =
                    am_invoice_line_item.purchase_order_id )
                LEFT OUTER JOIN am_payment USING ( payment_id )
                LEFT OUTER JOIN am_payment_group USING ( payment_group_id )
            WHERE TRUE ";

Re: query help

Posted: Wed Sep 12, 2007 10:12 am
by anjanesh
smilesmita wrote:the first field PRO/Air bill number...currently the users put a pro number and hit search and they get result.wht we want is to be able to enter tracking number in that field and hit search.
I dont understand how you get PRO results when the WHERE clause is empty (WHERE TRUE) ?

Posted: Wed Sep 12, 2007 11:04 am
by aceconcepts
Why dont you use the pro/tracking number to query both columns in database within the same query?

Re: query help

Posted: Wed Sep 12, 2007 11:07 am
by smilesmita
this class extends some other php program where theres is function whichc builds the where thingi..here it is

filter is the fields which we search by

Code: Select all

Function Build_Query($options = '', $browseQueryArrayIdx = null)
    {
        global $view;

        if (IsBlank($this->browse_query)) {
            $this->browse_query = $this->Build_Base_Query();
        }

        $use['filter'] = array();
        if ($view['filter']) {
            
            $use['filter'] = array_merge($use['filter'], $view['filter']);
            
        }
        if ($options['filter']) {
            $use['filter'] = array_merge($use['filter'], $options['filter'] );
        }

        $kwErrors = "";
        foreach ($use['filter'] as $fields => $term) {
            if (!IsBlank($term) && !isset($this->skip_fields[$fields])) {
                $val = $filter_field = "";
                if (is_array($term)) {
                    foreach($term AS $ff => $newterm) {
                        if ( $this->search_fields[$ff] == $fields ) {
                            $filter_field = $ff;
                            $val = $newterm;
                        }
                    }
                }
                if (!IsBlank($val) && $fields != 'submit') {
                    $or = '';
                    $current = "( ";
                    $field = explode (',',$fields);
                    foreach ($field as $k => $field_name) {
                        if(!$filter_field) {
                            $filter_field = $field_name;
                        }
                        $foptions = array();
                        if (isset($this->field_options[$filter_field])) {
                            $foptions = $this->field_options[$filter_field];
                        }
                        $f = Get_Field($filter_field, $foptions);
                        $current .= $or.$f->Get_Filter_SQL(
                            $field_name,
                            $f->Get_Filter_Value($val),
                            $kwErrors,
                            $f->Get_Not_Value("view[filter][$fields]")
                            );
                        $or = ' OR ';
                    }
                    $current .= " )";
                    $filters[] = $current;
                }
            }
        }

        if (!isBlank($kwErrors)) {
            // Set $view['filter']['submit'] to empty so that we don't get
            // ourselves into an infinite redirect loop.
            $view['filter']['submit'] = '';
            Abort_Post($kwErrors);
        }

        if ($options['sql_filters']) {
            if (is_array($filters)) {
                $filters = array_merge($filters, $options['sql_filters']);
               
            } else {
                $filters = $options['sql_filters'];
            }
        }

        if (!empty($joins) || !empty($filters)) {
            if (stristr($this->browse_query,'WHERE')) {
                $this->browse_query .= " AND ";
            } else {
                $this->browse_query .= " WHERE ";
            }

            if (!empty($joins)) {
                $this->browse_query .= join (' AND ', $joins);
            }
            if (!empty($joins) && !empty($filters)) {
                $this->browse_query .= " AND ";
            }
            if (!empty($filters)) {
                $this->browse_query .= join (' AND ', $filters);
              echo "<pre>";
              print_r($filters);
              echo "</pre>";
            }
        }
        // show ($this->browse_query); // DEBUG
    }

Posted: Wed Sep 12, 2007 11:13 am
by anjanesh
Also check out ORDER BY CASE if you want to sort the results

Code: Select all

SELECT ...
WHERE
`col1` = $PRO_Number OR
`col2` = $Tracking_Number
ORDER BY
CASE
WHEN `col2` = $PRO_Number THEN 1
WHEN `col1` = $Tracking_Number THEN 1000
ELSE 500
END

Posted: Thu Sep 13, 2007 10:11 am
by mabwi
Are the two types of numbers significantly different, in terms of number of digits or something?

If so, could you do the query based on which type of number was entered? This would also allow you to check if the entered number was completely invalid, and save a query.