OTRS diagram for Agent, Role, Group and Queue relationship

How to get Open ticket number for one queue in OTRS MySQL database by .Net ?

using MySql.Data.MySqlClient;
public class MySQLDBConnector
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    //Constructor
    public MySQLDBConnector()
    {
        Initialize();
    }

    //Initialize values
    private void Initialize()
    {
        server = "10.0.x.x"; // "otrs.chatham.teksavvy.ca";
        database = "otrs";
        uid = "xxxx";
        password = "xxxx";
        string connectionString = "SERVER=" + server + ";Port=3306;" + "DATABASE=" +
        database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

        connection = new MySqlConnection(connectionString);
    }

    //open connection to database
    public bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            switch (ex.Number)
            {
            }
            return false;
        }
    }

    public int Count()
    {
        string query = @"select count(*) from ticket inner join queue on ticket.queue_id= queue.id inner join ticket_state on ticket_state.id = ticket.ticket_state_id 
where queue.name ='Junk' and ticket.ticket_state_id = 4";
;
        int Count = -1;

        //Open Connection
        if (this.OpenConnection() == true)
        {
            //Create Mysql Command
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //ExecuteScalar will return one value
            Count = int.Parse(cmd.ExecuteScalar() + "");

            //close Connection
            connection.Close();

            return Count;
        }
        else
        {
            return Count;
        }
    }
}

How to get Open ticket count for specic queue in OTRS by SQL query?

select count(*) from ticket
inner join queue on ticket.queue_id= queue.id
inner join ticket_state on ticket_state.id = ticket.ticket_state_id
where queue.name ='Postmaster' and ticket.ticket_state_id = 4

How to add dynamic field in OTRS?

1. Cretae dynamic field first
Admin->Ticket->Dynamic Field

2. Change configuration to make avaiblae in different view
Goto Admin -> SysConfig
search by ###DynamicField
Enable the dynamic field just created
There are a lot of setting for different views.

What is OTRS?

OTRS is an Open-source Ticket Request System, developed by Perl, default database is MySQL.
http://www.otrs.com

Following link is a group of tips for integrating OTRS into .Net project
http://rayaspnet.blogspot.ca/search/label/OTRS

How to call OTRS web service in C#?

There are two ways for calling web service to create/get/search ticket in OTRS:
First way: call RPC.pl
Second way: GenericInterface

Follwoing are the steps for RPC:
1. Setup SOAP user in OTRS:
Log in OTRS with FULL admin permission
Admin -> SysConfig -> Framework -> Core: SOAP
Add and enable SOAP user and password
2. In C# project, prepare SOAP request like following
Then make Webrequest for URL: http://localhost/otrs/RPC.pl

<?xml version=""1.0"" encoding=""utf-8""?>
  <soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""
xmlns:soapenc=""http://schemas.xmlsoap.org/soap/encoding/""
xmlns:xsd=""http://www.w3.org/2001/XMLSchema""
soap:encodingStyle=""http://schemas.xmlsoap.org/soap/encoding/""
xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">
    <soap:Body>
      <Dispatch xmlns=""/Core"">
        <c-gensym4 xsi:type=""xsd:string"">soapUserName</c-gensym4>
        <c-gensym6 xsi:type=""xsd:string"">soapUserPassword</c-gensym6>
        <c-gensym8 xsi:type=""xsd:string"">TicketObject</c-gensym8>
        <c-gensym10 xsi:type=""xsd:string"">TicketSearch</c-gensym10>
        <c-gensym57 xsi:type=""xsd:string"">Limit</c-gensym57>
        <c-gensym59 xsi:type=""xsd:int"">10000</c-gensym59>
        <c-gensym8 xsi:type=""xsd:string"">Result</c-gensym8>
        <c-gensym10 xsi:type=""xsd:string"">ARRAY</c-gensym10>
        <c-gensym57 xsi:type=""xsd:string"">UserID</c-gensym57>
        <c-gensym59 xsi:type=""xsd:int"">1</c-gensym59>
      </Dispatch>
    </soap:Body>
  </soap:Envelope>
3. The result is in the XML format
If you meet problem, please give me an email, may I help a little bit. wangchiwei@gmail.com

OTRS SOAP interface documentation for development

http://www.iniy.org/?p=20
http://dev.otrs.org/3.0/Kernel/System/Ticket.html

How to solve problem "(OS 10013)An attempt was made to access a socket in a way forbidden by its access permissions. : make_sock: could not bind to address 0.0.0.0:80"

This error happens while install Appache, but port 80 is used by other application

Disable all of applications for Port 80
- Run netstat -ao to check if any application is listening this port
http://www.sitepoint.com/unblock-port-80-on-windows-run-apache/

How to reset MySQL password on Windows 7

http://www.geekpedia.com/KB105_How-to-reset-your-MySQL-root-password-under-Windows.html

How to disable port 80 on Windows 7 for OTRS / apache?

- Run netstat -ao to check if any application is listening this port
The disable one by one
http://www.sitepoint.com/unblock-port-80-on-windows-run-apache/

How to install SOAP::Lite module into OTRS on Windows IIS server?

Run Perl\bin\ppm.bat as administrator
Search SOAP and install it

How to set up SOAP / Web service user for OTRS?

- Login into your existing OTRS Installation as Admin
- in the Menu under “Admin”->”Sysconfig” set Group to “Framework” and click “show”
- In the list of subgroups click “Core::SOAP”
- Check the boxes for SOAP::User: and SOAP::Password: and enter the values you like
- click update

http://www.iniy.org/?p=20

Where is database connection setting in OTRS?

Under folder:
\OTRS\Kernel

File name is config.pm

Reference:
http://doc.otrs.org/2.1/en/html/c1154.html