Может сделать раздел с примерами запросов к POSTGRESQL из OpenOffice?
Например как сделать отчет по продажам за день
Spike писал(а):А еще лучше в HTML.
CREATE OR REPLACE VIEW kassa_transactions AS
SELECT a.n_transaction, a.tr_type_id, a.name, a.value_1, a.value_2, a.gain, a.barcode, a.tr_date_time, a.n_pc, a.n_kkm, a.seller, a.n_session, a.n_check, a.n_doc, a.check_type, a.code, a.code_tax_group, a.code_tax_rate, a.payment_type, a.card_code, a.reduction_info, a.reduction_type, a.auto_scheme_id, a.protokol_type, a.round_sum
FROM dblink('hostaddr=192.168.0.5 port=5432 dbname=Transaction user=kassir'::text, 'SELECT * FROM transaction_explain '::text) a(n_transaction integer, tr_type_id integer, name text, value_1 numeric(19,3), value_2 numeric(19,3), gain text, barcode text, tr_date_time timestamp without time zone, n_pc integer, n_kkm integer, seller integer, n_session integer, n_check integer, n_doc integer, check_type integer, code text, code_tax_group integer, code_tax_rate integer, payment_type integer, card_code text, reduction_info text, reduction_type integer, auto_scheme_id integer, protokol_type integer, round_sum numeric(19,3));
CREATE OR REPLACE VIEW sales AS
SELECT d.n_transaction, d.name, d.price, d.amount, d.operation, d.salesdate, d.code, d.salestime, d.n_pc, d.n_kkm, d.seller, d.n_session, d.n_check, d.check_type, d.n_doc, d.payment_type, d.round_sum, d.protokol_type, d.group_id,
CASE
WHEN d.code IS NOT NULL AND wg.longtitle IS NULL THEN 'Без группы'::character varying
WHEN d.code IS NOT NULL AND wg.longtitle IS NOT NULL THEN wg.longtitle
ELSE NULL::character varying
END AS waregroup
FROM ( SELECT kassa_transactions.n_transaction, w.longtitle AS name, kassa_transactions.value_1 AS price, kassa_transactions.value_2 AS amount,
CASE
WHEN kassa_transactions.check_type = 0 AND kassa_transactions.tr_type_id = 11 THEN 'продажа'::text
WHEN kassa_transactions.check_type = 1 AND kassa_transactions.tr_type_id = 11 THEN 'возврат'::text
ELSE NULL::text
END AS operation, kassa_transactions.tr_date_time::date AS salesdate, w.internal_id AS code, kassa_transactions.tr_date_time::time without time zone AS salestime, kassa_transactions.n_pc, kassa_transactions.n_kkm, kassa_transactions.seller, kassa_transactions.n_session, kassa_transactions.n_check, kassa_transactions.check_type, kassa_transactions.n_doc, kassa_transactions.payment_type, kassa_transactions.round_sum, kassa_transactions.protokol_type, w.group_id
FROM ware w
RIGHT JOIN kassa_transactions ON w.internal_id::text = kassa_transactions.code
WHERE kassa_transactions.tr_type_id = 11) d
LEFT JOIN ware_group wg ON wg.group_id = d.group_id;
Rafta писал(а):Извините, а в какой программе набирать эти запросы
LinCash=# * from
sales_bydate_operaion order by salesdate desc limit 10
;
salesdate | weekday | operation | summ
------------+---------+-----------+-----------
2011-05-20 | 5 | продажа | 7600.000
2011-05-19 | 4 | продажа | 2855.000
2011-05-18 | 3 | продажа | 6660.000
2011-05-17 | 2 | продажа | 14075.000
2011-05-16 | 1 | продажа | 7475.000
2011-05-14 | 6 | продажа | 1010.000
2011-05-13 | 5 | продажа | 5310.000
2011-05-12 | 4 | продажа | 8910.000
2011-05-11 | 3 | продажа | 9000.000
2011-05-10 | 2 | продажа | 15891.000
(10 rows)
Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1