<?php

namespace App\Libraries\Utils {


    use Config\Services;

    class ErpUtils extends BaseUtils
    {
        private static $iconvConvert = array('windows-1251', 'UTF-8');

        private static function _buildArrayReturn($data, $columns)
        {
            $return = array();
            foreach ($data as $item) {
                $obj = array();
                foreach ($columns as $input => $output) {
                    $obj[$output] = $item[$input];
                }
                $return[] = $obj;
            }
            return $return;
        }

        private static function _buildObjectsReturn($data, $columns)
        {
            $return = array();
            foreach ($data as $item) {
                $obj = new \stdClass();
                foreach ($columns as $input => $output) {
                    $obj->{$output} = $item->{$input};
                }
                $return[] = $obj;
            }

            return $return;
        }

        private static function _buildSingleReturn($data, $columns)
        {
            $return = new \stdClass();

            foreach ($columns as $input => $output) {
                $return->{$output} = $data->{$input};
            }
            return $return;
        }


        private static function _buildSingleReturnAsArray($data, $columns)
        {
            $return = array();

            foreach ($columns as $input => $output) {
                $return[$output] = $data[$input];
            }
            return $return;
        }


        public static function getAddressByID($addressID)
        {
            $inputOutput = array(
                'NOMER'    => 'NOMER',
                'IME'      => 'IME',
                'MOL'      => 'MOL',
                'POST_KOD' => 'POST_KOD',
                'ADRES'    => 'ADRES',
                'TEL'      => 'TEL',
                'KOMENTAR' => 'KOMENTAR',
                'GRAD'     => 'GRAD',
            );


            $address = Services::firebird()->table('KONTRAGENTI_OBEKTI')->select(implode(', ', $inputOutput))->where('NOMER', $addressID)->get()->getRow();

            $return = self::_buildSingleReturn($address, $inputOutput);
            unset($addresses);
            return $return;
        }

        public static function getAddressesByCompanyID($companyID)
        {
            $inputOutput = array(
                'NOMER'    => 'NOMER',
                'IME'      => 'IME',
                'POST_KOD' => 'POST_KOD',
                'ADRES'    => 'ADRES',
                'TEL'      => 'TEL',
                'KOMENTAR' => 'KOMENTAR',
                'GRAD'     => 'GRAD',
            );

            $addresses = Services::firebird()->table('KONTRAGENTI_OBEKTI')
                ->select(implode(', ', ($inputOutput)))
                ->where('KONTRAGENT_NOMER', $companyID)
                ->get()
                ->getResult();

            $return = self::_buildObjectsReturn($addresses, $inputOutput);
            unset($addresses);
            return $return;
        }


        public static function getCompanies($companyIDs = array())
        {
            $inputOutput = array(
                'COM_NOMER'               => 'COM_NOMER',
                'NOMER'                   => 'NOMER',
                'KONTR_GRUPA_NOMER'       => 'KONTR_GRUPA_NOMER',
                'KONTRAGENT'              => 'KONTRAGENT',
                'CENOVA_LISTA_NOMER'      => 'CENOVA_LISTA_NOMER',
                'TIP_CENA_NOMER'          => 'TIP_CENA_NOMER',
                'TIP_CENA_NOMER2'         => 'TIP_CENA_NOMER2',
                'TIP_CENA_NOMER3'         => 'TIP_CENA_NOMER3',
                'GRUPIKONTR_KOD'          => 'GRUPIKONTR_KOD',
                'NACHIN_PLASHTANE'        => 'NACHIN_PLASHTANE',
                'ZABRANA_ZA_PRODAZBA'     => 'ZABRANA_ZA_PRODAZBA',
                'PREDUPREZDENIE_PRODAZBA' => 'PREDUPREZDENIE_PRODAZBA',
                'MOL'                     => 'MOL',
                'DDSNOMER'                => 'DDSNOMER',
                'BULSTAT'                 => 'BULSTAT',
                'SEDALISHTE_ADRES'        => 'SEDALISHTE_ADRES',
                'SEDALISHTE_GRAD'         => 'SEDALISHTE_GRAD',
                'CREATED_ON'              => 'CREATED_ON',
                'SROK_NA_PLASHTANE_DNI'   => 'SROK_NA_PLASHTANE_DNI'
            );


            // Fetching Data
            $select = array(
                'COM_NOMER'               => 'KONTRAGENTI.COM_NOMER',
                'NOMER'                   => 'KONTRAGENTI.NOMER',
                'KONTR_GRUPA_NOMER'       => 'KONTR_GRUPA_NOMER',
                'KONTRAGENT'              => 'KONTRAGENT',
                'CENOVA_LISTA_NOMER'      => 'CENOVA_LISTA_NOMER',
                'TIP_CENA_NOMER'          => 'TIP_CENA_NOMER',
                'TIP_CENA_NOMER2'         => 'TIP_CENA_NOMER2',
                'TIP_CENA_NOMER3'         => 'TIP_CENA_NOMER3',
                'GRUPIKONTR_KOD'          => 'GRUPIKONTR_KOD',
                'NACHIN_PLASHTANE'        => 'NACHIN_PLASHTANE',
                'ZABRANA_ZA_PRODAZBA'     => 'ZABRANA_ZA_PRODAZBA',
                'PREDUPREZDENIE_PRODAZBA' => 'PREDUPREZDENIE_PRODAZBA',
                'MOL'                     => 'MOL',
                'DDSNOMER'                => 'DDSNOMER',
                'BULSTAT'                 => 'BULSTAT',
                'SEDALISHTE_ADRES'        => 'SEDALISHTE_ADRES',
                'SEDALISHTE_GRAD'         => 'SEDALISHTE_GRAD',
                'CREATED_ON'              => 'CREATED_ON',
                'SROK_NA_PLASHTANE_DNI'   => 'SROK_NA_PLASHTANE_DNI'
            );
            $firebird = Services::firebird()->table('KONTRAGENTI')->select(implode(',', $select))
                ->join('GRUPIKONTR', 'GRUPIKONTR.NOMER = KONTRAGENTI.KONTR_GRUPA_NOMER', 'LEFT');
            if (!empty($companyIDs)) {
                $firebird->whereIn('KONTRAGENTI.NOMER', $companyIDs);
            }
            $companies = $firebird->get()->getResult();
            // End fetching data


            if (count($companyIDs) == 1) {
                $return = self::_buildSingleReturn(current($companies), $inputOutput);
            } else {
                $return = self::_buildObjectsReturn($companies, $inputOutput);

            }

            unset($companies);
            return $return;
        }


        /**
         * @param $start
         * @param $end
         * @return void
         */
        public static function filterAccountsByDateRange($start, $end)
        {
            // Shutdown
            die();
            return Services::firebird()->table('KONTRAGENTI_LICA')
                ->where('CREATED_ON >=', $start)
                ->where('CREATED_ON <=', $end)
                ->where('EMAIL IS NOT NULL', NULL, NULL)
                ->get()
                ->getResult();
        }


        public static function getCompanyWithPriceType3()
        {
            $select = array(
                'KONTRAGENTI.NOMER',
                'KONTRAGENTI.COM_NOMER',
                'KONTRAGENTI.CREATED_BY',
                'KONTRAGENTI.CREATED_ON',
                'KONTRAGENTI.KONTRAGENT',
                'KONTRAGENTI.KRIME',
                'KONTRAGENTI.BULSTAT',
                'KONTRAGENTI.DDSNOMER',
                'KONTRAGENTI.CL_USER_FIELD_1',
                'KONTRAGENTI.NACHIN_PLASHTANE',
                'TIPOVE_CENI.FULLNAME AS TIP_CENA_NOMER3',
            );
            return Services::firebird()
                ->table('KONTRAGENTI')
                ->select(implode(',', $select))
                ->join('TIPOVE_CENI', 'TIPOVE_CENI.NOMER = KONTRAGENTI.TIP_CENA_NOMER3', 'LEFT')
                ->get()
                ->getResult();
        }


        public static function getCompaniesExcept($companyID = array())
        {
            return Services::firebird()->table('KONTRAGENTI')->select('NOMER')->whereNotIn('NOMER', $companyID)->get()->getResult();
        }


        public static function getCompanyByEmail($email)
        {
            return Services::firebird()->table('KONTRAGENTI_LICA')
                ->select('IME AS Names')
                ->where('UPPER(EMAIL)', strtoupper($email))
                ->get()
                ->getRow();
        }

        public static function getCompanyDebts($date, $companyIDs)
        {
            $allDebSelect = 'KONTRAGENT_NOMER, DALG, DOCUMENT_NOMER, OBEKT_NOMER';
            $sql = "SELECT {$allDebSelect} FROM SPRAVKA_DALGOVE_OT_PRODAZBI(NULL, '{$date}', NULL, 'IN({$companyIDs})', NULL, 0) as price";
            return Services::firebird()->query($sql)->getResult();
        }

        public static function getCompanyOverDueSales($date, $companyIDs)
        {
            $overDueSalesSelect = 'DOCUMENT_NOMER, DOCUMENT_DATA, OBEKT_NOMER, KONTRAGENT_NOMER, STOINOST_BEZ_DDS, STOINOST_S_DDS, SROK_NA_PLASHTANE, PLATENI, DALG';
            $sql = "SELECT {$overDueSalesSelect} FROM SPRAVKA_DALGOVE_OT_PRODAZBI(NULL, '{$date}', NULL, 'IN({$companyIDs})', NULL, 1) as price";
            return Services::firebird()->query($sql)->getResult();
        }

        public static function getCompanyPaidProfit($date, $companyIDs)
        {
            $sql = "SELECT KONTRAGENT_NOMER,OBEKT_NOMER,PLATENI  FROM SPRAVKA_DALG_KONTRAGENTI_VIDOVE(NULL, '{$date}', 1, NULL, 'IN({$companyIDs})', 1, NULL, NULL) as price";
            return Services::firebird()->query($sql)->getResult();
        }


        public static function getProductByID($productID, $asArray = false)
        {

            $inputOutput = array(
                'NOMER'         => 'NOMER',
                'IME'           => 'IME',
                'IME$1'         => 'IME$1',
                'OPISANIE_HTML' => 'OPISANIE_HTML',
            );


            $firebird = Services::firebird()->table('POZICII')->select(implode(', ', ($inputOutput)))->where('NOMER', $productID);
            if ($asArray) {
                $product = $firebird->get()->getRowArray();
                $return = self::_buildSingleReturnAsArray($product, $inputOutput);
            } else {
                $product = $firebird->get()->getRow();
                $return = self::_buildSingleReturn($product, $inputOutput);
            }
            unset($product);
            return $return;
        }

        public static function getProducts($asArray = false)
        {


            $cacheName = 'ErpUtils_getProducts' . intval($asArray);
            if (!$return = cache($cacheName)) {
                $inputOutput = array(
                    'NOMER'                          => 'NOMER',
                    'IME'                            => 'IME',
                    'IME$1'                          => 'IME$1',
                    'IME$2'                          => 'IME$2',
                    'IZKLUCHEN'                      => 'IZKLUCHEN',
                    'BEZNALICHEN'                    => 'BEZNALICHEN',
                    'OPISANIE_HTML'                  => 'OPISANIE_HTML',
                    'MIARKA_COEF'                    => 'MIARKA_COEF',
                    'MATERIAL'                       => 'MATERIAL',
                    'USER_FIELD_6'                   => 'USER_FIELD_6',
                    'USER_FIELD_5'                   => 'USER_FIELD_5',
                    'USER_FIELD_3'                   => 'USER_FIELD_3',
                    'GRUPA_NOMER'                    => 'GRUPA_NOMER',
                    'PREDUPREZDENIE_MESTENE'         => 'PREDUPREZDENIE_MESTENE',
                    'PREDUPREZDENIE_ZAIAVKA_MESTENE' => 'PREDUPREZDENIE_ZAIAVKA_MESTENE',
                    'PREDUPREZDENIE_DOSTAVKA'        => 'PREDUPREZDENIE_DOSTAVKA',
                    'PREDUPREZDENIE_ZAIAVKA_DOST'    => 'PREDUPREZDENIE_ZAIAVKA_DOST',
                );

                $firebird = Services::firebird()->table('POZICII')->select(implode(', ', ($inputOutput)));
                if ($asArray) {
                    $products = $firebird->get()->getResultArray();
                    $return = self::_buildArrayReturn($products, $inputOutput);
                } else {
                    $products = $firebird->get()->getResult();
                    $return = self::_buildObjectsReturn($products, $inputOutput);
                }

                unset($products);
                cache()->save($cacheName, $return, 300);
            }

            return $return;
        }

        public static function getProductMeasures()
        {
            $products = self::getProducts();

            $return = array();
            foreach ($products as $key => $product) {
                $item = new \stdClass();
                $item->ProductCode = $product->NOMER;
                $item->Divider = $product->MIARKA_COEF;
                $return[] = $item;
            }
            $products = null;
            unset($products);

            return $return;
        }

        public static function getNoProductsTurnover()
        {
            $products = self::getProducts();
            $return = array();
            foreach ($products as $key => $product) {
                $item = new \stdClass();
                $item->NOMER = $product->NOMER;
                $item->USER_FIELD_6 = $product->USER_FIELD_6;
                $item->USER_FIELD_5 = $product->USER_FIELD_5;
                $item->USER_FIELD_3 = $product->USER_FIELD_3;
                $item->PREDUPREZDENIE_MESTENE = $product->PREDUPREZDENIE_MESTENE;
                $item->PREDUPREZDENIE_ZAIAVKA_MESTENE = $product->PREDUPREZDENIE_ZAIAVKA_MESTENE;
                $item->PREDUPREZDENIE_DOSTAVKA = $product->PREDUPREZDENIE_DOSTAVKA;
                $item->PREDUPREZDENIE_ZAIAVKA_DOST = $product->PREDUPREZDENIE_ZAIAVKA_DOST;
                $return[] = $item;
            }
            $products = null;
            unset($products);

            return $return;
        }


        public static function getAverageDeliveryPrice()
        {
            $sql = "select t1.SREDNA_DOST_CENA, t1.POS_NOMER
from dost_ceni t1
 inner join (
   select
    MAX(data) as t2Date, POS_NOMER as t2Nomer
    from dost_ceni
    
    group by dost_ceni.pos_nomer
) as t2 on t1.pos_nomer = t2.t2Nomer AND t1.data = t2.t2Date";


            return Services::firebird()->query($sql)->getResult();
        }


        public static function getProductGroups()
        {
            $products = self::getProducts();
            $return = array();

            foreach ($products as $key => $product) {

                $item = new \stdClass();
                $item->NOMER = $product->NOMER;
                $item->GRUPA_NOMER = $product->GRUPA_NOMER;
                $return[] = $item;
            }

            $products = null;
            unset($products);
            return $return;
        }

        public static function getProductsForTurnover()
        {
            $products = self::getProducts();
            $return = array();

            foreach ($products as $key => $product) {

                if ($product->IZKLUCHEN == 0 and $product->BEZNALICHEN == 0 and $product->MATERIAL == 0) {
                    $item = new \stdClass();
                    $item->NOMER = $product->NOMER;
                    $item->IME = $product->IME;
                    $return[] = $item;
                }
            }

            $products = null;
            unset($products);
            return $return;
        }


        public static function getProductsByCategoryID($categoryID)
        {
            $select[] = 'GRUPIPOS2.NOMER AS ProductBrandCode';
            $select[] = 'GRUPIPOS2.FULLNAME AS ProviderBrandName';
            $select[] = 'POZICII.IME AS ProductName';
            $select[] = 'POZICII.IME$1 AS ProductNameWebsite';
            $select[] = 'POZICII.IME$2 AS ProductNameWebsiteEN';
            $select[] = 'POZICII.OPISANIE_HTML AS ProductDescription';
            $select[] = 'POZICII.USER_FIELD_1 AS CatalogNumber';
            $select[] = 'POZICII.USER_FIELD_3 AS Label';
            $select[] = 'BARCODES.BARCODE_LIST AS Barcodes';
            $select[] = 'POZICII.NOMER AS ProductCode';
            $select[] = 'POZICII.USER_FIELD_4 AS SelmaticRank';
            $select[] = 'POZICII.GOL_OP_X AS SizeX';
            $select[] = 'POZICII.GOL_OP_Y AS SizeY';
            $select[] = 'POZICII.GOL_OP_Z AS SizeZ';
            $select[] = 'POZICII.GOL_OP_BRUTO AS Weight';
            $select[] = 'POZICII.USER_FIELD_6 AS Available';
            $select[] = 'POZICII.USER_FIELD_5 AS ProductCategoryCode';
            $select[] = 'POZICII.USER_FIELD_5 AS ExportWebsites';
            $select[] = 'POZICII.OSNOVEN_DOSTAVCHIK_NOMER AS DefaultProvider';
            $select[] = 'GRUPIPOS.GRUPIPOS_KOD AS CategoryGroupCode';
            $select[] = 'POZICII.DDS_PROC AS Vat';
            $select[] = 'POZICII.MIARKA_COEF AS SmallPackage';
            $select[] = 'MIARKI.MIARKA AS FirstMeasureName';
            $select[] = 'MIARKI2.MIARKA AS SecondMeasureName';


            return Services::firebird()
                ->table('POZICII')
                ->select(implode(',', $select))
                ->join('GRUPIPOS', 'GRUPIPOS.NOMER = POZICII.GRUPA_NOMER', 'LEFT')
                ->join('GRUPIPOS2', 'GRUPIPOS2.NOMER = POZICII.GRUPA_NOMER2', 'LEFT')
                ->join('MIARKI', 'MIARKI.NOMER = POZICII.MIARKA_NOMER', 'LEFT')
                ->join('MIARKI AS MIARKI2', 'MIARKI2.NOMER = POZICII.MIARKA_NOMER2', 'LEFT')
                ->join('(SELECT LIST(BARCODE_POSNOMER.BARCODE) AS BARCODE_LIST, POS_NOMER AS B_NOMER FROM BARCODE_POSNOMER GROUP BY POS_NOMER) AS BARCODES', 'BARCODES.B_NOMER = POZICII.NOMER', 'LEFT', false)
                ->groupStart()
                ->orLike('USER_FIELD_5', '1#' . $categoryID)
                ->orLike('USER_FIELD_5', '2#' . $categoryID)
                ->orLike('USER_FIELD_5', '12#' . $categoryID)
                ->groupEnd()
                ->where('IZKLUCHEN', 0)
                ->where('BEZNALICHEN', 0)
                ->get()
                ->getResult();
        }

        public static function getProductCategories()
        {

            $inputOutput = array(
                'ID'             => 'ID',
                'GROUP_NAME'     => 'GROUP_NAME',
                'PARRENT_GROUP'  => 'PARRENT_GROUP',
                'SORTINDEX'      => 'SORTINDEX',
                'NOMER'          => 'NOMER',
                'CATEGORY_LEFT'  => 'CATEGORY_LEFT',
                'CATEGORY_RIGHT' => 'CATEGORY_RIGHT',
            );

            $categories = Services::firebird()->table('W_WEBSHOPPRODUCTGROUPS')->select(implode(', ', ($inputOutput)))->get()->getResult();
            $return = self::_buildObjectsReturn($categories, $inputOutput);
            unset($categories);
            return $return;
        }


        public static function getProductDeliveryPrice($productID)
        {
            $query = "select * from GET_DOST_CENA(NULL, current_date, {$productID}) as price";
            return Services::firebird()->query($query)->getRow();
        }

        public static function getProductGroupSortingCode()
        {

            return Services::firebird()->table('POZICII')
                ->select('GRUPIPOS.GRUPIPOS_KOD, POZICII.NOMER')
                ->join('GRUPIPOS', 'GRUPIPOS.NOMER = POZICII.GRUPA_NOMER', 'INNER')
                ->groupStart()
                ->orLike('USER_FIELD_5', '1#')
                ->orLike('USER_FIELD_5', '2#')
                ->orLike('USER_FIELD_5', '12#')
                ->groupEnd()
                ->where('IZKLUCHEN', 0)
                ->where('BEZNALICHEN', 0)
                ->get()
                ->getResult();
        }

        public static function getProductRelativeProducts($productID)
        {
            return self::singleton()->firebird
                ->table('POZICII_RELATED')
                ->where('POZICII_RELATED.POS_NOMER', $productID)
                ->get()
                ->getResult();
        }

        public static function getOfferByID($offerID)
        {

            $inputOutput = array(
                'POS_NOMER' => 'POS_NOMER',
            );

            $offerProducts = Services::firebird()->table('OFERTI')
                ->select(implode(', ', ($inputOutput)))
                ->join('OFERTI_DE', 'OFERTI.NOMER = OFERTI_DE.OFERTA_NOMER')
                ->where('OFERTI.NOMER', $offerID)
                ->get()
                ->getResult();

            $return = self::_buildObjectsReturn($offerProducts, $inputOutput);
            unset($offerProducts);
            return $return;
        }


        // Неизпълнени оферти/заявки
        // TIP_OFERTA 2 - Заявка - Заявката е ангажиращ документ, с който в склада се "запазват" количествата от съответните артикули, като информацията за това какво е заявено е достъпна от съответните справки и е сравнима с текущо наличните количества.
        // STATUS 0 - Неизпълнена

        public static function getUnfulfilledRequest($startDate, $endDate, $storeID)
        {
            return self::singleton()->firebird
                ->table('OFERTI')
                ->select('OFERTI.NOMER, OFERTI_DE.POS_NOMER, OFERTI_DE.KOL')
                ->join('OFERTI_DE', 'OFERTI_DE.OFERTA_NOMER = OFERTI.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = OFERTI_DE.POS_NOMER')
                ->where('OFERTI.STATUS', 0)
                ->where('OFERTI.TIP_OFERTA ', 2)
                ->where('OFERTI.DATA >', $startDate)
                ->where('OFERTI.DATA <=', $endDate)
                ->where('OFERTI.OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->get()
                ->getResult();
        }


        // Продажби които са в склана и не са взети
        // STATUS_PRODAZBA 1 -  стоката е още в склада, не е взета от клиента
        // TIP 0 3 - Обикновена, По заявка
        public static function getUnPickedSales($startDate, $endDate, $storeID)
        {
            return Services::firebird()->table('PRODAZBI')
                ->select('PRODAZBI.NOMER, PRODAZBI_DE.POS_NOMER, PRODAZBI_DE.KOL')
                ->join('PRODAZBI_DE', 'PRODAZBI_DE.PRODAZBA_NOMER = PRODAZBI.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = PRODAZBI_DE.POS_NOMER')
                ->where('PRODAZBI.STATUS_PRODAZBA ', 1)
                ->where('PRODAZBI.ANULIRAN ', 0)
                ->where('PRODAZBI.DATA >', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->where('PRODAZBI.OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->groupStart()
                ->orWhere('PRODAZBI.TIP', 0)
                ->orWhere('PRODAZBI.TIP', 3)
                ->groupEnd()
                ->get()
                ->getResult();
        }


        //    STATUS
        //    0, Неизпълнена
        //    1, Изпълнена
        //    2, Отказана
        public static function getOutstandingRequests($startDate, $endDate, $storeID)
        {
            return self::singleton()->firebird
                ->table('ZAIAVKI')
                ->select('ZAIAVKI.NOMER, ZAIAVKI_DE.POS_NOMER, ZAIAVKI_DE.KOL')
                ->join('ZAIAVKI_DE', 'ZAIAVKI_DE.ZAIAVKA_NOMER = ZAIAVKI.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = ZAIAVKI_DE.POS_NOMER')
                ->where('ZAIAVKI.STATUS', 0)
                ->where('ZAIAVKI.DATA >', $startDate)
                ->where('ZAIAVKI.DATA <=', $endDate)
                ->where('ZAIAVKI.OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->get()
                ->getResult();
        }


        //    STATUS_DOSTAVKA
        //    null пристигнал
        //    1 непристигнал
        public static function getUnfinishedDeliveries($startDate, $endDate, $storeID)
        {

            return self::singleton()->firebird
                ->table('DOSTAVKI')
                ->select('DOSTAVKI_DE.POS_NOMER, (DOSTAVKI_DE.KOL)')
                ->join('DOSTAVKI_DE', 'DOSTAVKI_DE.DOSTAVKA_NOMER = DOSTAVKI.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = DOSTAVKI_DE.POS_NOMER')
                ->where('DOSTAVKI.STATUS_DOSTAVKA', 1)
                ->where('DOSTAVKI.DATA >', $startDate)
                ->where('DOSTAVKI.DATA <=', $endDate)
                ->where('DOSTAVKI.OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->get()
                ->getResult();

        }


        public static function getSalesByObjectExcludingCompanies($startDate, $endDate, $storeID, $excludeCompanies = array())
        {
            $builder = self::singleton()->firebird->table('PRODAZBI')
                ->select('PRODAZBI_DE.POS_NOMER, OBEKT_NOMER, PRODAZBI_DE.KOL, DATA')
                ->join('PRODAZBI_DE', 'PRODAZBI_DE.PRODAZBA_NOMER = PRODAZBI.NOMER', 'INNER')
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', NULL, NULL)
                ->where('PRODAZBI.DATA >', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->where('OBEKT_NOMER', $storeID);


            if (!empty($excludeCompanies)) {

                $excludeContragents = array_filter(array_unique(array_map('intval', explode(',', $excludeCompanies))));
                if (!empty($excludeContragents)) {
                    $builder->whereNotIn('PRODAZBI.KONTRAGENT_NOMER', $excludeContragents);
                }
            }
            return $builder->get()->getResult();

        }

        public static function getSalesRepresentative()
        {

            $inputOutput = array(
                'REF_KONTRAGENTI' => 'REF_KONTRAGENTI',
                'LIST'            => 'LIST'
            );

            return Services::firebird()->table('PROPERTIES_LOG')
                ->select('REF_KONTRAGENTI, LIST((CAST(IME as varchar(30000)) || \' - \' || CAST(STOINOST as varchar(30000))))')
                ->join('PROPERTIES', 'PROPERTIES.NOMER = PROPERTIES_LOG.PROPERTY_NOMER')
                ->whereIn('PROPERTY_NOMER', array(1, 2))
                ->where('REF_KONTRAGENTI >', 1)
                ->groupBy('REF_KONTRAGENTI')
                ->get()
                ->getResult();
        }

        public static function getDateSalesRange($startDate, $endDate)
        {
            $select = array(
                'PRODAZBI.DATA',
                'KONTRAGENT_NOMER'
            );


            return Services::firebird()
                ->table('PRODAZBI')
                ->select(implode(',', $select))
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', NULL, NULL)
                ->where('PRODAZBI.DATA >=', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->get()
                ->getResult();
        }

        public static function getSalesProductsBySaleIDs($sales)
        {


            $select = array(
                'PRODAZBI_DE.NOMER',
                'PRODAZBA_NOMER',
                'POS_NOMER',
                'POZICII.IME',
                'KOL',
                'TO_PROC',
                'MIARKA_NOMER',
                'ED_CENA',
                'ED_CENA_TO',
                'ED_CENA_S_DDS',
                'STOINOST_S_DDS',
                'STOINOST_BEZ_DDS',
                'GRUPIPOS.FULLNAME'
            );

            if (empty($sales)) {
                return array();
            }


            $result = Services::firebird()->table('PRODAZBI_DE')
                ->select(implode(',', $select))
                ->join('POZICII', 'PRODAZBI_DE.POS_NOMER = POZICII.NOMER')
                ->join('GRUPIPOS', 'GRUPIPOS.NOMER = POZICII.GRUPA_NOMER')
                ->whereIn('PRODAZBI_DE.PRODAZBA_NOMER', $sales)
                ->get()
                ->getResult();


            $return = array();
            foreach ($result as $item) {
                $return[$item->PRODAZBA_NOMER][] = $item;
            }
            return $return;

        }

        public static function getSaleProductsByOfferIDs($offersIDs)
        {
            return Services::firebird()->table('PRODAZBI')
                ->select('PRODAZBI.NOMER, PRODAZBI.STATUS_PRODAZBA,  PRODAZBI_DE.POS_NOMER, ED_CENA_TO, PRODAZBI_DE.DDS_PROC, PRODAZBI_DE.STOINOST_S_DDS,  PRODAZBI_DE.STOINOST_BEZ_DDS, KOL, METRO_PORACHKA_NOMER, POZICII.IME, PRODAZBI.CREATED_ON')
                ->join('PRODAZBI_DE', 'PRODAZBI.NOMER = PRODAZBI_DE.PRODAZBA_NOMER')
                ->join('POZICII', 'POZICII.NOMER = PRODAZBI_DE.POS_NOMER')
                ->whereIn('METRO_PORACHKA_NOMER', $offersIDs)
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', false, false)
                ->orderBy('PRODAZBI.NOMER', 'DESC')
                ->orderBy('PRODAZBI_DE.POS_NOMER', 'DESC')
                ->get()
                ->getResult();
        }

        public static function getSelmaticTotals($offersIDs)
        {
            $dataResults = self::getSaleProductsByOfferIDs($offersIDs);

            $return = array();

            foreach ($dataResults as $dataResult) {
                $return[$dataResult->METRO_PORACHKA_NOMER]['NOMER'] = $dataResult->NOMER;
                $return[$dataResult->METRO_PORACHKA_NOMER]['METRO_PORACHKA_NOMER'] = $dataResult->METRO_PORACHKA_NOMER;
                $return[$dataResult->METRO_PORACHKA_NOMER]['STATUS_PRODAZBA'] = $dataResult->STATUS_PRODAZBA;
                $return[$dataResult->METRO_PORACHKA_NOMER]['PRODUCTS'][] = (object)array(
                    'PRODAZBA_NOMER'   => $dataResult->NOMER,
                    'STOINOST_S_DDS'   => $dataResult->STOINOST_S_DDS,
                    'STOINOST_BEZ_DDS' => $dataResult->STOINOST_BEZ_DDS,

                );

                @$return[$dataResult->METRO_PORACHKA_NOMER]['TOTAL_WITH_VAT'] += number_format($dataResult->STOINOST_S_DDS, 4, '.', '');
                @$return[$dataResult->METRO_PORACHKA_NOMER]['TOTAL_WITHOUT_VAT'] += number_format($dataResult->STOINOST_BEZ_DDS, 4, '.', '');

            }
            return $return;
        }

        public static function getSalesGetIncludedSales($sales)
        {
            $select = array(
                'PRODAZBI.NOMER AS NOMER',
                'PRODAZBI.KONTRAGENT_NOMER AS KONTRAGENT_NOMER',
                'PRODAZBI.PRIKLUCHEN AS PRIKLUCHEN',
                'PRODAZBI.FAKTURA_NOMER AS FAKTURA_NOMER',
                'PRODAZBI.FAKTURA_DATA AS FAKTURA_DATA',
                'PRODAZBI.NACHIN_PLASHTANE AS NACHIN_PLASHTANE',
                'PRODAZBI.SROK_NA_PLASHTANE AS SROK_NA_PLASHTANE',
                'PRODAZBI.SASTAVITEL AS SASTAVITEL',
                'PRODAZBI.SASTAVITEL_NOMER AS SASTAVITEL_NOMER',
                'PRODAZBI.STATUS_PRODAZBA AS STATUS_PRODAZBA',
                'PRODAZBI.METRO_PORACHKA_NOMER AS METRO_PORACHKA_NOMER',
                'PRODAZBI.KOMENTAR AS KOMENTAR',
                'KONTRAGENTI_OBEKTI.IME AS OBEKT_IME',
                'KONTRAGENTI_OBEKTI.ADRES AS ADRES',
                'KONTRAGENTI_OBEKTI.GRAD AS OBEKT_GRAD',
                'KONTRAGENTI_OBEKTI.MOL AS OBEKT_MOL',
                'KONTRAGENTI_OBEKTI.KONTRAGENT_NOMER AS KONTRAGENT_NOMER',
            );

            $sales = Services::firebird()->table('PRODAZBI')
                ->select(implode(',', $select))
                ->whereIn('PRODAZBI.NOMER', $sales)
                ->join('KONTRAGENTI_OBEKTI', 'PRODAZBI.KONTRAGENT_OBEKT_NOMER = KONTRAGENTI_OBEKTI.NOMER', 'LEFT')
                ->where('PRODAZBI.PRIKLUCHEN', 1)
                ->get()
                ->getResult();


            return $sales;
        }

        public static function getInvoices($companyID)
        {
            $select = array(
                'PRODAZBI.NOMER AS NOMER',
                'PRODAZBI.KONTRAGENT_NOMER AS KONTRAGENT_NOMER',
                'PRODAZBI.PRIKLUCHEN AS PRIKLUCHEN',
                'PRODAZBI.FAKTURA_NOMER AS FAKTURA_NOMER',
                'PRODAZBI.FAKTURA_DATA AS FAKTURA_DATA',
                'PRODAZBI.NACHIN_PLASHTANE AS NACHIN_PLASHTANE',
                'PRODAZBI.SROK_NA_PLASHTANE AS SROK_NA_PLASHTANE',
                'PRODAZBI.SASTAVITEL AS SASTAVITEL',
                'PRODAZBI.SASTAVITEL_NOMER AS SASTAVITEL_NOMER',
                'PRODAZBI.STATUS_PRODAZBA AS STATUS_PRODAZBA',
                'PRODAZBI.METRO_PORACHKA_NOMER AS METRO_PORACHKA_NOMER',
                'PRODAZBI.KOMENTAR AS KOMENTAR',
                'KONTRAGENTI_OBEKTI.IME AS OBEKT_IME',
                'KONTRAGENTI_OBEKTI.ADRES AS ADRES',
                'KONTRAGENTI_OBEKTI.GRAD AS OBEKT_GRAD',
                'KONTRAGENTI_OBEKTI.MOL AS OBEKT_MOL',
                'KONTRAGENTI_OBEKTI.KONTRAGENT_NOMER AS KONTRAGENT_NOMER',
            );

            $masterIncludedSales = array();
            $includedSales = Services::firebird()->table('PRODAZBI')
                ->select(implode(',', $select))
                ->join('KONTRAGENTI_OBEKTI', 'PRODAZBI.KONTRAGENT_OBEKT_NOMER = KONTRAGENTI_OBEKTI.NOMER', 'LEFT')
                ->where('PRODAZBI.KONTRAGENT_NOMER', $companyID)
                ->where('PRODAZBI.PRIKLUCHEN', 1)
                ->like('PRODAZBI.KOMENTAR', iconv('UTF-8', 'windows-1251', 'Включена в Продажба №'))
                ->orderBy('PRODAZBI.DATA_DAN', 'DESC')
                ->orderBy('PRODAZBI.METRO_PORACHKA_NOMER', 'ASC')
                ->get()
                ->getResult();

            foreach ($includedSales as $includedSale) {
                $matches = array();
                preg_match('/Включена в Продажба №.*?;/', $includedSale->KOMENTAR, $matches);
                if (!empty($matches)) {
                    $code = str_replace('Включена в Продажба №', '', $matches[0]);
                    $code = trim($code, ';');
                    $masterIncludedSales[$code][$includedSale->NOMER] = $includedSale;
                }
            }


            $invoices = Services::firebird()->table('PRODAZBI')
                ->select(implode(',', $select))
                ->join('KONTRAGENTI_OBEKTI', 'PRODAZBI.KONTRAGENT_OBEKT_NOMER = KONTRAGENTI_OBEKTI.NOMER', 'LEFT')
                ->where('PRODAZBI.KONTRAGENT_NOMER', $companyID)
                ->where('PRODAZBI.PRIKLUCHEN', 1)
                ->where('PRODAZBI.FAKTURA_NOMER IS NOT NULL', null, false)
                ->orderBy('PRODAZBI.DATA_DAN', 'DESC')
                ->orderBy('PRODAZBI.METRO_PORACHKA_NOMER', 'ASC')
                ->get()
                ->getResult();

            $reverse = array();
            foreach ($invoices as $laKey => $invoice) {

                $includedSaleIDs = array();
                $includedSales = array();
                if (strval($invoice->STATUS_PRODAZBA) === '') {
                    $includedSales[$invoice->NOMER] = current(self::getSalesGetIncludedSales([$invoice->NOMER]));
                    $includedSaleIDs[] = $invoice->NOMER;
                }


                if (isset($masterIncludedSales[$invoice->NOMER])) {
                    foreach ($masterIncludedSales[$invoice->NOMER] as $key => $masterIncludedSale) {
                        $includedSales[$key] = $masterIncludedSale;
                    }
                }

                if (empty($includedSales)) {
                    $invoice->FAKTURA_NOMER;
                    $reverseMatches = array();
                    preg_match('/Включва Продажби №.*?;/', $invoice->KOMENTAR, $reverseMatches);
                    if (!empty($reverseMatches)) {
                        $reverseCodes = str_replace('Включва Продажби №', '', $reverseMatches[0]);
                        $reverseCodes = trim($reverseCodes, ';');
                        $reverseCodes = explode(',', $reverseCodes);
                        echo '<pre>' . print_r($invoice, true) . '</pre>';
                        $reverseSales = self::getSalesGetIncludedSales($reverseCodes);
                        if (!empty($reverseSales)) {
                            foreach ($reverseSales as $reverseSale) {
                                $includedSales[$reverseSale->NOMER] = $reverseSale;
                            }
                        }
                    }
                }

                $salesProducts = self::getSalesProductsBySaleIDs(array_keys($includedSales));

                $return[$invoice->FAKTURA_NOMER]['Date'] = $invoice->FAKTURA_DATA;
                $return[$invoice->FAKTURA_NOMER]['Company'] = $companyID;
                $return[$invoice->FAKTURA_NOMER]['TotalWithVat'] = 0;
                $return[$invoice->FAKTURA_NOMER]['TotalWithoutVat'] = 0;

                foreach ($includedSales as $includedSale) {

                    $website = 0;
                    $budget = null;
                    $userName = null;
                    $costs = null;
                    if ($includedSale->METRO_PORACHKA_NOMER) {
                        $websiteID = self::singleton()->db->table('orders')
                            ->select('orders.OrderID, BudgetName,order_account_snippets.Names, Costs')
                            ->where('OfferID', str_replace('99$', '', $includedSale->METRO_PORACHKA_NOMER))
                            ->join('order_account_snippets', 'order_account_snippets.OrderID = orders.OrderID')
                            ->join('hierarchy_budgets', 'hierarchy_budgets.HierarchyBudgetID = orders.HierarchyBudgetID', 'LEFT')
                            ->get()
                            ->getRow();
                        if ($websiteID) {
                            $website = $websiteID->OrderID;
                            $budget = $websiteID->BudgetName;
                            $userName = $websiteID->Names;
                            $costs = $websiteID->Costs;
                        }
                    }

                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['TotalWithVat'] = 0;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['TotalWithoutVat'] = 0;

                    $products = @$salesProducts[$includedSale->NOMER];

                    if (!empty($products)) {
                        foreach ($products as $product) {
                            $return[$invoice->FAKTURA_NOMER]['TotalWithVat'] += $product->STOINOST_S_DDS;
                            $return[$invoice->FAKTURA_NOMER]['TotalWithoutVat'] += $product->STOINOST_BEZ_DDS;
                            $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['TotalWithVat'] += $product->STOINOST_S_DDS;
                            $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['TotalWithoutVat'] += $product->STOINOST_BEZ_DDS;

                        }
                    }


                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['WebsiteSaleID'] = $website;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['BudgetName'] = $budget;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['UserName'] = $userName;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['Costs'] = $costs;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['FAKTURA_DATA'] = $includedSale->FAKTURA_DATA;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['FAKTURA_NOMER'] = $invoice->FAKTURA_NOMER;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['NACHIN_PLASHTANE'] = $includedSale->NACHIN_PLASHTANE;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['SROK_NA_PLASHTANE'] = $includedSale->SROK_NA_PLASHTANE;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['SASTAVITEL'] = $includedSale->SASTAVITEL;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['SASTAVITEL_NOMER'] = $includedSale->SASTAVITEL_NOMER;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['OBEKT_GRAD'] = $includedSale->OBEKT_GRAD;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['OBEKT_MOL'] = $includedSale->OBEKT_MOL;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['OBEKT_IME'] = $includedSale->OBEKT_IME;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['ADRES'] = $includedSale->ADRES;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['sale_number'] = $includedSale->NOMER;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['metro'] = $includedSale->METRO_PORACHKA_NOMER;
                    $return[$invoice->FAKTURA_NOMER]['Sales'][$includedSale->NOMER]['products'] = $products;
                }


            }

            if (is_array($return) and !empty($return)) {
                foreach ($return as $laKey => $invoice) {
                    if (!isset($invoice['Sales'])) {
                        unset($return[$laKey]);
                    }
                }
            }

            return $return;
        }


        public static function getInvoiceByNumber($invoiceID)
        {
            return Services::firebird()->table('PRODAZBI')
                ->select('PRODAZBI.*')
                ->where('FAKTURA_NOMER', $invoiceID)
                ->get()
                ->getRow();

        }

        public static function getSalesObjectByDateRange($startDate, $endDate)
        {
            return Services::firebird()
                ->table('PRODAZBI')
                ->select('KONTRAGENT_NOMER, LIST(OBEKTI.OBEKT)')
                ->join('OBEKTI', 'OBEKTI.NOMER = PRODAZBI.OBEKT_NOMER')
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', NULL, NULL)
                ->where('PRODAZBI.DATA >=', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->groupBy('KONTRAGENT_NOMER')
                ->get()
                ->getResult();
        }

        //стоката е взета от клиента
        public static function getFinalSalesGroupByCompany($startDate, $endDate)
        {


            return Services::firebird()
                ->table('PRODAZBI')
                ->select('KONTRAGENT_NOMER, SUM(STOINOST_BEZ_DDS)')
                ->join('PRODAZBI_DE', 'PRODAZBI.NOMER = PRODAZBI_DE.PRODAZBA_NOMER')
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', NULL, NULL)
                ->where('PRODAZBI.DATA >=', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->like('METRO_PORACHKA_NOMER', '99$')
                ->groupBy('KONTRAGENT_NOMER')
                ->get()
                ->getResult();

        }

        public static function getSalesByCompanyID($companyID, $saleID)
        {
            $select = array(
                'PRODAZBI.NOMER AS PRODAZBI_NOMER',
                'PRODAZBI.FAKTURA_NOMER AS PRODAZBI_FAKTURA_NOMER',
                'PRODAZBI.OBEKT_NOMER AS PRODAZBI_OBEKT_NOMER',
                'PRODAZBI.OFERTA_NOMER AS PRODAZBI_OFERTA_NOMER',
                'PRODAZBI.KOMENTAR AS PRODAZBI_KOMENTAR',
                'PRODAZBI.DATA AS PRODAZBI_DATA',
                'PRODAZBI.KONTRAGENT_IME',
                'PRODAZBI.KONTRAGENT_SEDALISHTE_ADRES',
                'PRODAZBI.KONTRAGENT_SEDALISHTE_GRAD',
                'PRODAZBI.KONTRAGENT_DANNOMER',
                'PRODAZBI.KONTRAGENT_BULSTAT',
                'PRODAZBI.KONTRAGENT_DDSNOMER',
                'PRODAZBI.METRO_PORACHKA_NOMER'
            );

            $builder = Services::firebird()->table('PRODAZBI');
            $builder->select(implode(',', $select));
            $builder->where('PRODAZBI.KONTRAGENT_NOMER', $companyID);
            $builder->where('PRODAZBI.PRIKLUCHEN', 1);
            $builder->where('PRODAZBI.STATUS_PRODAZBA IS NULL', false, false);
            $builder->where('PRODAZBI.DATA >= ', date('Y-m-d', strtotime('-1 Year')));

            if ($saleID) {
                $builder->where('PRODAZBI.NOMER', $saleID);
            }
            $builder->orderBy('DATA', 'DESC');
            $sales = $builder->get()->getResult();

            return $sales;
        }

        public static function getPeakSales($startDate, $endDate, $storeID)
        {
            return Services::firebird()->table('PRODAZBI')
                ->select('PRODAZBI.NOMER, PRODAZBI_DE.POS_NOMER, PRODAZBI_DE.KOL, PRODAZBI.DATA')
                ->join('PRODAZBI_DE', 'PRODAZBI_DE.PRODAZBA_NOMER = PRODAZBI.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = PRODAZBI_DE.POS_NOMER')
                ->where('PRODAZBI.STATUS_PRODAZBA IS NULL', NULL, NULL)
                ->where('PRODAZBI.DATA >', $startDate)
                ->where('PRODAZBI.DATA <=', $endDate)
                ->where('PRODAZBI.OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->get()
                ->getResult();
        }

        // STATUS_OT_OBEKT
        //    null  изпратен
        //    1 неизпратен
        //    STATUS_KAM_OBEKT
        //    null пристигнал
        //   1 непристигнал
        public static function getMovements($startDate, $endDate, $storeID)
        {

            return Services::firebird()
                ->table('MESTENE')
                ->select('MESTENE_DE.POS_NOMER, SUM(MESTENE_DE.KOL) AS KOL')
                ->join('MESTENE_DE', 'MESTENE_DE.MESTENE_NOMER = MESTENE.NOMER', 'INNER')
                ->where('OT_OBEKT_NOMER', $storeID)
                ->where('MESTENE.DATA >', $startDate)
                ->where('MESTENE.DATA <=', $endDate)
                ->where('STATUS_OT_OBEKT IS NULL', NULL, NULL)
                ->where('STATUS_KAM_OBEKT IS NULL', NULL, NULL)
                ->groupBy('MESTENE_DE.POS_NOMER')
                ->get()
                ->getResult();
        }

        public static function getMovementsTurnover($startDate, $endDate, $storeID)
        {
            return Services::firebird()->table('MESTENE')
                ->select('MESTENE.NOMER, MESTENE_DE.POS_NOMER, MESTENE_DE.KOL, OT_OBEKT_NOMER')
                ->join('MESTENE_DE', 'MESTENE_DE.MESTENE_NOMER = MESTENE.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = MESTENE_DE.POS_NOMER')
                ->where('MESTENE.DATA >', $startDate)
                ->where('MESTENE.DATA <=', $endDate)
                ->where('MESTENE.KAM_OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->groupStart()
                ->where('MESTENE.STATUS_OT_OBEKT IS NULL', NULL, NULL)
                ->where('MESTENE.STATUS_KAM_OBEKT', 1)
                ->groupEnd()
                ->get()
                ->getResult();
        }

        public static function getMovementsTurnover2($startDate, $endDate, $fromStoreID, $toStoreID = array())
        {
            return Services::firebird()
                ->table('MESTENE')
                ->select('OT_OBEKT_NOMER, KAM_OBEKT_NOMER, POS_NOMER, KOL')
                ->join('MESTENE_DE', 'MESTENE_DE.MESTENE_NOMER = MESTENE.NOMER', 'INNER')
                ->groupStart()
                ->where('STATUS_OT_OBEKT IS NULL', NULL, NULL)
                ->orWhere('STATUS_OT_OBEKT', 1)
                ->groupEnd()
                ->where('OT_OBEKT_NOMER', $fromStoreID)
                ->whereIn('KAM_OBEKT_NOMER', $toStoreID)
                ->where('MESTENE.DATA >', $startDate)
                ->where('MESTENE.DATA <=', $endDate)
                ->get()
                ->getResult();
        }


        public static function expectedFromRequestТransfer($startDate, $endDate, $storeID)
        {

            return Services::firebird()->table('ZAIAVKI_MESTENE')
                ->select('ZAIAVKI_MESTENE.NOMER, ZAIAVKI_MESTENE_DE.POS_NOMER, ZAIAVKI_MESTENE_DE.KOL, OT_OBEKT_NOMER')
                ->join('ZAIAVKI_MESTENE_DE', 'ZAIAVKI_MESTENE_DE.ZAIAVKI_MESTENE_NOMER = ZAIAVKI_MESTENE.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = ZAIAVKI_MESTENE_DE.POS_NOMER')
                ->where('ZAIAVKI_MESTENE.DATA >', $startDate)
                ->where('ZAIAVKI_MESTENE.DATA <=', $endDate)
                ->where('ZAIAVKI_MESTENE.OT_OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->where('ZAIAVKI_MESTENE.STATUS', 0)
                ->get()
                ->getResult();
        }

        public static function expectedToRequestТransfer($startDate, $endDate, $storeID)
        {

            return Services::firebird()->table('ZAIAVKI_MESTENE')
                ->select('ZAIAVKI_MESTENE.NOMER, ZAIAVKI_MESTENE_DE.POS_NOMER, ZAIAVKI_MESTENE_DE.KOL, OT_OBEKT_NOMER')
                ->join('ZAIAVKI_MESTENE_DE', 'ZAIAVKI_MESTENE_DE.ZAIAVKI_MESTENE_NOMER = ZAIAVKI_MESTENE.NOMER', 'INNER')
                ->join('POZICII', 'POZICII.NOMER = ZAIAVKI_MESTENE_DE.POS_NOMER')
                ->where('ZAIAVKI_MESTENE.DATA >', $startDate)
                ->where('ZAIAVKI_MESTENE.DATA <=', $endDate)
                ->where('ZAIAVKI_MESTENE.KAM_OBEKT_NOMER', $storeID)
                ->where('POZICII.BEZNALICHEN', 0)
                ->where('POZICII.IZKLUCHEN', 0)
                ->where('POZICII.MATERIAL', 0)
                ->where('ZAIAVKI_MESTENE.STATUS', 0)
                ->get()
                ->getResult();
        }

        public static function getWarehouseOperations($startDate, $endDate, $storeID, $type)
        {

            return Services::firebird()->table('VATR_SKLAD')
                ->select('VATR_SKLAD.NOMER, OBEKT_NOMER, POS_NOMER, KOL')
                ->join('VATR_SKLAD_DE', 'VATR_SKLAD.NOMER = VATR_SKLAD_DE.VATR_SKLAD_NOMER', 'INNER')
                ->where('VATR_SKLAD.DATA >', $startDate)
                ->where('VATR_SKLAD.DATA <=', $endDate)
                ->where('OBEKT_NOMER', $storeID)
                ->whereIn('TIP_VATR_OPER_NOMER', $type)
                ->get()
                ->getResult();
        }

        public static function getDeliveries($startDate, $endDate, $storeID)
        {
            return Services::firebird()
                ->table('DOSTAVKI')
                ->select('DOSTAVKI_DE.POS_NOMER')
                ->join('DOSTAVKI_DE', 'DOSTAVKI_DE.DOSTAVKA_NOMER = DOSTAVKI.NOMER', 'INNER')
                ->where("DOSTAVKI.DATA between '{$startDate}' AND '{$endDate}'")
                ->where('DOSTAVKI.OBEKT_NOMER', $storeID)
                ->where('STATUS_DOSTAVKA', NULL, NULL)
                ->get()
                ->getResult();
        }

        public static function getNewProducts($startDate, $endDate, $storeID)
        {
            $sqls = array();
            $sqls[] = Services::firebird()
                ->table('MESTENE')
                ->select('MESTENE_DE.POS_NOMER')
                ->join('MESTENE_DE', 'MESTENE_DE.MESTENE_NOMER = MESTENE.NOMER', 'INNER')
                ->where("MESTENE.DATA between '{$startDate}' AND '{$endDate}'")
                ->where('MESTENE.KAM_OBEKT_NOMER', $storeID)
                ->getCompiledSelect();
            $sqls[] = Services::firebird()
                ->table('DOSTAVKI')
                ->select('DOSTAVKI_DE.POS_NOMER')
                ->join('DOSTAVKI_DE', 'DOSTAVKI_DE.DOSTAVKA_NOMER = DOSTAVKI.NOMER', 'INNER')
                ->where("DOSTAVKI.DATA between '{$startDate}' AND '{$endDate}'")
                ->where('DOSTAVKI.OBEKT_NOMER', $storeID)
                ->where('STATUS_DOSTAVKA', NULL, NULL)
                ->getCompiledSelect();
            return Services::firebird()->query(implode(' UNION ', $sqls))->getResult();
        }

        public static function getSalesLines($salesIDs = array())
        {
            sort($salesIDs);

            $select = array(
                'PRODAZBI_DE.NOMER',
                'PRODAZBI_DE.PRODAZBA_NOMER',
                'PRODAZBI_DE.POS_NOMER',
                'PRODAZBI_DE.KOL',
                'PRODAZBI_DE.ED_CENA',
                'PRODAZBI_DE.ED_CENA_TO',
                'PRODAZBI_DE.TO_PROC',
                'PRODAZBI_DE.ED_CENA_S_DDS',
                'PRODAZBI_DE.STOINOST_DDS',
                'PRODAZBI_DE.STOINOST_BEZ_DDS',
                'PRODAZBI_DE.STOINOST_S_DDS',
                'PRODAZBI_DE.DDS_PROC',
                'POZICII.IME',
                'MIARKI.MIARKA',
                'VALUTI.VALUTA',
            );


            $chunks = array_chunk($salesIDs, 100);

            $return = array();
            foreach ($chunks as $chunk) {

                $lines = Services::firebird()->table('PRODAZBI_DE')
                    ->select(implode(',', $select))
                    ->join('POZICII', 'PRODAZBI_DE.POS_NOMER = POZICII.NOMER')
                    ->join('MIARKI', 'MIARKI.NOMER = POZICII.MIARKA_NOMER')
                    ->join('VALUTI', 'VALUTI.NOMER = POZICII.VALUTA_NOMER')
                    ->whereIn('PRODAZBI_DE.PRODAZBA_NOMER', $chunk)
                    ->get()
                    ->getResult();


                foreach ($lines as $line) {
                    $line->ED_CENA = str_replace(',', '.', $line->ED_CENA);
                    $line->ED_CENA_TO = str_replace(',', '.', $line->ED_CENA_TO);
                    $line->ED_CENA_S_DDS = str_replace(',', '.', $line->ED_CENA_S_DDS);
                    $line->STOINOST_DDS = str_replace(',', '.', $line->STOINOST_DDS);
                    $line->STOINOST_BEZ_DDS = str_replace(',', '.', $line->STOINOST_BEZ_DDS);
                    $line->STOINOST_S_DDS = str_replace(',', '.', $line->STOINOST_S_DDS);

                    $return[$line->PRODAZBA_NOMER][] = $line;
                }
            }

            return $return;
        }

        public static function getOffersByCompanyID($companyID)
        {
            $inputOutput = array(
                'OFERTI_NOMER'     => 'OFERTI_NOMER',
                'OFERTI_KOMENTAR'  => 'OFERTI_KOMENTAR',
                'OFERTI_KOMENTAR2' => 'OFERTI_KOMENTAR2',
            );
            $select = array(
                'OFERTI.NOMER as OFERTI_NOMER',
                'OFERTI.KOMENTAR as OFERTI_KOMENTAR',
                'OFERTI.KOMENTAR2 as OFERTI_KOMENTAR2',
            );


            $builder = Services::firebird()->table('OFERTI');
            $builder->select(implode(',', $select));
            $builder->where('KONTRAGENT_NOMER', $companyID);
            $builder->where('OFERTI.DATA >= ', date('Y-m-d', strtotime('-1 Year')));
            $builder->orderBy('DATA', 'DESC');

            $offers = $builder->get()->getResult();
            $return = array();
            foreach ($offers as $offer) {
                $return[$offer->OFERTI_NOMER] = $offer;
            }
            $offers = NULL;
            unset($offers);

            return $return;

        }


        public static function getWebsiteOffersByDateRange($startDate, $endDate)
        {
            $select = array(
                'OFERTI.DATA',
                'OFERTI.KONTRAGENT_NOMER'
            );


            return Services::firebird()
                ->table('OFERTI')
                ->select(implode(',', $select))
                ->where('OFERTI.DATA >=', $startDate)
                ->where('OFERTI.DATA <=', $endDate)
                ->where('OFERTI.OBEKT_NOMER', 11)
                ->where('OFERTI.KONTRAGENT_NOMER IS NOT NULL', false, false)
                ->orderBy('OFERTI.KONTRAGENT_NOMER')
                ->get()
                ->getResult();
        }


        public static function getPriceTypes()
        {
            $inputOutput = array(
                'NOMER'        => 'NOMER',
                'TIP_CENA_KOD' => 'TIP_CENA_KOD',
                'FULLNAME'     => 'FULLNAME',
            );


            $priceTypes = Services::firebird()->table('TIPOVE_CENI')->select(implode(', ', ($inputOutput)))->get()->getResult();

            $return = self::_buildObjectsReturn($priceTypes, $inputOutput);
            unset($priceTypes);
            return $return;
        }


        public static function getWholesalePriceTypes()
        {
            return Services::firebird()->table('TIPOVE_CENI')
                ->like('TIP_CENA_KOD', '2.6.', 'LEFT')
                ->orderBy('TIP_CENA_KOD')
                ->get()
                ->getResult();
        }


        public static function getPriceLists()
        {
            $inputOutput = array(
                'NOMER' => 'NOMER',
                'IME'   => 'IME',
            );


            $priceTypes = Services::firebird()->table('CENOVA_LISTA')->select(implode(', ', ($inputOutput)))->get()->getResult();

            $return = self::_buildObjectsReturn($priceTypes, $inputOutput);
            unset($priceTypes);
            return $return;
        }

        /**
         * @return array
         */
        public static function getStoreObjects()
        {
            $inputOutput = array(
                'NOMER' => 'NOMER',
                'OBEKT' => 'OBEKT',
            );

            $priceTypes = Services::firebird()->table('OBEKTI')->select(implode(', ', ($inputOutput)))->get()->getResult();

            $return = self::_buildObjectsReturn($priceTypes, $inputOutput);
            unset($priceTypes);
            return $return;

        }


        public static function getWarehouseTypeOperations()
        {
            return Services::firebird()->table('TIPOVE_VATR_OPER')->get()->getResult();
        }

        public static function getAccountByID($id)
        {


            $inputOutput = array(
                'NOMER'            => 'NOMER',
                'KONTRAGENT_NOMER' => 'KONTRAGENT_NOMER',
            );

            $account = Services::firebird()->table('KONTRAGENTI_LICA')->where('NOMER', $id)->get()->getRow();

            $return = self::_buildSingleReturn($account, $inputOutput);
            unset($account);
            return $return;
        }

        public static function getBarcodes()
        {
            $inputOutput = array(
                'POS_NOMER' => 'POS_NOMER',
                'BARCODE'   => 'BARCODE',
            );

            $barcodes = Services::firebird()->table('BARCODE_POSNOMER')->get()->getResult();

            $return = self::_buildObjectsReturn($barcodes, $inputOutput);
            unset($barcodes);
            return $return;
        }

        public static function getBrands()
        {
            return Services::firebird()->table('GRUPIPOS2')->get()->getResult();
        }

        public static function getBrandByName($search)
        {
            $brands = self::getBrands();

            foreach ($brands as $brand) {
                if ($brand->IME == $search) {
                    return $brand;
                }
            }

            return [];
        }

        public static function getDiscountGroups()
        {

            $inputOutput = array(
                'KOD_GRUPA_POZICII'    => 'KOD_GRUPA_POZICII',
                'KOD_GRUPA_KONTRAGENT' => 'KOD_GRUPA_KONTRAGENT',
                'VID_CENA_NOMER'       => 'VID_CENA_NOMER',
                'TO_PROC'              => 'TO_PROC',
            );

            $discounts = Services::firebird()->table('Z$_GRUPI_OTSTAPKI', false)
                ->get()
                ->getResult();

            $return = self::_buildObjectsReturn($discounts, $inputOutput);
            unset($discounts);
            return $return;
        }

        // Идеята е да се сложат редирект линкове
        public static function getDisabledProducts()
        {


            $inputOutput = array(
                'NOMER' => 'NOMER',
                'IME$1' => 'IME$1',
                'IME$2' => 'IME$2',
            );


            $return = array();
            $disabledProducts = Services::firebird()->table('POZICII')
                ->select(implode(',', $inputOutput))
                ->where('IME$1 IS NOT NULL', false, false)
                ->where('IME$2 IS NOT NULL', false, false)
                ->where('BEZNALICHEN', 0)
                ->where('IZKLUCHEN', 1)
                ->get()
                ->getResult();

            foreach ($disabledProducts as $disabledProduct) {
                $return[$disabledProduct->NOMER] = $disabledProduct;
            }

            return $return;
        }

        public static function getActiveNoneVisibleToWebsiteProducts()
        {


            $inputOutput = array(
                'NOMER' => 'NOMER',
                'IME$1' => 'IME$1',
                'IME$2' => 'IME$2',
            );


            $disabledProducts = Services::firebird()->table('POZICII')
                ->select(implode(',', $inputOutput))
                ->where('IME$1 IS NOT NULL', false, false)
                ->where('IME$2 IS NOT NULL', false, false)
                ->where('USER_FIELD_5 IS NULL', false, false)
                ->where('BEZNALICHEN', 0)
                ->where('IZKLUCHEN', 0)
                ->get()
                ->getResult();

            foreach ($disabledProducts as $disabledProduct) {
                $return[$disabledProduct->NOMER] = $disabledProduct;
            }

            return $return;
        }

        public static function updatePassword($password, $email)
        {
            Services::firebird()->table('KONTRAGENTI_LICA')->set('PASSWRD', $password)->where('EMAIL', $email)->update();
        }

        public static function login($email, $password)
        {

            $inputOutput = array(
                'K_NOMER'             => 'K_NOMER',
                'KONTRAGENT'          => 'KONTRAGENT',
                'BULSTAT'             => 'BULSTAT',
                'DDSNOMER'            => 'DDSNOMER',
                'DANNOMER'            => 'DANNOMER',
                'MOL'                 => 'MOL',
                'KONTR_GRUPA_NOMER'   => 'KONTR_GRUPA_NOMER',
                'CENOVA_LISTA_NOMER'  => 'CENOVA_LISTA_NOMER',
                'TIP_CENA_NOMER'      => 'TIP_CENA_NOMER',
                'SEDALISHTE_ADRES'    => 'SEDALISHTE_ADRES',
                'SEDALISHTE_GRAD'     => 'SEDALISHTE_GRAD',
                'SEDALISHTE_DYRZAVA'  => 'SEDALISHTE_DYRZAVA',
                'SEDALISHTE_TEL'      => 'SEDALISHTE_TEL',
                'SEDALISHTE_EMAIL'    => 'SEDALISHTE_EMAIL',
                'KORESP_ADRES'        => 'KORESP_ADRES',
                'KORESP_GRAD'         => 'KORESP_GRAD',
                'TIP_CENA_NOMER2'     => 'TIP_CENA_NOMER2',
                'TIP_CENA_NOMER3'     => 'TIP_CENA_NOMER3',
                'SEDALISHTE_POST_KOD' => 'SEDALISHTE_POST_KOD',
                'KORESP_POST_KOD'     => 'KORESP_POST_KOD',
                'KL_NOMER'            => 'KL_NOMER',
                'IME'                 => 'IME',
                'DLAZNOST'            => 'DLAZNOST',
                'T