SearchDefinitions: moduleName: impactemailer summary: Weekly impact emailer cron version: 1.0.0 definitions: - name: stateWideData query: baseQuery: select array_to_json(array_agg(row_to_json(stateWideReports))) from ((SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= (extract(epoch from NOW())) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= (extract(epoch from NOW())) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= (extract(epoch from NOW())) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= (extract(epoch from NOW())) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= (extract(epoch from NOW())) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected,(SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= (extract(epoch from NOW())) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= (extract(epoch from NOW())) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE (createddate <= NOW()) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week0' as day) UNION (SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected,(SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE createddate <= to_timestamp((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week1' as day) UNION (SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected,(SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE createddate <= to_timestamp((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week2' as day) UNION (SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected,(SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE createddate <= to_timestamp((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week3' as day) UNION (SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected,(SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE createddate <= to_timestamp((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week4' as day) UNION (SELECT (SELECT count(*) FROM ((SELECT DISTINCT tenantid FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') UNION (SELECT DISTINCT tenantid FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')) AS tenants) as ulbCovered,(SELECT TRUNC(((SELECT SUM(ins.amount) FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') + (SELECT SUM(ins.amount) FROM eg_tl_tradelicense tl INNER JOIN egcl_receiptheader_v1 rh ON tl.applicationnumber = rh.consumercode INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tl.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) / 10000000, 3)) as revenueCollected, (SELECT (SELECT count(*) FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') + (SELECT count(*) FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')) as servicesApplied, (SELECT count(*) FROM eg_user WHERE createddate <= to_timestamp((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing' AND type = 'CITIZEN') as noOfUsersRegistered, 'Week5' as day)) stateWideReports searchParams: condition: AND params: - name: intervalinsecs isMandatory: true jsonPath: $.searchCriteria.intervalinsecs output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo - name: pgrData query: baseQuery: select array_to_json(array_agg(row_to_json(pgrReports))) from ((SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week0' as day FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') != 0 THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week1' as day FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') != 0 THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week2' as day FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') != 0 THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week3' as day FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') != 0 THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week4' as day FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing'), count(*) as totalComplaints, (SELECT (CASE WHEN (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') != 0 THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE status = 'closed' OR status = 'resolved' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week5' as day FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')) pgrReports searchParams: condition: AND params: - name: intervalinsecs isMandatory: true jsonPath: $.searchCriteria.intervalinsecs output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo - name: pgrChannelData query: baseQuery: select array_to_json(array_agg(row_to_json(pgrChannel))) from ((SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as ivr), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as webapp),(SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as mobileapp), 'Week0' as day FROM eg_pgr_service LIMIT 1) UNION (SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal),(SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week1' as day FROM eg_pgr_service LIMIT 1) UNION (SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal),(SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week2' as day FROM eg_pgr_service LIMIT 1) UNION (SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week3' as day FROM eg_pgr_service LIMIT 1) UNION (SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week4' as day FROM eg_pgr_service LIMIT 1) UNION (SELECT (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'ivr' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'web' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), (SELECT (CASE WHEN ((SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') != 0) THEN CONCAT(((SELECT count(*) FROM eg_pgr_service WHERE source = 'mobileapp' AND (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing') * 100 / (SELECT count(*) FROM eg_pgr_service WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing')), '%') ELSE '0%' END) as redressal), 'Week5' as day FROM eg_pgr_service LIMIT 1)) pgrChannel searchParams: condition: AND params: - name: intervalinsecs isMandatory: true jsonPath: $.searchCriteria.intervalinsecs output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo - name: ptData query: baseQuery: select array_to_json(array_agg(row_to_json(ptReports))) from ((SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week0' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= (extract(epoch from NOW())) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week1' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week2' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week3' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week4' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED') UNION (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_pt_property_v2 WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing'), TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, CONCAT((count(DISTINCT pt.propertyid) / 1000), '.', MOD(count(DISTINCT pt.propertyid), 1000)) as noofpropertiescreated, 'Week5' as day FROM eg_pt_property_v2 pt INNER JOIN egcl_receiptheader_v1 rh ON pt.propertyid = SPLIT_PART(rh.consumercode, ':', 1) INNER JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id WHERE (rh.receiptdate/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND pt.tenantid != 'pb.testing' AND rh.status != 'Cancelled' AND ins.instrumentstatus != 'CANCELLED')) ptReports searchParams: condition: AND params: - name: intervalinsecs isMandatory: true jsonPath: $.searchCriteria.intervalinsecs output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo - name: tlData query: baseQuery: select array_to_json(array_agg(row_to_json(tlReports))) from (SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing' ), count(*) as licenseIssued, 'Week0' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= (extract (epoch from NOW())) AND tenantid != 'pb.testing' AND status = 'APPROVED' UNION SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing'), count(*) as licenseIssued, 'Week1' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800) AND tenantid != 'pb.testing' AND status = 'APPROVED' UNION SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing'), count(*) as licenseIssued, 'Week2' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 2) AND tenantid != 'pb.testing' AND status = 'APPROVED' UNION SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing'), count(*) as licenseIssued, 'Week3' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 3) AND tenantid != 'pb.testing' AND status = 'APPROVED' UNION SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing'), count(*) as licenseIssued, 'Week4' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 4) AND tenantid != 'pb.testing' AND status = 'APPROVED' UNION SELECT (SELECT count(DISTINCT tenantid) as ulbCovered FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing'), count(*) as licenseIssued, 'Week5' as day FROM eg_tl_tradelicense WHERE (createdtime/1000) <= ((extract (epoch from (to_timestamp(CONCAT(CURRENT_DATE + integer '1'), 'YYYY-MM-DD H24:MI:SS')))) - 604800 * 5) AND tenantid != 'pb.testing' AND status = 'APPROVED') tlReports searchParams: condition: AND params: - name: intervalinsecs isMandatory: true jsonPath: $.searchCriteria.intervalinsecs output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo - name: miscCollections query: baseQuery: SELECT array_to_json(array_agg(row_to_json(misccollections))) from ((SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week0' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= (extract(epoch from NOW()))) UNION (SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week1' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= ((extract(epoch from NOW())) - 604800)) UNION (SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week2' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= ((extract(epoch from NOW())) - 604800 * 2)) UNION (SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week3' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= ((extract(epoch from NOW())) - 604800 * 3)) UNION (SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week4' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= ((extract(epoch from NOW())) - 604800 * 4)) UNION (SELECT COUNT(*) as receiptscreated, TRUNC(SUM(ins.amount) / 10000000, 3) as revenuecollected, 'Week5' as day FROM egcl_receiptheader_v1 rh LEFT JOIN egcl_receiptinstrument_v1 rins ON rh.id = rins.receiptheader INNER JOIN egcl_instrumentheader_v1 ins ON rins.instrumentheader = ins.id $where AND (rh.receiptdate/1000) <= ((extract(epoch from NOW())) - 604800 * 5))) misccollections searchParams: condition: AND params: - name: rh.tenantid isMandatory: true operator: NE jsonPath: $.searchCriteria.ignoreTenant - name: rh.businessdetails isMandatory: false operator: IN jsonPath: $.searchCriteria.taxHeads - name: rh.businessdetails isMandatory: false operator: NOT IN jsonPath: $.searchCriteria.ignoreTaxHeads - name: rh.status isMandatory: true operator: NE jsonPath: $.searchCriteria.ignoreStatus output: jsonFormat: {"ResponseInfo": {}} outJsonPath: $.data responseInfoPath: $.ResponseInfo