|
Server : Apache System : Linux srv.rainic.com 4.18.0-553.47.1.el8_10.x86_64 #1 SMP Wed Apr 2 05:45:37 EDT 2025 x86_64 User : rainic ( 1014) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/akaindir/www/crm/include/database/ |
Upload File : |
<?php
/*********************************************************************************
* The contents of this file are subject to the SugarCRM Public License Version 1.1.2
* ("License"); You may not use this file except in compliance with the
* License. You may obtain a copy of the License at http://www.sugarcrm.com/SPL
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
* the specific language governing rights and limitations under the License.
* The Original Code is: SugarCRM Open Source
* The Initial Developer of the Original Code is SugarCRM, Inc.
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.;
* All Rights Reserved.
* Contributor(s): ______________________________________.
********************************************************************************/
//Fix postgres queries
function fixPostgresQuery($query,$log,$debug)
{
// First select the query fields from the remaining query
$queryFields = substr($query, strlen('SELECT'), stripos($query,'FROM')-strlen('SELECT'));
$queryRecord = substr($query, stripos($query,'FROM'), strlen($query));
$groupClause = "";
$orderClause = "";
if( $debug)
$log->info( "fixPostgresQuery: ".$query);
// If we already have an order or group cluase separate ist for later use
if( stripos($queryRecord,'GROUP BY') > 0)
{
$groupClause = substr($queryRecord, stripos($queryRecord,'GROUP BY')+strlen('GROUP BY'), strlen($queryRecord));
if( stripos($groupClause,'ORDER BY') > 0)
{
$orderClause = substr($groupClause, stripos($groupClause,'ORDER BY'), strlen($groupClause));
$groupClause = substr($groupClause, 0, stripos($groupClause,'ORDER BY'));
}
$queryRecord = substr($queryRecord, 0, stripos($queryRecord,'GROUP BY'));
}
if( stripos($queryRecord,'ORDER BY') > 0)
{
$orderClause = substr($queryRecord, stripos($queryRecord,'ORDER BY'), strlen($queryRecord));
$queryRecord = substr($queryRecord, 0, stripos($queryRecord,'ORDER BY'));
}
// Construkt the privateGroupList from the filed list by separating combined
// record.field entries
$privateGroupList = array();
$token = strtok( $queryFields, ", () ");
while( $token !== false) {
if( strpos( $token, ".") !== false)
array_push( $privateGroupList, $token);
$token = strtok( ", () ");
}
sort( $privateGroupList);
$groupFields = "";
$last = "";
for( $i = 0; $i < count($privateGroupList); $i++) {
if( $last != $privateGroupList[$i])
if( $groupFields == "")
$groupFields = $privateGroupList[$i];
else
$groupFields .= ",".$privateGroupList[$i];
$last = $privateGroupList[$i];
}
// Rebuild the query
$query = "SELECT ".$queryFields.$queryRecord;
if( $groupClause != "" )
$groupClause =$groupClause.",".$groupFields;
else
$groupClause =$groupFields;
$query .= expandStar($groupClause,$log)." ".$orderClause;
if( $debug)
$log->info( "fixPostgresQuery result: ".$query);
return( $query);
}
// Postgres8 will not accept a "tablename.*" entry in the GROUP BY clause
function expandStar($fieldlist,$log)
{
$expanded="";
$field = strtok( $fieldlist, ",");
while( $field != "")
{
//remove leading and trailing spaces
$field = trim( $field);
//still spaces in the field indicate a complex structure
if( strpos( $field, " ") == 0)
{
//locate table- and fieldname
$pos = strpos( $field, ".");
if( $pos > 0)
{
$table = substr( $field, 0, $pos);
$subfield = substr( $field, $pos+1, strlen($field)-$pos);
//do we need to expand?
if( $subfield == "*")
$field = expandRecord($table,$log);
}
//add the propably expanded field to the querylist
if( $expanded == "")
$expanded = $field;
else
$expanded .= ",".$field;
}
//next field
$field = strtok(",");
}
if($expanded!= '')
$expanded = " GROUP BY ". trim($expanded, ",");
//return the expanded fieldlist
return( $expanded);
}
//return an expanded table field list
function expandRecord($table,$log)
{
$result = "";
$log->info( "Debug: expandRecord");
$subfields = array();
//vtiger_products table
if( $table == "vtiger_products" )
$subfields = array ( "productid", "productname", "productcode", "productcategory", "manufacturer", "qty_per_unit", "unit_price", "weight", "pack_size", "sales_start_date", "sales_end_date", "start_date", "expiry_date", "cost_factor", "commissionrate", "commissionmethod", "discontinued", "usageunit", "currency", "reorderlevel", "website", "taxclass", "mfr_part_no", "vendor_part_no", "serialno", "qtyinstock", "productsheet", "qtyindemand", "glacct", "vendor_id", "imagename");
//$subfields = array ( "productid", "productname", "productcode", "productcategory", "manufacturer", "qty_per_unit", "unit_price", "weight", "pack_size", "sales_start_date", "sales_end_date", "start_date", "expiry_date", "cost_factor", "commissionrate", "commissionmethod", "discontinued", "usageunit", "handler", "contactid", "currency", "reorderlevel", "website", "taxclass", "mfr_part_no", "vendor_part_no", "serialno", "qtyinstock", "productsheet", "qtyindemand", "glacct", "vendor_id", "imagename" );
//vtiger_activity table
elseif( $table == "vtiger_activity")
$subfields = array ( "activityid", "subject", "semodule", "activitytype", "date_start", "due_date", "time_start", "time_end", "sendnotification", "duration_hours", "duration_minutes", "status", "eventstatus", "priority", "location", "notime", "visibility", "recurringtype" );
//vtiger_notes table
elseif( $table == "vtiger_notes")
$subfields = array ( "notesid", "contact_id", "title", "filename", "notecontent");
//vtiger_faq table
elseif( $table == "vtiger_faq")
$subfields = array ( "id", "product_id", "question", "answer", "category", "status");
//vtiger_profile2field
elseif( $table == "vtiger_profile2field")
$subfields = array ( "profileid", "tabid", "fieldid", "visible", "readonly");
//vtiger_field
elseif( $table == "vtiger_field")
$subfields = array ( "tabid", "fieldid", "columnname", "tablename", "generatedtype", "uitype", "fieldname", "fieldlabel", "readonly", "presence", "selected", "maximumlength", "sequence", "block", "displaytype", "typeofdata", "quickcreate", "quickcreatesequence", "info_type");
//vtiger_producttaxrel
elseif( $table == "vtiger_producttaxrel")
$subfields = array ( "productid", "taxid", "taxpercentage");
//vtiger_inventorytaxinfo
elseif( $table == "vtiger_inventorytaxinfo")
$subfields = array ( "taxid", "taxname", "taxlabel", "percentage", "deleted");
//vtiger_role2picklist
elseif( $table == "vtiger_role2picklist")
$subfields = array ( "roleid", "picklistid", "sortid");
//vtiger_contactdetails
elseif( $table == "vtiger_contactdetails")
$subfields = array( "lastname", "contactid", "accountid", "salutation", "firstname", "email", "phone", "mobile", "title", "department", "fax", "reportsto", "training", "usertype", "contacttype", "otheremail", "yahooid", "donotcall", "emailoptout", "imagename", "reference", "notify_owner");
//vtiger_quotes
elseif( $table == "vtiger_quotes")
$subfields = array( "quoteid", "subject", "potentialid", "quotestage", "validtill", "contactid", "currency", "subtotal", "carrier", "shipping", "inventorymanager", "type", "adjustment", "total", "taxtype", "discount_percent", "discount_amount", "s_h_amount", "accountid", "terms_conditions");
//vtiger_crmentity
elseif( $table == "vtiger_crmentity")
$subfields = array("crmid", "smcreatorid", "smownerid", "modifiedby", "setype", "description", "createdtime", "modifiedtime", "viewedtime", "status", "version", "presence", "deleted");
//vtiger_salesorder
elseif( $table == "vtiger_salesorder")
$subfields = array("salesorderid", "subject", "potentialid", "customerno", "quoteid", "vendorterms", "contactid", "vendorid", "duedate", "carrier", "pending", "type", "adjustment", "salescommission", "exciseduty","total", "subtotal", "taxtype", "discount_percent", "discount_amount", "s_h_amount", "accountid", "terms_conditions", "purchaseorder", "sostatus");
//vtiger_invoice
elseif( $table == "vtiger_invoice")
$subfields = array("invoiceid", "subject", "salesorderid","customerno","contactid", "notes", "invoicedate", "duedate", "invoiceterms", "type", "adjustment","salescommission","exciseduty", "subtotal","total", "taxtype","discount_percent", "discount_amount", "s_h_amount","shipping", "accountid", "terms_conditions","purchaseorder","invoicestatus","invoice_no");
//vtiger_seactivityrel
elseif( $table == "vtiger_seactivityrel")
$subfields = array("crmid", "activityid");
//vtiger_cntactivityrel
elseif( $table == "vtiger_cntactivityrel")
$subfields = array("contactid", "activityid");
//vtiger_purchaseorder
elseif( $table == "vtiger_purchaseorder")
$subfields = array("purchaseorderid", "subject", "quoteid", "vendorid", "requisition_no", "tracking_no", "contactid", "duedate", "carrier", "type", "adjustment", "salescommission", "exciseduty", "total", "subtotal", "taxtype", "discount_percent","discount_amount", "s_h_amount", "terms_conditions", "postatus");
//vtiger_leaddetails
elseif( $table == "vtiger_leaddetails")
$subfields = array("leadid", "email", "interest", "firstname", "salutation", "lastname", "company", "annualrevenue", "industry", "campaign", "rating", "leadstatus", "leadsource", "converted", "designation", "space", "comments", "priority", "demorequest", "partnercontact", "productversion", "product", "maildate", "nextstepdate", "fundingsituation", "purpose", "evaluationstatus", "transferdate", "revenuetype", "noofemployees", "yahooid", "assignleadchk" );
//vtiger_campaignleadrel
elseif( $table == "vtiger_campaignleadrel")
$subfields = array("campaignid", "leadid");
//vtiger_pricebook
elseif( $table == "vtiger_pricebook")
$subfields = array("pricebookid", "bookname","active","description");
//fields of the requested array still undefined
else
$log->info("function expandRecord: please add structural information for table '".$table."'");
//construct an entity string
for( $i=0; $i<count($subfields); $i++)
{
$result .= $table.".".$subfields[$i].",";
}
//remove the trailiung ,
if( strlen( $result) > 0)
$result = substr( $result, 0, strlen( $result) -1);
//return out new string
return( $result);
}
?>