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"]);
}
