Hi @patrick
When I run the query below, I receive an error message .I think I can solve the problem on the PHP side. I would appreciate it if you could help me with this issue.
Wappler 6.4.1
PHP
Mac
Error
{
"code": 0,
"file": "/var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/modules/dbupdater.php",
"line": 408,
"message": "Undefined offset: 0",
"trace": "#0 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/modules/dbupdater.php(408): exception_error_handler() #1 [internal function]: modules\dbupdater->modules\{closure}() #2 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/modules/dbupdater.php(400): preg_replace_callback() #3 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/lib/App.php(206): modules\dbupdater->custom() #4 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/lib/App.php(146): lib\App->execSteps() #5 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/lib/App.php(135): lib\App->exec() #6 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnectLib/lib/App.php(113): lib\App->exec() #7 /var/www/vhosts/turkmobil.com.tr/meski-tablet.turkmobil.com.tr/dmxConnect/api/v1/AccrualReports/incomeAccuralReports.php(8): lib\App->define() #8 {main}"
}
SQL Query
select COALESCE(to_number(is_number(ip.code)),99999999) income_code, COALESCE(ip.account_code,'99999999')
account_code,count(distinct(a.installation_id)) subscriber_count,
sum(case when ip.id IS NULL OR (ip.is_calc_consumption=1 AND COALESCE(ai.is_discount,0)=0) then consumption_1 else 0 end ) consumption_1,
sum(case when ip.id IS NULL OR (ip.is_calc_consumption=1 AND COALESCE(ai.is_discount,0)=0) then consumption_2 else 0 end ) consumption_2,
sum(case when ip.id IS NULL OR (ip.is_calc_consumption=1 AND COALESCE(ai.is_discount,0)=0) then consumption_3 else 0 end ) consumption_3,
sum(case when ip.id IS NULL OR (ip.is_calc_consumption=1 AND COALESCE(ai.is_discount,0)=0) then consumption_4 else 0 end ) consumption_4,
sum(case when ip.id IS NULL OR (ip.is_calc_consumption=1 AND COALESCE(ai.is_discount,0)=0) then consumption else 0 end ) consumption,
SUM(AI.AMOUNT1*ai.status) AMOUNT1,
SUM(AI.AMOUNT2*ai.status) AMOUNT2,
SUM(AI.AMOUNT3*ai.status) AMOUNT3,
SUM(AI.AMOUNT4*ai.status) AMOUNT4,
SUM(AI.AMOUNT*ai.status) AMOUNT,
coalesce(ai.is_discount,0) is_discount
from cs_account a
join cs_account_action aa on (a.id=aa.account_id and aa.action_date BETWEEN TO_DATE('01/02/2023', 'DD/MM/YYYY') AND TO_DATE('02/02/2023' ||' 23:59:59', 'DD/MM/YYYY HH24:MI:SS'))
left join cs_account_income ai on (ai.account_action_id=aa.id)
left join cs_income_prm ip on (ai.income_id=ip.id)
left join cs_income_prm_lng ipl on (ai.income_id=ipl.prm_id and ipl.lang_id=11)
join cs_action_type_prm atp on (aa.action_type_id=atp.id and atp.type in (1,5))
left join gis_area_prm ap on (a.area_id=ap.id)
left join cs_register r on (a.register_id=r.id)
left join cs_installation i on (a.installation_id=i.id)
left join cs_subscriber_type_prm stp on (a.subscriber_type_id=stp.id)
left join cs_subscriber_type_prm_lng stpl on (a.subscriber_type_id=stpl.prm_id and stpl.lang_id=12)
left join cs_tariff_type_prm ttp on (a.tariff_type_id=ttp.id)
left join cs_tariff_type_prm_lng ttpl on (a.tariff_type_id=ttpl.prm_id and ttpl.lang_id=13)
LEFT JOIN CS_SUBSCRIBER S ON (I.SUBSCRIBER_ID=S.ID)
LEFT JOIN GIS_BUILDING_FLAT BF ON (S.BUILDING_FLAT_ID=BF.ID)
LEFT JOIN GIS_BUILDING_DOOR BD ON (BF.BUILDING_DOOR_ID=BD.ID)
LEFT JOIN GIS_QUARTER_STREET_PRM QSP ON (BD.QUARTER_STREET_ID=QSP.ID)
LEFT JOIN GIS_STREET_PRM SP ON (QSP.STREET_ID=SP.ID)
LEFT JOIN GIS_QUARTER_PRM QP ON (QSP.QUARTER_ID=QP.ID)
LEFT JOIN GIS_DISTRICT_PRM DP ON (QP.DISTRICT_ID=DP.ID)
LEFT JOIN GIS_SERVICE_BOX SB ON (SB.ID=I.SERVICE_BOX_ID)
where 1=1
and dp.id in (2086) and a.accrue_type_id in ( 1) and aa.action_type_id in ( 1)
GROUP BY IP.CODE, IP.ACCOUNT_CODE, IPL.VALUE, coalesce(ai.is_discount,0)