<?php
 namespace App\Utils\Db\ManageRecords; class Insert extends InsertUpdateAbstract { private $mainTableName; private $mainTableAlias; private $groupByColumns = []; private $orderByColumn = ''; private $orderBySort; public function __construct($populatingTableName) { if (!$populatingTableName) { throw new \Exception('You must set the populating table name.'); } $this->populatingTableName = $populatingTableName; } public function setMainTable($mainTableName, $mainTableAlias) { $this->mainTableName = $mainTableName; $this->mainTableAlias = $mainTableAlias; } protected function getSQL() :array { $bindVars = []; $sql = "INSERT INTO `{$this->populatingTableName}`"; $sql .= ' ( ' . implode(', ', array_keys($this->sqlData['columns'])) . ' )'; $sql .= " SELECT"; $sql .= ' ' . implode(', ', array_values($this->sqlData['columns'])); $sql .= " FROM ".$this->getSQL_from(); $whereSQL = $this->getSQL_where(); if ($whereSQL['sql']) { $sql .= " WHERE ".$whereSQL['sql']; $bindVars = $whereSQL['bindVars']; } $groupBySql = $this->getSQL_groupBy(); if ($groupBySql){ $sql .= $groupBySql; } $orderBySql = $this->getSQL_orderBy(); if ($orderBySql){ $sql .= $orderBySql; } return [ 'sql'=>$sql, 'bindVars'=>$bindVars ]; } private function getSQL_from() { $return = "`{$this->mainTableName}` AS {$this->mainTableAlias}"; foreach($this->sqlData['tables'] as $tbl=>$nada) { if ($tbl!=$this->mainTableAlias) { if ($tbl and !$this->joinTablesList[$tbl]) { throw new \Exception("Invalid table alias joined: `{$tbl}`"); } $tblDet = $this->joinTablesList[$tbl]; $return .= " {$tblDet['joinType']} JOIN `{$tblDet['table']}` AS {$tbl} ON ({$tblDet['foreignKeyRef']})"; } } return $return; } public function addGroupBy(Array $groupByColumns) { if (!$groupByColumns) { throw new \Exception("At least a column is needed for grouping."); } $this->groupByColumns = $groupByColumns; } private function getSQL_groupBy() { if (!$this->groupByColumns) { return null; } return " GROUP BY ".implode(',', $this->groupByColumns); } public function addOrderBy($orderByColumn, $sort) { if (!$orderByColumn) { throw new \Exception("At least a column is needed for ordering."); } if (!in_array($sort, ['ASC', 'DESC'])) { throw new \Exception("The sorting order must be either ASC or DESC."); } $this->orderByColumn = $orderByColumn; $this->orderBySort = $sort; } private function getSQL_orderBy() { if (!$this->orderByColumn or !$this->orderBySort) { return null; } return " ORDER BY ".$this->orderByColumn." ".$this->orderBySort; } }