Господа ну что вы .. Вот примерный 2 таблиц. В реале будет
Таблицы
RefProducts
RefCategory --Shoes Shirt etc
RefMeasurement
RefStores
RefCustomers
RefUsers
RefActions --- Sale Return Purchase Correction
HeaderDoc
HeaderDetail
Планирую 2 доп таблицу
Table Saldo
ProductID
BoxNumber
StoreID
CurrentDate
saldo_in,
in_come,
out_come,
saldo_out
Table Remain
ProductID
BoxNumber
StoreID
Qty.. Буду отсюда брать остатки
Пока селектом беру но такой подход не нравиться мне
Пока в день будет инсертится общий 500-1000 записи
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
/******************************************************************************/
/*** Generated by IBExpert 2016.1.11.1 11.01.2016 23:43:48 ***/
/******************************************************************************/
SET SQL DIALECT 3;
SET NAMES UTF8;
SET CLIENTLIB 'C:\fb3rc2\fbclient.dll';
CREATE DATABASE 'butik'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;
/******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE HEADDOC (
ID D_ID GENERATED BY DEFAULT AS IDENTITY,
DIRECTION VARCHAR(30),
FROM_STORE VARCHAR(20),
TO_STORE VARCHAR(20),
DATEOPERATION TIMESTAMP,
DOCNUM VARCHAR(20),
ISEXECUTED VARCHAR(3)
);
CREATE TABLE HEADERDETAIL (
ID INTEGER GENERATED BY DEFAULT AS IDENTITY,
ID_HEADDOC INTEGER NOT NULL,
ITEM VARCHAR(50) CHARACTER SET NONE,
QTY INTEGER DEFAULT 0 NOT NULL,
AMT NUMERIC(18,3) DEFAULT 0 NOT NULL,
BOXNUMBER INTEGER NOT NULL
);
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (1, 'purchase', 'China', 'Мainstore', '2016-01-10 00:00:00', '#1', 'Yes');
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (2, 'move', 'Мainstore', 'Store1', '2016-01-11 23:07:02', '#2', 'Yes');
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (3, 'move', 'Store1', 'Мainstore', '2016-01-12 00:00:00', '#3', 'Yes');
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (4, 'sale', 'Store1', 'client', '2016-01-13 00:00:00', '#3', 'Yes');
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (5, 'correction', 'Store1', NULL, '2016-01-20 00:00:00', '#2', 'Yes');
INSERT INTO HEADDOC (ID, DIRECTION, FROM_STORE, TO_STORE, DATEOPERATION, DOCNUM, ISEXECUTED) VALUES (6, 'return sale', 'client', 'Store1', '2016-01-26 00:00:00', '#Return ', 'Yes');
COMMIT WORK;
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (1, 1, 'Item A Black XXL', 10, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (2, 1, 'Item A Red XXX', 10, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (3, 1, 'Item A White XL', 10, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (4, 2, 'Item A Black XXL', 5, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (11, 3, 'Item A Black XXL', 3, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (12, 4, 'Item A Black XXL', 2, 25, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (13, 5, 'Item A Black XXL', 5, 15, 1);
INSERT INTO HEADERDETAIL (ID, ID_HEADDOC, ITEM, QTY, AMT, BOXNUMBER) VALUES (15, 6, 'Item A Black XXL', 1, 25, 1);
COMMIT WORK;
/******************************************************************************/
/*** Autoincrement generators ***/
/******************************************************************************/
ALTER TABLE HEADDOC ALTER ID RESTART WITH 6;
ALTER TABLE HEADERDETAIL ALTER ID RESTART WITH 15;
/******************************************************************************/
/*** Primary keys ***/
/******************************************************************************/
ALTER TABLE HEADERDETAIL ADD PRIMARY KEY (ID);
/******************************************************************************/
/*** Fields descriptions ***/
/******************************************************************************/
COMMENT ON COLUMN HEADDOC.DIRECTION IS
'sale,purchase,return purchase,return sale,move,correction';
COMMENT ON COLUMN HEADDOC.FROM_STORE IS
'from china turkey or stores or customers';
COMMENT ON COLUMN HEADDOC.TO_STORE IS
'to stores or customers';
COMMENT ON COLUMN HEADDOC.DATEOPERATION IS
'operation date';
COMMENT ON COLUMN HEADDOC.DOCNUM IS
'Doc number';
COMMENT ON COLUMN HEADDOC.ISEXECUTED IS
'yes/no calculated remain items total';
COMMENT ON COLUMN HEADERDETAIL.ITEM IS
'Name Item';
Сам селект для показа остатка
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
select item, boxnumber, sum(decode(stores, 'Store1', (qty), 0)) as "Store1",
sum(decode(stores, 'Store2', (qty), 0)) as "Store2",
sum(decode(stores, 'Store3', (qty), 0)) as "Store3",
sum(decode(stores, 'Мainstore', (qty), 0)) as "Мainstore",
sum((qty)) total
from (select headdoc.to_store stores, sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headdoc.id = headerdetail.id_headdoc and
headdoc.direction = 'purchase'
group by headdoc.to_store, item, headerdetail.boxnumber
union all
select headdoc.from_store stores, -sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headerdetail.id_headdoc = headdoc.id and
headdoc.direction = 'move'
group by headdoc.from_store, item, headerdetail.boxnumber
union all
select headdoc.to_store stores, sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headerdetail.id_headdoc = headdoc.id and
headdoc.direction = 'move'
group by headdoc.to_store, item, headerdetail.boxnumber
union all
select headdoc.from_store stores, sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headerdetail.id_headdoc = headdoc.id and
headdoc.direction = 'correction'
group by headdoc.from_store, item, headerdetail.boxnumber
union all
select headdoc.from_store stores, -sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headerdetail.id_headdoc = headdoc.id and
headdoc.direction = 'sale'
group by headdoc.from_store, item, headerdetail.boxnumber
union all
select headdoc.to_store stores, sum(headerdetail.qty) qty, headerdetail.item item,
headerdetail.boxnumber boxnumber
from headdoc
join headerdetail on headdoc.id = headerdetail.id_headdoc and
headdoc.direction = 'return sale'
group by headdoc.to_store, item, headerdetail.boxnumber
) x
group by item, boxnumber;
|
