public function getAll()
{
$db = self::$_msql;
$this->whereAndLimit();
$limit = $this->pagination ? $this->_limit : '';
$join = '';
$searchFields = $this->searchFields;
if ($searchFields['country_id'] !== null || $searchFields['stext'] !== null) {
$join .= ' LEFT JOIN `addresses` as a USING (`address_id`)';
}
if ($searchFields['stext'] !== null) {
$join .= ' LEFT JOIN `customers` as cus ON p.`customer_id` = cus.`customer_id`';
}
$sql = "SELECT SQL_CALC_FOUND_ROWS p.`prospect_id`\n FROM `prospects` as p\n {$join}\n WHERE ?p\n ORDER BY p.`prospect_id` DESC\n {$limit}";
$results = $db->getCol($sql, $this->where);
if (!$results) {
$this->pagination->countItems = 0;
return array();
}
$countRows = $db->getCol("SELECT FOUND_ROWS()");
$this->pagination->countItems = isset($countRows[0]) ? $countRows[0] : 0;
$sql = "SELECT\n p.`prospect_id`,\n p.`created`,\n p.`aff_id`,\n\t\t\t\t\taf.`aff_name`,\n p.`ip`,\n cus.`email`,\n a.`phone`,\n a.`fname`,\n a.`lname`,\n a.`address1`,\n a.`address2`,\n a.`city`,\n a.`state_id`,\n a.`country_id`,\n a.`zip`,\n c.`currency_id`,\n c.`url` as campaign,\n c.`campaign_name`\n FROM `prospects` as p\n\t\t\t\tLEFT JOIN `customers` as cus USING (`customer_id`)\n LEFT JOIN `campaigns` as c ON c.`campaign_id` = p.`campaign_id`\n LEFT JOIN `addresses` as a USING (`address_id`)\n\t\t\t\tLEFT JOIN `affiliates` as af using (`aff_id`)\n WHERE p.`prospect_id` IN(?a)\n ORDER BY p.`prospect_id` DESC";
$prospects = $db->getAll($sql, $results);
$models = array();
foreach ($prospects as $prospect) {
$prospectModel = new Prospect();
$prospectModel->fillFromEnterArray($prospect);
$models[] = $prospectModel;
unset($prospectModel);
}
return $models;
/*
$sql = "SELECT
p.`prospect_id`,
p.`created`,
p.`aff_id`,
af.`aff_name`,
p.`ip`,
cus.`email`,
a.`phone`,
a.`fname`,
a.`lname`,
a.`address1`,
a.`address2`,
a.`city`,
a.`state_id`,
a.`country_id`,
a.`zip`,
c.`currency_id`,
c.`url` as campaign,
c.`campaign_name`
FROM `prospects` as p
LEFT JOIN `customers` as cus USING (`customer_id`)
LEFT JOIN `campaigns` as c ON c.`campaign_id` = p.`campaign_id`
LEFT JOIN `addresses` as a USING (`address_id`)
LEFT JOIN `affiliates` as af using (`aff_id`)
WHERE ?p
ORDER BY p.`prospect_id` DESC";
$sqlParse = $db->parse($sql, $this->where);
return $this->getSqlParse($sqlParse);
*/
}
