change customer status based on last 3 bills.

Got something cool to share with AppGini users? Feel free to post it here!
Post Reply
zkarwinkar
Veteran Member
Posts: 32
Joined: 2021-06-12 21:01

change customer status based on last 3 bills.

Post by zkarwinkar » 2022-10-12 11:09

if continue last 3 bills unpaid customer status change to Freez

for example :
Attachments
example-AG.PNG
example-AG.PNG (6.56 KiB) Viewed 1138 times

angus
Veteran Member
Posts: 128
Joined: 2020-05-28 22:27

Re: change customer status based on last 3 bills.

Post by angus » 2022-10-15 19:23

Probably need to do this with a cron job I think. Maybe a stored procedure that checks the status of the bills and updates the status, this way you can schedule it to run daily. I think if you add something in the before/after update hook it might not be triggered if no one amends the records
AppGini 22.13

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: change customer status based on last 3 bills.

Post by pbottcher » 2022-10-16 08:09

Hi,

you could make it generic and add to the hooks/TABLENAME.php in the init function (this is not very performant, but should work just fine of non heavy used systems).

Code: Select all

sqlvalue("update customers left join (select customer_id, if(locate("unpaid,unpaid,unpaid",SUBSTRING_INDEX(group_concat(payment order by bill_id desc,',',1),',',3)),'freeze','active') as newstatus from Test group by Test.customer_id) m on m.id=customers.id set status=m.newstatus ");
based on this statement you could also use the after_insert, after_update and after_delete hooks and add the query for only the customer that is currently affected by the change.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

zkarwinkar
Veteran Member
Posts: 32
Joined: 2021-06-12 21:01

Re: change customer status based on last 3 bills.

Post by zkarwinkar » 2022-10-22 20:11

Thanks ,
but i did it with another solution as follows

1) i added one new auto calculated field in bill . and set condition as count

SELECT CASE
WHEN `bills`.`status` = 'unpaid' THEN '1'
WHEN `bills`.`status` = 'paid' THEN '-1'
ELSE '---'
END
FROM
`bills`
WHERE
`bills`.`id` = %ID%

2) and then in customer accounts table i added one extra field as calculated field

SELECT sum(count)
FROM (select bills.count from bills where c_id = %ID% order by bills.id desc limit 3) t1

3) made account status as calculated field and set condition as

SELECT CASE

WHEN customer.bill_count = 3 THEN 'freeze'
ELSE ''
END
FROM
customer
WHERE
`customer`.`id` = %ID%

Post Reply