From 0d437edd164b8b6525562b5173778a81dd5f7ce7 Mon Sep 17 00:00:00 2001 From: "kilian (ksmachome)" Date: Thu, 21 Nov 2019 14:13:47 +0100 Subject: [PATCH] sql schema ij --- dev/db/buchhaltung.schema.sql | 578 ++++++++++++++++++++++++++++++++++ 1 file changed, 578 insertions(+) create mode 100644 dev/db/buchhaltung.schema.sql diff --git a/dev/db/buchhaltung.schema.sql b/dev/db/buchhaltung.schema.sql new file mode 100644 index 0000000..5806387 --- /dev/null +++ b/dev/db/buchhaltung.schema.sql @@ -0,0 +1,578 @@ +CREATE TABLE sqlite_sequence(name,seq); +CREATE TABLE IF NOT EXISTS "zzold_banks" ( + bank_id integer not null, + bank_country Code TEXT, + bank_name TEXT, + bank_bic TEXT +); +CREATE TABLE IF NOT EXISTS "zzold_product_codes" ( + product_code_id integer not null, + product_name TEXT, + product_description TEXT, + product__net_price numeric +); +CREATE TABLE IF NOT EXISTS "zzold_invoice_type" ( + type_id intger integer not null, + type_sign text, + type_name text, + type_print text +); +CREATE TABLE IF NOT EXISTS "zzold_invoice_status" ( + status_id integer not null, + status_text text +); +CREATE TABLE IF NOT EXISTS "zzold_reminder_definition" ( + level integer not null, + days integer, + value numeric +); +CREATE TABLE IF NOT EXISTS "zzold_positions" + ( + uuid TEXT, + invoiceuuid TEXT, + productuuid TEXT, + quantity INTEGER, + unitamount NUMERIC, + unittype TEXT, + description TEXT, + taxamount NUMERIC, + moddate DATETIME, + taxpercent NUMERIC, + netamount NUMERIC, + totalamount NUMERIC + ); +CREATE TABLE IF NOT EXISTS "zzold_invoices" + ( + uuid TEXT, + invoicedate DATE, + type TEXT, + refnumber TEXT, + ordertext TEXT, + recipientsendername TEXT, + recepientsendername2 TEXT, + address TEXT, + address2 TEXT, + zip TEXT, + city TEXT, + country TEXT, + reftext TEXT, + status TEXT, + statusdate DATE, + invoicenote TEXT, + payedamount NUMERIC, + remindercount INTEGER, + reportlanguage TEXT, + netamount NUMERIC, + taxamount NUMERIC, + totalamount NUMERIC, + id_old INTEGER, + reminderlimitdate DATE, + direction TEXT + ); +CREATE TABLE IF NOT EXISTS "zzold_addresses" + ( + uuid TEXT, + name TEXT, + address TEXT, + zip TEXT, + city TEXT, + country TEXT, + invoice_language TEXT, + name2 TEXT, + address2 TEXT, + reportlanguage TEXT + , email TEXT, phone TEXT, vatnumber TEXT); +CREATE TABLE IF NOT EXISTS "zzold_countries" (iso TEXT, "de" TEXT, "en" TEXT, "fr" TEXT); +CREATE TABLE IF NOT EXISTS "zzold_vatpercents" (country TEXT, desciption TEXT, percents float); +CREATE TABLE IF NOT EXISTS "zzold_languages" ( +"iso" TEXT, +"de" TEXT, +"fr" TEXT, +"en" TEXT +); +CREATE TABLE IF NOT EXISTS "zzold_products" + ( + "uuid" TEXT NOT NULL, + "name" TEXT, + "description" TEXT, + "unittype" TEXT, + "unitamount" NUMERIC + ); +CREATE TABLE IF NOT EXISTS "zzold_offers"( + "uuid" TEXT NOT NULL, + "addressuuid" TEXT, + "offerdate" DATE, + "reccurence" TEXT, + "expirationdate" DATE, + "name" TEXT, + "description" TEXT, + "referencenumber" TEXT, + "referencetext" TEXT, + "offernote" TEXT, + "status" TEXT, + "statusdate" DATE + "offerlanguage" TEXT, + "direction" TEXT +); +CREATE TABLE IF NOT EXISTS "zzold_contracts"( + "uuid" TEXT NOT NULL, + "addressuuid" TEXT, + "contractdate" DATE, + "recurrence" TEXT, + "expirationdate" DATE, + "name" TEXT, + "description" TEXT, + "referencenumber" TEXT, + "referencetext" TEXT, + "contractnote" TEXT, + "status" TEXT, + "statusdate" DATE + "contractlanguage" TEXT, + "direction" TEXT +); +CREATE TABLE IF NOT EXISTS "zzold_offerpositions" ( + "uuid" TEXT NOT NULL, + "productuuid" TEXT, + "quantity" INTEGER +); +CREATE TABLE IF NOT EXISTS "zzold_contractpositions" ( + "uuid" TEXT NOT NULL, + "productuuid" TEXT, + "quantity" INTEGER +); +CREATE TABLE IF NOT EXISTS "zzold_tblprodukte" ( + "procuctuuid" TEXT, + "id" TEXT, + "name" TEXT, + "typ" TEXT, + "interne_beschreibung" TEXT, + "nettoeinheitspreis" TEXT, + "einheit" TEXT, + "prozentmehrwertsteuer" TEXT, + "bruttoeinheitspreis" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_tblkonten" ( + "address" TEXT, + "bankaccount" TEXT, + "bic" TEXT, + "calc_empfaenger" TEXT, + "calc_sender" TEXT, + "city" TEXT, + "clientnumber" TEXT, + "company" TEXT, + "country" TEXT, + "email" TEXT, + "id" TEXT, + "language" TEXT, + "mobile" TEXT, + "phone" TEXT, + "prename" TEXT, + "surname" TEXT, + "title" TEXT, + "type" TEXT, + "vatid" TEXT, + "zip" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_tbljournal" ( + "betragbezahlt" TEXT, + "betragbrutto" TEXT, + "betraggeplantnetto" TEXT, + "bussinessjahr" TEXT, + "bussinessquartal" TEXT, + "calc_jahr" TEXT, + "calc_quartal" TEXT, + "calc_rechnungbrutto" TEXT, + "calc_rechnungmwst" TEXT, + "calc_rechnungnetto" TEXT, + "calc_rechnungnettomitrabatt" TEXT, + "calc_rechnungrabatt" TEXT, + "calc_referenzrechnung" TEXT, + "created" TEXT, + "datum" TEXT, + "datumfaelligkeit" TEXT, + "datumstatus" TEXT, + "emailadresse" TEXT, + "emailbetreff" TEXT, + "emailnachricht" TEXT, + "empfaengeradresse" TEXT, + "empfaengerland" TEXT, + "empfaengername" TEXT, + "empfaengerort" TEXT, + "empfaengerplz" TEXT, + "empfaengerustid" TEXT, + "foreignaccount" TEXT, + "id" TEXT, + "id_angebot" TEXT, + "id_emailvorlage" TEXT, + "id_empfaenger" TEXT, + "id_sender" TEXT, + "kontengruppe" TEXT, + "kontoauszug" TEXT, + "last_editor" TEXT, + "modified" TEXT, + "nachwort" TEXT, + "nomoreuse_prozentmehrwertsteuer" TEXT, + "receipientident" TEXT, + "referenz" TEXT, + "remindernumber" TEXT, + "remindersenddates" TEXT, + "senderadresse" TEXT, + "senderland" TEXT, + "sendername" TEXT, + "senderort" TEXT, + "senderrplz" TEXT, + "senderustid" TEXT, + "status" TEXT, + "transactionident" TEXT, + "typ" TEXT, + "vorlage" TEXT, + "vorwort" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_tblausgaben" ( + "id" TEXT, + "company" TEXT, + "address" TEXT, + "zip" TEXT, + "city" TEXT, + "country" TEXT, + "netamount" TEXT, + "vatamount" TEXT, + "totalamount" TEXT, + "payedamount" TEXT, + "invoicedate" TEXT, + "reminderdate" TEXT, + "payementtype" TEXT, + "status" TEXT, + "statusdate" TEXT, + "reference" TEXT, + "statementreference" TEXT, + "note" TEXT, + "moddate" TEXT, + "calcinvoiceyear" TEXT, + "calcinvoicequarter" TEXT, + "geschaeftsjahr" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_rechnungslabels" ( + "addressheading" TEXT, + "clientnumber" TEXT, + "clientvatnumber" TEXT, + "commoninvoicenote" TEXT, + "date" TEXT, + "discount" TEXT, + "heading" TEXT, + "headpositionexplication" TEXT, + "headpositionnetamount" TEXT, + "headpositionquantity" TEXT, + "headpositiontaxpercent" TEXT, + "headpositionunit" TEXT, + "headpositionunitamount" TEXT, + "id" TEXT, + "letterfooter" TEXT, + "letterheader" TEXT, + "name" TEXT, + "netsubtotal" TEXT, + "refnumber" TEXT, + "refordernumber" TEXT, + "reftext" TEXT, + "reminderdate" TEXT, + "shortaddressbar" TEXT, + "sprache" TEXT, + "totaldiscountamount" TEXT, + "totalnetamount" TEXT, + "totalsum" TEXT, + "totaltaxamount" TEXT, + "typ" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_accounts_old" ( + "id" TEXT, + "company" TEXT, + "title" TEXT, + "prename" TEXT, + "surname" TEXT, + "address" TEXT, + "zip" TEXT, + "city" TEXT, + "country" TEXT, + "email" TEXT, + "phone" TEXT, + "mobile" TEXT, + "ident" TEXT, + "vatid" TEXT, + "iban" TEXT, + "bic" TEXT, + "type" TEXT, + "language" TEXT, + PRIMARY KEY (id) +); +CREATE TABLE IF NOT EXISTS "zzold_invoicejournal" ( + "id" TEXT, + "id_receipient" TEXT, + "id_sender" TEXT, + "payedamount" REAL, + "byear" INTEGER, + "bquarter" TEXT, + "created" TEXT, + "date" DATE, + "deadlinedate" DATE, + "statusdate" DATE, + "status" TEXT, + "statement" TEXT, + "modified" TEXT, + "footertext" TEXT, + "reference" TEXT, + "remindernumber" TEXT, + "reminderdates" TEXT, + "transactionident" TEXT, + "type" TEXT, + "id_template" TEXT, + "headertext" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_tblrechnungsdaten" ( + "anzahl" TEXT, + "beschreibung" TEXT, + "betrageinheit" TEXT, + "betragmwst" TEXT, + "date_created" TEXT, + "date_modified" TEXT, + "einheit" TEXT, + "id" TEXT, + "id_rechnung" TEXT, + "last_editor" TEXT, + "prozentmwst" TEXT, + "prozentrabatt" TEXT, + PRIMARY KEY ('id') +); +CREATE TABLE IF NOT EXISTS "zzold_invoicepositions" + ( + id TEXT, + id_invoice TEXT, + id_product TEXT, + quantity INTEGER, + unitamount NUMERIC, + unit TEXT, + description TEXT, + taxamount NUMERIC, + discountamount NUMERIC, + taxpercent NUMERIC, + discountpercent NUMERIC, + netamount NUMERIC, + totalamount NUMERIC, + created DATETIME, + modified DATETIME, id_sender TEXT, id_receipient TEXT, bookingdate DATE, + PRIMARY KEY (id) + ); +CREATE TABLE products ( + id TEXT, + ident TEXT, + ptype TEXT, + unit TEXT, + unitamount TEXT, + vatpercent TEXT, + description TEXT, + primary key (id) +); +CREATE TABLE IF NOT EXISTS "zzold_banktransactions_old" ( + id TEXT, + account TEXT, + statementnumber INTEGER, + bookingdate DATE, + amount REAL, + transactionident TEXT, + message TEXT, + foreignaccountowner TEXT, + bank TEXT, + transferaccount TEXT, + transfercosts REAL, + file TEXT, + PRIMARY KEY (id) +); +CREATE TABLE IF NOT EXISTS "zzold_accounts" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + receipient TEXT, + title TEXT, + prename TEXT, + surname TEXT, + address TEXT, + zip TEXT, + city TEXT, + country TEXT, + email TEXT, + phone TEXT, + mobile TEXT, + ident TEXT, + vatid TEXT, + iban TEXT, + bic TEXT, + types TEXT, + lang TEXT, + old_id TEXT +); +CREATE TABLE invoices ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + id_receipient integer, + id_sender integer, + payedamount REAL, + byear INTEGER, + bquarter TEXT, + invoicedate DATE, + deadlinedate DATE, + statusdate DATE, + status TEXT, + bankstatement integer, + footertext TEXT, + reference TEXT, + remindernumber TEXT, + reminderdates TEXT, + transactionident TEXT, + invoicetype TEXT, + templatename integer, + headertext TEXT, + old_id TEXT, + old_id_receipient TEXT, + old_id_sender TEXT, + modified DATETIME DEFAULT CURRENT_TIMESTAMP, + created DATETIME DEFAULT CURRENT_TIMESTAMP +); +CREATE TABLE IF NOT EXISTS "bookings" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + quantity NUMERIC, + unitamount NUMERIC, + unit TEXT, + description TEXT, + taxamount NUMERIC, + discountamount NUMERIC, + taxpercent NUMERIC, + discountpercent NUMERIC, + netamount NUMERIC, + totalamount NUMERIC, + bookingdate DATE, + old_id TEXT, + old_id_invoice TEXT, + old_id_product TEXT, + modified DATETIME DEFAULT CURRENT_TIMESTAMP, + created DATETIME DEFAULT CURRENT_TIMESTAMP + , id_invoice integer); +CREATE TABLE IF NOT EXISTS "zzold_transactions" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + "type" TEXT, + account TEXT, + statementnumber INTEGER, + bookingdate DATE, + amount REAL, + transactionident TEXT, + message TEXT, + foreignaccountowner TEXT, + bank TEXT, + transferaccount TEXT, + transfercosts REAL, + file TEXT +); +CREATE VIEW vw_banktransactions as +select * from "zzold_transactions"; +CREATE VIEW vw_businessyears as +select bb.byear as value,bb.byear as label from +(select strftime("%Y",invoicedate) as byear from invoices) bb group by byear order by byear; +CREATE VIEW vw_invoices as +SELECT inv.id, inv.id_receipient,recp.receipient, inv.id_sender,sen.receipient as sender, inv.payedamount, inv.byear, inv.bquarter, inv.invoicedate, inv.deadlinedate, inv.statusdate, inv.status, inv.bankstatement, inv.footertext, inv.reference, inv.remindernumber, inv.reminderdates, inv.transactionident, inv.invoicetype, inv.templatename, inv.headertext, inv.modified, inv.created +FROM invoices inv +left join "zzold_accounts" sen on (inv.id_sender=sen.id) +left join "zzold_accounts" recp on (inv.id_receipient=recp.id); +CREATE VIEW vw_receipients as +select id as value,receipient as label from "zzold_accounts" order by receipient; +CREATE VIEW vw_receipientsdata as +select id,receipient,coalesce(address,'') || '\n' || coalesce(zip,'') || ' ' || coalesce(city,'') || '\n' || coalesce(country,'') as address from "zzold_accounts"; +CREATE TABLE IF NOT EXISTS "accounts" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + receipient TEXT, + title TEXT, + prename TEXT, + surname TEXT, + address TEXT, + zip TEXT, + city TEXT, + country TEXT, + email TEXT, + phone TEXT, + mobile TEXT, + ident TEXT, + vatid TEXT, + iban TEXT, + bic TEXT, + types TEXT, + lang TEXT, + old_id TEXT +, created DATETIME DEFAULT CURRENT_TIMESTAMP, modified DATETIME default CURRENT_TIMESTAMP); +CREATE TRIGGER trg_accounts_upd UPDATE ON accounts + BEGIN + UPDATE accounts set modified=CURRENT_TIMESTAMP where id = NEW.id; + END; +CREATE TABLE IF NOT EXISTS "transactions" ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + "type" TEXT, + account TEXT, + statementnumber INTEGER, + bookingdate DATE, + amount REAL, + transactionident TEXT, + message TEXT, + foreignaccountowner TEXT, + bank TEXT, + transferaccount TEXT, + transfercosts REAL, + file TEXT, + modified DATETIME default CURRENT_TIMESTAMP, + created DATETIME default CURRENT_TIMESTAMP +); +CREATE TRIGGER trg_transactions_upd UPDATE ON transactions + BEGIN + UPDATE transactions set modified=CURRENT_TIMESTAMP where id = NEW.id; + END; +CREATE TRIGGER trg_invoices_upd UPDATE ON invoices + BEGIN + UPDATE invoices set modified=CURRENT_TIMESTAMP where id = NEW.id; + END; +CREATE TRIGGER trg_bookings_upd UPDATE ON bookings + BEGIN + UPDATE bookings set modified=CURRENT_TIMESTAMP where id = NEW.id; + END; +CREATE VIEW vw_quarters as +select bquarter as label, bquarter as value from invoices group by bquarter order by bquarter DESC; +CREATE TABLE files ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + id_invoice integer, + id_transaction integer, + filepath TEXT, + folder TEXT, + filetype TEXT, + created DATETIME DEFAULT (datetime('now','localtime')), + modified DATETIME default (datetime('now','localtime')) +); +CREATE TRIGGER trg_files_upd UPDATE ON files + BEGIN + UPDATE files SET modified=datetime('now','localtime') WHERE id = NEW.id; + END; +CREATE VIEW vw_invoicelist as +select inv.id,inv.id_receipient,rec.receipient,inv.id_sender,sen.receipient as sender,inv.bquarter, inv.invoicedate,inv.deadlinedate,inv.bankstatement,inv.reminderdates,inv.status,inv.statusdate,inv.reference +,printf("%.2f",sum(round(bk.quantity * bk.unitamount,2))) as netamount +,printf("%.2f",sum(round(bk.quantity * bk.unitamount * bk.taxpercent,2))) as vatamount +,printf("%.2f",sum(round(bk.quantity * bk.unitamount,2)) + sum(round(bk.quantity * bk.unitamount * bk.taxpercent,2))) as grossamount +,printf("%.2f",inv.payedamount) as payedamount +from invoices inv +join bookings bk on (inv.id=bk.id_invoice) +join accounts rec on (inv.id_receipient=rec.id) +join accounts sen on (inv.id_sender=sen.id) +where inv.invoicedate +group by inv.id +order by inv.invoicedate desc; +CREATE VIEW vw_bookings as +SELECT id, printf("%.2f",quantity) as quantity, printf("%.2f",coalesce(unitamount,0.0)) as unitamount, coalesce(unit,'') as unit, coalesce(description,'') as description, +printf("%.2f",case when taxpercent is not null then coalesce(taxpercent,0.0) * coalesce(quantity,0.0) * coalesce(unitamount,0.0) else coalesce(taxamount,0.0) end ) as taxamount, +printf("%.2f",coalesce(taxpercent,0.0)) as taxpercent, +printf("%.2f",coalesce(quantity,0.0) * coalesce(unitamount,0.0)) as netamount, +printf("%.2f",netamount) as netamount, id_invoice FROM bookings; -- 2.39.5