$cspan = 4;
} else {
$cspan = 2;
}
if ($row["osasto"] != $edosasto and $lask > 1) {
if ($osmyynti > 0) {
$ospuutepros = round($ospuute / ($ospuute + $osmyynti) * 100, 2);
} elseif ($ospuute > 0) {
$ospuutepros = 100;
} else {
$ospuutepros = 0;
}
echo "<tr>\n <th colspan='{$cspan}'>" . t("Osasto") . " {$edosasto} " . t("yhteensä") . ":</th>\n <th style='text-align:right'>" . sprintf("%.2f", $ospuutekpl) . "</th>\n <th style='text-align:right'>" . sprintf("%.2f", $ospuute) . "</th>\n <th style='text-align:right'>" . sprintf("%.2f", $osmyynti) . "</th>\n <th style='text-align:right'>" . sprintf("%.2f", $ospuutepros) . "</th>\n </tr>";
$worksheet->writeString($excelrivi, $excelsarake, t("Osasto") . " {$edosasto} " . t("yhteensä") . ":");
$excelsarake += $cspan;
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.2f", $ospuutekpl));
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.2f", $ospuute));
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.2f", $osmyynti));
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.2f", $ospuutepros));
$excelsarake = 0;
$excelrivi++;
$ospuute = 0;
$ospuutekpl = 0;
$osmyynti = 0;
$ospuutepros = 0;
}
$ospuute += $row["puuteeur"];
$osmyynti += $row["myyeur"];
include 'inc/pupeExcel.inc';
$worksheet = new pupeExcel();
$format_bold = array("bold" => TRUE);
$excelrivi = 0;
$sarakemaara = mysql_num_fields($result);
for ($i = 0; $i < $sarakemaara; $i++) {
$worksheet->write($excelrivi, $i, ucfirst(t(mysql_field_name($result, $i))), $format_bold);
}
$excelrivi++;
$bar = new ProgressBar();
$bar->initialize(mysql_num_rows($result));
while ($row = mysql_fetch_row($result)) {
$bar->increase();
for ($i = 0; $i < $sarakemaara; $i++) {
if (mysql_field_type($result, $i) == 'real') {
$worksheet->writeNumber($excelrivi, $i, sprintf("%.02f", $row[$i]));
} else {
$worksheet->writeString($excelrivi, $i, $row[$i]);
}
}
$excelrivi++;
}
$excelnimi = $worksheet->close();
echo "<br><br><table>";
echo "<tr><th>" . t("Tallenna Excel") . ":</th><td class='back'>";
echo "<form method='post' class='multisubmit'>";
echo "<input type='hidden' name='toim' value='{$toim}'>";
echo "<input type='hidden' name='tee' value='lataa_tiedosto'>";
echo "<input type='hidden' name='kaunisnimi' value='SQLhaku.xlsx'>";
echo "<input type='hidden' name='tmpfilenimi' value='{$excelnimi}'>";
echo "<input type='submit' value='" . t("Tallenna") . "'></form></td></tr>";
$ekk = round($epakurtasot_array["50%"] / (365 / 12), 1);
} elseif ($mikataso == 25) {
$ekk = round($epakurtasot_array["25%"] / (365 / 12), 1);
}
if (!$php_cli) {
echo "<td>" . t("Yli %s kk sitten", "", $ekk) . "</td>";
}
$worksheet->writeString($excelrivi, $excelsarake++, t("Yli %s kk sitten", "", $ekk));
if (!$php_cli) {
echo "<td align='right'>{$mikataso}%</td>";
}
$worksheet->writeString($excelrivi, $excelsarake++, $mikataso . "%");
if (!$php_cli) {
echo "<td align='right'>{$epakurantti_row['saldo']}</td>";
}
$worksheet->writeNumber($excelrivi, $excelsarake++, $epakurantti_row['saldo']);
if (!$php_cli) {
echo "<td align='right'>" . round($epakurantti_row['kehahin'], 2) . "</td>";
}
$worksheet->writeNumber($excelrivi, $excelsarake++, round($epakurantti_row['kehahin'], 2));
$vararvo_nyt = $vararvo_sit = round($epakurantti_row['kehahin'] * $epakurantti_row['saldo'], 2);
if (!$php_cli) {
echo "<td align='right'>{$vararvo_nyt}</td>";
}
$worksheet->writeNumber($excelrivi, $excelsarake++, $vararvo_nyt);
if ($tee != "" or $ajo_tee == "NAYTA" or $ajo_tee == "NAYTAPV") {
if ($mikataso == 100) {
$vararvo_sit = 0;
} elseif ($mikataso == 75) {
$vararvo_sit = round($epakurantti_row['bruttokehahin'] * 0.25 * $epakurantti_row['saldo'], 2);
} elseif ($mikataso == 50) {
function generoi_custom_excel($valmistukset, $valmistuslinjat, $esitysmuoto)
{
global $kukarow, $yhtiorow;
if (count($valmistukset) == 0) {
return false;
}
$xls_progress_bar = new ProgressBar(t("Tallennetaan exceliin"));
$xls_progress_bar->initialize(count($valmistukset));
$xls = new pupeExcel();
$rivi = 0;
$sarake = 0;
$valmistus_headerit = array('tuoteno' => t('Valmisteen tuoteno'), 'nimitys' => t('Valmisteen nimitys'), 'lasku_tunnus' => t('Valmistusnumero'), 'yksikko' => t('Valmistuslinja'), 'valmistettava_kpl' => t('Valmistetaan kpl'), 'ostohinta' => t('Valmistuksen tila'), 'kerayspvm' => t('Keräyspäivä'), 'toimaika' => t('Valmistuspäivä'));
$raaka_aine_headerit = array('tuoteno' => t('Raaka-Aineen Tuoteno'), 'nimitys' => t('Raaka-Aineen Nimitys'), 'kappalemaara' => t('Valmistusnumero'), 'yksikko' => t('Saldo'), 'paivitys_pvm' => t('Hyllyssä'), 'ostohinta' => t('Myytävissä'), 'kehahin' => t('Tilattu'), 'ryhman_ale' => t('Toimittaja'));
$tulostettu = false;
foreach ($valmistukset as $valmistus) {
foreach ($valmistus['tilausrivit'] as $tilausrivi) {
if ($esitysmuoto == 'A') {
foreach ($valmistus_headerit as $valmistus_header) {
$xls->write($rivi, $sarake, $valmistus_header, array('bold' => true));
$sarake++;
}
$sarake = 0;
$rivi++;
$xls->write($rivi, $sarake, $tilausrivi['tuoteno']);
$sarake++;
$xls->write($rivi, $sarake, $tilausrivi['nimitys']);
$sarake++;
$xls->write($rivi, $sarake, $tilausrivi['lasku_tunnus']);
$sarake++;
$valmistuslinja = search_array_key_for_value_recursive($valmistuslinjat, 'selite', $tilausrivi['valmistuslinja']);
$valmistuslinja = isset($valmistuslinja[0]['selitetark']) ? $valmistuslinja[0]['selitetark'] : '';
if (empty($valmistuslinja)) {
$xls->write($rivi, $sarake, t('Ei valmistuslinjaa'));
$sarake++;
} else {
$xls->write($rivi, $sarake, $valmistuslinja['selitetark']);
$sarake++;
}
$xls->writeNumber($rivi, $sarake, $tilausrivi['valmistettava_kpl']);
$sarake++;
$laskutyyppi = $tilausrivi['tila'];
$alatila = $tilausrivi['alatila'];
require 'inc/laskutyyppi.inc';
$xls->write($rivi, $sarake, $laskutyyppi . ' ' . $alatila);
$sarake++;
$xls->write($rivi, $sarake, date('d.m.Y', strtotime($tilausrivi['kerayspvm'])));
$sarake++;
$xls->write($rivi, $sarake, date('d.m.Y', strtotime($tilausrivi['toimaika'])));
$sarake++;
$rivi = $rivi + 2;
$sarake = 0;
}
if (!$tulostettu and $esitysmuoto == 'B') {
foreach ($raaka_aine_headerit as $raaka_aine_header) {
$xls->write($rivi, $sarake, $raaka_aine_header, array('bold' => true));
$sarake++;
}
$sarake = 0;
$rivi++;
$tulostettu = true;
}
foreach ($tilausrivi['raaka_aineet'] as $raaka_aine) {
$xls->write($rivi, $sarake, $raaka_aine['tuoteno']);
$sarake++;
$xls->write($rivi, $sarake, $raaka_aine['nimitys']);
$sarake++;
$xls->write($rivi, $sarake, $raaka_aine['lasku_tunnus']);
$sarake++;
$xls->writeNumber($rivi, $sarake, $raaka_aine['saldo']);
$sarake++;
$xls->writeNumber($rivi, $sarake, $raaka_aine['hyllyssa']);
$sarake++;
$xls->writeNumber($rivi, $sarake, $raaka_aine['myytavissa']);
$sarake++;
$xls->writeNumber($rivi, $sarake, $raaka_aine['tilattu']);
$sarake++;
$xls->write($rivi, $sarake, $raaka_aine['toimittaja']);
$sarake++;
$rivi++;
$sarake = 0;
}
$xls_progress_bar->increase();
if ($esitysmuoto == 'A') {
$rivi = $rivi + 2;
}
$sarake = 0;
}
}
echo "<br/>";
$xls_tiedosto = $xls->close();
return $xls_tiedosto;
}
} elseif ($vnim == "kateproskumul") {
if ($valisummat["myyntikumul"] != 0) {
$vsum = round($valisummat["katekumul"] / $valisummat["myyntikumul"] * 100, 2);
}
} elseif ($vnim == "kateproskumuled") {
if ($valisummat["myyntikumuled"] != 0) {
$vsum = round($valisummat["katekumuled"] / $valisummat["myyntikumuled"] * 100, 2);
}
} elseif ((string) $vsum != '') {
$vsum = sprintf("%.2f", $vsum);
}
if ($rivimaara <= $rivilimitti) {
echo "<td class='tumma' align='right'>{$vsum}</td>";
}
if (isset($worksheet)) {
$worksheet->writeNumber($excelrivi, $excelsarake++, $vsum);
}
}
$excelsarake = 0;
$excelrivi++;
if ($rivimaara <= $rivilimitti) {
echo "</tr>";
}
$valisummat = array();
}
$edluku = $row[$row_keys[0]];
foreach ($row as $ken_nimi => $kentta) {
// jos kyseessa on tuote
if ($ken_nimi == "tuoteno") {
$koskematon_tuoteno = $row["tuoteno"];
$row[$ken_nimi] = "<a href='#' onclick=\"window.open('{$palvelin2}tuote.php?tee=Z&tuoteno=" . urlencode($row[$ken_nimi]) . "', '_blank' ,'toolbar=0,scrollbars=1,location=0,statusbar=0,menubar=0,resizable=1,left=200,top=100,width=1000,height=800'); return false;\">{$row[$ken_nimi]}</a>";
$vajaasaldot_table .= "<tr class='aktiivi'>";
$vajaasaldot_table .= "<td>{$row['osasto']}</td>";
$vajaasaldot_table .= "<td>{$row['try']}</td>";
$vajaasaldot_table .= "<td><a href='{$palvelin2}tuote.php?tee=Z&tuoteno=" . urlencode($row["tuoteno"]) . "'>{$row['tuoteno']}</a></td>";
$vajaasaldot_table .= "<td>{$row['nimitys']}</td>";
$vajaasaldot_table .= "<td align='right'>{$saldo}</td>";
$vajaasaldot_table .= "<td align='right'>" . ($myytavissa - $jurow["jt"]) . "</td>";
$vajaasaldot_table .= "<td align='right'>{$ostorivi['tulossa']}</td>";
$vajaasaldot_table .= "<td>{$ostorivi['toimaika']}</td>";
$vajaasaldot_table .= "</tr>";
$excelsarake = 0;
$worksheet->writeString($excelrivi, $excelsarake++, $row["osasto"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["try"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["tuoteno"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["nimitys"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $saldo);
$worksheet->writeNumber($excelrivi, $excelsarake++, $myytavissa - $jurow["jt"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $ostorivi["tulossa"]);
$worksheet->writeString($excelrivi, $excelsarake++, $ostorivi["toimaika"]);
$excelrivi++;
$current_row++;
}
}
$vajaasaldot_table .= "</table>";
echo "<br>";
if ($current_row > 0) {
echo "<br>", $vajaasaldot_table;
$excelnimi = $worksheet->close();
echo "<br><br><table>";
echo "<tr><th>" . t("Tallenna tulos") . ":</th>";
echo "<form method='post' class='multisubmit'>";
if ($valitut["SARAKE4CB"] != '') {
$rivi .= "\"" . $ryhmanimet[$row["abcluokka_try"]] . "\"\t";
$worksheet->write($excelrivi, $excelsarake, $ryhmanimet[$row["abcluokka_try"]]);
$excelsarake++;
}
if ($valitut["SARAKE4D"] != '') {
if ($row["luontiaika"] == "0000-00-00 00:00:00") {
$row["luontiaika"] = "";
}
$rivi .= "\"{$row['luontiaika']}\"\t";
$worksheet->writeString($excelrivi, $excelsarake, $row["luontiaika"]);
$excelsarake++;
}
if ($valitut["SARAKE5"] != '') {
$rivi .= str_replace(".", ",", $saldo['saldo']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $saldo["saldo"]);
$excelsarake++;
}
if ($valitut["SARAKE6"] != '') {
$rivi .= str_replace(".", ",", $row['halytysraja']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $row["halytysraja"]);
$excelsarake++;
}
if ($valitut["SARAKE6B"] != '') {
$rivi .= str_replace(".", ",", $row['tilausmaara']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $row["tilausmaara"]);
$excelsarake++;
}
if ($valitut["SARAKE7"] != '') {
$rivi .= str_replace(".", ",", $ennp['tilattu']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $ennp["tilattu"]);
$kate_eur = $row["rivihinta"] * -1 - $ostohinta;
$kate_yht += $kate_eur;
} else {
$kate = "N/A";
}
} elseif ($kukarow['extranet'] == '') {
if ($row["rivihinta"] != 0) {
$kate = sprintf('%.2f', 100 * ($row["rivihinta"] - kehahin($row["tuoteno"]) * ($row["varattu"] + $row["jt"] + $row['määrä'])) / $row["rivihinta"]) . "%";
} elseif (kehahin($row["tuoteno"]) != 0) {
$kate = "-100.00%";
}
$kate_eur = $row["rivihinta"] - kehahin($row["tuoteno"]) * ($row["varattu"] + $row["jt"] + $row['määrä']);
$kate_yht += $kate_eur;
}
$row[$i] = $kate;
$worksheet->writeNumber($excelrivi, $excelsarake, $kate_eur, $format_num);
echo "<{$ero} align='right' valign='top' nowrap {$class}>" . sprintf("%.2f", $kate_eur) . "</{$ero}>";
echo "<{$ero} align='right' valign='top' nowrap {$class}>{$kate}</{$ero}>";
}
$excelsarake++;
} elseif (mysql_field_name($result, $i) == 'hinta' or mysql_field_name($result, $i) == 'rivihinta') {
echo "<{$ero} valign='top' align='right' nowrap {$class}>" . sprintf("%.2f", $row[$i]) . "</{$ero}>";
} else {
echo "<{$ero} valign='top' {$class}>{$row[$i]}</td>";
}
if (is_numeric($row[$i]) and mysql_field_name($result, $i) != 'ytunnus') {
$worksheet->writeNumber($excelrivi, $excelsarake, $row[$i]);
} else {
$worksheet->write($excelrivi, $excelsarake, $row[$i]);
}
$excelsarake++;
$varastotilasto_table .= "<td align='right'>{$myyntirivi['myynti3kk']}</td>";
if ($listaustyyppi == "kappaleet2") {
$varastotilasto_table .= "<td align='right'>{$kulutusrivi['kulutusVA']}</td>";
$varastotilasto_table .= "<td align='right'>{$kulutusrivi['kulutus12kk']}</td>";
$varastotilasto_table .= "<td align='right'>{$kulutusrivi['kulutus6kk']}</td>";
$varastotilasto_table .= "<td align='right'>{$kulutusrivi['kulutus3kk']}</td>";
}
$varastotilasto_table .= "</tr>";
}
$excelsarake = 0;
$worksheet->writeString($excelrivi, $excelsarake++, $row["osasto"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["try"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["tuoteno"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["nimitys"]);
$worksheet->writeString($excelrivi, $excelsarake++, $row["eankoodi"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $saldo);
if ($nayta_vapaa_saldo == "on") {
$worksheet->writeNumber($excelrivi, $excelsarake++, $vapaa_saldo);
}
$worksheet->writeNumber($excelrivi, $excelsarake++, $varastonarvo);
$worksheet->writeNumber($excelrivi, $excelsarake++, $row["myyntihinta"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $row["varmuus_varasto"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $ostorivi["tulossa"]);
$worksheet->writeString($excelrivi, $excelsarake++, $ostorivi["toimaika"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $varattu);
$worksheet->writeNumber($excelrivi, $excelsarake++, $myyntirivi["myyntiVA"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $myyntirivi["myynti12kk"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $myyntirivi["myynti6kk"]);
$worksheet->writeNumber($excelrivi, $excelsarake++, $myyntirivi["myynti3kk"]);
if ($listaustyyppi == "kappaleet2") {
$worksheet->writeNumber($excelrivi, $excelsarake++, $kulutusrivi["kulutusVA"]);
$excelsarake = 0;
$worksheet->writeString($excelrivi, $excelsarake++, $prow["isatuoteno"], $style);
if ($tunnus != $prow["tunnus"]) {
echo "<tr class='aktiivi{$del}'>";
echo "<td>{$prow['tuoteno']} {$error}</td>";
echo "<td>" . t_tuotteen_avainsanat($tuoterow, 'nimitys') . "</td>";
$worksheet->writeString($excelrivi, $excelsarake++, $prow["tuoteno"], $style);
$worksheet->writeString($excelrivi, $excelsarake++, $tuoterow["nimitys"], $style);
if ($myos_tuotetiedot_exceliin) {
$worksheet->writeString($excelrivi, $excelsarake++, $tuoterow["malli"], $style);
$worksheet->writeString($excelrivi, $excelsarake++, $tuoterow["mallitarkenne"], $style);
$worksheet->writeString($excelrivi, $excelsarake++, $tuoterow["tuotemerkki"], $style);
}
if ($toim != "LISAVARUSTE") {
echo "<td align='right'>" . (double) $prow["kerroin"] . "</td>";
$worksheet->writeNumber($excelrivi, $excelsarake++, $prow["kerroin"], $style);
}
if ($toim == "PERHE") {
echo "<td align='right'>{$prow['hintakerroin']}</td>";
echo "<td align='right'>{$prow['alekerroin']}</td>";
$worksheet->writeNumber($excelrivi, $excelsarake++, $prow["hintakerroin"], $style);
$worksheet->writeNumber($excelrivi, $excelsarake++, $prow["alekerroin"], $style);
//echo "<td align='right'>$prow[rivikommentti]</td>";
}
if ($toim == "OSALUETTELO") {
echo "<td align='right'>{$prow['hintakerroin']}</td>";
$worksheet->writeNumber($excelrivi, $excelsarake++, $prow["hintakerroin"], $style);
}
if ($toim == "RESEPTI") {
echo "<td align='left'>{$tuoterow['yksikko']}</td>";
$worksheet->writeString($excelrivi, $excelsarake++, $tuoterow["yksikko"], $style);
if ($valitut["SARAKE4CB"] != '') {
$rivi .= "\"" . $ryhmanimet[$row["abcluokka_try"]] . "\"\t";
$worksheet->write($excelrivi, $excelsarake, $ryhmanimet[$row["abcluokka_try"]]);
$excelsarake++;
}
if ($valitut["SARAKE4D"] != '') {
if ($row["luontiaika"] == "0000-00-00 00:00:00") {
$row["luontiaika"] = "";
}
$rivi .= "\"{$row['luontiaika']}\"\t";
$worksheet->writeString($excelrivi, $excelsarake, $row["luontiaika"]);
$excelsarake++;
}
if ($valitut["SARAKE5"] != '') {
$rivi .= str_replace(".", ",", $saldo['saldo']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $saldo["saldo"]);
$excelsarake++;
}
if ($valitut["SARAKE6"] != '') {
$rivi .= str_replace(".", ",", $row['halytysraja']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $row["halytysraja"]);
$excelsarake++;
}
if ($valitut["SARAKE6B"] != '') {
$rivi .= str_replace(".", ",", $row['tilausmaara']) . "\t";
$worksheet->writeNumber($excelrivi, $excelsarake, $row["tilausmaara"]);
$excelsarake++;
}
if ($valitut["SARAKE7"] != '') {
$_tilattu = $ennp['tilattu'] + $ennp['siirrot'];
$rivi .= str_replace(".", ",", $_tilattu) . "\t";
$worksheet->write($excelrivi, $i, t('Try'), $format_bold);
$i++;
$worksheet->write($excelrivi, $i, t('EAN'), $format_bold);
$i = 0;
$excelrivi++;
while ($productrow = mysql_fetch_array($productqueryresult)) {
list(, , $apu_myytavissa) = saldo_myytavissa($productrow["tuoteno"]);
$sresult = t_avainsana("TRY", "", "and avainsana.selite = '{$productrow['try']}'");
$srow = mysql_fetch_array($sresult);
if ($myytavissao == "" or $apu_myytavissa > 0) {
$worksheet->writeString($excelrivi, $i, $productrow['tuoteno']);
$i++;
$worksheet->writeString($excelrivi, $i, $productrow['nimitys']);
$i++;
if ($kehahinnat != "") {
$worksheet->writeNumber($excelrivi, $i, $productrow['kehahin']);
$i++;
}
$worksheet->writeNumber($excelrivi, $i, $productrow['myyntihinta']);
$i++;
$worksheet->writeNumber($excelrivi, $i, $apu_myytavissa);
$i++;
$worksheet->writeString($excelrivi, $i, $productrow["try"]);
$i++;
$worksheet->writeString($excelrivi, $i, $srow["selitetark"]);
$i++;
$worksheet->writeString($excelrivi, $i, $productrow['eankoodi']);
$i = 0;
$excelrivi++;
if ($showprod) {
echo "<tr class='aktiivi'>";
if ($pi == 0) {
$sisennys = 10 + (strlen($arvo) - 1) * 3;
if ($sarakkeet_px[1] != "") {
$nimi = $sarakkeet_px[1] . " - " . $sarakkeet_px[2];
} else {
$nimi = $sarakkeet_px[2];
}
if (isset($teexls) and $teexls == "OK") {
$worksheet->writeString($excelrivi, $pi, $nimi, $format_bold);
}
if (isset($teepdf) and $teepdf == "OK") {
$pdf->draw_text($sisennys, $bottom, $nimi, $firstpage, $b);
}
} elseif ($pi > 2) {
if (isset($teexls) and $teexls == "OK") {
$worksheet->writeNumber($excelrivi, $pi - 2, (double) str_replace(" ", "", str_replace(",", ".", $arvo)));
}
if (isset($teepdf) and $teepdf == "OK") {
$oikpos = $pdf->strlen($arvo, $p);
$pdf->draw_text($leveysarray[$pi - 3] - $oikpos, $bottom, $arvo, $firstpage, $p);
}
}
$pi++;
}
if (isset($teexls) and $teexls == "OK") {
$excelrivi++;
}
if (isset($teepdf) and $teepdf == "OK") {
$bottom -= $rivikork;
}
}
if (isset($_avoimia_yhteensa)) {
$_avoimia_yhteensa = $row["avoimia"] + $avoimettilaukset - $kaatotilisumma;
}
if ($eiliittymaa != 'ON') {
$excelsarake = 0;
if ($grouppaus != "kustannuspaikka") {
$worksheet->writeString($excelrivi, $excelsarake, str_replace("<br>", "\n", $row["ytunnus"]));
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, str_replace("<br>", "\n", $row["nimi"]));
$excelsarake++;
}
if ($grouppaus == "kustannuspaikka" or $tiliointilisa != "") {
$worksheet->writeString($excelrivi, $excelsarake, $kustpmuuttuja);
$excelsarake++;
}
$worksheet->writeNumber($excelrivi, $excelsarake, $row["alle_{$saatavat_array['0']}"]);
$excelsarake++;
for ($sa = 1; $sa < count($saatavat_array); $sa++) {
$worksheet->writeNumber($excelrivi, $excelsarake, $row[$saatavat_array[$sa - 1] + 1 . "_" . $saatavat_array[$sa]]);
$excelsarake++;
}
$worksheet->writeNumber($excelrivi, $excelsarake, $row["yli_{$saatavat_array[count($saatavat_array) - 1]}"]);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $row["avoimia"]);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $avoimettilaukset);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $kaatotilisumma);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $row["avoimia"] + $avoimettilaukset - $kaatotilisumma);
$excelsarake++;
if ($fieldname == "vararvoennen") {
preg_match("/ \\(([0-9\\.\\-]*?)\\) /", $row["selite"], $invkpl);
$row[$fieldname] = round((double) $invkpl[1] * $row[$fieldname], 2);
}
// Jos gruupataan enemmän kuin yksi taso niin tehdään välisumma
if ($gluku > 1 and $edluku != $row[mysql_field_name($result, 0)] and $edluku != 'x' and strpos($group, ',') !== FALSE) {
$excelsarake = $myyntiind = $kateind = $nettokateind = $myykplind = 0;
foreach ($valisummat as $vnim => $vsum) {
if ((string) $vsum != '') {
$vsum = sprintf("%.2f", $vsum);
}
if ($elements <= $rivilimitti) {
echo "<td class='tumma' align='right'>{$vsum}</td>";
}
if (isset($worksheet)) {
$worksheet->writeNumber($excelrivi, $excelsarake, $vsum);
}
$excelsarake++;
}
$excelrivi++;
if ($elements <= $rivilimitti) {
echo "</tr><tr>";
}
$valisummat = array();
}
$edluku = $row[mysql_field_name($result, 0)];
if (is_numeric($row[$fieldname]) and (mysql_field_type($result, $i) == 'real' or mysql_field_type($result, $i) == 'int' or substr($fieldname, 0, 4) == 'kate')) {
if ($elements <= $rivilimitti) {
echo "<td valign='top' align='right'>" . sprintf("%.02f", $row[$fieldname]) . "</td>";
}
if (isset($worksheet)) {
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $rrow["try"]);
$excelsarake++;
}
$worksheet->writeString($excelrivi, $excelsarake, t_tuotteen_avainsanat($rrow, 'nimitys', $hinkieli));
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $rrow["myynti_era"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, t_avainsana("Y", $hinkieli, "and avainsana.selite='{$rrow['yksikko']}'", "", "", "selite"));
$excelsarake++;
if (!$tuoteryhmaosasto) {
$worksheet->writeString($excelrivi, $excelsarake, $rrow["status"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $rrow["aleryhma"]);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $veroton);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $verollinen);
$excelsarake++;
} else {
$worksheet->writeNumber($excelrivi, $excelsarake, $veroton);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $verollinen);
$excelsarake++;
}
for ($alepostfix = 1; $alepostfix <= $yhtiorow['myynnin_alekentat']; $alepostfix++) {
if ($netto != "") {
$worksheet->writeString($excelrivi, $excelsarake, t("Netto", $hinkieli));
$excelsarake++;
} else {
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf('%.2f', ${'ale' . $alepostfix}));
$worksheet->write($excelrivi, 7, t("Ed.Määrä"), $format_bold);
$excelrivi++;
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>{$row['tuoteno']}</td>";
echo "<td>" . t_tuotteen_avainsanat($row, 'nimitys') . "</th>";
echo "<td>" . $row['summa'] . "</th>";
echo "<td>" . $row['kate'] . "</th>";
echo "<td>" . $row['kpl'] . "</th>";
echo "<td>" . $row['edsumma'] . "</th>";
echo "<td>" . $row['edkate'] . "</th>";
echo "<td>" . $row['edkpl'] . "</th>";
echo "</tr>";
$worksheet->write($excelrivi, 0, $row['tuoteno']);
$worksheet->write($excelrivi, 1, t_tuotteen_avainsanat($row, 'nimitys'));
$worksheet->writeNumber($excelrivi, 2, $row['summa']);
$worksheet->writeNumber($excelrivi, 3, $row['kate']);
$worksheet->writeNumber($excelrivi, 4, $row['kpl']);
$worksheet->writeNumber($excelrivi, 5, $row['edsumma']);
$worksheet->writeNumber($excelrivi, 6, $row['edkate']);
$worksheet->writeNumber($excelrivi, 7, $row['edkpl']);
$excelrivi++;
}
echo "</table><br>";
$excelnimi = $worksheet->close();
echo "<br><br><table>";
echo "<tr><th>" . t("Tallenna tulos") . ":</th>";
echo "<form method='post' class='multisubmit'>";
echo "<input type='hidden' name='tee' value='lataa_tiedosto'>";
echo "<input type='hidden' name='kaunisnimi' value='Asiakkaan_ostot.xlsx'>";
echo "<input type='hidden' name='tmpfilenimi' value='{$excelnimi}'>";
$sarake = 0;
$worksheet->writeString($excelrivi, $sarake++, $lrow["ytunnus"]);
$worksheet->write($excelrivi, $sarake++, $lrow["nimi"]);
$worksheet->write($excelrivi, $sarake++, $lrow["nimitark"]);
if ($summaa == '') {
$worksheet->write($excelrivi, $sarake++, $lrow["aleryhma"]);
}
if ($summaa == '') {
for ($alepostfix = 1; $alepostfix <= $yhtiorow['myynnin_alekentat']; $alepostfix++) {
$worksheet->write($excelrivi, $sarake++, ${'ale' . $alepostfix});
}
}
if ($summaa == '') {
$worksheet->write($excelrivi, $sarake++, $lrow["piiri"]);
}
$worksheet->writeNumber($excelrivi, $sarake++, $lrow["kpl"]);
$worksheet->writeNumber($excelrivi, $sarake++, $lrow["summa"]);
$worksheet->writeNumber($excelrivi, $sarake++, $lrow["kate"]);
$worksheet->writeNumber($excelrivi, $sarake++, $katepros);
$excelrivi++;
}
if (mysql_num_rows($result) < 2000) {
echo "</table>";
}
$excelnimi = $worksheet->close();
echo "<br><br><table>";
echo "<tr><th>" . t("Tallenna tulos") . ":</th>";
echo "<form method='post' class='multisubmit'>";
echo "<input type='hidden' name='tee' value='lataa_tiedosto'>";
echo "<input type='hidden' name='kaunisnimi' value='Myyntiasiakkaittain.xlsx'>";
echo "<input type='hidden' name='tmpfilenimi' value='{$excelnimi}'>";
foreach ($tuoterivit as $rivi) {
$rivi = explode("\t", $rivi);
foreach ($rivi as $sarake) {
// Poistetaan hipsut sarakkeesta
$_sarake = trim($sarake, '"');
// Tämä on string jos sarakkeessa oli hipsut tai sarake on tyhjä
$_string = ($_sarake == '' or $_sarake != $sarake);
// Katsotaan onko string date
$_datetime = date('Y-m-d H:i:s', strtotime($_sarake)) == $_sarake;
$_date = date('Y-m-d', strtotime($_sarake)) == $_sarake;
if ($_date or $_datetime) {
$worksheet->writeDate($excelrivi, $excelsarake++, $_sarake);
} elseif ($_string) {
$worksheet->writeString($excelrivi, $excelsarake++, $_sarake);
} else {
$worksheet->writeNumber($excelrivi, $excelsarake++, $_sarake);
}
}
$excelrivi++;
$excelsarake = 0;
$bar->increase();
//calls the bar with every processed element
}
// uniikki filenimi
$excelnimi = $worksheet->close();
echo "<br><br>";
echo "<form method='post' class='multisubmit'>";
echo "<input type='hidden' name='tee' value='lataa_tiedosto'>";
echo "<input type='hidden' name='kaunisnimi' value='ostoehdotus.xlsx'>";
echo "<input type='hidden' name='tmpfilenimi' value='{$excelnimi}'>";
echo "<table>";
echo "<td>{$tulrow['valkoodi']}</td>";
echo "</tr>";
$rivsum += $tulrow["maara"];
$tilsum += $tulrow["tilattu"];
$eursum += $tulrow["arvo"];
if (isset($worksheet)) {
$excelsarake = 0;
$worksheet->writeString($excelrivi, $excelsarake, $tulrow["tunnus"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $tulrow["ytunnus"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $tulrow["nimi"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, tv1dateconv($tulrow["toimaika"]));
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $tulrow["maara"]);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $tulrow["tilattu"]);
$excelsarake++;
$worksheet->writeNumber($excelrivi, $excelsarake, $tulrow["arvo"]);
$excelsarake++;
$worksheet->writeString($excelrivi, $excelsarake, $tulrow["valkoodi"]);
$excelsarake = 0;
$excelrivi++;
}
}
echo "<tr>";
echo "<th colspan='4'>" . t("Yhteensä") . ":</th>";
echo "<td class='tumma' align='right'>{$rivsum}</td>";
echo "<td class='tumma' align='right'>{$tilsum}</td>";
echo "<td class='tumma' align='right'>{$eursum}</td>";
fwrite($fh, pupesoft_csvstring($row["yksikko"]) . "\t");
}
if ($variaatiosummaus != "") {
foreach ($kaikkikoot as $kokonimi => $koko) {
if (isset($koot[$kokonimi])) {
if ($tallennusmuoto_check) {
$worksheet->writeString($excelrivi, $excelsarake, $koot[$kokonimi]);
} else {
fwrite($fh, pupesoft_csvstring($koot[$kokonimi]) . "\t");
}
}
$excelsarake++;
}
}
if ($tallennusmuoto_check) {
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.02f", $muutoskpl));
$excelsarake++;
} else {
fwrite($fh, pupesoft_csvstring(sprintf("%.02f", $muutoskpl)) . "\t");
}
if (isset($varatturajaus) and $varatturajaus == "O") {
if ($tallennusmuoto_check) {
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.02f", $varattu_saldo));
$excelsarake++;
} else {
fwrite($fh, pupesoft_csvstring(sprintf("%.02f", $varattu_saldo)) . "\t");
}
}
if ($tallennusmuoto_check) {
$worksheet->writeNumber($excelrivi, $excelsarake, sprintf("%.06f", $kehasilloin));
$excelsarake++;
$worksheet->write($excelrivi, 8, t("Varastonarvo ennen inventointia"), $format_bold);
$worksheet->write($excelrivi, 9, t("Arvonmuutos"), $format_bold);
$worksheet->write($excelrivi, 10, t("Selite"), $format_bold);
$excelrivi++;
while ($row = mysql_fetch_assoc($saldoresult)) {
if ($row["inventointiaika"] == '0000-00-00 00:00:00') {
$row["inventointiaika"] = t("Ei inventoitu");
}
preg_match("/ \\(([0-9\\.\\-]*?)\\) /", $row["selite"], $invkpl);
$vararvo_ennen = round((double) $invkpl[1] * $row["hinta"], 2);
$row["selite"] = str_replace("<br>", " ", $row["selite"]);
$worksheet->writeString($excelrivi, 0, $row["tuoteno"]);
$worksheet->writeString($excelrivi, 1, t_tuotteen_avainsanat($row, 'nimitys'));
$worksheet->writeString($excelrivi, 2, $row["toim_tuoteno"]);
$worksheet->writeString($excelrivi, 3, $row["hyllyalue"] . " " . $row["hyllynro"] . " " . $row["hyllyvali"] . " " . $row["hyllytaso"]);
$worksheet->writeNumber($excelrivi, 4, $row["kpl"]);
$worksheet->writeNumber($excelrivi, 5, $row["inventointipoikkeama"]);
$worksheet->writeString($excelrivi, 6, t_avainsana("Y", "", "and avainsana.selite='{$row['yksikko']}'", "", "", "selite"));
$worksheet->writeDate($excelrivi, 7, $row["inventointiaika"]);
$worksheet->writeNumber($excelrivi, 8, $vararvo_ennen);
$worksheet->writeNumber($excelrivi, 9, round($row["arvo"], 2));
$worksheet->writeString($excelrivi, 10, $row["selite"]);
$excelrivi++;
}
$excelnimi = $worksheet->close();
echo "<br><br><table>";
echo "<tr><th>" . t("Tallenna Excel") . ":</th>";
echo "<form method='post' class='multisubmit'>";
echo "<input type='hidden' name='tee' value='lataa_tiedosto'>";
echo "<input type='hidden' name='kaunisnimi' value='" . t("Inventointipoikkeamat") . ".xlsx'>";
echo "<input type='hidden' name='tmpfilenimi' value='{$excelnimi}'>";
}
$ed_tilaus = $set['tilaus'];
foreach ($set as $k => $v) {
if ($k == 'myyjä' and $user != '' and $v != '' and $user != $v) {
echo "<tr>";
echo "<th>{$user} ", t("Yhteensä"), "</th>";
echo "<th colspan='11' style='text-align: right;'>{$total_user}</th>";
echo "<th></th>";
echo "</tr>";
echo "<tr><td class='back' colspan='12'> </tr>";
echo "<tr>{$otsikot}</tr>";
$total_user = 0;
echo "<tr>";
}
if ($excelsarake > 6) {
$worksheet->writeNumber($excelrivi, $excelsarake, $v);
} else {
$worksheet->write($excelrivi, $excelsarake, $v);
}
$excelsarake++;
$stylelisa = $excelsarake > 7 ? " style='text-align: right;' " : "";
echo "<td class='{$odd}' {$stylelisa}>{$v}</td>";
if ($k == 'myyjä' and $v != '') {
$user = $v;
}
if ($k == 'ero' and $user != '') {
$total_user += $v;
$total += $v;
}
}
echo "</tr>";
$tyhja = 1;
}
}
// Suodatetaan extranetkäyttäjilta muut aleprossat
if ((($kukarow["extranet"] != "" or $tee == "eposti" or $yhdistetty != "" or $rajattunakyma == "JOO") and ($edtry != $asrow["try"] or $edryhma != $asrow["alennusryhmä"] or $edtuoteno != $asrow["tuoteno"]) or $kukarow["extranet"] == "" and $tee != "eposti" and $yhdistetty == "" and $rajattunakyma != "JOO") and $tyhja == 0) {
$edryhma = $asrow["alennusryhmä"];
$edtry = $asrow["try"];
$edtuoteno = $asrow["tuoteno"];
if ($yhdistetty == "") {
foreach ($otsik_spread as $key => $value) {
if ($value == "osasto_nimi") {
$worksheet->writeString($excelrivi, $key, $osastot[$asrow["osasto"]]);
} elseif ($value == "try_nimi") {
$worksheet->writeString($excelrivi, $key, $tryt[$asrow["try"]]);
} elseif ($value == "alennus" or $value == "alennuslaji" or $value == "hinta") {
$worksheet->writeNumber($excelrivi, $key, $asrow[$value]);
} else {
$worksheet->writeString($excelrivi, $key, $asrow[$value]);
}
}
$excelrivi++;
}
$dada = array();
if ($yhdistetty == "") {
$ulos .= "<tr>";
}
foreach ($otsik as $o) {
if ($yhdistetty != "") {
$dada[$o] = $asrow[$o];
} else {
// Kaunistetaan tulostusta..
$sarjanumerotrivi = mysql_fetch_assoc($res);
echo "<td class='{$class}' valign='top'>{$sarjanumerotrivi['sarjanumerot']}</td>";
} else {
echo "<td class='{$class}' valign='top'>" . $row[$fieldname] . "</td>";
}
if (isset($worksheet)) {
if ($fieldname == "asiakas") {
$nimiosat = explode("<br>", $row[$fieldname]);
$ytunnari = trim(array_shift($nimiosat));
$lopnimit = trim(implode("\n", $nimiosat));
$worksheet->writeString($excelrivi, $ii, $ytunnari);
$ii++;
$worksheet->writeString($excelrivi, $ii, $lopnimit);
$ii++;
} elseif (mysql_field_type($result, $i) == 'real') {
$worksheet->writeNumber($excelrivi, $ii, sprintf("%.02f", $row[$fieldname]));
$ii++;
} else {
$worksheet->writeString($excelrivi, $ii, $row[$fieldname]);
$ii++;
}
}
}
if ($row["tila"] == "N" and $row["alatila"] == "U") {
if ($jtok == 0) {
echo "<td class='{$class}' valign='top'><font class='green'>" . t("Voidaan toimittaa") . "</font></td>";
if (isset($worksheet)) {
$worksheet->writeString($excelrivi, $ii, "Voidaan toimittaa");
$ii++;
}
} else {
