<?php 
class ModelSaleOrder extends Model {
	public function addOrder($data) {	
		$invoice_no = '';
		$total = '';
				
		$this->load->model('setting/store');
		
		$store_info = $this->model_setting_store->getStore($data['store_id']);
		
		if ($store_info) {
			$store_name = $store_info['name'];
			$store_url = $store_info['url'];
		} else {
			$store_name = $this->config->get('config_name');
			$store_url = HTTP_CATALOG;			
		}
		
		$this->load->model('sale/customer');
		
		$customer_info = $this->model_sale_customer->getCustomer($data['customer_id']);
		
		if ($customer_info) {
			$customer_group_id = $customer_info['customer_group_id'];
		} elseif ($store_info) {
			$customer_group_id = $store_info['customer_group_id'];
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');		
		}
				
		$this->load->model('localisation/country');
		
		$country_info = $this->model_localisation_country->getCountry($data['shipping_country_id']);
		
		if ($country_info) {
			$shipping_country = $country_info['name'];
			$shipping_address_format = $country_info['address_format'];
		} else {
			$shipping_country = '';	
			$shipping_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';
		}	
		
		$this->load->model('localisation/zone');
		
		$zone_info = $this->model_localisation_zone->getZone($data['shipping_zone_id']);
		
		if ($zone_info) {
			$shipping_zone = $zone_info['name'];
		} else {
			$shipping_zone = '';			
		}	
					
		$country_info = $this->model_localisation_country->getCountry($data['payment_country_id']);
		
		if ($country_info) {
			$payment_country = $country_info['name'];
			$payment_address_format = $country_info['address_format'];			
		} else {
			$payment_country = '';	
			$payment_address_format = '{firstname} {lastname}' . "\n" . '{company}' . "\n" . '{address_1}' . "\n" . '{address_2}' . "\n" . '{city} {postcode}' . "\n" . '{zone}' . "\n" . '{country}';					
		}
	
		$zone_info = $this->model_localisation_zone->getZone($data['payment_zone_id']);
		
		if ($zone_info) {
			$payment_zone = $zone_info['name'];
		} else {
			$payment_zone = '';			
		}	

		$this->load->model('localisation/currency');

		$currency_info = $this->model_localisation_currency->getCurrencyByCode($this->config->get('config_currency'));
		
		if ($currency_info) {
			$currency_id = $currency_info['currency_id'];
			$currency_code = $this->config->get('config_currency');
			$currency_value = $currency_info['value'];
		} else {
			$currency_id = 0;
			$currency_code = $currency_info['code'];
			$currency_value = 1.00000;			
		}
		
      	$this->db->query("INSERT INTO " . DB_PREFIX . "order SET invoice_no = '" . (int)$invoice_no . "', invoice_prefix = '" . $this->db->escape($this->config->get('config_invoice_prefix')) . "', store_id = '" . (int)$data['store_id'] . "', store_name = '" . $this->db->escape($store_name) . "', store_url = '" . $this->db->escape($store_url) . "', customer_id = '" . (int)$data['customer_id'] . "', customer_group_id = '" . (int)$customer_group_id . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', shipping_firstname = '" . $this->db->escape($data['shipping_firstname']) . "', shipping_lastname = '" . $this->db->escape($data['shipping_lastname']) . "',  shipping_company = '" . $this->db->escape($data['shipping_company']) . "', shipping_address_1 = '" . $this->db->escape($data['shipping_address_1']) . "', shipping_address_2 = '" . $this->db->escape($data['shipping_address_2']) . "', shipping_city = '" . $this->db->escape($data['shipping_city']) . "', shipping_postcode = '" . $this->db->escape($data['shipping_postcode']) . "', shipping_country = '" . $this->db->escape($shipping_country) . "', shipping_country_id = '" . (int)$data['shipping_country_id'] . "', shipping_zone = '" . $this->db->escape($shipping_zone) . "', shipping_zone_id = '" . (int)$data['shipping_zone_id'] . "', shipping_address_format = '" . $this->db->escape($shipping_address_format) . "', shipping_method = '" . $this->db->escape($data['shipping_method']) . "', payment_firstname = '" . $this->db->escape($data['payment_firstname']) . "', payment_lastname = '" . $this->db->escape($data['payment_lastname']) . "', payment_company = '" . $this->db->escape($data['payment_company']) . "', payment_address_1 = '" . $this->db->escape($data['payment_address_1']) . "', payment_address_2 = '" . $this->db->escape($data['payment_address_2']) . "', payment_city = '" . $this->db->escape($data['payment_city']) . "', payment_postcode = '" . $this->db->escape($data['payment_postcode']) . "', payment_country = '" . $this->db->escape($payment_country) . "', payment_country_id = '" . (int)$data['payment_country_id'] . "', payment_zone = '" . $this->db->escape($payment_zone) . "', payment_zone_id = '" . (int)$data['payment_zone_id'] . "', payment_address_format = '" . $this->db->escape($payment_address_format) . "', payment_method = '" . $this->db->escape($data['payment_method']) . "', comment = '" . $this->db->escape($data['comment']) . "', total = '" . (float)$total . "', order_status_id = '" . (int)$data['order_status_id'] . "', affiliate_id  = '" . (int)$data['affiliate_id'] . "', language_id = '" . (int)$data['affiliate_id'] . "', currency_id = '" . $this->db->escape($this->config->get('config_currency')) . "', currency_code = '" . $this->db->escape($currency_code) . "', currency_value = '" . (float)$currency_value . "', date_added = NOW(), date_modified = NOW()");
      	
      	$order_id = $this->db->getLastId();
      	
      	if (isset($data['order_product'])) {		
      		foreach ($data['order_product'] as $order_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "'");
			
				$order_product_id = $this->db->getLastId();
	
				foreach ($order_product['order_option'] as $order_option) {
					if ($option['type'] != 'checkbox') {
						$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$option['product_option_id'] . "', product_option_value_id = '" . (int)$option['product_option_value_id'] . "', name = '" . $this->db->escape($option['name']) . "', `value` = '" . $this->db->escape($option['value']) . "', `type` = '" . $this->db->escape($option['type']) . "'");
					} else {
						foreach ($option['option_value'] as $option_value) {
							$this->db->query("INSERT INTO " . DB_PREFIX . "order_option SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', product_option_id = '" . (int)$option['product_option_id'] . "', product_option_value_id = '" . (int)$option['product_option_value_id'] . "', name = '" . $this->db->escape($option['name']) . "', `value` = '" . $this->db->escape($option['value']) . "', `type` = '" . $this->db->escape($option['type']) . "'");
						}
					}
				}
				
				foreach ($product['download'] as $download) {
					$this->db->query("INSERT INTO " . DB_PREFIX . "order_download SET order_id = '" . (int)$order_id . "', order_product_id = '" . (int)$order_product_id . "', name = '" . $this->db->escape($download['name']) . "', filename = '" . $this->db->escape($download['filename']) . "', mask = '" . $this->db->escape($download['mask']) . "', remaining = '" . (int)($download['remaining'] * $product['quantity']) . "'");
				}								
			}
		}
			
      	if (isset($data['order_total'])) {		
      		foreach ($data['order_total'] as $order_total) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_total SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$return_product['product_id'] . "', name = '" . $this->db->escape($return_product['name']) . "', model = '" . $this->db->escape($return_product['model']) . "', quantity = '" . (int)$return_product['quantity'] . "', manufacturer = '" . (int)$return_product['manufacturer'] . "', return_reason_id = '" . (int)$return_product['return_reason_id'] . "', opened = '" . (int)$return_product['opened'] . "', comment = '" . $this->db->escape($return_product['comment']) . "', return_action_id = '" . (int)$return_product['return_action_id'] . "'");
			}
		}		
	}

	
	public function editOrderStockist($order_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "order_stockist SET shipped = '" . $data['shipped'] . "', received = '" . $data['received'] . "' WHERE order_id = '" . (int)$order_id . "'");		
	}
	
	public function editOrder($order_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "return SET order_id = '" . (int)$data['order_id'] . "', customer_id = '" . (int)$data['customer_id'] . "', invoice_no = '" . $this->db->escape($data['invoice_no']) . "', invoice_date = '" . $this->db->escape($data['invoice_date']) . "', firstname = '" . $this->db->escape($data['firstname']) . "', lastname = '" . $this->db->escape($data['lastname']) . "', email = '" . $this->db->escape($data['email']) . "', telephone = '" . $this->db->escape($data['telephone']) . "', fax = '" . $this->db->escape($data['fax']) . "', company = '" . $this->db->escape($data['company']) . "', address_1 = '" . $this->db->escape($data['address_1']) . "', address_2 = '" . $this->db->escape($data['address_2']) . "', city = '" . $this->db->escape($data['city']) . "', postcode = '" . $this->db->escape($data['postcode']) . "', country_id = '" . (int)$data['country_id'] . "', zone_id = '" . (int)$data['zone_id'] . "', return_status_id = '" . (int)$data['return_status_id'] . "', comment = '" . $this->db->escape($data['comment']) . "', date_modified = NOW() WHERE return_id = '" . (int)$return_id . "'");
		
		$this->db->query("DELETE FROM " . DB_PREFIX . "return_product WHERE return_id = '" . (int)$return_id . "'");
      	
		if (isset($data['return_product'])) {		
      		foreach ($data['return_product'] as $return_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "return_product SET return_id = '" . (int)$return_id . "', product_id = '" . (int)$return_product['product_id'] . "', name = '" . $this->db->escape($return_product['name']) . "', model = '" . $this->db->escape($return_product['model']) . "', quantity = '" . (int)$return_product['quantity'] . "', return_reason_id = '" . (int)$return_product['return_reason_id'] . "', opened = '" . (int)$return_product['opened'] . "', comment = '" . $this->db->escape($return_product['comment']) . "', return_action_id = '" . (int)$return_product['return_action_id'] . "'");
			}
		} 
	}
	
	public function deleteOrder($order_id) {
		if ($this->config->get('config_stock_subtract')) {
			$order_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND order_id = '" . (int)$order_id . "'");

			if ($order_query->num_rows) {
				$product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");

				foreach($product_query->rows as $product) {
					$this->db->query("UPDATE `" . DB_PREFIX . "product` SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_id = '" . (int)$product['product_id'] . "'");

					$option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$product['order_product_id'] . "'");

					foreach ($option_query->rows as $option) {
						$this->db->query("UPDATE " . DB_PREFIX . "product_option_value SET quantity = (quantity + " . (int)$product['quantity'] . ") WHERE product_option_value_id = '" . (int)$option['product_option_value_id'] . "' AND subtract = '1'");
					}
				}
			}
		}

		$this->db->query("DELETE FROM `" . DB_PREFIX . "order` WHERE order_id = '" . (int)$order_id . "'");
      	$this->db->query("DELETE FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'");
      	$this->db->query("DELETE FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
      	$this->db->query("DELETE FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "'");
	  	$this->db->query("DELETE FROM " . DB_PREFIX . "order_download WHERE order_id = '" . (int)$order_id . "'");
      	$this->db->query("DELETE FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer_transaction WHERE order_id = '" . (int)$order_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "customer_reward WHERE order_id = '" . (int)$order_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "affiliate_transaction WHERE order_id = '" . (int)$order_id . "'");
		$this->db->query("DELETE FROM " . DB_PREFIX . "coupon_history WHERE order_id = '" . (int)$order_id . "'");
	}

	public function getOrder($order_id) {
		$order_query = $this->db->query("SELECT *, (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM " . DB_PREFIX . "customer c WHERE c.customer_id = o.customer_id) AS customer FROM `" . DB_PREFIX . "order` o WHERE o.order_id = '" . (int)$order_id . "'");

		if ($order_query->num_rows) {
			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['shipping_country_id'] . "'");

			if ($country_query->num_rows) {
				$shipping_iso_code_2 = $country_query->row['iso_code_2'];
				$shipping_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$shipping_iso_code_2 = '';
				$shipping_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['shipping_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$shipping_zone_code = $zone_query->row['code'];
			} else {
				$shipping_zone_code = '';
			}

			$country_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "country` WHERE country_id = '" . (int)$order_query->row['payment_country_id'] . "'");

			if ($country_query->num_rows) {
				$payment_iso_code_2 = $country_query->row['iso_code_2'];
				$payment_iso_code_3 = $country_query->row['iso_code_3'];
			} else {
				$payment_iso_code_2 = '';
				$payment_iso_code_3 = '';
			}

			$zone_query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "zone` WHERE zone_id = '" . (int)$order_query->row['payment_zone_id'] . "'");

			if ($zone_query->num_rows) {
				$payment_zone_code = $zone_query->row['code'];
			} else {
				$payment_zone_code = '';
			}

			if ($order_query->row['affiliate_id']) {
				$affiliate_id = $order_query->row['affiliate_id'];
			} else {
				$affiliate_id = 0;
			}				
				
			$this->load->model('sale/affiliate');
				
			$affiliate_info = $this->model_sale_affiliate->getAffiliate($affiliate_id);
				
			if ($affiliate_info) {
				$affiliate_firstname = $affiliate_info['firstname'];
				$affiliate_lastname = $affiliate_info['lastname'];
			} else {
				$affiliate_firstname = '';
				$affiliate_lastname = '';				
			}

			$this->load->model('localisation/language');
			
			$language_info = $this->model_localisation_language->getLanguage($order_query->row['language_id']);
			
			if ($language_info) {
				$language_code = $language_info['code'];
				$language_filename = $language_info['filename'];
				$language_directory = $language_info['directory'];
			} else {
				$language_code = '';
				$language_filename = '';
				$language_directory = '';
			}
			
			return array(
				'order_id'                => $order_query->row['order_id'],
				'invoice_no'              => $order_query->row['invoice_no'],
				'invoice_prefix'          => $order_query->row['invoice_prefix'],
				'store_id'                => $order_query->row['store_id'],
				'store_name'              => $order_query->row['store_name'],
				'store_url'               => $order_query->row['store_url'],
				'customer_id'             => $order_query->row['customer_id'],
				'customer'                => $order_query->row['customer'],
				'customer_group_id'       => $order_query->row['customer_group_id'],
				'firstname'               => $order_query->row['firstname'],
				'lastname'                => $order_query->row['lastname'],
				'telephone'               => $order_query->row['telephone'],
				'fax'                     => $order_query->row['fax'],
				'email'                   => $order_query->row['email'],
				'shipping_firstname'      => $order_query->row['shipping_firstname'],
				'shipping_lastname'       => $order_query->row['shipping_lastname'],
				'shipping_company'        => $order_query->row['shipping_company'],
				'shipping_address_1'      => $order_query->row['shipping_address_1'],
				'shipping_address_2'      => $order_query->row['shipping_address_2'],
				'shipping_postcode'       => $order_query->row['shipping_postcode'],
				'shipping_city'           => $order_query->row['shipping_city'],
				'shipping_zone_id'        => $order_query->row['shipping_zone_id'],
				'shipping_zone'           => $order_query->row['shipping_zone'],
				'shipping_zone_code'      => $shipping_zone_code,
				'shipping_country_id'     => $order_query->row['shipping_country_id'],
				'shipping_country'        => $order_query->row['shipping_country'],
				'shipping_iso_code_2'     => $shipping_iso_code_2,
				'shipping_iso_code_3'     => $shipping_iso_code_3,
				'shipping_address_format' => $order_query->row['shipping_address_format'],
				'shipping_method'         => $order_query->row['shipping_method'],
				'payment_firstname'       => $order_query->row['payment_firstname'],
				'payment_lastname'        => $order_query->row['payment_lastname'],
				'payment_company'         => $order_query->row['payment_company'],
				'payment_address_1'       => $order_query->row['payment_address_1'],
				'payment_address_2'       => $order_query->row['payment_address_2'],
				'payment_postcode'        => $order_query->row['payment_postcode'],
				'payment_city'            => $order_query->row['payment_city'],
				'payment_zone_id'         => $order_query->row['payment_zone_id'],
				'payment_zone'            => $order_query->row['payment_zone'],
				'payment_zone_code'       => $payment_zone_code,
				'payment_country_id'      => $order_query->row['payment_country_id'],
				'payment_country'         => $order_query->row['payment_country'],
				'payment_iso_code_2'      => $payment_iso_code_2,
				'payment_iso_code_3'      => $payment_iso_code_3,
				'payment_address_format'  => $order_query->row['payment_address_format'],
				'payment_method'          => $order_query->row['payment_method'],
				'comment'                 => $order_query->row['comment'],
				'total'                   => $order_query->row['total'],
				'reward'                  => $order_query->row['reward'],
				'order_status_id'         => $order_query->row['order_status_id'],
				'affiliate_id'            => $order_query->row['affiliate_id'],
				'affiliate_firstname'     => $affiliate_firstname,
				'affiliate_lastname'      => $affiliate_lastname,
				'commission'              => $order_query->row['commission'],
				'language_id'             => $order_query->row['language_id'],
				'language_code'           => $language_code,
				'language_filename'       => $language_filename,
				'language_directory'      => $language_directory,				
				'currency_id'             => $order_query->row['currency_id'],
				'currency_code'           => $order_query->row['currency_code'],
				'currency_value'          => $order_query->row['currency_value'],
				'date_added'              => $order_query->row['date_added'],
				'date_modified'           => $order_query->row['date_modified'],
				'ip'                      => $order_query->row['ip']
			);
		} else {
			return false;
		}
	}

	public function getOrders($data = array()) {
		$sql = "SELECT o.order_id, CONCAT(o.firstname, ' ', o.lastname) AS customer, (SELECT os.name FROM " . DB_PREFIX . "order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '" . (int)$this->config->get('config_language_id') . "') AS status, o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified FROM `" . DB_PREFIX . "order` o";

		if (isset($data['filter_order_status_id']) && !is_null($data['filter_order_status_id'])) {
			$sql .= " WHERE o.order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " WHERE o.order_status_id > '0'";
		}

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND LCASE(CONCAT(o.firstname, ' ', o.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}

		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		$sort_data = array(
			'o.order_id',
			'customer',
			'status',
			'o.date_added',
			'o.date_modified',
			'o.total',
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY o.order_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
	

	public function getOrderStockist($order_id) {		
		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received FROM `" . DB_PREFIX . "order_stockist` o , `" . DB_PREFIX . "customer` a where o.customer_id = a.customer_id and o.order_id = " . $order_id;

		$query = $this->db->query($sql);

		return $query->row;
	
	}
	public function getGemPurchaseList() {		
//		$query = $this->db->query("SELECT  * from custom_gem_purchases order by transaction_date desc");
		$query = $this->db->query("SELECT  * from custom_gem_purchases where order_id = 0 	order by transaction_date desc");

		return $query->rows;
	}
	
	public function getMetalPurchaseList() {		
//		$sql = "SELECT  * from custom_metal_purchases order by transaction_date desc";
		$sql = "SELECT  * from custom_metal_purchases where order_id = 0 order by transaction_date desc";

		$query = $this->db->query($sql);

		return $query->rows;
	}
	
	public function getOtherCostCodes() {		
		$sql = "SELECT * from custom_other_cost_desc order by Code ";

		$query = $this->db->query($sql);

		return $query->rows;
	} 

	public function getShapes() {		
		

		$query = $this->db->query("SELECT * from shape order by shape");

		return $query->rows;
	}
	public function getCurrencies() {		
		

		$query = $this->db->query("SELECT * from currency");

		return $query->rows;
	}


	public function getStockistDefaultPrice($data = array()) {
		$query = $this->db->query("SELECT * from custom_product_costing_defaults cmp");		
		return $query->rows;
	}
	
	
	public function getGemCostingAvailability($data = array()) {
		$query1 = $this->db->query("update `custom_gem_purchases` cgp set size = (select size_code from size where size_id = cgp.size_id) ");		
		$query2 = $this->db->query("update order_stockist set G1CostingError  = '' , G2CostingError  = '' , G3CostingError  = '' , G4CostingError  = '' , G5CostingError  = '' , M1CostingError = '' , M2CostingError = '' , M1CostingError = '' where  CostingComplete != 1 AND ProdComplete = 1 ");
//		$sql = "SELECT * FROM ( select Gem,shape_id as GemShapeID,size_id as GemSizeID,sum(Count) as GemCount ,sum(GemWeight) as GemWeight from ( SELECT order_id as order_id,G1 as Gem,G1ShapeId as shape_id,G1SizeId as size_id,`G1#` as Count,G1Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union     SELECT order_id as order_id,G2 as Gem,G2ShapeId as shape_id,G2SizeId as size_id,`G2#` as Count,G2Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union     SELECT order_id as order_id,G3 as Gem,G3ShapeId as shape_id,G3SizeId as size_id,`G3#` as Count,G3Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union     SELECT order_id as order_id,G4 as Gem,G4ShapeId as shape_id,G4SizeId as size_id,`G4#` as Count,G4Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union     SELECT order_id as order_id,G5 as Gem,G5ShapeId as shape_id,G5SizeId as size_id,`G5#` as Count,G5Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 ) AS OS where Gem != 0  group by      shape_id,size_id ) AS OS left outer join custom_gem_purchases GP on GP.gemstone_id = OS.Gem and GP.shape_id = OS.GemShapeID and GP.size = OS.GemSizeID AND GP.purchase_consumption_id = (select max(cgp.purchase_consumption_id ) from custom_gem_purchases cgp where cgp.gemstone_id = GP.gemstone_id and cgp.shape_id = GP.shape_id and cgp.size = GP.size ) ";
		$sql = "SELECT * FROM ( select Gem,shape_id as GemShapeID,size_id as GemSizeID,sum(Count) as GemCount ,sum(GemWeight) as GemWeight from ( SELECT order_id as order_id,G1 as Gem,G1ShapeId as shape_id,G1SizeId as size_id,`G1#` as Count,G1Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G2 as Gem,G2ShapeId as shape_id,G2SizeId as size_id,`G2#` as Count,G2Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G3 as Gem,G3ShapeId as shape_id,G3SizeId as size_id,`G3#` as Count,G3Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G4 as Gem,G4ShapeId as shape_id,G4SizeId as size_id,`G4#` as Count,G4Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G5 as Gem,G5ShapeId as shape_id,G5SizeId as size_id,`G5#` as Count,G5Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 ) AS OS where Gem != 0  group by gem, shape_id,size_id ) AS OS left outer join custom_gem_purchases GP on GP.gemstone_id = OS.Gem and GP.shape_id = OS.GemShapeID and GP.size = OS.GemSizeID AND GP.purchase_consumption_id = (select max(cgp.purchase_consumption_id ) from custom_gem_purchases cgp where cgp.gemstone_id = GP.gemstone_id and cgp.shape_id = GP.shape_id and cgp.size = GP.size ) ";
		$query = $this->db->query($sql);

		foreach ($query->rows as $Gem) {
			$this->log->write('Gem Errors');
			$ErrorMessage = "";$ErrorMessage1 = "";$ErrorMessage2 = "";
			if($Gem['count_balance']=='' ||  $Gem['count_balance'] < $Gem['GemCount']){
				$ErrorMessage1 = 'Gem ' . $Gem['Gem'] . ' has count ' . $Gem['count_balance'] . ' which is less than ' . $Gem['GemCount'] . '.<br>' ;
				$this->log->write($ErrorMessage1 .  " at Gem  = ". $Gem['Gem'] ." AND Gem ShapeId = '". $Gem['GemShapeID']  ."' AND Gem SizeId = '". $Gem['GemSizeID'] . "'" );
			}
			if( $Gem['weight_balance']=='' ||  $Gem['weight_balance'] < $Gem['GemWeight']){
				$ErrorMessage2 = 'Gem ' . $Gem['Gem'] . ' has weight ' . $Gem['weight_balance'] . ' which is less than ' . round($Gem['GemWeight'],2) ;
				$this->log->write($ErrorMessage2 .  " at G1 = ". $Gem['Gem'] ." AND G1ShapeId = '". $Gem['GemShapeID']  ."' AND G1SizeId = '". $Gem['GemSizeID'] . "'" );
			}
			$ErrorMessage = $ErrorMessage1 . $ErrorMessage2;
			if($ErrorMessage != "" ){
				$this->db->query("UPDATE order_stockist SET G1CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND G1 = ". $Gem['Gem'] ." AND G1ShapeId = '". $Gem['GemShapeID']  ."' AND G1SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G2CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND G2 = ". $Gem['Gem'] ." AND G2ShapeId = '". $Gem['GemShapeID']  ."' AND G2SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G3CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND G3 = ". $Gem['Gem'] ." AND G3ShapeId = '". $Gem['GemShapeID']  ."' AND G3SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G4CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND G4 = ". $Gem['Gem'] ." AND G4ShapeId = '". $Gem['GemShapeID']  ."' AND G4SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G5CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND G5 = ". $Gem['Gem'] ." AND G5ShapeId = '". $Gem['GemShapeID']  ."' AND G5SizeId = '". $Gem['GemSizeID'] . "'"  );
			}else{
				$this->db->query("UPDATE order_stockist SET G1CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND G1 = ". $Gem['Gem'] ." AND G1ShapeId = '". $Gem['GemShapeID']  ."' AND G1SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G2CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND G2 = ". $Gem['Gem'] ." AND G2ShapeId = '". $Gem['GemShapeID']  ."' AND G2SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G3CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND G3 = ". $Gem['Gem'] ." AND G3ShapeId = '". $Gem['GemShapeID']  ."' AND G3SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G4CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND G4 = ". $Gem['Gem'] ." AND G4ShapeId = '". $Gem['GemShapeID']  ."' AND G4SizeId = '". $Gem['GemSizeID'] . "'"  );
				$this->db->query("UPDATE order_stockist SET G5CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND G5 = ". $Gem['Gem'] ." AND G5ShapeId = '". $Gem['GemShapeID']  ."' AND G5SizeId = '". $Gem['GemSizeID'] . "'" );
			}
		}			
		return $query->rows;
	}	
	
	public function getMetalCostingAvailability($data = array()) {
		$sql = "SELECT * FROM ( SELECT Metal,sum(ExpectedWeight) as MetalWeight from (SELECT OS.metal_id, OS.MetalWeight,M.CorresBaseMetal as Metal,  OS.MetalWeight * (M.metal_claimed_percent / 100) * (1+(M.wastage_percent/100)) as ExpectedWeight FROM  	( select metal_id,sum(MetalWeight) as MetalWeight from (	SELECT order_id as order_id,M1 as metal_id,M1Wt as MetalWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union 	SELECT order_id as order_id,M2 as metal_id,M2Wt as MetalWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1  	) AS OS where metal_id != 0  group by metal_id ) AS OS, metal M where M.metal_id = OS.metal_id ) as MPurchase group by Metal ) AS Metals left outer join custom_metal_purchases MP on MP.metal_id = Metals.Metal   	AND MP.purchase_consumption_id = (select max(cgp.purchase_consumption_id ) from custom_metal_purchases cgp where cgp.metal_id = MP.metal_id )"; 
		$query = $this->db->query($sql);
		
		foreach ($query->rows as $Metal) {
			if($Metal['weight_balance'] < $Metal['MetalWeight']){
				$ErrorMessage = 'Metal ' . $Metal['Metal'] . ' has weight ' . $Metal['weight_balance'] . ' which is less than ' . round($Metal['MetalWeight'],2) ;
				$this->log->write( $ErrorMessage );
				$this->db->query("UPDATE order_stockist SET M1CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND M1 in ( SELECT metal_id from metal where CorresBaseMetal = '". $Metal['Metal'] ."' ) ");
				$this->db->query("UPDATE order_stockist SET M2CostingError  = '" . $ErrorMessage . "' WHERE CostingComplete!=1 and ProdComplete=1 AND M2 in ( SELECT metal_id from metal where CorresBaseMetal = '". $Metal['Metal'] ."' ) ");
			}else{
				$this->db->query("UPDATE order_stockist SET M1CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND M1 in ( SELECT metal_id from metal where CorresBaseMetal = '". $Metal['Metal'] ."' ) ");
				$this->db->query("UPDATE order_stockist SET M2CostingError  = '' WHERE CostingComplete!=1 and ProdComplete=1 AND M2 in ( SELECT metal_id from metal where CorresBaseMetal = '". $Metal['Metal'] ."' ) ");
			}
		}	
		return $query->rows;
	}
	
	
	public function updateGemCostingReady() {
		$sql = "select order_id, Gem,shape_id,size_id,sum(Count) as GemCount ,sum(GemWeight) as GemWeight from ( SELECT order_id as order_id,G1 as Gem,G1ShapeId as shape_id,G1SizeId as size_id,`G1#` as Count,G1Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G2 as Gem,G2ShapeId as shape_id,G2SizeId as size_id,`G2#` as Count,G2Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G3 as Gem,G3ShapeId as shape_id,G3SizeId as size_id,`G3#` as Count,G3Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G4 as Gem,G4ShapeId as shape_id,G4SizeId as size_id,`G4#` as Count,G4Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all SELECT order_id as order_id,G5 as Gem,G5ShapeId as shape_id,G5SizeId as size_id,`G5#` as Count,G5Wt as GemWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 ) AS OS where OS.Gem != 0 and order_id in (select order_id from order_stockist OS1 where OS.order_id = OS1.order_id and G1CostingError  = '' and G2CostingError  = '' and G3CostingError  = '' and G4CostingError  = '' and G5CostingError  = '' and M1CostingError = '' and M2CostingError = '' and M1CostingError = '' and M2CostingError = '') group by order_id, Gem, shape_id,size_id"; 
		$query = $this->db->query($sql);
		foreach ($query->rows as $Gem) {
			$count = -1 * $Gem['GemCount'] ;
			$weight = -1 * $Gem['GemWeight'] ;
			$sql = "INSERT INTO custom_gem_purchases SET order_id = '" .  $Gem['order_id']  . "',gemstone_id = '" .  $Gem['Gem']  . "',size = '" .  $Gem['size_id']  . "', shape_id = '" .  $Gem['shape_id']  . "'";
			$sql .= " ,transaction_date = now() ,  currency = 'INR', supplier_name = ''";
			
			$prevWtBalance = 0;
			$prevCountBalance = 0;
			$prevAverageRate = 0;
			$GemQuery = "SELECT weight_balance,count_balance,average_rate,size_id from custom_gem_purchases cmp where cmp.gemstone_id = '" .  $Gem['Gem']  . "' and size = '" .  $Gem['size_id'] . "'  and shape_id = '" .  $Gem['shape_id'] . "' and  purchase_consumption_id = (select max(cmp1.purchase_consumption_id)  from  custom_gem_purchases cmp1 where cmp1.gemstone_id = cmp.gemstone_id and  cmp1.size_id = cmp.size_id  and  cmp1.shape_id = cmp.shape_id ) ";
			$BalanceWeightquery = $this->db->query( $GemQuery );
			$this->log->write( "Previous Row: weight_balance=" . $BalanceWeightquery->row['weight_balance'] . ' ; count_balance= ' . $BalanceWeightquery->row['count_balance'] . ' ; average_rate=' . $BalanceWeightquery->row['average_rate'] . ' from query - ' . $GemQuery );

			if($BalanceWeightquery->num_rows){
				$prevCountBalance = $BalanceWeightquery->row['count_balance'];			
				$count_balance =  $prevCountBalance  - $Gem['GemCount'];
				
				$prevWtBalance = $BalanceWeightquery->row['weight_balance'];			
				$weight_balance =  $prevWtBalance + $weight;

				$prevAverageRate = $BalanceWeightquery->row['average_rate'];
				$totalCost =  $prevAverageRate * $weight;
			}else{ // This scenario will not happens. It will mark error in previous function itself ;
				$count_balance =  $Gem['GemCount'];
				$weight_balance =  $weight;
				$prevAverageRate = $weight;
				$totalCost =  0;
			}
			$transaction_rate = -1 * $totalCost / $prevAverageRate;
		
			$sql .= " , count = '" .  $count  . "' ";
			$sql .= " , total_cost = '" .  $totalCost  . "' ";
			$sql .= " , count_balance ='" .  $count_balance  . "' , carat_weight = '" .  $weight  . "' ";
			$sql .= " , weight_balance ='" .  $weight_balance  . "' ";
			$sql .= " , average_rate = '" .  $prevAverageRate  . "' ";
			$sql .= " , transaction_rate = '" .  $prevAverageRate  . "' ";	
			$sql .= " , size_id = '" .  $BalanceWeightquery->row['size_id']  . "' ";	
		
			$this->log->write($sql);
			$this->db->query($sql);
			
			$PurchaseConsumptionId = $this->db->getLastId();
			
			/* Updating the Purchase Consumption ID for all the order stocks which is calculated during this */
			$this->db->query("Update order_stockist set G1PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', G1Rate = '" . $prevAverageRate . "'  where order_id = '" .  $Gem['order_id']  . "' and G1 = '" .  $Gem['Gem']  . "' and G1SizeId = '" .  $Gem['size_id']  . "' and G1ShapeId = '" .  $Gem['shape_id']  . "' ");
			$this->db->query("Update order_stockist set G2PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', G2Rate = '" . $prevAverageRate . "'  where order_id = '" .  $Gem['order_id']  . "' and G2 = '" .  $Gem['Gem']  . "' and G2SizeId = '" .  $Gem['size_id']  . "' and G2ShapeId = '" .  $Gem['shape_id']  . "' ");
			$this->db->query("Update order_stockist set G3PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', G3Rate = '" . $prevAverageRate . "'  where order_id = '" .  $Gem['order_id']  . "' and G3 = '" .  $Gem['Gem']  . "' and G3SizeId = '" .  $Gem['size_id']  . "' and G3ShapeId = '" .  $Gem['shape_id']  . "' ");
			$this->db->query("Update order_stockist set G4PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', G4Rate = '" . $prevAverageRate . "'  where order_id = '" .  $Gem['order_id']  . "' and G4 = '" .  $Gem['Gem']  . "' and G4SizeId = '" .  $Gem['size_id']  . "' and G4ShapeId = '" .  $Gem['shape_id']  . "' ");
			$this->db->query("Update order_stockist set G5PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', G5Rate = '" . $prevAverageRate . "'  where order_id = '" .  $Gem['order_id']  . "' and G5 = '" .  $Gem['Gem']  . "' and G5SizeId = '" .  $Gem['size_id']  . "' and G5ShapeId = '" .  $Gem['shape_id']  . "' ");
		}
		// return $query->rows;
	}

	public function updateMetalCostingReady() {
		$sql = "SELECT * from (SELECT OS.order_id,OS.metal_id, OS.MetalWeight,M.CorresBaseMetal as Metal,  OS.MetalWeight * (M.metal_claimed_percent / 100) * (1+(M.wastage_percent/100)) as ExpectedWeight FROM  ( select OS.order_id,metal_id,sum(MetalWeight) as MetalWeight from ( 	SELECT order_id as order_id,M1 as metal_id,M1Wt as MetalWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1 union all	SELECT order_id as order_id,M2 as metal_id,M2Wt as MetalWeight FROM `order_stockist` WHERE CostingComplete!=1 and ProdComplete=1  	) AS OS where metal_id != 0 and order_id in (select order_id from order_stockist OS1 where OS.order_id = OS1.order_id and G1CostingError  = '' and G2CostingError  = '' and G3CostingError  = '' and G4CostingError  = '' and G5CostingError  = '' and M1CostingError = '' and M2CostingError = '' and M1CostingError = '' and M2CostingError = '') group by OS.order_id,metal_id ) AS OS, metal M where M.metal_id = OS.metal_id   ) as MPurchase"; 
		
		$query = $this->db->query($sql);


		foreach ($query->rows as $Metal) {

			$sql = "INSERT INTO " . DB_PREFIX . "custom_metal_purchases SET order_id = '" .  (int)$Metal['order_id']  . "',metal_id = '" .  $Metal['Metal']  . "' ";
			$sql .= " ,transaction_date = now() ,  currency = 'INR', supplier_name = ''";		

			$prevWtBalance = 0;
			$MetalQuery = "SELECT weight_balance, average_rate from custom_metal_purchases cmp where cmp.metal_id = '" .  $Metal['Metal']  . "' and  purchase_consumption_id = (select max(cmp1.purchase_consumption_id)  from  custom_metal_purchases cmp1 where cmp1.metal_id = cmp.metal_id  )";
			$BalanceWeightquery = $this->db->query($MetalQuery);
			$this->log->write( "Data : weight_balance = " . $BalanceWeightquery->row['weight_balance'] . ' ; average_rate = ' . $BalanceWeightquery->row['average_rate'] . ' from query - ' . $MetalQuery);

			if($BalanceWeightquery->num_rows)$prevWtBalance = $BalanceWeightquery->row['weight_balance'];
			$weight_balance =  $prevWtBalance - $Metal['ExpectedWeight'];

			$totalCost =  -1 * $BalanceWeightquery->row['average_rate'] *  $Metal['ExpectedWeight'];		
			$averageRate = $BalanceWeightquery->row['average_rate'] ;

			$transaction_rate = -1 * $totalCost / $Metal['ExpectedWeight'];

			$sql .= " ,grams_weight = '" .  (-1 * $Metal['ExpectedWeight'])  . "', total_cost = '" .  $totalCost  . "', average_rate = '" .  $averageRate  . "', weight_balance = '" . $weight_balance . "', transaction_rate = '" . $transaction_rate . "'";

		
			$this->log->write($sql);
			$this->db->query($sql);
			
			$PurchaseConsumptionId = $this->db->getLastId();
			$SQL="Update order_stockist set M1PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', M1Rate = '" . $averageRate . "'  ,  CostingComplete=1 where order_id = " .  $Metal['order_id']  . " and M1 = " .  $Metal['metal_id'] ;
			$this->db->query( $SQL );
			$this->log->write( "uPDATE sql " . $SQL);
			$this->db->query("Update order_stockist set M2PurchaseConsumptionId = '" . $PurchaseConsumptionId . "', M2Rate = '" . $averageRate . "'  ,  CostingComplete=1 where order_id = " .  $Metal['order_id']  . " and M2 = " .  $Metal['metal_id']  );
		}	
		return $query->rows;
	}
	
	public function addGemPurchase( $data ) {
		
		$transaction_rate = $data['total_cost']/ $data['carat_weight'];

		$prevWtBalance = 0;
		$prevCountBalance = 0;
		$BalanceWeightquery = $this->db->query("SELECT weight_balance,count_balance from custom_gem_purchases cmp where cmp.gemstone_id = '" .  $data['gemstone_id']  . "' and size_id = '" .  (int)$data['size_id'] . "'  and shape_id = '" .  (int)$data['shape_id'] . "' and  purchase_consumption_id = (select max(cmp1.purchase_consumption_id)  from  custom_gem_purchases cmp1 where cmp1.gemstone_id = cmp.gemstone_id and  cmp1.size_id = cmp.size_id  and  cmp1.shape_id = cmp.shape_id ) ");
		if($BalanceWeightquery->num_rows)$prevWtBalance = $BalanceWeightquery->row['weight_balance'];
		$weight_balance =  $prevWtBalance  + $data['carat_weight'];

		if($BalanceWeightquery->num_rows)$prevCountBalance = $BalanceWeightquery->row['count_balance'];
		$count_balance =  $prevCountBalance  + $data['count'];

		$grantTotalCost = 0;
		$totalCostQuery = $this->db->query("SELECT sum(total_cost) as total from custom_gem_purchases cmp where cmp.gemstone_id = '" .  $data['gemstone_id']  . "'  and size_id = '" .  (int)$data['size_id'] . "'  and shape_id = '" .  (int)$data['shape_id'] . "' ");
		if($totalCostQuery->num_rows)$grantTotalCost = $totalCostQuery->row['total'];
		
		if($grantTotalCost==0){
			$grantTotalCost = $data['total_cost'];
		}else{
			$grantTotalCost = $grantTotalCost + $data['total_cost'] ;			
		}
		$averageRate =  $grantTotalCost / $weight_balance;
		
		$sqrString = " , average_rate = " . $averageRate . ", count_balance = " . $count_balance . ", weight_balance = " . $weight_balance . ", transaction_rate = " . $transaction_rate     ;

		$rewardstable = $this->db->query("INSERT INTO " . DB_PREFIX . "custom_gem_purchases SET order_id = '" .  (int)$data['order_id']  . "',gemstone_id = '" .  $data['gemstone_id']  . "',size_id = '" .  (int)$data['size_id']  . "', shape_id = '" .  (int)$data['shape_id']  . "',transaction_date = '" .  $data['transaction_date']  . "',total_cost = '" .  $data['total_cost']  . "',currency = '" .  $data['currency']  . "', supplier_name = '" .  $data['supplier_name']  . "',count = '" .  $data['count']  . "',carat_weight = '" .  $data['carat_weight']  . "' "  . $sqrString);
		
		$this->db->query("update `custom_gem_purchases` cgp set size = (select size_code from size where size_id = cgp.size_id) ");
		
		
	}
	
	public function addMetalPurchase( $data ) {
	
		$transaction_rate = $data['total_cost']/ $data['grams_weight'];

		$prevWtBalance = 0;
		$BalanceWeightquery = $this->db->query("SELECT weight_balance from custom_metal_purchases cmp where cmp.metal_id = '" .  $data['metal_id']  . "' and  purchase_consumption_id = (select max(cmp1.purchase_consumption_id)  from  custom_metal_purchases cmp1 where cmp1.metal_id = cmp.metal_id  )");
		if($BalanceWeightquery->num_rows)$prevWtBalance = $BalanceWeightquery->row['weight_balance'];
		$weight_balance =  $prevWtBalance  + $data['grams_weight'];

		$grantTotalCost = 0;
		$totalCostQuery = $this->db->query("SELECT sum(total_cost) as total from custom_metal_purchases cmp where cmp.metal_id = '" .  $data['metal_id']  . "' ");
		if($totalCostQuery->num_rows)$grantTotalCost = $totalCostQuery->row['total'];
		if($grantTotalCost==0){
			$grantTotalCost = $data['total_cost'];
		}else{
			$grantTotalCost = $grantTotalCost + $data['total_cost'] ;
		}
		$averageRate =  $grantTotalCost / $weight_balance;

		$sqrString = " , average_rate = " . $averageRate . ", weight_balance = " . $weight_balance . ", transaction_rate = " . $transaction_rate     ;
		$rewardstable = $this->db->query("INSERT INTO " . DB_PREFIX . "custom_metal_purchases SET order_id = '" .  (int)$data['order_id']  . "',metal_id = '" .  $data['metal_id']  . "',transaction_date = '" .  $data['transaction_date']  . "',total_cost = '" .  $data['total_cost']  . "',currency = '" .  $data['currency']  . "', supplier_name = '" .  $data['supplier_name']  . "',grams_weight = '" .  $data['grams_weight']  . "' " . $sqrString);		
		
		
	}

	public function getOrderStockists($data = array()) {
		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received FROM `" . DB_PREFIX . "order_stockist` o , `" . DB_PREFIX . "customer` a where o.customer_id = a.customer_id ";

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND LCASE(CONCAT(a.firstname, ' ', a.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}

		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		$sort_data = array(
			'o.order_id',
			'customer',
			'o.date_added',
			'o.AJ_price',
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY o.order_id";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}

			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
	
	public function getStocks($data = array()) {
		//$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o , `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 ";

		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and o.manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}
		
		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name,od.FilingPolishing as DefaultFilingPolishing,od.rhodium_cost_id as Defaultrhodium_cost_id,od.casting_cost_id as Defaultcasting_cost_id,od.setting_cost_id as Defaultsetting_cost_id,od.plating_cost_id as Defaultplating_cost_id FROM `order_stockist` o left outer join custom_product_costing_defaults od on  o.product_id = od.product_id and o.M1 = od.M1_id, `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 ";
		$sql .= $userCondition ;
		$sql .= " and o.ProdComplete = 0 " ;
		
		//$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o left  join `product_description` b on o.product_id = b.product_id and b.language_id = 1 left  join `customer` a on o.customer_id = a.customer_id ";

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND o.order_id = '" . (int)$data['filter_order_id'] . "'";
		}
		if (!empty($data['filter_product'])) {
			$sql .= " AND LCASE(o.descr) like '%" . $this->db->escape(utf8_strtolower($data['filter_product'])) . "%'";
		}
/*
		if (!empty($data['filter_customer'])) {
			$sql .= " AND LCASE(CONCAT(a.firstname, ' ', a.lastname)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}
*/
		if (!empty($data['filter_customer'])) {
			$sql .= " AND LCASE(CONCAT(b.name)) LIKE '" . $this->db->escape(utf8_strtolower($data['filter_customer'])) . "%'";
		}
		
		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND o.total = '" . (float)$data['filter_total'] . "'";
		}

		$sort_data = array(
			'o.order_id',
			'customer',
			'o.date_added',
			'o.AJ_price',
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY o.order_id ";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}
			// $data['limit'] = 100;			
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
	

	public function getShipments($data = array()) {
//		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o , `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 ";

		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and o.manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}
		

		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o left outer join `product_description` b on o.product_id = b.product_id and b.language_id = 1  left outer join `customer` a on o.customer_id = a.customer_id where 1 = 1 ";
		
		$sql .= $userCondition ;
		
		$sql .= "  and o.ProdComplete = 1 and o.ProdShippingDate = '0000-00-00' order by order_id desc " ;

		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}
			//$data['limit'] = 5;
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}
	public function getTotalShipments($data = array()) {

		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and o.manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}
		$sql = "SELECT count(*) as total FROM `order_stockist` o where o.ProdComplete = 1 and o.ProdShippingDate ='0000-00-00' " ;
		$sql .= $userCondition;
		$query = $this->db->query($sql);

		return $query->row['total'];
	}
	public function getTotalShipmentsError($data = array()) {
		$sql = "SELECT count(*) as total FROM `order_stockist` o where 1 = 1 " ;
		$sql .= " and ( G1CostingError != '' or G2CostingError != '' or G3CostingError != '' or G4CostingError != '' or G5CostingError != '' or M1CostingError != '' or M2CostingError != '' )";
		$sql .= " and o.ProdComplete = 1" ;		
		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getTotalShipmentsHistory($data = array()) {
//		$sql = "SELECT count(*) as total FROM `order_stockist` o where 1 = 1 " ;

		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and o.manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}

		$sql = "SELECT count(*) as total FROM `order_stockist` o left outer join  `product_description` b  on o.product_id = b.product_id and b.language_id = 1 left outer join `customer` a on o.customer_id = a.customer_id where 1 = 1 ";		
		$sql .= $userCondition;
		$sql .= " and o.ProdShippingDate != '0000-00-00' " ;

		if (!empty($data['filter_date'])) {
			$sql .= " AND DATE(o.ProdShippingDate) >= DATE('" . $this->db->escape($data['filter_date']) . "')";
		}
		if (!empty($data['filter_date_to'])) {
			$sql .= " AND DATE(o.ProdShippingDate) <= DATE('" . $this->db->escape($data['filter_date_to']) . "')";
		}
		$this->log->write($sql);

		$query = $this->db->query($sql);

		return $query->row['total'];
	}	
	public function getShipmentHistory($data = array()) {
//		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o , `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 ";


		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and o.manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}

		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o left outer join  `product_description` b  on o.product_id = b.product_id and b.language_id = 1 left outer join `customer` a on o.customer_id = a.customer_id where 1 = 1 ";
		$sql .= $userCondition;
		$sql .= " and o.ProdShippingDate != '0000-00-00' " ;

		if (!empty($data['filter_date'])) {
			// $sql .= " AND DATE(o.date_added) = DATE('" . $this->db->escape($data['filter_date']) . "')";
			$sql .= " AND DATE(o.ProdShippingDate) >= DATE('" . $this->db->escape($data['filter_date']) . "')";
		}
		if (!empty($data['filter_date_to'])) {
			$sql .= " AND DATE(o.ProdShippingDate) <= DATE('" . $this->db->escape($data['filter_date_to']) . "')";
		}
		
		$sql .= " order by order_id desc";
		if (isset($data['start']) || isset($data['limit'])) { 
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				//$data['limit'] = 20;
			}
			//$data['limit'] = 5;
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}
		$this->log->write($sql);
		$query = $this->db->query($sql);

		return $query->rows;
	}
	public function getShipmentError($data = array()) {
		//$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o , `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 " ; 
		//$sql .= "and ( G1CostingError != '' or G2CostingError != '' or G3CostingError != '' or G4CostingError != '' or G5CostingError != '' or M1CostingError != '' or M2CostingError != '' )";
		//$sql .= " and o.ProdComplete = 1" ;		
		//$sql .= " order by order_id desc";
		
		$sql = "SELECT o.order_id, CONCAT(a.firstname, ' ', a.lastname) AS customer, AJ_price as total, o.date_added, o.product_id, o.customer_id,o.descr,o.comment,o.comment2,o.shipped,o.received,o.*, b.name FROM `order_stockist` o left outer join `product_description` b on o.product_id = b.product_id and b.language_id = 1 left outer join `customer` a on o.customer_id = a.customer_id ";
		$sql .= " where ( G1CostingError != '' or G2CostingError != '' or G3CostingError != '' or G4CostingError != '' or G5CostingError != '' or M1CostingError != '' or M2CostingError != '' ) ";
		$sql .= " and o.ProdComplete = 1 order by order_id desc	";
		if (isset($data['start']) || isset($data['limit'])) {
			if ($data['start'] < 0) {
				$data['start'] = 0;
			}

			if ($data['limit'] < 1) {
				$data['limit'] = 20;
			}
			$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
		}

		$query = $this->db->query($sql);

		return $query->rows;
	}		
	
	public function getGemCodes($data = array()) {
	
		$query = $this->db->query("SELECT gemstone_id,gemname,gemcode FROM `gemstone` where gemcode <> ' ' order by gemname ");

		return $query->rows;
	}
	
	public function getSettingCodes() {
	
		$query = $this->db->query("SELECT * FROM `custom_gem_setting_cost`");

		return $query->rows;
	}
	public function getRhodiumCodes() {
	
		$query = $this->db->query("SELECT * FROM `custom_rhodium_cost`");

		return $query->rows;
	}
	public function getCastingCodes() {
	
		$query = $this->db->query("SELECT * FROM `custom_casting_cost`");

		return $query->rows;
	}
	public function getPlatingCodes() {
	
		$query = $this->db->query("SELECT * FROM `custom_plating_cost`");

		return $query->rows;
	}
	public function getGemSizes() {
	
		$query = $this->db->query("SELECT * FROM `size`");

		return $query->rows;
	}
	
	public function geMetalCodes($data = array()) {
	
		$query = $this->db->query("SELECT metal_id,metalname,metal_code FROM `metal`");

		return $query->rows;
	}
	
	public function geMetals($data = array()) {
	
		$query = $this->db->query("SELECT metal_id,metalname,metal_code FROM `metal` where BaseMetal = 1");

		return $query->rows;
	}
	public function getTotalMetalPurchases() {
//      	$sql = "SELECT count(*) as total FROM `" . DB_PREFIX . "custom_metal_purchases`  ";
      	$sql = "SELECT count(*) as total FROM `" . DB_PREFIX . "custom_metal_purchases` where order_id = 0  ";
 

		$query = $this->db->query($sql);

		return $query->row['total'];
	}
	
	public function getTotalGemPurchases() {
//      	$sql = "SELECT count(*) as total FROM `" . DB_PREFIX . "custom_gem_purchases`  ";
      	$sql = "SELECT count(*) as total FROM `" . DB_PREFIX . "custom_gem_purchases` where order_id = 0  ";

		$query = $this->db->query($sql);

		return $query->row['total'];
	}
	public function updateOrderStockist($data,$OrderId,$Flag) {

		$url= "";
		if($OrderId==""){
			$OrderId = $data['processOrderId'];
		}
		
		if(isset($data['M2Weight'. $OrderId]) && $data['M2Weight'. $OrderId] > 0){
		//	$url .= ", M2Wt  = '" . $data['M2Weight' . $OrderId] . "' ";
		}
		
		$GemWeight = 0;
		if(isset($data['G1Wt'. $OrderId]) && $data['G1Wt'. $OrderId] > 0){
			$url .= ", G1Wt  = '" . $data['G1Wt' . $OrderId] . "', G1ShapeId  = '" . $data['G1ShapeId' . $OrderId] . "', G1SizeId  = '" . $data['G1SizeId' . $OrderId] . "', `G1#` = '" . $data['G1Count' . $OrderId] . "' ";
			$GemWeight = $GemWeight + $data['G1Wt' . $OrderId] ;
		}
		if(isset($data['G2Wt'. $OrderId]) && $data['G2Wt'. $OrderId] > 0){
			$url .= ", G2Wt  = '" . $data['G2Wt' . $OrderId] . "', G2ShapeId  = '" . $data['G2ShapeId' . $OrderId] . "', G2SizeId  = '" . $data['G2SizeId' . $OrderId] . "', `G2#` = '" . $data['G2Count' . $OrderId] . "'	";
			$GemWeight = $GemWeight + $data['G2Wt' . $OrderId] ;
		}
		if(isset($data['G3Wt'. $OrderId]) && $data['G3Wt'. $OrderId] > 0){
			$url .= ", G3Wt  = '" . $data['G3Wt' . $OrderId] . "', G3ShapeId  = '" . $data['G3ShapeId' . $OrderId] . "', G3SizeId  = '" . $data['G3SizeId' . $OrderId] . "', `G3#` = '" . $data['G3Count' . $OrderId] . "'		";
			$GemWeight = $GemWeight + $data['G3Wt' . $OrderId] ;
		}
		if(isset($data['G4Wt'. $OrderId]) && $data['G4Wt'. $OrderId] > 0){
			$url .= ", G4Wt  = '" . $data['G4Wt' . $OrderId] . "', G4ShapeId  = '" . $data['G4ShapeId' . $OrderId] . "', G4SizeId  = '" . $data['G4SizeId' . $OrderId] . "', `G4#` = '" . $data['G4Count' . $OrderId] . "'	";
			$GemWeight = $GemWeight + $data['G4Wt' . $OrderId] ;
		}
		if(isset($data['G5Wt'. $OrderId]) && $data['G5Wt'. $OrderId] > 0){
			$url .= ", G5Wt  = '" . $data['G5Wt' . $OrderId] . "', G5ShapeId  = '" . $data['G5ShapeId' . $OrderId] . "', G5SizeId  = '" . $data['G5SizeId' . $OrderId] . "', `G5#` = '" . $data['G5Count' . $OrderId] . "'	";
			$GemWeight = $GemWeight + $data['G5Wt' . $OrderId] ;
		}
		$GemTotal = $GemWeight / 5 ; // Conversion of Carat into Weight ;		

 
		if(isset($data['M2Weight'. $OrderId]) && $data['M2Weight'. $OrderId] > 0){			
			$MetalTotal = $data['M1Weight' . $OrderId] + $data['M2Weight' . $OrderId];
			$NetMetalTotal= $MetalTotal-$GemTotal; 
			
			$NewM1Wt = $data['M1Weight' . $OrderId] * $NetMetalTotal / $MetalTotal;
			$NewM2Wt = $data['M2Weight' . $OrderId] * $NetMetalTotal / $MetalTotal;			
			
			$url .= ", M1GrossWt = '" . $data['M1Weight' . $OrderId] . "'" ;
			$url .= ", M2GrossWt = '" . $data['M2Weight' . $OrderId] . "'"  ;
			$url .= ", M1Wt  = " . $NewM1Wt ;
			$url .= ", M2Wt  = " . $NewM2Wt ;
		}else{
			$NewM1Wt = $data['M1Weight' . $OrderId] - $GemTotal;

			$url .= ", M1GrossWt  = '" . $data['M1Weight' . $OrderId]  . "'" ;
			$url .= ", M1Wt  = '" . $NewM1Wt  . "'" ;
			$url .= ", M2Wt  = 0 ";
			$url .= ", M2GrossWt  = 0 ";
		}
				
		if( isset($data['ProdComplete'. $OrderId]) ){
			$url .= ' , ProdComplete = 1 ';
			$url .= ' , ShipQty = OrdProdQty '; // Happens only at 'Production Status & Costs'  page
		}
		if( $data['ShipQty'. $OrderId] > 0 ){
			$url .= ' , ShipQty = ' . $data['ShipQty'. $OrderId] ;			
		}
		if( $Flag == 'ShipNow' ){
			$url .= ' , ProdShippingDate = NOW() ' ; 
		} 
		
      	//$SQL = "UPDATE order_stockist SET M1Wt   = '" . $data['M1Weight' . $OrderId] . "' " . $url . "	, setting_cost_id  = '" . $data['Setting' . $OrderId] . "', casting_cost_id  = '" . $data['Casting' . $OrderId] . "', rhodium_cost_id  = '" . $data['Rhodium' . $OrderId] . "', plating_cost_id  = '" . $data['Plating' . $OrderId] . "', other_cost_id  = '" . $data['OtherCode' . $OrderId] . "', FilingPolishing  = '" . $data['FilingPolishing' . $OrderId] . "', OtherCost   = '" . $data['OtherCost' . $OrderId] . "', OrdProdQty  = '" . $data['OrdProdQty' . $OrderId]   . "' where order_id = '" . $OrderId   . "'" ;
		$SQL = "UPDATE order_stockist SET  setting_cost_id  = '" . $data['Setting' . $OrderId] . "' " . $url . " , casting_cost_id  = '" . $data['Casting' . $OrderId] . "', rhodium_cost_id  = '" . $data['Rhodium' . $OrderId] . "', plating_cost_id  = '" . $data['Plating' . $OrderId] . "', other_cost_id  = '" . $data['OtherCode' . $OrderId] . "', FilingPolishing  = '" . $data['FilingPolishing' . $OrderId] . "', OtherCost   = '" . $data['OtherCost' . $OrderId] . "', OrdProdQty  = '" . $data['OrdProdQty' . $OrderId]   . "' where order_id = '" . $OrderId   . "'" ;
		$this->log->write($SQL);
		$this->db->query($SQL);
      	
	}
	
	public function insertOrderStockist($data) {
		$url = "";
		$GemWeight = 0;
		if($data['G1Wt'] > 0 ){ $GemWeight = $GemWeight + $data['G1Wt'] ; }
		if($data['G2Wt'] > 0 ){ $GemWeight = $GemWeight + $data['G2Wt'] ; }
		if($data['G3Wt'] > 0 ){ $GemWeight = $GemWeight + $data['G3Wt'] ; }
		if($data['G4Wt'] > 0 ){ $GemWeight = $GemWeight + $data['G4Wt'] ; }
		if($data['G5Wt'] > 0 ){ $GemWeight = $GemWeight + $data['G5Wt'] ; }
		$GemTotal = $GemWeight / 5 ; // Conversion of Carat into Weight ;		
		
		if($data['M2Weight'] > 0 ){	
			$MetalTotal = $data['M1Weight'] + $data['M2Weight'];
			$NetMetalTotal= $MetalTotal-$GemTotal; 
			
			$NewM1Wt = $data['M1Weight'] * $NetMetalTotal / $MetalTotal;
			$NewM2Wt = $data['M2Weight'] * $NetMetalTotal / $MetalTotal;			
			
			$url .= ", M1GrossWt = '" . $data['M1Weight'] . "'" ;
			$url .= ", M2GrossWt = '" . $data['M2Weight'] . "'"  ;
			$url .= ", M1Wt  = " . $NewM1Wt ;
			$url .= ", M2Wt  = " . $NewM2Wt ;
		}else{
			$NewM1Wt = $data['M1Weight' . $OrderId] - $GemTotal;

			$url .= ", M1GrossWt  = '" . $data['M1Weight']  . "'" ;
			$url .= ", M1Wt  = '" . $NewM1Wt  . "'" ;
			$url .= ", M2Wt  = 0 ";
			$url .= ", M2GrossWt  = 0 ";
		}		
		
			
      	// $SQL = "insert into order_stockist SET descr = '" .  $data['prdname'] . "'	, manual_record=1, ProdComplete=1, M1  = '" . $data['Metal1'] . "'	, M1Wt   = '" . $data['M1Weight'] . "'		, M2  = '" . $data['Metal2'] . "'		, M2Wt   = '" . $data['M2Weight'] . "'				, G1  = '" . $data['Gem1'] . "', G1Wt  = '" . $data['G1Wt'] . "', G1ShapeId  = '" . $data['G1ShapeId'] . "', G1SizeId  = '" . $data['G1SizeId'] . "', `G1#` = '" . $data['G1Count'] . "'		, G2  = '" . $data['Gem2'] . "', G2Wt  = '" . $data['G2Wt'] . "', G2ShapeId  = '" . $data['G2ShapeId'] . "', G2SizeId  = '" . $data['G2SizeId'] . "', `G2#` = '" . $data['G2Count'] . "'		, G3  = '" . $data['Gem3'] . "', G3Wt  = '" . $data['G3Wt'] . "', G3ShapeId  = '" . $data['G3ShapeId'] . "', G3SizeId  = '" . $data['G3SizeId'] . "', `G3#` = '" . $data['G3Count'] . "'		, G4  = '" . $data['Gem4'] . "', G4Wt  = '" . $data['G4Wt'] . "', G4ShapeId  = '" . $data['G4ShapeId'] . "', G4SizeId  = '" . $data['G4SizeId'] . "', `G4#` = '" . $data['G4Count'] . "'		, G5  = '" . $data['Gem5'] . "', G5Wt  = '" . $data['G5Wt'] . "', G5ShapeId  = '" . $data['G5ShapeId'] . "', G5SizeId  = '" . $data['G5SizeId'] . "', `G5#` = '" . $data['G5Count'] . "'		, setting_cost_id  = '" . $data['Setting'] . "', casting_cost_id  = '" . $data['Casting'] . "', rhodium_cost_id  = '" . $data['Rhodium'] . "', plating_cost_id  = '" . $data['Plating'] . "', other_cost_id  = '" . $data['OtherCode'] . "', FilingPolishing  = '" . $data['FilingPolishing'] . "', OtherCost   = '" . $data['OtherCost'] . "', OrdProdQty  = '" . $data['OrdProdQty']   . "', ShipQty  = '" . $data['OrdProdQty']   . "'" ;
		$SQL = "insert into order_stockist SET descr = '" .  $data['prdname'] . "' " . $url .  " , manual_record=1, ProdComplete=1, M1  = '" . $data['Metal1'] . "'	, M2  = '" . $data['Metal2'] . "' ,  G1  = '" . $data['Gem1'] . "', G1Wt  = '" . $data['G1Wt'] . "', G1ShapeId  = '" . $data['G1ShapeId'] . "', G1SizeId  = '" . $data['G1SizeId'] . "', `G1#` = '" . $data['G1Count'] . "'		, G2  = '" . $data['Gem2'] . "', G2Wt  = '" . $data['G2Wt'] . "', G2ShapeId  = '" . $data['G2ShapeId'] . "', G2SizeId  = '" . $data['G2SizeId'] . "', `G2#` = '" . $data['G2Count'] . "'		, G3  = '" . $data['Gem3'] . "', G3Wt  = '" . $data['G3Wt'] . "', G3ShapeId  = '" . $data['G3ShapeId'] . "', G3SizeId  = '" . $data['G3SizeId'] . "', `G3#` = '" . $data['G3Count'] . "'		, G4  = '" . $data['Gem4'] . "', G4Wt  = '" . $data['G4Wt'] . "', G4ShapeId  = '" . $data['G4ShapeId'] . "', G4SizeId  = '" . $data['G4SizeId'] . "', `G4#` = '" . $data['G4Count'] . "'		, G5  = '" . $data['Gem5'] . "', G5Wt  = '" . $data['G5Wt'] . "', G5ShapeId  = '" . $data['G5ShapeId'] . "', G5SizeId  = '" . $data['G5SizeId'] . "', `G5#` = '" . $data['G5Count'] . "'		, setting_cost_id  = '" . $data['Setting'] . "', casting_cost_id  = '" . $data['Casting'] . "', rhodium_cost_id  = '" . $data['Rhodium'] . "', plating_cost_id  = '" . $data['Plating'] . "', other_cost_id  = '" . $data['OtherCode'] . "', FilingPolishing  = '" . $data['FilingPolishing'] . "', OtherCost   = '" . $data['OtherCost'] . "', OrdProdQty  = '" . $data['OrdProdQty']   . "', ShipQty  = '" . $data['OrdProdQty']   . "'" ;
		$this->log->write($SQL);

		$this->db->query($SQL);
      	
	}
	
	
	public function getTotalOrderStockists($data = array()) {
		$userCondition = '';
		if ($this->user->isLogged() <> 1 ){
			$userCondition =  ' and manuf_user_Id = ' . $this->user->isLogged() . ' ';
		}
      	$sql = "SELECT count(*) as total FROM `" . DB_PREFIX . "order_stockist` o  where ProdComplete = 0 " . $userCondition ;

//		$sql = "SELECT count(*) as total FROM `order_stockist` o , `product_description` b , `customer` a where o.customer_id = a.customer_id and o.product_id = b.product_id and b.language_id = 1 and o.ProdComplete = 0" ;

		if (!empty($data['filter_product'])) {
			$sql .= " AND LCASE(o.descr) like '%" . $this->db->escape(utf8_strtolower($data['filter_product'])) . "%'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}
	
	public function getProductOptionName($product_id) {
		$query = $this->db->query("SELECT optionname FROM  product_description_vw WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->row['optionname'];
	}
	public function getOrderProducts($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
		
		return $query->rows;
	}
	public function getOrderSVG($product_id) {
		$query = $this->db->query("SELECT svgContents FROM " . DB_PREFIX . "rea_product WHERE product_id = '" . (int)$product_id . "'");
		
		return $query->row['svgContents'];
	}
	
	public function getImagefromProduct($product_id) {
		$query = $this->db->query("SELECT image FROM " . DB_PREFIX . "product_vw WHERE product_id = '" . (int)$product_id . "'");
		if(isset($query->row['image']))
		{
		return $query->row['image'];
		} else { 
		return 0;
		}
	}
	
	public function getOrderOptions($order_id, $order_product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_option WHERE order_id = '" . (int)$order_id . "' AND order_product_id = '" . (int)$order_product_id . "'");

		return $query->rows;
	}
	
	public function getOrderTotals($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_total WHERE order_id = '" . (int)$order_id . "' ORDER BY sort_order");

		return $query->rows;
	}

	public function getOrderDownloads($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_download WHERE order_id = '" . (int)$order_id . "' ORDER BY name");

		return $query->rows;
	}

	public function getTotalOrders($data = array()) {
      	$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order`";

		if (isset($data['filter_order_status_id']) && !is_null($data['filter_order_status_id'])) {
			$sql .= " WHERE order_status_id = '" . (int)$data['filter_order_status_id'] . "'";
		} else {
			$sql .= " WHERE order_status_id > '0'";
		}

		if (!empty($data['filter_order_id'])) {
			$sql .= " AND order_id = '" . (int)$data['filter_order_id'] . "'";
		}

		if (!empty($data['filter_customer'])) {
			$sql .= " AND CONCAT(firstname, ' ', lastname) LIKE '%" . $this->db->escape($data['filter_customer']) . "%'";
		}

		if (!empty($data['filter_date_added'])) {
			$sql .= " AND DATE(date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

		if (!empty($data['filter_total'])) {
			$sql .= " AND total = '" . (float)$data['filter_total'] . "'";
		}

		$query = $this->db->query($sql);

		return $query->row['total'];
	}

	public function getTotalOrdersByStoreId($store_id) {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE store_id = '" . (int)$store_id . "'");

		return $query->row['total'];
	}

	public function getTotalOrdersByOrderStatusId($order_status_id) {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id = '" . (int)$order_status_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalOrdersByLanguageId($language_id) {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE language_id = '" . (int)$language_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalOrdersByCurrencyId($currency_id) {
      	$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order` WHERE currency_id = '" . (int)$currency_id . "' AND order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalSales() {
      	$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0'");

		return $query->row['total'];
	}

	public function getTotalSalesByYear($year) {
      	$query = $this->db->query("SELECT SUM(total) AS total FROM `" . DB_PREFIX . "order` WHERE order_status_id > '0' AND YEAR(date_added) = '" . (int)$year . "'");

		return $query->row['total'];
	}

	public function createInvoiceNo($order_id) {
		$order_info = $this->getOrder($this->request->get['order_id']);
			
		if ($order_info && !$order_info['invoice_no']) {
			$query = $this->db->query("SELECT MAX(invoice_no) AS invoice_no FROM `" . DB_PREFIX . "order` WHERE invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "'");
	
			if ($query->row['invoice_no']) {
				$invoice_no = $query->row['invoice_no'] + 1;
			} else {
				$invoice_no = 1;
			}
		
			$this->db->query("UPDATE `" . DB_PREFIX . "order` SET invoice_no = '" . (int)$invoice_no . "', invoice_prefix = '" . $this->db->escape($order_info['invoice_prefix']) . "' WHERE order_id = '" . (int)$order_id . "'");
			
			return $order_info['invoice_prefix'] . $invoice_no;
		}
	}
	
	public function addOrderHistory($order_id, $data) {
		$this->db->query("UPDATE `" . DB_PREFIX . "order` SET order_status_id = '" . (int)$data['order_status_id'] . "', date_modified = NOW() WHERE order_id = '" . (int)$order_id . "'");

		//  Affiliate System -- Add on Starts Here
 	  if((int)$data['order_status_id']=='5')
 	  {
 	  	 $query9 = $this->db->query("select * from  " . DB_PREFIX . "referralorders a where a.order_id= '" . (int)$order_id  . "'");
 	  	 if(isset($query9->row['affiliate_id']))
 	  	 {
           if($query9->row['Payment_Status'] == 0 )
   {
          $this->db->query("UPDATE " . DB_PREFIX . "affaccount SET  Sale_Commission = Sale_Commission + " . $query9->row['Earnings'] . ", Sale_Flat_Rate = Sale_Flat_Rate + " . $query9->row['Earnings_Flat'] . ", Sale_Total= Sale_Total + " . $query9->row['Earnings'] . " + " . $query9->row['Earnings_Flat'] . " WHERE affiliate_id = '" . (int)$query9->row['affiliate_id'] . "' and Payment_Status=0 ");

          $this->db->query("UPDATE " . DB_PREFIX . "referralorders SET Payment_date  = NOW(),  Payment_Status = 1 WHERE affiliate_id = '" . (int)$query9->row['affiliate_id'] . "' and order_id= '" . (int)$order_id  . "'");

   }

       }
	   	 $contest = $this->db->query("select contest_id from  `" . DB_PREFIX . "order` a where a.order_id= '" . (int)$order_id  . "'");
	   	
		 if(isset($contest->row['contest_id'])) {
		 if($contest->row['contest_id'])
 	  	 { 
			$firstname = $this->db->query("select c.firstname,a.store_name,a.email from  `" . DB_PREFIX . "order` a,`" . DB_PREFIX . "customer` c  where c.customer_id=a.customer_id AND a.order_id= '" . (int)$order_id  . "'");
			$fullname = $this->db->query("select special_ocassion,design_category,name,contest_id from  `" . DB_PREFIX . "contest_master`  where  order_id= '" . (int)$order_id  . "'");
			 if($fullname->num_rows)
 	  	 {
			$designname = $this->db->query("select design_category from  `" . DB_PREFIX . "contest_sample_designs` a where a.contest_sample_designs_id= '" . (int)$fullname->row['design_category']  . "'");
			$desc = $this->db->query("select detailed_description as des from  `" . DB_PREFIX . "occasion_master` where occasionmaster_id= '" . (int)$fullname->row['special_ocassion']  . "'");
			$name = $desc->row['des'].' '.$designname->row['design_category'].' for '. $firstname->row['firstname'].' ';
			$this->db->query("UPDATE `" . DB_PREFIX . "contest_master` SET status = '2',name='" . $name . "' WHERE order_id = '" . (int)$order_id . "' AND contest_id = '" . (int)$contest->row['contest_id'] . "'");
		
			$store_logo = $this->db->query("SELECT value FROM `contest_master` a,setting b WHERE a.store_id=b.store_id and `key`='config_logo' and contest_id='". (int)$contest->row['contest_id'] ."'");
			$store_name = $this->db->query("SELECT value FROM `contest_master` a,setting b WHERE a.store_id=b.store_id and `key`='config_name' and contest_id='". (int)$contest->row['contest_id'] ."'");

			$subject1 = $this->db->query("select title from  `" . DB_PREFIX . "information_description` where information_id=33 and language_id=1");
			$message1 = $this->db->query("select description from  `" . DB_PREFIX . "information_description` where information_id=33 and language_id=1");
			//$subject = html_entity_decode($subject1->row['title'], ENT_QUOTES, 'UTF-8');
			$subject = 'Contest Launched - ' . $fullname->row['name'] . '(Contest Id #'.$fullname->row['contest_id'].')';
//			$message = '<p>	<img alt="" src="'. HTTP_CATALOG . '/image/' . $this->config->get('config_logo') .'" style="width: 165px; height: 120px;" /></p>' . '<br>';
			$message = '<p>	<img alt="" src="'. HTTP_CATALOG . '/image/' . $store_logo->row['value'] .'" /></p>' . '<br>';
			$message .= 'Hi '.$firstname->row['firstname'] . ' , <br>';
			$message .= $message1->row['description'];
			$emails = array();  
			$emails[] = $firstname->row['email'];  
			$emails[] = $this->config->get('config_email');
			$emails = array_unique($emails);
			
			foreach ($emails as $email) {
			$mail = new Mail();
			$mail->protocol = $this->config->get('config_mail_protocol');
			$mail->parameter = $this->config->get('config_mail_parameter');
			$mail->hostname = $this->config->get('config_smtp_host');
			$mail->username = $this->config->get('config_smtp_username');
			$mail->password = $this->config->get('config_smtp_password');
			$mail->port = $this->config->get('config_smtp_port');
			$mail->timeout = $this->config->get('config_smtp_timeout');
			$mail->setTo($email);
			$mail->setFrom($this->config->get('config_email'));
			$mail->setSender($firstname->row['store_name']);
			//$mail->setSender($this->config->get('config_name'));
			$mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
			$mail->setHtml(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
			//$mail->addAttachment(DIR_IMAGE . $this->config->get('config_logo'), md5(basename($this->config->get('config_logo'))));
			$mail->send();
			}		
		}
		}
		}
     }
//  Affiliate System -- Add on Ends   Here

//  CUSTOMER- DESIGNER - GROUP Based REWARDS in ORDER STATUS as SHIPPED -------------- Starts Here-----------
	if((int)$data['order_status_id']=='3')
		{
 	  	 $prdid = $this->db->query("select product_id from  " . DB_PREFIX . "order_product where order_id= '" . (int)$order_id  . "'");
 	  	 $ccode = $this->db->query("select currency_code from  " . DB_PREFIX . "`order` where order_id= '" . (int)$order_id  . "'");
 	  	 $cusid = $this->db->query("select customer_id from  " . DB_PREFIX . "`order` where order_id= '" . (int)$order_id  . "'");
 	  	 $cusname = $this->db->query("select CONCAT(firstname, ' ', lastname) AS name from  " . DB_PREFIX . "customer where customer_id= '" . (int)$cusid->row['customer_id']  . "'");
			if (isset($prdid->rows)) {		
				foreach ($prdid->rows as $prdrows) {
				$product_id = $prdrows['product_id'];
				 $designreward = 0;$customreward = 0;$groupreward = 0;$owncount = 0;$totalcus = 0;
				if((int)$product_id < 100000){
					$designerid = $this->db->query("select designer_id from  " . DB_PREFIX . "sketches_master where product_id= '" . (int)$product_id  . "'");
				}else{
					$parentproduct = $this->db->query("select a.product_id from  " . DB_PREFIX . "product a,rea_product b where a.product_id=b.parent_product_id and b.product_id= '" . (int)$product_id  . "'");
					$designerid = $this->db->query("select designer_id from  " . DB_PREFIX . "sketches_master where product_id= '" . $parentproduct->row['product_id']  . "'");
				}
				$prdprice   = $this->db->query("select price from  " . DB_PREFIX . "order_product where product_id= '" . (int)$product_id  . "'");
				$owncount   = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "order_product` WHERE product_id = '" . (int)$product_id . "'");
				$totalcus   = $this->db->query("SELECT COUNT(o.customer_id) AS total FROM `" . DB_PREFIX . "order_product` op,`" . DB_PREFIX . "order` o WHERE o.order_id = op.order_id and o.order_status_id = 3 and op.product_id = '" . (int)$product_id . "'");
				$finalcount = $totalcus->row['total'] - 1;
				$allcus     = $this->db->query("SELECT o.customer_id as customer_id,CONCAT(c.firstname, ' ', c.lastname) AS name FROM `" . DB_PREFIX . "order_product` op,`" . DB_PREFIX . "order` o,`" . DB_PREFIX . "customer` c WHERE o.order_id = op.order_id and o.customer_id = c.customer_id and op.product_id = '" . (int)$product_id . "'");
				//Designer Reward calculation
				if (isset($designerid->row['designer_id'])) {
				$desname = $this->db->query("select CONCAT(firstname, ' ', lastname) AS name from  " . DB_PREFIX . "designer where designer_id= '" . (int)$designerid->row['designer_id']  . "'");
				if($this->config->get('designer_reward_sale_based')){ $designreward = ($this->config->get('designer_reward_sale_based') * $prdprice->row['price']) / 100; } 
				}
				//Customer Reward calculation
				if($this->config->get('customer_reward_sale_based')) { $customreward = ($this->config->get('customer_reward_sale_based') * $prdprice->row['price']) / 100; } 
				//Group Based Reward calculation
				if($this->config->get('group_reward_sale_based')) { $groupreward =  ($this->config->get('group_reward_sale_based') * $prdprice->row['price']) / (100 * $finalcount); } 
				// INSERTING VALUES INTO TABLES
				//Rewards
				$rewardstable = $this->db->query("INSERT INTO " . DB_PREFIX . "rewards SET order_id = '" . (int)$order_id . "',product_id = '" . (int)$product_id . "',sale_amount = '" .  $prdprice->row['price'] . "',currency = '" . $ccode->row['currency_code'] . "',designer_reward = '" . $designreward . "',group_reward = '" . $groupreward . "',customer_reward = '" . $customreward . "',owner_count = '" . (int)$finalcount . "'");
				$reward_id = $this->db->getLastId();
				//customer_rewards_distribution
				$this->db->query("INSERT INTO " . DB_PREFIX . "customer_rewards_distribution SET reward_id = '" . (int)$reward_id . "',customer_designer_id = '" . (int)$cusid->row['customer_id'] . "',designer_customer = '" . $cusname->row['name'] . "',reward_amount = '" . $customreward . "',currency = '" . $ccode->row['currency_code'] . "'");
				//designer_rewards_distribution
				if (isset($designerid->row['designer_id'])) {
					$this->db->query("INSERT INTO " . DB_PREFIX . "designer_rewards_distribution SET reward_id = '" . (int)$reward_id . "',customer_designer_id = '" . (int)$designerid->row['designer_id'] . "',designer_customer = '" . $desname->row['name'] . "',reward_amount = '" . $designreward . "',currency = '" . $ccode->row['currency_code'] . "'");
				}
				//group_rewards_distribution
				if (isset($groupreward)) {
				foreach ($allcus->rows as $allcusrows) {
				$allcusid = $allcusrows['customer_id'];
				$allcusname = $allcusrows['name'];
				$this->db->query("INSERT INTO " . DB_PREFIX . "group_rewards_distribution SET reward_id = '" . (int)$reward_id . "',customer_designer_id = '" . (int)$allcusid . "',designer_customer = '" . $allcusname . "',reward_amount = '" . $groupreward . "',currency = '" . $ccode->row['currency_code'] . "'");
				}
				}
				
				}
			} 
		}
//  CUSTOMER- DESIGNER - GROUP Based REWARDS in ORDER STATUS as SHIPPED --------------Ends Here-----------

		
		$this->db->query("INSERT INTO " . DB_PREFIX . "order_history SET order_id = '" . (int)$order_id . "', order_status_id = '" . (int)$data['order_status_id'] . "', notify = '" . (isset($data['notify']) ? (int)$data['notify'] : 0) . "', comment = '" . $this->db->escape(strip_tags($data['comment'])) . "', date_added = NOW()");

		$order_info = $this->getOrder($order_id);

		// Send out any gift voucher mails
		/*if ($this->config->get('config_complete_status_id') == $data['order_status_id']) {
			$this->load->model('sale/voucher');

			$results = $this->model_sale_voucher->getVouchersByOrderId($order_id);
			
			foreach ($results as $result) {
				$this->model_sale_voucher->sendVoucher($result['voucher_id']);
			}
		}

      	if ($data['notify']) {
			$language = new Language($order_info['language_directory']);
			$language->load($order_info['language_filename']);
			$language->load('mail/order');

			$subject = sprintf($language->get('text_subject'), $order_info['store_name'], $order_id);

			$message  = $language->get('text_order') . ' ' . $order_id . "\n";
			$message .= $language->get('text_date_added') . ' ' . date($language->get('date_format_short'), strtotime($order_info['date_added'])) . "\n\n";
			
			$order_status_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_status WHERE order_status_id = '" . (int)$data['order_status_id'] . "' AND language_id = '" . (int)$order_info['language_id'] . "'");
				
			if ($order_status_query->num_rows) {
				$message .= $language->get('text_order_status') . "\n";
				$message .= $order_status_query->row['name'] . "\n\n";
			}
			
			if ($order_info['customer_id']) {
				$message .= $language->get('text_link') . "\n";
				$message .= html_entity_decode($order_info['store_url'] . 'index.php?route=account/order/info&order_id=' . $order_id, ENT_QUOTES, 'UTF-8') . "\n\n";
			}
			
			if ($data['comment']) {
				$message .= $language->get('text_comment') . "\n\n";
				$message .= strip_tags(html_entity_decode($data['comment'], ENT_QUOTES, 'UTF-8')) . "\n\n";
			}

			$message .= $language->get('text_footer');

			$mail = new Mail();
			$mail->protocol = $this->config->get('config_mail_protocol');
			$mail->parameter = $this->config->get('config_mail_parameter');
			$mail->hostname = $this->config->get('config_smtp_host');
			$mail->username = $this->config->get('config_smtp_username');
			$mail->password = $this->config->get('config_smtp_password');
			$mail->port = $this->config->get('config_smtp_port');
			$mail->timeout = $this->config->get('config_smtp_timeout');
			$mail->setTo($order_info['email']);
			$mail->setFrom($this->config->get('config_email'));
			$mail->setSender($order_info['store_name']);
			$mail->setSubject($subject);
			$mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
			$mail->send();
		} */
	}
		
	public function getOrderHistories($order_id, $start = 0, $limit = 10) {
		$query = $this->db->query("SELECT oh.date_added, os.name AS status, oh.comment, oh.notify FROM " . DB_PREFIX . "order_history oh LEFT JOIN " . DB_PREFIX . "order_status os ON oh.order_status_id = os.order_status_id WHERE oh.order_id = '" . (int)$order_id . "' AND os.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY oh.date_added ASC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}
	
	public function getTotalOrderHistories($order_id) {
	  	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_id = '" . (int)$order_id . "'");

		return $query->row['total'];
	}	
		
	public function getTotalOrderHistoriesByOrderStatusId($order_status_id) {
	  	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_history WHERE order_status_id = '" . (int)$order_status_id . "'");

		return $query->row['total'];
	}	
}
?>