Version of script: 2.41
Hosting company:
URL of phpinfo.php:
URL of session_test.php:
What terms did you try when SEARCHING for a solution:
Write your message below:
Features:
1. Clickable link in report.php page;
2. replace id with true vale;
3. Structure the knowledge base article category;
that mean, combine the parent category with child category seperate by ":", after open csv file in excel, you can use data -> text to columns -> delimited -> other:: to seperate it in more than one columns which good for analysis like make pivot table.
1. admin report - export to csv
D:\wamp\www\hesk\admin\reports.php
line 58
from
to'type' => array(1=>'',2=>'',3=>'',4=>''),
Code: Select all
'type' => array(1=>'',2=>'',3=>'',4=>'',5=>''),D:\wamp\www\hesk\admin\reports.php
line 293
Code: Select all
<option value="5" <?php echo $selected['type'][5]; ?>><?php echo "Export to csv"; ?></option>D:\wamp\www\hesk\admin\reports.php
line 346
Code: Select all
case 5:
hesk_export_csv();
break;D:\wamp\www\hesk\admin\reports.php
line 794
add function hesk_export()
after function hesk_ticketsByDay()
Code: Select all
function hesk_export_csv()
{
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 = '';
?>
<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>
<?php
}
?>D:\wamp\www\hesk\admin\export.php
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');
function echocsv($fields)
{
$separator = '';
foreach ($fields as $field) {
if (preg_match('/\\r|\\n|,|"/', $field)) {
$field = '"' . str_replace('"', '""', $field) . '"';
}
$field = strip_tags($field);
$field = iconv('utf-8','gbk',$field);
echo $separator . $field;
$separator = ',';
}
echo "\r\n";
}
$export_type = $_GET['export_type'];
$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];
hesk_dbConnect();
switch ($export_type)
{
case 2:
export_articles();
break;
default:
export_tickets();
break;
}
function export_tickets()
{
global $date_from, $date_to;
$query = 'SELECT `a`.`id` AS `ID`, `a`.`trackid` AS `TrackID`, `a`.`name` AS `Emp Number`, `a`.`email` AS `Email`, `b`.`name` AS `Category`,`p`.`name` AS `Priority`, `a`.`subject` AS `Subject`,`a`.`message` AS `Message`,`a`.`dt` AS `Date Created`, `a`.`lastchange` AS `Date Changed`, `s`.`name` AS `Status`,`u`.`name` AS `Owner`, `r`.`name` AS `Last Replier`, `a`.`custom1` AS `Call/Email`, `a`.`custom2` AS `Product Series`, `a`.`custom3` AS `Inquiry Type`, `a`.`custom4` AS `Escalate to Dept.`, `a`.`custom5` AS `Escalate to Name`, `a`.`custom6` AS `Chinese Name`, `a`.`custom7` AS `Phone Ext.`, `a`.`custom8` AS `Office Mobile`, `a`.`custom9` AS `Personal Mobile`, `a`.`custom10` AS `Eng Name`, `a`.`custom11` AS `BU/Department`, `a`.`custom12` AS `Region`, `a`.`custom13` AS `Work City`, `a`.`custom14` AS `On Board Date`, `a`.`custom15` AS `Base Office`, `a`.`custom16` AS `Reporting Manager`, `a`.`custom17` AS `Team Name`, `a`.`custom18` AS ` Sales Type`, `a`.`custom19` AS `E Title`, `a`.`custom20` AS `Answer`
FROM hesk_tickets as a
LEFT JOIN hesk_categories as b ON a.category = b.id
LEFT JOIN hesk_users as u ON a.owner = u.id
LEFT JOIN hesk_users as r ON a.replierid = r.id
LEFT JOIN hesk_status as s ON a.status = s.id
LEFT JOIN hesk_priority as p ON a. priority = p.id
WHERE (DATE(`dt`) BETWEEN \'' . $date_from . '\' AND \'' . $date_to . '\') 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 export_articles()
{
global $date_from, $date_to;
$query = 'SELECT `a`.`id` AS `ID`, concat(CASE WHEN ISNULL(`f`.`name`) THEN "" ELSE concat(`f`.`name`,":") END,CASE WHEN ISNULL(`e`.`name`) THEN "" ELSE concat(`e`.`name`,":") END,CASE WHEN ISNULL(`d`.`name`) THEN "" ELSE concat(`d`.`name`,":") END,CASE WHEN ISNULL(`c`.`name`) THEN "" ELSE concat(`c`.`name`,":") END,CASE WHEN ISNULL(`b`.`name`) THEN "" ELSE `b`.`name` END) AS `Category`, `a`.`subject` AS `Subject`,`a`.`content` AS `Content`,`a`.`dt` AS `Date Created`, `a`.`rating` AS `rating`, `a`.`votes` AS `votes`,`u`.`name` AS `author`, `a`.`views` AS `views`, `t`.`name` AS `type`
FROM hesk_kb_articles as a
LEFT JOIN hesk_kb_categories as b ON a.catid = b.id
LEFT JOIN hesk_kb_categories as c ON b.parent = c.id
LEFT JOIN hesk_kb_categories as d ON c.parent = d.id
LEFT JOIN hesk_kb_categories as e ON d.parent = e.id
LEFT JOIN hesk_kb_categories as f ON e.parent = f.id
LEFT JOIN hesk_users as u ON a.author = u.id
LEFT JOIN hesk_kb_type as t ON a.type = t.id
WHERE (DATE(`dt`) BETWEEN \'' . $date_from . '\' AND \'' . $date_to . '\') order by id asc';
$result = mysql_query($query);
header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=hesk_kb_articles.csv');
$row = mysql_fetch_assoc($result);
if ($row) {
echocsv(array_keys($row));
}
while ($row) {
echocsv($row);
$row = mysql_fetch_assoc($result);
}
}
?>
