function execute()
{
/*
Employee Selection Form
*/
$this->obj_form_employee = new form_input();
$this->obj_form_employee->formname = "timereg_employee";
$this->obj_form_employee->language = $_SESSION["user"]["lang"];
// employee selection box
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
// if there is currently no employee set, and there is only one
// employee in the selection box, automatically select it and update
// the session variables.
if (!$this->employeeid && count($structure["values"]) == 1) {
$this->employeeid = $structure["values"][0];
$_SESSION["form"]["timereg"]["employeeid"] = $structure["values"][0];
}
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["defaultvalue"] = $this->employeeid;
$this->obj_form_employee->add_input($structure);
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form_employee->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->date;
$this->obj_form_employee->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Display";
$this->obj_form_employee->add_input($structure);
if ($this->employeeid) {
/*
DRAW DAY TABLE
We need to display a table showing all time booked for the currently
selected day.
*/
// establish a new table object
$this->obj_table_day = new table();
$this->obj_table_day->language = $_SESSION["user"]["lang"];
$this->obj_table_day->tablename = "timereg_table";
// define all the columns and structure
$this->obj_table_day->add_column("standard", "name_project", "CONCAT_WS(' -- ', projects.code_project, projects.name_project)");
$this->obj_table_day->add_column("standard", "name_phase", "project_phases.name_phase");
$this->obj_table_day->add_column("hourmins", "time_booked", "timereg.time_booked");
$this->obj_table_day->add_column("standard", "description", "timereg.description");
// defaults
$this->obj_table_day->columns = array("name_project", "name_phase", "description", "time_booked");
$this->obj_table_day->columns_order = array("name_project", "name_phase");
// create totals
$this->obj_table_day->total_columns = array("time_booked");
// define SQL
$this->obj_table_day->sql_obj->prepare_sql_settable("timereg");
$this->obj_table_day->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table_day->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table_day->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
$this->obj_table_day->sql_obj->prepare_sql_addwhere("timereg.employeeid = '" . $this->employeeid . "'");
$this->obj_table_day->sql_obj->prepare_sql_addwhere("timereg.date = '" . $this->date . "'");
// execute SQL statement
$this->obj_table_day->generate_sql();
$this->obj_table_day->load_data_sql();
}
}
function invoice_render_summarybox($type, $id)
{
log_debug("inc_invoices", "invoice_render_summarybox({$type}, {$id})");
// fetch invoice information
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_{$type}");
if ($type == "ar") {
$sql_obj->prepare_sql_addfield("date_sent");
$sql_obj->prepare_sql_addfield("sentmethod");
}
$sql_obj->prepare_sql_addfield("code_invoice");
$sql_obj->prepare_sql_addfield("amount_total");
$sql_obj->prepare_sql_addfield("amount_paid");
$sql_obj->prepare_sql_addwhere("id='{$id}'");
$sql_obj->prepare_sql_setlimit("1");
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
// check for presence of invoice items
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT id FROM account_items WHERE invoicetype='{$type}' AND invoiceid='{$id}' LIMIT 1";
$sql_item_obj->execute();
if (!$sql_item_obj->num_rows()) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " has no items on it</b>";
print "<p>This invoice is currently empty, add some items to it using the Invoice Items page.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
if ($sql_obj->data[0]["amount_paid"] == $sql_obj->data[0]["amount_total"]) {
print "<table width=\"100%\" class=\"table_highlight_open\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is closed (fully paid).</b>";
print "<p>This invoice has been fully paid and no further action is required.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is open (unpaid).</b>";
print "<table cellpadding=\"4\">";
print "<tr>";
print "<td>Total Due:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
print "</tr>";
print "<tr>";
print "<td>Total Paid:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_paid"]) . "</td>";
print "</tr>";
$amount_due = $sql_obj->data[0]["amount_total"] - $sql_obj->data[0]["amount_paid"];
print "<tr>";
print "<td>Amount Due:</td>";
print "<td>" . format_money($amount_due) . "</td>";
print "</tr>";
if ($type == "ar") {
print "<tr>";
print "<td>Date Sent:</td>";
if ($sql_obj->data[0]["sentmethod"] == "") {
print "<td><i>Has not been sent to customer</i></td>";
} else {
print "<td>" . $sql_obj->data[0]["date_sent"] . " (" . $sql_obj->data[0]["sentmethod"] . ")</td>";
}
print "</tr>";
}
print "</tr></table>";
print "</td>";
print "</tr>";
print "</table>";
}
}
print "<br>";
}
}
function render_html()
{
// calcuate next/previous week/year
if ($this->date_selected_weekofyear == 1) {
$date_option_previousyear = $this->date_selected_year - 1;
$date_option_previousweek = 52;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = 2;
} elseif ($this->date_selected_weekofyear == 52) {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = 51;
$date_option_nextyear = $this->date_selected_year + 1;
$date_option_nextweek = 1;
} else {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = $this->date_selected_weekofyear - 1;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = $this->date_selected_weekofyear + 1;
}
// Week view header
print "<h3>TIME REGISTRATION</h3><br><br>";
/*
Unbilled Time
*/
if (user_permissions_get("projects_timegroup")) {
/*
Create an array of all unbilled time records. We need to do the following to create this list:
1. Exclude any internal_only projects.
2. Include time which belongs to a time_group, but ONLY if the time group has not been added to an invoice.
*/
$unbilled_ids = array();
// select non-internal projects
$sql_projects_obj = new sql_query();
$sql_projects_obj->string = "SELECT projects.id as projectid, project_phases.id as phaseid FROM project_phases LEFT JOIN projects ON projects.id = project_phases.projectid WHERE projects.internal_only='0'";
$sql_projects_obj->execute();
if ($sql_projects_obj->num_rows()) {
$sql_projects_obj->fetch_array();
foreach ($sql_projects_obj->data as $project_data) {
// select non-group time records
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM timereg WHERE groupid='0' AND phaseid='" . $project_data["phaseid"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_obj);
// select unpaid group IDs
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM time_groups WHERE projectid='" . $project_data["projectid"] . "' AND invoiceid='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_group) {
// fetch all the time reg IDs belonging this group, but only select time entries marked as billable - we
// don't want to report a timegroup with unbillable time as being billed!
$sql_reg_obj = new sql_query();
$sql_reg_obj->string = "SELECT id FROM timereg WHERE groupid='" . $data_group["id"] . "' AND billable='1'";
$sql_reg_obj->execute();
if ($sql_reg_obj->num_rows()) {
$sql_reg_obj->fetch_array();
foreach ($sql_reg_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_reg_obj);
}
}
unset($sql_obj);
}
}
// fetch amount of unbilled time
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
// provide list of valid IDs
$unbilled_ids_keys = array_keys($unbilled_ids);
$unbilled_ids_count = count($unbilled_ids_keys);
$unbilled_ids_sql = "";
if ($unbilled_ids_count) {
$i = 0;
foreach ($unbilled_ids_keys as $id) {
$i++;
if ($i == $unbilled_ids_count) {
$unbilled_ids_sql .= "timereg.id='{$id}' ";
} else {
$unbilled_ids_sql .= "timereg.id='{$id}' OR ";
}
}
$sql_obj->prepare_sql_addwhere("({$unbilled_ids_sql})");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($unbilled_time_hours, $unbilled_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($unbilled_time_hours > 0 && $unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_hours} hours and {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_hours > 0) {
$message = "There are currently {$unbilled_time_hours} hours of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
}
} else {
$message = "There is no unbilled time to be processed.";
}
// display
print "<br>";
format_linkbox("default", "index.php?page=timekeeping/unbilled.php", "<p><b>UNBILLED TIME</b></p><p>{$message}</p>");
}
/*end unbilled time*/
print "<br />";
/*
Time booked
*/
// fetch amount of time booked for today
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
$sql_obj->prepare_sql_addwhere("date='" . date("Y-m-d") . "'");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($booked_time_hours, $booked_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($booked_time_hours > 0 && $booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours and {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} elseif ($booked_time_hours > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours.</b><br />Click here to add more time.";
} elseif ($booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} else {
$message = "<b>No time has been booked for today</b><br />Click here to add time.</b>";
}
format_linkbox("default", "index.php?page=timekeeping/timereg-day-edit.php", "<p>{$message}</p>");
print "<br />";
print "<table class=\"table_highlight\" width=\"100%\"><tr>";
// Week selection links
print "<td width=\"70%\">";
print "<b>WEEK " . $this->date_selected_weekofyear . ", " . $this->date_selected_year . "</b><br>";
print "(" . time_format_humandate($this->date_selected_start) . " to " . time_format_humandate($this->date_selected_end) . ")<br>";
print "<br>";
print "<p><b>";
print "<a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_previousweek . "&year=" . $date_option_previousyear . "\"><< Previous Week</a>";
// check for date in the future
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp(time_calculate_weekstart($date_option_nextweek, $date_option_nextyear)) < time()) {
// end date is in not in the future
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
} else {
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
print "</b></p>";
print "</td>";
// goto date form
print "<td width=\"30%\">";
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
$this->obj_form_goto->render_field("date");
print "<br>";
$this->obj_form_goto->render_field("page");
$this->obj_form_goto->render_field("submit");
print "</form>";
print "</td>";
print "</tr></table><br>";
// Employee selection form
//
// we use a custom form display method here, since the normal form
// class will draw a fully styled form in a table.
//
if ($this->employeeid) {
print "<table class=\"table_highlight\" width=\"100%\"><tr><td width=\"100%\">";
} else {
print "<table class=\"table_highlight_important\" width=\"100%\"><tr><td width=\"100%\">";
}
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
print "<p><b>Select an employee to view:</b></p>";
$this->obj_form_employee->render_field("employeeid");
$this->obj_form_employee->render_field("weekofyear");
$this->obj_form_employee->render_field("year");
$this->obj_form_employee->render_field("page");
$this->obj_form_employee->render_field("submit");
print "</form>";
print "</td></tr></table><br>";
if ($this->employeeid) {
// custom labels and links
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp($this->date_selected_daysofweek[0]) < time()) {
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[1]) < time()) {
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[2]) < time()) {
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[3]) < time()) {
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[4]) < time()) {
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[5]) < time()) {
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[6]) < time()) {
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
} else {
// add links
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
// column labels
$this->obj_table_week->custom_column_label("monday", "Monday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[0]) . ")</font>");
$this->obj_table_week->custom_column_label("tuesday", "Tuesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[1]) . ")</font>");
$this->obj_table_week->custom_column_label("wednesday", "Wednesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[2]) . ")</font>");
$this->obj_table_week->custom_column_label("thursday", "Thursday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[3]) . ")</font>");
$this->obj_table_week->custom_column_label("friday", "Friday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[4]) . ")</font>");
$this->obj_table_week->custom_column_label("saturday", "Saturday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[5]) . ")</font>");
$this->obj_table_week->custom_column_label("sunday", "Sunday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[6]) . ")</font>");
// display week time table
$this->obj_table_week->render_table_html();
print "<table width=\"100%\">";
// add time link
if (user_permissions_staff_get("timereg_write", $this->employeeid)) {
print "<td align=\"left\" valign=\"top\"><p><a class=\"button\" href=\"index.php?page=timekeeping/timereg-day-edit.php\">Add new time entry</a></p></td>";
} else {
print "<p><i>You have read-only access to this employee and therefore can not add any more time.</i></p>";
}
// display CSV/PDF download link
print "<td align=\"right\">";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=csv&page=timekeeping/timereg.php\">Export as CSV</a></p>";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=pdf&page=timekeeping/timereg.php\">Export as PDF</a></p>";
print "</td>";
print "</table>";
}
}
function execute()
{
/*
Create an array of all unbilled time records. We need to do the following to create this list:
1. Exclude any internal_only projects.
2. Include time which belongs to a time_group, but ONLY if the time group has not been added to an invoice.
*/
$unbilled_ids = array();
// select non-internal projects
$sql_projects_obj = new sql_query();
$sql_projects_obj->string = "SELECT projects.id as projectid, project_phases.id as phaseid FROM project_phases LEFT JOIN projects ON projects.id = project_phases.projectid WHERE projects.internal_only='0'";
$sql_projects_obj->execute();
if ($sql_projects_obj->num_rows()) {
$sql_projects_obj->fetch_array();
foreach ($sql_projects_obj->data as $project_data) {
// select non-group time records
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM timereg WHERE groupid='0' AND phaseid='" . $project_data["phaseid"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_obj);
// select unpaid group IDs
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM time_groups WHERE projectid='" . $project_data["projectid"] . "' AND invoiceid='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_group) {
// fetch all the time reg IDs belonging this group, but only select time entries marked as billable - we
// don't want to report a timegroup with unbillable time as being billed!
$sql_reg_obj = new sql_query();
$sql_reg_obj->string = "SELECT id FROM timereg WHERE groupid='" . $data_group["id"] . "' AND billable='1'";
$sql_reg_obj->execute();
if ($sql_reg_obj->num_rows()) {
$sql_reg_obj->fetch_array();
foreach ($sql_reg_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_reg_obj);
}
}
unset($sql_obj);
}
}
/*
Define table
*/
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "timereg_unbilled";
// define all the columns and structure
$this->obj_table->add_column("date", "date", "timereg.date");
$this->obj_table->add_column("standard", "name_phase", "CONCAT_WS(' -- ', projects.code_project, projects.name_project, project_phases.name_phase)");
$this->obj_table->add_column("standard", "name_staff", "CONCAT_WS(' -- ', staff.staff_code, staff.name_staff)");
$this->obj_table->add_column("standard", "time_group", "time_groups.name_group");
$this->obj_table->add_column("standard", "description", "timereg.description");
$this->obj_table->add_column("hourmins", "time_booked", "timereg.time_booked");
// defaults
$this->obj_table->columns = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
$this->obj_table->columns_order = array("date", "name_phase");
$this->obj_table->columns_order_options = array("date", "name_phase", "name_staff", "time_group", "description");
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("timereg");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table->sql_obj->prepare_sql_addfield("projectid", "projects.id");
$this->obj_table->sql_obj->prepare_sql_addfield("employeeid", "timereg.employeeid");
$this->obj_table->sql_obj->prepare_sql_addfield("timegroupid", "time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addfield("timegroupinvoiceid", "time_groups.invoiceid");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN staff ON timereg.employeeid = staff.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
// provide list of valid IDs
$unbilled_ids_keys = array_keys($unbilled_ids);
$unbilled_ids_count = count($unbilled_ids_keys);
$unbilled_ids_sql = "";
if ($unbilled_ids_count) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.id IN (" . format_arraytocommastring($unbilled_ids_keys) . ")");
}
// if the user only has access to specific staff, filter to these staff members
if ($this->access_staff_ids) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
/// Filtering/Display Options
// fixed options
$this->obj_table->add_fixed_option("id", $this->id);
// acceptable filter options
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["sql"] = "date >= 'value'";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["sql"] = "date <= 'value'";
$this->obj_table->add_filter($structure);
$structure = form_helper_prepare_dropdownfromdb("phaseid", "SELECT \n\t\t\t\t\t\t\t\t\t\t\tprojects.code_project as label,\n\t\t\t\t\t\t\t\t\t\t\tprojects.name_project as label1,\n\t\t\t\t\t\t\t\t\t\t\tproject_phases.id as id, \n\t\t\t\t\t\t\t\t\t\t\tproject_phases.name_phase as label1\n\t\t\t\t\t\t\t\t\t\tFROM `projects` \n\t\t\t\t\t\t\t\t\t\tLEFT JOIN project_phases ON project_phases.projectid = projects.id\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\tprojects.internal_only='0'\n\t\t\t\t\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t\t\t\t\tprojects.name_project,\n\t\t\t\t\t\t\t\t\t\t\tproject_phases.name_phase");
$structure["sql"] = "project_phases.id='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
$structure["sql"] = "timereg.employeeid='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "searchbox";
$structure["type"] = "input";
$structure["sql"] = "(timereg.description LIKE '%value%' OR project_phases.name_phase LIKE '%value%' OR staff.name_staff LIKE '%value%')";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "groupby";
$structure["type"] = "radio";
$structure["values"] = array("none", "name_phase", "name_staff");
$structure["defaultvalue"] = "none";
$this->obj_table->add_filter($structure);
// create totals
$this->obj_table->total_columns = array("time_booked");
// load options form
$this->obj_table->load_options_form();
// add group by options
if ($this->obj_table->filter["filter_groupby"]["defaultvalue"] != "none") {
$this->obj_table->sql_obj->prepare_sql_addgroupby($this->obj_table->filter["filter_groupby"]["defaultvalue"]);
// replace timereg value with SUM query
$this->obj_table->structure["time_booked"]["dbname"] = "SUM(timereg.time_booked)";
switch ($this->obj_table->filter["filter_groupby"]["defaultvalue"]) {
case "name_staff":
$this->obj_table->columns = array("name_staff", "time_booked");
$this->obj_table->columns_order = array();
$this->obj_table->columns_order_options = array("name_staff");
break;
case "name_phase":
$this->obj_table->columns = array("name_phase", "time_booked");
$this->obj_table->columns_order = array();
$this->obj_table->columns_order_options = array("name_phase");
break;
}
}
// generate & execute SQL query (only if time entries exist)
$this->obj_table->generate_sql();
if ($unbilled_ids_count) {
$this->obj_table->load_data_sql();
}
// delete any rows which belong to processed time groups
for ($i = 0; $i < $this->obj_table->data_num_rows; $i++) {
if ($this->obj_table->data[$i]["timegroupinvoiceid"]) {
$this->obj_table->data[$i] = NULL;
}
}
}
function execute()
{
/*
Filter selection form
*/
// fetch existing values
$this->date_start = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_start_yyyy"] . "-" . $_GET["date_start_mm"] . "-" . $_GET["date_start_dd"]);
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_end_yyyy"] . "-" . $_GET["date_end_mm"] . "-" . $_GET["date_end_dd"]);
$this->mode = @security_script_input("/^\\S*\$/", $_GET["mode"]);
if (!$this->mode) {
if ($_SESSION["account_reports"]["mode"]) {
$this->mode = $_SESSION["account_reports"]["mode"];
} else {
$this->mode = "Accrual/Invoice";
}
}
if (!$this->date_start || $this->date_start == "--") {
if ($_SESSION["account_reports"]["date_start"]) {
$this->date_start = $_SESSION["account_reports"]["date_start"];
} else {
$this->date_start = NULL;
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = NULL;
}
}
// save to session vars
$_SESSION["account_reports"]["date_start"] = $this->date_start;
$_SESSION["account_reports"]["date_end"] = $this->date_end;
$_SESSION["account_reports"]["mode"] = $this->mode;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_incomestatement";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_start;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// mode selection
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "radio";
$structure["values"] = array("Accrual/Invoice", "Cash");
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
/*
Income Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='5'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_income = $sql_obj->data;
unset($sql_obj);
/*
Expense Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='6'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_expense = $sql_obj->data;
unset($sql_obj);
/*
Amounts
This section needs to total up the the amounts in each account - however, we are unable to just
pull the information from account_trans, because we need to be able to fetch either the invoiced/accural
amount OR the cash (ie: paid) amount.
The reports for taxes can handle it simpler, by just calcuating the invoice item total, but we also have
to include general ledger transactions.
Credit notes add additional complexity, however the solution is the same for both accural/invoice and cash
basis - read all the items for the credit notes for the selected date range, and subtract from the appropiate
account.
Note that it is entirely possible for the credit notes to end up being more than the invoice amount for a selected
time period, in which case, the account may go into negative, which is acceptable.
Accural/Invoice:
1. Run though all invoices
2. Add item amounts to accounts
3. Run through all credit notes during period
4. Subtract credit notes falling during period
5. Run through all general ledger transactions
6. Add GL amounts to accounts
Cash:
1. Run through all invoices
2. If invoices are fully paid, then add item amounts to accounts.
3. Impossible to handle partially paid invoices properly, so we ignore them.
4. Run through all credit notes during period
5. Subtract credit notes falling during period
6. Run through all general ledger transactions
7. Add GL amounts to accounts.
Note: The date checks are made against the invoice date, not the payment date.
*/
//
// AR INVOICES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ar");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// paid invoices only
if ($this->mode == "Cash") {
$sql_obj->prepare_sql_addwhere("amount_total=amount_paid");
}
// run through invoices
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_invoice) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_invoice["id"] . "' AND invoicetype='ar'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_item["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] += $data_item["amount"]);
}
}
// end of loop through charts
}
// end of invoice item loop
}
// end if invoice items
}
// end of invoice loop
}
// end if invoices
unset($sql_obj);
//
// AR CREDIT NOTES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ar_credit");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through credit notes
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_credit) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_credit["id"] . "' AND invoicetype='ar_credit'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_item["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] -= $data_item["amount"]);
}
}
// end of loop through charts
}
// end of credit item loop
}
// end of credit items
}
// end of credit loop
}
// end of credits
unset($sql_obj);
//
// AP INVOICES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ap");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// paid invoices only
if ($this->mode == "Cash") {
$sql_obj->prepare_sql_addwhere("amount_total=amount_paid");
}
// run through invoices
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_invoice) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_invoice["id"] . "' AND invoicetype='ap'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_item["chartid"] == $this->data_expense[$i]["id"]) {
@($this->data_expense[$i]["amount"] += $data_item["amount"]);
}
}
// end of loop through charts
}
// end of invoice item loop
}
// end if invoice items
}
// end of invoice loop
}
// end if invoices
unset($sql_obj);
//
// AP CREDIT NOTES
//
/*
TODO: AP credit notes have not been implemented at this stage - when they are, an appropiate section will need to be added
here to support them.
*/
//
// GL TRANSACTIONS
//
// Fetch all the GL transactions during this period and add to totals.
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_trans");
$sql_obj->prepare_sql_addfield("chartid");
$sql_obj->prepare_sql_addfield("amount_debit");
$sql_obj->prepare_sql_addfield("amount_credit");
$sql_obj->prepare_sql_addwhere("type='gl'");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through GL entries
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_trans) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_trans["chartid"] == $this->data_income[$i]["id"]) {
$this->data_income[$i]["amount"] += $data_trans["amount_credit"];
}
}
// end of loop through income charts
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_trans["chartid"] == $this->data_expense[$i]["id"]) {
$this->data_expense[$i]["amount"] += $data_trans["amount_debit"];
}
}
// end of loop through expense charts
}
// end of transaction loop
}
// end if transaction exist
/*
Totals
*/
// income
$this->data_totals["income"] = 0;
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
@($this->data_totals["income"] += $this->data_income[$i]["amount"]);
}
// expense
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
@($this->data_totals["expense"] += $this->data_expense[$i]["amount"]);
}
// final
$this->data_totals["final"] = @$this->data_totals["income"] - $this->data_totals["expense"];
$this->data_totals["income"] = @format_money($this->data_totals["income"]);
$this->data_totals["expense"] = @format_money($this->data_totals["expense"]);
$this->data_totals["final"] = @format_money($this->data_totals["final"]);
}
function execute()
{
/*
Filter selection form
*/
// fetch existing values
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_as_of_yyyy"] . "-" . $_GET["date_as_of_mm"] . "-" . $_GET["date_as_of_dd"]);
$this->mode = @security_script_input("/^\\S*\$/", $_GET["mode"]);
if (!$this->mode) {
if ($_SESSION["account_reports"]["mode"]) {
$this->mode = $_SESSION["account_reports"]["mode"];
} else {
$this->mode = "Accrual/Invoice";
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = date("Y-m-d");
}
}
// save to session vars
$_SESSION["account_reports"]["date_end"] = $this->date_end;
$_SESSION["account_reports"]["mode"] = $this->mode;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_incomestatement";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_as_of";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// mode selection
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "radio";
$structure["values"] = array("Accrual/Invoice", "Cash");
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
/*
Asset Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='2'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_assets = $sql_obj->data;
unset($sql_obj);
/*
Liability Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='3'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_liabilities = $sql_obj->data;
unset($sql_obj);
/*
Equitity Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='4'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_equity = $sql_obj->data;
unset($sql_obj);
/*
Income Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='5'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_income = $sql_obj->data;
unset($sql_obj);
/*
Expense Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='6'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_expense = $sql_obj->data;
unset($sql_obj);
/*
Amounts
This section fetches the total amounts for the different accounts. This code is a bit different to the invoicestatement code
and instead of working on an invoice basis, works on a transaction basis.
Accural/Invoice:
1. Fetch all transactions from account_trans
2. Total up credits+debits for each account
Cash:
1. Fetch all transactions from account_trans.
2. Total up any ar_pay, ap_pay or gl transactions.
2. For all other transactions, do a lookup against the invoice - if the invoice has been paid at all, (either partially
or fully) then include the transaction.
Note: The behaviour of including partically paid invoces is different to how all the other application features (such as tax
collected/paid) work, however it is required in order to have the balance sheet showing correct tax/income amounts.
This behaviour is also the same as how SQL-Ledger generates balance sheets, which will not confuse users whom have migrated.
*/
// Run through all the transactions
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_trans");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("type");
$sql_obj->prepare_sql_addfield("customid");
$sql_obj->prepare_sql_addfield("chartid");
$sql_obj->prepare_sql_addfield("amount_debit");
$sql_obj->prepare_sql_addfield("amount_credit");
// date options
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through transaction entries
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_trans) {
log_debug("balancesheet", "Processing transaction " . $data_trans["id"] . " with type " . $data_trans["type"] . "");
$valid = 0;
if ($this->mode == "Cash") {
// CASH
if ($data_trans["type"] == "ar_pay" || $data_trans["type"] == "ap_pay" || $data_trans["type"] == "gl") {
$valid = 1;
} else {
// check if the transaction invoice has any payments or not
$sql_invoice_obj = new sql_query();
if ($data_trans["type"] == "ap" || $data_trans["type"] == "ap_tax") {
$sql_invoice_obj->prepare_sql_settable("account_ap");
} else {
$sql_invoice_obj->prepare_sql_settable("account_ar");
}
$sql_invoice_obj->prepare_sql_addfield("amount_paid");
$sql_invoice_obj->prepare_sql_addwhere("id='" . $data_trans["customid"] . "'");
$sql_invoice_obj->prepare_sql_setlimit("1");
$sql_invoice_obj->generate_sql();
$sql_invoice_obj->execute();
if ($sql_invoice_obj->num_rows()) {
$sql_invoice_obj->fetch_array();
if ($sql_invoice_obj->data[0]["amount_paid"] > 0) {
// invoice has some amount of payment against it, and should therefore be displayed.
$valid = 1;
}
} else {
log_write("error", "balancesheet", "Unable to find parent invoice (" . $data_trans["customid"] . ") for transaction " . $data_trans["id"] . " - Database might be damanged.");
}
unset($sql_invoice_obj);
}
} else {
// ACCURAL/INVOICE
$valid = 1;
}
if ($valid) {
log_debug("balancesheet", "Transaction is valid - chartid: " . $data_trans["chartid"] . ", credit: " . $data_trans["amount_credit"] . ", debit: " . $data_trans["amount_debit"] . "");
// run through asset charts
for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
if ($data_trans["chartid"] == $this->data_assets[$i]["id"]) {
@($this->data_assets[$i]["amount"] += $data_trans["amount_debit"]);
@($this->data_assets[$i]["amount"] -= $data_trans["amount_credit"]);
}
}
// end of loop through asset charts
// run through liability charts
for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
if ($data_trans["chartid"] == $this->data_liabilities[$i]["id"]) {
@($this->data_liabilities[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_liabilities[$i]["amount"] += $data_trans["amount_credit"]);
}
}
// end of loop through liability charts
// run through equity charts
if (isset($this->data_equity)) {
for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
if ($data_trans["chartid"] == $this->data_equity[$i]["id"]) {
@($this->data_equity[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_equity[$i]["amount"] += $data_trans["amount_credit"]);
}
}
}
// end of loop through equity charts
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_trans["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_income[$i]["amount"] += $data_trans["amount_credit"]);
}
}
// end of loop through income charts
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_trans["chartid"] == $this->data_expense[$i]["id"]) {
@($this->data_expense[$i]["amount"] += $data_trans["amount_debit"]);
@($this->data_expense[$i]["amount"] -= $data_trans["amount_credit"]);
}
}
// end of loop through expense charts
}
// end if valid
}
// end of transaction loop
}
// end if transaction exist
/*
Totals
*/
// assets
if ($this->data_assets) {
for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
@($this->data_totals["assets"] += $this->data_assets[$i]["amount"]);
}
}
// liabilities
if ($this->data_liabilities) {
for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
@($this->data_totals["liabilities"] += $this->data_liabilities[$i]["amount"]);
}
}
// equity
if ($this->data_equity) {
for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
@($this->data_totals["equity"] += $this->data_equity[$i]["amount"]);
}
}
// income
if ($this->data_income) {
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
@($this->data_totals["income"] += $this->data_income[$i]["amount"]);
}
}
// expense
if ($this->data_expense) {
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
@($this->data_totals["expense"] += $this->data_expense[$i]["amount"]);
}
}
// final
$this->data_totals["current_earnings"] = $this->data_totals["income"] - $this->data_totals["expense"];
$this->data_totals["equity"] += $this->data_totals["current_earnings"];
$this->data_totals["liabilities_and_equity"] = $this->data_totals["liabilities"] + $this->data_totals["equity"];
// formatting
$this->data_totals["liabilities"] = format_money($this->data_totals["liabilities"]);
$this->data_totals["assets"] = format_money($this->data_totals["assets"]);
$this->data_totals["equity"] = format_money($this->data_totals["equity"]);
$this->data_totals["current_earnings"] = format_money($this->data_totals["current_earnings"]);
$this->data_totals["liabilities_and_equity"] = format_money($this->data_totals["liabilities_and_equity"]);
}
function credit_render_invoiceselect($type, $id, $processpage)
{
log_debug("inc_credits", "credit_render_summarybox({$type}, {$id})");
// fetch credit information
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_{$type}");
if ($type == "ar_credit") {
$sql_obj->prepare_sql_addfield("date_sent");
$sql_obj->prepare_sql_addfield("sentmethod");
}
$sql_obj->prepare_sql_addfield("code_credit");
$sql_obj->prepare_sql_addfield("amount_total");
$sql_obj->prepare_sql_addfield("invoiceid");
$sql_obj->prepare_sql_addfield("locked");
$sql_obj->prepare_sql_addwhere("id='{$id}'");
$sql_obj->prepare_sql_setlimit("1");
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if ($sql_obj->data[0]["locked"]) {
// credit note is locked, nothing todo
return 1;
}
/*
Select Invoice Items
*/
$invoice_type = "unknown";
if ($type == "ar_credit") {
$invoice_type = "ar";
} elseif ($type == "ap_credit") {
$invoice_type = "ap";
}
$sql_invoice_obj = new sql_query();
$sql_invoice_obj->string = "SELECT id as itemid, type, customid, chartid, quantity, units, price, amount, description FROM account_items WHERE invoiceid='" . $sql_obj->data[0]["invoiceid"] . "' AND invoicetype='" . $invoice_type . "' AND type!='payment' AND type!='tax'";
$sql_invoice_obj->execute();
if ($sql_invoice_obj->num_rows()) {
$sql_invoice_obj->fetch_array();
}
/*
Create Form
*/
$obj_invoice_form = new form_input();
$obj_invoice_form->formname = $type . "_invoiceselect";
$obj_invoice_form->language = $_SESSION["user"]["lang"];
$obj_invoice_form->action = "index.php";
$obj_invoice_form->method = "GET";
// ID
$structure = NULL;
$structure["fieldname"] = "id";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $id;
$obj_invoice_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $processpage;
$obj_invoice_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "submit_add_credit_item";
$obj_invoice_form->add_input($structure);
/*
Generate Items Radio Array
*/
if ($sql_invoice_obj->num_rows()) {
$structure = NULL;
$structure["fieldname"] = "invoice_item";
$structure["type"] = "radio";
foreach ($sql_invoice_obj->data as $data_invoice) {
$description = $data_invoice["description"];
switch ($data_invoice["type"]) {
case "standard":
$description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data_invoice["chartid"] . "' LIMIT 1");
break;
case "product":
$description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_product, name_product) as value FROM products WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
break;
case "service":
case "service_usage":
$description = sql_get_singlevalue("SELECT name_service as value FROM services WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
break;
default:
$description = "unknown item";
break;
}
$description .= " <i>" . $data_invoice["description"] . "</i>";
$description .= " [" . format_money($data_invoice["amount"]) . " exc tax]";
$structure["values"][] = $data_invoice["itemid"];
$structure["translations"][$data_invoice["itemid"]] = $description;
}
$obj_invoice_form->add_input($structure);
}
/*
Render Form
*/
if ($sql_invoice_obj->num_rows()) {
print "<table width=\"100%\" class=\"table_highlight_info\">";
print "<tr>";
print "<td>";
print "<p><b>Select an item to be credited from the selected invoice - note that amounts can be varied once selected:</b></p>";
print "<form method=\"" . $obj_invoice_form->method . "\" action=\"" . $obj_invoice_form->action . "\">";
$obj_invoice_form->render_field("invoice_item");
print "<br>";
$obj_invoice_form->render_field("id");
$obj_invoice_form->render_field("page");
$obj_invoice_form->render_field("submit");
print "</form>";
print "</td>";
print "</tr>";
print "</table>";
} else {
/*
No invoice items!
*/
format_msgbox("important", "<p>Unable to add any items to this credit note - the selected invoice has no items on it.</p>");
}
print "<br>";
}
}
function execute()
{
/*
Date selection form
*/
// fetch existing dates
$this->date_start = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_start_yyyy"] . "-" . $_GET["date_start_mm"] . "-" . $_GET["date_start_dd"]);
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_end_yyyy"] . "-" . $_GET["date_end_mm"] . "-" . $_GET["date_end_dd"]);
if (!$this->date_start || $this->date_start == "--") {
if ($_SESSION["account_reports"]["date_start"]) {
$this->date_start = $_SESSION["account_reports"]["date_start"];
} else {
$this->date_start = NULL;
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = NULL;
}
}
// save to session vars
$_SESSION["account_reports"]["date_start"] = $this->date_start;
$_SESSION["account_reports"]["date_end"] = $this->date_end;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_trialbalance";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_start;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "accounts_reports_trialbalance";
// define all the columns and structure
$this->obj_table->add_column("standard", "code_chart", "account_charts.code_chart");
$this->obj_table->add_column("standard", "description", "account_charts.description");
$this->obj_table->add_column("standard", "chart_type", "account_chart_type.value");
// the debit and credit columns need to be calculated by a seporate query
$this->obj_table->add_column("price", "debit", "NONE");
$this->obj_table->add_column("price", "credit", "NONE");
// defaults
$this->obj_table->columns = array("code_chart", "description", "chart_type", "debit", "credit");
$this->obj_table->columns_order = array("code_chart");
// totals
$this->obj_table->total_columns = array("debit", "credit");
$this->obj_table->total_rows = array("debit", "credit");
$this->obj_table->total_rows_mode = "subtotal_nofinal";
// this is actually re-calculated based on chart type
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("account_charts");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "account_charts.id");
$this->obj_table->sql_obj->prepare_sql_addfield("chart_total_mode", "account_chart_type.total_mode");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN account_chart_type ON account_chart_type.id = account_charts.chart_type");
$this->obj_table->sql_obj->prepare_sql_addwhere("account_charts.chart_type != '1'");
// fetch all the chart information
$this->obj_table->generate_sql();
$this->obj_table->load_data_sql();
// fetch debit and credit summaries for all charts in advance - this
// is better than running a query per chart just to get all the totals
$sql_amount_obj = new sql_query();
$sql_amount_obj->prepare_sql_settable("account_trans");
$sql_amount_obj->prepare_sql_addfield("chartid");
$sql_amount_obj->prepare_sql_addfield("credit", "SUM(amount_credit)");
$sql_amount_obj->prepare_sql_addfield("debit", "SUM(amount_debit)");
if ($this->date_start) {
$sql_amount_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_amount_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
$sql_amount_obj->prepare_sql_addgroupby("chartid");
$sql_amount_obj->generate_sql();
$sql_amount_obj->execute();
if ($sql_amount_obj->num_rows()) {
$sql_amount_obj->fetch_array();
// run through all the chart rows and fill in the credit/debit fields
for ($i = 0; $i < count(array_keys($this->obj_table->data)); $i++) {
foreach ($sql_amount_obj->data as $data_amount) {
if ($data_amount["chartid"] == $this->obj_table->data[$i]["id"]) {
$this->obj_table->data[$i]["debit"] = $data_amount["debit"];
$this->obj_table->data[$i]["credit"] = $data_amount["credit"];
}
}
}
}
// process table data
$this->obj_table->render_table_prepare();
// correctly generate the total column
for ($i = 0; $i < count(array_keys($this->obj_table->data)); $i++) {
if (!empty($this->obj_table->data[$i]["debit"]) && !empty($this->obj_table->data[$i]["debit"])) {
if ($this->obj_table->data[$i]["chart_total_mode"] == "credit") {
$this->obj_table->data[$i]["total"] = $this->obj_table->data[$i]["credit"] - $this->obj_table->data[$i]["debit"];
} else {
$this->obj_table->data[$i]["total"] = $this->obj_table->data[$i]["debit"] - $this->obj_table->data[$i]["credit"];
}
$this->obj_table->data_render[$i]["total"] = format_money($this->obj_table->data[$i]["total"]);
}
}
$this->obj_table->data["total"]["total"] = $this->obj_table->data["total"]["debit"] - $this->obj_table->data["total"]["credit"];
$this->obj_table->data_render["total"]["total"] = format_money($this->obj_table->data["total"]["total"]);
}
$sql_entries_obj->prepare_sql_settable("timereg");
$sql_entries_obj->prepare_sql_addfield("id", "");
$sql_entries_obj->prepare_sql_addfield("locked", "");
$sql_entries_obj->prepare_sql_addfield("groupid", "");
$sql_entries_obj->prepare_sql_addfield("billable", "");
$sql_entries_obj->prepare_sql_addfield("time_booked", "");
if ($groupid) {
$sql_entries_obj->prepare_sql_addwhere("(groupid='{$groupid}' OR !groupid)");
} else {
$sql_entries_obj->prepare_sql_addwhere("!groupid");
}
// if user has limited employee access, only process time records for those employees
if ($access_staff_ids) {
$sql_entries_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($access_staff_ids) . ")");
}
$sql_entries_obj->generate_sql();
$sql_entries_obj->execute();
if ($sql_entries_obj->num_rows()) {
$sql_entries_obj->fetch_array();
foreach ($sql_entries_obj->data as $entries_data) {
// only get the data for selected time entries
if ($_POST["time_" . $entries_data["id"] . "_bill"] == "on") {
$data["time_entries"][$entries_data["id"]]["billable"] = 1;
} elseif ($_POST["time_" . $entries_data["id"] . "_nobill"] == "on") {
$data["time_entries"][$entries_data["id"]]["billable"] = 0;
}
// save to session array
$_SESSION["error"]["time_" . $entries_data["id"] . "_bill"] = $_POST["time_" . $entries_data["id"] . "_bill"];
$_SESSION["error"]["time_" . $entries_data["id"] . "_nobill"] = $_POST["time_" . $entries_data["id"] . "_nobill"];
}
}
function execute()
{
/*
Check if time entry can be adjusted
*/
if ($this->id) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT locked, groupid FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
$this->locked = $sql_obj->data[0]["locked"];
// so we can tell if the time is locked
$this->groupid = $sql_obj->data[0]["groupid"];
// tells us what group id the time belongs to
}
unset($sql_obj);
}
/*
Input Form
Allows the creation of a new entry for the day, or the adjustment of an existing one.
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "timereg_day";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "timekeeping/timereg-day-edit-process.php";
$this->obj_form->method = "post";
// hidden stuff
$structure = NULL;
$structure["fieldname"] = "id_timereg";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
// employee selection box
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids_write) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids_write) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
// if there is currently no employee set, and there is only one
// employee in the selection box, automatically select it and update
// the session variables.
if (!$this->employeeid && count($structure["values"]) == 1) {
$this->employeeid = $structure["values"][0];
$_SESSION["form"]["timereg"]["employeeid"] = $structure["values"][0];
}
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "yes";
$structure["defaultvalue"] = $this->employeeid;
$this->obj_form->add_input($structure);
// general
$structure = NULL;
$structure["fieldname"] = "date";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date;
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "time_booked";
$structure["type"] = "hourmins";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "60";
$this->obj_form->add_input($structure);
//project dropdown
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("projects");
$sql_struct_obj->prepare_sql_addfield("id", "projects.id");
$sql_struct_obj->prepare_sql_addfield("label", "projects.code_project");
$sql_struct_obj->prepare_sql_addfield("label1", "projects.name_project");
$sql_struct_obj->prepare_sql_addorderby("code_project");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("projectid", $sql_struct_obj);
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "yes";
if (count($structure["values"]) == 0) {
$structure["defaultvalue"] = "You need to create a project and add a phase to it in order to be able to book time.";
$_SESSION["error"]["phaseid-error"] = 1;
}
$this->obj_form->add_input($structure);
//phase dropdown
$structure = NULL;
$structure["fieldname"] = "phaseid";
$structure["type"] = "dropdown";
$structure["values"] = array("");
$structure["options"]["width"] = "600";
$structure["options"]["disabled"] = "yes";
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
//add project field
$structure = NULL;
$structure["fieldname"] = "add_project";
$structure["type"] = "input";
$structure["options"]["no_fieldname"] = "yes";
$structure["options"]["no_shift"] = "yes";
$structure["options"]["prelabel"] = "<div id=\"add_project_box\"><span id=\"toggle_add_project\">\n\t\t\t\t\t\t\t\t<strong>Add New Project</strong>\n\t\t\t\t\t\t\t\t<div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\tNew Project: ";
$structure["options"]["label"] = " <a class=\"insert_project_phase button_small\" id=\"insert_project\" href=\"\">Add</a>\n\t\t\t\t\t\t\t\t</span><div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\t<strong><a id=\"project_add_cancel\" class=\"add_link\" href=\"\">Add New Project</a></strong></div>";
$this->obj_form->add_input($structure);
//add phase field
$structure = NULL;
$structure["fieldname"] = "add_phase";
$structure["type"] = "input";
$structure["options"]["no_fieldname"] = "yes";
$structure["options"]["no_shift"] = "yes";
$structure["options"]["prelabel"] = "<div id=\"add_phase_box\"><span id=\"toggle_add_phase\">\n\t\t\t\t\t\t\t\t<strong>Add Phase to Current Project</strong>\n\t\t\t\t\t\t\t\t<div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\tNew Phase: ";
$structure["options"]["label"] = " <a class=\"insert_project_phase button_small\" id=\"insert_phase\" href=\"\">Add</a>\n\t\t\t\t\t\t\t\t</span><div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\t<strong><a id=\"phase_add_cancel\" class=\"add_link\" href=\"\">Add Phase to Current Project</a></strong></div>";
$this->obj_form->add_input($structure);
// submit section
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// define subforms
if (user_permissions_get("projects_write")) {
$this->obj_form->subforms["timereg_day"] = array("employeeid", "projectid", "phaseid", "add_project", "add_phase", "date", "time_booked", "description");
} else {
$this->obj_form->subforms["timereg_day"] = array("employeeid", "projectid", "phaseid", "date", "time_booked", "description");
}
$this->obj_form->subforms["hidden"] = array("id_timereg");
if ($this->locked) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("submit");
}
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
// fetch the form data
$this->obj_form->sql_query = "SELECT * FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$this->obj_form->load_data();
$this->obj_form->structure['projectid']['defaultvalue'] = sql_get_singlevalue("SELECT project_phases.projectid AS value FROM timereg LEFT JOIN project_phases ON project_phases.id = timereg.phaseid WHERE timereg.id='" . $this->id . "'");
} else {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
function execute()
{
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "timereg_table";
// define all the columns and structure
$this->obj_table->add_column("date", "date", "timereg.date");
$this->obj_table->add_column("standard", "name_phase", "project_phases.name_phase");
$this->obj_table->add_column("standard", "name_staff", "CONCAT_WS(' -- ', staff.staff_code, staff.name_staff)");
$this->obj_table->add_column("standard", "time_group", "time_groups.name_group");
$this->obj_table->add_column("standard", "description", "timereg.description");
$this->obj_table->add_column("hourmins", "time_booked", "timereg.time_booked");
// defaults
$this->obj_table->columns = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
$this->obj_table->columns_order = array("date", "name_phase");
$this->obj_table->columns_order_options = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("timereg");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table->sql_obj->prepare_sql_addfield("employeeid", "timereg.employeeid");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN staff ON timereg.employeeid = staff.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
$this->obj_table->sql_obj->prepare_sql_addwhere("projects.id = '" . $this->id . "'");
if ($this->access_staff_ids) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
/// Filtering/Display Options
// fixed options
$this->obj_table->add_fixed_option("id", $this->id);
// acceptable filter options
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["sql"] = "date >= 'value'";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["sql"] = "date <= 'value'";
$this->obj_table->add_filter($structure);
$structure = form_helper_prepare_dropdownfromdb("phaseid", "SELECT id, name_phase as label FROM project_phases WHERE projectid='" . $this->id . "' ORDER BY name_phase ASC");
$structure["sql"] = "project_phases.id='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
$structure["sql"] = "timereg.employeeid='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "no_group";
$structure["type"] = "checkbox";
$structure["sql"] = "groupid='0'";
$structure["options"]["label"] = "Only show unprocessed time";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "searchbox";
$structure["type"] = "input";
$structure["sql"] = "(timereg.description LIKE '%value%' OR project_phases.name_phase LIKE '%value%' OR staff.name_staff LIKE '%value%')";
$this->obj_table->add_filter($structure);
// create totals
$this->obj_table->total_columns = array("time_booked");
// load options form
$this->obj_table->load_options_form();
// generate & execute SQL query
$this->obj_table->generate_sql();
$this->obj_table->load_data_sql();
}
function load_data_all()
{
log_debug("attributes", "Executing load_data_all()");
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("attributes");
$sql_obj->prepare_sql_addjoin("left join attributes_group on attributes.id_group = attributes_group.id");
$sql_obj->prepare_sql_addfield("attributes.id");
$sql_obj->prepare_sql_addfield("id_owner");
$sql_obj->prepare_sql_addfield("id_group");
$sql_obj->prepare_sql_addfield("group_name");
$sql_obj->prepare_sql_addfield("type");
$sql_obj->prepare_sql_addfield("`key`");
$sql_obj->prepare_sql_addfield("value");
if ($this->id_owner) {
$sql_obj->prepare_sql_addwhere("id_owner='" . $this->id_owner . "'");
}
if ($this->id_group) {
$sql_obj->prepare_sql_addwhere("id_owner='" . $this->id_group . "'");
}
if ($this->type) {
$sql_obj->prepare_sql_addwhere("type='" . $this->type . "'");
}
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
$this->data = $sql_obj->data;
return 1;
}
// failure
return 0;
}
