/**
* @param $params
* @return Where
* @throws \Exception
*/
private function constructWhereForItemSearchForDatatable($params)
{
$where = new Where();
$itemTable = DbTables::TBL_EXPENSE_ITEM;
if (!empty($params['item-search-supplier'])) {
$where->equalTo("{$itemTable}.account_id", $params['item-search-supplier']);
}
if (!empty($params['creator_id'])) {
$where->equalTo("{$itemTable}.creator_id", $params['creator_id']);
}
if (!empty($params['item-search-creation-date'])) {
list($creationFrom, $creationTo) = explode(' - ', $params['item-search-creation-date']);
$creationFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationFrom));
$creationTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($creationTo));
$where->expression("date({$itemTable}.date_created) BETWEEN ? AND ?", [$creationFrom, $creationTo]);
}
if (!empty($params['item-search-reference'])) {
$where->like("{$itemTable}.account_reference", "%{$params['item-search-reference']}%");
}
if ($params['item-search-amount'] !== '') {
$where->equalTo("{$itemTable}.amount", $params['item-search-amount']);
}
if (!empty($params['item-search-period'])) {
list($periodFrom, $periodTo) = explode(' - ', $params['item-search-period']);
$periodFrom = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodFrom));
$periodTo = date(Constants::DATABASE_DATE_FORMAT, strtotime($periodTo));
$whereP = new Where();
$whereP->between("{$itemTable}.period_from", $periodFrom, $periodTo);
$whereP->or;
$whereP->between("{$itemTable}.period_to", $periodFrom, $periodTo);
$where->andPredicate($whereP);
}
if (!empty($params['item-search-category'])) {
list($categoryId, $categoryType) = explode('_', $params['item-search-category']);
// Conventional: 1 - category, 2 - sub category
if ($categoryType == 1) {
$where->equalTo("sub_category.category_id", $categoryId);
$where->equalTo("sub_category.category_id", $categoryId);
} elseif ($categoryType == 2) {
$where->equalTo("{$itemTable}.sub_category_id", $categoryId);
} else {
throw new \Exception('Invalid category type.');
}
}
if (!empty($params['item-search-cost-center'])) {
list($costCenterType, $costCenterId) = explode('_', $params['item-search-cost-center']);
// Conventional: 1 - apartment, 2 - office section
if (in_array($costCenterType, [1, 2])) {
$where->equalTo("cost.cost_center_id", $costCenterId);
$where->equalTo("cost.cost_center_type", $costCenterType);
} else {
throw new \Exception('Invalid cost center type.');
}
}
return $where;
}