source: temp/trunk/html/admin/home.php @ 4715

<?php

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);

// ¸½ºß¤Î²ñ°÷¿ô
$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();

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

// ¤ªÃΤ餻°ìÍ÷¤Î¼èÆÀ
$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')";
        }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)";
        }
        $return = $conn->getOne($sql);
    }
    return $return;
}

function lfGetOrderMonth($conn, $method){

    $month = date("Y/m", mktime());
    
    if ( $method == 'SUM' or $method == 'COUNT'){
        $sql = "SELECT ".$method."(total) FROM dtb_order
                 WHERE del_flg = 0 AND cast(substring(create_date,1,6) as date) = ? 
                 AND cast(substring(create_date,1, 10) as date) <> cast(substring(now(),1, 10) as date)";
        $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 
                 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')";
    }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)";
    }
    $return = $conn->getOne($sql);
    return $return;
}

function lfGetReviewNonDisp($conn){
    $sql = "SELECT COUNT(*) FROM dtb_review WHERE del_flg=0 AND status=2";
    $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();
    $col = "ord.order_id, customer_id, (ord.order_name01 || ' ' || ord.order_name02) AS name, 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.= "to_char(create_date, 'YYYY/MM/DD HH24:MI') AS create_date";
    $col.= "cast(create_date as 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);
    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;
}



?>