Changeset 11982 for branches/comu/html/admin/home.php

Timestamp:

2007/03/28 13:01:38 (19 years ago)

Author:

uehara

Message:

 

File:

• branches/comu/html/admin/home.php (modified) (5 diffs)

branches/comu/html/admin/home.php

@@ -85 +85 @@
         if (DB_TYPE == "pgsql") {
             $sql = "SELECT ".$method."(total) FROM dtb_order
-                     WHERE del_flg = 0 AND to_char(create_date,'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD')";
+                     WHERE del_flg = 0 AND to_char(create_date,'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD') AND status <> " . ORDER_CANCEL;
         }else if (DB_TYPE == "mysql") {
             $sql = "SELECT ".$method."(total) FROM dtb_order
-                     WHERE del_flg = 0 AND cast(substring(create_date,1, 10) as date) = DATE_ADD(current_date, interval -1 day)";
+                     WHERE del_flg = 0 AND cast(substring(create_date,1, 10) as date) = DATE_ADD(current_date, interval -1 day) AND status <> " . ORDER_CANCEL;
         }
         $return = $conn->getOne($sql);
@@ -104 +104 @@
         $sql = "SELECT ".$method."(total) FROM dtb_order
                  WHERE del_flg = 0 AND to_char(create_date,'YYYY/MM') = ? 
-                 AND to_char(create_date,'YYYY/MM/DD') <> to_char(now(),'YYYY/MM/DD')";
+                 AND to_char(create_date,'YYYY/MM/DD') <> to_char(now(),'YYYY/MM/DD') AND status <> " . ORDER_CANCEL;
     }else if (DB_TYPE == "mysql") {
         $sql = "SELECT ".$method."(total) FROM dtb_order
-                 WHERE del_flg = 0 AND cast(substring(create_date,1,7) as date) = ? 
-                 AND cast(substring(create_date,1, 10) as date) <> cast(substring(now(),1, 10) as date)";
+                 WHERE del_flg = 0 AND date_format(create_date, '%Y/%m') = ? 
+                 AND date_format(create_date, '%Y/%m/%d') <> date_format(now(), '%Y/%m/%d') AND status <> " . ORDER_CANCEL;
     }
         $return = $conn->getOne($sql, array($month));
@@ -127 +127 @@
     // postgresql ¤È mysql ¤È¤ÇSQL¤ò¤ï¤±¤ë
     if (DB_TYPE == "pgsql") {
-        $sql = "SELECT COUNT(*) FROM dtb_review 
-                 WHERE del_flg=0 AND to_char(create_date, 'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD')
-                 AND to_char(create_date,'YYYY/MM/DD') != to_char(now(),'YYYY/MM/DD')";
+        $sql = "SELECT COUNT(*) FROM dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id  
+                 WHERE A.del_flg=0 AND B.del_flg = 0 AND to_char(A.create_date, 'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD')
+                 AND to_char(A.create_date,'YYYY/MM/DD') != to_char(now(),'YYYY/MM/DD')";
     }else if (DB_TYPE == "mysql") {
-        $sql = "SELECT COUNT(*) FROM dtb_review 
-                 WHERE del_flg = 0 AND cast(substring(create_date,1, 10) as date) = DATE_ADD(current_date, interval -1 day)
-                 AND cast(substring(create_date,1, 10) as date) != cast(substring(now(),1, 10) as date)";
+        $sql = "SELECT COUNT(*) FROM dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id 
+                 WHERE A.del_flg = 0 AND B.del_flg = 0 AND cast(substring(A.create_date,1, 10) as date) = DATE_ADD(current_date, interval -1 day)
+                 AND cast(substring(A.create_date,1, 10) as date) != cast(substring(now(),1, 10) as date)";
     }
     $return = $conn->getOne($sql);
@@ -140 +140 @@
 
 function lfGetReviewNonDisp($conn){
-    $sql = "SELECT COUNT(*) FROM dtb_review WHERE del_flg=0 AND status=2";
+    $sql = "SELECT COUNT(*) FROM dtb_review AS A LEFT JOIN dtb_products AS B ON A.product_id = B.product_id WHERE A.del_flg=0 AND A.status=2 AND B.del_flg=0";
     $return = $conn->getOne($sql);
     return $return;
@@ -156 +156 @@
 function lfGetNewOrder() {
     $objQuery = new SC_Query();
-    $col = "ord.order_id, customer_id, ord.order_name01 AS name01, ord.order_name02 AS name02, ord.total, ";
-    $col.= "(SELECT det.product_name FROM dtb_order_detail AS det WHERE ord.order_id = det.order_id LIMIT 1) AS product_name, ";
-    $col.= "(SELECT pay.payment_method FROM dtb_payment AS pay WHERE ord.payment_id = pay.payment_id) AS payment_method, "; 
-    $col.= "create_date AS create_date";
-    $from = "dtb_order AS ord";
-    $where = "del_flg = 0";
-    $objQuery->setoption("ORDER BY create_date DESC LIMIT 10 OFFSET 0");
-    $arrRet = $objQuery->select($col, $from, $where);
+    $sql = "SELECT 
+                ord.order_id,
+                ord.customer_id,
+                ord.order_name01 AS name01,
+                ord.order_name02 AS name02,
+                ord.total,
+                ord.create_date,
+                (SELECT
+                    det.product_name
+                FROM
+                    dtb_order_detail AS det
+                WHERE
+                    ord.order_id = det.order_id LIMIT 1
+                ) AS product_name,
+                (SELECT
+                    pay.payment_method
+                FROM
+                    dtb_payment AS pay
+                WHERE
+                    ord.payment_id = pay.payment_id
+                ) AS payment_method 
+            FROM (
+                SELECT
+                    order_id,
+                    customer_id,
+                    order_name01,
+                    order_name02,
+                    total,
+                    create_date,
+                    payment_id
+                FROM
+                    dtb_order AS ord
+                WHERE
+                    del_flg = 0 AND status <> " . ORDER_CANCEL . " 
+                ORDER BY
+                    create_date DESC LIMIT 10 OFFSET 0
+            ) AS ord";
+    $arrRet = $objQuery->getAll($sql);
     return $arrRet;
 }