--- ReportDefinitions: - reportName: ReceiptRegister summary: Receipt Register version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: receiptnumber label: reports.pt.receiptNo type: string source: pt total: false - name: receiptdate label: reports.pt.receiptDate type: string source: pt total: false - name: g8issuedate label: reports.pt.g8issuedate type: string source: pt total: false - name: g8receiptno label: reports.pt.g8receiptno type: string source: pt total: false - name: amount label: reports.pt.amount type: string source: pt total: true - name: propertytax label: reports.pt.propertyTax type: string source: pt total: true - name: firecess label: reports.pt.fireCess type: string source: pt total: true - name: cancercess label: reports.pt.cancerCess type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.roundoff type: string source: pt total: true - name: pendingamount label: reports.pt.pendingamount type: string source: pt total: true - name: propertyid label: reports.pt.propertyId type: string source: pt total: false - name: usagetype label: reports.pt.usagetype type: string source: pt total: false - name: assessmentnumber label: reports.pt.assessmentNo type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: instrumenttype label: reports.pt.paymentMode type: string source: pt total: false - name: transactionnumber label: reports.pt.ddChequeTransactionNo type: string source: pt total: false - name: transactiondate label: reports.pt.ddChequeTransactionDate type: string source: pt total: false - name: bankname label: reports.pt.bankName type: string source: pt total: false - name: collectorname label: reports.pt.collectorname type: string source: pt total: false - name: remarks label: reports.pt.remarks type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate - name: paymentMode label: Payment Mode type: singlevaluelist pattern: 'list://Cash:Cash,Online:Online,Card:Card,DD:DD,Cheque:Cheque' source: pt isMandatory: false searchClause: AND instrumenttype = $paymentMode - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pt_addr.locality IN ($localityArray) query: | -- Use CTE to force query plan, compute & use CTE later WITH receipt_breakup AS ( SELECT receiptheader, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails='PT' GROUP BY rd.receiptheader ) SELECT rh.tenantid AS tenantid, rh.id, Substring(rh.consumercode, '(.*):') AS propertyid, Substring(rh.consumercode, ':(.*)') AS assessmentnumber, rh.consumercode, to_char((To_timestamp(rh.receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS receiptdate, bankid AS bankname, receiptnumber, amount, instrumenttype, transactionnumber, to_char((To_timestamp(transactiondate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS transactiondate, receipt_breakup.*, rh.totalamount - ih.amount AS pendingamount, pt_detail.financialyear, to_char((To_timestamp(rh.manualreceiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS g8issuedate, Initcap(COALESCE(pt_detail.usagecategoryminor, pt_detail.usagecategorymajor)) as usagetype, manualreceiptnumber as g8receiptno, (CASE WHEN collectiontype='COUNTER' THEN eg_user.name ELSE '' END) as collectorname, pt_detail.additionaldetails->>'remarks' as "remarks" FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN receipt_breakup ON rh.id = receipt_breakup.receiptheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails='PT' orderby: ORDER BY rh.receiptdate DESC - reportName: CancelledReceiptRegister summary: Receipt Register for cancelled Receipts version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: receiptnumber label: reports.pt.receiptNo type: string source: pt total: false - name: status label: reports.pt.status type: string source: pt total: false - name: transactiondate label: reports.pt.receiptDate type: string source: pt total: false - name: g8issuedate label: reports.pt.g8issuedate type: string source: pt total: false - name: g8receiptno label: reports.pt.g8receiptno type: string source: pt total: false - name: amount label: reports.pt.amount type: string source: pt total: true - name: propertytax label: reports.pt.propertyTax type: string source: pt total: true - name: firecess label: reports.pt.fireCess type: string source: pt total: true - name: cancercess label: reports.pt.cancerCess type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.roundoff type: string source: pt total: true - name: propertyid label: reports.pt.propertyId type: string source: pt total: false - name: usagetype label: reports.pt.usagetype type: string source: pt total: false - name: assessmentnumber label: reports.pt.assessmentNo type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: instrumenttype label: reports.pt.paymentMode type: string source: pt total: false - name: transactionnumber label: reports.pt.ddChequeTransactionNo type: string source: pt total: false - name: bankname label: reports.pt.bankName type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate <= $toDate - name: paymentMode label: Payment Mode type: singlevaluelist pattern: 'list://Cash:Cash,Online:Online,Card:Card,DD:DD,Cheque:Cheque' source: pt isMandatory: false searchClause: AND instrumenttype = $paymentMode - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pt_addr.locality IN ($localityArray) query: | -- Use CTE to force query plan, compute & use CTE later WITH receipt_breakup AS ( SELECT receiptheader, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid = $tenantid and rh.status = 'Cancelled' and businessdetails='PT' GROUP BY rd.receiptheader ) SELECT rh.tenantid AS tenantid, rh.id, Substring(rh.consumercode, '(.*):') AS propertyid, Substring(rh.consumercode, ':(.*)') AS assessmentnumber, rh.status, rh.consumercode, rh.receiptdate, to_char((To_timestamp(rh.receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS transactiondate, bankid AS bankname, receiptnumber, amount, instrumenttype, transactionnumber, receipt_breakup.*, rh.totalamount - ih.amount AS pendingamount, pt_detail.financialyear, to_char((To_timestamp(rh.manualreceiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS g8issuedate, Initcap(COALESCE(pt_detail.usagecategoryminor, pt_detail.usagecategorymajor)) as usagetype, manualreceiptnumber as g8receiptno FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN receipt_breakup ON rh.id = receipt_breakup.receiptheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property WHERE rh.tenantid = $tenantid and rh.status = 'Cancelled' and businessdetails='PT' orderby: ORDER BY rh.receiptdate DESC - reportName: OldUlbWisePtCollection summary: ULB-wise PT Collection version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: totalassessments label: reports.pt.totalassessments type: string source: pt total: true - name: totalamountpaid label: reports.pt.totalamountpaid type: string source: pt total: true - name: totalamountdue label: reports.pt.totalamountdue type: string source: pt total: true searchParams: - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: usage label: Usage type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=PropertyTax&masterName=UsageCategoryMajor|$..code|$..name source: pt wrapper: true isMandatory: false searchClause: AND usagecategorymajor = $usage query: | SELECT tenantid, COUNT(*) AS totalassessments, SUM(totalamount) AS totalamount, SUM(amount) AS totalamountpaid, (SUM(totalamount) - SUM(amount)) AS totalamountdue FROM eg_pt_propertydetail_v2 AS pd LEFT OUTER JOIN (SELECT consumercode, totalamount, amount FROM egcl_receiptheader_v1 AS rh LEFT OUTER JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader LEFT OUTER JOIN egcl_instrumentheader_v1 AS ih ON ri.instrumentheader = ih.id and ih.instrumentstatus != 'CANCELLED' where rh.status != 'Cancelled' ) AS instrument_data ON (pd.property || ':' || pd.assessmentnumber) = instrument_data.consumercode WHERE tenantid = $tenantid groupby: GROUP BY tenantid orderby: ORDER BY tenantid, totalamountpaid DESC NULLS LAST - reportName: StateCancelledReceiptRegister summary: Receipt Register for cancelled Receipts State version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: tenantid label: reports.pt.ulbName type: string source: pt total: false - name: receiptnumber label: reports.pt.receiptNo type: string source: pt total: false - name: status label: reports.pt.status type: string source: pt total: false - name: transactiondate label: reports.pt.receiptDate type: string source: pt total: false - name: g8issuedate label: reports.pt.g8issuedate type: string source: pt total: false - name: g8receiptno label: reports.pt.g8receiptno type: string source: pt total: false - name: amount label: reports.pt.amount type: string source: pt total: true - name: propertytax label: reports.pt.propertyTax type: string source: pt total: true - name: firecess label: reports.pt.fireCess type: string source: pt total: true - name: cancercess label: reports.pt.cancerCess type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.roundoff type: string source: pt total: true - name: propertyid label: reports.pt.propertyId type: string source: pt total: false - name: usagetype label: reports.pt.usagetype type: string source: pt total: false - name: assessmentnumber label: reports.pt.assessmentNo type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: instrumenttype label: reports.pt.paymentMode type: string source: pt total: false - name: transactionnumber label: reports.pt.ddChequeTransactionNo type: string source: pt total: false - name: bankname label: reports.pt.bankName type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate <= $toDate - name: paymentMode label: Payment Mode type: singlevaluelist pattern: 'list://Cash:Cash,Online:Online,Card:Card,DD:DD,Cheque:Cheque' source: pt isMandatory: false searchClause: AND instrumenttype = $paymentMode - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear query: | -- Use CTE to force query plan, compute & use CTE later WITH receipt_breakup AS ( SELECT receiptheader, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid !='pb.testing' and rh.status = 'Cancelled' and businessdetails='PT' GROUP BY rd.receiptheader ) SELECT rh.tenantid AS tenantid, rh.id, Substring(rh.consumercode, '(.*):') AS propertyid, Substring(rh.consumercode, ':(.*)') AS assessmentnumber, rh.status, rh.consumercode, rh.receiptdate, to_char((To_timestamp(rh.receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS transactiondate, bankid AS bankname, receiptnumber, amount, instrumenttype, transactionnumber, receipt_breakup.*, rh.totalamount - ih.amount AS pendingamount, pt_detail.financialyear, to_char((To_timestamp(rh.manualreceiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS g8issuedate, Initcap(COALESCE(pt_detail.usagecategoryminor, pt_detail.usagecategorymajor)) as usagetype, manualreceiptnumber as g8receiptno FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN receipt_breakup ON rh.id = receipt_breakup.receiptheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property WHERE rh.tenantid != 'pb.testing' and rh.status = 'Cancelled' and businessdetails='PT' orderby: ORDER BY rh.receiptdate DESC - reportName: StateWidePtCollection summary: State Wide PT Collection version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: tenantid label: reports.pt.ulbName type: string source: pt total: false - name: totalassessments label: reports.pt.totalassessments type: string source: pt total: true - name: totalamountpaid label: reports.pt.totalamountpaid type: string source: pt total: true - name: totaloffline label: reports.pt.totaloffline type: string source: pt total: true - name: totalonline label: reports.pt.totalonline type: string source: pt total: true - name: totalamountdue label: reports.pt.totalamountdue type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND receiptdate <= $toDate - name: ulb label: ULB type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=tenant&masterName=tenants|$.MdmsRes.tenant.tenants.*.code|$.MdmsRes.tenant.tenants.*.name source: pt wrapper: true isMandatory: false searchClause: AND tenantid = $ulb - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: usage label: Usage type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=PropertyTax&masterName=UsageCategoryMajor|$..code|$..name source: pt wrapper: true isMandatory: false searchClause: AND usagecategorymajor = $usage query: | SELECT initcap(split_part(tenantid, '.', 2)) as tenantid, COUNT(*) AS totalassessments, SUM(totalamount) AS totalamount, SUM(amount) AS totalamountpaid, SUM(CASE WHEN collectiontype LIKE 'ONLINE' THEN amount ELSE 0 END) AS totalonline, SUM(CASE WHEN collectiontype LIKE 'COUNTER' THEN amount ELSE 0 END) AS totaloffline, (SUM(totalamount) - SUM(amount)) AS totalamountdue FROM eg_pt_propertydetail_v2 AS pd LEFT OUTER JOIN (SELECT consumercode, totalamount, amount, collectiontype, receiptdate FROM egcl_receiptheader_v1 AS rh LEFT OUTER JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader LEFT OUTER JOIN egcl_instrumentheader_v1 AS ih ON ri.instrumentheader = ih.id and ih.instrumentstatus != 'CANCELLED' where rh.status != 'Cancelled' and rh.tenantid != 'pb.testing' and businessdetails='PT' ) AS tbl_amounts ON (pd.property || ':' || pd.assessmentnumber) = tbl_amounts.consumercode WHERE tenantid not in ('pb.testing','pb.ludhiana_legacy') groupby: GROUP BY tenantid orderby: ORDER BY tenantid, totalamountpaid DESC NULLS LAST - reportName: UlbWisePtCollection summary: State Wide PT Collection version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: totalassessments label: reports.pt.totalassessments type: string source: pt total: true - name: totalamountpaid label: reports.pt.totalamountpaid type: string source: pt total: true - name: totaloffline label: reports.pt.totaloffline type: string source: pt total: true - name: countoffline label: reports.pt.countoffline type: string source: pt total: true - name: totalonline label: reports.pt.totalonline type: string source: pt total: true - name: countonline label: reports.pt.countonline type: string source: pt total: true - name: totalamountdue label: reports.pt.totalamountdue type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND receiptdate <= $toDate - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: usage label: Usage type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=PropertyTax&masterName=UsageCategoryMajor|$..code|$..name source: pt wrapper: true isMandatory: false searchClause: AND usagecategorymajor = $usage query: | SELECT initcap(split_part(tenantid, '.', 2)) as tenantid, COUNT(collectiontype) AS totalassessments, SUM(totalamount) AS totalamount, SUM(amount) AS totalamountpaid, SUM(CASE WHEN collectiontype LIKE 'ONLINE' THEN amount ELSE 0 END) AS totalonline, SUM(CASE WHEN collectiontype LIKE 'ONLINE' THEN 1 ELSE 0 END) AS countonline, SUM(CASE WHEN collectiontype LIKE 'COUNTER' THEN amount ELSE 0 END) AS totaloffline, SUM(CASE WHEN collectiontype LIKE 'COUNTER' THEN 1 ELSE 0 END) AS countoffline, (SUM(totalamount) - SUM(amount)) AS totalamountdue FROM eg_pt_propertydetail_v2 AS pd LEFT OUTER JOIN (SELECT consumercode, totalamount, amount, collectiontype, receiptdate FROM egcl_receiptheader_v1 AS rh LEFT OUTER JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader LEFT OUTER JOIN egcl_instrumentheader_v1 AS ih ON ri.instrumentheader = ih.id and ih.instrumentstatus != 'CANCELLED' where rh.status != 'Cancelled' ) AS tbl_amounts ON (pd.property || ':' || pd.assessmentnumber) = tbl_amounts.consumercode WHERE tenantid LIKE CONCAT(SPLIT_PART($tenantid, '.', 1), '%') and tenantid = $tenantid groupby: GROUP BY tenantid orderby: ORDER BY tenantid, totalamountpaid DESC NULLS LAST - reportName: ULBPTDataDumpReportOld summary: ULB level report version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: ulb label: reports.pt.ulb type: string source: pt total: false - name: propertytaxuniqueid label: reports.pt.propertytaxuniqueid type: string source: pt total: false - name: transcationdate label: reports.pt.transcationdate type: string source: pt total: false - name: receiptnumber label: reports.pt.receiptnumber type: string source: pt total: false - name: existingpropertyid label: reports.pt.existingpropertyid type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: ownername label: reports.pt.ownername type: string source: pt total: false - name: guardianname label: reports.pt.guardianname type: string source: pt total: false - name: totalarea label: reports.pt.totalarea type: string source: pt total: false - name: builtarea label: reports.pt.builtarea type: string source: pt total: false - name: floors label: reports.pt.floors type: string source: pt total: false - name: occupancy label: reports.pt.occupancy type: string source: pt total: false - name: propertytype label: reports.pt.propertytype type: string source: pt total: false - name: mobileno label: reports.pt.mobileno type: string source: pt total: false - name: transcationmode label: reports.pt.transcationmode type: string source: pt total: false - name: paymentamount label: reports.pt.paymentamount type: string source: pt total: true - name: paymentdate label: reports.pt.paymentdate type: string source: pt total: false - name: emailid label: reports.pt.emailid type: string source: pt total: false - name: address label: reports.pt.address type: string source: pt total: false - name: remarks label: reports.pt.remarks type: string source: pt total: false - name: businessname label: reports.pt.businessname type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate query: | --S.no Service Name ULB Property Tax Unique ID Date Of Transcation Receipt Number Existing Property ID Owner Name Guardian Name Total Area (SQ.YARD) Covered Area/Built Area (SQ.FEET) Floors Occupancy (Rented,Self /Unoccupied) Type Of Property Mobile Number Transcation Mode of Payment Payment Amount Payment Date Email Id Address with eg_pt_unit_v2_ot as ( select propertydetail, string_agg(distinct pu.occupancytype, ',') as occupancytype, string_agg(distinct coalesce(usagecategoryminor, usagecategorymajor), ',') as propertytype from eg_pt_unit_v2 as pu where pu.tenantid = $tenantid group by propertydetail ) select distinct on (rh.receiptnumber) initcap(split_part(rh.tenantid, '.', 2)) as "ulb", split_part(consumercode, ':', 1) as "propertytaxuniqueid", to_char((To_timestamp(receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS "transcationdate", receiptnumber as "receiptnumber", pt.oldpropertyid as "existingpropertyid", pd.financialyear as "financialyear", eu."name" as "ownername", eu.guardian as "guardianname", pd.landarea as "totalarea", pd.builduparea * 9 as "builtarea", pd.nooffloors as "floors", pot.occupancytype as "occupancy", pot.propertytype as "propertytype", eu.mobilenumber as "mobileno", instrumenttype as "transcationmode", ih.amount as "paymentamount", to_char((To_timestamp(ih.transactiondate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS "paymentdate", eu.emailid as "emailid" , REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(pta.doorno, ',', pta.addressline1, ',', pta.addressline2, ',', pta.buildingname, ',', pta.pincode), ',+', ','), '^,|,$', '') as "address", pd.additionaldetails->>'remarks' as "remarks", pt.additionaldetails->>'FirmBusinessName' as "businessname" from egcl_receiptheader_v1 as rh inner join egcl_receiptinstrument_v1 as ri on ri.receiptheader = rh.id inner join egcl_instrumentheader_v1 as ih on ih.id = ri.instrumentheader inner join eg_pt_property_v2 as pt on pt.propertyid = split_part(consumercode, ':', 1) inner join eg_pt_propertydetail_v2 as pd on pd.assessmentnumber = split_part(consumercode, ':', 2) inner join eg_pt_owner_v2 as po on po.propertydetail = pd.assessmentnumber inner join eg_user as eu on eu.uuid = po.userid inner join eg_pt_address_v2 as pta on pta.property = pt.propertyid left outer join eg_pt_unit_v2_ot as pot on pot.propertydetail = pd.assessmentnumber where rh.tenantid = $tenantid and rh.status != 'Cancelled' - reportName: ReceiptRegisterShort summary: Account Receipt Register version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "Legal" sourceColumns: - name: receiptnumber label: reports.pt.receiptNo type: string source: pt total: false - name: transactiondate label: reports.pt.receiptDate type: string source: pt total: false - name: g8receiptno label: reports.pt.g8book type: string source: pt total: false - name: g8issuedate label: reports.pt.g8dated type: string source: pt total: false - name: propertyid label: reports.pt.ptrId type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: instrumenttype label: reports.pt.paymentMode type: string source: pt total: false - name: amount label: reports.pt.taxamount type: string source: pt total: true - name: transactionnumber label: reports.pt.ddChequeNo type: string source: pt total: false - name: bankname label: reports.pt.bankName type: string source: pt total: false - name: collectorname label: reports.pt.collectorname type: string source: pt total: false - name: remarks label: reports.pt.remarks type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate - name: paymentMode label: Payment Mode type: singlevaluelist pattern: 'list://Cash:Cash,Online:Online,Card:Card,DD:DD,Cheque:Cheque' source: pt isMandatory: false searchClause: AND instrumenttype = $paymentMode - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pt_addr.locality IN ($localityArray) - name: collectorname label: reports.pt.collectorname type: singlevaluelist pattern: http://egov-hrms:8080/egov-hrms/employees/_search?tenantId=$tenantid&roles=PTCEMP|$.Employees[*].user.id|$.Employees[*].user.name source: pt wrapper: true isMandatory: false searchClause: AND eg_user.id = $collectorname::INTEGER query: | -- Use CTE to force query plan, compute & use CTE later WITH receipt_breakup AS ( SELECT receiptheader, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails ='PT' GROUP BY rd.receiptheader ) SELECT rh.tenantid AS tenantid, rh.id, Substring(rh.consumercode, '(.*):') AS propertyid, Substring(rh.consumercode, ':(.*)') AS assessmentnumber, rh.consumercode, rh.receiptdate, to_char((To_timestamp(rh.receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS transactiondate, bankid AS bankname, receiptnumber, amount, instrumenttype, transactionnumber, receipt_breakup.*, rh.totalamount - ih.amount AS pendingamount, pt_detail.financialyear, to_char((To_timestamp(rh.manualreceiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS g8issuedate, Initcap(COALESCE(pt_detail.usagecategoryminor, pt_detail.usagecategorymajor)) as usagetype, manualreceiptnumber as g8receiptno, (CASE WHEN collectiontype='COUNTER' THEN eg_user.name ELSE '' END) as collectorname, pt_detail.additionaldetails->>'remarks' as "remarks" FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN receipt_breakup ON rh.id = receipt_breakup.receiptheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails ='PT' orderby: ORDER BY rh.receiptdate DESC - reportName: ULBPTDataDumpReport summary: ULB level report version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: ulb label: reports.pt.ulb type: string source: pt total: false - name: propertytaxuniqueid label: reports.pt.propertytaxuniqueid type: string source: pt total: false - name: transcationdate label: reports.pt.transcationdate type: string source: pt total: false - name: receiptnumber label: reports.pt.receiptnumber type: string source: pt total: false - name: existingpropertyid label: reports.pt.existingpropertyid type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: ownername label: reports.pt.ownername type: string source: pt total: false - name: guardianname label: reports.pt.guardianname type: string source: pt total: false - name: totalarea label: reports.pt.totalarea type: string source: pt total: false - name: builtarea label: reports.pt.builtarea type: string source: pt total: false - name: floors label: reports.pt.floors type: string source: pt total: false - name: occupancy label: reports.pt.occupancy type: string source: pt total: false - name: propertytype label: reports.pt.propertytype type: string source: pt total: false - name: mobileno label: reports.pt.mobileno type: string source: pt total: false - name: transcationmode label: reports.pt.transcationmode type: string source: pt total: false - name: paymentamount label: reports.pt.paymentamount type: string source: pt total: true - name: paymentdate label: reports.pt.paymentdate type: string source: pt total: false - name: emailid label: reports.pt.emailid type: string source: pt total: false - name: address label: reports.pt.address type: string source: pt total: false - name: payeraddress label: reports.pt.payeraddress type: string source: pt total: false - name: remarks label: reports.pt.remarks type: string source: pt total: false - name: businessname label: reports.pt.businessname type: string source: pt total: false - name: localitycode label: reports.pt.localitycode type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND pd.financialyear = $financialyear - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pta.locality IN ($localityArray) query: | --S.no Service Name ULB Property Tax Unique ID Date Of Transcation Receipt Number Existing Property ID Owner Name Guardian Name Total Area (SQ.YARD) Covered Area/Built Area (SQ.FEET) Floors Occupancy (Rented,Self /Unoccupied) Type Of Property Mobile Number Transcation Mode of Payment Payment Amount Payment Date Email Id Address with eg_pt_unit_v2_ot as ( select propertydetail, string_agg(distinct pu.occupancytype, ',') as occupancytype, string_agg(distinct coalesce(usagecategoryminor, usagecategorymajor), ',') as propertytype from eg_pt_unit_v2 as pu where pu.tenantid = $tenantid group by propertydetail ) select distinct on (rh.receiptnumber) initcap(split_part(rh.tenantid, '.', 2)) as "ulb", split_part(consumercode, ':', 1) as "propertytaxuniqueid", to_char((To_timestamp(receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS "transcationdate", receiptnumber as "receiptnumber", pt.oldpropertyid as "existingpropertyid", pd.financialyear as "financialyear", eu."name" as "ownername", eu.guardian as "guardianname", pd.landarea as "totalarea", pd.builduparea * 9 as "builtarea", pd.nooffloors as "floors", pot.occupancytype as "occupancy", pot.propertytype as "propertytype", eu.mobilenumber as "mobileno", instrumenttype as "transcationmode", ih.amount as "paymentamount", to_char((To_timestamp(ih.transactiondate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS "paymentdate", eu.emailid as "emailid", REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(pta.doorno, ',', pta.addressline1, ',', pta.addressline2, ',', pta.buildingname, ',', pta.pincode), ',+', ','), '^,|,$', '') as "address", rh.payeraddress as "payeraddress", pd.additionaldetails->>'remarks' as "remarks", pt.additionaldetails->>'FirmBusinessName' as "businessname", pta.locality as "localitycode" from egcl_receiptheader_v1 as rh inner join egcl_receiptinstrument_v1 as ri on ri.receiptheader = rh.id inner join egcl_instrumentheader_v1 as ih on ih.id = ri.instrumentheader inner join eg_pt_property_v2 as pt on pt.propertyid = split_part(consumercode, ':', 1) inner join eg_pt_propertydetail_v2 as pd on pd.assessmentnumber = split_part(consumercode, ':', 2) inner join eg_pt_owner_v2 as po on po.propertydetail = pd.assessmentnumber inner join eg_user as eu on eu.uuid = po.userid inner join eg_pt_address_v2 as pta on pta.property = pt.propertyid left outer join eg_pt_unit_v2_ot as pot on pot.propertydetail = pd.assessmentnumber where rh.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails ='PT' - reportName: Sewa_Kendra_ReceiptRegister summary: Sewa Kendra Receipt Register version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: receiptnumber label: reports.pt.receiptNo type: string source: pt total: false - name: receiptdate label: reports.pt.receiptDate type: string source: pt total: false - name: g8issuedate label: reports.pt.g8issuedate type: string source: pt total: false - name: g8receiptno label: reports.pt.g8receiptno type: string source: pt total: false - name: amount label: reports.pt.amount type: string source: pt total: true - name: propertytax label: reports.pt.propertyTax type: string source: pt total: true - name: firecess label: reports.pt.fireCess type: string source: pt total: true - name: cancercess label: reports.pt.cancerCess type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.roundoff type: string source: pt total: true - name: pendingamount label: reports.pt.pendingamount type: string source: pt total: true - name: propertyid label: reports.pt.propertyId type: string source: pt total: false - name: usagetype label: reports.pt.usagetype type: string source: pt total: false - name: assessmentnumber label: reports.pt.assessmentNo type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: instrumenttype label: reports.pt.paymentMode type: string source: pt total: false - name: transactionnumber label: reports.pt.ddChequeTransactionNo type: string source: pt total: false - name: transactiondate label: reports.pt.ddChequeTransactionDate type: string source: pt total: false - name: bankname label: reports.pt.bankName type: string source: pt total: false - name: collectorname label: reports.pt.collectorname type: string source: pt total: false - name: remarks label: reports.pt.remarks type: string source: pt total: false searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate - name: paymentMode label: Payment Mode type: singlevaluelist pattern: 'list://Cash:Cash,Online:Online,Card:Card,DD:DD,Cheque:Cheque' source: pt isMandatory: false searchClause: AND instrumenttype = $paymentMode - name: financialyear label: Financial Year type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=egf-master&masterName=FinancialYear|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].code|$.MdmsRes.egf-master.FinancialYear.[?(@.module=='PT')].name source: pt wrapper: true isMandatory: false searchClause: AND financialyear = $financialyear - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pt_addr.locality IN ($localityArray) query: | -- Use CTE to force query plan, compute & use CTE later WITH receipt_breakup AS ( SELECT receiptheader, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails='PT' and rh.createdby::INTEGER in (select user_id from eg_userrole_v1 where role_code ='SEVA_KENDRA_EMP') GROUP BY rd.receiptheader ) SELECT rh.tenantid AS tenantid, rh.id, Substring(rh.consumercode, '(.*):') AS propertyid, Substring(rh.consumercode, ':(.*)') AS assessmentnumber, rh.consumercode, to_char((To_timestamp(rh.receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS receiptdate, bankid AS bankname, receiptnumber, amount, instrumenttype, transactionnumber, to_char((To_timestamp(transactiondate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS transactiondate, receipt_breakup.*, rh.totalamount - ih.amount AS pendingamount, pt_detail.financialyear, to_char((To_timestamp(rh.manualreceiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY') AS g8issuedate, Initcap(COALESCE(pt_detail.usagecategoryminor, pt_detail.usagecategorymajor)) as usagetype, manualreceiptnumber as g8receiptno, (CASE WHEN collectiontype='COUNTER' THEN eg_user.name ELSE '' END) as collectorname, pt_detail.additionaldetails->>'remarks' as "remarks" FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN receipt_breakup ON rh.id = receipt_breakup.receiptheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails='PT' and rh.createdby::INTEGER in (select user_id from eg_userrole_v1 where role_code ='SEVA_KENDRA_EMP') orderby: ORDER BY rh.receiptdate DESC - reportName: ReceiptRegisterSummary summary: Summary Receipt Register version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: ulb label: reports.pt.ulb type: string source: pt total: false - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: firecess label: reports.pt.firecess type: string source: pt total: true - name: cancercess label: reports.pt.cancercess type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: propertytax label: reports.pt.propertytax type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.adjustment type: string source: pt total: true - name: noofassessments label: reports.pt.noofassessments type: string source: pt total: true - name: sumcash label: reports.pt.sumcash type: string source: pt total: true - name: sumcheque label: reports.pt.sumcheque type: string source: pt total: true - name: countcheque label: reports.pt.countcheque type: string source: pt total: true - name: sumonline label: reports.pt.sumonline type: string source: pt total: true - name: collectorname label: reports.pt.collectorname type: string source: pt total: false - name: totalamount label: reports.pt.totalamount type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND rh.receiptdate <= $toDate - name: collectorname label: reports.pt.collectorname type: singlevaluelist pattern: http://egov-hrms:8080/egov-hrms/employees/_search?tenantId=$tenantid&roles=PTCEMP|$.Employees[*].user.id|$.Employees[*].user.name source: pt wrapper: true isMandatory: false searchClause: AND eg_user.id = $collectorname::INTEGER query: | -- Use CTE to force query plan, compute & use CTE later SELECT split_part(rh.tenantid,'.',2) as ulb, Count(distinct rd.receiptheader) noofassessments, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment, SUM ( case when instrumenttype='Cash' THEN adjustedamount ELSE 0 END) as sumcash, SUM ( case when instrumenttype='Cheque' THEN adjustedamount ELSE 0 END) as sumcheque, count (distinct case when instrumenttype='Cheque' then concat(rd.receiptheader,instrumenttype) end) as countcheque, SUM ( case when instrumenttype='Online' THEN adjustedamount ELSE 0 END) as sumonline, sum(adjustedamount) as totalamount, (CASE WHEN collectiontype='COUNTER' THEN eg_user.name ELSE 'Online' END) as collectorname FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and businessdetails='PT' groupby: group by collectorname,rh.tenantid - reportName: StateReceiptRegisterSummary summary: Receipt Register Summary version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "A1" sourceColumns: - name: ulb label: reports.pt.ulb type: string source: pt total: false - name: adhocpenalty label: reports.pt.adhocpenalty type: string source: pt total: true - name: adhocrebate label: reports.pt.adhocrebate type: string source: pt total: true - name: firecess label: reports.pt.firecess type: string source: pt total: true - name: cancercess label: reports.pt.cancercess type: string source: pt total: true - name: interest label: reports.pt.interest type: string source: pt total: true - name: penalty label: reports.pt.penalty type: string source: pt total: true - name: rebate label: reports.pt.rebate type: string source: pt total: true - name: propertytax label: reports.pt.propertytax type: string source: pt total: true - name: exemption label: reports.pt.exemption type: string source: pt total: true - name: adjustment label: reports.pt.adjustment type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate <= $toDate - name: ulb label: ULB type: singlevaluelist pattern: http://egov-mdms-service:8080/egov-mdms-service/v1/_get?tenantId=$tenantid&moduleName=tenant&masterName=tenants|$.MdmsRes.tenant.tenants.*.code|$.MdmsRes.tenant.tenants.*.name source: pt wrapper: true isMandatory: false searchClause: AND rd.tenantid = $ulb query: | -- Use CTE to force query plan, compute & use CTE later SELECT split_part(rd.tenantid,'.',2) as ulb, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_PENALTY' THEN adjustedamount ELSE 0 END) as adhocpenalty, SUM(CASE WHEN taxheadcode LIKE 'PT_ADHOC_REBATE' THEN adjustedamount ELSE 0 END) as adhocrebate, SUM(CASE WHEN taxheadcode LIKE 'PT_FIRE_CESS' THEN adjustedamount ELSE 0 END) as firecess, SUM(CASE WHEN taxheadcode LIKE 'PT_CANCER_CESS' THEN adjustedamount ELSE 0 END) as cancercess, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_INTEREST' THEN adjustedamount ELSE 0 END) as interest, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_PENALTY' THEN adjustedamount ELSE 0 END) as penalty, SUM(CASE WHEN taxheadcode LIKE 'PT_TIME_REBATE' THEN adjustedamount ELSE 0 END) as rebate, SUM(CASE WHEN taxheadcode LIKE 'PT_TAX' THEN adjustedamount ELSE 0 END) as propertytax, (SUM(CASE WHEN taxheadcode LIKE 'PT_UNIT_USAGE_EXEMPTION' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_OWNER_EXEMPTION' THEN adjustedamount ELSE 0 END) )as exemption, ( SUM(CASE WHEN taxheadcode LIKE 'PT_ROUNDOFF' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_DEBIT' THEN adjustedamount ELSE 0 END) + SUM(CASE WHEN taxheadcode LIKE 'PT_DECIMAL_CEILING_CREDIT' THEN adjustedamount ELSE 0 END)) as adjustment FROM egcl_receiptdetails_v1 as rd INNER JOIN egcl_receiptheader_v1 as rh on rh.id = rd.receiptheader WHERE rd.tenantid != 'pb.testing' and rh.status != 'Cancelled' and businessdetails='PT' groupby: group by rd.tenantid orderby: order by rd.tenantid - reportName: FYCollectionsReport summary: FY Collections version: 1.0.0 moduleName: rainmaker-pt additionalConfig: print: pdfPageSize: "Legal" sourceColumns: - name: financialyear label: reports.pt.financialyear type: string source: pt total: false - name: residential label: reports.pt.residential type: string source: pt total: true - name: countresidential label: reports.pt.countresidential type: string source: pt total: true - name: nonresidential label: reports.pt.nonresidential type: string source: pt total: true - name: countnonresidential label: reports.pt.countnonresidential type: string source: pt total: true - name: mixed label: reports.pt.mixed type: string source: pt total: true - name: countmixed label: reports.pt.countmixed type: string source: pt total: true - name: totalamount label: reports.pt.totalamount type: string source: pt total: true - name: totalcount label: reports.pt.totalcount type: string source: pt total: true - name: online label: reports.pt.online type: string source: pt total: true - name: cash label: reports.pt.cash type: string source: pt total: true - name: cheque label: reports.pt.cheque type: string source: pt total: true - name: dd label: reports.pt.dd type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate >= $fromDate - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: false searchClause: AND rh.receiptdate <= $toDate - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: false searchClause: AND pt_addr.locality IN ($localityArray) query: | -- Use CTE to force query plan, compute & use CTE later SELECT financialyear, sum(case when usagecategorymajor='RESIDENTIAL' then amount else 0 end) as "residential", sum(case when usagecategorymajor='RESIDENTIAL' then 1 else 0 end) as "countresidential", sum(case when usagecategorymajor='NONRESIDENTIAL' then amount else 0 end) as "nonresidential", sum(case when usagecategorymajor='NONRESIDENTIAL' then 1 else 0 end) as "countnonresidential", sum(case when usagecategorymajor='MIXED' then amount else 0 end) as "mixed", sum(case when usagecategorymajor='MIXED' then 1 else 0 end) as "countmixed", sum(amount) as totalamount, count(amount) as totalcount, sum(case when instrumenttype='Online' then amount else 0 end) as "online", sum(case when instrumenttype='Cash' then amount else 0 end) as "cash", sum(case when instrumenttype='Cheque' then amount else 0 end) as "cheque", sum(case when instrumenttype='DD' then amount else 0 end) as "dd" FROM egcl_receiptheader_v1 AS rh JOIN egcl_receiptinstrument_v1 AS ri ON rh.id = ri.receiptheader JOIN egcl_instrumentheader_v1 AS ih ON ih.id = ri.instrumentheader JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' groupby: GROUP BY financialyear orderby: ORDER BY financialyear - reportName: ULBDEFAULTERPTREPORT summary: ULB Defaulter Pt report version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: ulb label: reports.pt.ulb type: string source: pt total: false - name: propertytaxuniqueid label: reports.pt.propertytaxuniqueid type: string source: pt total: false - name: transcationdate label: reports.pt.transcationdate type: string source: pt total: false - name: receiptnumber label: reports.pt.receiptnumber type: string source: pt total: false - name: existingpropertyid label: reports.pt.existingpropertyid type: string source: pt total: false - name: financialyear label: reports.pt.financialYear type: string source: pt total: false - name: ownername label: reports.pt.ownername type: string source: pt total: false - name: guardianname label: reports.pt.guardianname type: string source: pt total: false - name: propertytype label: reports.pt.propertytype type: string source: pt total: false - name: mobileno label: reports.pt.mobileno type: string source: pt total: false - name: address label: reports.pt.address type: string source: pt total: false - name: payeraddress label: reports.pt.payeraddress type: string source: pt total: false searchParams: - name: localityArray label: reports.pt.zoneList type: boundarylist source: pt isMandatory: true searchClause: AND pta.locality IN ($localityArray) query: | --S.no Service Name ULB Property Tax Unique ID Date Of Transcation Receipt Number Existing Property ID Type Of Property Mobile Number Address with eg_pt_unit_v2_ot as ( select propertydetail, string_agg(distinct pu.occupancytype, ',') as occupancytype, string_agg(distinct coalesce(usagecategoryminor, usagecategorymajor), ',') as propertytype from eg_pt_unit_v2 as pu where pu.tenantid =$tenantid group by propertydetail ) select string_agg(distinct rh.receiptnumber,',') as "receiptnumber", string_agg(distinct initcap(split_part(rh.tenantid, '.', 2)),',') as "ulb", string_agg(distinct split_part(consumercode, ':', 1),',') as "propertytaxuniqueid", string_agg(to_char((To_timestamp(receiptdate/1000) at time Zone 'Asia/Kolkata'),'DD-MM-YYYY'),',') AS "transcationdate", string_agg(distinct pt.oldpropertyid,',') as "existingpropertyid", string_agg(pd.financialyear,',') as "financialyear", string_agg(distinct eu."name",',') as "ownername", string_agg(distinct eu.guardian,',') as "guardianname", string_agg(distinct pot.propertytype,',') as "propertytype", string_agg(distinct eu.mobilenumber,',') as "mobileno", string_agg(distinct REGEXP_REPLACE(REGEXP_REPLACE(CONCAT(pta.doorno, ',', pta.addressline1, ',', pta.addressline2, ',', pta.buildingname, ',', pta.pincode), ',+', ','), '^,|,$', ''),',') as "address" from egcl_receiptheader_v1 as rh inner join egcl_receiptinstrument_v1 as ri on ri.receiptheader = rh.id inner join egcl_instrumentheader_v1 as ih on ih.id = ri.instrumentheader inner join eg_pt_property_v2 as pt on pt.propertyid = split_part(consumercode, ':', 1) inner join eg_pt_propertydetail_v2 as pd on pd.assessmentnumber = split_part(consumercode, ':', 2) inner join eg_pt_owner_v2 as po on po.propertydetail = pd.assessmentnumber inner join eg_user as eu on eu.uuid = po.userid inner join eg_pt_address_v2 as pta on pta.property = pt.propertyid left outer join eg_pt_unit_v2_ot as pot on pot.propertydetail = pd.assessmentnumber where rh.tenantid =$tenantid and pd.property not in (select property from eg_pt_propertydetail_v2 where financialyear in ('2020-21')) groupby: group by split_part(consumercode, ':', 1) - reportName: ZoneFYReport summary: Zone FY Collection Pt report version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: zonecode label: reports.pt.zonename type: string source: pt total: false - name: 2013_14PTR label: reports.pt.2013_14_PTR type: string source: pt total: true - name: 2013_14Collection label: reports.pt.2013_14Collection type: string source: pt total: true - name: 2014_15PTR label: reports.pt.2014_15_PTR type: string source: pt total: true - name: 2014_15Collection label: reports.pt.2014_15Collection type: string source: pt total: true - name: 2015_16PTR label: reports.pt.2015_16_PTR type: string source: pt total: true - name: 2015_16Collection label: reports.pt.2015_16Collection type: string source: pt total: true - name: 2016_17PTR label: reports.pt.2016_17_PTR type: string source: pt total: true - name: 2016_17Collection label: reports.pt.2016_17Collection type: string source: pt total: true - name: 2017_18PTR label: reports.pt.2017_18_PTR type: string source: pt total: true - name: 2017_18Collection label: reports.pt.2017_18Collection type: string source: pt total: true - name: 2018_19PTR label: reports.pt.2018_19_PTR type: string source: pt total: true - name: 2018_19Collection label: reports.pt.2018_19Collection type: string source: pt total: true - name: 2019_20PTR label: reports.pt.2019_20_PTR type: string source: pt total: true - name: 2019_20Collection label: reports.pt.2019_20Collection type: string source: pt total: true - name: 2020_21PTR label: reports.pt.2020_21_PTR type: string source: pt total: true - name: 2020_21Collection label: reports.pt.2020_21Collection type: string source: pt total: true - name: totalptr label: reports.pt.totalptr type: string source: pt total: true - name: totalcollection label: reports.pt.totalcollection type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND 1=1 - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND 1=1 query: | --S.no Service Name ULB Property Tax Unique ID Date Of Transcation Receipt Number Existing Property ID Type Of Property Mobile Number Address with fy2013 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2013-14' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2014 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2014-15' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2015 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2015-16' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2016 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2016-17' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2017 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2017-18' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2018 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2018-19' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2019 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2019-20' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), fy2020 as ( SELECT zonecode, string_agg(distinct zonename,',') as zonename, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2020-21' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY zonecode ), zones as ( select distinct zonecode,string_agg(distinct zonename,',') as zname from eg_bndry_mohalla where tenantid=$tenantid group by zonecode ) SELECT zones.zonecode as zonecode, zones.zname as zonename, coalesce(fy2013.totalptr,0) as "2013_14PTR", coalesce(fy2013.amountcollected,0) as "2013_14Collection", coalesce(fy2014.totalptr,0) as "2014_15PTR", coalesce(fy2014.amountcollected,0) as "2014_15Collection", coalesce(fy2015.totalptr,0) as "2015_16PTR", coalesce(fy2015.amountcollected,0) as "2015_16Collection", coalesce(fy2016.totalptr,0) as "2016_17PTR", coalesce(fy2016.amountcollected,0) as "2016_17Collection", coalesce(fy2017.totalptr,0) as "2017_18PTR", coalesce(fy2017.amountcollected,0) as "2017_18Collection", coalesce(fy2018.totalptr,0) as "2018_19PTR", coalesce(fy2018.amountcollected,0) as "2018_19Collection", coalesce(fy2019.totalptr,0) as "2019_20PTR", coalesce(fy2019.amountcollected,0) as "2019_20Collection", coalesce(fy2020.totalptr,0) as "2020_21PTR", coalesce(fy2020.amountcollected,0) as "2020_21Collection", coalesce(fy2013.totalptr,0)+coalesce(fy2014.totalptr,0)+coalesce(fy2015.totalptr,0)+coalesce(fy2016.totalptr,0)+coalesce(fy2017.totalptr,0)+coalesce(fy2018.totalptr,0)+coalesce(fy2019.totalptr,0)+coalesce(fy2020.totalptr,0) as "totalptr", coalesce(fy2013.amountcollected,0)+coalesce(fy2014.amountcollected,0)+coalesce(fy2015.amountcollected,0)+coalesce(fy2016.amountcollected,0)+coalesce(fy2017.amountcollected,0)+coalesce(fy2018.amountcollected,0)+coalesce(fy2019.amountcollected,0)+coalesce(fy2020.amountcollected,0) as "totalcollection" from zones left outer join fy2013 on fy2013.zonecode=zones.zonecode left outer join fy2014 on fy2014.zonecode=zones.zonecode left outer join fy2015 on fy2015.zonecode=zones.zonecode left outer join fy2016 on fy2016.zonecode=zones.zonecode left outer join fy2017 on fy2017.zonecode=zones.zonecode left outer join fy2018 on fy2018.zonecode=zones.zonecode left outer join fy2019 on fy2019.zonecode=zones.zonecode left outer join fy2020 on fy2020.zonecode=zones.zonecode where 1=1 orderby: order by zonename - reportName: BlockFYReport summary: Block FY Collection PT report version: 1.0.0 moduleName: rainmaker-pt sourceColumns: - name: blockname label: reports.pt.blockname type: string source: pt total: false - name: 2013_14PTR label: reports.pt.2013_14_PTR type: string source: pt total: true - name: 2013_14Collection label: reports.pt.2013_14Collection type: string source: pt total: true - name: 2014_15PTR label: reports.pt.2014_15_PTR type: string source: pt total: true - name: 2014_15Collection label: reports.pt.2014_15Collection type: string source: pt total: true - name: 2015_16PTR label: reports.pt.2015_16_PTR type: string source: pt total: true - name: 2015_16Collection label: reports.pt.2015_16Collection type: string source: pt total: true - name: 2016_17PTR label: reports.pt.2016_17_PTR type: string source: pt total: true - name: 2016_17Collection label: reports.pt.2016_17Collection type: string source: pt total: true - name: 2017_18PTR label: reports.pt.2017_18_PTR type: string source: pt total: true - name: 2017_18Collection label: reports.pt.2017_18Collection type: string source: pt total: true - name: 2018_19PTR label: reports.pt.2018_19_PTR type: string source: pt total: true - name: 2018_19Collection label: reports.pt.2018_19Collection type: string source: pt total: true - name: 2019_20PTR label: reports.pt.2019_20_PTR type: string source: pt total: true - name: 2019_20Collection label: reports.pt.2019_20Collection type: string source: pt total: true - name: 2020_21PTR label: reports.pt.2020_21_PTR type: string source: pt total: true - name: 2020_21Collection label: reports.pt.2020_21Collection type: string source: pt total: true - name: totalptr label: reports.pt.totalptr type: string source: pt total: true - name: totalcollection label: reports.pt.totalcollection type: string source: pt total: true searchParams: - name: fromDate label: reports.pt.fromDate type: epoch source: pt isMandatory: true searchClause: AND 1=1 - name: toDate label: reports.pt.toDate type: epoch source: pt isMandatory: true searchClause: AND 1=1 query: | --S.no Service Name ULB Property Tax Unique ID Date Of Transcation Receipt Number Existing Property ID Type Of Property Mobile Number Address with fy2013 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2013-14' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2014 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2014-15' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2015 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2015-16' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2016 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2016-17' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2017 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2017-18' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2018 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2018-19' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2019 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2019-20' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), fy2020 as ( SELECT blockcode, string_agg(distinct blockname,',') as blockname, string_agg(distinct financialYear,',') as financialyear, count(*) as totalptr, sum(amount) as amountcollected FROM egcl_receiptheader_v1 AS rh JOIN eg_pt_propertydetail_v2 pt_detail ON (pt_detail.property || ':' || pt_detail.assessmentnumber) = rh.consumercode JOIN eg_pt_address_v2 pt_addr ON pt_detail.property = pt_addr.property JOIN eg_bndry_mohalla on eg_bndry_mohalla.localitycode=pt_addr.locality INNER JOIN egcl_receiptinstrument_v1 ri on ri.receiptheader=rh.id INNER JOIN egcl_instrumentheader_v1 ih on ih.id=ri.instrumentheader LEFT OUTER JOIN eg_user ON eg_user.id = rh.createdby::INTEGER WHERE rh.tenantid = $tenantid and rh.status != 'Cancelled' and financialyear='2020-21' and rh.receiptdate>=$fromDate and rh.receiptdate<=$toDate GROUP BY blockcode ), blocks as ( select distinct blockcode,string_agg(distinct blockname,',') as blockname from eg_bndry_mohalla where tenantid=$tenantid group by blockcode ) SELECT blocks.blockcode as blockcode, blocks.blockname as blockname, coalesce(fy2013.totalptr,0) as "2013_14PTR", coalesce(fy2013.amountcollected,0) as "2013_14Collection", coalesce(fy2014.totalptr,0) as "2014_15PTR", coalesce(fy2014.amountcollected,0) as "2014_15Collection", coalesce(fy2015.totalptr,0) as "2015_16PTR", coalesce(fy2015.amountcollected,0) as "2015_16Collection", coalesce(fy2016.totalptr,0) as "2016_17PTR", coalesce(fy2016.amountcollected,0) as "2016_17Collection", coalesce(fy2017.totalptr,0) as "2017_18PTR", coalesce(fy2017.amountcollected,0) as "2017_18Collection", coalesce(fy2018.totalptr,0) as "2018_19PTR", coalesce(fy2018.amountcollected,0) as "2018_19Collection", coalesce(fy2019.totalptr,0) as "2019_20PTR", coalesce(fy2019.amountcollected,0) as "2019_20Collection", coalesce(fy2020.totalptr,0) as "2020_21PTR", coalesce(fy2020.amountcollected,0) as "2020_21Collection", coalesce(fy2013.totalptr,0)+coalesce(fy2014.totalptr,0)+coalesce(fy2015.totalptr,0)+coalesce(fy2016.totalptr,0)+coalesce(fy2017.totalptr,0)+coalesce(fy2018.totalptr,0)+coalesce(fy2019.totalptr,0)+coalesce(fy2020.totalptr,0) as "totalptr", coalesce(fy2013.amountcollected,0)+coalesce(fy2014.amountcollected,0)+coalesce(fy2015.amountcollected,0)+coalesce(fy2016.amountcollected,0)+coalesce(fy2017.amountcollected,0)+coalesce(fy2018.amountcollected,0)+coalesce(fy2019.amountcollected,0)+coalesce(fy2020.amountcollected,0) as "totalcollection" from blocks left outer join fy2013 on fy2013.blockcode=blocks.blockcode left outer join fy2014 on fy2014.blockcode=blocks.blockcode left outer join fy2015 on fy2015.blockcode=blocks.blockcode left outer join fy2016 on fy2016.blockcode=blocks.blockcode left outer join fy2017 on fy2017.blockcode=blocks.blockcode left outer join fy2018 on fy2018.blockcode=blocks.blockcode left outer join fy2019 on fy2019.blockcode=blocks.blockcode left outer join fy2020 on fy2020.blockcode=blocks.blockcode where 1=1 orderby: order by blockname