158 lines
7.3 KiB
PHP
158 lines
7.3 KiB
PHP
<?php
|
||
// accrual accounting = Abgrenzung
|
||
|
||
error_reporting(E_ALL ^ (E_DEPRECATED | E_NOTICE));
|
||
set_time_limit (0);
|
||
include_once ("../include/inc_mtf_func.inc.php");
|
||
include_once("../include/email/htmlMimeMail.php");
|
||
include_once("../include/calendar.inc.php");
|
||
|
||
$temp_csv_folder = "../temp/edifact/";
|
||
|
||
$serviceLief = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'LI'");
|
||
$serviceMont = $db->getOne("SELECT mt_sort FROM metatype WHERE mt_type = 'service' AND mt_mapped_value = 'MO'");
|
||
|
||
//$from_date_month_timestamp = mktime(0, 0, 0, 1, 12, 2015);
|
||
//$to___date_month_timestamp = mktime(0, 0, 0, 1, 31, 2015);
|
||
//$from_date_month_timestamp = mktime(0, 0, 0, 2, 1, 2015);
|
||
//$to___date_month_timestamp = mktime(0, 0, 0, 2, 28, 2015);
|
||
$from_date_month_timestamp = mktime(0, 0, 0, date("m") - 1, 1, date("Y"));
|
||
$to___date_month_timestamp = mktime(0, 0, 0, date("m"), 0, date("Y"));
|
||
$from_date_month_sql = date("Y-m-d 00:00:00", $from_date_month_timestamp);
|
||
$to___date_month_sql = date("Y-m-d 23:59:59", $to___date_month_timestamp);
|
||
|
||
foreach (array(1 => "Schnelsen" , 2 => "Moorfleet", 17051 => "Altona") as $csc_id_related => $marketname) {
|
||
$sql_query =
|
||
"SELECT jb.jb_id, LEFT(jb_createtime, 10) AS jb_createdate, LEFT(jb_ordertime, 10) AS jb_orderdate, SUM(trat_weight) as sum_trat_weight, SUM(trat_price) as sum_trat_price, COUNT(gdc_content) as cnt_packet, jb_service" .
|
||
" FROM job AS jb, jobpayment AS jbp LEFT JOIN tourarticle AS trat ON jb.jb_id = trat.jb_id LEFT JOIN genericdatacontainer AS gdc ON trat_id = gdc_obj_id AND gdc_gen_fieldname = 'packet'" .
|
||
" WHERE jb_status = 2 AND jb_offer = 0 AND (jb_storno IS NULL OR jb_storno = 0) AND jb_service IN (1, 6, 18)" .
|
||
" AND csc_id_related = " . $csc_id_related . " AND jb_ordertime > '$from_date_month_sql' AND jb_ordertime < '$to___date_month_sql'" .
|
||
" AND jb.jb_id = jbp.jb_id AND jbp_id != 0" .
|
||
" GROUP BY jb.jb_id ORDER BY jb_orderdate";
|
||
|
||
//echo $sql_query . "\n"; //die();
|
||
$csv_contents =
|
||
"Auftragsnr.;Eingangsdatum;Lieferdatum;Gewicht;Warenwert;taggleich;Tag1;Andere;Paketfahrscheine\n";
|
||
$csv_contents1 = "";
|
||
$csv_contents6 = "";
|
||
$csv_contents18 = "";
|
||
$csv_contents_tourarticle = "";
|
||
$sum_taggleich = 0;
|
||
$sum_tag1 = 0;
|
||
$sum_andere = 0;
|
||
$sum_cnt_packet = 0;
|
||
$cnt6 = 0;
|
||
$cnt18 = 0;
|
||
$tourarticles = array();
|
||
$res = $db->query($sql_query);
|
||
if (DB::isError($res)) reportDie ("$PHP_SELF: '$sqlq_uery': " . $res->getMessage());
|
||
while ($row = $res->fetch_assoc()):
|
||
if (
|
||
$db->getOne("SELECT gdc_gen_fieldname FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_obj_id = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'EKS'") != "EKS"
|
||
&& $db->getOne("SELECT gdc_content FROM genericdatacontainer WHERE gdc_obj_type = 'jb' AND gdc_content = " . $row["jb_id"] . " AND gdc_gen_fieldname = 'jb_id_addmont'") != $row["jb_id"]):
|
||
//echo $row["jb_id"] . " ";
|
||
if ($row["jb_service"] == 1):
|
||
$diff_days = diff_workdays($row["jb_createdate"], $row["jb_orderdate"]);
|
||
if ($row["sum_trat_weight"] == "")
|
||
$row["sum_trat_weight"] = "0";
|
||
if ($row["sum_trat_price"] == "")
|
||
$row["sum_trat_price"] = "0";
|
||
$csv_contents1 .=
|
||
$row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";" . $row["sum_trat_weight"] . ";" . $row["sum_trat_price"] . ";" . ($diff_days == 0 ? "1" : "") . ";" . ($diff_days == 1 ? "1" : "") . ";" . ($diff_days > 1 ? "1" : "") . ";" . ($row["cnt_packet"] != 0 ? $row["cnt_packet"] : "") . "\n";
|
||
$sum_taggleich += ($diff_days == 0 ? 1 : 0);
|
||
$sum_tag1 += ($diff_days == 1 ? 1 : 0);
|
||
$sum_andere += ($diff_days > 1 ? 1 : 0);
|
||
$sum_cnt_packet += $row["cnt_packet"];
|
||
|
||
// beliebteste Tourartikel
|
||
$sql_query = "SELECT trat_quantity, trat_serialno, trat_remark FROM tourarticle WHERE jb_id = " . $row["jb_id"];
|
||
$res1 = $db->query($sql_query);
|
||
if (DB::isError($res1)) reportDie ("$PHP_SELF: '$sql_query': " . $res1->getMessage());
|
||
while ($row1 = $res1->fetch_assoc()):
|
||
if (trim($row1["trat_serialno"]) != "" && $row1["trat_serialno"] != "0" && trim($row1["trat_remark"]) != "" && strtoupper(trim($row1["trat_remark"])) == trim($row1["trat_remark"]) && substr(strtoupper(trim($row1["trat_remark"])), 0, 4) != "EKS " && substr(strtoupper(trim($row1["trat_remark"])), 0, 3) != "TA ") {
|
||
$trat_serialno = " " . $row1["trat_serialno"];
|
||
if (!isset($tourarticles[$trat_serialno])) {
|
||
$tourarticles[$trat_serialno] = array($row1["trat_remark"], $row1["trat_quantity"]);
|
||
} else {
|
||
$tourarticles[$trat_serialno][1] += $row1["trat_quantity"];
|
||
}
|
||
}
|
||
endwhile;
|
||
$res1->free();
|
||
|
||
elseif ($row["jb_service"] == 6):
|
||
$csv_contents6 .=
|
||
$row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";;;;;;\n";
|
||
$cnt6++;
|
||
elseif ($row["jb_service"] == 18):
|
||
$csv_contents18 .=
|
||
$row["jb_id"] . ";" . $row["jb_createdate"] . ";" . $row["jb_orderdate"] . ";;;;;;\n";
|
||
$cnt18++;
|
||
endif;
|
||
endif;
|
||
endwhile;
|
||
$res->free();
|
||
|
||
//print_r($tourarticles);
|
||
$tourarticlesSort = array();
|
||
foreach($tourarticles as $trat_serialno => $tourarticle) {
|
||
$tourarticlesSort[] = array($trat_serialno, $tourarticle[0], $tourarticle[1]);
|
||
}
|
||
usort ($tourarticlesSort, "cmp_usort");
|
||
//print_r($tourarticlesSort);
|
||
|
||
while (list($idx, $tourarticle) = each($tourarticlesSort)) {
|
||
$csv_contents_tourarticle .= $idx + 1 . ";" . $tourarticle[0] . ";" . $tourarticle[1] . ";;;" . $tourarticle[2] . ";;;\n";
|
||
if ($idx >= 999)
|
||
break;
|
||
}
|
||
|
||
$csv_contents .=
|
||
$csv_contents1 .
|
||
"Gesamt;;;;;" . $sum_taggleich . ";" . $sum_tag1 . ";" . $sum_andere . ";" . $sum_cnt_packet . "\n" .
|
||
";;;;;;;;\n" .
|
||
"Beliebteste;;Artikel:;;;;;;\n" .
|
||
$csv_contents_tourarticle .
|
||
";;;;;;;;\n" .
|
||
// $csv_contents6 .
|
||
"Gesamt;Möbelmontagen:;;" . $cnt6 . ";;;;;\n" .
|
||
";;;;;;;;\n" .
|
||
$csv_contents18 .
|
||
"Gesamt;Küchenmontagen:;;" . $cnt18 . ";;;;;\n";
|
||
|
||
$csv_file_name = substr($to___date_month_sql, 0, 10) . "_" . $marketname . ".csv";
|
||
$fileHandle = @fopen($temp_csv_folder . $csv_file_name, 'w');
|
||
@fwrite($fileHandle, $csv_contents);
|
||
@fclose($fileHandle);
|
||
|
||
$xls_file_name = substr($to___date_month_sql, 0, 10) . "_" . $marketname . ".xls";
|
||
$cmd = "../tools/mycsv2xls.pl " . $temp_csv_folder . $csv_file_name . " '' " . $temp_csv_folder . $xls_file_name;
|
||
@unlink($temp_csv_folder . $xls_file_name);
|
||
$ausgabe = exec($cmd);
|
||
}
|
||
|
||
function diff_workdays($date1, $date2) {
|
||
$diff_days = 0;
|
||
|
||
$date1_timestamp = mktime(0, 0, 0, substr($date1, 5, 2), substr($date1, 8, 2), substr($date1, 0, 4));
|
||
$date2_timestamp = mktime(0, 0, 0, substr($date2, 5, 2), substr($date2, 8, 2), substr($date2, 0, 4));
|
||
//echo $date1 . " (" . date("D", $date1_timestamp) . ") -> ". $date2 . " (" . date("D", $date2_timestamp) . "): ";
|
||
|
||
while ($date2_timestamp > $date1_timestamp) {
|
||
if (!feiertag(date("Y", $date1_timestamp), date("n", $date1_timestamp), date("j", $date1_timestamp)) || $diff_days == 0) {
|
||
$diff_days++;
|
||
}
|
||
$date1_timestamp += 3600 * 24;
|
||
}
|
||
//echo $diff_days . "\n";
|
||
|
||
return $diff_days;
|
||
}
|
||
|
||
function cmp_usort ($a, $b) {
|
||
if ($a[2] != $b[2])
|
||
return ($a[2] > $b[2] ? -1 : 1);
|
||
return ($a[1] < $b[1] ? -1 : 1);
|
||
}
|
||
|
||
?>
|