2 -- @description: Erstellen der Tabellen shop_orders und shop_order_items
3 -- @depends: release_3_5_0 shops
5 CREATE TABLE shop_orders (
7 shop_trans_id integer NOT NULL, --id vom shop
8 shop_ordernumber TEXT, --Bestellnummer vom Shop
9 shop_data text, -- store whole order as json
10 shop_customer_comment text, --Bestellkommentar des Kunden
11 amount numeric(15,5), --Bruttogesamtbetrag
12 netamount numeric(15,5),--Nettogesamtbetrag
13 order_date timestamp, --Bestelldatum und Zeit
14 shipping_costs numeric(15,5),
15 shipping_costs_net numeric(15,5),
16 shipping_costs_id integer,
18 payment_id integer, --Bezahlart
19 payment_description TEXT, --Bezahlart
20 shop_id integer, --welcher shop bei mehreren
21 host TEXT, --Hostname vom Shop
22 remote_ip text, --IP Besteller
23 transferred boolean DEFAULT FALSE, -- übernommen
24 transfer_date date, -- Zeit wann übernommen
25 kivi_customer_id integer, -- Kundenid von Tbl customer wenn übernommen
26 oe_transid integer, -- id to
27 -- Bestell-, Rechnungs- und Lieferadresse. !!Manche Shops bieten sowas!!
28 -- In der Regel ist aber die Rechnungsadresse die Kundenadresse
29 -- Bestelldaten des Kunden
30 shop_customer_id integer,
31 shop_customer_number TEXT,
32 customer_lastname TEXT,
33 customer_firstname TEXT,
34 customer_company TEXT,
36 customer_zipcode TEXT,
38 customer_country TEXT,
39 customer_greeting TEXT,
40 customer_department TEXT,
45 customer_newsletter boolean,
47 shop_c_billing_id integer,
48 shop_c_billing_number TEXT,
49 billing_lastname TEXT,
50 billing_firstname TEXT,
56 billing_greeting TEXT,
57 billing_department TEXT,
64 sepa_account_holder TEXT,
69 shop_c_delivery_id integer,
70 shop_c_delivery_number TEXT,
71 delivery_lastname TEXT,
72 delivery_firstname TEXT,
73 delivery_company TEXT,
75 delivery_zipcode TEXT,
77 delivery_country TEXT,
78 delivery_greeting TEXT,
79 delivery_department TEXT,
85 obsolete boolean DEFAULT FALSE NOT NULL,
88 itime timestamp DEFAULT now(),
92 CREATE TABLE shop_order_items (
93 id SERIAL PRIMARY KEY,
94 shop_trans_id INTEGER NOT NULL, --id vom shop in shop-db? -> could use $order_item->shop_order->shop_trans_id instead
95 shop_order_id INTEGER REFERENCES shop_orders (id) ON DELETE CASCADE,
96 description TEXT, -- Artikelbezeichnung
100 tax_rate NUMERIC(15,2),
101 quantity NUMERIC(25,5), -- qty in invoice and orderitems is real, doi is numeric(25,5)