From 2b98d21bf277e7ba916a136eb140558c90700b43 Mon Sep 17 00:00:00 2001 From: Alexander Smirnov Date: Mon, 13 May 2024 16:44:59 +0300 Subject: [PATCH] dashboard service fix --- erp24/services/DashboardService.php | 60 ++++++++++++++--------------- 1 file changed, 30 insertions(+), 30 deletions(-) diff --git a/erp24/services/DashboardService.php b/erp24/services/DashboardService.php index 628f4cb..03083c5 100755 --- a/erp24/services/DashboardService.php +++ b/erp24/services/DashboardService.php @@ -245,7 +245,7 @@ class DashboardService SELECT count(*) as cnt, created_store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'DD.MM.YYYY') as date_t FROM users WHERE @@ -312,7 +312,7 @@ class DashboardService $command = $connection->createCommand(" SELECT - DATE_FORMAT(sales.date,'%d.%m.%Y') as dt, + TO_CHAR(sales.date,'DD.MM.YYYY') as dt, sales.store_id, count(*) as cnt FROM @@ -430,8 +430,8 @@ class DashboardService SELECT sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_p, - DATE_FORMAT(date,'%m.%Y') as monthdate + TO_CHAR(date,'DD.MM.YYYY') as date_p, + TO_CHAR(date,'MM.YYYY') as monthdate FROM sales WHERE @@ -523,8 +523,8 @@ class DashboardService SELECT sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_p, - DATE_FORMAT(date,'%m.%Y') as date_m + TO_CHAR(date,'DD.MM.YYYY') as date_p, + TO_CHAR(date,'MM.YYYY') as date_m FROM write_offs WHERE @@ -627,11 +627,11 @@ class DashboardService $command = $connection->createCommand(" SELECT - sum(if(tip='minus' AND tip_sale='sale',bonus,0)) as summ, - sum(if(tip='plus' AND tip_sale='sale',bonus,0)) as summ_plus, + sum(CASE WHEN (tip='minus' AND tip_sale='sale') THEN bonus ELSE 0 END) as summ, + sum(CASE WHEN (tip='plus' AND tip_sale='sale') THEN bonus ELSE 0 END) as summ_plus, store_id, tip, - DATE_FORMAT(date,'%d.%m.%Y') as date_p + TO_CHAR(date,'YYYY-MM-DD') as date_p FROM users_bonus WHERE ( @@ -701,11 +701,11 @@ class DashboardService $command = $connection->createCommand(" SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -747,11 +747,11 @@ class DashboardService SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -818,11 +818,11 @@ class DashboardService $command = $connection->createCommand(" SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -863,11 +863,11 @@ class DashboardService $command = $connection->createCommand(" SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -938,11 +938,11 @@ class DashboardService $command = $connection->createCommand(" SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -984,11 +984,11 @@ class DashboardService $command = $connection->createCommand(" SELECT count(*) as cnt, - sum(if(matrix>=15,summ,0)) as matrix, - sum(if(phone!='NULL',1,0)) as bonus_clients, + sum(CASE WHEN matrix>=15 THEN summ ELSE 0 END) as matrix, + sum(CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END) as bonus_clients, sum(summ) as summ, store_id, - DATE_FORMAT(date,'%d.%m.%Y') as date_t + TO_CHAR(date,'YYYY-MM-DD') as date_t FROM sales WHERE @@ -1215,7 +1215,7 @@ class DashboardService $z = " SELECT - DATE_FORMAT(sales.date,'%d.%m.%Y') as date_p, + TO_CHAR(sales.date,'YYYY-MM-DD') as date_p, sales.store_id, sum(sales_items.summa) as summa FROM @@ -1272,7 +1272,7 @@ class DashboardService $command = $connection->createCommand(" SELECT - DATE_FORMAT(sales.date,'%d.%m.%Y') as date_p, + TO_CHAR(sales.date,'YYYY-MM-DD') as date_p, sales.store_id, sum(sales_items.summa) as summa FROM @@ -1357,7 +1357,7 @@ class DashboardService sum(counter) as counter, store_id, date, - DATE_FORMAT(date,'%d.%m.%Y') as dt + TO_CHAR(date,'YYYY-MM-DD') as dt FROM store_visitors WHERE -- 2.39.5