<?php

class ReportsListRepository extends EntityRepository
{
    public function getInventory()
    {
        //$settings = new SettingsRepository();
        // $categoryForSupplie = $settings->_get('id_category_for_supplies');


        $login = new Login();

        $areaRepo = new AreaRepository();
        if ($login->getRole() != '1') {
        }


        $repo = new StoreRepository();
        $sucursales = $repo->getListActiveStores();

        $case = '';
        foreach ($sucursales as $sucursal) {
            $case .= " sum(CASE WHEN id_store = '{$sucursal['id']}' THEN i.stock ELSE 0 END) as '{$sucursal['id']}',";
            $arraySucursales[$sucursal['id']] = $sucursal['name'];
        }

        $case = trim($case, ',');
        $query = "SELECT "
            . "p.id as id_product,"
            . "p.code,"
            . "p.description,"
            . "fxGetPresentationDescription(p.presentation)as presentation,"
            . "fxGetUMDescription(p.unit_of_measurement_inventory)as um," //UG lo cambio de unit_of_measurement a unit_of_measurement_inventory por requerimiento de unidad de medida en formulario de productos (modal)
            . "fxGetCategoryDescription(p.category)as category,"
            . "fxGetBrandDescription(p.brand)as brand,"
            . "p.cost,"
            . "$case "
            . "FROM products p "
            . "LEFT JOIN inventory i ON p.id = i.id_product "
            . "WHERE type = 'product' "
            . "AND p.status = 1 "
            // . "AND p.category = '$categoryForSupplie' "
            . "GROUP BY p.id ORDER BY category,description ASC";

        $result = $this->query($query);

        if ($result->num_rows > 0) {
            //echo "<pre>";var_dump($this->resultToArray($result));echo "</pre>";exit;
            return array(
                'data' => $this->resultToArray($result),
                'stores' => $arraySucursales
            );
        } else {

            null;
        }
    }



    public function getSpecialProductionPlan($options)
    {

        $idsRequisitions = implode(',', $options['special_orders']);

        $repoSR = new SpecialOrderRepository();

        $requisitionsList = $repoSR->getListRequisitionsByIds($idsRequisitions);

        $requisitionsDetailsList = $repoSR->getSpecialProductionPlan($idsRequisitions);



        if ($requisitionsList == null) {

            return null;
        }



        return array(

            'requisitionsList' => $requisitionsList,

            'requisitionsDetailsList' => $requisitionsDetailsList

        );
    }



    public function getBakedPlan($options)
    {

        $detalles = array();

        $listRequisitions = null;

        $specialDetalles = array();

        $listSpecialRequisitions = null;

        $globalDetalles = null;



        /*

         if($options['requisitions']){

            $requisition = new RequisitionEntity();

            $idRequisitions = implode(',', $options['requisitions']);

            $detalles = $requisition->getBakedPlan($idRequisitions);

            $listRequisitions = $requisition->getListRequisitionsByIds($idRequisitions);



            if($detalles){

                $detallesTemp = null;

                foreach($detalles as $detalle){

                    $detallesTemp[$detalle['id_slice']] = $detalle;

                    $globalDetalles[$detalle['id_slice']] = $detalle;

                }

                $detalles = $detallesTemp;

            }else{

                $detalles = array();

            }

        }*/



        if ($options['special_orders']) {

            $specialRequisition = new SpecialOrderRepository();

            $idRequisitions = implode(',', $options['special_orders']);

            $specialDetalles = $specialRequisition->getBakedPlan($idRequisitions);

            $listSpecialRequisitions = $specialRequisition->getListRequisitionsByIds($idRequisitions);



            if ($specialDetalles) {

                $specialDetallesTemp = null;

                foreach ($specialDetalles as $specialDetalle) {

                    $specialDetallesTemp[$specialDetalle['id_slice']] = $specialDetalle;

                    $globalDetalles[$specialDetalle['id_slice']] = $specialDetalle;
                }

                $specialDetalles = $specialDetallesTemp;
            } else {

                $specialDetalles = array();
            }
        }



        return array(

            'globalDetalles' => $globalDetalles,

            'requisitionsDetalles' => $detalles,

            'specialRequisitionsDetalles' => $specialDetalles,

            'listRequisitions' => $listRequisitions,

            'listSpecialRequisitions' => $listSpecialRequisitions

        );
    }



    public function getStoreRequest($options)
    {

        $storeArray = array();

        $date = null;

        $area_bakery_production_id = null;

        $masa = null;



        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'r.delivery_date');

        if (isset($options['area_id']) && is_array($options['area_id']) && count($options['area_id']) > 0) {

            $area_bakery_production_ids = implode(',', $options['area_id']);

            $area_bakery_production_id = " AND find_in_set(r.area_id,'$area_bakery_production_ids')";
        } elseif (isset($options['area_id']) && trim($options['area_id']) !== '' && trim($options['area_id']) !== '0' && !is_null($options['area_id'])) {

            $area_bakery_production_id = " AND find_in_set(r.area_id,'{$options['area_id']}')";
        }



        if (isset($options['masa']) && is_array($options['masa']) && count($options['masa']) > 0) {

            $masas = implode(',', $options['masa']);

            $masa = " AND find_in_set(p.masa,'$masas')";
        } elseif (isset($options['masa']) && trim($options['masa']) !== '' && trim($options['masa']) !== '0' && !is_null($options['masa'])) {

            $masa = " AND find_in_set(p.masa,'{$options['masa']}')";
        }



        /*$repo = new StoreRepository();
        $sucursales = $repo->getListStores();*/

        $repo = new StoreRepository();
        $sucursales = $repo->getListStoresForBakeryProductionReport();



        $case = '';
        foreach ($sucursales as $key => $name) {
            $case .= "sum(CASE WHEN r.store_id = '{$key}' THEN d.quantity ELSE 0 END) as '{$key}',";
            $arraySucursales[$key] = $name;
        }

        $case = trim($case, ',');

        $query = "SELECT "

            . "d.id as id_product, "

            . "p.description, "

            . "fxGetSizeDescription(d.id_size)as size, "

            . "$case "

            . "FROM store_request r "

            . "LEFT JOIN store_request_details d ON r.id = d.id_store_request "

            . "LEFT JOIN products p ON d.id_product = p.id "

            . "LEFT JOIN masas m ON p.masa = m.id  "

            . "WHERE r.status != '2' "

            . "$date "

            . "$area_bakery_production_id "

            . "$masa "

            . "GROUP BY d.id_product "

            . "ORDER BY p.description ASC ";


        //echo $query;exit;
        $result = $this->query($query);



        if ($result->num_rows > 0) {

            return array(

                'data' => $this->resultToArray($result),

                'stores' => $arraySucursales

            );
        } else {

            null;
        }
    }



    public function getTimeClock($options = null)
    {

        $user  = null;

        $date = null;

        $store_id = null;

        $limit = null;



        if ($options != null) {

            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 't.date');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {

                //$store_ids = implode(',', $options['store_id']);

                //$store_id = " AND find_in_set(t.store_id,'$store_ids')";



                $store_id = "AND (";

                $storeIdArray = $options['store_id'];

                foreach ($storeIdArray as $key => $storeId) {

                    $store_id .= " find_in_set($storeId,t.store_id) OR ";
                }



                $store_id = trim($store_id, " OR ");

                $store_id .= ") ";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set({$options['store_id']},t.store_id)";
            }



            if (isset($options['id_user']) && is_array($options['id_user']) && count($options['id_user']) > 0) {

                $userIds = implode(',', $options['id_user']);

                $user = " AND find_in_set(t.id_user,'$userIds')";
            }
        } else {

            $limit = " LIMIT 1000 ";
        }



        $select = "SELECT

                    t.id,

                    t.id_user,

                    t.approved,

                    t.date as format_date_for_sort,

                    date_format(t.date,'%c/%d/%Y')as date,

                    fxGetStoreName(t.store_id)as sucursalName,

                    date_format(t.check_in,'%r')as check_in,

                    date_format(t.check_out,'%r')as check_out,

                    fxGetUserName(t.id_user)as userName,

                    timediff(t.check_out,t.check_in)as total_work,

                    ROUND(((time_to_sec(timediff(t.check_out,t.check_in)))/60)/60 ,2) as total,
                    u.phone

                    from timeclock t, users u

                    WHERE t.id_user = u.id

                    $date $user $store_id

                    ORDER BY u.last_name ASC, u.name ASC";

        //echo $select;exit;

        $result = $this->query($select);



        if ($result->num_rows > 0) {

            $result = $this->resultToArray($result);



            $data = null;

            foreach ($result as $row) {

                $data[$row['id_user']][] = $row;
            }



            return array(

                'data' => $data

            );
        }

        return null;
    }



    public function getSales($options = null)
    {

        $date = null;

        $store_id = null;

        $limit = null;



        if ($options != null) {

            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(date)');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {

                $store_ids = implode(',', $options['store_id']);

                $store_id = " AND find_in_set(store_id,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set(store_id,'{$options['store_id']}')";
            }
        } else {

            $limit = " LIMIT 1000 ";
        }



        $query = "SELECT *,"

            . "date_format(date,'%c/%d/%Y')as dateFormated,"

            . "fxGetStoreName(store_id)as storeName, "

            . "(final_cash + debit_card  + credit_card + door_dash + `check` + stamp )as total_sales,"

            . "status,"

            . "fxGetStatusName(status,'SalesRecord')as status_name "

            . "FROM sales_record "

            . "WHERE 1 = 1 "

            . "$store_id "

            . "$date "

            . "$limit "

            . "ORDER BY date ASC";



        // echo $query;exit;



        $result = $this->query($query);



        if ($result->num_rows > 0) {

            $data = null;

            foreach ($result as $row) {

                $data[$row['storeName']][] = $row;
            }



            return array(

                'data' => $data

            );
        }

        return null;
    }
    public function getMermas($options = null)
    {
        $date = null;
        $store_id = null;
        $limit = null;
        if ($options != null) {

            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(creado_fecha)');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(idSucursal,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {
                $store_id = " AND find_in_set(idSucursal,'{$options['store_id']}')";
            }
        } else {
            $limit = " LIMIT 1000 ";
        }

        $query = "SELECT *,"
            . "date_format(creado_fecha,'%c/%d/%Y')as dateFormated,"

            . "fxGetStoreName(idSucursal)as storeName "

            . "FROM waste "

            . "WHERE 1 = 1 "

            . "$store_id "

            . "$date "

            . "$limit "

            . "ORDER BY creado_fecha ASC";

        //var_dump($query);exit;
        $result = $this->query($query);
        if ($result->num_rows > 0) {
            $data = null;
            foreach ($result as $row) {
                $data[$row['storeName']][] = $row;
            }

            return array(
                'data' => $data
            );
        }
        return null;
    }

    public function getSpecialOrders($options)
    {

        $date = null;

        $store_id = null;

        $limit = null;



        if ($options != null) {

            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'date');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {

                $store_ids = implode(',', $options['store_id']);

                $store_id = " AND find_in_set(store_id,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set(store_id,'{$options['store_id']}')";
            }
        } else {

            $limit = " LIMIT 1000 ";
        }



        $query = "SELECT *,"

            . "date_format(date,'%c/%d/%Y')as dateFormated,"

            . "fxGetCustomerName(customer)as customerName, "

            . "phone,"

            . "ammount,"

            . "ammount_payments,"

            . "fxGetStoreName(store_id)as storeName "

            . "FROM special_orders WHERE 1 = 1 $store_id $date $limit ORDER BY date ASC";

        $result = $this->query($query);



        if ($result->num_rows > 0) {

            $data = null;

            foreach ($result as $row) {

                $data[$row['storeName']][] = $row;
            }



            return array(

                'data' => $data

            );
        }

        return null;
    }



    public function getInventoryTemplatePDF()
    {

        $settings = new SettingsRepository();

        $id_categories_of_products_in_physical_inventory = $settings->_get('id_categories_of_products_in_store_request');



        $query = "SELECT description,category,fxGetSizeDescription(size)as sizeName

                    FROM products

                    WHERE status = '1' "

            . "AND show_on_store_request = '1' "

            . "AND find_in_set(category,'$id_categories_of_products_in_physical_inventory') "

            . "ORDER BY description ASC";



        $result = $this->query($query);

        if ($result->num_rows > 0) {

            $result = $this->resultToArray($result);

            $areaRepo = new AreaRepository();

            $categoryArea = $areaRepo->getArrayCategoryArea();



            foreach ($result as $row) {

                $area = $categoryArea[$row['category']];

                $array[$area['name']][] = $row;
            }



            return array(

                'data' => $array

            );
        }
    }



    public function getPhysicalInventory($options)
    {

        $tools = new Tools();

        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'i.date');



        $startDate = new DateTime($tools->setFormatDateToDB($options['startDate']));

        $endDate = new DateTime($tools->setFormatDateToDB($options['endDate']));



        $stringCASE = "";

        $rangeDays = array();

        if ($startDate->format('Y-m-d') != $endDate->format('Y-m-d')) {

            while ($startDate->format('Y-m-d') != $endDate->format('Y-m-d')) {

                $stringCASE .= "CASE WHEN i.date = '{$startDate->format('Y-m-d')}' THEN quantity ELSE 0 END as '{$startDate->format('m/d/Y')}',";

                $rangeDays[] = $startDate->format('m/d/Y');

                $startDate->add(new DateInterval('P1D'));
            }

            /*EL IF deberia ser mientras startDate < endDate; pero por tiempo mejor lo deja asi, y agregue estas dos linea para que agregue cuando sea igual*/

            $rangeDays[] = $startDate->format('m/d/Y');

            $stringCASE .= "CASE WHEN i.date = '{$startDate->format('Y-m-d')}' THEN quantity ELSE 0 END as '{$startDate->format('m/d/Y')}',";
        } else {

            $rangeDays[] = $startDate->format('m/d/Y');

            $stringCASE = "CASE WHEN i.date = '{$startDate->format('Y-m-d')}' THEN quantity ELSE 0 END as '{$startDate->format('m/d/Y')}'";
        }



        $stringCASE = trim($stringCASE, ',');

        $query = "SELECT "

            . "p.code,"

            . "p.description as product,"

            . "fxGetSizeDescription(p.size)AS size,"

            . "fxGetCategoryDescription(p.category)AS category,"

            . "$stringCASE "

            . "FROM physical_inventory i "

            . "LEFT JOIN physical_inventory_details d ON i.id = d.id_physical_inventory "

            . "LEFT JOIN products p ON d.id_product = p.id "

            . "WHERE i.status = 1 "

            . "$date "

            . "ORDER BY category ASC, fxGetProductName(d.id_product) ASC";



        $result = $this->query($query);

        if ($result->num_rows > 0) {

            return array(

                'data' => $this->resultToArray($result),

                'rangeDays' => $rangeDays

            );
        }
    }



    public function getBakeryProduction($options)
    {
        if ($options['startDate'] == '') {
            return null;
        }
        $storeArray = array();
        $date = null;
        $area_bakery_production_id = null;

        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'r.delivery_date');

        if (isset($options['area_bakery_production_id']) && is_array($options['area_bakery_production_id']) && count($options['area_bakery_production_id']) > 0) {
            $area_bakery_production_ids = implode(',', $options['area_bakery_production_id']);
            $area_bakery_production_id = " AND find_in_set('$area_bakery_production_ids',p.area_bakery_production_id)";
        } elseif (isset($options['area_bakery_production_id']) && trim($options['area_bakery_production_id']) !== '' && trim($options['area_bakery_production_id']) !== '0' && !is_null($options['area_bakery_production_id'])) {
            $area_bakery_production_id = " AND find_in_set('{$options['area_bakery_production_id']}',p.area_bakery_production_id)";
        }

        $repo = new StoreRepository();
        $sucursales = $repo->getListStoresForBakeryProductionReport();

        $case = '';
        foreach ($sucursales as $key => $name) {
            $case .= "sum(CASE WHEN r.store_id = '{$key}' THEN d.quantity ELSE 0 END) as '{$key}',";
            $arraySucursales[$key] = $name;
        }

        /*
        $repo = new StoreRepository();
        $sucursales = $repo->getListStores();

        $case = '';
        foreach($sucursales as $key => $storeData){
                if($storeData['show_on_bakery_report'] == '1'){
                        $case .= "sum(CASE WHEN r.store_id = '{$storeData['id']}' THEN d.quantity ELSE 0 END) as '{$storeData['id']}',";
                        $arraySucursales[$key] = $storeData['name'];
                }
        }    */

        $case = trim($case, ',');
        $query = "SELECT "
            . "d.id as id_product, "
            . "p.description, "
            . "fxGetSizeDescription(d.id_size)as size, "
            . "$case,"
            . "p.masa "
            . "FROM store_request r "
            . "LEFT JOIN store_request_details d ON r.id = d.id_store_request "
            . "LEFT JOIN products p ON d.id_product = p.id "
            . "WHERE r.status != '2' "
            . "$date "
            . "$area_bakery_production_id "
            . "GROUP BY d.id_product "
            . "ORDER BY p.description ASC ";

        //echo $query;exit;

        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);
            $areaName = null;

            if ($options['area_bakery_production_id'] != '') {
                $areaRepo = new AreaRepository();
                $areaProduccionPanaderia = $areaRepo->getAreaProduccionPanaderiaById($options['area_bakery_production_id']);
                $areaName = $areaProduccionPanaderia['name'];
            }

            /*Condicion para paÃ±o*/
            $dataPanyo = array();
            $settings = new SettingsRepository();

            if ($settings->_get('id_production_for_panyo') == $options['area_bakery_production_id']) {
                $masasList = $this->query("SELECT * FROM masas");
                $masasList = $this->resultToArray($masasList);
                foreach ($masasList as $masa) {
                    $masasArray[$masa['id']] = $masa['description'];
                }


                foreach ($result as $row) {
                    $dataPanyo[$row['masa']][] = $row;
                    foreach ($sucursales as $key => $storeData) {
                        if (key_exists($storeData['id'], $row)) {
                            $dataPanyo[$row['masa']]['total_pano_para_masa'] += $row[$storeData['id']];
                        }
                    }
                }
            }

            //echo "<pre>";var_dump($array);echo "</pre>";exit;
            /*COMENTARIOS*/

            $query = "SELECT "
                . "fxGetStoreName(r.store_id)as store_name,"
                . "r.comments "
                . "FROM store_request r "
                . "LEFT JOIN store_request_details d ON r.id = d.id_store_request "
                . "LEFT JOIN products p ON d.id_product = p.id "
                . "WHERE r.status != '2' "
                . "$date "
                . "$area_bakery_production_id "
                . "GROUP BY r.id "
                . "ORDER BY p.description ASC ";

            $result2 = $this->query($query);

            $commentsArray = null;
            if ($result2->num_rows > 0) {
                $result2 = $this->resultToArray($result2);
                foreach ($result2 as $row) {
                    $commentsArray[] = $row;
                }
            }

            return array(
                'data' => $result,
                'comments' => $commentsArray,
                'dataPanyo' => $dataPanyo,
                'stores' => $arraySucursales,
                'areaBakeryProduction' => $areaName,
                'masasList' => $masasArray
            );
        } else {
            null;
        }
    }



    public function getDetailedBakeryOrders($options)
    {
        $store_id = null;
        $area_id = null;
        $filterDate = null;

        $settings = new SettingsRepository();
        $area_id = $settings->_get('id_area_for_pasteles_vitrina');


        $filterDate = $this->createFilterFecha($options['startDate'], $options['endDate'], 's.delivery_date');
        if (isset($options['store_id']) && $options['store_id'] != 0 && !is_null($options['store_id'])) {
            $store_id = " AND s.store_id = '{$options['store_id']}'";
        }

        $query = "SELECT DISTINCT(delivery_date) FROM store_request s WHERE 1 = 1 $filterDate $store_id ORDER BY delivery_date ASC";

        $result = $this->query($query);
        if ($result->num_rows > 0) {
            $requestDates = $this->resultToArray($result);
            $stringRequestDates = '';
            $stringRequestDates2 = '';

            foreach ($requestDates as $key => $date) {

                $stringRequestDates .= "CASE WHEN delivery_date = '{$date['delivery_date']}' THEN requested ELSE 0 END as '{$date['delivery_date']}_requested',";

                $stringRequestDates .= "CASE WHEN delivery_date = '{$date['delivery_date']}' THEN received ELSE 0 END as '{$date['delivery_date']}_received',";
            }



            foreach ($requestDates as $key => $date) {

                $dateFormated = date_create($date['delivery_date']);

                $dateFormated = date_format($dateFormated, 'm/d/Y'); /*Aplico formato a la fecha, solo es necesario hacerlo aqui*/

                $stringRequestDates2 .= "MAX(`{$date['delivery_date']}_requested`) as '{$dateFormated}_requested',";

                $stringRequestDates2 .= "MAX(`{$date['delivery_date']}_received`) as '{$dateFormated}_received',";
            }



            $stringRequestDates = trim($stringRequestDates, ',');

            $stringRequestDates2 = trim($stringRequestDates2, ',');



            $query = "SELECT product_id,product,$stringRequestDates2 "

                . "FROM (SELECT  product_id,product,"

                . "$stringRequestDates "

                . "FROM ("

                . "SELECT "

                . "p.id as product_id,"

                . "p.description as product, "

                . "s.delivery_date,"

                . "SUM(IFNULL(d.quantity,0))as requested,"

                . "SUM(IFNULL(ssd.received,0))as received "

                . "FROM store_request s "

                . "LEFT JOIN store_request_details d ON s.id = d.id_store_request "

                . "LEFT JOIN shipment_store_requests ss ON ss.id_store_request = s.id "

                . "LEFT JOIN shipment_store_requests_details ssd ON ssd.id_shipment = ss.id AND ssd.id_product = d.id_product  "

                . "LEFT JOIN products p ON d.id_product = p.id "

                . "WHERE s.status != '2' "

                . "$filterDate "

                . "$store_id "

                . " AND s.area_id != $area_id "

                . "GROUP BY d.id_product,s.delivery_date "

                . "ORDER BY s.delivery_date ASC"

                . ")as t )as t2 GROUP BY product_id";



            $result = $this->query($query);



            if ($result->num_rows > 0) {

                return array(

                    'data' => $this->resultToArray($result),

                    'requestDates' => $requestDates

                );
            }
        }

        return null;
    }

    public function getSalesToStore($options)
    {
        $date = null;
        $store_id = null;
        $limit = null;
        $storeData = array();

        // var_dump($options);exit;
        if ($options != null) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(date)');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(to_store,'$store_ids')";
                $storeRepo = new StoreRepository();
                foreach ($options['store_id'] as $storeId) {
                    $storeData[$storeId] = $storeRepo->getById($storeId); //UG Guardamos los datos de mas de una sucursal
                }
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {
                $store_id = " AND find_in_set(to_store,'{$options['store_id']}')";
                $storeRepo = new StoreRepository();
                $storeData = $storeRepo->getById($options['store_id']);
            }
            //echo "<pre>";var_dump($storeData);echo "</pre>";exit;

        } else {
            $limit = " LIMIT 1000 ";
        }

        $query = "SELECT "

            . "p.description,"
            . "p.category_for_sales_report,"
            . "SUM(IFNULL(sd.quantity,0))as quantity,"
            . "SUM(IFNULL(sd.received,0))as received,"
            . "IFNULL(p.sale_price,0)as sale_price, " //UG CAMBIO DE cost a sale_price 03.sep.24
            . " fxGetStoreName(to_store) storeName "
            . "FROM shipment_store_requests s "
            . "LEFT JOIN shipment_store_requests_details sd ON sd.id_shipment = s.id "
            . "LEFT JOIN products p ON  sd.id_product = p.id  "
            . "WHERE s.status != 4 "
            . "$date "
            . "$store_id "
            . "$limit "
            . "GROUP BY sd.id_product, to_store";
        #var_dump($query );exit;
        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);

            $categoryRepo = new CategoryRepository();
            $categoryList = $categoryRepo->getListSelectCategories('sales report');



            return array(
                'data' => $result,
                'storeName' => $storeData,
                'category_for_sales_report_list' => $categoryList
            );
        }

        return null;
    }

    public function getResumeSalesToStore($options)
    {
        $date = null;
        $store_id = null;
        $limit = null;
        $storeData = array();

        // var_dump($options);exit;
        if ($options != null) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(date)');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(to_store,'$store_ids')";
                $storeRepo = new StoreRepository();
                foreach ($options['store_id'] as $storeId) {
                    $storeData[$storeId] = $storeRepo->getById($storeId); //UG Guardamos los datos de mas de una sucursal
                }
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {
                $store_id = " AND find_in_set(to_store,'{$options['store_id']}')";
                $storeRepo = new StoreRepository();
                $storeData = $storeRepo->getById($options['store_id']);
            }
            //echo "<pre>";var_dump($storeData);echo "</pre>";exit;

        } else {
            $limit = " LIMIT 1000 ";
        }

        $query = "SELECT "

            // . "p.description,"
            // . "p.category_for_sales_report,"
            // . "SUM(IFNULL(sd.quantity,0))as quantity,"
            // . "SUM(IFNULL(sd.received,0))as received,"
            // . "IFNULL(p.cost,0)as sale_price, fxGetStoreName(to_store) storeName, to_store "
            // . "FROM shipment_store_requests s "
            // . "LEFT JOIN shipment_store_requests_details sd ON sd.id_shipment = s.id "
            // . "LEFT JOIN products p ON  sd.id_product = p.id  "
            // . "WHERE s.status != 4 "
            . "p.description,p.category_for_sales_report, "
            . "SUM(IFNULL(sd.quantity,0))as quantity, "
            . "SUM(IFNULL(sd.received,0))as received, "
            . "IFNULL(p.sale_price,0)as sale_price, " //UG CAMBIO DE cost a sale_price 03.sep.24
            . "fxGetStoreName(to_store) storeName,to_store "
            . "FROM shipment_store_requests s LEFT JOIN shipment_store_requests_details sd ON sd.id_shipment = s.id "
            . "LEFT JOIN products p ON sd.id_product = p.id WHERE s.status != 4 "

            . "$date "
            . "$store_id "
            . "$limit "
            . "GROUP BY sd.id_product, to_store";
        #var_dump($query);exit;
        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);

            $categoryRepo = new CategoryRepository();
            $categoryList = $categoryRepo->getListSelectCategories('sales report');



            return array(
                'data' => $result,
                'storeName' => $storeData,
                'category_for_sales_report_list' => $categoryList
            );
        }

        return null;
    }

    public function getTypePaymentByStore($options)
    {
        $date = null;
        $store_id = null;
        $limit = null;
        $storeData = array();


        if ($options != null) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(cp.date)');

            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(inv.billed_to_store,'$store_ids')";
                $storeRepo = new StoreRepository();
                foreach ($options['store_id'] as $storeId) {
                    $storeData[$storeId] = $storeRepo->getById($storeId); //UG Guardamos los datos de mas de una sucursal
                }
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {
                $store_id = " AND find_in_set(inv.billed_to_store,'{$options['store_id']}')";
                $storeRepo = new StoreRepository();
                $storeData = $storeRepo->getById($options['store_id']);
            }
            //echo "<pre>";var_dump($storeData);echo "</pre>";exit;

        } else {
            $limit = " LIMIT 1000 ";
        }

        $query = "SELECT "

            . "cp.* "
            . ",spt.description,cpd.invoice_id,inv.id,inv.billed_to_store, "
            . "(total - payments) as balance, "
            . "fxGetStoreName(inv.billed_to_store)as store_name, "
            . "inv.billed_to_store as store_id "
            . "FROM customer_payments cp "
            . "JOIN store_payment_type spt ON cp.store_payment_type_id = spt.id "
            . "JOIN customer_payment_details cpd ON cp.id = cpd.payment_id "
            . "JOIN invoices inv on cpd.invoice_id = inv.id "
            . "WHERE 1 = 1 "
            . "$date "
            . "$store_id "
            . "$limit "
            . "GROUP BY spt.id, inv.billed_to_store";
        //var_dump($query);exit;
        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);

            $customerPaymentRepo = new CustomerPaymentRepository();
            $typePaymentList = $customerPaymentRepo->getListTypePaymentByStore('Pago de sucursal');



            return array(
                'data' => $result,
                'storeName' => $storeData,
                'category_for_sales_report_list' => $typePaymentList
            );
        }

        return null;
    }

    public function getSalesByStore($options)
    {
        $date = null;
        $store_id = null;
        $limit = null;

        if ($options != null) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(v.fecha)');
            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(v.idSucursal,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set(v.idSucursal,'{$options['store_id']}')";
                $storeRepo = new StoreRepository();
                $storeData = $storeRepo->getById($options['store_id']);
            }
        } else {
            $limit = " LIMIT 1000 ";
        }

        $query = "SELECT v.*, "

            . "fxGetStoreName(v.idSucursal)as store_name,"

            . "DATE_FORMAT(v.fecha,'%m/%d/%Y')as formated_date, "

            //. "_tipoPago.descripcion as metodo_pago_ "
            . " GROUP_CONCAT(DISTINCT _tipoPago.descripcion SEPARATOR ' / ') AS metodo_pago_ " //UG para poder agrupar ventas con diferentes metodo de pago
            . "FROM ventas v "

            . "LEFT JOIN cobros _cobros on v.idSucursal = _cobros.idSucursal and v.id_of_tbl_venta_in_store = _cobros.idVenta "

            . "LEFT JOIN formas_metodos_pago _tipoPago ON _cobros.formaPago = _tipoPago.id "

            . "WHERE v.status != 3 "

            . "$date "

            . "$store_id "

            . " GROUP BY     v.num_venta " //UG necesita agrupar por v.id

            . "$limit ";


        //var_dump($query);exit;
        $result = $this->query($query);



        if ($result->num_rows > 0) {

            $result = $this->resultToArray($result);



            /*detalles de venta*/

            $queryDetalles = "SELECT vd.*, "

                . "vd.precio_sin_impuestos * vd.cantidad as importe,"

                . "vd.descuento_item_monto + vd.descuento_orden_monto as descuento,"

                . "v.num_venta,"

                . "fxGetStoreName(v.idSucursal)as store_name,"

                . "DATE_FORMAT(v.fecha,'%m/%d/%Y')as formated_date, "

                . "_tipoPago.descripcion as metodo_pago_ "

                . "FROM ventas v "

                . "LEFT JOIN ventasdetalles vd ON vd.ticket = v.ticket "

                . "LEFT JOIN cobros _cobros on v.idSucursal = _cobros.idSucursal and v.id_of_tbl_venta_in_store = _cobros.idVenta "

                . "LEFT JOIN formas_metodos_pago _tipoPago ON _cobros.formaPago = _tipoPago.id "


                . "WHERE v.status != 3 "

                . "$date "

                . "$store_id "

                . "$limit ";



            $resultDetalles = $this->query($queryDetalles);

            if ($resultDetalles->num_rows > 0) {

                $resultDetalles = $this->resultToArray($resultDetalles);

                $arrayDetalles = array();



                foreach ($resultDetalles as $detalle) {

                    $total = round($detalle['importe'], 2) - round($detalle['descuento'], 2) + round($detalle['impuestos_monto'], 2);

                    $detalle['total'] = $total;

                    $arrayDetalles[] = $detalle;
                }
            }



            return array(

                'data' => $result,

                'details' => $arrayDetalles,

                'storeName' => $storeData['name']

            );
        }



        return null;
    }



    public function getReviewPayroll($options)
    {

        $data = $this->getTimeClock($options);

        $groupByEmployee = array();



        if ($data['data']) {

            $data = $data['data'];

            foreach ($data as $user_id => $info) {

                foreach ($info as $row) {

                    if (key_exists($user_id, $groupByEmployee)) {

                        $groupByEmployee[$row['id_user']]['total'] += round($row['total'], 2);
                    } else {

                        $groupByEmployee[$row['id_user']] = array('name' => $row['userName'], 'phone' => $row['phone'], 'storeName' => $row['sucursalName'], 'total' => round($row['total'], 2));
                    }
                }
            }



            return array(

                'data' => $data,

                'groupByEmployee' => $groupByEmployee

            );
        }



        return null;
    }
    public function getinventoryByDate($options)
    {
        $user  = null;
        $date = null;
        $store_id = null;
        $limit = null;

        if ($options != null) {
            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(id,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set(id,'{$options['store_id']}')";
            }
        }

        $repo = new StoreRepository();
        $sucursales = $repo->getListActiveStoresByID($store_id);

        $case = '';
        foreach ($sucursales as $sucursal) {
            $case .= " sum(CASE WHEN id_store = '{$sucursal['id']}' THEN i.stock ELSE 0 END) as '{$sucursal['id']}',";
            $arraySucursales[$sucursal['id']] = $sucursal['name'];
        }

        if ($options != null) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(i.fecha_daily)');
            if (isset($options['store_id']) && is_array($options['store_id']) && count($options['store_id']) > 0) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(i.id_store,'$store_ids')";
            } elseif (isset($options['store_id']) && trim($options['store_id']) !== '' && trim($options['store_id']) !== '0' && !is_null($options['store_id'])) {

                $store_id = " AND find_in_set(i.id_store,'{$options['store_id']}')";
                $storeRepo = new StoreRepository();
                $storeData = $storeRepo->getById($options['store_id']);
            }
        } else {
            $limit = " LIMIT 1000 ";
        }


        $case = trim($case, ',');
        $query = "SELECT "
            . "p.id as id_product,"
            . "p.code,"
            . "p.description,"
            . "fxGetPresentationDescription(p.presentation)as presentation,"
            . "fxGetUMDescription(p.unit_of_measurement_inventory)as um," //UG lo cambio de unit_of_measurement a unit_of_measurement_inventory por requerimiento de unidad de medida en formulario de productos (modal)
            . "fxGetCategoryDescription(p.category)as category,"
            . "fxGetBrandDescription(p.brand)as brand,"
            . "p.cost,"
            . "$case "
            . "FROM products p "
            . "LEFT JOIN inventory_daily i ON p.id = i.id_product "
            . "WHERE type = 'product' "
            . "AND p.status = 1 $date $store_id "
            // . "AND p.category = '$categoryForSupplie' "
            . "GROUP BY p.id ORDER BY category,description ASC";

        $result = $this->query($query);

        if ($result->num_rows > 0) {
            return array(
                'data' => $this->resultToArray($result),
                'stores' => $arraySucursales,
            );
        } else {

            null;
        }
    }


    public function getInvoices($options)
    {

        $invoiceRepo = new InvoiceRepository();

        $list = $invoiceRepo->getListInvoice($options);



        if ($list) {

            return array('data' => $list);
        }



        return null;
    }



    public function getDeposits($options)
    {
        $limit = null;
        $deposit_number = null;
        $store_id = null;
        $status = null;
        $user_id = null;
        $date = $this->createFilterFecha($options, "c.date");

        if ($options) {
            if (isset($options['deposit_number']) && trim($options['deposit_number']) != '') {
                $deposit_number = " AND find_in_set(c.deposit_number,'{$options['deposit_number']}')";
            }

            if (isset($options['user_id'])) {
                if (is_array($options['user_id']) && count($options['user_id']) > 0) {
                    $userIds = implode(',', $options['user_id']);
                    $user_id = " AND find_in_set(c.creado_por,'{$userIds}')";
                } else {
                    if (trim($options['user_id']) != '') {
                        $user_id = " AND find_in_set(c.creado_por,'{$options['user_id']}')";
                    }
                }
            }

            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(c.store_id,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(c.store_id,'{$options['store_id']}')";
                    }
                }
            }

            if (isset($options['status'])) {
                if (is_array($options['status']) && count($options['status']) > 0) {
                    $statusIds = implode(',', $options['status']);
                    $status = " AND find_in_set(c.status,'{$statusIds}')";
                } else {
                    if (trim($options['status']) != '') {
                        $status = " AND find_in_set(c.status,'{$options['status']}')";
                    }
                }
            }

            if (
                is_null($date)
                && is_null($deposit_number)
                && is_null($store_id)
                && is_null($status)
                && is_null($user_id)
            ) {
                $limit = " LIMIT 1000";
            }
        } else {
            $limit = " LIMIT 1000";
        }

        $query = "SELECT

                    d.deposit_number,

                    DATE_FORMAT(d.date,'%m/%d/%Y')as date,

                    fxGetStoreName(d.store_id)as store_name,

                    GROUP_CONCAT(DATE_FORMAT(dd.sale_date,'%m/%d/%Y'))as sales_date,

                    d.total,

                    SUM(IFNULL(c.efectivoFinal,0))AS total_sale_from_cierre,

                    fxGetStatusName(d.status)as status_name

                    FROM deposits d

                    LEFT JOIN deposit_details dd ON dd.id_deposit = d.id

                    LEFT JOIN apertura_cierre_cajas c ON DATE(c.fechaApertura) = dd.sale_date

                    WHERE 1 = 1

                    $date

                    $store_id

                    $deposit_number

                    $status

                    $user_id

                    GROUP BY d.id $limit";



        $result = $this->query($query);



        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);
            return array('data' => $result);
        }



        return null;
    }



    public function getUsersList($options)
    {

        $status = null;



        if (isset($options['status'])) {

            if (is_array($options['status']) && count($options['status']) > 0) {

                $statusIds = implode(',', $options['status']);

                $status = " AND find_in_set(status,'{$statusIds}')";
            } else {

                if (trim($options['status']) != '') {
                    $status = " AND find_in_set(status,'{$options['status']}')";
                }
            }
        }



        $query = "SELECT "

            . "employe_number,"

            . "CONCAT(name,' ',last_name)as complete_name,"

            . "user,"

            . "ssn,"

            . "DATE_FORMAT(creado_fecha,'%m/%d/%Y')as created_date,"

            . "DATE_FORMAT(alta_payroll,'%m/%d/%Y')as alta_payroll,"

            . "DATE_FORMAT(baja_payroll,'%m/%d/%Y')as baja_payroll,"

            . "fxGetStoreName(store_id)as store_name,"

            . "phone,"

            . "email,"

            . "address,"

            . "fxGetStatusName(status,'User')as status_name "

            . " FROM users WHERE 1 = 1 $status";

        $result = $this->query($query);



        if ($result->num_rows > 0) {

            $result = $this->resultToArray($result);

            return array('data' => $result);
        }



        return null;
    }

    public function getTaxablesSales($options)
    {
        $flashmessenger =  new Flashmessenger();
        $limit = null;
        $store_id = null;
        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(v.fecha)');

        if ($options) {
            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(v.idSucursal,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(v.idSucursal,'{$options['store_id']}')";
                    }
                }
            }

            if (is_null($date) && is_null($store_id)) {
                $flashmessenger->addMessage(array('danger' => 'Debes seleccionar una fecha de inicio y fin.'));
                return null;
            }
        }

        $query = "
            SELECT
                SUM(ROUND(v.importe,2))AS importe,
                SUM(ROUND(v.descuento_orden_monto + v.descuento_items_monto,2))AS descuentos,
                SUM(ROUND(v.subtotal,2))AS subtotal,
                SUM(ROUND(v.total_impuestos,2))AS impuestos,
                SUM(ROUND(v.total_venta,2))as total_venta
                FROM ventas v
            WHERE v.`status` != 3
            $store_id
            $date HAVING importe > 0";
        //echo $query;exit;
        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result)[0];

            /*Obtener solo total de ventas con taxas*/
            $queryTableSales = "
            SELECT
                SUM(ROUND(CAST(precio_sin_impuestos AS DECIMAL(8,2)) * CAST(cantidad AS DECIMAL(8,3)),2)) as taxables_sales
                FROM ventas v
                LEFT JOIN ventasdetalles d ON v.id_of_tbl_venta_in_store = d.idVenta AND v.idSucursal = d.idSucursal
                WHERE v.`status` != 3
                AND impuestos_tasa > 0
                $store_id
                $date";
            //echo $queryTableSales;exit;
            $resultTaxableSales = $this->query($queryTableSales);
            if ($resultTaxableSales->num_rows > 0) {
                $resultTaxableSales = $this->resultToArray($resultTaxableSales)[0];

                //var_dump($resultTaxableSales);exit;
            }

            /*Ventas agrupadas por forma de pago*/
            $formasPago = null;
            $queryFormaPago = "SELECT "
                . "_tipoPago.descripcion as metodo_pago_, "
                . "SUM(ROUND(_cobros.monto,2))as monto "
                . "FROM ventas v "
                . "LEFT JOIN cobros _cobros on v.idSucursal = _cobros.idSucursal and v.id_of_tbl_venta_in_store = _cobros.idVenta "
                . "LEFT JOIN formas_metodos_pago _tipoPago ON _cobros.formaPago = _tipoPago.id "
                . "WHERE v.status != 3 "
                . "$date "
                . "$store_id "
                . "GROUP BY _cobros.formaPago";
            //echo  $queryFormaPago;exit;
            $resultFormaPago = $this->query($queryFormaPago);
            if ($resultFormaPago->num_rows > 0) {

                $formasPago = $this->resultToArray($resultFormaPago);
            }

            return array('data' => $result, 'taxable_sales' => $resultTaxableSales, 'formas_pago' => $formasPago);
        }

        return null;
    }

    public function getSalesByTransaction($options)
    {
        $flashmessenger =  new Flashmessenger();
        $limit = null;
        $store_id = null;
        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(v.fecha)');

        if ($options) {
            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(v.idSucursal,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(v.idSucursal,'{$options['store_id']}')";
                    }
                }
            }

            if (is_null($date) && is_null($store_id)) {
                $flashmessenger->addMessage(array('danger' => 'Debes seleccionar una fecha de inicio y fin.'));
                return null;
            }
        }

        $query = "SELECT "
            . "fxGetStoreName(v.idSucursal)as store_name,"
            . "v.num_venta as sale_number,"
            . "DATE_FORMAT(v.fecha,'%m/%d/%Y')as date,"
            . "ROUND(v.importe,2) as amount,"
            . "ROUND(v.descuento_orden_monto + v.descuento_items_monto,2)as discounts,"
            . "ROUND(v.subtotal,2)as subtotal,"
            . "ROUND(v.total_impuestos,2)as taxes,"
            . "ROUND(v.total_venta,2)as total_sales,"
            . "GROUP_CONCAT(DISTINCT(m.descripcion))as payment_method,"
            . "DATE_FORMAT(v.creado_fecha,'%m/%d/%Y %h:%i:%s %p')as sale_time "
            . "FROM ventas v "
            . "LEFT JOIN cobros c on v.idSucursal = c.idSucursal and v.id_of_tbl_venta_in_store = c.idVenta "
            . "LEFT JOIN formas_metodos_pago m ON c.formaPago = m.id "
            . "WHERE v.status !=3 "
            . "$date "
            . "$store_id "
            . "GROUP BY v.id";
        //echo $query;exit;
        $result = $this->query($query);


        if ($result->num_rows > 0) {
            $sales_transaction = $this->resultToArray($result);

            return array(
                'sales_transactions' => $sales_transaction,
                'summary' => $this->getTaxablesSales($options)
            );
        }

        return null;
    }

    public function getSalesByItem($options)
    {

        $flashmessenger =  new Flashmessenger();
        $limit = null;
        $store_id = null;
        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(v.fecha)');

        if ($options) {
            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(v.idSucursal,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(v.idSucursal,'{$options['store_id']}')";
                    }
                }
            }

            if (is_null($date) && is_null($store_id)) {
                $flashmessenger->addMessage(array('danger' => 'Debes seleccionar una fecha de inicio y fin.'));
                return null;
            }
        }

        $query = "SELECT "
            . "SUM(ROUND(d.cantidad,2))as quantity,"
            . "d.descripcion as item,"
            . "ROUND(SUM(d.precio_sin_impuestos)/SUM(d.cantidad),2) as ave_price,"
            . "SUM(ROUND(CAST(precio_sin_impuestos AS DECIMAL(8,2)) * CAST(cantidad AS DECIMAL(8,3)),2)) as amount,"
            . "SUM(ROUND(CAST(d.descuento_orden_monto as DECIMAL(8,4)),2) + ROUND(CAST(d.descuento_item_monto as DECIMAL(8,4)),2)     )AS discounts,"
            . "SUM(ROUND(d.impuestos_monto,2))as taxes, "
            . " v.idSucursal "
            . "FROM ventas v "
            . "LEFT JOIN ventasdetalles d ON d.ticket = v.ticket "
            . "WHERE v.status !=3 "
            . "$date "
            . "$store_id "
            . "GROUP BY d.idProducto , v.idSucursal ";
        //echo $query;exit;
        $result = $this->query($query);
        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);
            $sales_items = array();
            foreach ($result as $row) {
                $row['subtotal'] = round((float)$row['amount'], 2) - round((float)$row['discounts'], 2);
                $row['total'] = $row['subtotal'] + (float)$row['taxes'];
                $sales_items[] = $row;
            }

            $ave_sales_query = "SELECT "
                . "COUNT(id)as quantity_sales,"
                . "SUM(total_venta)as total_sales,"
                . "SUM(total_venta)/COUNT(id)as ave_sales "
                . "FROM ventas v "
                . "WHERE status !=3 "
                . "$store_id "
                . "$date";

            $result = $this->query($ave_sales_query);
            $result = $result->fetch_object();



            if (isset($options['store_id'])) {
                if (is_array($options['store_id'])) {
                    $store_ids = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(id,'$store_ids')";
                } else {
                    if ($options['store_id'] !== '') {
                        $store_id = " AND find_in_set(id,'{$options['store_id']}')";
                        $store_ids = $options['store_id'];
                    }
                }
            }

            //UG Obtener las sucursales
            //-----------------------------------------------------------------------------------------------------
            //$quert_sucursales = "SELECT fxGetStoreName('$store_ids') as sucursales, '$store_ids' as id_sucursal";
            $quert_sucursales = "  SELECT GROUP_CONCAT(name SEPARATOR ',') AS sucursales , GROUP_CONCAT(id SEPARATOR ',') AS id_sucursal "
                . " FROM stores WHERE 1= 1 $store_id ";

            $resultSucursales = $this->query($quert_sucursales);

            $resultSucursales = $this->resultToArray($resultSucursales);
            //var_dump($quert_sucursales);exit;


            $resultSucursalFormat = array(
                'id_sucursal' => explode(',', $resultSucursales[0]['id_sucursal']),
                'sucursales' => explode(',', $resultSucursales[0]['sucursales']),

            );

            $dateTime = new DateTime();
            $currentDate = $dateTime->format('Y-m-d H:i:s');

            $filtros = array('De' => $options['startDate'], 'year' => $options['endDate'], 'date' => $currentDate);
            return array(
                'sales_items' => $sales_items,
                'summary' => $this->getTaxablesSales($options),
                'quantity_sales' => $result->quantity_sales,
                'total_sales' => $result->total_sales,
                'ave_sales' => $result->ave_sales,
                'sucursales' => $resultSucursalFormat,
                'filtros' => $filtros
            );
        }

        return null;
    }
    public function getStoreStatement(array $options)
    {
        //var_dump($options);exit;
        $pending_balance_only = null;

        if (isset($options['pending_balance_only']) && $options['pending_balance_only'] == '1') {
            $pending_balance_only = " AND status_payment = '1'";
        }
        $limit = null;
        $date = null;
        $dueDate = null;
        $invoice_num = null;
        $customer_id = null;
        $customer_po = null;
        $status = null;
        $user_id = null;

        //$date = $this->createFilterFecha($options, "m.date");
        //$dueDate = $this->createFilterFecha(array('startDate' => $options['startDate'], 'endDate' => $options['endDate']), "m.date");
        $dueDate = $this->createFilterFecha($options['startDate'], $options['endDate'], "m.date");

        if ($options) {
            if (isset($options['invoice_num']) && trim($options['invoice_num']) != '') {
                $invoice_num = " AND find_in_set(m.invoice_num,'{$options['invoice_num']}')";
            }
            if (isset($options['customer_po']) && trim($options['customer_po']) != '') {
                $customer_po = " AND find_in_set(m.customer_po,'{$options['customer_po']}')";
            }

            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $customerIds = implode(',', $options['store_id']);
                    $customer_id = " AND find_in_set(m.billed_to_store,'{$customerIds}')";
                } else {
                    if (trim($options['vendor_id']) != '') {
                        $customer_id = " AND find_in_set(m.billed_to_store,'{$options['store_id']}')";
                    }
                }
            }

            if (isset($options['user_id'])) {
                if (is_array($options['user_id']) && count($options['user_id']) > 0) {
                    $userIds = implode(',', $options['user_id']);
                    $user_id = " AND find_in_set(m.creado_por,'{$userIds}')";
                } else {
                    if (trim($options['user_id']) != '') {
                        $user_id = " AND find_in_set(m.creado_por,'{$options['user_id']}')";
                    }
                }
            }

            if (isset($options['status'])) {
                if (is_array($options['status']) && count($options['status']) > 0) {
                    $statusIds = implode(',', $options['status']);
                    $status = " AND find_in_set(m.status,'{$statusIds}')";
                } else {
                    if (trim($options['status']) != '') {
                        $status = " AND find_in_set(m.status,'{$options['status']}')";
                    }
                }
            }

            if (
                is_null($date)
                && is_null($dueDate)
                && is_null($invoice_num)
                && is_null($customer_id)
                && is_null($customer_po)
                && is_null($status)
                && is_null($user_id)
            ) {
                $limit = " LIMIT 300";
            }
        } else {
            $limit = " LIMIT 300";
        }


        $query = "SELECT m.*,"
            . "fxGetCustomerName(m.id_customer) as customer_name,"
            . "DATE_FORMAT(m.date,'%m/%d/%Y')as formatedDate,"
            . "DATE_FORMAT(m.due_date,'%m/%d/%Y')as formatedDueDate,"
            . "fxGetStatusName(m.status,'Invoice')as statusName,"
            . "SUM(d.quantity)as quantity,"
            . "r.num_shipment, "
            . "fxGetStoreName(billed_to_store)as store_name,"
            . "ROUND(m.total - IFNULL(m.payments,0),2)as balance,"
            . "fxGetUserName(m.creado_por)as createdByName,"
            . "DATE_FORMAT(m.creado_fecha,'%m/%d/%Y %r')as formatedCreatedDate, "
            . " fxGetAreaName(sr.area_id)as area_name "
            . "FROM invoices m "
            . "   "
            . "LEFT JOIN invoice_details d ON m.id = d.id_invoice "
            . "LEFT JOIN receiving_store_requests r ON m.receiving_id = r.id "
            . "LEFT JOIN shipment_store_requests ssr on r.num_shipment = ssr.num_shipment "
            . "LEFT JOIN store_request sr on ssr.id_store_request = sr.id "
            . "WHERE 1 = 1 "
            . "$invoice_num "
            . "$customer_id "
            . "$customer_po "
            . "$status "
            . "$date "
            . "$dueDate "
            . "GROUP BY m.id "
            . "ORDER BY m.id DESC "
            . "$limit ";

        //  . "     s.status != 3 AND cp.status = 1 $fecha $cliente $pending_balance_only      ";
        //var_dump($query);exit;

        $result = $this->query($query);

        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);
            //    var_dump($result); exit;
            $data = array();

            foreach ($result as $row) {
                $datosCliente = array(
                    'customer_name' => $row['store_name'],
                    'contact_name' => $row['store_name'], //$row['contact_name'],
                    'phone' => '1', //$row['phone'],
                    'email' => '@paris.com' //$row['email'],
                );

                $data[$row['billed_to_store']]['datosCliente'] = $datosCliente;

                // $data[$row['billed_to_store']]['payments_by_invoice_id']['factura' . '_' . $row['num_venta']]['document_type'] = $row['document_type'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura'  . '_' . $row['invoice_num']]['document_name'] = $row['area_name'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura'  . '_' . $row['invoice_num']]['formatedDate'] = $row['formatedDate'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura'  . '_' . $row['invoice_num']]['invoice_num'] = $row['invoice_num'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura' . '_' . $row['invoice_num']]['total'] = $row['total'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura'  . '_' . $row['invoice_num']]['balance'] = $row['balance'];
                $data[$row['billed_to_store']]['payments_by_invoice_id']['factura' . '_' . $row['invoice_num']]['statusName'] = $row['statusName'];

                // if ($row['payment_id'] != null) {
                //     $data[$row['billed_to_store']]['payments_by_invoice_id']['factura'  . '_' . $row['invoice_num']]['payments'][$row['invoice_num']] = array(
                //         'payment_status' => $row['payment_status'],
                //         'payment_id' => $row['payment_id'],
                //         'payment_method' => $row['payment_method_name'],
                //         'payment_num_operation' => $row['num_operation'],
                //         'payment_amount' => $row['payment_amount'],
                //         'payment_date' => $row['payment_date'],
                //         'payment_status_name' => $row['payment_status_name'],
                //     );
                // }
            }

            return array(
                'data' => $data,
            );
        } else {
            return null;
        }
        return null;
    }

    public function getFixedExpensesByStore($options)
    {
        $store_id = null;
        $month = null;
        $year = null;

        if (!empty($options['month'])) {
            $month = " AND month = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND year = '{$options['year']}' ";
        }
        if (isset($options['store_id'])) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }

        $query = "SELECT * FROM expenses_fixed_history WHERE 1 = 1 $store_id $month $year";
        $result = $this->query($query);

        $data = array();
        if ($result->num_rows > 0) {
            $result = $this->resultToArray($result);

            foreach ($result as $row) {
                $data[$row['store_id']][$row['expense_fixed_id']] = array('cost' => $row['cost'], 'due_date' => $row['due_date']);
            }

            $categoryRepo = new CategoryRepository(array());
            $fixedExpenseList = $categoryRepo->getListSelectCategories('fixed expense', '1');

            $storeRepository = new StoreRepository();
            $storeList = $storeRepository->getListSelectStores($store_ids, '1');

            return array(
                'fixed_expense_list' => $fixedExpenseList,
                'store_list' => $storeList,
                'data_config' => $data
            );
        }

        return null;
    }

    public function getWaste($options)
    {
        $date = null;
        $store_id = null;
        $status = null;
        $limit = null;

        if ($options) {
            $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 't.date');
            if (trim($options['status']) !== '') {
                $status = " AND t.status = '{$options['status']}' ";
            }

            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(m.store_id,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(m.store_id,'{$options['store_id']}')";
                    }
                }
            }

            if (
                is_null($date)
                && is_null($store_id)
                && is_null($status)
            ) {
                $limit = " LIMIT 300";
            }
        } else {
            $limit = " LIMIT 300";
        }

        $query = "SELECT t.*,"
            . "DATE_FORMAT(t.date,'%m/%d/%Y %r')as formated_date,"
            . "fxGetUserName(t.creado_por)as user_name, "
            . "fxGetStatusName(t.status,'Waste')as status_name,"
            . "s.name as store_name,"
            . "d.quantity,"
            . "d.cost,"
            . "d.price, "
            . "d.total_cost, "
            . "d.total_price,"
            . "p.descripcion as product_name "
            . "FROM waste t "
            . "LEFT JOIN waste_details d ON d.waste_id = t.id "
            . "LEFT JOIN stores s ON t.store_id = s.id "
            . "LEFT JOIN pos_productos p ON d.product_id = p.id "
            . "WHERE 1 = 1 "
            . "$date "
            . "$store_id "
            . "GROUP BY d.id ORDER BY waste_num DESC "
            . "$limit";
        // echo $query;
        $result = $this->query($query);

        if ($result->num_rows > 0) {
            return array('data' => $this->resultToArray($result));
        }

        return null;
    }

    public function getsummary_byHour($options)
    {
        $flashmessenger =  new Flashmessenger();

        $dateTime = new DateTime();
        $currentDate = $dateTime->format('Y-m-d H:i:s');
        $store_id = null;
        $date = $this->createFilterFecha($options['startDate'], $options['endDate'], 'DATE(v.creado_fecha_local)');



        if (isset($options['store_id'])) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        $query_sucursales = " SELECT GROUP_CONCAT(name SEPARATOR ',') AS sucursales , GROUP_CONCAT(id SEPARATOR ',') AS id_sucursal "
            . " FROM stores v WHERE 1= 1 $store_id ";

        $resultSucursales = $this->query($query_sucursales);

        $resultSucursales = $this->resultToArray($resultSucursales);

        if ($options) {
            if (isset($options['store_id'])) {
                if (is_array($options['store_id']) && count($options['store_id']) > 0) {
                    $storeIds = implode(',', $options['store_id']);
                    $store_id = " AND find_in_set(v.idSucursal,'{$storeIds}')";
                } else {
                    if (trim($options['store_id']) != '') {
                        $store_id = " AND find_in_set(v.idSucursal,'{$options['store_id']}')";
                    }
                }
            }

            if (is_null($date) && is_null($store_id)) {
                $flashmessenger->addMessage(array('danger' => 'Debes seleccionar una fecha de inicio y fin.'));
                return null;
            }
        }

        $query_byHour = "SELECT "
            . " idSucursal, "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 7 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '7', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 8 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '8', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 9 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '9', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 10 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '10', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 11 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '11', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 12 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '12', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 13 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '13', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 14 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '14', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 15 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '15', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 16 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '16', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 17 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '17', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 18 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '18', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 19 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '19', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 20 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '20', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 21 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '21', "
            . " SUM(CASE WHEN HOUR(creado_fecha_local) = 22 THEN ROUND(v.total_venta, 2) ELSE 0 END) AS '22', "
            . " fxGetStoreName(idSucursal) as store_name "
            . " FROM "
            . " pbakeryadmin_adminapp.ventas v "
            . " WHERE "
            . " v.`status` != 3 "
            . " $store_id "
            . " $date "
            . " GROUP BY "
            . " idSucursal";

        $resultDataByHour = $this->query($query_byHour);

        $resultDataByHour = $this->resultToArray($resultDataByHour);

        $filtros = array('De' => $options['startDate'], 'year' => $options['endDate'], 'date' => $currentDate);

        $resultSucursalFormat = array(
            'id_sucursal' => explode(',', $resultSucursales[0]['id_sucursal']),
            'sucursales' => explode(',', $resultSucursales[0]['sucursales']),

        );

        $result = array(
            'sucursales' => $resultSucursalFormat,
            'dataByHour' => $resultDataByHour
        );


        return array('result' => $result, 'filtros' => $filtros);
    }
    public function getsummary_byStore($options)
    {
        $store_id = null;
        $month = null;
        $year = null;
        $dateTime = new DateTime();

        $currentDate = $dateTime->format('Y-m-d H:i:s');

        $filtros = array('month' => $options['month'], 'year' => $options['year'], 'date' => $currentDate);

        if (!empty($options['month'])) {
            $month = " AND MONTH(v.fecha) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(v.fecha) = '{$options['year']}' ";
        }
        if (isset($options['store_id'])) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }
        //UG Obtener las sucursales
        //-----------------------------------------------------------------------------------------------------
        //$quert_sucursales = "SELECT fxGetStoreName('$store_ids') as sucursales, '$store_ids' as id_sucursal";
        $quert_sucursales = "  SELECT GROUP_CONCAT(name SEPARATOR ',') AS sucursales , GROUP_CONCAT(id SEPARATOR ',') AS id_sucursal "
            . " FROM stores WHERE 1= 1 $store_id ";

        $resultSucursales = $this->query($quert_sucursales);

        $resultSucursales = $this->resultToArray($resultSucursales);
        //var_dump($quert_sucursales);exit;


        //---------------------------------------------------------------------

        //UG VENTAS
        //--------------------------------------------------------------------------------------------------------------

        $store_id = null;
        $month = null;
        $year = null;

        if (!empty($options['month'])) {
            $month = " AND MONTH(v.fecha) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(v.fecha) = '{$options['year']}' ";
        }
        if (isset($options['store_id'])) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(idSucursal,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(idSucursal,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }

        $query_ventas = "SELECT "
            . " idSucursal as id_sucursal ,SUM(ROUND(v.total_venta,2))as total_venta,fxGetStoreName(idSucursal) as store_name "
            . " FROM ventas v "
            . " WHERE v.status != 3 $store_id $month $year"
            . " GROUP BY idSucursal";
        //." AND idSucursal = 14 AND YEAR(v.fecha) = '2024' AND MONTH(v.fecha) = '7'";
        //var_dump($query);exit;
        $resultVentas = $this->query($query_ventas);
        $resultVentas = $this->resultToArray($resultVentas);
        //-------------------------------------------------------------------------------------------------------------------------

        //UG Rentas
        //------------------------------------------------------------------------------------------------------------------------------
        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(e.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(e.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND e.month = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND e.year = '{$options['year']}' ";
        }

        $query_rentas = "SELECT store_id as id_sucursal, "
            . " CASE WHEN ROUND(SUM(e.cost),2) > 0  THEN ROUND(SUM(e.cost),2) ELSE 0 END as total_valor, "
            . " (SELECT x.description FROM product_categories x WHERE x.id = e.expense_fixed_id) AS 'type' "
            . " FROM expenses_fixed_history e "
            . " WHERE 1 = 1  $store_id $month $year "
            . " GROUP BY store_id, expense_fixed_id ";
        $resultRentas = $this->query($query_rentas);
        $resultRentas = $this->resultToArray($resultRentas);
        //-----------------------------------------------------------------------------------------------------------------------------

        //UG Nomina por hora
        //------------------------------------------------------------------------------------------------------------------------------

        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(t.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(t.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND MONTH(t.date) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(t.date) = '{$options['year']}' ";
        }

        $query_horas = "SELECT "
            . " t.store_id as id_sucursal, "
            . " fxGetStoreName(t.store_id) as sucursalName, "
            //. " SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in)))) as total_work,"
            . " ROUND(SUM(TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in))) / 3600, 2) as total_hours, "

            // . " CASE  "
            // . "  WHEN u.payment_type = 1 AND u.categoria_empleado = 'Social' "
            // . " THEN ROUND (ROUND(SUM(TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in))) / 3600, 2) * u.payment_value,2) "
            // . " ELSE 0 "
            // . " END as salario_Hora "

            . "SUM( "
            . "    CASE "
            . "      WHEN u.payment_type = 1 AND u.categoria_empleado = 'Social' THEN "
            . "         ROUND(TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in)) / 3600 * u.payment_value, 2) "
            . "   ELSE "
            . "        0 "
            . " END "
            . ") AS salario_Hora "

            . " FROM timeclock t "
            . " JOIN  users u ON t.id_user = u.id "
            . " WHERE u.payment_type IS NOT NULL  "
            . " $store_id  "
            . " $year  "
            . " $month "
            . " GROUP BY t.store_id ";


        $resultHoras = $this->query($query_horas);
        $resultHoras = $this->resultToArray($resultHoras);

        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(u.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(u.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " '{$options['year']}' ";
        }

        $query_fijo_social = "SELECT u.store_id as id_sucursal, fxGetStoreName(u.store_id) as sucursalName, "
            . " CASE "
            . " WHEN u.payment_type = 2 AND  u.categoria_empleado = 'Social' "
            . " THEN CASE "
            . " WHEN $year = YEAR(CURDATE()) AND $month = MONTH(CURDATE()) "
            . " THEN ROUND((u.payment_value / 15.2) * DAY(CURDATE()), 2) "
            . " ELSE ROUND((u.payment_value *  2), 2) END "
            . " ELSE 0 END AS salario_fijo_Social "
            . " FROM  users u "
            . " WHERE u.payment_type IS NOT NULL  "
            . " $store_id "
            . " GROUP BY u.store_id ";

        $result_fijo_social = $this->query($query_fijo_social);
        $result_fijo_social = $this->resultToArray($result_fijo_social);
        //-------------------------------------------------------------------------------------------------------------------------

        //UG Nomina por fijo
        //------------------------------------------------------------------------------------------------------------------------------
        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(u.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(u.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " '{$options['year']}' ";
        }

        $query_fijo = "SELECT u.store_id as id_sucursal,"
            //  . " u.id, "
            //. " fxGetStoreName(u.store_id) as sucursalName, "
            //. " fxGetUserName(u.id) as userName, "
            // . " u.payment_type, "
            //  . " u.payment_value, "
            . " CASE WHEN "
            . " u.payment_type = 2 AND u.categoria_empleado = 'Contratista' "
            . "THEN"

            . " CASE "
            // . " WHEN  $year = YEAR(CURDATE()) AND $month = MONTH(CURDATE()) THEN "
            . " WHEN $year = YEAR(CURDATE()) AND $month = MONTH(CURDATE()) THEN "
            // . "   ROUND((u.payment_value * 30.4 / 2) * (DAY(CURDATE()) / 30.4), 2) "
            . " ROUND((u.payment_value / 15.2) * DAY(CURDATE()), 2) "
            . " ELSE "
            . "    ROUND((u.payment_value * 2), 2) "
            . " END "
            . " ELSE 0  END AS 'salario_fijo' "
            . " FROM users u "
            . " WHERE "
            . " u.payment_type IS NOT NULL "
            . " $store_id "
            . " GROUP BY "
            . "  u.store_id";
        //u.id,




        $resultFijo = $this->query($query_fijo);
        $resultFijo = $this->resultToArray($resultFijo);

        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(t.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(t.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND MONTH(t.date) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(t.date) = '{$options['year']}' ";
        }


        $query_horas_contratista = "SELECT "
            . " t.store_id as id_sucursal, "
            . " fxGetStoreName(t.store_id) as sucursalName, "

            // . "CASE  "
            // . "  WHEN u.payment_type = 1 AND u.categoria_empleado = 'Contratista' "
            // . " THEN ROUND (ROUND(SUM(TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in))) / 3600, 2) * u.payment_value,2) "
            // . " ELSE 0 "
            // . " END as salario_Hora_Contratista "

            . " SUM( "
            . "    CASE "
            . "       WHEN u.payment_type = 1 AND u.categoria_empleado = 'Contratista' "
            . "       THEN ROUND((TIME_TO_SEC(TIMEDIFF(t.check_out, t.check_in)) / 3600) * u.payment_value, 2)  "
            . "        ELSE 0 "
            . " END "
            . " ) AS salario_Hora_Contratista "
            . " FROM timeclock t "
            . " JOIN  users u ON t.id_user = u.id "
            . " WHERE u.payment_type IS NOT NULL  "
            . " $store_id  "
            . " $year  "
            . " $month "
            . " GROUP BY t.store_id ";



        $result_horas_contratista = $this->query($query_horas_contratista);
        $result_horas_contratista = $this->resultToArray($result_horas_contratista);


        //-------------------------------------------------------------------------------------------------------------------------

        //UG Produccion
        //------------------------------------------------------------------------------------------------------------------------------

        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(s.to_store,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(s.to_store,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND MONTH(s.date) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(s.date) = '{$options['year']}' ";
        }

        // $query_produccion = "SELECT "
        //     . " m.billed_to_store as id_sucursal, "
        //     . " fxGetStoreName(m.billed_to_store) as store_name,"
        //     . "ROUND(SUM(m.total), 2) as total_sum "
        //     . " FROM invoices m "
        //     // . " LEFT JOIN invoice_details d ON m.id = d.id_invoice "
        //     // . " LEFT JOIN receiving_store_requests r ON m.receiving_id = r.id "
        //     // . " LEFT JOIN shipment_store_requests ssr ON r.num_shipment = ssr.num_shipment "
        //     // . " LEFT JOIN store_request sr ON ssr.id_store_request = sr.id "


        //     . " WHERE 1 = 1 "
        //     . " $store_id "
        //     . " $year "
        //     . "  $month "
        //     . " GROUP BY m.billed_to_store, m.billed_to_store "
        //     . " ORDER BY m.billed_to_store DESC ";
        $query_produccion = "SELECT "
            . " s.to_store AS id_sucursal,"
            . " fxGetStoreName(s.to_store) as store_name, "
            . " IFNULL(ROUND(SUM(p.sale_price * sd.quantity),2),0) AS total_sum "

            . " FROM pbakeryadmin_adminapp.shipment_store_requests s "
            . " LEFT JOIN pbakeryadmin_adminapp.shipment_store_requests_details sd ON sd.id_shipment = s.id "
            . " LEFT JOIN pbakeryadmin_adminapp.products p ON sd.id_product = p.id "

            . " WHERE s.status != 4"
            . " $store_id "
            . " $year "
            . "  $month "
            . " GROUP BY s.to_store "
            . " ORDER BY s.to_store DESC ";

        $resultProduccion = $this->query($query_produccion);
        $resultProduccion = $this->resultToArray($resultProduccion);
        //-------------------------------------------------------------------------------------------------------------------------

        //UG Gastos
        //------------------------------------------------------------------------------------------------------------------------------

        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(s.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(s.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND MONTH(s.date) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(s.date) = '{$options['year']}' ";
        }

        $query_gastos = "SELECT s.store_id as id_sucursal, "
            . " ROUND(SUM(s.withdrawal), 2) as total_sum, "
            . " fxGetStoreName(s.store_id) as store_name "
            . " FROM pbakeryadmin_adminapp.sales_record s "
            . " WHERE 1 = 1 "
            . " $store_id "
            . " $year"
            . " $month"
            . "GROUP BY s.store_id ";

        $resultGastos = $this->query($query_gastos);
        $resultGastos = $this->resultToArray($resultGastos);
        //-------------------------------------------------------------------


        //-------------------------------------------------------------------------------------------------------------------------
        //UG Compras
        //------------------------------------------------------------------------------------------------------------------------------
        $store_id = null;
        $month = null;
        $year = null;
        if (
            isset($options['store_id'])
        ) {
            if (is_array($options['store_id'])) {
                $store_ids = implode(',', $options['store_id']);
                $store_id = " AND find_in_set(pc.store_id,'$store_ids')";
            } else {
                if ($options['store_id'] !== '') {
                    $store_id = " AND find_in_set(pc.store_id,'{$options['store_id']}')";
                    $store_ids = $options['store_id'];
                }
            }
        }


        if (!empty($options['month'])) {
            $month = " AND MONTH(pc.date) = '{$options['month']}' ";
        }
        if (!empty($options['year'])) {
            $year = " AND YEAR(pc.date) = '{$options['year']}' ";
        }

        $query_compras = "SELECT pc.store_id as id_sucursal,"
            . " CASE "
            . " WHEN "
            . " ROUND(SUM(pc.total), 2) > 0 THEN 	ROUND(SUM(pc.total), 2) ELSE 0 END  as total_compras "
            . " ,fxGetStoreName(pc.store_id) as store_name "
            . " FROM purchases pc "
            . " WHERE 1 = 1"
            . " $store_id "
            . " $year "
            . " $month "
            . "GROUP BY pc.store_id ";

        $resultCompras = $this->query($query_compras);
        $resultCompras = $this->resultToArray($resultCompras);
        //------------------------------------------------------------------------------------------------------------------------------

        $resultSucursalFormat = array(
            'id_sucursal' => explode(',', $resultSucursales[0]['id_sucursal']),
            'sucursales' => explode(',', $resultSucursales[0]['sucursales']),

        );
        //   echo "<pre>";var_dump($resultSucursalFormat);echo "</pre>";exit;


        $rentas = [];
        $luz = [];
        $telefono = [];
        $internet = [];

        foreach ($resultRentas as $item) {
            switch ($item['type']) {
                case 'Electricidad':
                    $luz[] = $item;
                    break;
                case 'Telefono':
                    $telefono[] = $item;
                    break;
                case 'Internet':
                    $internet[] = $item;
                    break;
                case 'Renta':
                    $rentas[] = $item;
                    break;
            }
        }

        $result_fijo_social_assoc = [];
        foreach ($result_fijo_social as $item1) {
            $result_fijo_social_assoc[$item1['id_sucursal']] = $item1['salario_fijo_Social'];
        }


        foreach ($resultHoras as &$item1) {
            $id_sucursal = $item1['id_sucursal'];
            if (isset($result_fijo_social_assoc[$id_sucursal])) {
                $item1['salario_fijo_Social'] = $result_fijo_social_assoc[$id_sucursal];
            } else {
                $item1['salario_fijo_Social'] = '0.0'; // O el valor que consideres apropiado si no hay correspondencia
            }
        }
        $result_horas_contratista_assoc = [];
        foreach ($result_horas_contratista as $item) {
            $result_horas_contratista_assoc[$item['id_sucursal']] = $item['salario_Hora_Contratista'];
        }


        foreach ($resultFijo as &$item) {
            $id_sucursal = $item['id_sucursal'];
            if (isset($result_horas_contratista_assoc[$id_sucursal])) {
                $item['salario_Hora_Contratista'] = $result_horas_contratista_assoc[$id_sucursal];
            } else {
                $item['salario_Hora_Contratista'] = '0.0';
            }
        }
        //  echo "<pre>";var_dump($resultHoras);echo "</pre>";exit;



        $dataA = array(
            'sucursales' => $resultSucursalFormat,
            'ventas' => $resultVentas,
            'rentas' => $rentas,
            'luz' => $luz,
            'telefono' => $telefono,
            'internet' => $internet,
            'horas' => $resultHoras,

            'fijo' => $resultFijo,

            'produccion' => $resultProduccion,
            'gastos' => $resultGastos,
            'compras' => $resultCompras
        );



        $result = array();

        // Inicializa las sucursales en el resultado
        for ($i = 0; $i < count($dataA['sucursales']['id_sucursal']); $i++) {
            $id = $dataA['sucursales']['id_sucursal'][$i];
            $sucursal = $dataA['sucursales']['sucursales'][$i];
            $result[$id] = array(
                'sucursal' => $sucursal,
                'ventas' => array(array('total_venta' => 0.0)),
                'rentas' => array(array('total_valor' => 0.00)),
                'luz' => array(array('total_valor' => 0.00)),
                'telefono' => array(array('total_valor' => 0.00)),
                'internet' => array(array('total_valor' => 0.00)),
                'horas' => array(array('salario_Hora' => 0.0, 'salario_fijo_Social' => 0.0)),

                'fijo' => array(array('salario_fijo' => 0.0)),

                'produccion' => array(array('total_sum' => 0.0)),
                'gastos' => array(array('total_sum' => 0.0)),
                'compras' => array(array('total_compras' => 0.0)),

            );
        }

        // Asigna los datos a cada sucursal
        $this->assignData($result, $dataA['ventas'], 'ventas', 'id_sucursal');
        $this->assignData($result, $dataA['rentas'], 'rentas', 'id_sucursal');
        $this->assignData($result, $dataA['luz'], 'luz', 'id_sucursal');
        $this->assignData($result, $dataA['telefono'], 'telefono', 'id_sucursal');
        $this->assignData($result, $dataA['internet'], 'internet', 'id_sucursal');
        $this->assignData($result, $dataA['horas'], 'horas', 'id_sucursal');
        $this->assignData($result, $dataA['fijo'], 'fijo', 'id_sucursal');

        $this->assignData($result, $dataA['produccion'], 'produccion', 'id_sucursal');
        $this->assignData($result, $dataA['gastos'], 'gastos', 'id_sucursal');
        $this->assignData($result, $dataA['compras'], 'compras', 'id_sucursal');

        return array('result' => $result, 'filtros' => $filtros);
    }

    // Función para asignar datos a las sucursales
    function assignData(&$result, $dataArray, $key, $idKey)
    {
        foreach ($dataArray as $data) {
            $id = $data[$idKey];
            if (isset($result[$id])) {
                $result[$id][$key][] = $data;
            }
        }
    }
    public function createFilterFecha($fechaInicio, $fechaFin, $campoFecha = null)
    {
        $fecha = null;
        $tools = new Tools();
        if ($fechaInicio != null) {
            $fechaInicio = $tools->setFormatDateToDB($fechaInicio);

            if ($fechaFin != null) {
                $fechaFin = $tools->setFormatDateToDB($fechaFin);
                $fecha .= " AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaFin}' ";
            } else {
                $fecha .= " AND $campoFecha BETWEEN '{$fechaInicio}' AND '{$fechaInicio}' ";
            }
        } elseif ($fechaFin != null) {
            $fecha .= " AND $campoFecha BETWEEN '{$fechaFin}' AND '{$fechaFin}' ";
        }

        return $fecha;
    }
}
