Webshop SQL

request.txt

select top 1000 * from (
SELECT
1 n_PAGES_COUNT,
D.Cislo_dokladu AS s_CISLO_DOKLADU,
D.Variabilni_symbol AS s_VARIABILNI_SYMBOL,
D.Objednavky AS s_OBJEDNAVKY,
CONVERT(varchar(10),D.Datum,104) AS d_DATUM,
D.Zpusob_uhrady AS s_ZPUSOB_UHRADY,
D.Celkem_bez_DPH_m AS n_CENA_BEZ_DPH,
D.Celkem_m AS n_CENA_S_DPH,
D.Firma AS s_FIRMA,
D.Nazev_firmy AS s_NAZEV_FIRMY,
D.Adresa AS s_ADRESA,
D.Adresa_2 AS s_DODACI_ADRESA,
D.ICO AS s_ICO,
D.Referent AS s_REFERENT,
D.Udaj_1,
isnull(D.Mena, 'Kč') AS s_MENA,
CASE LEFT(D.Udaj_1,3)
WHEN '_OK' THEN 'Vyřízená'
WHEN '_ČÁ' THEN 'Cástecne vyřízená'
ELSE 'Nevyřízená'
END AS s_STAV,
CASE LEFT(D.Udaj_1,3)
WHEN '_OK' THEN 1
WHEN '_ČÁ' THEN 2
ELSE 3
END AS n_STAV,
(D.Cislo_uctu + '/' + D.Kod_banky) as s_Cislo_uctu,
D.Kategorie AS s_ZPRAVA_ZAKAZNIKOVI,

(CONVERT(VARCHAR, Datum_splatnosti, 104)) AS s_DATUM_SPLATNOSTI,

InterChange.fn_ZAKAZKA_OBSAHUJE_NEPRECTENE_ZPRAVY(D.Cislo_dokladu) AS b_ZAKAZKA_OBSAHUJE_NEPRECTENE_ZPRAVY,
InterChange.fn_ZAKAZKA_POCET_ZPRAV(D.Cislo_dokladu) AS n_CELKOVY_POCET_ZPRAV,
-- D.Kategorie AS s_ZPRAVA_ZAKAZNIKOVI,
F.E_mail, F.Telefon_1, F.Telefon_2,
XDP_PIN.HodnotaRetezec AS PIN,
D.Datum AS dateDATUM ,
D.Kniha,
interchange.fnOrderGetPersonName(D.Cislo_dokladu) As sOrderedByName
-- XD.PIN
FROM
Data0001.dbo.Doklady D
LEFT JOIN Data0001.dbo.Firmy F WITH(NOLOCK) ON F.Firma = D.Firma
LEFT JOIN Data0001..x_DokladyParametry XDP_PIN WITH(NOLOCK) ON XDP_PIN.Cislo = D.Cislo_dokladu AND XDP_PIN.Parametr = 3
-- LEFT JOIN Data0001..x_Doklady XD
-- ON XD.CisloDokladu = D.Cislo_dokladu
-- INNER JOIN InterChange.fn_PAGING_ZAKAZKY_VSECHNY_A_VYRIZENE(@p_s_FIRMA_ID,@p_s_CISLO_DOKLADU,@p_n_PAGE,@p_n_PAGE_SIZE,
-- @d_D1,@d_D2,@p_s_STAV,@xcomputer)
-- AS FNTBL
-- ON D.Cislo_dokladu=FNTBL.s_CISLO_DOKLADU_ID

WHERE
Doklad = 'Zakázka'
AND D.Kniha NOT IN ('Automat')
AND NOT EXISTS(SELECT
1
FROM
Data0001..Polozky_dokladu WITH(NOLOCK)
WHERE
Produkt IN ( 'RO201a' , 'RO203' , 'RO204' , 'RO188e3' )
AND Cislo_dokladu = D.Cislo_dokladu)

AND D.Cislo_dokladu='141804882'
union
SELECT
1 n_PAGES_COUNT,
D.Cislo_dokladu AS s_CISLO_DOKLADU,
D.Variabilni_symbol AS s_VARIABILNI_SYMBOL,
D.Objednavky AS s_OBJEDNAVKY,
CONVERT(varchar(10),D.Datum,104) AS d_DATUM,
D.Zpusob_uhrady AS s_ZPUSOB_UHRADY,
D.Celkem_bez_DPH_m AS n_CENA_BEZ_DPH,
D.Celkem_m AS n_CENA_S_DPH,
D.Firma AS s_FIRMA,
D.Nazev_firmy AS s_NAZEV_FIRMY,
D.Adresa AS s_ADRESA,
D.Adresa_2 AS s_DODACI_ADRESA,
D.ICO AS s_ICO,
D.Referent AS s_REFERENT,
D.Udaj_1,
isnull(D.Mena, 'Kč') AS s_MENA,
CASE LEFT(D.Udaj_1,3)
WHEN '_OK' THEN 'Vyřízená'
WHEN '_ČÁ' THEN 'Cástecne vyřízená'
ELSE 'Nevyřízená'
END AS s_STAV,
CASE LEFT(D.Udaj_1,3)
WHEN '_OK' THEN 1
WHEN '_ČÁ' THEN 2
ELSE 3
END AS n_STAV,
(D.Cislo_uctu + '/' + D.Kod_banky) as s_Cislo_uctu,
D.Kategorie AS s_ZPRAVA_ZAKAZNIKOVI,

(CONVERT(VARCHAR, Datum_splatnosti, 104)) AS s_DATUM_SPLATNOSTI,

InterChange.fn_ZAKAZKA_OBSAHUJE_NEPRECTENE_ZPRAVY(D.Cislo_dokladu) AS b_ZAKAZKA_OBSAHUJE_NEPRECTENE_ZPRAVY,
InterChange.fn_ZAKAZKA_POCET_ZPRAV(D.Cislo_dokladu) AS n_CELKOVY_POCET_ZPRAV,
-- D.Kategorie AS s_ZPRAVA_ZAKAZNIKOVI,
F.E_mail, F.Telefon_1, F.Telefon_2,
XDP_PIN.HodnotaRetezec AS PIN,
D.Datum AS dateDATUM ,
D.Kniha,
interchange.fnOrderGetPersonName(D.Cislo_dokladu) As sOrderedByName
-- XD.PIN
FROM
Data0001a.dbo.Doklady D
LEFT JOIN Data0001.dbo.Firmy F WITH(NOLOCK) ON F.Firma = D.Firma
LEFT JOIN Data0001..x_DokladyParametry XDP_PIN WITH(NOLOCK) ON XDP_PIN.Cislo = D.Cislo_dokladu AND XDP_PIN.Parametr = 3
-- LEFT JOIN Data0001..x_Doklady XD
-- ON XD.CisloDokladu = D.Cislo_dokladu
-- INNER JOIN InterChange.fn_PAGING_ZAKAZKY_VSECHNY_A_VYRIZENE(@p_s_FIRMA_ID,@p_s_CISLO_DOKLADU,@p_n_PAGE,@p_n_PAGE_SIZE,
-- @d_D1,@d_D2,@p_s_STAV,@xcomputer)
-- AS FNTBL
-- ON D.Cislo_dokladu=FNTBL.s_CISLO_DOKLADU_ID

WHERE
Doklad = 'Zakázka'
AND D.Kniha NOT IN ('Automat')
AND NOT EXISTS(SELECT
1
FROM
Data0001..Polozky_dokladu WITH(NOLOCK)
WHERE
Produkt IN ( 'RO201a' , 'RO203' , 'RO204' , 'RO188e3' )
AND Cislo_dokladu = D.Cislo_dokladu)

AND D.Cislo_dokladu='141804882'
) as db ORDER BY dateDATUM DESC, s_CISLO_DOKLADU