Location: PHPKode > scripts > TheCartPress > thecartpress/daos/Orders.class.php
<?php
/**
 * This file is part of TheCartPress.
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

require_once( 'OrdersDetails.class.php' );
require_once( 'OrdersCosts.class.php' );

class Orders {

	public static $ORDER_PENDING	= 'PENDING';
	public static $ORDER_PROCESSING	= 'PROCESSING';
	public static $ORDER_COMPLETED	= 'COMPLETED';
	public static $ORDER_CANCELLED	= 'CANCELLED';
	public static $ORDER_SUSPENDED	= 'SUSPENDED';

	static function createTable() {
		global $wpdb;
		$sql = 'CREATE TABLE IF NOT EXISTS `' . $wpdb->prefix . 'tcp_orders` (
		  `order_id`				bigint(20) unsigned NOT NULL auto_increment,
		  `created_at`				datetime			NOT NULL,
		  `customer_id`				bigint(20) unsigned NOT NULL,
  		  `ip`						varchar(20)			NOT NULL,
		  `weight`					int(11)				NOT NULL default 0,
		  `shipping_method`			varchar(100)		NOT NULL,
		  `status`					varchar(50)			NOT NULL,
		  `order_currency_code`		char(3)				NOT NULL,
		  `shipping_amount`			decimal(13, 2)		NOT NULL default 0,
		  `discount_amount`			decimal(13, 2)		NOT NULL default 0,
		  `payment_name`			varchar(255)		NOT NULL,
  		  `payment_method`			varchar(100)		NOT NULL default \'\',
		  `payment_amount`			decimal(13, 2)		NOT NULL default 0,
		  `transaction_id`			varchar(250)		NOT NULL default \'\',
		  `comment`					varchar(250)		NOT NULL,
		  `comment_internal`		varchar(250)		NOT NULL default \'\',
		  `code_tracking`			varchar(50)			NOT NULL,
		  `shipping_firstname`		varchar(50)			NOT NULL,
		  `shipping_lastname`		varchar(100)		NOT NULL,
		  `shipping_company`		varchar(50)			NOT NULL,
		  `shipping_street`			varchar(100)		NOT NULL,
		  `shipping_city`			varchar(100)		NOT NULL,
		  `shipping_city_id`		char(4)				NOT NULL DEFAULT \'\',
		  `shipping_region`			varchar(100)		NOT NULL,
		  `shipping_region_id`		char(2)				NOT NULL DEFAULT \'\',
		  `shipping_postcode`		char(10)			NOT NULL,
 		  `shipping_country`		varchar(50)			NOT NULL,
		  `shipping_country_id`		char(2)				NOT NULL,
		  `shipping_telephone_1`	varchar(50)			NOT NULL,
		  `shipping_telephone_2`	varchar(50)			NOT NULL,
		  `shipping_fax`			varchar(50)			NOT NULL,
  		  `shipping_email`			varchar(255)		NOT NULL,
		  `billing_firstname`		varchar(50)			NOT NULL,
		  `billing_lastname`		varchar(100)		NOT NULL,
		  `billing_company`			varchar(50)			NOT NULL,
		  `billing_tax_id_number`	varchar(15)			NOT NULL,
		  `billing_street`			varchar(100)		NOT NULL,
		  `billing_city`			varchar(100)		NOT NULL default 0,
		  `billing_city_id`			char(4)				NOT NULL DEFAULT \'\',
		  `billing_region`			varchar(100)		NOT NULL,
		  `billing_region_id`		char(2)				NOT NULL DEFAULT \'\',
		  `billing_postcode`		char(10)			NOT NULL,
 		  `billing_country`			varchar(50)			NOT NULL,
		  `billing_country_id`		char(2)				NOT NULL,
		  `billing_telephone_1`		varchar(50)			NOT NULL,
		  `billing_telephone_2`		varchar(50)			NOT NULL,
		  `billing_fax`				varchar(50)			NOT NULL,
  		  `billing_email`			varchar(255)		NOT NULL,
		  PRIMARY KEY  (`order_id`)
		) ENGINE=MyISAM DEFAULT CHARSET=utf8;';
		$wpdb->query( $sql );
	}

	static function get( $order_id ) {
		global $wpdb;
		return $wpdb->get_row( $wpdb->prepare( 'select * from ' . $wpdb->prefix . 'tcp_orders where order_id = %d', $order_id ) );
	}

	static function getTotal( $order_id ) {
		$order = Orders::get( $order_id );
		if ( $order ) {
			$total = OrdersCosts::getTotalCost( $order_id, -$order->discount_amount );
			return OrdersDetails::getTotal( $order_id, $total );
		} else {
			return 0;
		}
	}

	static function delete( $order_id ) {
		global $wpdb;
		return $wpdb->query( $wpdb->prepare( 'delete from ' . $wpdb->prefix . 'tcp_orders where order_id = %d' , $order_id ) );
	}

	static function is_owner( $order_id, $customer_id ) {
		global $wpdb;
		$count = $wpdb->get_var( $wpdb->prepare( 'select count(*) from ' . $wpdb->prefix . 'tcp_orders where order_id = %d and customer_id = %d', $order_id, $customer_id ) );
		return $count == 1;
	}

	/**
	 * Inserts an order.
	 *
	 * @param $order is an array with all the values of the table orders.
	 */
	static function insert( $order ) {
		global $wpdb;
		$wpdb->insert($wpdb->prefix . 'tcp_orders', array(
			'created_at'			=> $order['created_at'],
			'customer_id'			=> $order['customer_id'],
			'ip'					=> $order['ip'],
			'weight'				=> $order['weight'],
			'shipping_method'		=> $order['shipping_method'],
			'status'				=> $order['status'],
			'order_currency_code'	=> $order['order_currency_code'],
			'shipping_amount'		=> $order['shipping_amount'],
			'discount_amount'		=> $order['discount_amount'],
			'payment_name'			=> $order['payment_name'],
			'payment_method'		=> $order['payment_method'],
			'payment_amount'		=> $order['payment_amount'],
			'transaction_id'		=> $order['transaction_id'],
			'comment'				=> $order['comment'],
			'comment_internal'		=> $order['comment_internal'],
			'code_tracking'			=> $order['code_tracking'],
			'shipping_firstname'	=> $order['shipping_firstname'],
			'shipping_lastname'		=> $order['shipping_lastname'],
			'shipping_company'		=> $order['shipping_company'],
			'shipping_street'		=> $order['shipping_street'],
			'shipping_city'			=> $order['shipping_city'],
			'shipping_city_id'		=> $order['shipping_city_id'],
			'shipping_region'		=> $order['shipping_region'],
			'shipping_region_id'	=> $order['shipping_region_id'],
			'shipping_postcode'		=> Orders::removeSpaces( $order['shipping_postcode'] ),
			'shipping_country'		=> $order['shipping_country'],
			'shipping_country_id'	=> $order['shipping_country_id'],
			'shipping_telephone_1'	=> $order['shipping_telephone_1'],
			'shipping_telephone_2'	=> $order['shipping_telephone_2'],
			'shipping_fax'			=> $order['shipping_fax'],
			'shipping_email'		=> $order['shipping_email'],
			'billing_firstname'		=> $order['billing_firstname'],
			'billing_lastname'		=> $order['billing_lastname'],
			'billing_company'		=> $order['billing_company'],
			'billing_tax_id_number'	=> $order['billing_tax_id_number'],
			'billing_street'		=> $order['billing_street'],
			'billing_city'			=> $order['billing_city'],
			'billing_city_id'		=> $order['billing_city_id'],
			'billing_region'		=> $order['billing_region'],
			'billing_region_id'		=> $order['billing_region_id'],
			'billing_postcode'		=> Orders::removeSpaces( $order['billing_postcode'] ),
			'billing_country'		=> $order['billing_country'],
			'billing_country_id'	=> $order['billing_country_id'],
			'billing_telephone_1'	=> $order['billing_telephone_1'],
			'billing_telephone_2'	=> $order['billing_telephone_2'],
			'billing_fax'			=> $order['billing_fax'],
			'billing_email'			=> $order['billing_email'],
		), array('%s', '%d', '%s', '%d', '%s', '%s', '%s', '%f', '%f', '%s', '%s', '%f', '%s', '%s',
				 '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
				 '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s',
				 '%s', '%s', '%s', '%s', '%s' )
		);
		return $wpdb->insert_id;
	}

	static function getCountOrdersByStatus( $status = 'PENDING', $customer_id = -1 ) {
		global $wpdb;
		$sql = 'select count(*) from ' . $wpdb->prefix . 'tcp_orders where 1=1';
		if ( $status != '' ) $sql .= ' and status=%s';
		if ( ! empty( $customer_id ) && ( $customer_id > -1 ) ) $sql .= ' and customer_id = %d';
		$sql = $wpdb->prepare( $sql, $status, $customer_id );
		$sql = apply_filters( 'get_count_orders_by_status_sql', $sql, $status , $customer_id );
		return $wpdb->get_var( $sql );
	}

	/**
	 * Returns a join between orders and details orders
	 */
	static function getOrders( $status = 'PENDING', $customer_id = -1 ) {
		global $wpdb;
		$sql = 'select o.order_id, od.order_detail_id, shipping_firstname,
				shipping_lastname, created_at, customer_id, status, post_id, price, tax,
				qty_ordered, shipping_method, shipping_amount, discount_amount, payment_name,
				payment_method, payment_amount, transaction_id, order_currency_code,
				code_tracking, is_downloadable, max_downloads, expires_at, billing_email
				from ' . $wpdb->prefix . 'tcp_orders o left join ' .
				$wpdb->prefix . 'tcp_orders_details od on o.order_id = od.order_id where 1=1';
		if ( strlen( $status ) > 0 ) $sql .= $wpdb->prepare( ' and status = %s', $status );
		if ( ! empty( $customer_id ) && ( $customer_id > -1 ) ) $wpdb->prepare( ' and customer_id = %d', $customer_id );
		$sql .= ' order by created_at desc';
		return $wpdb->get_results( $sql );
	}

	static function getOrdersEx( $paged, $per_page = 20, $status = 'PENDING', $customer_id = -1 ) {
		global $wpdb;
		$sql = 'select order_id, shipping_firstname, shipping_lastname, created_at, customer_id,
				status, shipping_method, shipping_amount, discount_amount, payment_name,
				payment_method, payment_amount, transaction_id, order_currency_code,
				code_tracking, billing_email
				from ' . $wpdb->prefix . 'tcp_orders where 1=1';
		if ( strlen( $status ) > 0 ) $sql .= $wpdb->prepare( ' and status = %s', $status );
		if ( $customer_id > -1 ) $sql .= $wpdb->prepare( ' and customer_id = %d', $customer_id );
		$sql .= ' order by created_at desc' . $wpdb->prepare( ' limit %d, %d', ($paged-1) * $per_page, $per_page );
		$sql = apply_filters( 'get_orders_ex_sql', $sql, $paged, $per_page, $status, $customer_id );
		return $wpdb->get_results( $sql );
	}

	static function getOrderByTransactionId( $payment_method, $transaction_id ) {
		global $wpdb;
		$sql = 'select * from ' . $wpdb->prefix . 'tcp_orders where payment_method = %s and transaction_id = %s';
		return $wpdb->get_row( $wpdb->prepare( $sql, $payment_method, $transaction_id ) );
	}

	static function quickEdit( $order_id, $new_status, $new_code_tracking ) {
		global $wpdb;
		$wpdb->update( $wpdb->prefix . 'tcp_orders',
			array(
				'status'		=> $new_status,
				'code_tracking'	=> $new_code_tracking,
			),
			array(
				'order_id'		=> $order_id,
			), 
			array( '%s', '%s', '%s' ), array( '%d' ) );
		Orders::edit_downloadable_details( $order_id, $new_status );
	}

	static function edit( $order_id, $new_status, $new_code_tracking, $comment, $internal_comment ) {
		global $wpdb;
		$wpdb->update( $wpdb->prefix . 'tcp_orders',
			array(
				'status'			=> $new_status,
				'code_tracking'		=> $new_code_tracking,
				'comment'			=> $comment,
				'comment_internal'	=> $internal_comment,
			),
			array(
				'order_id'		=> $order_id,
			), 
			array( '%s', '%s', '%s', '%s', ), array( '%d' ) );
		Orders::edit_downloadable_details( $order_id, $new_status );
	}

	static function editStatus( $order_id, $new_status, $transaction_id = '', $internal_comment = '' ) {
		global $wpdb;
		$wpdb->update( $wpdb->prefix . 'tcp_orders',
			array(
				'status'			=> $new_status,
				'transaction_id'	=> $transaction_id,
				'comment_internal'	=> $internal_comment,
			),
			array(
				'order_id'		=> $order_id,
			), 
			array( '%s', '%s', '%s' ), array( '%d' ) );
		Orders::edit_downloadable_details( $order_id, $new_status );
	}

	static function getStatus( $order_id ) {
		global $wpdb;
		$sql = "select status from {$wpdb->prefix}tcp_orders where order_id = %d";
		return $wpdb->get_var( $wpdb->prepare( $sql, $order_id ) );
	}

	static function edit_downloadable_details( $order_id, $new_status ) {
		$completed = tcp_get_completed_order_status();
		if ( $new_status == $completed ) {
			$details = OrdersDetails::getDetails( $order_id );
			foreach( $details as $detail ) {
				$days_to_expire = get_post_meta( $detail->post_id, 'tcp_days_to_expire', true );
				if ( $days_to_expire > 0 ) {
					$today = date( 'Y-m-d' );
					$expires_at = date ( 'Y-m-d', strtotime( date( 'Y-m-d', strtotime( $today ) ) . " +$days_to_expire day" ) );
					OrdersDetails::edit_downloadable_data( $detail->order_detail_id, $expires_at );
				}
			}
		}
	}

	static function isDownloadable( $order_id ) {
		return OrdersDetails::isDownloadable( $order_id );
	}

	static function getProductsDownloadables( $customer_id ) {
		global $wpdb;
		$completed = tcp_get_completed_order_status();
		$today = date ( 'Y-m-d' );
		$max_date = date ( 'Y-m-d', mktime( 0, 0, 0, 1, 1, 2000 ) );
		$sql = $wpdb->prepare( 'select o.order_id as order_id, order_detail_id, post_id, expires_at, max_downloads from 
			' . $wpdb->prefix . 'tcp_orders o left join ' . $wpdb->prefix . 'tcp_orders_details d 
			on o.order_id = d.order_id
			where customer_id = %d and d.is_downloadable = \'Y\' and status=%s
			and ( ( d.expires_at > %s and ( d.max_downloads = -1 or d.max_downloads > 0 ) )
				or ( d.expires_at = %s and ( d.max_downloads > 0 or d.max_downloads = -1 ) ) )'
			, $customer_id, $completed, $today, $max_date );
		return $wpdb->get_results( $sql );
	}

	static function isProductDownloadable( $customer_id, $orders_details_id ) {
		global $wpdb;
		$completed = tcp_get_completed_order_status();
		$today = date ( 'Y-m-d' );
		$max_date = date ( 'Y-m-d', mktime( 0, 0, 0, 1, 1, 2000 ) );
		$sql = $wpdb->prepare( 'select count(*) from
			' . $wpdb->prefix . 'tcp_orders o left join ' . $wpdb->prefix . 'tcp_orders_details d
			on o.order_id = d.order_id
			where customer_id = %d and order_detail_id = %d and d.is_downloadable = \'Y \' and status=%s
			and ( ( d.expires_at > %s and ( d.max_downloads = -1 or d.max_downloads > 0 ) )
				or ( d.expires_at = %s and ( d.max_downloads > 0 or d.max_downloads = -1 ) ) )'
			, $customer_id, $orders_details_id, $completed, $today, $max_date );
		$count = $wpdb->get_var( $sql );
		return $count > 0;
	}

	static function takeAwayDownload( $order_detail_id ) {
		global $wpdb;

		$sql = 'update ' . $wpdb->prefix . 'tcp_orders_details set 
			max_downloads = max_downloads - 1 where order_detail_id = %d and max_downloads > 0';
		$wpdb->query( $wpdb->prepare( $sql, $order_detail_id ) );
	}

	static function removeSpaces( $postcode ) {
		return str_replace( ' ', '', $postcode );
	}

	/**
	 * Returns the lastest orders
	 */
	static function getLastOrders( $limit = 5, $status = '', $customer_id = -1 ) {
		global $wpdb;
		$sql = 'select order_id, billing_firstname,
				billing_lastname, created_at, customer_id, status, billing_email, billing_country
				from ' . $wpdb->prefix . 'tcp_orders where 1=1 ';
		if ( strlen( $status ) > 0 ) $sql .= $wpdb->prepare( ' and status = %s', $status );
		if ( $customer_id > -1 ) $sql .= $wpdb->prepare( ' and customer_id = %d', $customer_id );		
		$sql .= $wpdb->prepare(' order by created_at desc limit 0, %d', $limit );
		return $wpdb->get_results( $sql );
	}

	/**
	 * @author: Joy Reynolds and TheCartPress team
	 */
	static function getCounts( $status = '', $days_prev = 7, $customer_id = -1 ) {
		global $wpdb;

		$sql = 'SELECT DATE(created_at) AS thedate, SUM(payment_amount) AS sales, SUM(1) AS count FROM ' . $wpdb->prefix . 'tcp_orders';
		$sql .= ' WHERE DATE_SUB( NOW(), INTERVAL %d DAY)  <= created_at';
		if ( $status != '' ) $sql .= $wpdb->prepare( ' AND status = %s', $status );
		if ( $customer_id > -1 ) $sql .= $wpdb->prepare( ' AND customer_id = %d', $customer_id );
		$sql .= ' GROUP BY DATE(created_at)';
		$sql = $wpdb->prepare( $sql, $days_prev );
		$sql = apply_filters( 'get_counts_sql', $sql, $status, $days_prev, $customer_id );
		return $wpdb->get_results( $sql );
	}

	static function getAmountByDay( $date, $status = '' ) {
		global $wpdb;

		$tomorrow = date( 'Y-m-d', strtotime( $date . ' +1 day' ) );
		$sql = $wpdb->prepare( 'select order_id from ' . $wpdb->prefix . 'tcp_orders where created_at > %s and created_at < %s', $date, $tomorrow );
		if ( $status != '' ) $sql .= $wpdb->prepare( ' AND status = %s', $status );
		$orders = $wpdb->get_results( $sql );
		$amount = 0;
		foreach( $orders as $order ) {
			$amount += Orders::getTotal( $order->order_id );
		}
		return $amount;
	}

	static function getAmountByMonth( $first_day, $status = '' ) {
		global $wpdb;

		$last_day = strtotime( '+1 month', strtotime( $first_day ) );
		$last_day = date( 'Y-m-d', strtotime( '-1 day', $last_day ) );
		$sql = $wpdb->prepare( 'select order_id from ' . $wpdb->prefix . 'tcp_orders where created_at > %s and created_at < %s', $first_day, $last_day );
		if ( $status != '' ) $sql .= $wpdb->prepare( ' AND status = %s', $status );
		$orders = $wpdb->get_results( $sql );
		$amount = 0;
		foreach( $orders as $order ) {
			$amount += Orders::getTotal( $order->order_id );
		}
		return array( $amount, count( $orders ) );
	}
}
?>
Return current item: TheCartPress