<?php
/*
 * shopping/report/promotion/Statistic.class.php
 * 
 * CopyRight(C) 2010 Shopformer Development Team. All Right Reserved
 * URL: http://sourceforge.jp/projects/shopformer/
 * 
 * 
 * Mail: m_nakashima@users.sourceforge.jp
 * Auther: Masanori Nakashima
 * Last Update: 2010-02-12
 * 
 */
require_once(dirname(dirname(dirname(__FILE__)))
.DIRECTORY_SEPARATOR.'PackageConfig.class.php');
class shopping_report_promotion_Statistic extends system_login_ModuleBase {
	function shopping_report_promotion_Statistic() {
	}
	function execute( & $request ) {
		
		$dbo			= $request->getAttribute( 'dbo' );
		$sortItems		= $request->getParam('shpsi','get','array');
		$sortOrders		= $request->getParam('shpso','get','array');
		$startYear		= $request->getParam('sy','get');
		$startMonth		= $request->getParam('sm','get');
		$startDay		= $request->getParam('sd','get');
		$endYear		= $request->getParam('ey','get');
		$endMonth		= $request->getParam('em','get');
		$endDay			= $request->getParam('ed','get');
		$loginUserObject	= $this->getLoginUserObject( $request );

		// 並び替え順序の整理
		if( count($sortItems) == 0 || ( isset($sortItems[0]) && strlen($sortItems[0])==0 ) ) {
			$sortItems	= array();
			// 何も指定がなければ、「プロモーションID」を設定
			array_push($sortItems,'pid');
		}
		if( count($sortOrders) == 0 || ( isset($sortOrders[0]) && strlen($sortOrders[0])==0 ) ) {
			$sortOrders	= array();
		}
		$sortOrderArray	= array();
		foreach( $sortItems as $num => $itemName ) {
			if ( strlen(trim($itemName)) > 0 ) {
				if( isset($sortOrders[$num]) && preg_match('/^[dD][eE][sS][cC]$/',$sortOrders[$num]) > 0 ) {
					$sortOrders[$num]	= 'DESC';
				} else {
					$sortOrders[$num]	= 'ASC';
				}
				if ( $itemName == 'pname' ) {
					array_push( $sortOrderArray, TABLE_NAME_SHOPPING_PROMOTION.'.promotion_name '.$sortOrders[$num]);
				} else if ( $itemName == 'shpstf' ) {
					array_push( $sortOrderArray, TABLE_NAME_SHOPPING_PROMOTION.'.status_flag '.$sortOrders[$num]);
				} else {
					array_push( $sortOrderArray, TABLE_NAME_SHOPPING_ORDER.'.promotion_code '.$sortOrders[$num]);
				}
			}
		}
		if( count($sortOrderArray) > 0 ) {
			$orderCondition	= ' ORDER BY '.implode(',',$sortOrderArray);
		}

		// 不正な年月日入力の場合当月に設定する
		if( preg_match('/^(20[0-2][0-9]|203[0-8]|19[0-9][0-9])$/',$startYear) == 0 and strlen(trim($startYear)) == 0 ) {
			$startYear = date('Y');
		}
		if( preg_match('/^((|0)[1-9]|1[0-2])$/',$startMonth) == 0 and strlen(trim($startMonth)) == 0 ) {
			$startMonth = date('m');
		}
		if( preg_match('/^((|0)[1-9]|[12][0-9]|3[0-1])$/',$startDay) == 0 and strlen(trim($startDay)) == 0 ) {
			$startDay = date('1');
		}
		if( preg_match('/^(20[0-2][0-9]|203[0-8]|19[0-9][0-9])$/',$endYear) == 0 and strlen(trim($endYear)) == 0 ) {
			$endYear = date('Y');
		}
		if( preg_match('/^((|0)[1-9]|1[0-2])$/',$endMonth) == 0 and strlen(trim($endMonth)) == 0 ) {
			$endMonth = date('m');
		}
		if( preg_match('/^((|0)[1-9]|[12][0-9]|3[0-1])$/',$endDay) == 0 and strlen(trim($endDay)) == 0 ) {
			$endDay = date('t');
		}
		// 入力年月日の確認
		if( preg_match('/^[0-9]{4}/',$startYear) > 0
			&& preg_match('/^[0-9]{1,2}/',$startMonth) > 0
			&& preg_match('/^[0-9]{4}/',$endYear) > 0
			&& preg_match('/^[0-9]{1,2}/',$endMonth) > 0
		) {
			// 範囲両端が入力されている場合
			if( intval(sprintf('%04d',$startYear).sprintf('%02d',$startMonth)) > intval(sprintf('%04d',$endYear).sprintf('%02d',$endMonth)) ) {
				// 最初の年月の方が大きかったら値を入れ替え
				$tmpYear	= $startYear;
				$tmpMonth	= $startMonth;
				$tmpDay		= $startDay;
				$startYear	= $endYear;
				$startMonth	= $endMonth;
				$startDay	= $endDay;
				$endYear	= $tmpYear;
				$endMonth	= $tmpMonth;
				$endDay		= $tmpDay;
			}
		}
		// 日の設定
		if ( strlen(trim($startDay)) == 0
			|| !is_numeric($startDay)
			|| $startDay < 1
			|| $startDay > 31
		) {
			// 日が正しく指定されていない場合、1日を設定
			$startDay = 1;
		}
		if ( strlen(trim($endDay)) == 0
			|| !is_numeric($endDay)
			|| $endDay < 1
			|| $endDay > 31
		) {
			// 日が正しく指定されていない場合、月末を設定
			$end_time	= strtotime($endYear.'-'.$endMonth);
			$endDay	= date('t',$end_time);
		}
		$startDate	= '';
		$endDate	= '';
		spider_Controller::loadClassDefinition('util_ValidateFunctions');
		if( !util_ValidateFunctions::isAvailableDate( $startYear, $startMonth, $startDay ) ) {
			// 日付の指定が正しくない場合、条件をクリア
			$startYear	= '';
			$startMonth	= '';
			$startDay		= '';
		} else {
			$startDate = $startYear.'-'.$startMonth.'-'.$startDay;
		}
		if( !util_ValidateFunctions::isAvailableDate( $endYear, $endMonth, $endDay ) ) {
			// 日付の指定が正しくない場合、条件をクリア
			$endYear	= '';
			$endMonth	= '';
			$endDay	= '';
		} else {
			$endDate = $endYear.'-'.$endMonth.'-'.$endDay;
		}
		
		$resultHash	= array();
		if( !$request->isError() ) {
			// 期間中の申し込み数を状態ごとにセレクト
			$sql	= 'SELECT '.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id, '
				.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_name,'
				.TABLE_NAME_SHOPPING_PROMOTION.'.status_flag AS promotion_status_flag,'
				.TABLE_NAME_SHOPPING_ORDER.'.status_flag, '
				.'COUNT('.TABLE_NAME_SHOPPING_ORDER.'.order_id) AS order_count'
				.' FROM '.TABLE_NAME_SHOPPING_ORDER
				.' LEFT OUTER JOIN ' . TABLE_NAME_SHOPPING_PROMOTION
				.' ON ' . TABLE_NAME_SHOPPING_PROMOTION.'.promotion_code='.TABLE_NAME_SHOPPING_ORDER.'.promotion_code '
				.' WHERE ';
			if ( SYSTEM_LOGIN_USER_ADMINISTRATORS != $loginUserObject->getLoginGroupLevel() ) {
				$sql	.= TABLE_NAME_SHOPPING_PROMOTION.'.member_id='.$dbo->quote($loginUserObject->getUniqueId())
				.' AND ';
			}
			$sql	.= TABLE_NAME_SHOPPING_ORDER.'.order_date BETWEEN '.$dbo->quote($startDate.' 00:00:00').' AND '.$dbo->quote($endDate.' 23:59:59')
				.' AND '
				.TABLE_NAME_SHOPPING_ORDER.'.buying_flag=0 '
				.' AND '
				.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id IS NOT NULL '
				.' AND '
				.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id<>\'\''
				.' GROUP BY '.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id '
				.','.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_name'
				.',promotion_status_flag'
				.','.TABLE_NAME_SHOPPING_ORDER.'.status_flag '
				.$orderCondition
			;
			$result	= $dbo->queryAll( $sql, 'hash' );
			if( false === $result ) {
				$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
			} else {
				foreach( $result as $row ) {
					$promotionId	= trim($row['promotion_id']);
					if( !is_array($resultHash[$promotionId]) ) {
						$resultHash[$promotionId]	= array();
					}
					$resultHash[$promotionId]['promotion_name']		= $row['promotion_name'];
					$resultHash[$promotionId]['promotion_status_flag']	= $row['promotion_status_flag'];
					if( $row['status_flag'] < 200 ) {
						$resultHash[$promotionId]['count_available']	+= $row['order_count'];
					} else {
						$resultHash[$promotionId]['count_cancel']		+= $row['order_count'];
					}
				}
			}
			// プロモーション状態ごとの数を取得
			$sql	= 'SELECT '.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id, '
				.TABLE_NAME_SHOPPING_ORDER.'.status_promotion,'
				.' COUNT('.TABLE_NAME_SHOPPING_ORDER.'.order_id) AS order_count,'
				.' SUM('.TABLE_NAME_SHOPPING_ORDER.'.promotion_fee) AS total_fee'
				.' FROM '.TABLE_NAME_SHOPPING_ORDER
				.' LEFT OUTER JOIN ' . TABLE_NAME_SHOPPING_PROMOTION
				.' ON ' . TABLE_NAME_SHOPPING_PROMOTION.'.promotion_code='.TABLE_NAME_SHOPPING_ORDER.'.promotion_code '
				.' WHERE '
				.TABLE_NAME_SHOPPING_ORDER.'.order_date BETWEEN '.$dbo->quote($startDate.' 00:00:00').' AND '.$dbo->quote($endDate.' 23:59:59')
				.' AND '
				.TABLE_NAME_SHOPPING_ORDER.'.buying_flag=0 '
				.' AND '
				.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id IS NOT NULL '
				.' AND '
				.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id<>\'\''
				.' AND '
				.TABLE_NAME_SHOPPING_ORDER.'.status_flag<200'
				.' GROUP BY '.TABLE_NAME_SHOPPING_PROMOTION.'.promotion_id'
				.','.TABLE_NAME_SHOPPING_ORDER.'.status_promotion'
				.$orderCondition
			;
			$result	= $dbo->queryAll( $sql, 'hash' );
			if( false === $result ) {
				$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
			} else {
				foreach( $result as $row ) {
					$promotionId	= trim($row['promotion_id']);
					if( !is_array($resultHash[$promotionId]) ) {
						$resultHash[$promotionId]	= array();
					}
					$status				= $row['status_promotion'];
					$statusCountLabel	= 'count_'.$status;
					$statusMoneyLabel	= 'money_'.$status;
					$resultHash[$promotionId][$statusCountLabel]			= $row['order_count'];
					$resultHash[$promotionId][$statusMoneyLabel]			= $row['total_fee'];
				}
			}
		}
		$resultHashCount = count($resultHash);
		$request->setAttribute('shopping.report.promotion.statistic.resultHash', $resultHash);
		$request->setAttribute('shopping.report.promotion.statistic.total_count', $resultHashCount);

		$request->setAttribute( 'shopping.report.promotion.statistic.sortItems',	$sortItems );
		$request->setAttribute( 'shopping.report.promotion.statistic.sortOrders', $sortOrders );
		$request->setAttribute( 'shopping.report.promotion.statistic.start_year',	$startYear );
		$request->setAttribute( 'shopping.report.promotion.statistic.start_month',$startMonth );
		$request->setAttribute( 'shopping.report.promotion.statistic.start_day',	$startDay );
		$request->setAttribute( 'shopping.report.promotion.statistic.end_year',	$endYear );
		$request->setAttribute( 'shopping.report.promotion.statistic.end_month',	$endMonth );
		$request->setAttribute( 'shopping.report.promotion.statistic.end_day',	$endDay );
	}
}
?>