<?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 delete = 0 AND status = 2";
	$return = $conn->getOne($sql);
	return $return;
}

// 昨日の売上高・売上件数
function lfGetOrderYesterday($conn, $method){
	if ( $method == 'SUM' or $method == 'COUNT'){
		$sql = "SELECT ".$method."(total) FROM dtb_order
				 WHERE delete = 0 AND to_char(create_date,'YYYY/MM/DD') = to_char(now() - interval '1 days','YYYY/MM/DD')";
		$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 delete = 0 AND to_char(create_date,'YYYY/MM') = ? 
				 AND to_char(create_date,'YYYY/MM/DD') <> to_char(now(),'YYYY/MM/DD')";
		$return = $conn->getOne($sql, array($month));
	}
	return $return;
}

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

function lfGetReviewYesterday($conn){
	$sql = "SELECT COUNT (*) FROM dtb_review 
			 WHERE delete=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')";
	$return = $conn->getOne($sql);
	return $return;
}

function lfGetReviewNonDisp($conn){
	$sql = "SELECT COUNT (*) FROM dtb_review WHERE delete=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";
	$from = "dtb_order AS ord";
	$where = "delete = 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;
}


?>