Subversion-Projekte lars-tiefland.content-management

Revision

Details | Letzte Änderung | Log anzeigen | RSS feed

Revision Autor Zeilennr. Zeile
1 lars 1 
<?php
2 
 
3 
    //$Id: pieper_export_bestellungen.php 3361 2011-02-24 15:34:41Z tiefland $
4 
 
5 
    /**
6 
     * @author Lars Tiefland <tiefland@weban.de>
7 
     * @copyright 2009 Webagentur Niewerth
8 
     * @package Content-management
9 
     * @version $Rev: 3361 $
10 
     * @license propietary
11 
     * @filesource
12 
     *
13 
     */
14 
 
15 
    /**
16 
     *
17 
     * @author Lars Tiefland <tiefland@weban.de>
18 
     * @copyright 2009 Webagentur Niewerth
19 
     * @package Content-management
20 
     */
21 
 
22 
    require_once 'Spreadsheet/Excel/Writer.php';
23 
 
24 
    // Creating a workbook
25 
    $workbook = new Spreadsheet_Excel_Writer( 'export_Bestellungen.xls' );
26 
 
27 
    // sending HTTP headers
28 
    //$workbook->send( 'export_Bestellungen.xls' );
29 
 
30 
    require_once  "../Online-Shop/connect2.php";
31 
    $bestellungen = array();
32 
    $sql = "
33 
        SELECT DISTINCT
34 
            b.*,
35 
            l.Name1,
36 
            l.Vorname,
37 
            s.Name AS shop_name,
38 
            s.database
39 
        FROM
40 
            Bestellung b,
41 
            shops s,
42 
            Logins l,
43 
            artikel_to_Bestellung ab
44 
        WHERE
45 
            ab.Bestellung=b.ID
46 
        AND
47 
            b.Language='DE'
48 
        AND
49 
            ab.rechnungStatus='schreiben'
50 
        AND
51 
            b.Login=l.ID
52 
        AND
53 
            s.ID=b.shops_ID
54 
 
55 
    ";
56 
    $res = mysql_query( $sql );
57 
    $b_anz = mysql_num_rows( $res );
58 
    echo $b_anz;
59 
    if ( $b_anz )
60 
    {
61 
        $r = 0;
62 
        $worksheet = &$workbook->addWorksheet( 'Bestellungen' );
63 
        $worksheet->write( $r, 0, "Bestellnr." );
64 
        $worksheet->write( $r, 1, "Bestelldatum" );
65 
        $worksheet->write( $r, 2, "Shop" );
66 
        $worksheet->write( $r, 3, "Kundenname" );
67 
        $worksheet->write( $r, 4, "Bemerkung" );
68 
        $worksheet->write( $r, 5, "Positionen" );
69 
        $worksheet->write( $r, 6, "Anzahl" );
70 
        $worksheet->write( $r, 7, "Positionswert" );
71 
        $worksheet->write( $r, 8, "Bestellwert" );
72 
        while ( $row = mysql_fetch_assoc($res) )
73 
        {
74 
            $bestellungen[] = $row;
75 
        }
76 
        foreach ( $bestellungen as $row )
77 
        {
78 
            $r++;
79 
            $gesamt = 0;
80 
            //var_dump($row);
81 
            $worksheet->write( $r, 0, $row["ID"] );
82 
            $worksheet->write( $r, 1, $row["erstellt_am"] );
83 
            $worksheet->write( $r, 2, $row["shop_name"] );
84 
            $worksheet->write( $r, 3, $row["Vorname"] . " " . $row["Name1"] );
85 
            $worksheet->write( $r, 4, $row["Bemerkung"] );
86 
            $sql_a = "
87 
                SELECT
88 
                    ab.Bezeichnung,
89 
                    ab.Menge,
90 
                    ab.Preis_pro_Stueck AS preis,
91 
                    ab.Menge*ab.Preis_pro_Stueck AS gesamt
92 
                FROM
93 
                    artikel_to_Bestellung AS  ab
94 
                WHERE
95 
                    ab.Bestellung=" . $row["ID"] . "
96 
                AND
97 
                    ab.rechnungStatus ='schreiben'
98 
            ";
99 
            $res_a = mysql_query( $sql_a );
100 
            while ( $row_a = mysql_fetch_assoc($res_a) )
101 
            {
102 
                $worksheet->write( $r, 5, $row_a["Bezeichnung"] );
103 
                $worksheet->write( $r, 6, $row_a["Menge"] );
104 
                $worksheet->write( $r, 7, number_format($row_a["preis"], 2,
105 
                    ',', '.') );
106 
                $worksheet->write( $r, 8, number_format($row_a["gesamt"], 2,
107 
                    ',', '.') );
108 
                $gesamt += $row_a["gesamt"];
109 
                $r++;
110 
            }
111 
            $sql_a = "
112 
                SELECT
113 
                    ab.Bezeichnung,
114 
                    ab.Preis_pro_Stueck AS preis,
115 
                    ab.Menge*ab.Preis_pro_Stueck AS gesamt
116 
                FROM
117 
                    artikel_to_Bestellung ab,
118 
                    artikel AS a
119 
                WHERE
120 
                    ab.Bestellung=" . $row["ID"] . "
121 
                AND
122 
                    a.ID=ab.artikel
123 
                AND
124 
                    a.Father=-3
125 
            ";
126 
            $res_a = mysql_query( $sql_a );
127 
            while ( $row_a = mysql_fetch_assoc($res_a) )
128 
            {
129 
                $worksheet->write( $r, 5, $row_a["Bezeichnung"] );
130 
                $worksheet->write( $r, 6, $row_a["Menge"] );
131 
                $worksheet->write( $r, 7, number_format($row_a["preis"], 2,
132 
                    ',', '.') );
133 
                $worksheet->write( $r, 8, number_format($row_a["gesamt"], 2,
134 
                    ',', '.') );
135 
                $gesamt += $row_a["gesamt"];
136 
                $r++;
137 
            }
138 
            $worksheet->write( $r, 9, number_format($gesamt, 2, ',', '.') );
139 
            $sql_b =
140 
                "UPDATE artikel_to_Bestellung SET rechnungStatus='geschrieben' WHERE Bestellung=" .
141 
                $row["ID"] . " AND rechnungStatus='schreiben'";
142 
            mysql_query( $sql_b );
143 
        }
144 
        $workbook->close();
145 
        $date = date( "Y-m-d_H-i-s" );
146 
        rename( "export_Bestellungen.xls", "archiv/$site/export_Bestellungen_$date.xls" );
147 
    }
148 
    header( "Location: bestell_export.php" );
149 
?>