$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 '' . print_r($invoice, true) . '
';
$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
