public function actionSearchChemicals(array $criteria)
{
$db = a('chemical')->db();
$shChemical = new \Gini\Those\SQLHelper('chemical');
$where = [];
if (isset($criteria['keyword'])) {
$orWhere = [$shChemical->whose('cas_no')->contains($criteria['keyword'])->fragment(), $shChemical->whose('name')->contains($criteria['keyword'])->fragment()];
$where[] = '(' . implode(' OR ', $orWhere) . ')';
}
$shType = new \Gini\Those\SQLHelper('chemical/type');
if (isset($criteria['type'])) {
if (is_array($criteria['type'])) {
$where[] = $shType->whose('name')->isIn($criteria['type'])->fragment();
} else {
$where[] = $shType->whose('name')->is($criteria['type'])->fragment();
}
}
if (count($where) > 0) {
$whereSQL = ' WHERE ' . implode(' AND ', $where);
}
$fromSQL = strtr('FROM :tableChemical AS :aliasChemical' . ' LEFT JOIN :tableType AS :aliasType ' . ' ON :aliasType."cas_no"=:aliasChemical."cas_no"' . $whereSQL, [':tableChemical' => $shChemical->table(), ':aliasChemical' => $shChemical->tableAlias(), ':tableType' => $shType->table(), ':aliasType' => $shType->tableAlias()]);
$countSQL = strtr('SELECT COUNT(DISTINCT :aliasChemical."id") ', [':aliasChemical' => $shChemical->tableAlias()]) . $fromSQL;
$SQL = strtr('SELECT DISTINCT :aliasChemical."id" ', [':aliasChemical' => $shChemical->tableAlias()]) . $fromSQL;
$count = $db->value($countSQL);
$token = \Gini\Session::tempToken();
$_SESSION[$token] = ['SQL' => $SQL];
return ['token' => $token, 'count' => $count];
}
