private function get_results($all = false)
{
$archive = Database_Mongo::collection('discrepancies');
$filters = array();
if (Arr::get($_GET, 'ticket')) {
$tickets = array();
foreach (explode(',', $_GET['ticket']) as $ticket) {
if (preg_match('/^T1W[0-9]{12}$/', $ticket)) {
$tickets[] = $ticket;
} else {
$tickets[] = new MongoRegex('/.*' . $ticket . '.*/i');
}
}
$filters['job_key']['$in'] = $tickets;
}
if (Arr::get($_GET, 'file')) {
$filters['filename'] = $_GET['file'];
}
if (Arr::get($_GET, 'start')) {
$start = strtotime($_GET['start']);
$filters['update_time']['$gt'] = $start;
}
if (isset($filters['end'])) {
$end = strtotime($_GET['end']);
$filters['update_time']['$lt'] = $end;
}
$result = $archive->find($filters)->sort(array('update_time' => -1, 'job_key' => 1));
if (!$all) {
Pager::$count = $result->count();
$result->skip(Pager::offset())->limit(Pager::limit());
}
$list = array();
$ids = array();
foreach ($result as $row) {
$list[] = $row;
$ids[$row['job_key']] = 1;
}
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($ids))));
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = Arr::get($job, 'data');
}
$items = array();
foreach ($list as $row) {
$row['data'] = array_intersect_key($row['data'], Columns::get_visible());
$row['current'] = array();
$row['discr'] = 1;
foreach ($row['data'] as $key => $value) {
$row['current'][$key] = Arr::path($jobs, array($row['job_key'], $key));
if ($row['current'][$key] != $value['old_value']) {
$row['discr'] = 0;
}
}
foreach (Columns::get_static() as $static => $show) {
$row['static'][$static] = Arr::path($jobs, array($row['job_key'], $static));
}
$items[] = $row;
}
return $items;
}
public function action_index()
{
$id = strval(Arr::get($_GET, 'ticket'));
if (!$id) {
throw new HTTP_Exception_404('Not found');
}
$filter = array('_id' => $id);
if (!Group::current('allow_assign')) {
$filter['$or'] = array(array('companies' => intval(User::current('company_id'))), array('ex' => intval(User::current('company_id'))));
}
$job = Database_Mongo::collection('jobs')->findOne($filter);
if (!$job) {
throw new HTTP_Exception_404('Not found');
}
$filter = array('job_key' => $id);
if (!Group::current('allow_assign')) {
$filter['user_id'] = array('$in' => DB::select('id')->from('users')->where('company_id', '=', User::current('company_id'))->execute()->as_array(NULL, 'id'));
}
$result = Database_Mongo::collection('submissions')->find($filter)->sort(array('update_time' => -1));
Pager::$count = $result->count(true);
$result->limit(Pager::limit())->skip(Pager::offset());
$submissions = array();
$users = array();
while ($row = $result->next()) {
$users[$row['user_id']] = 1;
$submissions[] = $row;
}
if ($users) {
User::get(array_keys($users));
}
$companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
$view = View::factory('Jobs/Submissions');
$view->bind('submissions', $submissions)->bind('companies', $companies)->bind('job', $job);
$this->response->body($view);
}
public function action_index()
{
$id = strval(Arr::get($_GET, 'ticket'));
if (!$id) {
throw new HTTP_Exception_404('Not found');
}
Pager::$count = DB::select(DB::expr('COUNT(*) as cnt'))->from('assign_log')->where('job_id', '=', $id)->execute()->get('cnt');
$result = DB::select()->from('assign_log')->where('job_id', '=', $id)->order_by('time', 'desc')->offset(Pager::offset())->limit(Pager::limit())->execute()->as_array();
$users = array();
foreach ($result as $row) {
$users[$row['user_id']] = 1;
}
if ($users) {
User::get(array_keys($users));
}
$companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
$types = DB::select('id', 'name')->from('job_types')->execute()->as_array('id', 'name');
$view = View::factory('Jobs/Assign');
$view->bind('list', $result)->bind('types', $types)->bind('companies', $companies);
$this->response->body($view);
}
<?php
// KioCMS - Kiofol Content Managment System
// modules/news/admin/entries/index.php
if ($kio->stats['news_entries']) {
$pager = new Pager('admin/modules/news', $kio->stats['news_entries']);
$pager->limit()->sort(array(t('ID') => 'n_id', t('Title') => 'n_title', t('Language') => 'lang', t('Content') => 'content', t('Author') => 'nickname', t('Category') => 'c_name', t('Added') => 'added'), 'added', 'desc');
$query = $sql->query('
SELECT u.nickname, u.group_id, c.id c_id, c.name c_name, c.description c_description, n.*, n.id n_id, n.title n_title
FROM ' . DB_PREFIX . 'news n
LEFT JOIN ' . DB_PREFIX . 'users u ON u.id = n.author_id
LEFT JOIN ' . DB_PREFIX . 'news_categories c ON c.id = n.category_id
ORDER BY ' . $pager->order . '
LIMIT ' . $pager->limit . '
OFFSET ' . $pager->offset);
while ($row = $query->fetch()) {
if ($row['author_id']) {
$row['author'] = User::format($row['author_id'], $row['nickname'], $row['group_id']);
}
$row['url_title'] = ($row['c_name'] ? clean_url($row['c_name']) . '/' : null) . clean_url($row['title']);
$entries[] = $row;
}
$tpl = new PHPTAL('modules/news/admin/entries.html');
$tpl->stats = $kio->stats;
$tpl->entries = $entries;
$tpl->sort = $pager->sorters;
$tpl->limit_form = $pager->limit_form;
$tpl->pagination = $pager->links();
echo $tpl->execute();
} else {
echo $lang_admin['NULL'];
/*
if($id==0||!isset($id)){
$app->redirect('/TwigBlog/');
$urlNext=1;
}
elseif($id>=0){
$urlNext=$id+1;
$urlPrev=$id-1;
}
*/
$db = $app->db;
$pager = new Pager($db, $app, $id);
$urlNext = $pager->getUrlNext();
$urlPrev = $pager->getUrlPrev();
// $numOfRows=4; //amount of files per 1 page
$files = $pager->limit(4);
// $offset = $limit['offset'];
// $files = $limit['files'];
//$offset=$id*$numOfRows; //number of row per page
/*
$foo = new Foo();
$foo->limit=$numOfRows;
$foo->offset=$offset;
$mapper = new FooMapper($db);
$files = $mapper->selectAll($foo);
*
*/
/*
function totalFiles($db){
$foo = new Foo();
<?php
// KioCMS - Kiofol Content Managment System
// modules/news/admin/categories.php
$total_categories = current($sql->query('SELECT COUNT(id) AS categories FROM ' . DB_PREFIX . 'news_categories')->fetch());
if ($total_categories) {
$pager = new Pager('admin/modules/news/categories', $total_categories);
$pager->limit();
$pager->sort(array($lang2['ID'] => 'id', $lang2['CATEGORY_NAME'] => 'name', $lang2['DESCRIPTION'] => 'description', $lang2['TOTAL_ENTRIES'] => 'entries'), 'id', 'desc');
$query = $sql->query('
SELECT *
FROM ' . DB_PREFIX . 'news_categories
ORDER BY ' . $pager->order . '
LIMIT ' . $pager->limit . '
OFFSET ' . $pager->offset);
while ($row = $query->fetch()) {
$categories[] = $row;
}
$tpl = new PHPTAL('modules/news/admin/categories.html');
$tpl->system = $system;
$tpl->total_categories = $total_categories;
$tpl->sorters = $pager->sorters;
$tpl->limit_form = $pager->limit_form;
$tpl->categories = $categories;
$tpl->lang_admin = $lang_admin;
$tpl->lang_system = $lang_system;
$tpl->pagination = $pager->paginate();
echo $tpl->execute();
} else {
echo $lang[14];
}
<nav>
<ul class="pagination">
<?php if (Pager::pages() > 1):?>
<li <?=Pager::page() > 1 ? '' : 'class="disabled"'?>>
<a href="<?=URL::query(array('page' => max(1, Pager::page() - 1)))?>" aria-label="Previous"><span aria-hidden="true">«</span></a>
</li>
<?php for ($i = -3; $i<4; $i++) if (Pager::page() + $i > 0 && Pager::page() + $i <= Pager::pages()): $page = Pager::page() + $i;?>
<li <?=$i ? '' : 'class="active"'?>><a href="<?=URL::query(array('page' => $page))?>"><?=$page?></a></li>
<?php endif;?>
<li <?=Pager::page() < Pager::pages() ? '' : 'class="disabled"'?>>
<a href="<?=URL::query(array('page' => min(Pager::pages(), Pager::page() + 1)))?>" aria-label="Next"><span aria-hidden="true">»</span></a>
</li>
<?php endif;?>
<li>
<span>Total <?=Pager::$count?> records</span>
</li>
<li><span>Items on page:</span></li>
<?php foreach (Pager::$counts as $count):?>
<li <?=$count == Pager::limit() ? 'class="disabled"' : ''?>><a href="javascript:;" class="pager-count " data-value="<?=$count?>"><?=$count?></a></li>
<?php endforeach;?>
</ul>
</nav>
<?php
// KioCMS - Kiofol Content Managment System
// modules/guestbook/admin/entries.php
if ($kio->stats['guestbook_entries']) {
$pager = new Pager('admin/modules/guestbook', $kio->stats['guestbook_entries']);
$pager->limit()->sort(array(t('Author') => 'nickname', t('IP') => 'author_ip', t('Message') => 'message', t('Added') => 'added'), 'added', 'desc');
$query = $sql->query('
SELECT gb.id, gb.added, gb.author, gb.author_id, gb.author_ip, gb.email, gb.website, gb.message, u.nickname, u.group_id
FROM ' . DB_PREFIX . 'guestbook AS gb
LEFT JOIN ' . DB_PREFIX . 'users AS u ON u.id = gb.author_id
ORDER BY ' . $pager->order . '
LIMIT ' . $pager->limit . '
OFFSET ' . $pager->offset);
while ($row = $query->fetch()) {
if ($row['author_id']) {
$row['author'] = User::format($row['author_id'], $row['nickname'], $row['group_id']);
}
$entries[] = $row;
}
$tpl = new PHPTAL('modules/guestbook/admin/entries.html');
$tpl->stats = $kio->stats;
$tpl->entries = $entries;
$tpl->sort = $pager->sorters;
$tpl->limit_form = $pager->limit_form;
$tpl->pagination = $pager->links();
echo $tpl->execute();
} else {
echo '<div class="null">' . $lang_admin['NULL'] . '</div>';
}
private function get_results($hidden)
{
$filters = array();
if (Arr::get($_GET, 'ticket')) {
$filters['ticket'] = $_GET['ticket'];
}
if (Arr::get($_GET, 'file')) {
$filters['file'] = $_GET['file'];
}
if (Arr::get($_GET, 'start')) {
$filters['start'] = strtotime($_GET['start']);
}
if (Arr::get($_GET, 'end')) {
$filters['end'] = strtotime($_GET['end']);
}
$count = DB::select(DB::expr("COUNT(id) as cnt"))->from('defects_archive');
$result = DB::select()->from('defects_archive');
if (isset($filters['ticket'])) {
$count->where('Id', '=', $filters['ticket']);
$result->where('Id', '=', $filters['ticket']);
}
if (isset($filters['file'])) {
$count->where('filename', '=', $filters['file']);
$result->where('filename', '=', $filters['file']);
}
if (isset($filters['start'])) {
$count->where('update_time', '>', date('Y-m-d H:i:s', $filters['start']));
$result->where('update_time', '>', date('Y-m-d H:i:s', $filters['start']));
}
if (isset($filters['end'])) {
$count->where('update_time', '<', date('Y-m-d H:i:s', $filters['end']));
$result->where('update_time', '<', date('Y-m-d H:i:s', $filters['end']));
}
if (Arr::get($_GET, 'action')) {
$count->where('update_type', '=', $_GET['action']);
$result->where('update_type', '=', $_GET['action']);
}
$count = $count->execute()->get('cnt');
Pager::$count = $count;
$result->offset(Pager::offset())->limit(Pager::limit())->order_by('Id', 'asc');
$result = $result->execute()->as_array();
$ids = array();
foreach ($result as $id => $ticket) {
if ($ticket['update_type'] == 2) {
$ids[$ticket['Id']] = 1;
}
}
if ($ids) {
$res = DB::select('key', 'Id', 'update_time', 'update_type')->from('defects_archive')->where('Id', 'IN', array_keys($ids))->execute();
$history = array();
while ($row = $res->current()) {
if (isset($history[$row['Id']])) {
$history[$row['Id']][] = $row;
} else {
$history[$row['Id']] = array($row);
}
$res->next();
}
$ids = array();
foreach ($result as $id => $ticket) {
if ($ticket['update_type'] == 2 && isset($history[$ticket['Id']])) {
$max_date = 0;
$max_id = 0;
foreach ($history[$ticket['Id']] as $item) {
if ($item['update_type'] < 3) {
if ($item['update_time'] < $ticket['update_time'] && $item['update_time'] > $max_date) {
$max_date = $item['update_time'];
$max_id = $item['key'];
}
}
}
if ($max_id) {
$ids[$id] = $max_id;
}
}
}
$res = DB::select()->from('defects_archive')->where('key', 'IN', array_values($ids))->execute()->as_array('key');
foreach ($result as $id => $ticket) {
if ($ticket['update_type'] == 2) {
$result[$id]['diff'] = array();
}
}
foreach ($ids as $id => $key) {
if (isset($res[$key])) {
$old = $res[$key];
$diff = array_diff_assoc($old, $result[$id]);
unset($diff['key']);
unset($diff['update_time']);
unset($diff['update_type']);
unset($diff['filename']);
$result[$id]['diff'] = array();
$result[$id]['hidden'] = array();
foreach ($diff as $key => $value) {
if (isset($hidden[$key])) {
$result[$id]['hidden'][$key] = $value;
} else {
$result[$id]['diff'][$key] = $value;
}
}
}
}
}
return $result;
}
public function action_uploads()
{
$id = $this->request->param('id');
Pager::$count = DB::select(DB::expr('COUNT(*) as cnt'))->from('upload_log')->where('job_id', '=', $id)->execute()->get('cnt');
$result = DB::select()->from('upload_log')->where('job_id', '=', $id)->order_by('uploaded', 'desc')->offset(Pager::offset())->limit(Pager::limit())->execute()->as_array();
$users = array();
foreach ($result as $log) {
$users[$log['user_id']] = 1;
}
User::get(array_keys($users));
$companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
$view = View::factory('Jobs/UploadLog')->bind('logs', $result)->bind('companies', $companies);
$this->response->body($view);
}
public function action_index()
{
define('JOB_STATUS_COLUMN', 'data.44');
if (!rand(0, 1000)) {
Database_Mongo::collection('search')->remove(array('expires' => array('$lt' => time())));
}
if ($_POST) {
$data = array('request' => $_POST, 'expires' => time() + 3600);
Database_Mongo::collection('search')->insert($data);
$search_id = strval($data['_id']);
Session::instance()->set('search-settings', $search_id);
header('Content-type: application/json');
die(json_encode(array('success' => true, 'id' => $search_id)));
} elseif (!$_GET && Session::instance()->get('search-settings')) {
$params = Session::instance()->get('search-settings');
if (!is_array($params)) {
$params = json_decode($params);
}
if ($params !== NULL) {
$data = array('request' => $params, 'expires' => time() + 3600);
Database_Mongo::collection('search')->insert($data);
$search_id = strval($data['_id']);
Session::instance()->set('search-settings', $search_id);
} else {
$search_id = Session::instance()->get('search-settings');
}
if ($search_id) {
$this->redirect('search?id=' . $search_id);
}
} elseif (isset($_GET['clear'])) {
Session::instance()->delete('search-settings');
$_GET = array();
} elseif (isset($_GET['id'])) {
$data = Database_Mongo::collection('search')->findOne(array('_id' => new MongoId($_GET['id'])));
if ($data) {
Session::instance()->set('search-settings', $_GET['id']);
$_GET = $data['request'];
} else {
Session::instance()->delete('search-settings');
}
}
$actions = array('contain', 'does not contain', '=', '<', '<=', '>', '>=', '<>', 'empty', 'not empty');
$actions_mongo = array('=' => '$eq', '<' => '$lt', '<=' => '$lte', '>' => '$gt', '>=' => '$gte', '<>' => '$ne');
$columns = Columns::get_visible();
$reports = Columns::get_static();
$regs = DB::select('region_id')->from('user_regions')->where('user_id', '=', User::current('id'))->execute()->as_array('region_id', 'region_id');
$regions = DB::select('id', 'name')->from('regions');
if ($regs) {
$regions->where('id', 'IN', $regs);
}
$regions = $regions->execute()->as_array('id', 'name');
$query = array();
$list_query = array();
if (Arr::get($_GET, 'submissions')) {
$result = Database_Mongo::collection('submissions')->aggregate(array(array('$match' => array('active' => 1)), array('$group' => array('_id' => '$job_key'))));
$result = array_column($result['result'], '_id');
$query['_id'] = array('$in' => $result);
}
if (Group::current('show_all_jobs')) {
if (Arr::get($_GET, 'company')) {
$companies = explode(',', $_GET['company']);
if (count($companies) > 1) {
$companies = array('$in' => array_map("intval", $companies));
} else {
$companies = intval($_GET['company']);
}
if (Arr::get($_GET, 'ex')) {
$list_query['$or'] = $query['$or'] = array(array('companies' => $companies), array('ex' => $companies));
} else {
$list_query['companies'] = $query['companies'] = $companies;
}
}
} else {
if (Arr::get($_GET, 'status', -1) == 0) {
$list_query['ex'] = $query['ex'] = intval(User::current('company_id'));
} else {
$list_query['companies'] = $query['companies'] = intval(User::current('company_id'));
}
}
if (Group::current('allow_assign')) {
foreach (Columns::$settings as $key => $value) {
if (isset($_GET['settings'][$key])) {
if ($_GET['settings'][$key]) {
$query[$key] = array('$exists' => 1);
} else {
$query[$key] = array('$exists' => 0);
}
}
}
}
if (Arr::get($_GET, 'ticket')) {
$tickets = explode(',', $_GET['ticket']);
$q = array();
foreach ($tickets as $ticket) {
$ticket = preg_replace('/[^a-z0-9]/i', '', strval($ticket));
if (!$ticket) {
continue;
}
if (preg_match('/^T1W[0-9]{12}$/', $ticket)) {
$q[] = $ticket;
} else {
$q[] = new MongoRegex('/.*' . $ticket . '.*/i');
}
}
if (count($q) > 1) {
$query['_id'] = array('$in' => $q);
} elseif ($q) {
$query['_id'] = $q[0];
}
}
if (Arr::get($_GET, 'start')) {
$query['last_update']['$gt'] = Columns::parse($_GET['start'], 'date');
}
if (Arr::get($_GET, 'end')) {
$query['last_update']['$lt'] = Columns::parse($_GET['end'], 'date');
}
if (Arr::get($_GET, 'submit-start')) {
$query['last_submit']['$gt'] = Columns::parse($_GET['submit-start'], 'date');
}
if (Arr::get($_GET, 'submit-end')) {
$query['last_submit']['$lt'] = Columns::parse($_GET['submit-end'], 'date');
}
$status = Arr::get($_GET, 'status', Group::current('allow_assign') ? -1 : Enums::STATUS_ALLOC);
$_GET['status'] = intval($status);
if ($status != -1 && (Group::current('show_all_jobs') || $status)) {
$query['status'] = intval($status) ?: array('$exists' => 0);
}
if (!isset($_GET['region'])) {
$_GET['region'] = User::current('default_region');
}
if (Arr::get($_GET, 'region') && (!$regs || isset($regs[$_GET['region']]))) {
$list_query['region'] = $query['region'] = strval($_GET['region']);
} elseif ($regs) {
$list_query['region'] = $query['region'] = array('$in' => array_values($regs));
}
if (Arr::get($_GET, 'type')) {
$query['assigned.' . $_GET['type']] = array('$exists' => 1);
}
foreach (Arr::get($_GET, 'columns', array()) as $id => $column) {
if ($column) {
$op = Arr::get($actions, Arr::path($_GET, 'actions.' . $id), 0);
$value = Arr::path($_GET, 'values.' . $id, '');
$value = Columns::parse($value, Columns::get_type($column));
if (substr(Columns::get_type($column), 0, 4) == 'enum' && Enums::is_multi(substr(Columns::get_type($column), 5)) && $op == '=' && $value) {
$op = 'contain';
}
if (Columns::get_type($column) == 'date') {
if ($op === 'contain') {
$op = '=';
} elseif ($op === 'does not contain') {
$op = '<>';
} elseif ($op == '<=') {
$value += 86399;
}
if ($op == '=' && date('His', $value) === '000000') {
$query['data.' . $column]['$lte'] = $value + 86399;
$op = '>=';
}
}
if ($op === 'contain') {
$op = '$eq';
$value = new MongoRegex('/.*' . preg_replace('/[^a-z0-9,.+:;!? -]/i', '', $value) . '.*/i');
} elseif ($op === 'does not contain') {
$op = '$not';
$value = new MongoRegex('/.*' . preg_replace('/[^a-z0-9,.+:;!? -]/i', '', $value) . '.*/i');
} elseif ($op === 'empty') {
$op = '$in';
$value = array(NULL, '', 0);
} elseif ($op === 'not empty') {
$op = '$nin';
$value = array(NULL, '', 0);
} else {
$op = Arr::get($actions_mongo, $op, '$eq');
}
if ($op == '$eq' && !$value) {
$value = NULL;
}
if (isset($query['data.' . $column]) && $op == '$eq') {
if (isset($query['data.' . $column]['$in'])) {
$query['data.' . $column]['$in'][] = $value;
} elseif (isset($query['data.' . $column]['$eq'])) {
$query['data.' . $column] = array('$in' => array($query['data.' . $column]['$eq'], $value));
} else {
$query['data.' . $column]['$in'] = array($value);
}
} else {
$query['data.' . $column][$op] = $value;
}
}
}
foreach ($query as $key => $ops) {
if (substr($key, 0, 5) == 'data.' && count($ops) == 1 && key($ops) == '$eq') {
$query[$key] = array_shift($ops);
}
}
$jobs = Database_Mongo::collection('jobs');
$list_values = array();
foreach (Columns::get_search() as $key => $value) {
if ($value == 2) {
$list_values[$key] = $jobs->distinct('data.' . $key, $query ?: NULL) ?: array();
if (substr(Columns::get_type($key), 0, 4) == 'enum') {
$list = array();
foreach ($list_values[$key] as $values) {
if ($values) {
$values = explode(', ', $values);
foreach ($values as $value) {
$list[$value] = 1;
}
} else {
$list[''] = 1;
}
}
$list_values[$key] = array_keys($list);
}
if ($list_values[$key]) {
sort($list_values[$key]);
}
}
}
Pager::$count = $jobs->count($query);
$show = array('region' => 1, 'status' => 1, 'created' => 1, 'last_update' => 1, 'last_submit' => 1, 'assigned' => 1, 'companies' => 1, 'ex' => 1);
foreach (Columns::$settings as $key => $value) {
$show[$key] = 1;
}
foreach (Columns::get_search() as $column => $type) {
$show['data.' . $column] = 1;
}
$result = $jobs->find($query, $show);
$sort = Arr::get($_GET, 'sort');
if (is_array($sort)) {
$save = implode('|', $sort);
DB::update('users')->set(array('default_sort' => $save))->where('id', '=', User::current('id'))->execute();
} else {
$sort = explode('|', User::current('default_sort') ?: '-update');
$_GET['sort'] = $sort;
}
$sorting = array();
foreach ($sort as $order) {
$dir = substr($order, 0, 1) == '-' ? -1 : 1;
$order = substr($order, 1);
switch ($order) {
case 'id':
$order = '_id';
break;
case 'update':
$order = 'last_update';
break;
case 'submit':
$order = 'last_submit';
break;
case 'status':
$order = 'status';
break;
case 'data-8':
$sorting['address'] = $dir;
$order = false;
break;
default:
if (substr($order, 0, 5) == 'data-') {
$order = 'data.' . intval(substr($order, 5));
} else {
$order = false;
}
}
if ($order) {
$sorting[$order] = $dir;
}
}
$result->sort($sorting)->limit(Pager::limit())->skip(Pager::offset());
$tickets = array();
while ($row = $result->next()) {
$tickets[] = $row;
}
$ids = array_column($tickets, '_id');
$types = DB::select('id', 'name')->from('job_types')->execute()->as_array('id', 'name');
$companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
if ($ids) {
$attachments = DB::select('job_id', DB::expr('COUNT(*) as cnt'))->from('attachments')->where('job_id', 'IN', $ids)->and_where('uploaded', '>', 0)->and_where('folder', '<>', 'Signatures')->group_by('job_id')->execute()->as_array('job_id', 'cnt');
} else {
$attachments = array();
}
if (Group::current('allow_assign')) {
$result = Database_Mongo::collection('submissions')->aggregate(array(array('$match' => array('job_key' => array('$in' => $ids), 'active' => 1)), array('$group' => array('_id' => '$job_key', 'count' => array('$sum' => 1)))));
$submissions = array();
foreach (Arr::get($result, 'result', array()) as $value) {
$submissions[$value['_id']] = $value['count'];
}
}
$result = Database_Mongo::collection('forms')->find(array(), array('_id' => 1, 'type' => 1, 'name' => 1));
$forms = array();
foreach ($result as $form) {
$forms[$form['type']][strval($form['_id'])] = $form['name'];
}
$view = View::factory('Jobs/Search')->bind('reports', $reports)->bind('regions', $regions)->bind('columns', $columns)->bind('actions', $actions)->bind('tickets', $tickets)->bind('submissions', $submissions)->bind('attachments', $attachments)->bind('list_values', $list_values)->bind('types', $types)->bind('sort', $sort)->bind('companies', $companies)->bind('forms', $forms)->bind('query', $query);
$this->response->body($view);
}
public function action_export()
{
if (!User::current('is_admin')) {
throw new HTTP_Exception_403('Forbidden');
}
$projects = DB::select('id', DB::expr('CONCAT(`client_code`, \' \', name) as name'))->from('projects')->execute()->as_array('id', 'name');
$suppliers = DB::select('id', 'name')->from('suppliers')->execute()->as_array('id', 'name');
$companies = DB::select('id', 'name')->from('companies')->execute()->as_array('id', 'name');
$prefixes = DB::select('id', 'prefix')->from('companies')->execute()->as_array('id', 'prefix');
$items = DB::select('id', 'name')->from('items')->execute()->as_array('id', 'name');
$count = DB::select(DB::expr('COUNT(*) as cnt'))->from('orders');
$list = DB::select('id', 'company_id', 'user_id', 'address_id', 'supplier_id', 'project_id', 'total', 'created', 'last_update', 'instruction', 'delivery', 'final_items')->from('orders')->order_by('last_update', 'DESC');
if (isset($_GET['all'])) {
$list = $list->execute()->as_array();
} else {
Pager::$count = $count->execute()->get('cnt');
$list = $list->offset(Pager::offset())->limit(Pager::limit())->execute()->as_array();
}
$users = array();
$ids = array();
foreach ($list as $item) {
if (!$item['final_items']) {
$ids[$item['id']] = 1;
}
$users[$item['user_id']] = 1;
}
if ($users) {
User::get(array_keys($users));
}
$order_items = array();
if ($ids) {
$result = DB::select('order_id', 'item_id', 'qty')->from('order_items')->where('order_id', 'IN', array_keys($ids))->execute();
foreach ($result as $item) {
$order_items[$item['order_id']][$item['item_id']] = $item['qty'];
}
}
$data = array(array('Order Number', 'Company', 'User', 'Supplier', 'Project', 'Items', 'Total', 'Created', 'Last Update'));
foreach ($list as $order) {
$items_list = array();
if ($order['final_items']) {
$values = json_decode($order['final_items'], true);
foreach ($values as $item) {
$items_list[] = $item['qty'] . ' x ' . $item['name'];
}
} else {
foreach (Arr::get($order_items, $order['id'], array()) as $key => $value) {
$items_list[] = $value . ' x ' . Arr::get($items, $key, 'Unknown');
}
}
$data[] = array(Arr::get($prefixes, $order['company_id'], '') . str_pad($order['id'], 5, '0', STR_PAD_LEFT), Arr::get($companies, $order['company_id'], 0), User::get(Arr::get($order, 'user_id', User::current('id')), 'name') ?: User::get(Arr::get($order, 'user_id', User::current('id')), 'login'), Arr::get($suppliers, $order['supplier_id'], 0), Arr::get($projects, $order['project_id'], 0), implode("\n", $items_list), number_format($order['total'], 2), date('d-m-Y H:i', $order['created']), date('d-m-Y H:i', $order['last_update']));
}
$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();
$sheet->setTitle('Purchase Orders');
$sheet->fromArray($data, NULL, 'A1');
$sheet->getStyleByColumnAndRow(0, 1, 8, 1)->getFont()->setBold(true);
//die();
$sheet->freezePane('A2');
foreach (range('A', $sheet->getHighestDataColumn()) as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
$name = tempnam(sys_get_temp_dir(), 'excel');
header('Content-type: application/xlsx');
header('Content-disposition: filename="Purchase Orders.xlsx"');
$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save($name);
readfile($name);
unlink($name);
die;
}
public function action_index()
{
$tables = DB::select()->from('reports')->execute()->as_array('id', 'name');
$geo = false;
$attachments = false;
if ($_POST) {
$id = intval(Arr::get($_POST, 'id'));
if (!isset($tables[$id])) {
throw new HTTP_Exception_404('Not found');
}
$query = array('report_id' => $id);
if (!Group::current('show_all_jobs')) {
$query['company'] = User::current('company_id');
}
$columns = DB::select('id', 'name', 'type', 'visible')->from('report_columns')->where('report_id', '=', $query['report_id'])->and_where('visible', '<>', 'hidden')->execute()->as_array('id');
foreach ($columns as $column) {
if (isset($_POST[$column['id']])) {
$key = $column['id'];
$type = $column['type'];
if (isset($_POST[$key]['from'])) {
$query[$key]['$gte'] = Columns::parse($_POST[$key]['from'], $type);
}
if (isset($_POST[$key]['to'])) {
$query[$key]['$lte'] = Columns::parse($_POST[$key]['to'], $type);
}
if (isset($_POST[$key]['value'])) {
$values = explode('|', $_POST[$key]['value']);
foreach ($values as $value) {
if ($value) {
$query[$key]['$in'][] = new MongoRegex('/' . $value . '/i');
}
}
}
}
}
$query['expires'] = time() + 3600;
Database_Mongo::collection('search')->insert($query);
header('Content-type: application/json');
die(json_encode(array('success' => true, 'id' => strval($query['_id']))));
} elseif (Arr::get($_GET, 'id')) {
$query = Database_Mongo::collection('search')->findOne(array('_id' => new MongoId($_GET['id'])));
if (!$query) {
$this->redirect('reports/forms');
}
$columns = DB::select('id', 'name', 'type', 'visible')->from('report_columns')->where('report_id', '=', $query['report_id'])->and_where('visible', '<>', 'hidden')->execute()->as_array('id');
unset($query['_id']);
unset($query['expires']);
$result = Database_Mongo::collection('reports')->find($query);
Pager::$count = $result->count();
$result->skip(Pager::offset())->limit(Pager::limit());
$reports = array();
foreach ($result as $report) {
$id = strval($report['_id']);
$data = array('id' => $id, 'attachment' => Arr::get($report, 'attachment', 'Unknown file'), 'attachment_id' => Arr::get($report, 'attachment_id', 0), 'colors' => Arr::get($report, 'colors', array()));
foreach ($columns as $key => $column) {
$data[$key] = Arr::get($report, $key, '');
}
if (isset($report['geo'])) {
$geo = true;
$data['geo'] = $report['geo'];
}
if (Arr::get($report, 'attachments')) {
$attachments = true;
$data['attachments'] = $report['attachments'];
}
$reports[$id] = $data;
}
if (isset($_GET['export'])) {
$header = array('Name');
foreach ($columns as $column) {
$header[] = $column['name'];
}
$data = array($header);
$ids = isset($_GET['ids']) && !isset($_GET['all']) ? array_flip(explode(',', $_GET['ids'])) : array();
foreach ($reports as $report) {
if (!$ids || isset($ids[$report['attachment_id']])) {
$row = array($report['attachment']);
foreach ($columns as $column) {
$row[] = Arr::get($report, $column['id']) ? Columns::output($report[$column['id']], $column['type'], true) : '';
}
$data[] = $row;
}
}
switch ($_GET['export']) {
case 'excel':
$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();
$sheet->setTitle('Search Results');
$sheet->fromArray($data, NULL, 'A1');
foreach (range('A', $sheet->getHighestDataColumn()) as $col) {
$sheet->getColumnDimension($col)->setAutoSize(true);
}
$name = tempnam(sys_get_temp_dir(), 'excel');
header('Content-type: application/xlsx');
header('Content-disposition: filename="' . Arr::get($tables, $query['report_id'], 'Unknown') . '.xlsx"');
$writer = new PHPExcel_Writer_Excel2007($excel);
$writer->save($name);
readfile($name);
unlink($name);
break;
default:
header('Content-type: text/csv');
header('Content-disposition: attachment;filename="' . Arr::get($tables, $query['report_id'], 'Unknown') . '.csv"');
$file = fopen('php://output', 'w');
foreach ($data as $row) {
fputcsv($file, $row);
}
fclose($file);
break;
}
die;
}
} else {
$columns = array();
$reports = array();
}
$view = View::factory('Reports/Forms')->set('geo', $geo)->set('attachments', $attachments)->bind('tables', $tables)->bind('reports', $reports)->bind('filters', $query)->bind('columns', $columns);
$this->response->body($view);
}
" aria-label="Next"><span aria-hidden="true">»</span></a>
</li>
<?php
}
?>
<li>
<span>Total <?php
echo Pager::$count;
?>
records</span>
</li>
<li><span>Items on page:</span></li>
<?php
foreach (Pager::$counts as $count) {
?>
<li <?php
echo $count == Pager::limit() ? 'class="disabled"' : '';
?>
><a href="javascript:;" class="pager-count " data-value="<?php
echo $count;
?>
"><?php
echo $count;
?>
</a></li>
<?php
}
?>
</ul>
</nav>
<div class="clearfix"></div>