<?php
/*=======================================================================
 * Lieferungen (Nur normale Lieferordern ohne Sams{Lieferung; Abholung; Austausch}) mit folgenden Spalten: 
 * Auftragsnummer/Oderdatum/Lieferdatum/Warenwert/Lieferpreis Brutto 
 * 
 * Küchen (Nur Küchenkomplettmontage ohne Sams {Nachmontage; Lieferung; Abholung; Austausch}) mit folgenden Spalten 
 * Auftragsnummer/Orderdatum/Lieferdatum/Anzahl Küchenmeter/Preis (inkl. Anfahrtspauschale)
  =======================================================================*/

// error_reporting(E_ALL ^ E_NOTICE ^ E_DEPRECATED);

include_once ("../include/mcglobal.inc.php");
include_once ("../include/auth.inc.php");
include_once ("../include/inc_file.inc.php");
include_once ("../include/inc_dbfields2array.inc.php");
include_once ("../include/html.inc.php");


// Check HTTP-Parameters
getSecHttpVars("1",array("f_act", "hqName", "statMode", "yearMonth", "sYear", "sMonth"));


function outputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, $filename, $postParserField, $postParserFunction) {
  global $db, $PHP_SELF;
	global $f_secretFileName, $statusMessage, $stkIdRoot;

  // Remove old files
  foreach (glob("../temp/download/*.stat_01.csv") as $fileNameToBoRemoved) {
      if ((time() - filemtime($fileNameToBoRemoved)) > 60) :
         if (file_exists($fileNameToBoRemoved)) :
            unlink($fileNameToBoRemoved);
         endif;
      endif;
  }

  $f_secretFileName = "";

  $result = $db->query($sqlStmt);     
  if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());

  // Define output parameters
  $delimiter = ';';
  $writeHeader = "YES";
  $mode = "1";

  include_once ("../include/inc_list_defineoutput2file.inc.php");

  $outputLinesArrayLen = count($outputLinesArray);
  if ($outputLinesArrayLen > 0) :

     if ($filename == "") : $filename = "HTM"; endif;
     $f_fileName = $filename . "_" . date("YmdHis");

     // $f_secretFileName = md5($f_fileName) . ".stat_htm_01.csv";
     $f_secretFileName = $f_fileName . ".stat_01.csv";

     if ($outputHeader != "") :
        writeToFile("../temp/download/" . $f_secretFileName, $outputHeader);   // write header if requested
     endif;

     for ($i = 0; $i < $outputLinesArrayLen; $i++) :
         // Post parsing if necessary
         if ($postParserField != "") :
            if ($postParserFunction == "postParseXXXX") :
               // $outputLinesArray[$i] = postParseXXXX($outputLinesArray[$i]);
            elseif ($postParserFunction == "postParseYYYY") :
               // ....
            endif;
         endif;

         writeToFile("../temp/download/" . $f_secretFileName, $outputLinesArray[$i]);   // write statistic data
     endfor;
  else :
     $statusMessage = "Es wurden keine Daten gefunden! Eine Datei wurde deshalb nicht generiert!";
     $f_stkat_export = false;
  endif;
}


function showData ($statMode, $hqId, $yearMonth, $whereClause = "", $orderByClause = "") {
  global $db, $PHP_SELF;
  global $statusMessage, $statName, $fromDate, $toDate, $hqName;
  $retOut = "";
  if (($statMode == "0" || $statMode == "1") && $hqId != "" && is_numeric($hqId)) :
  
      if ($yearMonth != "") :
         $statYear = substr($yearMonth,0,4);
         $statMonth = substr($yearMonth,4);
      else :
         $statYear = getDateTime("year");
         $statMonth = getDateTime("lastmonth");
         if ($statMonth == "12") : $statYear = $statYear -1; endif;
      endif;
      $calMonthDays = array("31","28","31","30","31","30","31","31","30","31","30","31");
      $calCurrentYearIsLeapYear = date("L", mktime(0, 0, 0, $statMonth, 1, $statYear));
      if ($calCurrentYearIsLeapYear == "1") : $calMonthDays[1] = "29"; endif;

      $fromDate = $statYear . "-" . $statMonth . "-" . "01" . " 00:00:00";
      $toDate   = $statYear . "-" . $statMonth . "-" . $calMonthDays[($statMonth - 1)] . "-" . " 23:59:59";

      if ($statMode == "0") :
         $statName .= " LIEFERUNGEN " . $hqName;

         // SELECT CONCAT(cs.cs_eid,';',jb.jb_id,';',LEFT(jb.jb_createtime,10),';',LEFT(jb.jb_ordertime,10),';',IFNULL(SUM(trat.trat_price),'0'),';',ROUND((jb.jb_totalprice * 1.19),2)) AS output
         $sqlStmt = "
            SELECT cs.cs_eid,jb.jb_id,LEFT(jb.jb_createtime,10) AS jb_createtime,LEFT(jb.jb_ordertime,10) AS jb_ordertime,REPLACE(IFNULL(SUM(trat.trat_price),'0'),'.',',') AS trat_price,REPLACE(ROUND((jb.jb_totalprice * 1.19),2),'.',',') AS jb_totalprice
              FROM
                phoenix.job AS jb 
                  LEFT JOIN phoenix.tourarticle AS trat ON trat.jb_id = jb.jb_id AND trat.tr_sort = '2',
                phoenix.costcenter AS csc,
                phoenix.customer AS cs
              WHERE
                jb.hq_id = '" . $hqId . "'                  AND
                jb.jb_createtime >= '" . $fromDate . "'     AND
                jb.jb_createtime <= '" . $toDate . "'       AND
                (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1') AND
                (jb.jb_service & 1 = jb.jb_service)         AND
                jb.csc_id_related = csc.csc_id              AND
                csc.cs_id = cs.cs_id                        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'))
              GROUP BY jb.jb_id
              ORDER BY jb.jb_id";

         $titleArray = array(getLngt("Kundennr.&nbsp;"),getLngt("Auftrag&nbsp;"), getLngt("Erstellungszeitpunkt&nbsp;"), getLngt("Auftragszeit&nbsp;"), getLngt("Warenwert&nbsp;"), getLngt("Bruttoauftragswert&nbsp;"));
         $aligns = "l,l,l,l,r,r";
         $fieldArray = array("cs_eid", "jb_id", "jb_createtime", "jb_ordertime", "trat_price", "jb_totalprice");
         $postParserField = "";
         $alignArray = spliti(",",$aligns);
         $alignTitles = "left";
         $widths = "100";
         $widthArray = spliti(",",$widths);
         $summationField = "jb_totalprice";
         $mode = "1";             // Output from DB-RESULT
         // $javaScriptFunctionNameForSort = "finishPageXXXX";
         $sortDBField = "YES";    // Used in following include-file for sorting per column;
         $fieldSortArray = array("YES","YES","YES","YES","YES","YES","YES");

      elseif ($statMode == "1") :
         $statName .= " KÜCHENMONTAGEN " . $hqName;

         // SELECT CONCAT(cs.cs_eid,';',jb.jb_id,';',LEFT(jb.jb_createtime,10),';',LEFT(jb.jb_ordertime,10),';',IFNULL(jbc.jbc_amount,'0'),';',ROUND((jb.jb_subtotalprice * 1.19),2)) AS output
         $sqlStmt = "
            SELECT cs.cs_eid,jb.jb_id,LEFT(jb.jb_createtime,10) AS jb_createtime,LEFT(jb.jb_ordertime,10) AS jb_ordertime,REPLACE(IFNULL(jbc.jbc_amount,'0'),'.',',') AS jbc_amount,REPLACE(ROUND((jb.jb_subtotalprice * 1.19),2),'.',',') AS jb_subtotalprice
              FROM
                phoenix.costcenter AS csc,
                phoenix.customer AS cs,
                phoenix.job AS jb LEFT JOIN phoenix.jobcalculator AS jbc ON jb.jb_id = jbc.jb_id AND jbc.srvt_name = '0010:par_01'
              WHERE
                jb.hq_id = '" . $hqId . "'                 AND
                jb.jb_createtime >= '" . $fromDate . "'    AND
                jb.jb_createtime <= '" . $toDate . "'      AND
                (isnull(jb.jb_storno) OR jb.jb_storno = '0' OR jb.jb_storno = '1') AND
                (jb.jb_service & 18 = jb.jb_service)       AND
                jb.csc_id_related = csc.csc_id             AND
                csc.cs_id = cs.cs_id                       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'))
              GROUP BY jb.jb_id
              ORDER BY cs.cs_eid, jb.jb_id";

         $titleArray = array(getLngt("Kundennr.&nbsp;"),getLngt("Auftrag&nbsp;"), getLngt("Erstellungszeitpunkt&nbsp;"), getLngt("Auftragszeit&nbsp;"), getLngt("Anzahl&nbsp;Küchenmeter&nbsp;"), getLngt("Preis&nbsp;(inkl.&nbsp;Anfahrtspauschale)&nbsp;"));
         $aligns = "l,l,l,l,r,r";
         $fieldArray = array("cs_eid", "jb_id", "jb_createtime", "jb_ordertime", "jbc_amount", "jb_subtotalprice");
         $postParserField = "";
         $alignArray = spliti(",",$aligns);
         $alignTitles = "left";
         $widths = "100";
         $widthArray = spliti(",",$widths);
         $summationField = "jb_subtotalprice";
         $mode = "1";             // Output from DB-RESULT
         // $javaScriptFunctionNameForSort = "finishPageXXXX";
         $sortDBField = "YES";    // Used in following include-file for sorting per column;
         $fieldSortArray = array("","","","","","","");
      endif;

      $result = $db->query($sqlStmt);     
      if (DB::isError($result)) die ("$PHP_SELF: " . $result->getMessage());

      include ("../include/inc_list_defineoutput.inc.php");
      // Post parsing if necessary
      if ($postParserField != "") :
         $tableBody = postParseXXXX($tableBody);
      endif;

      $retOut .= "<table>\n";
      $retOut .= $tableHeader . $tableBody;
      $retOut .= "</table>\n";

      $result->free();
      
      $numOfStocks = $rowCounter;
      $numOfPlaces = $sumOfSummationField;
      
      // Optional output to file
      if (true) :
         outputCSV($sqlStmt, $titleArray, $fieldArray, $aligns, str_replace(" ","_",$statName), $postParserField, "");
      endif;
  endif;
  return $retOut;
}


// Initialize date-ranges to the current date
if ($sMonth == "") : $sMonth = getDateTime("lastmonth"); endif;
if ($sYear == "") : $sYear = getDateTime("year"); endif;
if ($sMonth == "12") : $sYear--; endif;
$yearMonth = $sYear . $sMonth;

$statName = "STATISTIK";

$hqId = "";
if ($hqName == "HTHH") : $hqId = "1020"; endif;
if ($hqName == "WAST" || $hqName == "") : $hqId = "1111"; endif;

if (!isset($statMode)) : $statMode = "0"; endif;
if ($statMode != "0" && $statMode != "1") : $statMode = "0"; endif;

$out = "";
if ($f_act == "execute") :
   $out = showData($statMode, $hqId, $yearMonth, "", "");
endif;
?>

<html>

<head>
<title><?php echo $statName ?></title>

<link rel="stylesheet" type="text/css" href="../css/phoenix.css">
<script src="../include/checkFormTags.js" type="text/javascript"></script>

<script type="text/javascript">
<!--
function execBodyOnLoad() {
  // displayStatusMessage();
  <?php if ($f_act == "execute") : echo "downloadStatisticFile();\n"; endif;  ?>
};

function finishPage() {
  document.forms[0].f_act.value = 'execute';
  document.forms[0].submit();
}

function downloadStatisticFile() {
  var widthPopupWin = 850;
  var heightPopupWin = 600;
  var leftPopupWin = (screen.width / 2) - (widthPopupWin / 2) - 12;
  var topPopupWin = (screen.height / 2) - (heightPopupWin / 2) - 50;
  var popupWin;
  popupWin = window.open("../include/data_download.php?f_path=../temp/download/&f_fileName=<?php echo $f_secretFileName ?>", "","dependent=yes,width=" + widthPopupWin + ",height=" + heightPopupWin +",left=" + leftPopupWin + ",top=" + topPopupWin + ",scrollbars=yes");
};

-->
</script>

</head>

<body onLoad="execBodyOnLoad();">

<form name="statistic_HTM_01" action="../tools/statistic_HTM_01.php" method="post">

<input type="hidden" name="f_act" value="">

<table>
  <tr>
    <td style="margin:10px; padding:10px;">
      <h2><?php echo $statName ?></h2><br>
      <h3><?php // echo $fromDate . " - " . $toDate ?></h3>
      <h3>
      <select name="hqName">
        <option value="HTHH" <?php echo ($hqName == "HTHH" ? "selected" : "") ?>>HTHH</option>
        <option value="WAST" <?php echo ($hqName == "WAST" ? "selected" : "") ?>>WAST</option>
      </select>&nbsp;&nbsp;
      <select name="statMode">
        <option value="0" <?php echo ($statMode == "0" ? "selected" : "") ?>>Lieferungen</option>
        <option value="1" <?php echo ($statMode == "1" ? "selected" : "") ?>>Küchenmontagen</option>
      </select>&nbsp;&nbsp;
      <select name="sYear">
        <option value="2014" <?php echo ($sYear == "2014" ? "selected" : "") ?>>2014</option>
        <option value="2015" <?php echo ($sYear == "2015" ? "selected" : "") ?>>2015</option>
        <option value="2016" <?php echo ($sYear == "2016" ? "selected" : "") ?>>2016</option>
      </select>&nbsp;&nbsp;
      <select name="sMonth">
        <option value="1" <?php echo ($sMonth == "1" ? "selected" : "") ?>>Januar</option>
        <option value="2" <?php echo ($sMonth == "2" ? "selected" : "") ?>>Februar</option>
        <option value="3" <?php echo ($sMonth == "3" ? "selected" : "") ?>>März</option>
        <option value="4" <?php echo ($sMonth == "4" ? "selected" : "") ?>>April</option>
        <option value="5" <?php echo ($sMonth == "5" ? "selected" : "") ?>>Mai</option>
        <option value="6" <?php echo ($sMonth == "6" ? "selected" : "") ?>>Juni</option>
        <option value="7" <?php echo ($sMonth == "7" ? "selected" : "") ?>>Juli</option>
        <option value="8" <?php echo ($sMonth == "8" ? "selected" : "") ?>>August</option>
        <option value="9" <?php echo ($sMonth == "9" ? "selected" : "") ?>>September</option>
        <option value="10" <?php echo ($sMonth == "10" ? "selected" : "") ?>>Oktober</option>
        <option value="11" <?php echo ($sMonth == "11" ? "selected" : "") ?>>November</option>
        <option value="12" <?php echo ($sMonth == "12" ? "selected" : "") ?>>Dezember</option>
      </select>&nbsp;&nbsp;
      <input type="button" value="Berechnen" onClick="javascript:finishPage();">
      </h3>
      
      <h6><?php echo $out ?></h6>
    </td>
  </tr>
</table>

</form>

</body>

</html>