function __construct()
{
parent::__construct();
$this->view_data['core_settings'] = Setting::first();
$this->view_data['datetime'] = date('Y-m-d H:i', time());
if ($this->input->cookie('fc2language') != "") {
$language = $this->input->cookie('fc2language');
} else {
if (isset($this->view_data['language'])) {
$language = $this->view_data['language'];
} else {
if (!empty($this->view_data['core_settings']->language)) {
$language = $this->view_data['core_settings']->language;
} else {
$language = "english";
}
}
}
$this->lang->load('application', $language);
$this->lang->load('messages', $language);
$this->lang->load('event', $language);
$this->user = $this->session->userdata('user_id') ? User::find_by_id($this->session->userdata('user_id')) : FALSE;
$this->client = $this->session->userdata('client_id') ? Client::find_by_id($this->session->userdata('client_id')) : FALSE;
if ($this->user || $this->client) {
//check if user or client
if ($this->user) {
$access = $this->user->access;
$access = explode(",", $access);
$update = User::find($this->user->id);
$email = 'u' . $this->user->id;
$this->view_data['menu'] = Module::find('all', array('order' => 'sort asc', 'conditions' => array('id in (?) AND type = ?', $access, 'main')));
$this->view_data['widgets'] = Module::find('all', array('conditions' => array('id in (?) AND type = ?', $access, 'widget')));
$this->view_data['user_online'] = User::all(array('conditions' => array('last_active+(30 * 60) > ? AND status = ?', time(), "active")));
$this->view_data['client_online'] = Client::all(array('conditions' => array('last_active+(30 * 60) > ? AND inactive = ?', time(), "0")));
$this->view_data['quotations_new'] = Quote::find_by_sql("select count(id) as amount from quotations where status='New'");
$this->view_data['sticky'] = Project::find_by_sql("select distinct(projects.name), projects.id, projects.tracking, projects.progress from projects, project_has_workers where projects.sticky = 1 AND projects.id = project_has_workers.project_id AND project_has_workers.user_id=" . $this->user->id);
$this->view_data['tickets_new'] = Ticket::find_by_sql("select count(id) as amount from tickets where `status`='New'");
$this->view_data['estimates_new'] = Invoice::find_by_sql("select count(id) as amount from invoices where (`estimate_status`='Open' or `estimate_status`='Sent' or `estimate_status`='Revised')");
} else {
$this->theme_view = 'application_client';
$access = $this->client->access;
$access = explode(",", $access);
$email = 'c' . $this->client->id;
$this->view_data['menu'] = Module::find('all', array('order' => 'sort asc', 'conditions' => array('id in (?) AND type = ?', $access, 'client')));
$update = Client::find($this->client->id);
$this->view_data['estimates_new'] = Invoice::find_by_sql("select count(id) as amount from invoices where (`estimate_status`='Sent' or `estimate_status`='Revised') and company_id =" . $this->client->company_id);
}
//Update user last active
$update->last_active = time();
$update->save();
$this->view_data['messages_new'] = Privatemessage::find_by_sql("select count(id) as amount from privatemessages where `status`='New' AND recipient = '" . $email . "'");
}
/*$this->load->database();
$sql = "select * FROM templates WHERE type='notes'";
$query = $this->db->query($sql); */
$this->view_data["note_templates"] = "";
//$query->result();
}
function index()
{
$this->view_data['estimates'] = Invoice::find('all', array('conditions' => array('estimate != ?', 0)));
$days_in_this_month = days_in_month(date('m'), date('Y'));
$lastday_in_month = strtotime(date('Y') . "-" . date('m') . "-" . $days_in_this_month);
$firstday_in_month = strtotime(date('Y') . "-" . date('m') . "-01");
$this->view_data['estimates_paid_this_month'] = Invoice::count(array('conditions' => 'UNIX_TIMESTAMP(`paid_date`) <= ' . $lastday_in_month . ' and UNIX_TIMESTAMP(`paid_date`) >= ' . $firstday_in_month . ' AND estimate != 0'));
$this->view_data['estimates_due_this_month'] = Invoice::count(array('conditions' => 'UNIX_TIMESTAMP(`due_date`) <= ' . $lastday_in_month . ' and UNIX_TIMESTAMP(`due_date`) >= ' . $firstday_in_month . ' AND estimate != 0'));
//statistic
$now = time();
$beginning_of_week = strtotime('last Monday', $now);
// BEGINNING of the week
$end_of_week = strtotime('next Sunday', $now) + 86400;
// END of the last day of the week
$this->view_data['estimates_due_this_month_graph'] = Invoice::find_by_sql('select count(id) AS "amount", DATE_FORMAT(`due_date`, "%w") AS "date_day", DATE_FORMAT(`due_date`, "%Y-%m-%d") AS "date_formatted" from invoices where UNIX_TIMESTAMP(`due_date`) >= "' . $beginning_of_week . '" AND UNIX_TIMESTAMP(`due_date`) <= "' . $end_of_week . '" AND estimate != 0');
$this->view_data['estimates_paid_this_month_graph'] = Invoice::find_by_sql('select count(id) AS "amount", DATE_FORMAT(`paid_date`, "%w") AS "date_day", DATE_FORMAT(`paid_date`, "%Y-%m-%d") AS "date_formatted" from invoices where UNIX_TIMESTAMP(`paid_date`) >= "' . $beginning_of_week . '" AND UNIX_TIMESTAMP(`paid_date`) <= "' . $end_of_week . '" AND estimate != 0');
$this->content_view = 'estimates/all';
}
function filter($condition = FALSE)
{
$days_in_this_month = days_in_month(date('m'), date('Y'));
$lastday_in_month = date('Y') . "-" . date('m') . "-" . $days_in_this_month;
$firstday_in_month = date('Y') . "-" . date('m') . "-01";
$this->view_data['invoices_paid_this_month'] = Invoice::count(array('conditions' => 'paid_date <= ' . $lastday_in_month . ' and paid_date >= ' . $firstday_in_month . ' AND estimate != 1'));
$this->view_data['invoices_due_this_month'] = Invoice::count(array('conditions' => 'due_date <= ' . $lastday_in_month . ' and due_date >= ' . $firstday_in_month . ' AND estimate != 1'));
//statistic
$now = time();
$beginning_of_week = strtotime('last Monday', $now);
// BEGINNING of the week
$end_of_week = strtotime('next Sunday', $now) + 86400;
// END of the last day of the week
$this->view_data['invoices_due_this_month_graph'] = Invoice::find_by_sql('select count(id) AS "amount", DATE_FORMAT(`due_date`, "%w") AS "date_day", DATE_FORMAT(`due_date`, "%Y-%m-%d") AS "date_formatted" from invoices where UNIX_TIMESTAMP(`due_date`) >= "' . $beginning_of_week . '" AND UNIX_TIMESTAMP(`due_date`) <= "' . $end_of_week . '" AND estimate != 1');
$this->view_data['invoices_paid_this_month_graph'] = Invoice::find_by_sql('select count(id) AS "amount", DATE_FORMAT(`paid_date`, "%w") AS "date_day", DATE_FORMAT(`paid_date`, "%Y-%m-%d") AS "date_formatted" from invoices where UNIX_TIMESTAMP(`paid_date`) >= "' . $beginning_of_week . '" AND UNIX_TIMESTAMP(`paid_date`) <= "' . $end_of_week . '" AND estimate != 1');
switch ($condition) {
case 'open':
$this->view_data['invoices'] = Invoice::find('all', array('conditions' => array('status = ? and estimate != ?', 'Open', 1)));
break;
case 'sent':
$this->view_data['invoices'] = Invoice::find('all', array('conditions' => array('status = ? and estimate != ?', 'Sent', 1)));
break;
case 'paid':
$this->view_data['invoices'] = Invoice::find('all', array('conditions' => array('status = ? and estimate != ?', 'Paid', 1)));
break;
default:
$this->view_data['invoices'] = Invoice::find('all', array('conditions' => array('estimate != ?', 1)));
break;
}
$this->content_view = 'invoices/all';
}
function index()
{
if ($this->user->admin == 1) {
$settings = Setting::first();
$this->load->helper('curl');
$object = remote_get_contents('http://fc2.luxsys-apps.com/updates/xml.php?code=' . $settings->pc);
$object = json_decode($object);
$this->view_data['update'] = FALSE;
if (isset($object->error)) {
if ($object->error == FALSE && $object->lastupdate > $settings->version) {
$this->view_data['update'] = $object->lastupdate;
}
}
}
$year = date('Y', time());
$this->view_data["year"] = $year;
$thismonth = date('m');
$this->view_data["month"] = date('M');
$tax = $this->view_data['core_settings']->tax;
//calculate stats
$result = Invoice::find_by_sql("SELECT paid_date, SUM(`sum`) AS summary FROM `invoices` WHERE estimate != 1 AND status = 'Paid' AND paid_date between '{$year}-01-01' AND '{$year}-12-31' GROUP BY SUBSTR(paid_date,1,7)");
$this->view_data["stats"] = $result;
//Projects
//open
$this->view_data["projects_open"] = Project::count(array('conditions' => array('progress < ?', 100)));
//all
$this->view_data["projects_all"] = Project::count();
//invoices
//open
$this->view_data["invoices_open"] = Invoice::count(array('conditions' => array('status != ? AND estimate != ?', 'Paid', 1)));
//all
$this->view_data["invoices_all"] = Invoice::count(array('conditions' => array('estimate != ?', 1)));
//payments open
$result = Invoice::find_by_sql("SELECT SUM(`sum`) AS summary FROM `invoices` WHERE estimate != 1 AND status = 'Paid' AND paid_date between '{$year}-{$thismonth}-01' AND '{$year}-{$thismonth}-31'");
$this->view_data["payments"] = $result;
//payments outstanding
$result = Invoice::find_by_sql("SELECT SUM(`sum`) AS summary FROM `invoices` WHERE estimate != 1 AND status != 'Paid'");
$this->view_data["paymentsoutstanding"] = $result;
//Events
$events = array();
$date = date('Y-m-d', time());
$eventcount = 0;
foreach ($this->view_data['menu'] as $key => $value) {
if ($value->link == "invoices") {
$sql = 'SELECT * FROM invoices WHERE status != "Paid" AND due_date < "' . $date . '" AND estimate != 1 ORDER BY due_date';
$res = Invoice::find_by_sql($sql);
//$res = $res->result();
foreach ($res as $key2 => $value2) {
$eventline = str_replace("{invoice_number}", '<a href="' . base_url() . 'invoices/view/' . $value2->id . '">#' . $value2->reference . '</a>', $this->lang->line('event_invoice_overdue'));
$events[$value2->due_date . "." . $value2->id] = $eventline;
$eventcount = $eventcount + 1;
}
}
if ($value->link == "projects") {
$sql = 'SELECT * FROM projects WHERE progress != "100" AND end < "' . $date . '" ORDER BY end';
$res = Project::find_by_sql($sql);
//$res = $res->result();
foreach ($res as $key2 => $value2) {
if ($this->user->admin == 0) {
$sql = "SELECT id FROM `project_has_workers` WHERE project_id = " . $value->id . " AND user_id = " . $this->user->id;
$res = Project::find_by_sql($sql);
//$res = $query;
if ($res) {
$eventline = str_replace("{project_number}", '<a href="' . base_url() . 'projects/view/' . $value2->id . '">#' . $value2->reference . '</a>', $this->lang->line('event_project_overdue'));
$events[$value2->end . "." . $value2->id] = $eventline;
$eventcount = $eventcount + 1;
}
} else {
$eventline = str_replace("{project_number}", '<a href="' . base_url() . 'projects/view/' . $value2->id . '">#' . $value2->reference . '</a>', $this->lang->line('event_project_overdue'));
$events[$value2->end . "." . $value2->id] = $eventline;
$eventcount = $eventcount + 1;
}
}
}
if ($value->link == "subscriptions") {
$sql = 'SELECT * FROM subscriptions WHERE status != "Inactive" AND end_date > "' . $date . '" AND next_payment <= "' . $date . '" ORDER BY next_payment';
$res = Subscription::find_by_sql($sql);
//$res = $res->result();
foreach ($res as $key2 => $value2) {
$eventline = str_replace("{subscription_number}", '<a href="' . base_url() . 'subscriptions/view/' . $value2->id . '">#' . $value2->reference . '</a>', $this->lang->line('event_subscription_new_invoice'));
$events[$value2->next_payment . "." . $value2->id] = $eventline;
$eventcount = $eventcount + 1;
}
}
if ($value->link == "messages") {
$sql = 'SELECT privatemessages.id, privatemessages.`status`, privatemessages.subject, privatemessages.message, privatemessages.`time`, privatemessages.`recipient`, clients.`userpic` as userpic_c, users.`userpic` as userpic_u , users.`email` as email_u , clients.`email` as email_c , CONCAT(users.firstname," ", users.lastname) as sender_u, CONCAT(clients.firstname," ", clients.lastname) as sender_c
FROM privatemessages
LEFT JOIN clients ON CONCAT("c",clients.id) = privatemessages.sender
LEFT JOIN users ON CONCAT("u",users.id) = privatemessages.sender
GROUP by privatemessages.id HAVING privatemessages.recipient = "u' . $this->user->id . '"AND privatemessages.status != "deleted" ORDER BY privatemessages.`time` DESC LIMIT 6';
$query = Privatemessage::find_by_sql($sql);
$this->view_data["message"] = array_filter($query);
}
if ($value->link == "projects") {
$sql = 'SELECT * FROM project_has_tasks WHERE status != "done" AND user_id = "' . $this->user->id . '" ORDER BY project_id';
$taskquery = Project::find_by_sql($sql);
$this->view_data["tasks"] = $taskquery;
}
}
krsort($events);
$this->view_data["events"] = $events;
$this->view_data["eventcount"] = $eventcount;
$this->content_view = 'dashboard/dashboard';
}
