REM ---------------------------------------------------------
REM About Author of this Script
REM Ronney Rajapathirana
REM Created for BCD Travels
REM Project : eTours Automation Project
REM ---------------------------------------------------------

REM ---------------------------------------------------------
REM Setting up the Environmental Variables
REM ---------------------------------------------------------

set feedback off
set echo off
set verify off

set pagesize 50000;
set pages 0;
SET LINESIZE 20000;

set term off;
set heading off;
set headsep off;

set termout off;

set trimspool on;
set wrap off;
SET SQLBLANKLINES Off;


REM ---------------------------------------------------------
REM Configuring the Spool File Name with Date and Time Stamp
REM ---------------------------------------------------------

	col sysdt noprint new_value sysdt;
   	SELECT TO_CHAR(SYSDATE, 'ddmmyyyy_hh24miss') sysdt FROM DUAL;

REM	mem_filename:=Z:\Staff-Files\eTours-Bulk_Data\exported_etours_data_.&sysdt..csv;

REM	spool &mem_filename.;


	spool D:\eTravelVer3\eToursBAckup\exp_etours_summary_&sysdt..csv;

REM ---------------------------------------------------------
REM Select the Column Headings
REM ---------------------------------------------------------

select 'SEQ'||','||'FREQUENCY'||','||'BRANCH'||','||'COUNT'||','||'SALES'||','||'COST'||','||'PROFIT'||','||'PROFIT_RATIO' from dual;

REM ---------------------------------------------------------
REM Formatting Columns
REM ---------------------------------------------------------

COLUMN TBK_INVOICE_TOTAL FORMAT 99,999,999.99
COLUMN TBK_PROVISION_TOTAL FORMAT 99,999,999.99

REM ---------------------------------------------------------
REM Selecting Data from Data Tables
REM ---------------------------------------------------------


REM Start Script

select '01'||','||'YTD'||','||'HO'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke014t3_support.id_tb_booking_header where tbk_customer<>'S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),-1)+30 from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '02'||','||'MTD'||','||'HO'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke014t3_support.id_tb_booking_header where tbk_customer<>'S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(select LAST_DAY(ADD_MONTHS(sysdate, -1)) from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '03'||','||'Yesterday'||','||'HO'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke014t3_support.id_tb_booking_header where tbk_customer<>'S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(select to_date(sysdate-1) from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '04'||','||'YTD'||','||'SAFCOM-HO'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke014t3_support.id_tb_booking_header where tbk_customer='S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),-1)+30 from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '05'||','||'YTD'||','||'SAFCOM-NB'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(case when TBK_PROVISION_TOTAL>TBK_INVOICE_TOTAL then TBK_INVOICE_TOTAL else TBK_PROVISION_TOTAL end),0)||','||round(((sum(TBK_INVOICE_TOTAL)-sum(case when TBK_PROVISION_TOTAL>TBK_INVOICE_TOTAL then TBK_INVOICE_TOTAL else TBK_PROVISION_TOTAL end))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)-sum(case when TBK_PROVISION_TOTAL>TBK_INVOICE_TOTAL then TBK_INVOICE_TOTAL else TBK_PROVISION_TOTAL end))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke074t3_support.id_tb_booking_header where tbk_customer='S0171'and tbk_status<>'F' and tbk_booking_type in ('IB','OB') and tbk_date>(SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'),-1)+30 from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '06'||','||'MTD'||','||'SAFCOM-NB'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke074t3_support.id_tb_booking_header where tbk_customer='S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(select LAST_DAY(ADD_MONTHS(sysdate, -1)) from dual) and TBK_NEXT_PROCESS_NUMBER>3;
select '07'||','||'Yesterday'||','||'SAFCOM-NB'||','||count(TBK_BOOKING_NUMBER)||','||(round(sum(TBK_INVOICE_TOTAL),0))||','||round(sum(TBK_PROVISION_TOTAL),0)||','||round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)||','||((round((((round(((sum(TBK_INVOICE_TOTAL)- sum(TBK_PROVISION_TOTAL))),0)))/round(sum(TBK_INVOICE_TOTAL),0)),2))*100) from ke074t3_support.id_tb_booking_header where tbk_customer='S0171'and tbk_status<>'F'and tbk_booking_type in ('IB','OB') and tbk_date>(select to_date(sysdate-1) from dual) and TBK_NEXT_PROCESS_NUMBER>3;
spool off;


REM ---------------------------------------------------------
REM Data File Has been Created
REM ---------------------------------------------------------

quit;