În primul rând, scuze de întârziere: de vreo 3-4 zile stau cu fereastra asta deschisă în Firefox şi am tot amânat răspunsul pentru o zi în care am mai mult timp (mai ales din cauza celei de-a doua întrebări).
Pentru prima întrebare, răspunsul e destul de simplu. Presupunând că avem tabela PARTENERI, cu următoarele date:
CREATE TABLE PARTENERI (
ID varchar(10) PRIMARY KEY,
NUME varchar(50) NOT NULL UNIQUE,
CF varchar(15) NULL
)
INSERT INTO PARTENERI VALUES ('01', 'BUCUR OBOR SA', 'RO19')
INSERT INTO PARTENERI VALUES ('02', 'ELPROF SA', 'RO27')
Iar datele din tabela NOTE_CONTABILE sunt următoarele:
INSERT INTO NOTE_CONTABILE (TIPDOC, NRDOC, DATADOC, CONTDB, CONTCR, LEI)
SELECT 'FACT' TIPDOC, '1' NRDOC, '20070101' DATADOC, '371.1' CONTDB, '401.01' CONTCR, 100 LEI
UNION ALL SELECT 'FACT', '1', '20070101', '4426.2', '401.01', 19
UNION ALL SELECT 'FACT', '2', '20070102', '214', '404.01', 2000
UNION ALL SELECT 'FACT', '2', '20070102', '4426.1', '404.01', 380
UNION ALL SELECT 'FACT', '2', '20070102', '303', '401.01', 200
UNION ALL SELECT 'FACT', '2', '20070102', '4426.3', '401.01', 38
UNION ALL SELECT 'FACT', '3', '20070103', '628', '401.02', 1000
UNION ALL SELECT 'FACT', '3', '20070103', '4426.3', '401.02', 190
UNION ALL SELECT 'FACT', '3', '20070103', '303', '401.02', 200
UNION ALL SELECT 'FACT', '3', '20070103', '4426.3', '401.02', 38
UNION ALL SELECT 'FACT', '4', '20070104', '628', '401.03', 1000
UNION ALL SELECT 'FACT', '4', '20070104', '4426.3', '401.03', 190
UNION ALL SELECT 'FACT', '5', '20070105', '628', '401', 1000
UNION ALL SELECT 'FACT', '5', '20070105', '4426.3', '401', 190
atunci putem obţine rezultatul dorit cu următorul query:
SELECT DATADOC, NRDOC, NUME AS FURNIZOR, CF, SUM(VALOARE) as TOTAL,
SUM(CASE WHEN COLOANA=1 THEN VALOARE ELSE 0 END) AS COLOANA1,
SUM(CASE WHEN COLOANA=2 THEN VALOARE ELSE 0 END) AS COLOANA2,
SUM(CASE WHEN COLOANA=3 THEN VALOARE ELSE 0 END) AS COLOANA3,
SUM(CASE WHEN COLOANA=4 THEN VALOARE ELSE 0 END) AS COLOANA4,
SUM(CASE WHEN COLOANA=5 THEN VALOARE ELSE 0 END) AS COLOANA5,
SUM(CASE WHEN COLOANA=6 THEN VALOARE ELSE 0 END) AS COLOANA6
FROM (
SELECT DATADOC, NRDOC, COLOANA,
SUBSTRING(N.CONTCR,NULLIF(CHARINDEX('.',N.CONTCR),0)+1,20) AS CODFURNIZOR,
SUM(LEI) as VALOARE
FROM NOTE_CONTABILE N INNER JOIN SCHEME S
ON N.CONTDB LIKE S.CONTDB+'%' AND N.CONTCR LIKE S.CONTCR+'%'
WHERE JURNAL='JC'
GROUP BY DATADOC, NRDOC, COLOANA,
SUBSTRING(N.CONTCR,NULLIF(CHARINDEX('.',N.CONTCR),0)+1,20)
) X LEFT JOIN PARTENERI P ON X.CODFURNIZOR=P.ID
GROUP BY DATADOC, NRDOC, NUME, CF
ORDER BY DATADOC, NRDOC
Ar fi de observat că dacă nu există un partener cu codul respectiv (sau dacă nu a fost precizat niciun analitic la contul de furnizori) atunci documentul respectiv apare în jurnalul de cumpărări, dar fără niciun nume.
Referitor la opinia mea despre cum ar trebui să fie construit un program de contabilitate, sunt de părere că tabelele şi view-urile care stau la baza rapoartelor ar putea arăta cam aşa (am simplificat mult, bineînţeles):
CREATE TABLE Furnizori (
ID_Furnizor int IDENTITY PRIMARY KEY,
Nume nvarchar(50) NOT NULL UNIQUE,
CUI varchar(15) NULL
)
CREATE TABLE Facturi (
ID_Factura int IDENTITY PRIMARY KEY,
Data smalldatetime NOT NULL,
Numar varchar(10) NOT NULL,
ID_Furnizor int NOT NULL REFERENCES Furnizori
)
CREATE TABLE TipuriOperatiuniCumparari (
TipOperatiune char(1) PRIMARY KEY,
DenumireOperatiune nvarchar(50) NOT NULL UNIQUE
)
INSERT INTO TipuriOperatiuniCumparari VALUES ('R',N'Bunuri destinate revânzării')
INSERT INTO TipuriOperatiuniCumparari VALUES ('C',N'Bunuri de capital')
INSERT INTO TipuriOperatiuniCumparari VALUES ('P',N'Bunuri şi servicii pt nevoi proprii')
-- şi alte tipuri, după necesităţi...
CREATE TABLE DetaliiFacturi (
ID_DetaliuFactura int IDENTITY PRIMARY KEY,
ID_Factura int NOT NULL
REFERENCES Facturi ON DELETE CASCADE,
Explicatii nvarchar(50) NULL,
ContDebitor varchar(20) NOT NULL,
ContTVA varchar(20) NOT NULL,
ContFurnizori varchar(20) NOT NULL,
TipOperatiune char(1) NOT NULL -- completat cu valori implicite în funcţie de ContDebitor
REFERENCES TipuriOperatiuniCumparari ON UPDATE CASCADE,
Valoare money NOT NULL,
ProcentTVA tinyint NOT NULL DEFAULT (19),
TVA money NOT NULL,
Total money NOT NULL,
CHECK (Valoare+TVA=Total),
-- ar mai trebui şi un constraint "TVA=Valoare*ProcentTVA/100", dar să permită mici diferenţe
)
GO
CREATE VIEW InregistrariContabileFacturi
AS
SELECT f.Data, f.Numar as NumarDocument, 'Factura' as TipDocument,
f.ID_Factura as ID_Document, 'V' as TipDetaliu, d.ID_DetaliuFactura as ID_DetaliuDocument,
d.ContDebitor AS CD, d.ContFurnizori as CC, d.Valoare,
ISNULL(d.Explicatii+' ','')+n.Nume as Explicatii
FROM DetaliiFacturi d
INNER JOIN Facturi f ON f.ID_Factura=d.ID_Factura
INNER JOIN Furnizori n ON f.ID_Furnizor=n.ID_Furnizor
UNION ALL
SELECT f.Data, f.Numar as NumarDocument, 'Factura' as TipDocument,
f.ID_Factura as ID_Document, 'T' as TipDetaliu, d.ID_DetaliuFactura as ID_DetaliuDocument,
d.ContTVA AS CD, d.ContFurnizori as CC, d.TVA,
'TVA '+ISNULL(d.Explicatii+' ','')+n.Nume as Explicatii
FROM DetaliiFacturi d
INNER JOIN Facturi f ON f.ID_Factura=d.ID_Factura
INNER JOIN Furnizori n ON f.ID_Furnizor=n.ID_Furnizor
WHERE d.TVA<>0
GO
CREATE VIEW InregistrariContabile
AS
SELECT Data, NumarDocument, TipDocument, ID_Document, TipDetaliu, ID_DetaliuDocument, CD, CC, Valoare, Explicatii
FROM InregistrariContabileFacturi
--UNION ALL alte view-uri pt celelalte tabele cu documente
GO
CREATE VIEW JurnalDeCumparari
AS
SELECT Data, Numar, Nume as Furnizor, CUI, x.*
FROM (
SELECT ID_Factura, SUM(Total) as Total,
SUM(CASE WHEN TipOperatiune='C' AND ProcentTVA=19 THEN Valoare ELSE 0 END) AS BazaCapital19,
SUM(CASE WHEN TipOperatiune='C' AND ProcentTVA=19 THEN TVA ELSE 0 END) AS TVACapital19,
SUM(CASE WHEN TipOperatiune='R' AND ProcentTVA=19 THEN Valoare ELSE 0 END) AS BazaMarfuri19,
SUM(CASE WHEN TipOperatiune='R' AND ProcentTVA=19 THEN Valoare ELSE 0 END) AS TVAMarfuri19,
SUM(CASE WHEN TipOperatiune='P' AND ProcentTVA=19 THEN Valoare ELSE 0 END) AS BazaDiverse19,
SUM(CASE WHEN TipOperatiune='P' AND ProcentTVA=19 THEN Valoare ELSE 0 END) AS TVADiverse19
-- şi aşa mai departe...
FROM DetaliiFacturi GROUP BY ID_Factura
) x INNER JOIN Facturi f ON x.ID_Factura=f.ID_Factura
INNER JOIN Furnizori n ON f.ID_Furnizor=n.ID_Furnizor
-- ORDER BY Data, Numar
Bineînţeles, pentru fiecare tip de document ar trebui să existe o tabelă separată, deci am putea să mai avem nişte tabele pentru chitanţe, pentru detaliile de factură care intră în gestiune (care sunt legate totuşi de acelaşi antet de factură), pentru facturile de vânzare şi detaliile lor, pentru bonuri (care sunt un fel de facturi care sunt şi chitanţe simultan, deci trebuie să apară şi în jurnalul de cumpărări, dar şi în registrul de casă), etc. În plus, am putea să mai avem o altă tabelă în care se vor introduce note contabile care nu sunt prevăzute într-un alt document (nota cu impozitul pe profit, de exemplu).
Răzvan