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;
}
private function get_results($all = false)
{
$archive = Database_Mongo::collection('archive');
$filters = array();
if (Arr::get($_GET, 'action')) {
if ($_GET['action'] == 2 && !Group::current('is_admin')) {
$show = array_keys(Columns::get_visible());
$filters['fields'] = array('$in' => $show);
}
$filters['update_type'] = intval($_GET['action']);
}
if (Arr::get($_GET, 'ticket')) {
$filters['job_key'] = $_GET['ticket'];
}
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;
}
$filters = $filters ? array(array('$match' => $filters)) : array();
$filters[] = array('$sort' => array('update_time' => -1, 'job_key' => 1));
if (!$all) {
$count = Database_Mongo::collection('archive')->find(Arr::path($filters, '0.$match', array()))->count();
Pager::$count = $count;
$filters[] = array('$skip' => Pager::offset());
$filters[] = array('$limit' => Pager::limit());
}
$result = $archive->aggregateCursor($filters, array('allowDiskUse' => true));
$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());
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()
{
$start = Arr::get($_GET, 'start') ? strtotime($_GET['start']) : strtotime('this week', strtotime('this week') > time() ? strtotime('yesterday') : time());
$end = Arr::get($_GET, 'end') ? strtotime($_GET['end']) + 86399 : time();
$query = array('update_time' => array('$gt' => intval($start), '$lt' => intval($end)));
if (Arr::get($_GET, 'app-start')) {
$query['process_time']['$gt'] = strtotime($_GET['app-start']);
}
if (Arr::get($_GET, 'app-end')) {
$query['process_time']['$lt'] = strtotime($_GET['app-end']) + 86399;
}
if (Group::current('allow_assign')) {
$companies = DB::select('id', 'name')->from('companies')->order_by('name', 'asc')->execute()->as_array('id', 'name');
}
if (!Group::current('allow_assign') || Arr::get($_GET, 'company')) {
$query['user_id'] = array('$in' => DB::select('id')->from('users')->where('company_id', '=', Group::current('allow_assign') ? $_GET['company'] : User::current('company_id'))->execute()->as_array(NULL, 'id'));
}
$jobs = array();
if (Arr::get($_GET, 'finished')) {
$jobs['data.245'] = $_GET['finished'];
}
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) {
$jobs['_id'] = array('$in' => $q);
} elseif ($q) {
$jobs['_id'] = $q[0];
}
}
if (Arr::get($_GET, 'fsa')) {
$values = is_array($_GET['fsa']) ? $_GET['fsa'] : explode(',', $_GET['fsa']);
$jobs['data.12'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'fsam')) {
$values = is_array($_GET['fsam']) ? $_GET['fsam'] : explode(',', $_GET['fsam']);
$jobs['data.13'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'fda')) {
$values = is_array($_GET['fda']) ? $_GET['fda'] : explode(',', $_GET['fda']);
$jobs['data.14'] = count($values) > 1 ? array('$in' => array_values($values)) : current($values);
}
if (Arr::get($_GET, 'address')) {
$jobs['data.8'] = new MongoRegex('/.*' . strval($_GET['address']) . '.*/mi');
}
if ($jobs) {
if (count($jobs) == 1 && isset($jobs['_id'])) {
$query['job_key'] = $jobs['_id'];
} else {
$query['job_key'] = array('$in' => Database_Mongo::collection('jobs')->distinct('_id', $jobs));
}
}
$sort = array('job_key' => 1);
if (!Arr::get($_GET, 'sort')) {
$_GET['sort'] = array('-submission');
}
foreach ($_GET['sort'] as $s) {
$dir = substr($s, 0, 1) == '-' ? -1 : 1;
$s = substr($s, 1);
switch ($s) {
case 'submission':
$sort['update_time'] = $dir;
break;
case 'approval':
$sort['process_time'] = $dir;
break;
}
}
$result = Database_Mongo::collection('submissions')->find($query)->sort($sort);
$submissions = array();
$users = array();
foreach ($result as $submission) {
$submissions[$submission['job_key']][] = $submission;
$users[$submission['user_id']] = 1;
}
if ($users) {
User::get(array_keys($users));
}
if (isset($_GET['export'])) {
header('Content-type: text/csv');
header('Content-disposition: filename="Submissions.' . date('Ymd', $start) . '-' . date('Ymd', $end) . '.' . date('YmdHi', time()) . '.csv"');
$file = tmpfile();
$headers = array('Tickets ID', 'FDA ID', 'LOC ID', 'Address', 'Submission Date', 'Approval Date', 'User');
if (Group::current('allow_assign')) {
$headers[] = 'Company';
}
$headers[] = 'Column';
$headers[] = 'Value';
fputcsv($file, $headers);
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($submissions))), array('data.8' => 1, 'data.9' => 1, 'data.14' => 1));
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = array('a' => Arr::path($job, 'data.8', ''), 'f' => Arr::path($job, 'data.14', ''), 'l' => Arr::path($job, 'data.9', ''));
}
foreach ($submissions as $job => $list) {
foreach ($list as $submission) {
$key = substr($submission['key'], 5);
$data = array($job, Arr::path($jobs, array($job, 'f')), Arr::path($jobs, array($job, 'l')), Arr::path($jobs, array($job, 'a')), date('d-m-Y H:i', $submission['update_time']), Arr::get($submission, 'process_time') ? date('d-m-Y H:i', $submission['process_time']) : '', User::get($submission['user_id'], 'login'));
if (Group::current('allow_assign')) {
$data[] = Arr::get($companies, User::get($submission['user_id'], 'company_id'), 'Unknown');
}
$data[] = Columns::get_name($key);
$data[] = Columns::output($submission['value'], Columns::get_type($key), true);
fputcsv($file, $data);
}
}
fseek($file, 0);
fpassthru($file);
fclose($file);
die;
} elseif (isset($_GET['export2'])) {
//header('Content-type: text/plain');
header('Content-type: text/csv');
header('Content-disposition: filename="Submissions.' . date('Ymd', $start) . '-' . date('Ymd', $end) . '.' . date('YmdHi', time()) . '.csv"');
$result = array();
$columns = array();
foreach ($submissions as $job => $list) {
foreach ($list as $submission) {
$key = substr($submission['key'], 5);
$result[$job][$submission['update_time']][$submission['user_id']][$key] = $submission['value'];
$columns[$key] = Columns::get_name($key);
}
}
$submissions = $result;
ksort($columns);
$headers = array('Tickets ID', 'FDA ID', 'LOC ID', 'Address', 'Submission Date', 'User');
if (Group::current('allow_assign')) {
$headers[] = 'Company';
}
/*$columns = Columns::get_visible();*/
foreach ($columns as $column) {
$headers[] = $column;
}
$columns = array_keys($columns);
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($submissions))), array('data.8' => 1, 'data.9' => 1, 'data.14' => 1));
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = array('f' => Arr::path($job, 'data.14', ''), 'l' => Arr::path($job, 'data.9', ''), 'a' => Arr::path($job, 'data.8', ''));
}
$file = tmpfile();
fputcsv($file, $headers);
foreach ($submissions as $job => $list) {
foreach ($list as $time => $values) {
foreach ($values as $user => $submission) {
$row = array($job, Arr::path($jobs, $job . '.f'), Arr::path($jobs, $job . '.l'), Arr::path($jobs, $job . '.a'), date('d-m-Y H:i', $time), User::get($user, 'login'));
if (Group::current('allow_assign')) {
$row[] = Arr::get($companies, User::get($user, 'company_id'));
}
foreach ($columns as $column) {
$row[] = Columns::output(Arr::get($submission, $column, ''), Columns::get_type($column), true);
}
fputcsv($file, $row);
}
}
}
fseek($file, 0);
fpassthru($file);
fclose($file);
//print_r($result);
die;
} elseif (isset($_GET['export3'])) {
//header('Content-type: text/plain');
header('Content-type: text/csv');
header('Content-disposition: filename="Submissions.' . date('Ymd', $start) . '-' . date('Ymd', $end) . '.' . date('YmdHi', time()) . '.csv"');
$result = array();
foreach ($submissions as $job => $list) {
foreach ($list as $submission) {
$key = substr($submission['key'], 5);
$result[$job][$submission['update_time']][$submission['user_id']][$key] = $submission['value'];
}
}
$submissions = $result;
$headers = array('Tickets ID', 'FDA ID', 'LOC ID', 'Address', 'Submission Date', 'User');
if (Group::current('allow_assign')) {
$headers[] = 'Company';
}
$columns = Columns::get_visible();
foreach ($columns as $column) {
$headers[] = $column;
}
$columns = array_keys($columns);
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($submissions))), array('data.8' => 1, 'data.9' => 1, 'data.14' => 1));
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = array('f' => Arr::path($job, 'data.14', ''), 'l' => Arr::path($job, 'data.9', ''), 'a' => Arr::path($job, 'data.8', ''));
}
$file = tmpfile();
fputcsv($file, $headers);
foreach ($submissions as $job => $list) {
foreach ($list as $time => $values) {
foreach ($values as $user => $submission) {
$row = array($job, Arr::path($jobs, $job . '.f'), Arr::path($jobs, $job . '.l'), Arr::path($jobs, $job . '.a'), date('d-m-Y H:i', $time), User::get($user, 'login'));
if (Group::current('allow_assign')) {
$row[] = Arr::get($companies, User::get($user, 'company_id'));
}
foreach ($columns as $column) {
$row[] = Arr::get($submission, $column) ? Columns::output($submission[$column], Columns::get_type($column), true) : '';
}
fputcsv($file, $row);
}
}
}
fseek($file, 0);
fpassthru($file);
fclose($file);
//print_r($result);
die;
}
$result = Database_Mongo::collection('jobs')->find(array('_id' => array('$in' => array_keys($submissions))), array('data.8' => 1, 'data.9' => 1, 'data.14' => 1));
$jobs = array();
foreach ($result as $job) {
$jobs[$job['_id']] = array('f' => Arr::path($job, 'data.14', ''), 'l' => Arr::path($job, 'data.9', ''), 'a' => Arr::path($job, 'data.8', ''));
}
$view = View::factory("Reports/Submissions")->bind('companies', $companies)->bind('submissions', $submissions)->bind('jobs', $jobs);
$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_index()
{
$id = $this->request->param('id');
$job = Database_Mongo::collection('jobs')->findOne(array('_id' => strval($id)));
if (!$job) {
throw new HTTP_Exception_404('Not found');
}
if (!Group::current('show_all_jobs') && !in_array((int) User::current('company_id'), Arr::get($job, 'companies', array()), true) && !in_array((int) User::current('company_id'), Arr::get($job, 'ex', array()), true)) {
throw new HTTP_Exception_403('Forbidden');
}
if (!Group::current('show_all_jobs') && !in_array((int) User::current('company_id'), Arr::get($job, 'companies', array()), true)) {
Columns::set_readonly();
}
$job['attachments'] = DB::select()->from('attachments')->where('job_id', '=', $id)->and_where('uploaded', '>', '0')->order_by('folder', 'asc')->order_by('numbering', 'asc')->execute()->as_array('id');
if (isset($_GET['delete']) && Group::current('allow_assign')) {
$file_id = intval($_GET['delete']);
if (isset($job['attachments'][$file_id])) {
if (file_exists(DOCROOT . 'storage/' . $file_id)) {
unlink(DOCROOT . 'storage/' . $file_id);
}
$attachment = $job['attachments'][$file_id];
$data = array('filename' => $attachment['folder'] . ' / ' . $attachment['fda_id'] . ' / ' . $attachment['address'] . ' / ' . $attachment['filename'], 'uploaded' => time(), 'user_id' => User::current('id'), 'job_id' => $id, 'action' => 2);
Database::instance()->begin();
DB::insert('upload_log', array_keys($data))->values(array_values($data))->execute();
DB::delete('attachments')->where('id', '=', $file_id)->execute();
Database::instance()->commit();
die(json_encode(array('success' => true)));
} else {
throw new HTTP_Exception_404('Not found');
}
}
$users = array();
$job['region'] = DB::select('name')->from('regions')->where('id', '=', $job['region'])->execute()->get('name');
$tabs = DB::select()->from('job_tabs')->execute()->as_array('id');
foreach ($job['attachments'] as $attachment) {
$users[$attachment['user_id']] = 1;
}
$job_types = DB::select('id', 'name')->from('job_types')->order_by('name', 'asc')->execute()->as_array('id', 'name');
$companies = DB::select('id', 'name')->from('companies')->order_by('name', 'asc')->execute()->as_array('id', 'name');
$submissions = array();
if (Group::current('allow_assign')) {
$result = Database_Mongo::collection('submissions')->find(array('job_key' => $id))->sort(array('update_time' => -1, 'user_id' => 1, 'key' => 1));
} else {
$u = DB::select('id')->from('users')->where('company_id', '=', User::current('company_id'))->execute()->as_array(NULL, 'id');
$result = Database_Mongo::collection('submissions')->find(array('job_key' => $id, 'user_id' => array('$in' => $u)))->sort(array('update_time' => -1, 'user_id' => 1, 'key' => 1));
}
foreach ($result as $submission) {
$users[$submission['user_id']] = 1;
$key = substr($submission['key'], 5);
$data = array('id' => $submission['_id'], 'key' => str_replace('.', '-', $submission['key']), 'user_id' => $submission['user_id'], 'time' => $submission['update_time'], 'name' => Columns::get_name($key), 'type' => Columns::get_type($key), 'active' => Arr::get($submission, 'active'), 'value' => $submission['value']);
if (isset($submission['version'])) {
$data['version'] = $submission['version'];
}
if (Group::current('allow_assign') && Arr::get($submission, 'active') == 1) {
$tabs[Columns::get_tab($key)]['submissions'][$key] = 1;
$submissions[$submission['key']][] = $data;
}
$submissions['list'][(string) $data['id']] = $data;
}
foreach (Columns::get_visible() as $key => $name) {
$tabs[Columns::get_tab($key)]['columns'][$key] = $name;
}
User::get(array_keys($users));
if ($_POST) {
$update_time = time();
if (!Arr::path($_FILES, 'attachment.error', -1) && is_uploaded_file($_FILES['attachment']['tmp_name'])) {
$data = array('filename' => $_FILES['attachment']['name'], 'mime' => $_FILES['attachment']['type'], 'uploaded' => $update_time, 'user_id' => User::current('id'), 'job_id' => $id);
Database::instance()->begin();
$result = DB::insert('attachments', array_keys($data))->values(array_values($data))->execute();
$image_id = Arr::get($result, 0);
if ($image_id && move_uploaded_file($_FILES['attachment']['tmp_name'], DOCROOT . 'storage/' . $image_id)) {
Database::instance()->commit();
Messages::save("File " . $data['filename'] . ' was successfully uploaded!', 'success');
} else {
Database::instance()->rollback();
}
}
if (Group::current('allow_assign')) {
$update = array();
$archive = array();
foreach (Arr::get($_POST, 'data', array()) as $key => $value) {
$value = Columns::parse($value, Columns::get_type($key));
if (Arr::path($job, 'data.' . $key) != $value) {
if ($value) {
$update['$set']['data.' . $key] = $value;
} else {
$update['$unset']['data.' . $key] = 1;
}
$archive['data'][$key] = array('old_value' => Arr::path($job, 'data.' . $key), 'new_value' => $value);
$job['data'][$key] = $value;
}
}
$list = Arr::get($submissions, 'list', array());
$pending = array();
$accepted = array();
$ignored = array();
foreach ($list as $submission) {
$key = 'submission-' . str_replace('-', '.', $submission['key']);
$value = Arr::path($_POST, $key);
if ($value !== NULL) {
$pending[] = $submission['id'];
if ($value) {
$accepted[] = $submission['id'];
} else {
$ignored[] = $submission['id'];
}
}
}
foreach (Arr::get($_POST, 'submission-data', array()) as $key => $value) {
if ($value && isset($list[$value])) {
$value = Arr::path($list, $value . '.value');
if ($key == 177 || $key == 205) {
$value = $value ? 1 : 0;
}
if (Arr::path($job, 'data.' . $key) != $value) {
if ($value) {
$update['$set']['data.' . $key] = $value;
unset($update['$unset']['data.' . $key]);
} else {
$update['$unset']['data.' . $key] = 1;
unset($update['$set']['data.' . $key]);
}
$archive['data'][$key] = array('old_value' => Arr::path($job, 'data.' . $key), 'new_value' => $value);
$job['data'][$key] = $value;
}
}
}
if ($pending) {
Database_Mongo::collection('submissions')->update(array('_id' => array('$in' => $ignored)), array('$set' => array('admin_id' => User::current('id'), 'process_time' => $update_time), '$unset' => array('active' => 1)), array('multiple' => 1));
Database_Mongo::collection('submissions')->update(array('_id' => array('$in' => $accepted)), array('$set' => array('admin_id' => User::current('id'), 'process_time' => $update_time, 'active' => -1)), array('multiple' => 1));
}
$ass = false;
$values = array();
foreach (Arr::get($_POST, 'assigned', array()) as $key => $value) {
if (Arr::path($job, 'assigned.' . $key) != $value) {
if ($ass < 1) {
$ass = $value ? 1 : -1;
}
$values[] = array('time' => $update_time, 'user_id' => User::current('id'), 'company_id' => max($value, 0), 'job_id' => $job['_id'], 'type' => $key);
$unassigned = Arr::path($job, 'assigned.' . $key);
if ($value) {
$job['assigned'][$key] = $value;
$users = DB::select('id')->from('users')->where('company_id', '=', $value)->execute()->as_array(NULL, 'id');
$update['$set']['data.266'] = $update_time;
$message = '1 ticket was allocated on ' . date('d-m-Y H:i', $update_time) . '. <a href="' . URL::base() . 'search/view/' . $job['_id'] . '">View ticket</a>';
$insert = DB::insert('notifications', array('user_id', 'message'));
foreach ($users as $user) {
$insert->values(array($user, $message));
}
$insert->execute();
} else {
unset($job['assigned'][$key]);
}
if ($unassigned) {
$users = DB::select('id')->from('users')->where('company_id', '=', $unassigned)->execute()->as_array(NULL, 'id');
$subs = Database_Mongo::collection('submissions')->findOne(array('job_key' => $job['_id'], 'active' => 1));
if ($subs) {
Messages::save('Unable to unassign the job - please, approve or reject all submissions first!', 'danger');
} else {
$subs = Database_Mongo::collection('submissions')->find(array('job_key' => $job['_id'], 'user_id' => array('$in' => $users), 'financial' => array('$exists' => 0), 'active' => -1))->sort(array('process_time' => 1));
$list = array();
foreach ($subs as $sub) {
$list[$sub['key']] = $sub['_id'];
}
$financial = DB::select('id')->from('job_columns')->where('financial', '>', 0)->execute()->as_array(NULL, 'id');
$result = array();
foreach ($financial as $column) {
if (isset($list['data.' . $column])) {
$result[$column] = $list['data.' . $column];
}
}
Database_Mongo::collection('submissions')->update(array('_id' => array('$in' => array_values($result))), array('$set' => array('financial_time' => 0)), array('multiple' => 1));
}
}
}
}
if ($ass) {
$update['$set']['assigned'] = $job['assigned'];
$insert = DB::insert('assign_log', array_keys($values[0]));
foreach ($values as $value) {
$insert->values($value);
}
$insert->execute();
$result = Database_Mongo::collection('submissions')->distinct('user_id', array('job_key' => $job['_id']));
if ($result) {
$result = DB::select('company_id')->distinct(true)->from('users')->where('id', 'IN', $result)->and_where('company_id', '>', 0)->execute()->as_array(NULL, 'company_id');
}
$companies = array_flip($result);
foreach ($companies as $key => $value) {
$companies[$key] = 1;
}
$update['$set']['ex'] = array_keys($companies);
}
if (!Arr::get($update, '$unset')) {
unset($update['$unset']);
}
$companies = array();
if (isset($job['assigned'])) {
foreach ($job['assigned'] as $company) {
if ($company) {
$companies[$company] = 1;
}
}
}
foreach (Columns::$settings as $key => $value) {
if (!in_array($key, Columns::$settings_read_only, true)) {
if (Arr::get($job, $key) != Arr::get($_POST, $key)) {
$value = Arr::get($_POST, $key) ? 1 : 0;
$update[$value ? '$set' : '$unset'][$key] = 1;
$job[$key] = $value;
}
}
}
$discrepancies = Database_Mongo::collection('discrepancies')->find(array('job_key' => $job['_id']))->sort(array('update_time' => -1))->getNext();
if (Group::current('allow_reports') && $discrepancies) {
$fl = 0;
$set = array();
$ignores = Arr::get($_POST, 'ignore-discrepancy');
foreach ($discrepancies['data'] as $key => $values) {
$val = Arr::get($values, 'ignore') ? 1 : 0;
$new_val = Arr::get($ignores, $key) ? 1 : 0;
if ($val != $new_val) {
if ($new_val) {
if (!$fl) {
$fl = -1;
}
$set['$set']['data.' . $key . '.ignore'] = 1;
$discrepancies['data'][$key]['ignore'] = 1;
} else {
if ($values['old_value'] != Arr::get($job['data'], $key, '')) {
$fl = 1;
}
$set['$unset']['data.' . $key . '.ignore'] = 1;
$discrepancies['data'][$key]['ignore'] = 0;
}
}
}
if ($set) {
Database_Mongo::collection('discrepancies')->update(array('_id' => new MongoId($discrepancies['_id'])), $set);
}
if ($fl > 0 && !Arr::get($job, 'discrepancies')) {
$update['$set']['discrepancies'] = 1;
} elseif ($fl <= 0 && Arr::get($job, 'discrepancies')) {
$fl = true;
foreach ($discrepancies['data'] as $key => $values) {
if (!Arr::get($values, 'ignore')) {
$value = $values['old_value'];
$new_value = Arr::get($job['data'], $key, '');
if ($key == 44) {
$value = preg_replace('/[^a-z]/i', '', strtolower($value));
$new_value = preg_replace('/[^a-z]/i', '', strtolower($new_value));
}
if ($value != $new_value) {
$fl = false;
}
}
}
if ($fl) {
$update['$unset']['discrepancies'] = 1;
}
}
}
if ($update) {
Utils::calculate_financial($job);
$status = preg_replace('/[^a-z]/', '', strtolower(Arr::path($update, array('$set', 'data.44'), '')));
if ($status == 'built' && !Arr::path($job, 'data.264')) {
$update['$set']['data.264'] = $update_time;
}
if ($status == 'tested' && !Arr::path($job, 'data.265')) {
$update['$set']['data.265'] = $update_time;
if (!Arr::path($job, 'data.264')) {
$update['$set']['data.264'] = $update_time;
}
}
$update['$set']['companies'] = array_keys($companies);
$status = Arr::get($job, 'status', Enums::STATUS_UNALLOC);
if ($companies && $status == Enums::STATUS_UNALLOC) {
$update['$set']['status'] = Enums::STATUS_ALLOC;
} elseif (!$companies && $status == Enums::STATUS_ALLOC) {
$update['$unset']['status'] = 1;
}
$update['$set']['last_update'] = $update_time;
if (isset($update['$set']['data.8'])) {
$update['$set']['address'] = MapQuest::parse($update['$set']['data.8']);
} elseif (isset($update['$unset']['data.8'])) {
$update['$unset']['address'] = 1;
}
Database_Mongo::collection('jobs')->update(array('_id' => $id), $update);
if ($archive) {
foreach (Columns::get_static() as $key => $value) {
$archive['static'][$key] = Arr::path($job, 'data.' . $key);
}
$archive['fields'] = array_keys($archive['data']);
$archive['job_key'] = $id;
$archive['user_id'] = User::current('id');
$archive['update_time'] = $update_time;
$archive['update_type'] = 2;
$archive['filename'] = 'MANUAL';
Database_Mongo::collection('archive')->insert($archive);
}
Messages::save("Changes were succesfully saved!", 'success');
} else {
Messages::save("No changes were submitted", 'warning');
}
if ($pending) {
$count = Database_Mongo::collection('submissions')->find(array('job_key' => $id, 'active' => 1))->count();
$status = Arr::get($job, 'status', Enums::STATUS_UNALLOC);
if ($status == Enums::STATUS_PENDING && !$count) {
$update = array('$set' => array('last_update' => $update_time, 'status' => Enums::STATUS_COMPLETE));
Database_Mongo::collection('jobs')->update(array('_id' => $id), $update);
}
} elseif (in_array(Arr::get($job, 'status', Enums::STATUS_UNALLOC), array(Enums::STATUS_UNALLOC, Enums::STATUS_ALLOC))) {
if ($companies) {
$status = Enums::STATUS_ALLOC;
} else {
$status = Enums::STATUS_UNALLOC;
}
if ($status != Arr::get($job, 'status', Enums::STATUS_UNALLOC)) {
$update = array('$set' => array('last_update' => $update_time));
if ($status == Enums::STATUS_UNALLOC) {
$update['$unset']['status'] = 1;
} else {
$update['$set']['status'] = $status;
}
Database_Mongo::collection('jobs')->update(array('_id' => $id), $update);
}
} elseif ($ass == 1 && in_array(Arr::get($job, 'status', Enums::STATUS_UNALLOC), array(Enums::STATUS_COMPLETE, Enums::STATUS_ARCHIVE))) {
$update = array('$set' => array('last_update' => $update_time, 'status' => Enums::STATUS_ALLOC));
Database_Mongo::collection('jobs')->update(array('_id' => $id), $update);
}
if ($submissions) {
Messages::save(count($pending) . '/' . count($submissions['list']) . ' submission(s) were processed.', 'info');
}
} else {
$submissions = array();
foreach (Arr::get($_POST, 'data', array()) as $key => $value) {
$value = Columns::parse($value, Columns::get_type($key));
if (Arr::path($job, 'data.' . $key) != $value) {
$submissions[$key] = $value;
}
}
if ($submissions) {
$submission = array('job_key' => $id, 'user_id' => User::current('id'), 'active' => 1, 'update_time' => $update_time);
if (Arr::get($_POST, 'location')) {
$submission['location'] = $_POST['location'];
}
$status = Arr::get($job, 'status', Enums::STATUS_UNALLOC);
$update = array();
$approval = false;
$archive = array();
foreach ($submissions as $key => $value) {
if (Columns::get_direct($key)) {
if ($value) {
$update['$set']['data.' . $key] = $value;
} else {
$update['$unset']['data.' . $key] = 1;
}
$archive['data'][$key] = array('old_value' => Arr::path($job, 'data.' . $key), 'new_value' => $value);
$job['data'][$key] = $value;
$submission['key'] = 'data.' . $key;
$submission['value'] = $value;
$submission['active'] = -1;
$submission['process_time'] = $submission['update_time'];
Database_Mongo::collection('submissions')->insert($submission);
unset($submission['process_time']);
unset($submission['_id']);
} else {
$approval = true;
if ($status != Enums::STATUS_PENDING) {
$status = $update['$set']['status'] = Enums::STATUS_PENDING;
}
$submission['key'] = 'data.' . $key;
$submission['value'] = $value;
$submission['active'] = 1;
Database_Mongo::collection('submissions')->insert($submission);
unset($submission['_id']);
}
}
if ($update) {
$update['$set']['last_update'] = $update_time;
Database_Mongo::collection('jobs')->update(array('_id' => $id), $update);
if ($archive) {
foreach (Columns::get_static() as $key => $value) {
$archive['static'][$key] = Arr::path($job, 'data.' . $key);
}
$archive['fields'] = array_keys($archive['data']);
$archive['job_key'] = $id;
$archive['user_id'] = User::current('id');
$archive['update_time'] = $update_time;
$archive['update_type'] = 2;
$archive['filename'] = 'MANUAL';
Database_Mongo::collection('archive')->insert($archive);
}
}
Messages::save("Changes were succesfully submitted. " . ($approval ? 'Manager will review changes and confirm them.' : ''), 'success');
} else {
Messages::save("No changes were submitted", 'warning');
}
}
$this->redirect('/search');
}
$values = array();
if (!Group::current('allow_assign')) {
$last = Database_Mongo::collection('submissions')->find(array('job_key' => $id, 'active' => 1, 'user_id' => array('$in' => DB::select('id')->from('users')->where('company_id', '=', User::current('company_id'))->execute()->as_array(NULL, 'id'))))->sort(array('update_time' => 1));
foreach ($last as $submission) {
$values[str_replace('.', '', $submission['key'])] = $submission['value'];
}
}
$job['discr'] = array();
if (Group::current('allow_reports')) {
$job['discr'] = Database_Mongo::collection('discrepancies')->find(array('job_key' => $id))->sort(array('update_time' => -1))->getNext();
}
if (Group::current('time_machine')) {
$result = Database_Mongo::collection('archive')->find(array('job_key' => $job['_id']))->sort(array('update_time' => -1));
$archive = array();
$ids = array();
foreach ($result as $item) {
$archive[] = $item;
$ids[Arr::get($item, 'user_id', 0)] = 1;
}
User::get(array_keys($ids));
}
if (Group::current('allow_finance')) {
$query = DB::select('company_id', 'payment_time', 'admin_id', array('payments.amount', 'total'), array('payment_jobs.amount', 'amount'))->from('payment_jobs')->join('payments')->on('payment_jobs.payment_id', '=', 'payments.id')->where('job_key', '=', $job['_id'])->order_by('payment_time', 'desc');
if (!Group::current('show_all_jobs')) {
$query->and_where('company_id', '=', User::current('company_id'));
}
$job['payments'] = $query->execute()->as_array();
$ids = array();
foreach ($job['payments'] as $payment) {
$ids[$payment['admin_id']] = 1;
}
if ($ids) {
User::get(array_keys($ids));
}
}
$forms = array();
if (Group::current('allow_custom_forms')) {
$forms = array('job' => $job['_id']);
if (!Group::current('show_all_jobs')) {
$forms['company'] = User::current('company_id');
}
$result = Database_Mongo::collection('forms-data')->find($forms, array('data' => 0))->sort(array('last_update' => -1));
$forms = array();
$ids = array();
foreach ($result as $form) {
$ids[$form['form_id']] = 1;
$forms[] = $form;
}
$list = array();
foreach ($ids as $key => $dummy) {
$list[] = new MongoId($key);
}
$result = Database_Mongo::collection('forms')->find(array('_id' => array('$in' => $list)), array('name' => 1));
$list = array();
foreach ($result as $form) {
$list[strval($form['_id'])] = $form['name'];
}
foreach ($forms as $key => $form) {
if (isset($list[$form['form_id']])) {
$forms[$key]['name'] = $list[$form['form_id']];
}
}
}
$view = View::factory('Jobs/View')->bind('job', $job)->bind('tabs', $tabs)->bind('job_types', $job_types)->bind('companies', $companies)->bind('submissions', $submissions)->bind('values', $values)->bind('archive', $archive)->bind('forms', $forms);
$this->response->body($view);
}
