function sort_ajax_alert($a, $b)
{
if (isset($a['time']) && isset($b['time'])) {
return $a['time'] > $b['time'];
}
return strtotime(input_date($a['date'])) > strtotime(input_date($b['date']));
}
function input_datetime($field, $value)
{
$seg = $field . '_seg';
$min = $field . '_min';
$hour = $field . '_hour';
$sel_seg = substr($value, 17, 2) ? substr($value, 17, 2) : date('s');
$sel_min = substr($value, 14, 2) ? substr($value, 14, 2) : date('i');
$sel_hour = substr($value, 11, 2) ? substr($value, 11, 2) : date('h');
$ret = input_date($field, $value) . ' @ ';
$ret .= select_range($hour, $sel_hour, 0, 23) . ':';
$ret .= select_range($min, $sel_min, 0, 59, 5) . ':';
$ret .= select_range($seg, $sel_seg, 0, 59, 5);
return $ret;
}
/** Specifies the content for the caller interface view. */
public function caller_interface($user_id)
{
if (!correct_user($user_id)) {
return;
}
$user = $this->userModel->get_user_by_id($user_id);
$experiments = $this->callerModel->get_experiments_by_caller($user_id);
$nr_experiments = count($experiments);
// Count total number of participants (and especially for longitudinal experiments)
$longitudinal = array();
$nr_participants = 0;
foreach ($experiments as $e) {
$n = count($this->participantModel->find_participants($e));
$nr_participants += $n;
$prereqs = $this->relationModel->get_relation_ids_by_experiment($e->id, RelationType::Prerequisite, TRUE);
if ($prereqs && $n > 0) {
$longitudinal[$e->name] = $n;
}
}
// Check if there are participants that need to be called back today
$callback_count = $this->participationModel->count_to_be_called_back(input_date());
$callback_msg = '';
if ($callback_count) {
$callback_msg = '<p class="warning">' . sprintf(lang('call_back_warn'), $callback_count) . '</p>';
}
// Count testinvites that need to be reminded manually
$testinvite_count = $this->testInviteModel->count_to_be_reminded_testinvites();
$testinvite_url = array('url' => 'testinvite/index/1', 'title' => sprintf(lang('testinvite_action'), $testinvite_count));
create_experiment_table();
$data['ajax_source'] = 'experiment/table/0/' . $user_id;
$data['page_title'] = sprintf(lang('welcome'), $user->username);
$data['page_info'] = sprintf(lang('info_caller'), $nr_experiments, $nr_participants) . $callback_msg . $this->construct_longitudinal_message($longitudinal);
$data['action_urls'] = array($testinvite_url);
$this->load->view('templates/header', $data);
$this->authenticate->authenticate_redirect('templates/list_view', $data, UserRole::Caller);
$this->load->view('templates/footer');
}
function process()
{
if ('save_note' == $_REQUEST['_process']) {
$note_id = $_REQUEST['note_id'];
$options = unserialize(base64_decode($_REQUEST['options']));
if (!$options) {
return;
}
if (!$note_id || $note_id == 'new') {
$note_data = array('note_id' => $note_id, 'owner_id' => $options['owner_id'], 'owner_table' => $options['owner_table'], 'note_time' => strtotime(input_date(urldecode($_REQUEST['note_time']), true)), 'note' => urldecode($_REQUEST['note']), 'rel_data' => isset($_REQUEST['rel_data']) ? $_REQUEST['rel_data'] : '', 'reminder' => isset($_REQUEST['reminder']) ? $_REQUEST['reminder'] : 0, 'user_id' => isset($_REQUEST['user_id']) ? $_REQUEST['user_id'] : 0);
} else {
// some fields we dont want to overwrite on existing notes:
$note_data = array('note_id' => $note_id, 'note_time' => strtotime(input_date(urldecode($_REQUEST['note_time']), true)), 'note' => urldecode($_REQUEST['note']), 'reminder' => isset($_REQUEST['reminder']) ? $_REQUEST['reminder'] : 0, 'user_id' => isset($_REQUEST['user_id']) ? $_REQUEST['user_id'] : 0);
}
if (isset($_REQUEST['public_chk']) && $_REQUEST['public_chk']) {
$note_data['public'] = isset($_REQUEST['public']) ? $_REQUEST['public'] : 0;
}
// TODO - sanatise this note data with security module.
// make sure we're saving a note we have access too.
//module_security::sanatise_data('note',$note_data);
// sanatise broke our update code.
$note_id = update_insert('note_id', $note_id, 'note', $note_data);
if (isset($_REQUEST['from_normal'])) {
set_message('Note saved successfully');
redirect_browser($this->link_open($note_id, false, $options));
}
echo $this->print_note($note_id, false, isset($options['display_summary']) && $options['display_summary'], false, false, $options);
exit;
}
}
public static function save_invoice($invoice_id, $data)
{
if (!(int) $invoice_id && isset($data['job_id']) && $data['job_id']) {
$linkedjob = module_job::get_job($data['job_id']);
$data['currency_id'] = $linkedjob['currency_id'];
$data['customer_id'] = $linkedjob['customer_id'];
}
if ($invoice_id) {
// used when working out the hourly rate fix below
$original_invoice_data = self::get_invoice($invoice_id);
} else {
$original_invoice_data = 0;
}
$invoice_id = update_insert("invoice_id", $invoice_id, "invoice", $data);
if ($invoice_id) {
module_cache::clear('invoice');
// save the invoice tax rates (copied to finance.php)
if (isset($data['tax_ids']) && isset($data['tax_names']) && $data['tax_percents']) {
$existing_taxes = get_multiple('invoice_tax', array('invoice_id' => $invoice_id), 'invoice_tax_id', 'exact', 'order');
$order = 1;
foreach ($data['tax_ids'] as $key => $val) {
if (isset($data['tax_percents'][$key]) && $data['tax_percents'][$key] == 0) {
// we are not saving this particular tax item because it has a 0% tax rate
} else {
if ((int) $val > 0 && isset($existing_taxes[$val])) {
// this means we are trying to update an existing record on the invoice_tax table, we confirm this id matches this invoice.
$invoice_tax_id = $val;
unset($existing_taxes[$invoice_tax_id]);
// so we know which ones to remove from the end.
} else {
$invoice_tax_id = false;
// create new record
}
$invoice_tax_data = array('invoice_id' => $invoice_id, 'percent' => isset($data['tax_percents'][$key]) ? $data['tax_percents'][$key] : 0, 'amount' => 0, 'name' => isset($data['tax_names'][$key]) ? $data['tax_names'][$key] : 'TAX', 'order' => $order++, 'increment' => isset($data['tax_increment_checkbox']) && $data['tax_increment_checkbox'] ? 1 : 0);
$invoice_tax_id = update_insert('invoice_tax_id', $invoice_tax_id, 'invoice_tax', $invoice_tax_data);
}
}
foreach ($existing_taxes as $existing_tax) {
delete_from_db('invoice_tax', array('invoice_id', 'invoice_tax_id'), array($invoice_id, $existing_tax['invoice_tax_id']));
}
}
$invoice_data = self::get_invoice($invoice_id);
if (!$invoice_data) {
set_error('No permissions to access invoice.');
return $invoice_id;
}
// check for new invoice_items or changed invoice_items.
$invoice_items = self::get_invoice_items($invoice_id, $invoice_data);
if (isset($data['invoice_invoice_item']) && is_array($data['invoice_invoice_item'])) {
foreach ($data['invoice_invoice_item'] as $invoice_item_id => $invoice_item_data) {
$invoice_item_id = (int) $invoice_item_id;
if (!is_array($invoice_item_data)) {
continue;
}
if ($invoice_item_id > 0 && !isset($invoice_items[$invoice_item_id])) {
continue;
}
// wrong invoice_item save - will never happen.
if (!isset($invoice_item_data['description']) || $invoice_item_data['description'] == '') {
if ($invoice_item_id > 0) {
// remove invoice_item.
$sql = "DELETE FROM `" . _DB_PREFIX . "invoice_item` WHERE invoice_item_id = '{$invoice_item_id}' AND invoice_id = {$invoice_id} LIMIT 1";
query($sql);
}
continue;
}
// add / save this invoice_item.
$invoice_item_data['invoice_id'] = $invoice_id;
// what type of task is this?
$invoice_task_type = isset($invoice_item_data['manual_task_type']) && $invoice_item_data['manual_task_type'] >= 0 ? $invoice_item_data['manual_task_type'] : $invoice_data['default_task_type'];
$invoice_item_data['hours_mins'] = 0;
if (isset($invoice_item_data['hours']) && $invoice_task_type == _TASK_TYPE_HOURS_AMOUNT) {
}
if (isset($invoice_item_data['hours']) && $invoice_task_type == _TASK_TYPE_HOURS_AMOUNT && function_exists('decimal_time_in')) {
$invoice_item_data['hours'] = decimal_time_in($invoice_item_data['hours']);
if (strpos($invoice_item_data['hours'], ':') !== false) {
$invoice_item_data['hours_mins'] = str_replace(":", ".", $invoice_item_data['hours']);
}
} else {
if (isset($invoice_item_data['hours']) && strlen($invoice_item_data['hours'])) {
$invoice_item_data['hours'] = number_in($invoice_item_data['hours']);
} else {
$invoice_item_data['hours'] = 0;
}
}
// number formatting
//print_r($invoice_item_data);
if (isset($invoice_item_data['hourly_rate']) && strlen($invoice_item_data['hourly_rate'])) {
$invoice_item_data['hourly_rate'] = number_in($invoice_item_data['hourly_rate'], module_config::c('task_amount_decimal_places', -1));
}
//print_r($invoice_item_data);exit;
// somenew hacks here to support out new method of creating an item.
// the 'amount' column is never edited any more
// this column is now always automatically calculated based on
// 'hours' and 'hourly_rate'
if (!isset($invoice_item_data['amount'])) {
if ($invoice_task_type == _TASK_TYPE_AMOUNT_ONLY) {
// ignore the quantity field all together.
$invoice_item_data['amount'] = $invoice_item_data['hourly_rate'];
$invoice_item_data['hourly_rate'] = 0;
} else {
if (isset($invoice_item_data['hourly_rate']) && strlen($invoice_item_data['hourly_rate']) > 0) {
// if we have inputted an hourly rate (ie: not left empty)
if (isset($invoice_item_data['hours']) && strlen($invoice_item_data['hours']) == 0) {
// no hours entered (eg: empty) so we treat whatever was in 'hourly_rate' as the amount
$invoice_item_data['amount'] = $invoice_item_data['hourly_rate'];
} else {
if (isset($invoice_item_data['hours']) && strlen($invoice_item_data['hours']) > 0) {
// hours inputted, along with hourly rate. work out the new amount.
$invoice_item_data['amount'] = round($invoice_item_data['hours'] * $invoice_item_data['hourly_rate'], module_config::c('currency_decimal_places', 2));
}
}
}
}
}
if ($invoice_task_type == _TASK_TYPE_HOURS_AMOUNT) {
if ($invoice_item_data['hourly_rate'] == $invoice_data['hourly_rate'] || isset($original_invoice_data['hourly_rate']) && $invoice_item_data['hourly_rate'] == $original_invoice_data['hourly_rate']) {
$invoice_item_data['hourly_rate'] = -1;
}
}
// remove the amount of it equals the hourly rate.
/*if(isset($invoice_item_data['amount']) && isset($invoice_item_data['hours']) && $invoice_item_data['amount'] > 0 && $invoice_item_data['hours'] > 0){
if($invoice_item_data['amount'] - ($invoice_item_data['hours'] * $data['hourly_rate']) == 0){
unset($invoice_item_data['amount']);
}
}*/
// check if we haven't unticked a non-hourly invoice_item
/*if(isset($invoice_item_data['completed_t']) && $invoice_item_data['completed_t'] && !isset($invoice_item_data['completed'])){
$invoice_item_data['completed'] = 0;
}*/
if (!isset($invoice_item_data['taxable_t'])) {
$invoice_item_data['taxable'] = module_config::c('task_taxable_default', 1);
} else {
if (isset($invoice_item_data['taxable_t']) && $invoice_item_data['taxable_t'] && !isset($invoice_item_data['taxable'])) {
$invoice_item_data['taxable'] = 0;
}
}
if (!strlen($invoice_item_data['hours'])) {
$invoice_item_data['hours'] = 0;
}
$invoice_item_data['hourly_rate'] = number_out($invoice_item_data['hourly_rate'], false, module_config::c('task_amount_decimal_places', -1));
$invoice_item_data['hours'] = number_out($invoice_item_data['hours']);
$invoice_item_data['amount'] = number_out($invoice_item_data['amount']);
update_insert('invoice_item_id', $invoice_item_id, 'invoice_item', $invoice_item_data);
}
}
$last_payment_time = 0;
if (isset($data['invoice_invoice_payment']) && is_array($data['invoice_invoice_payment'])) {
foreach ($data['invoice_invoice_payment'] as $invoice_payment_id => $invoice_payment_data) {
$invoice_payment_id = (int) $invoice_payment_id;
if (!is_array($invoice_payment_data)) {
continue;
}
if (isset($invoice_payment_data['amount'])) {
$invoice_payment_data['amount'] = number_in($invoice_payment_data['amount']);
// toggle between 'normal' and 'refund' payment types
if (isset($invoice_payment_data['payment_type'])) {
if ($invoice_payment_data['amount'] < 0 && $invoice_payment_data['payment_type'] == _INVOICE_PAYMENT_TYPE_NORMAL) {
// this is a refund.
$invoice_payment_data['payment_type'] = _INVOICE_PAYMENT_TYPE_REFUND;
} else {
if ($invoice_payment_data['payment_type'] == _INVOICE_PAYMENT_TYPE_REFUND) {
$invoice_payment_data['payment_type'] = _INVOICE_PAYMENT_TYPE_NORMAL;
}
}
}
}
// check this invoice payment actually matches this invoice.
$invoice_payment_data_existing = false;
if ($invoice_payment_id > 0) {
$invoice_payment_data_existing = get_single('invoice_payment', array('invoice_payment_id', 'invoice_id'), array($invoice_payment_id, $invoice_id));
if (!$invoice_payment_data_existing || $invoice_payment_data_existing['invoice_payment_id'] != $invoice_payment_id || $invoice_payment_data_existing['invoice_id'] != $invoice_id) {
$invoice_payment_id = 0;
$invoice_payment_data_existing = false;
}
}
if (!isset($invoice_payment_data['amount']) || $invoice_payment_data['amount'] == '' || $invoice_payment_data['amount'] == 0) {
// || $invoice_payment_data['amount'] <= 0
if ($invoice_payment_id > 0) {
// if this is a customer credit payment, return that back to the customer account.
if ($invoice_payment_data_existing && $invoice_data['customer_id']) {
switch ($invoice_payment_data_existing['payment_type']) {
case _INVOICE_PAYMENT_TYPE_CREDIT:
module_customer::add_credit($invoice_data['customer_id'], $invoice_payment_data_existing['amount'], 'Refunded credit from invoice payment');
break;
}
}
// remove invoice_payment.
$sql = "DELETE FROM `" . _DB_PREFIX . "invoice_payment` WHERE invoice_payment_id = '{$invoice_payment_id}' AND invoice_id = {$invoice_id} LIMIT 1";
query($sql);
// delete any existing transactions from the system as well.
hook_handle_callback('invoice_payment_deleted', $invoice_payment_id, $invoice_id);
}
continue;
}
if (!$invoice_payment_id && (!isset($_REQUEST['add_payment']) || $_REQUEST['add_payment'] != 'go')) {
continue;
// not saving a new one.
}
// add / save this invoice_payment.
$invoice_payment_data['invoice_id'] = $invoice_id;
// $invoice_payment_data['currency_id'] = $invoice_data['currency_id'];
$last_payment_time = max($last_payment_time, strtotime(input_date($invoice_payment_data['date_paid'])));
if (isset($invoice_payment_data['custom_notes'])) {
$details = @unserialize($invoice_payment_data['data']);
if (!is_array($details)) {
$details = array();
}
$details['custom_notes'] = $invoice_payment_data['custom_notes'];
$invoice_payment_data['data'] = serialize($details);
}
$invoice_payment_data['amount'] = number_out($invoice_payment_data['amount']);
update_insert('invoice_payment_id', $invoice_payment_id, 'invoice_payment', $invoice_payment_data);
}
}
if (!$last_payment_time) {
$last_payment_time = strtotime(date('Y-m-d'));
}
// check if the invoice has been paid
module_cache::clear('invoice');
//module_cache::clear_cache(); // this helps fix the bug where part payments are not caulcated a correct paid date.
$invoice_data = self::get_invoice($invoice_id);
if (!$invoice_data) {
set_error('No permissions to access invoice.');
return $invoice_id;
}
if ((!$invoice_data['date_paid'] || $invoice_data['date_paid'] == '0000-00-00') && $invoice_data['total_amount_due'] <= 0 && ($invoice_data['total_amount_paid'] > 0 || $invoice_data['discount_amount'] > 0) && (!$invoice_data['date_cancel'] || $invoice_data['date_cancel'] == '0000-00-00')) {
// find the date of the last payment history.
// if the sent date is null also update that.
$date_sent = $invoice_data['date_sent'];
if (!$date_sent || $date_sent == '0000-00-00') {
$date_sent = date('Y-m-d', $last_payment_time);
}
update_insert("invoice_id", $invoice_id, "invoice", array('date_paid' => date('Y-m-d', $last_payment_time), 'date_sent' => $date_sent, 'status' => _l('Paid')));
// hook for our ticketing plugin to mark a priority support ticket as paid.
// or anything else down the track.
module_cache::clear('invoice');
handle_hook('invoice_paid', $invoice_id);
if (module_config::c('invoice_automatic_receipt', 1)) {
// send receipt to customer.
self::email_invoice_to_customer($invoice_id);
}
}
if ($invoice_data['total_amount_due'] > 0) {
// update the status to unpaid.
update_insert("invoice_id", $invoice_id, "invoice", array('date_paid' => '', 'status' => $invoice_data['status'] == _l('Paid') ? module_config::s('invoice_status_default', 'New') : $invoice_data['status']));
}
if (class_exists('module_extra', false) && module_extra::is_plugin_enabled()) {
module_extra::save_extras('invoice', 'invoice_id', $invoice_id);
}
if ($invoice_data['customer_id']) {
//module_cache::clear_cache();
module_cache::clear('invoice');
module_customer::update_customer_status($invoice_data['customer_id']);
}
hook_handle_callback('invoice_saved', $invoice_id, $invoice_data);
}
module_cache::clear('invoice');
module_cache::clear('job');
return $invoice_id;
}
}
uasort($upcoming_finances, 'sort_recurring_finance');
// we have to search in PHP because our filters return results from all over the place
if (isset($search) && is_array($search)) {
foreach ($upcoming_finances as $recurring_id => $recurring) {
if ($recurring['next_due_date'] && $recurring['next_due_date'] != '0000-00-00') {
$recurring_date = strtotime($recurring['next_due_date']);
if (isset($search['date_from']) && strlen($search['date_from'])) {
$search_from = strtotime(input_date($search['date_from']));
if ($recurring_date < $search_from) {
unset($upcoming_finances[$recurring_id]);
continue;
}
}
if (isset($search['date_to']) && strlen($search['date_to'])) {
$search_to = strtotime(input_date($search['date_to']));
if ($recurring_date > $search_to) {
unset($upcoming_finances[$recurring_id]);
continue;
}
}
}
if (isset($search['generic']) && strlen($search['generic']) > 0) {
$name = strip_tags(isset($recurring['url']) && $recurring['url'] ? $recurring['url'] : module_finance::link_open_recurring($recurring['finance_recurring_id'], true, $recurring));
if (stripos($name, $search['generic']) === false) {
unset($upcoming_finances[$recurring_id]);
continue;
}
}
if (isset($search['amount_from']) && strlen($search['amount_from'])) {
$amount = number_in($search['amount_from']);
/** Returns all future impediments for a participant */
public function get_future_impediments_by_participant($participant_id)
{
$this->db->where('participant_id', $participant_id);
$this->db->where('to >=', input_date());
return $this->db->get('impediment')->result();
}
public function replace_content()
{
$content = $this->content;
$this->add_tags($this->template_key, $this->values);
// add todays date values
if (!isset($this->values['day'])) {
$this->values['day'] = date('d');
}
if (!isset($this->values['month'])) {
$this->values['month'] = date('m');
}
if (!isset($this->values['year'])) {
$this->values['year'] = date('y');
}
if (!isset($this->values['current_user']) && module_security::is_logged_in()) {
$this->values['current_user'] = module_security::get_loggedin_id();
$user_details = module_user::get_replace_fields(module_security::get_loggedin_id());
foreach ($user_details as $key => $val) {
$this->values['current_' . $key . ''] = $val;
}
}
// basic conditional tags - eg: {if:name}Dear {name},{else:name}Hello,{endif:name}
// todo - support nested if - pass to template module.
$debug = false;
if (preg_match_all('#\\{if:([^\\}=]+)([^\\}]*)\\}(.*){endif:\\1\\}.*#imsU', $content, $matches)) {
foreach ($matches[1] as $key => $template_tag) {
// does this first if have a conditional statement (eg: {if:STATUS=New}sdfasdf{else}asdfasdf{/if:STATUS}
$bits = preg_split('#{else[^\\}]*}#', $matches[3][$key]);
preg_match_all('#\\{(else)?(if:([^\\}=]+)([^\\}]*))?\\}#ims', $matches[0][$key], $elseif_matches);
$new_content = false;
if ($debug) {
echo $matches[0][$key] . "\n";
}
if ($debug) {
print_r($bits);
}
if ($debug) {
print_r($elseif_matches);
}
foreach ($elseif_matches[0] as $elseif_key => $elseif_condition) {
$template_tag_value = isset($this->values[strtolower($elseif_matches[3][$elseif_key])]) ? trim($this->values[strtolower($elseif_matches[3][$elseif_key])]) : '';
if (!strlen(trim($elseif_matches[3][$elseif_key]))) {
// we are at the final {else} statement, this means all other checks before have failed. we use this content.
if ($debug) {
echo "Reached final {else} statement, using this value\n";
}
$new_content = $bits[$elseif_key];
break;
} else {
if ($debug) {
echo "Checking {$elseif_condition} against " . $elseif_matches[3][$elseif_key] . " which has the value: " . $template_tag_value . "\n";
}
if (strlen($elseif_matches[4][$elseif_key]) && $elseif_matches[4][$elseif_key][0] == '=') {
// the if/elseif tag is checking a condition, rather than just an "does exist" check
$check_matching_value = ltrim(trim($elseif_matches[4][$elseif_key]), '=');
if ($debug) {
echo "Checking if it matches {$check_matching_value} \n";
}
if ($check_matching_value == $template_tag_value) {
if ($debug) {
echo "YES WE HAVE A MATCH \n";
}
// this first if statement matches this query! yes!
// use its value in the final $new_content
$new_content = $bits[$elseif_key];
break;
} else {
if ($debug) {
echo "No match this time \n";
}
}
} else {
// we're just checking if this value exists or not.
if (strlen($template_tag_value) > 0 && $template_tag_value != '0000-00-00' && $template_tag_value != _l('N/A')) {
// it's a match!
$new_content = $bits[$elseif_key];
break;
} else {
// no match, move onto next bit.
}
}
}
}
if ($debug) {
echo "Final content to use will be: \n" . $new_content;
}
$content = str_replace($matches[0][$key], $new_content, $content);
}
}
foreach ($this->values as $key => $val) {
if (is_array($val)) {
continue;
}
// if this isn't a html field we add newlines.
if (!preg_match('#<[^>]+>#', $val)) {
// raw text. nl2br
$val = nl2br($val);
}
$content = str_replace('{' . strtoupper($key) . '}', $val, $content);
// we perform some basic arithmetic on some replace fields.
if (preg_match_all('#\\{(currency:)?' . preg_quote(strtoupper($key), '#') . '([*+-])([\\d\\.]+)\\}#', $content, $matches)) {
// pull the "number" portion out of this string for math processing.
// string could look like this: "$150.10 USD"
$mathval = $originalval = $val;
if (preg_match('#([\\d.,]+)#', $val, $mathvalmatches)) {
$mathval = $originalval = $mathvalmatches[1];
}
foreach ($matches[0] as $i => $v) {
$mathval = $originalval;
if ($matches[2][$i] == '-') {
$mathval = $mathval - $matches[3][$i];
} else {
if ($matches[2][$i] == '+') {
$mathval = $mathval + $matches[3][$i];
} else {
if ($matches[2][$i] == '*') {
$mathval = $mathval * $matches[3][$i];
}
}
}
if (strtolower($matches[1][$i]) == 'currency:') {
$mathval = dollar($mathval, true, isset($this->values['currency_id']) ? $this->values['currency_id'] : false);
}
$newval = str_replace($originalval, $mathval, $val);
$content = str_replace($v, $newval, $content);
}
}
if (preg_match_all('#\\{currency:(' . preg_quote(strtoupper($key), '#') . ')\\}#', $content, $matches)) {
foreach ($matches[0] as $i => $v) {
$content = str_replace($v, dollar($val, true, isset($this->values['currency_id']) ? $this->values['currency_id'] : false), $content);
}
}
// we perform some arithmetic on date fields.
$matches = false;
if (stripos($key, 'date') !== false && $val && strlen($val) > 6 && preg_match_all('#' . preg_quote('{' . strtoupper($key), '#') . '((?>[+-]\\d+[ymd])*)\\}#', $content, $matches)) {
//$processed_date = (input_date($val)); $processed_date_timeo =
$processed_date_time = strtotime(input_date($val));
foreach ($matches[0] as $i => $v) {
if (preg_match_all('#([+-])(\\d+)([ymd])#', $matches[1][$i], $date_math)) {
foreach ($date_math[1] as $di => $dv) {
$period = $date_math[3][$di];
$period = $period == 'd' ? 'day' : ($period == 'm' ? 'month' : ($period == 'y' ? 'year' : 'days'));
//echo $dv.$date_math[2][$di]." ".$period."\n";
$processed_date_time = strtotime($dv . $date_math[2][$di] . " " . $period, $processed_date_time);
}
$content = str_replace($v, print_date($processed_date_time), $content);
//echo "Processing date: $val - $processed_date (time: $processed_date_timeo / ".print_date($processed_date_timeo).") with result of: ".print_date($processed_date_time); exit;
}
}
}
// we perform some date splitting
$matches = false;
if (stripos($key, 'date') !== false && $val && strlen($val) > 6 && preg_match_all('#' . preg_quote('{' . strtoupper($key), '#') . '-([ymdYMDjlSWFn])\\}#', $content, $matches)) {
$processed_date_time = strtotime(input_date($val));
foreach ($matches[0] as $i => $v) {
$content = str_replace($v, date($matches[1][$i], $processed_date_time), $content);
}
}
//$val = str_replace(array('\\', '$'), array('\\\\', '\$'), $val);
//$content = preg_replace('/\{'.strtoupper(preg_quote($key,'/')).'\}/',$val,$content);
}
if (preg_match_all('#\\{l:([^\\}]+)\\}#', $content, $matches)) {
foreach ($matches[1] as $key => $label) {
$content = str_replace($matches[0][$key], _l($label), $content);
}
}
return $content;
}
public function process()
{
if ("save_facebook" == $_REQUEST['_process']) {
$social_facebook_id = isset($_REQUEST['social_facebook_id']) ? (int) $_REQUEST['social_facebook_id'] : 0;
$facebook = new ucm_facebook_account($social_facebook_id);
if (isset($_POST['butt_del']) && module_social::can_i('delete', 'Facebook', 'Social', 'social')) {
if (module_form::confirm_delete('social_facebook_id', "Really delete this Facebook account from the system? All messages will be lost.", self::link_open($_REQUEST['social_facebook_id']))) {
$facebook->delete();
set_message("Facebook account deleted successfully");
redirect_browser(self::link_open(false));
}
}
$facebook->save_data($_POST);
$social_facebook_id = $facebook->get('social_facebook_id');
if (isset($_POST['butt_save_connect'])) {
$redirect = $this->link_open($social_facebook_id, false, false, 'facebook_account_connect');
} else {
set_message('Facebook account saved successfully');
$redirect = $this->link_open($social_facebook_id);
}
redirect_browser($redirect);
exit;
} else {
if ("send_facebook_message" == $_REQUEST['_process']) {
if (module_form::check_secure_key()) {
$social_facebook_id = isset($_REQUEST['social_facebook_id']) ? (int) $_REQUEST['social_facebook_id'] : 0;
$facebook = new ucm_facebook_account($social_facebook_id);
if ($social_facebook_id && $facebook->get('social_facebook_id') == $social_facebook_id) {
// queue the message into the facebook_message table
// if there's a scheduled date in the past we send it in the past, no date we send straight away, date in the future we leave it in the db table for the cron job to pick up.
//print_r($_POST);exit;
$send_time = false;
// default: now
if (isset($_POST['schedule_date']) && isset($_POST['schedule_time']) && !empty($_POST['schedule_date']) && !empty($_POST['schedule_time'])) {
$date = $_POST['schedule_date'];
$time_hack = $_POST['schedule_time'];
$time_hack = str_ireplace('am', '', $time_hack);
$time_hack = str_ireplace('pm', '', $time_hack);
$bits = explode(':', $time_hack);
if (strpos($_POST['schedule_time'], 'pm')) {
$bits[0] += 12;
}
// add the time if it exists
$date .= ' ' . implode(':', $bits) . ':00';
$send_time = strtotime(input_date($date, true));
} else {
if (isset($_POST['schedule_date']) && !empty($_POST['schedule_date'])) {
$send_time = strtotime(input_date($_POST['schedule_date'], true));
}
}
//echo print_date($send_time,true);
//echo '<br>';
//echo date('c',$send_time);
//exit;
/* @var $available_pages ucm_facebook_page[] */
$available_pages = $facebook->get('pages');
$send_pages = isset($_POST['compose_page_id']) && is_array($_POST['compose_page_id']) ? $_POST['compose_page_id'] : array();
$page_count = 0;
if ($send_pages) {
foreach ($send_pages as $facebook_page_id => $tf) {
if (!$tf) {
continue;
}
// see if this is an available page.
if (isset($available_pages[$facebook_page_id])) {
// push to db! then send.
$facebook_message = new ucm_facebook_message($facebook, $available_pages[$facebook_page_id], false);
$facebook_message->create_new();
$facebook_message->update('social_facebook_page_id', $available_pages[$facebook_page_id]->get('social_facebook_page_id'));
$facebook_message->update('social_facebook_id', $facebook->get('social_facebook_id'));
$facebook_message->update('summary', isset($_POST['message']) ? $_POST['message'] : '');
$facebook_message->update('type', 'pending');
$facebook_message->update('link', isset($_POST['link']) ? $_POST['link'] : '');
$facebook_message->update('data', json_encode($_POST));
$facebook_message->update('user_id', module_security::get_loggedin_id());
// do we send this one now? or schedule it later.
$facebook_message->update('status', _SOCIAL_MESSAGE_STATUS_PENDINGSEND);
if ($send_time) {
// schedule for sending at a different time (now or in the past)
$facebook_message->update('last_active', $send_time);
} else {
// send it now.
$facebook_message->update('last_active', 0);
}
if (isset($_FILES['picture']['tmp_name']) && is_uploaded_file($_FILES['picture']['tmp_name'])) {
$facebook_message->add_attachment($_FILES['picture']['tmp_name']);
}
$facebook_message->send_queued(isset($_POST['debug']));
$page_count++;
} else {
// log error?
}
}
}
set_message(_l('Message delivered successfully to %s Facebook pages', $page_count));
$redirect = $this->link_open_message_view($social_facebook_id);
redirect_browser($redirect);
}
}
} else {
if ("ajax_facebook_url_info" == $_REQUEST['_process']) {
header('Content-type: text/javascript');
$url = isset($_REQUEST['url']) ? $_REQUEST['url'] : false;
if (strlen($url) > 4 && preg_match('#https?://#', $url)) {
// pass this into graph api debugger to get some information back about the URL
$facebook = new ucm_facebook();
$data = $facebook->get_url_info($url);
// return the data formatted in json ready to be added into the relevant input boxes.
$data['link_picture'] = isset($data['image'][0]['url']) ? $data['image'][0]['url'] : '';
$data['link_name'] = isset($data['title']) ? $data['title'] : '';
$data['link_caption'] = isset($data['caption']) ? $data['caption'] : '';
$data['link_description'] = isset($data['description']) ? $data['description'] : '';
echo json_encode($data);
}
exit;
} else {
if ("ajax_social_facebook" == $_REQUEST['_process']) {
// ajax functions from wdsocial. copied from the datafeed.php sample files.
header('Content-type: text/javascript');
if (module_form::check_secure_key()) {
// todo: check user has access to this message.
$social_facebook_id = isset($_REQUEST['social_facebook_id']) ? (int) $_REQUEST['social_facebook_id'] : 0;
//$facebook = new ucm_facebook_account($social_facebook_id);
//if($social_facebook_id && $facebook->get('social_facebook_id') == $social_facebook_id){
$action = isset($_REQUEST['action']) ? $_REQUEST['action'] : false;
$message_id = isset($_REQUEST['id']) ? (int) $_REQUEST['id'] : 0;
$facebook_message = new ucm_facebook_message();
$facebook_message->load($message_id);
//if($facebook_message->get('social_facebook_id') == $social_facebook_id){
switch ($action) {
case "send-message-reply":
if (module_social::can_i('create', 'Facebook Comments', 'Social', 'social')) {
$return = array();
$message = isset($_POST['message']) && $_POST['message'] ? $_POST['message'] : '';
$facebook_id = isset($_POST['facebook_id']) && $_POST['facebook_id'] ? $_POST['facebook_id'] : false;
$debug = isset($_POST['debug']) && $_POST['debug'] ? $_POST['debug'] : false;
if ($message) {
if ($debug) {
ob_start();
}
$facebook_message->send_reply($facebook_id, $message, $debug);
if ($debug) {
$return['message'] = ob_get_clean();
} else {
// todo - option to ask the user if they want to archive a message during the send.
set_message(_l('Message sent and conversation archived.'));
if ($social_facebook_id) {
$return['redirect'] = module_social_facebook::link_open_message_view($social_facebook_id);
} else {
// return to the 'combined' view:
}
}
}
echo json_encode($return);
}
break;
case "set-answered":
if (module_social::can_i('edit', 'Facebook Comments', 'Social', 'social')) {
$facebook_message->update('status', _SOCIAL_MESSAGE_STATUS_ANSWERED);
?>
$('.facebook_message_row[data-id=<?php
echo $message_id;
?>
]').hide();
<?php
}
break;
case "set-unanswered":
if (module_social::can_i('edit', 'Facebook Comments', 'Social', 'social')) {
$facebook_message->update('status', _SOCIAL_MESSAGE_STATUS_UNANSWERED);
?>
$('.facebook_message_row[data-id=<?php
echo $message_id;
?>
]').hide();
<?php
}
break;
}
//echo 'The status is '.$facebook_message->get('status');
//}
}
// }
exit;
}
}
}
}
}
if (input_date($data_field_value) != input_date($data['date_created'])) {
unset($datas[$data_id]);
continue;
}
break;
case 'created_time':
echo 'Searching by time not supported yet.';
break;
case 'updated_date_time':
if (input_date($data_field_value, true) != input_date($data['date_updated'], true)) {
unset($datas[$data_id]);
continue;
}
break;
case 'updated_date':
if (input_date($data_field_value) != input_date($data['date_updated'])) {
unset($datas[$data_id]);
continue;
}
break;
case 'updated_time':
echo 'Searching by time not supported yet.';
break;
case 'created_by':
if ($data_field_value != $data['create_user_id']) {
unset($datas[$data_id]);
continue;
}
break;
case 'updated_by':
if ($data_field_value != $data['update_user_id']) {
// remove cancelled invoices
foreach ($invoices as $invoice_id => $invoice) {
if ($invoice['date_cancel'] != '0000-00-00') {
unset($invoices[$invoice_id]);
}
}
if (count($invoices)) {
$fieldset_data = array('heading' => array('type' => 'h3', 'title' => _l('Invoices Paid Between %s and %s', print_date($date_from), print_date($date_to))));
$fieldset_data['elements_before'] = customer_admin_email_generate_invoice_list($invoices, $customer_id);
$email_details .= module_form::generate_fieldset($fieldset_data);
}
}
if (isset($_REQUEST['email']['invoice_unpaid'])) {
// find all unpaid invoices
$date_from = input_date($_REQUEST['email']['invoice_paid_date_from']);
$date_to = input_date($_REQUEST['email']['invoice_paid_date_to']);
$invoices = module_invoice::get_invoices(array('customer_id' => $customer['customer_id'], 'date_paid' => '0000-00-00'));
// remove cancelled invoices
foreach ($invoices as $invoice_id => $invoice) {
if ($invoice['date_cancel'] != '0000-00-00') {
unset($invoices[$invoice_id]);
}
}
if (count($invoices)) {
$fieldset_data = array('heading' => array('type' => 'h3', 'title' => _l('Unpaid Invoices')));
$fieldset_data['elements_before'] = customer_admin_email_generate_invoice_list($invoices, $customer_id);
$email_details .= module_form::generate_fieldset($fieldset_data);
}
}
}
$template->assign_values(array('email_details' => $email_details));
/** Posts the data for an experiment */
private function post_experiment()
{
$date_start = $this->input->post('date_start');
$date_end = $this->input->post('date_end');
$exp = array('location_id' => $this->input->post('location'), 'name' => $this->input->post('name'), 'type' => $this->input->post('type'), 'description' => $this->input->post('description'), 'duration' => $this->input->post('duration'), 'wbs_number' => $this->input->post('wbs_number'), 'experiment_color' => $this->input->post('experiment_color'), 'date_start' => $date_start ? input_date($date_start) : NULL, 'date_end' => $date_end ? input_date($date_end) : NULL, 'dyslexic' => $this->input->post('dyslexic') === '1', 'multilingual' => $this->input->post('multilingual') === '1', 'agefrommonths' => $this->input->post('agefrommonths'), 'agefromdays' => $this->input->post('agefromdays'), 'agetomonths' => $this->input->post('agetomonths'), 'agetodays' => $this->input->post('agetodays'), 'target_nr_participants' => $this->input->post('target_nr_participants'));
if ($this->attachment) {
$exp['attachment'] = $this->attachment;
}
if ($this->informedconsent) {
$exp['informedconsent'] = $this->informedconsent;
}
return $exp;
}
/**
* Copyright: dtbaker 2012
* Licence: Please check CodeCanyon.net for licence details.
* More licence clarification available here: http://codecanyon.net/wiki/support/legal-terms/licensing-terms/
* Deploy: 9809 f200f46c2a19bb98d112f2d32a8de0c4
* Envato: 4ffca17e-861e-4921-86c3-8931978c40ca
* Package Date: 2015-11-25 02:55:20
* IP Address: 67.79.165.254
*/
// this is shown in a lightbox, editing a particular payment by payment id
$type = $_REQUEST['w'];
$date = explode('|', $_REQUEST['date']);
$start_date = input_date($date[0]);
if (isset($date[1])) {
$end_date = input_date($date[1]);
$end_date_str = ' to ' . print_date($end_date);
} else {
$end_date = $start_date;
$end_date_str = '';
}
switch ($type) {
case 'amount_spent':
// pass this off to it's own file because it's getting a bit messy in here.
include 'dashboard_popup_amount_spent.php';
return false;
case 'amount_paid':
// pass this off to it's own file because it's getting a bit messy in here.
include 'dashboard_popup_amount_paid.php';
return false;
// find all payments made this week.
function get_multiple($table, $search = false, $id = false, $search_type = "exact", $order = false)
{
$sql = "SELECT *";
if ($id) {
$sql .= ",`{$id}` AS id";
}
$sql .= " FROM `" . _DB_PREFIX . "{$table}`";
$fields = get_fields($table, array(), array(), true);
// we force the system id searching if it exists.
if (isset($fields['system_id']) && defined('_SYSTEM_ID')) {
$search['system_id'] = _SYSTEM_ID;
}
if (is_array($search)) {
$sql .= " WHERE 1";
foreach ($search as $key => $val) {
$this_search_type = $search_type;
$spesh = false;
if (trim($val) == '' || $val === false) {
continue;
}
// switch types if searching on numbers..
// this allows easy fuzzy and exact matches
// when we have forms that allow user input and drop down id input.
if (isset($fields[$key]) && $fields[$key]['type'] == 'number') {
$this_search_type = 'exact';
}
if (isset($fields[$key]) && $fields[$key]['type'] == 'date') {
// we need to format the user input to the database friendly date
$val = input_date($val);
}
// check the operator type
$operator = "=";
switch ($key[0]) {
case "<":
$operator = "<=";
$spesh = true;
$key = substr($key, 1);
break;
case ">":
$operator = ">=";
$spesh = true;
$key = substr($key, 1);
break;
}
$foo = explode("|", $key);
$sql .= " AND (";
foreach ($foo as $k) {
if (!isset($fields[$k])) {
continue;
}
if ($spesh) {
$sql .= " `{$k}` {$operator} '" . mysql_real_escape_string($val) . "'";
} else {
if ($this_search_type == "fuzzy") {
$sql .= " `{$k}` LIKE '%" . mysql_real_escape_string($val) . "%'";
} else {
if ($this_search_type == "exact") {
$sql .= " `{$k}` = '" . mysql_real_escape_string($val) . "'";
}
}
}
$sql .= " OR ";
}
$sql = rtrim($sql, " OR ");
$sql .= ") ";
$sql = str_replace(' AND () ', '', $sql);
// incase any of them have incorrect fields.
}
}
if ($order) {
if (strpos($order, ' ') === false && strpos($order, '`') === false) {
$order = '`' . $order . '`';
}
$sql .= " ORDER BY " . mysql_real_escape_string($order) . "";
}
$result = qa($sql);
//module_security::filter_data_set($table,$result);
return $result;
}
public function table($include_past = FALSE)
{
$id = $this->session->userdata('user_id');
$this->datatables->select('from, comment, id');
$this->datatables->from('availability');
$this->datatables->where("`user_id` = '" . $this->session->userdata('user_id') . "'");
if (!$include_past) {
$this->db->where('to >=', input_date());
}
$this->datatables->edit_column('from', '$1', 'availability_dates_by_id(id)');
$this->datatables->edit_column('id', '$1', 'availability_actions(id)');
$this->datatables->unset_column('user_id');
echo $this->datatables->generate();
}
public static function get_finance_recurring_items($hook, $search)
{
/**
* next_due_date
* url
* type (i or e)
* amount
* currency_id
* days
* months
* years
* last_transaction_finance_id
* account_name
* categories
* finance_recurring_id
*/
// find list of all members.
// then go through and fine list of all upcoming subscription payments.
// add these ones (and future ones up to (int)module_config::c('finance_recurring_months',6) months from todays date.
$end_date = isset($search['date_to']) && !empty($search['date_to']) ? strtotime(input_date($search['date_to'])) : strtotime("+" . (int) module_config::c('finance_recurring_months', 6) . ' months');
/*$sql = "SELECT s.*, sm.*";
$sql .= " FROM `"._DB_PREFIX."subscription_member` sm ";
$sql .= " LEFT JOIN `"._DB_PREFIX."subscription` s USING (subscription_id)";
$sql .= " WHERE sm.`deleted` = 0";
$members = qa($sql);
$sql = "SELECT s.*, sc.*";
$sql .= " FROM `"._DB_PREFIX."subscription_customer` sc ";
$sql .= " LEFT JOIN `"._DB_PREFIX."subscription` s USING (subscription_id)";
$sql .= " WHERE sc.`deleted` = 0";
$customers = qa($sql);
$items = array_merge($members,$customers);*/
//$members = module_member::ge
$sql = "SELECT s.*, so.*";
$sql .= " FROM `" . _DB_PREFIX . "subscription_owner` so ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "subscription` s USING (subscription_id)";
$sql .= " WHERE so.`deleted` = 0";
$sql .= " GROUP BY `owner_table`, `owner_id`";
$items = qa($sql);
//$members = module_member::get_members(array());
$return = array();
foreach ($items as $member) {
$subscriptions = module_subscription::get_subscriptions_by($member['owner_table'], $member['owner_id']);
/*if(isset($member['member_id']) && $member['member_id']){
}else if(isset($member['customer_id']) && $member['customer_id']){
$subscriptions = module_subscription::get_subscriptions_by_customer($member['customer_id']);
}else{
$subscriptions = array();
}*/
foreach ($subscriptions as $subscription) {
$time = strtotime($subscription['next_generation_date'] ? $subscription['next_generation_date'] : $subscription['next_due_date']);
if (!$time) {
continue;
}
switch ($member['owner_table']) {
case 'customer':
$type = 'customer';
$member_name = module_customer::link_open($member['owner_id'], true);
$subscription_invoices = self::get_subscription_history($subscription['subscription_id'], $member['owner_table'], $member['owner_id']);
break;
case 'website':
$type = 'website';
$member_name = module_website::link_open($member['owner_id'], true);
$subscription_invoices = self::get_subscription_history($subscription['subscription_id'], $member['owner_table'], $member['owner_id']);
break;
case 'member':
$type = 'member';
$member_name = module_member::link_open($member['owner_id'], true);
$subscription_invoices = self::get_subscription_history($subscription['subscription_id'], $member['owner_table'], $member['owner_id']);
break;
default:
$subscription_invoices = array();
$member_name = 'unknown2';
$type = 'unknown2';
}
$subscription_name = module_subscription::link_open($subscription['subscription_id'], true);
foreach ($subscription_invoices as $subscription_invoice_id => $subscription_invoice) {
if ($subscription_invoice['invoice_id']) {
$subscription_invoices[$subscription_invoice_id] = array_merge($subscription_invoice, module_invoice::get_invoice($subscription_invoice['invoice_id'], 2));
}
}
$original = true;
$c = 0;
while ($time < $end_date) {
if ($c++ > 200) {
break;
}
$next_time = 0;
if (!$subscription['days'] && !$subscription['months'] && !$subscription['years']) {
// it's a once off..
// add it to the list but dont calculate the next one.
} else {
if (!$original) {
// work out when the next one will be.
$next_time = self::_calculate_next_time($time, $subscription);
$time = $next_time;
} else {
$original = false;
// it's the original one.
$next_time = $time;
}
}
if ($next_time) {
// don't show it here if an invoice has already been generated.
// because invoice will already be in the list as outstanding
foreach ($subscription_invoices as $subscription_invoice) {
if (isset($subscription_invoice['date_create']) && $subscription_invoice['date_create'] == date('Y-m-d', $next_time)) {
//echo 'match';
continue 2;
}
}
$return[] = array('next_due_date' => date('Y-m-d', $next_time), 'url' => _l('Subscription: %s', $member_name), 'type' => 'i', 'amount' => $subscription['amount'], 'currency_id' => $subscription['currency_id'], 'days' => $subscription['days'], 'months' => $subscription['months'], 'years' => $subscription['years'], 'last_transaction_finance_id' => 0, 'account_name' => '', 'categories' => '', 'finance_recurring_id' => 0, 'last_transaction_text' => '(see member page)', 'end_date' => '0000-00-00', 'start_date' => $subscription['start_date'], 'recurring_text' => _l('Payment from %s %s on subscription %s', $type, $member_name, $subscription_name));
}
}
}
}
return $return;
}
public static function get_statistics_jobs($search)
{
$results = array();
// any jobs that were created within this time period
$sql = "SELECT * FROM `" . _DB_PREFIX . "job` j WHERE 1";
if (isset($search['type']) && $search['type']) {
$sql .= " AND j.`type` = '" . mysql_real_escape_string($search['type']) . "'";
}
if (isset($search['date_from']) && $search['date_from']) {
$sql .= " AND j.date_start >= '" . input_date($search['date_from']) . "'";
}
if (isset($search['date_to']) && $search['date_to']) {
$sql .= " AND j.date_start <= '" . input_date($search['date_to']) . "'";
}
$results = qa($sql);
// find any jobs that are due to be renewed within this time period
$sql = "SELECT * FROM `" . _DB_PREFIX . "job` j WHERE 1";
$sql .= " AND j.date_renew != '0000-00-00' ";
if (isset($search['type']) && $search['type']) {
$sql .= " AND j.`type` = '" . mysql_real_escape_string($search['type']) . "'";
}
if (isset($search['date_from']) && $search['date_from']) {
$sql .= " AND j.date_renew >= '" . input_date($search['date_from']) . "'";
}
if (isset($search['date_to']) && $search['date_to']) {
$sql .= " AND j.date_renew <= '" . input_date($search['date_to']) . "'";
}
$sql .= " AND (renew_job_id IS NULL OR renew_job_id = 0)";
foreach (qa($sql) as $renewed_job) {
$renewed_job['renew_from_job_id'] = $renewed_job['job_id'];
$time_diff = strtotime($renewed_job['date_renew']) - strtotime($renewed_job['date_start']);
$date_renew = $renewed_job['date_renew'];
$renewed_job['date_renew'] = date('Y-m-d', strtotime($renewed_job['date_renew']) + $time_diff);
$renewed_job['date_start'] = $date_renew;
$results[] = $renewed_job;
}
// any jobs due for renewal before this time period, that haven't been renewed.
// calculate their next renewal date(s) and see if one of them lands in time period.
if (isset($search['date_from']) && $search['date_from'] && isset($search['date_to']) && $search['date_to']) {
$from_timestamp = strtotime(input_date($search['date_from']));
$to_timestamp = strtotime(input_date($search['date_to']));
$sql = "SELECT * FROM `" . _DB_PREFIX . "job` j WHERE 1";
if (isset($search['type']) && $search['type']) {
$sql .= " AND j.`type` = '" . mysql_real_escape_string($search['type']) . "'";
}
$sql .= " AND j.date_start != '0000-00-00' ";
$sql .= " AND j.date_renew != '0000-00-00' ";
//$sql .= " AND j.date_start < '".input_date($search['date_from'])."'";
$sql .= " AND j.date_renew < '" . input_date($search['date_to']) . "'";
$sql .= " AND (j.renew_job_id IS NULL OR j.renew_job_id = 0)";
foreach (qa($sql) as $possible_renewed_job) {
$time_diff = strtotime($possible_renewed_job['date_renew']) - strtotime($possible_renewed_job['date_start']);
$new_renewal_date = strtotime($possible_renewed_job['date_renew']);
for ($x = 0; $x < 5; $x++) {
$new_renewal_date = $new_renewal_date + $time_diff;
if ($new_renewal_date >= $from_timestamp) {
// this job will be renewed in our period! yay!
if ($to_timestamp == 0 || $to_timestamp > 0 && $new_renewal_date <= $to_timestamp) {
// this is within our bounds! yay!
$possible_renewed_job['renew_from_job_id'] = $possible_renewed_job['job_id'];
$possible_renewed_job['date_start'] = date('Y-m-d', $new_renewal_date);
$possible_renewed_job['date_renew'] = date('Y-m-d', $new_renewal_date + $time_diff);
$results[] = $possible_renewed_job;
} else {
break;
// gone too far
}
}
}
}
}
usort($results, array('module_statistic', "get_statistics_jobs_sort"));
return $results;
}
public function check_moment($datetime, $experiment_id, $leader_id)
{
$experiment = $this->experimentModel->get_experiment_by_id($experiment_id);
$location = $this->locationModel->get_location_by_id($experiment->location_id);
$leader = $this->userModel->get_user_by_id($leader_id);
$starttime = input_datetime($datetime);
$date = input_date($datetime);
$endtime = new DateTime($datetime);
$endtime->add(new DateInterval('PT' . $experiment->duration . "M"));
$endtime = input_datetime($endtime->format('Y-m-d H:i'));
$lab_closed = $this->closingModel->within_bounds($starttime, $location->id) || $this->closingModel->within_bounds($endtime, $location->id);
$closings = $this->closingModel->get_closing_by_location_for_time($location->id, $date);
$locked_down = $this->closingModel->within_bounds($starttime, NULL) || $this->closingModel->within_bounds($endtime, NULL);
$lockdowns = $this->closingModel->get_closing_by_location_for_time(NULL, $date);
$user_available = !($this->availabilityModel->within_bounds($starttime, $leader_id) || $this->availabilityModel->within_bounds($starttime, $leader_id));
$user_availability = $this->availabilityModel->get_availability_by_user_and_day($leader_id, $date);
$locks = array();
foreach ($lockdowns as $lockdown) {
array_push($locks, sprintf(lang('timeframe'), format_datetime($lockdown->from), format_datetime($lockdown->to)));
}
$locked = array("status" => $locked_down, "string" => lang('lockdown_timeframe'), "times" => $locks);
$closings = array();
foreach ($closings as $closing) {
array_push($closings, sprintf(lang('timeframe'), format_datetime($closing->from), format_datetime($closing->to)));
}
$closed = array("status" => $lab_closed, "string" => lang('lab_closed'), "times" => $closings);
$availabilities = array();
if (isset($user_availability)) {
foreach ($user_availability as $av) {
array_push($availabilities, sprintf(lang('timeframe'), format_datetime($av->from), format_datetime($av->to)));
}
}
$availability = array("status" => $user_available, "string" => sprintf(lang('is_not_available'), $leader->username), "times" => $availabilities);
echo json_encode(array("locks" => $locked, "closings" => $closed, "availability" => $availability));
}
public function table($include_past = FALSE)
{
$this->datatables->select('name, from, comment, closing.id AS id, location_id');
$this->datatables->from('closing');
$this->datatables->join('location', 'location.id = closing.location_id', 'LEFT');
if (!$include_past) {
$this->db->where('to >=', input_date());
}
$this->datatables->edit_column('name', '$1', 'closing_location_link_by_id(location_id)');
$this->datatables->edit_column('from', '$1', 'closing_dates_by_id(id)');
$this->datatables->edit_column('comment', '$1', 'comment_body(comment, 30)');
$this->datatables->edit_column('id', '$1', 'closing_actions(id)');
$this->datatables->unset_column('location_id');
echo $this->datatables->generate();
}
function process_alert($check_date, $item, $alert_days_in_future = false)
{
if ($alert_days_in_future === false) {
$alert_days_in_future = module_config::c('alert_days_in_future', 5);
}
$date = input_date($check_date, true);
if ($check_date != 's2009-07-12') {
//echo $date;
}
if (!strtotime($date)) {
$date = false;
}
/*if(preg_match('#^\d?\d/\d?\d/\d{2,4}$#',$check_date)){
$date = implode("-",array_reverse(explode("/",$check_date)));
}else if(preg_match('#^\d{2,4}-\d?\d-\d?\d$#',$check_date)){
$date = $check_date;
}*/
$alert_res = false;
if ($date) {
// we have a date
$secs = date("U") - date("U", strtotime($date));
$days = $secs / 86400;
$alert_field = false;
$warning = false;
if ($secs > 0) {
$days = floor($days);
if ($days == 0) {
$alert_field = " " . _l('today!');
$warning = true;
} else {
$alert_field = " " . _l('%s days ago', $days);
$warning = true;
}
} else {
$days = abs($days);
$days = ceil($days);
if ($days == 0) {
$alert_field = " " . _l('today!');
$warning = true;
} else {
if ($days < $alert_days_in_future) {
$alert_field = " " . _l('in %s days', $days);
}
}
}
if ($alert_field) {
$alert_res = array("warning" => $warning, "alert" => _l($item) . $alert_field, "item" => _l($item), "days" => $alert_field, "date" => $date);
}
}
return $alert_res;
}
public function table_by_age($date, $age_in_months)
{
$this->datatables->where('TIMESTAMPDIFF(MONTH, dateofbirth, "' . input_date($date) . '") = ' . $age_in_months);
$this->datatables->where('activated', TRUE);
$this->table();
}
$ord_sql = ' order by clicked_date desc';
$select_fb_main = "select count(*) as total, 'Facebook' as provider,cs.shared_by,date_format(cs.date_time,'%Y-%m-%d') as clicked_date from cs_share_clicked cs join (select member_id,birthday,gender from cs_fb_share_users where client_id='" . $_SESSION['admin_id'] . "') as cu on cu.member_id=cs.shared_by where cs.client_id='" . $_SESSION['admin_id'] . "' and cs.is_from='1'";
$select_li_main = "select count(*) as total, 'LinkedIn' as provider,cl.shared_by,date_format(cl.date_time,'%Y-%m-%d') as clicked_date from cs_share_clicked cl join (select member_id,concat(byear,'-',bmonth,'-',bday) as birthday,byear from cs_li_data where client_id='" . $_SESSION['admin_id'] . "') as cu on cu.member_id=cl.shared_by where cl.client_id='" . $_SESSION['admin_id'] . "' and cl.is_from='2'";
}
if ($_POST['day_range'] == '0' || $_POST['day_range'] == '1') {
$select_fb_main .= " and datediff(current_date,date_time) ='" . $_POST['day_range'] . "'";
$select_li_main .= $stype == 'trs' ? " and datediff(current_date,date_time) ='" . $_POST['day_range'] . "'" : " and datediff(current_date,datetime) ='" . $_POST['day_range'] . "'";
} else {
if ($_POST['day_range'] > 1) {
$select_fb_main .= " and datediff(current_date,date_time) <='" . $_POST['day_range'] . "'";
$select_li_main .= $stype == 'trs' ? " and datediff(current_date,date_time) <='" . $_POST['day_range'] . "'" : " and datediff(current_date,datetime) <='" . $_POST['day_range'] . "'";
}
}
if (isset($_POST['csdate_2']) && $_POST['csdate_2'] != '' && isset($_POST['csdate_3']) && $_POST['csdate_3'] != '') {
$select_fb_main .= " and date_format(date_time,'%Y-%m-%d') between '" . input_date($_POST['csdate_2']) . "' and '" . input_date($_POST['csdate_3']) . "'";
$select_li_main .= $stype == 'trs' ? " and date_format(date_time,'%Y-%m-%d') between '" . input_date($_POST['csdate_2']) . "' and '" . input_date($_POST['csdate_3']) . "'" : " and date_format(datetime,'%Y-%m-%d') between '" . input_date($_POST['csdate_2']) . "' and '" . input_date($_POST['csdate_3']) . "'";
}
if ($_POST['age_range'] == '1') {
$select_fb_main .= " and (year(current_date)-year(birthday)) < 13";
} else {
if ($_POST['age_range'] == '2') {
$select_fb_main .= " and (year(current_date)-year(birthday)) >= 13 and (year(current_date)-year(birthday)) <= 17";
} else {
if ($_POST['age_range'] == '3') {
$select_fb_main .= " and (year(current_date)-year(birthday)) >= 18 and (year(current_date)-year(birthday)) <= 34";
} else {
if ($_POST['age_range'] == '4') {
$select_fb_main .= " and (year(current_date)-year(birthday)) >= 35 and (year(current_date)-year(birthday)) <= 49";
} else {
if ($_POST['age_range'] == '5') {
$select_fb_main .= " and (year(current_date)-year(birthday)) >= 50";
public static function update_insert($pkey, $pid, $table, $data = false, $do_replace = false)
{
if ($data === false) {
$data = $_REQUEST;
}
$fields = self::get_fields($table, array("date_created", "date_updated"));
//
if (isset($fields['system_id']) && defined('_SYSTEM_ID')) {
$data['system_id'] = _SYSTEM_ID;
}
if (isset($fields['date_created'])) {
unset($fields['date_created']);
}
$now_string = mysql_real_escape_string(date('Y-m-d H:i:s'));
if ($do_replace || !is_numeric($pid) || !$pid) {
$pid = 'new';
if ($do_replace) {
$sql = "REPLACE INTO ";
} else {
$sql = "INSERT INTO ";
}
$sql .= "`" . _DB_PREFIX . "{$table}` SET date_created = '{$now_string}', ";
if (isset($fields['create_user_id']) && isset($_SESSION['_user_id']) && $_SESSION['_user_id']) {
$sql .= "`create_user_id` = '" . (int) $_SESSION['_user_id'] . "', ";
unset($fields['create_user_id']);
}
if (isset($fields['create_ip_address'])) {
$sql .= "`create_ip_address` = '" . mysql_real_escape_string($_SERVER['REMOTE_ADDR']) . "', ";
unset($fields['create_ip_address']);
}
// check there's a valid site id
if (isset($fields['site_id']) && (!isset($data['site_id']) || !$data['site_id']) && isset($_SESSION['_site_id'])) {
$data['site_id'] = $_SESSION['_site_id'];
}
$where = "";
//module_security::sanatise_data($table,$data);
// todo - sanatise data here before we go through teh loop.
// if sanatisation fails or data access fails then we stop the update/insert.
if (!$data) {
// dont do this becuase $email->new_email() fails.
// return false;
}
} else {
// TODO - security hook here, check if we can access this data.
/*$security_dummy=array();
if(!module_security::can_access_data($table,$security_dummy,$pid)){
echo 'Security warning - unable to save data';
exit;
return false;
}*/
$updated = false;
if (isset($data['date_updated'])) {
$updated = "'" . mysql_real_escape_string(input_date($data['date_updated'], true)) . "'";
}
if (!$updated) {
$updated = "'{$now_string}'";
}
$sql = "UPDATE `" . _DB_PREFIX . "{$table}` SET date_updated = {$updated},";
if (isset($fields['update_user_id']) && isset($_SESSION['_user_id']) && $_SESSION['_user_id']) {
$sql .= "`update_user_id` = '" . (int) $_SESSION['_user_id'] . "', ";
unset($fields['update_user_id']);
}
if (isset($fields['update_ip_address'])) {
$sql .= "`update_ip_address` = '" . mysql_real_escape_string($_SERVER['REMOTE_ADDR']) . "', ";
unset($fields['update_ip_address']);
}
$where = " WHERE `{$pkey}` = '" . mysql_real_escape_string($pid) . "'";
if (isset($fields['system_id']) && defined('_SYSTEM_ID')) {
$where .= " AND system_id = '" . _SYSTEM_ID . "'";
}
}
//print_r($fields);exit;
//print_r($data);exit;
if (!$do_replace && isset($data[$pkey])) {
unset($data[$pkey]);
}
foreach ($fields as $field) {
if (!isset($data[$field['name']]) || $data[$field['name']] === false) {
continue;
}
// special format for date fields.
if ($field['type'] == 'date') {
$data[$field['name']] = input_date($data[$field['name']]);
}
// special format for int / double fields.
if (($field['type'] == 'decimal' || $field['type'] == 'double') && function_exists('number_in')) {
// how many decimals are we rounding this number to?
if (preg_match('#\\(\\d+,(\\d+)\\)#', $field['dbtype'], $matches)) {
$data[$field['name']] = number_in($data[$field['name']], $matches[1]);
} else {
$data[$field['name']] = number_in($data[$field['name']]);
}
}
if (is_array($data[$field['name']])) {
$val = serialize($data[$field['name']]);
} else {
$val = $data[$field['name']];
}
$sql .= " `" . $field['name'] . "` = '" . mysql_real_escape_string($val) . "', ";
}
$sql = rtrim($sql, ', ');
$sql .= $where;
query($sql);
if ($pid == "new") {
$pid = mysql_insert_id();
}
return $pid;
}
/**
* Creates a token entry.
* We don't save the participant details in LimeSurvey (hence John Doe as participant name).
* The participant details should be looked up in the administration system.
* @param Participant $participant
* @param integer $survey_id
* @param string $token
* @return integer The ID of the created token
*/
public function create_token($participant, $survey_id, $token)
{
$token_table = 'tokens_' . $survey_id;
$token_insert = array('firstname' => 'John', 'lastname' => 'Doe', 'email' => BABYLAB_MANAGER_EMAIL, 'emailstatus' => 'OK', 'token' => $token, 'language' => 'nl', 'sent' => 'Y');
if ($survey_id == 65377) {
$token_insert['attribute_1'] = strtoupper($participant->gender);
$token_insert['attribute_2'] = input_date($participant->dateofbirth);
$token_insert['attribute_3'] = $participant->birthweight;
$token_insert['attribute_4'] = $participant->pregnancyweeks;
$token_insert['attribute_5'] = $participant->pregnancydays;
$token_insert['attribute_6'] = $participant->dyslexicparent;
$token_insert['attribute_7'] = $participant->problemsparent;
$token_insert['attribute_8'] = $participant->multilingual;
$languages = $this->languageModel->get_languages_by_participant($participant->id);
$n = 9;
foreach ($languages as $language) {
$token_insert['attribute_' . $n++] = $language->language;
$token_insert['attribute_' . $n++] = $language->percentage;
}
}
if ($survey_id == 21825 || $survey_id == 23863) {
$token_insert['attribute_1'] = strtoupper($participant->gender);
$token_insert['attribute_2'] = input_date($participant->dateofbirth);
}
$this->survey_db->insert($token_table, $token_insert);
return $this->survey_db->insert_id();
}
/** Activates or deactivates the specified user */
public function set_activate($user_id, $activated)
{
$this->db->where('id', $user_id);
if ($activated) {
$this->db->update('user', array('activated' => input_date()));
} else {
$this->db->update('user', array('activated' => NULL));
}
}
<?php
echo count($staff_messages);
?>
in <?php
echo count($staff_tickets);
?>
tickets
</td>
<td>
<?php
echo count($staff_private_messages);
?>
</td>
<td>
<?php
$sql = "SELECT * FROM `" . _DB_PREFIX . "ticket` WHERE last_message_timestamp >= " . (int) strtotime(input_date($search['date_from'])) . " AND last_message_timestamp <= " . (int) strtotime(input_date($search['date_to'])) . " AND assigned_user_id = " . (int) $staff_member['user_id'];
$tickets = qa($sql);
echo count($tickets);
?>
</td>
<td>
<?php
$r = 0;
foreach ($tickets as $ticket) {
if ($ticket['status_id'] == _TICKET_STATUS_RESOLVED_ID) {
$r++;
}
}
echo $r;
?>
</td>
public static function get_finances($search = array())
{
// we have to search for recent transactions. this involves combining the "finance" table with the "invoice_payment" table
// then sort the results by date
$hide_invoice_payments = false;
$sql = "SELECT f.* ";
$sql .= " , fa.name AS account_name ";
$sql .= " , GROUP_CONCAT(fc.`name` ORDER BY fc.`name` ASC SEPARATOR ', ') AS categories ";
$sql .= " FROM `" . _DB_PREFIX . "finance` f ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "finance_account` fa USING (finance_account_id) ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "finance_category_rel` fcr ON f.finance_id = fcr.finance_id ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "finance_category` fc ON fcr.finance_category_id = fc.finance_category_id ";
$where = " WHERE 1 ";
if (isset($search['finance_account_id']) && is_array($search['finance_account_id'])) {
$fo = array();
foreach ($search['finance_account_id'] as $val) {
if ((int) $val > 0) {
$fo[(int) $val] = true;
}
}
if (count($fo) > 0) {
$where .= " AND ( ";
foreach ($fo as $f => $ff) {
$where .= " f.finance_account_id = " . $f . ' OR';
}
$where = rtrim($where, 'OR');
$where .= ' )';
$hide_invoice_payments = true;
}
}
if (isset($search['finance_recurring_id']) && $search['finance_recurring_id']) {
$where .= " AND f.finance_recurring_id = '" . (int) $search['finance_recurring_id'] . "'";
$hide_invoice_payments = true;
}
if (isset($search['finance_category_id']) && is_array($search['finance_category_id'])) {
$fo = array();
foreach ($search['finance_category_id'] as $val) {
if ((int) $val > 0) {
$fo[(int) $val] = true;
}
}
if (count($fo) > 0) {
$where .= " AND EXISTS ( SELECT * FROM `" . _DB_PREFIX . "finance_category_rel` fcr2 WHERE fcr2.finance_id = f.finance_id AND ( ";
foreach ($fo as $f => $ff) {
$where .= " fcr2.finance_category_id = " . $f . ' OR';
}
$where = rtrim($where, 'OR');
$where .= ' )';
$where .= ' )';
$hide_invoice_payments = true;
}
}
if (isset($search['invoice_payment_id']) && $search['invoice_payment_id']) {
$where .= " AND f.invoice_payment_id = '" . (int) $search['invoice_payment_id'] . "'";
$hide_invoice_payments = true;
}
// below 6 searches are repeated again below in invoice payments
if (isset($search['job_id']) && (int) $search['job_id'] > 0) {
$where .= " AND f.`job_id` = " . (int) $search['job_id'];
}
if (isset($search['invoice_id']) && (int) $search['invoice_id'] > 0) {
$where .= " AND f.`invoice_id` = " . (int) $search['invoice_id'];
}
if (isset($search['customer_id']) && (int) $search['customer_id'] > 0) {
$where .= " AND f.`customer_id` = " . (int) $search['customer_id'];
}
if (isset($search['company_id']) && (int) $search['company_id'] > 0) {
// check this user can view this company id or not
if (class_exists('module_company', false) && module_company::can_i('view', 'Company') && module_company::is_enabled()) {
$companys = module_company::get_companys();
if (isset($companys[$search['company_id']])) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "company_customer` cc ON f.customer_id = cc.customer_id ";
$where .= " AND ( cc.`company_id` = " . (int) $search['company_id'] . " OR f.`company_id` = " . (int) $search['company_id'] . " )";
}
}
}
if (isset($search['generic']) && strlen(trim($search['generic']))) {
$name = mysql_real_escape_string(trim($search['generic']));
$where .= " AND (f.`name` LIKE '%{$name}%' OR f.description LIKE '%{$name}%' )";
}
if (isset($search['date_from']) && $search['date_from'] != '') {
$where .= " AND f.transaction_date >= '" . input_date($search['date_from']) . "'";
}
if (isset($search['date_to']) && $search['date_to'] != '') {
$where .= " AND f.transaction_date <= '" . input_date($search['date_to']) . "'";
}
if (isset($search['amount_from']) && $search['amount_from'] != '') {
$where .= " AND f.amount >= '" . mysql_real_escape_string($search['amount_from']) . "'";
}
if (isset($search['amount_to']) && $search['amount_to'] != '') {
$where .= " AND f.amount <= '" . mysql_real_escape_string($search['amount_to']) . "'";
}
if (isset($search['type']) && $search['type'] != '' && $search['type'] != 'ie') {
$where .= " AND f.type = '" . mysql_real_escape_string($search['type']) . "'";
}
// permissions from job module.
/*switch(module_job::get_job_access_permissions()){
case _JOB_ACCESS_ALL:
break;
case _JOB_ACCESS_ASSIGNED:
// only assigned jobs!
//$from .= " LEFT JOIN `"._DB_PREFIX."task` t ON u.job_id = t.job_id ";
//u.user_id = ".(int)module_security::get_loggedin_id()." OR
$where .= " AND (t.user_id = ".(int)module_security::get_loggedin_id().")";
break;
case _JOB_ACCESS_CUSTOMER:
break;
}*/
// permissions from customer module.
// tie in with customer permissions to only get jobs from customers we can access.
switch (module_customer::get_customer_data_access()) {
case _CUSTOMER_ACCESS_ALL:
// all customers! so this means all jobs!
break;
case _CUSTOMER_ACCESS_ALL_COMPANY:
case _CUSTOMER_ACCESS_CONTACTS:
case _CUSTOMER_ACCESS_TASKS:
case _CUSTOMER_ACCESS_STAFF:
$valid_customer_ids = module_security::get_customer_restrictions();
if (count($valid_customer_ids)) {
$where .= " AND f.customer_id IN ( ";
foreach ($valid_customer_ids as $valid_customer_id) {
$where .= (int) $valid_customer_id . ", ";
}
$where = rtrim($where, ', ');
$where .= " )";
}
}
$where .= " GROUP BY f.finance_id ";
$where .= " ORDER BY f.transaction_date DESC ";
$sql .= $where;
$finances_from_finance_db_table = qa($sql);
// invoice payments:
$finance_from_invoice_payments = array();
$finance_from_job_staff_expenses = array();
if (!$hide_invoice_payments && (!isset($search['invoice_id']) || !(int) $search['invoice_id'] > 0)) {
$sql = "SELECT j.*, f.finance_id AS existing_finance_id ";
$sql .= " FROM `" . _DB_PREFIX . "job` j ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "finance` f ON j.job_id = f.job_id AND f.job_staff_expense > 0 ";
$where = " WHERE 1 ";
//j.date_completed != '0000-00-00' ";
$where .= " AND j.`c_staff_total_amount` > 0 ";
if (isset($search['job_id']) && (int) $search['job_id'] > 0) {
$where .= " AND (j.`job_id` = " . (int) $search['job_id'] . " ) ";
}
if (isset($search['customer_id']) && (int) $search['customer_id'] > 0) {
$where .= " AND j.`customer_id` = " . (int) $search['customer_id'];
}
/*if(isset($search['generic']) && strlen(trim($search['generic']))){
$name = mysql_real_escape_string(trim($search['generic']));
$where .= " AND (i.`name` LIKE '%$name%' OR p.method LIKE '%$name%' )";
}*/
if (isset($search['company_id']) && (int) $search['company_id'] > 0) {
// check this user can view this company id or not
if (class_exists('module_company', false) && module_company::can_i('view', 'Company') && module_company::is_enabled()) {
$companys = module_company::get_companys();
if (isset($companys[$search['company_id']])) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "company_customer` cc ON j.customer_id = cc.customer_id ";
$where .= " AND cc.`company_id` = " . (int) $search['company_id'];
}
}
}
if (isset($search['date_from']) && $search['date_from'] != '') {
$where .= " AND j.date_completed >= '" . input_date($search['date_from']) . "'";
}
if (isset($search['date_to']) && $search['date_to'] != '') {
$where .= " AND j.date_completed <= '" . input_date($search['date_to']) . "'";
}
if (isset($search['amount_from']) && $search['amount_from'] != '') {
$where .= " AND j.c_staff_total_amount >= '" . mysql_real_escape_string($search['amount_from']) . "'";
}
if (isset($search['amount_to']) && $search['amount_to'] != '') {
$where .= " AND j.c_staff_total_amount <= '" . mysql_real_escape_string($search['amount_to']) . "'";
}
switch (module_job::get_job_access_permissions()) {
case _JOB_ACCESS_ALL:
break;
case _JOB_ACCESS_ASSIGNED:
// only assigned jobs!
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task` t ON j.job_id = t.job_id ";
$where .= " AND (j.user_id = " . (int) module_security::get_loggedin_id() . " OR t.user_id = " . (int) module_security::get_loggedin_id() . ")";
break;
case _JOB_ACCESS_CUSTOMER:
// tie in with customer permissions to only get jobs from customers we can access.
$valid_customer_ids = module_security::get_customer_restrictions();
if (count($valid_customer_ids)) {
$where .= " AND j.customer_id IN ( ";
foreach ($valid_customer_ids as $valid_customer_id) {
$where .= (int) $valid_customer_id . ", ";
}
$where = rtrim($where, ', ');
$where .= " )";
}
break;
}
switch (module_customer::get_customer_data_access()) {
case _CUSTOMER_ACCESS_ALL:
// all customers! so this means all jobs!
break;
case _CUSTOMER_ACCESS_ALL_COMPANY:
case _CUSTOMER_ACCESS_CONTACTS:
case _CUSTOMER_ACCESS_TASKS:
case _CUSTOMER_ACCESS_STAFF:
$valid_customer_ids = module_security::get_customer_restrictions();
if (count($valid_customer_ids)) {
$where .= " AND j.customer_id IN ( ";
foreach ($valid_customer_ids as $valid_customer_id) {
$where .= (int) $valid_customer_id . ", ";
}
$where = rtrim($where, ', ');
$where .= " )";
}
}
$sql .= $where . " GROUP BY j.job_id ORDER BY j.date_completed DESC ";
//echo $sql;
$finance_from_job_staff_expenses = array();
$res = qa($sql);
foreach ($res as $finance) {
// we have a job with staff expenses. split this up into gruops based on staff members.
$staff_total_grouped = false;
if (isset($finance['c_staff_total_grouped']) && strlen($finance['c_staff_total_grouped'])) {
$staff_total_grouped = @unserialize($finance['c_staff_total_grouped']);
}
if ($staff_total_grouped === false) {
// echo 'here: ';
// var_dump($finance);
// var_dump($staff_total_grouped);
$job_data = module_job::get_job($finance['job_id']);
$staff_total_grouped = $job_data['staff_total_grouped'];
}
if (is_array($staff_total_grouped)) {
foreach ($staff_total_grouped as $staff_id => $staff_total) {
$staff_member = module_user::get_user($staff_id);
if ($staff_member && $staff_member['user_id'] == $staff_id) {
// make sure this entry doesn't already exist in the database table for this job
// there MAY be an existing entry if 'existing_finance_id' is set
if ($finance['existing_finance_id'] > 0) {
// check if it exists for this staff member.
$existing = get_single('finance', array('job_id', 'job_staff_expense', 'amount'), array($finance['job_id'], $staff_id, $staff_total));
if ($existing) {
// match exists already, skip adding this one to the list.
continue;
}
}
//$finance = self::_format_invoice_payment($finance, $finance);
//$finance['url'] = module_job::link_open($finance['job_id'],false,$finance);
$finance['url'] = module_finance::link_open('new', false) . '&job_staff_expense=' . $staff_id . '&from_job_id=' . $finance['job_id'];
$finance['transaction_date'] = $finance['date_completed'];
$finance['description'] = _l('Job Expense For Staff Member: %s', $staff_member['name'] . ' ' . $staff_member['last_name']);
//"Exiting: ".$finance['existing_finance_id'].": ".
$finance['amount'] = $staff_total;
$finance['debit'] = $staff_total;
$finance['sub_amount'] = $staff_total;
$finance['taxable_amount'] = $staff_total;
$finance['credit'] = 0;
$finance['type'] = 'e';
$finance_from_job_staff_expenses[] = $finance;
}
}
}
}
}
if (!$hide_invoice_payments) {
$sql = "SELECT p.*, i.customer_id ";
if (module_config::c('finance_date_type', 'payment') == 'invoice') {
// show entries by invoice create date, not payment date.
$sql .= " , i.date_create AS transaction_date ";
} else {
// default, show by paid date.
$sql .= " , p.date_paid AS transaction_date ";
}
$sql .= " FROM `" . _DB_PREFIX . "invoice_payment` p ";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "invoice` i ON p.invoice_id = i.invoice_id ";
$where = " WHERE p.date_paid != '0000-00-00' ";
$where .= " AND p.`amount` != 0 ";
$where .= " AND ( p.`payment_type` = " . _INVOICE_PAYMENT_TYPE_NORMAL . " OR p.`payment_type` = " . _INVOICE_PAYMENT_TYPE_REFUND . ' OR p.`payment_type` = ' . _INVOICE_PAYMENT_TYPE_OVERPAYMENT_CREDIT . ' OR p.`payment_type` = ' . _INVOICE_PAYMENT_TYPE_CREDIT . ')';
if (isset($search['job_id']) && (int) $search['job_id'] > 0) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "invoice_item` ii ON i.invoice_id = ii.invoice_id";
$sql .= " LEFT JOIN `" . _DB_PREFIX . "task` t ON ii.task_id = t.task_id";
$where .= " AND (t.`job_id` = " . (int) $search['job_id'] . " OR i.`deposit_job_id` = " . (int) $search['job_id'] . " ) ";
}
if (isset($search['invoice_id']) && (int) $search['invoice_id'] > 0) {
$where .= " AND p.`invoice_id` = " . (int) $search['invoice_id'];
}
if (isset($search['customer_id']) && (int) $search['customer_id'] > 0) {
$where .= " AND i.`customer_id` = " . (int) $search['customer_id'];
}
/*if(isset($search['generic']) && strlen(trim($search['generic']))){
$name = mysql_real_escape_string(trim($search['generic']));
$where .= " AND (i.`name` LIKE '%$name%' OR p.method LIKE '%$name%' )";
}*/
if (isset($search['company_id']) && (int) $search['company_id'] > 0) {
// check this user can view this company id or not
if (class_exists('module_company', false) && module_company::can_i('view', 'Company') && module_company::is_enabled()) {
$companys = module_company::get_companys();
if (isset($companys[$search['company_id']])) {
$sql .= " LEFT JOIN `" . _DB_PREFIX . "company_customer` cc ON i.customer_id = cc.customer_id ";
$where .= " AND cc.`company_id` = " . (int) $search['company_id'];
}
}
}
if (isset($search['date_from']) && $search['date_from'] != '') {
if (module_config::c('finance_date_type', 'payment') == 'invoice') {
$where .= " AND i.date_create >= '" . input_date($search['date_from']) . "'";
} else {
$where .= " AND p.date_paid >= '" . input_date($search['date_from']) . "'";
}
}
if (isset($search['date_to']) && $search['date_to'] != '') {
if (module_config::c('finance_date_type', 'payment') == 'invoice') {
$where .= " AND i.date_create <= '" . input_date($search['date_to']) . "'";
} else {
$where .= " AND p.date_paid <= '" . input_date($search['date_to']) . "'";
}
}
if (isset($search['amount_from']) && $search['amount_from'] != '') {
$where .= " AND p.amount >= '" . mysql_real_escape_string($search['amount_from']) . "'";
}
if (isset($search['amount_to']) && $search['amount_to'] != '') {
$where .= " AND p.amount <= '" . mysql_real_escape_string($search['amount_to']) . "'";
}
if (isset($search['type']) && $search['type'] != '' && $search['type'] != 'ie') {
if ($search['type'] == 'i') {
$where .= " AND p.amount > 0";
} else {
if ($search['type'] == 'e') {
$where .= " AND p.amount < 0";
}
}
}
switch (module_customer::get_customer_data_access()) {
case _CUSTOMER_ACCESS_ALL:
// all customers! so this means all jobs!
break;
case _CUSTOMER_ACCESS_ALL_COMPANY:
case _CUSTOMER_ACCESS_CONTACTS:
case _CUSTOMER_ACCESS_TASKS:
case _CUSTOMER_ACCESS_STAFF:
$valid_customer_ids = module_security::get_customer_restrictions();
if (count($valid_customer_ids)) {
$where .= " AND i.customer_id IN ( ";
foreach ($valid_customer_ids as $valid_customer_id) {
$where .= (int) $valid_customer_id . ", ";
}
$where = rtrim($where, ', ');
$where .= " )";
}
}
$sql .= $where . " ORDER BY p.date_paid DESC ";
//echo $sql;
$finance_from_invoice_payments = qa($sql);
foreach ($finance_from_invoice_payments as $finance_id => $finance) {
// doesn't have an finance / account reference just yet.
// but they can create one and this will become a child entry to it.
$finance = self::_format_invoice_payment($finance, $finance);
/*if(!isset($finance['customer_id']) || !$finance['customer_id']){
$invoice_data = module_invoice::get_invoice($finance['invoice_id'],2);
$finance['customer_id'] = $invoice_data['customer_id'];
}*/
// grab a new name/descriptino/etc.. from other plugins (at the moment only subscription)
/*$new_finance = hook_handle_callback('finance_invoice_listing',$finance['invoice_id'],$finance);
if(is_array($new_finance) && count($new_finance)){
foreach($new_finance as $n){
$finance = array_merge($finance,$n);
}
}*/
$finance_from_invoice_payments[$finance_id] = $finance;
}
if (isset($search['generic']) && strlen(trim($search['generic']))) {
$name = mysql_real_escape_string(trim($search['generic']));
// $where .= " AND (i.`name` LIKE '%$name%' OR p.method LIKE '%$name%' )";
// we have to do a PHP search here because
foreach ($finance_from_invoice_payments as $finance_id => $finance) {
if (stripos($finance['name'], $name) === false && stripos($finance['description'], $name) === false) {
unset($finance_from_invoice_payments[$finance_id]);
}
}
}
}
$finances = array_merge($finances_from_finance_db_table, $finance_from_invoice_payments, $finance_from_job_staff_expenses);
unset($finances_from_finance_db_table);
unset($finance_from_invoice_payments);
unset($finance_from_job_staff_expenses);
// sort this
if (!function_exists('sort_finance')) {
function sort_finance($a, $b)
{
$t1 = strtotime($a['transaction_date']);
$t2 = strtotime($b['transaction_date']);
if ($t1 == $t2) {
// sort by finance id, putting ones with a finance id first before others. then amount.
if (isset($a['finance_id']) && !isset($b['finance_id'])) {
// put $a before $b
return -1;
} else {
if (!isset($a['finance_id']) && isset($b['finance_id'])) {
// put $b before $a
return 1;
} else {
return $a['amount'] > $b['amount'];
}
}
} else {
return $t1 < $t2;
}
}
}
uasort($finances, 'sort_finance');
foreach ($finances as $finance_id => $finance) {
// we load each of these transactions
// transaction can be a "transaction" or an "invoice_payment"
// find out if this transaction is a child transaction to another transaction.
// if it is a child transaction and we haven't already dispayed it in this listing
// then we find the parent transaction and display it along with all it's children in this place.
// this wont be perfect all the time but will be awesome in 99% of cases.
if (isset($finance['finance_id']) && $finance['finance_id']) {
// displayed before already?
if (isset($displayed_finance_ids[$finance['finance_id']])) {
$finances[$displayed_finance_ids[$finance['finance_id']]]['link_count']++;
unset($finances[$finance_id]);
continue;
}
$displayed_finance_ids[$finance['finance_id']] = $finance_id;
if (isset($finance['invoice_payment_id']) && $finance['invoice_payment_id']) {
$displayed_invoice_payment_ids[$finance['invoice_payment_id']] = $finance_id;
// so we dont display again.
}
} else {
if (isset($finance['invoice_payment_id']) && $finance['invoice_payment_id'] && isset($finance['invoice_id']) && $finance['invoice_id']) {
// this is an invoice payment (incoming payment)
// displayed before already?
if (isset($displayed_invoice_payment_ids[$finance['invoice_payment_id']])) {
$finances[$displayed_invoice_payment_ids[$finance['invoice_payment_id']]] = array_merge($finance, $finances[$displayed_invoice_payment_ids[$finance['invoice_payment_id']]]);
$finances[$displayed_invoice_payment_ids[$finance['invoice_payment_id']]]['link_count']++;
unset($finances[$finance_id]);
continue;
}
$displayed_invoice_payment_ids[$finance['invoice_payment_id']] = $finance_id;
// so we dont display again.
} else {
if (isset($finance['c_staff_total_amount'])) {
// staff expense.
} else {
// nfi?
unset($finances[$finance_id]);
continue;
}
}
}
if (isset($finance['parent_finance_id']) && $finance['parent_finance_id']) {
// check if it's parent finance id has been displayed already somewhere.
if (isset($displayed_finance_ids[$finance['parent_finance_id']])) {
$finances[$displayed_finance_ids[$finance['parent_finance_id']]]['link_count']++;
unset($finances[$finance_id]);
continue;
// already done it on this page.
}
$displayed_finance_ids[$finance['parent_finance_id']] = $finance_id;
// we haven't displayed the parent one yet.
// display the parent one in this listing.
$finance = self::get_finance($finance['parent_finance_id']);
}
/*if(isset($finance['invoice_payment_id']) && $finance['invoice_payment_id'] && isset($finance['invoice_id']) && $finance['invoice_id']){
// moved to above.
}else*/
if (isset($finance['finance_id']) && $finance['finance_id']) {
$finance['url'] = self::link_open($finance['finance_id'], false);
$finance['credit'] = $finance['type'] == 'i' ? $finance['amount'] : 0;
$finance['debit'] = $finance['type'] == 'e' ? $finance['amount'] : 0;
if (!isset($finance['categories'])) {
$finance['categories'] = '';
}
if (!isset($finance['account_name'])) {
$finance['account_name'] = '';
}
}
if (isset($finance['taxes']) && !isset($finance['sub_amount'])) {
$finance['sub_amount'] = $finance['amount'];
foreach ($finance['taxes'] as $tax) {
if (isset($tax['amount'])) {
$finance['sub_amount'] -= $tax['amount'];
}
}
}
$finance['link_count'] = 0;
$finances[$finance_id] = $finance;
}
return $finances;
}
public static function run_pagination_hook(&$rows, &$per_page)
{
if (self::$table_sort_options) {
self::is_currently_sorting();
// loads the session data.
$new_sort_column = $new_sort_direction = $new_sort_per_page = false;
if (isset($_SESSION['_table_sort']) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']]) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']][0])) {
$new_sort_column = $_SESSION['_table_sort'][self::$table_sort_options['table_id']][0];
}
if (isset($_SESSION['_table_sort']) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']]) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']][1])) {
$new_sort_direction = $_SESSION['_table_sort'][self::$table_sort_options['table_id']][1];
}
if (isset($_SESSION['_table_sort']) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']]) && isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']][2])) {
$new_sort_per_page = $_SESSION['_table_sort'][self::$table_sort_options['table_id']][2];
}
// count how many results for the "per page" drop down below.
self::$table_sort_options['row_count'] = is_resource($rows) ? mysql_num_rows($rows) : count($rows);
if (!isset($_SESSION['_table_sort']) || !isset($_SESSION['_table_sort'][self::$table_sort_options['table_id']])) {
return;
}
if ($new_sort_column && $new_sort_direction) {
// clear defaults! time for a user defined one.
foreach (self::$table_sort_options['sortable'] as $column_id => $options) {
if (isset($options['current'])) {
unset(self::$table_sort_options['sortable'][$column_id]['current']);
}
if ($column_id == $new_sort_column) {
self::$table_sort_options['sortable'][$column_id]['current'] = $new_sort_direction;
}
}
}
if ($new_sort_per_page >= 1) {
$per_page = $new_sort_per_page;
} else {
if ($new_sort_per_page == -2) {
// special flag for "all"
$per_page = false;
}
}
if (!$new_sort_column) {
return;
}
// sort results by selected option.
if (is_resource($rows)) {
$new_rows = array();
while ($row = mysql_fetch_assoc($rows)) {
$new_rows[] = $row;
}
mysql_free_result($rows);
$rows = $new_rows;
} else {
// rows stays the same.
}
if (is_array($rows) && count($rows)) {
foreach (self::$table_sort_options['sortable'] as $column_id => $options) {
if (isset($options['current'])) {
// we have a sortable key! yay!
// is this a special "group sort" ?
if (isset($options['group_sort']) && $options['group_sort'] && $options['owner_table'] && $options['owner_id']) {
// find the group(s) for EVERY row in the result set.
// this is super slow, but only way to sort.
// we also sort multiple groups in the same order that is selected here.
if (class_exists('module_group', false)) {
foreach ($rows as $row_id => $row) {
if (!isset($row[$options['owner_id']]) || !$row[$options['owner_id']]) {
continue;
}
// find the groups for this customer.
$groups = module_group::get_groups_search(array('owner_table' => $options['owner_table'], 'owner_id' => $row[$options['owner_id']]));
$g = array();
foreach ($groups as $group) {
$g[] = $group['name'];
}
natcasesort($g);
if ($options['current'] == 1) {
// ascendine
} else {
// descenting
$g = array_reverse($g);
}
$rows[$row_id]['group_sort_' . $options['owner_table']] = implode($g, ', ');
}
self::$sortables['group_sort_' . $options['owner_table']] = $options['current'];
}
} else {
if (isset($options['extra_sort']) && $options['extra_sort'] && $options['owner_table'] && $options['owner_id']) {
// find the extra(s) for EVERY row in the result set.
// this is super slow, but only way to sort.
// we also sort multiple extras in the same order that is selected here.
if (class_exists('module_extra', false)) {
foreach ($rows as $row_id => $row) {
if (!isset($row[$options['owner_id']]) || !$row[$options['owner_id']]) {
continue;
}
// find the extras for this customer.
$extras = module_extra::get_extras(array('owner_table' => $options['owner_table'], 'owner_id' => $row[$options['owner_id']], 'extra_key' => $options['field']));
if (count($extras) == 1) {
// found a match!
$extra_val = current($extras);
if (isset($options['field_type']) && $options['field_type'] == 'date') {
$extra_val['extra'] = input_date($extra_val['extra']);
}
$rows[$row_id]['extra_header_' . $options['default_field_id']] = $extra_val['extra'];
}
}
self::$sortables['extra_header_' . $options['default_field_id']] = $options['current'];
}
} else {
// nope! yay! normal sort.
self::$sortables[$options['field']] = $options['current'];
}
}
}
}
uasort($rows, array('module_table_sort', 'dosort'));
}
// set the 'per page' value based on session setting.
}
}
// code for generate relationship status
$select_fb_main = "select if(relationship_status!='',relationship_status,'Not Provided') as relation_status, relationship_status as relation from cs_fb_share_users where reg_on is not null and reg_on!='0000-00-00 00:00:00' and client_id='" . $_SESSION['admin_id'] . "'";
$select_gp_main = "select if(relationship_status!='',relationship_status,'Not Provided') as relation_status, relationship_status as relation from cs_gp_users where reg_on is not null and reg_on!='0000-00-00 00:00:00' and client_id='" . $_SESSION['admin_id'] . "'";
$select_li_main = "select 'Not Provided' as relation_status, '' as relation from cs_li_data where reg_on is not null and reg_on!='0000-00-00 00:00:00' and client_id='" . $_SESSION['admin_id'] . "'";
$select_yh_main = "select 'Not Provided' as relation_status, '' as relation from cs_yahoo_data where reg_on is not null and reg_on!='0000-00-00 00:00:00' and client_id='" . $_SESSION['admin_id'] . "'";
$select_ig_main = "select 'Not Provided' as relation_status, '' as relation from cs_ig_data where reg_on is not null and reg_on!='0000-00-00 00:00:00' and client_id='" . $_SESSION['admin_id'] . "'";
$select_main = "";
if ($_POST['day_range'] == '0' || $_POST['day_range'] == '1') {
$select_main .= " and datediff(current_date,reg_on) ='" . $_POST['day_range'] . "'";
} else {
if ($_POST['day_range'] > 1) {
$select_main .= " and datediff(current_date,reg_on) <='" . $_POST['day_range'] . "'";
}
}
if (isset($_POST['csdate_2']) && $_POST['csdate_2'] != '' && isset($_POST['csdate_3']) && $_POST['csdate_3'] != '') {
$select_main .= " and date_format(reg_on,'%Y-%m-%d') between '" . input_date($_POST['csdate_2']) . "' and '" . input_date($_POST['csdate_3']) . "'";
}
$select_fb_main .= $select_main;
$select_gp_main .= $select_main;
$select_li_main .= $select_main;
$select_yh_main .= $select_main;
$select_ig_main .= $select_main;
$select_main_qry = "select a.relation_status, count(*) as tot, relation from \n ( " . $select_fb_main . " union all " . $select_gp_main . " union all " . $select_li_main . " union all " . $select_yh_main . " union all " . $select_ig_main . " ) \n as a group by a.relation_status order by relation_status";
$select_main_sql = re_db_query($select_main_qry);
$tot_rec = re_db_num_rows($select_main_sql);
?>
<section class="widget">
<header>
<h4>New Users by <small>Gender</small></h4>
<div class="widget-controls">
<a data-widgster="close" title="Close" href="#"><i class="glyphicon glyphicon-remove"></i></a>
public static function get_jobs($search = array(), $return_options = array())
{
// limit based on customer id
/*if(!isset($_REQUEST['customer_id']) || !(int)$_REQUEST['customer_id']){
return array();
}*/
$cache_key = 'get_jobs_' . md5(serialize(array($search, $return_options)));
if ($cached_item = module_cache::get('job', $cache_key)) {
return $cached_item;
}
$cache_timeout = module_config::c('cache_objects', 60);
// build up a custom search sql query based on the provided search fields
$sql = "SELECT ";
if (isset($return_options['columns'])) {
$sql .= $return_options['columns'];
} else {
$sql .= "u.*,u.job_id AS id ";
$sql .= ", u.name AS name ";
$sql .= ", c.customer_name ";
if (class_exists('module_website', false) && module_website::is_plugin_enabled()) {
$sql .= ", w.name AS website_name";
// for export
}
$sql .= ", us.name AS staff_member";
// for export
}
$from = " FROM `" . _DB_PREFIX . "job` u ";
$from .= " LEFT JOIN `" . _DB_PREFIX . "customer` c USING (customer_id)";
if (class_exists('module_website', false) && module_website::is_plugin_enabled()) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "website` w ON u.website_id = w.website_id";
// for export
}
$from .= " LEFT JOIN `" . _DB_PREFIX . "user` us ON u.user_id = us.user_id";
// for export
$where = " WHERE 1 ";
if (is_array($return_options) && isset($return_options['custom_where'])) {
// put in return options so harder to push through from user end.
$where .= $return_options['custom_where'];
}
if (isset($search['generic']) && $search['generic']) {
$str = mysql_real_escape_string($search['generic']);
$where .= " AND ( ";
$where .= " u.name LIKE '%{$str}%' ";
//OR ";
//$where .= " u.url LIKE '%$str%' ";
$where .= ' ) ';
}
if (isset($search['date_start_after']) && $search['date_start_after'] !== '' && $search['date_start_after'] !== false) {
$date = input_date($search['date_start_after']);
$where .= " AND u.`date_start` >= '" . mysql_real_escape_string($date) . "'";
}
if (isset($search['date_start_before']) && $search['date_start_before'] !== '' && $search['date_start_before'] !== false) {
$date = input_date($search['date_start_before']);
$where .= " AND u.`date_start` != '0000-00-00' AND u.`date_start` <= '" . mysql_real_escape_string($date) . "'";
}
if (isset($search['task_due_after']) && $search['task_due_after'] !== '' && $search['task_due_after'] !== false) {
$date = input_date($search['task_due_after']);
if (!strpos($from, 'task`')) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "task` ts ON u.job_id = ts.job_id ";
}
$where .= " AND ts.`date_due` >= '" . mysql_real_escape_string($date) . "'";
}
if (isset($search['task_due_before']) && $search['task_due_before'] !== '' && $search['task_due_before'] !== false) {
$date = input_date($search['task_due_before']);
if (!strpos($from, 'task`')) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "task` ts ON u.job_id = ts.job_id ";
}
$where .= " AND ts.`date_due` != '0000-00-00' AND ts.`date_due` <= '" . mysql_real_escape_string($date) . "'";
}
if (isset($search['user_id']) && $search['user_id'] !== '' && $search['user_id'] !== false && (int) $search['user_id'] > 0) {
$user_id = (int) $search['user_id'];
if (!strpos($from, 'task`')) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "task` ts ON u.job_id = ts.job_id ";
}
$where .= " AND ( u.`user_id` = {$user_id} OR `ts`.`user_id` = {$user_id} ) ";
}
if (strpos($sql, 'ts.') && !strpos($from, 'task')) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "task` ts ON u.job_id = ts.job_id ";
}
if (isset($search['group_id']) && trim($search['group_id'])) {
$str = (int) $search['group_id'];
$from .= " LEFT JOIN `" . _DB_PREFIX . "group_member` gm ON (u.job_id = gm.owner_id)";
$where .= " AND (gm.group_id = '{$str}' AND gm.owner_table = 'job')";
}
if (isset($search['extra_fields']) && is_array($search['extra_fields']) && class_exists('module_extra', false)) {
$extra_fields = array();
foreach ($search['extra_fields'] as $key => $val) {
if (strlen(trim($val))) {
$extra_fields[$key] = trim($val);
}
}
if (count($extra_fields)) {
$from .= " LEFT JOIN `" . _DB_PREFIX . "extra` ext ON (ext.owner_id = u.job_id)";
//AND ext.owner_table = 'customer'
$where .= " AND (ext.owner_table = 'job' AND ( ";
foreach ($extra_fields as $key => $val) {
$val = mysql_real_escape_string($val);
$key = mysql_real_escape_string($key);
$where .= "( ext.`extra` LIKE '%{$val}%' AND ext.`extra_key` = '{$key}') OR ";
}
$where = rtrim($where, ' OR');
$where .= ' ) )';
}
}
foreach (array('customer_id', 'website_id', 'renew_job_id', 'status', 'type', 'date_start', 'date_quote', 'quote_id') as $key) {
if (isset($search[$key]) && $search[$key] !== '' && $search[$key] !== false) {
$str = mysql_real_escape_string($search[$key]);
if ($str[0] == '!') {
// hack for != sql searching.
$str = ltrim($str, '!');
$where .= " AND u.`{$key}` != '{$str}'";
} else {
$where .= " AND u.`{$key}` = '{$str}'";
}
}
}
if (isset($search['completed']) && (int) $search['completed'] > 0) {
switch ($search['completed']) {
case 1:
// both complete and not complete jobs, dont modify query
break;
case 2:
// only completed jobs.
$where .= " AND u.date_completed != '0000-00-00'";
break;
case 3:
// only non-completed jobs.
$where .= " AND u.date_completed = '0000-00-00'";
break;
case 4:
// only quoted jobs
$where .= " AND u.date_start = '0000-00-00' AND u.date_quote != '0000-00-00'";
break;
case 5:
// only not started jobs
$where .= " AND u.date_start = '0000-00-00'";
break;
}
}
if (isset($return_options['custom_group_by'])) {
$group_order = $return_options['custom_group_by'];
} else {
$group_order = ' GROUP BY u.job_id ORDER BY u.name';
}
switch (self::get_job_access_permissions()) {
case _JOB_ACCESS_ALL:
break;
case _JOB_ACCESS_ASSIGNED:
// only assigned jobs!
$from .= " LEFT JOIN `" . _DB_PREFIX . "task` t ON u.job_id = t.job_id ";
$where .= " AND (u.user_id = " . (int) module_security::get_loggedin_id() . " OR t.user_id = " . (int) module_security::get_loggedin_id() . ")";
break;
case _JOB_ACCESS_CUSTOMER:
// tie in with customer permissions to only get jobs from customers we can access.
$customers = module_customer::get_customers();
if (count($customers)) {
$where .= " AND u.customer_id IN ( ";
foreach ($customers as $customer) {
$where .= $customer['customer_id'] . ', ';
}
$where = rtrim($where, ', ');
$where .= " ) ";
}
break;
}
// tie in with customer permissions to only get jobs from customers we can access.
switch (module_customer::get_customer_data_access()) {
case _CUSTOMER_ACCESS_ALL:
// all customers! so this means all jobs!
break;
case _CUSTOMER_ACCESS_ALL_COMPANY:
case _CUSTOMER_ACCESS_CONTACTS:
case _CUSTOMER_ACCESS_TASKS:
case _CUSTOMER_ACCESS_STAFF:
$valid_customer_ids = module_security::get_customer_restrictions();
if (count($valid_customer_ids)) {
$where .= " AND ( u.customer_id = 0 OR u.customer_id IN ( ";
foreach ($valid_customer_ids as $valid_customer_id) {
$where .= (int) $valid_customer_id . ", ";
}
$where = rtrim($where, ', ');
$where .= " )";
$where .= " )";
}
}
$sql = $sql . $from . $where . $group_order;
// echo $sql;print_r(debug_backtrace());exit;
$result = qa($sql);
//module_security::filter_data_set("job",$result);
module_cache::put('job', $cache_key, $result, $cache_timeout);
return $result;
// return get_multiple("job",$search,"job_id","fuzzy","name");
}
