<?php
use Carbon\Carbon;
ini_set('memory_limit','3096M');

/*
 * Sincroniza dados do ERP do mês corrente e D-1
 */

class ImportData {

    private $indexCompanyExec = 0;
    private $ignoreProductList = array();
    private $filterProductCodes = array();
    private $filterCompanies = array();
    private $errorsCompany;
    private $productOrderSysList = array();
    private $supplierOrderSysList = array();
    private $productSaleHistoryOrderSysList = array();
    private $productCompanyOrderSysList = array();
    private $signalOrderSysList = array();
    private $seasonalityProductsOrderSysList = array();
    private $emailLogList = array();
    private $filterSupplierId;
    private $monthsSaleHistory = 2;

    public function importAll($companyId = null, $supplierId = null) {
        $this->checkDateToRun();
        if (!empty($companyId))
            $this->filterCompanies = array($companyId);
        if (!empty($supplierId)) {
            $this->filterSupplierId = $supplierId;
            $this->filterProductCodes = Product::where("supplier_id", "=", $this->filterSupplierId)->lists("code");
        }
        $startImpLog = "\n\n\n******** Iniciando importacao **********";
        OrderSysUtils::log('erpimport', $startImpLog, Session::get('user'));
        Log::info($startImpLog);
        Log::info("Importando mês atual e " . $this->monthsSaleHistory - 1 . " meses anteriores");

        $this->errorsCompany = new Illuminate\Support\MessageBag();

        $this->loadLocalData();
        $this->process();

        if (count($this->emailLogList > 0)) {
            OrderSysUtils::sendMailNotification($this->emailLogList);
            OrderSysUtils::log('erpimport', print_r($this->emailLogList, true), Session::get('user'));
        }

        $endImpLog = "******** Importacao concluida! **********";
        OrderSysUtils::log('erpimport', $endImpLog, Session::get('user'));
        Log::info($endImpLog);
        return $this->errorsCompany;
    }
    
    private function checkDateToRun() {
        if (date('y') == '17') {
            Log::info("Abortado importações ano 2017");
            die;            
        } else if (date('y') == '18' && date('m') == '01') {
            $this->monthsSaleHistory = 1;
        }
    }

    private function loadLocalData() {
        Log::info("Carregando dados locais... memory_get_usage: " . memory_get_usage());
        foreach (Product::all() as $product) {
            $this->productOrderSysList[$product->code] = $product;
        }
        Log::info(count($this->productOrderSysList) . " Produtos carregados!");

        foreach (Seasonality::with('seasonalityProducts')->where('active', true)->get() as $productSeasonality) {
            foreach ($productSeasonality['seasonality_products'] as $product) {
                if (isset($this->seasonalityProductsOrderSysList[$product->id])) {
                    $this->seasonalityProductsOrderSysList[$product->id] = $this->seasonalityProductsOrderSysList[$product->id] . ";" . $productSeasonality->months;
                } else {
                    $this->seasonalityProductsOrderSysList[$product->id] = $productSeasonality->months;
                }
            }
        }
        Log::info(count($this->seasonalityProductsOrderSysList) . " Produtos x Sazonalidades carregados!");

        foreach (Supplier::all(array('id', 'name')) as $supplier) {
            $this->supplierOrderSysList[$supplier->name] = $supplier;
        }
        Log::info(count($this->supplierOrderSysList) . " Fornecedores carregados!");

        //TODO considerar (opcional) filtro company foreach (ProductSaleHistory::whereIn('company_id',$this->filterCompanies)
        foreach (ProductSaleHistory::where('ref_date', '<=', OrderSysUtils::formatDate("Y-m-01", Carbon::today()->startofMonth()))
                ->where('ref_date', '>=', OrderSysUtils::formatDate("Y-m-01", Carbon::today()->subMonths($this->monthsSaleHistory - 1)->startofMonth()))
                ->get() as $productSaleHistory) {
            $this->productSaleHistoryOrderSysList[$productSaleHistory->company_id . '_' . $productSaleHistory->product_id . '_' . $productSaleHistory->ref_date] = $productSaleHistory;
        }
        Log::info(count($this->productSaleHistoryOrderSysList) . " Histórico de vendas carregados!");

        //TODO considerar (opcional) filtro company ProductCompany::whereIn('company_id',$this->filterCompanies)->get() 
        foreach (ProductCompany::all() as $productCompany) {
            $this->productCompanyOrderSysList[$productCompany->company_id . '_' . $productCompany->product_id] = $productCompany;
        }
        Log::info(count($this->productCompanyOrderSysList) . " Produtos x Empresas e informações de compras carregados!");

        $this->signalOrderSysList = Signal::orderBy("value", "asc")->get();
        Log::info(count($this->signalOrderSysList) . " Alertas de compra/venda carregados!");

        Log::info("finalizado! memory_get_usage: " . memory_get_usage());
    }

    private function process() {
        Log::info("Iniciando importacao de dados");
        foreach (Company::all() as $company) {
            Log::info("Empresa $company->name");

            if (count($this->filterCompanies) > 0 && !in_array($company->id, $this->filterCompanies)) {
                Log::info("Filtro de empresa ativo: empresa " . $company->name . " ignorada!");
            } else {
                $this->indexCompanyExec++;
                try {
                    $this->importERPProducts($company);
                    $this->importERPProductsPurchase($company);
                    $this->generatePurchaseOrders($company);
                } catch (Exception $e) {
                    $this->indexCompanyExec--;
                    $message = mb_convert_encoding("Erro ao importar dados da $company->name. Exception: " . $e->getMessage(), "UTF-8");
                    $this->errorsCompany->add('', $message);
                    $this->emailLogList[] = $message;
                    Log::error($e);
                }
                $this->callgc();
            }
        }
        Log::info("Importacao de dados concluída!");
    }

    private function importERPProducts($company) {
        $productsToImp = $this->getERPProducts($company);
        //Dados de produtos e fornecedores não diferem nas empresas, portanto importa somente na primeira iteração
        if ($this->indexCompanyExec == 1) {
            $this->importSuppliers($productsToImp);
            $this->importProducts($productsToImp);
        }
        $this->importSalesHistory($productsToImp, $company);
        $this->importProductsCompany($productsToImp, $company);

        Log::info("Concluido importacao de produtos, fornecedores e vendas.");
    }

    private function importERPProductsPurchase($company) {
        $productsToImp = $this->getERPProductsPurchase($company);
        $this->importProductsPurchase($productsToImp, $company);
        if (CURRENT_DATASOURCE == Config::get('constants.DISTRIBUTOR_DATASOURCE')) {
            $this->updateStockAndBoxQttyFromJKToOrderSys($company->id);
        }
        Log::info("Concluido importacao de compras.");
    }

    private function importSuppliers($productsToImp) {
        $suppliers = array();
        foreach ($productsToImp as $productToImp)
            if (!in_array($productToImp->referencia, $suppliers))
                $suppliers[] = OrderSysUtils::strNormalize($productToImp->referencia);

        $newSuppliers = array_diff($suppliers, array_map(
                        function($a) {
                    return $a['name'];
                }, Supplier::all(array('name'))->toArray()));
        Log::info(count($newSuppliers) . " novos fornecedores encontrados!");

        foreach ($newSuppliers as $supplierName) {
            if ($supplierName != null && !empty($supplierName)) {
                $s = Supplier::create(array('name' => OrderSysUtils::strNormalize($supplierName)));
                $this->supplierOrderSysList[] = $s;
            }
        }
        Log::info("Fornecedores importados com sucesso!");
        return true;
    }

    private function importProducts($productsToImp) {
        Log::info(count((array) $productsToImp) . " produtos encontrados: ");

        foreach ($productsToImp as $productToImp) {
            Log::debug("P: $productToImp->descricao");

            if (!$productToImp->custo > 0 || !$productToImp->preco > 0) {
                $message = mb_convert_encoding("Produto $productToImp->descricao sem preço ou custo definido!", "UTF-8");
                Log::warning($message);
            }

            if (isset($this->productOrderSysList[$productToImp->codigo])) {
                $product = $this->productOrderSysList[$productToImp->codigo];
                $productOld = clone $product;
            } else {
                $product = new Product();
                $productOld = null;
            }

            $product->name = OrderSysUtils::strNormalize(OrderSysUtils::strNormalize($productToImp->descricao));
            $product->code = $productToImp->codigo;
            $product->product_group = $productToImp->posicionamento;
            $product->cost = $productToImp->custo;
            $product->price = $productToImp->preco;
            $product->box_qtty = $productToImp->caixa_f;

            $local2Value = '';
            if (strlen($productToImp->local2) > 2)
                $local2Value = substr(str_replace("-", ".", $productToImp->local2), 1, strlen($productToImp->local2) - 2);
            $local3Value = '';
            if (strlen($productToImp->local3) > 2)
                $local3Value = substr(str_replace("-", ".", $productToImp->local3), 1, strlen($productToImp->local3) - 2);

            $product->local2 = is_numeric($local2Value) ? $local2Value : null;
            $product->local3 = is_numeric($local3Value) ? $local3Value : null;
            $product->image = $this->buildProductImgPath(OrderSysUtils::strNormalizeWithCase($productToImp->imagem));

            try {
                $product->supplier()->associate($this->supplierOrderSysList[OrderSysUtils::strNormalize($productToImp->referencia)]);

                if (is_null($productOld)) {
                    $product->save();
                    $this->productOrderSysList[] = $product;
                    Log::debug("Produto inserido!");
                } else if (!$product->equals($productOld)) {
                    $product->update();
                    Log::debug("Produto atualizado");
                } else {
                    Log::debug("Sem atualizações");
                }
            } catch (Exception $e) {
                $this->ignoreProductList[] = $productToImp->codigo;
                $errorMsg = mb_convert_encoding("Produto $product->name desconsiderado! Mensagem: " . $e->getMessage(), "UTF-8");
                $this->emailLogList[] = $errorMsg;
                Log::warning($errorMsg);
                Log::debug($e);
            }
        }
        Log::info("Produtos importados com sucesso!");
        return true;
    }

    private function buildProductImgPath($erpImgPath) {
        $POA_ERP_PATH_LABEL = "PS.COM\\IMAGENS VHX\\";
        $SL_ERP_PATH_LABEL = "PS.COM\\IMAGENS\\";

        $orderSysImgPath = null;
        $label = null;
        $path = null;
        if (!empty($erpImgPath)) {
            if (stripos($erpImgPath, $POA_ERP_PATH_LABEL)) {
                $orderSysImgPath = "static/imagens/erp/";
                $label = $POA_ERP_PATH_LABEL;
            } else {
                $orderSysImgPath = "static/imagens/erp/";
                $label = $SL_ERP_PATH_LABEL;
            }
        }

        if (!empty($orderSysImgPath)) {
            try {
                $path = str_ireplace($label, '|||', $erpImgPath);
                $path = explode('|||', $path)[1];
                $path = str_replace('\\', '/', $path);
                $path = str_replace('"', '', $path);
                $path = $orderSysImgPath . $path;
            } catch (Exception $e) {
                $message = "Erro ao tentar construir path da imagem " . $erpImgPath . ": " . $e->getMessage();
                $this->emailLogList[] = $message;
                Log::warning($message);
            }
        }

        return $path;
    }

    private function importSalesHistory($productsToImp, $company) {
        Log::info("Importando histórico de vendas...");

        foreach ($productsToImp as $productToImp) {
            if (!in_array($productToImp->codigo, $this->ignoreProductList)) {
                if (isset($this->productOrderSysList[$productToImp->codigo])) {
                    $product = $this->productOrderSysList[$productToImp->codigo];

                    for ($i = 1; $i <= $this->monthsSaleHistory; $i++) {
                        $this->importSaleHistory($product, $company, OrderSysUtils::formatDate("Y-m-01", Carbon::today()->subMonths($i - 1)->startofMonth()), $productToImp->{'notas' . $i}, $productToImp->{'qtmes' . $i});
                    }
                } else {
                    Log::warning("Produto $productToImp->descricao código $productToImp->codigo não encontrado para importa histórico de vendas");
                }
            }
        }
        Log::info("Historico de Vendas importado com sucesso!");
        return true;
    }

    private function importSaleHistory($product, $company, $refDate, $totalQttySaleInvoice, $totalQttySale) {
        $key = $company->id . '_' . $product->id . '_' . $refDate;

        if (isset($this->productSaleHistoryOrderSysList[$key])) {
            $productSaleHistory = $this->productSaleHistoryOrderSysList[$key];
            $productSaleHistoryOld = clone $productSaleHistory;

            if ($totalQttySaleInvoice > 0)
                $productSaleHistory->total_qtty_sale_invoice = !empty($totalQttySaleInvoice) ? $totalQttySaleInvoice : 0;
            if ($totalQttySale > 0)
                $productSaleHistory->total_qtty_sale = !empty($totalQttySale) ? round($totalQttySale) : 0;
        } else {
            $productSaleHistory = new ProductSaleHistory();
            $productSaleHistoryOld = null;
            $productSaleHistory->total_qtty_sale_invoice = !empty($totalQttySaleInvoice) ? $totalQttySaleInvoice : 0;
            $productSaleHistory->total_qtty_sale = !empty($totalQttySale) ? round($totalQttySale) : 0;
        }

        $productSaleHistory->product()->associate($product);
        $productSaleHistory->company()->associate($company);

        $productSaleHistory->ref_date = $refDate;

        Log::debug("$product->name |$productSaleHistory->total_qtty_sale_invoice|$productSaleHistory->total_qtty_sale|$productSaleHistory->ref_date");
        if (is_null($productSaleHistoryOld)) {
            try {
                $productSaleHistory->save();
            } catch (Exception $e) {
                $this->emailLogList[] = $e->getMessage();
                Log::warning($e->getMessage());
                Log::error($e);
            }
            $this->productSaleHistoryOrderSysList[$key] = $productSaleHistory;
            Log::debug("Venda inserida");
        } else if (!$productSaleHistory->equals($productSaleHistoryOld)) {
            $productSaleHistory->update();
            Log::debug("Venda atualizada");
        } else {
            Log::debug("Sem atualizações");
        }
    }

    private function importProductsCompany($productsToImp, $company) {
        Log::info("Importando produtos x empresas...");

        foreach ($productsToImp as $productToImp) {
            if (!in_array($productToImp->codigo, $this->ignoreProductList)) {

                $productToImp->datacompra = $this->ftERPDate($productToImp->datacompra);
                $productToImp->datavenda = $this->ftERPDate($productToImp->datavenda);
                $product = null;
                if (isset($this->productOrderSysList[$productToImp->codigo])) {
                    $product = $this->productOrderSysList[$productToImp->codigo];
                } else {
                    Log::warning("Produto código ERP $productToImp->codigo não foi carregado!");
                    $this->ignoreProductList[] = $productToImp->codigo;
                    continue;
                }

                if (isset($this->productCompanyOrderSysList[$company->id . '_' . $product->id])) {
                    $productCompany = $this->productCompanyOrderSysList[$company->id . '_' . $product->id];
                    $productCompanyOld = clone $productCompany;
                } else {
                    $productCompanyOld = null;
                    $productCompany = new ProductCompany();
                    $productCompany->product()->associate($product);
                    $productCompany->company()->associate($company);
                    $productCompany->min_qtty_allowed = 0;
                }

                $d1 = new Carbon($productToImp->datacompra);
                $d2 = new Carbon($productCompany->last_qtty_bought_date);
                if (!is_null($productToImp->datacompra) && $d1->gt($d2)) {
                    $productCompany->initial_stock = $productToImp->estoque > 0 ? $productToImp->estoque : 0;
                    $productCompany->stock = $productCompany->initial_stock;
                    $productCompany->generate_label = true;

                    if (!is_null($productToImp->datacompra))
                        DB::update("
                            update purchase_order_item
                               set received_date = now(),
                                   updated_at = now(),
                                   user_update = 'system'
                              from purchase_order
                             where purchase_order.id = purchase_order_item.purchase_order_id
                               and purchase_order.status = ?
                               and purchase_order.supplier_id = ?
                               and purchase_order.company_id = ?
                               and purchase_order_item.received_date is null"
                                , [Config::get('constants.STPO_SENT'), $product->supplier_id, $productCompany->company_id]);
                } else {
                    $productCompany->stock = $productToImp->estoque;
                }

                if (is_null($productCompany->initial_stock) ||
                        $productCompany->initial_stock < $productCompany->stock) {
                    $productCompany->initial_stock = $productCompany->stock;
                }

                $productCompany->last_qtty_bought_date = $productToImp->datacompra;
                $productCompany->last_sale_date = $productToImp->datavenda;

                /*
                 * Segundo Jorge, diferente do que foi definid desde o início, preço na verdade é por empresa, 
                 * porém relata que nunca ocorre diferença. Foi solicitado importar a data da ultima alteração do preço,
                 * que já esta no conceito correto de ser por empresa. No caso de precisar exibir essa informação independente
                 * de empresa no sistema, por hora exibir uma arbitrariamente
                 */
                if (!empty($productToImp->preco_ultima_alteracao))
                    $productCompany->price_last_change_date = $this->ftERPDate($productToImp->preco_ultima_alteracao, true);

                $productCompany->promo_price = $productToImp->promo_preco;
                $productCompany->promo_start = $this->ftERPDate($productToImp->promo_inicio);
                $productCompany->promo_end = $this->ftERPDate($productToImp->promo_fim);

                $this->setSignal($product, $productCompany);

                Log::debug("$product->name |$productCompany->initial_stock|$productCompany->stock|$productCompany->last_sale_date");

                $productCompany->initial_stock = round($productCompany->initial_stock);
                $productCompany->stock = round($productCompany->stock);


                try {
                    if (is_null($productCompanyOld)) {
                        $productCompany->save();
                        $this->productCompanyOrderSysList[] = $productCompany;
                        Log::debug("Produto x empresa inserido");
                    } else if (!$productCompany->equals($productCompanyOld)) {
                        $productCompany->update();
                        Log::debug("Produto x empresa atualizado");
                    } else {
                        Log::debug("Sem atualizações");
                    }
                } catch (Exception $e) {
                    $this->emailLogList[] = $e->getMessage();
                    Log::warning($e->getMessage());
                    Log::error($e);
                }
            }
        }
        Log::info("Produto versus Empresa importados com sucesso!");
        return true;
    }

    private function importProductsPurchase($productsToImp, $company) {
        foreach ($productsToImp as $productToImp) {
            if (!in_array($productToImp->codigo, $this->ignoreProductList)) {

                $product = $this->productOrderSysList[$productToImp->codigo];

                if (isset($this->productCompanyOrderSysList[$company->id . '_' . $product->id])) {
                    $productCompany = $this->productCompanyOrderSysList[$company->id . '_' . $product->id];
                    $productCompanyOld = clone $productCompany;

                    $productCompany->last_qtty_bought = round($productToImp->qtde);
                    if (is_null($productCompany->initial_stock) || $productCompany->initial_stock == 0)
                        $productCompany->initial_stock = $productToImp->qtde;

                    $this->setSignal($product, $productCompany);

                    if (!$productCompany->equals($productCompanyOld)) {
                        if ($productCompany->signal_id != $productCompanyOld->signal_id)
                            $productCompany->signal_date = OrderSysUtils::formatDate();

                        $productCompany->update();
                        Log::debug("Quantidade da última compra atualizada");
                    }
                }
            }
        }
        Log::info("Quantidade de últimas compras atualizadas com sucesso!");
        return true;
    }

    private function setSignal($product, &$productCompany) {
        $blackSignal = "";
        $emptySignal = "";
        $redSignal = "";
        $graySignal = "";
        $purpleSignal = "";
        $blueSignal = "";
        foreach ($this->signalOrderSysList as $signal) {
            if ($signal->code == Config::get('constants.SIGNAL_BLACK')) {
                $blackSignal = $signal;
            } else if ($signal->code == Config::get('constants.SIGNAL_EMPTY')) {
                $emptySignal = $signal;
            } else if ($signal->code == Config::get('constants.SIGNAL_RED')) {
                $redSignal = $signal;
            } else if ($signal->code == Config::get('constants.SIGNAL_GRAY')) {
                $graySignal = $signal;
            } else if ($signal->code == Config::get('constants.SIGNAL_PURPLE')) {
                $purpleSignal = $signal;
            } else if ($signal->code == Config::get('constants.SIGNAL_BLUE')) {
                $blueSignal = $signal;
            }
        }
        $productCompany->signal()->associate($emptySignal);
        $tryCalcSignal = true;

        if (!$product->active || $product->not_buy) {
            $productCompany->signal()->associate($blackSignal);
            $tryCalcSignal = false;
        } else if (empty($product->price) || $product->price == 0) {
            $productCompany->signal()->associate($emptySignal);
            $tryCalcSignal = false;
        } else if ($product->active && !$product->not_buy && $productCompany->signal_id == $blackSignal->id) {
            $productCompany->signal()->associate($emptySignal);
            $tryCalcSignal = false;
        }

        if ($tryCalcSignal) {
            if (isset($this->seasonalityProductsOrderSysList[$product->id])) {
                $months = preg_split('/(,|;)/', $this->seasonalityProductsOrderSysList[$product->id]);
                Log::debug("Identificado sazonalidades para o produto $product->name : " . json_encode($months));

                if (in_array(date('m'), $months)) {
                    $tryCalcSignal = true;
                    Log::debug("Mês atual está contido nas sazonalidades");
                } else {
                    $tryCalcSignal = false;
                    $productCompany->signal()->associate($graySignal);
                    Log::debug("Mês atual NÃO está contido nas sazonalidades");
                }
            }
        }
        if ($tryCalcSignal) {
            if ($productCompany->min_qtty_allowed > 0) {
                if ($productCompany->stock < $productCompany->min_qtty_allowed) {
                    $productCompany->signal()->associate($redSignal);
                    $tryCalcSignal = false;
                }
            } else if ($productCompany->initial_stock > 0) {
                foreach ($this->signalOrderSysList as $signal) {
                    if ($signal->value > 0 &&
                            ($productCompany->stock / $productCompany->initial_stock <= $signal->value)) {
                        $productCompany->signal()->associate($signal);
                        $tryCalcSignal = false;
                        break;
                    }
                }
            }
        }
        if ($tryCalcSignal && $productCompany->initial_stock > 0) {
            $lastBoughtDate = new Carbon($productCompany->last_qtty_bought_date);
            $currentDate = new Carbon();
            //Se em 15 dias ou mais o estoque do produto é de 90% ou mais é um produto ruim (roxo).
            if (!is_null($productCompany->last_qtty_bought_date) &&
                    $currentDate->diffInDays($lastBoughtDate) >= 30 &&
                    $productCompany->stock / $productCompany->initial_stock >= 0.9) {
                $productCompany->signal()->associate($purpleSignal);
                $tryCalcSignal = false;

                //Se em 30 dias ou mais o estoque do produto é 70% ou menos é um produto bom (azul).
            } else if (!is_null($productCompany->last_qtty_bought_date) &&
                    $currentDate->diffInDays($lastBoughtDate) >= 30 &&
                    $productCompany->stock / $productCompany->initial_stock <= 0.7) {
                $productCompany->signal()->associate($blueSignal);
                $tryCalcSignal = false;
            }
        }
    }

    private function getERPProducts($company) {
        Log::info("Buscando dados de produtos ERP...");

        if (Config::get('app.env') == 'production' ||
                Config::get('app.env') == 'dev') {


            $sqlQtAndNFMonthMain = "";
            $sqlQtAndNFMonthUnions = "";
            for ($i = 1; $i <= $this->monthsSaleHistory; $i++) {
                $sqlQtAndNFMonthMain .= " SUM(QTMES$i) as qtmes$i, ";
                $sqlQtAndNFMonthMain .= " SUM(NOTAS$i) as notas$i, ";

                $sqlQtAndNFMonthUnions .= " 0 AS QTMES$i, ";
                $sqlQtAndNFMonthUnions .= " 0 AS NOTAS$i, ";
            }

            $sql = "
                SELECT CODIGO, 
                MAX(DESCRICAO) as descricao,
                MAX(REFERENCIA) as referencia,
                max(palete) as caixa_f,
                MAX(POSIC) as posicionamento,
                MAX(DATACOMPRA) as datacompra, 
                MAX(QTDECOMPRA) as qtdecompra, 
                SUM(ESTOQUE) as estoque,
                MAX(CUSTO) as custo, 
                MAX(LOCAL2) as local2, 
                MAX(LOCAL3) as local3,
                $sqlQtAndNFMonthMain
                MAX(DATAVENDA) as datavenda,
                MAX(PRECO) as preco,
                MAX(preco_ultima_alteracao) as preco_ultima_alteracao,
                MAX(promo_preco) as promo_preco,
                MAX(promo_inicio) as promo_inicio,
                MAX(promo_fim) as promo_fim,
                max(imagem) as imagem
                FROM
                (SELECT CP1CODIG AS CODIGO,
                CP1DESCR AS DESCRICAO,
                CP1REF99 AS REFERENCIA,
                cp1palete as palete,
                0 AS ESTOQUE,
                CP1POSIC AS POSIC,
                0 AS DATACOMPRA,
                0 AS QTDECOMPRA,
                0 as preco,
                0 as preco_ultima_alteracao,
                0 as promo_preco,
                0 as promo_inicio,
                0 as promo_fim,
                0 AS CUSTO,
                CP1LOCAL1 AS LOCAL2,
                CP1LOCAL2 AS LOCAL3,
                $sqlQtAndNFMonthUnions
                0 AS DATAVENDA,
                cp1imagem as imagem FROM PRODUTO
                
                UNION
                SELECT CP1CODIG AS CODIGO,
                CP1DESCR AS DESCRICAO,
                CP1REF99 AS REFERENCIA,
                cp1palete as palete,
                0 AS ESTOQUE,
                CP1POSIC AS POSIC,
                0 AS DATACOMPRA,
                0 AS QTDECOMPRA,
                0 as preco,
                0 as preco_ultima_alteracao,
                0 as promo_preco,
                0 as promo_inicio,
                0 as promo_fim,
                CUSULTCU AS CUSTO,
                CP1LOCAL1 AS LOCAL2,
                CP1LOCAL2 AS LOCAL3,
                $sqlQtAndNFMonthUnions
                0 AS DATAVENDA,
                ' ' as imagem
                FROM CUSTOPRODUTO
                INNER JOIN PRODUTO ON CUSCODIG = CP1CODIG
                
                UNION
                SELECT CP1CODIG AS CODIGO,
                ' ' AS DESCRICAO,
                ' ' AS REFERENCIA,
                0 as palete,
                SUM(ESTQUANT) AS ESTOQUE,
                0 AS POSIC,
                0 AS DATACOMPRA,
                0 AS QTDECOMPRA,
                0 as preco,
                0 as preco_ultima_alteracao,
                0 as promo_preco,
                0 as promo_inicio,
                0 as promo_fim,
                0 AS CUSTO,
                ' ' AS LOCAL2,
                ' ' AS LOCAL3,
                $sqlQtAndNFMonthUnions
                0 AS DATAVENDA,
                ' ' as imagem
                 FROM ESTOQUE
                INNER JOIN PRODUTO ON ESTCODIG = CP1CODIG
                WHERE ESTFILIAL = $company->code
                GROUP BY CP1CODIG                
                UNION
                SELECT MD3PRODUTO AS CODIGO,
                ' ' AS DESCRICAO,
                ' ' AS REFERENCIA,
                0 as palete,
                0 AS ESTOQUE,
                0 AS POSIC,
                MAX(MD3DATA) AS DATACOMPRA,
                0 AS QTDECOMPRA,
                0 as preco,
                0 as preco_ultima_alteracao,
                0 as promo_preco,
                0 as promo_inicio,
                0 as promo_fim,
                0 AS CUSTO,
                ' ' AS LOCAL2,
                ' ' AS LOCAL3,
                $sqlQtAndNFMonthUnions
                0 AS DATAVENDA,
                ' ' as imagem
                FROM ITENSNOTA
                INNER JOIN PRODUTO ON MD3PRODUTO = CP1CODIG
                INNER JOIN NATUREZA ON MD3CDNOP = NATCODIG
                WHERE NATTIPNF = 'E' AND MD3CDNOP IN (2102, 1102, 1101, 2101, 1403, 2403, 2401, 3102, 1910, 2910)
                AND NATESTATISTICA = 1
                AND MD3FILIAL = $company->code
                GROUP BY MD3PRODUTO ";

            $sqlBlock = '';
            for ($i = 1; $i <= $this->monthsSaleHistory; $i++) {
                $sqlQtAndNFMonthUnionsQTMESNFCurrent = str_replace("0 AS QTMES" . $i, "SUM(MD3QUANTIDADE) AS QTMES" . $i, $sqlQtAndNFMonthUnions);

                $sqlQtAndNFMonthUnionsQTMESNFCurrent = str_replace("0 AS NOTAS" . $i, "COUNT(MD3NOTAF) AS NOTAS" . $i, $sqlQtAndNFMonthUnionsQTMESNFCurrent);

                $sqlBlock .= "  UNION
                        SELECT MD3PRODUTO AS CODIGO,
                        ' ' AS DESCRICAO,
                        ' ' AS REFERENCIA,
                        0 AS palete,
                        0 AS ESTOQUE,
                        0 AS POSIC,
                        0 AS DATACOMPRA,
                        0 AS QTDECOMPRA,
                        0 AS preco,
                        0 as preco_ultima_alteracao,
                        0 as promo_preco,
                        0 as promo_inicio,
                        0 as promo_fim,
                        0 AS CUSTO,
                        ' ' AS LOCAL2,
                        ' ' AS LOCAL3,
                        $sqlQtAndNFMonthUnionsQTMESNFCurrent
                        MAX(MD3DATA) AS DATAVENDA,
                        ' ' AS imagem
                         FROM ITENSNOTA
                        INNER JOIN PRODUTO ON MD3PRODUTO = CP1CODIG
                        INNER JOIN NATUREZA ON MD3CDNOP = NATCODIG
                        WHERE NATTIPNF = 'S'
                        AND NATESTATISTICA = 1
                        AND MD3FILIAL = $company->code
                        AND MD3DATA >= " . OrderSysUtils::formatDate("Ymd", Carbon::today()->subMonths($i - 1)->startofMonth()) . "
                        AND MD3DATA <= " . OrderSysUtils::formatDate("Ymd", Carbon::today()->subMonths($i - 1)->endofMonth()) . "                
                        GROUP BY MD3PRODUTO  ";
            }
            $sql .= $sqlBlock;
            $sql .= "                
                UNION
                SELECT CP1CODIG AS CODIGO,
                CP1DESCR AS DESCRICAO,
                CP1REF99 AS REFERENCIA,
                0 AS palete,
                0 AS ESTOQUE,
                CP1POSIC AS POSIC,
                0 AS DATACOMPRA,
                0 AS QTDECOMPRA,
                lp1preco as preco,
                lp1data as preco_ultima_alteracao,
                lp2preco as promo_preco,
                lp2datai as promo_inicio,
                lp2dataf as promo_fim,
                0 AS CUSTO,
                CP1LOCAL1 AS LOCAL2,
                CP1LOCAL2 AS LOCAL3,
                $sqlQtAndNFMonthUnions
                0 AS DATAVENDA,
                ' ' AS imagem
                FROM lp1
                inner JOIN PRODUTO ON lp1produto = CP1CODIG
                left JOIN lp2 ON CP1CODIG = LP2PRODUTO
                where lp1tabela = '01'
                )
                AS ANALISE
                GROUP BY CODIGO
                ORDER BY CODIGO
            ";
            Log::info($sql);
            /*
              TODO: Comparar com versão antiga, não possui join com lp2. Info de lp2 é por empresa, implementar importação independente
              Data ultima alteração também é por empresa, porém o preço hoje esta no produto...
             */

            $erpDatasource = 'import';
            if (CURRENT_DATASOURCE == Config::get('constants.DISTRIBUTOR_DATASOURCE'))
                $erpDatasource = 'importJK';

            $data = OrderSysUtils::convertToObj(DB::connection($erpDatasource)->select($sql));
        } else {
            $data = OrderSysUtils::convertToObj(DB::connection('import')->select("select * from tmp_erp_produto order by codigo limit 3"));
        }
        Log::info("Busca finalizada!");
        return $this->filterProductsToImport($data);
    }

    private function getERPProductsPurchase($company) {
        Log::info("Buscando dados de ultimas compras ERP...");
        $previousMonths = 4;  //para trazer registros mais antigos, casos contrario não atualiza as sinaleiras
        if (Config::get('app.env') == 'production' || Config::get('app.env') == 'dev') {
            $sql = "
               with Q_COMPRAS as (
                    SELECT CODIGO,
                    DATACOMPRA,
                    QTDE
                    FROM 
                    (SELECT MD3PRODUTO AS CODIGO,
                    MD3DATA AS DATACOMPRA,
                    MD3QUANTIDADE AS QTDE
                    FROM ITENSNOTA
                    INNER JOIN PRODUTO ON MD3PRODUTO = CP1CODIG
                    INNER JOIN NATUREZA ON MD3CDNOP = NATCODIG
                    WHERE MD3DATA >= " . OrderSysUtils::formatDate("Ymd", Carbon::today()->subMonths($this->monthsSaleHistory + $previousMonths)->startofMonth()) . "
                      AND MD3FILIAL = $company->code
                       -- and MD3PRODUTO between 123338  and 129339
                      AND NATTIPNF = 'E' AND MD3CDNOP IN (2102, 1102, 1101, 2101, 1403, 2403, 2401, 3102, 1910, 2910)
                      AND NATESTATISTICA = 1) AS ANALISE
                   ORDER BY CODIGO, DATACOMPRA
               )
               SELECT   Q.CODIGO,
                        MAX(Q.DATACOMPRA) DATACOMPRA,
                        (SELECT SUM(Q2.QTDE) FROM Q_COMPRAS Q2 WHERE Q2.CODIGO = Q.CODIGO AND Q2.DATACOMPRA = MAX(Q.DATACOMPRA)) QTDE
               FROM	Q_COMPRAS Q
               GROUP BY Q.CODIGO
               ORDER BY Q.CODIGO
            ";
            Log::info($sql);

            $erpDatasource = 'import';
            if (CURRENT_DATASOURCE == Config::get('constants.DISTRIBUTOR_DATASOURCE'))
                $erpDatasource = 'importJK';

            $data = OrderSysUtils::convertToObj(DB::connection($erpDatasource)->select($sql));
        } else {
            $data = OrderSysUtils::convertToObj(DB::connection('import')->select("select * from tmp_erp_qtd_compra order by codigo "));
        }
        Log::info("Busca finalizada!");
        return $this->filterProductsToImport($data);
    }

    private function filterProductsToImport($list) {
        $data = array();
        if (count($this->filterProductCodes) > 0) {
            Log::info("Importação reestringida aos seguintes produtos (codes): " . json_encode($this->filterProductCodes));
            foreach ($list as $record) {
                if (in_array($record->codigo, $this->filterProductCodes)) {
                    $data[] = $record;
                }
            }
        } else {
            $data = $list;
        }
        return $data;
    }

    private function generatePurchaseOrders($company) {
        Log::info("Iniciando manutenção de pedidos");

        $productFacade = new ProductFacade();
        $producMapAvgSale = array();
        foreach ($productFacade->getProductAvgSale($company->id) as $item) {
            $producMapAvgSale[$item->company_id . "_" . $item->product_id] = $item;
        }

        $purchaseOrderFacade = new PurchaseOrderFacade();
        $rawQuery = "status = ? and company_id = ?";
        $rawParams = array([Config::get('constants.STPO_GENERATED'), $company->id]);
        if (!empty($this->filterSupplierId)) {
            $rawQuery .= " and supplier_id = ?";
            $rawParams[] = $this->filterSupplierId;
        }
        foreach (PurchaseOrder::whereRaw($rawQuery, $rawParams)->get() as $po) {
            $purchaseOrderFacade->deletePurchaseOrder($po->id);
        }

        $currentSupplierId = null;
        $currentCompanyId = null;
        $currentPOItems = array();
        $restrictSupplier = "";
        if (!empty($this->filterSupplierId)) {
            $restrictSupplier = "and su.id = $this->filterSupplierId";
        }
        foreach (DB::select("select p.*,
                                    pc.company_id,
                                    pc.product_id,
                                    pc.stock
                               from product_company pc
                               join signal s on (s.id = pc.signal_id)
                               join product p on (p.id = pc.product_id)                               
                               join supplier su on (su.id = p.supplier_id)
                              where pc.company_id = $company->id 
                                $restrictSupplier                                
                                and s.gen_po = true
                                and p.not_buy = false
                                and p.active = true
                                and s.code in ('red','yellow','blue')
                                and p.product_group not in ( "
                . Config::get('constants.PRODUCT_GROUP_NATAL') . ","
                . Config::get('constants.PRODUCT_GROUP_IMPORTED')
                . ") 
                           order by p.supplier_id,
                                    pc.company_id") as $p) {

            if (!empty($currentSupplierId) && ($currentSupplierId != $p->supplier_id || $currentCompanyId != $p->company_id)) {
                $currentPO = new PurchaseOrder();

                $supplier = Supplier::find($currentSupplierId);
                $currentPO->company_id = $currentCompanyId;
                $currentPO->supplier_id = $currentSupplierId;
                $currentPO->phone = $supplier->phone;
                $currentPO->contact = $supplier->contact;
                $currentPO->mail = $supplier->mail;
                $currentPO->delivery_date = Carbon::today()->addDays(!empty($supplier->delivery_days) ? $supplier->delivery_days : 0);
                $currentPO->status = Config::get('constants.STPO_GENERATED');
                $currentPO->total = 0;
                $currentPO->freight = empty($supplier->freight_default) ? 'FOB' : $supplier->freight_default;
                $purchaseOrderFacade->savePurchaseOrder($currentPO, $currentPOItems);
                $currentPOItems = array();
            }
            if (isset($producMapAvgSale[$p->company_id . "_" . $p->product_id])) {
                $poItem = new PurchaseOrderItem();
                $poItem->product_id = $p->product_id;
                if (isset($p->box_qtty_jk) && !empty($p->box_qtty_jk)) {
                    $poItem->quantity_per_box = $p->box_qtty_jk;
                } else {
                    $poItem->quantity_per_box = 1;
                }
                $poItem->quantity = ceil($producMapAvgSale[$p->company_id . "_" . $p->product_id]->avg_sale * 22 * 5) - $p->stock;
                if ($poItem->quantity <= 0) {
                    $poItem->quantity = 1;
                }
                $priceValue = null;
                $local2Value = null;
                if ($p->local2 != null) {
                    $priceValue = $p->local2;
                    $local2Value = true;
                } else {
                    $priceValue = $p->cost;
                    $local2Value = false;
                }
                $poItem->price = $priceValue;
                $poItem->local2_value = $local2Value;
                $poItem->total = $poItem->price * $poItem->quantity;
                $currentPOItems[] = $poItem;
            } else {
                Log::info("Empresa_produto " . $p->company_id . "_" . $p->product_id . " não possui histórico de vendas recente para sugerir a quantidade a compra");
            }
            $currentSupplierId = $p->supplier_id;
            $currentCompanyId = $p->company_id;
        }
        //Persistir o ultimo pedido da iteração acima, que não é incluso devido a lógica de agrupamento
        if (!empty($currentSupplierId)) {
            $currentPO = new PurchaseOrder();
            $supplier = Supplier::find($currentSupplierId);

            $currentPO->company_id = $currentCompanyId;
            $currentPO->supplier_id = $currentSupplierId;
            $currentPO->phone = $supplier->phone;
            $currentPO->contact = $supplier->contact;
            $currentPO->mail = $supplier->mail;
            $currentPO->delivery_date = Carbon::today()->addDays(!empty($supplier->delivery_days) ? $supplier->delivery_days : 0);
            $currentPO->status = Config::get('constants.STPO_GENERATED');
            $currentPO->total = 0;
            $currentPO->freight = empty($supplier->freight_default) ? 'FOB' : $supplier->freight_default;
            $purchaseOrderFacade->savePurchaseOrder($currentPO, $currentPOItems);
        }

        Log::info("Manutenção de pedidos concluída!");
    }

    //Atualiza o estoque da jk banco distributor no produto do banco ordersys
    private function updateStockAndBoxQttyFromJKToOrderSys($companyId) {
        Log::info("Iniciando manutenção de estoque e quantidade de caixa JK -> OrderSys");
        $productsOrderSys = DB::connection(Config::get('constants.ORDERSYS_DATASOURCE'))
                ->select('select * from product');

        foreach ($this->productOrderSysList as $productDistributor) {
            if (count($this->filterProductCodes) == 0 ||
                    (count($this->filterProductCodes) > 0 && in_array($productDistributor->code, $this->filterProductCodes))) {

                if (!is_int($productDistributor->box_qtty)) {
                    Log::info("box_qtty deve ser um número inteiro [$productDistributor->stock,$productDistributor->box_qtty, $productDistributor->code]");
                    continue;
                }

                $keyDistributor = explode(" ", $productDistributor->name);

                foreach ($productsOrderSys as $productOrderSys) {
                    $keyOrderSys = explode(" ", $productOrderSys->name);

                    if (isset($this->productCompanyOrderSysList[$companyId . '_' . $productDistributor->id])) {
                        $productCompanyDistributor = $this->productCompanyOrderSysList[$companyId . '_' . $productDistributor->id];

                        if (is_array($keyOrderSys) && is_array($keyDistributor) && trim($keyOrderSys[0]) == trim($keyDistributor[0]) && ($productCompanyDistributor->stock != $productOrderSys->stock_jk || $productDistributor->box_qtty != $productOrderSys->box_qtty_jk)) {

                            Log::info("[$productDistributor->stock,$productDistributor->box_qtty,$productOrderSys->id]");

                            DB::connection(Config::get('constants.ORDERSYS_DATASOURCE'))
                                    ->update("update product set stock_jk = ?, box_qtty_jk = ? where id = ?", [$productCompanyDistributor->stock, $productDistributor->box_qtty, $productOrderSys->id]);

                            Log::info("Estoque JK atualizado no banco ordersys");
                            break;
                        }
                    }
                }
            }
        }
        Log::info("Finalizado manutenção de estoque e quantidade de caixa JK -> OrderSys");
    }

    private function ftERPDate($date, $addCurrentTime = false) {
        $result = null;
        if (strlen($date) == 8) {
            $result = substr($date, 0, 4) . "-" . substr($date, 4, 2) . "-" . substr($date, 6, 2);
        } else if (strlen($date) == 10 && substr_count($date, "-") == 2) {
            $result = $date;
        }

        if ($addCurrentTime)
            $result = $result . ' ' . date('H:i:s');

        return $result;
    }

    private function callgc() {
        Log::info("Chamando gc " . memory_get_usage());
        gc_collect_cycles();
        Log::info("Gc executado " . memory_get_usage());
    }

}

?>