Aktualna wersja: 19.08.21


Definiowane Funkcje Użytkownika - obliczenia SQL


W poprzednim przykładzie opisano podstawy budowania funkcji użytkownika w Arkuszu Kalkulacyjnym.

W tym dokumencie zaprezentujemy wykorzystanie UDF do obliczeń z bazą danych SQL systemu ERP. Dla uproszczenia funkcji, przykład wykorzystuje bazę danych programu księgowego do obsługi mikro firmy Sz@rk Księga Express.

W swoich autorskich rozwiązaniach zalecamy wykorzystanie tego mechanizmu w połączeniu z bazami programów Finansowo Księgowych, Magazynowych i Płacowych.


Jeżeli jesteś analitykiem lub księgowym poproś informatyka w swojej firmie o przygotowanie odpowiedniego skryptu.


Za pomocą funkcji możesz wykonywać dynamiczne analizy księgowe w trybie rzeczywistym, zawsze na aktualnych danych: np. Rachunek zysków i strat, obliczenia do CIT, ZUS, VAT, aktualne zużycie materiałów i surowców, itp.


Funkcja użytkownika Excel UDF obliczenia SQL

Zdjęcie prezentuje zapisy księgowe w programie Księga Express PRO, na który nałożone jest zdjęcie Arkusza z użytą funkcją KPiR().

Arkusz w trybie rzeczywistym oblicza aktualne wartości kolumn Księgi z podanego okresu czasu (w przykładzie wg miesięcy roku 2018).

Po analizie w/w przykładu widać jak w prosty i szybki sposób można wykonywać skomplikowane dynamiczne analizy na podstawie zewnętrznych danych pobieranych z systemów ERP. Dla informatyka (programisty), który zna wykorzystywany system Księgowy, stworzenie odpowiednich funkcji nie powinno być dużym wyzwaniem. Natomiast dział księgowości może otrzymać profesjonalne narzędzie analityczne bez ponoszenia dodatkowych kosztów.

Doskonałym przykładem na wykorzystanie tego mechanizmu będzie przygotowanie odpowiedniej funkcji dla programu FK, która potrafi obliczyć Obroty i Salda Wn/Ma podanych kont księgowych z wybranego okresu czasu. Możemy wykonywać sprawozdania finansowe oraz analizy porównawcze w poszczególnych latach.


W firmach wielooddziałowych otrzymamy zbiorcze obroty z poszczególnych baz danych (firm), jeżeli zdefiniujemy kilka połączeń SQL. Identyfikator połączenia może być oczywiście parametrem naszej funkcji.


Pamiętaj, po zdefiniowaniu funkcji dział Księgowości nie musi w ogóle zaglądać do zakładki ze skryptami funkcji, te będą ładowane automatycznie po otwarciu arkusza.


using System;
using TGSoft.DB;
using TGSoft.UI.XLS;
using TGSoft.Utils;

namespace Szark
{
    public class CustomFunctions
    {
        private MsSql ms;

        public CustomFunctions(SSController sender)
        {
            ms = new MsSql(1);
        }
        
        public double KPiR(object rok, object mc1, object mc2, object col)
        {
            int iCol = col.ToInt();
            string columnName;
            
            switch (iCol)
            {
                case 7: columnName = "sprzedaz"; break;
                case 8: columnName = "pozostale_przychody"; break;

                case 10: columnName = "zakup_towarow"; break;
                case 11: columnName = "koszty_uboczne"; break;
                case 12: columnName = "wynagrodzenia"; break;
                case 13: columnName = "pozostale_wydatki"; break;
                default: return 0;
            }

            int r = rok.ToInt();
            int m1 = mc1.ToInt();
            int m2 = mc2.ToInt();
            DateTime dt1 = new DateTime(r, m1, 1);
            DateTime dt2 = new DateTime(r, m2, DateTime.DaysInMonth(r, m2));
            string sql = string.Format("SELECT SUM({0}) AS v1 FROM ke_ksiega WHERE data BETWEEN {1} AND {2}",
                            columnName, SQLut.DateToSql(dt1), SQLut.DateToSql(dt2));

            object x = ms.GetScalar(sql);
            return x.ToDouble();
        }
    }
}