SQLize Online / PHPize Online  /  SQLtest Online

-- FUNCTION: public.p_rstit_report_license(bigint, bigint, text, date, boolean) -- DROP FUNCTION public.p_rstit_report_license(bigint, bigint, text, date, boolean); CREATE OR REPLACE FUNCTION public.p_rstit_report_license( uid bigint, agent2_id bigint, softwareprogname text, daterep date, showrevoked boolean) RETURNS void LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ declare NUID BIGINT:=UID; -- Пользователь sLID text; -- уровень доступа nAgent2 BIGINT:=agent2_id; sAgent2 text; sAgentName text; sSOFTWAREPROGNAME text:=softwareprogname; sSOFTWARENAME text; dDATEREP date:=daterep; bSHOWREVOKED boolean:=showrevoked; sSHOWREVOKED text; sLICREVOKED text; sLICTRANSFER text; rec record; idx int; sIndx text; npp INTEGER:=0; SHEET1 constant text := 'LICENSE'; LINE1 constant text := 'LINE1'; begin -- инициализация Excel perform p_excel_prepare(); --< Страница 1 perform p_excel_sheet_select(SHEET1); perform p_excel_cell_describe( 'ORG' ); perform p_excel_cell_describe( 'DATAREP' ); perform p_excel_cell_describe( 'SOFTWAREPROGNAME' ); perform p_excel_cell_describe( 'SHOWREVOKED' ); -- организация begin SELECT A.DESCRIPTION, A.CODE INTO sAgentName, sAgent2 FROM AGENT A WHERE A.ID=nAgent2 ; exception when others then null; end; if (nAgent2 is null) or sAgent2 in ('Росстандарт','Росстандарт СВОД') then sLID := (select trim(L.NAME) from USERS U, LEVACCESS L where U.ID=NUID and U.LEVACCESSID=L.ID); sAgentName := 'Федеральное агентство по техническому регулированию и метрологии'; end if; begin if sSOFTWAREPROGNAME is not null then select S.NAME into sSOFTWARENAME from SOFTWARE S where S.NAME=sSOFTWAREPROGNAME; else select S.NAME into sSOFTWARENAME from LICENSE L, SOFTWAREPROG SP, SOFTWARE S where L.SOFTWAREPROG_ID=SP.ID and SP.SOFTWARE_ID=S.ID; end if; if bSHOWREVOKED='true' then sSHOWREVOKED := 'Да'; else sSHOWREVOKED := 'Нет'; end if; perform p_excel_cell_value_write ( 'ORG' , sAgentName ); perform p_excel_cell_value_write ( 'DATAREP', to_char(dDATEREP, 'DD.MM.YYYY')); perform p_excel_cell_value_write ( 'SOFTWAREPROGNAME' , sSOFTWARENAME ); perform p_excel_cell_value_write ( 'SHOWREVOKED' , sSHOWREVOKED ); -- таблица perform p_excel_line_describe(LINE1); for i in 1..14 loop sIndx := trim(to_char( i,'09')); perform p_excel_line_cell_describe(LINE1, 'l1_cell'||sIndx ); end loop; for rec in (select SP.ID SOFTWAREPROG_ID, -- SOFTWAREPROG SP.SOFTWARE_ID SOFTWARE_ID, SP.AGENT2_ID AGENT2_ID, S.NAME S_NAME, -- SOFTWARE L.LICNUMB LICNUMB, -- LICENSE L.DATEBEGIN DATEBEGIN, L.DATEEND DATEEND, L.LICREVOKED LICREVOKED, L.LICTRANSFER LICTRANSFER, L.SUMM SUMM, L.AGENT1_ID AGENT1_ID, LS.SOFTWARESPEC_ID SOFTWARESPEC_ID, -- LICENSESPEC LS.OKEI3_ID OKEI3_ID, LS.QUANTITY QUANTITY, LS.LICUSED LICUSED, LS.LICLEFT LICLEFT from LICENSE L, LICENSESPEC LS, SOFTWAREPROG SP, SOFTWARE S, DOCSTATUS_IT DS, AGENT A where (SP.AGENT2_ID=nAgent2 or (((nAgent2 is null) or sAgent2 in ('Росстандарт','Росстандарт СВОД')) and (sLID in ('Персональный', 'Росстандарт')))) and SP.AGENT2_ID=A.ID and L.DATEEND <= dDATEREP and ((sSOFTWAREPROGNAME is not NULL and S.NAME = sSOFTWARENAME) or sSOFTWAREPROGNAME is NULL) and L.DOCSTATUSIT_ID=DS.ID and DS.CODE='На учете' and ((bSHOWREVOKED='false' and LS.LICREVOKED='true') or (bSHOWREVOKED='true' and (LS.LICREVOKED='true' or LS.LICREVOKED='false'))) and SP.ID = L.SOFTWAREPROG_ID and S.ID = SP.SOFTWARE_ID and LS.LICENSE_ID = L.ID order by A.code, L.LICNUMB) loop if rec.LICREVOKED='true' then sLICREVOKED := 'Да'; else sLICREVOKED := 'Нет'; end if; if rec.LICTRANSFER='true' then sLICTRANSFER := 'Да'; else sLICTRANSFER := 'Нет'; end if; npp := npp+1; idx := p_excel_line_append(LINE1); perform p_excel_cell_value_write( 'l1_cell01' , 0, idx, npp ); perform p_excel_cell_value_write( 'l1_cell02' , 0, idx, rec.LICNUMB ); perform p_excel_cell_value_write( 'l1_cell03' , 0, idx, to_char(rec.DATEBEGIN, 'DD.MM.YYYY') ); perform p_excel_cell_value_write( 'l1_cell04' , 0, idx, to_char(rec.DATEEND, 'DD.MM.YYYY') ); perform p_excel_cell_value_write( 'l1_cell05' , 0, idx, sLICREVOKED ); perform p_excel_cell_value_write( 'l1_cell06' , 0, idx, sLICTRANSFER ); perform p_excel_cell_value_write( 'l1_cell07' , 0, idx, rec.S_NAME ); perform p_excel_cell_value_write( 'l1_cell08' , 0, idx, rec.SOFTWARESPEC_ID ); perform p_excel_cell_value_write( 'l1_cell09' , 0, idx, rec.OKEI3_ID ); perform p_excel_cell_value_write( 'l1_cell10' , 0, idx, rec.QUANTITY ); perform p_excel_cell_value_write( 'l1_cell11' , 0, idx, rec.LICUSED ); perform p_excel_cell_value_write( 'l1_cell12' , 0, idx, rec.LICLEFT ); perform p_excel_cell_value_write( 'l1_cell13' , 0, idx, rec.AGENT1_ID ); perform p_excel_cell_value_write( 'l1_cell14' , 0, idx, rec.SUMM ); end loop; -- rec if rec is null then perform p_system_exception(0,'Нет данных для формирования отчёта!'); end if; -- удаляем строки perform p_excel_line_delete(LINE1); end; $BODY$; ALTER FUNCTION public.p_rstit_report_license(bigint, bigint, text, date, boolean) OWNER TO magicbox;