PHPJunkyard support forum • dump all tickets to CSV and others

Page 1 of 2

dump all tickets to CSV and others

Posted: Wed Sep 26, 2012 1:14 am

by dr_patso

Hi all,

I am sharing SQL queries for Hesk 2.4.1

Here is my general query on hesk tickets, with columns that I deemed important. You can do them in phpmyadmin and export to CSV or create a link to some php code to dump a query to CSV (handy! filter with excel / create pie charts ETC...)

For this to work and you must create a few tables that you may need to update if you change any priorities or statuses.

in phpmyadmin, select your datadase then click import and import my two tables to your hesk database.

http://nerd-alert.info/111aaa/hesk_priority.sql
http://nerd-alert.info/111aaa/hesk_status.sql

Code: Select all

SELECT `hesk_tickets`.`id` AS "Ticket Number", `hesk_tickets`.`dt` AS "Date Created", `hesk_users`.`name` AS Owner, `hesk_categories`.`name` AS Category, `hesk_tickets`.`name` AS "Contact", `hesk_tickets`.`subject` AS Subject, `hesk_status`.`name` AS "Status", `hesk_priority`.`name` AS Priority, `hesk_tickets`.`time_worked` As "Time Worked"

FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

WHERE (

hesk_categories.id = hesk_tickets.category

AND hesk_users.id = hesk_tickets.owner

AND hesk_status.id = hesk_tickets.status

AND hesk_priority.id = hesk_tickets.priority

)

in phpmyadmin you can then export to CSV..

Below is the code i found over at 911-need-to-code for generating CSV.. all you have to do is create a .php file that contains this code and link to it inside of hesk or wherever. You must replace (databaseusernmae / databaseusername / datbasename) with your info.. also update localhost if your mysql server is different from the webserver running hesk.

Code: Select all

<?php

/*

 * PHP code to export MySQL data to CSV

 * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html

 *

 * Sends the result of a MySQL query as a CSV file for download

 */



/*

 * establish database connection

 */



$conn = mysql_connect('localhost', 'databaseusername', 'databasepassword') or die(mysql_error());

mysql_select_db('databasename', $conn) or die(mysql_error($conn));



/*

 * execute sql query

 */



$query = sprintf('SELECT `hesk_tickets`.`id` AS "Ticket Number", `hesk_tickets`.`dt` AS "Date Created", `hesk_users`.`name` AS Owner, `hesk_categories`.`name` AS Category, `hesk_tickets`.`name` AS "Contact", `hesk_tickets`.`subject` AS Subject, `hesk_status`.`name` AS "Status", `hesk_priority`.`name` AS Priority, `hesk_tickets`.`time_worked` As "Time Worked"

FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

WHERE (

hesk_categories.id = hesk_tickets.category

AND hesk_users.id = hesk_tickets.owner

AND hesk_status.id = hesk_tickets.status

AND hesk_priority.id = hesk_tickets.priority

)');

$result = mysql_query($query, $conn) or die(mysql_error($conn));



/*

 * send response headers to the browser

 * following headers instruct the browser to treat the data as a csv file called export.csv

 */



header('Content-Type: text/csv');

header('Content-Disposition: attachment;filename=hesk_tickets.csv');



/*

 * output header row (if atleast one row exists)

 */



$row = mysql_fetch_assoc($result);

if ($row) {

    echocsv(array_keys($row));

}



/*

 * output data rows (if atleast one row exists)

 */



while ($row) {

    echocsv($row);

    $row = mysql_fetch_assoc($result);

}



/*

 * echo the input array as csv data maintaining consistency with most CSV implementations

 * - uses double-quotes as enclosure when necessary

 * - uses double double-quotes to escape double-quotes 

 * - uses CRLF as a line separator

 */



function echocsv($fields)

{

    $separator = '';

    foreach ($fields as $field) {

        if (preg_match('/\\r|\\n|,|"/', $field)) {

            $field = '"' . str_replace('"', '""', $field) . '"';

        }

        echo $separator . $field;

        $separator = ',';

    }

    echo "\r\n";

}

?>

Here is the same thing for timespent per category.

Code: Select all

<?php

/*

 * PHP code to export MySQL data to CSV

 * http://911-need-code-help.blogspot.com/2009/07/export-mysql-data-to-csv-using-php.html

 *

 * Sends the result of a MySQL query as a CSV file for download

 */



/*

 * establish database connection

 */



$conn = mysql_connect('localhost', 'username', 'databasepassword') or die(mysql_error());

mysql_select_db('databasename', $conn) or die(mysql_error($conn));



/*

 * execute sql query

 */



$query = sprintf('SELECT `hesk_categories`.`name` AS Category, SEC_TO_TIME( sum( TIME_TO_SEC( time_worked ) ) ) AS "Time Worked"
FROM hesk_tickets, hesk_categories
WHERE (
hesk_categories.id = hesk_tickets.category
)
GROUP BY hesk_categories.name

');

$result = mysql_query($query, $conn) or die(mysql_error($conn));



/*

 * send response headers to the browser

 * following headers instruct the browser to treat the data as a csv file called export.csv

 */



header('Content-Type: text/csv');

header('Content-Disposition: attachment;filename=timespent.csv');



/*

 * output header row (if atleast one row exists)

 */



$row = mysql_fetch_assoc($result);

if ($row) {

    echocsv(array_keys($row));

}



/*

 * output data rows (if atleast one row exists)

 */



while ($row) {

    echocsv($row);

    $row = mysql_fetch_assoc($result);

}



/*

 * echo the input array as csv data maintaining consistency with most CSV implementations

 * - uses double-quotes as enclosure when necessary

 * - uses double double-quotes to escape double-quotes 

 * - uses CRLF as a line separator

 */



function echocsv($fields)

{

    $separator = '';

    foreach ($fields as $field) {

        if (preg_match('/\\r|\\n|,|"/', $field)) {

            $field = '"' . str_replace('"', '""', $field) . '"';

        }

        echo $separator . $field;

        $separator = ',';

    }

    echo "\r\n";

}

?>

Re: dump all tickets to CSV and others

Posted: Thu Sep 27, 2012 8:36 am

by Klemen

Thanks for sharing!

Re: dump all tickets to CSV and others

Posted: Wed Oct 31, 2012 3:01 am

by deserteagle369

Thanks, I created a export.php and it works. I want to improve it.

It export all tickets, it's better to be able to select the from and to date, I think if integrate it into the report.php then will be able use the calendar jquery javascript. But I tried few days can't get it work.

first, I add a type option:
report.php
line 306

Code: Select all

<option value="5" <?php echo $selected['type'][5]; ?>><?php echo "Ticket Export"; ?></option>

second, add a switch case:
line 360

Code: Select all

	case 5:
		hesk_tickets_export();
		break;

last, add a function:
line 828

Code: Select all

function hesk_tickets_export()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	$tickets = array();
    $totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);

	/* Pre-populate date values */
	foreach ($dt as $day)
	{
		$tickets[$day] = array(
		'all' => 0,
		'resolved' => 0,
		);
	}

	/* SQL query for all */
	$sql = 'SELECT YEAR(`dt`) AS `myyear`, MONTH(`dt`) AS `mymonth`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `myyear`,`mymonth`';
	$sql = 'SELECT `hesk_tickets`.`name` AS "Emp Number", `hesk_tickets`.`email` AS "Email", `hesk_categories`.`name` AS Category,`hesk_priority`.`name` AS Priority, `hesk_tickets`.`subject` AS Subject,`hesk_tickets`.`message` AS Message,`hesk_tickets`.`dt` AS "Date Created", `hesk_tickets`.`lastchange` AS "Date Changed", `hesk_status`.`name` AS "Status",`hesk_users`.`name` AS Owner,  `hesk_tickets`.`custom1` AS "Call/Email", `hesk_tickets`.`custom2` AS "Product Series", `hesk_tickets`.`custom3` AS "Inquiry Type", `hesk_tickets`.`custom4` AS "Escalate to Dept.", `hesk_tickets`.`custom5` AS "Escalate to Name", `hesk_tickets`.`custom6` AS "Chinese Name", `hesk_tickets`.`custom7` AS "Phone Ext.", `hesk_tickets`.`custom8` AS "Office Mobile", `hesk_tickets`.`custom9` AS "Personal Mobile", `hesk_tickets`.`custom10` AS "Eng Name", `hesk_tickets`.`custom11` AS "BU/Department", `hesk_tickets`.`custom12` AS "Region", `hesk_tickets`.`custom13` AS "Work City", `hesk_tickets`.`custom14` AS "On Board Date", `hesk_tickets`.`custom15` AS "Base Office", `hesk_tickets`.`custom16` AS "Reporting Manager", `hesk_tickets`.`custom17` AS "Team Name", `hesk_tickets`.`custom19` AS "E Title", `hesk_tickets`.`custom20` AS "Answer"

    FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

    WHERE ( (DATE(`dt`) BETWEEN `$date_from` AND `$date_to`)  and

    hesk_categories.id = hesk_tickets.category

    AND hesk_users.id = hesk_tickets.owner

    AND hesk_status.id = hesk_tickets.status

    AND hesk_priority.id = hesk_tickets.priority

    ) order by dt asc';
	#die($sql);
    $res = hesk_dbQuery($sql);
	$row = mysql_fetch_assoc($res);
if ($row) {
 print_r($row);
 //   echocsv(array_keys($row));
}
while ($row) {
//    echocsv($row);
    $row = mysql_fetch_assoc($res);
}
function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
		$field = iconv('utf-8','gb2312',$field);
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=hesk_tickets.csv');
}

when I select "Ticket Export" then click "Display Report" it say
Error:

Can't execute SQL.

Can anybody help?

thanks

Re: dump all tickets to CSV and others

Posted: Wed Oct 31, 2012 8:51 am

by Klemen

Having a quick look at your SQL at least the WHERE clause is wrong.

Turn on Debug mode in HESK settings to get the exact SQL error and post it here.

Re: dump all tickets to CSV and others

Posted: Wed Oct 31, 2012 9:31 am

by deserteagle369

MySQL said:
Unknown column '$date_from' in 'where clause'

I copied those piece of code from function hesk_ticketsByDay().

I changed the $date_from, $date_to to "2012-09-01", "2012-09-30",a download window pop up, I got a csv file with the content of report.php not the query result.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Help Desk</title>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<link href="../hesk_style_v23.css" type="text/css" rel="stylesheet" />
<script language="Javascript" type="text/javascript" src="../hesk_javascript.js"></script>
<script language="Javascript" type="text/javascript" src="../inc/calendar/calendar_js.php"></script>
<link href="../inc/calendar/calendar.css" type="text/css" rel="stylesheet" />

</head>

I can't upload the csv file in this post.

Re: dump all tickets to CSV and others

Posted: Wed Oct 31, 2012 10:06 am

by deserteagle369

I saw sb said in stackoverflow this job can't be done in one go, must create a link in report.php link to a export_csv.php file.

since I already have a working export_csv.php file, so problem is only how to pass the from and to date to export_csv.php file and how to get the value do the query.

thx.

Re: dump all tickets to CSV and others

Posted: Wed Oct 31, 2012 11:05 am

by Klemen

You can't use $variables in PHP within single quotes. In your case instead of using value of the $date_from variable value PHP actually uses "$date_from" text (not for example "2012-03-12").

Try changing

Code: Select all

`$date_from` AND `$date_to`

to

Code: Select all

\'' . $date_from . '\' AND \'' . $date_to . '\'

Re: dump all tickets to CSV and others

Posted: Thu Nov 01, 2012 3:55 am

by deserteagle369

can't work, it still export the report.php page not the query result. We need put export un another php file not report.php file.

How to pass date_from and date_to to another php file? I knew like "export.php?from=2012-09-01&to=2012-09-30 can pass two value but no idea how to get the value in export.php

Re: dump all tickets to CSV and others

Posted: Thu Nov 01, 2012 9:27 am

by deserteagle369

Finally, I made it works after whole day digging in the internet.

First, get export.php work.

Code: Select all

<?php
define('IN_SCRIPT',1);
define('HESK_PATH','../');

/* Get all the required files and functions */
require(HESK_PATH . 'hesk_settings.inc.php');
require(HESK_PATH . 'inc/common.inc.php');
require(HESK_PATH . 'inc/database.inc.php');

$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];

hesk_dbConnect();
	$query = 'SELECT `hesk_tickets`.`name` AS "Emp Number", `hesk_tickets`.`email` AS "Email", `hesk_categories`.`name` AS Category,`hesk_priority`.`name` AS Priority, `hesk_tickets`.`subject` AS Subject,`hesk_tickets`.`message` AS Message,`hesk_tickets`.`dt` AS "Date Created", `hesk_tickets`.`lastchange` AS "Date Changed", `hesk_status`.`name` AS "Status",`hesk_users`.`name` AS Owner,  `hesk_tickets`.`custom1` AS "Call/Email", `hesk_tickets`.`custom2` AS "Product Series", `hesk_tickets`.`custom3` AS "Inquiry Type", `hesk_tickets`.`custom4` AS "Escalate to Dept.", `hesk_tickets`.`custom5` AS "Escalate to Name", `hesk_tickets`.`custom6` AS "Chinese Name", `hesk_tickets`.`custom7` AS "Phone Ext.", `hesk_tickets`.`custom8` AS "Office Mobile", `hesk_tickets`.`custom9` AS "Personal Mobile", `hesk_tickets`.`custom10` AS "Eng Name", `hesk_tickets`.`custom11` AS "BU/Department", `hesk_tickets`.`custom12` AS "Region", `hesk_tickets`.`custom13` AS "Work City", `hesk_tickets`.`custom14` AS "On Board Date", `hesk_tickets`.`custom15` AS "Base Office", `hesk_tickets`.`custom16` AS "Reporting Manager", `hesk_tickets`.`custom17` AS "Team Name", `hesk_tickets`.`custom19` AS "E Title", `hesk_tickets`.`custom20` AS "Answer"

    FROM hesk_tickets, hesk_categories, hesk_users, hesk_status, hesk_priority

    WHERE ( (DATE(`dt`) BETWEEN \'' . $date_from . '\' AND \'' . $date_to . '\')  and

    hesk_categories.id = hesk_tickets.category

    AND hesk_users.id = hesk_tickets.owner

    AND hesk_status.id = hesk_tickets.status

    AND hesk_priority.id = hesk_tickets.priority

    ) order by dt asc';
	$result = mysql_query($query); 
	
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=hesk_tickets.csv');
$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}
while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}
function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
		$field = iconv('utf-8','gb2312',$field);
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}
?>

so, the call to export.php will like this export.php?date_from=2012-09-01&date_to=2012-09-30

second, the export function in report.php, to make it simple, I just copy the whole function hesk_ticketsByDay() and rename to hesk_ticket_export which called in the type switch, then add a link before the table start.

Code: Select all

function hesk_tickets_export()
...
	?><a href="export.php?date_from=<?php echo $date_from;?> &date_to=<?php echo $date_to;?>" title="export as csv">Export as CSV</a>
	<br></br>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['date']; ?></td>
	        <td><?php echo $hesklang['atik']; ?></td>
	        <td><?php echo $hesklang['topen']; ?></td>
	        <td><?php echo $hesklang['closed']; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
...

I use all the custom fields, you can delete it if you don't need it.

thanks Klemen correct my sql and dr_patso share the code too.

cheer!

Re: dump all tickets to CSV and others

Posted: Fri Nov 02, 2012 4:00 pm

by dr_patso

awesome!! I am happy someone used and improved this report! I want to mess around with this now. of course after I make this work, the requests for reports dwindle.

Re: dump all tickets to CSV and others

Posted: Tue Nov 27, 2012 5:59 pm

by dr_patso

deserteagle369, could you post your entire reports.php?

I cannot get it to work.

I have copied the entire hesk_tickets_ByDay function and renamed it to hesk_tickets_export and added your link above the table.

Code: Select all

<?php
function hesk_ticket_export()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	$tickets = array();
    $totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);

	/* Pre-populate date values */
	foreach ($dt as $day)
	{
		$tickets[$day] = array(
		'all' => 0,
		'resolved' => 0,
		);
	}

	/* SQL query for all */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['all'] += $row['cnt'];
	    $totals['all'] += $row['cnt'];
	}

	/* SQL query for resolved */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE `status` = \'3\' AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['resolved'] += $row['cnt'];
	    $totals['resolved'] += $row['cnt'];
	}

	?><a href="export.php?date_from=<?php echo $date_from;?> &date_to=<?php echo $date_to;?>" title="export as csv">Export as CSV</a>
   <br></br>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['date']; ?></td>
	        <td><?php echo $hesklang['atik']; ?></td>
	        <td><?php echo $hesklang['topen']; ?></td>
	        <td><?php echo $hesklang['closed']; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo hesk_dateToString($k); ?></td>
	        <td><?php echo $d['all']; ?></td>
	        <td><?php echo $d['all']-$d['resolved']; ?></td>
	        <td><?php echo $d['resolved']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticket_export
?>

I then added ticket_export to text.php (t5 code) and added it to the options.

Code: Select all

    <!-- START TYPE -->
    <p><b><?php echo $hesklang['crt']; ?></b><br />
		<select name="type" style="margin-top:5px;margin-bottom:5px;">
			<option value="1" <?php echo $selected['type'][1]; ?>><?php echo $hesklang['t1']; ?></option>
			<option value="2" <?php echo $selected['type'][2]; ?>><?php echo $hesklang['t2']; ?></option>
			<option value="3" <?php echo $selected['type'][3]; ?>><?php echo $hesklang['t3']; ?></option>
            <option value="4" <?php echo $selected['type'][4]; ?>><?php echo $hesklang['t4']; ?></option>
			<option value="5" <?php echo $selected['type'][13]; ?>><?php echo $hesklang['t5']; ?></option>
		</select>
    </p>
    <!-- END TYPE -->

and added it to the switch

Code: Select all

/* Report type */
switch ($type)
{
	case 2:
    	hesk_ticketsByMonth();
        break;
	case 3:
    	hesk_ticketsByUser();
        break;
	case 4:
    	hesk_ticketsByCategory();
        break;
    case 13:
    	hesk_ticket_export();
        break;
	default:
    	hesk_ticketsByDay();
}

require_once(HESK_PATH . 'inc/footer.inc.php');
exit();

not sure what else I can do.

Re: dump all tickets to CSV and others

Posted: Tue Nov 27, 2012 6:00 pm

by dr_patso

here is my entire reports.php

Code: Select all

<?php
/*******************************************************************************
*  Title: Help Desk Software HESK
*  Version: 2.4.1 from 18th August 2012
*  Author: Klemen Stirn
*  Website: http://www.hesk.com
********************************************************************************
*  COPYRIGHT AND TRADEMARK NOTICE
*  Copyright 2005-2012 Klemen Stirn. All Rights Reserved.
*  HESK is a registered trademark of Klemen Stirn.

*  The HESK may be used and modified free of charge by anyone
*  AS LONG AS COPYRIGHT NOTICES AND ALL THE COMMENTS REMAIN INTACT.
*  By using this code you agree to indemnify Klemen Stirn from any
*  liability that might arise from it's use.

*  Selling the code for this program, in part or full, without prior
*  written consent is expressly forbidden.

*  Using this code, in part or full, to create derivate work,
*  new scripts or products is expressly forbidden. Obtain permission
*  before redistributing this software over the Internet or in
*  any other medium. In all cases copyright and header must remain intact.
*  This Copyright is in full effect in any country that has International
*  Trade Agreements with the United States of America or
*  with the European Union.

*  Removing any of the copyright notices without purchasing a license
*  is expressly forbidden. To remove HESK copyright notice you must purchase
*  a license for this script. For more information on how to obtain
*  a license please visit the page below:
*  https://www.hesk.com/buy.php
*******************************************************************************/

define('IN_SCRIPT',1);
define('HESK_PATH','../');

/* Get all the required files and functions */
require(HESK_PATH . 'hesk_settings.inc.php');
require(HESK_PATH . 'inc/common.inc.php');
require(HESK_PATH . 'inc/admin_functions.inc.php');
require(HESK_PATH . 'inc/reporting_functions.inc.php');
require(HESK_PATH . 'inc/database.inc.php');

hesk_session_start();
hesk_dbConnect();
hesk_isLoggedIn();

/* Check permissions for this feature */
hesk_checkPermission('can_run_reports');

/* Set defaul values */
define('CALENDAR',1);

$selected = array(
	'w'    => array(0=>'',1=>''),
	'time' => array(1=>'',2=>'',3=>'',4=>'',5=>'',6=>'',7=>'',8=>'',9=>'',10=>'',11=>'',12=>''),
    'type' => array(1=>'',2=>'',3=>'',4=>''),
);
$is_all_time = 0;

/* Default this month to date */
$date_from = date('Y-m-d',mktime(0, 0, 0, date("m"), 1, date("Y")));
$date_to = date('Y-m-d');
$input_datefrom = date('m/d/Y', strtotime('last month'));
$input_dateto = date('m/d/Y');

/* Date */
if (!empty($_GET['w']))
{
	$df = preg_replace('/[^0-9]/','',$_GET['datefrom']);
    if (strlen($df) == 8)
    {
    	$date_from = substr($df,4,4) . '-' . substr($df,0,2) . '-' . substr($df,2,2);
        $input_datefrom = substr($df,0,2) . '/' . substr($df,2,2) . '/' . substr($df,4,4);
    }
    else
    {
    	$date_from = date('Y-m-d', strtotime('last month') );
    }

	$dt = preg_replace('/[^0-9]/','',$_GET['dateto']);
    if (strlen($dt) == 8)
    {
    	$date_to = substr($dt,4,4) . '-' . substr($dt,0,2) . '-' . substr($dt,2,2);
        $input_dateto = substr($dt,0,2) . '/' . substr($dt,2,2) . '/' . substr($dt,4,4);
    }
    else
    {
    	$date_to = date('Y-m-d');
    }

    if ($date_from > $date_to)
    {
        $tmp = $date_from;
        $tmp2 = $input_datefrom;

        $date_from = $date_to;
        $input_datefrom = $input_dateto;

        $date_to = $tmp;
        $input_dateto = $tmp2;

        $note_buffer = $hesklang['datetofrom'];
    }

    if ($date_to > date('Y-m-d'))
    {
    	$date_to = date('Y-m-d');
        $input_dateto = date('m/d/Y');
    }

    $query_string = 'reports.php?w=1&datefrom='.urlencode($input_datefrom).'&dateto='.urlencode($input_dateto);
	$selected['w'][1]='checked="checked"';
    $selected['time'][3]='selected="selected"';
}
else
{
	$selected['w'][0]='checked="checked"';
	$_GET['time'] = isset($_GET['time']) ? intval($_GET['time']) : 3;

    switch ($_GET['time'])
    {
    	case 1:
			/* Today */
			$date_from = date('Y-m-d');
			$date_to = $date_from;
			$selected['time'][1]='selected="selected"';
            $is_all_time = 1;
        break;

    	case 2:
			/* Yesterday */
			$date_from = date('Y-m-d',mktime(0, 0, 0, date("m"), date("d")-1, date("Y")));
			$date_to = $date_from;
			$selected['time'][2]='selected="selected"';
            $is_all_time = 1;
        break;

    	case 4:
			/* Last month */
			$date_from = date('Y-m-d',mktime(0, 0, 0, date("m")-1, 1, date("Y")));
			$date_to = date('Y-m-d',mktime(0, 0, 0, date("m"), 0, date("Y")));
			$selected['time'][4]='selected="selected"';
        break;

    	case 5:
			/* Last 30 days */
			$date_from = date('Y-m-d',mktime(0, 0, 0, date("m")-1, date("d"), date("Y")));
			$date_to = date('Y-m-d');
			$selected['time'][5]='selected="selected"';
        break;

    	case 6:
			/* This week */
			list($date_from,$date_to)=dateweek(0);
            $date_to = date('Y-m-d');
			$selected['time'][6]='selected="selected"';
        break;

    	case 7:
			/* Last week */
			list($date_from,$date_to)=dateweek(-1);
			$selected['time'][7]='selected="selected"';
        break;

    	case 8:
			/* This business week */
			list($date_from,$date_to)=dateweek(0,1);
            $date_to = date('Y-m-d');
			$selected['time'][8]='selected="selected"';
        break;

    	case 9:
			/* Last business week */
			list($date_from,$date_to)=dateweek(-1,1);
			$selected['time'][9]='selected="selected"';
        break;

    	case 10:
			/* This year */
			$date_from = date('Y').'-01-01';
			$date_to = date('Y-m-d');
			$selected['time'][10]='selected="selected"';
        break;

    	case 11:
			/* Last year */
			$date_from = date('Y')-1 . '-01-01';
			$date_to = date('Y')-1 . '-12-31';
			$selected['time'][11]='selected="selected"';
        break;

    	case 12:
			/* All time */
			$date_from = hesk_getOldestDate();
			$date_to = date('Y-m-d');
			$selected['time'][12]='selected="selected"';
            $is_all_time = 1;
        break;

        default:
        	$_GET['time'] = 3;
			$selected['time'][3]='selected="selected"';
    }

    $query_string = 'reports.php?w=0&time='.$_GET['time'];
}

unset($tmp);

/* Type */
$type = isset($_GET['type']) ? intval($_GET['type']) : 1;
if (isset($selected['type'][$type]))
{
	$selected['type'][$type] = 'selected="selected"';
}

/* Print header */
require_once(HESK_PATH . 'inc/header.inc.php');

/* Print main manage users page */
require_once(HESK_PATH . 'inc/show_admin_nav.inc.php');
?>

</td>
</tr>
<tr>
<td>

<?php
/* This will handle error, success and notice messages */
hesk_handle_messages();
?>

<h3 align="center"><?php echo $hesklang['reports']; ?></h3>

<p><?php echo $hesklang['reports_intro']; ?></p>


<table width="100%" border="0" cellspacing="0" cellpadding="0">
	<tr>
		<td width="7" height="7"><img src="../img/roundcornerslt.jpg" width="7" height="7" alt="" /></td>
		<td class="roundcornerstop"></td>
		<td><img src="../img/roundcornersrt.jpg" width="7" height="7" alt="" /></td>
	</tr>
	<tr>
	<td class="roundcornersleft">&nbsp;</td>
	<td>

	<form action="reports.php" method="get" name="form1">

    <table border="0" width="100%">
    <tr>
    <td valign="top" width="50%">

    <!-- START DATE -->
    <p><b><?php echo $hesklang['cdr']; ?></b><br />
        <input type="radio" name="w" value="0" id="w0" <?php echo $selected['w'][0]; ?> />
		<select name="time" onclick="document.getElementById('w0').checked = true" onfocus="document.getElementById('w0').checked = true" style="margin-top:5px;margin-bottom:5px;">
			<option value="1" <?php echo $selected['time'][1]; ?>><?php echo $hesklang['r1']; ?> (<?php echo $hesklang['d'.date('w')]; ?>)</option>
			<option value="2" <?php echo $selected['time'][2]; ?>><?php echo $hesklang['r2']; ?> (<?php echo $hesklang['d'.date('w',mktime(0, 0, 0, date('m'), date('d')-1, date('Y')))]; ?>)</option>
			<option value="3" <?php echo $selected['time'][3]; ?>><?php echo $hesklang['r3']; ?> (<?php echo $hesklang['m'.date('n')]; ?>)</option>
			<option value="4" <?php echo $selected['time'][4]; ?>><?php echo $hesklang['r4']; ?> (<?php echo $hesklang['m'.date('n',mktime(0, 0, 0, date('m')-1, date('d'), date('Y')))]; ?>)</option>
			<option value="5" <?php echo $selected['time'][5]; ?>><?php echo $hesklang['r5']; ?></option>
			<option value="6" <?php echo $selected['time'][6]; ?>><?php echo $hesklang['r6']; ?></option>
			<option value="7" <?php echo $selected['time'][7]; ?>><?php echo $hesklang['r7']; ?></option>
			<option value="8" <?php echo $selected['time'][8]; ?>><?php echo $hesklang['r8']; ?></option>
			<option value="9" <?php echo $selected['time'][9]; ?>><?php echo $hesklang['r9']; ?></option>
			<option value="10" <?php echo $selected['time'][10]; ?>><?php echo $hesklang['r10']; ?> (<?php echo date('Y'); ?>)</option>
			<option value="11" <?php echo $selected['time'][11]; ?>><?php echo $hesklang['r11']; ?> (<?php echo date('Y',mktime(0, 0, 0, date('m'), date('d'), date('Y')-1)); ?>)</option>
			<option value="12" <?php echo $selected['time'][12]; ?>><?php echo $hesklang['r12']; ?></option>
		</select>

        <br />

        <input type="radio" name="w" value="1" id="w1" <?php echo $selected['w'][1]; ?> />
		<?php echo $hesklang['from']; ?> <input type="text" name="datefrom" value="<?php echo $input_datefrom; ?>" id="datefrom" class="tcal" size="10" onclick="document.getElementById('w1').checked = true" onfocus="document.getElementById('w1').checked = true;this.focus;" />
        <?php echo $hesklang['to']; ?> <input type="text" name="dateto" value="<?php echo $input_dateto; ?>" id="dateto" class="tcal" size="10" onclick="document.getElementById('w1').checked = true" onfocus="document.getElementById('w1').checked = true; this.focus;" />
    </p>
    <!-- END DATE -->

    </td>
    <td valign="top" width="50%">

    <!-- START TYPE -->
    <p><b><?php echo $hesklang['crt']; ?></b><br />
		<select name="type" style="margin-top:5px;margin-bottom:5px;">
			<option value="1" <?php echo $selected['type'][1]; ?>><?php echo $hesklang['t1']; ?></option>
			<option value="2" <?php echo $selected['type'][2]; ?>><?php echo $hesklang['t2']; ?></option>
			<option value="3" <?php echo $selected['type'][3]; ?>><?php echo $hesklang['t3']; ?></option>
            <option value="4" <?php echo $selected['type'][4]; ?>><?php echo $hesklang['t4']; ?></option>
			<option value="5" <?php echo $selected['type'][13]; ?>><?php echo $hesklang['t5']; ?></option>
		</select>
    </p>
    <!-- END TYPE -->

    </td>
    </tr>
    </table>

	<p align="center">
    <input type="hidden" name="token" value="<?php hesk_token_echo(); ?>" />
    <input type="submit" value="<?php echo $hesklang['dire']; ?>" class="orangebutton" onmouseover="hesk_btn(this,'orangebuttonover');" onmouseout="hesk_btn(this,'orangebutton');" />
    </p>
	</form>

    </td>
	<td class="roundcornersright">&nbsp;</td>
	</tr>
	<tr>
	<td><img src="../img/roundcornerslb.jpg" width="7" height="7" alt="" /></td>
	<td class="roundcornersbottom"></td>
	<td width="7" height="7"><img src="../img/roundcornersrb.jpg" width="7" height="7" alt="" /></td>
	</tr>
</table>

<p>&nbsp;</p>

<?php
if ($date_from == $date_to)
{
	?>
	<p><b><?php echo hesk_dateToString($date_from,0); ?></b></p>
	<?php
}
else
{
	?>
	<p><b><?php echo hesk_dateToString($date_from,0); ?></b> - <b><?php echo hesk_dateToString($date_to,0); ?></b></p>
	<?php
}

/* Report type */
switch ($type)
{
	case 2:
    	hesk_ticketsByMonth();
        break;
	case 3:
    	hesk_ticketsByUser();
        break;
	case 4:
    	hesk_ticketsByCategory();
        break;
    case 13:
    	hesk_ticket_export();
        break;
	default:
    	hesk_ticketsByDay();
}

require_once(HESK_PATH . 'inc/footer.inc.php');
exit();


/*** START FUNCTIONS ***/


function hesk_ticketsByCategory()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	/* List of categories */
	$cat = array();
	$sql = "SELECT `id`,`name` FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."categories` ORDER BY `id` ASC";
	$res = hesk_dbQuery($sql);
	while ($row=hesk_dbFetchAssoc($res))
	{
		$cat[$row['id']]=$row['name'];
	}

	$tickets = array();
    $totals = array('num_tickets' => 0, 'all_replies' => 0, 'staff_replies' => 0);

    /* Populate category counts */
    foreach ($cat as $id => $name)
    {
    	$tickets[$id] = array(
        'num_tickets' => 0,
        'all_replies' => 0,
        'staff_replies' => 0,
        );
    }

	/* SQL query for category stats */
	$sql='
	SELECT DISTINCT `t1`.`category`, `t2`.`num_tickets`, IFNULL(`t3`.`all_replies`,0) AS `all_replies`, IFNULL(`t4`.`staff_replies`,0) AS `staff_replies`
	FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` AS `t1`
	LEFT JOIN
	(SELECT COUNT(*) AS `num_tickets` , `category` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` AS `t1` WHERE DATE(`t1`.`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `category`) AS `t2`
	ON `t1`.`category`=`t2`.`category`
	LEFT JOIN
	(SELECT COUNT(*) AS `all_replies` , `t1`.`category` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` AS `t1`, `'.hesk_dbEscape($hesk_settings['db_pfix']).'replies` AS `t5` WHERE `t1`.`id`=`t5`.`replyto` AND DATE(`t5`.`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `t1`.`category`) AS `t3`
	ON `t1`.`category`=`t3`.`category`
	LEFT JOIN
	(SELECT COUNT(*) AS `staff_replies`, `t1`.`category` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` AS `t1`, `'.hesk_dbEscape($hesk_settings['db_pfix']).'replies` AS `t5` WHERE `t1`.`id`=`t5`.`replyto` AND `t5`.`staffid`>0 AND DATE(`t5`.`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `t1`.`category`) AS `t4`
	ON `t1`.`category`=`t4`.`category`
	WHERE DATE(`t1`.`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\'
	';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
    	if (isset($cat[$row['category']]))
        {
        	$tickets[$row['category']]['num_tickets'] += $row['num_tickets'];
            $tickets[$row['category']]['all_replies'] += $row['all_replies'];
            $tickets[$row['category']]['staff_replies'] += $row['staff_replies'];
        }
        else
        {
        	/* Category deleted */
        	$tickets[9999]['num_tickets'] += $row['num_tickets'];
            $tickets[9999]['all_replies'] += $row['all_replies'];
            $tickets[9999]['staff_replies'] += $row['staff_replies'];
        }

		$totals['num_tickets'] += $row['num_tickets'];
		$totals['all_replies'] += $row['all_replies'];
		$totals['staff_replies'] += $row['staff_replies'];
	}

	?>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['category']; ?></td>
	        <td><?php echo $hesklang['tickets']; ?></td>
	        <td><?php echo $hesklang['replies'] . ' (' . $hesklang['all'] .')'; ?></td>
	        <td><?php echo $hesklang['replies'] . ' (' . $hesklang['staff'] .')'; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['num_tickets']; ?></b></td>
	        <td><b><?php echo $totals['all_replies']; ?></b></td>
	        <td><b><?php echo $totals['staff_replies']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo $cat[$k]; ?></td>
	        <td><?php echo $d['num_tickets']; ?></td>
	        <td><?php echo $d['all_replies']; ?></td>
	        <td><?php echo $d['staff_replies']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['num_tickets']; ?></b></td>
	        <td><b><?php echo $totals['all_replies']; ?></b></td>
	        <td><b><?php echo $totals['staff_replies']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticketsByCategory


function hesk_ticketsByUser()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	/* List of users */
	$admins = array();
	$sql = "SELECT `id`,`name` FROM `".hesk_dbEscape($hesk_settings['db_pfix'])."users` ORDER BY `id` ASC";

	$res = hesk_dbQuery($sql);
	while ($row=hesk_dbFetchAssoc($res))
	{
		$admins[$row['id']]=$row['name'];
	}

    $admins[9999] = $hesklang['e_udel'];

	$tickets = array();
    $totals = array('asstickets' => 0, 'tickets' => 0, 'replies' => 0);

    /* Populate admin counts */
    foreach ($admins as $id => $name)
    {
    	$tickets[$id] = array(
        'asstickets' => 0,
        'tickets' => 0,
        'replies' => 0,
        );
    }

	/* SQL query for tickets */
	$sql = 'SELECT `owner`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE `owner` > 0 AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `owner`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
    	if (isset($admins[$row['owner']]))
        {
            $tickets[$row['owner']]['asstickets'] += $row['cnt'];
        }
        else
        {
            $tickets[9999]['asstickets'] += $row['cnt'];
        }
        $totals['asstickets'] += $row['cnt'];
	}

	/* SQL query for replies */
	$sql = 'SELECT `staffid`, COUNT(*) AS `cnt`, COUNT(DISTINCT `replyto`) AS `tcnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'replies` WHERE `staffid` > 0 AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `staffid`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
    	if (isset($admins[$row['staffid']]))
        {
        	$tickets[$row['staffid']]['tickets'] += $row['tcnt'];
            $tickets[$row['staffid']]['replies'] += $row['cnt'];
        }
        else
        {
        	/* User deleted */
            if (isset($tickets[9999]))
            {
	        	$tickets[9999]['tickets'] += $row['tcnt'];
	            $tickets[9999]['replies'] += $row['cnt'];
            }
            else
            {
	        	$tickets[9999]['tickets'] = $row['tcnt'];
	            $tickets[9999]['replies'] = $row['cnt'];
            }
        }

		$totals['tickets'] += $row['tcnt'];
		$totals['replies'] += $row['cnt'];
	}

    /* Do we have any posts from users who had their account deleted? */
    if (isset($tickets[9999]) && empty($tickets[9999]['tickets']) && empty($tickets[9999]['replies']) && empty($tickets[9999]['asstickets']))
    {
    	unset($tickets[9999]);
    }
	?>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['user']; ?></td>
	        <td><?php echo $hesklang['ticass']; ?></td>
	        <td><?php echo $hesklang['ticall']; ?></td>
	        <td><?php echo $hesklang['replies']; ?></td>
	      </tr>

		  
	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['asstickets']; ?></b></td>
	        <td><b><?php echo $totals['tickets']; ?></b></td>
	        <td><b><?php echo $totals['replies']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo $admins[$k]; ?></td>
	        <td><?php echo $d['asstickets']; ?></td>
	        <td><?php echo $d['tickets']; ?></td>
	        <td><?php echo $d['replies']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['asstickets']; ?></b></td>
	        <td><b><?php echo $totals['tickets']; ?></b></td>
	        <td><b><?php echo $totals['replies']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticketsByUser


function hesk_ticketsByMonth()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	$tickets = array();
    $totals = array('all' => 0, 'resolved' => 0);
	$dt = MonthsArray($date_from,$date_to);

    #print_r($dt);

	/* Pre-populate date values */
	foreach ($dt as $month)
	{
		$tickets[$month] = array(
		'all' => 0,
		'resolved' => 0,
		);
	}

	/* SQL query for all */
	$sql = 'SELECT YEAR(`dt`) AS `myyear`, MONTH(`dt`) AS `mymonth`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `myyear`,`mymonth`';
	#die($sql);
    $res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
    	$row['mymonth'] = sprintf('%02d',$row['mymonth']);
		$tickets[$row['myyear'].'-'.$row['mymonth'].'-01']['all'] += $row['cnt'];
	    $totals['all'] += $row['cnt'];
	}

	/* SQL query for resolved */
	$sql = 'SELECT YEAR(`dt`) AS `myyear`, MONTH(`dt`) AS `mymonth`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE `status` = \'3\' AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `myyear`,`mymonth`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
    	$row['mymonth'] = sprintf('%02d',$row['mymonth']);
		$tickets[$row['myyear'].'-'.$row['mymonth'].'-01']['resolved'] += $row['cnt'];
	    $totals['resolved'] += $row['cnt'];
	}

	?>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['month']; ?></td>
	        <td><?php echo $hesklang['atik']; ?></td>
	        <td><?php echo $hesklang['topen']; ?></td>
	        <td><?php echo $hesklang['closed']; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo hesk_dateToString($k,0,0,1); ?></td>
	        <td><?php echo $d['all']; ?></td>
	        <td><?php echo $d['all']-$d['resolved']; ?></td>
	        <td><?php echo $d['resolved']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticketsByMonth


function hesk_ticketsByDay()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	$tickets = array();
    $totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);

	/* Pre-populate date values */
	foreach ($dt as $day)
	{
		$tickets[$day] = array(
		'all' => 0,
		'resolved' => 0,
		);
	}

	/* SQL query for all */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['all'] += $row['cnt'];
	    $totals['all'] += $row['cnt'];
	}

	/* SQL query for resolved */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE `status` = \'3\' AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['resolved'] += $row['cnt'];
	    $totals['resolved'] += $row['cnt'];
	}

	?>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['date']; ?></td>
	        <td><?php echo $hesklang['atik']; ?></td>
	        <td><?php echo $hesklang['topen']; ?></td>
	        <td><?php echo $hesklang['closed']; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo hesk_dateToString($k); ?></td>
	        <td><?php echo $d['all']; ?></td>
	        <td><?php echo $d['all']-$d['resolved']; ?></td>
	        <td><?php echo $d['resolved']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticketsByDay
?>

<?php
function hesk_ticket_export()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;

	$tickets = array();
    $totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);

	/* Pre-populate date values */
	foreach ($dt as $day)
	{
		$tickets[$day] = array(
		'all' => 0,
		'resolved' => 0,
		);
	}

	/* SQL query for all */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['all'] += $row['cnt'];
	    $totals['all'] += $row['cnt'];
	}

	/* SQL query for resolved */
	$sql = 'SELECT DATE(`dt`) AS `mydt`, COUNT(*) AS `cnt` FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'tickets` WHERE `status` = \'3\' AND DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `mydt`';
	$res = hesk_dbQuery($sql);

	/* Update ticket values */
	while ($row = hesk_dbFetchAssoc($res))
	{
		$tickets[$row['mydt']]['resolved'] += $row['cnt'];
	    $totals['resolved'] += $row['cnt'];
	}

	?><a href="export.php?date_from=<?php echo $date_from;?> &date_to=<?php echo $date_to;?>" title="export as csv">Export as CSV</a>
   <br></br>
	    <table width="100%" cellpadding="5" style="text-align:justify;border-collapse:collapse;padding:10px;">
	      <tr style="border-bottom:1px solid #000000;">
	        <td><?php echo $hesklang['date']; ?></td>
	        <td><?php echo $hesklang['atik']; ?></td>
	        <td><?php echo $hesklang['topen']; ?></td>
	        <td><?php echo $hesklang['closed']; ?></td>
	      </tr>

	<?php
	$num_tickets = count($tickets);
	if ($num_tickets > 10)
	{
	?>
	      <tr style="border-bottom:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	<?php
	}

	$cls = '';
	foreach ($tickets as $k => $d)
	{
		$cls = $cls ? '' : 'style="background:#EEEEE8;"';

	    ?>
	      <tr <?php echo $cls; ?>>
	        <td><?php echo hesk_dateToString($k); ?></td>
	        <td><?php echo $d['all']; ?></td>
	        <td><?php echo $d['all']-$d['resolved']; ?></td>
	        <td><?php echo $d['resolved']; ?></td>
	      </tr>
	    <?php
	}
	?>
	      <tr style="border-top:1px solid #000000;">
	        <td><b><?php echo $hesklang['totals']; ?></b></td>
	        <td><b><?php echo $totals['all']; ?></b></td>
	        <td><b><?php echo $totals['all']-$totals['resolved']; ?></b></td>
	        <td><b><?php echo $totals['resolved']; ?></b></td>
	      </tr>
	    </table>

	    <p>&nbsp;</p>
    <?php
} // END hesk_ticket_export
?>

Re: dump all tickets to CSV and others

Posted: Tue Nov 27, 2012 7:37 pm

by dr_patso

I've got the link to appear by using switch number 5..

I'm not sure where the code is to retain what you have selected in the select box for report type. After I select export tickets and hit display report, the right function is being run, but the select box defaults back to Tickets per day.

here is how my switch and option look now.

options:

Code: Select all

    <!-- START TYPE -->
    <p><b><?php echo $hesklang['crt']; ?></b><br />
		<select name="type" style="margin-top:5px;margin-bottom:5px;">
			<option value="1" <?php echo $selected['type'][1]; ?>><?php echo $hesklang['t1']; ?></option>
			<option value="2" <?php echo $selected['type'][2]; ?>><?php echo $hesklang['t2']; ?></option>
			<option value="3" <?php echo $selected['type'][3]; ?>><?php echo $hesklang['t3']; ?></option>
            <option value="4" <?php echo $selected['type'][4]; ?>><?php echo $hesklang['t4']; ?></option>
			<option value="5" <?php echo $selected['type'][5]; ?>><?php echo $hesklang['t5']; ?></option>
		</select>
    </p>
    <!-- END TYPE -->

switch

Code: Select all

/* Report type */
switch ($type)
{
	case 2:
    	hesk_ticketsByMonth();
        break;
	case 3:
    	hesk_ticketsByUser();
        break;
	case 4:
    	hesk_ticketsByCategory();
        break;
    case 5:
    	hesk_ticket_export();
        break;
	default:
    	hesk_ticketsByDay();
}

require_once(HESK_PATH . 'inc/footer.inc.php');
exit();

Note: Case 5 is what I added.

Re: dump all tickets to CSV and others

Posted: Tue Nov 27, 2012 7:48 pm

by dr_patso

got it!

find this reports.php

Code: Select all

'type' => array(1=>'',2=>'',3=>'',4=>''),

and change to

Code: Select all

    'type' => array(1=>'',2=>'',3=>'',4=>'',5=>''),

Re: dump all tickets to CSV and others

Posted: Thu Dec 06, 2012 9:37 am

by deserteagle369

Congratuation!
I also made export article possible.

Code: Select all

	<a href="export.php?export_type=1&date_from=<?php echo $date_from;?>&date_to=<?php echo $date_to;?>" title="export as csv">Ticket Export as CSV</a>
	<br></br>
	<a href="export.php?export_type=2&date_from=<?php echo $date_from;?>&date_to=<?php echo $date_to;?>" title="export as csv">Article Export as CSV</a>
	<br></br>