"; echo "f_roleView : " . $f_roleView . "
"; echo "fromDateRange : " . $fromDateRange . "
"; echo "toDateRange : " . $toDateRange . "
"; echo "csEid : " . $csEid . "
"; echo "crEid : " . $crEid . "
"; echo "orderByClause : " . $orderByClause . "
"; echo "aligns : " . $aligns . "
"; echo "f_statusMode : " . $f_statusMode . "
"; echo "f_dateMode : " . $f_dateMode . "
"; echo "f_showDateAndTime : " . $f_showDateAndTime . "
"; echo "f_crvh_sid : " . $f_crvh_sid . "
"; echo "f_direction_sort : " . $f_direction_sort . "
"; echo "f_filter : " . $f_filter . "
"; echo "f_filter2 : " . $f_filter2 . "
"; echo "f_filter3 : " . $f_filter3 . "
"; echo "f_filter4 : " . $f_filter4 . "
"; echo "f_filter5 : " . $f_filter5 . "
"; echo "f_filter6 : " . $f_filter6 . "
"; echo "f_filter7 : " . $f_filter7 . "
"; echo "f_filter8 : " . $f_filter8 . "
"; echo "f_filter9 : " . $f_filter9 . "
"; echo "f_filter10 : " . $f_filter10 . "
"; echo "f_filter11 : " . $f_filter11 . "
"; echo "f_filter12 : " . $f_filter12 . "
"; echo "f_filter13 : " . $f_filter13 . "
"; echo "f_filter14 : " . $f_filter14 . "
"; echo "f_price_formular : " . $f_price_formular . "
"; echo "f_filter_a : " . $f_filter_a . "
"; echo "f_filter_interval : " . $f_filter_interval . "
"; echo "g_crvh_filter : " . $g_crvh_filter . "
"; echo "f_mode_statistic : " . $f_mode_statistic . "
"; echo "f_filter_calc : " . $f_filter_calc . "
"; echo "f_service : " . $f_service . "
"; echo "f_servicetype : " . $f_servicetype . "
"; echo "f_jb_service : " . $f_jb_service . "
"; echo "count(f_jb_service) : " . count($f_jb_service) . "
"; echo "f_split_jb_services : " . $f_split_jb_services . "
"; echo "f_jb_specifics : " . $f_jb_specifics . "
"; echo "f_cs_meta : " . $f_cs_meta . "
"; echo "f_cr_meta : " . $f_cr_meta . "
"; echo "f_cr_parent : " . $f_cr_parent . "
"; echo "f_cr_pda_actions : " . $f_cr_pda_actions . "
"; echo "f_show_invoice_address : " . $f_show_invoice_address . "
"; echo "f_show_delivery_address : " . $f_show_delivery_address . "
"; echo "f_show_master_data : " . $f_show_master_data . "
"; echo "summationField : " . $summationField . "
"; echo "summationField2 : " . $summationField2 . "
"; echo "summationField3 : " . $summationField3 . "
"; echo "summationField4 : " . $summationField4 . "
"; echo "summationField5 : " . $summationField5 . "
"; echo "summationField6 : " . $summationField6 . "
"; echo "summationField7 : " . $summationField7 . "
"; echo "summationField8 : " . $summationField8 . "
"; echo "summationField9 : " . $summationField9 . "
"; echo "uniqueSearchFields : " . $uniqueSearchFields . "
"; echo "hour_from : " . $hour_from . "
"; echo "minute_from : " . $minute_from . "
"; echo "hour_to : " . $hour_to . "
"; echo "minute_to : " . $minute_to . "
"; echo "fromTimeRange : " . $fromTimeRange . "
"; echo "toTimeRange : " . $toTimeRange . "
"; echo "f_cmp_new_date : " . $f_cmp_new_date . "
"; echo "f_cs_become_cs_date : " . $f_cs_become_cs_date . "
"; echo "f_net_gross : " . $f_net_gross . "
"; echo "f_group : " . $f_group . "
"; echo "f_groupLen : " . $f_groupLen . "
"; echo "f_staticGroup : " . $f_staticGroup . "
"; echo "f_staticGroupLen : " . $f_staticGroupLen . "
"; echo "f_priceMode : " . $f_priceMode . "
"; echo "statisticAllValues : " . $statisticAllValues . "
"; echo "f_editCsMode : " . $f_editCsMode . "
"; echo "station_based_group_field : " . $station_based_group_field . "
"; echo "f_bookedJobsOnly : " . $f_bookedJobsOnly . "
"; echo "f_permanentJobsOnly : " . $f_permanentJobsOnly . "
"; echo "f_filter_jb_weight_from : " . $f_filter_jb_weight_from . "
"; echo "f_filter_jb_length_from : " . $f_filter_jb_length_from . "
"; echo "f_filter_jb_width_from : " . $f_filter_jb_width_from . "
"; echo "f_filter_jb_height_from : " . $f_filter_jb_height_from . "
"; echo "f_filter_jb_position_from : " . $f_filter_jb_position_from . "
"; echo "f_filter_jb_weight_to : " . $f_filter_jb_weight_to . "
"; echo "f_filter_jb_length_to : " . $f_filter_jb_length_to . "
"; echo "f_filter_jb_width_to : " . $f_filter_jb_width_to . "
"; echo "f_filter_jb_height_to : " . $f_filter_jb_height_to . "
"; echo "f_filter_jb_position_to : " . $f_filter_jb_position_to . "
"; endif; // Decision to use the job archive or normal tables getDBNames($dbhistory); // Mapping exportcategoryfields from "service statistic" to "normal statistic" $stmNumberArrayTmp = array(101 => 30, 102 => 31, 103 => 32, 104 => 33, 105 => 34, 201 => 10, 202 => 11, 203 => 12, 204 => 13, 205 => 14, 301 => 60, 302 => 61, 303 => 62, 304 => 63, 305 => 64); $stmNumberTmp = $stmNumber; if (($stmNumber >= 101 && $stmNumber <= 109) || ($stmNumber >= 201 && $stmNumber <= 209) || ($stmNumber >= 301 && $stmNumber <= 309)) : $stmNumberTmp = $stmNumberArrayTmp[$stmNumber]; endif; // Computation of the amount and the business volume according to involving table "tourservice" or using the filed "jb_totalprice" $fieldClauseAmountFormular = "COUNT(jb.jb_id)"; $fieldClauseMarkupFormular = ""; $fromClauseJobpriceFormular = ""; if ((($f_priceMode >= "1" && $f_priceMode <= "3") || $f_priceMode == "19") && $f_price_formular > "0") : $fromClauseJobpriceFormular = " LEFT JOIN jobprice AS jbprc_10 ON jbprc_10.jb_id = jb.jb_id AND jbprc_10.mt_sort = '10' "; // jb_cr_subprice endif; $fieldClausePriceFormular = ""; $fromClausePriceFormular = ""; $whereClausePriceFormular = ""; $fromClauseCrvh = ""; $whereClauseCrvh = ""; $tmpPF = ""; $tmpMF = ""; writeToFile("../log/mc_stat_special.log", "f_priceMode = " . $f_priceMode); if ($f_priceMode == "0" || $statisticAllValues) : // "Auftragswert (= Kundenumsatz)" $tmpPF = " SUM(jb.jb_totalprice) "; if ($f_price_formular == "1") : $tmpPF = " SUM(jb.jb_subtotalprice) "; elseif ($f_price_formular == "2") : $tmpPF = " SUM(jb.jb_totalprice - jb.jb_subtotalprice - jb.jb_serviceprice) "; elseif ($f_price_formular == "3") : // Nota bene: $fieldClauseMarkupFormular contains the markup in an extra field; $fieldClausePriceFormular will be the same defined above $tmpMF = " SUM(jb.jb_totalprice - jb.jb_subtotalprice - jb.jb_serviceprice) "; endif; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; if ($tmpMF != "") : $tmpMF = " (" . $tmpMF . " * " . $tmpMF . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS turnover " : " ") : ""); $fieldClauseMarkupFormular .= ($tmpMF != "" ? ($fieldClauseMarkupFormular != "" ? ", " : "") . $tmpMF . ($statisticAllValues ? " AS turnover_markup " : " ") : ""); $fromClausePriceFormular = ""; $whereClausePriceFormular = ""; endif; if ($f_priceMode == "1" || $statisticAllValues) : // "Frachtvergütung" $tmpPF = " SUM(IF (jb.jb_cr_price != 0 AND NOT (ISNULL(jb.jb_cr_price)), jb.jb_cr_price, ROUND(jb.jb_totalprice * (100 - crvh.crvh_prov) / 100, 2))) "; if ($f_price_formular == "1") : // $tmpPF = " SUM(IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2))) "; $tmpPF = "SUM(IF (jbprc_10.jbprc_price != 0 AND NOT (ISNULL(jbprc_10.jbprc_price)), jbprc_10.jbprc_price, IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2)))) "; elseif ($f_price_formular == "2") : // $tmpPF = " SUM(IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))) "; $tmpPF = " SUM(IF (NOT (ISNULL(jbprc_10.jbprc_price)) AND NOT (ISNULL(jb.jb_cr_price)) AND (jb.jb_cr_price - jbprc_10.jbprc_price) != 0, jb.jb_cr_price - jbprc_10.jbprc_price, IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)) AND NOT (ISNULL(jb.jb_cr_price)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) "; elseif ($f_price_formular == "3") : // Nota bene: $fieldClauseMarkupFormular contains the markup in an extra field; $fieldClausePriceFormular will be the same defined above // $tmpMF = " SUM(IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))) "; $tmpMF = " SUM(IF (NOT (ISNULL(jbprc_10.jbprc_price)) AND NOT (ISNULL(jb.jb_cr_price)) AND (jb.jb_cr_price - jbprc_10.jbprc_price) != 0, jb.jb_cr_price - jbprc_10.jbprc_price, IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)) AND NOT (ISNULL(jb.jb_cr_price)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) "; endif; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; if ($tmpMF != "") : $tmpMF = " (" . $tmpMF . " * " . $tmpMF . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS remuneration " : " ") : ""); $fieldClauseMarkupFormular .= ($tmpMF != "" ? ($fieldClauseMarkupFormular != "" ? ", " : "") . $tmpMF . ($statisticAllValues ? " AS remuneration_markup " : " ") : ""); $fromClauseCrvh = " LEFT JOIN couriervehicle AS crvh ON jb.cr_sid = crvh.crvh_sid "; endif; if ($f_priceMode == "2" || $statisticAllValues) : // "EAU (= Auftragswert minus Frachtvergütung)" $tmpPF = " SUM(jb.jb_totalprice - (IF (jb.jb_cr_price != 0 AND NOT (ISNULL(jb.jb_cr_price)), jb.jb_cr_price, ROUND(jb.jb_totalprice * (100 - crvh.crvh_prov) / 100, 2)))) "; if ($f_price_formular == "1") : // $tmpPF = " SUM(jb.jb_subtotalprice - (IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2)))) "; $tmpPF = " SUM(jb.jb_subtotalprice - (IF (jbprc_10.jbprc_price != 0 AND NOT (ISNULL(jbprc_10.jbprc_price)), jbprc_10.jbprc_price, IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2))))) "; elseif ($f_price_formular == "2") : // $tmpPF = " SUM(jb.jb_totalprice - (IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_subtotalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) "; $tmpPF = " SUM(jb.jb_totalprice - (IF ((jb.jb_cr_price - jbprc_10.jbprc_price) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jbprc_10.jbprc_price)), (jb.jb_cr_price - jbprc_10.jbprc_price), IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_subtotalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))))) "; elseif ($f_price_formular == "3") : // Nota bene: $fieldClauseMarkupFormular contains the markup in an extra field; $fieldClausePriceFormular will be the same defined above // $fieldClauseMarkupFormular .= " SUM(jb.jb_totalprice - jb.jb_subtotalprice - (IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_subtotalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) "; $tmpMF = " SUM(jb.jb_totalprice - (IF ((jb.jb_cr_price - jbprc_10.jbprc_price) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jbprc_10.jbprc_price)), (jb.jb_cr_price - jbprc_10.jbprc_price), IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), (jb.jb_cr_price - jb.jb_cr_subprice), ROUND((jb.jb_subtotalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))))) "; endif; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; if ($tmpMF != "") : $tmpMF = " (" . $tmpMF . " * " . $tmpMF . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS eau " : " ") : ""); $fieldClauseMarkupFormular .= ($tmpMF != "" ? ($fieldClauseMarkupFormular != "" ? ", " : "") . $tmpMF . ($statisticAllValues ? " AS eau_markup " : " ") : ""); if (!$statisticAllValues) : $fromClauseCrvh = " LEFT JOIN couriervehicle AS crvh ON jb.cr_sid = crvh.crvh_sid "; endif; endif; if ($f_priceMode == "3" || $statisticAllValues) : // "BSG [%]" $tmpPF = " IF (SUM(jb.jb_totalprice) != 0, ((SUM(jb.jb_totalprice) - SUM(IF (jb.jb_cr_price != 0 AND NOT (ISNULL(jb.jb_cr_price)), jb.jb_cr_price, ROUND(jb.jb_totalprice * (100 - crvh.crvh_prov) / 100, 2)))) / SUM(jb.jb_totalprice)) * 100, 0) "; if ($f_price_formular == "1") : // $tmpPF = " IF (SUM(jb.jb_subtotalprice) != 0, ((SUM(jb.jb_subtotalprice) - SUM(IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2)))) / SUM(jb.jb_subtotalprice)) * 100, 0) "; $tmpPF = " IF (SUM(jb.jb_subtotalprice) != 0, ((SUM(jb.jb_subtotalprice) - SUM(IF (jbprc_10.jbprc_price != 0 AND NOT (ISNULL(jbprc_10.jbprc_price)), jbprc_10.jbprc_price, IF (jb.jb_cr_subprice != 0 AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_subprice, ROUND(jb.jb_subtotalprice * (100 - crvh.crvh_prov) / 100, 2))))) / SUM(jb.jb_subtotalprice)) * 100, 0) "; elseif ($f_price_formular == "2") : // $tmpPF = " IF (SUM(jb.jb_totalprice - jb.jb_subtotalprice) != 0, ((SUM(jb.jb_totalprice - jb.jb_subtotalprice) - SUM(IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) / SUM(jb.jb_totalprice - jb.jb_subtotalprice)) * 100, 0) "; $tmpPF = " IF (SUM(jb.jb_totalprice - jb.jb_subtotalprice) != 0, ((SUM(jb.jb_totalprice - jb.jb_subtotalprice) - SUM(IF ((jb.jb_cr_price - jbprc_10.jbprc_price) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jbprc_10.jbprc_price)), jb.jb_cr_price - jbprc_10.jbprc_price, IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))))) / SUM(jb.jb_totalprice - jb.jb_subtotalprice)) * 100, 0) "; elseif ($f_price_formular == "3") : // Nota bene: $fieldClauseMarkupFormular contains the markup in an extra field; $fieldClausePriceFormular will be the same defined above // $tmpPF = " IF (SUM(jb.jb_totalprice - jb.jb_subtotalprice) != 0, ((SUM(jb.jb_totalprice - jb.jb_subtotalprice) - SUM(IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2)))) / SUM(jb.jb_totalprice - jb.jb_subtotalprice)) * 100, 0) "; $tmpPF = " IF (SUM(jb.jb_totalprice - jb.jb_subtotalprice) != 0, ((SUM(jb.jb_totalprice - jb.jb_subtotalprice) - SUM(IF ((jb.jb_cr_price - jbprc_10.jbprc_price) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jbprc_10.jbprc_price)), jb.jb_cr_price - jbprc_10.jbprc_price, IF ((jb.jb_cr_price - jb.jb_cr_subprice) != 0 AND NOT (ISNULL(jb.jb_cr_price)) AND NOT (ISNULL(jb.jb_cr_subprice)), jb.jb_cr_price - jb.jb_cr_subprice, ROUND((jb.jb_totalprice - jb.jb_subtotalprice) * (100 - crvh.crvh_prov) / 100, 2))))) / SUM(jb.jb_totalprice - jb.jb_subtotalprice)) * 100, 0) "; endif; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; if ($tmpMF != "") : $tmpMF = " (" . $tmpMF . " * " . $tmpMF . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS bsg " : " ") : ""); $fieldClauseMarkupFormular .= ($tmpMF != "" ? ($fieldClauseMarkupFormular != "" ? ", " : "") . $tmpMF . ($statisticAllValues ? " AS bsg_markup " : " ") : ""); if (!$statisticAllValues) : $fromClauseCrvh = " LEFT JOIN couriervehicle AS crvh ON jb.cr_sid = crvh.crvh_sid "; endif; endif; if ($f_priceMode == "4" || $statisticAllValues) : // "Servicepreis" $tmpPF = " SUM(jb.jb_serviceprice) "; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS service_price " : " ") : ""); endif; if ($f_priceMode == "5" || $statisticAllValues) : // "Manuelle Service-Frachtvergütung" $tmpPF = " SUM(jbprc_cr_srvprc_man.jbprc_price) "; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS service_remuneration_man " : " ") : ""); $fromClausePriceFormular .= " LEFT JOIN jobprice AS jbprc_cr_srvprc_man ON jbprc_cr_srvprc_man.jb_id = jb.jb_id AND jbprc_cr_srvprc_man.mt_sort = '3' "; endif; /* if ($f_priceMode == "6") : // "Automatische Servicevergütung" $tmpPF = " SUM(jbprc_cr_srvprc_auto.jbprc_price) "; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS service_remuneration_auto " : " ") : ""); $fromClausePriceFormular .= " LEFT JOIN jobprice AS jbprc_cr_srvprc_auto ON jbprc_cr_srvprc_auto.jb_id = jb.jb_id AND jbprc_cr_srvprc_auto.mt_sort = '4' "; endif; if ($f_priceMode == "7") : // "Manuelle Frachtvergütung" // TODO $tmpPF = " SUM(jbprc_cr_man.jbprc_price) "; $fromClausePriceFormular = " LEFT JOIN jobprice AS jbprc_cr_man ON jbprc_cr_man.jb_id = jb.jb_id AND jbprc_cr_man.mt_sort = '2' "; if ($f_price_formular == "1") : $tmpPF = " SUM(jbprc_cr_sub_man.jbprc_price) "; $fromClausePriceFormular = " LEFT JOIN jobprice AS jbprc_cr_sub_man ON jbprc_cr_sub_man.jb_id = jb.jb_id AND jbprc_cr_sub_man.mt_sort = '2' "; elseif ($f_price_formular == "2") : $tmpPF = " SUM(jbprc_cr_man.jbprc_price) - SUM(jbprc_cr_sub_man.jbprc_price) "; $fromClausePriceFormular = " LEFT JOIN jobprice AS jbprc_cr_man ON jbprc_cr_man.jb_id = jb.jb_id AND jbprc_cr_man.mt_sort = '2' LEFT JOIN jobprice AS jbprc_cr_sub_man ON jbprc_cr_sub_man.jb_id = jb.jb_id AND jbprc_cr_sub_man.mt_sort = '2' "; elseif ($f_price_formular == "3") : // Nota bene: $fieldClauseMarkupFormular contains the markup in an extra field; $fieldClausePriceFormular will be the same defined above $tmpPF = " SUM(jbprc_cr_man.jbprc_price) - SUM(jbprc_cr_sub_man.jbprc_price) "; $fromClausePriceFormular = " LEFT JOIN jobprice AS jbprc_cr_man ON jbprc_cr_man.jb_id = jb.jb_id AND jbprc_cr_man.mt_sort = '2' LEFT JOIN jobprice AS jbprc_cr_sub_man ON jbprc_cr_sub_man.jb_id = jb.jb_id AND jbprc_cr_sub_man.mt_sort = '2' "; endif; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS remuneration_man " : " ") : ""); endif; */ if ($f_priceMode == "11" || $statisticAllValues) : // "KM" $tmpPF = " SUM(jbprc_km.jbprc_price) "; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS km " : " ") : ""); $fromClausePriceFormular .= " LEFT JOIN jobprice AS jbprc_km ON jbprc_km.jb_id = jb.jb_id AND jbprc_km.mt_sort = '11' "; endif; if ($f_priceMode == "12" || $statisticAllValues) : // "CO2E" $tmpPF = " SUM(jbprc_co2e.jbprc_price) "; if ($f_net_gross == "1") : if ($tmpPF != "") : $tmpPF = " (" . $tmpPF . " * " . $taxRate . ") "; endif; endif; $fieldClausePriceFormular .= ($tmpPF != "" ? ($fieldClausePriceFormular != "" ? ", " : "") . $tmpPF . ($statisticAllValues ? " AS co2e " : " ") : ""); $fromClausePriceFormular .= " LEFT JOIN jobprice AS jbprc_co2e ON jbprc_co2e.jb_id = jb.jb_id AND jbprc_co2e.mt_sort = '12' "; endif; // Check for statistic mode based on services ("Preis-/Leistungstypen") => Overwrite settings if ($f_mode_statistic == "1") : $fieldClauseAmountFormular .= " "; $fieldClauseAmountFormular = "SUM(jbc.jbc_amount) AS count_amount, " . $fieldClauseAmountFormular; $fieldClausePriceFormular = " SUM(jbc.jbc_totalprice) "; $fromClausePriceFormular = ", " . $dbh_jbc . " AS jbc "; $whereClausePriceFormular = " AND jb.jb_id = jbc.jb_id "; $fieldClauseMarkupFormular = ""; if ($f_service != "" && $f_service != "0") : $whereClausePriceFormular .= "AND jbc.srv_name = '" . $f_service . "' "; endif; if ($f_servicetype != "" && $f_servicetype != "0") : $whereClausePriceFormular .= "AND jbc.srvt_name = '" . $f_servicetype . "' "; endif; if ($f_filter_calc == "1") : if ($f_net_gross == "1") : $fieldClauseAmountFormular = "jbc.srv_name, (jbc.jbc_price * " . $taxRate . ") AS jbc_price," . $fieldClauseAmountFormular; else : $fieldClauseAmountFormular = "jbc.srv_name, jbc.jbc_price," . $fieldClauseAmountFormular; endif; endif; endif; // Job state $whereClauseJbStatus = " jb.jb_status = '2' AND "; if ($f_statusMode == "0") : $whereClauseJbStatus = ""; elseif ($f_statusMode == "1") : $whereClauseJbStatus = " jb.jb_status = '2' AND "; elseif ($f_statusMode == "2") : $whereClauseJbStatus = " jb.jb_status = '2' AND (NOT isnull(jb.jb_export_time)) AND jb.jb_export_time != '0000-00-00 00:00:00' AND jb.jb_export_time != '9999-12-31 23:59:59' AND "; endif; // Job created by customer or headquarters $fromClauseCreator = ""; $whereClauseCreator = ""; if ($f_editCsMode == "1") : // $whereClauseCreator = " (jb.csc_id = '0' OR isnull(jb.csc_id)) AND "; $fromClauseCreator = ", user AS usr_creator "; $whereClauseCreator = " AND jb.emp_id = usr_creator.usr_id AND usr_creator.usr_type = '1' "; elseif ($f_editCsMode == "2") : // $whereClauseCreator = " jb.csc_id != '0' AND (NOT isnull(jb.csc_id)) AND "; $fromClauseCreator = ", user AS usr_creator "; $whereClauseCreator = " AND jb.emp_id = usr_creator.usr_id AND usr_creator.usr_type = '2' "; endif; // Date mode $dateMode = "jb_finishtime"; if ($f_dateMode == "0") : $dateMode = "jb_createtime"; elseif ($f_dateMode == "1") : $dateMode = "jb_booktime"; elseif ($f_dateMode == "2") : $dateMode = "jb_finishtime"; elseif ($f_dateMode == "3") : $dateMode = "jb_export_time"; elseif ($f_dateMode == "4") : $dateMode = "jb_ordertime"; endif; $whereClause = ""; if (!($stmNumber >= "1000" && $stmNumber <= "1999")) : // Date range if ($fromDateRange != "") : $whereClause .= " AND jb." . $dateMode . " >= '$fromDateRange' "; endif; if ($toDateRange != "") : $whereClause .= " AND jb." . $dateMode . " <= '$toDateRange' "; endif; // With time interval if ($f_showDateAndTime == "1") : $whereClause .= " AND RIGHT(jb." . $dateMode . ",8) >= '" . pad($hour_from,2) . ":" . pad($minute_from,2) . ":00' "; $whereClause .= " AND RIGHT(jb." . $dateMode . ",8) <= '" . pad($hour_to,2) . ":" . pad($minute_to,2) . ":59' "; endif; endif; $whereClauseCsEid = ""; if ($csEid != "") : $whereClauseCsEid .= " AND cs.cs_eid = '$csEid' "; endif; $whereClauseCrEid = ""; if ($crEid != "") : $whereClauseCrEid .= " AND cr.cr_eid = '$crEid' "; endif; // With or without STORNO $isStornoClause = ""; $parStatisticNoStorno = getParameterValue("0", "STATISTIC_NO_STORNOJOBS", $hq_id); if ($parStatisticNoStorno == "") : getParameterValue("0", "STATISTIC_NO_STORNOJOBS", "0"); endif; if ($parStatisticNoStorno == "1") : $isStornoClause = " AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3')"; endif; // ONLY EXPORTED jobs $exportedJobClause = ""; if (false && ($stmNumber == "25" || $stmNumber == "26" || $stmNumber == "27")) : $exportedJobClause = " AND (NOT isnull(jb.jb_export_time)) "; if ($stmNumber == "25") : $stmNumber = "20"; endif; if ($stmNumber == "26") : $stmNumber = "21"; endif; if ($stmNumber == "27") : $stmNumber = "22"; endif; endif; // SPLIT FILTER $fieldClauseFilter = ""; $fromClauseFilter = ""; $groupByClauseFilter = ""; // Split per vehicletypes if ($f_filter == "1") : // Check kind of vehicletype [[="0"] <=> Requested vehicle type / [="1"] <=> Real vehicle type] if ($f_filter_a == "0") : $fieldClauseFilter = ", jb.vht_id AS vehicletype"; $groupByClauseFilter = " jb.vht_id "; else : $fieldClauseFilter = ", jb.vht_id_real AS vehicletype"; $groupByClauseFilter = " jb.vht_id_real "; endif; endif; // Split per start zipcode OR all target zipcodes if ($f_filter == "2") : $fieldClauseFilter = ", ad.ad_zipcode"; $fromClauseFilter = ", " . $dbh_tr . " AS tr, address AS ad"; $groupByClauseFilter = " ad.ad_zipcode "; $whereClause .= " AND tr.jb_id = jb.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id "; endif; // Split per target zipcode if ($f_filter == "3") : $fieldClauseFilter = "," . " SUBSTRING_INDEX(SUBSTRING(jb.jb_tourdata, 1, (INSTR(jb.jb_tourdata, '|') - 1) ), ';', -1) AS jb_lastzipcode "; $groupByClauseFilter = " jb_lastzipcode "; endif; // Split per start zipcode AND per vehicletypes if ($f_filter == "4") : if ($f_filter_a == "0") : $fieldClauseFilter = ", jb.vht_id AS vehicletype, ad.ad_zipcode"; $fromClauseFilter = ", " . $dbh_tr . " AS tr, address AS ad"; $groupByClauseFilter = " jb.vht_id, ad.ad_zipcode "; $whereClause .= " AND tr.jb_id = jb.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id "; else : $fieldClauseFilter = ", jb.vht_id_real AS vehicletype, ad.ad_zipcode"; $fromClauseFilter = ", " . $dbh_tr . " AS tr, address AS ad"; $groupByClauseFilter = " jb.vht_id_real, ad.ad_zipcode "; $whereClause .= " AND tr.jb_id = jb.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id "; endif; endif; // Split per target zipcode AND per vehicletypes if ($f_filter == "5") : if ($f_filter_a == "0") : $fieldClauseFilter = ", jb.vht_id AS vehicletype, SUBSTRING_INDEX(SUBSTRING(jb.jb_tourdata, 1, (INSTR(jb.jb_tourdata, '|') - 1) ), ';', -1) AS jb_lastzipcode"; $groupByClauseFilter = " jb.vht_id, jb_lastzipcode "; else : $fieldClauseFilter = ", jb.vht_id_real AS vehicletype, SUBSTRING_INDEX(SUBSTRING(jb.jb_tourdata, 1, (INSTR(jb.jb_tourdata, '|') - 1) ), ';', -1) AS jb_lastzipcode"; $groupByClauseFilter = " jb.vht_id_real, jb_lastzipcode "; endif; endif; // Split per start and target address (street, zipcode and city) if ($f_filter == "6") : $fieldClauseFilter = ", CONCAT(ad.ad_street,'|',ad.ad_zipcode,'|',ad.ad_city) AS start_address " . ", CONCAT(ad2.ad_street,'|',ad2.ad_zipcode,'|',ad2.ad_city) AS target_address "; $fromClauseFilter = ", " . $dbh_tr . " AS tr, " . $dbh_tr . " AS tr2, address AS ad, address AS ad2 "; $whereClause .= " AND tr.jb_id = jb.jb_id AND tr.tr_sort = '1' AND tr.ad_id = ad.ad_id " . " AND tr2.jb_id = jb.jb_id AND tr2.tr_sort = '2' AND tr2.ad_id = ad2.ad_id "; $groupByClauseFilter = " start_address, target_address "; endif; // Split per all target zipcodes if ($f_filter == "7") : $fieldClauseFilter = " ad.ad_zipcode "; $fromClauseFilter = ", " . $dbh_tr . " AS tr, address AS ad"; $groupByClauseFilter = " ad.ad_zipcode "; $whereClause .= " AND tr.jb_id = jb.jb_id AND tr.tr_sort > '1' AND tr.ad_id = ad.ad_id "; endif; // Split by jobs with amount < 0 and amount > 0 // ONLY jobs $whereClausePrice = ""; if ($f_filter2 == "1") : $whereClausePrice = " AND jb.jb_totalprice > '0' "; endif; // ONLY credit notes if ($f_filter2 == "2") : $whereClausePrice = " AND jb.jb_totalprice < '0' "; endif; // Check for statistic mode based on services ("Preis-/Leistungstypen") if ($f_mode_statistic == "1") : $whereClausePrice = ""; if ($f_filter2 == "1") : $whereClausePrice = " AND jbc.jbc_totalprice > '0' "; endif; if ($f_filter2 == "2") : $whereClausePrice = " AND jbc.jbc_totalprice < '0' "; endif; endif; // Filter for vehicle (special SID) if ($f_crvh_sid != "") : $whereClause .= " AND jb.cr_sid = '" . $f_crvh_sid . "' "; endif; // Filter by vehicle product (jb_type) if ($f_filter3 != "" && is_numeric($f_filter3) && $f_filter3 > "0") : $whereClause .= " AND jb.jb_type = '" . $f_filter3 . "' "; endif; // Filter by weight, measures and positions if ($f_filter_jb_weight_from != "" && is_numeric($f_filter_jb_weight_from) && $f_filter_jb_weight_from > "0") : $whereClause .= " AND jb.jb_weight >= '" . $f_filter_jb_weight_from . "' "; endif; if ($f_filter_jb_weight_to != "" && is_numeric($f_filter_jb_weight_to) && $f_filter_jb_weight_to > "0") : $whereClause .= " AND jb.jb_weight <= '" . $f_filter_jb_weight_to . "' "; endif; if ($f_filter_jb_length_from != "" && is_numeric($f_filter_jb_length_from) && $f_filter_jb_length_from > "0") : $whereClause .= " AND jb.jb_crvh_length >= '" . $f_filter_jb_length_from . "' "; endif; if ($f_filter_jb_length_to != "" && is_numeric($f_filter_jb_length_to) && $f_filter_jb_length_to > "0") : $whereClause .= " AND jb.jb_crvh_length <= '" . $f_filter_jb_length_to . "' "; endif; if ($f_filter_jb_width_from != "" && is_numeric($f_filter_jb_width_from) && $f_filter_jb_width_from > "0") : $whereClause .= " AND jb.jb_crvh_width >= '" . $f_filter_jb_width_from . "' "; endif; if ($f_filter_jb_width_to != "" && is_numeric($f_filter_jb_width_to) && $f_filter_jb_width_to > "0") : $whereClause .= " AND jb.jb_crvh_width <= '" . $f_filter_jb_width_to . "' "; endif; if ($f_filter_jb_height_from != "" && is_numeric($f_filter_jb_height_from) && $f_filter_jb_height_from > "0") : $whereClause .= " AND jb.jb_crvh_height >= '" . $f_filter_jb_height_from . "' "; endif; if ($f_filter_jb_height_to != "" && is_numeric($f_filter_jb_height_to) && $f_filter_jb_height_to > "0") : $whereClause .= " AND jb.jb_crvh_height <= '" . $f_filter_jb_height_to . "' "; endif; if ($f_filter_jb_position_from != "" && is_numeric($f_filter_jb_position_from) && $f_filter_jb_position_from > "0") : $whereClause .= " AND jb.jb_crvh_position >= '" . $f_filter_jb_position_from . "' "; endif; if ($f_filter_jb_position_to != "" && is_numeric($f_filter_jb_position_to) && $f_filter_jb_position_to > "0") : $whereClause .= " AND jb.jb_crvh_position <= '" . $f_filter_jb_position_to . "' "; endif; // Split per job types if ($f_filter9 == "1") : $fieldClauseFilter .= ", jb.jb_type"; $fromClauseFilter .= ""; $whereClause .= ""; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " jb.jb_type " . $groupByClauseFilter; endif; // Split per "payer costcenter" or "related costcenter" if ($f_filter10 == "1" || $f_filter10 == "2") : if ($f_filter10 == "1") : $statJbCscIdField = "jb.csc_id_payer"; endif; // Split possible by "csc_id_payer" only! if ($f_filter10 == "2") : $statJbCscIdField = "jb.csc_id_related"; endif; // Split possible by "csc_id_related" only! $fieldClauseFilter .= ", " . $statJbCscIdField . ", csc.csc_name "; if ($customerTablesDefinedForSqlStatement || $stmNumber == "60" || $stmNumber == "61" || $stmNumber == "62" || $stmNumber == "63" || $stmNumber == "64" || $stmNumber == "301" || $stmNumber == "302" || $stmNumber == "303" || $stmNumber == "304" || $stmNumber == "305") : $fromClauseFilter .= ""; $whereClause .= ""; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id "; $customerTablesDefinedForSqlStatement = true; endif; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " " . $statJbCscIdField . " " . $groupByClauseFilter; if ($orderByClause == "cs.cs_eid") : $orderByClause = " cs.cs_eid, csc.csc_name "; endif; endif; // Split per branches if ($f_filter5 == "1") : $fieldClauseFilter .= ", CONCAT(br_key,' ',br_name) AS branch"; if ($stmNumber == "50" || $stmNumber == "51" || $stmNumber == "60" || $stmNumber == "61" || $stmNumber == "62" || $stmNumber == "63" || $stmNumber == "64" || $stmNumber == "301" || $stmNumber == "302" || $stmNumber == "303" || $stmNumber == "304" || $stmNumber == "305") : // Especially the customer statistic uses most of needed tables... (!!!!) $fromClauseFilter .= ", branch AS br "; $whereClause .= " AND cmp.br_id = br.br_id "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs, company AS cmp2 LEFT JOIN branch AS br ON cmp2.br_id = br.br_id "; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cmp_id = cmp2.cmp_id "; endif; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " branch " . $groupByClauseFilter; $customerTablesDefinedForSqlStatement = true; endif; // Split per couriers [ONLY customer statistics] if ($f_filter6 == "1") : $fieldClauseFilter .= ", cr.cr_eid, usr.usr_firstname, usr.usr_name"; $fromClauseFilter .= ", courier AS cr, user AS usr "; $whereClause .= " AND jb.cr_id = cr.cr_id AND cr.usr_id = usr.usr_id "; if ($groupByClauseFilter != "") : $groupByClauseFilter = $groupByClauseFilter . ","; endif; $groupByClauseFilter = $groupByClauseFilter . " cr.cr_eid "; endif; // Split per customers [ONLY carrier statistics] if ($f_filter11 == "1") : $fieldClauseFilter .= ", cr_cs.cs_eid, cr_cs_cmp.cmp_comp AS cr_cs_cmp_comp, cr_cs_cmp.cmp_comp2 AS cr_cs_cmp_comp2"; $fromClauseFilter .= ", costcenter AS cr_csc, customer AS cr_cs, company AS cr_cs_cmp "; $whereClause .= " AND jb.csc_id_payer = cr_csc.csc_id AND cr_csc.cs_id = cr_cs.cs_id AND cr_cs.cmp_id = cr_cs_cmp.cmp_id "; if ($groupByClauseFilter != "") : $groupByClauseFilter = $groupByClauseFilter . ","; endif; $groupByClauseFilter = $groupByClauseFilter . " cr_cs.cs_eid "; endif; // Split per vehicles if ($f_filter7 == "1") : $fieldClauseFilter .= ", jb.cr_sid"; $fromClauseFilter .= ""; $whereClause .= ""; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " jb.cr_sid " . $groupByClauseFilter; endif; // Split per partner provision if ($f_filter12 == "1") : $fieldClauseFilter .= ", jbprc_crvh_partner_commission.jbprc_price AS jb_crvh_partner_commission"; // $fromClauseFilter .= ""; $fromClausePriceFormular = " LEFT JOIN jobprice AS jbprc_crvh_partner_commission ON jbprc_crvh_partner_commission.jb_id = jb.jb_id AND jbprc_crvh_partner_commission.mt_sort = '14' " . $fromClausePriceFormular; $whereClause .= ""; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " jb_crvh_partner_commission " . $groupByClauseFilter; endif; // Split per system fee [Presently no split, only joined !!!!] $f_filter13 = "0"; // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! if ($f_category == "2" && $f_priceMode == "19") : $f_filter13 = "1"; endif; // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! $fromClauseVehicles = ""; if ($f_filter13 == "1") : $fieldClauseFilter .= ", crvh_system_fee_amount.crvh_system_fee_amount AS jb_crvh_system_fee_amount"; // "$fromClauseVehicles" // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! $fromClauseVehicles = " LEFT JOIN couriervehicle AS crvh_system_fee_amount ON crvh_system_fee_amount.cr_id = cr.cr_id AND crvh_system_fee_amount.crvh_sid = cr.cr_sid "; $whereClause .= ""; // if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; // $groupByClauseFilter = " jb_crvh_system_fee_amount " . $groupByClauseFilter; endif; // Split per advertising allowance [Presently no split, only joined !!!!] $f_filter14 = "0"; // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! if ($f_category == "2" && $f_priceMode == "19") : $f_filter14 = "4"; endif; // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! if ($f_filter14 == "1") : $fieldClauseFilter .= ", crvh_advertising_allowance.crvh_advertising_allowance AS jb_crvh_advertising_allowance"; // "$fromClauseVehicles" // !!!!!!!!!!!!!!!!!!!!!!!!!!!! To be removed !!!! $fromClauseVehicles = " LEFT JOIN couriervehicle AS crvh_advertising_allowance ON crvh_advertising_allowance.cr_id = cr.cr_id AND crvh_advertising_allowance.crvh_sid = cr.cr_sid "; $whereClause .= ""; // if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; // $groupByClauseFilter = " jb_crvh_system_fee_amount " . $groupByClauseFilter; endif; // Split per employees $groupByClauseEmployee = ""; $orderByClauseEmployee = ""; if ($f_filter8 == "1") : $fieldClauseFilter .= ", jb.emp_id"; $fromClauseFilter .= ""; $whereClause .= ""; $groupByClauseEmployee = "jb.emp_id "; $orderByClauseEmployee = "jb.emp_id, "; endif; // Split per headquarters if ($f_filter4 == "1") : $fieldClauseFilter .= ", " . $hqIdClause . " AS headquarters"; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " " . $hqIdClause . " " . $groupByClauseFilter; endif; // Split per time intervals if ($f_filter_interval != "0") : if ($f_filter_interval == "1") : $tmpSeconds = 1800; endif; if ($f_filter_interval == "2") : $tmpSeconds = 3600; endif; if ($f_filter_interval == "3") : $tmpSeconds = 7200; endif; $fieldClauseFilter .= ", FLOOR((UNIX_TIMESTAMP(jb." . $dateMode . ") - UNIX_TIMESTAMP(CONCAT(LEFT(jb." . $dateMode . ", 10), ' 00:00:00'))) / " . $tmpSeconds . ") AS time_interval"; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " time_interval " . $groupByClauseFilter; endif; // Split per calculator if ($f_mode_statistic == "1" && $f_filter_calc == "1") : $groupByClauseFilter = " jbc.srv_name, jbc.jbc_price " . $groupByClauseFilter; endif; // Filter by job service (delivery, installation, etc.) if (is_array($f_jb_service) && count($f_jb_service) > 0) : // $f_decval = pow(2,$f_jb_service); // $whereClause .= " AND (jb.jb_service & " . $f_decval . " = " . $f_decval . ") "; $f_decval = mcConvertSel2Num($f_jb_service); $whereClause .= " AND (jb.jb_service & " . $f_decval . " > 0) "; endif; // Split per job services /* if ($f_split_jb_services == "1") : $fieldClauseFilter .= ", jb.jb_service"; if ($groupByClauseFilter != "") : $groupByClauseFilter = "," . $groupByClauseFilter; endif; $groupByClauseFilter = " jb.jb_service " . $groupByClauseFilter; endif; */ // Filter by job service (delivery, installation, etc.) if (is_array($f_jb_specifics) && count($f_jb_specifics) > 0) : // Service job, added installation, ... if (in_array("1001", $f_jb_specifics)) : $whereClause .= " AND (EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_service_job')) "; endif; if (in_array("2001", $f_jb_specifics)) : $whereClause .= " AND (NOT EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_service_job')) "; endif; if (in_array("1002", $f_jb_specifics)) : $fieldClausePriceFormular = " (SUM(jb.jb_totalprice) - SUM(jb.jb_subtotalprice)) "; // $whereClause .= " AND (EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_addmont' AND gdc.gdc_context != '')) "; endif; if (in_array("2002", $f_jb_specifics)) : $fieldClausePriceFormular = " SUM(jb.jb_subtotalprice) "; // $whereClause .= " AND (NOT EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_addmont' AND gdc.gdc_context != '')) "; endif; if (in_array("1003", $f_jb_specifics)) : $whereClause .= " AND (EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_no_payment_job')) "; endif; if (in_array("2003", $f_jb_specifics)) : $whereClause .= " AND (NOT EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_no_payment_job')) "; endif; /* if (in_array("1003", $f_jb_specifics)) : $whereClause .= " AND (EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_business_card')) "; endif; if (in_array("2003", $f_jb_specifics)) : $whereClause .= " AND (NOT EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_business_card')) "; endif; if (in_array("1004", $f_jb_specifics)) : $whereClause .= " AND (EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_family_card')) "; endif; if (in_array("2004", $f_jb_specifics)) : $whereClause .= " AND (NOT EXISTS (SELECT * FROM phoenix.genericdatacontainer AS gdc WHERE gdc.gdc_obj_id = jb.jb_id AND gdc.gdc_obj_type = 'jb' AND gdc.gdc_gen_fieldname = 'jb_family_card')) "; endif; */ endif; // META CUSTOMER $fromClauseMetaCustomer = ""; $whereClauseMetaCustomer = ""; if ($f_cs_meta == "1") : $fieldClauseFilter .= ", cs.cs_id_parent"; if ($whereClauseCsEid != "") : $whereClauseCsEid = " AND cs2.cs_eid = '$csEid' "; // Reconfiguration of declaration above !!! $fromClauseMetaCustomer = ", customer AS cs2"; $whereClauseMetaCustomer = " AND cs.cs_id_parent = cs2.cs_id "; endif; endif; // SPLIT FOR DRIVER OF A CONTRACTOR $fromClauseMetaCourier = ""; $whereClauseMetaCourier = ""; if ($f_cr_meta == "1" && $whereClauseCrEid != "") : $whereClauseCrEid = " AND cr2.cr_eid = '$crEid' "; // Reconfiguration of declaration above !!! $fromClauseMetaCourier = ", phoenix.courier AS cr2"; $whereClauseMetaCourier = " AND cr.cr_id_parent = cr2.cr_id "; endif; // MAP EACH DRIVER TO HIS/HER PARENT $tableClauseCourier = "cr"; $fromClauseBaseCourier = ", company AS cmp, user AS usr"; $whereClauseBaseCourier = $tableClauseCourier . ".cmp_id = cmp.cmp_id AND " . $tableClauseCourier . ".usr_id = usr.usr_id AND"; if ($f_cr_parent == "1" || $f_cr_parent == "2") : $tableClauseCourier = "cr2"; $whereClauseBaseCourier = $tableClauseCourier . ".cmp_id = cmp.cmp_id AND " . $tableClauseCourier . ".usr_id = usr.usr_id AND"; if (!($f_cr_meta == "1" && $whereClauseCrEid != "")) : $fromClauseMetaCourier = ", phoenix.courier AS cr2"; $whereClauseMetaCourier = " AND cr.cr_id_parent = cr2.cr_id "; endif; if ($f_cr_parent == "2") : $whereClauseMetaCourier = " AND ( (cr.cr_id_parent = cr2.cr_id AND (NOT ISNULL(cr.cr_id_parent)) AND cr.cr_id_parent != '0') OR (cr.cr_id = cr2.cr_id AND (ISNULL(cr.cr_id_parent) OR cr.cr_id_parent = '0')) ) "; endif; endif; // Only customers regarding to the entered date the data had been generated $whereClauseCmpNewDate = ""; if ($f_cmp_new_date != "") : $whereClauseCmpNewDate = " AND cmp.cmp_new_date >= '" . $f_cmp_new_date . "' "; endif; // Only new customers regarding to entered date (e.g. switched from prospect to customer) $whereClauseBecomeCsDate = ""; if ($f_cs_become_cs_date != "") : $whereClauseBecomeCsDate = " AND cs.cs_become_cs_date >= '" . $f_cs_become_cs_date . "' "; endif; // Filter by selected vehicle filters $whereClauseVehicleFilters = ""; if ($g_crvh_filter != "") : $tmpFilter = spliti(",",$g_crvh_filter); $lenTmp = count($tmpFilter); for ($i = 0; $i < $lenTmp; $i++) : // $whereClauseVehicleFilters .= " AND (jb.jb_cr_filter LIKE '%" . $tmpFilter[$i] . "%' OR jb.jb_cr_filter_opt LIKE '%" . $tmpFilter[$i] . "%') "; $tfx = $tmpFilter[$i]; $tfxLen = strlen($tfx) + 1; // with comma $whereClauseVehicleFilters .= " AND (jb.jb_cr_filter = '" . $tfx . "' OR jb.jb_cr_filter LIKE '%," . $tfx . ",%' OR LEFT(jb.jb_cr_filter," . $tfxLen . ") = '" . $tfx . ",' OR RIGHT(jb.jb_cr_filter," . $tfxLen . ") = '," . $tfx . "' OR jb.jb_cr_filter_opt = '" . $tfx . "' OR jb.jb_cr_filter_opt LIKE '%," . $$tfx . ",%' OR LEFT(jb.jb_cr_filter_opt," . $tfxLen . ") = '" . $tfx . ",' OR RIGHT(jb.jb_cr_filter_opt," . $tfxLen . ") = '," . $tfx . "')"; endfor; endif; // STATISTIC OF THE BEHAVIOUR OF THE COURIERS if ($stmNumber >= "1000" && $stmNumber <= "1999") : if ($stmNumber >= "1000" && $stmNumber <= "1099") : $logOpId = ($stmNumber - 1000); $stmNumber = "1000"; endif; if ($stmNumber >= "1100" && $stmNumber <= "1199") : $logOpId = ($stmNumber - 1100); $stmNumber = "1100"; endif; // Convert "datetime" to "timestamp" if needed $convertDatetime2Timestamp = DB_FIELDTYPE_WRAPPER_1; if ($convertDatetime2Timestamp != "1") : $fromDateRange = formatOutput($fromDateRange,"datetime","12"); $toDateRange = formatOutput($toDateRange,"datetime","12"); endif; $whereClauseClockTime = ""; if ($f_showDateAndTime == "1") : if ($convertDatetime2Timestamp != "1") : $whereClauseClockTime .= " AND RIGHT(log.log_createtime,6) >= '" . pad($hour_from,2) . pad($minute_from,2) . "00' "; $whereClauseClockTime .= " AND RIGHT(log.log_createtime,6) <= '" . pad($hour_to,2) . pad($minute_to,2) . "59' "; else : $whereClauseClockTime .= " AND RIGHT(log.log_createtime,8) >= '" . pad($hour_from,2) . ":" . pad($minute_from,2) . ":00' "; $whereClauseClockTime .= " AND RIGHT(log.log_createtime,8) <= '" . pad($hour_to,2) . ":" . pad($minute_to,2) . ":59' "; endif; endif; endif; $whereClausePDActionsPerJob = "DISTINCT "; if ($f_cr_pda_actions == "1") : $whereClausePDActionsPerJob = ""; endif; // Get aligns from the database according to the category // $aligns = getFieldValueFromId("exportcategory","expc_id",$stmNumberTmp,"expc_aligns"); if ($groupByClauseFilter != "") : $tmpLen = strlen($aligns); if ($tmpLen > 2) : $tmpextRowAlign = ""; $tmp4extRowAlign = ""; if ($f_filter > "0") : $tmpextRowAlign = ",l"; endif; if ($f_filter == "4" || $f_filter == "5" || $f_filter == "6") : $tmpextRowAlign = ",l,l"; endif; if ($f_filter4 == "1") : $tmp4extRowAlign = "l,"; endif; if ($f_filter5 == "1") : $tmp4extRowAlign .= "l,"; endif; $tmpLeft = substr($aligns, 0, ($tmpLen - 2)); $tmpRight = substr($aligns, -2); $aligns = $tmp4extRowAlign . $tmpLeft . $tmpextRowAlign . $tmpRight; if ($f_filter6 == "1") : $aligns = "l,l,l," . $aligns; endif; if ($f_filter7 == "1") : $aligns = "l," . $aligns; endif; if ($f_filter9 == "1") : $aligns = "l," . $aligns; endif; if ($f_filter10 == "1") : $aligns = "l," . $aligns; endif; if ($f_filter12 == "1") : $aligns = "l," . $aligns; endif; if ($f_filter_interval != "0") : $aligns = "l," . $aligns; endif; else : $tmpextRowAlign = ""; $tmp4extRowAlign = ""; if ($f_filter > "0") : $tmpextRowAlign = "l,"; endif; if ($f_filter == "4" || $f_filter == "5" || $f_filter == "6") : $tmpextRowAlign = "l,l,"; endif; if ($f_filter4 == "1") : $tmp4extRowAlign = "l,"; endif; if ($f_filter5 == "1") : $tmp4extRowAlign .= "l,"; endif; if ($f_filter7 == "1") : $aligns = $aligns . "l," ; endif; if ($f_filter9 == "1") : $aligns = $aligns . "l," ; endif; if ($f_filter10 == "1") : $aligns = $aligns . "l," ; endif; if ($f_filter12 == "1") : $aligns = $aligns . "l," ; endif; $aligns = $tmp4extRowAlign . $tmpextRowAlign . $aligns; if ($f_filter6 == "1") : $aligns = "l,l,l," . $aligns; endif; if ($f_filter_interval != "0") : $aligns = $aligns . ",l" ; endif; endif; endif; if ($f_filter8 == "1") : $aligns = "l," . $aligns; endif; if ($f_cs_meta == "1") : $aligns = "l," . $aligns; endif; // Check for selected groups $csArray = array(); $crArray = array(); $usrArray = array(); $srvArray = array(); $srvtArray = array(); if ($f_groupLen > 0) : $csArray = getGroupMemberIDs($f_group, "cs", $f_hq_id); $csArrayLen = count($csArray); $crArray = getGroupMemberIDs($f_group, "cr", $f_hq_id); $crArrayLen = count($crArray); $usrArray = getGroupMemberIDs($f_group, "emp", $f_hq_id); $usrArrayLen = count($usrArray); $srvArray = getGroupMemberIDs($f_group, "srv", $f_hq_id); $srvArrayLen = count($srvArray); $srvtArray = getGroupMemberIDs($f_group, "srvt", $f_hq_id); $srvtArrayLen = count($srvtArray); if ($crArrayLen > 0) : if ($stmNumber < "1000" || $stmNumber > "1999") : $whereClause .= " AND jb.cr_id IN (" . implode(",", $crArray) . ") "; endif; endif; if ($usrArrayLen > 0) : $whereClause .= " AND jb.emp_id IN (" . implode(",", $usrArray) . ") "; endif; if ($srvArrayLen > 0 || $srvtArrayLen > 0) : if ($f_mode_statistic != "1") : // Needed check for definition of a concrete selection of a service or servicetype $fieldClauseAmountFormular = " SUM(jbc.jbc_amount) "; $fieldClausePriceFormular = " SUM(jbc.jbc_totalprice) "; $fieldClauseMarkupFormular = ""; $fromClausePriceFormular = "," . $dbh_jbc . " AS jbc "; $whereClausePriceFormular = " AND jb.jb_id = jbc.jb_id "; $f_mode_statistic = "1"; endif; if ($srvArrayLen > 0) : $fromClausePriceFormular .= ", service AS srv "; $whereClausePriceFormular .= " AND jbc.srv_name = srv.srv_name AND srv.srv_id IN (" . implode(",", $srvArray) . ") "; endif; if ($srvtArrayLen > 0) : $fromClausePriceFormular .= ", servicetype AS srvt "; $whereClausePriceFormular .= " AND jbc.srvt_name = srvt.srvt_name AND srvt.srvt_id IN (" . implode(",", $srvtArray) . ") "; endif; endif; // Check for all customers being a meta customer if filter for split per subcustomer is requested if ($f_cs_meta == "1") : $tmpCsArray = $csArray; $tmpCsArrayLen = $csArrayLen; for ($i = 0; $i < $tmpCsArrayLen; $i++) : $tmpCsIdsSub = getColVectorFromDB2ArrayByClause("customer", "cs_id", " cs_id_parent = '" . $tmpCsArray[$i] . "'"); if (count($tmpCsIdsSub) > 0) : $csArray = array_merge($csArray, $tmpCsIdsSub); endif; endfor; endif; endif; // Check for selected STATIC groups $staticCsArray = array(); if ($f_staticGroupLen > 0) : $staticCsArray = getGroupMemberIDs($f_staticGroup, "cs"); endif; $staticCsArrayLen = count($staticCsArray); // Exclude jobs for defined special customers $whereClauseExcludedCustomers = getParameterValue("0", "STATISTIC_EXCLUDED_CS", "0"); $whereClauseExcludedCustomers = trim($whereClauseExcludedCustomers); if ($whereClauseExcludedCustomers != "") : $tmpArray = spliti(",", $whereClauseExcludedCustomers); $whereClauseExcludedCustomers = " AND cs.cs_eid NOT IN ('" . implode("','", $tmpArray) . "')"; endif; // Patch for error "ambiguous" if ($orderByClause == "cs_id_parent") : $orderByClause = "cs." . $orderByClause; endif; // NET OR GROSS if ($f_net_gross == "1" && !$statisticAllValues) : $fieldClausePriceFormular = " (" . $fieldClausePriceFormular . " * " . $taxRate . ") "; if ($fieldClauseMarkupFormular != "") : $fieldClauseMarkupFormular = " (" . $fieldClauseMarkupFormular . " * " . $taxRate . ") "; endif; endif; // Get prefered index $indexClauseJb = ""; $parIndex = getParameterValue("0", "STATISTIC_PREFERED_CATEGORY_INDEX_JB", $hq_id); if ($parIndex == "") : $parIndex = getParameterValue("0", "STATISTIC_PREFERED_CATEGORY_INDEX_JB", "0"); endif; if ($parIndex != "") : $parIndexArray = getKeyValueArrayFromString($parIndex); if ($parIndexArray[$stmNumber] != "") : $indexClauseJb = " FORCE INDEX (" . $parIndexArray[$stmNumber] . ") "; endif; endif; // Grouped (station based) statements will be overwritten (!!!!) if ($station_based_group_field != "") : $fieldClauseAmountFormular = "COUNT(" . $station_based_group_field . ")"; $fieldClausePriceFormular = ""; $fieldClauseMarkupFormular = ""; // $fromClausePriceFormular = ""; // $whereClausePriceFormular = ""; // $f_filter_calc = ""; // $groupByClause .= ", $station_based_group_field"; endif; // Filter for booked jobs only ("Nacherfassungen") $fromClauseBookedJobs = ""; $whereClauseBookedJobs = ""; if ($f_bookedJobsOnly == "1") : $fromClauseBookedJobs = ", phoenix_log.log AS log"; $whereClauseBookedJobs = " AND jb.jb_id = log.jb_id AND log.logo_id IN ('1','25') AND log.logo_description LIKE 'Nacherfassung%' "; elseif ($f_bookedJobsOnly == "2") : $whereClauseBookedJobs = " AND NOT EXISTS (SELECT * FROM phoenix_log.log AS log WHERE jb.jb_id = log.jb_id AND log.logo_id IN ('1','25') AND log.logo_description LIKE 'Nacherfassung%') "; endif; // Filter for permanent jobs only ("Daueraufträge") $whereClausePermanentJobs = ""; if ($f_permanentJobsOnly == "1") : $whereClausePermanentJobs = " AND (NOT isnull(jb.jb_permanent)) AND jb.jb_permanent != '0'"; elseif ($f_permanentJobsOnly == "2") : $whereClausePermanentJobs = " AND (isnull(jb.jb_permanent) OR jb.jb_permanent = '0')"; endif; // ************ // * COURIERS * // ************ if ($stmNumber == "10" || $stmNumber == "11" || $stmNumber == "12" || $stmNumber == "13" || $stmNumber == "14" || $stmNumber == "201" || $stmNumber == "202" || $stmNumber == "203" || $stmNumber == "204" || $stmNumber == "205" || $stmNumber == "3010" || $stmNumber == "3011" || $stmNumber == "3012") : $fieldClauseSplitByTime = ""; $groupClauseSplitByTime = ""; $uniqueSearchFields[] = "cr_eid"; $uniqueSearchFields[] = "usr_name"; if ($stmNumber == "10" || $stmNumber == "201" || $stmNumber == "3010") : // ... endif; if ($stmNumber == "11" || $stmNumber == "202" || $stmNumber == "3011") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",7) AS jb_finishtime, "; $groupClauseSplitByTime = ", LEFT(jb." . $dateMode . ",7)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", LEFT(jb." . $dateMode . ",7) "; endif; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "12" || $stmNumber == "203" || $stmNumber == "3012") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",10) AS jb_finishtime, "; $groupClauseSplitByTime = ", LEFT(jb." . $dateMode . ",10)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", LEFT(jb." . $dateMode . ",10) "; endif; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "13" || $stmNumber == "204") : $fieldClauseSplitByTime = " YEARWEEK(LEFT(jb." . $dateMode . ",10),3) AS jb_finishtime, "; $groupClauseSplitByTime = ", YEARWEEK(LEFT(jb." . $dateMode . ",10),3)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", YEARWEEK(LEFT(jb." . $dateMode . ",10),3) "; endif; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "14" || $stmNumber == "205") : $fieldClauseSplitByTime = " CONCAT(LEFT(jb." . $dateMode . ",7),'-',IF((DAY(jb." . $dateMode . ") & 16) <> 16,'I','II')) AS jb_finishtime, "; $groupClauseSplitByTime = ", CONCAT(LEFT(jb." . $dateMode . ",7),'-',(DAY(jb." . $dateMode . ") & 16))"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", CONCAT(LEFT(jb." . $dateMode . ",7),'-',(DAY(jb." . $dateMode . ") & 16)) "; endif; $uniqueSearchFields[] = "jb_finishtime"; endif; $groupByClause = ""; if ($groupByClauseFilter != "") : $groupByClause .= "," . $groupByClauseFilter; endif; if ($groupByClauseEmployee != "") : $groupByClauseEmployee = $groupByClauseEmployee . ","; endif; $countJobsField = $fieldClauseAmountFormular . " AS count_jobs, "; $summationField = "stat.count_jobs"; if ($stmNumber == "10" || $stmNumber == "11" || $stmNumber == "12" || $stmNumber == "13" || $stmNumber == "14" || $stmNumber == "201" || $stmNumber == "202" || $stmNumber == "203" || $stmNumber == "204" || $stmNumber == "205") : if ($statisticAllValues) : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular; $summationField2 = "stat.turnover"; $summationField3 = "stat.remuneration"; $summationField4 = "stat.eau"; $summationField5 = "stat.bsg"; $summationField6 = "stat.service_price"; $summationField7 = "stat.service_remuneration_man"; $summationField8 = "stat.km"; $summationField9 = "stat.co2e"; endif; /* if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular; $xxxx = "stat.turnover_markup"; $xxxx = "stat.remuneration_markup"; $xxxx] = "stat.eau_markup"; $xxxx = "stat.bsg_markup"; endif; */ else : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular . " as business_volume"; $summationField2 = "stat.business_volume"; endif; if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular . " as markup_volume"; $summationField3 = "stat.markup_volume"; endif; endif; endif; // Check for groups if (mcIsSet($csArrayLen, 0) > 0) : if ($customerTablesDefinedForSqlStatement) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $csArray) . ") "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cs_id IN (" . implode(",", $csArray) . ") "; endif; $customerTablesDefinedForSqlStatement = true; endif; // Check for STATIC groups if ($staticCsArrayLen > 0) : if ($customerTablesDefinedForSqlStatement) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $staticCsArray) . ") "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND jb.csc_id_related = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cs_id IN (" . implode(",", $staticCsArray) . ") "; endif; $customerTablesDefinedForSqlStatement = true; endif; // Check for excluded customers if ($whereClauseExcludedCustomers != "") : if ($customerTablesDefinedForSqlStatement) : $whereClause .= $whereClauseExcludedCustomers; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id " . $whereClauseExcludedCustomers; endif; endif; // Survey if ($stmNumber == "3010" || $stmNumber == "3011" || $stmNumber == "3012") : $countJobsField = " gdc_survey_1.gdc_content AS gdc_content_survey, gdc_survey_1.gdc_context AS gdc_context_survey, mt_survey.mt_value AS mt_value_survey, COUNT(gdc_survey_1.gdc_content) AS count_jobs "; $businessVolumeField = ""; $markupVolumeField = ""; $fromClausePriceFormular = ""; $fromClauseSurvey = " LEFT JOIN genericdatacontainer AS gdc_survey_1 ON gdc_survey_1.gdc_obj_id = tr.tr_id AND gdc_survey_1.gdc_obj_type = 'tr' AND gdc_survey_1.gdc_gen_fieldname = 'survey' AND gdc_survey_1.gdc_context >= '1' " . " LEFT JOIN metatype AS mt_survey ON gdc_survey_1.gdc_content = mt_survey.mt_sort AND mt_survey.mt_type = 'customer_survey'"; if (!(strpos($fromClauseFilter, " AS tr,") === false)) : $fromClauseFilter = str_replace(" AS tr,", " AS tr" . $fromClauseSurvey . ",", $fromClauseFilter); else : $fromClauseFilter .= "," . $dbh_tr . " AS tr" . $fromClauseSurvey; endif; $whereClause .= " AND jb.jb_id = tr.jb_id "; $groupByClause .= ", gdc_survey_1.gdc_content, gdc_survey_1.gdc_context" . " HAVING count_jobs > 0 "; endif; // If special customer is defined then take all jobs of this customer only if ($csEid != "") : if ($customerTablesDefinedForSqlStatement) : $whereClause .= " AND cs.cs_eid = '" . $csEid . "' "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cs_eid = '" . $csEid . "' "; endif; $customerTablesDefinedForSqlStatement = true; endif; // Booked jobs only $fromClauseFilter .= $fromClauseBookedJobs; $whereClause .= $whereClauseBookedJobs; $sqlquery = "SELECT " . $tableClauseCourier . ".cr_eid, usr.usr_firstname, usr.usr_name, cmp.cmp_comp AS cr_cmp_comp, " . $fieldClauseSplitByTime . " " . $countJobsField . $businessVolumeField . $markupVolumeField . $fieldClauseFilter . " FROM courier AS cr " . $fromClauseVehicles . $fromClauseMetaCourier . $fromClauseBaseCourier . ", " . $dbh_jb . " AS jb " . $indexClauseJb . $fromClauseJobpriceFormular . $fromClauseCrvh . $fromClausePriceFormular . $fromClauseCreator . $fromClauseFilter . " WHERE " . $whereClauseBaseCourier . " jb.cr_id = cr.cr_id AND" . $whereClauseJbStatus . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . $isStornoClause . $whereClausePermanentJobs . $whereClauseCrEid . $whereClausePrice . $whereClause . $whereClauseMetaCourier . $whereClausePriceFormular . $whereClauseCreator . $whereClauseVehicleFilters . " GROUP BY " . $groupByClauseEmployee . " " . $tableClauseCourier . ".cr_id" . $groupClauseSplitByTime . $groupByClause . " ORDER BY " . ($orderByClause == "" ? $orderByClauseEmployee . $tableClauseCourier . ".cr_id" : $orderByClause) . $orderByDirection; endif; // Couriers with ID, SID, name, firstname and TIME from taking a job to reach first address if ($stmNumber == "15") : $groupByClause = ""; if ($groupByClauseFilter != "") : $groupByClause .= $groupByClauseFilter; endif; // if (anotherFilter != "") : $groupByClause .= $anotherFilter; endif; // ATTENTION TO "," sign !!! if ($groupByClause != "") : $groupByClause = " GROUP BY " . $groupByClause; endif; $uniqueSearchFields[] = "cr_eid"; $uniqueSearchFields[] = "usr_name"; $uniqueSearchFields[] = "minutetext"; $sqlquery = "SELECT cr.cr_eid, usr.usr_firstname, usr.usr_name, jb.jb_taketime, tr.tr_finishtime, (UNIX_TIMESTAMP(tr.tr_finishtime)-UNIX_TIMESTAMP(jb.jb_taketime)) AS time" . $fieldClauseFilter . " FROM courier AS cr, user AS usr, " . $dbh_jb . " AS jb" . $indexClauseJb . ", " . $dbh_tr . " AS tr" . $fromClauseFilter . $fromClauseMetaCourier . " WHERE cr.usr_id = usr.usr_id AND jb.cr_id = cr.cr_id AND" . $whereClauseJbStatus . $whereClauseCreator . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . $isStornoClause . $whereClausePermanentJobs . " AND jb.jb_id = tr.jb_id AND tr_sort = '1'" . $whereClauseCrEid . $whereClause . $whereClauseMetaCourier . $groupByClause . " ORDER BY " . ($orderByClause == "" ? "cr.cr_id" : $orderByClause) . $orderByDirection; endif; // Couriers usage behaviour regarding to the devices if ($stmNumber == "2000" || $stmNumber == "2010") : // Exclude jobs with special vehicles (e.g.: SID=1888) // [1. Exclude by database parameter] $excludedVehicles = getParameterValue("0", "STATISTIC_CRVHSIDS_EXCLUDED", "0"); // "Meta-Global" <=> hq_id = 0 $whereClauseExcludedVehicles = ""; if ($excludedVehicles != "") : $tmp = spliti(",",$excludedVehicles); $lenTmp = count($tmp); for ($i = 0; $i < $lenTmp; $i++) { $whereClauseExcludedVehicles .= " RIGHT(jb.cr_sid, " . strlen($tmp[$i]) . ") != '" . $tmp[$i] . "' AND "; }; endif; // [2. Exclude by master data, vehicles with system fee have to be excluded] $excludedVehicleAray = getColVectorFromDB2ArrayByClause("couriervehicle", "crvh_sid", " crvh_system_fee = '1'"); $excludedVehicleArayLen = count($excludedVehicleAray); for ($i = 0; $i < $excludedVehicleArayLen; $i++) { $whereClauseExcludedVehicles .= " RIGHT(jb.cr_sid, " . strlen($excludedVehicleAray[$i]) . ") != '" . $excludedVehicleAray[$i] . "' AND "; }; // Exclude jobs with special couriers (e.g.: EID=HTHH99999) $excludedCouriers = getParameterValue("0", "STATISTIC_CREIDS_EXCLUDED", "0"); // "Meta-Global" <=> hq_id = 0 $whereClauseExcludedCouriers = ""; if ($excludedCouriers != "") : $excludedCouriers2 = getParameterValue("0", "STATISTIC_CREIDS_EXCLUDED_2", "0"); if ($excludedCouriers2 != "") : $excludedCouriers .= "," . $excludedCouriers2; endif; $tmp = spliti(",",$excludedCouriers); $lenTmp = count($tmp); for ($i = 0; $i < $lenTmp; $i++) { $whereClauseExcludedCouriers .= " RIGHT(cr.cr_eid, " . strlen($tmp[$i]) . ") != '" . $tmp[$i] . "' AND "; }; endif; // Exclude jobs with special customers (e.g.: EID=HTHH17000) // [1. Exclude by master data, take customers with "PDA usage enabled" only] $usageWhereClauseExcludedCustomers = "cmp.cmp_pda_usage = '1' AND "; // [2. Exclude by database parameter] $excludedCustomers = getParameterValue("0", "STATISTIC_CSEIDS_EXCLUDED", "0"); // "Meta-Global" <=> hq_id = 0 if ($excludedCustomers != "") : $excludedCustomers2 = getParameterValue("0", "STATISTIC_CSEIDS_EXCLUDED_2", "0"); if ($excludedCustomers2 != "") : $excludedCustomers .= "," . $excludedCustomers2; endif; $tmp = spliti(",",$excludedCustomers); $lenTmp = count($tmp); for ($i = 0; $i < $lenTmp; $i++) { $usageWhereClauseExcludedCustomers .= " RIGHT(cs.cs_eid, " . strlen($tmp[$i]) . ") != '" . $tmp[$i] . "' AND "; }; endif; $fieldClause = "cr.cr_eid, usr.usr_firstname, usr.usr_name, a1.num AS count_job_stations, MAX(a1.num2) AS count_jobs_signed, MAX(a1.num3) AS count_jobs, IF(SUM(a1.num3) > 0, ROUND(((SUM(a1.num2) / SUM(a1.num3)) * 100), 2), 0) AS device_usage"; if ($stmNumber == "2010") : $fieldClause = "cr.cr_eid, usr.usr_firstname, usr.usr_name, IF(SUM(a1.num3) > 0, ROUND(((SUM(a1.num2) / SUM(a1.num3)) * 100), 2), 0) AS device_usage"; endif; $groupByClause = "cr.cr_id"; if ($stmNumber == "2000") : $groupByClause = "cr.cr_id, count_job_stations"; endif; if ($groupByClauseFilter != "") : $groupByClause .= $groupByClauseFilter; endif; // if (anotherFilter != "") : $groupByClause .= $anotherFilter; endif; // ATTENTION TO "," sign !!! $orderByClauseDevice = "cr.cr_eid"; if ($stmNumber == "2000") : $orderByClauseDevice = "cr.cr_eid, a1.num"; endif; if ($orderByClause != "" && $orderByClause != "cr.cr_eid") : $orderByClauseDevice = $orderByClause . ", " . $orderByClauseDevice; endif; $uniqueSearchFields[] = "cr_eid"; $uniqueSearchFields[] = "usr_name"; $sqlquery = "SELECT " . $fieldClause . " FROM" . " courier AS cr," . " user AS usr," . " (" . " (" . " SELECT" . " x.cr_id AS cr_id, x.num AS num, COUNT(*) AS num2, '0' AS num3" . " FROM (" . " SELECT" . " cr.cr_id AS cr_id, COUNT(*) AS num" . " FROM" . " courier AS cr," . " " . $dbh_jb . " AS jb," . ($usageWhereClauseExcludedCustomers != "" ? " costcenter AS csc, customer AS cs, company AS cmp," : "") . " " . $dbh_tr . " AS tr" . " WHERE" . " jb." . $dateMode . " >= '" . $fromDateRange . "' AND" . " jb." . $dateMode . " <= '" . $toDateRange . "' AND" . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . " AND" . $whereClauseExcludedVehicles . " jb.cr_id = cr.cr_id AND" . $whereClauseExcludedCouriers . ($usageWhereClauseExcludedCustomers != "" ? $statJbCscIdField . " = csc.csc_id AND cs.cs_id = csc.cs_id AND cs.cmp_id = cmp.cmp_id AND " . $usageWhereClauseExcludedCustomers : "") . " jb.jb_id = tr.jb_id AND" . " (NOT ISNULL(tr.tr_sign)) AND" . " tr.tr_sign != ''" . " GROUP BY cr.cr_id, tr.jb_id" . " ) AS x" . " GROUP BY x.cr_id, x.num" . " )" . " UNION" . " (" . " SELECT" . " y.cr_id AS cr_id, y.num AS num, '0' AS num2, COUNT(*) AS num3" . " FROM (" . " SELECT" . " cr.cr_id AS cr_id, COUNT(*) AS num" . " FROM" . " courier AS cr," . " " . $dbh_jb . " AS jb," . ($usageWhereClauseExcludedCustomers != "" ? " costcenter AS csc, customer AS cs, company AS cmp," : "") . " " . $dbh_tr . " AS tr" . " WHERE" . " jb." . $dateMode . " >= '" . $fromDateRange . "' AND" . " jb." . $dateMode . " <= '" . $toDateRange . "' AND" . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . " AND" . $whereClauseExcludedVehicles . " jb.cr_id = cr.cr_id AND" . $whereClauseExcludedCouriers . ($usageWhereClauseExcludedCustomers != "" ? $statJbCscIdField . " = csc.csc_id AND cs.cs_id = csc.cs_id AND cs.cmp_id = cmp.cmp_id AND " . $usageWhereClauseExcludedCustomers : "") . " (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND" . " jb.jb_id = tr.jb_id" . " GROUP BY cr.cr_id, tr.jb_id" . " ) AS y" . " GROUP BY y.cr_id, y.num" . " )" . " ) AS a1" . " WHERE" . " cr.cr_id = a1.cr_id AND" . " cr.usr_id = usr.usr_id" . " GROUP BY " . $groupByClause . " ORDER BY " . $orderByClauseDevice; if ($stmNumber == "2000") : $summationField = "stat.count_jobs_signed"; $summationField2 = "stat.count_jobs"; elseif ($stmNumber == "2010") : $summationField = "stat.device_usage"; endif; endif; // Couriers with ID, SID, name, firstname and ONLINETIME with PDA/VPA if ($stmNumber == "16" || $stmNumber == "17" || $stmNumber == "18") : // Pay ATTENTION for usage of "logintime" and "logouttime" because of the ranges !!! $whereClause = ""; if ($fromDateRange != "") : $whereClause .= " AND cdt.cdt_logintime >= '" . $fromDateRange . "' "; $whereClause .= " AND cdt.cdt_logouttime >= CONCAT(LEFT(cdt.cdt_logintime,10),'" . $fromTimeRange . "')"; endif; if ($toDateRange != "") : $whereClause .= " AND cdt.cdt_logintime <= '" . $toDateRange . "' "; $whereClause .= " AND cdt.cdt_logintime <= CONCAT(LEFT(cdt.cdt_logintime,10),'" . $toTimeRange . "')"; endif; $whereClauseGroup = ""; if (mcIsSet($crArrayLen, 0) > 0) : $whereClauseGroup .= " AND cr.cr_id IN (" . implode(",", $crArray) . ") "; endif; $fieldClauseSplitByTime = ""; $groupClauseSplitByTime = ""; $uniqueSearchFields[] = "cr_eid"; if ($stmNumber == "16") : // ... endif; if ($stmNumber == "17") : $fieldClauseSplitByTime = " LEFT(cdt.cdt_logintime,7) AS cdt_logintime, "; $groupClauseSplitByTime = ", LEFT(cdt.cdt_logintime,7)"; $uniqueSearchFields[] = "cdt_logintime"; endif; if ($stmNumber == "18") : $fieldClauseSplitByTime = " LEFT(cdt.cdt_logintime,10) AS cdt_logintime, "; $groupClauseSplitByTime = ", LEFT(cdt.cdt_logintime,10)"; $uniqueSearchFields[] = "cdt_logintime"; endif; $groupByClause = ""; if ($groupByClauseFilter != "") : $groupByClause .= "," . $groupByClauseFilter; endif; $sqlquery = "SELECT cr.cr_eid, usr.usr_firstname, usr.usr_name, " . $fieldClauseSplitByTime . " SEC_TO_TIME(SUM((LEAST(UNIX_TIMESTAMP(cdt.cdt_logouttime),UNIX_TIMESTAMP(CONCAT(LEFT(cdt.cdt_logintime,10),'" . $toTimeRange . "')))-GREATEST(UNIX_TIMESTAMP(cdt.cdt_logintime),UNIX_TIMESTAMP(CONCAT(LEFT(cdt.cdt_logintime,10),'" . $fromTimeRange . "')))))) AS time" . " FROM courier AS cr, user AS usr, " . $dbh_cdt . " AS cdt " . $fromClauseMetaCourier . " WHERE cdt.cdt_logouttime != '0000-00-00 00:00:00' AND cdt.cr_id = cr.cr_id " . $whereClauseGroup . " AND cr.usr_id = usr.usr_id AND" . " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . $whereClauseCrEid . $whereClause . $whereClauseMetaCourier . " GROUP BY cr.cr_id" . $groupClauseSplitByTime . $groupByClause . " ORDER BY " . ($orderByClause == "" ? "cr.cr_id" . $groupClauseSplitByTime : $orderByClause) . $orderByDirection; endif; // Behaviour of the couriers if ($stmNumber == "1000") : $summationField = "stat.count_jobs"; $businessVolumeField = ""; $markupVolumeField = ""; if ($fieldClausePriceFormular != "") : $businessVolumeField = ", " . $fieldClausePriceFormular . " as business_volume"; $summationField2 = "stat.business_volume"; endif; if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular . " as markup_volume"; $summationField3 = "stat.markup_volume"; endif; $whereClauseGroup = ""; if (mcIsSet($crArrayLen, 0) > 0) : $whereClauseGroup .= " AND log.cr_id IN (" . implode(",", $crArray) . ") "; endif; $groupByClause = ""; if ($groupByClauseFilter != "") : $groupByClause .= "," . $groupByClauseFilter; endif; if ($groupByClauseEmployee != "") : $groupByClause .= "," . $groupByClauseEmployee; endif; $uniqueSearchFields[] = "cr_eid"; $uniqueSearchFields[] = "usr_name"; /* $sqlquery = "SELECT cr.cr_eid, usr.usr_firstname, usr.usr_name, count(" . $whereClausePDActionsPerJob . "log.jb_id) AS count_jobs" . $fieldClauseFilter . " FROM phoenix_log.log AS log, user AS usr, courier AS cr" . $fromClauseFilter . $fromClauseMetaCourier . " WHERE log.log_createtime >= '$fromDateRange' AND log.log_createtime <= '$toDateRange' AND" . " log.cr_id = cr.cr_id AND cr.usr_id = usr.usr_id AND" . " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . $whereClauseCrEid . $whereClause . $whereClauseMetaCourier . " GROUP BY cr.cr_id" . $groupByClause . " ORDER BY " . ($orderByClause == "" ? "cr.cr_id" : $orderByClause) . $orderByDirection; $summationField = "stat.count_jobs"; */ if ($whereClausePDActionsPerJob == "") : $sqlquery = "SELECT cr.cr_eid, usr.usr_firstname, usr.usr_name, COUNT(log.jb_id) AS count_jobs" . $businessVolumeField . $markupVolumeField . $fieldClauseFilter . " FROM phoenix_log.log AS log, phoenix.user AS usr, phoenix.courier AS cr, phoenix." . $dbh_jb . " AS jb " . $fromClauseCrvh . $fromClauseFilter . $fromClauseMetaCourier . " WHERE log.log_createtime >= '$fromDateRange' AND log.log_createtime <= '$toDateRange' " . $whereClauseGroup . "AND" . " log.jb_id = jb.jb_id AND log.cr_id = cr.cr_id AND cr.usr_id = usr.usr_id AND" . " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . $whereClauseCrEid . " AND log.logo_id = '" . $logOpId . "' " . $whereClauseClockTime . $whereClause . $whereClauseMetaCourier . " GROUP BY cr.cr_id" . $groupByClause . " ORDER BY " . ($orderByClause == "" ? "cr.cr_id" : $orderByClause) . $orderByDirection; else : $sqlquery = "SELECT cr.cr_eid, usr.usr_firstname, usr.usr_name, COUNT(jb.jb_id) AS count_jobs" . $businessVolumeField . $markupVolumeField . $fieldClauseFilter . " FROM" . " (SELECT DISTINCT log.logo_id, log.hq_id, log.cr_id, log.usr_id, log.jb_id" . " FROM phoenix_log.log AS log" . " WHERE log.log_createtime >= '$fromDateRange' AND log.log_createtime <= '$toDateRange' AND" . " log.hq_id IN " . getSQLMandatorArray($f_hq_id) . " AND log.logo_id = '" . $logOpId . "'" . $whereClauseClockTime . $whereClauseGroup . " ORDER BY log.cr_id, log.jb_id" . " ) AS log," . " phoenix.user AS usr, phoenix.courier AS cr, phoenix." . $dbh_jb . " AS jb " . $fromClauseCrvh . $fromClauseFilter . $fromClauseMetaCourier . " WHERE" . " log.jb_id = jb.jb_id AND log.cr_id = cr.cr_id AND cr.usr_id = usr.usr_id AND" . " cr.hq_id IN " . getSQLMandatorArray($f_hq_id) . $whereClauseCrEid . $whereClause . $whereClauseMetaCourier . " GROUP BY cr.cr_id" . $groupByClause . " ORDER BY " . ($orderByClause == "" ? "cr.cr_id" : $orderByClause) . $orderByDirection; endif; endif; // ******** // * JOBS * // ******** if ($stmNumber == "30" || $stmNumber == "31" || $stmNumber == "32" || $stmNumber == "33" || $stmNumber == "34" || $stmNumber == "101" || $stmNumber == "102" || $stmNumber == "103" || $stmNumber == "104" || $stmNumber == "105" || $stmNumber == "3000" || $stmNumber == "3001" || $stmNumber == "3002") : $fieldClauseSplitByTime = ""; $groupAndOrderClauseSplitByTime = ""; $keyFieldArray = ""; if ($stmNumber == "30" || $stmNumber == "101" || $stmNumber == "3000") : // ... endif; if ($stmNumber == "31" || $stmNumber == "102" || $stmNumber == "3001") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",7) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = "LEFT(jb." . $dateMode . ",7)"; $keyFieldArray = "jb_finishtime"; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "32" || $stmNumber == "103" || $stmNumber == "3002") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",10) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = "LEFT(jb." . $dateMode . ",10)"; $keyFieldArray = "jb_finishtime"; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "33" || $stmNumber == "104") : $fieldClauseSplitByTime = " YEARWEEK(LEFT(jb." . $dateMode . ",10),3) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = "YEARWEEK(LEFT(jb." . $dateMode . ",10),3)"; $keyFieldArray = "jb_finishtime"; $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "34" || $stmNumber == "105") : $fieldClauseSplitByTime = " CONCAT(LEFT(jb." . $dateMode . ",7),'-',IF((DAY(jb." . $dateMode . ") & 16) <> 16,'I','II')) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = "CONCAT(LEFT(jb." . $dateMode . ",7),'-',(DAY(jb." . $dateMode . ") & 16))"; $keyFieldArray = "jb_finishtime"; $uniqueSearchFields[] = "jb_finishtime"; endif; $groupByClause = $groupAndOrderClauseSplitByTime; if ($groupByClause != "" && $groupByClauseFilter != "") : $groupByClause .= ","; endif; if ($groupByClauseFilter != "") : $groupByClause .= $groupByClauseFilter; endif; if ($groupByClause != "" && $groupByClauseEmployee != "") : $groupByClause = "," . $groupByClause; endif; if ($groupByClauseEmployee != "") : $groupByClause = $groupByClauseEmployee . $groupByClause; endif; // if (anotherFilter != "") : $groupByClause .= $anotherFilter; endif; // ATTENTION TO "," sign !!! $countJobsField = $fieldClauseAmountFormular . " AS count_jobs, "; $summationField = "stat.count_jobs"; if ($stmNumber == "30" || $stmNumber == "31" || $stmNumber == "32" || $stmNumber == "33" || $stmNumber == "34" || $stmNumber == "101" || $stmNumber == "102" || $stmNumber == "103" || $stmNumber == "104" || $stmNumber == "105") : if ($statisticAllValues) : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular; $summationField2 = "stat.turnover"; $summationField3 = "stat.remuneration"; $summationField4 = "stat.eau"; $summationField5 = "stat.bsg"; $summationField6 = "stat.service_price"; $summationField7 = "stat.service_remuneration_man"; $summationField8 = "stat.km"; $summationField9 = "stat.co2e"; endif; /* if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular; $xxxx = "stat.turnover_markup"; $xxxx = "stat.remuneration_markup"; $xxxx] = "stat.eau_markup"; $xxxx = "stat.bsg_markup"; endif; */ else : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular . " as business_volume"; $summationField2 = "stat.business_volume"; $sumField = "business_volume"; writeToFile("../log/mc_stat_special.log", "1111"); endif; if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular . " as markup_volume"; $summationField3 = "stat.markup_volume"; // $sumField2 = "markup_volume"; endif; endif; endif; writeToFile("../log/mc_stat_special.log", "businessVolumeField = " . $businessVolumeField); writeToFile("../log/mc_stat_special.log", "fieldClausePriceFormular = " . $fieldClausePriceFormular); writeToFile("../log/mc_stat_special.log", "fieldClauseMarkupFormular = " . $fieldClauseMarkupFormular); // Check for groups if (mcIsSet($csArrayLen, 0) > 0) : if ($customerTablesDefinedForSqlStatement) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $csArray) . ") "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cs_id IN (" . implode(",", $csArray) . ") "; endif; $customerTablesDefinedForSqlStatement = true; endif; // Check for STATIC groups if ($staticCsArrayLen > 0) : if ($customerTablesDefinedForSqlStatement) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $staticCsArray) . ") "; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND jb.csc_id_related = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cs_id IN (" . implode(",", $staticCsArray) . ") "; endif; $customerTablesDefinedForSqlStatement = true; endif; // Check for excluded customers if ($whereClauseExcludedCustomers != "") : if ($customerTablesDefinedForSqlStatement) : $whereClause .= $whereClauseExcludedCustomers; else : $fromClauseFilter .= ", costcenter AS csc, customer AS cs"; $whereClause .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id " . $whereClauseExcludedCustomers; endif; endif; // Survey if ($stmNumber == "3000" || $stmNumber == "3001" || $stmNumber == "3002") : $countJobsField = " gdc_survey_1.gdc_content AS gdc_content_survey, gdc_survey_1.gdc_context AS gdc_context_survey, mt_survey.mt_value AS mt_value_survey, COUNT(gdc_survey_1.gdc_content) AS count_jobs "; $businessVolumeField = ""; $markupVolumeField = ""; $fromClausePriceFormular = ""; $fromClauseSurvey = " LEFT JOIN genericdatacontainer AS gdc_survey_1 ON gdc_survey_1.gdc_obj_id = tr.tr_id AND gdc_survey_1.gdc_obj_type = 'tr' AND gdc_survey_1.gdc_gen_fieldname = 'survey' AND gdc_survey_1.gdc_context >= '1' " . " LEFT JOIN metatype AS mt_survey ON gdc_survey_1.gdc_content = mt_survey.mt_sort AND mt_survey.mt_type = 'customer_survey'"; if (!(strpos($fromClauseFilter, " AS tr,") === false)) : $fromClauseFilter = str_replace(" AS tr,", " AS tr" . $fromClauseSurvey . ",", $fromClauseFilter); else : $fromClauseFilter .= "," . $dbh_tr . " AS tr" . $fromClauseSurvey; endif; $whereClause .= " AND jb.jb_id = tr.jb_id "; if ($groupByClause != "") : $groupByClause .= ", "; endif; $groupByClause .= "gdc_survey_1.gdc_content, gdc_survey_1.gdc_context" . " HAVING count_jobs > 0 "; endif; // Booked jobs only $fromClauseFilter .= $fromClauseBookedJobs; $whereClause .= $whereClauseBookedJobs; if ($groupByClause != "") : $groupByClause = " GROUP BY " . $groupByClause; endif; $sqlquery = "SELECT " . $fieldClauseSplitByTime. " " . $countJobsField . $businessVolumeField . $markupVolumeField . $fieldClauseFilter . " FROM " . $dbh_jb . " AS jb " . $indexClauseJb . $fromClauseJobpriceFormular . $fromClauseCrvh . $fromClausePriceFormular . $fromClauseCreator . $fromClauseFilter . " WHERE " . $whereClauseJbStatus . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . $isStornoClause . $whereClausePermanentJobs . $exportedJobClause . $whereClausePrice . $whereClause . $whereClausePriceFormular . $whereClauseCreator . $whereClauseVehicleFilters . $groupByClause . " ORDER BY " . ($orderByClause == "" ? ($groupAndOrderClauseSplitByTime == "" ? $orderByClauseEmployee . "business_volume" : $groupAndOrderClauseSplitByTime) : $orderByClause) . $orderByDirection; endif; writeToFile("../log/mc_stat_special.log", "fieldClausePriceFormular = " . $fieldClausePriceFormular); writeToFile("../log/mc_stat_special.log", "businessVolumeField = " . $businessVolumeField); // ************* // * CUSTOMERS * // ************* if ($stmNumber == "60" || $stmNumber == "61" || $stmNumber == "62" || $stmNumber == "63" || $stmNumber == "64" || $stmNumber == "301" || $stmNumber == "302" || $stmNumber == "303" || $stmNumber == "304" || $stmNumber == "305" || $stmNumber == "3020" || $stmNumber == "3021") : $customerTablesDefinedForSqlStatement = true; // Customer tables defined inherently because a customer statistic is requested $fieldClauseSplitByTime = ""; $groupAndOrderClauseSplitByTime = ""; $keyFieldArray = array("cs_eid","cmp_comp","cmp_comp2"); $uniqueSearchFields[] = "cs_eid"; if ($stmNumber == "60" || $stmNumber == "301" || $stmNumber == "3020") : // ... endif; if ($stmNumber == "61" || $stmNumber == "302" || $stmNumber == "3021") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",7) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = ", LEFT(jb." . $dateMode . ",7)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", LEFT(jb." . $dateMode . ",7) "; endif; $keyFieldArray = array("cs_eid","cmp_comp","cmp_comp2","jb_finishtime"); $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "64" || $stmNumber == "305") : $fieldClauseSplitByTime = " LEFT(jb." . $dateMode . ",10) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = ", LEFT(jb." . $dateMode . ",10)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", LEFT(jb." . $dateMode . ",10) "; endif; $keyFieldArray = array("cs_eid","cmp_comp","cmp_comp2","jb_finishtime"); $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "62" || $stmNumber == "303") : $fieldClauseSplitByTime = " YEARWEEK(LEFT(jb." . $dateMode . ",10),3) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = ", YEARWEEK(LEFT(jb." . $dateMode . ",10),3)"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", YEARWEEK(LEFT(jb." . $dateMode . ",10),3) "; endif; $keyFieldArray = array("cs_eid","cmp_comp","cmp_comp2","jb_finishtime"); $uniqueSearchFields[] = "jb_finishtime"; endif; if ($stmNumber == "63" || $stmNumber == "304") : $fieldClauseSplitByTime = " CONCAT(LEFT(jb." . $dateMode . ",7),'-',IF((DAY(jb." . $dateMode . ") & 16) <> 16,'I','II')) AS jb_finishtime, "; $groupAndOrderClauseSplitByTime = ", CONCAT(LEFT(jb." . $dateMode . ",7),'-',(DAY(jb." . $dateMode . ") & 16))"; if ($orderByClause == "cs.cs_eid" || trim($orderByClause) == "cs.cs_eid, csc.csc_name") : $orderByClause .= ", CONCAT(LEFT(jb." . $dateMode . ",7),'-',(DAY(jb." . $dateMode . ") & 16)) "; endif; $keyFieldArray = array("cs_eid","cmp_comp","cmp_comp2","jb_finishtime"); $uniqueSearchFields[] = "jb_finishtime"; endif; $groupByClause = ""; if ($groupByClauseFilter != "") : $groupByClause .= "," . $groupByClauseFilter; endif; if ($groupByClauseEmployee != "") : $groupByClauseEmployee = $groupByClauseEmployee . ","; endif; $countJobsField = $fieldClauseAmountFormular . " AS count_jobs, "; $summationField = "stat.count_jobs"; if ($stmNumber == "60" || $stmNumber == "61" || $stmNumber == "62" || $stmNumber == "63" || $stmNumber == "64" || $stmNumber == "301" || $stmNumber == "302" || $stmNumber == "303" || $stmNumber == "304" || $stmNumber == "305") : if ($statisticAllValues) : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular; $summationField2 = "stat.turnover"; $summationField3 = "stat.remuneration"; $summationField4 = "stat.eau"; $summationField5 = "stat.bsg"; $summationField6 = "stat.service_price"; $summationField7 = "stat.service_remuneration_man"; $summationField8 = "stat.km"; $summationField9 = "stat.co2e"; endif; /* if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular; $xxxx = "stat.turnover_markup"; $xxxx = "stat.remuneration_markup"; $xxxx] = "stat.eau_markup"; $xxxx = "stat.bsg_markup"; endif; */ else : if ($fieldClausePriceFormular != "") : $businessVolumeField = $fieldClausePriceFormular . " as business_volume"; $summationField2 = "stat.business_volume"; $sumField = "business_volume"; endif; if ($fieldClauseMarkupFormular != "") : $markupVolumeField = ", " . $fieldClauseMarkupFormular . " as markup_volume"; $summationField3 = "stat.markup_volume"; // $sumField2 = "markup_volume"; endif; endif; endif; // Check for groups if (mcIsSet($csArrayLen, 0) > 0) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $csArray) . ") "; endif; // Check for STATIC groups if ($staticCsArrayLen > 0) : $whereClause .= " AND cs.cs_id IN (" . implode(",", $staticCsArray) . ") "; endif; // Check for excluded customers if ($whereClauseExcludedCustomers != "") : if ($customerTablesDefinedForSqlStatement) : $whereClause .= $whereClauseExcludedCustomers; endif; endif; // Computation of the business amount according to involving table "tourservice" or using the filed "jb_totalprice" $whereClausePriceFormular .= " AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cmp_id = cmp.cmp_id "; // Display invoice address data $fieldClauseInvoiceAddressData = ""; $fromClauseInvoiceAddressData = ""; if ($f_show_invoice_address == "1") : $fieldClauseInvoiceAddressData = ", ad_inv.ad_street AS ad_inv_street, cscad_inv.cscad_hsno AS ad_inv_hsno, ad_inv.ad_zipcode AS ad_inv_zipcode, ad_inv.ad_city AS ad_inv_city "; $fromClauseInvoiceAddressData = " LEFT JOIN costcenter AS csc_root_inv ON cs.csc_id = csc_root_inv.csc_id LEFT JOIN costcenteraddress AS cscad_inv ON csc_root_inv.csc_id = cscad_inv.csc_id AND cscad_inv.adt_id = '2' LEFT JOIN address AS ad_inv ON cscad_inv.ad_id = ad_inv.ad_id "; endif; // Display delivery address data $fieldClauseDeliveryAddressData = ""; $fromClauseDeliveryAddressData = ""; if ($f_show_delivery_address == "1") : $fieldClauseDeliveryAddressData = ", ad.ad_street AS ad_street, cscad.cscad_hsno AS ad_hsno, ad.ad_zipcode AS ad_zipcode, ad.ad_city AS ad_city "; $fromClauseDeliveryAddressData = " LEFT JOIN costcenter AS csc_root ON cs.csc_id = csc_root.csc_id LEFT JOIN costcenteraddress AS cscad ON csc_root.csc_id = cscad.csc_id AND cscad.adt_id = '4' LEFT JOIN address AS ad ON cscad.ad_id = ad.ad_id "; endif; // Display master data $fieldClauseMasterData = ""; $fromClauseMasterData = ""; if ($f_show_master_data == "1") : $fieldClauseMasterData = ", cs_usr.usr_email, cs_usr.usr_phone "; $fromClauseMasterData = " LEFT JOIN employee AS cs_emp ON cs.cs_admin = cs_emp.emp_id LEFT JOIN user AS cs_usr ON cs_emp.usr_id = cs_usr.usr_id "; endif; // Survey if ($stmNumber == "3020" || $stmNumber == "3021") : $countJobsField = " gdc_survey_1.gdc_content AS gdc_content_survey, gdc_survey_1.gdc_context AS gdc_context_survey, mt_survey.mt_value AS mt_value_survey, COUNT(gdc_survey_1.gdc_content) AS count_jobs "; $businessVolumeField = ""; $markupVolumeField = ""; $fromClausePriceFormular = ""; $fromClauseSurvey = " LEFT JOIN genericdatacontainer AS gdc_survey_1 ON gdc_survey_1.gdc_obj_id = tr.tr_id AND gdc_survey_1.gdc_obj_type = 'tr' AND gdc_survey_1.gdc_gen_fieldname = 'survey' AND gdc_survey_1.gdc_context >= '1' " . " LEFT JOIN metatype AS mt_survey ON gdc_survey_1.gdc_content = mt_survey.mt_sort AND mt_survey.mt_type = 'customer_survey'"; if (!(strpos($fromClauseFilter, " AS tr,") === false)) : $fromClauseFilter = str_replace(" AS tr,", " AS tr" . $fromClauseSurvey . ",", $fromClauseFilter); else : $fromClauseFilter .= "," . $dbh_tr . " AS tr" . $fromClauseSurvey; endif; $whereClause .= " AND jb.jb_id = tr.jb_id "; $groupByClause .= ", gdc_survey_1.gdc_content, gdc_survey_1.gdc_context" . " HAVING count_jobs > 0 "; endif; // If special COURIER is defined for requested CUSTOMER statistic data, then take all jobs of this courier AND/OR his drivers only // "$f_cr_meta" and "$f_cr_parent" will be interpreted for output as "split by cr_id" (!!!!) if ($crEid != "") : $fromClauseFilter .= ", courier AS cr, courier AS cr2"; $whereClause = " AND jb.cr_id = cr.cr_id AND (cr.cr_eid = '" . $crEid . "' OR ((NOT ISNULL(cr.cr_id_parent)) AND cr.cr_id_parent != '0' AND cr.cr_id_parent = cr2.cr_id AND cr2.cr_eid = '" . $crEid . "')) "; if ($f_cr_meta == "1" || $f_cr_parent == "1" || $f_cr_parent == "2") : $groupAndOrderClauseSplitByTime .= ", cr2.cr_id"; endif; endif; // Booked jobs only $fromClauseFilter .= $fromClauseBookedJobs; $whereClause .= $whereClauseBookedJobs; $sqlquery = "SELECT cs.cs_eid, cmp.cmp_comp, cmp.cmp_comp2" . $fieldClauseInvoiceAddressData . $fieldClauseDeliveryAddressData . $fieldClauseMasterData . ", " . $fieldClauseSplitByTime . " " . $countJobsField . $businessVolumeField . $markupVolumeField . $fieldClauseFilter . " FROM " . $dbh_jb . " AS jb " . $indexClauseJb . $fromClauseJobpriceFormular . $fromClauseCrvh . $fromClausePriceFormular . $fromClauseCreator . ", costcenter AS csc, customer AS cs" . $fromClauseInvoiceAddressData . $fromClauseDeliveryAddressData . $fromClauseMasterData . ", company AS cmp" . $fromClauseFilter . $fromClauseMetaCustomer . " WHERE " . $whereClauseJbStatus . " " . $hqIdClause . " IN " . getSQLMandatorArray($f_hq_id) . $isStornoClause . $whereClausePermanentJobs . $whereClausePrice . $whereClause . $whereClausePriceFormular . $whereClauseCreator . $whereClauseCsEid . $whereClauseMetaCustomer . $whereClauseCmpNewDate . $whereClauseBecomeCsDate . $whereClauseVehicleFilters . " GROUP BY " . $groupByClauseEmployee . " cs.cs_id" . $groupAndOrderClauseSplitByTime . $groupByClause . " ORDER BY " . ($orderByClause == "" ? $orderByClauseEmployee . "cs.cs_id" . $groupAndOrderClauseSplitByTime : $orderByClause) . $orderByDirection; endif; // Get db-result and convert to array if ($sqlquery != "") : // echo "Category:" . $stmNumber . "
" . "Index-Clause:" . $indexClauseJb . "

" . $sqlquery . "

"; // die(); writeToFile("../log/mc_stat_special.log", $sqlquery); $result = $db_conn->query($sqlquery); if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage()); $retArray = convertRowToArray($result); $result->free(); if ($sqlquery2 != "") : // echo $sqlquery2 . "

"; $result = $db_conn->query($sqlquery2); if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage()); $tmpArray = convertRowToArray($result); $result->free(); // Get summed up array if (count($keyFieldArray) < 2) : $retArray = array_merge_sum ($retArray, $tmpArray, $keyFieldArray, $sumField); else : $retArray = array_merge_sum2 ($retArray, $tmpArray, $keyFieldArray, $sumField); endif; endif; endif; $globalParArray = array($aligns, $f_statusMode, $f_dateMode, $f_showDateAndTime, $f_crvh_sid, $f_direction_sort, $f_filter, $f_filter2, $f_filter3, $f_filter4, $f_filter5, $f_filter6, $f_filter7, $f_filter8, $f_filter9, $f_filter10, $f_price_formular, $f_filter_a, $f_filter_interval, $g_crvh_filter, $f_mode_statistic, $f_filter_calc, $f_service, $f_servicetype, $f_jb_service, $f_split_jb_services, $f_jb_specifics, $f_cs_meta, $f_cr_meta, $f_cr_parent, $f_cr_pda_actions, $f_show_invoice_address, $f_show_delivery_address, $summationField, $summationField2, $uniqueSearchFields, $hour_from, $minute_from, $hour_to, $minute_to, $fromTimeRange, $toTimeRange, $f_cmp_new_date, $f_cs_become_cs_date, $f_net_gross, $f_group, $f_groupLen, $f_staticGroup, $f_staticGroupLen, $f_priceMode, $f_editCsMode, $station_based_group_field, $f_bookedJobsOnly, $f_permanentJobsOnly, $f_filter_jb_weight_from, $f_filter_jb_length_from, $f_filter_jb_width_from, $f_filter_jb_height_from, $f_filter_jb_position_from, $f_filter_jb_weight_to, $f_filter_jb_length_to, $f_filter_jb_width_to, $f_filter_jb_height_to, $f_filter_jb_position_to, $f_show_master_data, $summationField3, $f_filter11, $f_filter12, $f_filter13, $f_filter14, $summationField4, $summationField5, $summationField6, $summationField7, $summationField8, $summationField9); return array($retArray, $globalParArray); } // Special customer statistic function getCsStatistic ($csId, $fromDate, $toDate, $fromTime = "", $toTime = "", $retRawVal = "") { global $db, $PHP_SELF; global $parGlobalUseRelatedCustomer; $statisticValue = 0; if ($csId != "" && is_numeric($csId)) : // Init times if empty if ($fromTime == "") : $fromTime = "00:00:00"; endif; if ($toTime == "") : $toTime = "23:59:59"; endif; // Init to current year if empty values if ($fromDate == "") : $fromDate = getDateTime("year") . "-01-01"; endif; if ($toDate == "") : $toDate = getDateTime("3"); endif; $fromDateTime = $fromDate . " " . $fromTime; $toDateTime = $toDate . " " . $toTime; $usedHqId = getFieldValueFromId("customer", "cs_id", $csId, "hq_id"); // The statistic can be switched from "jb.csc_id_payer" e.g. to "jb.csc_id_related" by "$f_filter10" (Default "jb.csc_id_payer") !!!! // Init the value per global parameter "GLOBAL_USE_RELATED_CUSTOMER" because $f_filter10 could be "0" $statJbCscIdField = "jb.csc_id_payer"; if ($parGlobalUseRelatedCustomer == "1") : $statJbCscIdField = "jb.csc_id_related"; endif; // Statistic business volume from 01.01. of the current year to the current day $tmpSqlRequest = "SELECT SUM(jb.jb_totalprice) AS business_volume FROM job AS jb,costcenter AS csc,customer AS cs,company AS cmp WHERE jb.jb_status = '2' AND jb.hq_id = '" . $usedHqId . "' AND (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1' OR jb.jb_storno = '3') AND jb.jb_finishtime >= '" . $fromDateTime . "' AND jb.jb_finishtime <= '" . $toDateTime . "' AND " . $statJbCscIdField . " = csc.csc_id AND csc.cs_id = cs.cs_id AND cs.cmp_id = cmp.cmp_id AND cs.cs_id = '". $csId . "' GROUP BY cs.cs_id"; $statisticValue = $db->getOne($tmpSqlRequest); if ($statisticValue == "") : $statisticValue = 0; endif; if ($retRawVal != "1") : $statisticValue = number_format(round($statisticValue,2), 2, ",", "."); endif; endif; return $statisticValue; } ?>