php_assessment_1/query-service.php

<?php
/*Powered By: Manaknightdigital Inc. https://manaknightdigital.com/ Year: 2021*/
/**
 * Query Service
 * @copyright 2021 Manaknightdigital Inc.
 * @link https://manaknightdigital.com
 * @license Proprietary Software licensing
 * @author Samyam kafle <samyam1kafle@gmail.com>
 *
 */
class QueryService
{
    public function create_where($type_array, $fields=[], $operators=[], $field_values=[])
    {
        $where = [];
        $model_fields = array_keys($type_array);
         
        if (count($fields) != count($operators) || count($operators) != count($field_values))
        {
            return $where;
        }

        for ($i=0; $i < count($fields); $i++)
        {
            if (in_array($fields[$i], $model_fields))
            {
                $type = $type_array[$fields[$i]];
                $single_field_values = $field_values[$i];

                switch( $single_field_values )
                {
                    case 'integer':
                        $single_field_values = filter_var($single_field_values, FILTER_SANITIZE_NUMBER_INT );
                        break;
                    case 'string':
                    case 'date':
                    case 'datetime':
                        if ($operators[$i] == 'LIKE')
                        {
                            $single_field_values = "'%" . filter_var( $single_field_values, FILTER_SANITIZE_STRING ) . "%'";
                        }
                        else
                        {
                            $single_field_values = "'" . filter_var( $single_field_values, FILTER_SANITIZE_STRING ) . "'";
                        }
                        break;
                    case 'float':
                        $single_field_values = filter_var( $single_field_values, FILTER_SANITIZE_NUMBER_FLOAT );
                        break;
                }

                switch ($operators[$i])
                {
                    case 'EQUAL':
                        $where[] = " `{$fields[$i]}` = '{$single_field_values}' ";
                        break;
                    case 'NOT_EQUAL':
                        $where[] = "{ `$fields[$i]}` != '{$single_field_values}' ";
                        break;
                    case 'GREATER_THAN':
                        if ($type == 'integer' || $type == 'float')
                        {
                            $where[] = " `{$fields[$i]}` > {$single_field_values}";
                        }
                        break;
                    case 'GREATER_THAN_EQUAL':
                        if ($type == 'integer' || $type == 'float')
                        {
                            $where[] = " `{$fields[$i]}` >= {$single_field_values}";
                        }
                        break;
                    case 'LESS_THAN':
                        if ($type == 'integer' || $type == 'float')
                        {
                            $where[] = " `{$fields[$i]}` < {$single_field_values}";
                        }
                        break;
                    case 'LESS_THAN_EQUAL':
                        if ($type == 'integer' || $type == 'float')
                        {
                            $where[] = " `{$fields[$i]}` <= {$single_field_values}";
                        }
                        break;
                    case 'LIKE':
                        if ($type != 'integer' && $type != 'float' && $type != 'date' && $type != 'datetime')
                        {
                            $where[] = " `{$fields[$i]}` LIKE {$single_field_values}";
                        }
                        break;
                    //TODO: IN Operator
                }
            }
        }

        return $where;
    }

    public function create_join_query ($where_query, $column_fields, $join_tables, $join_field, $fields, $operators, $values, $page, $per_page, $sort, $direction)
    {
        $select = [];

        $from = "FROM {$join_tables['a']} a ";
        $from_list = [];
        $join = [];

        foreach ($join_tables as $key => $value)
        {
            $from_list[] = " $value $key";
            if ($key != 'a')
            {
                $join[] = " INNER JOIN $value $key ON a.id = {$key}.{$join_field} ";
            }
        }

        foreach ($column_fields as $key => $value)
        {
            $select[] = " $value ";
        }

        $from = $from . implode (' ', $join);
        $sql = 'SELECT ' . implode(',', $select) . $from;
        $total_sql = 'SELECT count(*) as total ' . $from;

        if ($sort)
        {
            $sql .= " ORDER BY {$sort} {$direction}";
        }

        if ($per_page && $page)
        {
            $offset = ($page - 1) * $per_page;
            $sql .= " LIMIT $per_page, $offset";
        }

        return [
            'total' => $total_sql,
            'query' => $sql
        ];
    }

    public function perform_join ($model, $query, $page, $per_page)
    {
        $total = $model->raw_query($query['total']);
        $list = $model->raw_query($query['query']);
        $last_id = $list[array_key_last($list)]['id'];
        return [
            'total' => $total->total,
            'num_page' => ceil($total->total / $per_page),
            'page' => $page,
            'list' => $list,
            'id' => $last_id
        ];
    }
}