<?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);
}
?>