<?php
/*
 * shopping/order/LoadList.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-06-18
 */
require_once( dirname(dirname(__FILE__))
.DIRECTORY_SEPARATOR.'PackageConfig.class.php' );
class shopping_order_LoadList extends system_login_ModuleBase {
	function shopping_order_LoadList() {
	}
	function execute( & $request ) {
		$dbo			= $request->getAttribute( 'dbo' );
		$queryWords		= $request->getParam('shoq','get');
		$sortItems		= $request->getParam('shpsi','get','array');
		$sortOrders		= $request->getParam('shpso','get','array');
		$promotionCode	= $request->getParam('shppc','get');
		$statusFlag		= $request->getParam('shpstf','get');
		$upperStatusFlag		= $request->getParam('shpustf','get');
		$settleMethod		= $request->getParam('shpsettle','get');
		$statusSettle		= $request->getParam('shpstsettle','get');
		$statusPromotion	= $request->getParam('shpstpromo','get');
		// 商品から検索
		$productQuery	= $request->getParam('shoppq','get');
		// 申し込み者
		$memberId				= $request->getParam('shpmi','get');
		$memberClass		= $request->getParam('shpmc','get');
		// 商品登録者から検索
		$unitOwnerId	= $request->getParam('shpiom','get');
		$startYear		= $request->getParam('shpsy','get');
		$startMonth		= $request->getParam('shpsm','get');
		$startDay		= $request->getParam('shpsd','get');
		$endYear		= $request->getParam('shpey','get');
		$endMonth		= $request->getParam('shpem','get');
		$endDay			= $request->getParam('shped','get');
		// 検索データ連携
		$searchDataId	= $request->getParam('schdi','get');
		// 操作中のユーザ
		$loginUserObject	= $this->getLoginUserObject( $request );

		// 並び替え順序の整理
		if( count($sortItems) == 0 || ( isset($sortItems[0]) && strlen($sortItems[0])==0 ) ) {
			$sortItems	= array();
			array_push($sortItems,'order_date');
			array_push($sortItems,'registered_date');
		}
		if( count($sortOrders) == 0 || ( isset($sortOrders[0]) && strlen($sortOrders[0])==0 ) ) {
			$sortOrders	= array();
		}
		$sortOrderArray	= array();
		foreach( $sortItems as $num => $itemName ) {
			if( strlen($itemName) > 0 ) {
				if( isset($sortOrders[$num])
				&& preg_match('/^[aA][sS][cC]$/',$sortOrders[$num]) > 0 ) {
					$sortOrders[$num]	= 'ASC';
				} else {
					$sortOrders[$num]	= 'DESC';
				}
				array_push( $sortOrderArray, TABLE_NAME_SHOPPING_ORDER.'.'.$itemName.' '.$sortOrders[$num]);
			}
		}
		if( count($sortOrderArray) > 0 ) {
			$orderCondition	= ' ORDER BY sdatetime DESC, '
			.implode(',',$sortOrderArray);
		}
		// 期間指定 開始年月日の整理
		if( SYSTEM_LOGIN_USER_ADMINISTRATORS == $loginUserObject->getLoginGroupLevel() ) {
			if( preg_match('/^[0-9]{4}$/',$startYear) == 0 || $startYear > date('Y') || $startYear < date('Y') - 10 ) {
				$startYear	= date('Y');
			} else {
				$startYear	= sprintf('%04d',$startYear);
			}
			if( preg_match('/^[0-9]{1,2}$/',$startMonth) == 0 || $startMonth > 12 || $startMonth < 1 ) {
				$startMonth	= date('m');
			} else {
				$startMonth	= sprintf('%02d',$startMonth);
			}
			$startYearMonth			= $startYear.'-'.$startMonth.'-01';
			$startYearMonthDayCount	= date('t',strtotime($startYearMonth));
			if( preg_match('/^[0-9]{1,2}$/',$startDay) == 0 || $startDay < 1 || $startDay > $startYearMonthDayCount ) {
				$startDay	= '01';
			} else {
				$startDay	= sprintf('%02d',$startDay);
			}
		} else {
			$startYear	= date('Y',strtotime('-1 year'));
			$startMonth	= date('m',strtotime('-1 year'));
			$startDay	= '01';
		}
		// 期間指定 終了年月日の整理
		if( preg_match('/^[0-9]{4}$/',$endYear) == 0 || $endYear > date('Y') || $endYear < date('Y') - 10 ) {
			$endYear	= date('Y');
		} else {
			$endYear	= sprintf('%04d',$endYear);
		}
		if( preg_match('/^[0-9]{1,2}$/',$endMonth) == 0 || $endMonth > 12 || $endMonth < 1 ) {
			$endMonth	= date('m');
		} else {
			$endMonth	= sprintf('%02d',$endMonth);
		}
		$endYearMonth			= $endYear.'-'.$endMonth.'-01';
		$endYearMonthDayCount	= date('t',strtotime($endYearMonth));
		if( preg_match('/^[0-9]{1,2}$/',$endDay) == 0 || $endDay < 1 || $endDay > $endYearMonthDayCount ) {
			$endDay	= $endYearMonthDayCount;
		} else {
			$endDay	= sprintf('%02d',$endDay);
		}
		// 日付前後が逆なら入れ替え
		if( $startYear.$startMonth.$startDay > $endYear.$endMonth.$endDay ) {
			$ty	= $startYear;
			$tm	= $startMonth;
			$td	= $startDay;
			$startYear	= $endYear;
			$startMonth	= $endMonth;
			$startDay	= $endDay;
			$endYear	= $ty;
			$endMonth	= $tm;
			$endDay		= $td;
		}
				
		$conditionArray	= array();
		if ( strlen( $queryWords ) > 0 ) {
			$queryWords	= str_replace('　', ' ', $queryWords);
			while( strpos($queryWords,'  ') !== false ) {
				$queryWords	= str_replace('  ',' ',$queryWords);
			}
			$queryWordArray	= explode(' ',$queryWords);
			foreach( $queryWordArray as $queryWord ) {
				if( strlen($queryWord) > 0 ) {
					// 検索文字列
					$str	= '( '.TABLE_NAME_SHOPPING_ORDER.'.order_id LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.member_id LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.order_comment LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.admin_note LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.member_id IN ( SELECT member_id FROM '.TABLE_NAME_MEMBER.' WHERE '
						.' '.TABLE_NAME_MEMBER.'.view_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.first_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.family_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.first_kana LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.family_kana LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.pc_mail LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.mb_mail LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.zip_code LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_MEMBER.'.telephone_number LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.')'
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.order_id IN ( SELECT order_id FROM '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.' WHERE '
						.' '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.address_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.address_kana LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.address LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.building LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.zip_code LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_ORDER_ADDRESS.'.telephone_number LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.')'
						.' OR '
						.TABLE_NAME_SHOPPING_ORDER.'.order_id IN ( SELECT order_id FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
						.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_PRODUCT_UNIT
						.' ON '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id='.TABLE_NAME_SHOPPING_ORDER_ITEM.'.unit_id'
						.' OR '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id='.TABLE_NAME_SHOPPING_ORDER_ITEM.'.used_unit_id'
						.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_PRODUCT
						.' ON '.TABLE_NAME_SHOPPING_PRODUCT.'.product_id='.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.product_id'
						.' WHERE '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.unit_id LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_PRODUCT.'.product_name LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.' OR '.TABLE_NAME_SHOPPING_PRODUCT.'.product_kana LIKE '.$dbo->quote( '%'.$queryWord.'%' )
						.')'
					;
					$str	.= ' )'	;
					array_push( $conditionArray, $str );
				}
			}
		}
		// プロモーションコード
		if( strlen($promotionCode) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.promotion_code='.$dbo->quote($promotionCode);
			array_push( $conditionArray, $str );
		}
		// 決済方法
		if( preg_match('/^[0-9]+$/',$settleMethod) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.settle_method='.$settleMethod;
			array_push( $conditionArray, $str );
		}
		// 状態フラグ
		if( preg_match('/^[0-9]+$/',$statusFlag) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.status_flag='.$statusFlag;
			array_push( $conditionArray, $str );
		} else if( preg_match('/^[0-9]+$/',$upperStatusFlag) > 0 ){
			$str	= TABLE_NAME_SHOPPING_ORDER.'.status_flag<='.$upperStatusFlag;
			array_push( $conditionArray, $str );
		} else {
			array_push( $conditionArray, TABLE_NAME_SHOPPING_ORDER.'.status_flag<200' );
		}
		// 決済状態フラグ
		if( preg_match('/^[0-9]+$/',$statusSettle) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.status_settle='.$statusSettle;
			array_push( $conditionArray, $str );
		}
		// プロモーション状態フラグ
		if( preg_match('/^[0-9]+$/',$statusPromotion) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.status_promotion='.$statusPromotion;
			array_push( $conditionArray, $str );
		}
		// 期間
		$str	= TABLE_NAME_SHOPPING_ORDER.'.order_date BETWEEN '.$dbo->quote($startYear.'-'.$startMonth.'-'.$startDay.' 00:00:00')
			.' AND '.$dbo->quote($endYear.'-'.$endMonth.'-'.$endDay.' 23:59:59');
		array_push( $conditionArray, $str );
		// サービス提供開始予定日
		$str	= $this->_createServiceStartCondition( $request );
		if( strlen($str) > 0 ) {
			array_push( $conditionArray, $str );
		}
		// サービス提供終了予定日
		$str	= $this->_createServiceLimitCondition( $request );
		if( strlen($str) > 0 ) {
			array_push( $conditionArray, $str );
		}
		// 指定日サービス中
		$str	= $this->_createOnServiceCondition( $request );
		if( strlen($str) > 0 ) {
			array_push( $conditionArray, $str );
		}
		// 指定メール送信日
		$str	= $this->_createSendMailDayCondition( $request );
		if( strlen($str) > 0 ) {
			array_push( $conditionArray, $str );
		}
		// 商品条件
		if( strlen($productQuery) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('
				.' SELECT DISTINCT '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id '
				.' FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
				.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_PRODUCT_UNIT
				.' ON '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id='.TABLE_NAME_SHOPPING_ORDER_ITEM.'.unit_id'
				.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_PRODUCT
				.' ON '.TABLE_NAME_SHOPPING_PRODUCT.'.product_id='.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.product_id'
				.' WHERE '
				.TABLE_NAME_SHOPPING_PRODUCT.'.product_id='.$dbo->quote($productQuery)
				.' OR '
				.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id='.$dbo->quote($productQuery)
				.' OR '
				.TABLE_NAME_SHOPPING_PRODUCT.'.product_name LIKE '.$dbo->quote('%'.$productQuery.'%')
				.' OR '
				.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_name LIKE '.$dbo->quote('%'.$productQuery.'%')
				.' )'
			;
			array_push( $conditionArray, $str );
		}
		// 申し込み者情報
		if( SYSTEM_LOGIN_USER_ADMINISTRATORS == $loginUserObject->getLoginGroupLevel() ) {
			// 管理者の場合全て閲覧可能
			if( strlen($memberId) > 0 ) {
				$str	= TABLE_NAME_SHOPPING_ORDER.'.member_id='.$dbo->quote($memberId);
				array_push( $conditionArray, $str );
				// 対象の会員情報を取得しておく
				$memberObject	= spider_Controller::createObject('member_DaoMember');
				if( $dbo->loadById($memberObject,$memberId) ) {
					$request->setAttribute('shopping.order.memberObject',$memberObject);
				}
			}
			if( preg_match('/^[0-9]{1,3}/',$memberClass)>0 ){
				$str	= TABLE_NAME_SHOPPING_ORDER.'.member_id IN ('
				.'SELECT '.TABLE_NAME_MEMBER.'.member_id FROM '.TABLE_NAME_MEMBER
				.' WHERE member_class='.$memberClass
				.')';
				array_push( $conditionArray, $str );
			}
		} else if( $this->isLogin($request) ){
			// 管理者以外でログインしている場合は自分の関連履歴以外閲覧できない
			$str	= '( '.TABLE_NAME_SHOPPING_ORDER.'.member_id='.$dbo->quote($loginUserObject->getUniqueId())
				.' OR '.TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('
				.' SELECT '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id '
				.' FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
				.' WHERE '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.unit_id IN('
					.' SELECT '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id FROM '.TABLE_NAME_SHOPPING_PRODUCT_UNIT
					.' WHERE '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.owner_id='.$dbo->quote($loginUserObject->getUniqueId()).')'
				.' ) )'
			;
			array_push( $conditionArray, $str );
		}
		// 商品登録者ＩＤ
		if( strlen($unitOwnerId) > 0 ) {
			$str	= TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('
				.' SELECT DISTINCT '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id '
				.' FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
				.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_PRODUCT_UNIT
				.' ON '.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.unit_id='.TABLE_NAME_SHOPPING_ORDER_ITEM.'.unit_id'
				.' WHERE '
				.TABLE_NAME_SHOPPING_PRODUCT_UNIT.'.owner_id='.$dbo->quote($unitOwnerId)
				.' )'
			;
			array_push( $conditionArray, $str );
		}
		// 検索データ連携
		if( strlen($searchDataId) > 0 ){
			$str	= TABLE_NAME_SHOPPING_ORDER.'.search_data_id='.$dbo->quote($searchDataId);
			array_push( $conditionArray, $str );
		}
				
		$conditionString	= '';
		if ( count( $conditionArray ) > 0 ) {
			$conditionString	= ' WHERE '.implode( ' AND ', $conditionArray );
		}
		// Last Query
		$lastQueryHash	= array();
		
		// トータル件数を取得
		$sql	= 'SELECT COUNT(DISTINCT('
			.TABLE_NAME_SHOPPING_ORDER.'.order_id)) '
			.' FROM '.TABLE_NAME_SHOPPING_ORDER
			.$conditionString
		;
		$lastQueryHash['countQuery']	= $sql;
		$totalCount	= 0;
		$result			= $dbo->queryOne( $sql );
		if ( false === $result ) {
			$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
		} else {
			$totalCount	= $result;
		}
		
		// ページ分取得
		$sql	= 'SELECT '.TABLE_NAME_SHOPPING_ORDER.'.* '
		.',MIN('.TABLE_NAME_SHOPPING_ORDER_ITEM.'.svc_start_date) AS sdatetime'
		.' FROM '.TABLE_NAME_SHOPPING_ORDER
		.' LEFT OUTER JOIN '.TABLE_NAME_SHOPPING_ORDER_ITEM
		.' ON '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id='.TABLE_NAME_SHOPPING_ORDER.'.order_id'
		.$conditionString
		.' GROUP BY '.TABLE_NAME_SHOPPING_ORDER.'.order_id '
		.$orderCondition;
		$obj		= spider_Controller::createObject('shopping_DaoShoppingOrder');
		$result		= $dbo->queryAll( $sql,$obj,false,false );
		$lastQueryHash['loadQuery']	= $sql;
		$request->setSession(SHOPPING_SESSION_NAME_ORDER_SEARCH_LAST_QUERY, $lastQueryHash, SPIDER_SESSION_SCOPE_GLOBAL);
		if ( false === $result ) {
			$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
		} else {
			// 注文情報の整理
			$orderIdObjectHash		= array();
			$memberIdObjectHash		= array();
			$quotedOrderIdArray		= array();
			$quotedMemberIdArray	= array();
			foreach( $result as $key => $shoppingOrderObject ){
				$orderIdObjectHash[$shoppingOrderObject->order_id]		= & $result[$key];
				if( !is_array($memberIdObjectHash[$shoppingOrderObject->member_id]) ){
					$memberIdObjectHash[$shoppingOrderObject->member_id]	= array();
				}
				$memCount = count($memberIdObjectHash[$shoppingOrderObject->member_id]);
				$memberIdObjectHash[$shoppingOrderObject->member_id][$memCount]	= & $result[$key];
				array_push( $quotedOrderIdArray, $dbo->quote($shoppingOrderObject->order_id));
				array_push( $quotedMemberIdArray, $dbo->quote($shoppingOrderObject->member_id));
				$result[$key]->getRelatedPromotionObject( $dbo->request );
				if( strlen($result[$key]->search_data_id) > 0 ) {
					$searchDataObject = spider_Controller::createObject('search_DaoSearchData');
					if( $dbo->loadById( $searchDataObject, $result[$key]->search_data_id) ) {
						$result[$key]->searchDataObject	= $searchDataObject;
					} else {
						$result[$key]->searchDataObject	= null;
					}
				}
			}
			if( count($orderIdObjectHash) > 0 ){
				// 会員情報の一括読み込み
				$sql	= 'SELECT * FROM '.TABLE_NAME_MEMBER.' WHERE member_id IN('.implode(',',$quotedMemberIdArray).')';
				$memberObjectArray	= $dbo->queryAll( $sql,new member_DaoMember(),false,false );
				if ( false === $memberObjectArray ) {
					$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
				} else {
					foreach( $memberObjectArray as $memberObject ){
						if( is_array($memberIdObjectHash[$memberObject->member_id]) ) {
							foreach( $memberIdObjectHash[$memberObject->member_id] as $mNum => $ordObj ){
								if( is_object($memberIdObjectHash[$memberObject->member_id][$mNum]) ){
									$memberIdObjectHash[$memberObject->member_id][$mNum]->memberObject	= $memberObject;
									if( strlen($memberObject->mb_mail) > 0
									&& ( $memberIdObjectHash[$memberObject->member_id][$mNum]->mail_flag == 2
									|| $memberIdObjectHash[$memberObject->member_id][$mNum]->mail_flag == 12 ) ) {
										$memberIdObjectHash[$memberObject->member_id][$mNum]->memberObject->email	= $memberObject->mb_mail;
										$memberIdObjectHash[$memberObject->member_id][$mNum]->email	= $memberObject->mb_mail;
									} else if( strlen($memberObject->pc_mail) == 0 ) {
										$memberIdObjectHash[$memberObject->member_id][$mNum]->memberObject->email	= $memberObject->mb_mail;
										$memberIdObjectHash[$memberObject->member_id][$mNum]->email	= $memberObject->mb_mail;
									} else {
										$memberIdObjectHash[$memberObject->member_id][$mNum]->memberObject->email	= $memberObject->pc_mail;
										$memberIdObjectHash[$memberObject->member_id][$mNum]->email	= $memberObject->pc_mail;
									}
								}
							}
						}
					}
				}
				// 紐づき宛先情報の一括読み込み
				$shoppingOrderAddressObject	= spider_Controller::createObject('shopping_DaoShoppingOrderAddress');
				$sql	= 'SELECT * FROM '.TABLE_NAME_SHOPPING_ORDER_ADDRESS
					.' WHERE order_id IN('.implode(',',$quotedOrderIdArray).')'
					.' ORDER BY order_id ASC, address_order ASC';
				$shoppingOrderAddressObjectArray	= $dbo->queryAll( $sql,$shoppingOrderAddressObject,false,false );
				if ( false === $shoppingOrderAddressObjectArray ) {
					$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
				} else {
					foreach( $shoppingOrderAddressObjectArray as $shoppingOrderAddressObject ) {
						if( is_object($orderIdObjectHash[$shoppingOrderAddressObject->order_id]) ){
							if( !is_array($orderIdObjectHash[$shoppingOrderAddressObject->order_id]->shoppingOrderAddressObjectArray) ){
								$orderIdObjectHash[$shoppingOrderAddressObject->order_id]->shoppingOrderAddressObjectArray	= array();
							}
							array_push($orderIdObjectHash[$shoppingOrderAddressObject->order_id]->shoppingOrderAddressObjectArray,$shoppingOrderAddressObject);
						}
					}
				}
				// 紐づきアイテム情報の一括読み込み
				$itemRefHash	= array();
				$quotedUnitIdArray	= array();
				$shoppingOrderItemObject	= spider_Controller::createObject('shopping_DaoShoppingOrderItem');
				$sql	= 'SELECT * FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
					.' WHERE order_id IN('.implode(',',$quotedOrderIdArray).')'
					.' ORDER BY order_id ASC, item_number ASC';
				$shoppingOrderItemObjectArray	= $dbo->queryAll( $sql,$shoppingOrderItemObject,false,false );
				if ( false === $shoppingOrderItemObjectArray ) {
					$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
				} else {
					foreach( $orderIdObjectHash as $oId => $orderObject ) {
						foreach( $orderObject->shoppingOrderAddressObjectArray as $aId => $addressObject ){
							$deferentItemObjectArray	= array();
							foreach( $shoppingOrderItemObjectArray as $shoppingOrderItemObject ) {
								if( $addressObject->address_id == $shoppingOrderItemObject->address_id ){
									array_push($orderIdObjectHash[$oId]->shoppingOrderAddressObjectArray[$aId]->shoppingOrderItemObjectArray,$shoppingOrderItemObject);
									$cnt	= count($orderIdObjectHash[$oId]->shoppingOrderAddressObjectArray[$aId]->shoppingOrderItemObjectArray);
									$itemRefHash[$shoppingOrderItemObject->item_id]	= $orderIdObjectHash[$oId]->shoppingOrderAddressObjectArray[$aId]->shoppingOrderItemObjectArray[$cnt-1];
									array_push($quotedUnitIdArray,$dbo->quote($shoppingOrderItemObject->unit_id));
								} else {
									array_push($deferentItemObjectArray,$shoppingOrderItemObject);
								}
							}
							$shoppingOrderItemObjectArray	= $deferentItemObjectArray;
						}
					}
				}
				// 紐づきアイテムの販売単位情報を一括読み込み
				$sql	= 'SELECT * FROM '.TABLE_NAME_SHOPPING_PRODUCT_UNIT
					.' WHERE unit_id IN('.implode(',',$quotedUnitIdArray).')';
				$shoppingUnitObject	= spider_Controller::createObject('shopping_DaoShoppingProductUnit');
				$shoppingUnitObjectArray	= $dbo->queryAll( $sql,$shoppingUnitObject,false,false );
				if ( false === $shoppingOrderItemObjectArray ) {
					$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
				} else {
					foreach( $shoppingUnitObjectArray as $unitObject ){
						foreach( $itemRefHash as $key => $itemObject ){
							if( $unitObject->unit_id == $itemObject->unit_id ){
								$itemRefHash[$key]->shoppingProductUnitObject	= $unitObject;
							}
						}
					}
				}

				// 紐づきオプション情報の一括読み込み
				spider_Controller::loadClassDefinition('shopping_DaoShoppingOrderOption');
				$sql	= 'SELECT * FROM '.TABLE_NAME_SHOPPING_ORDER_OPTION
					.' WHERE order_id IN('.implode(',',$quotedOrderIdArray).')'
					.' ORDER BY order_id ASC, item_order ASC';
				$optionRows	= $dbo->queryAll( $sql, 'hash' );
				if ( false === $optionRows ) {
					$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
				} else {
					$optionObjectArray	= array();
					foreach( $optionRows as $optionRow ) {
						$optionObject	= null;
						if( strlen($optionRow['option_class']) > 0 ){
							$optionObject	= new $optionRow['option_class'];
						} else {
							$optionObject	= spider_Controller::createObject('shopping_DaoShoppingOrderOption');
						}
						if( is_object($optionObject) ){
							$optionObject->setFieldByHash($optionRow);
							if( strlen($optionObject->item_id) == 0 && is_object($orderIdObjectHash[$optionObject->order_id]) ){
								if( !is_array($orderIdObjectHash[$optionObject->order_id]->shoppingOrderOptionObjectArray) ){
									$orderIdObjectHash[$optionObject->order_id]->shoppingOrderOptionObjectArray	= array();
								}
								array_push($orderIdObjectHash[$optionObject->order_id]->shoppingOrderOptionObjectArray,$optionObject);
							} else if( is_object($itemRefHash[$optionObject->item_id]) ) {
								if( !is_array($itemRefHash[$optionObject->item_id]->shoppingOrderOptionObjectArray) ){
									$itemRefHash[$optionObject->item_id]->shoppingOrderOptionObjectArray	= array();
								}
								array_push($itemRefHash[$optionObject->item_id]->shoppingOrderOptionObjectArray,$optionObject);
							}
						}
					}
				}
			}
			$request->setAttribute( 'shopping.order.data_array', $orderIdObjectHash );
			$request->setAttribute( 'shopping.order.totalCount', $totalCount );
			$request->setAttribute( 'shopping.order.query', $queryWords );
			$request->setAttribute( 'shopping.order.sortItems', $sortItems );
			$request->setAttribute( 'shopping.order.sortOrders', $sortOrders );
			$request->setAttribute( 'shopping.order.promotionCode', $promotionCode );
			$request->setAttribute( 'shopping.order.statusFlag', $statusFlag );
			$request->setAttribute( 'shopping.order.settleMethod', $settleMethod );
			$request->setAttribute( 'shopping.order.statusSettle', $statusSettle );
			$request->setAttribute( 'shopping.order.statusPromotion', $statusPromotion );
			$request->setAttribute( 'shopping.order.productQuery', $productQuery );
			$request->setAttribute( 'shopping.order.unitOwnerId', $unitOwnerId );
			$request->setAttribute( 'shopping.order.startYear', $startYear );
			$request->setAttribute( 'shopping.order.startMonth', $startMonth );
			$request->setAttribute( 'shopping.order.startDay', $startDay );
			$request->setAttribute( 'shopping.order.endYear', $endYear );
			$request->setAttribute( 'shopping.order.endMonth', $endMonth );
			$request->setAttribute( 'shopping.order.endDay', $endDay );
			$request->setAttribute( 'shopping.order.memberId', $memberId );
		}
		
		// 期間の集計作成
		$sql	= 'SELECT '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.price_total) AS total_price, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.promotion_fee) AS total_fee, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.discount_price) AS total_discount, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.payment_total) AS total_payment, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.settle_price) AS total_settle, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.delivery_price) AS total_delivery, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.wrapping_price) AS total_wrapping, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.settle_fee) AS total_settle_fee, '
			.'SUM('.TABLE_NAME_SHOPPING_ORDER.'.cost_total) AS total_cost '
			.' FROM '.TABLE_NAME_SHOPPING_ORDER
			.$conditionString
		;
		$result		= $dbo->queryRow( $sql,'hash' );
		if ( false === $result ) {
			$request->addLocaledError('database2.error.common',SPIDER_LOG_LEVEL_FATAL,array($sql));
		} else {
			$request->setAttribute( 'shopping.order.totalPrice', $result['total_price'] );
			$request->setAttribute( 'shopping.order.totalSettle', $result['total_settle'] );
			$request->setAttribute( 'shopping.order.totalFee', $result['total_fee'] );
			$request->setAttribute( 'shopping.order.totalDiscount', $result['total_discount'] );
			$request->setAttribute( 'shopping.order.totalPayment', $result['total_payment'] );
			$request->setAttribute( 'shopping.order.totalCost', $result['total_cost'] );
			$request->setAttribute( 'shopping.order.totalDelivery', $result['total_delivery'] );
			$request->setAttribute( 'shopping.order.totalWrapping', $result['total_wrapping'] );
			$request->setAttribute( 'shopping.order.totalSettleFee', $result['total_settle_fee'] );
		}
		
	}
	/**
	 * サービス開始日で検索
	 */
	function _createServiceStartCondition( & $request ) {
		$dbo				= $request->getAttribute('dbo');
		$condition	= $dbo->createTermConditionStrings($request,
		TABLE_NAME_SHOPPING_ORDER_ITEM.'.svc_start_date','shpsvcs','shopping.order.svcStart');
		if( strlen($condition) > 0 ){
			$subSql	= 'SELECT '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
			.' WHERE '.$condition;
			return TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('.$subSql.')';
		}
		return '';
	}
	/**
	 * サービス終了日で検索
	 */
	function _createServiceLimitCondition( & $request ) {
		$dbo				= $request->getAttribute('dbo');
		$condition	= $dbo->createTermConditionStrings($request,
		TABLE_NAME_SHOPPING_ORDER_ITEM.'.svc_limit_date','shpsvcl','shopping.order.svcLimit');
		if( strlen($condition) > 0 ){
			$subSql	= 'SELECT '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.order_id FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
			.' WHERE '.$condition;
			return TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('.$subSql.')';
		}
		return '';
	}
	/**
	 * 宿泊サービス中で検索
	 */
	function _createOnServiceCondition( & $request ) {
		$dbo				= $request->getAttribute('dbo');
		$taretYear	= $request->getParam('shponsty','get');
		$targetMonth= $request->getParam('shponstm','get');
		$targetDay	= $request->getParam('shponstd','get');
		$targetDate	= $taretYear.'-'.$targetMonth.'-'.$targetDay;
		spider_Controller::loadClassDefinition('util_ValidateFunctions');
		if( util_ValidateFunctions::isAvailableDateFormat($targetDate) ){
			$subSql	= 'SELECT order_id FROM '.TABLE_NAME_SHOPPING_ORDER_ITEM
			.' WHERE '.$dbo->quote($targetDate.' 23:59:59').' BETWEEN '.TABLE_NAME_SHOPPING_ORDER_ITEM.'.svc_start_date AND '
			.TABLE_NAME_SHOPPING_ORDER_ITEM.'.svc_limit_date';
			$request->setAttribute('shopping.order.onServiceYear', $taretYear );
			$request->setAttribute('shopping.order.onServiceMonth', $targetMonth );
			$request->setAttribute('shopping.order.onServiceDay', $targetDay );
			return TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('.$subSql.')';
		}
		return '';
	}
	/**
	 * メールテンプレートと送信日時で検索
	 */
	function _createSendMailDayCondition( & $request ){
		$templateId	= $request->getParam('shpmailt','get');
		if( strlen($templateId) > 0 ){
			$dbo				= $request->getAttribute('dbo');
			$condition	= $dbo->createTermConditionStrings($request,
			TABLE_NAME_SHOPPING_ORDER_HISTORY.'.history_date','shphstm','shopping.order.historyMail');
			if( strlen($condition) > 0 ){
				$request->setAttribute('shopping.order.mailTemplateId', $templateId);
				$subSql	= 'SELECT '.TABLE_NAME_SHOPPING_ORDER_HISTORY.'.order_id FROM '.TABLE_NAME_SHOPPING_ORDER_HISTORY
				.' WHERE template_id='.$dbo->quote($templateId).' AND '.$condition;
				return TABLE_NAME_SHOPPING_ORDER.'.order_id IN ('.$subSql.')';
			}
		}
		return '';
	}
}
?>