Location: PHPKode > projects > PhpBMS > phpbms/modules/bms/include/clients.php
<?php
/*
 $Rev: 254 $ | $LastChangedBy: brieb $
 $LastChangedDate: 2007-08-07 18:38:38 -0600 (Tue, 07 Aug 2007) $
 +-------------------------------------------------------------------------+
 | Copyright (c) 2004 - 2010, Kreotek LLC                                  |
 | All rights reserved.                                                    |
 +-------------------------------------------------------------------------+
 |                                                                         |
 | Redistribution and use in source and binary forms, with or without      |
 | modification, are permitted provided that the following conditions are  |
 | met:                                                                    |
 |                                                                         |
 | - Redistributions of source code must retain the above copyright        |
 |   notice, this list of conditions and the following disclaimer.         |
 |                                                                         |
 | - Redistributions in binary form must reproduce the above copyright     |
 |   notice, this list of conditions and the following disclaimer in the   |
 |   documentation and/or other materials provided with the distribution.  |
 |                                                                         |
 | - Neither the name of Kreotek LLC nor the names of its contributore may |
 |   be used to endorse or promote products derived from this software     |
 |   without specific prior written permission.                            |
 |                                                                         |
 | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS     |
 | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT       |
 | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A |
 | PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT      |
 | OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,   |
 | SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT        |
 | LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,   |
 | DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY   |
 | THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT     |
 | (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE   |
 | OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.    |
 |                                                                         |
 +-------------------------------------------------------------------------+
*/

if(class_exists("phpbmsTable")){
	class clients extends phpbmsTable{

		var $_availablePaymentMethodUUIDs = NULL;
		var $_availableShippingMethodUUIDs = NULL;
		var $_availableDiscountUUIDs = NULL;
		var $_availableTaxUUIDs = NULL;
		var $_availableUserUUIDs = NULL;

		function checkForInvoices($id){
			$querystatement="SELECT id FROM invoices WHERE clientid=".((int) $id);
			$queryresult = $this->db->query($querystatement);

			return !($this->db->numRows($queryresult)===0);
		}//end method


		// CLASS OVERRIDES ===================================================================
		// ===================================================================================

		function clients($db,$tabledefid = 0,$backurl = NULL){

			$this->phpbmsTable($db,$tabledefid,$backurl);

			if(!class_exists("addresstorecord")){
				include_once("modules/bms/include/addresses.php");
				include_once("modules/bms/include/addresstorecord.php");
			}//endif

			$this->address = new addresstorecord($db, "tbld:27b99bda-7bec-b152-8397-a3b09c74cb23");

		}//end function - init


		function getDefaults(){

			$therecord = parent::getDefaults();

			$therecord["type"] = DEFAULT_CLIENTTYPE;

			if($therecord["type"] == "client") {

				$therecord["becameclient"] = dateToString(mktime(), "SQL");
				$therecord["hascredit"] = DEFAULT_HASCREDIT;
				$therecord["creditlimit"] = DEFAULT_CREDITLIMIT;

			}//end if

			$therecord["webaddress"] = "http://";

			//now for the address information.
			$addressinfo = $this->address->getDefaults();

			unset($addressinfo["id"], $addressinfo["uuid"], $addressinfo["createdby"], $addressinfo["creationdate"], $addressinfo["modifiedby"], $addressinfo["modifieddate"]);

			$addressinfo["addressid"] = NULL;

			return array_merge($therecord, $addressinfo);

		}//end function - getDefaults


		function getRecord($id, $useUuid = false){

			$id = (!$useUuid)? (int) $id: mysql_real_escape_string($id);

			$therecord = parent::getRecord($id, $useUuid);

			if($therecord["id"]){
				//need to grab the address as well

				$querystatement = "
					SELECT
						id
					FROM
						addresstorecord
					WHERE
						`tabledefid` = 'tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083'
						AND `primary` = '1'
						AND `recordid` = '".$therecord["uuid"]."'
				";

				$queryresult = $this->db->query($querystatement);

				$addressinfo = $this->db->fetchArray($queryresult);

				if(count($addressinfo)) {

					$addressinfo = $this->address->getRecord($addressinfo["id"]);

				} else {

					$addressinfo = $this->address->getDefaults();
					$addressinfo["addressid"] = NULL;

				}//endif

				unset($addressinfo["id"], $addressinfo["uuid"], $addressinfo["notes"], $addressinfo["email"], $addressinfo["createdby"], $addressinfo["creationdate"], $addressinfo["modifiedby"], $addressinfo["modifieddate"]);

				$therecord = array_merge($therecord, $addressinfo);

			}//endif

			return $therecord;

		}//end function - getRecord


		function verifyVariables($variables){

			if(isset($variables["type"])){
				switch($variables["type"]){

					case "prospect":

						if(isset($variables["becameclient"])){
							if($variables["becameclient"] !== "" || $variables["becameclient"] !== NULL)
								$this->verifyErrors[] = "Records with `type` of 'prospect'
									must have the `becameclient` field kept blank.";
						}//end if

						if(isset($variables["hascredit"])){
							if($variables["hascredit"])
								$this->verifyErrors[] = "Records with `type` of 'prospect'
									must have the `hascredit` field kept blank or 0.";
						}//end if

						if(isset($variables["creditlimit"])){
							if($variables["creditlimit"])
								$this->verifyErrors[] = "Records with `type` of 'prospect'
									must have the `creditlimit` field kept blank or 0.";
						}//end if

					break;

					case "client":
						if(isset($variables["becameclient"])){
							//Possibly run through string to date functions
							if(!$variables["becameclient"])
								$this->verifyErrors[] = "Records with `type` of 'client'
									must have not have the `becameclient` field blank.";
						}else
							$this->verifyErrors[] = "Records with `type` of 'client'
								must set the `becameclient` field.";
					break;

					default:
						$this->verifyErrors[] = "The value of the `type` field is invalid.
							It must either be 'prospect' or 'client'.";
					break;

				}//end switch
			}else
				$this->verifyErrors[] = "The `type` field must be set.";

			////check for currency on credit limit (((real value) >= 0 ... non-negative)
			//if(isset($variables["creditlimit"]))
			//	if(!is_numeric($variables["creditlimit"]) && $variables["creditlimit"])
			//		$this->verifyErrors[] = "The `creditlimit` field must be a real number or equivalent to zero.";

			//----------------[ phone & email ]------------------------------------------------------
			/*//check valid email
			if(isset($variables["email"]))
				if( $variables["email"] !== NULL && $variables["email"] !== "" && !validateEmail($variables["email"]))
					$this->verifyErrors[] = "The `email` field must have a valid email or must be left blank.";

			//check valid homephone
			if(isset($variables["homephone"]))
				if( $variables["homephone"] !== NULL && $variables["homephone"] !== "" && !validatePhone($variables["homephone"]))
					$this->verifyErrors[] = "The `homephone` field must have a valid phone number (as set in configuration) or must be left blank.";

			//check valid workphone
			if(isset($variables["workphone"]))
				if( $variables["workphone"] !== NULL && $variables["workphone"] !== "" && !validatePhone($variables["workphone"]))
					$this->verifyErrors[] = "The `workphone` field must have a valid phone number (as set in configuration) or must be left blank.";

			//check valid mobilephone
			if(isset($variables["mobilephone"]))
				if( $variables["mobilephone"] !== NULL && $variables["mobilephone"] !== "" && !validatePhone($variables["mobilephone"]))
					$this->verifyErrors[] = "The `mobilephone` field must have a valid phone number (as set in configuration) or must be left blank.";

			//check valid fax
			if(isset($variables["fax"]))
				if( $variables["fax"] !== NULL && $variables["fax"] !== "" && !validatePhone($variables["fax"]))
					$this->verifyErrors[] = "The `fax` field must have a valid phone number (as set in configuration) or must be left blank.";

			//check valid otherphone
			if(isset($variables["otherphone"]))
				if( $variables["otherphone"] !== NULL && $variables["otherphone"] !== "" && !validatePhone($variables["otherphone"]))
					$this->verifyErrors[] = "The `otherphone` field must have a valid phone number (as set in configuration) or must be left blank.";
			*/
			//check bool on has credit
			if(isset($variables["hascredit"]))
				if($variables["hascredit"] && $variables["hascredit"] != 1)
					$this->verifyErrors[] = "The `hascredit` field must be a boolean (equivalent to 0 or exactly 1).";


			//----------------[ Order Defaults]------------------------------------------------------

			//Payement Method
			if(isset($variables["paymentmethodid"])){

				if($this->_availablePaymentMethodUUIDs === NULL){
					$this->_availablePaymentMethodUUIDs = $this->_loadUUIDList("paymentmethods");
					$this->_availablePaymentMethodUUIDs[] = ""; //for none
				}

				if(!in_array((string)$variables["paymentmethodid"], $this->_availablePaymentMethodUUIDs))
					$this->verifyErrors[] = "The `paymentmethodid` field does not give an existing/acceptable payment method uuid.";

			}//end if

			if(isset($variables["shippingmethodid"])){

				if($this->_availableShippingMethodUUIDs === NULL){
					$this->_availableShippingMethodUUIDs = $this->_loadUUIDList("shippingmethods");
					$this->_availableShippingMethodUUIDs[] = ""; // for none
				}//end if

				if(!in_array((string)$variables["shippingmethodid"], $this->_availableShippingMethodUUIDs))
					$this->verifyErrors[] = "The `shippingmethodid` field does not give an existing/acceptable shipping method uuid.";

			}//end if

			if(isset($variables["discountid"])){

				if($this->_availableDiscountUUIDs === NULL){
					$this->_availableDiscountUUIDs = $this->_loadUUIDList("discounts");
					$this->_availableDiscountUUIDs[] = ""; //for none
				}//end if

				if(!in_array((string)$variables["discountid"], $this->_availableDiscountUUIDs))
					$this->verifyErrors[] = "The `discount` field does not give an existing/acceptable discount uuid.";

			}//end if

			if(isset($variables["taxareaid"])){

				if($this->_availableTaxUUIDs === NULL){
					$this->_availableTaxUUIDs = $this->_loadUUIDList("tax");
					$this->_availableTaxUUIDs[] = ""; //for none
				}//end if

				if(!in_array((string)$variables["taxareaid"], $this->_availableTaxUUIDs))
					$this->verifyErrors[] = "The `taxareaid` field does not give an existing/acceptable tax uuid.";

			}//end if

			//---------------------[ end order defaults ]----------------------------------------

			//check sales manager id
			if(isset($variables["salesmanagerid"])){

				if($this->_availableUserUUIDs === NULL){
					$this->_availableUserUUIDs = $this->_loadUUIDList("users");
					$this->_availableUserUUIDs[] = "";
				}//end if

				if(!in_array((string)$variables["salesmanagerid"], $this->_availableUserUUIDs))
					$this->verifyErrors[] = "The `salesmanagerid` field does not give an existing/acceptable user uuid.";

			}//end if


			return parent::verifyVariables($variables);

		}//end method


		function prepareVariables($variables){

			if(isset($variables["webaddress"]))
				if ($variables["webaddress"]=="http://")
					$variables["webaddress"] = NULL;

			if(!isset($variables["type"]))
				$variables["type"] = "client";

			if($variables["type"] == "prospect"){

				$variables["hascredit"] = 0;
				$variables["creditlimit"] = 0;
				$variables["becameclient"] = NULL;
			}else{
				$variables["type"] = "client";
				if(!isset($variables["becameclient"]))
					$variables["becameclient"] = NULL;
				if(!$variables["becameclient"])
					$variables["becameclient"] = dateToString(mktime());
			}//end if

			return $variables;

		}//end method


		function updateRecord($variables, $modifiedby = NULL, $useUuid = false){

			$thereturn = parent::updateRecord($variables, $modifiedby, $useUuid);

			$variables["recordid"] = $variables["uuid"];//here to pass addresstorecord validation
			$variables["tabledefid"] = "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083";//here to pass addresstorecord validation
			//need to update the address

			$variables["id"] = $variables["addressid"];
			// don't want to blank out extra address information
			// if it was added later.
			unset($this->address->fields["email"]);
			unset($this->address->fields["phone"]);
			unset($this->address->fields["notes"]);
			unset($this->address->fields["title"]);
			unset($this->address->fields["createdby"]);
			unset($this->address->fields["creationdate"]);
			unset($this->address->fields["uuid"]);

			$variables = $this->address->prepareVariables($variables);
			$errorArray = $this->address->verifyVariables($variables);
			if(!count($errorArray)){
				$this->address->updateRecord($variables, $modifiedby);
			}else{
				foreach($errorArray as $error)
					$logError = new appError(-910, $error, "Address Verification Error");
			}//end if

			//restore the fields
			$this->address->getTableInfo();

			return $thereturn;

		}//end method - updateRecord


		function insertRecord($variables, $createdby = NULL, $overrideID = false, $replace = false, $useUuid = false){

			$newid = parent::insertRecord($variables, $createdby, $overrideID, $replace, $useUuid);

			if(is_array($newid))
				$newUuid = $newid["uuid"];
			else
				$newUuid = $variables["uuid"];

			//need to create the address and addresstorecord id
			// make sure we are not setting extra info
			unset($this->address->fields["email"]);
			unset($this->address->fields["phone"]);
			unset($this->address->fields["notes"]);
			unset($variables["id"]);// This breaks the import otherwise...needs further testing and possibly a better solution

			$variables["title"] = "Main Address";
			$variables["tabledefid"] = "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083";
			$variables["recordid"] = $newUuid;
			$variables["uuid"] = "";
			$variables["defaultshipto"] = 1;
			$variables["primary"] = 1;

			if($newUuid){ // temporary fix... may need to verify client id before hand... dunno

				$variables = $this->address->prepareVariables($variables);
				$errorArray = $this->address->verifyVariables($variables);

				if(!count($errorArray)){

					/**
					  *  If we are replacing the record, we need to cut the links
					  *  from the client to the old address record.
					  */
					if($replace){

						$querystatement = "
							DELETE FROM
								`addresstorecord`
							WHERE
								`recordid` = '".$newUuid."'
						";

						$queryresult = $this->db->query($querystatement);

					}//end if

					$this->address->insertRecord($variables, $createdby);

				} else {

					foreach($errorArray as $error)
						$logError = new appError(-910, $error, "Address Verification Error");

				}//end if

			}//end if

			//restore the fields
			$this->address->getTableInfo();

			return $newid;

		}//end method - insertRecord

		/*
		 * function api_searchByEmail
		 * @param array $requestData Array containing a key named "email"
		 * @param bool $returnUuid If true, returns result's uuid , if
		 * false, the id.
		 * @return array An array containing response information
		 * @returnf string 'type' The type of response (e.g. 'error' or 'result')
		 * @returnf string 'message' Message explaining the type / result
		 * @returnf array details Either the array of uuid / ids if no errors
		 * were encountered, or the original $requestData if there was an error
		 */

		function api_searchByEmail($requestData, $returnUuid = true) {

			/**
			  *  Check for required fields and return error if not there.
			  */
			if(!isset($requestData["email"])){
				$response["type"] = "error";
				$response["message"] = "Data does not contain a key of 'email'";
				$response["details"] = $requestData;
				return $response;
			}//end if

			/**
			  *  Do sql search
			  */
			$querystatement = "
				SELECT
					`id`,
					`uuid`
				FROM
					`clients`
				WHERE
					`email` = '".mysql_real_escape_string($requestData["email"])."'
			";

			$queryresult = $this->db->query($querystatement);

			/**
			  *  Construct return array
			  */
			$thereturn["type"] = "result";
			$thereturn["message"] = "The function api_searchByEmail has been run successfully.";
			$thereturn["details"] = array();
			while($therecord = $this->db->fetchArray($queryresult)){

				if($returnUuid)
					$thereturn["details"][] = $therecord["uuid"];
				else
					$thereturn["details"][] = $therecord["id"];

			}//end while

			return $thereturn;

		}//end function --api_serchByEmail--

		/*
		 * function api_searchByNameAndPostalcode
		 * @param array $requestData Array containing the keys 'postalcode',
		 * 'firstname', or 'lastname'.
		 * @param bool $returnUuid If true, returns result's uuid , if
		 * false, the id.
		 * @return array An array containing response information
		 * @returnf string 'type' The type of response (e.g. 'error' or 'result')
		 * @returnf string 'message' Message explaining the type / result
		 * @returnf array details Either the array of uuid / ids if no errors
		 * were encountered, or the original $requestData if there was an error
		 */

		function api_searchByNameAndPostalcode($requestData, $returnUuid = true) {

			/**
			  *  check for required fields
			  */
			$requiredArray = array(
				"postalcode",
				"firstname",
				"lastname"
			);

			$missingArray = array();

			foreach($requiredArray as $requiredField)
				if(!isset($requestData[$requiredField]))
					$missingArray[] = $requiredField;

			$count = count($missingArray);
			if($count){

				$response["type"] = "error";
				$response["details"] = $requestData;
				$response["message"] = "";

				$i=0;
				foreach($missingArray as $missingField){

					if(++$i == $count){
						if($response["message"])
							$response["message"] .= "and '".$missingField."'";
						else
							$response["message"] .= "'".$missingField."'";
					}else
						$response["message"] .= "'".$missingField."', ";

				}//end foreach

				$response["messsage"] = "Data does not contain the key(s): ".$response["message"];

				return $response;

			}//end if

			/**
			  *  do sql search
			  */
			$querystatement = "
				SELECT
					`clients`.`id`,
					`clients`.`uuid`
				FROM
					(
						(clients INNER JOIN addresstorecord on clients.uuid = addresstorecord.recordid AND addresstorecord.tabledefid='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary=1)
						INNER JOIN 	addresses ON  addresstorecord.addressid = addresses.uuid
					)
				WHERE
					`clients`.`firstname` = '".mysql_real_escape_string($requestData["firstname"])."'
					AND
					`clients`.`lastname` = '".mysql_real_escape_string($requestData["lastname"])."'
					AND
					`addresses`.`postalcode` = '".mysql_real_escape_string($requestData["postalcode"])."'
			";

			$queryresult = $this->db->query($querystatement);

			/**
			  *  report findings
			  */
			$thereturn["details"] = array();
			$thereturn["type"] = "result";
			$thereturn["message"] = "The function api_searchByNameAndPostalcode has been run successfully.";
			while($therecord = $this->db->fetchArray($queryresult)){

				if($returnUuid)
					$thereturn["details"][] = $therecord["uuid"];
				else
					$thereturn["details"][] = $therecord["id"];

			}//end while

			return $thereturn;

		}//end function --api_searchByNameAndPostalcode

		/*
		 * function api_searchByUsernameAndPassword
		 * @param array $requestData Array containing the keys 'username' and
		 * 'password'.
		 * @param bool $returnUuid If true, returns result's uuid , if
		 * false, the id.
		 * @return array An array containing response information
		 * @returnf string 'type' The type of response (e.g. 'error' or 'result')
		 * @returnf string 'message' Message explaining the type / result
		 * @returnf array details Either the array of uuid / ids if no errors
		 * were encountered, or the original $requestData if there was an error
		 */

		function api_searchByUsernameAndPassword($requestData, $returnUuid = true) {

			/**
			  *  check for required fields
			  */
			$requiredArray = array(
				"username",
				"password"
			);

			$missingArray = array();

			foreach($requiredArray as $requiredField)
				if(!isset($requestData[$requiredField]))
					$missingArray[] = $requiredField;

			$count = count($missingArray);
			if($count){

				$response["type"] = "error";
				$response["details"] = $requestData;
				$response["message"] = "";

				$i=0;
				foreach($missingArray as $missingField){

					if(++$i == $count){
						if($response["message"])
							$response["message"] .= "and '".$missingField."'";
						else
							$response["message"] .= "'".$missingField."'";
					}else
						$response["message"] .= "'".$missingField."', ";

				}//end foreach

				$response["messsage"] = "Data does not contain the key(s): ".$response["message"];

				return $response;

			}//end if

			/**
			  *  do sql search
			  */
			$querystatement = "
				SELECT
					`id`,
					`uuid`
				FROM
					`clients`
				WHERE
					`username` = '".mysql_real_escape_string($requestData["username"])."'
					AND
					`password` = '".mysql_real_escape_string($requestData["password"])."'
			";

			$queryresult = $this->db->query($querystatement);

			/**
			  *  report findings
			  */
			$thereturn["details"] = array();
			$thereturn["type"] = "result";
			$thereturn["message"] = "The function api_searchByUsernameAndPassword has been run successfully.";
			while($therecord = $this->db->fetchArray($queryresult)){

				if($returnUuid)
					$thereturn["details"][] = $therecord["uuid"];
				else
					$thereturn["details"][] = $threturn["id"];

			}//end while

			return $thereturn;

		}//end function --api_searchByUsernameAndPassword--

	}//end class

}//end if


if(class_exists("searchFunctions")){
	class clientsSearchFunctions extends searchFunctions{

                function consolidate_clients($useUuid = false){

                    $noOutput = true;
                    include("modules/bms/clients_consolidate.php");

                    $consolidator = new clientConsolidator($this->db);

                    $consolidator->showPicker($this->idsArray, $useUuid);
                    exit();


                }//end function consolidate_clients

		function mark_asclient($useUuid = false){

			if(!$useUuid)
				$whereclause = $this->buildWhereClause();
			else
				$whereclause = $this->buildWhereClause($this->maintable.".uuid");

			$querystatement = "
				UPDATE
					`clients`
				SET
					`clients`.`type`='client',
					`modifiedby`='".$_SESSION["userinfo"]["id"]."'
				WHERE
					(".$whereclause.")
			";

			$queryresult = $this->db->query($querystatement);

			$message = $this->buildStatusMessage();
			$message.=" converted to client.";
			return $message;
		}//end function --mark_asclient--


		//Stamp Comments Field with info packet sent
		function stamp_infosent($useUuid = false){

			if(!$useUuid)
				$whereclause = $this->buildWhereClause();
			else
				$whereclause = $this->buildWhereClause($this->maintable.".uuid");

			$querystatement = "
				UPDATE
					`clients`
				SET
					`clients`.`comments` = concat('Information Packet Sent', char(10), `clients`.`comments`),
					`clients`.`modifiedby` = '".$_SESSION["userinfo"]["id"]."',
					`clients`.`modifieddate` = NOW()
				WHERE
					(".$whereclause.")
					AND
					`clients`.`comments` IS NOT NULL
			";

			$queryresult = $this->db->query($querystatement);

			$affected = $this->db->affectedRows();

			$querystatement = "
				UPDATE
					`clients`
				SET
					`clients`.`comments` = 'Information Packet Sent',
					`clients`.`modifiedby` = '".$_SESSION["userinfo"]["id"]."',
					`clients`.`modifieddate` = NOW()
				WHERE
					(".$whereclause.")
					AND
					`clients`.`comments` IS NULL
			";

			$queryresult = $this->db->query($querystatement);

			$affected += $this->db->affectedRows();

			$message = $this->buildStatusMessage($affected);
			$message .= " marked as info packet sent.";
			return $message;

		}//end function --stamp_infosent


		//remove prospects
		function delete_prospects($useUuid = false){

			if(!$useUuid)
				$clientWhereClause = $this->buildWhereClause();
			else
				$clientWhereClause = $this->buildWhereClause($this->maintable.".uuid");

			$querystatement = "
				SELECT
					`id`,
					`uuid`
				FROM
					`clients`
				WHERE
					(".$clientWhereClause.")
					AND
					`clients`.`type` = 'prospect'";

			$queryresult = $this->db->query($querystatement);

			//build array of ids to be removed
			$deleteIDs = array();
			$deleteUuids = array();
			while($therecord = $this->db->fetchArray($queryresult)){
				array_push($deleteIDs, $therecord["id"]);
				array_push($deleteUuids, $therecord["uuid"]);
			}//end while

			if(count($deleteIDs)){

				$a2rWhere = $this->buildWhereClause("recordid", $deleteUuids);

				//First we get a list of all the addresses for the prospect
				$querystatement = "
					SELECT DISTINCT
						`addressid`
					FROM
						`addresstorecord`
					WHERE
						`tabledefid` = 'tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083'
						AND
						(".$a2rWhere.")";

				$a2rResult = $this->db->query($querystatement);

				$addressIDs = array();
				while($a2r = $this->db->fetchArray($a2rResult))
					array_push($addressIDs, $a2r["addressid"]);

				// delete all a2r records for prospect
				$deletestatement = "
					DELETE FROM
						`addresstorecord`
					WHERE
						`tabledefid` = 'tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083'
						AND (".$a2rWhere.")
				";

				$this->db->query($deletestatement);

				//now go get a list of orphaned addresses
				$querystatement = "
					SELECT
						`addresses`.`uuid`,
						`addresstorecord`.`id` AS `a2rid`
					FROM
						`addresses` LEFT JOIN `addresstorecord` ON `addresstorecord`.`addressid` = `addresses`.`uuid`
					WHERE
						".$this->buildWhereClause("`addresses`.`uuid`", $addressIDs);

				$addressResult = $this->db->query($querystatement);

				$addressIDs = array();
				while($address = $this->db->fetchArray($addressResult))
					if(!$address["a2rid"])
						array_push($addressIDs, $address["uuid"]);

				if(count($addressIDs)){

					//delete orphaned addresses
					$deletestatement = "
						DELETE FROM
							`addresses`
						WHERE
							".$this->buildWhereClause("`addresses`.`uuid`", $addressIDs);

					$this->db->query($deletestatement);

				}//endif - addressids

				//next get any quotes that we may have to delete
				$invoiceWhereClause = $this->buildWhereClause("clientid", $deleteUuids);
				$invoicestatement = "
					SELECT
						`invoices`.`id`,
						`invoices`.`uuid`
					FROM
						`invoices`
					WHERE
						".$invoiceWhereClause;

				$invoiceresult = $this->db->query($invoicestatement);

				//build invoice id array
				$invoiceids = array();
				$invoiceuuids = array();
				while($therecord = $this->db->fetchArray($invoiceresult)){
					array_push($invoiceids, $therecord["id"]);
					array_push($invoiceuuids, $therecord["uuid"]);
				}//end while

				if(count($invoiceids)) {
					$invoiceWhereClause = $this->buildWhereClause("invoices.id", $invoiceids);

					$lineitemWhereClause = $this->buildWhereClause("invoiceid", $invoiceids);

					$statushistoryWhereClause = $this->buildWhereClause("invoiceid", $invoiceuuids);

					$lineItemDeleteStatement = "
						DELETE FROM
							`lineitems`
						WHERE
							".$lineitemWhereClause;

					$queryresult = $this->db->query($lineItemDeleteStatement);

					$statushistoryDeleteStatement = "
						DELETE FROM
							`invoicestatushistory`
						WHERE
							".$statushistoryWhereClause;

					$queryresult = $this->db->query($statushistoryDeleteStatement);

					$invoiceDeleteStatement = "
						DELETE FROM
							`invoices`
						WHERE
							".$invoiceWhereClause;

					$queryresult = $this->db->query($invoiceDeleteStatement);

				}//end if

				//lastly we remove the prospect record
				$delWhere = $this->buildWhereClause("clients.id", $deleteIDs);

				$deletestatement = "
					DELETE FROM
						clients
					WHERE
						".$delWhere;

				$this->db->query($deletestatement);

			}//endif - count deleteIDS


			$message = $this->buildStatusMessage(count($deleteIDs));
			$message.=" deleted.";
			return $message;

		}// end method - delete_prospects


		function massEmail(){
			if(DEMO_ENABLED != "true"){
				$_SESSION["emailids"]= $this->idsArray;
				goURL("modules/mailchimp/manual_list_sync.php");
			} else {
				return "mass e-mail feature disabled in demo";
			}
		}


	}//end class
}//end if

if(class_exists("phpbmsImport")){
	class clientsImport extends phpbmsImport{


		function clientsImport($table, $importType = "csv"){

			if($importType == "sugarcrm"){

				$importType = "csv";
				$switchedFrom = "sugarcrm";

			}//end if

			parent::phpbmsImport($table, $importType);

			if(isset($switchedFrom))
				$this->importType = $switchedFrom;

		}//end method --clientsImport--


		function _parseFromData($data){

			if($this->importType == "sugarcrm"){

				$this->importType = "csv";
				$switchedFrom = "sugarcrm";

			}//end if

			$thereturn = parent::_parseFromData($data);

			if(isset($switchedFrom))
				$this->importType = $switchedFrom;

			return $thereturn;

		}//end method --_parseFromFile--


		function _formatSugarVariables($rows, $titles){

			//Replace the titles with valid ones
			//(At the moment we only really need
			//the correct count, but, it seems
			//logical to correct the titles in case
			//they are needed in the future)
			$newTitles = array();
			foreach($titles as $index => $name){

				switch($name){

					case "name":
						$newTitles[] = "company";
					break;

					case "date_entered":
						$newTitles[] = "becameclient";
					break;

					case "description":
						$newTitles[] = "comments";
					break;

					case "deleted":
						$newTitles[] = "inactive";
					break;

					case "account_type":
						$newTitles[] = "type";
					break;

					case "industry":
						$newTitles[] = "category";
					break;

					case "phone_fax":
						$newTitles[] = "fax";
					break;

					case "billing_address_street":
						$newTitles[] = "address1";
					break;

					case "billing_address_city":
						$newTitles[] = "city";
					break;

					case "billing_address_state":
						$newTitles[] = "state";
					break;

					case "billing_address_postalcode":
						$newTitles[] = "postalcode";
					break;

					case "billing_address_country":
						$newTitles[] = "country";
					break;

					case "phone_office":
						$newTitles[] = "workphone";
					break;

					case "phone_alternate":
						$newTitles[] = "otherphone";
					break;

					case "website":
						$newTitles[] = "webaddress";
					break;

					case "shipping_address_street":
						$newTitles[] = "shipaddress1";
					break;

					case "shipping_address_city":
						$newTitles[] = "shipcity";
					break;

					case "shipping_address_state":
						$newTitles[] = "shipstate";
					break;

					case "shipping_address_postalcode":
						$newTitles[] = "shippostalcode";
					break;

					case "shipping_address_country":
						$newTitles[] = "shipcountry";
					break;

				}//end switch

			}//end foreach


			$newRows = array();
			foreach($rows as $rowData){

				$newRowData = array();
				$addComments = "";
				foreach($rowData as $name => $data){

					switch($name){

						case "name":
							$newRowData["company"] = trim($data);
						break;

						case "date_entered":
							$newRowData["becameclient"] = trim($data);
						break;

						case "description":
							$newRowData["comments"] = trim($data);
						break;

						case "deleted":
							$newRowData["inactive"] = trim($data);
						break;

						case "industry":
							$newRowData["category"] = trim($data);
						break;

						case "account_type":
							$newRowData["type"] = trim($data);
						break;

						case "phone_fax":
							$newRowData["fax"] = trim($data);
						break;

						case "billing_address_street":
							$newRowData["address1"] = trim($data);
						break;

						case "billing_address_city":
							$newRowData["city"] = trim($data);
						break;

						case "billing_address_state":
							$newRowData["state"] = trim($data);
						break;

						case "billing_address_postalcode":
							$newRowData["postalcode"] = trim($data);
						break;

						case "billing_address_country":
							$newRowData["country"] = trim($data);
						break;

						case "phone_office":
							$newRowData["workphone"] = trim($data);
						break;

						case "phone_alternate":
							$newRowData["otherphone"] = trim($data);
						break;

						case "website":
							if(strpos(trim($data), "http://") !== 0)
								$newRowData["webaddress"] = "http://".trim($data);
							else
								$newRowData["webaddress"] = trim($data);
						break;

						case "shipping_address_street":
							$newRowData["shipaddress1"] = trim($data);
						break;

						case "shipping_address_city":
							$newRowData["shipcity"] = trim($data);
						break;

						case "shipping_address_state":
							$newRowData["shipstate"] = trim($data);
						break;

						case "shipping_address_postalcode":
							$newRowData["shippostalcode"] = trim($data);
						break;

						case "shipping_address_country":
							$newRowData["shipcountry"] = trim($data);
						break;

						case "annual_revenue":
						case "rating":
						case "ownership":
						case "employees":
						case "ticker_symbol":
						case "id":
							if($data)
								$addComments .= "\n".str_replace("_"," ",$name).": ".trim($data);
						break;

					}//end switch

				}//end foreach

				if($newRowData["type"] == "prospect")
					$newRowData["becameclient"] = NULL;
				else
					$newRowData["type"] = "client";

				$newRowData["comments"] .= $addComments;
				$newRows[] = $newRowData;

			}//end foreach

			$thereturn["rows"] = $newRows;
			$thereturn["titles"] = $newTitles;
			return $thereturn;

		}//end method --_formatSugarvariables--


		function importRecords($rows, $titles){

			switch($this->importType){

				case "sugarcrm":
					$thereturn = $this->_formatSugarVariables($rows, $titles);
					$rows = $thereturn["rows"];
					$titles = $thereturn["titles"];

				case "csv":
					//count total fieldnames (top row of csv document)
					$fieldNum = count($titles);

					//the file starts at line number 1, but since line 1 is
					//supposed to be the fieldnames in the table(s), the lines
					//being insereted start @ 2.
					$rowNum = 2;

					//get the data one row at a time
					foreach($rows as $rowData){

						$theid = 0; // set for when verifification does not pass
						$verify = array(); //set for when number of field rows does not match number of titles

						//trim off leading/trailing spaces
						$trimmedRowData = array();
						foreach($rowData as $name => $data)
							$trimmedRowData[$name] = trim($data);

						//check to see if number of fieldnames is consistent for each row
						$rowFieldNum = count($trimmedRowData);

						//if valid, insert, if not, log error and don't insert.
						if($rowFieldNum == $fieldNum){
							$verify = $this->table->verifyVariables($trimmedRowData);
							if(!count($verify)){
								$createdby = NULL;
								$overrideID = true;
								$replace = false;
								if(!isset($trimmedRowData["uuid"])){
									$useUuid = true;
									$thereturn = $this->table->insertRecord($trimmedRowData, $createdby, $overrideID, $replace, $useUuid);
									$theid = $thereturn["id"];
								}else{
									$useUuid = false;
									$thereturn = $this->table->insertRecord($trimmedRowData, $createdby, $overrideID, $replace, $useUuid);
									$theid = $thereturn;
								}//end if
							}//end if
								
						}else
							$this->error .= '<li> incorrect amount of fields for line number '.$rowNum.'.</li>';

						if($theid){
							//keep track of the ids in the transaction to be able to select them
							//for preview purposes
							$this->transactionIDs[] = $theid;

							//get first id to correct auto increment
							if(!$this->revertID)
								$this->revertID = $theid;

							$theuuid = getUuid($this->table->db, "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083", (int) $theid);
							
							//If it is a sugarcrm import, insert the shipping address as well
							$addressVerify = array();
							if($this->importType == "sugarcrm"){


								$variables = array();
								if($trimmedRowData["shipaddress1"]) $variables["address1"] = $trimmedRowData["shipaddress1"];
								if($trimmedRowData["shipcity"]) $variables["city"] = $trimmedRowData["shipcity"];
								if($trimmedRowData["shipstate"]) $variables["state"] = $trimmedRowData["shipstate"];
								if($trimmedRowData["shipcountry"]) $variables["country"] = $trimmedRowData["shipcountry"];

								//check to see if there is a shipping address
								if(count($variables)){

									//If there is a shipping address, we need to make any others'
									//`defaultshipto` to 0

									$querystatement = "
										UPDATE
											`addresstorecord`
										SET
											`addresstorecord`.`defaultshipto` = '0'
										WHERE
											`addresstorecord`.`recordid` = '".mysql_real_escape_string($theuuid)."'
											AND
											`addresstorecord`.`tabledefid` = 'tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083';
										";

									$this->table->db->query($querystatement);

									$variables["title"] = "Main Shipping Address";
									$variables["tabledefid"] = "tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083";
									$variables["recordid"] = $theuuid;
									$variables["defaultshipto"] = 1;
									$variables["primary"] = 0;
									$variables["existingaddressid"] = false;
									$variables["uuid"] = uuid($this->table->address->prefix);

									$addressVerify = $this->table->address->verifyVariables($variables);//verify address
									if(!count($addressVerify))//check for errors
										$this->table->address->insertRecord($variables);//insert if no errors

								}//end if

							}//end if
						}else
							$this->error .= '<li> failed insert for line number '.$rowNum.'.</li>';

							foreach($verify as $error)//log verify errors for display
								$this->error .= '<li class="subError">'.$error.'</li>';

							if(isset($addressVerify))
									foreach($addressVerify as $error)//log address verify errors for display
										$this->error .= '<li class="subError">'.$error.'</li>';

						$rowNum++;

					}//end foreach
				break;

			}//end switch

		}//end method --importRecords--


		function _getTransactionData(){

			$inStatement = "";
			foreach($this->transactionIDs as $theid)
				$inStatement .= $theid.",";

			if($inStatement)
				$inStatement = substr($inStatement, 0, -1);
			else
				$inStatement = "0";

			//There are two cases to minimize joins for csv files
			switch($this->importType){

				case "sugarcrm":
					$querystatement = "
						SELECT
							`clients`.*,
							`addresses1`.`address1` AS `mainaddress1`,
							`addresses1`.`address2` AS `mainaddress2`,
							`addresses1`.`city` AS `maincity`,
							`addresses1`.`state` AS `mainstate`,
							`addresses1`.`postalcode` AS `mainpostalcode`,
							`addresses1`.`country` AS `maincountry`,
							`addresses1`.`phone` AS `mainphone`,
							`addresses1`.`email` AS `mainemail`,
							`addresses1`.`custom1` AS `maincustom1`,
							`addresses1`.`custom2` AS `maincustom2`,
							`addresses1`.`custom3` AS `maincustom3`,
							`addresses1`.`custom4` AS `maincustom4`,
							`addresses1`.`custom5` AS `maincustom5`,
							`addresses1`.`custom6` AS `maincustom6`,
							`addresses1`.`custom7` AS `maincustom7`,
							`addresses1`.`custom8` AS `maincustom8`,
							`addresses2`.`address1` AS `shipaddress1`,
							`addresses2`.`address2` AS `shipaddress2`,
							`addresses2`.`city` AS `shipcity`,
							`addresses2`.`state` AS `shipstate`,
							`addresses2`.`postalcode` AS `shippostalcode`,
							`addresses2`.`country` AS `shipcountry`,
							`addresses2`.`phone` AS `shipphone`,
							`addresses2`.`email` AS `shipemail`,
							`addresses2`.`custom1` AS `shipcustom1`,
							`addresses2`.`custom2` AS `shipcustom2`,
							`addresses2`.`custom3` AS `shipcustom3`,
							`addresses2`.`custom4` AS `shipcustom4`,
							`addresses2`.`custom5` AS `shipcustom5`,
							`addresses2`.`custom6` AS `shipcustom6`,
							`addresses2`.`custom7` AS `shipcustom7`,
							`addresses2`.`custom8` AS `shipcustom8`
						FROM
							((((clients INNER JOIN addresstorecord AS `addresstorecord1` ON clients.uuid = addresstorecord1.recordid AND addresstorecord1.tabledefid='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord1.primary=1) INNER JOIN addresses AS addresses1 ON  addresstorecord1.addressid = addresses1.uuid)LEFT JOIN addresstorecord AS `addresstorecord2` on clients.uuid = addresstorecord2.recordid AND addresstorecord2.tabledefid='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord2.primary=0 AND addresstorecord2.defaultshipto=1) LEFT JOIN addresses AS addresses2 ON  addresstorecord2.addressid = addresses2.uuid)
						WHERE
							`clients`.`id` IN (".$inStatement.")
						ORDER BY
							`clients`.`id` ASC;
						";
				break;

				case "csv":
					$querystatement = "
						SELECT
							`clients`.*,
							`addresses`.`address1`,
							`addresses`.`address2`,
							`addresses`.`city`,
							`addresses`.`state`,
							`addresses`.`postalcode`,
							`addresses`.`country`,
							`addresses`.`phone`
						FROM
							((clients INNER JOIN addresstorecord ON clients.uuid = addresstorecord.recordid AND addresstorecord.tabledefid='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary='1') INNER JOIN addresses ON  addresstorecord.addressid = addresses.uuid)
						WHERE
							`clients`.`id` IN (".$inStatement.")
						ORDER BY
							`clients`.`id` ASC;
						";
				break;

			}//end switch

			$queryresult = $this->table->db->query($querystatement);

			while($therecord = $this->table->db->fetchArray($queryresult))
				$this->transactionRecords[] = $therecord;


		}//end method --_gettransactionData--


		function displayTransaction($recordsArray, $fieldsArray){
			
			if(count($recordsArray) && count($fieldsArray)){

				//Need to include addresses in the fieldArray

				//list of values that should not be displayed
				$removalArray = array("id", "modifiedby", "modifieddate", "createdby", "creationdate", "notes", "title", "shiptoname", "uuid");
				//gets the address table's columnnames/information (fields)
				$addressArray = $this->table->address->fields;

				//gets rid of the values that should not be displayed
				foreach($removalArray as $removalField){

					if(isset($addressArray[$removalField])){

						unset($addressArray[$removalField]);

					}//end if

				}//end foreach

				//get rid of stuff that should only be in addresses but is present in clients
				foreach($addressArray as $removalField => $junk){

					if(isset($fieldsArray[$removalField])){

						unset($fieldsArray[$removalField]);

					}//end if

				}//end foreach

				//need to get two sets of address fields, one named main* and the other ship*.
				if($this->importType == "sugarcrm"){

					foreach($addressArray as $field => $junk){

						$mainAddressArray["main".$field] = $junk;
						$shipAddressArray["ship".$field] = $junk;

					}//end foreach

					$addressArray = $mainAddressArray + $shipAddressArray;

				}//end if

				$fieldsArray = $fieldsArray + $addressArray;

				parent::displayTransaction($recordsArray, $fieldsArray);

			}//end if

		}//end method --displayTransaction--

	}//end class --clientsImport--
}//end if
?>
Return current item: PhpBMS