Gecko [ unitednationsplay.com ]

Name	Size	Permission	Date	Action
.gitignore	0 B	-rw-rw-r--	2022-08-25 15:00
CatalogRepository.php	20.76 KB	-rw-rw-r--	2023-08-29 09:19

Rename

<?php

namespace App\Repository;

use App\Entity\Catalog;
use Doctrine\ORM\EntityRepository;

/**
 * LessonRepository.
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class CatalogRepository extends EntityRepository
{

public function getCatalog($user, $date_from = '', $date_to = '', $brand = '', $warehouse = '')
    {
        $arResult = [];
        $arTmp = [
            'id' => '',
            'our_rate' => '',
            'img' => '',
            'object' => '',
            'brand' => '',
            'nm_id' => '',
            'media_files' => [],
            'vendor_code' => '',
            'wb_rating' => '',
            'update_at' => '',
            'supplierArticle' => '',
            'amount' => '',
            'amount_total' => '',
            'amountStocks' => [],
            'sales' => [],
            'delivery_amount' => 0,
            'return_amount' => 0,
            'price' => 0,
            'price_array' => [],
            'cost_price' => 0,
            'commision' => 0,
            'logistics' => 0,
            'money_in_good' => '',
            'free_fee_one' => '',
            'free_fee_all' => '',
            'medium_buy' => '',
            'selfcost' => '',
            'number_sale' => '',
            'number_sale_price' => '',
            'ar_number_sale' => [],
            'fee' => '',
            'free_fee_from_one' => '',
            'free_fee_from_all' => '',
            'sum_free_fee_all_part' => '',
            'part_amount' => '',
            'sum_two' => '',
        ];
        $order_by = 'id';
        $order = 'DESC';
        $where = '';

if ($brand) $where .= " AND brand = '".$brand."'";

//left join stocks st on c.nm_id=st.nm_id
        $rawQuery = "SELECT * FROM `catalog` c WHERE account_id='".$user."' ".$where;
        if ($date_from) $where .= " AND update_at >= '".$date_from."'";
        if ($date_to) $where .= " AND update_at <= '".$date_to."'";

$conn = $this->getEntityManager()->getConnection();
        $stmt = $conn->prepare($rawQuery);
        $result = $stmt->executeQuery();
        foreach ($result->fetchAllAssociative() as $item) {

$arTmp2 = $arTmp;
            $arTmp2['id'] = $item['id'];
            $arTmp2['nm_id'] = $item['nm_id'];
            $arTmp2['object'] = $item['object'];
            $arTmp2['brand'] = $item['brand'];
            $arTmp2['vendor_code'] = $item['vendor_code'];
            $arTmp2['update_at'] = $item['update_at'];
            $arTmp2['cost_price'] = $item['cost_price'];
            $arTmp2['media_files'] = $item['media_files'];
            $arTmp2['wb_rating'] = $item['feedback_rating'];
            //$arTmp2['commision'] = $item['commision'];
            //$arTmp2['logistics'] = $item['logistics'];

$arMedia = json_decode($item['media_files']);
            if (!empty($arMedia)) $arTmp2['img'] = $arMedia[0];

//dump($item['nm_id']);
            $amount_total = 0;
            $amountStocks = $amountStocksLog = [];
            if ($item['nm_id']) {
                //dump($item['nm_id']);
                $rawQuery2 = "SELECT * FROM `stocks` s WHERE account_id='".$user."' AND s.nm_id='" . $item['nm_id'] . "' AND is_realization=1 ORDER BY last_change_date ASC";
                $conn2 = $this->getEntityManager()->getConnection();
                $stmt2 = $conn2->prepare($rawQuery2);
                $result2 = $stmt2->executeQuery();
                foreach ($result2->fetchAllAssociative() as $item2) {

$add = 1;
                    if ($warehouse AND $warehouse != $item2['warehouse_name']) $add = '';

if ($add==1) {
                        $arTmp2['supplierArticle'] = $item2['supplier_article'];
                        $amountStocks[] = [
                            'id' => $item2['id'],
                            'tech_size' => $item2['tech_size'],
                            'barcode' => $item2['barcode'],
                            'quantity' => $item2['quantity'],
                            'is_supply' => $item2['is_supply'],
                            'is_realization' => $item2['is_realization'],
                            'quantity_full' => $item2['quantity_full'],
                            'warehouse_name' => $item2['warehouse_name'],
                            'subject' => $item2['subject'],
                            'brand' => $item2['brand'],
                            'category' => $item2['category'],
                            'days_on_site' => $item2['days_on_site'],
                            'price' => $item2['price'],
                            'discount' => $item2['discount'],
                            'supplierArticle' => $item2['supplier_article'],
                            'commision' => $item2['commision'],
                            'logistics' => $item2['logistics'],
                        ];

//$arTmp2['price'] = $item2['price'] * (1 - $item2['discount'] / 100);
                        //$amount_total += $item2['quantity_full'];
                        $amount_total += $item2['quantity'];
                    }
                    //$warehouse = $item2['warehouse_name'];
                    //dump($item2);
                }
                $rawQuery2 = "SELECT * FROM `stocks` s WHERE account_id='".$user."' AND s.nm_id='" . $item['nm_id'] . "' AND is_realization=1 ORDER BY last_change_date DESC LIMIT 0,1";
                $conn2 = $this->getEntityManager()->getConnection();
                $stmt2 = $conn2->prepare($rawQuery2);
                $result2 = $stmt2->executeQuery();
                foreach ($result2->fetchAllAssociative() as $item2) {
                    $arTmp2['price'] = $item2['price'] * (1 - $item2['discount'] / 100);
                }

//dump($item['nm_id']);
                //dump($amountStocks);

//AND is_realization is null
                $where = '';
                if (!$date_from) $where .= " AND last_change_date >= '".date("Y-m-d 00:00:00", (time()-86400*30))."'"; else $where .= " AND last_change_date >= '".str_replace(["T", "Z"],[" ", ""],$date_from)."'";
                if (!$date_to) $where .= " AND last_change_date <= '".date("Y-m-d 00:00:00", time())."'"; else $where .= " AND last_change_date <= '".str_replace(["T", "Z"],[" ", ""],$date_to)."'";
                $rawQuery2 = "SELECT * FROM `stocks_log` WHERE account_id='".$user."' AND nm_id='" . $item['nm_id'] . "' ".$where." ORDER BY last_change_date ASC";
                $conn2 = $this->getEntityManager()->getConnection();
                $stmt2 = $conn2->prepare($rawQuery2);
                $result2 = $stmt2->executeQuery();
                foreach ($result2->fetchAllAssociative() as $item2) {
                        $arTmp2['supplierArticle'] = $item2['supplier_article'];
                        if (intval($item2['quantity'])>0) {
                            $amountStocksLog[date("d.m.Y", strtotime($item2['last_change_date']))] = [
                                'id' => $item2['id'],
                                'tech_size' => $item2['tech_size'],
                                'barcode' => $item2['barcode'],
                                'quantity' => $item2['quantity'],
                                'is_supply' => $item2['is_supply'],
                                'is_realization' => $item2['is_realization'],
                                'quantity_full' => $item2['quantity_full'],
                                'warehouse_name' => $item2['warehouse_name'],
                                'subject' => $item2['subject'],
                                'brand' => $item2['brand'],
                                'category' => $item2['category'],
                                'days_on_site' => $item2['days_on_site'],
                                'price' => $item2['price'],
                                'discount' => $item2['discount'],
                                'supplierArticle' => $item2['supplier_article'],
                                'commision' => $item2['commision'],
                                'logistics' => $item2['logistics'],
                            ];
                        }

$arTmp2['price_array'][] = $item2['price'] * (1 - $item2['discount'] / 100);
                        //$amount_total += $item2['quantity_full'];

//$warehouse = $item2['warehouse_name'];
                    //dump($item2);
                }

$reports = [];
                $arNumberSale = [];
                $where = '';
                if (!$date_from) $where .= " AND date_from >= '".date("Y-m-d 00:00:00", (time()-86400*30))."'"; else $where .= " AND date_from >= '".str_replace(["T", "Z"],[" ", ""],$date_from)."'";
                if (!$date_to) $where .= " AND date_from <= '".date("Y-m-d 00:00:00", time())."'"; else $where .= " AND date_to <= '".str_replace(["T", "Z"],[" ", ""],$date_to)."'";
                $rawQuery2 = "SELECT delivery_amount, return_amount, create_dt, quantity, retail_price, retail_amount, sale_percent, commission_percent, retail_price_withdisc_rub, product_discount_for_report, ppvz_kvw_prc_base, ppvz_kvw_prc, ppvz_sales_commission, ppvz_reward, acquiring_fee, ppvz_vw, ppvz_vw_nds FROM `reports` r WHERE account_id='".$user."' AND r.nm_id='" . $item['nm_id'] . "' ".$where;
                $conn2 = $this->getEntityManager()->getConnection();
                $stmt2 = $conn2->prepare($rawQuery2);
                $result2 = $stmt2->executeQuery();
                foreach ($result2->fetchAllAssociative() as $item2) {
                    $arTmp2['delivery_amount'] += $item2['delivery_amount'];
                    $arTmp2['return_amount'] += $item2['return_amount'];
                    $reports[] = [
                        'create_dt' => $item2['create_dt'],
                        'quantity' => $item2['quantity'],
                        'delivery_amount' => $item2['delivery_amount'],
                        'return_amount' => $item2['return_amount'],
                        'retail_price' => $item2['retail_price'],
                        'retail_amount' => $item2['retail_amount'],
                        'sale_percent' => $item2['sale_percent'],
                        'commission_percent' => $item2['commission_percent'],
                        'retail_price_withdisc_rub' => $item2['retail_price_withdisc_rub'],
                        'product_discount_for_report' => $item2['product_discount_for_report'],
                        'ppvz_kvw_prc_base' => $item2['ppvz_kvw_prc_base'],
                        'ppvz_kvw_prc' => $item2['ppvz_kvw_prc'],
                        'ppvz_sales_commission' => $item2['ppvz_sales_commission'],
                        'ppvz_reward' => $item2['ppvz_reward'],
                        'acquiring_fee' => $item2['acquiring_fee'],
                        'ppvz_vw' => $item2['ppvz_vw'],
                        'ppvz_vw_nds' => $item2['ppvz_vw_nds'],
                    ];
                    if (empty($arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))])) $arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))] = 0;
                    $arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))] += intval($item2['quantity']);
                }
                $arTmp2["reports"] = $reports;
                $arTmp2["ar_number_sale"] = $arNumberSale;

$sales = [];
                $where = '';
                if (!$date_from) $where .= " AND date >= '".date("Y-m-d 00:00:00", (time()-86400*30))."'"; else $where .= " AND date >= '".str_replace(["T", "Z"],[" ", ""],$date_from)."'";
                if (!$date_to) $where .= " AND date <= '".date("Y-m-d 00:00:00", time())."'"; else $where .= " AND date <= '".str_replace(["T", "Z"],[" ", ""],$date_to)."'";
                $rawQuery2 = "SELECT total_price, discount_percent, is_supply, is_realization, promo_code_discount, warehouse_name, income_ID, sale_ID, for_pay, finished_price, price_with_disc, is_storno, g_number FROM `sales` s WHERE is_realization=1 AND account_id='".$user."' AND s.nm_id='" . $item['nm_id'] . "' ".$where;
                $conn2 = $this->getEntityManager()->getConnection();
                $stmt2 = $conn2->prepare($rawQuery2);
                $result2 = $stmt2->executeQuery();
                foreach ($result2->fetchAllAssociative() as $item2) {
                    $sales[] = [
                        'total_price' => $item2['total_price'],
                        'discount_percent' => $item2['discount_percent'],
                        'is_supply' => $item2['is_supply'],
                        'is_realization' => $item2['is_realization'],
                        'promo_code_discount' => $item2['promo_code_discount'],
                        'warehouse_name' => $item2['warehouse_name'],
                        'income_ID' => $item2['income_ID'],
                        'sale_ID' => $item2['sale_ID'],
                        'for_pay' => $item2['for_pay'],
                        'finished_price' => $item2['finished_price'],
                        'price_with_disc' => $item2['price_with_disc'],
                        'is_storno' => $item2['is_storno'],
                        'g_number' => $item2['g_number'],
                    ];
                }
                $arTmp2["sales"] = $sales;

}
/*
            if (!empty($amountStocks)) {
                foreach ($amountStocks as $amountStock) {
                    $amount_total += $amountStock['quantity_full'];
                }
            }
*/
            $arTmp2['amountStocks'] = $amountStocks;
            $arTmp2['amountStocksLog'] = $amountStocksLog;
            $arTmp2['amount_total'] = $amount_total;

/*
            $rawQuery2 = "SELECT * FROM `reports` r WHERE r.nm_id='".$item['nm_id']."'";
            $conn2 = $this->getEntityManager()->getConnection();
            $stmt2 = $conn2->prepare($rawQuery2);
            $result2 = $stmt2->executeQuery();
            foreach ($result2->fetchAllAssociative() as $item2) {
                dump($item2);

}*/
            //if (isset($item['count'])) $count = $item['count'];

if ($warehouse AND empty($amountStocks)) unset($arTmp2);

if (!empty($arTmp2)) $arResult[] = $arTmp2;
        }

return $arResult;

}

public function getChartStats($user, $nm_id, $date_from, $date_to) {
        $arResult = [
            'id' => '',
            'our_rate' => '',
            'img' => '',
            'object' => '',
            'brand' => '',
            'nm_id' => '',
            'vendor_code' => '',
            'update_at' => '',
            'supplierArticle' => '',
            'amount' => '',
            'amount_total' => '',
            'amountStocks' => [],
            'sales' => [],
            'delivery_amount' => 0,
            'return_amount' => 0,
            'price' => 0,
            'price_array' => [],
            'cost_price' => 0,
            'commision' => 0,
            'logistics' => 0,
            'money_in_good' => '',
            'free_fee_one' => '',
            'free_fee_all' => '',
            'medium_buy' => '',
            'selfcost' => '',
            'number_sale' => '',
            'number_sale_price' => '',
            'ar_number_sale' => [],
            'fee' => '',
            'free_fee_from_one' => '',
            'free_fee_from_all' => '',
            'sum_free_fee_all_part' => '',
            'part_amount' => '',
            'sum_two' => '',
        ];

$amountStocksLog = [];
        $where = '';
        if (!$date_from) $where .= " AND last_change_date >= '".date("Y-m-d 00:00:00", (time()-86400*30))."'"; else $where .= " AND last_change_date >= '".$date_from."'";
        if (!$date_to) $where .= " AND last_change_date <= '".date("Y-m-d 00:00:00", time())."'"; else $where .= " AND last_change_date <= '".$date_to."'";
        $rawQuery2 = "SELECT * FROM `stocks_log` WHERE account_id='".$user."' AND nm_id='" . $nm_id . "' ".$where." ORDER BY last_change_date ASC";
        $conn2 = $this->getEntityManager()->getConnection();
        $stmt2 = $conn2->prepare($rawQuery2);
        $result2 = $stmt2->executeQuery();
        foreach ($result2->fetchAllAssociative() as $item2) {
            $arResult['supplierArticle'] = $item2['supplier_article'];
            $amountStocksLog[date("d.m.Y", strtotime($item2['last_change_date']))] = [
                'id' => $item2['id'],
                'tech_size' => $item2['tech_size'],
                'barcode' => $item2['barcode'],
                'quantity' => $item2['quantity'],
                'is_supply' => $item2['is_supply'],
                'is_realization' => $item2['is_realization'],
                'quantity_full' => $item2['quantity_full'],
                'warehouse_name' => $item2['warehouse_name'],
                'subject' => $item2['subject'],
                'brand' => $item2['brand'],
                'category' => $item2['category'],
                'days_on_site' => $item2['days_on_site'],
                'price' => $item2['price'],
                'discount' => $item2['discount'],
                'supplierArticle' => $item2['supplier_article'],
                'commision' => $item2['commision'],
                'logistics' => $item2['logistics'],
            ];

$arResult['price_array'][] = $item2['price'] * (1 - $item2['discount'] / 100);
        }
        $arResult['amountStocksLog'] = $amountStocksLog;

$reports = [];
        $arNumberSale = [];
        $where = '';
        if (!$date_from) $where .= " AND date_from >= '".date("Y-m-d 00:00:00", (time()-86400*30))."'"; else $where .= " AND date_to >= '".$date_from."'";
        if (!$date_to) $where .= " AND date_from <= '".date("Y-m-d 00:00:00", time())."'"; else $where .= " AND date_to <= '".$date_to."'";
        $rawQuery2 = "SELECT delivery_amount, return_amount, create_dt, quantity, retail_price, retail_amount, sale_percent, commission_percent, retail_price_withdisc_rub, product_discount_for_report, ppvz_kvw_prc_base, ppvz_kvw_prc, ppvz_sales_commission, ppvz_reward, acquiring_fee, ppvz_vw, ppvz_vw_nds FROM `reports` r WHERE account_id='".$user."' AND r.nm_id='" . $nm_id . "' ".$where;
        $conn2 = $this->getEntityManager()->getConnection();
        $stmt2 = $conn2->prepare($rawQuery2);
        $result2 = $stmt2->executeQuery();
        foreach ($result2->fetchAllAssociative() as $item2) {
            $arResult['delivery_amount'] += $item2['delivery_amount'];
            $arResult['return_amount'] += $item2['return_amount'];
            $reports[] = [
                'create_dt' => $item2['create_dt'],
                'quantity' => $item2['quantity'],
                'delivery_amount' => $item2['delivery_amount'],
                'return_amount' => $item2['return_amount'],
                'retail_price' => $item2['retail_price'],
                'retail_amount' => $item2['retail_amount'],
                'sale_percent' => $item2['sale_percent'],
                'commission_percent' => $item2['commission_percent'],
                'retail_price_withdisc_rub' => $item2['retail_price_withdisc_rub'],
                'product_discount_for_report' => $item2['product_discount_for_report'],
                'ppvz_kvw_prc_base' => $item2['ppvz_kvw_prc_base'],
                'ppvz_kvw_prc' => $item2['ppvz_kvw_prc'],
                'ppvz_sales_commission' => $item2['ppvz_sales_commission'],
                'ppvz_reward' => $item2['ppvz_reward'],
                'acquiring_fee' => $item2['acquiring_fee'],
                'ppvz_vw' => $item2['ppvz_vw'],
                'ppvz_vw_nds' => $item2['ppvz_vw_nds'],
            ];
            if (empty($arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))])) $arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))] = 0;
            $arNumberSale[date("d.m.Y", strtotime($item2['create_dt']))] += intval($item2['quantity']);
        }
        $arResult["reports"] = $reports;
        $arResult["ar_number_sale"] = $arNumberSale;

return $arResult;
    }

public function getBrands($user)
    {
        $arResult = [];

$rawQuery = "SELECT brand FROM `catalog` c WHERE account_id='".$user."' GROUP BY brand";
        $conn = $this->getEntityManager()->getConnection();
        $stmt = $conn->prepare($rawQuery);
        $result = $stmt->executeQuery();
        foreach ($result->fetchAllAssociative() as $item) {
            if ($item['brand']) $arResult[$item['brand']] = $item['brand'];
        }

return $arResult;
    }

public function getWarehouses($user)
    {
        $arResult = [];

$rawQuery = "SELECT warehouse_name FROM `stocks` s WHERE account_id='".$user."' GROUP BY warehouse_name";
        $conn = $this->getEntityManager()->getConnection();
        $stmt = $conn->prepare($rawQuery);
        $result = $stmt->executeQuery();
        foreach ($result->fetchAllAssociative() as $item) {
            if ($item['warehouse_name']) $arResult[$item['warehouse_name']] = $item['warehouse_name'];
        }

return $arResult;
    }

}