You are here: Home / Ticket System / osTicket / Display The Forgot Ticket Number List Including The Closed Status in osTicket v1.6 RC5

Display The Forgot Ticket Number List Including The Closed Status in osTicket v1.6 RC5

Tuesday, October 6, 2009 · By 15 Comments

For those of you who have implemented the modification I made for osTicket through this article: Add Forgot Ticket Number Feature into osTicket System v1.6 RC5 and need to display the forgot ticket number list including the ticket which have closed status, I made a modification regarding this. Please make sure that you have implemented that modification on that link above before doing this modification.

  1. Open forgotticket.php file, and find this code:

    27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

      $sqle='SELECT email, ticketID, name FROM '.TICKET_TABLE.
      ' WHERE email='.db_input($femail).' AND status = "open"';
  $respe=db_query($sqle);
  $ticketNumber = '';
  $countRec = db_num_rows($respe);
 
  if ($countRec > 0) {
    $i = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name) = db_fetch_row($respe)){
      if ($i==1) {
	  $ticketNumber .= $ticketID;
      } else {
          $ticketNumber .= ", ". $ticketID;	
      }
      $nameForgot = $name;
      $i++;
    }
 
    $tplId=$cfg->getDefaultTemplateId();
 
    $sql='SELECT forgot_ticket_subj,forgot_ticket_body FROM '.EMAIL_TEMPLATE_TABLE.
          ' WHERE cfg_id='.db_input($cfg->getId()).' AND tpl_id='.db_input($tplId);
    $resp=db_query($sql);
    if(db_num_rows($resp) && list($subj,$body)=db_fetch_row($resp)){
      $body = str_replace("%name", $nameForgot, $body);
      $body = str_replace("%email", $email, $body);
      $body = str_replace("%ticketlist", $ticketNumber, $body);

    then replace with this following code:

    27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

      $sqle='SELECT email, ticketID, name, status FROM '.TICKET_TABLE.
      ' WHERE email='.db_input($femail).'';
  $respe=db_query($sqle);
  $ticketNumber = '';
  $countRec = db_num_rows($respe);
 
  if ($countRec > 0) {
    $i = 1;
    $j = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name,$status) = db_fetch_row($respe)){
      if ($status=='open') {
        if ($i==1) {
          $ticketNumber .= $ticketID;
        } else {
          $ticketNumber .= ", ". $ticketID;	
        }
        $i++;
      } else { // closed status
        if ($j==1) {
          $ticketClosed .= $ticketID;
        } else {
          $ticketClosed .= ", ". $ticketID;
        }
        $j++;
      }
      $nameForgot = $name;  	  
    }
 
    $tplId=$cfg->getDefaultTemplateId();
 
    $sql='SELECT forgot_ticket_subj,forgot_ticket_body FROM '.EMAIL_TEMPLATE_TABLE.
         ' WHERE cfg_id='.db_input($cfg->getId()).' AND tpl_id='.db_input($tplId);
    $resp=db_query($sql);
    if(db_num_rows($resp) && list($subj,$body)=db_fetch_row($resp)){
 
      $body = str_replace("%name", $nameForgot, $body);
      $body = str_replace("%email", $email, $body);
      $body = str_replace("%ticketlist", $ticketNumber, $body);
      $body = str_replace("%ticketclosed", $ticketClosed, $body);
  2. Adjust the email template from your admin panel menu: Emails -> Templates:

    from:

    Subject = Forgot Ticket Number
    Message Body = %name,

    This is the list of your tickets:
    %ticketlist

    Admin

    become:

    Subject = Forgot Ticket Number
    Message Body = %name,

    This is the list of your opened tickets:
    %ticketlist

    This is the list of your closed tickets:
    %ticketclosed

    Admin

Hopefully this is helpful.

Share
Filed Under: osTicket Tagged With: , , , , , , , , , ,

431 viewsPrint This Post Print This Post

Comments

  1. Chandu says:
    Wednesday, 18 November 2009 at 13:50:15

    Hi Masino,

    There is problem while updating email templates.It is giving error

    Chandu

    Reply
  2. Masino Sinaga says:
    Thursday, 19 November 2009 at 08:50:06

    Hi Chandu,

    Thanks for your feedback. May I know the error message you received?

    Reply
  3. Francois says:
    Thursday, 31 December 2009 at 21:26:28

    Hi Masino,

    The closed list includes delayed tickets.
    Is there any workaround ?

    thanks,
    fv

    Reply
  4. Masino Sinaga says:
    Sunday, 3 January 2010 at 07:12:04

    @Francois

    Simply exclude them by modifying the SQL script regarding it.

    Reply
  5. Francois says:
    Saturday, 30 January 2010 at 11:55:01

    Masino,

    Sorry not to have been precise enough.
    What I want in the email sent to client is:
    - opened ticket(s),
    - delayed ticket(s),
    - and closed ticket(s).

    So let’s go (please let us know if it does work on your side).

    You just need to change forgotticket.php.

    Find in forgotticket.php:

      if ($countRec > 0) {
    $i = 1;
    $j = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name,$status) = db_fetch_row($respe)){
      if ($status=='open') {
        if ($i==1) {
          $ticketNumber .= $ticketID;
        } else {
          $ticketNumber .= ", ". $ticketID;	
        }
        $i++;
      } else { // closed status
        if ($j==1) {
          $ticketClosed .= $ticketID;
        } else {
          $ticketClosed .= ", ". $ticketID;
        }
        $j++;
      }
      $nameForgot = $name;  	  
    }

    And replace it with:

      if ($countRec > 0) {
    $i = 1;
    $j = 1;
    $k = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name,$status) = db_fetch_row($respe))
    { //begin while
      if ($status=='open') { //if2
        if ($i==1) {
          $ticketNumber .= $ticketID;
        }
        else {
          $ticketNumber .= ", ". $ticketID;
        }
        $i++;
      } //fi2
      elseif ($status=='delay') { //delayed status begin elseif
        if ($j==1) {
          	$ticketDelayed .= $ticketID;
          }
          else {
          	$ticketDelayed .= ", ". $ticketID;
          }
          $j++;
      } //end elseif
        else { //closed status begin else
          if ($k==1) {
            $ticketClosed .= $ticketID;
          }
          else {
          $ticketClosed .= ", ". $ticketID;
          }
        $k++;
       } //closed status end else
      $nameForgot = $name;
    } //end while
 
if ($ticketNumber == "")  { $ticketNumber =  "No opened ticket.";}
	else { $ticketNumber .= ".";};
if ($ticketDelayed == "") { $ticketDelayed = "No delayed ticket.";}
	else { $ticketDelayed .= ".

    To unlock tickets for which you have lost passwords,
    please follow (procedure blablabla…).”;

    //Change this to your needs.
	};
if ($ticketClosed == "")  { $ticketClosed =  "No closed ticket.";}
	else { $ticketClosed .= ".";};

    Then, in the same file find (a few lines below):

          $body = str_replace("%ticketclosed", $ticketClosed, $body);

    After this line, add this one:

          $body = str_replace("%ticketdelayed", $ticketDelayed, $body);

    Save the file and go to your admin panel -> email -> templates -> your template -> Forgot Ticket Number.
    There you can add “%ticketdelayed” variable in your auto-reply message.

    Download the file to your site (keep a copy of the orginal or use svn or cvs as you like) and try it out.

    Do you want to go a bit further?
    You may like to modify: “include/staff/templates.inc.php” to add your new variables so you can remember they’re here and what do they do.

    If so, open this file (include/staff/templates.inc.php) and find:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question. 
 
    <b>Base Variables</b><b>Other Variables</b>
 
                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed
 
 
 
 
                %messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)

    Select this and paste it with:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question.
 
 
    	<b>Base Variables</b>
 
    	<b>Other Variables (OST)</b>
 
    	<b>Added Variables (non OST)</b>
 
                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed
 
 
 
 
            	%messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)
 
 
 
 
                %ticketlistOpened Ticket(s)
                %ticketdelayedDelayed Ticket(s)
                %ticketclosedClosed Ticket(s)

    Save it, download it, and try it.
    Check out the ‘with=”50%”‘ which have been change to 33% and adapt according to your needs.

    If you have any trouble with this I can provide you with unified diffs if you need.

    This is for what it’s worth, don’t blame me if it’s not working.
    Cheers.

    Reply
  6. Francois says:
    Saturday, 30 January 2010 at 12:01:46

    Masino,

    How do I put html code in a comment?
    Everything has been messed after:
    “If so, open this file (include/staff/templates.inc.php) and find:”

    Thanks,
    fv

    Reply
  7. Masino Sinaga says:
    Saturday, 30 January 2010 at 12:24:21

    Francois :

    Masino,

    How do I put html code in a comment?
    Everything has been messed after:
    “If so, open this file (include/staff/templates.inc.php) and find:”

    Thanks,
    fv

    You could enclose the code by using this following tag:
    <pre lang=”php” escaped=”true”>
    your code here …
    </pre>

    Reply
  8. Masino Sinaga says:
    Saturday, 30 January 2010 at 12:26:10

    @Francois

    Thanks so much for your improvement. Hope it helps others who need it.

    Reply
  9. Francois says:
    Saturday, 30 January 2010 at 19:48:28

    Masino,

    Sorry not to have been precise enough.
    What I want in the email sent to client is:
    - opened ticket(s),
    - delayed ticket(s),
    - and closed ticket(s).

    So let’s go (please let us know if it does work on your side).

    You just need to change forgotticket.php.

    Find in forgotticket.php:

      if ($countRec > 0) {
    $i = 1;
    $j = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name,$status) = db_fetch_row($respe)){
      if ($status=='open') {
        if ($i==1) {
          $ticketNumber .= $ticketID;
        } else {
          $ticketNumber .= ", ". $ticketID;
        }
        $i++;
      } else { // closed status
        if ($j==1) {
          $ticketClosed .= $ticketID;
        } else {
          $ticketClosed .= ", ". $ticketID;
        }
        $j++;
      }
      $nameForgot = $name;
    }

    And replace it with:

      if ($countRec > 0) {
    $i = 1;
    $j = 1;
    $k = 1;
    $nameForgot = '';
    while (list($email,$ticketID,$name,$status) = db_fetch_row($respe))
    { //begin while
      if ($status=='open') { //if2
        if ($i==1) {
          $ticketNumber .= $ticketID;
        }
        else {
          $ticketNumber .= ", ". $ticketID;
        }
        $i++;
      } //fi2
      elseif ($status=='delay') { //delayed status begin elseif
        if ($j==1) {
          	$ticketDelayed .= $ticketID;
          }
          else {
          	$ticketDelayed .= ", ". $ticketID;
          }
          $j++;
      } //end elseif
        else { //closed status begin else
          if ($k==1) {
            $ticketClosed .= $ticketID;
          }
          else {
          $ticketClosed .= ", ". $ticketID;
          }
        $k++;
       } //closed status end else
      $nameForgot = $name;
    } //end while

if ($ticketNumber == "")  { $ticketNumber =  "No opened ticket.";}
	else { $ticketNumber .= ".";};
if ($ticketDelayed == "") { $ticketDelayed = "No delayed ticket.";}
	else { $ticketDelayed .= ".

		   To unlock tickets for which you have lost passwords,
		   please follow (procedure blablabla...)."; //Change this to your needs.
	};
if ($ticketClosed == "")  { $ticketClosed =  "No closed ticket.";}
	else { $ticketClosed .= ".";};

    Then, in the same file find (a few lines below):

          $body = str_replace("%ticketclosed", $ticketClosed, $body);

    After this line, add this one:

          $body = str_replace("%ticketdelayed", $ticketDelayed, $body);

    Save the file and go to your admin panel -> email -> templates -> your template -> Forgot Ticket Number.
    There you can add “%ticketdelayed” variable in your auto-reply message.

    Download the file to your site (keep a copy of the orginal or use svn or cvs as you like) and try it out.

    Do you want to go a bit further?
    You may like to modify: “include/staff/templates.inc.php” to add your new variables so you can remember they’re here and what do they do.

    If so, open this file (include/staff/templates.inc.php) and find:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question. 

    Base VariablesOther Variables

                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed

                %messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)

    Select this and replace it with the following:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question.

    	Base Variables

    	Other Variables (OST)

    	Added Variables (non OST)

                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed

            	%messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)

                %ticketlistOpened Ticket(s)
                %ticketdelayedDelayed Ticket(s)
                %ticketclosedClosed Ticket(s)

    Save it, download it, and try it.
    Check out the 2 ‘width=”50%”‘ attributes which have been change to 33% and adapt them according to your needs.

    If you have any trouble with this I can provide you with unified diffs if you need.

    This is for what it’s worth, don’t blame me if it’s not working.
    Cheers.

    Reply
  10. Francois says:
    Saturday, 30 January 2010 at 19:58:40

    Do you want to go a bit further?
    You may like to modify: “include/staff/templates.inc.php” to add your new variables so you can remember they’re here and what do they do.

    If so, open this file (include/staff/templates.inc.php) and find:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question. 

    Base VariablesOther Variables

                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed

                %messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)

    Select this and replace it with the following:

     Variables are used on email templates as placeholders. Please note that non-base variables depends on the context in question.

    	Base Variables

    	Other Variables (OST)

    	Added Variables (non OST)

                %idTicket ID (internal ID)
                %ticketTicket number (external ID)
                %emailEmail address
                %nameFull name
                %subjectSubject
                %topicHelp topic (web only)
                %phonePhone number | ext
                %statusStatus
                %priorityPriority
                %deptDepartment
                %assigned_staffAssigned staff (if any)
                %createdateDate created
                %duedateDue date
                %closedateDate closed

            	%messageMessage (incoming)
                %responseResponse (outgoing)
                %noteInternal note
                %staffStaff's name (alert/notices)
                %assigneeAssigned staff
                %assignerStaff assigning the ticket
                %urlosTicket's base url (FQDN)

                %ticketlistOpened Ticket(s)
                %ticketdelayedDelayed Ticket(s)
                %ticketclosedClosed Ticket(s)
    Reply
  11. Francois says:
    Saturday, 30 January 2010 at 20:10:45

    Masino,

    It’s better with the php code but still doesn’t make it for html.
    In #10, I’ve tried:
    pre lang=”html4strict” escaped=”true”
    …html code…
    /pre

    with no luck.
    A short try with lang=”text”:

    
    Base VariablesOther Variables

    Apparently there’s a plug-in (http://wordpress.org/extend/plugins/insert-html-here/).

    Please could you remove #5, and #10?

    Thanks

    Reply
  12. Masino Sinaga says:
    Sunday, 31 January 2010 at 15:32:38

    @Francois

    Try to replace “html4strict” with “html”. If you want to display php code, use “php” parameter instead of “html”.

    Reply
  13. Francois says:
    Saturday, 6 February 2010 at 01:24:09

    Let’s try a short one:

        	<b>Base Variables</b>
    	<b>Other Variables (OST)</b>    	
    	<b>Added Variables (non OST)</b>
    Reply
  14. Francois says:
    Saturday, 6 February 2010 at 01:30:06

    Nope.
    Doesn’t work with “html” as well.

    I installed a wordpress 2.9.1 and started trying this on my side.
    It works fine fine without extension in the core article though I’ve to check out in comments if it does or not.

    Reply
  15. Masino Sinaga says:
    Saturday, 6 February 2010 at 10:01:59

    @Francois

    Yours did not work since you enclose with double quotation mark twice. I have edited it, and now it works properly. Hope it helps you.

    Reply

Speak Your Mind

*

CAPTCHA Image

*