<?php

class Route {

    protected $info = array();
    private $db;
    private $year_id;
    private $date_time;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->logged_id = smc_session()['user_logged_id'];
        $this->date_time = date('Y-m-d H:i:s');
    }

    public function setData($data) {
        $this->info = $data;
        return $this;
    }

    public function submitRoute() {
        $route_id = $this->info['route_id'];
        $this->info['route_color'] = !empty($this->info['route_color']) ? $this->info['route_color'] : NULL;
        if ($route_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_route`(`route_name`, `route_color`) VALUES (?,?)', [&$this->info['route_name'], $this->info['route_color']]);
        } else {
            $this->db->prepare_Stmt('update `tbl_route` set `route_name` = ?, `route_color` = ? where route_id = ?', [&$this->info['route_name'], $this->info['route_color'],
                &$route_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteRoute() {
        //        $query = $this->db->query('select * from tbl_invoices where  shipping_company_id = "' . $this->info['route_id'] . '"');
        //        $row_count = $this->db->rowsno($query);
        //        if ($row_count > 0) {
        //            return [CommonLanguage::getMessage('unable_delete_route')];
        //        }
        $this->db->prepare_Stmt('Delete from tbl_route WHERE route_id = ?', [&$this->info['route_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function submitRouteSchedule() {
        $schedule_id = $this->info['schedule_id'];
        $this->info['user_id'] = empty($this->info['user_id']) ? NULL : $this->info['user_id'];
        $this->info['employee_id'] = empty($this->info['employee_id']) ? NULL : $this->info['employee_id'];
        if ($schedule_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO `tbl_route_schedule`
            (`employee_id`, `route_ids`, `schedule_date`, `is_one_time`,`user_id`) VALUES (?,?,?,?,?)', [&$this->info['employee_id'], &$this->info['route_ids'],
                &$this->info['schedule_date'], &$this->info['is_one_time'], &$this->info['user_id']]);
        } else {
            $this->db->prepare_Stmt('update `tbl_route_schedule` set
            `employee_id` =?, `route_ids`=?, `schedule_date` = ?, `is_one_time`= ?,user_id = ? where schedule_id = ?', [&$this->info['employee_id'], &$this->info['route_ids'],
                &$this->info['schedule_date'], &$this->info['is_one_time'],
                &$this->info['user_id'],
                &$schedule_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deleteRouteSchedule() {
        $this->db->prepare_Stmt('Delete from tbl_route_schedule WHERE schedule_id = ?', [&$this->info['schedule_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function getRouteSchedule($id) {
        $data = array();
        $query = $this->db->query('
        select *
        from route_schedule
        where schedule_id= "' . $id . '" ');
        $data['main'] = $this->db->assoc($query);
        if (!empty($data['main']['route_ids'])) {
            $data['details'] = self::getRoutes($data['main']['route_ids'])['arr'];
        }
        return $data;
    }

    public function getRoutes($ids) {
        $data = array();
        $routes_name = '';
        $query = $this->db->query('select route_id as id,route_name as text from tbl_route
                                       where route_id in (' . $ids . ')');
        while ($row = $this->db->assoc($query)) {
            $data['arr'][] = $row;
            $routes_name .= (empty($routes_name) ? '' : ',') . $row['text'];
        }
        $data['route_names'] = $routes_name;
        return $data;
    }

    public function submitCustomersRoute() {
        $this->info['route_id'] = empty($this->info['route_id']) ? NULL : $this->info['route_id'];
        $this->info['cust_ids'] = json_decode($this->info['cust_ids'], true);
        $table_name = $this->info['is_crm'] == 0 ? 'tbl_customers' : 'tbl_customer_organization';
        $cust_id = $this->info['is_crm'] == 0 ? 'Customer_ID' : 'organization_id';
        $this->db->prepare_Stmt('update ' . $table_name . ' set route_id = ? 
                                 WHERE ' . $cust_id . ' in (' . implode(",", $this->info['cust_ids']) . ')', [&$this->info['route_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function route_report() {
        $routes = array();
        $customers_data = array();
        $customers_details_data = array();
        //
        $employee_id = $this->info['employee_id'];
        $user_id = $this->info['user_id'];
        $current_date = $this->info['current_date'];
        $current_type = $this->info['current_type'];
        $route_ids = $this->info['route_ids'];
        //
        $route_schedule_condition = '';
        $route_ids_arr = array();
        //
        if (!empty($route_ids)) {
            $route_ids_arr = explode(",", $route_ids);
            $route_schedule_condition = ' and ( ';
            $arr_condition = '';
            foreach ($route_ids_arr as $route_id) {
                $arr_condition .= ($arr_condition == '' ? '' : 'or') . ' find_in_set ("' . $route_id . '",route_ids)';
            }
            $route_schedule_condition .= $arr_condition . ' )';
        }
        //
        $sales_man_arr = array();
        $sales_man_query = $this->db->query('select distinct employee_id from route_schedule 
            where employee_id  is not null ' . (empty($employee_id) ? '' : ' and employee_id = "' . $employee_id . '"'));
        while ($row = $this->db->assoc($sales_man_query)) {
            $sales_man_arr[] = $row['employee_id'];
        }
        //
        $user_ids = array();
        $users_query = $this->db->query('select distinct user_id from route_schedule 
            where user_id  is not null  ' . (empty($user_id) ? '' : ' and user_id = "' . $user_id . '"'));
        while ($row = $this->db->assoc($users_query)) {
            $user_ids[] = $row['user_id'];
        }
        //
        if (empty($user_ids) && empty($sales_man_arr))
            return array();
        //
        $sales_user_route_ids = array();
        $user_employee = array();
        $users_employee_query = $this->db->query(' select * from  tbl_common_users 
            where Employee_ID is not null  and Employee_ID <> 0 ' . (empty($sales_man_arr) ? ' and 1=2' : ' and Employee_ID in (' . implode(",", $sales_man_arr) . ') '));
        while ($row = $this->db->assoc($users_employee_query)) {
            $user_employee[$row['User_ID']] = $row['Employee_ID'];
        }
        //
        foreach ($sales_man_arr as $id) {
            $query_route = $this->db->query('select * from route_schedule
                    where schedule_date <= "' . $current_date . '" 
                        and DayName(schedule_date) = "' . date('l', strtotime($current_date)) . '" 
                        and (schedule_date = "' . $current_date . '" or is_one_time = 0) 
                        and employee_id ="' . $id . '" ' . $route_schedule_condition . '
                    order by schedule_date desc limit 1 ');
            $row = $this->db->assoc($query_route);
            $current_route_ids_arr = explode(",", $row['route_ids']);
            foreach ($current_route_ids_arr as $route_id) {
                if (empty($route_ids) || (!empty($route_ids) && in_array($route_id, $route_ids_arr))) {
                    $row['route_id'] = $route_id;
                    $sales_user_route_ids[] = $route_id;
                    $routes[] = $row;
                }
            }
        }
        //
        foreach ($user_ids as $id) {
            $query_route = $this->db->query('select * from route_schedule
                        where schedule_date <= "' . $current_date . '"   
                            and DayName(schedule_date) = "' . date('l', strtotime($current_date)) . '" 
                            and (schedule_date = "' . $current_date . '" or is_one_time = 0)
                            and user_id ="' . $id . '" ' . $route_schedule_condition . '
                        order by schedule_date desc limit 1 ');
            $row = $this->db->assoc($query_route);
            $current_route_ids_arr = explode(",", $row['route_ids']);
            foreach ($current_route_ids_arr as $route_id) {
                if (empty($route_ids) || (!empty($route_ids) && in_array($route_id, $route_ids_arr))) {
                    $row['route_id'] = $route_id;
                    $sales_user_route_ids[] = $route_id;
                    $routes[] = $row;
                }
            }
        }
        //
        $sales_user_route_ids = array_unique($sales_user_route_ids);
        if (!empty($sales_user_route_ids)) {
            if ($this->info['is_crm'] == 0) {
                $customer_ids_str = '';
                $query_customers = $this->db->query('select cust.Customer_ID, cust.Customer_Name, route.route_name, route.route_id
                        from tbl_customers cust
                            inner join tbl_route route using(route_id)
                        where route_id in (' . implode(",", $sales_user_route_ids) . ')');
                while ($row = $this->db->assoc($query_customers)) {
                    $customers_data[$row['Customer_ID']] = ['Customer_ID' => $row['Customer_ID'],
                        'Customer_Name' => $row['Customer_Name'], 'route_name' => $row['route_name'],
                        'route_id' => $row['route_id']];
                    $customer_ids_str .= ($customer_ids_str == '' ? '' : ',') . $row['Customer_ID'];
                }
                if ($customer_ids_str != '') {
                    $inv_sls_usr_cond = "";
                    if (!empty($sales_man_arr))
                        $inv_sls_usr_cond .= ' and Sales_ID in (' . implode(",", $sales_man_arr) . ') ';
                    if (!empty($user_ids))
                        $inv_sls_usr_cond .= (empty($inv_sls_usr_cond) ? ' and ' : ' or ') . ' created_logged_id in (' . implode(",", $user_ids) . ') ';
                    $query_invoices = $this->db->query('select Invoice_ID_Auto, Customer_ID, Sales_ID, created_logged_id
                    from tbl_invoices 
                    where date(date_created) = "' . $current_date . '" ' . $inv_sls_usr_cond . '
                        and Customer_ID in (' . $customer_ids_str . ') ');
                    while ($row = $this->db->assoc($query_invoices)) {
                        if (!isset($customers_details_data[$row['Customer_ID']]['sales'][$row['Sales_ID']]['inv']))
                            $customers_details_data[$row['Customer_ID']]['sales'][$row['Sales_ID']]['inv'] = 0;
                        $customers_details_data[$row['Customer_ID']]['sales'][$row['Sales_ID']]['inv'] ++;
                        //
                        if (!isset($customers_details_data[$row['Customer_ID']]['users'][$row['created_logged_id']]['inv']))
                            $customers_details_data[$row['Customer_ID']]['users'][$row['created_logged_id']]['inv'] = 0;
                        $customers_details_data[$row['Customer_ID']]['users'][$row['created_logged_id']]['inv'] ++;
                    }
                    $query_visits = $this->db->query('select visit_id, customer_id, app_user_id
                                    from tbl_customer_visit 
                                where date = "' . $current_date . '" and customer_id in (' . $customer_ids_str . ')
                                    and app_user_id in (' . implode(",", $user_ids) . ')');
                    while ($row = $this->db->assoc($query_visits)) {
                        if (!empty($user_employee[$row['app_user_id']])) {
                            if (!isset($customers_details_data[$row['customer_id']]['sales'][$user_employee[$row['app_user_id']]]['visits']))
                                $customers_details_data[$row['customer_id']]['sales'][$user_employee[$row['app_user_id']]]['visits'] = 0;
                            $customers_details_data[$row['customer_id']]['sales'][$user_employee[$row['app_user_id']]]['visits'] ++;
                        }
                        if (!isset($customers_details_data[$row['customer_id']]['users'][$row['app_user_id']]['visits']))
                            $customers_details_data[$row['customer_id']]['users'][$row['app_user_id']]['visits'] = 0;
                        $customers_details_data[$row['customer_id']]['users'][$row['app_user_id']]['visits'] ++;
                    }
                    $rec_sls_usr_cond = "";
                    if (!empty($sales_man_arr))
                        $rec_sls_usr_cond .= ' and receipts.salesman_id in (' . implode(",", $sales_man_arr) . ') ';
                    if (!empty($user_ids))
                        $rec_sls_usr_cond .= (empty($rec_sls_usr_cond) ? ' and ' : ' or ') . ' receipts.created_logged_id in (' . implode(",", $user_ids) . ') ';
                    $query_receipt = $this->db->query(' select pay_rec_id, accounts.main_id, receipts.salesman_id, receipts.created_logged_id
                            from tbl_payment_receipt receipts
                                inner join tbl_chart_of_accounts accounts on accounts.Account_ID = receipts.Account_ID
                            where Date(receipts.date_created) = "' . $current_date . '" and receipts.is_receipt = 1
                                and accounts.main_id in (' . $customer_ids_str . ') ' . $rec_sls_usr_cond . '
                        ');
                    while ($row = $this->db->assoc($query_receipt)) {
                        if (!isset($customers_details_data[$row['main_id']]['sales'][$row['salesman_id']]['receipt']))
                            $customers_details_data[$row['main_id']]['sales'][$row['salesman_id']]['receipt'] = 0;
                        $customers_details_data[$row['main_id']]['sales'][$row['salesman_id']]['receipt'] ++;
                        if (!isset($customers_details_data[$row['main_id']]['users'][$row['created_logged_id']]['receipt']))
                            $customers_details_data[$row['main_id']]['users'][$row['created_logged_id']]['receipt'] = 0;
                        $customers_details_data[$row['main_id']]['users'][$row['created_logged_id']]['receipt'] ++;
                    }
                }
            }
            else {
                $customer_ids_str = '';
                $query_customers = $this->db->query('select cust.organization_id as Customer_ID,
                               cust.organization_customer_name as Customer_Name, route.route_name, route.route_id
                        from tbl_customer_organization cust
                            inner join tbl_route route using(route_id)
                        where route_id in (' . implode(",", $sales_user_route_ids) . ')');
                while ($row = $this->db->assoc($query_customers)) {
                    $customers_data[$row['Customer_ID']] = ['Customer_ID' => $row['Customer_ID'],
                        'Customer_Name' => $row['Customer_Name'],
                        'route_name' => $row['route_name'],
                        'route_id' => $row['route_id']];
                    $customer_ids_str .= ($customer_ids_str == '' ? '' : ',') . $row['Customer_ID'];
                }
                if ($customer_ids_str != '') {
                    $query_invoices = $this->db->query(' select activity_id, organization_id as Customer_ID, created_by
                    from tbl_deals_activity 
                    where Date(date_created) = "' . $current_date . '" and created_by in (' . implode(",", $user_ids) . ')
                        and organization_id in (' . $customer_ids_str . ') and activity_type_id =  2 ');
                    while ($row = $this->db->assoc($query_invoices)) {
                        if (!empty($user_employee[$row['created_by']])) {
                            if (!isset($customers_details_data[$row['Customer_ID']]['sales'][$user_employee[$row['created_by']]]['act']))
                                $customers_details_data[$row['Customer_ID']]['sales'][$user_employee[$row['created_by']]]['act'] = 0;
                            $customers_details_data[$row['Customer_ID']]['sales'][$user_employee[$row['created_by']]]['act'] ++;
                        }
                        if (!isset($customers_details_data[$row['Customer_ID']]['users'][$row['created_by']]['act']))
                            $customers_details_data[$row['Customer_ID']]['users'][$row['created_by']]['act'] = 0;
                        $customers_details_data[$row['Customer_ID']]['users'][$row['created_by']]['act'] ++;
                    }
                }
            }
        }
        //
        $data = array();
        foreach ($routes as $route) {
            foreach ($customers_data as $customers) {
                if ($customers['route_id'] == $route['route_id']) {
                    $user_id = $route['user_id'];
                    $employee_id = $route['employee_id'];
                    $customer_id = $customers['Customer_ID'];
                    //                    print_r($customers);
                    $actions = '';
                    if (!empty($customers_details_data[$customer_id]['sales'][$employee_id]['receipt']) && $route['employee_id'] == $employee_id && (empty($current_type) || (!empty($current_type) && (in_array(3, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Receipts: ' . $customers_details_data[$customer_id]['sales'][$employee_id]['receipt'];
                    }
                    if (!empty($customers_details_data[$customer_id]['sales'][$employee_id]['inv']) && $route['employee_id'] == $employee_id && (empty($current_type) || (!empty($current_type) && (in_array(1, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Invoices: ' . $customers_details_data[$customer_id]['sales'][$employee_id]['inv'];
                    }
                    if (!empty($customers_details_data[$customer_id]['sales'][$employee_id]['visits']) && $route['employee_id'] == $employee_id && (empty($current_type) || (!empty($current_type) && (in_array(2, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Visits: ' . $customers_details_data[$customer_id]['sales'][$employee_id]['visits'];
                    }
                    if (!empty($customers_details_data[$customer_id]['sales'][$employee_id]['act']) && $route['employee_id'] == $employee_id && (empty($current_type) || (!empty($current_type) && (in_array(4, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Visits: ' . $customers_details_data[$customer_id]['sales'][$employee_id]['act'];
                    }

                    if (!empty($customers_details_data[$customer_id]['users'][$user_id]['receipt']) && $route['user_id'] == $user_id && (empty($current_type) || (!empty($current_type) && (in_array(3, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Receipts: ' . $customers_details_data[$customer_id]['users'][$user_id]['receipt'];
                    }
                    if (!empty($customers_details_data[$customer_id]['users'][$user_id]['inv']) && $route['user_id'] == $user_id && (empty($current_type) || (!empty($current_type) && (in_array(1, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Invoices: ' . $customers_details_data[$customer_id]['users'][$user_id]['inv'];
                    }
                    if (!empty($customers_details_data[$customer_id]['users'][$user_id]['visits']) && $route['user_id'] == $user_id && (empty($current_type) || (!empty($current_type) && (in_array(2, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Visits: ' . $customers_details_data[$customer_id]['users'][$user_id]['visits'];
                    }
                    if (!empty($customers_details_data[$customer_id]['users'][$user_id]['act']) && $route['user_id'] == $user_id && (empty($current_type) || (!empty($current_type) && (in_array(4, $current_type))))) {
                        $actions .= ($actions == '' ? '' : '<br>') . 'Activity: ' . $customers_details_data[$customer_id]['users'][$user_id]['act'];
                    }
                    $current_actions = $actions == '' ? 'No Actions' : $actions;
                    if (empty($this->info['status_id']) || ($this->info['status_id'] == 1 && !empty($actions)) || ($this->info['status_id'] == 2 && empty($actions)))
                        $data[] = [$customers['route_name'], $route['User_Name'], $route['employee'], $customers['Customer_Name'], $current_actions];
                }
            }
        }
        //
        return $data;
    }

}
