<?php defined('BASEPATH') or exit('No direct script access allowed');

class Reports extends MY_Controller
{

    function __construct()
    {
        parent::__construct();


        if (!$this->loggedIn) {
            redirect('login');
        }

        // if ( ! $this->Admin) {
        //     $this->session->set_flashdata('error', lang('access_denied'));
        //     redirect('pos');
        // }

        $this->load->model('reports_model');
    }

    function daily_sales($year = NULL, $month = NULL)
    {
        if (!$year) {
            $year = date('Y');
        }
        if (!$month) {
            $month = date('m');
        }
        $this->data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $this->lang->load('calendar');
        $config = array(
            'show_next_prev' => TRUE,
            'next_prev_url' => site_url('reports/daily_sales'),
            'month_type' => 'long',
            'day_type' => 'long'
        );
        $config['template'] = '

        {table_open}<table border="0" cellpadding="0" cellspacing="0" class="table table-bordered" style="min-width:522px;">{/table_open}

        {heading_row_start}<tr class="active">{/heading_row_start}

        {heading_previous_cell}<th><div class="text-center"><a href="{previous_url}">&lt;&lt;</div></a></th>{/heading_previous_cell}
        {heading_title_cell}<th colspan="{colspan}"><div class="text-center">{heading}</div></th>{/heading_title_cell}
        {heading_next_cell}<th><div class="text-center"><a href="{next_url}">&gt;&gt;</a></div></th>{/heading_next_cell}

        {heading_row_end}</tr>{/heading_row_end}

        {week_row_start}<tr>{/week_row_start}
        {week_day_cell}<td class="cl_equal"><div class="cl_wday">{week_day}</div></td>{/week_day_cell}
        {week_row_end}</tr>{/week_row_end}

        {cal_row_start}<tr>{/cal_row_start}
        {cal_cell_start}<td>{/cal_cell_start}

        {cal_cell_content}<div class="cl_left">{day}</div><div class="cl_right">{content}</div>{/cal_cell_content}
        {cal_cell_content_today}<div class="cl_left highlight">{day}</div><div class="cl_right">{content}</div>{/cal_cell_content_today}

        {cal_cell_no_content}{day}{/cal_cell_no_content}
        {cal_cell_no_content_today}<div class="highlight">{day}</div>{/cal_cell_no_content_today}

        {cal_cell_blank}&nbsp;{/cal_cell_blank}

        {cal_cell_end}</td>{/cal_cell_end}
        {cal_row_end}</tr>{/cal_row_end}

        {table_close}</table>{/table_close}
        ';

        $this->load->library('calendar', $config);

        $sales = $this->reports_model->getDailySales($year, $month);

        if (!empty($sales)) {
            foreach ($sales as $sale) {
                $daily_sale[$sale->date] = "<span class='text-warning'>" . $sale->tax . "</span><br>" . $sale->discount . "<br><span class='text-success'>" . $sale->total . "</span><br><span style='border-top:1px solid #DDD;'>" . $sale->grand_total . "</span>";
            }
        } else {
            $daily_sale = array();
        }

        $this->data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $this->data['calender'] = $this->calendar->generate($year, $month, $daily_sale);

        $start = $year . '-' . $month . '-01 00:00:00';
        $end = $year . '-' . $month . '-' . days_in_month($month, $year) . ' 23:59:59';
        $this->data['total_purchases'] = $this->reports_model->getTotalPurchases($start, $end);
        $this->data['total_sales'] = $this->reports_model->getTotalSales($start, $end);
        $this->data['total_expenses'] = $this->reports_model->getTotalExpenses($start, $end);
        $this->data['comprobantes_diarios'] = $this->reports_model->getTotalComprobantesPorMes($year, $month);

        $arrayMeses = array(
            "01" => "ENERO", "02" => "FEBRERO", "03" => "MARZO", "04" => "ABRIL", "05" => "MAYO", "06" => "JUNIO",
            "07" => "JULIO", "08" => "AGOSTO", "09" => "SEPTIEMBRE", "10" => "OCTUBRE", "11" => "NOVIEMBRE", "12" => "DICIEMBRE"
        );

        $this->data['mes_letra'] = $arrayMeses[$month];


        $this->data['page_title'] = $this->lang->line("daily_sales");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('daily_sales')));
        $meta = array('page_title' => lang('daily_sales'), 'bc' => $bc);
        $this->page_construct('reports/daily', $this->data, $meta);
    }


    function monthly_sales($year = NULL)
    {
        if (!$year) {
            $year = date('Y');
        }
        $this->lang->load('calendar');
        $this->data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $start = $year . '-01-01 00:00:00';
        $end = $year . '-12-31 23:59:59';
        $this->data['total_purchases'] = $this->reports_model->getTotalPurchases($start, $end);
        $this->data['total_sales'] = $this->reports_model->getTotalSales($start, $end);
        $this->data['total_expenses'] = $this->reports_model->getTotalExpenses($start, $end);
        $this->data['year'] = $year;
        $this->data['sales'] = $this->reports_model->getMonthlySales($year);
        $this->data['comprobantes_mensuales'] = $this->reports_model->getTotalComprobantesPorAnio($year);
        $this->data['page_title'] = $this->lang->line("monthly_sales");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('monthly_sales')));
        $meta = array('page_title' => lang('monthly_sales'), 'bc' => $bc);
        $this->page_construct('reports/monthly', $this->data, $meta);
    }

    function index()
    {

        $this->data['error'] = (validation_errors() ? validation_errors() : $this->session->flashdata('error'));

        if ($this->input->post('customer')) {
            $start_date = $this->input->post('start_date') ? $this->input->post('start_date') : NULL;
            $end_date = $this->input->post('end_date') ? $this->input->post('end_date') : NULL;
            $user = $this->input->post('user') ? $this->input->post('user') : NULL;
            $this->data['total_sales'] = $this->reports_model->getTotalSalesforCustomer($this->input->post('customer'), $user, $start_date, $end_date);
            $this->data['total_sales_value'] = $this->reports_model->getTotalSalesValueforCustomer($this->input->post('customer'), $user, $start_date, $end_date);
        }
        $status = $this->input->get('status') ? $this->input->get('status') : 1;
        $this->data['customers'] = $this->getAllCustomers();
        $this->data['users'] = $this->reports_model->getAllStaff();
        $this->data['locals'] = $this->reports_model->getAllLocals();

        $this->data['page_title'] = $this->lang->line("sales_report");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('sales_report')));
        $meta = array('page_title' => lang('sales_report'), 'bc' => $bc);

        //Obtener listado de vendedores
        $traders = [];
        $get_traders = $this->get_traders();
        $response = json_decode($get_traders, true);
        if ($response['result'] == 'ok') {
            $traders = $response['data']['list'];
        }
        $this->data['traders'] = $traders;
        $this->page_construct('reports/sales', $this->data, $meta);
    }
    function sire(){
        $this->data['page_title'] = $this->lang->line("sire_report");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('sire_report')));
        $meta = array('page_title' => lang('sire_report'), 'bc' => $bc);
        $this->page_construct('reports/sire', $this->data, $meta);
    }

    function get_sales()
    {
        $fecha_inicio = ($this->input->get('start_date') != "") ? str_replace('/', '-',$this->input->get('start_date')) : NULL;
        $fecha_fin = ($this->input->get('end_date') != "") ? str_replace('/', '-',$this->input->get('end_date')) : NULL;
        $cliente = $this->input->get('customer') ? $this->input->get('customer') : NULL;
        $usuario = $this->input->get('user') ? $this->input->get('user') : NULL;
        $estado = $this->input->get('status') ? $this->input->get('status') : 1;
        $local = $this->input->get('locals') ? $this->input->get('locals') : NULL;
        $traders_id = $this->input->get('traders_id') ? $this->input->get('traders_id') : NULL;
        $facturas = $this->input->get('facturas');
        $boletas = $this->input->get('boletas');
        $notas_venta = $this->input->get('notas_venta');
        $notas_credito = $this->input->get('notas_credito');

        $columnas = array(0 => 'fecha_venta',1 => 'tipo_comprobante',2 => 'nro_comprobante',3 => 'nombre_cliente',4 => 'moneda',5 => 'total',6 => 'IGV',7 => 'descuento',8 => 'total',9 => 'estado_reg');
        $orderby = $columnas[$_GET['order'][0]['column']];
		$orderdir = $_GET['order'][0]['dir'];
        $start = $_GET['start'];
        $length = $_GET['length'];
        $search = $_GET['search']['value'];

        $filtros = array(
            "date_start" => $fecha_inicio,
            "date_end" => $fecha_fin,
            "client_id" => $cliente,
            "user_id" => $usuario,
            "estado" => ($estado==2)?0:$estado,
            "local_id" => $local,
            "traders_id" => $traders_id
        );
        $pestañas = array(
            "facturas"=> $facturas,
            "boletas"=> $boletas,
            "notas_ventas"=> $notas_venta,
            "notas_credito"=> $notas_credito,
            "notas_debito"=> 0
        );
        $data = array(
            "orderby"=>$orderby,
            "orderdir"=>$orderdir,
            "start"=>$start,
            "length"=>$length,
            "search"=>$search,
        );
        $data['tab'] = $pestañas;
        $data['filters'] = $filtros;
        $data['business_information'] = array(
            "ruc_number" => $this->session->userdata('ruc'),
        );
        $url = $this->config->item('api_ventas');
        $curl = curl_init();
        curl_setopt_array($curl, array(
        CURLOPT_URL => $url.'reports/sales',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_POSTFIELDS =>json_encode($data),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
        ));

        $response = curl_exec($curl);
        curl_close($curl);
        $sales = json_decode($response, true);
        $dataSet = [
            "data" => $sales['data']['list'],
            "recordsTotal"=>$sales['data']['total'],
            "draw"=>$_GET['draw'],
            "recordsFiltered" => $sales['data']['total']
        ];
        echo json_encode($dataSet);
        
    }

    function get_traders()
    {
        $urlapi_venta = $this->config->item('api_ventas');
        $url_traders = $urlapi_venta . "traders";
        //$ruc = $this->session->userdata('ruc');
        $headers = array(
            'Content-Type:application/json',
            'Accept: application/json'
        );
        $payload = array(
            'business_information' => array(
                'ruc_number' => $this->session->userdata('ruc')
            ),
        );
        $url = sprintf("%s?%s", $url_traders, http_build_query($payload));
        $process = curl_init($url); //your API url
        curl_setopt($process, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($process, CURLOPT_RETURNTRANSFER, TRUE);
        $return = curl_exec($process);
        curl_close($process);
        return $return;
    }

    function products()
    {

        $this->data['error'] = (validation_errors() ? validation_errors() : $this->session->flashdata('error'));

        $this->data['products'] = $this->reports_model->getAllProducts();
        $this->data['page_title'] = $this->lang->line("products_report");
        $this->data['page_title'] = $this->lang->line("products_report");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('products_report')));
        $meta = array('page_title' => lang('products_report'), 'bc' => $bc);
        $this->page_construct('reports/products', $this->data, $meta);
    }

    function get_products()
    {
        $product = $this->input->get('product') ? $this->input->get('product') : NULL;
        $start_date = $this->input->get('start_date') ? $this->input->get('start_date') : NULL;
        $end_date = $this->input->get('end_date') ? $this->input->get('end_date') : NULL;
        //COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity)*".$this->db->dbprefix('products').".cost, 0) as cost,
        // $this->datatables
        //->select($this->db->dbprefix('products').".name, ".$this->db->dbprefix('products').".code, COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity), 0) as sold, ROUND(COALESCE(((sum(".$this->db->dbprefix('sale_items').".subtotal)*".$this->db->dbprefix('products').".tax)/100), 0), 2) as tax, COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity)*".$this->db->dbprefix('sale_items').".cost, 0) as cost, COALESCE(sum(".$this->db->dbprefix('sale_items').".subtotal), 0) as income, ROUND((COALESCE(sum(".$this->db->dbprefix('sale_items').".subtotal), 0)) - COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity)*".$this->db->dbprefix('sale_items').".cost, 0) -COALESCE(((sum(".$this->db->dbprefix('sale_items').".subtotal)*".$this->db->dbprefix('products').".tax)/100), 0), 2) as profit", FALSE)

        //Modificacion | Diego
        // $this->datatables
        // ->select($this->db->dbprefix('products').".name, " . $this->db->dbprefix('products').".code,
        // COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity), 0) as sold,
        // ROUND(COALESCE(((sum(".$this->db->dbprefix('sale_items').".subtotal)*".$this->Settings->default_tax_rate.")/100), 0), 2) as tax,
        //  COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity)*".$this->db->dbprefix('sale_items').".cost, 0) as cost, COALESCE(sum(".$this->db->dbprefix('sale_items').".subtotal), 0) as income,
        // ROUND((COALESCE(sum(".$this->db->dbprefix('sale_items').".subtotal), 0)) - COALESCE(sum(".$this->db->dbprefix('sale_items').".quantity)*".$this->db->dbprefix('sale_items').".cost, 0) - COALESCE(((sum(".$this->db->dbprefix('sale_items').".subtotal)*".$this->Settings->default_tax_rate.")/100), 0), 2)
        // as profit", FALSE)

        // ROUND(COALESCE(sum(IF(".$this->db->dbprefix('sales').".currency='USD', ROUND(".$this->db->dbprefix('sale_items').".item_tax * exchange, 2), ".$this->db->dbprefix('sale_items').".item_tax)), 0), 2) as tax,

        $this->load->library('datatables');
        $this->datatables
            ->select($this->db->dbprefix('products') . ".name, " . $this->db->dbprefix('products') . ".code,
        COALESCE(sum(" . $this->db->dbprefix('sale_items') . ".quantity), 0) as sold,
        COALESCE(sum((" . $this->db->dbprefix('sale_items') . ".quantity)* IF(currency_cost='USD', ROUND(" . $this->db->dbprefix('sale_items') . ".cost * exchange, 2), " . $this->db->dbprefix('sale_items') . ".cost) / (1 + (igv / 100))), 0) as cost,
        COALESCE(sum(IF(" . $this->db->dbprefix('sales') . ".currency='USD', ROUND(" . $this->db->dbprefix('sale_items') . ".subtotal * exchange, 2), " . $this->db->dbprefix('sale_items') . ".subtotal) / (1 + (igv / 100))), 0) as income,
        ROUND(COALESCE(sum(IF(" . $this->db->dbprefix('sales') . ".currency='USD', " . $this->db->dbprefix('sale_items') . ".subtotal * exchange, " . $this->db->dbprefix('sale_items') . ".subtotal) / (1 + (igv / 100))), 0)
      - COALESCE(sum((" . $this->db->dbprefix('sale_items') . ".quantity)* IF(currency_cost='USD', " . $this->db->dbprefix('sale_items') . ".cost * exchange, " . $this->db->dbprefix('sale_items') . ".cost) / (1 + (igv / 100))), 0), 2) as profit", FALSE)

            ->from('sale_items')
            ->join('products', 'sale_items.product_id=products.id', 'left')
            ->join('sales', 'sale_items.sale_id=sales.id', 'left')
            // ->where('sales.status !=', "DEBE")
            ->where('sales.invoice_id !=', NULL)
            ->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
            ->group_by('products.id');

        if ($product) {
            $this->datatables->where('products.id', $product);
        }
        if ($start_date) {
            $this->datatables->where('date >=', $start_date);
        }
        if ($end_date) {
            $endDate = strtotime('+1 day', strtotime($end_date));
            $endDate = date('Y-m-d 00:00:00', $endDate);
            $this->datatables->where('date <=', $endDate);
        }

        echo $this->datatables->generate();
    }

    function profit($income, $cost, $tax)
    {
        return floatval($income) . " - " . floatval($cost) . " - " . floatval($tax);
    }

    function top_products()
    {

        $this->data['topProducts'] = $this->reports_model->topProducts();
        $this->data['topProducts1'] = $this->reports_model->topProducts1();
        $this->data['topProducts3'] = $this->reports_model->topProducts3();
        $this->data['topProducts12'] = $this->reports_model->topProducts12();

        $this->data['page_title'] = $this->lang->line("top_products");
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('top_products')));
        $meta = array('page_title' => lang('top_products'), 'bc' => $bc);
        $this->page_construct('reports/top', $this->data, $meta);
    }

    function registers()
    {

        $this->data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $this->data['users'] = $this->reports_model->getAllStaff();
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('registers_report')));
        $meta = array('page_title' => lang('registers_report'), 'bc' => $bc);
        $this->page_construct('reports/registers', $this->data, $meta);
    }

    function get_register_logs()
    {

        $user = $this->input->get('user') ? $this->input->get('user') : NULL;
        $start_date = $this->input->get('start_date') ? $this->input->get('start_date') : NULL;
        $end_date = $this->input->get('end_date') ? $this->input->get('end_date') : NULL;
        //***********TRJ026 - ALEXANDER ROCA - 02/04/2019***********
        $this->load->library('datatables');
        $this->datatables
            ->select("date, closed_at, CONCAT(" . $this->db->dbprefix('users') . ".first_name, ' ', " . $this->db->dbprefix('users') . ".last_name, '<br>', " . $this->db->dbprefix('users') . ".email) as user, cash_in_hand, CONCAT(total_cc_slips, ' (', total_cc_slips_submitted, ')') as cc_slips, CONCAT(total_stripe, ' (', total_stripe_submitted, ')') as c_stripe, CONCAT(total_cheques, ' (', total_cheques_submitted, ')') as total_cheques, CONCAT(total_cash, ' (', total_cash_submitted, ')') as total_cash, CONCAT(total_cc_slips + total_stripe + total_cheques + total_cash, ' (', total_cc_slips_submitted + total_stripe_submitted + total_cheques_submitted + total_cash_submitted, ')') as total_ventas, note", FALSE)
            ->from("registers")
            ->join('users', 'users.id=registers.user_id', 'left');
        //***********TRJ026 - ALEXANDER ROCA - 02/04/2019***********
        if ($user) {
            $this->datatables->where('registers.user_id', $user);
        }
        if ($start_date) {
            $this->datatables->where('date BETWEEN "' . $start_date . '" and "' . $end_date . '"');
        }

        echo $this->datatables->generate();
    }

    function payments()
    {
        $this->data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $this->data['users'] = $this->reports_model->getAllStaff();
        $this->data['customers'] = $this->reports_model->getAllCustomers();
        $bc = array(array('link' => '#', 'page' => lang('reports')), array('link' => '#', 'page' => lang('payments_report')));
        $meta = array('page_title' => lang('payments_report'), 'bc' => $bc);
        $this->page_construct('reports/payments', $this->data, $meta);
    }

    function get_payments()
    {
        $user = $this->input->get('user') ? $this->input->get('user') : NULL;
        $ref = $this->input->get('payment_ref') ? $this->input->get('payment_ref') : NULL;
        $sale_id = $this->input->get('sale_no') ? $this->input->get('sale_no') : NULL;
        $customer = $this->input->get('customer') ? $this->input->get('customer') : NULL;
        $paid_by = $this->input->get('paid_by') ? $this->input->get('paid_by') : NULL;
        $start_date = $this->input->get('start_date') ? $this->input->get('start_date') : NULL;
        $end_date = $this->input->get('end_date') ? $this->input->get('end_date') : NULL;

        $this->load->library('datatables');
        $this->datatables
            ->select($this->db->dbprefix('payments') . ".date, " . $this->db->dbprefix('payments') . ".reference as ref, " . $this->db->dbprefix('sales') . ".id as sale_no, paid_by, " . $this->db->dbprefix('sales') . ".currency, " . $this->db->dbprefix('payments') . ".amount as pay_amount")
            ->from('payments')
            ->join('sales', 'payments.sale_id=sales.id', 'left')
            ->where('sales.estado =', 1) //**********TRJ074 - ALEXANDER ROCA - 23/08/2019**************
            ->group_by('payments.id');

        if ($user) {
            $this->datatables->where('payments.created_by', $user);
        }
        if ($ref) {
            $this->datatables->where('payments.reference', $ref);
        }
        if ($paid_by) {
            $this->datatables->where('payments.paid_by', $paid_by);
        }
        if ($sale_id) {
            $this->datatables->where('sales.id', $sale_id);
        }
        if ($customer) {
            $this->datatables->where('sales.customer_id', $customer);
        }
        if ($customer) {
            $this->datatables->where('sales.customer_id', $customer);
        }
        if ($start_date) {
            $this->datatables->where($this->db->dbprefix('payments') . '.date BETWEEN "' . $start_date . '" and "' . $end_date . '"');
        }

        echo $this->datatables->generate();
    }

    function alerts()
    {
        $data['error'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('error');
        $this->data['page_title'] = lang('stock_alert');
        $bc = array(array('link' => '#', 'page' => lang('stock_alert')));
        $meta = array('page_title' => lang('stock_alert'), 'bc' => $bc);
        $this->page_construct('reports/alerts', $this->data, $meta);
    }

    function get_alerts()
    {

        $this->load->library('datatables');
        $this->datatables->select($this->db->dbprefix('products') . ".id as pid, " . $this->db->dbprefix('products') . ".image as image, " . $this->db->dbprefix('products') . ".code as code, " . $this->db->dbprefix('products') . ".name as pname, type, " . $this->db->dbprefix('categories') . ".name as cname, stock as quantity, alert_quantity, tax_method, cost, price", FALSE)
            ->join('categories', 'categories.id=products.category_id')
            ->join('warehouse_stock', 'warehouse_stock.product_id=products.id')
            ->from('products')
            ->where('stock < alert_quantity', NULL, FALSE)
            ->where('products.estado !=', 0)
            ->group_by('products.id');
        $this->datatables->add_column("Actions", "<div class='text-center'><a href='#' class='btn btn-xs btn-primary ap tip' data-id='$1' title='" . lang('add_to_purcahse_order') . "'><i class='fa fa-plus'></i></a></div>", "pid");
        $this->datatables->unset_column('pid');
        echo $this->datatables->generate();
    }

    public function exportarExcelVentas()
    {

        $fecha_inicio = ($this->input->post('fecha_inicio') != "") ? (str_replace('/','-',$this->input->post('fecha_inicio'))) : NULL;
        $fecha_fin = ($this->input->post('fecha_fin') != "") ? (str_replace('/','-',$this->input->post('fecha_fin'))) : NULL;
        $cliente = $this->input->post('cliente');
        $usuario = $this->input->post('usuario');
        $estado = $this->input->post('estado');
        $local = $this->input->post('local');
        $traders_id = $this->input->post('traders_id');
        $facturas = $this->input->post('facturas');
        $boletas = $this->input->post('boletas');
        $notas_venta = $this->input->post('notas_venta');
        $notas_credito = $this->input->post('notas_credito');

        $filtros = array(
            "date_start" => $fecha_inicio,
            "date_end" => $fecha_fin,
            "client_id" => $cliente,
            "user_id" => $usuario,
            "estado" => $estado,
            "local_id" => $local,
            "traders_id" => $traders_id
        );
        $tabs = array(
            "facturas"=> $facturas,
            "boletas"=> $boletas,
            "notas_ventas"=> $notas_venta,
            "notas_credito"=> $notas_credito,
            "notas_debito"=> 0
        );
        $sales = $this->obtenerReporte($filtros, $tabs);
        if(count($sales['facturas'])>0 || count($sales['boletas'])>0 || count($sales['notas_venta'])> 0){

        
            $this->load->library('excel');
            $excel = new PHPExcel();

            $styleArray = array(
                'font'  => array(
                    'bold'  => true,
                    'color' => array('rgb' => 'FFFFFF'),
                    'size'  => 11,
                    'name'  => 'Arial Narrow'
                ),
                'alignment' => array(
                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                )
            );
            $hoja = 1;
            if ($excel->getSheetCount() > 0) {
                // Establece la hoja activa, en este caso, la primera hoja (índice 0)
                $excel->setActiveSheetIndex(0);
            }
            foreach ($tabs as $nombrePestana => $activar) {
                if ($nombrePestana != 'notas_credito' && $activar == 1) {
                    $sheet = $excel->createSheet();
                    if($nombrePestana == 'facturas'){
                        $tabName = "Facturas";
                    } // Crea una nueva hoja
                    if($nombrePestana == 'boletas'){
                        $tabName = "Boletas";
                    } // Crea una nueva hoja
                    if($nombrePestana == 'notas_ventas'){
                        $tabName = "Notas de venta";
                    } // Crea una nueva hoja
                    $sheet->setTitle($tabName);
                    
                    $sheet->getStyle('A1:U1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF0B4EC2');
                    $sheet->getStyle('A1:U1')->applyFromArray($styleArray);
                    foreach (range('A', 'U') as $columnID) {
                        $sheet->getColumnDimension($columnID)->setAutoSize(true);
                    }
            
                    $excel->setActiveSheetIndex($hoja)->setCellValue('A1', "FECHA DE VENTA");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('B1', "FECHA DE VENCIMIENTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('C1', "TIPO DE COMPROBANTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('D1', "N° DE COMPROBANTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('E1', "LOCAL");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('F1', "NOMBRE DE CLIENTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('G1', "NUMERO DE DOCUMENTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('H1', "MONEDA");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('I1', "AFECTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('J1', "IGV");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('K1', "NO AFECTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('L1', "EXONERADO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('M1', "DESCUENTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('N1', "TOTAL");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('O1', "PAGADO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('P1', "FORMA DE PAGO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('Q1', "ESTADO REGISTRO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('R1', "ESTADO SUNAT");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('S1', "DESCRIPCION");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('T1', "OBSERVACIONES");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('U1', "CREADO POR");

                    $no = 1;
                    $numrow = 2;
                    foreach ($sales[$nombrePestana] as $value) {
                        $excel->setActiveSheetIndex($hoja)->setCellValue('A' . $numrow, $value['fecha_venta']);
                        $excel->getActiveSheet()->getStyle('A' . $numrow)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
                        $excel->setActiveSheetIndex($hoja)->setCellValue('B' . $numrow, $value['fecha_vencimiento']);
                        $excel->getActiveSheet()->getStyle('B' . $numrow)->getNumberFormat()->setFormatCode('yyyy-mm-dd h:mm:ss');
                        $excel->setActiveSheetIndex($hoja)->setCellValue('C' . $numrow, $value['tipo_comprobante']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('D' . $numrow, $value['nro_comprobante']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('E' . $numrow, $value['local']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('F' . $numrow, $value['nombre_cliente']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('G' . $numrow, $value['nro_documento']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('H' . $numrow, $value['moneda']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('I' . $numrow, $value['afecto']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('J' . $numrow, $value['IGV']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('K' . $numrow, $value['no_afecto']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('L' . $numrow, $value['exonerado']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('M' . $numrow, $value['descuento']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('N' . $numrow, $value['total']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('O' . $numrow, $value['pagado']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('P' . $numrow, $value['forma_pago']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('Q' . $numrow, $value['estado_reg']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('R' . $numrow, $value['estado_sunat']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('S' . $numrow, $value['descripcion']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('T' . $numrow, $value['observaciones']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('U' . $numrow, $value['creado_por']);
                        
                        $no++;
                        $numrow++;
                    }
                    $hoja++;
                }else if ($nombrePestana == 'notas_credito' && $activar == 1) {
                    $sheet = $excel->createSheet(); // Crea una nueva hoja
                    $sheet->setTitle($nombrePestana);
                    
                    $sheet->getStyle('A1:Q1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FF0B4EC2');
                    $sheet->getStyle('A1:Q1')->applyFromArray($styleArray);
                    foreach (range('A', 'Q') as $columnID) {
                        $sheet->getColumnDimension($columnID)->setAutoSize(true);
                    }
            
                    $excel->setActiveSheetIndex($hoja)->setCellValue('A1', "FECHA DE NC");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('B1', "TIPO DE COMPROBANTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('C1', "N° DE COMPROBANTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('D1', "MOTIVO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('E1', "NOMBRE DE CLIENTE");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('F1', "NUMERO DE DOCUMENTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('G1', "MONEDA");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('H1', "AFECTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('I1', "IGV");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('J1', "NO AFECTO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('K1', "EXONERADO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('L1', "TOTAL");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('M1', "DESCRIPCION");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('N1', "ESTADO REGISTRO");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('O1', "ESTADO SUNAT");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('P1', "DESCRIPCION SUNAT");
                    $excel->setActiveSheetIndex($hoja)->setCellValue('Q1', "CREADO POR");

                    $no = 1;
                    $numrow = 2;
                    foreach ($sales[$nombrePestana] as $value) {
                        $fecha_emision = date('d/m/Y H:i:s', strtotime($value['fecha'])); // Cambiado 'h' a 'H' para formato de 24 horas
                        $excel->setActiveSheetIndex($hoja)->setCellValueExplicit('A' . $numrow, $fecha_emision, PHPExcel_Cell_DataType::TYPE_STRING);

                        // También puedes aplicar un estilo para que Excel reconozca la celda como una fecha
                        $excel->getActiveSheet()->getStyle('A' . $numrow)->getNumberFormat()->setFormatCode('dd/mm/yyyy hh:mm:ss');
                        $excel->setActiveSheetIndex($hoja)->setCellValue('B' . $numrow, $value['tipo_comprobante']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('C' . $numrow, $value['nro_comprobante']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('D' . $numrow, $value['motivo']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('E' . $numrow, $value['nombre_cliente']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('F' . $numrow, $value['nro_documento']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('G' . $numrow, $value['moneda']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('H' . $numrow, $value['afecto']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('I' . $numrow, $value['impuesto']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('J' . $numrow, $value['inafecto']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('K' . $numrow, $value['exonerado']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('L' . $numrow, $value['total']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('M' . $numrow, $value['descripcion']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('N' . $numrow, $value['estado_registro']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('O' . $numrow, $value['estado_sunat']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('P' . $numrow, $value['respuesta_sunat']);
                        $excel->setActiveSheetIndex($hoja)->setCellValue('Q' . $numrow, $value['creado_por']);
                        
                        $no++;
                        $numrow++;
                    }
                    $hoja++;
                }

            }
            $excel->setActiveSheetIndex(0);
            $excel->removeSheetByIndex(0); 
            // Enviar el archivo Excel al navegador
            ob_start();
            $excel_writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
            $excel_writer->save('php://output');
            $xlsData = ob_get_contents();
            ob_end_clean();

            // Generar una respuesta JSON con el archivo Excel codificado en base64
            $response = array(
                'status' => TRUE,
                'file' => "data:application/vnd.ms-excel;base64," . base64_encode($xlsData),
                'filename' => "REPORTE_VENTAS_" . date('dmY') . ".xls",
                'result'=>'ok'
            );
        }else{
            $response = array(
                'status' => TRUE,
                'result'=>'failed',
                'mensaje' => 'No se encontró información para generar el reporte.'
            );
        }
        die(json_encode($response));
    }
    public function exportarSireVentas(){

        $year = ($this->input->post('anio') != "") ? $this->input->post('anio') : NULL;
        $month = ($this->input->post('mes') != "") ? $this->input->post('mes') : NULL;
        $filtros = array(
            "estado" => 1,
            "year" => $year,
            "month" => $month
        );
        $pestañas = array(
            "facturas"=> 1,
            "boletas"=> 1
        );

        $data['tab'] = $pestañas;
        $data['filters'] = $filtros;
        $data['business_information'] = array(
            "ruc_number" => $this->session->userdata('ruc'),
        );
        
        $curl = curl_init();
        $url = $this->config->item('api_ventas');
        curl_setopt_array($curl, array(
        CURLOPT_URL => $url.'reports/sire',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_POSTFIELDS =>json_encode($data),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
        ));

        $response = curl_exec($curl);
        curl_close($curl);
        $res = json_decode($response, true);
        if($res['result']=='ok'){
            if(count($res['data']) > 0){            
                $txtContent = "";
                foreach($res['data'] as $item){
                    // "fecha_emision","fecha_vencimiento","tipo_comprobante","nro_comprobante","document_type",
                    // "nro_documento","nro_ruc","nombre_cliente","afecto","IGV","exonerado","no_afecto","total",
                    // "moneda","descuento","pagado","fecha_modificacion","nota_credito"
                    $periodo=$year.$month;
                    if($periodo == ""){
                        $periodo = date('Ym');
                    }
                    $partesComprobante = explode('-', $item['nro_comprobante']);
                    $serie = $partesComprobante[0];
                    $correlativo = $partesComprobante[1];


                    $serieNC = "";
                    $correlativoNC = "";
                    if($item['nota_credito'] != null){
                        $numeroNC = explode('-', $item['nota_credito']);
                        $serieNC = $numeroNC[0];
                        $correlativoNC = $numeroNC[1];
                    }

                    if($item['tipo_comprobante'] == "02"){
                        $tipo_documento_cliente = 6;
                        $numero_documento = $item['nro_ruc'];
                    }else{
                        $tipo_documento_cliente = $item['document_type'];
                        if($tipo_documento_cliente == 6 && $item['nro_documento'] == null){
                            $numero_documento = $item['nro_ruc'];
                        }else{
                            $numero_documento = $item['nro_documento'];
                        }
                    }
                    $txtContent.= $this->session->userdata('ruc').'|';
                    $txtContent.= $this->session->userdata('razon_social').'|';
                    $txtContent.= $periodo.'||';
                    $txtContent.= date('d/m/Y',strtotime($item['fecha_emision']))."|";
                    $txtContent.= ($item['fecha_vencimiento'] != NULL? date('d/m/Y',strtotime($item['fecha_vencimiento'])):"")."|";
                    $txtContent.= $item['tipo_comprobante']."|";
                    $txtContent.= $serie."|";
                    $txtContent.= $correlativo."|";
                    $txtContent.= "|";
                    $txtContent.= $tipo_documento_cliente."|";
                    $txtContent.= $numero_documento."|";
                    $txtContent.=  $item['nombre_cliente']."|";
                    $txtContent.= "|";
                    $txtContent.=  $item['afecto']."|";
                    $txtContent.= "|";
                    $txtContent.=  $item['IGV']."|";
                    $txtContent.= "|";
                    $txtContent.=  $item['exonerado']."|";
                    $txtContent.=  $item['no_afecto']."|";
                    $txtContent.= "|||||";
                    $txtContent.=  $item['total']."|";
                    $txtContent.=  $item['moneda']."|";
                    $txtContent.=  $item['tipo_cambio']."|";
                    $txtContent.=  ($item['fecha_modificacion'] != null?date('d/m/Y',strtotime($item['fecha_modificacion'])):'')."|";
                    $txtContent.= ($item['fecha_modificacion'] != null?$item['tipo_comprobante']:"")."|";
                    $txtContent.= $serieNC."|";
                    $txtContent.= $correlativoNC."|";
                    $txtContent.= "|";
                    $txtContent.= "\n";
                }
                $nombreArchivo = "LE".$this->session->userdata('ruc').date('Ym').'00140400021112';
                $archivoTxt = FCPATH . 'uploads/'.$nombreArchivo . '.txt';
                file_put_contents($archivoTxt, $txtContent);
                $archivoZip = FCPATH . 'uploads/'.$nombreArchivo . '.zip';
                $zip = new ZipArchive();
                if ($zip->open($archivoZip, ZipArchive::CREATE) === TRUE) {
                    $zip->addFile($archivoTxt, basename($archivoTxt));
                    $zip->close();
                } 
                $contenidoZipBase64 = base64_encode(file_get_contents($archivoZip));
                unlink($archivoTxt);
                unlink($archivoZip);
                $response =  array(
                    'status' => TRUE,
                    'file' => "data:application/zip;base64," . $contenidoZipBase64,
                    'filename' => $nombreArchivo.".zip",
                    'result' => 'ok',
                    'mensaje' => "Reporte generado.",
                );
            }else{
                $response =  array(
                    'status' => TRUE,
                    'file' => null,
                    'filename' => null,
                    'result' => 'failed',
                    'mensaje' => 'No existe información para el periodo seleccionado.'
                );
            }
            die(json_encode($response));

        }
        


    }

    public function obtenerReporte($filtros, $pestañas){

        $curl = curl_init();
        $data['tab'] = $pestañas;
        $data['filters'] = $filtros;
        $data['business_information'] = array(
            "ruc_number" => $this->session->userdata('ruc'),
        );
        
        $url = $this->config->item('api_ventas');
        curl_setopt_array($curl, array(
        CURLOPT_URL => $url.'reports/sales_report',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_ENCODING => '',
        CURLOPT_MAXREDIRS => 10,
        CURLOPT_TIMEOUT => 0,
        CURLOPT_FOLLOWLOCATION => true,
        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
        CURLOPT_CUSTOMREQUEST => 'GET',
        CURLOPT_POSTFIELDS =>json_encode($data),
        CURLOPT_HTTPHEADER => array(
            'Content-Type: application/json'
        ),
        ));

        $response = curl_exec($curl);
        curl_close($curl);
        $res = json_decode($response, true);
        return $res['data'];

    }
    public function getAllCustomers()
	{
		$urlapi_venta = $this->config->item('api_ventas');
		$url_customer = $urlapi_venta . "customer";
		$headers = array(
			'Content-Type:application/json',
			'Accept: application/json'
		);
		$payload = array(
			'business_information' => array(
				'ruc_number' => $this->session->userdata('ruc')
			),
      "tipo_listado"=>2
		);
		$url = sprintf("%s?%s", $url_customer, http_build_query($payload));
		$process = curl_init($url); //your API url
		curl_setopt($process, CURLOPT_HTTPHEADER, $headers);
		curl_setopt($process, CURLOPT_RETURNTRANSFER, TRUE);
		$return = curl_exec($process);
		curl_close($process);

		$customer_list = json_decode($return, false);
		return $customer_list->data;
	}
}
