source: branches/comu/html/admin/home.php @ 11982

<?php
/*
 * Copyright(c) 2000-2007 LOCKON CO.,LTD. All Rights Reserved.
 *
 * http://www.lockon.co.jp/
 */
require_once("./require.php");

class LC_Page {
    var $arrSession;
    function LC_Page() {
        $this->tpl_mainpage = 'home.tpl';
    }
}

$conn = new SC_DBConn();
$objPage = new LC_Page();
$objView = new SC_AdminView();
$objSess = new SC_Session();

// ǧ¾Ú²ÄÈݤÎȽÄê
sfIsSuccess($objSess);

// DB¥Ð¡¼¥¸¥ç¥ó¤Î¼èÆÀ
$objPage->db_version = sfGetDBVersion();

// PHP¥Ð¡¼¥¸¥ç¥ó¤Î¼èÆÀ
$objPage->php_version = "PHP " . phpversion();

// ¸½ºß¤Î²ñ°÷¿ô
$objPage->customer_cnt = lfGetCustomerCnt($conn);

// ºòÆü¤ÎÇä¾å¹â
$objPage->order_yesterday_amount = lfGetOrderYesterday($conn, "SUM");

// ºòÆü¤ÎÇä¾å·ï¿ô
$objPage->order_yesterday_cnt = lfGetOrderYesterday($conn, "COUNT");

// º£·î¤ÎÇä¾å¹â
$objPage->order_month_amount = lfGetOrderMonth($conn, "SUM");

// º£·î¤ÎÇä¾å·ï¿ô
$objPage->order_month_cnt = lfGetOrderMonth($conn, "COUNT");

// ¸ÜµÒ¤ÎÎ߷ץݥ¤¥ó¥È
$objPage->customer_point = lfGetTotalCustomerPoint();

//ºòÆü¤Î¥ì¥Ó¥å¡¼½ñ¤­¹þ¤ß¿ô
$objPage->review_yesterday_cnt = lfGetReviewYesterday($conn);

//¥ì¥Ó¥å¡¼½ñ¤­¹þ¤ßÈóɽ¼¨¿ô
$objPage->review_nondisp_cnt = lfGetReviewNonDisp($conn);

// ÉÊÀڤ쾦ÉÊ
$objPage->arrSoldout = lfGetSoldOut();

// ¿·µ¬¼õÉÕ°ìÍ÷
$arrNewOrder = lfGetNewOrder();

foreach ($arrNewOrder as $key => $val){
    $arrNewOrder[$key]['create_date'] = str_replace("-", "/", substr($val['create_date'], 0,19));
    
}
$objPage->arrNewOrder = $arrNewOrder;

// ¤ªÃΤ餻°ìÍ÷¤Î¼èÆÀ
$objPage->arrInfo = lfGetInfo();

$objView->assignobj($objPage);
$objView->display(MAIN_FRAME);
//---------------------------------------------------------

// ²ñ°÷¿ô
function lfGetCustomerCnt($conn){
    
    $sql = "SELECT COUNT(customer_id) FROM dtb_customer WHERE del_flg = 0 AND status = 2";
    $return = $conn->getOne($sql);
    return $return;
}

// ºòÆü¤ÎÇä¾å¹â¡¦Çä¾å·ï¿ô
function lfGetOrderYesterday($conn, $method){
    if ( $method == 'SUM' or $method == 'COUNT'){
        // postgresql ¤È mysql ¤È¤ÇSQL¤ò¤ï¤±¤ë
        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') 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) AND status <> " . ORDER_CANCEL;
        }
        $return = $conn->getOne($sql);
    }
    return $return;
}

function lfGetOrderMonth($conn, $method){

    $month = date("Y/m", mktime());
    
    if ( $method == 'SUM' or $method == 'COUNT'){
    // postgresql ¤È mysql ¤È¤ÇSQL¤ò¤ï¤±¤ë
    if (DB_TYPE == "pgsql") {
        $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 status <> " . ORDER_CANCEL;
    }else if (DB_TYPE == "mysql") {
        $sql = "SELECT ".$method."(total) FROM dtb_order
                 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));
    }
    return $return;
}

function lfGetTotalCustomerPoint() {
    $objQuery = new SC_Query();
    $col = "SUM(point)";
    $where = "del_flg = 0";
    $from = "dtb_customer";
    $ret = $objQuery->get($from, $col, $where);
    return $ret;    
}

function lfGetReviewYesterday($conn){
    // postgresql ¤È mysql ¤È¤ÇSQL¤ò¤ï¤±¤ë
    if (DB_TYPE == "pgsql") {
        $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 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);
    return $return;
}

function lfGetReviewNonDisp($conn){
    $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;
}

// ÉÊÀڤ쾦ÉÊÈÖ¹æ¤Î¼èÆÀ
function lfGetSoldOut() {
    $objQuery = new SC_Query();
    $where = "product_id IN (SELECT product_id FROM dtb_products_class WHERE stock_unlimited IS NULL AND stock <= 0)";
    $arrRet = $objQuery->select("product_id, name", "dtb_products", $where);
    return $arrRet;
}

// ¿·µ¬¼õÉÕ°ìÍ÷
function lfGetNewOrder() {
    $objQuery = new SC_Query();
    $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;
}

// ¤ªÃΤ餻¼èÆÀ
function lfGetInfo() {
    // ¹¹¿·¾ðÊó¤òºÇ¿·¤Ë¤¹¤ë
    $objQuery = new SC_Query();
    $path = UPDATE_HTTP . "info.txt";
    $fp = @fopen($path, "rb");
    
    $arrRet = array();
    if(!$fp) {
        sfErrorHeader(">> " . $path . "¤Î¼èÆÀ¤Ë¼ºÇÔ¤·¤Þ¤·¤¿¡£");
    } else {
        while (!feof($fp)) {
            $arrRet[] = $arrCSV = fgetcsv($fp, UPDATE_CSV_LINE_MAX);
        }
        fclose($fp);
    }
    
    return $arrRet;
}



?>