Day of week check / SQL

General Help
1

Hello guys,
I need help figuring this out…
What would be the best to check if a caller has called this week already?

I created a database, which has the fields “CallerID”,“TicketNumber”,CalledThisWeek"

The caller calls and get’s an IVR, which requests his ticket number. When he enters it, FreePBX enters it into a database. I’m OK till this step.
Here’s where it get’s messy…
I need to be able to check if the customer has called this week already, so I need the database to check in the “CalledThisWeek” field if it has sometype of digit there.
Is there a way to accomplish this? Or can anyone suggest anything similar to this?

Thank you!

2

You didn’t say anything about your database, but most relational dtabases support a

WHERE dateCreated BETWEEN “&DateAdd(“d”,-6,Date())&” AND “&Date()&”

or something similar, you would need to add a very important field “dateCreated” to your records for anything to be useful, doesn’t that make sense?

3

Yeah I would ditch what I assume is a boolean field of called thisweek for a timestamp then figure out if the timestamp is in range

4

I’m sorry guys, I use MYSQL for the database.
Here is the dial plan.

    [from-internal-custom]
exten => 1234,1,Answer
exten => 1234,2,Flite("Hello",any)
exten => 1234,3,Goto(ReadPhoneNumber,s,1) 


[ReadPhoneNumber]
exten => s,1,Set(TIMEOUT(digit)=7)
exten => s,2,Set(TIMEOUT(response)=10)
exten => s,3,Flite("I have detected you are calling from phone number",any)
exten => s,4,SayDigits(${CALLERID(number)})
exten => s,5,Flite("If the number is correct please press 1, otherwise please hang up and call back",any)
exten => s,6,WaitExten()
exten => 1,1,Goto(CheckIfCalledAlready,s,1) 
exten => t,1,Goto(s,1)
exten => i,1,Goto(s,1)
exten => o,1,Goto(s,1)

[CheckIfCalledAlready]
exten => s,1,MYSQL(Connect connid xxx.xxx.xxx.xxx TheUser ThePassword TheDatabase)
exten => s,2,MYSQL(Query resultid ${connid} SELECT Date from TheTable where uniqueid=${CALLERID(number)})
exten => s,3,MYSQL(Fetch fetchid ${resultid} HasData)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Disconnect ${connid})
exten => s,6,GoToIf($[ ${HasData} = ${STRFTIME(${EPOCH},,%U)} ]?SorryButWeHaveData1,s,1)
exten => s,7,Flite("Please enter your ticket number",any)
exten => s,8,Read(TENDIGITNUMBER,beep,10,,2,5)
exten => s,9,SayDigits(${TENDIGITNUMBER})
exten => s,10,Flite("If this is correct please press 1, otherwise press 2",any)
exten => s,11,WaitExten()
exten => 1,1,Goto(EnterDataToDB,s,1) 
exten => t,1,Goto(s,1)
exten => i,1,Goto(s,1)
exten => o,1,Goto(s,1)

[SorryButWeHaveData1]
exten => s,1,Flite("I'm sorry, you have called regarding your ticket this week already, please try back.",any)
exten => s,2,Goto(ReadPhoneNumber,s,1)

[EnterDataToDB]
exten => s,1,Flite("Please wait while I process your request.",any)
exten => s,2,MYSQL(Connect connid xxx.xxx.xxx.xxx TheUser ThePassword TheDatabase) 
exten => s,3,MYSQL(Query resultid ${connid} INSERT INTO TheTable SET uniqueid=${CALLERID(number)},number=${TENDIGITNUMBER},Date=${STRFTIME(${EPOCH},,%U)})
exten => s,4,MYSQL(Disconnect ${connid}) 
exten => s,5,Flite("Please wait",any)
5

I would argue that your table is badly designed and loses valuable data, you should probably have an entry for each call, add a field named callerid and

SELECT Date from TheTable where uniqueid=${CALLERID(number)}

could then perhaps be something like

SELECT COUNT(*) FROM TheTable WHERE (To_Days(now()) - To_Days(Date)) < 7 AND callerid=’${CALLERID(number)} ;’

But even on your table the same logic should work