Raporty, zapytania SQL dla kientów

Nazwa klientaKrótki opis zapytaniatreść zapytania SQLUwagi
Nest BankWszystkie aukcje dla firmy Nest Bank od 2018-01-01 do 2019-10-31

select au.auctioncode, au.startdate, au.auctionname, aus.email,
aus.notregistrationsupplierdata_companyname, com.companycontactinfo_email,
com.companyname,com.companycontactinfo_phone, com.companycontactinfo_cellphone
from auction_supplier aus
join auction au on au.id = aus.auction_id
join company com on com.id = aus.supplier_id
where au.company_id = (select cc.id from company cc
where cc.companyname ilike '%nest bank%')
and au.createdate BETWEEN '2018-01-01' AND '2019-10-31'
order by au.id asc

Task, w którym zgłoszone potrzebę: 

Błąd renderowania makra „jira”

null

Credit AgricoleLista kampanii - ocena całościowaSELECT
rc.id,
rc.name AS Nazwa_kampanii,
rating_type.name AS Typ_oceny,
questionnaire.name AS Kwestionariusz,
CONCAT(au.firstname||' '||au.lastname) AS osoba_odpowiedzialna,
rc.status AS Status,
rc.creationdate AS Data_rozpoczecia,
rc.enddate AS Data_zakonczenia,
company.shortname AS Dostawca,
rcr.progress AS Progres,
rcr.result AS Punkty,
(select t2.name
from rating_type_item t2
where t2.resultto >= rcr.result
and ratingtype_id = rc.ratingtype_id
order by t2.resultto asc
limit 1),
ec.senddate AS Data_wyslania
FROM rating_campaign rc
INNER JOIN rating_type ON rc.company_id = rating_type.company_id
INNER JOIN questionnaire ON rc.questionnaire_id = questionnaire.id
INNER JOIN rating_campaign_response rcr on rc.id = rcr.ratingcampaign_id
INNER JOIN application_user au on au.id = rc.responsibleoperator_id
INNER JOIN evaluated_company ec on ec.ratingcampaign_id = rc.id
INNER JOIN company on ec.evaluatedcompany_id = company.id;

Task, w którym zgłoszone potrzebę:

ISRM-1877

Credit AgricoleLista kampanii - ocena cząstkowaSELECT
rc.id,
rc.name AS nazwa_kampanii,
co.shortname AS dostawca,
CONCAT(au.firstname||' '||au.lastname) AS osoba_oceniajaca,
rcq.name AS pytanie,
rca.name AS odpowiedz,
CONCAT(main_criterion_result.result/GREATEST(rcc.value::numeric, 1.0)*100, '%') AS wynik_procenty,
rcc.value AS waga,
rcqr.chosenResult AS wartosc_odpowiedzi,
main_criterion_result.result AS wynik
FROM rating_campaign rc
INNER JOIN rating_campaign_criterion rcc ON rc.id = rcc.ratingcampaign_id
INNER JOIN rating_campaign_question rcq ON rcc.id = rcq.parentcriterion_id
INNER JOIN rating_campaign_response rcr ON rc.id = rcr.ratingcampaign_id
INNER JOIN application_user au ON rcr.answeringoperator_id = au.id
INNER JOIN evaluated_company ec ON rc.id = ec.ratingcampaign_id
INNER JOIN company co ON ec.evaluatedcompany_id = co.id
INNER JOIN rating_campaign_question_response rcqr ON rcq.id = rcqr.ratingcampaignquestion_id
INNER JOIN rating_campaign_answer rca ON rcqr.chosenanswer_id = rca.id
INNER JOIN main_criterion_result ON main_criterion_result.criterion_id = rcc.id

Task, w którym zgłoszone potrzebę:

ISRM-1877

EurocashLista użytkowników zgłaszających zapotrzebowania + ścieżki akceptacjiSELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname,
CONCAT(accept1.firstname || ' ' || accept1.lastname) AS Akceptujacy1,
CONCAT(accept2.firstname || ' ' || accept3.lastname) AS Akceptujacy2,
CONCAT(accept2.firstname || ' ' || accept3.lastname) AS Akceptujacy3
FROM company_join_company_operator cjco
    JOIN company_operator co ON cjco.operator_id = co.id
    JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
    JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
    JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
    JOIN application_user au ON co.id = au.id
    JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_request_acceptance_level cojral
        JOIN company_operator_request_acceptance_level coral ON coral.id = cojral.company_operator_acceptance_level_id
        JOIN company_operator co ON coral.companyoperator_id = co.id
        JOIN application_user au ON co.id = au.id
        JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
        JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
        JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
        WHERE cojral.acceptance_level_id = '592' AND au.locked = 'false'
    ) accept1 ON accept1.shortname = cou.shortname
    JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_request_acceptance_level cojral
        JOIN company_operator_request_acceptance_level coral ON coral.id = cojral.company_operator_acceptance_level_id
        JOIN company_operator co ON coral.companyoperator_id = co.id
        JOIN application_user au ON co.id = au.id
        JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
        JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
        JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
        WHERE cojral.acceptance_level_id = '599' AND au.locked = 'false'
    ) accept2 ON accept2.shortname = cou.shortname
    JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_request_acceptance_level cojral
        JOIN company_operator_request_acceptance_level coral ON coral.id = cojral.company_operator_acceptance_level_id
        JOIN company_operator co ON coral.companyoperator_id = co.id
        JOIN application_user au ON co.id = au.id
        JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
        JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
        JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
        WHERE cojral.acceptance_level_id = '600' AND au.locked = 'false'
    ) accept3 ON accept3.shortname = cou.shortname
WHERE cjco.company_id = '36623' AND co.deletedate IS NULL AND au.locked = 'false' AND accept1.contactinfo_email != co.contactinfo_email AND accept2.contactinfo_email != co.contactinfo_email AND accept3.contactinfo_email != co.contactinfo_email
ORDER BY co.contactinfo_email
AmicaLiczba użytkowników stworzonych na dany rok

SELECT COUNT(*) AS Ilosc FROM company_operator CO
JOIN company_join_company_operator CJCO ON CJCO.operator_id = CO.id
WHERE CJCO.company_id = '4210' AND co.createdatetime BETWEEN '2025-01-01' AND '2025-12-31'

Potrzeba do liczenia bonusu według umowy z Amica
EurocashAktywni użytkownicy + Ostatnie czasy logowania - wariant bez jednostekSELECT r2.firstname, r2.lastname, r2.contactinfo_email, r1.max FROM (
SELECT ale.accountlogin, max(ale.eventtime) FROM company_join_company_operator cjco
JOIN login_account la ON cjco.operator_id = la.applicationuser_id
JOIN application_login_event ale ON ale.accountlogin = la.account_login
WHERE cjco.company_id = '36623' AND ale.administrativelogin = 'false' AND ale.eventtype = 'LOGIN'
GROUP BY ale.accountlogin) r1
RIGHT OUTER JOIN (
SELECT au.firstname, au.lastname, co.contactinfo_email FROM company_join_company_operator cjco
JOIN login_account la ON cjco.operator_id = la.applicationuser_id
JOIN company_operator co ON cjco.operator_id = co.id
JOIN application_user au ON co.id = au.id
WHERE cjco.company_id = '36623' AND au.deletedate IS NULL AND au.locked = 'false'
GROUP BY au.firstname, au.lastname, co.contactinfo_email) r2 ON r1.accountlogin = r2.contactinfo_email
ORDER BY r2.contactinfo_email		Raport aktywnych użytkowników
EurocashAktywni użytkownicy + Ostatnie czasy logowania - wariant z jednostkamiSELECT r2.firstname, r2.lastname, r2.contactinfo_email, r2.shortname, r1.max FROM (
SELECT ale.accountlogin, max(ale.eventtime) FROM company_join_company_operator cjco
JOIN login_account la ON cjco.operator_id = la.applicationuser_id
JOIN application_login_event ale ON ale.accountlogin = la.account_login
WHERE cjco.company_id = '36623' AND ale.administrativelogin = 'false' AND ale.eventtype = 'LOGIN'
GROUP BY ale.accountlogin) r1
RIGHT OUTER JOIN (
SELECT au.firstname, au.lastname, co.contactinfo_email, cou.shortname FROM company_join_company_operator cjco
JOIN login_account la ON cjco.operator_id = la.applicationuser_id
JOIN company_operator co ON cjco.operator_id = co.id
JOIN application_user au ON co.id = au.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
WHERE cjco.company_id = '36623' AND au.deletedate IS NULL AND au.locked = 'false'
GROUP BY au.firstname, au.lastname, co.contactinfo_email, cou.shortname) r2 ON r1.accountlogin = r2.contactinfo_email
ORDER BY r2.contactinfo_email		Raport aktywnych użytkowników wraz z ich jednostkami organizacyjnymi/gospodarczymi
AmicaUżytkownicy którzy byli widoczni jako osoba odpowiedzialna w postępowaniach w ciągu ostatniego rokuSELECT DISTINCT ON (A.owneroperator_id) A.owneroperator_id, A.auctioncode, AU.firstname, AU.lastname FROM auction A
LEFT JOIN application_user AU ON AU.id = A.owneroperator_id
WHERE A.company_id = '4210' AND A.createdate >= NOW() - INTERVAL '1 year'
GROUP BY A.owneroperator_id, A.auctioncode, AU.firstname, AU.lastname
TchiboLista użytkowników zgłaszających zapotrzebowania + ścieżki akceptacjiSELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname,
CONCAT(accept1.firstname || ' ' || accept1.lastname) AS Akceptujacy1,
CONCAT(accept2.firstname || ' ' || accept3.lastname) AS Akceptujacy2,
CONCAT(accept2.firstname || ' ' || accept3.lastname) AS Akceptujacy3
FROM company_join_company_operator cjco
JOIN company_operator co ON cjco.operator_id = co.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
JOIN application_user au ON co.id = au.id
JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_order_acceptance_level cojoal
JOIN company_operator_order_acceptance_level cooal ON cooal.id = cojoal.company_operator_acceptance_level_id
JOIN company_operator co ON cooal.companyoperator_id = co.id
JOIN application_user au ON co.id = au.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
WHERE cojoal.acceptance_level_id = '432' AND au.locked = 'false'
) accept1 ON accept1.shortname = cou.shortname
JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_order_acceptance_level cojoal
JOIN company_operator_order_acceptance_level cooal ON cooal.id = cojoal.company_operator_acceptance_level_id
JOIN company_operator co ON cooal.companyoperator_id = co.id
JOIN application_user au ON co.id = au.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
WHERE cojoal.acceptance_level_id = '617' AND au.locked = 'false'
) accept2 ON accept2.shortname = cou.shortname
JOIN (SELECT au.firstname, au.lastname, co.contactinfo_email, co.companyname, cou.shortname FROM company_operator_join_order_acceptance_level cojoal
JOIN company_operator_order_acceptance_level cooal ON cooal.id = cojoal.company_operator_acceptance_level_id
JOIN company_operator co ON cooal.companyoperator_id = co.id
JOIN application_user au ON co.id = au.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
WHERE cojoal.acceptance_level_id = '619' AND au.locked = 'false'
) accept3 ON accept3.shortname = cou.shortname
WHERE cjco.company_id = '69526' AND co.deletedate IS NULL AND au.locked = 'false' AND accept1.contactinfo_email != co.contactinfo_email AND accept2.contactinfo_email != co.contactinfo_email AND accept3.contactinfo_email != co.contactinfo_email
ORDER BY co.contactinfo_email
Credit AgricoleLista użytkowników (Imię, Nazwisko, E-mail, Uprawnienia, Jednostka org., Akceptujący zapotrzebowania 1 poziomu dla jednostki, Akceptujący zapotrzebowania 2 poziomu dla jednostki, Kategorie zakupowe)WITH RECURSIVE category_tree AS (
-- Base case: root categories (no parent)
SELECT
id,
categorycode,
parentcategory_id,
ownercompany_id,
categorycode::VARCHAR AS full_path
FROM global_buyer_category
WHERE parentcategory_id IS NULL
AND ownercompany_id = 69775

UNION ALL

-- Recursive case: children
SELECT
c.id,
c.categorycode,
c.parentcategory_id,
c.ownercompany_id,
ct.full_path || '-' || c.categorycode AS full_path
FROM global_buyer_category c
INNER JOIN category_tree ct ON c.parentcategory_id = ct.id
WHERE c.ownercompany_id = 69775
)
SELECT
au.firstname,
au.lastname,
co.contactinfo_email,
STRING_AGG(DISTINCT SR.rolename, ', ' ORDER BY SR.rolename) AS rolenames,
cou.shortname,
STRING_AGG(DISTINCT CASE WHEN cojral.acceptance_level_id = '478'
THEN accept_au.firstname || ' ' || accept_au.lastname
END, ', ') AS Akceptujacy1,
STRING_AGG(DISTINCT CASE WHEN cojral.acceptance_level_id = '621'
THEN accept_au.firstname || ' ' || accept_au.lastname
END, ', ') AS Akceptujacy2,
STRING_AGG(DISTINCT tb.translation, ', ' ORDER BY tb.translation) AS buyer_categories
FROM company_join_company_operator cjco
JOIN company_operator co ON cjco.operator_id = co.id
JOIN company_operator_organisation_unit coou ON coou.companyoperator_id = co.id
JOIN company_operator_unit_join_company_operator coujco ON coujco.operator_organisation_unit_id = coou.id
JOIN company_organisation_unit cou ON coujco.company_unit_id = cou.id
JOIN application_user au ON co.id = au.id
LEFT JOIN company_operator_security_role COSR ON COSR.companyoperator_id = CO.id
LEFT JOIN company_operator_role_join_security_role CORJSR ON CORJSR.company_operator_role_id = COSR.id
LEFT JOIN security_role SR ON SR.id = CORJSR.security_role_id
LEFT JOIN company_operator_join_request_acceptance_level cojral ON cojral.acceptance_level_id IN ('478', '621')
LEFT JOIN company_operator_request_acceptance_level coral ON coral.id = cojral.company_operator_acceptance_level_id
LEFT JOIN company_operator accept_co ON coral.companyoperator_id = accept_co.id
LEFT JOIN application_user accept_au ON accept_co.id = accept_au.id
LEFT JOIN company_operator_organisation_unit accept_coou ON accept_coou.companyoperator_id = accept_co.id
LEFT JOIN company_operator_unit_join_company_operator accept_coujco ON accept_coujco.operator_organisation_unit_id = accept_coou.id
LEFT JOIN company_organisation_unit accept_cou ON accept_coujco.company_unit_id = accept_cou.id
LEFT JOIN company_operator_buyer_category COBC ON COBC.companyoperator_id = co.id
LEFT JOIN operator_join_buyer_categories OJBC ON COBC.id = OJBC.company_operator_buyer_category_id
LEFT JOIN global_buyer_category gbc ON gbc.id = OJBC.category_id
LEFT JOIN category_tree ct ON gbc.id = ct.id
LEFT JOIN translation_message_key tmk
ON tmk.messagekey = '69775_GLOBAL_BUYER_CATEGORY_' || ct.full_path
LEFT JOIN translation_bundle tb
ON tb.messagekey_id = tmk.id
WHERE
cjco.company_id = '69775'
AND co.deletedate IS NULL
AND au.locked = 'false'
AND (accept_cou.shortname = cou.shortname OR accept_cou.shortname IS NULL)
AND (accept_au.locked = 'false' OR accept_au.locked IS NULL)
AND (accept_co.contactinfo_email != co.contactinfo_email OR accept_co.contactinfo_email IS NULL)
GROUP BY
au.firstname,
au.lastname,
co.contactinfo_email,
co.companyname,
cou.shortname
ORDER BY
co.contactinfo_email;
AmicaPostępowania za okres czasu + Użytkownik tworzącySELECT A.id, A.auctionname, A.auctionstatus, A.startdate, A.enddate, A.bidsettings_auctiondirection, CO.contactinfo_email FROM auction A
LEFT JOIN company_operator CO ON CO.id = A.owneroperator_id
WHERE A.company_id = '10473' AND A.auctionstatus != 'CANCELLED' AND A.startdate BETWEEN '2025-09-01' AND '2026-08-26'
ORDER BY A.startdate DESC