Changeset 20129 for branches/version-2_5-dev/data/class/db/dbfactory

Timestamp:

2011/02/10 15:30:51 (13 years ago)

Author:

kimoto

Message:

売上集計リファクタリング　#962

Location:

branches/version-2_5-dev/data/class/db/dbfactory

Files:

• SC_DB_DBFactory_MYSQL.php (modified) (5 diffs)
• SC_DB_DBFactory_PGSQL.php (modified) (1 diff)

branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_MYSQL.php

@@ -148 +148 @@
     }
 
+
+    /**
+     * 売上集計の期間別集計のSQLを返す
+     *
+     * @param mixed $type
+     * @return string 検索条件のSQL
+     */
+    function getOrderTotalDaysWhereSql($type) {
+        switch($type){
+        case 'month':
+            $format = '%m';
+            break;
+        case 'year':
+            $format = '%Y';
+            break;
+        case 'wday':
+            $format = '%a';
+            break;
+        case 'hour':
+            $format = '%H';
+            break;
+        default:
+            $format = '%Y-%m-%d';
+            break;
+        }
+
+        return " date_format(create_date, '".$format."') AS str_date,
+            COUNT(order_id) AS total_order,
+            SUM(CASE WHEN order_sex = 1 THEN 1 ELSE 0 END) AS men,
+            SUM(CASE WHEN order_sex = 2 THEN 1 ELSE 0 END) AS women,
+            SUM(CASE WHEN customer_id <> 0 AND order_sex = 1 THEN 1 ELSE 0 END) AS men_member,
+            SUM(CASE WHEN customer_id <> 0 AND order_sex = 2 THEN 1 ELSE 0 END) AS women_member,
+            SUM(CASE WHEN customer_id = 0 AND order_sex = 1 THEN 1 ELSE 0 END) AS men_nonmember,
+            SUM(CASE WHEN customer_id = 0 AND order_sex = 2 THEN 1 ELSE 0 END) AS women_nonmember,
+            SUM(total) AS total,
+            AVG(total) AS total_average";
+    }
+
+
     /**
      * 文字列連結を行う.
@@ -278 +317 @@
         return $changesql;
     }
-    
+
     /**
      * ARRAY_TO_STRING(ARRAY(A),B) を GROUP_CONCAT() に変換する.
@@ -289 +328 @@
         if(strpos(strtoupper($sql), 'ARRAY_TO_STRING') !== FALSE) {
             preg_match_all('/ARRAY_TO_STRING.*?\(.*?ARRAY\(.*?SELECT (.+?) FROM (.+?) WHERE (.+?)\).*?\,.*?\'(.+?)\'.*?\)/is', $sql, $match, PREG_SET_ORDER);
-            
+
             foreach($match as $item) {
                 $replace = 'GROUP_CONCAT(' . $item[1] . ' SEPARATOR \'' . $item[4] . '\') FROM ' . $item[2] . ' WHERE ' . $item[3];
@@ -297 +336 @@
         return $sql;
     }
-    
+
     /**
      * WHERE 句置換用の配列を返す.
@@ -434 +473 @@
 
     }
-    
+
     /**
      * インデックス作成の追加定義を取得する

branches/version-2_5-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php

@@ -129 +129 @@
         return "(SELECT CASE WHEN (SELECT d1.downloadable_days_unlimited FROM dtb_baseinfo d1) = 1 AND o.payment_date IS NOT NULL THEN 1 WHEN DATE(NOW()) <= DATE(o.payment_date + '". $downloadable_days ." days') THEN 1 ELSE 0 END)";
     }
+
+    /**
+     * 売上集計の期間別集計のSQLを返す
+     *
+     * @param mixed $type
+     * @return string 検索条件のSQL
+     */
+    function getOrderTotalDaysWhereSql($type) {
+        switch($type){
+        case 'month':
+            $format = 'MM';
+            break;
+        case 'year':
+            $format = 'YYYY';
+            break;
+        case 'wday':
+            $format = 'Dy';
+            break;
+        case 'hour':
+            $format = 'HH24';
+            break;
+        default:
+            $format = 'YYYY-MM-DD';
+            break;
+        }
+
+        return "to_char(create_date, '".$format."') AS str_date,
+            COUNT(order_id) AS total_order,
+            SUM(CASE WHEN order_sex = 1 THEN 1 ELSE 0 END) AS men,
+            SUM(CASE WHEN order_sex = 2 THEN 1 ELSE 0 END) AS women,
+            SUM(CASE WHEN customer_id <> 0 AND order_sex = 1 THEN 1 ELSE 0 END) AS men_member,
+            SUM(CASE WHEN customer_id <> 0 AND order_sex = 2 THEN 1 ELSE 0 END) AS women_member,
+            SUM(CASE WHEN customer_id = 0 AND order_sex = 1 THEN 1 ELSE 0 END) AS men_nonmember,
+            SUM(CASE WHEN customer_id = 0 AND order_sex = 2 THEN 1 ELSE 0 END) AS women_nonmember,
+            SUM(total) AS total,
+            AVG(total) AS total_average";
+    }
+
 
     /**