$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 {