Urgent - Modify Asterisk IVR/Query - Simple

I need to have someone modify the following IVR so that it does the following. This is an urgent request as we need to have this working tomorrow and the developer that did it hasn't responded to resolve this, even though he said he would. I just about had it done myself, but don't have the time. Here are the 2 things that need to be changed. There is a field in the pr_sites table now called GMT, this holds the + or – GMT time for each location. the systems time is -8 GMT. Need to calculate the local time of the site and modify the Set-Time-Arrive and Set-time-departure query’s to enter the local time using the GMT field. Neet to modify the set-the-comment to go to the pr_comments table, which will require setting the following fields: id_site, author_type = 'provider', author_id=tech_id, date_time = Local Date and Time, content=url Here is the current IVR script [ivr-arrive-tech] exten => s,1,Answer() exten => s,n,Set(TIMEOUT(digit)=3) exten => s,n(WELCOME),Background(ivr-tech/thankyou-for-calling) exten => s,n,Set(tech_id_try=0) exten => s,n(TECH_ID),Set(STEP=TECH_ID) exten => s,n,Set(NEXT_STEP=TECH_NAME) exten => s,n,GotoIf($[${tech_id_try} < 3]?:call-ring-group,s,1) exten => s,n,Set(tech_id_try=$[${tech_id_try} + 1] exten => s,n,BackGround(ivr-tech/please-enter-tech-id) exten => s,n,Read(TECH_ID) exten => s,n,GotoIf($["a${TECH_ID}" = "a"]?s,TECH_ID:${TECH_ID},1) exten => s,n(TECH_NAME),GoSub(tech-name,s,1) exten => s,n,Set(project_id_try=0) exten => s,n(PROJECT_ID),Set(STEP=PROJECT_ID) exten => s,n,Set(NEXT_STEP=PROJECT_NAME) exten => s,n,GotoIf($[${project_id_try} < 3]?:call-ring-group,s,1) exten => s,n,Set(project_id_try=$[${project_id_try} + 1] exten => s,n,BackGround(ivr-tech/please-enter-project-id) exten => s,n,Read(PROJECT_ID) exten => s,n,GotoIf($["a${PROJECT_ID}" = "a"]?s,PROJECT_ID:${PROJECT_ID},1) exten => s,n(PROJECT_NAME),GoSub(project-name,s,1) exten => s,n,Set(store_num_try=0) exten => s,n(STORE_NUM),Set(STEP=STORE_NUM) exten => s,n,Set(NEXT_STEP=STORE_NAME) exten => s,n,GotoIf($[${store_num_try} < 3]?:call-ring-group,s,1) exten => s,n,Set(store_num_try=$[${store_num_try} + 1] exten => s,n,BackGround(ivr-tech/please-enter-the-store-number) exten => s,n,Read(STORE_NUM) exten => s,n,GotoIf($["a${STORE_NUM}" = "a"]?s,STORE_NUM:${STORE_NUM},1) exten => s,n(STORE_NAME),GoSub(store-name,s,1) exten => s,n,Goto(time-recording,s,1) exten => _X,1,Set(ENTER=${LEN(${EXTEN})}) exten => _X,n,GotoIf($["${EXTEN:$[${${LEN(${EXTEN})}} - 1]:${LEN(EXTEN)}}" = "#"]?${EXTEN:0:$[${ENTER} - 1]},1) exten => _X,n,Set(ENTER_${STEP}=${EXTEN}) exten => _X,n,Goto(s,${NEXT_STEP}) exten => _X.,1,Set(ENTER=${LEN(${EXTEN})}) exten => _X.,n,GotoIf($["${EXTEN:$[${${LEN(${EXTEN})}} - 1]:${LEN(EXTEN)}}" = "#"]?${EXTEN:0:$[${ENTER} - 1]},1) exten => _X.,n,Set(ENTER_${STEP}=${EXTEN}) exten => _X.,n,Goto(s,${NEXT_STEP}) exten => i,1,Playback(ivr-tech/invalid-option) exten => i,n,Goto(s,${STEP}) [tech-name] exten => s,1,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=select firstname, secondname from provider where id='${ENTER_TECH_ID}' limit 1) exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} TECH_FIRST_NAME TECH_LAST_NAME) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,Noop(${MYSQL_STATUS}) exten => s,n,GotoIf($["${fetchid}" = "0"]?wrong,1) exten => s,n,Noop("Fecth id = ${fetchid}") exten => s,n,Noop("Tech name = ${TECH_FIRST_NAME} ${TECH_LAST_NAME}") exten => s,n,Set(FILE_NAME=${ENTER_TECH_ID}_${STRFTIME(${EPOCH},+2,%H_%M_%S-%G_%m_%d)}) exten => s,n,System(echo "${TECH_FIRST_NAME} ${TECH_LAST_NAME}"|text2wave -o /tmp/${FILE_NAME}.ulaw -otype ulaw -) exten => s,n,Background(ivr-tech/you-are) exten => s,n,Background(/tmp/${FILE_NAME}) exten => s,n,System(rm /tmp/${FILE_NAME}.ulaw) exten => s,n,Set(tech_name_try=0) exten => s,n(confirmation),GotoIf($[${tech_name_try} < 2]?:call-ring-group,s,1) exten => s,n,Set(tech_name_try=$[${tech_name_try} + 1] exten => s,n,Background(ivr-tech/press-1-to-confirm-or-2-to-enter-again) exten => s,n,WaitExten() exten => 1,1,Noop("Tech name is OK) exten => 1,n,Return() exten => 2,1,Noop("Tech name is WRONG") exten => 2,n,Goto(ivr-arrive-tech,s,TECH_ID) exten => i,1,Playback(ivr-tech/invalid-option) exten => i,n,Goto(s,confirmation) exten => t,1,Goto(s,confirmation) exten => wrong,1,Playback(ivr-tech/you-have-entered-a-wrong-tech-id) exten => wrong,n,Goto(ivr-arrive-tech,s,TECH_ID) exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) [project-name] exten => s,1,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Noop(${MYSQL_STATUS}) exten => s,n,Set(QUERY=select title from projects where id='${ENTER_PROJECT_ID}' limit 1) exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} PROJECT_NAME) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,Noop(${MYSQL_STATUS}) exten => s,n,GotoIf($["${fetchid}" = "0"]?wrong,1) exten => s,n,Noop("Project name = ${PROJECT_NAME}") exten => s,n,System(echo "${PROJECT_NAME} "|text2wave -o /tmp/${FILE_NAME}.ulaw -otype ulaw -) exten => s,n,Background(ivr-tech/project-name-is) exten => s,n,Background(/tmp/${FILE_NAME}) exten => s,n,System(rm /tmp/${FILE_NAME}.ulaw) exten => s,n,Set(project_name_try=0) exten => s,n(confirmation),GotoIf($[${project_name_try} < 2]?:call-ring-group,s,1) exten => s,n,Set(project_name_try=$[${project_name_try} + 1] exten => s,n,Background(ivr-tech/press-1-to-confirm-or-2-to-enter-again) exten => s,n,WaitExten() exten => 1,1,Noop("Project name is OK) exten => 1,n,Return() exten => 2,1,Noop("Project name is WRONG") exten => 2,n,Goto(ivr-arrive-tech,s,PROJECT_ID) exten => i,1,Playback(ivr-tech/invalid-option) exten => i,n,Goto(s,confirmation) exten => wrong,1,Playback(ivr-tech/you-have-entered-a-wrong-project-id) exten => wrong,n,Goto(ivr-arrive-tech,s,PROJECT_ID) exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) [store-name] exten => s,1,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=select id, street_address from pr_sites where id_project='${ENTER_PROJECT_ID}' and store_number='${ENTER_STORE_NUM}' and technician='${ENTER_TECH_ID}' limit 1) exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} STORE_ID STORE_ADDRESS) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,GotoIf($["${fetchid}" = "0"]?wrong,1) exten => s,n,Noop("Store address = ${STORE_ADDRESS}") exten => s,n,System(echo "${STORE_ADDRESS} "|text2wave -o /tmp/${FILE_NAME}.ulaw -otype ulaw -) exten => s,n,Background(ivr-tech/store-address-is) exten => s,n,Background(/tmp/${FILE_NAME}) exten => s,n,System(rm /tmp/${FILE_NAME}.ulaw) exten => s,n,Set(store_name_try=0) exten => s,n(confirmation),GotoIf($[${store_name_try} < 2]?:call-ring-group,s,1) exten => s,n,Set(store_name_try=$[${store_name_try} + 1] exten => s,n,Background(ivr-tech/press-1-to-confirm-or-2-to-enter-again) exten => s,n,WaitExten() exten => 1,1,Noop("Store name is OK) exten => 1,n,Return() exten => 2,1,Noop("Store name is WRONG") exten => 2,n,Goto(ivr-arrive-tech,s,STORE_ID) exten => i,1,Playback(ivr-tech/invalid-option) exten => i,n,Goto(s,confirmation) exten => t,1,Goto(s,confirmation) exten => wrong,1,Playback(ivr-tech/you-have-entered-a-wrong-store-number) exten => wrong,n,Goto(ivr-arrive-tech,s,STORE_NUM) exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) [time-recording] exten => s,1,Set(TIME=${STRFTIME(${EPOCH},+2,%H:%M:%S)}) exten => s,n,Set(DATE=${STRFTIME(${EPOCH},+2,%G-%m-%d)}) exten => s,n,Set(DAY=${STRFTIME(${EPOCH},+2,%d)}) exten => s,n,Set(DATE_PREV=${STRFTIME(${EPOCH},+2,%G-%m-)}$[${DAY} - 1]) exten => s,n,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=select actual_start, actual_stop, comment from pr_sites_workdays where id_site='${STORE_ID}' and date='${DATE}' limit 1) exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} ACTUAL_START ACTUAL_STOP COMMENT) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,GotoIf($["${fetchid}" = "1"]?check1,1) exten => s,n,Set(DAY=${STRFTIME(${EPOCH},+2,%d)}) exten => s,n,Set(DATE=${STRFTIME(${EPOCH},+2,%G-%m-)}$[${DAY} - 1]) exten => s,n,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=select actual_start, actual_stop, comment from pr_sites_workdays where id_site='${STORE_ID}' and date='${DATE}' limit 1) exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} ACTUAL_START ACTUAL_STOP COMMENT) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,GotoIf($["${fetchid}" = "0"]?wrong,1) exten => s,n,GotoIf($["${ACTUAL_START}" = "00:00:00"]?wrong,1) exten => s,n,GotoIf($["${ACTUAL_STOP}" = "00:00:00"]?s,departure) exten => s,n,GotoIf($["${COMMENT}" = "None"]?s,comment) exten => s,n,Goto(wrong,1) exten => s,n(check1),GotoIf($["${ACTUAL_START}" = "00:00:00"]?s,arrive) exten => s,n(check2),GotoIf($["${ACTUAL_STOP}" = "00:00:00"]?s,departure) exten => s,n(check2),GotoIf($["${COMMENT}" = "None"]?s,comment) exten => s,n(check3),Goto(wrong,1) exten => s,n(arrive),Background(ivr-tech/press-one-to-record-arrival-time) exten => s,n,Goto(s,read) exten => s,n(departure),Background(ivr-tech/press-two-to-record-departure-time) exten => s,n(comment),Background(ivr-tech/press-three-to-record-a-comment) exten => s,n(read),WaitExten() exten => 1,1,Noop("Record the arrival time") exten => 1,n,Goto(set-time-arrive,s,1) exten => 2,1,Noop("Record the daparture time") exten => 2,n,Goto(set-time-departure,s,1) exten => 3,1,Noop("Record the commnent about the site") exten => 3,n,Goto(set-the-comment,s,1) exten => i,1,Playback(ivr-tech/invalid-option) exten => i,n,Goto(s,check1) exten => t,1,Goto(s,check1) exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) exten => wrong,1,Playback(ivr-tech/wrong-data-please-wait-while-connecting-to-operator) exten => wrong,n,Goto(call-ring-group,s,1) [set-time-arrive] exten => s,1,Noop("Record the arrival time") exten => s,n,GotoIf($["${DATE}" = "${DATE_PREV}"]?wrong,1) exten => s,n,GotoIf($["${actual_start}" = "00:00:00"]?:wrong,1) exten => s,n,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=update pr_sites_workdays set actual_start='${TIME}' where id_site='${STORE_ID}' and date='${DATE}') exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,Set(QUERY=select actual_start from pr_sites_workdays where id_site='${STORE_ID}' and date='${DATE}') exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} MYSQL_TIME) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,GotoIf($["${MYSQL_TIME}" = "${TIME}"]?:mysql_error,1) exten => s,n,Playback(ivr-tech/arrive-time-is-saved) exten => s,n,Playback(ivr-tech/goodbye) exten => s,n,Hangup() exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) exten => wrong,1,Playback(ivr-tech/wrong-data-please-wait-while-connecting-to-operator) exten => wrong,n,Goto(call-ring-group,s,1) [set-time-departure] exten => s,1,Noop("Record the arrival time") exten => s,n,GotoIf($["${actual_stop}" = "00:00:00"]?:wrong,1) exten => s,n,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=update pr_sites_workdays set actual_stop='${TIME}' where id_site='${STORE_ID}' and date='${DATE}') exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,Set(QUERY=select actual_stop from pr_sites_workdays where id_site='${STORE_ID}' and date='${DATE}') exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Fetch fetchid ${resultid} MYSQL_TIME) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,GotoIf($["${MYSQL_TIME}" = "${TIME}"]?:mysql_error,1) exten => s,n,Playback(ivr-tech/departure-time-is-saved) exten => s,n,Goto(time-recording,s,comment) exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) exten => wrong,1,Playback(ivr-tech/wrong-data-please-wait-while-connecting-to-operator) exten => wrong,n,Goto(call-ring-group,s,1) [set-the-comment] exten => s,1,Noop("Record the comment") exten => s,n,Record(/var/www/html/comments/${FILE_NAME}.wav) exten => s,n,MySQL(Connect connid ${DBHOST} ${DBUSER} ${DBPASS} ${DB}) exten => s,n,GotoIf($["${MYSQL_STATUS}" = "0"]?:mysql_error,1) exten => s,n,Set(QUERY=update pr_sites_workdays set comment='${COMMENT_LINK}/${FILE_NAME}.wav' where id_site='${STORE_ID}' and date='${DATE}') exten => s,n,MYSQL(Query resultid ${connid} ${QUERY}) exten => s,n,MYSQL(Clear ${resultid}) exten => s,n,MYSQL(Disconnect ${connid}) exten => s,n,Playback(ivr-tech/the-comment-is-saved) exten => s,n,Playback(ivr-tech/goodbye) exten => s,n,Hangup() exten => mysql_error,1,Playback(ivr-tech/an-error-has-occured-connecting-to-operator) exten => mysql_error,n,Goto(call-ring-group,s,1) [call-ring-group] exten => s,1,Noop("Dialing ring group") exten => s,n,Goto(ext-group,${RING_GROUP},1)