Support - zapytania SQL

INSERT INTO public.currency(code)

VALUES ('CNY'); - Wojtka SQL

SELECT relname,n_live_tup as rowcount
FROM pg_stat_user_tables
ORDER BY rowcount DESC;

Rozmiar: 
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;

select * from company_operator where contactinfo_email like '%bogusz.parzyszek@workplace.pl%';

select * from company where companyname like '%Workplace%';

a następnie:

update auction_supplier set supplier_id = 71552 where id = 38604;

update auction_supplier set supplieroperator_id = 73107 where id = 38604

select * from request r
join company com on r.company_id = com.id
join request_join_company_organisation_units rjcou on rjcou.request_id = r.id
full join request_join_related_auctions rjra on rjra.request_id = r.id
join request_join_buyer_category rjbc on rjbc.request_id = r.id
join request_response_element_state rres on rres.request_id = r.id
where r.requestsendingdate is not null
and r.company_id = 10
and r.archivedate is null
and r.temporary = false

select cc.id, count(r) from request r
join company cc on r.company_id = cc.id
group by cc.id
order by count(r) desc

Zrzut do pliku następujących danych:

ID postępowania + Nazwa Postępowania + status + Wybrany dostawca

select auctioncode as "Kod", auctionname as "Nazwa Postępowania", auctionstatus as "Status", supco.companyname as "Wybrany Dostawca" /*,
rtr.buyedquantity, co.id,co.companyname,auctiontype */from response_table_row rtr
join table_rfx_response_element trre on rtr.parenttablestate_id = trre.id
join rfx_response_element rre on trre.id = rre.id
join rfx_response_version rrv on rrv.id = rre.parentresponse_id
join auction_rfx_response arr on arr.id = rrv.response_id
join auction_supplier aus on aus.id = arr.auctionsupplier_id
join auction au on au.id = aus.auction_id
join company co on co.id=au.company_id
join company supco on supco.id = aus.supplier_id
where /*aus.auction_id = 6533 and*/ co.id = 69775 and rtr.buyedquantity > 0 GROUP BY co.id, auctiontype, auctioncode,
auctionstatus,auctionname,co.companyname, supco.companyname

UPDATE request_approver SET 
changestatusdate = null,
status = 'NONE',
decisiondetails_ipaddress = null,
decisiondetails_method = null,
approvedadmin_id = null
where request_id = tutaj_id_zapotrzebowania
 
update request set acceptanceonlevel = 1, status = 'PENDING_APPROVAL'
where id = tutaj_id_zapotrzebowania

Najpierw ten pierwszy update a później ten drugi

Cofnięcie akceptacji do statusu w przygotowaniu

update auction  set auctionapproveworkflow_id  = null, auctionstatus = 'IN_PREPARE'  where  auctioncode in (NUMER PRZETARGU')

Cofnięcie rekomendacji do status ocena ofert

update auction  set auctionsupplierrecommendationworkflow_id = null, auctionstatus = 'OFFERS_EVALUATION'  where  auctioncode in (NUMER PRZETARGU)

1. Znalezienie przetargu - 
   SELECT * FROM public.auction
   where  auctioncode in (NUMER PRZETARGU)
   ORDER BY id ASC
2. Znalezienie ID - 
   SELECT * FROM public.auction_criterion
   where auction_id = tutaj_id_przetargu
3. Znalezienie odpowiedniego ID użytkownika do jego zamiany - 
   SELECT * FROM public.application_user
   where firstname in (nazwisko)
   where id in (id wyciągnięte z listy użytkowników przypisanych do kryterium - auction_criterion)
4. Znalezienie osób przypisanych do odpowiednich kryteriów - 
   SELECT * from auction_criterion_raters
   where auction_criterion_id in(tutaj_id_kryterium)    
   and raters_id in (tutaj_id_użytkownika)
5. Podmiana użytkownika w kryteriach oceny - 
   update auction_criterion_raters set  raters_id = tutaj_id_użytkownika_prawidłowego
   where auction_criterion_id in(tutaj_id_kryteriów)    
   and raters_id in (tutaj_id_użytkownika_błędnego)

Potrzeba zgłoszona w tasku:

ISRM-1631

update auction set comparison_status = 'NONE'
where id = tutaj_id_przetargu

select c.table_schema,
c.table_name,
c.column_name,
case c.is_nullable
when 'NO' then 'not nullable'
when 'YES' then 'is nullable'
end as nullable
from information_schema.columns c
join information_schema.tables t
on c.table_schema = t.table_schema
and c.table_name = t.table_name
where c.table_schema not in ('pg_catalog', 'information_schema')
and t.table_type = 'BASE TABLE' and t.table_name = tutaj nazwa tabeli
order by table_schema,
table_name,
column_name;

update auction set auctionplayoff_playofftime = 1440 (czas dogrywki w minutach)
where id = tutaj_id_aukcji

update auction set auctionplayoff_playoffstartedin = 1440 (dodaj dogrywkę w ostatnich minutach)
where id = tutaj_id_aukcji

select * from auction where auctioncode = 'tutaj_kod_aukcji'

select * from table_form_element_row tfer
join table_form_element_state tfes on tfer.parenttablestate_id = tfes.id
join form_element_state fes on fes.id = tfes.id
where fes.auction_id = tutaj_id_aukcji
order by tfer.id asc

update table_form_element_row set name = 'tutaj_nazwa_produktu' where id = tutaj_id_linii_w_tabeli

update request set status = 'IN_PREPARE'
where id = 20018

update request set requestsendingdate = 'null'
where id = 20018

select * from request
where company_id = 69775
order by requestnumber desc

select * from company
where companyname like '%Credit Agricole%'

select * from request_approve_action
where request_id = 20018

delete from request_approve_action
where request_approve_action.id = 159914

select * from request_visit_action
where id = 767481

delete from request_visit_action
where id = 767481

select email, CONCAT('https://preprod.nextbuy24.com/services/actions/', url.uniqueidentifier) from auction_supplier asup

inner join auction au
on au.id = asup.auction_id

inner join rfx_response_action rfxr
on rfxr.auctionsupplier_id = asup.id

inner join url_action url
on url.id = rfxr.id

where au.id = tutaj_id_aukcji

select asup.email from auction_supplier asup

inner join auction au
on au.id = asup.auction_id

inner join rfx_response_action rfxr
on rfxr.auctionsupplier_id = asup.id

inner join url_action url
on url.id = rfxr.id

where au.id = tutaj_id_aukcji
and url.executed = true

select asup.*, url.executed, email, CONCAT('https://app.nextbuy24.com/services/actions/', url.uniqueidentifier) from auction_supplier asup

inner join auction au
on au.id = asup.auction_id

inner join rfx_response_action rfxr
on rfxr.auctionsupplier_id = asup.id

inner join url_action url
on url.id = rfxr.id

where au.id = tutaj_id_aukcji

select * from company_order where company_order.company_id = tutaj_id_firmy
and company_order.ordernumber = 'tutaj_numer_zamówienia'

update company_order set orderstatus = 'SEND'
where id = tutaj_id_zamówienia

1. Znalezienie firmy kupca
   
   SELECT * FROM public.auction
   where auctioncode = 'KOD RUNDY'
   ORDER BY id ASC
2. Usunięcie punktów oceny ofert
   
   DELETE FROM auction_supplier_criterion_value ASCV
   WHERE id IN (SELECT ASCV.id FROM auction_supplier_criterion_value ASCV
   LEFT JOIN public.auction_criterion AC ON AC.id = ASCV.auctioncriterion_id
   WHERE AC.auction_id = 'ID RUNDY')