/**
* Displays homepage.
*
* @return mixed
*/
public function actionIndex()
{
$dataProvider = new ActiveDataProvider(['query' => Firms::find()]);
$dataProviderPhones = new ActiveDataProvider(['query' => Phones::find()]);
$sql = "SELECT id, Name, p.Phone as pPhone FROM " . Firms::tableName() . " LEFT JOIN " . Phones::tableName() . " as p ON id = p.FirmID " . " GROUP BY id";
$query = Firms::findBySql($sql);
$dataProvider1 = new ActiveDataProvider(['query' => $query]);
$sql = "SELECT id, Name, p.Phone as pPhone FROM " . Firms::tableName() . " LEFT JOIN " . Phones::tableName() . " as p ON id = p.FirmID " . " GROUP BY id HAVING count(p.phone_id) = '0'";
$query = Firms::findBySql($sql);
$dataProvider2 = new ActiveDataProvider(['query' => $query]);
$sql = "SELECT id, Name, p.Phone as pPhone FROM " . Firms::tableName() . " LEFT JOIN " . Phones::tableName() . " as p ON id = p.FirmID " . " GROUP BY id HAVING count(p.phone_id) >= '2'";
$query = Firms::findBySql($sql);
$dataProvider3 = new ActiveDataProvider(['query' => $query]);
$sql = "SELECT id, Name, p.Phone as pPhone FROM " . Firms::tableName() . " LEFT JOIN " . Phones::tableName() . " as p ON id = p.FirmID " . " GROUP BY id HAVING count(p.phone_id) < '2'";
$query = Firms::findBySql($sql);
$dataProvider4 = new ActiveDataProvider(['query' => $query]);
$sql = "SELECT id, Name, p.Phone as pPhone FROM " . Firms::tableName() . " LEFT JOIN " . Phones::tableName() . " as p ON id = p.FirmID " . " GROUP BY id HAVING count(p.phone_id) = (SELECT MAX( cpid) AS mcpid\n FROM (\n SELECT count(FirmID) AS cpid\n FROM " . Firms::tableName() . "\n LEFT JOIN " . Phones::tableName() . " ON id = FirmID\n GROUP BY FirmID\n ) AS cphone)";
$query = Firms::findBySql($sql);
$dataProvider5 = new ActiveDataProvider(['query' => $query]);
return $this->render('index', ['dataProvider' => $dataProvider, 'dataProviderPhones' => $dataProviderPhones, 'dataProvider1' => $dataProvider1, 'dataProvider2' => $dataProvider2, 'dataProvider3' => $dataProvider3, 'dataProvider4' => $dataProvider4, 'dataProvider5' => $dataProvider5]);
}
