<?php
/***************************************************************************
*                                                                          *
* Copyright (c) 2005 ISPG Technologies (I) Pvt Ltd. All rights reserved.   *
*                                                                          *
* This  is  commercial  software,  only  users  who have purchased a valid *
* license  and  accept  to the terms of the  License Agreement can install *
* and use this program.                                                    *
*                                                                          *
****************************************************************************
* PLEASE READ THE FULL TEXT  OF THE SOFTWARE  LICENSE   AGREEMENT  IN  THE *
* "copyright.txt" FILE PROVIDED WITH THIS DISTRIBUTION PACKAGE.            *
****************************************************************************/
//
//$Id: UserprofileModel, v 1.0 2014/10/27 11:16:13
//All functions 
//
class UserprofileModel extends Model{
        //last inserted users
        public function saveUserProfile($userID=0) {  
				
			 	$sql = "INSERT INTO " . DB_PREFIX . "user_profile (
				`userID`,
				`userGroupID`,
				`userFirstName`,
				`userLastName`,
				`userEmail`,
				`userPhone`,
				`user_type`,
				`userRegistredDate`,
				`referedBy`)
				    SELECT 
						usr.`userID`, 
						userGroupID,
						userFirstName,
						userLastName,
						userEmail,
						userPhone,
						'registered',
						userRegistredDate,
						referedBy			
                    FROM 
						" . DB_PREFIX . "users usr
					LEFT JOIN " . DB_PREFIX . "users_referrals usr_ref
						ON usr_ref.referedTo=usr.`userID`				
					WHERE 
						userStatus <>'Deleted'  ";
				if($userID>0){
					$sql 	.=	"	AND userID ='".$userID."'";					
				}else{
					$sql 	.=	"	AND userRegistredDate > 
							(SELECT
								executionTime
							FROM 
								" . DB_PREFIX . "crone_execution_details
							WHERE 
								cronType='user_profile'
						)";
				}
						//echo $sql;exit;
				$this->ObjDatabase->setQuery($sql);			
        }
        //END FUNCTION
		//set cron for user profile
		public function setCronForUserProfile($params=array())
		{
			extract($params);
			$sql 	= " SELECT 1 FROM `" . DB_PREFIX . "user_profile` LIMIT 1";
			$resProfileCount	=	$this->ObjDatabase->getField($sql);
			if(empty($resProfileCount)){
				$this->saveUserProfile();	
			}
             //Todays updated orders
              $sql = "SELECT 
						orderCreatedDate, 
						orderSubtotal AS orderTotalAmount, 
						ordMas.masterOrderID, 
						orderQty, 
						billingAddress,
						purchasedCurrency AS order_currency,
						shippingAddress,
						billingFirstName,
						billingLastName,
						paymentSettingsGroupID,
						extraData,
						extra,
						guest_order,
						usr.userFirstName,
						usr.userLastName,
						usr.userPhone,
						usr.userID,
						ordMas.customer_email
                    FROM 
						" . DB_PREFIX . "order_masters ordMas
					LEFT JOIN " . DB_PREFIX . "order_items ordIte
					ON  ordMas.masterOrderID=ordIte.masterOrderID							
					LEFT JOIN " . DB_PREFIX . "users usr
					ON  ordMas.userID=usr.userID						
					AND ordMas.guest_order='No'
					AND ordMas.orderType<>'coupon'
					AND userStatus <>'Deleted'				
					WHERE 
						orderCreatedDate > 
						(SELECT
							executionTime
						FROM 
							" . DB_PREFIX . "crone_execution_details
						WHERE 
							cronType='user_profile'
						)
						GROUP BY	ordMas.masterOrderID	
						 ORDER BY  ordMas.masterOrderID DESC
						";
            $listLatestOrders		= $this->ObjDatabase->readValues($sql, MYSQLI_ASSOC);
			$tempUser				=	array();
			//Iterate the latest orders			
			foreach($listLatestOrders as $resorder){
				$total_order_qty			=0;
				$total_order_amount			=0;

			//echo "<pre>";print_r($resAllOrders);
			//$totalOrderCount			=	count($resAllOrders);
			
			//Iterate the whole orders	of a updated user
				$resorder['user_currency'] =  $resorder['order_currency'];
				if($resorder['guest_order']=='No'){					
					$user_temp_id =$resorder['userID'];				
				}else{
					$user_temp_id =$resorder['customer_email'].'~*'.$resorder['order_currency'];
				}
				$tempUser[$user_temp_id]['user_temp_id']	=isset($tempUser[$user_temp_id]['user_temp_id'])?$tempUser[$user_temp_id]['user_temp_id']:'';
				if(isset($tempUser[$user_temp_id]['user_temp_id']) && $tempUser[$user_temp_id]['user_temp_id']==''){
					$tempUser[$user_temp_id]['user_temp_id']	=	$user_temp_id;
					//Last updated order details
					if($resorder['guest_order']=='Yes'){	
						 $tempUser[$user_temp_id]['userFirstName']		=	isset($resorder['billingFirstName'])?$resorder['billingFirstName']:'';
						 $tempUser[$user_temp_id]['userLastName ']		=	isset($resorder['billingLastName'])?$resorder['billingLastName']:'';
						 $tempUser[$user_temp_id]['userPhone'] 			=	'';						
						 $tempUser[$user_temp_id]['user_type'] 			=	'guest';
						 $tempUser[$user_temp_id]['customer_email'] 	=	$resorder['customer_email'];
						 
					}else{
						 $tempUser[$user_temp_id]['userFirstName'] 		=	isset($resorder['userFirstName'])?$resorder['userFirstName']:'';
						 $tempUser[$user_temp_id]['userLastName'] 		=	isset($resorder['userLastName'])?$resorder['userLastName']:'';
						 $tempUser[$user_temp_id]['userPhone'] 			=	$resorder['userPhone'];
						 $tempUser[$user_temp_id]['user_type'] 			=	'registered';
						
					}
					
					 $tempUser[$user_temp_id]['last_order_date'] 		=	$resorder['orderCreatedDate'];
					 $tempUser[$user_temp_id]['last_oder_id'] 			=	$resorder['masterOrderID'];
					 $tempUser[$user_temp_id]['last_purchase_currency'] =	$resorder['order_currency'];
					 $tempUser[$user_temp_id]['last_payment_gateway'] 	=	$resorder['paymentSettingsGroupID'];
					 $tempUser[$user_temp_id]['last_order_amount'] 		=	$ObjView->getFormattedAmount($resorder['orderTotalAmount'],'5',$resorder['order_currency'],$resorder['user_currency']);
					 $billingAddress 		=   unserialize($resorder['billingAddress']);
					 $tempUser[$user_temp_id]['last_billing_country'] 	=	$billingAddress['billingCountry'];
					 $tempUser[$user_temp_id]['last_billing_state'] 	=	$billingAddress['billingState'];
					 $tempUser[$user_temp_id]['last_billing_city'] 		=	$billingAddress['billingCity'];
					 $tempUser[$user_temp_id]['last_billing_zip'] 		=	$billingAddress['billingZip'];
					 $shippingAddress 		=   unserialize($resorder['shippingAddress']);
					 $tempUser[$user_temp_id]['last_shipping_country'] 	=	$shippingAddress['shippingCountryID'];
					 $tempUser[$user_temp_id]['last_shipping_state'] 	=	$shippingAddress['shippingStateID'];
					 $tempUser[$user_temp_id]['last_shipping_city'] 	=	$shippingAddress['shippingCity'];
					 $tempUser[$user_temp_id]['last_shipping_zip'] 		=	$shippingAddress['shippingZip'];	
					 $extraData 				=   unserialize($resorder['extra']);				 
					 $tempUser[$user_temp_id]['last_shipping_method'] 	=	!empty($extraData['shippingMethod']['methodID'])?$extraData['shippingMethod']['methodID']:0;					 
					 
									
					
				}				
					//First updated order details
					$tempUser[$user_temp_id]['first_order_date'] 		=	$resorder['orderCreatedDate'];
					$tempUser[$user_temp_id]['first_oder_id'] 			=	$resorder['masterOrderID'];
					$tempUser[$user_temp_id]['first_order_amount'] 		=	$ObjView->getFormattedAmount($resorder['orderTotalAmount'],'5',$resorder['order_currency'],$resorder['user_currency']);	
					
			
				//Total order details
					$tempUser[$user_temp_id]['total_order_qty']	= isset($tempUser[$user_temp_id]['total_order_qty'])?$tempUser[$user_temp_id]['total_order_qty']:0;
					$tempUser[$user_temp_id]['total_order_qty']	    	+=		$resorder['orderQty'];
					$tempUser[$user_temp_id]['total_order']	= isset($tempUser[$user_temp_id]['total_order'])?$tempUser[$user_temp_id]['total_order']:0;					
					$tempUser[$user_temp_id]['total_order']	    	+=		1;
					
					$tempUser[$user_temp_id]['total_order_amount']	=isset($tempUser[$user_temp_id]['total_order_amount'])?$tempUser[$user_temp_id]['total_order_amount']:0;
					$tempUser[$user_temp_id]['total_order_amount']		+=		$ObjView->getFormattedAmount($resorder['orderTotalAmount'],'5',$resorder['order_currency'],$resorder['user_currency']);
		 }	
			//echo "<pre>";
			//print_r($tempUser);//exit;		 
			//query string for both insert & update
			//Iterate the latest orders			
			foreach($tempUser as $key=>$user){
			//echo "<pre>";
			//print_r($user);exit;						 
			 $user['userLastName']=isset($user['userLastName'])?$user['userLastName']:'';
			$updateData ="
					`userFirstName`='" . $user['userFirstName'] . "',
					`userLastName`='" . $user['userLastName'] . "',
					`userPhone`	  ='" . $user['userPhone'] . "',
					`user_type`	  ='" . $user['user_type'] . "',
					`first_order_date`=IF(`first_order_date`<>'0000-00-00',first_order_date,'" . $user['first_order_date'] . "'),
					`first_order_amount`=IF(`first_order_amount`>0.00000,first_order_amount,'" . $user['first_order_amount'] . "'),
					`first_oder_id`=IF(`first_oder_id`<>'0',first_oder_id,'" . $user['first_oder_id'] . "'),
					`last_order_date`='" . $user['last_order_date'] . "',
					`last_order_amount`='" . $user['last_order_amount'] . "',
					`last_oder_id`='" . $user['last_oder_id'] . "',
					`total_order_qty`=`total_order_qty`+'" . $user['total_order_qty'] . "',
					`total_order`=`total_order`+'" . $user['total_order'] . "',
					`total_order_amount`=`total_order_amount`+'" . $user['total_order_amount'] . "',
					`last_shipping_country`='" . $user['last_shipping_country'] . "',
					`last_shipping_state`='" . $user['last_shipping_state'] . "',
					`last_shipping_city`='" . $user['last_shipping_city'] . "',
					`last_shipping_zip`='" . $user['last_shipping_zip'] . "',
					`last_billing_country`='" . $user['last_billing_country'] . "',
					`last_billing_state`='" . $user['last_billing_state'] . "',
					`last_billing_city`='" . $user['last_billing_city'] . "',
					`last_billing_zip`='" . $user['last_billing_zip'] . "',
					`last_purchase_currency`='" . $user['last_purchase_currency'] . "',
					`last_payment_gateway`='" . $user['last_payment_gateway'] . "',
					`last_shipping_method`='" . $user['last_shipping_method'] . "'			
			
			";
			if($user['user_type']=='registered'){
			$sql = "
				UPDATE 
					`" . DB_PREFIX . "user_profile` 
				SET $updateData

			WHERE 
					";
				$sql .=	"	  userID='".$key."' ";
				//echo $sql;exit;
			$this->ObjDatabase->setQuery($sql);				
		  }else{
			 $sql 	= " SELECT 1 
						FROM 
							`" . DB_PREFIX . "user_profile`
						WHERE 
						userEmail='".$user['customer_email']."'	
						AND user_type			='guest'
						AND last_purchase_currency=		'".$user['last_purchase_currency']."'			
						LIMIT 1";
			$resProfileCount	=	$this->ObjDatabase->getField($sql);
			if(empty($resProfileCount)){
				$sql = "
					INSERT INTO 
						`" . DB_PREFIX . "user_profile` 
					SET $updateData,
					userEmail='".$user['customer_email']."'	
						";				 
				}else{
					$sql = "
						UPDATE 
							`" . DB_PREFIX . "user_profile` 
						SET $updateData

					WHERE 
							";
						$sql .=	"	userEmail='".$user['customer_email']."'	
									AND user_type			='guest'
									AND last_purchase_currency=		'".$user['last_purchase_currency']."'							
									";
				}				
			  $this->ObjDatabase->setQuery($sql);	
		 }	

		}
 			$sql = "
				UPDATE 
					`" . DB_PREFIX . "crone_execution_details` 
				SET executionTime=NOW()
			WHERE 
				cronType='user_profile'
					";   
			$this->ObjDatabase->setQuery($sql);
		return true;			
		}
	//manage/UserController.php
	//Get customer details to display in customer view page
	public function getCustomerProfileDetails($params=array())
	{
		extract($params);
		$sql  = "
				SELECT 
					total_order,
					last_order_date,
					last_order_amount,
					total_order_amount
				FROM
				`" . DB_PREFIX . "user_profile`	
				WHERE userID='".$userID."'
			";
		return	$this->ObjDatabase->readValue($sql);	
	}		
}