[Summary view] [Print] [Text view]

   1  <?php
   2  /*+**********************************************************************************
   3   * The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9   ************************************************************************************/
  10  
  11  require_once  'modules/Reports/ReportRun.php';
  12  require_once  'include/ChartUtils.php';
  13  require_once  'include/utils/CommonUtils.php';
  14  
  15  Class CustomReportUtils {
  16  
  17  	public static function getCustomReportsQuery($reportid, $filterlist=null) {
  18          global $current_user;
  19          $reportnew = ReportRun::getInstance($reportid);
  20          $groupby = $reportnew->getGroupingList($reportid);
  21          $showcharts = false;
  22          if (!empty($groupby)) {
  23              $showcharts = true;
  24          }
  25          $reportQuery = $reportnew->sGetSQLforReport($reportid, $filterlist, 'HTML', $showcharts);
  26          return $reportQuery;
  27      }
  28  
  29  	public static function getReportChart($reportid, $chartType) {
  30          global $log, $adb;
  31          $oReportRun = ReportRun::getInstance($reportid);
  32          $groupBy = $oReportRun->getGroupingList($reportid);
  33          foreach ($groupBy as $key => $value) {
  34              // $groupByConditon = explode(" ",$value);
  35              //$groupByNew = explode("'",$groupByConditon[0]);
  36              list($tablename, $colname, $module_field, $fieldname, $single) = split(":", $key);
  37              list($module, $field) = split("_", $module_field);
  38              $fieldDetails = $key;
  39              break;
  40          }
  41          $queryReports = self::getCustomReportsQuery($reportid);
  42  
  43          $queryResult = $adb->pquery($queryReports, array());
  44          //ChartUtils::generateChartDataFromReports($queryResult, strtolower($groupByNew[1]));
  45          if ($chartType == 'horizontalbarchart') {
  46              $Chart = ChartUtils::getReportBarChart($queryResult, strtolower($module_field), $fieldDetails, $reportid);
  47          } else if ($chartType == 'verticalbarchart') {
  48              $Chart = ChartUtils::getReportBarChart($queryResult, strtolower($module_field), $fieldDetails, $reportid, 'vertical');
  49          } else if ($chartType == 'piechart') {
  50              $Chart = ChartUtils::getReportPieChart($queryResult, strtolower($module_field), $fieldDetails, $reportid);
  51          }
  52          return $Chart;
  53      }
  54  
  55  	public static function IsDateField($reportColDetails) {
  56          list($tablename, $colname, $module_field, $fieldname, $typeOfData) = split(":", $reportColDetails);
  57          if ($typeOfData == "D") {
  58              return true;
  59          } else {
  60              return false;
  61          }
  62      }
  63  
  64  	public static function getAdvanceSearchCondition($fieldDetails, $criteria, $fieldvalue) {
  65          list($tablename, $colname, $module_field, $fieldname, $single) = split(":", $fieldDetails);
  66          list($module, $field) = split("_", $module_field);
  67          list($year, $month, $day) = split("-", $fieldvalue);
  68          $grteqCondition = 'h';
  69          $eqCondition = 'e';
  70          $lessCondititon = 'l';
  71          $json = new Zend_Json();
  72          $advft_criteria_groups = array('1' => array('groupcondition' => null));
  73          $advft_criteria = array();
  74          if (empty($fieldvalue)) {
  75              $condition = 'query=true&searchtype=advance&advft_criteria=' . $json->encode($advft_criteria) . '&advft_criteria_groups=' . $json->encode($advft_criteria_groups);
  76              return $condition;
  77          }
  78          if (strtolower($criteria) == 'year') {
  79              $firstDate = DateTimeField::convertToUserFormat($year);
  80              $secondDate = DateTimeField::convertToUserFormat($year + 1);
  81              $condition = array(
  82                  array(
  83                      'groupid' => 1,
  84                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
  85                      'comparator' => $grteqCondition,
  86                      'value' => $firstDate,
  87                      'columncondition' => 'and'
  88                  ),
  89                  array(
  90                      'groupid' => 1,
  91                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
  92                      'comparator' => $lessCondititon,
  93                      'value' => $secondDate,
  94                      'columncondition' => 'and'
  95                  )
  96              );
  97              $conditionJson = urlencode($json->encode($condition));
  98              $condition = "query=true&searchtype=advance&advft_criteria=" . $conditionJson . "&advft_criteria_groups=" . urlencode($json->encode($advft_criteria_groups));
  99          } else if (strtolower($criteria) == 'month') {
 100              $date = DateTimeField::convertToUserFormat($year . "-" . $month);
 101              $endMonth = $month + 1;
 102              if ($endMonth < 10) {
 103                  $endMonth = "0" . $endMonth;
 104              }
 105              $endDate = DateTimeField::convertToUserFormat($year . "-" . $endMonth . "-01");
 106              $condition = array(
 107                  array(
 108                      'groupid' => 1,
 109                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
 110                      'comparator' => $grteqCondition,
 111                      'value' => $date,
 112                      'columncondition' => 'and'
 113                  ),
 114                  array(
 115                      'groupid' => 1,
 116                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
 117                      'comparator' => $lessCondititon,
 118                      'value' => $endDate,
 119                      'columncondition' => 'and'
 120                  )
 121              );
 122              $conditionJson = urlencode($json->encode($condition));
 123              $condition = "query=true&searchtype=advance&advft_criteria=" . $conditionJson . "&advft_criteria_groups=" . urlencode($json->encode($advft_criteria_groups));
 124          } else if (strtolower($criteria) == 'quarter') {
 125              $condition = "";
 126              $quraterNum = $month / 3;
 127              if ($month % 3 == 0)
 128                  $quraterNum = $quraterNum - 1;
 129              $startingMonth = 3 * ($quraterNum);
 130              $quarterMonth = $startingMonth;
 131              if ($quarterMonth < 10) {
 132                  $quarterMonth = "0" . $quarterMonth;
 133              }
 134              $date = DateTimeField::convertToUserFormat($year . "-" . $quarterMonth . "-01");
 135              $quarterMonth +=3;
 136              if ($quarterMonth < 10) {
 137                  $quarterMonth = "0" . $quarterMonth;
 138              }
 139              $date1 = DateTimeField::convertToUserFormat($year . "-" . $quarterMonth . "-01");
 140              $condition = array(
 141                  array(
 142                      'groupid' => 1,
 143                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
 144                      'comparator' => $grteqCondition,
 145                      'value' => $date,
 146                      'columncondition' => 'and'
 147                  ),
 148                  array(
 149                      'groupid' => 1,
 150                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
 151                      'comparator' => $lessCondititon,
 152                      'value' => $date1,
 153                      'columncondition' => 'and'
 154                  )
 155              );
 156              $conditionJson = urlencode($json->encode($condition));
 157              $condition = "query=true&searchtype=advance&advft_criteria=" . $conditionJson . "&advft_criteria_groups=" . urlencode($json->encode($advft_criteria_groups));
 158          } elseif (strtolower($criteria) == 'none') {
 159              $date = DateTimeField::convertToUserFormat($fieldvalue);
 160              $condition = array(
 161                  array(
 162                      'groupid' => 1,
 163                      'columnname' => $tablename . ':' . $colname . ':' . $colname . ':' . $module_field . ':' . $single,
 164                      'comparator' => $eqCondition,
 165                      'value' => $date,
 166                      'columncondition' => 'and'
 167                  )
 168              );
 169              $conditionJson = urlencode($json->encode($condition));
 170              $condition = "query=true&searchtype=advance&advft_criteria=" . $conditionJson . "&advft_criteria_groups=" . urlencode($json->encode($advft_criteria_groups));
 171          }
 172          return $condition;
 173      }
 174  
 175  	public static function getXAxisDateFieldValue($dateFieldValue, $criteria) {
 176          global $log;
 177          $timeStamp = strtotime($dateFieldValue);
 178          $year = date('Y', $timeStamp);
 179          $month = date('m', $timeStamp);
 180          $day = date('d', $timeStamp);
 181          $xaxisLabel = "";
 182          if (strtolower($criteria) == 'year') {
 183              $xaxisLabel = "Year $year";
 184          } else if (strtolower($criteria) == 'month') {
 185              $monthLabel = date('M', $timeStamp);
 186              $xaxisLabel = "$monthLabel $year";
 187          } else if (strtolower($criteria) == "quarter") {
 188              $monthNum = date('n', $timeStamp);
 189              $quarter = (($monthNum - 1) / 3) + 1;
 190              $textNumArray = array('', 'I', 'II', 'III', 'IV');
 191              $textNum = $textNumArray[$quarter];
 192              $xaxisLabel = $textNumArray[$quarter] . " Quarter of " . $year;
 193          } else if (strtolower($criteria) == 'none') {
 194              $xaxisLabel = DateTimeField::convertToUserFormat($dateFieldValue);
 195          }
 196          return $xaxisLabel;
 197      }
 198  
 199  	public static function getEntityTypeFromName($entityName, $modules=false) {
 200          global $adb;
 201  
 202          if($modules == false) {
 203              $modules = array();
 204              $result = $adb->pquery('SELECT modulename FROM vtiger_entityname', array());
 205              $noOfModules = $adb->num_rows($result);
 206              for($i=0; $i<$noOfModules; ++$i) {
 207                  $modules[] = $adb->query_result($result, $i, 'modulename');
 208              }
 209          }
 210          foreach ($modules as $referenceModule) {
 211              $entityFieldInfo = getEntityFieldNames($referenceModule);
 212              $tableName = $entityFieldInfo['tablename'];
 213              $fieldsName = $entityFieldInfo['fieldname'];
 214  
 215              if(is_array($fieldsName)) {
 216                  $concatSql = 'CONCAT('. implode(",' ',", $fieldsName). ')';
 217              } else {
 218                  $concatSql = $fieldsName;
 219              }
 220  
 221              $entityQuery = "SELECT 1 FROM $tableName WHERE $concatSql = ?";
 222              $entityResult = $adb->pquery($entityQuery, array($entityName));
 223              $num_rows = $adb->num_rows($entityResult);
 224              if ($num_rows > 0) {
 225                  return $referenceModule;
 226              }
 227          }
 228      }
 229  
 230  }
 231  
 232  ?>