if continue last 3 bills unpaid customer status change to Freez
for example :
change customer status based on last 3 bills.
-
- Veteran Member
- Posts: 32
- Joined: 2021-06-12 21:01
change customer status based on last 3 bills.
- Attachments
-
- example-AG.PNG (6.56 KiB) Viewed 1138 times
Re: change customer status based on last 3 bills.
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
Re: change customer status based on last 3 bills.
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).
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.
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 ");
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.
-
- Veteran Member
- Posts: 32
- Joined: 2021-06-12 21:01
Re: change customer status based on last 3 bills.
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%
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%