Subversion-Projekte lars-tiefland.content-management

<?php

    //$Id: pieper_export_bestellungen.php 3361 2011-02-24 15:34:41Z tiefland $

    /**
     * @author Lars Tiefland <tiefland@weban.de>
     * @copyright 2009 Webagentur Niewerth
     * @package Content-management
     * @version $Rev: 3361 $
     * @license propietary
     * @filesource
     * 
     */

    /**
     * 
     * @author Lars Tiefland <tiefland@weban.de>
     * @copyright 2009 Webagentur Niewerth
     * @package Content-management
     */

    require_once 'Spreadsheet/Excel/Writer.php';

    // Creating a workbook
    $workbook = new Spreadsheet_Excel_Writer( 'export_Bestellungen.xls' );

    // sending HTTP headers
    //$workbook->send( 'export_Bestellungen.xls' );

    require_once  "../Online-Shop/connect2.php";
    $bestellungen = array();
    $sql = "
        SELECT DISTINCT 
            b.*,
            l.Name1,
            l.Vorname,
            s.Name AS shop_name,
            s.database
        FROM
            Bestellung b,
            shops s,
            Logins l,
            artikel_to_Bestellung ab
        WHERE
            ab.Bestellung=b.ID
        AND
            b.Language='DE'
        AND
            ab.rechnungStatus='schreiben'
        AND
            b.Login=l.ID
        AND
            s.ID=b.shops_ID
            
    ";
    $res = mysql_query( $sql );
    $b_anz = mysql_num_rows( $res );
    echo $b_anz;
    if ( $b_anz )
    {
        $r = 0;
        $worksheet = &$workbook->addWorksheet( 'Bestellungen' );
        $worksheet->write( $r, 0, "Bestellnr." );
        $worksheet->write( $r, 1, "Bestelldatum" );
        $worksheet->write( $r, 2, "Shop" );
        $worksheet->write( $r, 3, "Kundenname" );
        $worksheet->write( $r, 4, "Bemerkung" );
        $worksheet->write( $r, 5, "Positionen" );
        $worksheet->write( $r, 6, "Anzahl" );
        $worksheet->write( $r, 7, "Positionswert" );
        $worksheet->write( $r, 8, "Bestellwert" );
        while ( $row = mysql_fetch_assoc($res) )
        {
            $bestellungen[] = $row;
        }
        foreach ( $bestellungen as $row )
        {
            $r++;
            $gesamt = 0;
            //var_dump($row);
            $worksheet->write( $r, 0, $row["ID"] );
            $worksheet->write( $r, 1, $row["erstellt_am"] );
            $worksheet->write( $r, 2, $row["shop_name"] );
            $worksheet->write( $r, 3, $row["Vorname"] . " " . $row["Name1"] );
            $worksheet->write( $r, 4, $row["Bemerkung"] );
            $sql_a = "
                SELECT
                    ab.Bezeichnung,
                    ab.Menge,
                    ab.Preis_pro_Stueck AS preis,
                    ab.Menge*ab.Preis_pro_Stueck AS gesamt
                FROM
                    artikel_to_Bestellung AS  ab
                WHERE
                    ab.Bestellung=" . $row["ID"] . "
                AND
                    ab.rechnungStatus ='schreiben'
            ";
            $res_a = mysql_query( $sql_a );
            while ( $row_a = mysql_fetch_assoc($res_a) )
            {
                $worksheet->write( $r, 5, $row_a["Bezeichnung"] );
                $worksheet->write( $r, 6, $row_a["Menge"] );
                $worksheet->write( $r, 7, number_format($row_a["preis"], 2,
                    ',', '.') );
                $worksheet->write( $r, 8, number_format($row_a["gesamt"], 2,
                    ',', '.') );
                $gesamt += $row_a["gesamt"];
                $r++;
            }
            $sql_a = "
                SELECT
                    ab.Bezeichnung,
                    ab.Preis_pro_Stueck AS preis,
                    ab.Menge*ab.Preis_pro_Stueck AS gesamt
                FROM
                    artikel_to_Bestellung ab,
                    artikel AS a
                WHERE
                    ab.Bestellung=" . $row["ID"] . "
                AND
                    a.ID=ab.artikel
                AND
                    a.Father=-3
            ";
            $res_a = mysql_query( $sql_a );
            while ( $row_a = mysql_fetch_assoc($res_a) )
            {
                $worksheet->write( $r, 5, $row_a["Bezeichnung"] );
                $worksheet->write( $r, 6, $row_a["Menge"] );
                $worksheet->write( $r, 7, number_format($row_a["preis"], 2,
                    ',', '.') );
                $worksheet->write( $r, 8, number_format($row_a["gesamt"], 2,
                    ',', '.') );
                $gesamt += $row_a["gesamt"];
                $r++;
            }
            $worksheet->write( $r, 9, number_format($gesamt, 2, ',', '.') );
            $sql_b =
                "UPDATE artikel_to_Bestellung SET rechnungStatus='geschrieben' WHERE Bestellung=" .
                $row["ID"] . " AND rechnungStatus='schreiben'";
            mysql_query( $sql_b );
        }
        $workbook->close();
        $date = date( "Y-m-d_H-i-s" );
        rename( "export_Bestellungen.xls", "archiv/$site/export_Bestellungen_$date.xls" );
    }
    header( "Location: bestell_export.php" );
?>