TUTORIAL PIAF Hospitality Management System

[wardmundy]

Wish List

Let me preface this by saying it's been about 2 decades since I've done any programming work with hotels. And I haven't had a chance to actually try out Claudio's masterpiece yet. Having said all of that, here are a couple of features that my customers always requested.

Maid List. The way this worked was, when someone checked out of their room or the checkout time arrived on the room (whichever occurred first), the system would set a RoomDirty flag to indicate that the room needed to be cleaned before it was rented again. Based upon this flag, a Maid List (aka Rooms to be Cleaned Report) could be generated for the hotel. When the cleaning staff arrived in the room, they would dial in a code to indicate that the room was vacated and was being cleaned. This set a RoomBeingCleaned flag. When the room had been cleaned and checked, a staff member could dial a code from the room phone to reset the RoomDirty and RoomBeingCleaned flags to false. This, in turn, meant that an Available Rooms Report could be run showing all of the rooms that were ready for occupancy.

Automatic Check Out. Some hotels give their customers the option of auto-checkout at the front desk. The hotel then generates a bill and charge against a credit card on the last night of occupancy. The customer then is advised that they can checkout of the hotel by leaving the key in the room and dialing a code on the room phone to signify physical checkout. This, in turn, would also set the RoomDirty flag. The cleaning people then would key in a different code to indicate that the room had, in fact, been vacated and was now being cleaned, the RoomBeingCleaned flag as outlined above. Once the room was cleaned and checked, another code was entered to signify that the room was ready for occupancy.

 

[TheShniz]

Alrighty now...

The reason why Bills are not showing any call log except direct system access is because of the SELECT statement used:

SELECT calldate, dst, billsec FROM `cdr` WHERE calldate BETWEEN '" . $Checkin . "' AND '" . $Checkout ."' AND src = " . $Ext)

...specifically the column you're matching by, that is the 'src' data. Now I don't have enough experience with Asterisk CDR to know what all the data represents, but the select statement is spitting back derivatives of FMGL and FMPR based on the extension placing the call; in the dst field you're basing your collection from.

Instead, if you use this SELECT statement...

SELECT * FROM `cdr` WHERE channel LIKE '%123%'

...where 123 is the extension number, you will get all of the call activity for that extension; including the dst entries you're really looking for.

I'm very hungry (it's a late lunch), but I figured this would help out... the select statement will need to be modified to only return outbound calls (don't charge for inbound), but that shouldn't be difficult. Otherwise, I'll do it myself later today after a couple errands.

 

[claudiopizzillo]

Are you sure?

Here an example of my log from asteriskcdrdb->cdr

calldate clid src dst dcontext channel dstchannel lastapp lastdata duration billsec disposition amaflags accountcode uniqueid userfield
2009-05-15 17:03:05 "Claudio" <2> 2 0921422XXX from-internal SIP/2-097e87c8 IAX2/VOIP4BIZ-11430 ResetCDR vw 67 60 ANSWERED 3 1242399785.16
At 17:03 i called the 0921422XXX from my extension (n. 2) via a iax2 provider.

Every outbounds call follow this schema:

src = source (my ext)
dst = called number

What's wrong?

 

[TheShniz]

Pretty sure, here's a complete call record...

Extension 123 called 18007425877 (UPS) using Teliax
(seperated each field onto new line for easier reading)

2009-07-20 15:20:17
"My Company Name" <5551234567>
5551234567
18007425877
from-internal
SIP/123-094023c8
SIP/Teliax-093cd2c8
Dial
SIP/Teliax/18007425877|300|
92
60
ANSWERED
3
employees

...I'll spend more time hopefully before I leave here today with a more thorough explanation.

::updated::

Here's the key...

calldate
clid
src
dst
dcontext
channel
dstchannel
lastapp
lastdata
duration
billsec
disposition
amaflags
accountcode

...I should mention that 1.) Extension 123's Outbound CID is BLANK, and 2.) Teliax's Trunk Outbound CID is "My Company Name" <5551234567>

 

[claudiopizzillo]

My friend

You save me !!!

I think this is the right sql select:
$query = "SELECT calldate, dst, billsec FROM `cdr` WHERE dstchannel IS NOT NULL AND lastapp = 'Dial' AND calldate BETWEEN '" . $Checkin . "' AND '" . $Checkout ."' AND channel LIKE '%" . $Ext ."%'";

Re-download

 

[TheShniz]

Alrighty, making some excellent progress on the Billing now... I've got calls listed now!

1.)

I was thinking last night, that there may be some scenario where the select statement requests the bill for room 121... but what if there's a room 121 and 1121 or 3121. The LIKE statement will match all of the rooms with 121 in the number and compile a single bill for all there. Being that the call is being recorded as SIP/121-whatever... to avoid this issue, I believe the solution is to match "/%121%-" instead of simply %121%.

2.)

Also unexpected, I'm getting multiple FMGL line items in the report... specifically for inbound calls to "the hotel's main number" from the local extension. The reason being that the same LIKE statement is matching "Local/FMGL-121@from-internal-2950,2 (as an example, direct from CDR). The above solution (#1) will also correct this issue.

3.)

Is there supposed to be a Total of some kind for the report? The report is assigning a cost to each call, but not tallying the total cost to the room. It'd also be nice if there were a direct link to the room's bill upon checkout.

4.)

It should be noted that the Rates Prefix is based on the number as dialed by the user, not by PiaF Trunks. Therefore two rates must be defined (atleast here in the USA) for 1555 AND 555... such that 15551234567 AND 5551234567 are both billed.

X.)

The download link to hotel.tar.gz is still pointing to hotel.tgz

You Rock, very well written lil prog here!
unkb:

 

[claudiopizzillo]

New release. Re-download

Bug-fix:

1. change the asteriskcdrdb query: $query = "SELECT calldate, dst, billsec FROM `cdr` WHERE dstchannel IS NOT NULL AND (lastapp = 'Dial' OR lastapp = 'ResetCDR') AND dcontext = 'from-internal' AND calldate BETWEEN '" . $Checkin . "' AND '" . $Checkout ."' AND channel LIKE '%/" . $Ext ."-%'";
2. Excluded every call to internal extension from billing (if i have a rate prefix "3" and i call the ext N. 3 this was billed, WRONG).
3. Added Total amount for users on Bills section

P.S.
Remember on check-out the total of the calls is write in the users table with the actual rates.
If you change the rates, the calls report for the users will have wrongs value, but the total is still unchanged (the report are calculated on the fly).

 

[ghurty]

This script looks great, it is just that I am having a problem....


When a "guest" is checked in. and they try to direct dial an extension, it does not connect. (I am dealing with 3 digit extension.. 101,102, etc..)

Looking at the the log, it apears to be trying to use the outbound trunk, rather then using it internally.

It is something with the added code to the dialplan, becuase when I comment it out, internal calls do work.

THanks

 

[claudiopizzillo]

change in from-internal-custom


exten => 1XX,1,Dial(SIP/${DIAL})

exten => _X.,1,Set(result=${CURL(http://MYPBX/hotel/checkuser.php?Ext=${CALLERID(num)})})
exten => _X.,2,NoOp(Results: ${result})
exten => _X.,3,GotoIf($["${result}" = "OK"]?OK)
exten => _X.,4,GotoIf($["${result}" = "KO"]?KO)
exten => _X.,5(OK),Goto(outbound-allroutes,${DIAL},1)
exten => _X.,6,Hangup()
exten => _X.,7(KO),DIAL(SIP/RECEPTION)
exten => _X.,8,Hangup()

 

[unsichtbarre]

This is awesome stuff; simple and effective!

I have two questions:

1. Is there a way to password-protect the PIAF/hotels directory? It would be handy if guests couldn't accidentally stumble upon this page and change the rates themselves/
2. Much like in the previous message (I am also using 3 digit extension), I have included in extensions_custom the lines to prevent extensions from dialing-out unless the room is checked-in, however it seems to have no effect. Extension dials all numbers weather or not checked-in.

Thanks for the great tool!
-j

 

[claudiopizzillo]

Right, don't work on piaf 1.4 asterisk 1.4.26, but damn, works on piaf 1.4 asterisk 1.6.1 (my dev platform).

Need help from dialplan guru!

on asterisk 1.4 i resolved with this code for every ext loaded in H.M.S.:

exten => 101,Dial(SIP/101)
exten => 102,Dial(SIP/102)
.......

 

[alex_tel]

Wish list

Awesome piece of software.

Would be nice to have feature codes to perform the check-in/out actions, etc.

 

[unsichtbarre]

I think it would be awesome if there was a "one button" check-out. You would click on check-out and it would display the charges without having to go to the bill separately.

Nevertheless, awesome stuff!

-J

 

[newconnection]

Thank you for your great Application!
Outgoing calls are working perfectly, but internal calls are declined!

Any idea, how to solve this problem?

 

[ghurty]

Thank you for your great Application!
Outgoing calls are working perfectly, but internal calls are declined!

Any idea, how to solve this problem?

This is the answer I got earlier:

change in from-internal-custom


exten => 1XX,1,Dial(SIP/${DIAL})

exten => _X.,1,Set(result=${CURL(http://MYPBX/hotel/checkuser.php?Ext=${CALLERID(num)})})
exten => _X.,2,NoOp(Results: ${result})
exten => _X.,3,GotoIf($["${result}" = "OK"]?OK)
exten => _X.,4,GotoIf($["${result}" = "KO"]?KO)
exten => _X.,5(OK),Goto(outbound-allroutes,${DIAL},1)
exten => _X.,6,Hangup()
exten => _X.,7(KO),DIAL(SIP/RECEPTION)
exten => _X.,8,Hangup()

 

[unsichtbarre]

This is the answer I got earlier:

I have tried both 3 and 4 digit extensions in all combination's with the original and modified versions of extensions_custom.conf and can not dial internally with either. Any suggestions?
-J

 

[unsichtbarre]

Solved, at least for me!

OK, I had to do some learning about Asterisk dialplan options, but what I discovered is that the digit appearing between two commas is NOT some sort of line number but rather a priority. In the example:

exten => 1XX,1,Dial(SIP/${DIAL})

exten => _X.,1,Set(result=${CURL(http://MYPBX/hotel/checkuser.php?Ext=${CALLERID(num)})}) 
exten => _X.,2,NoOp(Results: ${result})
exten => _X.,3,GotoIf($["${result}" = "OK"]?OK)
exten => _X.,4,GotoIf($["${result}" = "KO"]?KO)
exten => _X.,5(OK),Goto(outbound-allroutes,${DIAL},1)
exten => _X.,6,Hangup()
exten => _X.,7(KO),DIAL(SIP/RECEPTION)
exten => _X.,8,Hangup()

there are two lines with priority 1!

My fix was:

exten => 1XX,1,Dial(SIP/${DIAL})

exten => _X.,2,Set(result=${CURL(http://192.168.XX.YY/hotel/checkuser.php?Ext=${CALLERID(num)})})
exten => _X.,3,NoOp(Results: ${result})
exten => _X.,4,GotoIf($["${result}" = "OK"]?OK)
exten => _X.,5,GotoIf($["${result}" = "KO"]?KO)
exten => _X.,6(OK),Goto(outbound-allroutes,${DIAL},1)
exten => _X.,7,Hangup()
exten => _X.,8(KO),DIAL(SIP/0)
exten => _X.,9,Hangup()

Hope this helps-
-J

 

[gwinton]

I have tried everything and still can't get billing to display any numbers.

I also had to change the dialplan so I could get wake up calls to work

;## Hotel Interface (redirects all outbound calls to the operator)
exten => _NXXNXXXXXX,1,Set(result=${CURL(http://192.168.1.14/hotel/checkuser.php?Ext=${CALLERID(num)})})
exten => _NXXNXXXXXX,2,NoOp(Results: ${result})
exten => _NXXNXXXXXX,3,GotoIf($["${result}" = "OK"]?OK)
exten => _NXXNXXXXXX,4,GotoIf($["${result}" = "KO"]?KO)
exten => _NXXNXXXXXX,5(OK),Goto(outbound-allroutes,${DIAL},1)
exten => _NXXNXXXXXX,6,Hangup()
exten => _NXXNXXXXXX,7(KO),DIAL(SIP/703)
exten => _NXXNXXXXXX,8,Hangup()

Where else do I need to look for billing?

 

[cityguru]

How to get Rates to work?

I am bit confused on how to input the rates. I have to create a rate for Domestic just to see if it works and when i check out the PIAF system doesnt show anything.

Could someone give me an example of what I should put into rates section for any call outside the 301 Area code?

Anything out of 301 is considered long distance.

For example what should go into
TYPE
PREFIX
MINUTE RATE
FIXED RATE

i am a little confused as to what the difference is to minute or fixed rate and when to use them.

By the way, i tried the following as a test

Description: test
Type: voicemail
Prefix: *97
Minute Rate: .25
Fixed Rate: .10

Then i checke in my room and from that rooms extension i placed some calls to *97 and checked voicemail.
Then i checked myself out after the calls, looked at the bills and saw nothing in the bills, so what am i missing here?


Anyway if there are any examples anyone can share that would be great.

 

[gwinton]

Anyone have any ideas why the billing is not working?