PageRenderTime 61ms CodeModel.GetById 19ms RepoModel.GetById 0ms app.codeStats 0ms

/modules/Reports/CustomReportUtils.php

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