<?php
defined('BASEPATH') OR exit('No direct script access allowed');
defined('DIR_SEPARATOR') || define('DIR_SEPARATOR', '/');

class Accountmain extends CI_Model {
	
	public function __construct() { parent::__construct(); }
	
	public function getAccount($user) {
		$sql = "SELECT fldid, email, username, imageFile FROM tbl_usermaster WHERE (is_delete IS NULL OR is_delete = 0) AND fldid = ".$user;
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getAccountByEmail($email) {
		$sql = "SELECT ID, fullname FROM tbl_usermaster WHERE (is_delete IS NULL OR is_delete = 0) AND LOWER(email) = '".(strtolower(trim($email)))."'";
		$q = $this->db->query($sql);		
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
		
	public function getMyBillingInfo($user) {
		$sql = "SELECT ID, user, phoneNo, faxNo, email, address, city, postcode, state, country
						FROM user_billing_info
						WHERE (is_delete IS NULL OR is_delete = 0)
						AND user = ".$user;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
	}
	
	public function getUserOrganizationDetail($user) {
		$sql = "SELECT ID, user, name, regNo, phoneNo, faxNo, email, website, address, city, postcode, state, country
						FROM user_organization_main
						WHERE (is_delete IS NULL OR is_delete = 0)
						AND user = ".$user;
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL;
		
	}
	
	public function checkAccountExists($id) {
		$sql = "SELECT COUNT(1) CT FROM tbl_usermaster WHERE fldid = ".$id." AND (is_delete IS NULL OR is_delete = 0)";
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function checkEmailExists($email, $id = 0) {
		$sql = "SELECT COUNT(1) CT FROM tbl_usermaster WHERE LOWER(email) = '".(strtolower($email))."' AND (is_delete IS NULL OR is_delete = 0)";
		$sql .= ($id) ? " AND fldid != ".$id : NULL;
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE;
	}
	
	public function getOldPassword($id) {
		$sql = "SELECT password FROM tbl_usermaster WHERE fldid = ".$id." AND (is_delete IS NULL OR is_delete = 0)";
		$q = $this->db->query($sql);
		
		return ($q && ($r = $q->result()) && $r[0]) ? $r[0]->password : NULL;
	}
	
	public function deleteAccount($id) {
		$sql = "UPDATE user SET is_delete = 1 WHERE ID = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function saveAccount($data, $id) {
		$sql = "SELECT COUNT(1) CT FROM tbl_usermaster WHERE fldid = ".$id;
		$q = $this->db->query($sql);
		
		if($ct = ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE) {
			$sql = "UPDATE tbl_usermaster
								SET
										username = '".(addslashes(trim($data['username'])))."',
										email = '".(addslashes(trim($data['email'])))."',
										modified_dt = NOW()
								WHERE fldid = ".$id;
			return ($this->db->query($sql)) ? TRUE : FALSE;
		} else {
			return FALSE;
		}
	}
	
	public function savePassword($password, $id) {
		$sql = "UPDATE tbl_usermaster SET password = '".(md5($password))."' WHERE fldid = ".$id;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function saveBilling($data, $user) {
		$sql = "SELECT COUNT(1) CT FROM user_billing_info WHERE user = ".$user;
		$q = $this->db->query($sql);
		
		if($ct = ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE) {
			$sql = "UPDATE user_billing_info
								SET
										phoneNo = '".(addslashes(trim($data['phoneNo'])))."',
										faxNo = '".(addslashes(trim($data['faxNo'])))."',
										email = '".(addslashes(trim($data['email'])))."',
										address = '".(addslashes(trim($data['address'])))."',
										postcode = '".(addslashes(trim($data['postcode'])))."',
										city = '".(addslashes(trim($data['city'])))."',
										country = '".(addslashes(trim($data['country'])))."',
										state = '".(addslashes(trim($data['state'])))."',
										updatedTS = NOW(),
										updatedBy = ".$user."
								WHERE user = ".$user;
			return ($this->db->query($sql)) ? TRUE : FALSE;
		} else {
			$sql = "INSERT INTO user_billing_info (
								user, phoneNo, faxNo, email, address, postcode, city, country, state, createdTS, createdBy, updatedTS, updatedBy
							) VALUES (
								".$user.",
								'".(addslashes(trim($data['phoneNo'])))."',
								'".(addslashes(trim($data['faxNo'])))."',
								'".(addslashes(trim($data['email'])))."',
								'".(addslashes(trim($data['address'])))."',
								'".(addslashes(trim($data['postcode'])))."',
								'".(addslashes(trim($data['city'])))."',
								'".(addslashes(trim($data['country'])))."',
								'".(addslashes(trim($data['state'])))."',
								NOW(),
								".$user.",
								NOW(),
								".$user."
							)";
			return ($this->db->query($sql)) ? TRUE : FALSE;
		}
	}
	
	public function saveOrganization($data, $user) {
		$sql = "SELECT COUNT(1) CT FROM user_organization_main WHERE user = ".$user;
		$q = $this->db->query($sql);
		
		if($ct = ($q && ($r = $q->result()) && $r[0] && $r[0]->CT) ? TRUE : FALSE) {
			$sql = "UPDATE user_organization_main
								SET
										name = '".(addslashes(trim($data['name'])))."',
										regNo = '".(addslashes(trim($data['regNo'])))."',
										phoneNo = '".(addslashes(trim($data['phoneNo'])))."',
										faxNo = '".(addslashes(trim($data['faxNo'])))."',
										website = '".(addslashes(trim($data['website'])))."',
										email = '".(addslashes(trim($data['email'])))."',
										address = '".(addslashes(trim($data['address'])))."',
										postcode = '".(addslashes(trim($data['postcode'])))."',
										city = '".(addslashes(trim($data['city'])))."',
										country = '".(addslashes(trim($data['country'])))."',
										state = '".(addslashes(trim($data['state'])))."',
										updatedTS = NOW(),
										updatedBy = ".$user."
								WHERE user = ".$user;
			return ($this->db->query($sql)) ? TRUE : FALSE;
		} else {
			$sql = "INSERT INTO user_organization_main (
								user, name, regNo, phoneNo, faxNo,
								website, email, address, postcode,
								city, country, state,
								createdTS, updatedTS, createdBy, updatedBy
							) VALUES (
								".$user.",
								'".(addslashes(trim($data['name'])))."',
								'".(addslashes(trim($data['regNo'])))."',
								'".(addslashes(trim($data['phoneNo'])))."',
								'".(addslashes(trim($data['faxNo'])))."',
								'".(addslashes(trim($data['website'])))."',
								'".(addslashes(trim($data['email'])))."',
								'".(addslashes(trim($data['address'])))."',
								'".(addslashes(trim($data['postcode'])))."',
								'".(addslashes(trim($data['city'])))."',
								'".(addslashes(trim($data['country'])))."',
								'".(addslashes(trim($data['state'])))."',
								NOW(),
								NOW(),
								".$user.",
								".$user."
							)";
			return ($this->db->query($sql)) ? TRUE : FALSE;
		}
	}
	
	public function saveResetPassword($user, $code) {		
		$sql = "INSERT INTO password_reset ( user, code, createdDT ) VALUES ( ".$user.", '".$code."', NOW() )";
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function getResetInfo($code) {
		$sql = "SELECT user, createdDT FROM password_reset WHERE code = '".$code."'";
		$q = $this->db->query($sql);
		$return = NULL;
		if($result = ($q && ($r = $q->result()) && $r[0]) ? $this->ObjToArray($r[0]) : NULL) {
			$return = $result;
			$return['elapsed'] = $this->getTimeElapsed($result['createdDT']);
		}
		return $return;
	}
	
	public function processReset($user, $code) {
		$sql = "UPDATE password_reset SET processed = 1 WHERE user = ".$user." AND code = '".$code."'";
		return ($this->db->query($ql)) ? TRUE : FALSE;
	}
	
	public function saveImageFile($user, $file) {
		$sql = "UPDATE tbl_usermaster SET imageFile = '".($file)."' WHERE fldid = ".$user;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	public function resetImageFile($user) {
		$sql = "UPDATE tbl_usermaster SET imageFile = NULL WHERE fldid = ".$user;
		return ($this->db->query($sql)) ? TRUE : FALSE;
	}
	
	/**
	* HELPERS
	*/
	public function getAllCountries() {
		$sql = "SELECT fldid ID, fldcountry name FROM _ref_country ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
	}
	
	public function getAllStates($country = NULL) {
		$sql = "SELECT fldid ID, fldname name FROM _ref_state WHERE 1 = 1 ".(($country) ? " AND fldregion_id = ".$country : NULL)." ORDER BY name ASC";
		$q = $this->db->query($sql);
		return ($q && ($r = $q->result())) ? $this->ObjToArray($r) : NULL;
	}
	
	public function ObjToArray($item) { return json_decode(json_encode($item),true); }
	
	private function getTimeElapsed($datetime) {
		$now = new DateTime;
		$ago = new DateTime($datetime);
		$diff = $now->diff($ago);

		$diff->w = floor($diff->d / 7);
		$diff->d -= $diff->w * 7;

		$string = array(
				'y' => 'year',
				'm' => 'month',
				'w' => 'week',
				'd' => 'day',
				'h' => 'hour',
				'i' => 'minute',
				's' => 'second',
		);
		
		foreach ($string as $k => &$v) {
			$v = ($diff->$k) ? $diff->$k : 0;
		}
		
		return $string ? $string : NULL;
	}
		
	
}































