SMS Notification (hacky but works)

Jutboy

First off, I really like this program and am very impressed with it. I decided to add a feature for my personal use, I only spent about 15 minutes on it so its kinda hacky but I am willing to improve it (with some help from you guys) and get it into the next version if you guys want.

FEATURE: If level is Emergency and topic is "Support" send me a SMS message

Step One) Enable Emergency Level - There may be a way to do this in the admin area but I could find it.....open ost_ticket_priority and setup ispublic to 1 for Emergency in database

Step Two) Add filter statement to /open.php that does the main work....

Replace

//Ticket:...checks for errors..

if(($ticket=Ticket:($_POST,$errors,SOURCE))){

$msg='Support ticket request created';

if($thisclient && $thisclient->isValid()) //Logged in...simply view the newly created ticket.

<USERMENTION username="header">@header</USERMENTION>('Location: tickets.php?id='.$ticket->getExtId());

//Thank the user and promise speedy resolution!

$inc='thankyou.inc.php';

}else{

$errors=$errors?$errors:'Unable to create a ticket. Please correct errors below and try again!';

}

with

//Ticket:...checks for errors..

if(($ticket=Ticket:($_POST,$errors,SOURCE))){

$msg='Support ticket request created';

if ($_POST == "4" && $_POST == "1"){

$sql='SELECT email, smsuser '.

' FROM `'.EMAIL_TABLE.'`';

$emails=db_query($sql.' ORDER BY email');

while ($row = db_fetch_array($emails)) {

if ($row == "1"){

$message_to = $row;

$message_from = "noreply@yourdomain.com";

$message_subject = "Emergency Ticket";

$message_body = "Emergency Ticket Just Got Sent";

$headers = 'From: '.$message_from. "\r\n" .

'Reply-To: '. $message_from . "\r\n" .

'X-Mailer: PHP/' . phpversion();

mail($message_to, $message_subject, $message_body, $headers);

}

if($thisclient && $thisclient->isValid()) //Logged in...simply view the newly created ticket.

<USERMENTION username="header">@header</USERMENTION>('Location: tickets.php?id='.$ticket->getExtId());

//Thank the user and promise speedy resolution!

$inc='thankyou.inc.php';

}else{

$errors=$errors?$errors:'Unable to create a ticket. Please correct errors below and try again!';

}

Step 3) Add checkbox to email system...../include/staff/email.inc.php I put it on line 81....

<tr><th>SMS User</th>

<td>

<input type="checkbox" name="smsuser" value=1 <?=$info? 'checked': ''?> ><b>If priority is level 4 (Emergency) and (Billing)</b> email this address.

</td>

</tr>

Step 4) Edit save and load functions in include/class.email.php

Line #50

$sql='SELECT * FROM '.EMAIL_TABLE.' WHERE email_id='.db_input($this->id);

if(($res=db_query($sql)) && db_num_rows($res)) {

$info=db_fetch_array($res);

$this->id=$info;

$this->email=$info;

$this->name=$info;

$this->address=$info?($info.'<'.$info.'>'):$info;

$this->deptId=$info;

$this->priorityId=$info;

$this->autoresp=$info?false;

$this->smsuser=$info?false;

$this->info=$info;

return true;

}

Line #363

$sql='updated=NOW(),mail_errors=0, mail_lastfetch=NULL'.

',email='.db_input($vars).

',name='.db_input(Format:($vars)).

',dept_id='.db_input($vars).

',priority_id='.db_input($vars).

',noautoresp='.db_input(isset($vars)?1).

',smsuser='.db_input(isset($vars)?1).

',userid='.db_input($vars).

',userpass='.db_input(Misc:($vars,SECRET_SALT)).

',mail_active='.db_input($vars).

',mail_host='.db_input($vars).

',mail_protocol='.db_input($vars?$vars:'POP').

',mail_encryption='.db_input($vars).

',mail_port='.db_input($vars?$vars).

',mail_fetchfreq='.db_input($vars?$vars).

',mail_fetchmax='.db_input($vars?$vars).

',mail_delete='.db_input(isset($vars)?$vars).

',smtp_active='.db_input($vars).

',smtp_host='.db_input($vars).

',smtp_port='.db_input($vars?$vars).

',smtp_auth='.db_input($vars);

Your code will have the same queries except the smsuser is not there...just add it in.

Step 5) Add field to table ost_email

ALTER TABLE `ost_email` CHANGE `smsuser` `smsuser` TINYINT( 1 ) NOT NULL DEFAULT '0'

Step 6) Bug Test...

Notes:

This setup does not handle email automatic parsing....I don't know anything about that part of the system but I bet if you moved the filter out of open and into Ticket:() then it would work in both places. I only want it on the web site part of my site however.

This script assumes that topicID for support is 1.

When echoing $_POST, do a intro echo of some random string to see output, I think this has something do with php flush..not 100% but I would be interested to know.

Enjoy, I subscribed to this thread so I'll come back if I wasn't clear about anything.

-J

Jutboy

I didn't even think to mention, the whole idea is to use this to SMS the email you check off smsuser on, simply set the email address to be your wireless carriers email service...

(http://www.tech-recipes.com/rx/939/sms_email_cingular_nextel_sprint_tmobile_verizon_virgin)

ie, I'm on verizon so mine is ##########@vtext.com

manuelbarmby

Pls more light on SMS notification steps

Jutboy,

Am a newbie trying to add your script to the configuration on my server.

I have a few questions to ask.

1. Step One) Enable Emergency Level - There may be a way to do this in the admin area but I could find it.....open ost_ticket_priority and setup ispublic to 1 for Emergency in database

So the thing is: How can I set the open ost_ticket_priority and setup ispublic to 1 for Emergency in database?

2. Add field to table ost_email

ALTER TABLE `ost_email` CHANGE `smsuser` `smsuser` TINYINT( 1 ) NOT NULL DEFAULT '0'

How can add this to where and how?

3 How can i Bug Test...?

Manuel

Jutboy

You need to find a way to edit/control your database Manuel...

I use phpmyadmin, talk to your host and see what they provide. You can always install your own version of phpmyadmin but you shouldn't have to.

Once you get into it, you can edit the tables/rows just like I pointed out.

Jutboy

In regards to bug testing, set it all up, send an emergency ticket see what happens. If you don't get a sms try a direct email to you phone to make sure that is not the problem. If that goes through break down the code and see where the failure point is (does it recognize the ticket is an emergency? Can it pull the sms email address from the db etc.) (echo and exit() are your friend).

manuelbarmby

SMS Notification (hacky but works) // Pls more light on SMS notification steps

Jutboy,

Let me tell you how my setup looks like. Am running osticket on centos 5.5 which has MYSQL as its database. So invariably i dont have any host hosting my database for me since am running all this in my small lab for test be i decide to deploy it onto a production environment.

I can log into MYSQL to do one or two things but the thing is how to edit tables/row is my first challenge. am not so good in mysql as well but am trying.

Thanks

Jutboy

I run my own centos 5.5 box as well. Do you have any control panel installed with it?

http://servertune.com/kbase/entry/299(http://servertune.com/kbase/entry/299)

I'm not sure what your thinking for your "production" setup..if its just going to be a cheapy host I wouldn't waste the time it's going to take transferring and setting everything up again on the new server. I would just build on the location you want it.

Anyway, you can manipulate mysql a lot of ways. If you have ssh (shell) access you could use the command prompt but that is kind of tricky.

http://www.devshed.com/c/a/MySQL/MySQL-in-SSH-Basic-Guide/1(http://www.devshed.com/c/a/MySQL/MySQL-in-SSH-Basic-Guide/1)

I gave you most of the commands to run, one I didn't include was to update priority...

UPDATE `TABLENAMEHERE`.`ost_ticket_priority` SET `ispublic` = '1' WHERE `ost_ticket_priority`.`priority_id` =4;

Like I said you can use install phpmyadmin which is probably going to be the easiest in the long run.

You can also use php directly to do everything you want but I only do this when I want to automated stuff not to run commands manually....

http://php.net/manual/en/function.mysql-query.php(http://php.net/manual/en/function.mysql-query.php)

Really you need to find some good intro to mysql, there are a million guides out there.

manuelbarmby

Re:

hello Jutboy,

The thing is I got this project from one of the telecom coys in Nigeria and they want to be recieving sms alerts on their phones whenever there is any fault alarm on their network,that's why i opted for osticket.

Therefore I have to test-run whatever that needs to be permanently installed for them on a system before finally deploying app for them.

Invariably the whole idea is for the app to do what they want first before i the final deployment.

Jutboy

I'm not sure how much help I can provide with things being so vague. I just don't want you have to set everything up, test it and then have to a bunch of work to move and then, because the production environment is different then the release, have to do testing again.

You can create a duplicate enviroment with some version control software (git or subversion etc.) or build on the full production environment and just keep everything private/hidden/password protected.

manuelbarmby

Re: 2

I dont know my TABLENAME. So I feel need to create a table in mysql in order to query the database with

UPDATE `TABLENAMEHERE`.`ost_ticket_priority` SET `ispublic` = '1' WHERE `ost_ticket_priority`.`priority_id` =4;

Am I right?

Though when I did :

mysql> select database();

+------------+

| database() |

+------------+

| NULL |

+------------+

1 row in set (0.00 sec) this is what i got. Since I have not yet selected any database, the result is NULL.

mysql> show tables;

ERROR 1046 (3D000): No database selected

Jutboy

I think you have to declare this when you install osticket....

http://osticket.com/wiki/Installation(http://osticket.com/wiki/Installation)

along with username/pw and address(usually localhost) of mysql.

anyway the command you are looking for is mysqlshow

http://dev.mysql.com/doc/refman/5.0/en/mysqlshow.html(http://dev.mysql.com/doc/refman/5.0/en/mysqlshow.html)

and for an introduction...

http://dev.mysql.com/tech-resources/articles/mysql_intro.html(http://dev.mysql.com/tech-resources/articles/mysql_intro.html)

-J

manuelbarmby

Am making progress

Dear Jutboy,

I dont know how to thank u for your responses. Av been making progress on it abd av gotten to Step 5. Below is what av been getting when I did: ALTER TABLE `ost_email` CHANGE `smsuser` `smsuser` TINYINT( 1 ) NOT NULL DEFAULT '0';

mysql> use osticket;

mysql> describe ost_email;

+-----------------+---------------------+------+-----+---------------------+----------------+

+-----------------+---------------------+------+-----+---------------------+----------------+

| noautoresp | tinyint(1) unsigned | NO | | 0 | |

| priority_id | tinyint(3) unsigned | NO | MUL | 2 | |

| dept_id | tinyint(3) unsigned | NO | MUL | 0 | |

| email | varchar(125) | NO | UNI | | |

| mail_active | tinyint(1) | NO | | 0 | |

| mail_protocol | enum('POP','IMAP') | NO | | POP | |

| mail_port | int(6) | YES | | NULL | |

| mail_fetchfreq | tinyint(3) | NO | | 5 | |

| mail_fetchmax | tinyint(4) | NO | | 30 | |

| mail_delete | tinyint(1) | NO | | 0 | |

| mail_errors | tinyint(3) | NO | | 0 | |

| smtp_active | tinyint(1) | YES | | 0 | |

| smtp_port | int(6) | YES | | NULL | |

| smtp_secure | tinyint(1) | NO | | 1 | |

| smtp_auth | tinyint(1) | NO | | 1 | |

| created | datetime | NO | | 0000-00-00 00 | |

| updated | datetime | NO | | 0000-00-00 00 | |

+-----------------+---------------------+------+-----+---------------------+----------------+

26 rows in set (0.00 sec)

mysql> ALTER TABLE `ost_email` CHANGE `smsuser` `smsuser` TINYINT( 1 ) NOT NULL DEFAULT '0';

ERROR 1054 (42S22): Unknown column 'smsuser' in 'ost_email'

How do i go about this pls?

manuelbarmby

When I logged into the GUI of osticket as administrator, under "Email" I saw the where there is a checkbox for SMS User ===If priority is level 4 (Emergency) and (Billing) email this address so I tried to check the box and tried to save but an internal error occurred as i was trying to save.

Am just informing you on what av done so far.

Pls help on Step 5.

Thanks

Manuel

manuelbarmby

I just checked my admin email,which is gmail and i saw this in my inbox:

DB Error #1054

osTicket Alerts

show details 10 PM (19 minutes ago)

Unknown column 'smsuser' in 'field list'

Jutboy

Don't worry about the errors yet...

the column smsuser didn't get created properly...try....

ALTER TABLE `ost_email` ADD `smsuser` INT NOT NULL DEFAULT '0'

manuelbarmby

I av added the smsuser and it saved fine

Thanks a bunch Jutboy. I added smsuser and it saved fine.

1. Step 6: Bug test , what do i do here?

2. After getting my mobile carrier, what else do i need to with them. I know the address if am creating a new ticket I'd be using xxxxxxxxxx@gloworld.com i.e xxxxxxxxxx == phone number, and i'd select PHONE under 'Ticket source'. Am I right?

3. I also need you to clarify something for me: Let me explain the scenerio as regards the mobile phone number thing. I'd like to know the precise digit to use.

In Nigeria if someone from outside wants to call in, lets say the +2348060713222

+234 is nigeria area code and the if it's a call within the number would be 08060713222.

Now am I going to use something like 8060713222@gloworld.com?

Jutboy

1.In regards to bug testing, set it all up, send an emergency ticket see what happens. If you don't get a sms try a direct email to you phone to make sure that is not the problem. If that goes through break down the code and see where the failure point is (does it recognize the ticket is an emergency? Can it pull the sms email address from the db etc.) (echo and exit() are your friend).

2. You don't need to select phone or anything like that, its not part of this system and I don't know what it does. Basically you need to check off the checkbox that you added in step3, and set the email address for that user to your phone number's email address.

3.You'll only need one address and it won't change. Your server is sending out this email. Just try sending an email through your email client and see if your phone gets it. Once it does then you know you have the right one.

manuelbarmby

Let me start from the bottom:

3. You'll only need one address and it won't change. Your server is sending out this email. Just try sending an email through your email client and see if your phone gets it. Once it does then you know you have the right one.

My response: You said i only need one address. So what u are saying is that i can only send out one ticket email from osticket at a time.

I also need to know that if I is possible for multiple staffs on osticket can get the alert on their phone.

My response: I think i understand this part

My response: I'd try and test this as soon as possible. But I need to know that where am meant to type email address of osticket staff I'd have to type something like ##########@gloworld.com. and like I said ,can I add more than one phone email addresses on the box where email address is meant to be? Am I right?

Jutboy

The changes I added (step two -> see the loop) will send an email out to all users that have the checkbox "smsuser" (step3). Just create multiple users.

Just start by getting one sms number working.....just use regular email and try to get it working. Don't think about osticket yet. Once you get that working, setup the user with the email address that works, and check off SMS User. Send emergency ticket.... Once you have it working with that user then add the others.

manuelbarmby

1. You said: The changes I added (step two -> see the loop) will send an email out to all users that have the checkbox "smsuser" (step3). Just create multiple users.

My response: It's only under "Email---->Edit Email" settings that has SMS USER checkbox not users/staffs and I checked the box. Though am recieving alerts on any email address i decide to send the ticket to.

2. You also mentioned something about:

I didn't even think to mention, the whole idea is to use this to SMS the email you check off smsuser on, simply set the email address to be your wireless carriers email service...

http://www.tech-recipes.com/rx/939/s...erizon_virgin(http://www.tech-recipes.com/rx/939/s...erizon_virgin)

ie, I'm on verizon so mine is ##########@vtext.com

Am I meant to type in my own phone email address (8023431234@gloworld.com) where the email address is meant to be typed?

Edit Email

Email Info

Settings are mainly for emailed tickets. For online/web tickets see help topics.

Email Address alerts@yahoo.com*

Email Name: Support (Optional email's FROM name.)

New Ticket Priority Emergency *

New Ticket Dept. Support Dept

Auto Response Disable autoresponse for this email. (Overwrite Dept setting)

SMS User (I checked the box)If priority is level 4 (Emergency) and (Billing)email this address.

Username maabitsolutions@gmail.com

Password ***********

Mail Account (Optional)

Setting for fetching incoming emails. Mail fetching must be enabled with autocron active or external

cron setup.

Please be patient, the system will try to login to mail server to validate the entered login info.

Status I enabled this

Host pop.gmail.com

Port 995

Protocol POP

Encryption ssl

Fetch Frequency 5 Delay intervals in minutes

Maximum Emails Per 30 Maximum emails to process per fetch.

Fetch

Delete Messages Delete fetched message(s) (recommended when using POP)

SMTP Settings (Optional)

When enabled the email account will use SMTP server instead of internal PHP mail() function for

outgoing emails.

Please be patient, the system will try to login to SMTP server to validate the entered login info.

Status enabled

SMTP Host ssl://smtp.gmail.com

SMTP Port 465

Authentication ssl

Required?

Under:

System Preferences and Settings (v1.6 ST)

General Settings

Ticket Options & Settings

If enabled ticket lock get auto-renewed on form activity.

Ticket IDs: Sequential Random (recommended)

Ticket Priority: Emergency

Maximum Open

0 per email. (Helps with spam and flood control. Enter 0 for unlimited)

Tickets:

Auto-Lock Time: 0 (Minutes to lock a ticket on activity. Enter 0 to disable locking)

Ticket Grace Period: 0 (Hours before ticket is marked overdue. Enter 0 to disable aging.)

Reopened Tickets: Auto-assign reopened tickets to last respondent 'available'. ( 3 months limit)

Assigned Tickets: Show assigned tickets on open queue.

Answered Tickets: Show answered tickets on open queue.

Ticket Activity Log: Log ticket's activity as internal notes.

Staff Identity: Hide staff's name on responses.

Human Verification: Enable captcha on new web tickets.

Email Settings

Note that global settings can be disabled at dept/email level.

For mail fetcher (POP/IMAP) to work you must set a cron job or simply enable auto-

Incoming cron

Emails: yes Enable POP/IMAP email fetch (Global setting which can be disabled at email

level)

Enable email piping (You pipe we accept policy)

yes Strip quoted reply (depends on the tag below)

Reply Separator Tag

Default Email: Only applies to outgoing emails with no SMTP settings.

Outgoing

Select One None: Use PHP mail function

Emails:

Default

System osTicket Alerts

Email:

Default Alert

Select One Use Default System Email (above)

Email:

System

Admin Email maabitsolutions@gmail.com

Address:

Autoresponders (Global Setting)

This is global setting which can be disabled at department level.

New Ticket: Autoresponse includes the ticket ID required to check status of the ticket

Enable Disable

New Ticket by Staff: Notice sent when staff creates a ticket on behalf of the user (Staff can disable)

Enable Disable

New Message: Message appended to an existing ticket confirmation

Enable Disable

Overlimit notice: Ticket denied notice sent only once on limit violation to the user.

Enable Disable

Note: Admin gets alerts on ALL denials by default.

Alerts & Notices

Notices sent to user use 'No Reply Email' whereas alerts to staff use 'Alert Email' set above as FROM

address respectively.

New Ticket Alert: Enable Disable

Select recipients

Admin Email Department Manager Department Members (spammy)

New Message Alert: Enable Disable

Select recipients

Last Respondent Assigned Staff Department Manager (spammy)

New Internal Note Alert: Enable Disable

Select recipients

Last Respondent Assigned Staff Department Manager (spammy)

Overdue Ticket Alert: Enable Disable

Admin Email gets an alert by default. Select additional recipients below

Assigned Staff Department Manager Department Members (spammy)

System Errors: Enabled errors are sent to admin email set above

System Errors SQL errors Excessive Login attempts