<?php
require_once(SF_ROOT_DIR . DIRECTORY_SEPARATOR.'classes'. DIRECTORY_SEPARATOR.'class.dmsd.php');
class dms_list_query
{
    private $CustomerId = false;
    private $InvalidCustomers;
    private $clauses = array();
    private $warrantyclauses = "";
    private $criteria;

    # Return list of business name pander keys
    public function GetPanderKeys()
    {
        $name_pander_keys = array();
        $C = new Criteria();
        $C->addSelectColumn(AmmsListPanderKeyPeer::PANDER_KEY);
        $rs = AmmsListPanderKeyPeer::doSelectRs($C);
        while($rs->next())
        {
            $pander_key			= $rs->getString(1);
            if ( strlen($pander_key) > 0 )
            {
                $name_pander_keys[] = $pander_key;
            }
        }

        return $name_pander_keys;
    }

    public function GetBusinessFilterClause()
    {
        $criteria = array();
        foreach($this->BusinessNamePanderKeys as $key)
        {
            $criteria[] = " customer_name NOT LIKE '$key' ";
        }

        return join(' AND ', $criteria);
    }

    public function getDeptClause()
    {
        if (in_array("INTERNAL", $this->criteria["DepartmentInc"]["departments"]))
        {
            return "1";
        }
        else
        {
            return "( dms_ro.department IN ('SERVICE', 'S', 'QUICK LUBE', 'Q', '') OR dms_ro.department IS NULL )";
        }
    }

    public function dms_list_query($criteria, $customer_id = false)
    {
        # Get list of business name pander keys
        $this->BusinessNamePanderKeys = $this->GetPanderKeys();

        $this->criteria = $criteria;
        $this->CustomerId = $customer_id;
        $this->buildClauses();
    }

    public function createTempTables($conn)
    {
        $dept_clause = $this->getDeptClause();
        $deal_clauses = $this->getDealClauses();
        $ro_clauses = $this->getRoClauses();

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_deal_counts";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_deal_counts (
                    dms_customer_id char(36) NOT NULL,
                    dms_vehicle_id varchar(50) NOT NULL,
                    deal_visits int NOT NULL DEFAULT 0,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "INSERT INTO     temp_deal_counts
                SELECT          dms_deal.dms_customer_id,
                                dms_deal.dms_vehicle_id,
                                COUNT(deal_id)
                FROM            dms_deal
                INNER JOIN      dms_consumer
                ON              dms_deal.dms_customer_id = dms_consumer.external_customer_id
                INNER JOIN      dms_vehicle
                ON              dms_deal.dms_vehicle_id = dms_vehicle.dms_vehicle_id AND
                                dms_deal.dms_customer_id = dms_vehicle.external_customer_id AND
                                dms_consumer.external_customer_id = dms_vehicle.external_customer_id
                WHERE           {$deal_clauses}
                GROUP BY        dms_deal.dms_customer_id, dms_deal.dms_vehicle_id";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_ro_counts";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_ro_counts (
                    dms_customer_id varchar(50) NOT NULL,
                    dms_vehicle_id varchar(50) NOT NULL,
                    ro_visits int NOT NULL DEFAULT 0,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "INSERT INTO     temp_ro_counts
                SELECT          dms_ro.dms_customer_id,
                                dms_ro.dms_vehicle_id,
                                COUNT(repair_order_id)
                FROM            dms_ro
                INNER JOIN      dms_consumer
                ON              dms_ro.dms_customer_id = dms_consumer.external_customer_id
                INNER JOIN      dms_vehicle
                ON              dms_ro.dms_vehicle_id = dms_vehicle.dms_vehicle_id AND
                                dms_ro.dms_customer_id = dms_vehicle.external_customer_id AND
                                dms_consumer.external_customer_id = dms_vehicle.external_customer_id
                WHERE           {$dept_clause} AND {$ro_clauses}
                GROUP BY        dms_ro.dms_customer_id, dms_ro.dms_vehicle_id";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_pay";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_pay (
                    repair_order_id int NOT NULL PRIMARY KEY,
                    dms_customer_id varchar(50),
                    dms_vehicle_id varchar(50),
                    open_date datetime,
                    customer_pay decimal(10,2),
                    warranty_pay decimal(10,2),
                    INDEX (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        if ($this->criteria["ServiceDateInc"] && $this->criteria["ServiceDateInc"]["min_date"] && $this->criteria["ServiceDateInc"]["max_date"])
        {
            $sql = "INSERT INTO temp_pay
                    SELECT      repair_order_id,
                                dms_customer_id,
                                dms_vehicle_id,
                                open_date,
                                invoice_total,
                                warranty_total
                    FROM        dms_ro
                    WHERE       open_date BETWEEN '" . $this->criteria["ServiceDateInc"]["min_date"] . " 00:00:00' AND '" . $this->criteria["ServiceDateInc"]["max_date"] . " 23:59:59'";
            $this->executeTempTableStatement($conn, $sql);
        }

        if ($this->criteria["NoServiceInc"])
        {
            if (!empty($this->criteria["NoServiceInc"]["days"]))
            {
                $date = date('Y-m-d', strtotime("-" . intval($this->criteria["NoServiceInc"]["days"]) . " days")) . " 00:00:00";
                $sql = "DROP TEMPORARY TABLE IF EXISTS temp_last_service";
                $this->executeTempTableStatement($conn, $sql);

                // Create a temporary table of all customers or vehicles
                // that have been serviced recently.
                // This table will be used to EXCLUDE these items in the final query.
                if ($this->criteria['ListType'] == 'names')
                {
                    $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_last_service (
                                last_service_id varchar(50) NOT NULL PRIMARY KEY
                            )";
                    $this->executeTempTableStatement($conn, $sql);

                    $sql = "INSERT INTO temp_last_service
                            SELECT      dms_customer_id
                            FROM        dms_ro
                            WHERE       open_date >= '" . $date . "'
                            GROUP BY    dms_customer_id";
                    $this->executeTempTableStatement($conn, $sql);
                }
                else
                {
                    $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_last_service (
                                last_service_id varchar(101) NOT NULL PRIMARY KEY
                            )";
                    $this->executeTempTableStatement($conn, $sql);

                    $sql = "INSERT INTO temp_last_service
                            SELECT      CONCAT(dms_customer_id,'_',dms_vehicle_id) AS last_service_id
                            FROM        dms_ro
                            WHERE       open_date >= '" . $date . "'
                            GROUP BY    dms_customer_id, dms_vehicle_id";
                    $this->executeTempTableStatement($conn, $sql);
                }
            }
        }

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_deal_warranty_months";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_deal_warranty_months (
                    dms_customer_id varchar(50),
                    dms_vehicle_id varchar(50),
                    matches int,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_deal_warranty_miles";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_deal_warranty_miles (
                    dms_customer_id varchar(50),
                    dms_vehicle_id varchar(50),
                    matches int,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_ro_warranty_months";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_ro_warranty_months (
                    dms_customer_id varchar(50),
                    dms_vehicle_id varchar(50),
                    matches int,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "DROP TEMPORARY TABLE IF EXISTS temp_ro_warranty_miles";
        $this->executeTempTableStatement($conn, $sql);

        $sql = "CREATE TEMPORARY TABLE IF NOT EXISTS temp_ro_warranty_miles (
                    dms_customer_id varchar(50),
                    dms_vehicle_id varchar(50),
                    matches int,
                    CONSTRAINT PRIMARY KEY (dms_customer_id, dms_vehicle_id)
                )";
        $this->executeTempTableStatement($conn, $sql);

        if ($this->criteria["ExtWarrantyInc"])
        {
            // First for the deal table
            // If months remaining has been specified
            if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_max"]))
            {
                // Test the warranty expiration date against the number of months remaining from now
                $sql = "INSERT INTO temp_deal_warranty_months
                        SELECT      dms_customer_id,
                                    dms_vehicle_id,
                                    CASE
                                        WHEN warranty_expiration_date < '1970-01-01 00:00:00' THEN 0
                                        WHEN PERIOD_DIFF(DATE_FORMAT(warranty_expiration_date, '%Y%m'), DATE_FORMAT(NOW(), '%Y%m')) BETWEEN '" .
                                        intval($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_min"]) . "' AND '" .
                                        intval($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_max"]) . "' THEN 1
                                        WHEN warranty_expiration_date IS NULL THEN 0
                                        ELSE 0
                                    END AS matches
                        FROM        dms_deal
                        GROUP BY    dms_customer_id, dms_vehicle_id";
                $this->executeTempTableStatement($conn, $sql);
            }

            // If miles remaining has been specified
            if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_max"]))
            {
                // Test the warranty expiration mileage against the calculated estimated mileage
                $sql = "INSERT INTO temp_deal_warranty_miles
                        SELECT      dms_deal.dms_customer_id,
                                    dms_deal.dms_vehicle_id,
                                    CASE
                                        WHEN dms_deal.warranty_expiration_miles = 0 THEN 0
                                        WHEN dms_deal.warranty_expiration_miles - ((DATEDIFF(CURRENT_DATE, dms_vehicle.amms_last_mileage_date) * dms_vehicle.amms_avg_daily_miles) + dms_vehicle.amms_last_miles) BETWEEN '" .
                                        intval($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_min"]) . "' AND '" .
                                        intval($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_max"]) . "' THEN 1
                                        WHEN dms_deal.warranty_expiration_miles IS NULL THEN 0
                                        ELSE 0
                                    END AS matches
                        FROM        dms_deal
                        INNER JOIN  dms_vehicle
                        ON          dms_deal.dms_customer_id = dms_vehicle.external_customer_id AND
                                    dms_deal.dms_vehicle_id = dms_vehicle.dms_vehicle_id
                        GROUP BY    dms_deal.dms_customer_id, dms_deal.dms_vehicle_id";
                $this->executeTempTableStatement($conn, $sql);
            }

            // Now for the ro table
            // If months remaining has been specified
            if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_max"]))
            {
                // Test the warranty expiration date against the number of months remaining from now
                $sql = "INSERT INTO temp_ro_warranty_months
                        SELECT      dms_customer_id,
                                    dms_vehicle_id,
                                    CASE
                                        WHEN warranty_expiration_date < '1970-01-01 00:00:00' THEN 0
                                        WHEN PERIOD_DIFF(DATE_FORMAT(warranty_expiration_date, '%Y%m'), DATE_FORMAT(NOW(), '%Y%m')) BETWEEN '" .
                                        intval($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_min"]) . "' AND '" .
                                        intval($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_max"]) . "' THEN 1
                                        WHEN warranty_expiration_date IS NULL THEN 0
                                        ELSE 0
                                    END AS matches
                        FROM        dms_ro
                        GROUP BY    dms_customer_id, dms_vehicle_id";
                $this->executeTempTableStatement($conn, $sql);
            }

            // If miles remaining has been specified
            if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_max"]))
            {
                // Test the warranty expiration mileage against the calculated estimated mileage
                $sql = "INSERT INTO temp_ro_warranty_miles
                        SELECT      dms_ro.dms_customer_id,
                                    dms_ro.dms_vehicle_id,
                                    CASE
                                        WHEN dms_ro.warranty_expiration_miles = 0 THEN 0
                                        WHEN dms_ro.warranty_expiration_miles - ((DATEDIFF(CURRENT_DATE, dms_vehicle.amms_last_mileage_date) * dms_vehicle.amms_avg_daily_miles) + dms_vehicle.amms_last_miles) BETWEEN '" .
                                        intval($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_min"]) . "' AND '" .
                                        intval($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_max"]) . "' THEN 1
                                        WHEN dms_ro.warranty_expiration_miles IS NULL THEN 0
                                        ELSE 0
                                    END AS matches
                        FROM        dms_ro
                        INNER JOIN  dms_vehicle
                        ON          dms_ro.dms_customer_id = dms_vehicle.external_customer_id AND
                                    dms_ro.dms_vehicle_id = dms_vehicle.dms_vehicle_id
                        GROUP BY    dms_ro.dms_customer_id, dms_ro.dms_vehicle_id";
                $this->executeTempTableStatement($conn, $sql);
            }
        }
    }

    private function executeTempTableStatement($conn, $sql)
    {
        echo "\nTempTableStatement:\n\n{$sql}\n";
        if (is_resource($conn))
        {
            mysql_query($sql, $conn) or die("\r\nerror executing:\n" . $sql . "\n" . mysql_error());
        }
        else
        {
            $conn->executeQuery($sql) or die("\r\nerror executing:\n" . $sql . "\n" . mysql_error());
        }
    }

    private function getDealClauses()
    {
        $clauses = array();
        if($this->criteria["EmployeeEx"])
        {
            $arEmpex = array();
            $empexsolddate = "";
            if(in_array($this->criteria["EmployeeEx"]["ex_sales_service"], array("Sold by", "Sold or Serviced by")))
            {
                if(is_array($this->criteria["EmployeeEx"]["ex_employee"]) && !empty($this->criteria["EmployeeEx"]["ex_employee"]))
                {
                    $ex_employees = array_values($this->criteria["EmployeeEx"]["ex_employee"]);
                    array_walk($ex_employees, "mysql_real_escape_string");
                    $arEmpex[] = "loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "')";
                }
                elseif(is_string($this->criteria["EmployeeEx"]["ex_employee"]) && $this->criteria["EmployeeEx"]["ex_employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $ex_employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeEx"]["ex_employee"]));
                    $arEmpex[] = "loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "')";
                }

                if($this->criteria["EmployeeEx"]["ex_start_date"] && $this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexsolddate = "(sold_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL)";
                }
                elseif($this->criteria["EmployeeEx"]["ex_start_date"])
                {
                    $empexsolddate = "(sold_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' OR sold_date IS NULL)";
                }
                elseif($this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexsolddate = "(sold_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL)";
                }
                else
                {
                    $empexsolddate = "deal_id IS NULL";
                }
                $arEmpex[] = $empexsolddate;
                $empex = implode(" OR ", $arEmpex);
            }

            if(!empty($empex))
            {
                $clauses[] = "(" . $empex . ")";
            }
        }
        if(!$this->criteria["EmployeeInc"] || $this->criteria["EmployeeInc"]["sales_service"] != $this->criteria["EmployeeEx"]["ex_sales_service"])
        {
            if($empexsolddate != "")
            {
                $clauses[] = "(" . $empexsolddate . ")";
            }
        }
        if($this->criteria["EmployeeInc"])
        {
            $arTemp = array();
            if(in_array($this->criteria["EmployeeInc"]["sales_service"], array("Sold by", "Sold or Serviced by")))
            {
                if(is_array($this->criteria["EmployeeInc"]["employee"]) && !empty($this->criteria["EmployeeInc"]["employee"]))
                {
                    $employees = array_values($this->criteria["EmployeeInc"]["employee"]);
                    array_walk($employees, "mysql_real_escape_string");
                    $arTemp[] =  "loop_sales_person_id IN ('" . implode("','", $employees) . "')";
                }
                elseif(is_string($this->criteria["EmployeeInc"]["employee"]) && $this->criteria["EmployeeInc"]["employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeInc"]["employee"]));
                    $arTemp[] = "loop_sales_person_id IN ('" . implode("','", $employees) . "')";
                }

                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(sold_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                else
                {
                    if(!empty($employees))
                    {
                        $arTemp[] = "deal_id IS NOT NULL";
                    }
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            elseif($this->criteria["EmployeeInc"]["sales_service"] == "Sold and Serviced by")
            {
                // The salesperson and service advisor will almost certainly not be the same person,
                // so we don't allow the user to select an employee for this option.
                // The GUI will hide this selection in this case.
                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(sold_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                else
                {
                    $arTemp[] = "deal_id IS NOT NULL";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            if(!empty($tempstr))
            {
                $clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["VehicleInc"])
        {
            if($this->criteria["VehicleInc"]["make"] && intval($this->criteria["VehicleInc"]["make"][0]) != -1)
            {
                if($this->criteria["VehicleInc"]["make"][0] == 'DEALER_MAKES')
                {
                    if($this->CustomerId)
                    {
                        $Wilma = Propel::getConnection();
                        $customer_id = $this->CustomerId;
                        $query = "SELECT makes FROM customer_extra WHERE customer_id = '$customer_id'";
                        $results = $Wilma->executeQuery($query);
                        while($results->next())
                            $makes = unserialize($results->get('makes'));
                        if(empty($makes))
                        {
                            $query = "SELECT store_brands FROM market_evaluation WHERE customer_id = '$customer_id'";
                            $results = $Wilma->executeQuery($query);
                            while($results->next())
                                $makes = explode("|",trim($results->get('store_brands'),"|"));
                        }

                        $clauses[] = "dms_vehicle.vehicle_make IN ('".implode("','",$makes)."')";
                    }
                }
                else
                {
                    $tempstr = "";
                    foreach($this->criteria["VehicleInc"]["make"] as $item)
                    $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                    $tempstr = rtrim($tempstr,',');
                    $clauses[] = "dms_vehicle.vehicle_make IN (" . $tempstr . ")";
                }
            }

            if($this->criteria["VehicleInc"]["model"] && intval($this->criteria["VehicleInc"]["model"][0]) != -1)
            {
                $tempstr = "";
                foreach($this->criteria["VehicleInc"]["model"] as $item)
                $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                $tempstr = rtrim($tempstr,',');
                $clauses[] = "dms_vehicle.vehicle_model IN (" . $tempstr . ")";
            }
            if($this->criteria["VehicleInc"]["start_year"])
            {
                if(!is_numeric($this->criteria["VehicleInc"]["start_year"]))
                    $this->criteria["VehicleInc"]["start_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["start_year"]));
                if(!is_numeric($this->criteria["VehicleInc"]["end_year"]))
                    $this->criteria["VehicleInc"]["end_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["end_year"]));
                $clauses[] = "dms_vehicle.vehicle_year BETWEEN " . intval($this->criteria["VehicleInc"]["start_year"]) . " AND " . intval($this->criteria["VehicleInc"]["end_year"]);
            }
        }
        if($this->criteria["VinInc"])
        {
            if($this->criteria["VinInc"]["start_vin"])
            {
                $clauses[] = "SUBSTRING(vin,-6) BETWEEN '" . mysql_real_escape_string($this->criteria["VinInc"]["start_vin"]) . "' AND '" . mysql_real_escape_string($this->criteria["VinInc"]["end_vin"]) . "'";
            }
        }
        if($this->criteria["MileageInc"])
        {
            if(isset($this->criteria["MileageInc"]["start_miles"]))
            {
                $clauses[] = "dms_vehicle.mileage BETWEEN " . intval($this->criteria["MileageInc"]["start_miles"]) . " AND " . intval($this->criteria["MileageInc"]["end_miles"]);
            }
        }
        if($this->criteria["StateInc"])
        {
            if($this->criteria["StateInc"]["state"])
            {
                $clauses[] = "current_state ='" . mysql_real_escape_string($this->criteria["StateInc"]["state"]) . "'";
            }
        }
        if($this->criteria["EquityInc"])
        {
            if($this->criteria["EquityInc"]["equity"])
            {
                $temp = "";
                if($this->criteria["EquityInc"]["equitygt"] == "greater")
                {
                    $temp = ">=";
                }
                else
                {
                    $temp = "<=";
                }
                $clauses[] = "(equity_balance != 0 AND equity_balance " . $temp . " " . $this->criteria["EquityInc"]["equity"] . ")";
            }
        }
        if($this->criteria["EmailInc"])
        {
            if($this->criteria["EmailInc"] == "valid email")
            {
                $clauses[] = "customer_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
            else
            {
                $clauses[] = "customer_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
        }
        if($this->criteria["AddressInc"])
        {
            if($this->criteria["AddressInc"] == "valid addresses")
            {
                $clauses[] = "address_id > 0";
            }
            else
            {
                $clauses[] = "address_id < 0";
            }
        }
        if($this->criteria["NewUsedInc"])
        {
            if($this->criteria["NewUsedInc"] == "used")
            {
                $clauses[] = "new_used_type_id = 2";
            }
            elseif($this->criteria["NewUsedInc"] == "new")
            {
                $clauses[] = "new_used_type_id = 1";
            }
            else
            {
                $clauses[] = "new_used_type_id = 3";
            }
        }
        if($this->criteria["PurchDateEx"])
        {
            $clauses[] = "( sold_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateEx"]["ex_purchdate_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateEx"]["ex_purchdate_end_date"]))) . " 23:59:59' OR sold_date IS NULL )";
        }
        if($this->criteria["PurchDateInc"])
        {
            $clauses[] = "( sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateInc"]["purchdate_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateInc"]["purchdate_end_date"]))) . " 23:59:59' OR sold_date IS NULL )";
        }
        if($this->criteria["PurchaseInc"])
        {
            if($this->criteria["PurchaseInc"] == "lease")
            {
                $clauses[] = "purchase_type_id = 2";
            }
            elseif($this->criteria["PurchaseInc"] == "loan")
            {
                $clauses[] = "purchase_type_id = 3";
            }
            else
            {
                $clauses[] = "purchase_type_id = 1";
            }
        }
        if($this->criteria["WarrantyInc"])
        {
            if($this->criteria["WarrantyInc"] == "under warranty")
            {
                $this->warrantyclauses = "  NOT((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
            else
            {
                $this->warrantyclauses = " ((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
        }
        if($this->criteria["LoyaltyInc"])
        {
            // Check to see they are in the loyalty program
            if (!empty($this->criteria["LoyaltyInc"]["active"]))
            {
                // 0 = ignore; 1 = active; 2 = inactive
                $active = intval($this->criteria["LoyaltyInc"]["active"]) % 2;
                $clauses[] = "is_in_loyalty_program = '" . $active . "'";
            }

            // Check how many points they have
            if (!empty($this->criteria["LoyaltyInc"]["start_loyalty"]) || !empty($this->criteria["LoyaltyInc"]["end_loyalty"]))
            {
                $clauses[] = "current_loyalty_points BETWEEN " . intval($this->criteria["LoyaltyInc"]["start_loyalty"]) . " AND " . intval($this->criteria["LoyaltyInc"]["end_loyalty"]);
            }
        }
        if($this->criteria["BirthdayInc"])
        {
            if($this->criteria["BirthdayInc"]["start_birth_month"])
            {
                $clauses[] = "DAYOFYEAR(birthday) BETWEEN DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_day"]),2,'0',STR_PAD_LEFT) . "') AND DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_day"]),2,'0',STR_PAD_LEFT) . "')";
            }
        }
        if($this->criteria["BlackBookInc"])
        {
            $clauses[] = "dms_vehicle.blackbook_value BETWEEN " . floatval($this->criteria["BlackBookInc"]["blackbookinc_min"]) . " AND " . floatval($this->criteria["BlackBookInc"]["blackbookinc_max"]);
        }
        if($this->criteria["MonthlyPmtInc"])
        {
            $clauses[] = "dms_deal.monthly_payment BETWEEN " . floatval($this->criteria["MonthlyPmtInc"]["monthlypmtinc_min"]) . " AND " . floatval($this->criteria["MonthlyPmtInc"]["monthlypmtinc_max"]);
        }
        if($this->criteria["LoanBalanceInc"])
        {
            if($this->criteria["LoanBalanceInc"]["loanbalanceinc_min"] != 'all')
            {
                $loanEndsMin = date("Y-m-d 00:00:00", strtotime("+".$this->criteria["LoanBalanceInc"]["loanbalanceinc_min"]." months"));
                $clauses[] = "dms_deal.final_payment_date >= '".$loanEndsMin."'";
            }
            if($this->criteria["LoanBalanceInc"]["loanbalanceinc_max"] != 'all')
            {
                $loanEndsMax = date("Y-m-d 00:00:00", strtotime("+".$this->criteria["LoanBalanceInc"]["loanbalanceinc_max"]." months"));
                $clauses[] = "dms_deal.final_payment_date <= '".$loanEndsMax."'";
            }
        }
        if($this->criteria["TermMonthsInc"])
        {
            if ($this->criteria["TermMonthsInc"]["termmonthsinc_min"] != 'all')
            {
                $clauses[] = "dms_deal.term_months >= '".$this->criteria["TermMonthsInc"]["termmonthsinc_min"]."'";
            }
            if ($this->criteria["TermMonthsInc"]["termmonthsinc_max"] != 'all')
            {
                $clauses[] = "dms_deal.term_months <= '".$this->criteria["TermMonthsInc"]["termmonthsinc_max"]."'";
            }
        }
        if($this->criteria["CampaignEx"])
        {
            if($this->criteria["CampaignEx"]["campaigns"])
            {
                if (!empty($this->exclude_external_customer_ids))
                {
                    $clauses[] = "dms_consumer.external_customer_id NOT IN (" . $this->exclude_external_customer_ids . ")";
                }
            }
        }
        if($this->criteria["TrigEx"])
        {
            if (!empty($this->exclude_addids))
            {
                $clauses[] = "address_id NOT IN (".$this->exclude_addids.")";
            }
        }
        if($this->criteria["extraClauses"])
        {
            $clauses[] = "(" . $this->criteria["extraClauses"] . ")";
        }

        if($this->criteria["StuInc"])
        {
            if($this->criteria["StuInc"] == 1)
            {
                $clauses[] = "polk_owned_status <> 4";
            }
            if($this->criteria["StuInc"] == 2)
            {
                $clauses[] = "polk_owned_status = 4";
            }
        }

        if($this->criteria["DistInc"])
        {
            $clauses[] = "(dist <= '".intval($this->criteria["DistInc"])."' AND dist >= 0)";
        }

        if($this->criteria["b2b"])
        {
            $temp = intval($this->criteria["b2b"]);
            if($temp == 1)
            {
                $clauses[] = "dms_consumer.is_b2b = 0";
            }
            elseif($temp == 2)
            {
                $clauses[] = "dms_consumer.is_b2b = 1";
            }

            if($temp == 1)
            {
                $clauses[] = $this->GetBusinessFilterClause();
            }
        }

        if($this->criteria["PhoneInc"])
        {
            $regex = " REGEXP '^[1]?[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$'";
            if(!empty($this->criteria["PhoneInc"]["any_phone_valid"]))
            {
                $temp = array();
                $temp[] = "customer_voice_phone" . $regex;
                $temp[] = "customer_text_phone" . $regex;
                $temp[] = "home_phone" . $regex;
                $temp[] = "work_phone" . $regex;
                $temp[] = "mobile_phone" . $regex;
                $temp[] = "customer_voice_phone_normal" . $regex;
                $clauses[] = "( " . implode(" OR ", $temp) . " )";
            }
            else
            {
                $temp = array();
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_valid"]))
                {
                    $temp[] = "customer_voice_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_text_phone_valid"]))
                {
                    $temp[] = "customer_text_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["home_phone_valid"]))
                {
                    $temp[] = "home_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["work_phone_valid"]))
                {
                    $temp[] = "work_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["mobile_phone_valid"]))
                {
                    $temp[] = "mobile_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_normal_valid"]))
                {
                    $temp[] = "customer_voice_phone_normal" . $regex;
                }
                if (!empty($temp))
                {
                    $clauses[] = "( " . implode(" OR ", $temp) . " )";
                }
            }
        }

        if($this->criteria["LoanRateInc"])
        {
            if($this->criteria["LoanRateInc"]["loanrateinc_min"])
            {
                $clauses[] = "interest_rate >= '" . $this->criteria["LoanRateInc"]["loanrateinc_min"] . "'";
            }
            if($this->criteria["LoanRateInc"]["loanrateinc_max"])
            {
                $clauses[] = "interest_rate <= '" . $this->criteria["LoanRateInc"]["loanrateinc_max"] . "'";
            }
        }

        if ($this->criteria["ZipCodeInc"])
        {
            if (!empty($this->criteria["ZipCodeInc"]["zipcodes"]))
            {
                $clauses[] = "SUBSTRING(current_zip,1,5) in ('" . implode("','", $this->criteria["ZipCodeInc"]["zipcodes"]) . "')";
            }
        }

        if(count ($clauses) > 0)
        {
            return implode(" AND ", $clauses);
        }
        else
        {
            return "1";
        }
    }

    private function getRoClauses()
    {
        $clauses = array();
        if($this->criteria["EmployeeEx"])
        {
            $arEmpex = array();
            $empexopendate = "";
            if(in_array($this->criteria["EmployeeEx"]["ex_sales_service"], array("Serviced by", "Sold or Serviced by")))
            {
                if(is_array($this->criteria["EmployeeEx"]["ex_employee"]) && !empty($this->criteria["EmployeeEx"]["ex_employee"]))
                {
                    $ex_employees = array_values($this->criteria["EmployeeEx"]["ex_employee"]);
                    array_walk($ex_employees, "mysql_real_escape_string");
                    $arEmpex[] = "(advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeEx"]["ex_employee"]) && $this->criteria["EmployeeEx"]["ex_employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $ex_employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeEx"]["ex_employee"]));
                    $arEmpex[] = "(advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }

                if($this->criteria["EmployeeEx"]["ex_start_date"] && $this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = "(open_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL)";
                }
                elseif($this->criteria["EmployeeEx"]["ex_start_date"])
                {
                    $empexopendate = "(open_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' OR open_date IS NULL)";
                }
                elseif($this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = "(open_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL)";
                }
                else
                {
                    $empexopendate = "repair_order_id IS NULL";
                }
                $arEmpex[] = $empexopendate;
                $empex = implode(" OR ", $arEmpex);
            }

            if(!empty($empex))
            {
                $clauses[] = "(" . $empex . ")";
            }
        }
        if(!$this->criteria["EmployeeInc"] || $this->criteria["EmployeeInc"]["sales_service"] != $this->criteria["EmployeeEx"]["ex_sales_service"])
        {
            if($empexopendate != "")
            {
                $clauses[] = "(" . $empexopendate . ")";
            }
        }
        if($this->criteria["EmployeeInc"])
        {
            $arTemp = array();
            if(in_array($this->criteria["EmployeeInc"]["sales_service"], array("Serviced by", "Sold or Serviced by")))
            {
                if(is_array($this->criteria["EmployeeInc"]["employee"]) && !empty($this->criteria["EmployeeInc"]["employee"]))
                {
                    $employees = array_values($this->criteria["EmployeeInc"]["employee"]);
                    array_walk($employees, "mysql_real_escape_string");
                    $arTemp[] = "(advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeInc"]["employee"]) && $this->criteria["EmployeeInc"]["employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeInc"]["employee"]));
                    $arTemp[] = "(advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }

                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(open_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                else
                {
                    $arTemp[] = "repair_order_id IS NOT NULL";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            elseif($this->criteria["EmployeeInc"]["sales_service"] == "Sold and Serviced by")
            {
                // The salesperson and service advisor will almost certainly not be the same person,
                // so we don't allow the user to select an employee for this option.
                // The GUI will hide this selection in this case.
                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(open_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                else
                {
                    $arTemp[] = "repair_order_id IS NOT NULL";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            if(!empty($tempstr))
            {
                $clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["VehicleInc"])
        {
            if($this->criteria["VehicleInc"]["make"] && intval($this->criteria["VehicleInc"]["make"][0]) != -1)
            {
                if($this->criteria["VehicleInc"]["make"][0] == 'DEALER_MAKES')
                {
                    if($this->CustomerId)
                    {
                        $Wilma = Propel::getConnection();
                        $customer_id = $this->CustomerId;
                        $query = "SELECT makes FROM customer_extra WHERE customer_id = '$customer_id'";
                        $results = $Wilma->executeQuery($query);
                        while($results->next())
                        {
                            $makes = unserialize($results->get('makes'));
                        }
                        if(empty($makes))
                        {
                            $query = "SELECT store_brands FROM market_evaluation WHERE customer_id = '$customer_id'";
                            $results = $Wilma->executeQuery($query);
                            while($results->next())
                            {
                                $makes = explode("|",trim($results->get('store_brands'),"|"));
                            }
                        }

                        $clauses[] = "dms_vehicle.vehicle_make IN ('".implode("','",$makes)."')";
                    }
                }
                else
                {
                    $tempstr = "";
                    foreach($this->criteria["VehicleInc"]["make"] as $item)
                    {
                        $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                    }
                    $tempstr = rtrim($tempstr,',');
                    $clauses[] = "dms_vehicle.vehicle_make IN (" . $tempstr . ")";
                }
            }

            if($this->criteria["VehicleInc"]["model"] && intval($this->criteria["VehicleInc"]["model"][0]) != -1)
            {
                $tempstr = "";
                foreach($this->criteria["VehicleInc"]["model"] as $item)
                {
                    $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                }
                $tempstr = rtrim($tempstr,',');
                $clauses[] = "dms_vehicle.vehicle_model IN (" . $tempstr . ")";
            }
            if($this->criteria["VehicleInc"]["start_year"])
            {
                if(!is_numeric($this->criteria["VehicleInc"]["start_year"]))
                {
                    $this->criteria["VehicleInc"]["start_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["start_year"]));
                }
                if(!is_numeric($this->criteria["VehicleInc"]["end_year"]))
                {
                    $this->criteria["VehicleInc"]["end_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["end_year"]));
                }
                $clauses[] = "dms_vehicle.vehicle_year BETWEEN " . intval($this->criteria["VehicleInc"]["start_year"]) . " AND " . intval($this->criteria["VehicleInc"]["end_year"]);
            }
        }
        if($this->criteria["VinInc"])
        {
            if($this->criteria["VinInc"]["start_vin"])
            {
                $clauses[] = "SUBSTRING(vin,-6) BETWEEN '" . mysql_real_escape_string($this->criteria["VinInc"]["start_vin"]) . "' AND '" . mysql_real_escape_string($this->criteria["VinInc"]["end_vin"]) . "'";
            }
        }
        if($this->criteria["MileageInc"])
        {
            if(isset($this->criteria["MileageInc"]["start_miles"]))
            {
                $clauses[] = "dms_vehicle.mileage BETWEEN " . intval($this->criteria["MileageInc"]["start_miles"]) . " AND " . intval($this->criteria["MileageInc"]["end_miles"]);
            }
        }
        if($this->criteria["StateInc"])
        {
            if($this->criteria["StateInc"]["state"])
            {
                $clauses[] = "current_state ='" . mysql_real_escape_string($this->criteria["StateInc"]["state"]) . "'";
            }
        }
        if($this->criteria["OpCodeInc"])
        {
            if($this->criteria["OpCodeInc"]["opcode"])
            {
                $temparr = explode(' ',$this->criteria["OpCodeInc"]["opcode"]);
                $tempstr = "";
                foreach($temparr as $item)
                $tempstr .= " OR op_codes REGEXP '([|]|[[:<:]])" . mysql_real_escape_string($item) . "([[:>:]]|[|])'";
                $tempstr = ltrim($tempstr,' OR');
                $clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["LastServEx"])
        {
            $clauses[] = " (open_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServEx"]["ex_lastserv_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServEx"]["ex_lastserv_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
        }
        if($this->criteria["LastServInc"])
        {
            $clauses[] = " (open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServInc"]["lastserv_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServInc"]["lastserv_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
        }
        if($this->criteria["WarRepInc"])
        {
            $clauses[] = " (warranty_total BETWEEN '" . mysql_real_escape_string($this->criteria["WarRepInc"]["war_rep_inc_start"]) . "' AND '" . mysql_real_escape_string($this->criteria["WarRepInc"]["war_rep_inc_end"]) . "' OR warranty_total IS NULL )";
        }
        if($this->criteria["EmailInc"])
        {
            if($this->criteria["EmailInc"] == "valid email")
            {
                $clauses[] = "customer_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
            else
            {
                $clauses[] = "customer_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
        }
        if($this->criteria["AddressInc"])
        {
            if($this->criteria["AddressInc"] == "valid addresses")
            {
                $clauses[] = "address_id > 0";
            }
            else
            {
                $clauses[] = "address_id < 0";
            }
        }
        if($this->criteria["WarrantyInc"])
        {
            if($this->criteria["WarrantyInc"] == "under warranty")
            {
                $this->warrantyclauses = "  NOT((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
            else
            {
                $this->warrantyclauses = " ((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
        }
        if($this->criteria["LoyaltyInc"])
        {
            // Check to see they are in the loyalty program
            if (!empty($this->criteria["LoyaltyInc"]["active"]))
            {
                // 0 = ignore; 1 = active; 2 = inactive
                $active = intval($this->criteria["LoyaltyInc"]["active"]) % 2;
                $clauses[] = "is_in_loyalty_program = '" . $active . "'";
            }

            // Check how many points they have
            if (!empty($this->criteria["LoyaltyInc"]["start_loyalty"]) || !empty($this->criteria["LoyaltyInc"]["end_loyalty"]))
            {
                $clauses[] = "current_loyalty_points BETWEEN " . intval($this->criteria["LoyaltyInc"]["start_loyalty"]) . " AND " . intval($this->criteria["LoyaltyInc"]["end_loyalty"]);
            }
        }
        if($this->criteria["BirthdayInc"])
        {
            if($this->criteria["BirthdayInc"]["start_birth_month"])
            {
                $clauses[] = "DAYOFYEAR(birthday) BETWEEN DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_day"]),2,'0',STR_PAD_LEFT) . "') AND DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_day"]),2,'0',STR_PAD_LEFT) . "')";
            }
        }
        if($this->criteria["BlackBookInc"])
        {
            $clauses[] = "dms_vehicle.blackbook_value BETWEEN " . floatval($this->criteria["BlackBookInc"]["blackbookinc_min"]) . " AND " . floatval($this->criteria["BlackBookInc"]["blackbookinc_max"]);
        }
        if($this->criteria["CampaignEx"])
        {
            if($this->criteria["CampaignEx"]["campaigns"])
            {
                if (!empty($this->exclude_external_customer_ids))
                {
                    $clauses[] = "dms_consumer.external_customer_id NOT IN (" . $this->exclude_external_customer_ids . ")";
                }
            }
        }
        if($this->criteria["TrigEx"])
        {
            if (!empty($this->exclude_addids))
            {
                $clauses[] = "address_id NOT IN (".$this->exclude_addids.")";
            }
        }
        if($this->criteria["OpCodeEx"])
        {
            if($this->criteria["OpCodeEx"]["exopcode"])
            {
                $temparr = explode(' ',$this->criteria["OpCodeEx"]["exopcode"]);
                $tempstr = "";
                foreach($temparr as $item)
                {
                    //[[:<:]] = start of word [[:>:]] = end of word Mysql REGEXP metachars
                    $tempstr .= " AND op_codes NOT REGEXP '([|]|[[:<:]])" . mysql_real_escape_string($item) . "([[:>:]]|[|])'";
                }
                $tempstr = ltrim($tempstr,' AND');
                $clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["extraClauses"])
        {
            $clauses[] = "(" . $this->criteria["extraClauses"] . ")";
        }

        if($this->criteria["StuInc"])
        {
            if($this->criteria["StuInc"] == 1)
            {
                $clauses[] = "polk_owned_status <> 4";
            }
            if($this->criteria["StuInc"] == 2)
            {
                $clauses[] = "polk_owned_status = 4";
            }
        }

        if($this->criteria["DistInc"])
        {
            $clauses[] = "(dist <= '".intval($this->criteria["DistInc"])."' AND dist >= 0)";
        }

        if($this->criteria["b2b"])
        {
            $temp = intval($this->criteria["b2b"]);
            if($temp == 1)
            {
                $clauses[] = "dms_consumer.is_b2b = 0";
            }
            elseif($temp == 2)
            {
                $clauses[] = "dms_consumer.is_b2b = 1";
            }

            if($temp == 1)
            {
                $clauses[] = $this->GetBusinessFilterClause();
            }
        }

        if($this->criteria["PhoneInc"])
        {
            $regex = " REGEXP '^[1]?[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$'";
            if(!empty($this->criteria["PhoneInc"]["any_phone_valid"]))
            {
                $temp = array();
                $temp[] = "customer_voice_phone" . $regex;
                $temp[] = "customer_text_phone" . $regex;
                $temp[] = "home_phone" . $regex;
                $temp[] = "work_phone" . $regex;
                $temp[] = "mobile_phone" . $regex;
                $temp[] = "customer_voice_phone_normal" . $regex;
                $clauses[] = "( " . implode(" OR ", $temp) . " )";
            }
            else
            {
                $temp = array();
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_valid"]))
                {
                    $temp[] = "customer_voice_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_text_phone_valid"]))
                {
                    $temp[] = "customer_text_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["home_phone_valid"]))
                {
                    $temp[] = "home_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["work_phone_valid"]))
                {
                    $temp[] = "work_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["mobile_phone_valid"]))
                {
                    $temp[] = "mobile_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_normal_valid"]))
                {
                    $temp[] = "customer_voice_phone_normal" . $regex;
                }
                if (!empty($temp))
                {
                    $clauses[] = "( " . implode(" OR ", $temp) . " )";
                }
            }
        }

        if ($this->criteria["ZipCodeInc"])
        {
            if (!empty($this->criteria["ZipCodeInc"]["zipcodes"]))
            {
                $clauses[] = "SUBSTRING(current_zip,1,5) in ('" . implode("','", $this->criteria["ZipCodeInc"]["zipcodes"]) . "')";
            }
        }

        if ($this->criteria["DepartmentInc"])
        {
            if (!empty($this->criteria["DepartmentInc"]["departments"]))
            {
                $clauses[] = "department in ('" . implode("','", $this->criteria["DepartmentInc"]["departments"]) . "')";
            }
        }

        if(count ($clauses) > 0)
        {
            return implode(" AND ", $clauses);
        }
        else
        {
            return "1";
        }
    }

    public function baseQuery($extraClauses = "")
    {
        $dept_clause = $this->getDeptClause();

        $sql = "
                            SELECT      *
                            FROM
                            (
                                SELECT      *
                                FROM
                                (
                                    SELECT      dms_consumer.gender,
                                                dms_vehicle.polk_owned_status,
                                                dms_vehicle.polk_owned_date,
                                                dms_consumer.customer_name,
                                                dms_consumer.first_name,
                                                dms_consumer.last_name,
                                                dms_consumer.middle_name,
                                                dms_consumer.external_customer_id,
                                                dms_consumer.is_b2b,
                                                dms_consumer.is_fake,
                                                dms_consumer.is_deleted,
                                                CASE
                                                    WHEN dms_email.email != '' THEN dms_email.email
                                                    ELSE dms_consumer.customer_email
                                                END as 'customer_email',
                                                dms_consumer.birthday,
                                                dms_consumer.customer_voice_phone,
                                                dms_consumer.customer_text_phone,
                                                dms_consumer.home_phone,
                                                dms_consumer.work_phone,
                                                dms_consumer.mobile_phone,
                                                dms_consumer.customer_voice_phone_normal,
                                                dms_consumer.address_id,
                                                dms_consumer.is_in_loyalty_program,
                                                dms_consumer.current_loyalty_points,
                                                dms_consumer.current_state,
                                                dms_consumer.street_address,
                                                dms_consumer.city,
                                                dms_consumer.state_province,
                                                dms_consumer.postal_code,
                                                dms_consumer.current_address,
                                                dms_consumer.current_city,
                                                dms_consumer.current_zip,
                                                dms_consumer.dist,
                                                dms_vehicle.dms_vehicle_id,
                                                dms_vehicle.currently_owned,
                                                dms_vehicle.vehicle_make,
                                                dms_vehicle.vehicle_model,
                                                dms_vehicle.vehicle_year,
                                                dms_vehicle.vin,
                                                dms_vehicle.mileage,
                                                dms_vehicle.blackbook_value,
                                                ((DATEDIFF(CURRENT_DATE, dms_vehicle.amms_last_mileage_date) * dms_vehicle.amms_avg_daily_miles) + dms_vehicle.amms_last_miles) AS est_mileage,
                                                dms_ro.repair_order_id,
                                                dms_ro.open_date,
                                                dms_ro.mileage AS ro_mileage,
                                                dms_ro.op_codes,
                                                dms_ro.advisor_id,
                                                dms_ro.technician_id,
                                                dms_ro.warranty_total,
                                                dms_ro.invoice_total,
                                                dms_ro.invoice_date,
                                                dms_ro.department,
                                                dms_ro.warranty_company AS ro_warranty_company,
                                                COALESCE(temp_ro_warranty_months.matches, 0) AS ro_months_match,
                                                COALESCE(temp_ro_warranty_miles.matches, 0) AS ro_miles_match,
                                                dms_deal.deal_id,
                                                dms_deal.sold_date,
                                                dms_deal.equity_balance,
                                                dms_deal.new_used_type_id,
                                                dms_deal.loop_sales_person_id,
                                                dms_deal.mileage AS deal_mileage,
                                                dms_deal.monthly_payment,
                                                dms_deal.final_payment_date,
                                                dms_deal.term_months,
                                                dms_deal.interest_rate,
                                                dms_deal.warranty_company AS deal_warranty_company,
                                                COALESCE(temp_deal_warranty_months.matches, 0) AS deal_months_match,
                                                COALESCE(temp_deal_warranty_miles.matches, 0) AS deal_miles_match,
                                                dms_warranty_summary.warranty_expiration_miles,
                                                dms_warranty_summary.warranty_expiration_date,
                                                dms_warranty_summary.last_warranty_pay_amount,
                                                dms_warranty_summary.last_warranty_pay_date,
                                                dms_warranty_summary.total_warranty_pay_amount,
                                                dms_deal.purchase_type_id,
                                                dms_deal.back_end_gross,
                                                dms_svappt.appointment_date,
                                                COALESCE(temp_deal_counts.deal_visits, 0) AS dms_sales_visits,
                                                COALESCE(temp_ro_counts.ro_visits, 0) AS dms_service_visits,
                                                COALESCE(temp_pay.customer_pay, 0) AS customer_pay,
                                                COALESCE(temp_pay.warranty_pay, 0) AS warranty_pay
                                    FROM        dms_vehicle
                                    LEFT JOIN   dms_consumer
                                    ON          dms_vehicle.external_customer_id = dms_consumer.external_customer_id
                                    LEFT JOIN   dms_ro
                                    ON          dms_vehicle.dms_vehicle_id = dms_ro.dms_vehicle_id AND
                                                dms_vehicle.external_customer_id = dms_ro.dms_customer_id AND
                                                {$dept_clause}
                                    LEFT JOIN   dms_deal
                                    ON          dms_vehicle.dms_vehicle_id = dms_deal.dms_vehicle_id AND
                                                dms_vehicle.external_customer_id = dms_deal.dms_customer_id
                                    LEFT JOIN   dms_warranty_summary
                                    ON          dms_consumer.external_customer_id = dms_warranty_summary.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = dms_warranty_summary.dms_vehicle_id
                                    LEFT JOIN   dms_email
                                    ON          dms_email.external_customer_id = dms_consumer.external_customer_id
                                    LEFT JOIN   dms_pander
                                    ON          dms_pander.external_customer_id = dms_consumer.external_customer_id AND
                                                (
                                                    dms_pander.pander_mail_yn = 'Y' OR
                                                    dms_pander.pander_email_yn = 'Y' OR
                                                    dms_pander.pander_phone_yn = 'Y' OR
                                                    dms_pander.pander_sms_yn = 'Y'
                                                )
                                    LEFT JOIN   temp_deal_counts
                                    ON          dms_vehicle.external_customer_id = temp_deal_counts.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_deal_counts.dms_vehicle_id
                                    LEFT JOIN   temp_ro_counts
                                    ON          dms_vehicle.external_customer_id = temp_ro_counts.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_ro_counts.dms_vehicle_id
                                    LEFT JOIN   temp_pay
                                    ON          dms_vehicle.external_customer_id = temp_pay.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_pay.dms_vehicle_id
                                    LEFT JOIN   dms_svappt
                                    ON          dms_vehicle.external_customer_id = dms_svappt.external_customer_id AND
                                                dms_vehicle.vin = dms_svappt.vin
                                    LEFT JOIN   temp_ro_warranty_months
                                    ON          dms_vehicle.external_customer_id = temp_ro_warranty_months.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_ro_warranty_months.dms_vehicle_id
                                    LEFT JOIN   temp_ro_warranty_miles
                                    ON          dms_vehicle.external_customer_id = temp_ro_warranty_miles.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_ro_warranty_miles.dms_vehicle_id
                                    LEFT JOIN   temp_deal_warranty_months
                                    ON          dms_vehicle.external_customer_id = temp_deal_warranty_months.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_deal_warranty_months.dms_vehicle_id
                                    LEFT JOIN   temp_deal_warranty_miles
                                    ON          dms_vehicle.external_customer_id = temp_deal_warranty_miles.dms_customer_id AND
                                                dms_vehicle.dms_vehicle_id = temp_deal_warranty_miles.dms_vehicle_id
                                    WHERE       dms_pander.external_customer_id IS NULL OR
                                                (
                                                    dms_pander.pander_mail_yn != 'Y' AND
                                                    dms_pander.pander_email_yn != 'Y' AND
                                                    dms_pander.pander_phone_yn != 'Y' AND
                                                    dms_pander.pander_sms_yn != 'Y'
                                                )
                                    ORDER BY    dms_vehicle.vin,
                                                GREATEST
                                                (
                                                    COALESCE
                                                    (
                                                        CAST(dms_ro.open_date AS DATE),
                                                        CAST(dms_deal.sold_date AS DATE)
                                                    ),
                                                    COALESCE
                                                    (
                                                        CAST(dms_deal.sold_date AS DATE),
                                                        CAST(dms_ro.open_date AS DATE)
                                                    )
                                                )
                                    DESC
                                ) AS dataset
                                GROUP BY external_customer_id, vin
                            ) AS vehiclegroup
                        WHERE is_fake = 0 AND is_deleted = 0";
        $notStatement = array();

        if(count ($this->clauses) > 0)
        {
            $tempstr = "";
            foreach($this->clauses as $item)
            {
                $tempstr .= " AND " . $item;

                $notStatement[] = "NOT ($item)";
            }
            $sql .= $tempstr;
        }

        $notStatement = implode(" OR ", $notStatement);
        //This will only run if a customer id has been set. It reverses the
        //where clause to find any customers who don't fall inside the criteria.
        //Then uses the resulting customer list to create a NOT IN statement.
        //This is used later to create an explicit query that filters out
        //customers with multiple vehicles who have one or more vehicles
        //That fail to meet the export criteria
        //I did my best to keep this code light, so just ignore the
        //unset() calls and the unorthodox way of generating the query
        //
        // 20120425: Change conditional test from checking for customer id to
        // checking for list type of names. We need the customer id to check
        // for trigger mails to exclude.
        if(($this->criteria['explicit_names'] == 'Y') && $notStatement && ($this->criteria["ListType"] == 'names'))
        {
            $this->CDmsd 	= new Dmsd();
            $this->Dms		= $this->CDmsd->GetDmsDbConn($this->CustomerId);
            unset($this->CDmsd);
            memory_get_usage();
            $dept_clause = $this->getDeptClause();
            $query = "
                        SELECT DISTINCT(external_customer_id) FROM
                        (
                            SELECT      dms_consumer.gender,
                                        dms_vehicle.polk_owned_status,
                                        dms_vehicle.polk_owned_date,
                                        dms_consumer.customer_name,
                                        dms_consumer.first_name,
                                        dms_consumer.last_name,
                                        dms_consumer.middle_name,
                                        dms_consumer.external_customer_id,
                                        dms_consumer.is_b2b,
                                        dms_consumer.is_fake,
                                        dms_consumer.is_deleted,
                                        CASE
                                            WHEN dms_email.email != '' THEN dms_email.email
                                            ELSE dms_consumer.customer_email
                                        END as 'customer_email',
                                        dms_consumer.birthday,
                                        dms_consumer.customer_voice_phone,
                                        dms_consumer.customer_text_phone,
                                        dms_consumer.home_phone,
                                        dms_consumer.work_phone,
                                        dms_consumer.mobile_phone,
                                        dms_consumer.customer_voice_phone_normal,
                                        dms_consumer.address_id,
                                        dms_consumer.is_in_loyalty_program,
                                        dms_consumer.current_loyalty_points,
                                        dms_consumer.current_state,
                                        dms_consumer.street_address,
                                        dms_consumer.city,
                                        dms_consumer.state_province,
                                        dms_consumer.postal_code,
                                        dms_consumer.current_address,
                                        dms_consumer.current_city,
                                        dms_consumer.current_zip,
                                        dms_consumer.dist,
                                        dms_vehicle.dms_vehicle_id,
                                        dms_vehicle.currently_owned,
                                        dms_vehicle.vehicle_make,
                                        dms_vehicle.vehicle_model,
                                        dms_vehicle.vehicle_year,
                                        dms_vehicle.vin,
                                        dms_vehicle.mileage,
                                        dms_vehicle.blackbook_value,
                                        ((DATEDIFF(CURRENT_DATE, dms_vehicle.amms_last_mileage_date) * dms_vehicle.amms_avg_daily_miles) + dms_vehicle.amms_last_miles) AS est_mileage,
                                        dms_ro.repair_order_id,
                                        dms_ro.open_date,
                                        dms_ro.mileage AS ro_mileage,
                                        dms_ro.op_codes,
                                        dms_ro.advisor_id,
                                        dms_ro.technician_id,
                                        dms_ro.warranty_total,
                                        dms_ro.invoice_total,
                                        dms_ro.invoice_date,
                                        dms_ro.department,
                                        dms_ro.warranty_company AS ro_warranty_company,
                                        COALESCE(temp_ro_warranty_months.matches, 0) AS ro_months_match,
                                        COALESCE(temp_ro_warranty_miles.matches, 0) AS ro_miles_match,
                                        dms_deal.deal_id,
                                        dms_deal.sold_date,
                                        dms_deal.equity_balance,
                                        dms_deal.new_used_type_id,
                                        dms_deal.loop_sales_person_id,
                                        dms_deal.mileage AS deal_mileage,
                                        dms_deal.monthly_payment,
                                        dms_deal.final_payment_date,
                                        dms_deal.term_months,
                                        dms_deal.warranty_company AS deal_warranty_company,
                                        COALESCE(temp_deal_warranty_months.matches, 0) AS deal_months_match,
                                        COALESCE(temp_deal_warranty_miles.matches, 0) AS deal_miles_match,
                                        dms_warranty_summary.warranty_expiration_miles,
                                        dms_warranty_summary.warranty_expiration_date,
                                        dms_warranty_summary.last_warranty_pay_amount,
                                        dms_warranty_summary.last_warranty_pay_date,
                                        dms_warranty_summary.total_warranty_pay_amount,
                                        dms_deal.purchase_type_id,
                                        dms_deal.back_end_gross,
                                        dms_svappt.appointment_date,
                                        COALESCE(temp_deal_counts.deal_visits, 0) AS dms_sales_visits,
                                        COALESCE(temp_ro_counts.ro_visits, 0) AS dms_service_visits,
                                        COALESCE(customer_pay, 0) AS customer_pay,
                                        COALESCE(warranty_pay, 0) AS warranty_pay
                            FROM        dms_vehicle
                            LEFT JOIN   dms_consumer
                            ON          dms_vehicle.external_customer_id = dms_consumer.external_customer_id
                            LEFT JOIN   dms_ro
                            ON          dms_vehicle.dms_vehicle_id = dms_ro.dms_vehicle_id AND
                                        dms_vehicle.external_customer_id = dms_ro.dms_customer_id AND
                                        {$dept_clause}
                            LEFT JOIN   dms_deal
                            ON          dms_vehicle.dms_vehicle_id = dms_deal.dms_vehicle_id AND
                                        dms_vehicle.external_customer_id = dms_deal.dms_customer_id
                            LEFT JOIN   dms_warranty_summary
                            ON          dms_consumer.external_customer_id = dms_warranty_summary.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = dms_warranty_summary.dms_vehicle_id
                            LEFT JOIN   dms_email
                            ON          dms_email.external_customer_id = dms_consumer.external_customer_id
                            LEFT JOIN   temp_deal_counts
                            ON          dms_vehicle.external_customer_id = temp_deal_counts.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_deal_counts.dms_vehicle_id
                            LEFT JOIN   temp_ro_counts
                            ON          dms_vehicle.external_customer_id = temp_ro_counts.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_ro_counts.dms_vehicle_id
                            LEFT JOIN   temp_pay
                            ON          dms_vehicle.external_customer_id = temp_pay.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_pay.dms_vehicle_id
                            LEFT JOIN   dms_svappt
                            ON          dms_vehicle.vin = dms_svappt.vin AND
                                        dms_vehicle.external_customer_id = dms_svappt.external_customer_id
                            LEFT JOIN   temp_ro_warranty_months
                            ON          dms_vehicle.external_customer_id = temp_ro_warranty_months.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_ro_warranty_months.dms_vehicle_id
                            LEFT JOIN   temp_ro_warranty_miles
                            ON          dms_vehicle.external_customer_id = temp_ro_warranty_miles.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_ro_warranty_miles.dms_vehicle_id
                            LEFT JOIN   temp_deal_warranty_months
                            ON          dms_vehicle.external_customer_id = temp_deal_warranty_months.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_deal_warranty_months.dms_vehicle_id
                            LEFT JOIN   temp_deal_warranty_miles
                            ON          dms_vehicle.external_customer_id = temp_deal_warranty_miles.dms_customer_id AND
                                        dms_vehicle.dms_vehicle_id = temp_deal_warranty_miles.dms_vehicle_id
                        )
                        as BlahdieBlah
                        WHERE		(".$notStatement.")
            ";

            // We must create the temporary tables here, because this function
            // closes the DB connection, which loses the tables.
            $this->createTempTables($this->Dms);
            $results = $this->Dms->executeQuery($query);

            unset($query);
            unset($this->Dms);
            memory_get_usage();

            $arrayCustomers = array();
            while ($results->next())
            {
                $arrayCustomers[] = $results->get('external_customer_id');
            }
            if (!empty($arrayCustomers))
            {
                array_walk($arrayCustomers, "mysql_real_escape_string");
                $sql .= " AND external_customer_id NOT IN ('" . implode("','", $arrayCustomers) . "')";
            }
            memory_get_usage();
        }
        else
        {
            $this->InvalidCustomers = "";
        }

        if($this->warrantyclauses != "")
        {
            $sql .= " AND (" . $this->warrantyclauses . ")";
        }

        if($extraClauses != "")
        {
            $sql .= " AND (" . $extraClauses . ")";
        }

        if($this->criteria["ListType"] == "names")
        {
            $sql = "SELECT * FROM(" . $sql . ") AS custgroup GROUP BY external_customer_id";
        }

        return $sql;
    }

    public function listQuery($extraClauses = "")
    {
        $result = "
                SELECT      *
                FROM        (
                                SELECT      external_customer_id,
                                            dms_vehicle_id,
                                            CAST(open_date AS DATE) AS open_date,
                                            sold_date,
                                            loop_sales_person_id,
                                            advisor_id,
                                            mileage,
                                            customer_voice_phone,
                                            customer_text_phone,
                                            home_phone,
                                            work_phone,
                                            mobile_phone,
                                            customer_voice_phone_normal,
                                            customer_email
                                FROM        ("
                                                .$this->baseQuery($extraClauses).
                                            ") AS listgroup
                            )
                            as finalTable
                ";

        if ($this->criteria["OrderBy"])
        {
            $orderBy = array();
            foreach ($this->criteria["OrderBy"] as $field_name => $direction)
            {
                if ($field_name && $direction)
                {
                    $orderBy[] = "$field_name $direction";
                }
            }
            if ($orderBy)
            {
                $result .= " ORDER BY " . implode(", ", $orderBy);
            }
        }

        if ($this->criteria["Limit"])
        {
            $result .= " LIMIT 0, " . intval($this->criteria["Limit"]);
        }

        return $result;
    }

    public function downloadQuery($extraClauses = "")
    {
        $result = "
                SELECT      external_customer_id,
                            customer_name,
                            first_name,
                            last_name,
                            middle_name,
                            current_city,
                            current_state,
                            city,
                            state_province,
                            vehicle_make,
                            vehicle_model,
                            vehicle_year
                FROM        ("
                                .$this->baseQuery($extraClauses).
                            ") AS listgroup
                ";

        if ($this->criteria["OrderBy"])
        {
            $orderBy = array();
            foreach ($this->criteria["OrderBy"] as $field_name => $direction)
            {
                if ($field_name && $direction)
                {
                    $orderBy[] = "$field_name $direction";
                }
            }
            if ($orderBy)
            {
                $result .= " ORDER BY " . implode(", ", $orderBy);
            }
        }

        if ($this->criteria["Limit"])
        {
            $result .= " LIMIT 0, " . intval($this->criteria["Limit"]);
        }

        return $result;
    }

    public function sonicQuery($extraClauses = "")
    {
        $result = "
                SELECT      address_id,
                            external_customer_id,
                            gender,
                            customer_name,
                            first_name,
                            last_name,
                            middle_name,
                            current_city,
                            current_state,
                            city,
                            state_province,
                            postal_code,
                            current_zip,
                            street_address,
                            current_address,
                            vehicle_make,
                            vehicle_model,
                            vehicle_year,
                            CAST(open_date AS DATE) AS open_date,
                            sold_date,
                            loop_sales_person_id,
                            advisor_id,
                            mileage,
                            vin,
                            work_phone,
                            home_phone,
                            mobile_phone,
                            customer_email,
                            warranty_expiration_date,
                            warranty_expiration_miles,
                            warranty_total,
                            new_used_type_id,
                            purchase_type_id,
                            back_end_gross,
                            last_warranty_pay_amount,
                            last_warranty_pay_date,
                            total_warranty_pay_amount,
                            dms_vehicle_id,
                            invoice_date,
                            invoice_total
                FROM        ("
                                .$this->baseQuery($extraClauses).
                            ") AS listgroup";

        if ($this->criteria["OrderBy"])
        {
            $orderBy = array();
            foreach ($this->criteria["OrderBy"] as $field_name => $direction)
            {
                if ($field_name && $direction)
                {
                    $orderBy[] = "$field_name $direction";
                }
            }
            if ($orderBy)
            {
                $result .= " ORDER BY " . implode(", ", $orderBy);
            }
        }

        if ($this->criteria["Limit"])
        {
            $result .= " LIMIT 0, " . intval($this->criteria["Limit"]);
        }

        return $result;
    }

    public function buildClauses()
    {
        if($this->criteria["EmployeeEx"])
        {
            $arEmpex = "";
            $empexsolddate = "";
            $empexopendate = "";
            if($this->criteria["EmployeeEx"]["ex_sales_service"] == "Sold by")
            {
                if(is_array($this->criteria["EmployeeEx"]["ex_employee"]) && !empty($this->criteria["EmployeeEx"]["ex_employee"]))
                {
                    $ex_employees = array_values($this->criteria["EmployeeEx"]["ex_employee"]);
                    array_walk($ex_employees, "mysql_real_escape_string");
                    $arEmpex[] = "loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "')";
                }
                elseif(is_string($this->criteria["EmployeeEx"]["ex_employee"]) && $this->criteria["EmployeeEx"]["ex_employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $ex_employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeEx"]["ex_employee"]));
                    $arEmpex[] = "loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "')";
                }

                if($this->criteria["EmployeeEx"]["ex_start_date"] && $this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexsolddate = " ( sold_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_start_date"])
                {
                    $empexsolddate = " ( sold_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' OR sold_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexsolddate = " ( sold_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL )";
                }
                else
                {
                    $empexsolddate = "deal_id IS NULL";
                }
                $arEmpex[] = $empexsolddate;
                $empex = implode(" OR ", $arEmpex);
            }
            elseif($this->criteria["EmployeeEx"]["ex_sales_service"] == "Serviced by")
            {
                if(is_array($this->criteria["EmployeeEx"]["ex_employee"]) && !empty($this->criteria["EmployeeEx"]["ex_employee"]))
                {
                    $ex_employees = array_values($this->criteria["EmployeeEx"]["ex_employee"]);
                    array_walk($ex_employees, "mysql_real_escape_string");
                    $arEmpex[] = "(advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeEx"]["ex_employee"]) && $this->criteria["EmployeeEx"]["ex_employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $ex_employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeEx"]["ex_employee"]));
                    $arEmpex[] = "(advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }

                if($this->criteria["EmployeeEx"]["ex_start_date"] && $this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = " ( open_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_start_date"])
                {
                    $empexopendate = " ( open_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' OR open_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = " ( open_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
                }
                else
                {
                    $empexopendate = "repair_order_id IS NULL";
                }
                $arEmpex[] = $empexopendate;
                $empex = implode(" OR ", $arEmpex);
            }
            elseif($this->criteria["EmployeeEx"]["ex_sales_service"] == "Sold or Serviced by")
            {
                if(is_array($this->criteria["EmployeeEx"]["ex_employee"]) && !empty($this->criteria["EmployeeEx"]["ex_employee"]))
                {
                    $ex_employees = array_values($this->criteria["EmployeeEx"]["ex_employee"]);
                    array_walk($ex_employees, "mysql_real_escape_string");
                    $arEmpex[] = "(loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "') AND advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeEx"]["ex_employee"]) && $this->criteria["EmployeeEx"]["ex_employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $ex_employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeEx"]["ex_employee"]));
                    $arEmpex[] = "(loop_sales_person_id NOT IN ('" . implode("','", $ex_employees) . "') AND advisor_id NOT IN ('" . implode("','", $ex_employees) . "') AND technician_id NOT IN ('" . implode("','", $ex_employees) . "'))";
                }

                if($this->criteria["EmployeeEx"]["ex_start_date"] && $this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = " ( open_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
                    $empexsolddate = " ( sold_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_start_date"])
                {
                    $empexopendate = " ( open_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . " 00:00:00' OR open_date IS NULL )";
                    $empexsolddate = " ( sold_date < '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_start_date"]))) . "' OR sold_date IS NULL )";
                }
                elseif($this->criteria["EmployeeEx"]["ex_end_date"])
                {
                    $empexopendate = " ( open_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
                    $empexsolddate = " ( sold_date > '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeEx"]["ex_end_date"]))) . "' OR sold_date IS NULL )";
                }
                else
                {
                    $empexopendate = "repair_order_id IS NULL";
                    $empexsolddate = "deal_id IS NULL";
                }
                $arEmpex[] = $empexopendate;
                $arEmpex[] = $empexsolddate;
                $empex = implode(" OR ", $arEmpex);
            }

            if(!empty($empex))
            {
                $this->clauses[] = "(" . $empex . ")";
            }
        }
        if(!$this->criteria["EmployeeInc"] || $this->criteria["EmployeeInc"]["sales_service"] != $this->criteria["EmployeeEx"]["ex_sales_service"])
        {
            if($empexopendate != "")
            {
                $this->clauses[] = "(" . $empexopendate . ")";
            }
            if($empexsolddate != "")
            {
                $this->clauses[] = "(" . $empexsolddate . ")";
            }
        }
        if($this->criteria["EmployeeInc"])
        {
            $arTemp = array();
            if($this->criteria["EmployeeInc"]["sales_service"] == "Sold by")
            {
                if(is_array($this->criteria["EmployeeInc"]["employee"]) && !empty($this->criteria["EmployeeInc"]["employee"]))
                {
                    $employees = array_values($this->criteria["EmployeeInc"]["employee"]);
                    array_walk($employees, "mysql_real_escape_string");
                    $arTemp[] = "loop_sales_person_id IN ('" . implode("','", $employees) . "')";
                }
                elseif(is_string($this->criteria["EmployeeInc"]["employee"]) && $this->criteria["EmployeeInc"]["employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeInc"]["employee"]));
                    $arTemp[] = "loop_sales_person_id IN ('" . implode("','", $employees) . "')";
                }

                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(sold_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(sold_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                else
                {
                    $arTemp[] = "deal_id IS NOT NULL";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            elseif($this->criteria["EmployeeInc"]["sales_service"] == "Serviced by")
            {
                if(is_array($this->criteria["EmployeeInc"]["employee"]) && !empty($this->criteria["EmployeeInc"]["employee"]))
                {
                    $employees = array_values($this->criteria["EmployeeInc"]["employee"]);
                    array_walk($employees, "mysql_real_escape_string");
                    $arTemp[] =  "(advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeInc"]["employee"]) && $this->criteria["EmployeeInc"]["employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeInc"]["employee"]));
                    $arTemp[] =  "(advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }

                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = " (open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = " (open_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = " (open_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                }
                else
                {
                    $arTemp[] = "repair_order_id IS NOT NULL";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            elseif($this->criteria["EmployeeInc"]["sales_service"] == "Sold or Serviced by")
            {
                if(is_array($this->criteria["EmployeeInc"]["employee"]) && !empty($this->criteria["EmployeeInc"]["employee"]))
                {
                    $employees = array_values($this->criteria["EmployeeInc"]["employee"]);
                    array_walk($employees, "mysql_real_escape_string");
                    $arTemp[] =  "(loop_sales_person_id IN ('" . implode("','", $employees) . "') OR advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }
                elseif(is_string($this->criteria["EmployeeInc"]["employee"]) && $this->criteria["EmployeeInc"]["employee"] != '-1')
                {
                    // Some of these are pipe-separated pairs
                    $employees = explode('|', mysql_real_escape_string($this->criteria["EmployeeInc"]["employee"]));
                    $arTemp[] =  "(loop_sales_person_id IN ('" . implode("','", $employees) . "') OR advisor_id IN ('" . implode("','", $employees) . "') OR technician_id IN ('" . implode("','", $employees) . "'))";
                }

                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59'" .
                                " OR sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = "(open_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00'" .
                                " OR sold_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = "(open_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59'" .
                                " OR sold_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                else
                {
                    $arTemp[] = " (deal_id IS NOT NULL OR repair_order_id IS NOT NULL)";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            elseif($this->criteria["EmployeeInc"]["sales_service"] == "Sold and Serviced by")
            {
                // The salesperson and service advisor will almost certainly not be the same person,
                // so we don't allow the user to select an employee for this option.
                // The GUI will hide this selection in this case.
                if($this->criteria["EmployeeInc"]["start_date"] && $this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = " (open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59'" .
                                " AND sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["start_date"])
                {
                    $arTemp[] = " (open_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . " 00:00:00'" .
                                " AND sold_date >= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["start_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                elseif($this->criteria["EmployeeInc"]["end_date"])
                {
                    $arTemp[] = " (open_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . " 23:59:59'" .
                                " AND sold_date <= '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["EmployeeInc"]["end_date"]))) . "')";
                    if($empexopendate != "")
                    {
                        $arTemp[] = "(" . $empexopendate . ")";
                    }
                    if($empexsolddate != "")
                    {
                        $arTemp[] = "(" . $empexsolddate . ")";
                    }
                }
                else
                {
                    $arTemp[] = " (deal_id IS NOT NULL AND repair_order_id IS NOT NULL)";
                }
                $tempstr = implode(" AND ", $arTemp);
            }
            if(!empty($tempstr))
            {
                $this->clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["VehicleInc"])
        {
            if($this->criteria["VehicleInc"]["make"] && intval($this->criteria["VehicleInc"]["make"][0]) != -1)
            {
                if($this->criteria["VehicleInc"]["make"][0] == 'DEALER_MAKES')
                {
                    if($this->CustomerId)
                    {
                        $Wilma = Propel::getConnection();
                        $customer_id = $this->CustomerId;
                        $query = "SELECT makes FROM customer_extra WHERE customer_id = '$customer_id'";
                        $results = $Wilma->executeQuery($query);
                        while($results->next())
                        {
                            $makes = unserialize($results->get('makes'));
                        }
                        if(empty($makes))
                        {
                            $query = "SELECT store_brands FROM market_evaluation WHERE customer_id = '$customer_id'";
                            $results = $Wilma->executeQuery($query);
                            while($results->next())
                            {
                                $makes = explode("|",trim($results->get('store_brands'),"|"));
                            }
                        }

                        $this->clauses[] = "vehicle_make IN ('".implode("','",$makes)."')";
                    }
                }
                else
                {
                    $tempstr = "";
                    foreach($this->criteria["VehicleInc"]["make"] as $item)
                    {
                        $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                    }
                    $tempstr = rtrim($tempstr,',');
                    $this->clauses[] = "vehicle_make IN (" . $tempstr . ")";
                }
            }

            if($this->criteria["VehicleInc"]["model"] && intval($this->criteria["VehicleInc"]["model"][0]) != -1)
            {
                $tempstr = "";
                foreach($this->criteria["VehicleInc"]["model"] as $item)
                {
                    $tempstr .= "'" . mysql_real_escape_string($item) . "',";
                }
                $tempstr = rtrim($tempstr,',');
                $this->clauses[] = "vehicle_model IN (" . $tempstr . ")";
            }
            if($this->criteria["VehicleInc"]["start_year"])
            {
                if(!is_numeric($this->criteria["VehicleInc"]["start_year"]))
                {
                    $this->criteria["VehicleInc"]["start_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["start_year"]));
                }
                if(!is_numeric($this->criteria["VehicleInc"]["end_year"]))
                {
                    $this->criteria["VehicleInc"]["end_year"] = date("Y", strtotime($this->criteria["VehicleInc"]["end_year"]));
                }
                $this->clauses[] = "vehicle_year BETWEEN " . intval($this->criteria["VehicleInc"]["start_year"]) . " AND " . intval($this->criteria["VehicleInc"]["end_year"]);
            }
        }
        if($this->criteria["VinInc"])
        {
            if($this->criteria["VinInc"]["start_vin"])
            {
                $this->clauses[] = "SUBSTRING(vin,-6) BETWEEN '" . mysql_real_escape_string($this->criteria["VinInc"]["start_vin"]) . "' AND '" . mysql_real_escape_string($this->criteria["VinInc"]["end_vin"]) . "'";
            }
        }
        if($this->criteria["MileageInc"])
        {
            if(isset($this->criteria["MileageInc"]["start_miles"]))
            {
                $this->clauses[] = "mileage BETWEEN " . intval($this->criteria["MileageInc"]["start_miles"]) . " AND " . intval($this->criteria["MileageInc"]["end_miles"]);
            }
        }
        if($this->criteria["StateInc"])
        {
            if($this->criteria["StateInc"]["state"])
            {
                $this->clauses[] = "current_state ='" . mysql_real_escape_string($this->criteria["StateInc"]["state"]) . "'";
            }
        }
        if($this->criteria["OpCodeInc"])
        {
            if($this->criteria["OpCodeInc"]["opcode"])
            {
                $temparr = explode(' ',$this->criteria["OpCodeInc"]["opcode"]);
                $tempstr = "";
                foreach($temparr as $item)
                {
                    $tempstr .= " OR op_codes REGEXP '([|]|[[:<:]])" . mysql_real_escape_string($item) . "([[:>:]]|[|])'";
                }
                $tempstr = ltrim($tempstr,' OR');
                $this->clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["EquityInc"])
        {
            if($this->criteria["EquityInc"]["equity"])
            {
                $temp = "";
                if($this->criteria["EquityInc"]["equitygt"] == "greater")
                {
                    $temp = ">=";
                }
                else
                {
                    $temp = "<=";
                }
                $this->clauses[] = "(equity_balance != 0 AND equity_balance " . $temp . " " . $this->criteria["EquityInc"]["equity"] . ")";
            }
        }
        if($this->criteria["EmailInc"])
        {
            if($this->criteria["EmailInc"] == "valid email")
            {
                $this->clauses[] = "customer_email REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
            else
            {
                $this->clauses[] = "customer_email NOT REGEXP '^[A-Z0-9._%-]+@[A-Z0-9.-]+\.[A-Z]{2,4}'";
            }
        }
        if($this->criteria["AddressInc"])
        {
            if($this->criteria["AddressInc"] == "valid addresses")
            {
                $this->clauses[] = "address_id > 0";
            }
            else
            {
                $this->clauses[] = "address_id < 0";
            }
        }
        if($this->criteria["NewUsedInc"])
        {
            if($this->criteria["NewUsedInc"] == "used")
            {
                $this->clauses[] = "new_used_type_id = 2";
            }
            elseif($this->criteria["NewUsedInc"] == "new")
            {
                $this->clauses[] = "new_used_type_id = 1";
            }
            else
            {
                $this->clauses[] = "new_used_type_id = 3";
            }
        }
        if($this->criteria["LastServEx"])
        {
            $this->clauses[] = " (open_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServEx"]["ex_lastserv_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServEx"]["ex_lastserv_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
        }
        if($this->criteria["LastServInc"])
        {
            $this->clauses[] = " (open_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServInc"]["lastserv_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["LastServInc"]["lastserv_end_date"]))) . " 23:59:59' OR open_date IS NULL )";
        }
        if($this->criteria["WarRepInc"])
        {
            $this->clauses[] = " (warranty_total BETWEEN '" . mysql_real_escape_string($this->criteria["WarRepInc"]["war_rep_inc_start"]) . "' AND '" . mysql_real_escape_string($this->criteria["WarRepInc"]["war_rep_inc_end"]) . "' OR warranty_total IS NULL )";
        }
        if($this->criteria["PurchDateEx"])
        {
            $this->clauses[] = " (sold_date NOT BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateEx"]["ex_purchdate_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateEx"]["ex_purchdate_end_date"]))) . " 23:59:59' OR sold_date IS NULL )";
        }
        if($this->criteria["PurchDateInc"])
        {
            $this->clauses[] = " (sold_date BETWEEN '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateInc"]["purchdate_start_date"]))) . " 00:00:00' AND '" . mysql_real_escape_string(date('Y-m-d',strtotime($this->criteria["PurchDateInc"]["purchdate_end_date"]))) . " 23:59:59' OR sold_date IS NULL )";
        }
        if($this->criteria["PurchaseInc"])
        {
            if($this->criteria["PurchaseInc"] == "lease")
            {
                $this->clauses[] = "purchase_type_id = 2";
            }
            elseif($this->criteria["PurchaseInc"] == "loan")
            {
                $this->clauses[] = "purchase_type_id = 3";
            }
            else
            {
                $this->clauses[] = "purchase_type_id = 1";
            }
        }
        if($this->criteria["WarrantyInc"])
        {
            if($this->criteria["WarrantyInc"] == "under warranty")
            {
                $this->warrantyclauses = "  NOT((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
            else
            {
                $this->warrantyclauses = " ((mileage > warranty_expiration_miles AND warranty_expiration_miles != 0) OR (warranty_expiration_date <= NOW() AND warranty_expiration_date != '1969-12-31' AND warranty_expiration_date != '0000-00-00') OR (warranty_expiration_date = '1969-12-31' AND warranty_expiration_miles = 0) OR (warranty_expiration_date = '0000-00-00' AND warranty_expiration_miles = 0) OR (warranty_expiration_date IS NULL AND warranty_expiration_miles IS NULL))";
            }
/*          if($this->criteria["WarrantyInc"] == "under warranty")
            {
                $this->warrantyclauses = " ((CAST(open_date AS DATE) <= sold_date OR open_date IS NULL) AND ((deal_expiration_miles > deal_mileage AND (deal_expiration IS NULL OR deal_expiration = '' OR deal_expiration = '0000-00-00 00:00:00' OR deal_expiration = '1969-12-31 19:00:00'))OR (deal_expiration > NOW() AND (deal_expiration_miles > deal_mileage OR deal_expiration_miles = 0 OR deal_expiration_miles IS NULL OR deal_expiration_miles = '')))) OR ((CAST(open_date AS DATE) > sold_date OR sold_date IS NULL) AND ((ro_expiration_miles > ro_mileage AND (ro_expiration IS NULL OR ro_expiration = '' OR ro_expiration = '0000-00-00 00:00:00' OR ro_expiration = '1969-12-31 19:00:00')) OR (ro_expiration > NOW() AND (ro_expiration_miles > ro_mileage OR ro_expiration_miles = 0 OR ro_expiration_miles IS NULL OR ro_expiration_miles = ''))))";
            }
            else
            {
                $this->warrantyclauses = " ((CAST(open_date AS DATE) <= sold_date OR open_date IS NULL) AND ((deal_expiration_miles <= deal_mileage AND (deal_expiration_miles != 0)) OR (deal_expiration <= NOW() AND (deal_expiration != ''AND deal_expiration IS NOT NULL AND deal_expiration != '0000-00-00 00:00:00' AND deal_expiration != '1969-12-31 19:00:00')) OR (deal_expiration_miles = 0 AND (deal_expiration = '' OR deal_expiration IS NULL OR deal_expiration = '0000-00-00 00:00:00' OR deal_expiration = '1969-12-31 19:00:00')))) OR ((CAST(open_date AS DATE) > sold_date OR sold_date IS NULL) AND ((ro_expiration_miles <= ro_mileage AND (ro_expiration_miles != 0)) OR (ro_expiration <= NOW() AND (ro_expiration != '' AND ro_expiration IS NOT NULL AND ro_expiration != '0000-00-00 00:00:00' AND ro_expiration != '1969-12-31 19:00:00')) OR (ro_expiration_miles = 0 AND (ro_expiration = '' OR ro_expiration IS NULL OR ro_expiration = '0000-00-00 00:00:00' OR ro_expiration = '1969-12-31 19:00:00'))))";
            }*/
        }
        if($this->criteria["LoyaltyInc"])
        {
            // Check to see they are in the loyalty program
            if (!empty($this->criteria["LoyaltyInc"]["active"]))
            {
                // 0 = ignore; 1 = active; 2 = inactive
                $active = intval($this->criteria["LoyaltyInc"]["active"]) % 2;
                $this->clauses[] = "is_in_loyalty_program = '" . $active . "'";
            }

            // Check how many points they have
            if (!empty($this->criteria["LoyaltyInc"]["start_loyalty"]) || !empty($this->criteria["LoyaltyInc"]["end_loyalty"]))
            {
                $this->clauses[] = "current_loyalty_points BETWEEN " . intval($this->criteria["LoyaltyInc"]["start_loyalty"]) . " AND " . intval($this->criteria["LoyaltyInc"]["end_loyalty"]);
            }
        }
        if($this->criteria["BirthdayInc"])
        {
            if($this->criteria["BirthdayInc"]["start_birth_month"])
            {
                $this->clauses[] = "DAYOFYEAR(birthday) BETWEEN DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["start_birth_day"]),2,'0',STR_PAD_LEFT) . "') AND DAYOFYEAR('2010-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_month"]),2,'0',STR_PAD_LEFT) . "-" . str_pad(mysql_real_escape_string($this->criteria["BirthdayInc"]["end_birth_day"]),2,'0',STR_PAD_LEFT) . "')";
            }
        }
        if($this->criteria["SalesVisitsInc"])
        {
            $this->clauses[] = "dms_sales_visits BETWEEN '" . intval($this->criteria["SalesVisitsInc"]["min_sales_visits"]) . "' AND '" . intval($this->criteria["SalesVisitsInc"]["max_sales_visits"]) . "'";
        }
        if($this->criteria["ServiceVisitsInc"])
        {
            $this->clauses[] = "dms_service_visits BETWEEN '" . intval($this->criteria["ServiceVisitsInc"]["min_service_visits"]) . "' AND '" . intval($this->criteria["ServiceVisitsInc"]["max_service_visits"]) . "'";
        }
        if($this->criteria["BlackBookInc"])
        {
            $this->clauses[] = "blackbook_value BETWEEN " . floatval($this->criteria["BlackBookInc"]["blackbookinc_min"]) . " AND " . floatval($this->criteria["BlackBookInc"]["blackbookinc_max"]);
        }
        if($this->criteria["MonthlyPmtInc"])
        {
            $this->clauses[] = "monthly_payment BETWEEN " . floatval($this->criteria["MonthlyPmtInc"]["monthlypmtinc_min"]) . " AND " . floatval($this->criteria["MonthlyPmtInc"]["monthlypmtinc_max"]);
        }
        if($this->criteria["LoanBalanceInc"])
        {
            if($this->criteria["LoanBalanceInc"]["loanbalanceinc_min"] != 'all')
            {
                $loanEndsMin = date("Y-m-d 00:00:00", strtotime("+".$this->criteria["LoanBalanceInc"]["loanbalanceinc_min"]." months"));
                $this->clauses[] = "final_payment_date >= '".$loanEndsMin."'";
            }
            if($this->criteria["LoanBalanceInc"]["loanbalanceinc_max"] != 'all')
            {
                $loanEndsMax = date("Y-m-d 00:00:00", strtotime("+".$this->criteria["LoanBalanceInc"]["loanbalanceinc_max"]." months"));
                $this->clauses[] = "final_payment_date <= '".$loanEndsMax."'";
            }
        }
        if($this->criteria["TermMonthsInc"])
        {
            if ($this->criteria["TermMonthsInc"]["termmonthsinc_min"] != 'all')
            {
                $this->clauses[] = "term_months >= '".$this->criteria["TermMonthsInc"]["termmonthsinc_min"]."'";
            }
            if ($this->criteria["TermMonthsInc"]["termmonthsinc_max"] != 'all')
            {
                $this->clauses[] = "term_months <= '".$this->criteria["TermMonthsInc"]["termmonthsinc_max"]."'";
            }
        }
        if($this->criteria["CampaignEx"])
        {
            if($this->criteria["CampaignEx"]["campaigns"])
            {
                $temp = sfContext::getInstance()->getDatabaseManager();
                $temp->shutdown();
                $temp->initialize();
                $conn = sfContext::getInstance()->getDatabaseConnection('dms_main');

                $sql = "SELECT list_data FROM dms_export where dms_export_id in ('". implode("','", $this->criteria["CampaignEx"]["campaigns"]) . "')";
                $this->result = $conn->executeQuery($sql);

                $external_customer_ids = array();
                // for each DMS export
                while ($this->result->next())
                {
                    $list_data = unserialize($this->result->getString('list_data'));
                    foreach ($list_data as $key => $value)
                    {
                        $external_customer_ids[] = $key;
                    }
                }
                $this->exclude_external_customer_ids = "'" . implode("','", array_unique($external_customer_ids)) . "'";
                $this->clauses[] = "external_customer_id NOT IN (" . $this->exclude_external_customer_ids . ")";
            }
            else
            {
                $this->exclude_external_customer_ids = '';
            }
        }
        if($this->criteria["TrigEx"])
        {
            $customer_id = $this->CustomerId;
            $sql = "SELECT address.address_id FROM mailing JOIN mail_history ON mailing.mailing_id = mail_history.mailing_id JOIN address ON address.address_id = mail_history.address_id WHERE mailing.work_order_number LIKE 'trm%' AND DATE(mailing.mail_date) BETWEEN '" . date('Y-m-d',strtotime($this->criteria["TrigEx"]["trigex_start_date"])) . "' AND '" . date('Y-m-d',strtotime($this->criteria["TrigEx"]["trigex_end_date"])) . "' AND mailing.customer_id = '$customer_id' GROUP BY address.address_id ORDER BY address.address_id";

            $conn = Propel::GetConnection();
            $sqlconn = $conn->getResource();

            $this->result = mysql_query($sql,$sqlconn) or die($sql . "\r\n" . mysql_error());

            $addids = array();
            while($row = mysql_fetch_array($this->result)) $addids[] = $row["address_id"];
            if (count($addids) > 0)
            {
                $addids = implode(',', array_unique($addids));
                $this->exclude_addids = $addids;
                $this->clauses[] = "address_id NOT IN (".$addids.")";
            }
            else
            {
                $this->exclude_addids = '';
            }
        }
        if($this->criteria["OpCodeEx"])
        {
            if($this->criteria["OpCodeEx"]["exopcode"])
            {
                $temparr = explode(' ',$this->criteria["OpCodeEx"]["exopcode"]);
                $tempstr = "";
                foreach($temparr as $item)
                //[[:<:]] = start of word [[:>:]] = end of word Mysql REGEXP metachars
                $tempstr .= " AND op_codes NOT REGEXP '([|]|[[:<:]])" . mysql_real_escape_string($item) . "([[:>:]]|[|])'";
                $tempstr = ltrim($tempstr,' AND');
                $this->clauses[] = "(" . $tempstr . ")";
            }
        }
        if($this->criteria["extraClauses"])
        {
                $this->clauses[] = "(" . $this->criteria["extraClauses"] . ")";
        }

        if($this->criteria["StuInc"])
        {
            if($this->criteria["StuInc"] == 1)
            {
                $this->clauses[] = "polk_owned_status <> 4";
            }
            if($this->criteria["StuInc"] == 2)
            {
                $this->clauses[] = "polk_owned_status = 4";
            }
        }

        if($this->criteria["DistInc"])
        {
            $this->clauses[] = "(dist <= '".intval($this->criteria["DistInc"])."' AND dist >= 0)";
        }

        if($this->criteria["b2b"])
        {
            $temp = intval($this->criteria["b2b"]);
            if($temp == 1)
            {
                $this->clauses[] = "is_b2b = 0";
            }
            elseif($temp == 2)
            {
                $this->clauses[] = "is_b2b = 1";
            }

            if($temp == 1)
            {
                $this->clauses[] = $this->GetBusinessFilterClause();
            }
        }

        if($this->criteria["PhoneInc"])
        {
            $regex = " REGEXP '^[1]?[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{3}[() -]*[[:digit:]]{4}$'";
            if(!empty($this->criteria["PhoneInc"]["any_phone_valid"]))
            {
                $temp = array();
                $temp[] = "customer_voice_phone" . $regex;
                $temp[] = "customer_text_phone" . $regex;
                $temp[] = "home_phone" . $regex;
                $temp[] = "work_phone" . $regex;
                $temp[] = "mobile_phone" . $regex;
                $temp[] = "customer_voice_phone_normal" . $regex;
                $this->clauses[] = "( " . implode(" OR ", $temp) . " )";
            }
            else
            {
                $temp = array();
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_valid"]))
                {
                    $temp[] = "customer_voice_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_text_phone_valid"]))
                {
                    $temp[] = "customer_text_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["home_phone_valid"]))
                {
                    $temp[] = "home_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["work_phone_valid"]))
                {
                    $temp[] = "work_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["mobile_phone_valid"]))
                {
                    $temp[] = "mobile_phone" . $regex;
                }
                if(!empty($this->criteria["PhoneInc"]["customer_voice_phone_normal_valid"]))
                {
                    $temp[] = "customer_voice_phone_normal" . $regex;
                }
                if (!empty($temp))
                {
                    $this->clauses[] = "( " . implode(" OR ", $temp) . " )";
                }
            }
        }

        if($this->criteria["LoanRateInc"])
        {
            if($this->criteria["LoanRateInc"]["loanrateinc_min"])
            {
                $this->clauses[] = "interest_rate >= '" . $this->criteria["LoanRateInc"]["loanrateinc_min"] . "'";
            }
            if($this->criteria["LoanRateInc"]["loanrateinc_max"])
            {
                $this->clauses[] = "interest_rate <= '" . $this->criteria["LoanRateInc"]["loanrateinc_max"] . "'";
            }
        }

        if($this->criteria["SvApptInc"])
        {
            if($this->criteria["SvApptInc"]["svappt_min"])
            {
                $this->clauses[] = "appointment_date >= '" . $this->criteria["SvApptInc"]["svappt_min"] . "'";
            }
            if($this->criteria["SvApptInc"]["svappt_max"])
            {
                $this->clauses[] = "appointment_date <= '" . $this->criteria["SvApptInc"]["svappt_max"] . "'";
            }
        }

        if ($this->criteria["ZipCodeInc"])
        {
            if (!empty($this->criteria["ZipCodeInc"]["zipcodes"]))
            {
                $this->clauses[] = "SUBSTRING(current_zip,1,5) in ('" . implode("','", $this->criteria["ZipCodeInc"]["zipcodes"]) . "')";
            }
        }

        if ($this->criteria["NoServiceInc"])
        {
            if (!empty($this->criteria["NoServiceInc"]["days"]))
            {
                if ($this->criteria["ListType"] == 'names')
                {
                    $this->clauses[] = "external_customer_id NOT IN ( SELECT last_service_id FROM temp_last_service )";
                }
                else
                {
                    $this->clauses[] = "CONCAT(external_customer_id,'_',dms_vehicle_id) NOT IN ( SELECT last_service_id FROM temp_last_service )";
                }
            }
        }

        if ($this->criteria["ServiceDateInc"] && $this->criteria["ServiceDateInc"]["min_date"] && $this->criteria["ServiceDateInc"]["max_date"])
        {
            $this->clauses[] = "open_date BETWEEN '" . $this->criteria["ServiceDateInc"]["min_date"] . " 00:00:00' AND '" . $this->criteria["ServiceDateInc"]["max_date"] . " 23:59:59'";

            if ($this->criteria["ServiceDateInc"]["warranty_pay_min"] || $this->criteria["ServiceDateInc"]["warranty_pay_max"])
            {
                $this->clauses[] = "warranty_pay BETWEEN '" . floatval($this->criteria["ServiceDateInc"]["warranty_pay_min"]) . "' AND '" . floatval($this->criteria["ServiceDateInc"]["warranty_pay_max"]) . "'";
            }

            if ($this->criteria["ServiceDateInc"]["customer_pay_min"] || $this->criteria["ServiceDateInc"]["customer_pay_max"])
            {
                $this->clauses[] = "customer_pay BETWEEN '" . floatval($this->criteria["ServiceDateInc"]["customer_pay_min"]) . "' AND '" . floatval($this->criteria["ServiceDateInc"]["customer_pay_max"]) . "'";
            }
        }

        if ($this->criteria["DepartmentInc"])
        {
            if (!empty($this->criteria["DepartmentInc"]["departments"]))
            {
                $this->clauses[] = "department in ('" . implode("','", $this->criteria["DepartmentInc"]["departments"]) . "')";
            }
        }

        if ($this->criteria["EstMileageInc"])
        {
            if (!empty($this->criteria["EstMileageInc"]["est_mileage_min"]) && !empty($this->criteria["EstMileageInc"]["est_mileage_max"]))
            {
                $this->clauses[] = "est_mileage BETWEEN '" . $this->criteria["EstMileageInc"]["est_mileage_min"] . "' AND '" . $this->criteria["EstMileageInc"]["est_mileage_max"] . "'";
            }
        }

        if ($this->criteria["ExtWarrantyInc"])
        {
            $deal_clauses = array();

            if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_company"]))
            {
                array_walk($this->criteria["ExtWarrantyInc"]["deal_warranty_company"], "mysql_real_escape_string");
                $temp_list = implode("','", $this->criteria["ExtWarrantyInc"]["deal_warranty_company"]);
                if (!empty($temp_list))
                {
                    $deal_clauses[] = "deal_warranty_company IN ('" . $temp_list . "')";
                }
                else
                {
                    $deal_clauses[] = "1";
                }
            }

            // If at least one mileage or month value is entered
            if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_max"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_max"]))
            {
                if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_min"]) ||
                    !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_months_remaining_max"]))
                {
                    $deal_clauses[] = "deal_months_match = 1";
                }
                else
                {
                    $deal_clauses[] = "0";
                }

                if (!empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_min"]) ||
                    !empty($this->criteria["ExtWarrantyInc"]["deal_warranty_miles_remaining_max"]))
                {
                    $deal_clauses[] = "deal_miles_match = 1";
                }
                else
                {
                    $deal_clauses[] = "0";
                }
            }

            switch (count($deal_clauses))
            {
                case 1:
                    $str_deal = $deal_clauses[0];
                    break;

                case 2:
                    $str_deal = "(" . $deal_clauses[0] . " OR " . $deal_clauses[1] . ")";
                    break;

                case 3:
                    $str_deal = $deal_clauses[0] . " AND (" . $deal_clauses[1] . " OR " . $deal_clauses[2] . ")";
                    break;

                case 0:
                    $str_deal = "";
                default:
            }

            $ro_clauses = array();

            if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_company"]))
            {
                array_walk($this->criteria["ExtWarrantyInc"]["ro_warranty_company"], "mysql_real_escape_string");
                $temp_list = implode("','", $this->criteria["ExtWarrantyInc"]["ro_warranty_company"]);
                if (!empty($temp_list))
                {
                    $ro_clauses[] = "ro_warranty_company IN ('" . $temp_list . "')";
                }
                else
                {
                    $ro_clauses[] = "1";
                }
            }

            // If at least one mileage or month value is entered
            if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_max"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_min"]) ||
                !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_max"]))
            {
                if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_min"]) ||
                    !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_months_remaining_max"]))
                {
                    $ro_clauses[] = "ro_months_match = 1";
                }
                else
                {
                    $ro_clauses[] = "0";
                }

                if (!empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_min"]) ||
                    !empty($this->criteria["ExtWarrantyInc"]["ro_warranty_miles_remaining_max"]))
                {
                    $ro_clauses[] = "ro_miles_match = 1";
                }
                else
                {
                    $ro_clauses[] = "0";
                }
            }

            switch (count($ro_clauses))
            {
                case 1:
                    $str_ro = $ro_clauses[0];
                    break;

                case 2:
                    $str_ro = "(" . $ro_clauses[0] . " OR " . $ro_clauses[1] . ")";
                    break;

                case 3:
                    $str_ro = $ro_clauses[0] . " AND (" . $ro_clauses[1] . " OR " . $ro_clauses[2] . ")";
                    break;

                case 0:
                    $str_ro = "";
                default:
            }

            if (!empty($str_deal) && !empty($str_ro) && ($this->criteria["ExtWarrantyInc"]["deal_join_ro"] == "OR"))
            {
                if ($str_deal === "1")
                {
                    $this->clauses[] = $str_ro;
                }
                elseif ($str_ro === "1")
                {
                    $this->clauses[] = $str_deal;
                }
                else
                {
                    $this->clauses[] = "((" . $str_deal . ") OR (" . $str_ro . "))";
                }
            }
            else
            {
                if (!empty($str_deal))
                {
                    $this->clauses[] = $str_deal;
                }
                if (!empty($str_ro))
                {
                    $this->clauses[] = $str_ro;
                }
            }
        }
    }
}