Functions.php

	static function getPickListValuesFromTableForRole($tablename, $roleid) {
		global $adb;
        $tablename = Vtiger_Util_Helper::validateStringForSql($tablename);
		$query = "select $tablename from vtiger_$tablename inner join vtiger_role2picklist on vtiger_role2picklist.picklistvalueid = vtiger_$tablename.picklist_valueid where roleid=? and picklistid in (select picklistid from vtiger_picklist) order by sortorderid";
		$result = $adb->pquery($query, array($roleid));
		$fldVal = Array();
		while ($row = $adb->fetch_array($result)) {
			$fldVal [] = $row[$tablename];
		}
		return $fldVal;
	}

	static function getActivityType($id) {
		global $adb;
		$query = "select activitytype from vtiger_activity where activityid=?";
		$res = $adb->pquery($query, array($id));
		$activity_type = $adb->query_result($res, 0, "activitytype");
		return $activity_type;
	}

	static function getInvoiceStatus($id) {
		global $adb;
		$result = $adb->pquery("SELECT invoicestatus FROM vtiger_invoice where invoiceid=?", array($id));
		$invoiceStatus = $adb->query_result($result,0,'invoicestatus');
		return $invoiceStatus;
	}

	static function mkCountQuery($query) {
		// Remove all the \n, \r and white spaces to keep the space between the words consistent.
		// This is required for proper pattern matching for words like ' FROM ', 'ORDER BY', 'GROUP BY' as they depend on the spaces between the words.
		$query = preg_replace("/[\n\r\s]+/"," ",$query);

		//Strip of the current SELECT fields and replace them by "select count(*) as count"
		// Space across FROM has to be retained here so that we do not have a clash with string "from" found in select clause
		$query = "SELECT count(*) AS count ".substr($query, stripos($query,' FROM '),strlen($query));

		//Strip of any "GROUP BY" clause
		if(stripos($query,'GROUP BY') > 0)
		$query = substr($query, 0, stripos($query,'GROUP BY'));

		//Strip of any "ORDER BY" clause
		if(stripos($query,'ORDER BY') > 0)
		$query = substr($query, 0, stripos($query,'ORDER BY'));

		return $query;
	}

	/** Function to get unitprice for a given product id
	* @param $productid -- product id :: Type integer
	* @returns $up -- up :: Type string
	*/
	static function getUnitPrice($productid, $module='Products') {
		$adb = PearDatabase::getInstance();
		if($module == 'Services') {
			$query = "select unit_price from vtiger_service where serviceid=?";
		} else {
			$query = "select unit_price from vtiger_products where productid=?";
		}
		$result = $adb->pquery($query, array($productid));
		$unitpice = $adb->query_result($result,0,'unit_price');
		return $unitpice;
	}

	/**
	* Function to fetch the list of vtiger_groups from group vtiger_table
	* Takes no value as input
	* returns the query result set object
	*/
	static function get_group_options() {
        global $adb, $noof_group_rows;
		$sql = "select groupname,groupid from vtiger_groups";
		$result = $adb->pquery($sql, array());
		$noof_group_rows = $adb->num_rows($result);
		return $result;
	}

	/**
	* Function to determine mime type of file.
	* Compatible with mime_magic or fileinfo php extension.
	*/
	static function mime_content_type($filename) {
		$type = null;
		if (function_exists('mime_content_type')) {
			$type = mime_content_type($filename);
		} else if (function_exists('finfo_open')) {
			$finfo = finfo_open(FILEINFO_MIME_TYPE);
			$type = finfo_file($finfo, $filename);
			finfo_close($finfo);
		} else {
			throw new Exception('mime_magic or fileinfo extension required.');
		}
		return $type;
	}

	 /**
	 * Check the file MIME Type
	 * @param $targetFile  Filepath to validate
	 * @param  $claimedMime Array of bad file extenstions
	 */
	static function verifyClaimedMIME($targetFile,$claimedMime) {
		$fileMimeContentType= self::mime_content_type($targetFile);
		if (in_array(strtolower($fileMimeContentType), $claimedMime)) {
			return false;
		}
		return true;
	}

	/*
	 * Function to generate encrypted password.
	 */
	static function generateEncryptedPassword($password, $mode='') {
		if ($mode == '') {
			$mode = (version_compare(PHP_VERSION, '5.5.0') >= 0)? 'PHASH' : 'CRYPT';
		}

		if ($mode == 'PHASH') return password_hash($password, PASSWORD_DEFAULT);

		if ($mode == 'MD5') return md5($password);

		if ($mode == 'CRYPT') {
			$salt = null;
			if (function_exists('password_hash')) { // php 5.5+
				$salt = password_hash($password, PASSWORD_DEFAULT);
			} else {
				$salt = '$2y$11$'.str_replace("+",".",substr(base64_encode(openssl_random_pseudo_bytes(17)),0,22));
			}
			return crypt($password, $salt);
		}

		throw new Exception('Invalid encryption mode: '.$mode);
	}

	/*
	 * Function to compare encrypted password.
	 */
	static function compareEncryptedPassword($plainText, $encryptedPassword, $mode='CRYPT') {
		$reEncryptedPassword = null;
		switch ($mode) {
			case 'PHASH': return password_verify($plainText, $encryptedPassword);
			case 'CRYPT': $reEncryptedPassword = crypt($plainText, $encryptedPassword); break;
			case 'MD5'  : $reEncryptedPassword = md5($plainText);	break;
			default     : $reEncryptedPassword = $plainText;		break;
		}
		return ($reEncryptedPassword == $encryptedPassword);
	}

	/**
	* Function to get modules which has line items
	* @returns array of modules
	*/
	static function getLineItemFieldModules() {
		return array('Invoice', 'Quotes', 'PurchaseOrder', 'SalesOrder', 'Products', 'Services');
	}

	/**
	 * Function to encode an array to json with all the options
	 * @param <Array> $array
	 * @return <sting> Json String
	 */
	static function jsonEncode($array) {
		return json_encode($array, JSON_HEX_TAG | JSON_HEX_APOS | JSON_HEX_QUOT | JSON_HEX_AMP | JSON_UNESCAPED_UNICODE);
	}

	/**
	 * Function to get the special date conditions
	 * @return <array> array of special date conditions
	 */
	static function getSpecialDateConditions() {
		return array('lessthandaysago', 'morethandaysago', 'inlessthan', 'inmorethan', 'daysago', 'dayslater');
	}

	/**
	 * Function to get the special time conditions
	 * @return <array> array of special time conditions
	 */
	static function getSpecialTimeConditions() {
		return array('lessthanhoursbefore', 'lessthanhourslater', 'morethanhoursbefore', 'morethanhourslater');
	}

	/**
	 * Function to get the special date and time conditions
	 * @return <array> array of special date and time conditions
	 */
	static function getSpecialDateTimeCondtions() {
		return array_merge(self::getSpecialDateConditions(), self::getSpecialTimeConditions());
	}

	/**
	 * Function to get track email image contents
	 * @param $recordId Email record Id
	 * @param $parentId Parent record Id of Email record
	 * @return string returns track image contents
	 */
	static function getTrackImageContent($recordId, $parentId) {
            $params = array();
            $params['record'] = $recordId;
            $params['parentId'] = $parentId;
            $params['method'] = 'open';

            $trackURL = Vtiger_Functions::generateTrackingURL($params);
            $imageDetails = "<img src='$trackURL' alt='' width='1' height='1'>";
            return $imageDetails;
	}

	/**
	 * Function to get the list of urls from html content
	 * @param <string> $content
	 * @return <array> $urls
	 */
	public static function getUrlsFromHtml($content) {
		$doc = new DOMDocument();
		$urls = array();

		//handling utf8 characters present in the template source
		$formattedContent = mb_convert_encoding($content, 'HTML-ENTITIES', "UTF-8");
		if(empty($formattedContent)) return $urls;

		$doc->loadHTML($formattedContent);
		$tags = $doc->getElementsByTagName('a');
		foreach ($tags as $tag) {
			$hrefTag = $tag->getAttribute('href');
			//If href start with mailto:,tel:,# then skip those URLS from tracking
			if (strpos($hrefTag, 'mailto:') !== 0 && strpos($hrefTag, 'tel:') !== 0 && $hrefTag[0] !== self::LINK_TO_ANCHOR_TEXT_SYMBOL) {
				$urls[$hrefTag][] = $tag->nodeValue;
			}

		}
		return $urls;
	}

	static function redirectUrl($targetUrl) {
		$regExp = "~^(?:f|ht)tps?://~i"; // This regular expression is to detect if targetUrl which was stored in database contains
										//http:// or https:// then it will redirect as normal if not for target http:// will prepend and then redirect
		if (!preg_match($regExp, $targetUrl)) {
			return header("Location:http://" . $targetUrl);
		}
		return header("Location:" . $targetUrl);
	}

	/**
	 * Function to check if a string is a valid date value or not
	 * @param string $value string to check if that is a date value or not
	 * @return boolean Returns true if $value is date else returns false
	 */
	static function isDateValue($value) {
            $value = trim($value);
            $delim = array('/','.');
            foreach ($delim as $delimiter){
                    $x = strpos($value, $delimiter);
                    if($x === false) continue;
                    else{
                            $value=str_replace($delimiter, '-', $value);
                            break;
                    }
            }
            $valueParts = explode('-', $value);
            if (count($valueParts) == 3 && (strlen($valueParts[0]) == 4 || strlen($valueParts[1]) == 4 || strlen($valueParts[2]) == 4)) {
                    $time = strtotime($value);
                    if ($time && $time > 0) {
                            return true;
                    } else {
                            return false;
                    }
            } else {
                    return false;
            }
	}

	/**
	 * Function to get name and email value from a string of format <b>Your Name<youremail@company.com></b>
	 * @param String $string Name and email value in required format.
	 * @return Array Returns array of name and email in format <b><br>Array(<br>&#09;name => Your Name,<br>&#09;email => youemail@company.com<br>)</b>
	 * @return Boolean Returns FALSE if given string doesn't match required format.
	 */
	static function extractNameEmail($string) {
		$pattern = '/([(\w+) ]+)(<)([\\w-+]+(?:\\.[\\w-+]+)*@(?:[\\w-]+\\.)+[a-zA-Z]{2,7})(>)/is';
		if (preg_match($pattern, $string, $matches)) {
			return array('name' => $matches[1], 'email' => $matches[3]);
		}
		return false;
	}

	/**
	 * Function to get value for all mandatory relation field of a module (except Users).
	 * @param String $module Module for which mandatory relation field is needed
	 * @param String $mode (Optional) Label or Id for relation fields. Default label
	 * @return Array Returns array of all mandatory relation field values
	 */
	public static function getMandatoryReferenceFields($module, $mode = 'label') {
		$mandatoryReferenceFields = array();
		$userId = Users::getActiveAdminId();
		$db = PearDatabase::getInstance();
		$moduleInstance = Vtiger_Module_Model::getInstance($module);
		$referenceFields = $moduleInstance->getFieldsByType(array('reference', 'multireference'));
		foreach ($referenceFields as $field => $fieldModel) {
			$uiType = $fieldModel->get('uitype');
			$referenceModules = $fieldModel->getReferenceList();
			if (!is_array($referenceModules)) {
				$referenceModules = array($referenceModules);
			}
			$referenceModule = $referenceModules[0];

			if ($fieldModel->isMandatory() && !empty($referenceModule) && !in_array($uiType, array(50, 51, 52))) {
				$recordName = '?????';
				$result = $db->pquery("SELECT crmid, label FROM vtiger_crmentity WHERE label LIKE ? AND deleted = ? AND setype = ?", array("%$recordName%", 0, $referenceModule));
				if ($db->num_rows($result) < 1) {
					$moduleModel = Vtiger_Module_Model::getInstance($referenceModule);
					$recordModel = Vtiger_Record_Model::getCleanInstance($referenceModule);

					$fieldInstances = Vtiger_Field_Model::getAllForModule($moduleModel);
					foreach ($fieldInstances as $blockInstance) {
						foreach ($blockInstance as $fieldInstance) {
							$fieldName = $fieldInstance->getName();
							$defaultValue = $fieldInstance->getDefaultFieldValue();
							$dataType = $fieldInstance->getFieldDataType();
							if ($defaultValue) {
								$recordModel->set($fieldName, decode_html($defaultValue));
							}
							if ($fieldInstance->isMandatory() && !$defaultValue && !in_array($dataType, array('reference', 'multireference'))) {
								$randomValue = Vtiger_Util_Helper::getDefaultMandatoryValue($fieldInstance->getFieldDataType());
								if ($dataType == 'picklist' || $dataType == 'multipicklist') {
									$picklistValues = $fieldInstance->getPicklistValues();
									$randomValue = reset($picklistValues);
								}
								$recordModel->set($fieldName, $randomValue);
							}

							$referenceRelationFields = Vtiger_Functions::getMandatoryReferenceFields($referenceModule, 'id');
							foreach ($referenceRelationFields as $relationFieldName => $relationValue) {
								$recordModel->set($relationFieldName, $relationValue);
							}
						}
					}
					$recordModel->set('mode', '');
					$recordModel->set('assigned_user_id', $userId);
					$recordModel->save();
					if ($mode == 'label') {
						$recordName = Vtiger_Util_Helper::getRecordName($recordModel->getId());
					} else {
						$recordName = $recordModel->getId();
					}
				} else {
					if ($mode == 'label') {
						$recordName = $db->query_result($result, 0, 'label');
					} else {
						$recordName = $db->query_result($result, 0, 'crmid');
					}
				}
				$mandatoryReferenceFields[$field] = $recordName;
			}
		}

		return $mandatoryReferenceFields;
	}

	static function setEventsContactIdToRequest($recordId) {
		$db = PearDatabase::getInstance();
		$contactIds = array();
		$result = $db->pquery("SELECT contactid FROM vtiger_cntactivityrel WHERE activityid = ?", array($recordId));
		$count = $db->num_rows($result);
		for ($i = 0; $i < $count; $i++) {
			$contactIds[] = $db->query_result($result, $i, 'contactid');
		}
		$_REQUEST['contactidlist'] = implode(';', $contactIds);
	}

	 static function getNonQuickCreateSupportedModules() {
		$nonQuickCreateModules = array();
		$modules = Vtiger_Module_Model::getAll(array(0, 2));
		foreach ($modules as $module) {
			if (!$module->isQuickCreateSupported()) {
				$nonQuickCreateModules[] = $module->getName();
			}
		}
		return $nonQuickCreateModules;
	}

	static function getPrivateCommentModules() {
		return array('HelpDesk', 'Faq');
	}

	/**
	 * Function which will return user field table for a module
	 * @param type $moduleName -- module for which table name need to be retrieved
	 * @return type -- table name
	 */
	public static function getUserSpecificTableName($moduleName) {
		return 'vtiger_crmentity_user_field';
	}

	/**
	 * Function which will determine whether the table contains user specific field
	 * @param type $tableName -- name of the table
	 * @param type $moduleName -- moduleName
	 * @return boolean
	 */
	public static function isUserSpecificFieldTable($tableName, $moduleName) {
		$moduleName = strtolower($moduleName);
		return (self::getUserSpecificTableName($moduleName) == $tableName) ? true : false;
	}

	public static function isUserExist($userId) {
		$adb = PearDatabase::getInstance();
		$query = "SELECT 1 FROM vtiger_users WHERE id=? AND deleted=? AND status = ?";
		$result = $adb->pquery($query, array($userId, 0, 'Active'));
		if ($adb->num_rows($result) > 0) {
			return true;
		} else {
			return false;
		}
	}
	/**
	 * Function to decode JWT web token
	 * @param <string> $id_token
	 * @return <array>
	 */
	static function jwtDecode($id_token) {
		$token_parts = explode(".", $id_token);

		// First, in case it is url-encoded, fix the characters to be
		// valid base64
		$encoded_token = str_replace('-', '+', $token_parts[1]);
		$encoded_token = str_replace('_', '/', $encoded_token);

		// Next, add padding if it is needed.
		switch (strlen($encoded_token) % 4) {
			case 0:	break;// No pad characters needed.
			case 2:	$encoded_token = $encoded_token . "==";	break;
			case 3:	$encoded_token = $encoded_token . "=";	break;
			default:return null;// Invalid base64 string!
		}

		$json_string = base64_decode($encoded_token);
		$jwt = json_decode($json_string, true);
		return $jwt;
	}

	/**
	 * Function to mask input text.
	 */
	static function toProtectedText($text) {
		if (empty($text)) return $text;

		require_once 'include/utils/encryption.php';
		$encryption = new Encryption();
		return '$ve$'.$encryption->encrypt($text);
	}

	/*
	 * Function to determine if text is masked.
	 */
	static function isProtectedText($text) {
		return !empty($text) && (strpos($text, '$ve$') === 0);
	}

	/*
	 * Function to unmask the text.
	 */
	static function fromProtectedText($text) {
		if (static::isProtectedText($text)) {
			require_once 'include/utils/encryption.php';
			$encryption = new Encryption();
			return $encryption->decrypt(substr($text, 4));
		}
		return $text;
	}

	/*
	 * Function to convert file size in bytes to user displayable format
	 */
	static function convertFileSizeToUserFormat($sizeInBytes) {
		$fileSizeUnits = array('KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB');
		$i = -1;
		do {
			$sizeInBytes = $sizeInBytes / 1024;
			$i++;
		} while ($sizeInBytes > 1024);
		return round($sizeInBytes, 2) . $fileSizeUnits[$i];
	}

	/**
	 * Function to check if a module($sourceModule) is related to Documents module.
	 * @param <string> $sourceModule - Source module
	 * @return <boolean> Returns TRUE if $sourceModule is related to Documents module and
	 * Documents module is active else returns FALSE.
	 */
	static function isDocumentsRelated($sourceModule) {
		$isRelated = false;
		$moduleName = 'Documents';
		if (vtlib_isModuleActive($moduleName)) {
			$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
			$sourceModuleModel = Vtiger_Module_Model::getInstance($sourceModule);
			if ($moduleModel && $sourceModuleModel) {
				$relationModel = Vtiger_Relation_Model::getInstance($sourceModuleModel, $moduleModel);
			}
			if ($relationModel) {
				$isRelated = true;
			}
		}
		return $isRelated;
	}

	/**
	 * Function to Escapes special characters in a string for use in an SQL statement
	 * @param type $value
	 * @return type
	 */
	static function realEscapeString($value){
		$db = PearDatabase::getInstance();
		$value = $db->sql_escape_string($value);
		return $value;
	}

    /**
     * Request parameters and it's type.
     * @var type
     */
    protected static $type = array(
	'record' => 'id',
	'src_record' => 'id',
	'parent_id' => 'id',
        '_mfrom' => 'email',
        '_mto' => 'email',
        'sequencesList' => 'idlist',
        'search_value' => 'keyword',
    );

    /**
     * Function to validate request parameters.
     * @param type $request
     * @throws Exception - Bad Request
     */
    public static function validateRequestParameters($request) {
        foreach (self::$type as $param => $type) {
            if ( isset($request[$param])&& $request[$param] && !self::validateRequestParameter($type, $request[$param])) {
                http_response_code(400);
                throw new Exception('Bad Request');
            }
        }
    }

    /**
     * Function to validate request parameter by it's type.
     * @param  <String> type   - Type of paramter.
     * @param  <String> $value - Which needs to be validated.
     * @return <Boolean>
     */
    public static function validateRequestParameter($type, $value) {
        $ok = true;
        switch ($type) {
            case 'id' : $ok = (preg_match('/[^0-9xH]/', $value)) ? false : $ok;
                break;
            case 'email' : $ok = (!filter_var($value, FILTER_VALIDATE_EMAIL)) ? false : $ok;
                break;
            case 'idlist' : $ok = (preg_match('/[a-zA-Z]/', $value)) ? false : $ok;
                break;
            case 'keyword':
                $blackList = array('UNION', '--', 'SELECT ', 'SELECT*', '%', 'NULL', 'HEX');
                foreach ($blackList as $keyword) {
                    if (stripos($value, $keyword) !== false) {
                        $ok = false;
                        break;
                    }
                }
                break;
        }
        return $ok;
    }

    /**
	 * Function to get file public url to access outside of CRM (from emails)
	 * @param <Integer> $fileId
	 * @param <String> $fileName
	 * @return <String> $sourceUrl
	 */
	public static function getFilePublicURL($imageId, $imageName) {
		$publicUrl = '';
        $fileId = $imageId;
        $fileName = $imageName;
		if ($fileId) {
			$publicUrl = "public.php?fid=$fileId&key=".md5($fileName);
		}
		return $publicUrl;
	}
    
    /**
     * Function to get the attachmentsid to given crmid
     * @param type $crmid
     * @param type $webaservice entity id
     * @return <Array> 
     */
    static function getAttachmentIds($crmid, $WsEntityId) {
        $adb = PearDatabase::getInstance();
        $attachmentIds = false;
        if(!empty($crmid)) {
            $query = "SELECT attachmentsid FROM vtiger_seattachmentsrel WHERE crmid = ?";
            $result = $adb->pquery($query, array($crmid));
            $noofrows = $adb->num_rows($result);
            if ($noofrows) {
                for ($i = 0; $i < $noofrows; $i++) {
                    $attachmentIds[] = vtws_getId($WsEntityId,$adb->query_result($result, $i, 'attachmentsid'));
                }
            }
        }
        return $attachmentIds;
    }
    
    static function generateTrackingURL($params = []){
        $options = array(
            'handler_path' => 'modules/Emails/handlers/Tracker.php',
            'handler_class' => 'Emails_Tracker_Handler',
            'handler_function' => 'process',
            'handler_data' => $params
        );

        return Vtiger_ShortURL_Helper::generateURL($options);
    }
}