public static function LoadAllWithCustomFields($strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
Manufacturer::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Manufacturer::ExpandQuery('manufacturer', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$arrCustomFieldSql = CustomField::GenerateSql(5);
$strQuery = sprintf('
SELECT
%s
`manufacturer`.`manufacturer_id` AS `manufacturer_id`,
`manufacturer`.`short_description` AS `short_description`,
`manufacturer`.`long_description` AS `long_description`,
`manufacturer`.`image_path` AS `image_path`,
`manufacturer`.`created_by` AS `created_by`,
`manufacturer`.`creation_date` AS `creation_date`,
`manufacturer`.`modified_by` AS `modified_by`,
`manufacturer`.`modified_date` AS `modified_date`
%s
%s
FROM
`manufacturer` AS `manufacturer`
%s
%s
WHERE
1=1
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $strOrderBy, $strLimitSuffix);
//echo($strQuery); exit;
$objDbResult = $objDatabase->Query($strQuery);
return Manufacturer::InstantiateDbResult($objDbResult);
}
/**
* Load an array of Contact objects
* by FirstName, LastName, or Company ShortDescription
*
* @param string $strFirstName
* @param string $strLastName
* @param string $strCompany
* @param string $strDateModified
* @param string $strDateModifiedFirst
* @param string $strDateModifiedLast
* @param string $strOrderBy
* @param string $strLimit
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @return Contact[]
*/
public static function LoadArrayBySearch($strFirstName = null, $strLastName = null, $strCompany = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
Contact::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Contact::ExpandQuery('contact', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$arrSearchSql = Contact::GenerateSearchSql($strFirstName, $strLastName, $strCompany, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Contact);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Contact);
$strQuery = sprintf('
SELECT
%s
`contact`.`contact_id` AS `contact_id`,
`contact`.`address_id` AS `address_id`,
`contact`.`company_id` AS `company_id`,
`contact`.`first_name` AS `first_name`,
`contact`.`last_name` AS `last_name`,
`contact`.`title` AS `title`,
`contact`.`email` AS `email`,
`contact`.`phone_office` AS `phone_office`,
`contact`.`phone_home` AS `phone_home`,
`contact`.`phone_mobile` AS `phone_mobile`,
`contact`.`fax` AS `fax`,
`contact`.`description` AS `description`,
`contact`.`created_by` AS `created_by`,
`contact`.`creation_date` AS `creation_date`,
`contact`.`modified_by` AS `modified_by`,
`contact`.`modified_date` AS `modified_date`
%s
%s
%s
FROM
`contact` AS `contact`
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $arrAttachmentSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strFirstNameSql'], $arrSearchSql['strLastNameSql'], $arrSearchSql['strCompanySql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql'], $arrAttachmentSql['strGroupBy'], $strOrderBy, $strLimitSuffix);
//echo($strQuery); exit;
$objDbResult = $objDatabase->Query($strQuery);
return Contact::InstantiateDbResult($objDbResult);
}
/**
* Load an array of InventoryModel objects
* by CategoryId, ManufacturerId Index(es)
* InventoryModel ShortDescription, or InventoryModelCode
*
* @param string $strInventoryModelCode
* @param int $intLocationId
* @param int $intInventoryModelId
* @param int $intCategoryId
* @param int $intManufacturerId
* @param string $strShortDescription
* @param string $strOrderBy
* @param string $strLimit
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @return InventoryModel[]
*/
public static function LoadArrayBySearch($strInventoryModelCode = null, $intLocationId = null, $intInventoryModelId = null, $intCategoryId = null, $intManufacturerId = null, $strShortDescription = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
InventoryModel::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
InventoryModel::ExpandQuery('inventory_model', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$arrSearchSql = InventoryModel::GenerateSearchSql($strInventoryModelCode, $intLocationId, $intInventoryModelId, $intCategoryId, $intManufacturerId, $strShortDescription, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Inventory);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Inventory);
$strQuery = sprintf('
SELECT
%s
SUM( `inventory_location` . `quantity` ) AS `inventory_model_quantity`,
`inventory_model`.`inventory_model_id` AS `inventory_model_id`,
`inventory_model`.`category_id` AS `category_id`,
`inventory_model`.`manufacturer_id` AS `manufacturer_id`,
`inventory_model`.`inventory_model_code` AS `inventory_model_code`,
`inventory_model`.`short_description` AS `short_description`,
`inventory_model`.`long_description` AS `long_description`,
`inventory_model`.`image_path` AS `image_path`,
`inventory_model`.`price` AS `price`,
`inventory_model`.`created_by` AS `created_by`,
`inventory_model`.`creation_date` AS `creation_date`,
`inventory_model`.`modified_by` AS `modified_by`,
`inventory_model`.`modified_date` AS `modified_date`
%s
%s
%s
FROM
`inventory_model` AS `inventory_model`
LEFT JOIN `inventory_location` AS `inventory_location` ON `inventory_model` . `inventory_model_id` = `inventory_location` . `inventory_model_id`
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $arrAttachmentSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strInventoryModelCodeSql'], $arrSearchSql['strLocationSql'], $arrSearchSql['strInventoryModelSql'], $arrSearchSql['strCategorySql'], $arrSearchSql['strManufacturerSql'], $arrSearchSql['strShortDescriptionSql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql'], $arrAttachmentSql['strGroupBy'], $strOrderBy, $strLimitSuffix);
$objDbResult = $objDatabase->Query($strQuery);
return InventoryModel::InstantiateDbResult($objDbResult);
}
/**
* Load an array of Shipment objects
* by To Company, To Contact, From Company, From Contact, Shipment Number, Asset Code, Inventory Code, Tracking Number, or Status
*
* @param string $strToCompany
* @param string $strToContact
* @param string $strFromCompany
* @param string $strFromContact
* @param string $strShipmentNumber
* @param string $strAssetCode
* @param string $strInventoryModelCode
* @param int $intStatus
* @param string $strTrackingNumber
* @param int $intCourierId
* @param string $strNote
* @param string $strDateModified
* @param string $strDateModifiedFirst
* @param string $strDateModifiedLast
* @param string $strOrderBy
* @param string $strLimit
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @return Shipment[]
*/
public static function LoadArrayBySearch($strToCompany = null, $strToContact = null, $strFromCompany = null, $strFromContact = null, $strShipmentNumber = null, $strAssetCode = null, $strInventoryModelCode = null, $intStatus = null, $strTrackingNumber = null, $intCourierId = null, $strNote = null, $strShipmentDate = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
Shipment::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Shipment::ExpandQuery('shipment', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$arrSearchSql = Shipment::GenerateSearchSql($strToCompany, $strToContact, $strFromCompany, $strFromContact, $strShipmentNumber, $strAssetCode, $strInventoryModelCode, $intStatus, $strTrackingNumber, $intCourierId, $strNote, $strShipmentDate, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Shipment);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Shipment);
$strQuery = sprintf('
SELECT
%s
DISTINCT
`shipment`.`shipment_id` AS `shipment_id`,
`shipment`.`shipment_number` AS `shipment_number`,
`shipment`.`to_contact_id` AS `to_contact_id`,
`shipment`.`from_company_id` AS `from_company_id`,
`shipment`.`from_contact_id` AS `from_contact_id`,
`shipment`.`transaction_id` AS `transaction_id`,
`shipment`.`ship_date` AS `ship_date`,
`shipment`.`from_address_id` AS `from_address_id`,
`shipment`.`to_company_id` AS `to_company_id`,
`shipment`.`to_address_id` AS `to_address_id`,
`shipment`.`courier_id` AS `courier_id`,
`shipment`.`shipped_flag` AS `shipped_flag`,
`shipment`.`tracking_number` AS `tracking_number`,
`shipment`.`created_by` AS `created_by`,
`shipment`.`creation_date` AS `creation_date`,
`shipment`.`modified_by` AS `modified_by`,
`shipment`.`modified_date` AS `modified_date`
%s
%s
%s
FROM
`shipment` AS `shipment`
%s
%s
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $arrAttachmentSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strAssetCodeFromSql'], $arrSearchSql['strInventoryModelCodeFromSql'], $arrSearchSql['strToCompanySql'], $arrSearchSql['strToContactSql'], $arrSearchSql['strFromCompanySql'], $arrSearchSql['strFromContactSql'], $arrSearchSql['strShipmentNumberSql'], $arrSearchSql['strAssetCodeSql'], $arrSearchSql['strInventoryModelCodeSql'], $arrSearchSql['strStatusSql'], $arrSearchSql['strTrackingNumberSql'], $arrSearchSql['strCourierSql'], $arrSearchSql['strNoteSql'], $arrSearchSql['strShipmentDateSql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql'], $arrAttachmentSql['strGroupBy'], $strOrderBy, $strLimitSuffix);
$objDbResult = $objDatabase->Query($strQuery);
return Shipment::InstantiateDbResult($objDbResult);
}
/**
* Load an array of Asset objects
* by CategoryId, ManufacturerId Index(es)
* AssetModel ShortDescription, or AssetModelCode
*
* @param int $intCategoryId
* @param int $intManufacturerId
* @param string $strShortDescription
* @param string $strAssetModelCode
* @param string $strOrderBy
* @param string $strLimit
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @return Asset[]
*/
public static function LoadArrayBySearch($strAssetCode = null, $intLocationId = null, $intAssetModelId = null, $intCategoryId = null, $intManufacturerId = null, $blnOffsite = false, $strAssetModelCode = null, $intReservedBy = null, $intCheckedOutBy = null, $strShortDescription = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
Asset::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Asset::ExpandQuery('asset', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$arrSearchSql = Asset::GenerateSearchSql($strAssetCode, $intLocationId, $intAssetModelId, $intCategoryId, $intManufacturerId, $blnOffsite, $strAssetModelCode, $intReservedBy, $intCheckedOutBy, $strShortDescription, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Asset);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Asset);
$strQuery = sprintf('
SELECT
%s
`asset`.`asset_id` AS `asset_id`,
`asset`.`asset_model_id` AS `asset_model_id`,
`asset`.`location_id` AS `location_id`,
`asset`.`asset_code` AS `asset_code`,
`asset`.`image_path` AS `image_path`,
`asset`.`checked_out_flag` AS `checked_out_flag`,
`asset`.`reserved_flag` AS `reserved_flag`,
`asset`.`archived_flag` AS `archived_flag`,
`asset`.`created_by` AS `created_by`,
`asset`.`creation_date` AS `creation_date`,
`asset`.`modified_by` AS `modified_by`,
`asset`.`modified_date` AS `modified_date`
%s
%s
%s
FROM
`asset` AS `asset`
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $arrAttachmentSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strAssetCodeSql'], $arrSearchSql['strLocationSql'], $arrSearchSql['strAssetModelSql'], $arrSearchSql['strCategorySql'], $arrSearchSql['strManufacturerSql'], $arrSearchSql['strOffsiteSql'], $arrSearchSql['strAssetModelCodeSql'], $arrSearchSql['strReservedBySql'], $arrSearchSql['strCheckedOutBySql'], $arrSearchSql['strShortDescriptionSql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql'], $arrAttachmentSql['strGroupBy'], $strOrderBy, $strLimitSuffix);
$objDbResult = $objDatabase->Query($strQuery);
return Asset::InstantiateDbResult($objDbResult);
}
/**
* Load an array of Company objects
* by ShortDescription, City, StateProvince, or Country
*
* @param string $strShortDescription
* @param string $strCity
* @param integer $intStateProvinceId
* @param integer $intCountryId
* @param string $strDateModified
* @param string $strDateModifiedFirst
* @param string $strDateModifiedLast
* @param string $strOrderBy
* @param string $strLimit
* @param array $objExpansionMap map of referenced columns to be immediately expanded via early-binding
* @return Company[]
*/
public static function LoadArrayBySearch($strShortDescription = null, $strCity = null, $intStateProvinceId = null, $intCountryId = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $strOrderBy = null, $strLimit = null, $objExpansionMap = null)
{
Company::ArrayQueryHelper($strOrderBy, $strLimit, $strLimitPrefix, $strLimitSuffix, $strExpandSelect, $strExpandFrom, $objExpansionMap, $objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Company::ExpandQuery('company', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
// Generate an array of SQL strings to be used in the search query
$arrSearchSql = Company::GenerateSearchSql($strShortDescription, $strCity, $intStateProvinceId, $intCountryId, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Company);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Company);
$strQuery = sprintf('
SELECT
%s
`company`.`company_id` AS `company_id`,
`company`.`address_id` AS `address_id`,
`company`.`short_description` AS `short_description`,
`company`.`website` AS `website`,
`company`.`telephone` AS `telephone`,
`company`.`fax` AS `fax`,
`company`.`email` AS `email`,
`company`.`long_description` AS `long_description`,
`company`.`created_by` AS `created_by`,
`company`.`creation_date` AS `creation_date`,
`company`.`modified_by` AS `modified_by`,
`company`.`modified_date` AS `modified_date`
%s
%s
%s
FROM
`company` AS `company`
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $strLimitPrefix, $objQueryExpansion->GetSelectSql(",\n\t\t\t\t\t", ",\n\t\t\t\t\t"), $arrCustomFieldSql['strSelect'], $arrAttachmentSql['strSelect'], $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strShortDescriptionSql'], $arrSearchSql['strCitySql'], $arrSearchSql['strStateProvinceSql'], $arrSearchSql['strCountrySql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql'], $arrAttachmentSql['strGroupBy'], $strOrderBy, $strLimitSuffix);
$objDbResult = $objDatabase->Query($strQuery);
return Company::InstantiateDbResult($objDbResult);
}
/**
* Count DISTINCT Transactions
* @param $strAssetModel
* @param $strAssetCode
* @param $strAssetModelCode
* @param $strUser
* @param $intCheckedOutBy
* @param $intReservedBy
* @param $strCategory
* @param $strManufacturer
* @param $strDateModified
* @param $strDateModifiedFirst
* @param $strDateModifiedLast
* @param $arrTransactionTypes
* @param $objExpansionMap
* @return int
*/
public function CountTransactionsBySearch($strAssetModel = null, $strAssetCode = null, $strAssetModelCode = null, $strUser = null, $intCheckedOutBy = null, $intReservedBy = null, $strCategory = null, $strManufacturer = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $arrTransactionTypes = null, $objExpansionMap = null)
{
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
AssetTransaction::ExpandQuery('asset_transaction', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
$strTransactionTypes = "";
if ($arrTransactionTypes) {
$strTransactionTypes = sprintf("AND `asset_transaction__transaction_id`.`transaction_type_id` IN ('%s') ", implode("', '", $arrTransactionTypes));
}
if ($strAssetModel) {
$strAssetModel = "AND `asset_transaction__asset_id__asset_model_id`.`short_description` LIKE '%" . $strAssetModel . "%'\n";
}
if ($strAssetCode) {
$strAssetCode = "AND `asset_transaction__asset_id`.`asset_code` LIKE '%" . $strAssetCode . "%'\n";
}
if ($strAssetModelCode) {
$strAssetModelCode = "AND `asset_transaction__asset_id__asset_model_id`.`asset_model_code` LIKE '%" . $strAssetModelCode . "%'\n";
}
if ($strUser) {
$strUser = sprintf("AND (`asset_transaction__transaction_id__created_by`.`user_account_id` = '%s' OR `asset_transaction__transaction_id__modified_by`.`user_account_id` = '%s')\n", $strUser, $strUser);
}
$strCheckedOutBy = "";
if ($intCheckedOutBy) {
$strCheckedOutBy = sprintf("AND `asset_transaction__asset_id`.`checked_out_flag` = true\n");
if ($intCheckedOutBy != 'any') {
$strCheckedOutBy .= sprintf("AND `asset_transaction`.`created_by` = '%s'\n", $intCheckedOutBy);
}
}
$strReservedBy = "";
if ($intReservedBy) {
$strReservedBy = sprintf("AND `asset_transaction__asset_id`.`reserved_flag` = true\n");
if ($intReservedBy != 'any') {
$strReservedBy .= sprintf("AND `asset_transaction`.`created_by` = '%s'\n", $intReservedBy);
}
}
if ($strCategory) {
$strCategory = sprintf("AND `asset_transaction__asset_id__asset_model_id`.`category_id` = '%s'\n", $strCategory);
}
if ($strManufacturer) {
$strManufacturer = sprintf("AND `asset_transaction__asset_id__asset_model_id`.`manufacturer_id` = '%s'\n", $strManufacturer);
}
$arrSearchSql['strDateModifiedSql'] = null;
if ($strDateModified) {
if ($strDateModified == "before" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND (UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) < %s OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) < %s)\n", $strDateModifiedFirst, $strDateModifiedFirst);
} elseif ($strDateModified == "after" && $strDateModifiedFirst instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
$arrSearchSql['strDateModifiedSql'] = sprintf("AND (UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) > %s OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) > %s)\n", $strDateModifiedFirst, $strDateModifiedFirst);
} elseif ($strDateModified == "between" && $strDateModifiedFirst instanceof QDateTime && $strDateModifiedLast instanceof QDateTime) {
$strDateModifiedFirst = QApplication::$Database[1]->SqlVariable($strDateModifiedFirst->Timestamp, false);
// Added 86399 (23 hrs., 59 mins., 59 secs) because the After variable needs to include the date given
// When only a date is given, conversion to a timestamp assumes 12:00am
$strDateModifiedLast = QApplication::$Database[1]->SqlVariable($strDateModifiedLast->Timestamp, false) + 86399;
$arrSearchSql['strDateModifiedSql'] = sprintf("AND (UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) > %s AND UNIX_TIMESTAMP(`asset_transaction`.`modified_date`) < %s", $strDateModifiedFirst, $strDateModifiedLast);
$arrSearchSql['strDateModifiedSql'] .= sprintf(" OR UNIX_TIMESTAMP(`asset_transaction`.`creation_date`) > %s AND UNIX_TIMESTAMP(`asset_transaction`.`creation_date`))\n", $strDateModifiedFirst, $strDateModifiedLast);
}
}
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Asset);
/* $strQuery = sprintf('
SELECT
COUNT(DISTINCT `asset_transaction`.`transaction_id`) AS row_count
FROM
`asset_transaction` AS `asset_transaction`
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
OR `asset_transaction__asset_id`.`archived_flag` is TRUE
', $objQueryExpansion->GetFromSql("", "\n "), str_replace("`asset`.`asset_id`", " `asset_transaction__asset_id`.`asset_id`", $arrCustomFieldSql['strFrom']),
$strTransactionTypes, $strAssetModel, $strAssetCode, $strAssetModelCode, $strUser, $strCheckedOutBy, $strReservedBy, $strCategory, $strManufacturer, $arrSearchSql['strDateModifiedSql']
);*/
$strQuery = sprintf('
SELECT
COUNT(DISTINCT `asset_transaction`.`transaction_id`) AS row_count
FROM
`asset_transaction` AS `asset_transaction`
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
%s
%s
', $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $strTransactionTypes, $strAssetModel, $strAssetCode, $strAssetModelCode, $strUser, $strCheckedOutBy, $strReservedBy, $strCategory, $strManufacturer, $arrSearchSql['strDateModifiedSql']);
//echo($strQuery); exit;
$objDatabase = AssetTransaction::GetDatabase();
$objDbResult = $objDatabase->Query($strQuery);
$strDbRow = $objDbResult->FetchRow();
return QType::Cast($strDbRow[0], QType::Integer);
}
/**
* Count the total companies based on the submitted search criteria
*
* @param int $strShortDescription
* @param int $strCity
* @param string $intStateProvinceId
* @param string $intCountryId
* @param string $strDateModified
* @param string $strDateModifiedFirst
* @param string $strDateModifiedLast
* @param array $objExpansionMap
* @return integer Count
*/
public static function CountBySearch($strShortDescription = null, $strCity = null, $intStateProvinceId = null, $intCountryId = null, $arrCustomFields = null, $strDateModified = null, $strDateModifiedFirst = null, $strDateModifiedLast = null, $blnAttachment = null, $objExpansionMap = null)
{
// Call to QueryHelper to Get the Database Object
Company::QueryHelper($objDatabase);
// Setup QueryExpansion
$objQueryExpansion = new QQueryExpansion();
if ($objExpansionMap) {
try {
Company::ExpandQuery('company', null, $objExpansionMap, $objQueryExpansion);
} catch (QCallerException $objExc) {
$objExc->IncrementOffset();
throw $objExc;
}
}
// Generate the search SQL used
$arrSearchSql = Company::GenerateSearchSql($strShortDescription, $strCity, $intStateProvinceId, $intCountryId, $arrCustomFields, $strDateModified, $strDateModifiedFirst, $strDateModifiedLast, $blnAttachment);
$arrAttachmentSql = Attachment::GenerateSql(EntityQtype::Company);
$arrCustomFieldSql = CustomField::GenerateSql(EntityQtype::Company);
$strQuery = sprintf('
SELECT
COUNT(company.company_id) AS row_count
FROM
`company` AS `company`
%s
%s
%s
WHERE
1=1
%s
%s
%s
%s
%s
%s
%s
%s
', $objQueryExpansion->GetFromSql("", "\n\t\t\t\t\t"), $arrCustomFieldSql['strFrom'], $arrAttachmentSql['strFrom'], $arrSearchSql['strShortDescriptionSql'], $arrSearchSql['strCitySql'], $arrSearchSql['strStateProvinceSql'], $arrSearchSql['strCountrySql'], $arrSearchSql['strCustomFieldsSql'], $arrSearchSql['strDateModifiedSql'], $arrSearchSql['strAttachmentSql'], $arrSearchSql['strAuthorizationSql']);
$objDbResult = $objDatabase->Query($strQuery);
$strDbRow = $objDbResult->FetchRow();
return QType::Cast($strDbRow[0], QType::Integer);
}
