private function sqlPhoneBillBase($sum_mode, $timeperiod, $user_cols)
{
if (!$sum_mode) {
$id = self::XML_REPORT_MONTHLY_BILL;
$rm = new reportManager($this->dbh, $id);
$rm->addColumn('Datum', 'DATE_FORMAT(c.date,\'%b %d. %H:%i:%s\')', 'date');
$rm->addColumn('Wochentag', 'DATE_FORMAT(c.date,\'%W\')', 'time');
$rm->addColumn('Telefonnr.', 'c.phonenumber', 'phonenumber');
$rm->addColumn('Identität', 'uc.identity', 'identity');
//$rm->addColumn('Dauer Schätzung', 'c.estimated_duration', 'estimated_duration');
$rm->addColumn('Dauer', 'CEIL(c.billed_duration/60)', 'billed_duration');
$rm->addColumn('Provider', 'pd.provider_name', 'provider_name');
$rm->addColumn('Tariftyp', 'c.rate_type', 'rate_type');
$rm->addColumn('Tarifcheck', 'IF( ABS( prt.price_per_minute * CEIL( c.billed_duration / 60 ) - c.billed_cost ) = 0, \'OK\', \'FEHLER\')', 'xcheck');
$rm->addColumn('ungebucht', 'IF( ISNULL(u.username) AND ISNULL(c.user), CONCAT(c.billed_cost, \' EUR\'), \'\')', 'unbooked_calls');
$rm->setTitle('Outgoing calls');
} else {
$id = self::XML_REPORT_MONTHLY_BILL_SUM;
$rm = new reportManager($this->dbh, $id);
//$rm->addColumn('invisible_1', 'SUM(c.estimated_duration)', 'sum_estimated_duration');
$rm->addColumn('Dauer', 'CEIL(SUM(c.billed_duration)/60)', 'sum_billed_duration');
$rm->addColumn('Summe Kosten', "CONCAT(FORMAT(SUM(c.billed_cost),4),' EUR')", 'sum_cost');
$rm->addColumn('ungebucht', "CONCAT( FORMAT( SUM( IF( ISNULL(u.username) AND ISNULL(c.user), CONCAT(c.billed_cost, ' EUR'), 0)), 4), ' EUR')", 'unbooked_costs');
$rm->setTitle('Outgoing calls - Sums');
}
if ($user_cols === true) {
//get user list from db
$userlist = $this->getUserList();
//add user specific cols to sql select statement
foreach ($userlist as $user) {
$uid = intval($user['id']);
$usermatch_condition = ' (ISNULL(c.user) AND uc.related_user = '******') OR c.user = '******' ';
if ($sum_mode) {
$rm->addColumn($user['username'], "CONCAT( FORMAT( SUM( IF( " . $usermatch_condition . ", c.billed_cost, 0 )), 4), ' EUR')", "user_col_" . $uid);
} else {
$rm->addColumn($user['username'], "IF( " . $usermatch_condition . ", CONCAT(c.billed_cost, ' EUR'), '')", "user_col_" . $uid);
}
}
} else {
$rm->addColumn('Kosten', "CONCAT(c.billed_cost, ' EUR')", 'billed_cost');
$rm->addColumn('User', 'u.username', 'username');
}
$rm->addSelectFromTable('callprotocol c ' . self::SQL_LEFT_JOIN_PROVIDER_DETAILS_ID . 'c.provider_id ' . 'LEFT JOIN provider_rate_types prt ON (c.provider_id = prt.provider_id AND c.rate_type = prt.rate_type AND c.date >= prt.valid_from AND c.date <= prt.valid_to ) ' . 'LEFT JOIN user_contacts uc ON (c.phonenumber = uc.phonenumber ) ' . 'LEFT JOIN users u ON (uc.related_user = u.id ) ' . ' WHERE c.calltype = 3 ' . $timeperiod);
return $rm;
}
private function addExtJsGrid(reportManager $rm)
{
$stdWidth = 120;
$fieldsMapping = '';
$gridColumns = '';
foreach ($rm->getColumns() as $fieldname => $columndata) {
$columndataTitle = addslashes($columndata['title']);
$fieldname = addslashes($fieldname);
$fieldsMapping .= "\t\t\t\t'{$fieldname}'," . CR;
$gridColumns .= "\t\t\t\t\t" . "{header: '" . $columndataTitle . "', align: 'left', width: " . $stdWidth . ", dataIndex: '" . $fieldname . "', sortable: true}," . CR;
}
$fieldsMapping = substr($fieldsMapping, 0, strlen($fieldsMapping) - 2) . CR;
$gridColumns = substr($gridColumns, 0, strlen($gridColumns) - 2) . CR;
$id = addslashes($rm->getXmlId());
$storeName = 'store_' . $id;
$this->extJsScriptArea .= "\n\t\t\t\n\t\t\t // create the Data Store\n\t\t\t var " . $storeName . " = new Ext.data.Store({\n\t\t\t // load using HTTP\n\t\t\t url: '" . $this->xmlURI($id) . "',\n\t\t\t\n\t\t\t // the return will be XML, so lets set up a reader\n\t\t\t reader: new Ext.data.XmlReader({\n\t\t\t // records will have an \"row\" tag\n\t\t\t record: 'row',\n\t\t\t id: 'id', //todo: find better id!\n\t\t\t totalRecords: '@total'\n\t\t\t }, [" . CR . $fieldsMapping . "\n\t\t\t ])\n\t\t\t });\n\n\t\t\t // create the grid\n\t\t\t var grid = new Ext.grid.GridPanel({\n\t\t\t store: " . $storeName . ",\n\t\t\t columns: [ " . CR . $gridColumns . "\n\t\t ],\n\t\t\t renderTo:'yaphobia-phonebill-grid-" . $id . "',\n\t\t\t width: " . (count($rm->getColumns()) * $stdWidth + 30) . ",\n\t\t\t height: 200,\n\t\t\t title:'" . addslashes($rm->getTitle()) . "',\n\t\t\t frame:true\n\t\t\t });\n\t\t\t " . $storeName . ".load();";
$this->extJsBodyArea .= "\t<div class=\"yaphobia-extjs-grid\" id=\"yaphobia-phonebill-grid-" . $id . "\"></div><br/>" . CR;
}