Temat: Kalkulator odsetek

Witam
Mam problem ze zrobieniem takie kalkulatora dla kilku faktur i wpłat.Może ktoś przerabiał coś podobnego i trochę mi pomoże.
Mam 2 tabele:
CREATE TABLE `naleznosci` (
`id` int(6) NOT NULL,
`faktura` varchar(255) default NULL,
`kwota` float(9,2) default '0.00',
`termin` date default NULL,
`idd` int(5) NOT NULL auto_increment,
PRIMARY KEY (`idd`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=cp1250;

INSERT INTO `naleznosci` VALUES ('1', 'xxxx', '10.00', '2015-06-08', '1');
INSERT INTO `naleznosci` VALUES ('1', 'yyyy', '5.00', '2015-11-18', '2');
INSERT INTO `naleznosci` VALUES ('1', 'zzzz', '5.00', '2005-10-01', '3');

i
CREATE TABLE `wplaty` (
`id` int(11) NOT NULL auto_increment,
`naleznosci_id` int(11) NOT NULL,
`kwota` float(10,2) default NULL,
`data_wplaty` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=cp1250;

INSERT INTO `wplaty` VALUES ('1', '1', '50.00', '2015-11-16');
INSERT INTO `wplaty` VALUES ('2', '1', '4.00', '2006-07-14');

Problem polega na tym że z tych 2 muszę uzyskać trzecią tabele w której będa pola:
naleznosci.id,naleznosci.faktura,naleznosci.kwota,naleznosci.termin,wplaty.kwota,wplaty.data_wplaty

ale z pewnymi warunkami (wpłaty nie są na faktury, trzeba je zaliczać na najstarszą):
- jeśli kwota wpłaty jest równa kwocie faktury to wszystko mamy w jednym wierszu
- jeśli kwota jest większa od faktury do wypełniamy ją do wysokości faktury a resztę przenosimy na drugą fakturę (do kolejnego wiersza)
- kolejną wpłatę (w trzecim wierszu) wpisujemy na tą fakturę która jeszcze nie jest dopełniona (oczywiście umniejszoną o resztę wcześniejszej wpłaty) itd.

Jest to dość skomplikowana operacja i nie bardzo mogę sobie z nią poradzić. Próbowałem coś na kursorach ale poległem Może ktoś coś pomoże.

Temat: Kalkulator odsetek

Nie rozumiem tej struktury trochę.
naleznosci.id masz sparowane z wplaty.nazleznosci_id czyli wynika z tego że zakładasz dla określonej liczby faktur określoną liczbę wpłat. Ale pod kodem SQL piszesz że nie są sparowane i trzeba parować narastająco. Potem tabela która będzie przechowywała kawałek z naleznosci i kawalek z wplat. hmm... Zasadniczy problem jaki tu widzę to brak informacji w nowej tabeli z jakich wpłat była pokryta dana faktura. Jeżeli dana faktura pokryta została np z 3 wpłat do która data_wplaty będzie cię interesowała?

Może warto dodać tabelę

CREATE TABLE naleznosci_wplaty(

  naleznosci_idd INT(5),

  wplaty_id INT(11),

  kwota FLOAT(10,2);

która będzie przechowywała informacje jaka część danej wpłaty pokrywa daną fakturę. Potem wystarczy zaimplementować algorytm parujący i przy jakiejkolwiek zmianie wiersza w którejkolwiek z tabel nazeznosci, wplaty wykonywać parowanie ponownie od nowa (trigger) . Wtedy zawsze wiem jaki kawałek i której wpłaty pokrywa jaką należność a wygenerowanie takiej tabeli dostajesz z prostego zapytania SQL albo robisz widok który do przygotuje.

Proponuję takie rozwiązanie bo możesz potem jednoznacznie sprawdzić lewo <=> prawo parowania i namierzać ewentualne błędy. Nie będzie problemu z namierzeniem tego jednego grosza jak ktoś będzie twierdził, że nie zalega. Dodatkowo wiesz która faktura nie jest pokryta w jakiej wysokości i możesz naliczać odsetki od konkretnej kwoty. Dalej mając takie przyporządkowania możesz wstecznie obliczyć czy naliczone odsetki się zgadzają, W takim wypadku możesz także sprawdzić ile być zarobił/stracił na odsetkach w przypadku zmiany stopy naliczanych odsetek.

Możesz też zrobić szereg zabezpieczeń, np blokada możliwości zmiany faktur/wpłaty jeżeli wpłata została sparowana z fakturą zapłaconą w całości.

Pytanie tylko do finansisty/księgowego czy tak można zrobić z ponownym parowaniem po pod maską są jeszcze księgowania wpłaty.

Dokładnie to co opisujesz wykonywałem w PostgreSQL ale tam zaprzęgnąłem plperl'a do pracy bo potrzebne były tablice na składowanie danych tymczasowych np własnie informacji ile z danej wpłaty mogę jeszcze wykorzystać pokrywając daną fakturę i w jakim stopniu faktura została pokryta wpłatami. Tu za każdą prośbą o dane wykonywane było parowanie dynamiczne bo zawsze dane mogły w międzyczasie ulec zmianie i dodatkowa tabelka nie wchodziła w grę.

Temat: Kalkulator odsetek

Ciekawie to opisałeś.Ja mam do dyspozycji tylko Mysql. Kolumna naleznosc.id i wplaty.naleznosc_id (trochę niefortunna nazwa) identyfikują klienta (w bazie mam kilka tysięcy klientów).Każdy Klient ma "x" faktur i "y" wpłat (albo nie ma żadnej).Niestety ale wpłaty na etapie importu do bazy nie są sparowane z numerem faktury tylko z id Klienta.Dlatego zdecydowałem się zaliczać wpłaty na najstarsze faktury.Ta dodatkowa tabela ma się właśnie stworzyć z właściwego zaliczenia wpłat na faktury.Jeśli wpłata pokrywa całą fakturą to nie ma problemu bo mamy kwotę i datę wpłaty (do kiedy naliczać odsetki).Jeśli wpłata jest większa niż faktura to reszta z tej wpłaty ma się przepisać na drugą fakturę.Gdy faktura została pokryta z kilku wpłat to ja rozumiem to tak:
1 rekord nr faktury,kwota faktury,kwota wpłaty,data wymagalności,data wpłaty
2 rekord ten sam nr faktury,kwota faktury pomniejszona o poprzednią wpłatę,kwota nowej wpłaty,data wymagalności taka jak poprzednia wpłata, i data nowej wpłaty i tak do pokrycia całej pierwotnej wartości faktury.W ten sposób odsetki będą prawidłowo naliczone przy zmniejszaniu zadłużenia danej faktury-tak mi się w każdym razie wydaje.

Temat: Kalkulator odsetek

W idealnym świecie może by i tak zadziałało ale wprowadzam czynnik ludzki i wystarczy jedna zmiana daty wpłaty i tabelka się sypie. Jeżeli zmieni ci się jakaś dana po stronie fv albo wpłata (ktoś się pomylił...) to będziesz musiał całą tabelę generować jeszcze raz.
Zaproponowałem sparowanie fv z wpłatą oraz podanie kwoty parowania bo potem wszystkie dane tak jak przedstawiasz możesz wygenerować na bieżąco widokiem. Gdzieś się zmieni dana to od razu masz aktualizację.

Jeśli chodzi o same odsetki to chyba wybrałbym opcję, która przechowuje listę naliczonych odsetek w cyklu dziennym do danej fv. Przechowywałbym kwotę od jakiej naliczam, % oraz naliczoną kwotę jaka mi się należy za dany dzień. Dodatkowo kwotę odsetek trzymałbym przy fv. Po opłaceniu całej fv wraz z odsetkami (sprawa zamknięta) kasowałbym historię naliczanych odsetek.

Widzę to tak:

CREATE TABLE odsetki_fv(

  naleznosci_id int,

  dzien_naliczenia DATE

  zalegla_naleznosc float(10,2),

  procent int,

  kara float(10,2)

);

Codziennie dla kazdej fv naliczam ile mi sie za ten dzien nalezy. Potem robie dla kazdej fv sume z naliczonych odsetek i wpisuje do kolumny w tabeli naleznosci.

Może toporne ale na pewno nie czułe na wszelakie zmiany w konfiguracji oraz pamięta jak to było w momencie naliczania. Dodatkowo w czasie można zmieniać parowania, daty, kwoty itp a odsetki pozostaną takie same. Minus taki, że nie można ich ponownie przeliczyć.

Temat: Kalkulator odsetek

To ci powinno pomóc rozwiązać problem. Zrobiłem tak aby nie trzeba było używać funkcji czy pętli. Ostatni select to chyba to co cię interesuje. Przykład dla PostgreSQL (aby przemapować na mySQL chyba trzeba tylko usunąć ::FLOAT):

DROP TABLE IF EXISTS odsetki_fv;

DROP TABLE IF EXISTS naleznosci_wplaty;

DROP TABLE IF EXISTS naleznosci;

DROP TABLE IF EXISTS wplaty;

DROP TABLE IF EXISTS kontrahenci;



CREATE TABLE kontrahenci(

  id INT PRIMARY KEY,

  nazwa TEXT,

  nazwisko TEXT,

  imie TEXT

);



INSERT INTO kontrahenci VALUES (1, 'Firma Sp. z o. o.', NULL, NULL);

INSERT INTO kontrahenci VALUES (2, NULL, 'Kowalski', 'Jan');



CREATE TABLE naleznosci(

  id INT PRIMARY KEY,

  id_kontrahent INT,

  numer TEXT,

  kwota INT, -- kwota w groszach

  odsetki INT, -- odsetki w groszach

  termin DATE,

  CONSTRAINT naleznosci_fkey FOREIGN KEY (id_kontrahent)

    REFERENCES kontrahenci (id) MATCH SIMPLE

    ON UPDATE CASCADE ON DELETE RESTRICT

);



INSERT INTO naleznosci VALUES (1, 1, 'FV/12/2010', 9999, 0, '2015-10-01');

INSERT INTO naleznosci VALUES (2, 1, 'FV-X/12/2012', 999, 0, '2015-12-01');

INSERT INTO naleznosci VALUES (3, 2, 'ER-12', 12345, 0, '2015-09-01');





CREATE TABLE wplaty(

  id INT PRIMARY KEY,

  id_kontrahent INT,

  kwota INT, -- kwota w groszach

  data_wplaty DATE,

  CONSTRAINT wplaty_fkey FOREIGN KEY (id_kontrahent)

    REFERENCES kontrahenci (id) MATCH SIMPLE

    ON UPDATE CASCADE ON DELETE RESTRICT

);



INSERT INTO wplaty VALUES (1, 1, 5000, '2015-09-01');

INSERT INTO wplaty VALUES (2, 1, 5998, '2015-11-01');

INSERT INTO wplaty VALUES (4, 2, 2345, '2015-09-01');

INSERT INTO wplaty VALUES (5, 2, 5000, '2015-09-10');



CREATE TABLE naleznosci_wplaty(

  id_naleznosci INT,

  id_wplaty INT,

  kwota INT, -- kwota w groszach

  CONSTRAINT naleznosci_wplaty_pkey PRIMARY KEY (id_naleznosci, id_wplaty),

  CONSTRAINT naleznosci_wplaty_fkey FOREIGN KEY (id_naleznosci)

    REFERENCES naleznosci (id) MATCH SIMPLE

    ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT naleznosci_wplaty_fkey2 FOREIGN KEY (id_wplaty)

    REFERENCES wplaty (id) MATCH SIMPLE

    ON DELETE RESTRICT ON UPDATE CASCADE

);



INSERT INTO naleznosci_wplaty VALUES (1, 1, 5000);

INSERT INTO naleznosci_wplaty VALUES (1, 2, 4999);

INSERT INTO naleznosci_wplaty VALUES (2, 2, 999);

INSERT INTO naleznosci_wplaty VALUES (3, 4, 2345);

INSERT INTO naleznosci_wplaty VALUES (3, 5, 5000);



CREATE TABLE odsetki_fv(

  id_naleznosci INT,

  kwota_bazowa INT, -- kwota w groszach

  kwota_odsetek INT, -- kwota w groszach

  procent INT,

  data_naliczenia DATE,

  CONSTRAINT odsetki_fv_pkey PRIMARY KEY (id_naleznosci),

  CONSTRAINT odsetki_fv_fkey FOREIGN KEY (id_naleznosci)

    REFERENCES naleznosci (id) MATCH SIMPLE

    ON DELETE RESTRICT ON UPDATE CASCADE

);



-- lista fv

SELECT n.*, CONCAT(k.nazwa, k.nazwisko, k.imie) AS nazwa FROM naleznosci n JOIN kontrahenci k ON n.id_kontrahent = k.id;



-- lista wplat

SELECT w.*, CONCAT(k.nazwa, k.nazwisko, k.imie) AS nazwa FROM wplaty w JOIN kontrahenci k ON w.id_kontrahent = k.id;



-- lista fv + kwota zaleglosci

SELECT 

  n.id, CONCAT(k.nazwa, k.nazwisko, k.imie) AS nazwa, n.numer, n.termin, n.kwota/100::FLOAT AS kwota, 

  n.odsetki/100::FLOAT AS odsetki, (n.kwota + n.odsetki - SUM(nw.kwota))/100::FLOAT AS do_zaplaty

FROM naleznosci n 

JOIN kontrahenci k ON n.id_kontrahent = k.id

LEFT JOIN naleznosci_wplaty nw ON n.id = nw.id_naleznosci

GROUP BY n.id, k.nazwa, k.nazwisko, k.imie, n.numer, n.termin

;



-- lista fv + rozklad wplat

SELECT 

  n.id, CONCAT(k.nazwa, k.nazwisko, k.imie) AS nazwa, n.numer, n.termin, n.kwota/100::FLOAT AS kwota, 

  n.odsetki/100::FLOAT AS odsetki, w.data_wplaty, nw.kwota/100::FLOAT AS kwota_parowania

FROM naleznosci n 

JOIN kontrahenci k ON n.id_kontrahent = k.id

LEFT JOIN naleznosci_wplaty nw ON nw.id_naleznosci = n.id

JOIN wplaty w on nw.id_wplaty = w.id

ORDER BY n.id, w.data_wplaty

;



-- lista fv + rozklad wplat z malejacymi kwotami do zaplaty

SELECT 

  n.id, CONCAT(k.nazwa, k.nazwisko, k.imie) AS nazwa, n.numer, n.termin, n.kwota/100::FLOAT AS kwota, 

  n.odsetki/100::FLOAT AS odsetki, w.data_wplaty, nw.kwota/100::FLOAT AS kwota_parowania,

  (n.kwota - nw.kwota - COALESCE(nn.kwota, 0))/100::FLOAT AS pozostalo_do_zaplaty

FROM naleznosci n 

JOIN kontrahenci k ON n.id_kontrahent = k.id

LEFT JOIN naleznosci_wplaty nw ON nw.id_naleznosci = n.id

JOIN wplaty w on nw.id_wplaty = w.id

LEFT JOIN naleznosci_wplaty nn ON nn.id_naleznosci = nw.id_naleznosci AND nn.id_wplaty < nw.id_wplaty

GROUP BY 

  n.id, k.nazwa, k.nazwisko, k.imie, n.numer, w.data_wplaty, nw.kwota, nn.kwota

ORDER BY n.id, w.data_wplaty;

Temat: Kalkulator odsetek

A jeszcze pytanie.W jaki sposób uzyskujesz dane do tabeli naleznosci_wplaty? Ja w bazie nie mam wpłat na należności (id_należności) tylko na Klienta (id_Klienta).I właśnie chodzi o to jak stworzyć tabele naleznosci_wplaty automatycznie.Ten post został edytowany przez Autora dnia 22.02.16 o godzinie 12:12

Temat: Kalkulator odsetek

A to na tabeli z wpłatami robię wyzwalacz i implementuję parowanie według algorytmu otrzymanego (wyproszonego) od biznesu. To jest niestety do zaimplementowania ale każde dodanie/zmiana wiersza wykonuje parowanie.

W PostgreSQL napisałbym sobie funkcje np plperl która mi to ładnie rozbije i uporządkuje. Z uwagi na to, że w mySQL nie wymiatam to pewnie zrobił bym sobie skrypt który mi to rozparuję a następnie SQL-em sprawdziłbym lewo <=> prawo czy się kwotowo zgadza. Jednorazowo byłoby ok ale na dłuższą metę nie jest dobry pomysł.
Myślę, że poświęciłbym trochę czasu i zrobiłbym funkcję czyto mySQL-ową na zmiennych @ bo wiem, że mySQL to posiada.
Dla kogoś, kto codziennie pracuje z mySQL implementacja nie powinna stanowić problemu. Ja musiałbym chwilę dłużej posiedzieć.

Temat: Kalkulator odsetek

No tak tylko nikt oprócz Ciebie się nie zaangażowała w dyskusję.