Individual column search not working with SSP

Individual column search not working with SSP

bbrindzabbrindza Posts: 299Questions: 68Answers: 1

I am using a DataTables SSP script with a Where clause that is preventing accurate individual column searching.

When I remove the Where clause, column filtering works as designed.

Any thoughts?

<?php
/*
 * DataTables server-side processing custom script.
 */
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
date_default_timezone_set('America/Chicago'); // CDT

$today  = date("Y-m-d");

$archiveDate = date('Ymd', strtotime('-1 year'));

include('i5db2connect.php');

// DB table to use
$table = 'NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC';

//Used for optional WHERE clauses that will be appended to the SQL string
$extraWhere = "FCSTAT not in
 ('Closed-Deducted',
     'Closed-Overpaid',
     'Closed-Paid',
     'Closed-PWO',
     'Closed-Rejected',
     'Closed-WO',
     'Closed-WO/Ded mix',
     'Declined',
     'Rejected',
     'Removed')

     or

     FCSTAT in
     (   'Deduction Pending',
         'Partial Pending',
         'Reinstated',
         'Pending-WO taken'   ) 

and FCMCRD >= '" . $archiveDate . "'";

// Table's primary key
$primaryKey = 'FCCLMN';

$columns = array(
    array( 'db' => 'FCCLMN',   'dt' => 'claim_number' ),
    array( 'db' => 'FCCLMN', 'dt' => 'claim_number_link',
        'formatter' => function($d, $row){
        return ' <a id="myLink" class="link" onclick="editFreightClaim('.$d.');return false;">' . $d . '</a>';
        }
        ),
        
        array( 'db' => 'LOCDES',  'dt' => 'location',
            'formatter' => function( $d, $row ) {
            return substr( $d, 4, strlen($d));
            }
            ),
            array( 'db' => 'FCDNAM',   'dt' => 'department_name' ),
            array( 'db' => 'FCCVNM',   'dt' => 'vendor_customer' ),
            array( 'db' => 'FCMPRO',   'dt' => 'pro_number' ),
            array( 'db' => 'FCMNAM',   'dt' => 'carrier_name' ),
            array( 'db' => 'FCPAMT',   'dt' => 'claim_amount' ),
            array( 'db' => 'FCFACL',   'dt' => 'freight_claim_amount' ),
            array( 'db' => 'FCTOTL',   'dt' => 'total_claim_amount'),
            array( 'db' => 'FCMPAM',   'dt' => 'amount_paid'),
            array( 'db' => 'FCSTAT',   'dt' => 'status' ),
            
            array( 'db' => 'FCMSHP',   'dt' => 'ship_date',
                'formatter' => function( $d, $row ) {
                if ($d > 0)
                {
                    $dt = DateTime::createFromFormat('Ymd', $d);
                    return $dt->format('m/d/Y');
                }
                else
                {
                    return '';
                }
                }
                
                ),
                array( 'db' => 'FCMREC',   'dt' => 'received_date',
                    'formatter' => function( $d, $row ) {
                    if ($d > 0)
                    {
                        $dt = DateTime::createFromFormat('Ymd', $d);
                        return $dt->format('m/d/Y');
                    }
                    else
                    {
                        return '';
                    }
                    }
                    
                    ),
                    array( 'db' => 'FCLMTP',   'dt' => 'claim_type',
                        'formatter' => function( $d, $row ) {
                        if (trim($d) == 'I') {
                            return "Inbound";
                        } else {
                            return "Outbound";
                        }
                        }
                        
                        ),
                        
                        array( 'db' => 'FCMCRD',   'dt' => 'created_age',
                            'formatter' => function( $d, $row ) {
                            
                            if ($d > 0 ){
                                
                                $createdOn = substr($d,0,4).'-'.substr($d,4,2).'-'.substr($d,6,2);
                                
                                $createdOn = date_create($createdOn);
                                $today = date_create($today);
                                
                                $diff=date_diff($createdOn,$today);
                                
                                if($diff->format('%y') !== '0'){
                                    $created_age = $diff->format('%y Year %m Month %d Day');
                                    
                                }else if($diff->format('%m') !== '0'){
                                    $created_age =  $diff->format('%m Month %d Day');
                                    
                                }else{
                                    $created_age =  $diff->format('%d Day');
                                }
                                return  $created_age;
                            } else {
                                return '';
                            }
                            }
                            ),
                            
                            array( 'db' => 'FCMCLM',   'dt' => 'filed_age',
                                'formatter' => function( $d, $row ) {
                                
                                if ($d > 0 && trim($row[FCSTAT]) == 'Filed'){
                                    
                                    $createdOn = substr($d,0,4).'-'.substr($d,4,2).'-'.substr($d,6,2);
                                    
                                    $createdOn = date_create($createdOn);
                                    $today = date_create($today);
                                    
                                    $diff=date_diff($createdOn,$today);
                                    
                                    if($diff->format('%y') !== '0'){
                                        $filed_age = $diff->format('%y Year %m Month %d Day');
                                        
                                    }else if($diff->format('%m') !== '0'){
                                        $filed_age =  $diff->format('%m Month %d Day');
                                        
                                    }else{
                                        $filed_age =  $diff->format('%d Day');
                                    }
                                    return  $filed_age;
                                } else {
                                    return '';
                                }
                                }
                                ),
                                
                                array( 'db' => 'FCMPAM',   'dt' => 'amount_due',
                                    'formatter' => function( $d, $row ) {
                                    $amountDue =  $row[FCTOTL] - $row[FCMPAM];
                                    $amountDue =  number_format((float)$amountDue, 2, '.', '');
                                    return $amountDue;
                                    }
                                    ),
                                    );

require( 'ssp.class_Template.php' );
//var_dump($connection);

echo json_encode(
    SSP::simple( $_GET, $connection, $select, $table, $primaryKey, $columns, $extraWhere)
    );

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    It might actually be as simple as wrapping the extra condition in parenthesis:

    $extraWhere = "( FCSTAT not in ...)";
    

    Allan

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1

    Allan ,
    That did not work. Has the column search api been tested using where clause in the ssp class?

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Yes, it should work no problem. That said, it looks like this is a customised version of the SSP class since the one we provide doesn't have a parameter after $columns for the simple method.

    I'd suggest echoing out the SQL that is being generated as the starting point for debugging this.

    Allan

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1

    Here is the the Network Preview showing the echoed SQL from the ssp.class.php.

    SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') or FCSTAT in ( 'Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) and FCMCRD >= '20200625' ORDER BY FCCLMN DESC LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') or FCSTAT in ( 'Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) and FCMCRD >= '20200625'SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":10,"recordsTotal":18219,"recordsFiltered":52,"data":[{"claim_number":"19307","claim_number_link":" 19307<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-WAT ","pro_number":" ","carrier_name":"XPO VOL (G!)QTE#5797041639646 ","claim_amount":"27.99","freight_claim_amount":"14.45","total_claim_amount":"42.44","amount_paid":".00","status":"Reinstated ","ship_date":"05\/24\/2021","received_date":"","claim_type":"Outbound","created_age":"21 Day","filed_age":"","amount_due":"42.44"},{"claim_number":"19258","claim_number_link":" 19258<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"OKLAHOMA CITY MEAT COMPAN ","pro_number":"707242082 ","carrier_name":"XPO B ","claim_amount":"830.00","freight_claim_amount":".00","total_claim_amount":"830.00","amount_paid":"207.50","status":"Partial Pending ","ship_date":"05\/26\/2021","received_date":"05\/28\/2021","claim_type":"Outbound","created_age":"23 Day","filed_age":"","amount_due":"622.50"},{"claim_number":"19251","claim_number_link":" 19251<\/a>","location":"MODESTO ","department_name":"OFFICE - A\/P ","vendor_customer":"OXIDRY\/WORLD TECH INGRE ","pro_number":"089243533 ","carrier_name":"ABF FREIGHT SYSTEM, INC. ","claim_amount":"235.90","freight_claim_amount":"16.55","total_claim_amount":"252.45","amount_paid":"156.55","status":"Partial Pending ","ship_date":"05\/13\/2021","received_date":"05\/24\/2021","claim_type":"Inbound","created_age":"23 Day","filed_age":"","amount_due":"95.90"},{"claim_number":"19231","claim_number_link":" 19231<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS YRK ","pro_number":"773780191156 ","carrier_name":"FEDEX GROUND 871620 ","claim_amount":"264.76","freight_claim_amount":".00","total_claim_amount":"264.76","amount_paid":"211.26","status":"Partial Pending ","ship_date":"05\/20\/2021","received_date":"05\/24\/2021","claim_type":"Outbound","created_age":"28 Day","filed_age":"","amount_due":"53.50"},{"claim_number":"19229","claim_number_link":" 19229<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/P ","vendor_customer":"INGREDION ","pro_number":"402609815 ","carrier_name":"xpo ","claim_amount":"249.05","freight_claim_amount":"77.76","total_claim_amount":"326.81","amount_paid":"269.05","status":"Partial Pending ","ship_date":"04\/23\/2021","received_date":"04\/27\/2021","claim_type":"Inbound","created_age":"28 Day","filed_age":"","amount_due":"57.76"},{"claim_number":"19208","claim_number_link":" 19208<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"NEWLY WEDS FOODS,INC. MTP ","pro_number":"688302101 ","carrier_name":"XPO-B ","claim_amount":"857.80","freight_claim_amount":".00","total_claim_amount":"857.80","amount_paid":"514.68","status":"Partial Pending ","ship_date":"05\/20\/2021","received_date":"05\/25\/2021","claim_type":"Outbound","created_age":"29 Day","filed_age":"","amount_due":"343.12"},{"claim_number":"19180","claim_number_link":" 19180<\/a>","location":"MODESTO ","department_name":"OFFICE - A\/P ","vendor_customer":"INGREDION ","pro_number":"050284045 ","carrier_name":"ABF FREIGHT SYSTEM, INC. ","claim_amount":"235.13","freight_claim_amount":"25.65","total_claim_amount":"260.78","amount_paid":"229.25","status":"Partial Pending ","ship_date":"05\/10\/2021","received_date":"05\/18\/2021","claim_type":"Inbound","created_age":"1 Month 0 Day","filed_age":"","amount_due":"31.53"},{"claim_number":"19090","claim_number_link":" 19090<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/R ","vendor_customer":"CITRUS&ALLIED ESSENCES LT ","pro_number":"8766087341 ","carrier_name":"FEDERAL EXPRESS A ","claim_amount":"4242.13","freight_claim_amount":".00","total_claim_amount":"4242.13","amount_paid":"1257.08","status":"Partial Pending ","ship_date":"05\/12\/2021","received_date":"05\/14\/2021","claim_type":"Inbound","created_age":"1 Month 6 Day","filed_age":"","amount_due":"2985.05"},{"claim_number":"19087","claim_number_link":" 19087<\/a>","location":"YORKVILLE ","department_name":"OFFICE - A\/P ","vendor_customer":"DSM FOOD SPECIALTIES ","pro_number":"223-793076 ","carrier_name":"XPO B ","claim_amount":"381.62","freight_claim_amount":"5.24","total_claim_amount":"386.86","amount_paid":".00","status":"Reinstated ","ship_date":"04\/12\/2021","received_date":"04\/15\/2021","claim_type":"Inbound","created_age":"1 Month 6 Day","filed_age":"","amount_due":"386.86"},{"claim_number":"19037","claim_number_link":" 19037<\/a>","location":"SPRINGDALE ","department_name":"OFFICE - A\/P ","vendor_customer":"KERRY ","pro_number":"7772099930 7772099974 ","carrier_name":"FEDEX FREIGHT (FXA) ","claim_amount":"747.42","freight_claim_amount":"14.51","total_claim_amount":"761.93","amount_paid":"380.97","status":"Partial Pending ","ship_date":"05\/07\/2021","received_date":"05\/13\/2021","claim_type":"Inbound","created_age":"1 Month 8 Day","filed_age":"","amount_due":"380.96"},{"claim_number":"18938","claim_number_link":" 18938<\/a>","location":"SPRINGDALE ","department_name":"OFFICE - A\/P ","vendor_customer":"BIOSPRINGER NORTH AMERICA ","pro_number":"721-822850 ","carrier_name":"XPO A ","claim_amount":"83.60","freight_claim_amount":"3.25","total_claim_amount":"86.85","amount_paid":"83.60","status":"Partial Pending ","ship_date":"05\/03\/2021","received_date":"05\/05\/2021","claim_type":"Inbound","created_age":"1 Month 18 Day","filed_age":"","amount_due":"3.25"},{"claim_number":"18889","claim_number_link":" 18889<\/a>","location":"ERLANGER ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"HIGH LINER FOODS, INC. ","pro_number":"567391156 ","carrier_name":"XPO 4\/28 B ","claim_amount":"74.70","freight_claim_amount":"3.54","total_claim_amount":"78.24","amount_paid":"74.70","status":"Partial Pending ","ship_date":"04\/28\/2021","received_date":"04\/30\/2021","claim_type":"Outbound","created_age":"1 Month 22 Day","filed_age":"","amount_due":"3.54"},{"claim_number":"18876","claim_number_link":" 18876<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/P ","vendor_customer":"PACIFIC SPICE COMPANY INC ","pro_number":"595003883 ","carrier_name":"XPO LOGISTICS, INC. A ","claim_amount":"84.50","freight_claim_amount":"16.48","total_claim_amount":"100.98","amount_paid":"84.50","status":"Partial Pending ","ship_date":"04\/22\/2021","received_date":"04\/29\/2021","claim_type":"Inbound","created_age":"1 Month 22 Day","filed_age":"","amount_due":"16.48"},{"claim_number":"18702","claim_number_link":" 18702<\/a>","location":"MODESTO ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"HYDE & HYDE ","pro_number":"7575936771 ","carrier_name":"FEDEX FREIGHT PRIORITY ","claim_amount":"945.00","freight_claim_amount":"29.37","total_claim_amount":"974.37","amount_paid":"557.47","status":"Partial Pending ","ship_date":"04\/14\/2021","received_date":"04\/16\/2021","claim_type":"Outbound","created_age":"2 Month 6 Day","filed_age":"","amount_due":"416.90"},{"claim_number":"18497","claim_number_link":" 18497<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-SOU ","pro_number":"6418318255 ","carrier_name":"FED EX FRT PRIORITY ","claim_amount":"147.80","freight_claim_amount":".00","total_claim_amount":"147.80","amount_paid":"129.34","status":"Partial Pending ","ship_date":"03\/30\/2021","received_date":"04\/01\/2021","claim_type":"Outbound","created_age":"2 Month 23 Day","filed_age":"","amount_due":"18.46"}]}

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1
    edited June 2021

    Here is the the Network Response from the ssp.class.php. **NOTE: Not sure why the SQL from is repeated in the response output

    SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM
    FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC
    WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in
    ('Closed-Deducted',
    'Closed-Overpaid',
    'Closed-Paid',
    'Closed-PWO',
    'Closed-Rejected',
    'Closed-WO',
    'Closed-WO/Ded mix',
    'Declined',
    'Rejected',
    'Removed')
    or
    FCSTAT in
    ( 'Deduction Pending',
    'Partial Pending',
    'Reinstated',
    'Pending-WO taken' )

    and FCMCRD >= '20200625'
    ORDER BY FCCLMN DESC
    LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT
    FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC
    WHERE upper(FCCLMN) LIKE ? AND FCSTAT not in
    ('Closed-Deducted',
    'Closed-Overpaid',
    'Closed-Paid',
    'Closed-PWO',
    'Closed-Rejected',
    'Closed-WO',
    'Closed-WO/Ded mix',
    'Declined',
    'Rejected',
    'Removed')
    or
    FCSTAT in
    ( 'Deduction Pending',
    'Partial Pending',
    'Reinstated',
    'Pending-WO taken' )

    and FCMCRD >= '20200625'SELECT COUNT(FCCLMN) as RCDCNT
    FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":10,"recordsTotal":18219,"recordsFiltered":52,"data":[{"claim_number":"19307","claim_number_link":" <a id=\"myLink\" class=\"link\" onclick=\"editFreightClaim(19307);return false;\">19307<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"NEWLY WEDS FOODS, INC-WAT ","pro_number":" ","carrier_name":"XPO VOL (G!)QTE#5797041639646 ","claim_amount":"27.99","freight_claim_amount":"14.45","total_claim_amount":"42.44","amount_paid":".00","status":"Reinstated ","ship_date":"05\/24\/2021","received_date":"","claim_type":"Outbound","created_age":"21 Day","filed_age":"","amount_due":"42.44"},........................................]}

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Thanks.

    Formatting the SQL I think helps to focus down on the issue.

    SELECT
       FCCLMN,
       FCCLMN,
       LOCDES,
       FCDNAM,
       FCCVNM,
       FCMPRO,
       FCMNAM,
       FCPAMT,
       FCFACL,
       FCTOTL,
       FCMPAM,
       FCSTAT,
       FCMSHP,
       FCMREC,
       FCLMTP,
       FCMCRD,
       FCMCLM,
       FCMPAM 
    FROM
       NWFF.FCMASTER 
       left outer join
          NWFO.LOCNAMES 
          on LOCLOC = FCMLOC 
    WHERE
       upper(FCCLMN) LIKE ? 
       AND FCSTAT not in 
       (
          'Closed-Deducted',
          'Closed-Overpaid',
          'Closed-Paid',
          'Closed-PWO',
          'Closed-Rejected',
          'Closed-WO',
          'Closed-WO/Ded mix',
          'Declined',
          'Rejected',
          'Removed'
       )
       or FCSTAT in 
       (
          'Deduction Pending',
          'Partial Pending',
          'Reinstated',
          'Pending-WO taken' 
       )
       and FCMCRD >= '20200625' 
    ORDER BY FCCLMN DESC
    LIMIT 0,  15
    

    Which column in the above is it you are doing the individual column filtering on? I'd expect to see a LIKE %?% for it, but it isn't there, so I wonder if your modifications have removed that part?

    This is the part that should be doing it normally.

    It would be worth your checking that that part of the code is actually running in your modified version.

    Allan

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1

    I was filtering the FCCLMN that is in the tables as a link.

        array( 'db' => 'FCCLMN', 'dt' => 'claim_number_link',
            'formatter' => function($d, $row){
            return ' <a id="myLink" class="link" onclick="editFreightClaim('.$d.');return false;">' . $d . '</a>';
            }
            ),
    

    I don't believe I removed any code from the original ssp.class.php.

    I would like to share this script with you , however the forum will not allow me to post a message that large.

    I could send it to you via email.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Hi,

    Many thanks for your e-mail. It looks like the column filtering condition is being added:

    upper(FCCLMN) LIKE ?

    Which makes me suspect the grouping of the conditions that are being applied again. Can you show me the SQL output when you add the parenthesis around the $extraWhere please?

    Allan

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1

    Allan,
    I added the parenthesis around the $extraWhere, but when I did, it only returned 88 rows. I should see a little over 3000.

    Anyway, here is my $extraWhere

    $extraWhere = "(FCSTAT in
         'Closed-Deducted',
         'Closed-Overpaid',
         'Closed-Paid',
         'Closed-PWO',
         'Closed-Rejected',
         'Closed-WO',
         'Closed-WO/Ded mix',
         'Declined',
         'Rejected',
         'Removed'
    
         and FCMCRD >= '" . $archiveDate . "')
    
         or
    
      ( FCSTAT in
             'Deduction Pending',
             'Partial Pending',
             'Reinstated',
             'Pending-WO taken'  ) ";
    

    Here is the echoed SQL

    SELECT FCCLMN, FCCLMN, LOCDES, FCDNAM, FCCVNM, FCMPRO, FCMNAM, FCPAMT, FCFACL, FCTOTL, FCMPAM, FCSTAT, FCMSHP, FCMREC, FCLMTP, FCMCRD, FCMCLM, FCMPAM FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE FCSTAT in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') and FCMCRD >= '20200628' or FCSTAT in ('Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) ORDER BY FCCLMN DESC LIMIT 0, 15SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC WHERE FCSTAT in ('Closed-Deducted', 'Closed-Overpaid', 'Closed-Paid', 'Closed-PWO', 'Closed-Rejected', 'Closed-WO', 'Closed-WO/Ded mix', 'Declined', 'Rejected', 'Removed') and FCMCRD >= '20200628' or FCSTAT in ('Deduction Pending', 'Partial Pending', 'Reinstated', 'Pending-WO taken' ) SELECT COUNT(FCCLMN) as RCDCNT FROM NWFF.FCMASTER left outer join NWFO.LOCNAMES on LOCLOC = FCMLOC{"draw":1,"recordsTotal":18247,"recordsFiltered":3595,"data":[{"claim_number":"19505","claim_number_link":" 19505<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"KAYEM ","pro_number":"7095982784 ","carrier_name":"FED EX FRT PRIORITY ","claim_amount":"12.48","freight_claim_amount":"1.29","total_claim_amount":"13.77","amount_paid":".00","status":"Rejected ","ship_date":"06\/01\/2021","received_date":"06\/07\/2021","claim_type":"Outbound","created_age":"5 Day","filed_age":"","amount_due":"13.77"},{"claim_number":"19493","claim_number_link":" 19493<\/a>","location":"WATERTOWN ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"NESTLE USA ","pro_number":"792440641-6 ","carrier_name":"FEDEX FREIGHT PRIORITY ","claim_amount":"14.05","freight_claim_amount":"4.35","total_claim_amount":"18.40","amount_paid":".00","status":"Rejected ","ship_date":"06\/16\/2021","received_date":"06\/18\/2021","claim_type":"Outbound","created_age":"6 Day","filed_age":"","amount_due":"18.40"},{"claim_number":"19488","claim_number_link":" 19488<\/a>","location":"BETHLEHEM ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"AGRI-MARK, INC. ","pro_number":"193 201 831 BAE ","carrier_name":"PICK UP 6\/15 ","claim_amount":"319.00","freight_claim_amount":".00","total_claim_amount":"319.00","amount_paid":".00","status":"Removed ","ship_date":"06\/17\/2021","received_date":"","claim_type":"Outbound","created_age":"7 Day","filed_age":"","amount_due":"319.00"},{"claim_number":"19486","claim_number_link":" 19486<\/a>","location":"CHICAGO ","department_name":"OFFICE - A\/P ","vendor_customer":"SONSTEGARD FOODS CO ","pro_number":"739713148 ","carrier_name":"FEDEX FREIGHT A ","claim_amount":"8190.00","freight_claim_amount":"145.24","total_claim_amount":"8335.24","amount_paid":".00","status":"Removed ","ship_date":"05\/11\/2021","received_date":"05\/12\/2021","claim_type":"Inbound","created_age":"7 Day","filed_age":"","amount_due":"8335.24"},{"claim_number":"19482","claim_number_link":" 19482<\/a>","location":"ERLANGER ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"SYSCO FOODS SERVICES ","pro_number":"8727941552 ","carrier_name":"FED EX FREIGHT PRIORITY 6\/9 ","claim_amount":"10.35","freight_claim_amount":"4.61","total_claim_amount":"14.96","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"","claim_type":"Outbound","created_age":"7 Day","filed_age":"","amount_due":"14.96"},{"claim_number":"19475","claim_number_link":" 19475<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"NEWLY WEDS FOODS, INC. ","pro_number":"707242491 ","carrier_name":"XPO A ","claim_amount":"17.09","freight_claim_amount":".00","total_claim_amount":"17.09","amount_paid":".00","status":"Rejected ","ship_date":"06\/11\/2021","received_date":"06\/17\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"17.09"},{"claim_number":"19466","claim_number_link":" 19466<\/a>","location":"MEMPHIS ","department_name":"CUSTOMER SERVICE - SALES ","vendor_customer":"THE DISTRIBUTION GROUP, ","pro_number":"009729658 ","carrier_name":"ABF WWW9794449 ","claim_amount":"11.68","freight_claim_amount":"1.08","total_claim_amount":"12.76","amount_paid":".00","status":"Rejected ","ship_date":"06\/11\/2021","received_date":"","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"12.76"},{"claim_number":"19464","claim_number_link":" 19464<\/a>","location":"YORKVILLE ","department_name":"OFFICE - A\/P ","vendor_customer":"J.M. SWANK, LLC ","pro_number":"5268726245 ","carrier_name":"FED EX B ","claim_amount":"883.80","freight_claim_amount":"344.66","total_claim_amount":"1228.46","amount_paid":".00","status":"Removed ","ship_date":"05\/28\/2021","received_date":"06\/03\/2021","claim_type":"Inbound","created_age":"10 Day","filed_age":"","amount_due":"1228.46"},{"claim_number":"19462","claim_number_link":" 19462<\/a>","location":"SPRINGDALE ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"BRAKEBUSH BROTHERS, INC. ","pro_number":"707299935 ","carrier_name":"XPO A ","claim_amount":"127.44","freight_claim_amount":"29.43","total_claim_amount":"156.87","amount_paid":"156.87","status":"Closed-Paid ","ship_date":"06\/15\/2021","received_date":"06\/18\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19461","claim_number_link":" 19461<\/a>","location":"CLEVELAND ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"US FOODS FL ","pro_number":"8442256631 ","carrier_name":"FEDEX PER EMAIL ","claim_amount":"13.10","freight_claim_amount":"3.62","total_claim_amount":"16.72","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"06\/16\/2021","claim_type":"Outbound","created_age":"10 Day","filed_age":"","amount_due":"16.72"},{"claim_number":"19460","claim_number_link":" 19460<\/a>","location":"CLEVELAND ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"FARMERS PRIDE, INC. ","pro_number":"CLE225796 ","carrier_name":"CH ROB PER SCOTT ","claim_amount":"25.05","freight_claim_amount":"4.32","total_claim_amount":"29.37","amount_paid":".00","status":"Rejected ","ship_date":"06\/15\/2021","received_date":"06\/17\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"29.37"},{"claim_number":"19459","claim_number_link":" 19459<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"PRECISION FOODS, INC. ","pro_number":"718382523 ","carrier_name":"XPO A #5807228237364 ","claim_amount":"229.00","freight_claim_amount":".00","total_claim_amount":"229.00","amount_paid":"229.00","status":"Closed-Paid ","ship_date":"06\/02\/2021","received_date":"06\/04\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19453","claim_number_link":" 19453<\/a>","location":"WATERTOWN ","department_name":"CUSTOMER SERVICE - BR ","vendor_customer":"CUSTOM SEASONINGS, INC. ","pro_number":"408082883-2 ","carrier_name":"NEW PENN ****CLASS 50**** ","claim_amount":"23.95","freight_claim_amount":"2.61","total_claim_amount":"26.56","amount_paid":".00","status":"Rejected ","ship_date":"06\/10\/2021","received_date":"06\/14\/2021","claim_type":"Outbound","created_age":"11 Day","filed_age":"","amount_due":"26.56"},{"claim_number":"19452","claim_number_link":" 19452<\/a>","location":"GERALD ","department_name":"CUSTOMER SERVICE - S\/C ","vendor_customer":"NEWLY WEDS FOODS,INC. MEM ","pro_number":"352572603 ","carrier_name":"XPO B #5814933616428 ","claim_amount":"424.21","freight_claim_amount":".00","total_claim_amount":"424.21","amount_paid":"424.21","status":"Closed-Paid ","ship_date":"06\/14\/2021","received_date":"06\/15\/2021","claim_type":"Outbound","created_age":"12 Day","filed_age":"","amount_due":"0.00"},{"claim_number":"19450","claim_number_link":" 19450<\/a>","location":"BETHLEHEM ","department_name":"OFFICE - A\/R ","vendor_customer":"NU-TEK NATURAL ING ","pro_number":"7407879872 ","carrier_name":"FEDERAL EXPRESS B ","claim_amount":"21.00","freight_claim_amount":"7.32","total_claim_amount":"28.32","amount_paid":".00","status":"Rejected ","ship_date":"05\/18\/2021","received_date":"05\/21\/2021","claim_type":"Inbound","created_age":"12 Day","filed_age":"","amount_due":"28.32"}]}

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    Formatting the SQL it looks like this:

    SELECT
       FCCLMN,
       FCCLMN,
       LOCDES,
       FCDNAM,
       FCCVNM,
       FCMPRO,
       FCMNAM,
       FCPAMT,
       FCFACL,
       FCTOTL,
       FCMPAM,
       FCSTAT,
       FCMSHP,
       FCMREC,
       FCLMTP,
       FCMCRD,
       FCMCLM,
       FCMPAM 
    FROM
       NWFF.FCMASTER 
       left outer join
          NWFO.LOCNAMES 
          on LOCLOC = FCMLOC 
    WHERE
       FCSTAT in 
       (
          'Closed-Deducted',
          'Closed-Overpaid',
          'Closed-Paid',
          'Closed-PWO',
          'Closed-Rejected',
          'Closed-WO',
          'Closed-WO/Ded mix',
          'Declined',
          'Rejected',
          'Removed'
       )
       and FCMCRD >= '20200628' 
       or FCSTAT in 
       (
          'Deduction Pending',
          'Partial Pending',
          'Reinstated',
          'Pending-WO taken' 
       )
    ORDER BY FCCLMN DESC
    LIMIT 0, 15
    

    It doesn't look like that trace was from when column filtering was being performed as I don't see FCCLMN in the condition at all. However, the extra where as a whole still doesn't look like it has parenthesis around it as a whole. What if you use:

    $extraWhere = "((FCSTAT in
         'Closed-Deducted',
         'Closed-Overpaid',
         'Closed-Paid',
         'Closed-PWO',
         'Closed-Rejected',
         'Closed-WO',
         'Closed-WO/Ded mix',
         'Declined',
         'Rejected',
         'Removed'
     
         and FCMCRD >= '" . $archiveDate . "')
     
         or
     
      ( FCSTAT in
             'Deduction Pending',
             'Partial Pending',
             'Reinstated',
             'Pending-WO taken'  )) ";
    

    The point is that I think A and (B and C) or D is causing a program due to the order. It needs to be grouped: A and ((B and C) or D) or something like that.

    Allan

  • bbrindzabbrindza Posts: 299Questions: 68Answers: 1

    Here is the working solution

    $extraWhere = $extraWhere = "(FCSTAT in
         ('Closed-Deducted',
         'Closed-Overpaid',
         'Closed-Paid',
         'Closed-PWO',
         'Closed-Rejected',
         'Closed-WO',
         'Closed-WO/Ded mix',
         'Declined',
         'Rejected',
         'Removed')
        
         and FCMCRD >= '" . $archiveDate . "'
             
         or
             
       FCSTAT in
             ('Open',
              'AP Verified',
              'Filed',
              'Deduction Pending',
              'Partial Pending',
              'Reinstated',
              'Pending-WO taken'   )) ";
    
Sign In or Register to comment.