PHP sql_query::generate_sql方法代码示例

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

//.........这里部分代码省略.........
                                 // 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();

//.........这里部分代码省略.........
     $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);
//.........这里部分代码省略.........

 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;
//.........这里部分代码省略.........

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\">";
//.........这里部分代码省略.........

 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();
//.........这里部分代码省略.........

 $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";
//.........这里部分代码省略.........

 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;
 }