[Summary view] [Print] [Text view]

   1  <?php
   2  /*********************************************************************************
   3   * The contents of this file are subject to the SugarCRM Public License Version 1.1.2
   4   * ("License"); You may not use this file except in compliance with the
   5   * License. You may obtain a copy of the License at http://www.sugarcrm.com/SPL
   6   * Software distributed under the License is distributed on an  "AS IS"  basis,
   7   * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for
   8   * the specific language governing rights and limitations under the License.
   9   * The Original Code is:  SugarCRM Open Source
  10   * The Initial Developer of the Original Code is SugarCRM, Inc.
  11   * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.;
  12   * All Rights Reserved.
  13   * Contributor(s): ______________________________________.
  14   ********************************************************************************/
  15  
  16  //Fix postgres queries
  17  function fixPostgresQuery($query,$log,$debug)
  18  {
  19      // First select the query fields from the remaining query
  20      $queryFields = substr($query, strlen('SELECT'), stripos($query,'FROM')-strlen('SELECT'));
  21      $queryRecord = substr($query, stripos($query,'FROM'), strlen($query));
  22      $groupClause = "";
  23      $orderClause = "";
  24  
  25      if( $debug)
  26      $log->info( "fixPostgresQuery: ".$query);
  27  
  28      // If we already have an order or group cluase separate ist for later use
  29      if( stripos($queryRecord,'GROUP BY') > 0)
  30      {
  31          $groupClause = substr($queryRecord, stripos($queryRecord,'GROUP BY')+strlen('GROUP BY'), strlen($queryRecord));
  32          if( stripos($groupClause,'ORDER BY') > 0)
  33          {
  34              $orderClause = substr($groupClause, stripos($groupClause,'ORDER BY'), strlen($groupClause));
  35              $groupClause = substr($groupClause, 0, stripos($groupClause,'ORDER BY'));
  36          }
  37          $queryRecord = substr($queryRecord, 0, stripos($queryRecord,'GROUP BY'));
  38      }
  39  
  40      if( stripos($queryRecord,'ORDER BY') > 0)
  41      {
  42          $orderClause = substr($queryRecord, stripos($queryRecord,'ORDER BY'), strlen($queryRecord));
  43          $queryRecord = substr($queryRecord, 0, stripos($queryRecord,'ORDER BY'));
  44      }
  45  
  46      // Construkt the privateGroupList from the filed list by separating combined
  47      // record.field entries
  48      $privateGroupList = array();
  49      $token = strtok( $queryFields, ", ()    ");
  50      while( $token !== false) {
  51          if( strpos( $token, ".") !== false)
  52              array_push( $privateGroupList, $token);
  53          $token = strtok( ", ()    ");
  54      }
  55      sort( $privateGroupList);
  56      $groupFields = "";
  57      $last = "";
  58      for( $i = 0; $i < count($privateGroupList); $i++) {
  59          if( $last != $privateGroupList[$i])
  60              if( $groupFields == "")
  61                  $groupFields = $privateGroupList[$i];
  62              else
  63                  $groupFields .= ",".$privateGroupList[$i];
  64          $last = $privateGroupList[$i];
  65      }
  66  
  67      // Rebuild the query
  68      $query = "SELECT ".$queryFields.$queryRecord;
  69      if( $groupClause != "" )
  70          $groupClause =$groupClause.",".$groupFields;
  71      else
  72          $groupClause =$groupFields;
  73      $query .= expandStar($groupClause,$log)." ".$orderClause;
  74  
  75      if( $debug)
  76      $log->info( "fixPostgresQuery result: ".$query);
  77  
  78      return( $query);
  79  }
  80  
  81  // Postgres8 will not accept a "tablename.*" entry in the GROUP BY clause
  82  function expandStar($fieldlist,$log)
  83  {
  84      $expanded="";
  85      $field = strtok( $fieldlist, ",");
  86      while( $field != "")
  87      {
  88          //remove leading and trailing spaces
  89          $field = trim( $field);
  90  
  91          //still spaces in the field indicate a complex structure
  92          if( strpos( $field, " ") == 0)
  93          {
  94  
  95              //locate table- and fieldname
  96              $pos = strpos( $field, ".");
  97              if( $pos > 0)
  98               {
  99              $table = substr( $field, 0, $pos);
 100              $subfield = substr( $field, $pos+1, strlen($field)-$pos);
 101  
 102              //do we need to expand?
 103              if( $subfield == "*")
 104                  $field = expandRecord($table,$log);
 105            }
 106  
 107               //add the propably expanded field to the querylist
 108               if( $expanded == "")
 109              $expanded = $field;
 110            else
 111              $expanded .= ",".$field;
 112          }
 113  
 114          //next field
 115          $field = strtok(",");
 116      }
 117      if($expanded!= '')
 118          $expanded = " GROUP BY ". trim($expanded, ",");
 119      //return the expanded fieldlist
 120      return( $expanded);
 121  }
 122  
 123  //return an expanded table field list
 124  function expandRecord($table,$log)
 125  {
 126      $result = "";
 127      $log->info( "Debug: expandRecord");
 128      $subfields = array();
 129  
 130      //vtiger_products table
 131      if( $table == "vtiger_products" )
 132      $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");
 133      //$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" );
 134  
 135      //vtiger_activity table
 136      elseif( $table == "vtiger_activity")
 137      $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"  );
 138  
 139      //vtiger_notes table
 140      elseif( $table == "vtiger_notes")
 141      $subfields = array ( "notesid", "contact_id", "title", "filename", "notecontent");
 142  
 143      //vtiger_faq table
 144      elseif( $table == "vtiger_faq")
 145      $subfields = array ( "id", "product_id", "question", "answer", "category", "status");
 146  
 147      //vtiger_profile2field
 148      elseif( $table == "vtiger_profile2field")
 149      $subfields = array ( "profileid", "tabid", "fieldid", "visible", "readonly");
 150  
 151      //vtiger_field
 152      elseif( $table == "vtiger_field")
 153      $subfields = array ( "tabid", "fieldid", "columnname", "tablename", "generatedtype", "uitype", "fieldname", "fieldlabel", "readonly", "presence", "selected", "maximumlength", "sequence", "block", "displaytype", "typeofdata", "quickcreate", "quickcreatesequence", "info_type");
 154  
 155      //vtiger_producttaxrel
 156      elseif( $table == "vtiger_producttaxrel")
 157      $subfields = array ( "productid", "taxid", "taxpercentage");
 158  
 159      //vtiger_inventorytaxinfo
 160      elseif( $table == "vtiger_inventorytaxinfo")
 161      $subfields = array ( "taxid", "taxname", "taxlabel", "percentage", "deleted");
 162  
 163      //vtiger_role2picklist
 164      elseif( $table == "vtiger_role2picklist")
 165      $subfields = array ( "roleid", "picklistid", "sortid");
 166  
 167      //vtiger_contactdetails
 168      elseif( $table == "vtiger_contactdetails")
 169      $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");
 170  
 171      //vtiger_quotes
 172      elseif( $table == "vtiger_quotes")
 173      $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");
 174  
 175      //vtiger_crmentity
 176      elseif( $table == "vtiger_crmentity")
 177      $subfields = array("crmid", "smcreatorid", "smownerid", "modifiedby", "setype", "description", "createdtime", "modifiedtime", "viewedtime", "status", "version", "presence", "deleted");
 178  
 179      //vtiger_salesorder
 180      elseif( $table == "vtiger_salesorder")
 181      $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");
 182  
 183      //vtiger_invoice
 184      elseif( $table == "vtiger_invoice")
 185      $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");
 186  
 187      //vtiger_seactivityrel
 188      elseif( $table == "vtiger_seactivityrel")
 189      $subfields = array("crmid", "activityid");
 190  
 191      //vtiger_cntactivityrel
 192      elseif( $table == "vtiger_cntactivityrel")
 193      $subfields = array("contactid", "activityid");
 194  
 195      //vtiger_purchaseorder
 196      elseif( $table == "vtiger_purchaseorder")
 197      $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");
 198  
 199      //vtiger_leaddetails
 200      elseif( $table == "vtiger_leaddetails")
 201      $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" );
 202  
 203      //vtiger_campaignleadrel
 204      elseif( $table == "vtiger_campaignleadrel")
 205      $subfields = array("campaignid", "leadid");
 206  
 207      //vtiger_pricebook
 208      elseif( $table == "vtiger_pricebook")
 209      $subfields = array("pricebookid", "bookname","active","description");
 210  
 211      //fields of the requested array still undefined
 212      else
 213      $log->info("function expandRecord: please add structural information for table '".$table."'");
 214  
 215      //construct an entity string
 216      for( $i=0; $i<count($subfields); $i++)
 217      {
 218      $result .= $table.".".$subfields[$i].",";
 219      }
 220  
 221      //remove the trailiung ,
 222      if( strlen( $result) > 0)
 223      $result = substr( $result, 0, strlen( $result) -1);
 224  
 225      //return out new string
 226      return( $result);
 227  }
 228  ?>