"""
Embedded SQL schema for rateloader.

This module contains the complete database schema as a Python string,
allowing it to be bundled with the executable without requiring external files.
"""

# Complete SQL schema embedded as a string
RATELOADER_SCHEMA_SQL = r"""
CREATE SEQUENCE seq_m_rl_log START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE TABLE m_rl_log
    (log_id                         NUMBER ,
    procedure_name                 VARCHAR2(100 BYTE),
    log_timestamp                  TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
    input_params                   VARCHAR2(4000 BYTE),
    error_code                     VARCHAR2(100 BYTE),
    error_message                  VARCHAR2(4000 BYTE),
    call_stack                     CLOB,
    created_by                     VARCHAR2(100 BYTE) DEFAULT USER)
  SEGMENT CREATION IMMEDIATE
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
  LOB ("CALL_STACK") STORE AS SECUREFILE SYS_LOB0000115970C00007$$
  (
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     106496
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
    NOCACHE LOGGING
    CHUNK 8192
  )
  NOPARALLEL
  LOGGING
/


ALTER TABLE m_rl_log
ADD PRIMARY KEY (log_id)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/


CREATE OR REPLACE
PROCEDURE print_clob_to_output (p_clob IN CLOB)
 IS
    l_offset     INT := 1;
 BEGIN
    dbms_output.put_line('Print CLOB');
     loop
         exit when l_offset > dbms_lob.getlength(p_clob);
         dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
         l_offset := l_offset + 255;
     END LOOP;
 END print_clob_to_output;
/


-- Start of DDL Script for Function V8LIVE.M_CRSTOFID
-- Generated 23.06.2023 5:51:34 from V8LIVE@V8
create or replace view m_avl as
select
    tot.YRMS_YCAT_ID ycat_id
    , tot.WDAT_DATE wdat_date
    , tot.NOOFROOMS tot
    , yocc.defNOOFROOMS def
    , bocc.defNOOFROOMS bld
    , nvl(tot.noofrooms, 0) - nvl(yocc.defnoofrooms, 0) - nvl(bocc.defnoofrooms, 0) avl
from v8_sys_noofroomspercategory tot, v8_sys_ydet_occ_wb yocc, v8_sys_ybld_occ bocc
where
    yocc.YDET_YCAT_ID(+) = tot.yrms_ycat_id
    and yocc.YDET_DATE(+) = tot.wdat_date
    and bocc.YBLD_YCAT_ID(+) = tot.yrms_ycat_id
    and bocc.YBLD_DATE(+) = tot.wdat_date;


CREATE OR REPLACE
FUNCTION m_getzcnvid
return number
as
r number;
begin
select zota.zota_zcnv_id into r from zota where zota_name like 'TravelLine';
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE
function m_is_weekend(rid yrch.yrch_id%type, dat wdat.wdat_date%type) return number
as
r yrch%rowtype;
w number;
begin
w := 0;

select case when to_char(dat,'D')=1 and yrch_weekend_sun=1 then 1
                        when to_char(dat,'D')=2 and yrch_weekend_mon=1 then 1
                        when to_char(dat,'D')=3 and yrch_weekend_tue=1 then 1
                        when to_char(dat,'D')=4 and yrch_weekend_wed=1 then 1
                        when to_char(dat,'D')=5 and yrch_weekend_thu=1 then 1
                        when to_char(dat,'D')=6 and yrch_weekend_fri=1 then 1
                        when to_char(dat,'D')=7 and yrch_weekend_sat=1 then 1
                        else 0 end into w from yrch where yrch_id = rid;

return w;

end;

-- Start of DDL Script for Function V8LIVE.M_YRCH_PRICE_ANT_V1
-- Generated 27-aae-2022 16:36:53 from V8LIVE@V8
/


CREATE OR REPLACE
FUNCTION m_crstofid(id varchar2)
return number
as
r number;
begin

select mcnv.mcnv_pms_code into r from mcnv where mcnv.mcnv_zcnv_id =
(select zota.zota_zcnv_id from zota where lower(zota.zota_requestorurl) like '%travelline%' and rownum = 1
)
--and mcnv.mcnv_type in ('RC', 'RT')
and mcnv.mcnv_crs_code = id;

return r;
end;
/


CREATE OR REPLACE
FUNCTION m_fidtocrs(id number)
return varchar2
as
r varchar2(100);
begin
select mcnv.mcnv_crs_code into r from mcnv where mcnv.mcnv_zcnv_id = m_getzcnvid
and mcnv.mcnv_pms_code = id and rownum = 1;
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE
function m_getrate(id ydet.ydet_id%type)
return ydet.ydet_rateamount%type
as
r ydet.ydet_ratemanual%type;
begin
    r := 0;
    select (decode(ydet.ydet_ratemanual,null,ydet.ydet_rateamount,ydet.ydet_ratemanual)+nvl(ydet.ydet_ratediff,0))
                     *((100-nvl(ydet.ydet_ratediscountperc,0))/100) into r
           from ydet
    where ydet_id = id;
    return r;
    exception when others then return null;
end;

-- Start of DDL Script for View V8LIVE.M_YDET_NOOFROOMS
-- Generated 01.10.2020 20:46:36 from V8LIVE@V8
/


CREATE OR REPLACE
function m_getrateavxmlbydate(bdate varchar2, edate varchar2) return clob as

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';

cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;
open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    c := 0;
    rm := '<AvailStatusMessage>';
    rm := rm || '<StatusApplicationControl';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '" Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '"/>';

    rm := rm || '<RestrictionStatus Status="';
    select m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, 1, 0) into ratev from dual;
    if ratev = 0 then rm := rm || 'Closed';
        else rm := rm || 'Open';
    end if;

    rm := rm || '"/></AvailStatusMessage>';


    end loop;
    close gdat;

    q := q || rm;
    rm := '';

    end loop;
    close gt;
    end loop;
    close gd;


q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';


return q;


end;
/


CREATE OR REPLACE
function m_getrateupxml(months number) return clob as
--declare
--months number;
cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin
--months := 1;
q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';

dbms_output.put_line('Opening details');

open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;

    dbms_output.put_line(to_char(rd.yrcd_id));
    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';

    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="';
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="';
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/>';
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;
/


CREATE OR REPLACE
FUNCTION m_yrch_price_ant_v1(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ADULTS in number, CALCDAYTYPE in NUMBER)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
    pap number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;

select
        nvl(decode(m_is_weekend(YRCHID_FINAL, WDATDATE), 1,
            yrcv_weekend_value, 0, yrcv_value, 0), 0) into RatePrice
from yrct, yrch, yrcd, yrcv
where
    yrcd_id=yrcv_yrcd_id
    and yrch_id=yrcd_yrch_id
    and yrcv.yrcv_default = 0
    and yrcv_noofadults=ADULTS
    and yrcd_id=yrct_yrcd_id
    and yrch.yrch_id = YRCHID_FINAL
    and yrcd.yrcd_enddate >= WDATDATE
    and yrcd.yrcd_startdate <= WDATDATE
    and yrct_ycat_id=YCATID;



    pap := 0;
    begin
 select sign(rateprice) * nvl(sum(decode(ypap.ypap_type, 1, ypap_value * ADULTS, 2, ypap_value, 0)), 0) into pap
 from ypac, ypat, ypap, yrch, yrcp
 where yrch_id = yrcp.yrcp_yrch_id
 and yrcp.yrcp_ypac_id = ypac.ypac_id
 and ypac.ypac_id = ypat.ypat_ypac_id
 and ypat.ypat_from <= WDATDATE
 and ypat.ypat_until >= WDATDATE
 and ypap.ypap_ypat_id = ypat.ypat_id
 and yrch_id = YRCHID
 and ypap_xcac_id = 0
 and ypac_included = 0;
          exception when no_data_found then null;

end;


 SELECT NVL (MAX (yrdv_value), 0)
  INTO daytypeprice
  FROM (SELECT yrdv_value
          FROM yrdc,
                yrdt,
                yrdd,
                yrdy,
                yrde,
                yrdv,
                yrch
          WHERE     yrdc_yrdt_id = yrdt_id
                AND yrdt_id = yrdd_yrdt_id
                AND yrde_id = yrdy_yrde_id
                AND yrdd_id = yrde_yrdd_id
                AND yrdy_id = yrdv_yrdy_id
                AND yrdd_yrds_id = yrch_yrds_id
                AND yrdv_adultcount = 1
                AND wdatdate BETWEEN NVL (yrde_startdate, wdatdate)
                                 AND NVL (yrde_enddate, wdatdate)
                AND yrdy_ycat_id = ycatid
                AND yrch_id = yrchid_final
                AND yrdc_date = wdatdate);

    if CALCDAYTYPE=1 then
        PRICE:=pap + RatePrice+DayTypePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*(RatePrice+DayTypePrice));
        else
        PRICE:=pap + RatePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*RatePrice);
    end if;
return round(PRICE,0);
END;
/


CREATE OR REPLACE
FUNCTION m_yrch_price_dm(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ca yrcx.yrcx_id%type)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;
    select nvl(max(Rate_price),0) into RatePrice
        from
        (select wdat_date, yrcd_id, yrcd_yrch_id, yrct_ycat_id,
            nvl(
                (select
                    decode(m_is_weekend(YRCHID_FINAL, wdatdate), 1, yrcx_weekend_value, 0, yrcx.yrcx_value, 0)
                   from yrch, yrcd, yrcx, (select * from yrcv where yrcv.yrcv_default = 1) yrcv
                where yrcd_id=yrcv_yrcd_id and yrch_id=yrcd_yrch_id and yrcd.yrcd_id=y.yrcd_id and rownum=1
                                                and yrcv_id = yrcx.yrcx_yrcv_id and yrcx.yrcx_xcac_id = ca),0) as Rate_price
            from wdat,
                (select nvl(yrse_startdate,yrcd_startdate) st, nvl(yrse_enddate,yrcd_enddate) en, yrcd.* From yrcd, yrse
                    where yrcd_yrse_id=yrse_id(+)) y, yrct
                where wdat_date between st and en and yrcd_id=yrct_yrcd_id
                    and yrct_ycat_id=YCATID and wdat_date = WDATDATE and yrcd_yrch_id=YRCHID_FINAL);
return round(RATEPRICE,0);
END;
/


CREATE OR REPLACE
function m_getrateupxmlbydate(bdate varchar2, edate varchar2) return clob as
--declare

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';
        --and mcnv.mcnv_crs_code = '286536'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        --and yrcd.yrcd_enddate >= v8_sys_fideliodate
        --and yrcd.yrcd_startdate <= to_date(edate, 'dd.mm.yyyy')
        --and yrcd.yrcd_enddate >= to_date(bdate, 'dd.mm.yyyy');

--cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
--        order by yrcv_default, yrcv_noofadults;
--cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
--    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
--    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');
cursor gn is select wnum.wnum_value from wnum where wnum_value >= 1 and wnum_value <= 6;
cursor gc is select * from xcac, mcnv where xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA'
    and nvl(mcnv.mcnv_inactive, 0) = 0 and mcnv.mcnv_zcnv_id = m_getzcnvid;


dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
--rx gx%rowtype;
rn number;
rc gc%rowtype;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><RateAmountMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;

    c := 0;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';

    open gdat;
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gn;
        loop fetch gn into rn;
        exit when gn%notfound;
                rma := '';

                rma := rma || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                --rm := rm || chr(10) || '<BaseByGuestAmt AmountAfterTax="';
                select nvl(m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, rn, 1), 0) into ratev from dual;
                c := c + ratev;
                rma := rma || to_char(ratev);
                rma := rma || '" CurrencyCode="RUB" NumberOfGuests="';
                rma := rma || to_char(rn);
                rma := rma || '"/>';
                if (ratev > 0) then rm := rm || rma; end if;
                rma := '';

        end loop;
        close gn;

        rm := rm || '</BaseByGuestAmts>';
        rms := '<AdditionalGuestAmounts>';
        rma := '';
        cd := 0;
        open gc;
        loop fetch gc into rc;
        exit when gc%notfound;
            rma := rma || '<AdditionalGuestAmount AgeQualifyingCode="';
            rma := rma || rc.mcnv_crs_code;
            rma := rma || '" Amount="';
            select nvl(m_yrch_price_dm(rd.yrch_id, rt.ycat_id, dat, rc.xcac_id), 0) into ratev from dual;
            rma := rma || to_char(ratev);
            cd := cd + ratev;
            rma := rma || '" CurrencyCode="RUB" TaxInclusive="true"/>';
            if (ratev > 0) then rms := rms || rma; end if;
            rma := '';

        end loop;
        close gc;
    rms := rms || '</AdditionalGuestAmounts>';

    if cd > 0 then rm := rm || rms; end if;

    rm := rm || '</Rate>';
    end loop;
    close gdat;

    rm := rm || '</Rates></RateAmountMessage>';
    if c > 0 then q := q || rm; c := 0; end if;
    rm := '';


    end loop;
    close gt;
    end loop;
    close gd;


q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('https://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);

return q;


end;
/


CREATE OR REPLACE
function m_getrateupxmldt(months number)
return clob
as
--declare

cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';


open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;


    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';

    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="';
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="';
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/> ';
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;
/


--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
--select * from yrcv
--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
/


CREATE OR REPLACE
FUNCTION m_getzcnvid
return number
as
r number;
begin
select zota.zota_zcnv_id into r from zota where zota_name like 'TravelLine';
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE
FUNCTION m_getroomavxmlbydate(bdate varchar2, edate varchar2) return clob as


cursor gt is select ycat.*, mcnv.* from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT';-- and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;

q clob;
rm clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www/opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    begin
        select avl into c from m_avl where wdat_date = dat and ycat_id = rt.ycat_id;
        rm := '<AvailStatusMessage BookingLimitMessageType="2" BookingLimit="' || to_char(c) || '">';
        rm := rm || '<StatusApplicationControl';

        rm := rm || ' InvTypeCode="';
        rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
        rm := rm || '" Start="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '" End="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '"/>';

        rm := rm || '</AvailStatusMessage>';
    exception when no_data_found then null;
    end;

    end loop;
    close gdat;

    q := q || rm;
    rm := '';

    end loop;

    close gt;

q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';

return q;

end;
/
"""

def get_embedded_schema() -> str:
    """Return the embedded RATELOADER schema SQL."""
    return RATELOADER_SCHEMA_SQL
