<?php

class sales {

    /**
     * @var array Holds an array of data
     */
    protected $info = array();

    /**
     * @var obj Holds the database adapter object
     */
    private $db;
    private $year_id;
    private $year_id_actual;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
    }

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

    public function salesConsilidateByUser($user, $from, $to,$is_actual=0) {
        $user_condition = ' ';
        $sales_condition = ' ';
        if (!empty($user)) {
            $user_condition = ' where User_ID in (' . implode(",", $user) . ')';
        }
        if (!empty($this->info['branch_id'])) {
            $sales_condition = ' and branch_id = "'.$this->info['branch_id'].'" ';
        }
        $data = array();
        $query = $this->db->query('select User_ID,User_Name,
            0 as invoice_count,0 as invoice_total,0 as payment_count,0 as payment_total,0 as balance 
            from tbl_users_accounting_accounts inner join tbl_common_users using(User_ID)' . $user_condition);
        while ($row = $this->db->assoc($query)) {
            $data[$row['User_ID']] = $row;
        }
        $query_inv = $this->db->query('select count(Invoice_ID) as invoice_count,
                                        sum(Invoice_Net_Total * current_rate) as invoice_total,
                                        sum(Invoice_Paid_Amount * current_rate) as payment_total,
                                        sum(Payment_Paid_Amount * current_rate) as payment_paid,
                                        created_logged_id
                                        from tbl_invoices 
                                        where created_logged_id in (' . implode(',', array_keys($data)) . ') 
                                        and Invoice_Date >= "' . $from . '"
                                        and Invoice_Date <= "' . $to . '"
                                        and is_committed = 1
                                         '.(!empty($this->info['sales_man_id']) ? ' and Sales_ID = "'.$this->info['sales_man_id'].'"' : '').'
                                            '.(($is_actual==0) ? ' and is_actual = 0 ' : '').$sales_condition.'
                                        group by created_logged_id

            ');
        while ($row_inv = $this->db->assoc($query_inv)) {
            $data[$row_inv['created_logged_id']]['invoice_count'] = $row_inv['invoice_count'];
            $data[$row_inv['created_logged_id']]['invoice_total'] = $row_inv['invoice_total'];
            $data[$row_inv['created_logged_id']]['payment_total'] = $row_inv['payment_total'];
            $data[$row_inv['created_logged_id']]['balance'] = $row_inv['invoice_total'] - $row_inv['payment_total'] - $row_inv['payment_paid'];
        }
        $query_pay = $this->db->query('select count(distinct p.pay_rec_id) as payment_count,
                                        sum(d.payment_amount * d.current_rate) as payment_total,
                                        p.created_logged_id
                                        from tbl_payment_receipt_details d
                                        join tbl_payment_receipt p
                                        on p.pay_rec_id = d.pay_rec_id
                                        where p.created_logged_id in (' . implode(',', array_keys($data)) . ') 
                                        and p.payment_date >= "' . $from . '"
                                        and p.payment_date <= "' . $to . '"
                                        and p.is_receipt = 1 and p.is_return=0
                                        '.(($is_actual==0) ? ' and p.is_actual = 0 ' : '').$sales_condition.'
                                        group by p.created_logged_id
            ');
//        $query_pay = $this->db->query('select count(Payment_ID) as payment_count,
//                                        sum(Payment_Amount * current_rate) as payment_total,
//                                        created_logged_id
//                                        from tbl_payments_on_accounts
//                                        where created_logged_id in (' . implode(',', array_keys($data)) . ') 
//                                        and Payment_Date >= "' . $from . '"
//                                        and Payment_Date <= "' . $to . '"
//                                        and IsReceipt = 1
//                                        '.(($is_actual==0) ? ' and Status_ID = 0 ' : '').$sales_condition.'
//                                        group by created_logged_id
//            ');
        while ($row_pay = $this->db->assoc($query_pay)) {
            $data[$row_pay['created_logged_id']]['payment_count'] = $row_pay['payment_count'];
            $data[$row_pay['created_logged_id']]['payment_total'] +=$row_pay['payment_total'];
        }
        $data_final = array_map(function($arr) {
            unset($arr['User_ID']);
            $arr['invoice_total'] = smc_session()['local_currency_symbol'].' '.CommonFunctions::public_round_number($arr['invoice_total']);
            $arr['payment_total'] = smc_session()['local_currency_symbol'].' '.CommonFunctions::public_round_number($arr['payment_total']);
            $arr['balance'] = smc_session()['local_currency_symbol'].' '.CommonFunctions::public_round_number($arr['balance']);
            return  array_values($arr);
        }, $data);
        return array_values($data_final);
    }


    public function detailInvDistByCustomer($customer_id, $from, $to) {
        $data = array();
        $query = $this->db->query('select
                                    i.Invoice_Date,
                                    concat(i.invoice_prefix,i.Invoice_ID) as invoice_nb,
                                    (i.Invoice_VAT * current_rate) as Invoice_VAT,
                                    count(Product_ID) as product_count,
                                    (i.Invoice_Sub_Total * i.current_rate) as Invoice_Sub_Total,
                                    i.Invoice_Discount,
                                    (i.Invoice_Net_Total * i.current_rate) as Invoice_Net_Total,
                                    ((i.Invoice_Paid_Amount + Payment_Paid_Amount) * i.current_rate) as paid_amount,
                                    ((i.Invoice_Net_Total-(i.Invoice_Paid_Amount + Payment_Paid_Amount)) * current_rate) as balance
                                    from tbl_invoices i
                                    join tbl_invoice_products_details d using(Invoice_ID_Auto)
                                    where i.Customer_ID ="' . $customer_id . '"
                                    and i.Invoice_Date >= "' . $from . '"
                                    and i.Invoice_Date <= "' . $to . '"
                                    group by i.Invoice_ID_Auto
            ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

}
