copy=> MASK_CR_REPORT_FORMS_ENABLED // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ // Init if ($rpObjType == "") : $rpObjType = "cs"; endif; $pageTitel = getLngt("BERICHTE"); $pageHeadline = "" . $pageTitel . ":"; $authCheck = FALSE; $hqId = "0"; $isMeta = "0"; $f_secretFileName = ""; $statisticValueCurrentYear = ""; $rpObjId = ""; $whereClauseRp = ""; if ($rpObjType == "cs") : $pageTitel = getLngt("KUNDENBERICHTE"); $authCheck = authCheckEmployeeRights($emp_id, "0"); $metatypeReportType = "report_type"; // metatype.mt_type $titleArrayObjName = getLngt("Kunde  "); // Parameters $constMaskRpReportFormsEnabled = getParameterValue("0", "MASK_CS_REPORT_FORMS_ENABLED", $hqId); $constMaskRpReportFormsMapping = getParameterValue("0", "MASK_CS_REPORT_FORMS_MAPPING", $hqId); $limitOfRowsDisplayed = getParameterValue("0", "MASK_CS_REPORT_ROWS_DISPLAYED", "0"); // For limitation of the rows displayed // Get the cs_id if "g_cs_eid" does exist $cs_id = ""; if ($g_cs_eid != "") : $cs_id = getFieldValueFromId("customer", "cs_eid", $g_cs_eid, "cs_id"); $rpObjId = $cs_id; elseif ($g_csc_id != "") : $cs_id = getFieldValueFromId("costcenter", "csc_id", $g_csc_id, "cs_id"); $rpObjId = $cs_id; $g_cs_eid = getFieldValueFromId("customer", "cs_id", $cs_id, "cs_eid"); endif; // Get status of the customer according to being meta customer $csParentIdArray = array(); if ($cs_id != "") : if (existsEntry("customer",array("cs_id_parent",$cs_id))) : $isMeta = "1"; $csParentIdArray = getColVectorFromDB2Array("customer","cs_id_parent",$cs_id,"cs_id",""); array_push($csParentIdArray, $cs_id); // meta customer inclusive endif; endif; // Clause for report statistics if ($rpObjId != "") : $cmpId = getFieldValueFromId("customer", "cs_id", $cs_id, "cmp_id"); $tmpFields = getFieldsValueFromId("company","cmp_id",$cmpId,array("cmp_comp","cmp_comp2")); $out_1 = "" . $tmpFields[0] . ""; $out_2 = $tmpFields[1]; $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid = '" . $rpObjId . "' AND "; if ($isMeta == "1") : $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid IN (" . implode(",", $csParentIdArray) . ") AND "; endif; endif; // Statistic values of the customer if ($rpObjId != "") : // Statistic business volume from 01.01. of the current year to the current day $tmpFromDate = getDateTime("year") . "-01-01"; $tmpToDate = getDateTime("3"); $statisticValueCurrentYear = getCsStatistic ($rpObjId, $tmpFromDate, $tmpToDate); // Year before in the same interval $tmpFromDate = getDateTime("lastyear") . "-01-01"; $tmpToDate = getDateTime("date_lastyear"); $statisticValueLastYear = getCsStatistic ($rpObjId, $tmpFromDate, $tmpToDate); // Year before complete $tmpFromDate = getDateTime("lastyear") . "-01-01"; $tmpToDate = getDateTime("lastyear") . "-12-31"; $statisticValueLastYearComplete = getCsStatistic ($rpObjId, $tmpFromDate, $tmpToDate); endif; // Link to invoice data of the customer if ($rpObjId != "") : $invoiceOverview = "" . getLngt("Rechnungsübersicht") . ""; endif; elseif ($rpObjType == "cr") : $pageTitel = getLngt("TRANSPORTEURSBERICHTE"); $authCheck = authCheckEmployeeRights($emp_id, "1"); $metatypeReportType = "report_type_2"; // metatype.mt_type $titleArrayObjName = getLngt("Transporteur  "); // Parameters $constMaskRpReportFormsEnabled = getParameterValue("0", "MASK_CR_REPORT_FORMS_ENABLED", $hqId); $constMaskRpReportFormsMapping = getParameterValue("0", "MASK_CR_REPORT_FORMS_MAPPING", $hqId); $limitOfRowsDisplayed = getParameterValue("0", "MASK_CR_REPORT_ROWS_DISPLAYED", "0"); // For limitation of the rows displayed // Get the (g_)cr_eid if "g_cr_id" does exist if ($g_cr_id != "") : $g_cr_eid = getFieldValueFromId("courier", "cr_id", $g_cr_id, "cr_eid"); $rpObjId = $g_cr_id; endif; // Clause for report statistics if ($rpObjId != "") : $cmpId = getFieldValueFromId("courier", "cr_id", $g_cr_id, "cmp_id"); $tmpFields = getFieldsValueFromId("company","cmp_id",$cmpId,array("cmp_comp","cmp_comp2")); $out_1 = $tmpFields[0]; $out_2 = $tmpFields[1]; $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid = '" . $rpObjId . "' AND "; if (FALSE && $isMeta == "1") : $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid IN (" . implode($crParentIdArray, ",") . ") AND "; endif; endif; elseif ($rpObjType == "pt") : $pageTitel = getLngt("INTERESSENTENBERICHTE"); $authCheck = authCheckEmployeeRights($emp_id, "0"); $metatypeReportType = "report_type_3"; // metatype.mt_type $titleArrayObjName = getLngt("Interessent  "); // Parameters $constMaskRpReportFormsEnabled = getParameterValue("0", "MASK_PT_REPORT_FORMS_ENABLED", $hqId); $constMaskRpReportFormsMapping = getParameterValue("0", "MASK_PT_REPORT_FORMS_MAPPING", $hqId); $limitOfRowsDisplayed = getParameterValue("0", "MASK_PT_REPORT_ROWS_DISPLAYED", "0"); // For limitation of the rows displayed // Get the (g_)cr_eid if "g_cr_id" does exist if ($g_cr_id != "") : $g_cr_eid = getFieldValueFromId("courier", "cr_id", $g_cr_id, "cr_eid"); $rpObjId = $g_cr_id; endif; // Get the pt_id if "g_pt_cs_eid" does exist (e.g. init the page) if ($g_pt_cs_eid != "") : $pt_id = getFieldValueFromId("prospect", "pt_cs_eid", $g_pt_cs_eid, "pt_id"); $rpObjId = $pt_id; else : $g_pt_cs_eid = getFieldValueFromId("prospect", "pt_id", $pt_id, "pt_cs_eid"); $rpObjId = $pt_id; endif; // Clause for report statistics if ($rpObjId != "") : $out_1 = getFieldValueFromId("prospect", "pt_id", $pt_id, "pt_cmp_comp"); $out_2 = getFieldValueFromId("prospect", "pt_id", $pt_id, "pt_cmp_comp2"); $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid = '" . $rpObjId . "' AND "; if (FALSE && $isMeta == "1") : $whereClauseRp = " rp.rp_objtype = '" . $rpObjType . "' AND rp.rp_objid IN (" . implode($ptParentIdArray, ",") . ") AND "; endif; endif; endif; $pageHeadline .= "     " . ($rpObjId != "" ? $tmpFields[0] . " " . $tmpFields[1] . "  " : getLngt(" - ALLE -  ")); include_once ("../admin/menu.php"); include_once ("../include/html.inc.php"); getCurrentScript(__FILE__); if (!$authCheck || !authCheckEmployeeRights($emp_id, "16")) : gotoReferer("1"); endif; // Mandator filter (related here to the USER !!!) if ($f_hq_id == "") : $f_hq_id = array(); endif; if (count($f_hq_id) == 0) : array_push($f_hq_id, $hq_id); endif; if ($f_rpReportType == "") : $f_rpReportType = "0"; endif; if ($f_sort == "") : $f_sort = "0"; endif; // ORDER BY column "$fieldArray[$f_sort]" if ($f_rp_stat_sort == "") : $f_rp_stat_sort = ""; endif; // ORDER BY column "$fieldArray[$f_rp_stat_sort]" if ($f_filter_rpType == "") : $f_filter_rpType = "0"; endif; // Filter "rp_reporttype" if ($limitOfRowsDisplayed == "") : $limitOfRowsDisplayed = "1000"; endif; $f_text = trim($f_text); // Text of an entry if ($f_text) : $f_text = str_replace("'", "´", $f_text); endif; $confirmForNewAppointment = false; // Get the array for formatting the database-values for the output according to the defined type $outputFormatField = defineOutputFormats(); // Check range of date. All fields have to be set if ($day_from == "" || $month_from == "" || $year_from == "" || $day_to == "" || $month_to == "" || $year_to == "") : // Initialize date-ranges to the current date // $fromDateRange = getDateTime("1") . "000000"; $currentMonth = getDateTime("month"); if ($currentMonth >= 7) : $fromDateRange = getDateTime("year") . "0701" . "000000"; else : $fromDateRange = getDateTime("year") . "0101" . "000000"; endif; $toDateRange = getDateTime("1") . "235959"; // $day_from = getDateTime("day"); $day_from = "01"; // $month_from = getDateTime("month"); $month_from = "01"; if ($currentMonth >= 7) : $month_from = "07"; endif; $year_from = getDateTime("year"); $day_to = getDateTime("day"); $month_to = getDateTime("month"); $year_to = getDateTime("year"); else : $fromDateRange = $year_from . pad($month_from,2) . pad($day_from,2) . "000000"; $toDateRange = $year_to . pad($month_to,2) . pad($day_to,2) . "235959"; endif; // Get report types $reportTypeArray = getColVectorFromDB2Array("metatype","mt_type",$metatypeReportType,"mt_value","mt_sort"); $reportTypeArray = array_merge (array(" "), $reportTypeArray); // Inserts the first element to position "0" ("shift right") // Checkbox if ($f_rpConfidential == "" || count($f_rpConfidential) == 0) : $f_rpConfidential = "0"; else: $f_rpConfidential = "1"; endif; // *********** // * Actions * // *********** if ($f_act == "insertRpEntry") : if ($rpObjId != "" && $rpObjType != "") : if ($f_text != "") : $currentTime = getDateTime("0"); // $execDatetime = getDateTime("format",array($f_hour,$f_minute,0,$selMonth,$selDay,$selYear),"Y-m-d H:i:s"); // $endDatetime = getDateTime("format",array($f_hour_to,$f_minute_to,0,$selMonth,$selDay,$selYear),"Y-m-d H:i:s"); insertStmt("phoenix_group.report_process", array("usr_id", $usr_id, "hq_id", $hq_id, "rp_reporttype", $f_rpReportType, "rp_text", $f_text, "rp_createtime", $currentTime, "rp_objId", $rpObjId, "rp_objtype", $rpObjType, "rp_confidential", $f_rpConfidential)); $rp_id_new = getLastInsertId(); $f_rpReportType = "0"; // Reset Type $f_rpConfidential = "0"; // Reset Confidence $confirmForNewAppointment = true; // Get all appointments of the day the report is written. Get appointments associated to the customer of the inserted report only. $constMaskRpEnableDeactivationApWarning = getParameterValue("0", "MASK_RP_ENABLE_DEACTIVATION_APPOINTMENT_WARNING_OF_SAME_DAY", "0"); if ($constMaskRpEnableDeactivationApWarning == "1") : $currentDate = getDateTime("3"); $tmpApArray = getColVectorFromDB2ArrayByClause("phoenix_group.appointment", "ap_id", "cs_id = '" . $rpObjId . "' AND ap_execdate >= '" . $currentDate . " 00:00:00' AND ap_execdate <= '" . $currentDate . " 23:59:59' AND ap_category_4 = '1'", "", ""); $tmpApArrayLen = count($tmpApArray); if ($tmpApArrayLen > 0) : updateStmt("phoenix_group.appointment","","",array("ap_category_4", "2"),"ap_id IN (" . implode(",", $tmpApArray) . ")"); endif; endif; else: $statusMessage = getLngt("Erfassen Sie bitte einen Text für einen Kundeneintrag!"); endif; else: $statusMessage = getLngt("Es wurde kein Kunde gewählt!"); endif; endif; if ($f_act == "removeRpEntry") : if ($f_rpIdToRemove != "") : deleteStmt("phoenix_group.report_process","rp_id = ".$f_rpIdToRemove); $f_rpIdToRemove = ""; // Reset ID else: $statusMessage = getLngt("Es wurde kein Eintrag zum Löschen selektiert!"); endif; endif; if ($f_act == "updateRpEntry") : if ($f_rpIdToUpdate != "" && $f_text != "") : updateStmt("phoenix_group.report_process", "rp_id", $f_rpIdToUpdate, array("rp_reporttype", $f_rpReportType, "rp_text", $f_text, "rp_confidential", $f_rpConfidential)); $f_rpIdToUpdate = ""; // Reset ID $f_rpIdToCopy = ""; // Reset ID $f_rpReportType = "0"; // Reset Type $f_rpConfidential = "0"; // Reset Confidence $confirmForNewAppointment = true; else: $statusMessage = getLngt("Eine Aktualisierung fand nicht statt!"); endif; endif; if ($f_act == "reportStatistic") : // Split statistic by report type if ($f_rpstat_splitbyreporttype == "" || count($f_rpstat_splitbyreporttype) == 0) : $f_rpstat_splitbyreporttype = false; else: $f_rpstat_splitbyreporttype = true; endif; // Split statistic by company type if ($f_rpstat_splitbycmptype == "" || count($f_rpstat_splitbycmptype) == 0) : $f_rpstat_splitbycmptype = false; else: $f_rpstat_splitbycmptype = true; endif; // Export report statistic data as CSV file if ($f_rpstat_export == "" || count($f_rpstat_export) == 0) : $f_rpstat_export = false; else: $f_rpstat_export = true; endif; $fieldClauseRpType = ""; $fromClauseRpType = ""; $groupByClauseRpType = ""; $whereClauseRpType = ""; $orderByClauseRpType = ""; if ($f_rpstat_splitbyreporttype) : $fieldClauseRpType = "mt.mt_value,"; $fromClauseRpType = ", metatype AS mt"; $whereClauseRpType = " mt.mt_type = '" . $metatypeReportType . "' AND mt.mt_sort = rp.rp_reporttype AND "; $groupByClauseRpType = ", rp.rp_reporttype"; $orderByClauseRpType = ", rp.rp_reporttype"; endif; $fieldClauseCmpType = ""; $fromClauseCmpType = ""; $whereClauseCmpType = ""; $groupByClauseCmpType = ""; $orderByClauseCmpType = ""; if ($f_rpstat_splitbycmptype) : if ($rpObjType == "cs") : $fieldClauseCmpType = "mt2.mt_value AS mt2_value,"; $fromClauseCmpType = ", customer AS cs, company AS cmp, metatype AS mt2"; $whereClauseCmpType = " rp.rp_objid = cs.cs_id AND cmp.cmp_id = cs.cmp_id AND mt2.mt_type = 'customer_type' AND mt2.mt_sort = cmp.cmp_type AND "; $groupByClauseCmpType = ", cmp.cmp_type"; $orderByClauseCmpType = ", cmp.cmp_type"; endif; endif; // Generate list with header $titleArray = array(getLngt("Zentrale  "),getLngt("Name  "),getLngt("Vorname  ")); $fieldArray = array("hq_name","usr_name","usr_firstname"); $aligns = "l,l,l"; $widths = "80,150,150"; if ($f_rpstat_splitbyreporttype) : $titleArray[] = getLngt("Berichtstyp"); $fieldArray[] = "mt_value"; $aligns .= ",l"; $widths .= ",150"; endif; if ($f_rpstat_splitbycmptype) : if ($rpObjType == "cs") : $titleArray[] = getLngt("Kundentyp"); $fieldArray[] = "mt2_value"; $aligns .= ",l"; $widths .= ",150"; endif; endif; $titleArray[] = getLngt("Anzahl Berichte  "); $fieldArray[] = "reports"; $aligns .= ",r"; $widths .= ",100"; $tmpSortField = ""; if ($f_rp_stat_sort != "") : if (false && $fieldArray[$f_rp_stat_sort] == "field_xxx") : $tmpSortField = "rp.rp_reporttype"; else : $tmpSortField = $fieldArray[$f_rp_stat_sort]; endif; endif; if ($tmpSortField == "") : $tmpSortField = "usr.usr_name, usr.usr_firstname" . $orderByClauseRpType . $orderByClauseCmpType; endif; $orderByClause = $tmpSortField; /* $sqlquery = "SELECT hq.hq_name, usr.usr_firstname, usr.usr_name, " . $fieldClauseRpType . $fieldClauseCmpType . " count(*) AS reports" . " FROM phoenix_group.report_process AS rp, phoenix.user AS usr, phoenix.headquarters AS hq" . $fromClauseRpType . $fromClauseCmpType . " WHERE rp.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND rp.hq_id = hq.hq_id AND" . " rp.usr_id = usr.usr_id AND rp.rp_objtype = '" . $rpObjType . "' AND " . $whereClauseRp . $whereClauseRpType . $whereClauseCmpType . " rp.rp_createtime >= '$fromDateRange' AND rp.rp_createtime <= '$toDateRange'" . " GROUP BY usr.usr_id" . $groupByClauseRpType . $groupByClauseCmpType . " ORDER BY " . $orderByClause; */ $sqlquery = "SELECT hq.hq_name, usr.usr_firstname, IF(LENGTH(CONCAT(usr.usr_firstname,usr.usr_name)) > 0, usr.usr_name, '" . getLngt("Nutzer gelöscht!") . "') AS usr_name, " . $fieldClauseRpType . $fieldClauseCmpType . " count(*) AS reports" . " FROM phoenix_group.report_process AS rp LEFT JOIN phoenix.user AS usr ON rp.usr_id = usr.usr_id, phoenix.headquarters AS hq" . $fromClauseRpType . $fromClauseCmpType . " WHERE rp.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND rp.hq_id = hq.hq_id AND" . " rp.rp_objtype = '" . $rpObjType . "' AND " . $whereClauseRp . $whereClauseRpType . $whereClauseCmpType . " rp.rp_createtime >= '$fromDateRange' AND rp.rp_createtime <= '$toDateRange'" . " GROUP BY rp.hq_id, usr.usr_id" . $groupByClauseRpType . $groupByClauseCmpType . " ORDER BY " . $orderByClause; // echo $sqlquery . "
"; $result = $db->query($sqlquery); if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage()); $alignArray = spliti(",",$aligns); $alignTitles = "center"; $widthArray = spliti(",",$widths); $summationField = "reports"; // $postParserField = ""; $mode = "1"; // Output from DB-RESULT $fieldSortArray = $fieldArray; $sortDBField = "YES"; // Used in following include-file for sorting per column; $javaScriptFunctionNameForSort = "finishPageReportStatisticSort"; include ("../include/inc_list_defineoutput.inc.php"); $result->free(); $sortDBField = "YES"; // Reset $javaScriptFunctionNameForSort = ""; // Reset // Define output table $reportStatistic = $tableHeader . $tableBody; $tableHeader = ""; $tableBody = ""; // Checkboxes: // - Split by report category // - Export of the report statistic $reportStatistic .= ""; $reportStatistic .= ""; if ($rpObjType == "cs") : $reportStatistic .= ""; endif; $reportStatistic .= ""; $reportStatistic .= "
" . getLngt("Aufteilung nach Berichtstyp:") . " 
" . getLngt("Aufteilung nach Kundentyp:") . " 
" . getLngt("Statistik exportieren:") . " 
"; // *** File output *** if ($f_rpstat_export) : // Remove old files foreach (glob("../temp/download/*.rpstat.csv") as $fileNameToBoRemoved) { if ((time() - filemtime($fileNameToBoRemoved)) > 60) : if (file_exists($fileNameToBoRemoved)) : unlink($fileNameToBoRemoved); endif; endif; } // 2nd request for file output $result = $db->query($sqlquery); if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage()); // Define output parameters $delimiter = ';'; $writeHeader = "YES"; $filename = ""; $mode = "1"; include_once ("../include/inc_list_defineoutput2file.inc.php"); $outputLinesArrayLen = count($outputLinesArray); if ($outputLinesArrayLen > 0) : if ($filename == "") : $filename = getLngt("BERICHTSSTATISTIK"); endif; $f_fileName = $filename . "_" . date("YmdHis"); // $f_secretFileName = md5($f_fileName) . ".rpstat.csv"; $f_secretFileName = $f_fileName . ".rpstat.csv"; if ($outputHeader != "") : writeToFile("../temp/download/" . $f_secretFileName, $outputHeader); // write header if requested endif; for ($i = 0; $i < $outputLinesArrayLen; $i++) : // Post parsing if necessary if ($postParserField != "") : if ($postParserFunction == "postParseReportStatistic") : $outputLinesArray[$i] = postParseReportStatistic($outputLinesArray[$i]); endif; endif; writeToFile("../temp/download/" . $f_secretFileName, $outputLinesArray[$i]); // write statistic data endfor; else : $statusMessage = getLngt("Es wurden keine Daten gefunden! Eine Datei wurde deshalb nicht generiert!"); $f_rpstat_export = false; endif; endif; endif; function postParseHistory ($textToParse) { global $emp_id, $usr_id, $rpObjId, $reportTypeArray; $textToParse = trim($textToParse); if ($textToParse != "") : $count = 1; // Iterate ALL occurrences of "..." while (!(strpos($textToParse, "") === FALSE)) { $beginTagPosBegin = strpos($textToParse, ""); $beginTagPosEnd = $beginTagPosBegin + 12; $endTagPosBegin = strpos($textToParse, ""); $endTagPosEnd = $endTagPosBegin + 13; if ($beginTagPosEnd < $endTagPosBegin) : $tagContent = substr($textToParse, $beginTagPosEnd, $endTagPosBegin - $beginTagPosEnd); $tagContentArray = spliti(",",$tagContent); $tmpTagContent = $tagContentArray[0]; $tmpTagUsrId = $tagContentArray[1]; $tmpTagRpType = $tagContentArray[2]; $tmpTagRpConfidential = $tagContentArray[3]; if ($tmpTagContent != "") : // Set background color if