środa, 4 grudnia 2013

Znaki specjalne, a wersja 2013.X

Jakiś czas po konwersji do wersji 2013 zauważyłem, iż niektóre towary przestały spełniać warunki pewnej filtracji, co do tej pory normalnie działało. Jak się okazało wersja 2013 ma nową funkcjonalność polegającą na wklejaniu w kod towaru znaku ASCII [US], czyli separatora.
Dla przykładu:
SELECT 'TEST1' AS [String1], 'TEST' + CHAR(31) + '2'  AS [String2]

da nam wynik 


co na pierwszy rzut oka wydaje się być poprawne i tak jest, tak długo jak zrobiliśmy to świadomie. W przypadku wprowadzania przez użytkowników kodów w kartotekach towarowych ten warunek niekoniecznie jest spełniony.

Dla pewności napisałem więc funkcję, która zamienia znaki specjalne na coś bardziej czytelniejszego:

CREATE FUNCTION [CDN].[PokazUkryteZnaki] (@tekst VARCHAR(8000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE @Wynik VARCHAR(8000);
   SET @Wynik = @tekst

   SET @Wynik = REPLACE( @Wynik, CHAR(1),  '[SOH]')
   SET @Wynik = REPLACE( @Wynik, CHAR(2),  '[STX]')
   SET @Wynik = REPLACE( @Wynik, CHAR(3),  '[ETX]')
   SET @Wynik = REPLACE( @Wynik, CHAR(4),  '[EOT]')
   SET @Wynik = REPLACE( @Wynik, CHAR(5),  '[ENQ]')
   SET @Wynik = REPLACE( @Wynik, CHAR(6),  '[ACK]')
   SET @Wynik = REPLACE( @Wynik, CHAR(7),  '[BEL]')
   SET @Wynik = REPLACE( @Wynik, CHAR(8),  '[BS]')
   SET @Wynik = REPLACE( @Wynik, CHAR(9),  '[HT]')
   SET @Wynik = REPLACE( @Wynik, CHAR(10), '[LF]')
   SET @Wynik = REPLACE( @Wynik, CHAR(11), '[VT]')
   SET @Wynik = REPLACE( @Wynik, CHAR(12), '[FF]')
   SET @Wynik = REPLACE( @Wynik, CHAR(13), '[CR]')
   SET @Wynik = REPLACE( @Wynik, CHAR(14), '[SO]')
   SET @Wynik = REPLACE( @Wynik, CHAR(15), '[SI]')
   SET @Wynik = REPLACE( @Wynik, CHAR(16), '[DLE]') 
   SET @Wynik = REPLACE( @Wynik, CHAR(17), '[DC1]')
   SET @Wynik = REPLACE( @Wynik, CHAR(18), '[DC2]')
   SET @Wynik = REPLACE( @Wynik, CHAR(19), '[DC3]')
   SET @Wynik = REPLACE( @Wynik, CHAR(20), '[DC4]')
   SET @Wynik = REPLACE( @Wynik, CHAR(21), '[NAK]')
   SET @Wynik = REPLACE( @Wynik, CHAR(22), '[SYN]')
   SET @Wynik = REPLACE( @Wynik, CHAR(23), '[ETB]')
   SET @Wynik = REPLACE( @Wynik, CHAR(24), '[CAN]')
   SET @Wynik = REPLACE( @Wynik, CHAR(25), '[EM]')
   SET @Wynik = REPLACE( @Wynik, CHAR(26), '[SUB]')
   SET @Wynik = REPLACE( @Wynik, CHAR(27), '[ESC]')
   SET @Wynik = REPLACE( @Wynik, CHAR(28), '[FS]')
   SET @Wynik = REPLACE( @Wynik, CHAR(29), '[GS]')
   SET @Wynik = REPLACE( @Wynik, CHAR(30), '[RS]')
   SET @Wynik = REPLACE( @Wynik, CHAR(31), '[US]')
   --SET @Wynik = REPLACE( @Wynik, CHAR(32), '[SP]')   -- SPACJA

   RETURN(@Wynik)
END
GO

W efekcie jej wywołania, nasze kody wyglądają już trochę inaczej:

Dla towarów można napisać coś takiego:

SELECT CDN.PokazUkryteZnaki(Twr_Kod) AS KodPelny,Twr_Kod AS Kod
FROM CDN.TwrKarty
WHERE Twr_Archiwalny = 0
AND CDN.PokazUkryteZnaki(Twr_Kod) <> Twr_Kod

Powyższe zapytanie spowoduje wyświetlenie listy towarów których kod zawiera niepożądany znak specjalny. Warto zauważyć, że w ciele funkcji wykomentowany jest fragment zamieniający "spację" na [SP], ponieważ w moim przypadku znak spacji jest dopuszczalny w kodzie towaru i nie było potrzeby wyszukiwania towarów, które ją zawierają. W bazie którą sprawdzałem zapytanie zwróciło skromne 70 kartotek, a wszystkie zostały zmodyfikowane po wgraniu wersji 2013.5 (nowy interface). Wychodzi więc na to, że nowa wersja XL-a nie ignoruje znaków specjalnych i użytkownik może wprowadzić co tylko wymyśli :)

Pozdrawiam,
Mateusz

czwartek, 28 lutego 2013

DataGridView Performance

Jak zapewne wiadomo, korzystanie ze standardowej kontrolki DataGridView dostępnej w .NET często wiąże się z problemami z jej wydajnością. Pomijając już kwestię zaawansowanej optymalizacji, czy też wyłączania efektów wizualnych (w końcu nie chodzi o to żeby coś było brzydkie) najprościej stworzyć klasę:
public static class ExtensionMethods
    {
        public static void DoubleBuffered(this DataGridView dgv, bool setting)
        {
            Type type = dgv.GetType();
            PropertyInfo pi = type.GetProperty("DoubleBuffered", BindingFlags.Instance | BindingFlags.NonPublic);
            pi.SetValue(dgv, setting, null);
        }
    }

Następnie w konstruktorze, po inicjalizacji kontrolek, umieszczamy taki kawałek kodu:
ExtensionMethods.DoubleBuffered(MyGridName, true);

Jak można się domyślać po "DoubleBuffered", nasz DataGridView powinien teraz działać znacznie szybciej :)

Pozdrawiam

środa, 30 stycznia 2013

Reaktywacja

Minęło sporo czasu od ostatniego wpisu, jako że po prostu zaniedbałem to co niegdyś zacząłem. Niemniej jednak postaram się ożywić ten blog i mam nadzieje, że poruszę kilka interesujących tematów.

Dzisiejszy wpis to coś niezbyt trudnego, związanego z problemem z którym borykał się jeden ze znajomych użytkowników XL-a. Idea była taka, aby podczas wywoływania wydruku faktury pokazywał się parametr "opis" z domyślną wartością wziętą z opisu tego konkretnego dokumentu FS. Problemem jest fakt, że w momencie wywołania wydruku nie ma jak się dobrać do GID-u faktury na której stoimy (pomijam doklejanie do filtra obowiązkowego czegoś na zasadzie 1=1, gdzie jedynki to GID-y, które potem można powycinać). W tym przypadku z pomocą może nam przyjść pole TrN_Aktywny, dzięki któremu możemy stworzyć parametr na zasadzie poniższego przykładu:

@PAR ?@S255|Opis|&Opis:{GetSQL('SELECT TnO_Opis FROM CDN.TraNag INNER JOIN CDN.TrNOpisy ON CDN.TraNag.TrN_GIDNumer=CDN.TrNOpisy.TnO_TrnNumer INNER JOIN CDN.Sesje ON CDN.TraNag.TrN_Aktywny=CDN.Sesje.SES_SesjaID AND SES_OpeIdent = '''& XLOpeIdent() &'''')} @? PAR@

Coś takiego spowoduje, że na oknie z parametrami, ten zdefiniowany powyżej będzie mieć domyślną wartość pobraną z opisu faktury, na której właśnie stoimy (Warto zaznaczyć, że w przypadku otwarcia kilku faktur jednocześnie przez jednego usera, warto się zabezpieczyć jakąś funkcją agregującą i pamiętać o tym, iż będzie to tylko ochrona przed błędem, a nie zwracanie dobrej wartości). Sposób ten jest o tyle fajny, że pozwala nam w parametrach wstawiać domyślne wartości pobrane z dowolnego miejsca w bazie które jest powiązane z fakturą na której użytkownik się znajduje (np. wartość atrybutu, który jest wypełniony na tej fakturze).

Dziękuje za zainteresowanie i do "poczytania" wkrótce :)

poniedziałek, 31 stycznia 2011

ShowDialog() i Vista :)

Dawno nic tutaj nie wrzuciłem, aczkolwiek dzisiaj sam z siebie znalazł się ciekawy temat. Pisząc dodatek mający za zadanie import danych do XL ze wskazanego pliku natknąłem się na pewien problem. Coś normalnego dla XP:
 OpenFileDialog dialog = new OpenFileDialog();
 if (dialog.ShowDialog() == DialogResult.OK)
       {
        }

zawiesza XL w Viście. Okazało się, że OpenFileDialog korzysta z działającego już wątku - nie tworzy nowego, a co za tym idzie zawiesza moduł XL-a. Obejściem jest stworzenie osobnego wątku, który po wywołaniu otworzy nam nasze okno :) Przykład:
        private static DialogResult WywolajDialog(FileDialog dia)
        {
            DialogResult wynik = DialogResult.None;
            Thread t = new Thread(new ThreadStart(delegate
            {
                wynik = dia.ShowDialog();
            }));
            t.SetApartmentState(ApartmentState.STA);
            t.Start();
            t.Join();
            return wynik;
        } 

Następnie możemy już bez problemu wywołać nasze okno dialogowe:
OpenFileDialog dialog = new OpenFileDialog();
if (WywolajDialog(dialog) == DialogResult.OK)
                {
                }


Jak ktoś się pewnie zorientował po ApartmentState.STA, jeżeli mielibyśmy standardowy WindowsForm, a nie bibliotekę dla XL wystarczyłoby przed main() użyć [STAThread].

poniedziałek, 13 grudnia 2010

Filtr dla dokumentów

Na prośbę kolegi z forum XL, zamieszczam filtr dla dokumentów handlowych z możliwością zawężenia również do operatora.

@PAR ?@O(FS:2033|FSK:2041|FSL:1824|KSL:1832|RA:2035)|Typ_dokumentu|&Typ_dokumentu:REG=1 @? @MSG(Typ Dokumentu) @TIP(Wybierz typ dokumentu) PAR@
@PAR ?@X|Spinacz|&Czy Spinacz ?:REG=0 @? @H({??Typ_dokumentu <> 2033}) PAR@
@PAR ?@X|CzyOperator|&Filtrowanie wg Operatora:REG=0 @? PAR@
@PAR ?@R(SELECT Ope_GIDNumer, Ope_Ident from CDN.OpeKarty WHERE Ope_Zablokowane = 0 ORDER BY Ope_Ident)|Operator|&Operator:REG=ADMIN @? @H({??CzyOperator=0}) PAR@

Trn_GIDNumer IN
(
SELECT Trn_GIDNumer FROM CDN.Tranag
WHERE Trn_GIDTyp = ??Typ_dokumentu
AND
Trn_SpiTyp = CASE ??Spinacz WHEN 1 THEN 0 ELSE 2033 END
AND
Trn_OpeNumerW = CASE ??CzyOperator WHEN 1 THEN ??Operator ELSE Trn_OpeNumerW END
)

czwartek, 9 grudnia 2010

Filtr dla płatności

W nawiązaniu do tematu z forum, które nieraz tutaj wspominam, napisałem filtr (mnie też się przyda :) ), dla dokumentów sprzedażowych ze względu na płatności i ich termin.

@PAR ?@O(Gotówka:10|Przelew:20|Kredyt:30|Czek:40|Karta:50)|Forma|&Forma:REG=1 @? PAR@
@PAR ?@X|Opcja|&Uwzględniać termin:REG=0 @? PAR@
@PAR ?@O(Równy:1|Większy niż:2|Mniejszy niż:3)|Porownanie|&Termin:REG=1 @? @H({??Opcja = 0}) PAR@
@PAR ?@N3|Dni|&Dni:REG=0 @? @H({??Opcja = 0}) @RH(999) PAR@

TrN_GIDNumer IN
(

SELECT    DISTINCT Trn_GIDNumer
FROM         CDN.TraNag INNER JOIN
                      CDN.TraPlat ON CDN.TraNag.TrN_GIDNumer = CDN.TraPlat.TrP_GIDNumer AND CDN.TraNag.TrN_GIDTyp = CDN.TraPlat.TrP_GIDTyp
WHERE
Trp_FormaNr = ??Forma
AND
Trp_Termin - Trn_Data2 = CASE ??Opcja WHEN 0 THEN Trp_Termin - Trn_Data2 ELSE CASE ??Porownanie WHEN 1 THEN ??Dni ELSE Trp_Termin - Trn_Data2 END END
AND
Trp_Termin - Trn_Data2 > CASE ??Opcja WHEN 0 THEN -100 ELSE
CASE ??Porownanie WHEN 1 THEN Trp_Termin - Trn_Data2 - 1 WHEN 2 THEN ??Dni WHEN 3 THEN
CASE WHEN ??Dni - (Trp_Termin - Trn_Data2) > 0 THEN -100 ELSE ??Dni END END END

)


Co my tu mamy ? Jak widać filtrujemy po formie płatności, a następnie możemy określić zadaną przez nas liczbę dni i porównać do terminu na dokumencie. ( = > < ). Zapewne rzuca się w oczy, iż zakroiłem formę płatności do 5 pre-definiowanych form - jeżeli chcecie mieć ich więcej, to wystarczy zmienić typ parametru forma na listę rozwijaną i napisać odpowiednie zapytanie (np. dla tabeli CDN.Konfig o Kon_Numer = 736).

środa, 8 grudnia 2010

Zestawienie modułów XL

W przeciwieństwie do Optimy, XL nie przechowuje wartości Bool w osobnych kolumnach dla każdego modułu zaznaczonego na operatorze, a jedynie określa dane moduły za pomocą jednej wartości liczbowej. Zapewne jest gdzieś procedura, która to zwraca, niemniej jednak jako Pan "zrób to sam" napisałem kawałek kodu, który zwróci nam zestawienie analogiczne jak Optimowa tabela.

DECLARE @Wynik TABLE
(Ope_GID INT,
Ope_Ident VARCHAR(50),
Ope_Nazwisko VARCHAR(50),
Place INT,
Produkcja INT,
Adm_Oddzialow INT,
Serwis INT,
Zamowienia INT,
Administrator INT,
CRM INT,
BI INT,
Srodki INT,
Kompletacja INT,
Ksiegowosc INT,
Sprzedaz INT
)
DECLARE @OpeGID INT
DECLARE @OpeIdent VARCHAR(10)
DECLARE @OpeNazwisko VARCHAR(100)
DECLARE @OpeModuly INT

DECLARE @Place INT
DECLARE @Produkcja INT
DECLARE @Adm_Oddzialow INT
DECLARE @Serwis INT
DECLARE @Zamowienia INT
DECLARE @Administrator INT
DECLARE @CRM INT
DECLARE @BI INT
DECLARE @Srodki INT
DECLARE @Kompletacja INT
DECLARE @Ksiegowosc INT
DECLARE @Sprzedaz INT




DECLARE k CURSOR
FOR
SELECT Ope_GIDNumer, Ope_IDENT, Ope_Nazwisko, Ope_Moduly
FROM CDN.OpeKarty
WHERE Ope_Zablokowane = 0


OPEN k

FETCH NEXT FROM k
INTO @OpeGID, @OpeIdent, @OpeNazwisko, @OpeModuly

WHILE @@FETCH_STATUS = 0
BEGIN

IF @OpeModuly >= 67108864
BEGIN
SET @Place = 1
SET @OpeModuly = @OpeModuly - 67108864
END
ELSE
BEGIN
SET @Place = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 262144
BEGIN
SET @Produkcja = 1
SET @OpeModuly = @OpeModuly - 262144
END
ELSE
BEGIN
SET @Produkcja = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 4096
BEGIN
SET @Adm_Oddzialow = 1
SET @OpeModuly = @OpeModuly - 4096
END
ELSE
BEGIN
SET @Adm_Oddzialow = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 1024
BEGIN
SET @Serwis = 1
SET @OpeModuly = @OpeModuly - 1024
END
ELSE
BEGIN
SET @Serwis = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 512
BEGIN
SET @Zamowienia = 1
SET @OpeModuly = @OpeModuly - 512
END
ELSE
BEGIN
SET @Zamowienia = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 128
BEGIN
SET @Administrator = 1
SET @OpeModuly = @OpeModuly - 128
END
ELSE
BEGIN
SET @Administrator = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 64
BEGIN
SET @CRM = 1
SET @OpeModuly = @OpeModuly - 64
END
ELSE
BEGIN
SET @CRM = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 16
BEGIN
SET @BI = 1
SET @OpeModuly = @OpeModuly - 16
END
ELSE
BEGIN
SET @BI = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 8
BEGIN
SET @Srodki = 1
SET @OpeModuly = @OpeModuly - 8
END
ELSE
BEGIN
SET @Srodki = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 4
BEGIN
SET @Kompletacja = 1
SET @OpeModuly = @OpeModuly - 4
END
ELSE
BEGIN
SET @Kompletacja = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 2
BEGIN
SET @Ksiegowosc = 1
SET @OpeModuly = @OpeModuly - 2
END
ELSE
BEGIN
SET @Ksiegowosc = 0
END
----------------------------------------------------------------------------------------------------------------
IF @OpeModuly >= 1
BEGIN
SET @Sprzedaz = 1
SET @OpeModuly = @OpeModuly - 1
END
ELSE
BEGIN
SET @Sprzedaz = 0
END
----------------------------------------------------------------------------------------------------------------
INSERT INTO @Wynik
SELECT
@OpeGID,
@OpeIdent,
@OpeNazwisko ,
@Place,
@Produkcja,
@Adm_Oddzialow,
@Serwis,
@Zamowienia,
@Administrator,
@CRM,
@BI,
@Srodki,
@Kompletacja,
@Ksiegowosc,
@Sprzedaz

FETCH NEXT FROM k INTO @OpeGID, @OpeIdent, @OpeNazwisko, @OpeModuly
END

CLOSE k
DEALLOCATE k

SELECT Ope_Ident, Ope_Nazwisko, Place, Produkcja, Adm_Oddzialow, Serwis, Zamowienia, Administrator, CRM, BI, Srodki,
Kompletacja,Ksiegowosc,Sprzedaz
FROM @Wynik
ORDER BY Ope_Ident
Komu się to przyda ? Myślę, że niejedna osoba mając mirsz-marsz z loginami (niekiedy jeden użytkownik ma 2-3 loginy w zależności od potrzeb)  będzie kiedyś chciała uporządkować swoje dotychczasowe osiągnięcia w zaznaczaniu fistaszków na kartach operatorów, a wtedy takie zestawienie będzie jak znalazł.

Oczywiście, ostatnie zapytanie w kodzie wymaga modyfikacji, tak aby osiągnąć efekt, który będzie nas interesować w danej chwili, aczkolwiek samo generowanie tabeli modułów uważam za wykonane :P

wtorek, 9 listopada 2010

Stan magazynu - wartość magazynowa

Dawno nie było żadnego wpisu, więc trzeba to nadrobić. Tradycyjne na forum poświęconym XL http://cdn.3lance.pl/viewtopic.php?f=13&t=1451 natrafiłem na ciekawy temat. Co więcej sam ostatnio się z nim zetknąłem i przygotowałem rozwiązanie. Zaznaczam, iż na pewno nie jest ono do końca optymalne jeśli chodzi o czas wykonania, aczkolwiek dla moich potrzeb działa bez zarzutu - dla 150 000 towarów wykonuje się w 1m 46 sekund - idzie przeżyć :D

Na początek procedura obliczająca wartość magazynu ze względu na ilości magazynowe:
CREATE PROCEDURE [CDN].[_Magazyn_Stan] (@Data INT, @magazyn INT )
AS
BEGIN

SELECT     MAX(twr_kod) AS Kod,MAX(twr_nazwa) AS Nazwa,MAX(twr_jm) AS JM,
ISNULL(CAST(SUM( CASE man_gidtyp when 1089 then CDN._Dostawy_na_dzien(dst_GIDnumer, dst_GIDtyp, @Data,1)
ELSE (-CDN._Dostawy_na_dzien(dst_GIDnumer, dst_GIDtyp, @Data,1)) END 
/ CASE CDN._Dostawy_na_dzien(dst_GIDnumer, dst_GIDtyp, @Data,2) WHEN 0 THEN mas_ilosc ELSE CDN._Dostawy_na_dzien(dst_GIDnumer, dst_GIDtyp, @Data,2)
END * mas_ilosc) AS DECIMAL(15,2)),0) AS Wartosc,
ISNULL(SUM( CASE man_gidtyp WHEN 1089 THEN mas_ilosc ELSE (mas_ilosc - 2*mas_ilosc) END ),0) AS Ilosc
FROM         CDN.MagNag INNER JOIN
                      CDN.MagElem ON CDN.MagNag.MaN_GIDNumer = CDN.MagElem.MaE_GIDNumer INNER JOIN
                      CDN.MagSElem ON CDN.MagElem.MaE_GIDLp = CDN.MagSElem.MaS_GIDLp AND
                      CDN.MagElem.MaE_GIDNumer = CDN.MagSElem.MaS_GIDNumer AND CDN.MagElem.MaE_GIDTyp = CDN.MagSElem.MaS_GIDTyp INNER JOIN
                      CDN.Dostawy ON CDN.MagSElem.MaS_DstNumer = CDN.Dostawy.Dst_GIDNumer AND
                      CDN.MagSElem.MaS_DstTyp = CDN.Dostawy.Dst_GIDTyp INNER JOIN
                      CDN.Magazyny ON CDN.MagNag.MaN_MagDTyp = CDN.Magazyny.MAG_GIDTyp AND
                      CDN.MagNag.MaN_MagDNumer = CDN.Magazyny.MAG_GIDNumer INNER JOIN
                      CDN.TwrKarty ON CDN.MagElem.MaE_TwrNumer = CDN.TwrKarty.Twr_GIDNumer AND CDN.MagElem.MaE_TwrTyp = CDN.TwrKarty.Twr_GIDTyp
WHERE twr_typ IN (1,2) AND man_magdnumer = @magazyn AND man_data3 <= @Data
GROUP BY twr_gidnumer
ORDER BY MAX(twr_kod)
END

Jak widać wykorzystuje w niej odwołania do funkcji. Dlaczego ? Ponieważ pola w tabeli dostaw podlegają edycji ze względu na korekty i często zdarza się, że obecnie dst_ilosc = 0, a jeszcze tydzień temu nie mieliśmy korekty i zamiast 0 mieliśmy 5. Chodzi więc o to, aby otrzymać rzeczywistą wartość magazynu na dany dzień - nie interesuje na korekta z marca, skoro sprawdzamy stan dla stycznia - wtedy tej korekty przecież nie było. Funkcja ma za zadanie obliczyć tak wartość dostawy na dany dzień, jak i ilość jaka z niej wynika. Poniżej kod funkcji:
CREATE FUNCTION [CDN].[_Dostawy_na_dzien] (@Dst_GIDNumer int, @Dst_GIDTyp int,  @Data int, @Opcja int)
RETURNS DECIMAL(15,4)
AS
BEGIN

DECLARE @Wynik DECIMAL(15,4);

IF @Opcja = 1 
BEGIN
SELECT    @Wynik = isnull(sum(TrS_KosztKsiegowy),0)
FROM         CDN.TraSElem INNER JOIN
                      CDN.TraElem ON CDN.TraSElem.TrS_GIDNumer = CDN.TraElem.TrE_GIDNumer AND CDN.TraSElem.TrS_GIDLp = CDN.TraElem.TrE_GIDLp INNER JOIN
                      CDN.TraNag ON CDN.TraElem.TrE_GIDNumer = CDN.TraNag.TrN_GIDNumer INNER JOIN
                      CDN.Dostawy ON CDN.TraSElem.TrS_DstTyp = CDN.Dostawy.Dst_GIDTyp AND CDN.TraSElem.TrS_DstNumer = CDN.Dostawy.Dst_GIDNumer
WHERE Dst_GIDNumer = @Dst_GIDNumer
                and Dst_GIDTyp = @Dst_GIDTyp
                and TrN_GIDTyp in (1489, 1617, 1521)


SELECT    @Wynik = @Wynik + isnull(sum(TrS_KosztKsiegowy),0)
FROM         CDN.TraSElem INNER JOIN
                      CDN.TraElem ON CDN.TraSElem.TrS_GIDNumer = CDN.TraElem.TrE_GIDNumer AND CDN.TraSElem.TrS_GIDLp = CDN.TraElem.TrE_GIDLp INNER JOIN
                      CDN.TraNag ON CDN.TraElem.TrE_GIDNumer = CDN.TraNag.TrN_GIDNumer INNER JOIN
                      CDN.Dostawy ON CDN.TraSElem.TrS_DstTyp = CDN.Dostawy.Dst_GIDTyp AND CDN.TraSElem.TrS_DstNumer = CDN.Dostawy.Dst_GIDNumer
WHERE Dst_GIDNumer = @Dst_GIDNumer AND Dst_GIDTyp = @Dst_GIDTyp AND TrN_GIDTyp IN (1497, 1625, 1529) AND TrN_Data3 <= @Data
END
-----------------------------------------------------------------------------------------------------------------------------
ELSE
BEGIN
SELECT    @Wynik = isnull(sum(TrS_Ilosc),0)
FROM         CDN.TraSElem INNER JOIN
                      CDN.TraElem ON CDN.TraSElem.TrS_GIDNumer = CDN.TraElem.TrE_GIDNumer AND CDN.TraSElem.TrS_GIDLp = CDN.TraElem.TrE_GIDLp INNER JOIN
                      CDN.TraNag ON CDN.TraElem.TrE_GIDNumer = CDN.TraNag.TrN_GIDNumer INNER JOIN
                      CDN.Dostawy ON CDN.TraSElem.TrS_DstTyp = CDN.Dostawy.Dst_GIDTyp AND CDN.TraSElem.TrS_DstNumer = CDN.Dostawy.Dst_GIDNumer
WHERE Dst_GIDNumer = @Dst_GIDNumer
                AND Dst_GIDTyp = @Dst_GIDTyp
                AND TrN_GIDTyp in (1489, 1617, 1521)


SELECT    @Wynik = @Wynik + isnull(sum(TrS_Ilosc),0)
FROM         CDN.TraSElem INNER JOIN
                      CDN.TraElem ON CDN.TraSElem.TrS_GIDNumer = CDN.TraElem.TrE_GIDNumer AND CDN.TraSElem.TrS_GIDLp = CDN.TraElem.TrE_GIDLp INNER JOIN
                      CDN.TraNag ON CDN.TraElem.TrE_GIDNumer = CDN.TraNag.TrN_GIDNumer INNER JOIN
                      CDN.Dostawy ON CDN.TraSElem.TrS_DstTyp = CDN.Dostawy.Dst_GIDTyp AND CDN.TraSElem.TrS_DstNumer = CDN.Dostawy.Dst_GIDNumer
WHERE Dst_GIDNumer = @Dst_GIDNumer AND Dst_GIDTyp = @Dst_GIDTyp AND TrN_GIDTyp IN (1497, 1625, 1529) AND TrN_Data3 <= @Data

END

RETURN (@Wynik)
END


Złożone razem daje poprawny stan MAGAZYNU na dzień - a nie stan handlowy. W wolnej chwili postaram się usprawnić to zapytanie tak, aby działało szybciej :)

środa, 27 października 2010

Trigger dla kopiowania wartości atrybutów w zależności od innego atrybutu.

W nawiązaniu do tematu http://cdn.3lance.pl/viewtopic.php?f=13&t=1428 zamieszczam przykładowe rozwiązanie, wraz z opisem.

CREATE TRIGGER [CDN].[_Kopiowanie_z_wysylki]
   ON  [CDN].[Atrybuty]
   FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

-- Dla zmiany wartości atrybutu
    IF UPDATE (Atr_Wartosc)
    BEGIN
-- Jeżeli ID klasy atrybutu jest równe X (u mnie 79 - atrybut na dokumentach PKA/WKA z numerem wysyłki)
-- i numer wysyłki jest na parametrze ustalony:
        IF EXISTS (SELECT inserted.Atr_ID FROM inserted WHERE inserted.Atr_Atkid = 79 AND inserted.Atr_Wartosc <> '' AND inserted.Atr_Wartosc <> '<Brak>')
            BEGIN
                DECLARE @GIDWysylka INT            -- GID numer wysyłki
                DECLARE @ObiNumer INT            -- GID obiektu dla którego uruchamiany jest wyzwalacz
                DECLARE @ObiTyp INT                -- GID Typ obiektu dla którego uruchamiany jest wyzwalacz
                DECLARE @Wysylka VARCHAR(30)    -- Wartość atrybutu z numerem wysyłki
                DECLARE @param1 VARCHAR(100)    -- Wartość parametru pierwszego
                DECLARE @param2 VARCHAR(100)    -- Wartość parametru drugiego
                DECLARE @param3 VARCHAR(100)    -- Wartość parametru trzeciego

-- Pobranie wartości atrybutu z numerem wysyłki
                SET @Wysylka = (SELECT Atr_wartosc FROM inserted)

-- Pobranie GID-u obiektu (w tym przypadku PKA/WKA) dla którego zmieniana jest wartość atrybutu.
                SET @ObiNumer = (SELECT Atr_ObiNumer FROM inserted)

-- Pobranie GID typ-u obiektu (w tym przypadku PKA/WKA) dla którego zmieniana jest wartość atrybutu.
                SET @ObiTyp = (SELECT Atr_ObiTyp FROM inserted)

-- Pobranie GID-u wysyłki o określonym numerze (w moim przypadku seria jest pusta więc ją pomijam).
-- Generalnie jest to zależne od tego w jaki sposób uzupełniamy wartość atrybutu z numerem wysyłki.
-- Dla mnie seria zawsze jest pusta, a więc nie biorę jej pod uwagę - numer wysyłki na atrybucie określam poprzez wybór z listy za pomocą zapytania:
-- SELECT     cast(Wys_numer as varchar(5)) + '/' +  cast(Wys_rok as varchar(4)) as Numer FROM CDN.Wysylki
-- Stąd też wyjściowy sposób zwrócenia GID-u dla moich wysyłek:

                SET @GIDWysylka = (SELECT isnull(WYS_GIDNumer,0) FROM CDN.Wysylki
                                   WHERE WYS_GIDTyp = 337
                                   AND WYS_Numer = cast(substring(@Wysylka,1,charindex('/',@Wysylka)-1) as int)
                                   AND WYS_Rok = cast(substring(@Wysylka,charindex('/',@Wysylka)+1,4) as int))
               
-- Pobranie wartości pierwszego parametru z wysyłki, który będziemy kopiować
                SET @param1 = (SELECT isnull(Atr_Wartosc,'') FROM CDN.Atrybuty
                               WHERE Atr_ObiTyp = 337 AND Atr_ObiNumer = @GIDWysylka AND Atr_AtkID = 80) 

-- Pobranie wartości drugiego parametru z wysyłki, który będziemy kopiować
                SET @param2 = (SELECT isnull(Atr_Wartosc,'') FROM CDN.Atrybuty
                               WHERE Atr_ObiTyp = 337 AND Atr_ObiNumer = @GIDWysylka AND Atr_AtkID = 81)

-- Pobranie wartości trzeciego parametru z wysyłki, który będziemy kopiować
                SET @param3 = (SELECT isnull(Atr_Wartosc,'') FROM CDN.Atrybuty
                               WHERE Atr_ObiTyp = 337 AND Atr_ObiNumer = @GIDWysylka AND Atr_AtkID = 82)
               
-- Skopiowanie wartości pierwszego parametru na obiekt docelowy
                UPDATE CDN.Atrybuty
                SET Atr_Wartosc = @param1
                WHERE Atr_ObiTyp = @ObiTyp AND CDN.Atrybuty.Atr_ObiNumer = @ObiNumer AND Atr_AtkID = 80

-- Skopiowanie wartości drugiego parametru na obiekt docelowy
                UPDATE CDN.Atrybuty
                SET Atr_Wartosc = @param2
                WHERE Atr_ObiTyp = @ObiTyp AND CDN.Atrybuty.Atr_ObiNumer = @ObiNumer AND Atr_AtkID = 81

-- Skopiowanie wartości trzeciego parametru na obiekt docelowy
                UPDATE CDN.Atrybuty
                SET Atr_Wartosc = @param3
                WHERE Atr_ObiTyp = @ObiTyp AND CDN.Atrybuty.Atr_ObiNumer = @ObiNumer AND Atr_AtkID = 82
   
            END
    END
   
END


Zaznaczam, iż wyzwalacz ma charakter edukacyjny i z tym wiąże się deklarowanie tylu zmiennych oraz jego dość rozbudowana forma. To samo można osiągnąć przy użyciu jednego UPDATE FROM SELECT, aczkolwiek uznałem, że nie dla wszystkich byłoby to czytelne.

Pole Atr_AtkID to oczywiście ID atrybutów które nas interesują - w moim przypadku są to ID ze zbioru {79,80,81,82} - w waszym zapewne będą inne i wymaga to sprawdzenia. Wyzwalacz skopiuje wartość danego atrybutu tylko w przypadku gdy ten atrybut będzie istniał na docelowym elemencie.

Jak na razie to pierwszy trigger jaki tu zamieszczam, aczkolwiek zapewne nie ostatni, jako iż jest to ciekawa funkcja SQL Server, która daje administratorowi dużo możliwości.

poniedziałek, 25 października 2010

Sklejanie pól tekstowych - unikanie kursora.

Przeglądając, jak co dzień, forum 3lance, zauważyłem temat gdzie jeden z użytkowników umieścił funkcję w której wykorzystywał kursor do sklejania wartości tekstowych. http://cdn.3lance.pl/viewtopic.php?f=13&t=1435 Tak troszkę mnie tknęło, bo przecież generalnie kursor mistrzem wydajności nie jest i w miarę możliwości winno się go unikać. Napisałem więc swoje rozwiązanie, które jest bardziej wydajne - być może się wam przyda :)
Przykładowy kod generujący "sklejone" płatności dla pierwszych 49 GID-ów w tabeli płatności.

SELECT DISTINCT    tab1.TrP_GIDNumer,
        STUFF((SELECT ',' + LTRIM(RTRIM(tab2.TrP_FormaNazwa))  FROM CDN.TraPlat AS tab2 WHERE tab2.TrP_GIDNumer = tab1.TrP_GIDNumer FOR XML PATH('')), 1, 1, '') AS Formy
FROM        CDN.TraPlat AS tab1 WHERE trp_GIDNumer < 50
ORDER BY    tab1.TrP_GIDNumer


Rozwiązanie trochę bardziej eleganckie, no i pozbyliśmy się nieszczęsnego kursora :P Na dzisiaj to by było na tylko, bo czasu brak :)

EDIT: Jeszcze mały edit - podane przeze mnie rozwiązanie będzie działać od SQL w wersji 2005.