From 4f27d434b6bfb98d922b6f6c94eec838449e684a Mon Sep 17 00:00:00 2001 From: Vladimir Fomichev Date: Tue, 2 Sep 2025 11:06:54 +0300 Subject: [PATCH] =?utf8?q?=D0=A3=D0=B4=D0=B0=D0=BB=D0=B5=D0=BD=D0=B8=D0=B5?= =?utf8?q?=20=D0=BD=D0=B5=D0=B8=D1=81=D0=BF=D0=BE=D0=BB=D1=8C=D0=B7=D1=83?= =?utf8?q?=D0=B5=D0=BC=D1=8B=D1=85=20=D0=BC=D0=B5=D1=82=D0=BE=D0=B4=D0=BE?= =?utf8?q?=D0=B2?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- erp24/services/AutoPlannogrammaService.php | 313 --------------------- 1 file changed, 313 deletions(-) diff --git a/erp24/services/AutoPlannogrammaService.php b/erp24/services/AutoPlannogrammaService.php index 105e389e..ed872016 100644 --- a/erp24/services/AutoPlannogrammaService.php +++ b/erp24/services/AutoPlannogrammaService.php @@ -2530,319 +2530,6 @@ private function buildCategoryGoals(array $rawGoals, bool $subtractMatrix, int $ } - /** - * Считает взвешенную долю категорий за целевой месяц: - * берёт три предыдущих к «месяцу-цели» месяца (пропуская сразу предыдущий), - * присваивает им веса 3, 2 и 1 (самый старый месяц — 3, самый свежий — 1), - * суммирует взвешенные итоги по каждой категории и выдаёт их доли - * от общего взвешенного итога. - * - * @param string $month Целевой месяц в формате 'YYYY-MM' - * @param array|null $filters ['store_id'=>…, …] - * @param array|null $productFilter Опционально: [product_id, …] - * @param string $type 'sales' или 'writeOffs' - * @return array [ - * => [ - * ['category'=>string, 'total_sum'=>float, 'share_of_total'=>float], - * … - * ], - * … - * ] - */ - public function getMonthCategoryShareOrWriteOffWeighted( - string $month, - ?array $filters = null, - ?array $productFilter = null, - string $type = 'sales' - ): array - { - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); - if (!empty($filters['store_id'])) { - $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); - } - if (empty($storeIds)) { - return []; - } - - $baseMonth = strtotime("{$month}-01"); - - $monthOffsets = [3, 4, 5]; - $monthWeights = [3, 2, 1]; - - - $weightedSums = []; - $monthStoreTotalsWeighted = []; - - foreach ($monthOffsets as $idx => $offsetMonths) { - $w = $monthWeights[$idx]; - $start = date('Y-m-01 00:00:00', strtotime("-{$offsetMonths} months", $baseMonth)); - $end = date('Y-m-t 23:59:59', strtotime($start)); - - - $q = (new Query()) - ->select([ - 'store_id' => 'ex.entity_id', - 'category' => 'p1c.category', - 'month_sum' => new Expression('SUM(CAST(wop.summ AS NUMERIC))'), - ]) - ->from(['w' => 'write_offs']); - - if ($type === 'writeOffs') { - $q->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = w.store_id') - ->leftJoin(['wop' => 'write_offs_products'], 'wop.write_offs_id = w.id') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = wop.product_id') - ->andWhere(['>=', 'w.date', $start]) - ->andWhere(['<=', 'w.date', $end]); - - if ($productFilter !== null) { - $q->andWhere(['wop.product_id' => $productFilter]); - } - } else { - $q->leftJoin(['sp' => 'sales_products'], 'sp.check_id = s.id') - ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = sp.product_id') - ->andWhere(['>=', 's.date', $start]) - ->andWhere(['<=', 's.date', $end]); - - if ($productFilter !== null) { - $q->andWhere(['sp.product_id' => $productFilter]); - } - } - - $q->andWhere(['ex.entity_id' => $storeIds]) - // ->andWhere(['<>', 'p1c.category', '']) - ->groupBy(['ex.entity_id', 'p1c.category']); - - $rows = $q->all(); - - foreach ($rows as $r) { - $sid = $r['store_id']; - $cat = $r['category']; - $sum = (float)$r['month_sum'] * $w; - $weightedSums[$sid][$cat] = ($weightedSums[$sid][$cat] ?? 0) + $sum; - } - - } - - $result = []; - foreach ($weightedSums as $storeId => $cats) { - $grand = array_sum($cats) ?: 1; - - $unlabeledSum = $cats[''] ?? 0; - $labeledCount = count($cats) - (isset($cats['']) ? 1 : 0); - - $distribution = $labeledCount > 0 ? $unlabeledSum / $labeledCount : 0; - - foreach ($cats as $category => $weightedSum) { - if ($category === '') { - continue; - } - - $adjustedSum = $weightedSum + $distribution; - - $result[$storeId][] = [ - 'category' => $category, - 'total_sum_cat' => $weightedSum, - 'total_sum_store' => $grand, - 'share_of_total' => round($adjustedSum / $grand, 4), - ]; - } - } - return $result; - } - - public function getMonthSubcategoryShareOrWriteOffWeighted(string $dateFrom, ?array $filters = null, ?array $productFilter = null, string $type = 'sales'): array - { - try { - $dt = new \DateTime($dateFrom); - } catch (\Exception $e) { - // Неверный формат даты - return []; - } - $month = (int)$dt->format('m'); - $year = (int)$dt->format('Y'); - - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); - if (!empty($filters['store_id'])) { - $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); - } - if (empty($storeIds)) { - return []; - } - - $years = [$year - 2, $year - 1]; - - $query = (new Query()) - ->select([ - 'store_id' => 'ex.entity_id', - 'subcategory' => 'p1c.subcategory', - 'category' => 'p1c.category', - 'total_sum' => new Expression( - $type === 'writeOffs' - ? 'SUM(CAST(wop.summ AS NUMERIC))' - : 'SUM(sp.summ)' - ), - ]); - - if ($type === 'writeOffs') { - $query->from(['w' => 'write_offs']) - ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = w.store_id') - ->leftJoin(['wop' => 'write_offs_products'], 'wop.write_offs_id = w.id') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = wop.product_id') - ->andWhere(['in', new Expression('EXTRACT(YEAR FROM w.date)'), $years]) - ->andWhere(['=', new Expression('EXTRACT(MONTH FROM w.date)'), $month]); - if ($productFilter !== null) { - $query->andWhere(['wop.product_id' => $productFilter]); - } - } else { - $query->from(['s' => 'sales']) - ->leftJoin(['sp' => 'sales_products'], 'sp.check_id = s.id') - ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = sp.product_id') - ->andWhere(['in', new Expression('EXTRACT(YEAR FROM s.date)'), $years]) - ->andWhere(['=', new Expression('EXTRACT(MONTH FROM s.date)'), $month]); - if ($productFilter !== null) { - $query->andWhere(['sp.product_id' => $productFilter]); - } - } - - $query->andWhere(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.subcategory', '']) - ->groupBy(['ex.entity_id', 'p1c.subcategory', 'p1c.category']); - - $rows = $query->all(); - if (empty($rows)) { - return []; - } - - $sumByStoreCategory = []; - foreach ($rows as $r) { - $sid = $r['store_id']; - $cat = $r['category']; - $sumByStoreCategory[$sid][$cat] = ($sumByStoreCategory[$sid][$cat] ?? 0) + $r['total_sum']; - } - - - $result = []; - foreach ($rows as $r) { - $sid = $r['store_id']; - $cat = $r['category']; - $total = $sumByStoreCategory[$sid][$cat] ?: 1; - $result[] = [ - 'store_id' => $sid, - 'category' => $cat, - 'subcategory' => $r['subcategory'], - 'total_sum' => $r['total_sum'], - 'percent_of_month' => round($r['total_sum'] / $total, 4), - ]; - } - - return $result; - } - - public function getMonthSpeciesShareOrWriteOffWeighted( - string $dateFrom, - string $dateTo, - ?array $filters = null, - ?array $productFilter = null, - string $type = 'sales' - ): array - { - try { - $dt = new \DateTime($dateFrom); - } catch (\Exception $e) { - return []; - } - $month = (int)$dt->format('m'); - $year = (int)$dt->format('Y'); - - $stores = $this->getVisibleStores(); - $storeIds = array_map(fn($s) => $s->id, $stores); - if (!empty($filters['store_id'])) { - $storeIds = array_intersect($storeIds, [(int)$filters['store_id']]); - } - if (empty($storeIds)) { - return []; - } - - $years = [$year - 2, $year - 1]; - - $query = (new Query())->select([ - 'store_id' => 'ex.entity_id', - 'category' => 'p1c.category', - 'subcategory' => 'p1c.subcategory', - 'species' => 'p1c.species', - 'total_sum' => new Expression( - $type === 'writeOffs' - ? 'SUM(CAST(wop.summ AS NUMERIC))' - : 'SUM(sp.summ)' - ), - ]); - - if ($type === 'writeOffs') { - $query->from(['w' => 'write_offs']) - ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = w.store_id') - ->leftJoin(['wop' => 'write_offs_products'], 'wop.write_offs_id = w.id') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = wop.product_id') - ->andWhere(['IN', new Expression('EXTRACT(YEAR FROM w.date)'), $years]) - ->andWhere(['=', new Expression('EXTRACT(MONTH FROM w.date)'), $month]); - if ($productFilter !== null) { - $query->andWhere(['wop.product_id' => $productFilter]); - } - } else { - $query->from(['s' => 'sales']) - ->leftJoin(['sp' => 'sales_products'], 'sp.check_id = s.id') - ->leftJoin(['ex' => 'export_import_table'], 'ex.export_val = s.store_id_1c') - ->leftJoin(['p1c' => 'products_1c_nomenclature'], 'p1c.id = sp.product_id') - ->andWhere(['IN', new Expression('EXTRACT(YEAR FROM s.date)'), $years]) - ->andWhere(['=', new Expression('EXTRACT(MONTH FROM s.date)'), $month]); - if ($productFilter !== null) { - $query->andWhere(['sp.product_id' => $productFilter]); - } - } - - $query->andWhere(['ex.entity_id' => $storeIds]) - ->andWhere(['<>', 'p1c.species', '']) - ->groupBy([ - 'ex.entity_id', - 'p1c.category', - 'p1c.subcategory', - 'p1c.species', - ]); - - $rows = $query->all(); - if (empty($rows)) { - return []; - } - - $sumByStoreSubcategory = []; - foreach ($rows as $r) { - $sid = $r['store_id']; - $sub = $r['subcategory']; - $sumByStoreSubcategory[$sid][$sub] = - ($sumByStoreSubcategory[$sid][$sub] ?? 0) + $r['total_sum']; - } - - $result = []; - foreach ($rows as $r) { - $sid = $r['store_id']; - $sub = $r['subcategory']; - $total = $sumByStoreSubcategory[$sid][$sub] ?: 1; - $result[] = [ - 'store_id' => $sid, - 'category' => $r['category'], - 'subcategory' => $sub, - 'species' => $r['species'], - 'total_sum' => (float)$r['total_sum'], - 'percent_of_month' => round($r['total_sum'] / $total, 4), - ]; - } - - return $result; - } - // Недельные расчеты -- 2.39.5