241 lines
7.3 KiB
PHP
Executable File
241 lines
7.3 KiB
PHP
Executable File
<?php
|
|
if (! defined ( 'sugarEntry' ) || ! sugarEntry)
|
|
die ( 'Not A Valid Entry Point' );
|
|
/**
|
|
* ********************* PREPARE ********************
|
|
*/
|
|
|
|
global $app_list_strings;
|
|
global $current_user;
|
|
$db = $GLOBALS ['db'];
|
|
$data = array ();
|
|
$date_from = date("Y-01-01");
|
|
$date_to = date("Y-m-01");
|
|
$query = "";
|
|
$selectedColumns = array();
|
|
$suma = array();
|
|
$mons = array( 1 => "Styczeń",
|
|
2 => "Luty",
|
|
3 => "Marzec",
|
|
4 => "Kwiecień",
|
|
5 => "Maj",
|
|
6 => "Czerwiec",
|
|
7 => "Lipiec",
|
|
8 => "Sierpień",
|
|
9 => "Wrzesień",
|
|
10 => "Październik",
|
|
11 => "Listopad",
|
|
12 => "Grudzień");
|
|
// Pobieranie danych z formularza
|
|
if(!$_GET['date_from']){
|
|
$date_from = date("Y-01-01");
|
|
}else{
|
|
$date_from = $_GET['date_from'];
|
|
$tmp = date_parse_from_format("d.m.Y", $_GET['date_from']);
|
|
$date_from = date($tmp['year']."-".$tmp['month']."-01");
|
|
}
|
|
if(!$_GET['date_to']){
|
|
$date_to = date("Y-m-d");
|
|
}else{
|
|
$date_to = $_GET['date_to'];
|
|
}
|
|
//formatowanie daty
|
|
$Calendar_daFormat = str_replace("d","%d",str_replace("m","%m",str_replace("Y","%Y",$GLOBALS['timedate']->get_date_format())));
|
|
$date_from = $GLOBALS['timedate']->to_display_date($date_from);
|
|
$date_to = $GLOBALS['timedate']->to_display_date($date_to);
|
|
|
|
$selectedColumns = json_decode($current_user->getPreference('ecmreports_reportvalue_column'));
|
|
//Sprawdzanie jakie kolumny ma zaznaczone uzytkownik i zapisywanie do bazy w razie zmian
|
|
|
|
if($_GET['selectedColumns']==NULL){
|
|
//Jeśli nic nie bylo przeslane z formularza i nie bylo nic w bazie
|
|
//tworzy nowa tablice z zaznaczonymi wszystkimi kolumnami
|
|
if($selectedColumns==NULL){
|
|
$selectedColumns = array();
|
|
foreach($app_list_strings['document_category_dom'] as $key=> $value){
|
|
$selectedColumns[] = $key;
|
|
}
|
|
// i ja zapisuje
|
|
$current_user->setPreference('ecmreports_reportvalue_column', json_encode($selectedColumns));
|
|
}
|
|
}else{
|
|
// Jeśli było coś przesłane przez formularz i nie bylo nic w bazie
|
|
// to zapisuje przeslane kolumny
|
|
if($selectedColumns==NULL){
|
|
$current_user->setPreference('ecmreports_reportvalue_column', json_encode($_GET['selectedColumns']));
|
|
}else{
|
|
// Jesli bylo cos przeslane przez formularz i bylo cos w bazie
|
|
// sprawdza czy tablice sie roznia elementami - jak tak to zapisuje zmiany do bazy
|
|
// i ustawia nowe kolumny
|
|
if(count($_GET['selectedColumns'])!=count($selectedColumns)){
|
|
if(count($_GET['selectedColumns'])!=0){
|
|
$current_user->setPreference('ecmreports_reportvalue_column', json_encode($_GET['selectedColumns']));
|
|
$selectedColumns = $_GET['selectedColumns'];
|
|
}
|
|
}
|
|
// Jak nic sie nie zmienilo to nic nie robi bo po co
|
|
}
|
|
}
|
|
|
|
/**
|
|
* ************* GET DATA FROM DB********************
|
|
*/
|
|
|
|
//Wybieranie zaznaczonych kolumn
|
|
$querycategory="";
|
|
foreach($selectedColumns as $value){
|
|
$querycategory = $querycategory . "'" . $value ."',";
|
|
}
|
|
$querycategory = rtrim($querycategory, ',');
|
|
$tmp = date_parse_from_format("d.m.Y", $date_to);
|
|
$date_finish_m = $tmp['month'];
|
|
$date_finish_y = $tmp['year'];
|
|
$date_finish_d = $tmp['day'];
|
|
|
|
$tmp = date_parse_from_format("d.m.Y", $date_from);
|
|
$date_f_month = $tmp['month']-1;
|
|
$date_f_year = $tmp['year'];
|
|
$date_f = "01.". $date_f_month . "." . $date_f_year;
|
|
$date_t_year = $date_f_year;
|
|
$data[$date_f_year] = array();
|
|
do{
|
|
$date_f_month++;
|
|
if($date_f_month == 13){
|
|
$date_f_month = 1;
|
|
$date_f_year++;
|
|
$data[$date_f_year] = array();
|
|
}
|
|
$date_f = "01.". $date_f_month . "." . $date_f_year;
|
|
|
|
$date_t_month = $date_f_month + 1;
|
|
if($date_t_month == 13){
|
|
$date_t_month = 1;
|
|
$date_t_year++;
|
|
}
|
|
if($date_f_year == $date_finish_y && $date_f_month == $date_finish_m){
|
|
$date_t_month = $date_finish_m;
|
|
$date_t_year = $date_finish_y;
|
|
$date_t_day = $date_finish_d;
|
|
}else{
|
|
$date_t_day = "01";
|
|
}
|
|
$date_t = $date_t_day .".". $date_t_month . "." . $date_t_year;
|
|
// inicjalizacja danych
|
|
|
|
|
|
$querydatefrom = "STR_TO_DATE('".$date_f."','%d.%m.%Y') ";
|
|
$querydateto = "STR_TO_DATE('".$date_t."','%d.%m.%Y') ";
|
|
$query = "SELECT
|
|
category_id,
|
|
ROUND(SUM(ifnull(value,'0,00')),2) as value
|
|
FROM
|
|
documents
|
|
WHERE
|
|
deleted=0 AND
|
|
category_id IN (".$querycategory.") AND
|
|
date_entered >= " . $querydatefrom . " AND
|
|
date_entered <= " . $querydateto . "
|
|
group by category_id";
|
|
$rows = $db->query ( $query );
|
|
$data[$date_f_year][$mons[$date_f_month]] = array();
|
|
if($rows->num_rows == 0){
|
|
// Jeśli baza nie zwróci żadnych rekordów to ustawia puste pola
|
|
foreach($selectedColumns as $wartosc){
|
|
$data[$date_f_year][$mons[$date_f_month]][] = '0,00';
|
|
}
|
|
// Dodatkowa kolumna na sumę
|
|
$data[$date_f_year][$mons[$date_f_month]][] = '0,00';
|
|
}else{
|
|
$tmp2 = array();
|
|
$sumka = 0;
|
|
// Rekordy z bazy danych wpisujemy do tymczasowej tabeli by móc wstawić dane w odpowiednie miejsce
|
|
while ( $r = $db->fetchByAssoc ( $rows ) ) {
|
|
$row = array ();
|
|
$row['category_id'] = $r['category_id'];
|
|
$row['value'] = number_format($r['value'], 2, ",", ".");
|
|
$sumka +=$r['value'];
|
|
$tmp2[] = $row;
|
|
}
|
|
// tutaj wstawiamy w odpowiednie miejsce
|
|
foreach($selectedColumns as $wartosc){
|
|
$sprawdzamy = false;
|
|
foreach($tmp2 as $cos){
|
|
if($wartosc==$cos['category_id']){
|
|
$sprawdzamy = true;
|
|
$data[$date_f_year][$mons[$date_f_month]][] = $cos['value'];
|
|
}
|
|
}
|
|
if(!$sprawdzamy){
|
|
$data[$date_f_year][$mons[$date_f_month]][] = '0,00';
|
|
}
|
|
}
|
|
// dodajemy ostatni wiersz sumy
|
|
$data[$date_f_year][$mons[$date_f_month]][]=number_format($sumka, 2, ",", ".");
|
|
}
|
|
}while(!($date_f_year == $date_finish_y && $date_f_month == $date_finish_m && $date_t_day == $date_finish_d));
|
|
|
|
//Pobieranie sumy dla każdego typu
|
|
$query = "SELECT
|
|
category_id,
|
|
ROUND(SUM(ifnull(value,'0,00')),2) as value
|
|
FROM
|
|
documents
|
|
WHERE
|
|
deleted=0 AND
|
|
category_id IN (".$querycategory.") AND
|
|
date_entered >= STR_TO_DATE('".$date_from."','%d.%m.%Y') AND
|
|
date_entered <= STR_TO_DATE('".$date_to."','%d.%m.%Y')
|
|
group by category_id";
|
|
$rows = $db->query ( $query );
|
|
|
|
if($rows->num_rows == 0){
|
|
// Jeśli baza nie zwróci żadnych rekordów to ustawia puste pola
|
|
foreach($selectedColumns as $wartosc){
|
|
$suma[] = '0,00';
|
|
}
|
|
// Dodatkowa kolumna na sumę
|
|
$suma[] = '0,00';
|
|
}else{
|
|
$tmp2 = array();
|
|
$sumka = 0;
|
|
// Rekordy z bazy danych wpisujemy do tymczasowej tabeli by móc wstawić dane w odpowiednie miejsce
|
|
while ( $r = $db->fetchByAssoc ( $rows ) ) {
|
|
$row = array ();
|
|
$row['category_id'] = $r['category_id'];
|
|
$row['value'] = number_format($r['value'], 2, ",", ".");
|
|
$sumka +=$r['value'];
|
|
$tmp2[] = $row;
|
|
}
|
|
// tutaj wstawiamy w odpowiednie miejsce
|
|
foreach($selectedColumns as $wartosc){
|
|
$sprawdzamy = false;
|
|
foreach($tmp2 as $cos){
|
|
if($wartosc==$cos['category_id']){
|
|
$sprawdzamy = true;
|
|
$suma[] = $cos['value'];
|
|
}
|
|
}
|
|
if(!$sprawdzamy){
|
|
$suma[] = '0,00';
|
|
}
|
|
}
|
|
// dodajemy ostatni wiersz sumy
|
|
$suma[]=number_format($sumka, 2, ",", ".");
|
|
}
|
|
|
|
/**
|
|
* ******************** SMARTY **********************
|
|
*/
|
|
$smarty = new Sugar_Smarty ();
|
|
global $mod_strings;
|
|
$smarty -> assign('MOD', $mod_strings);
|
|
$smarty -> assign('data', $data);
|
|
$smarty -> assign('date_from', $date_from);
|
|
$smarty -> assign('suma', $suma);
|
|
$smarty -> assign('date_to', $date_to );
|
|
$smarty -> assign('dateFormat', $Calendar_daFormat);
|
|
$smarty -> assign('selectedColumns', $selectedColumns);
|
|
$smarty -> assign('category', $app_list_strings['document_category_dom']);
|
|
echo $smarty->display ( 'modules/EcmReports/tpls/ReportValue.tpl' );
|
|
?>
|