B.lakshman B.lakshman - 6 months ago 15
PHP Question

how to generate dynamically rent posting in mysql query by using checkin date? Plan to run in cron daily by date basis

SELECT pc.id,pc.chin_roomnumber,pc.chin_guestname,us.name,pc.checkin_time,
-> rd.rd_propertyname,(DATE_FORMAT(CURDAte() + INTERVAL 1 MONTH, '%Y-%m-%d')) as Billedmonth
-> FROM pgownersprofile_checkin as pc
-> join pgownersprofile_roomdetails as rd on pc.chin_roomid = rd.id
-> left join users us on us.uid=pc.chin_guestname
-> WHERE DATE_FORMAT(Checkin_time, '%d') = DATE_FORMAT(CURDAte(), '%d')
-> and Date_Format(Checkin_time, '%m') = Date_Format(CurDate(), '%m');


output:

id|chin_room|chin_guestname|name|**checkin_time**|rd_propertyname| **Billed** |
**values**
| 59 | 56B | 75 | fizel | **2016-03-21 00:00:00** | Lakshmi Hotel | **2016-04-21** |


I generated output for next month but i was confused one thing if i were checkin on 31-jan-2016 how we can generate for Feb month bill(only 28 days). some month date with 30 and some with 31,? how we can generate bill for next month at same or before date

Answer
$automaticrentpost = db_query("set @dateciolumn=:date", array(':date'=> '2016-05-01'));

$automaticrentpost =db_query("SELECT @dateciolumn as Billed_Date,case when PERIOD_DIFF(DATE_FORMAT(@dateciolumn,'%y%m'),DATE_FORMAT(Checkin_time,'%y%m')) < 2 then DATE_FORMAT(checkin_time,'%Y-%m-%d') else DATE_FORMAT(date_add(@dateciolumn,INTERVAL -1 month),'%Y-%m-01') end as Billed_From,case  when PERIOD_DIFF(DATE_FORMAT(@dateciolumn,'%y%m'),DATE_FORMAT(Checkin_time,'%y%m')) <2 then rd.rd_tariff - ((day(checkin_time)-1)*rd.rd_tariff/30) else rd.rd_tariff end as Bill_Amount,pc.checkin_time,rd.rd_occupancy,rd.rd_tariff,pc.chin_guestname,us.name,pc.id,rd.rd_propertyname,pc.chin_roomnumber
FROM pgownersprofile_checkin as pc join pgownersprofile_roomdetails as rd on pc.chin_roomid = rd.id left join users us on us.uid=pc.chin_guestname WHERE chin_status=1 and rd_propertyid=:pid and @dateciolumn>checkin_time", array(':pid'=>$pid));

I created by hard code of the current date where the Cron to be run. So I manually run my page to generate the output of month rent as per check-in date by Guest