dump all tickets to CSV and others

[danonee]

any chance u can get a dump with the messagges and replyies as well ?

btw even if i set all right the php sql queries etc is not working for me it make me save the same file O_o anyone knows why?

cant get the php version of the sql version working... setted all good anyone willing to help ?

[deserteagle369]

After upgraded to 2.41, my export article function can't work, can anybody help?
link in the report.php page to call export.php page like below:

http://localhost/hesk/admin/export.php? ... 2012-12-30

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/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();
	
$export_type = $_GET['export_type'];
$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];


switch ($export_type)
{
	case 2:
    	export_articles();
        break;
	default:
    	export_tickets();
		break;
}

function echocsv($fields)
{
	$separator = '';
	foreach ($fields as $field) {
		if (preg_match('/\\r|\\n|,|"/', $field)) {
			$field = '"' . str_replace('"', '""', $field) . '"';
		}
		$field = iconv('utf-8','gb2312//IGNORE',$field);
		echo $separator . $field;
		$separator = ',';
	}
	echo "\r\n";
}
	
function export_tickets()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;
	$tickets = array();
	$totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);
	$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_dbEscape($hesk_settings['db_pfix']).'tickets` INNER JOIN hesk_categories ON hesk_tickets.category = hesk_categories.id INNER JOIN hesk_users ON hesk_tickets.owner = hesk_users.id  INNER JOIN hesk_status ON hesk_tickets.status = hesk_status.id INNER JOIN hesk_priority ON hesk_tickets.priority = hesk_priority.id

    WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `Date Created`';
	$res = hesk_dbQuery($sql);
	
	header('Content-Type: text/csv');
	header('Content-Disposition: attachment;filename=hesk_tickets.csv');
	$row = mysql_fetch_assoc($res);
	if ($row) {
		echocsv(array_keys($row));
	}
	while ($row) {
		echocsv($row);
		$row = mysql_fetch_assoc($res);
	}
}

function export_articles()
{
	global $hesk_settings, $hesklang, $date_from, $date_to;
	$tickets = array();
	$totals = array('all' => 0, 'resolved' => 0);
	$dt = DateArray($date_from,$date_to);
	$sql = 'SELECT `hesk_kb_articles`.`id` AS `ID`, `hesk_kb_categories`.`name` AS `Category`, `hesk_kb_articles`.`subject` AS `Subject`, `hesk_kb_articles`.`content` AS `Content`, `hesk_kb_articles`.`dt` AS `Date Created`, `hesk_kb_articles`.`rating` AS `Rating`,  `hesk_kb_articles`.`votes` AS `Votes`, `hesk_users`.`name` AS `Author`, `hesk_kb_articles`.`views` AS `Views`, `hesk_kb_type`.`name` AS `Type`

    FROM `'.hesk_dbEscape($hesk_settings['db_pfix']).'kb_articles` INNER JOIN hesk_kb_categories ON hesk_kb_articles.catid = hesk_kb_categories.id INNER JOIN hesk_users ON hesk_kb_articles.author = hesk_users.id  INNER JOIN hesk_kb_type ON hesk_kb_articles.type = hesk_kb_type.name

    WHERE DATE(`dt`) BETWEEN \'' . hesk_dbEscape($date_from) . '\' AND \'' . hesk_dbEscape($date_to) . '\' GROUP BY `Date Created`';
	$res = hesk_dbQuery($sql);
	
	header('Content-Type: text/csv');
	header('Content-Disposition: attachment;filename=hesk_kb_articles.csv');
	$row = mysql_fetch_assoc($res);
	if ($row) {
		echocsv(array_keys($row));
	}
	while ($row) {
		echocsv($row);
		$row = mysql_fetch_assoc($res);
	}
}

?>

[kavisrini84]

Hi @dr_patso i can't download your sql files. please you can post it again?

[kavisrini84]

are you able to post the sql files again?

or send an email 'kavisrini84@gmail.com'. i need it urgent. please help me

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";

}

?>

[dr_patso]

Hey, you ccan import my files or just create tables naming the priority and their ids


use phpmyadmin, select your database and import these tables

http://hummingbirdofdeath.com/hesk/hesk_priority.sql
http://hummingbirdofdeath.com/hesk/hesk_status.sql

my query will use these tables to get the actual name of those priority and status IDs

If you have modified them in any way, you will need to do that with my tables..

[kavisrini84]

thank you so much for your quick reply.

Hey, you ccan import my files or just create tables naming the priority and their ids


use phpmyadmin, select your database and import these tables

http://hummingbirdofdeath.com/hesk/hesk_priority.sql
http://hummingbirdofdeath.com/hesk/hesk_status.sql

my query will use these tables to get the actual name of those priority and status IDs

If you have modified them in any way, you will need to do that with my tables..

[atif]

Hello,

For a report I need following data for all tickets:

1. difference of (first reply dt) and (ticket raised dt)
2. difference of (ticket status changed to resolved dt) and (ticket raised dt)

Can someboday assist please?

Thanks & Regards