PHP sql_query类代码示例

 function check_requirements()
 {
     // verify that the service exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id, id_rate_table FROM services WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if ($sql_obj->num_rows()) {
         $sql_obj->fetch_array();
         // verify the rate is valid
         if ($sql_obj->data[0]["id_rate_table"]) {
             $this->obj_cdr_rate_table->id = $sql_obj->data[0]["id_rate_table"];
             if (!$this->obj_cdr_rate_table->verify_id()) {
                 log_write("error", "page_output", "The requested CDR rate table is invalid, there may be some problems with the information in the database.");
                 return 0;
             }
         } else {
             log_write("error", "page_output", "You have yet to set a CDR Rate Table for this service to use - please do so using the plan page before attempting to override the rates");
             return 0;
         }
     } else {
         log_write("error", "page_output", "The requested service (" . $this->id . ") does not exist - possibly the service has been deleted.");
         return 0;
     }
     unset($sql_obj);
     // verify that this is a phone service
     if ($this->service_type != ("phone_single" || "phone_trunk" || "phone_tollfree")) {
         log_write("error", "page_output", "The requested service is not a phone service.");
         return 0;
     }
     return 1;
 }

 function check_requirements()
 {
     // verify that the invoice exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM account_ar WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested invoice (" . $this->id . ") does not exist - possibly the invoice has been deleted.");
         return 0;
     }
     unset($sql_obj);
     // verify that the item id supplied exists and fetch required information
     if ($this->itemid) {
         $sql_obj = new sql_query();
         $sql_obj->string = "SELECT id, type FROM account_items WHERE id='" . $this->itemid . "' AND invoiceid='" . $this->id . "' LIMIT 1";
         $sql_obj->execute();
         if (!$sql_obj->num_rows()) {
             log_write("error", "page_output", "The requested payment/invoice combination does not exist. Are you trying to use a link to a deleted payment?");
             return 0;
         } else {
             $sql_obj->fetch_array();
             $this->item_type = $sql_obj->data[0]["type"];
         }
     }
     return 1;
 }

 function check_requirements()
 {
     // verify that project exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM projects WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested project (" . $this->id . ") does not exist - possibly the project has been deleted.");
         return 0;
     }
     unset($sql_obj);
     // verify that the time group exists and belongs to this project
     if ($this->groupid) {
         $sql_obj = new sql_query();
         $sql_obj->string = "SELECT projectid, locked FROM time_groups WHERE id='" . $this->groupid . "' LIMIT 1";
         $sql_obj->execute();
         if (!$sql_obj->num_rows()) {
             log_write("error", "page_output", "The requested time group (" . $this->groupid . ") does not exist - possibly the time group has been deleted.");
             return 0;
         } else {
             $sql_obj->fetch_array();
             $this->locked = $sql_obj->data[0]["locked"];
             if ($sql_obj->data[0]["projectid"] != $this->id) {
                 log_write("error", "page_output", "The requested time group (" . $this->groupid . ") does not belong to the selected project (" . $this->id . ")");
                 return 0;
             }
         }
         unset($sql_obj);
     }
     return 1;
 }

 function list_taxes()
 {
     log_debug("accounts_taxes_manage_soap", "Executing list_taxes()");
     if (user_permissions_get("accounts_taxes_view")) {
         // fetch taxes
         $sql_obj = new sql_query();
         $sql_obj->string = "SELECT id, name_tax, taxrate, chartid, taxnumber, description FROM account_taxes";
         $sql_obj->execute();
         $return = NULL;
         if ($sql_obj->num_rows()) {
             $sql_obj->fetch_array();
             // package data into array for passing back to SOAP client
             foreach ($sql_obj->data as $data) {
                 $return_tmp = NULL;
                 $return_tmp["id"] = $data["id"];
                 $return_tmp["name_tax"] = $data["name_tax"];
                 $return_tmp["taxrate"] = $data["taxrate"];
                 $return_tmp["chartid"] = $data["chartid"];
                 $return_tmp["chartid_label"] = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data["chartid"] . "'");
                 $return_tmp["taxnumber"] = $data["taxnumber"];
                 $return_tmp["description"] = $data["description"];
                 $return[] = $return_tmp;
             }
         }
         return $return;
     } else {
         throw new SoapFault("Sender", "ACCESS_DENIED");
     }
 }

 function execute()
 {
     /*
     	Define form structure
     */
     $this->obj_form = new form_input();
     $this->obj_form->formname = "users_permissions_staff";
     $this->obj_form->language = $_SESSION["user"]["lang"];
     $this->obj_form->action = "user/user-staffaccess-edit-process.php";
     $this->obj_form->method = "post";
     // run through all the avaliable permissions
     $sql_perms_obj = new sql_query();
     $sql_perms_obj->string = "SELECT * FROM `permissions_staff`";
     $sql_perms_obj->execute();
     if ($sql_perms_obj->num_rows()) {
         $sql_perms_obj->fetch_array();
         foreach ($sql_perms_obj->data as $data_perms) {
             // define the checkbox
             $structure = NULL;
             $structure["fieldname"] = $data_perms["value"];
             $structure["type"] = "checkbox";
             $structure["options"]["label"] = $data_perms["description"];
             // check the database to see if this checkbox is selected
             $sql_obj = new sql_query();
             $sql_obj->string = "SELECT " . "id " . "FROM `users_permissions_staff` " . "WHERE " . "userid='" . $this->id . "' " . "AND permid='" . $data_perms["id"] . "' " . "AND staffid='" . $this->staffid . "'";
             $sql_obj->execute();
             if ($sql_obj->num_rows()) {
                 $structure["defaultvalue"] = "on";
             }
             // add checkbox
             $this->obj_form->add_input($structure);
             // add checkbox to subforms
             $this->obj_form->subforms["user_permissions_staff"][] = $data_perms["value"];
         }
     }
     // hidden fields
     $structure = NULL;
     $structure["fieldname"] = "id_user";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $this->id;
     $this->obj_form->add_input($structure);
     $structure = NULL;
     $structure["fieldname"] = "id_staff";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $this->staffid;
     $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
     $this->obj_form->subforms["hidden"] = array("id_user", "id_staff");
     $this->obj_form->subforms["submit"] = array("submit");
     /*
     	Note: We don't load from error data, since there should never
     	be any errors when using this form.
     */
 }

function quotes_render_summarybox($id)
{
    log_debug("inc_quotes", "quotes_render_summarybox({$id})");
    // fetch quote information
    $sql_obj = new sql_query();
    $sql_obj->string = "SELECT code_quote, amount_total, date_validtill, date_sent, sentmethod FROM account_quotes WHERE id='{$id}' LIMIT 1";
    $sql_obj->execute();
    if ($sql_obj->num_rows()) {
        $sql_obj->fetch_array();
        if ($sql_obj->data[0]["amount_total"] == 0) {
            print "<table width=\"100%\" class=\"table_highlight_important\">";
            print "<tr>";
            print "<td>";
            print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " has no items on it</b>";
            print "<p>This quote needs to have some items added to it using the links in the nav menu above.</p>";
            print "</td>";
            print "</tr>";
            print "</table>";
        } else {
            if (time_date_to_timestamp($sql_obj->data[0]["date_validtill"]) <= time()) {
                print "<table width=\"100%\" class=\"table_highlight_important\">";
                print "<tr>";
                print "<td>";
                print "<p><b>Quote " . $sql_obj->data[0]["code_quote"] . " has now expired and is no longer valid.</b></p>";
                print "</td>";
                print "</tr>";
                print "</table>";
            } else {
                print "<table width=\"100%\" class=\"table_highlight_important\">";
                print "<tr>";
                print "<td>";
                print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " is currently valid.</b>";
                print "<table cellpadding=\"4\">";
                print "<tr>";
                print "<td>Quote Total:</td>";
                print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
                print "</tr>";
                print "<tr>";
                print "<td>Valid Until:</td>";
                print "<td>" . $sql_obj->data[0]["date_validtill"] . "</td>";
                print "</tr>";
                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 execute()
 {
     /*
     	Define form structure
     */
     $this->obj_form = new form_input();
     $this->obj_form->formname = "users_permissions_staff";
     $this->obj_form->language = $_SESSION["user"]["lang"];
     $this->obj_form->action = "user/user-staffaccess-edit-process.php";
     $this->obj_form->method = "post";
     // staff member dropdown
     $structure = form_helper_prepare_dropdownfromdb("id_staff", "SELECT id, staff_code as label, name_staff as label1 FROM `staff` ORDER BY name_staff");
     $this->obj_form->add_input($structure);
     $this->obj_form->subforms["user_permissions_selectstaff"] = array("id_staff");
     /*
     	Permissions sub-form
     */
     // run through all the avaliable permissions
     $sql_perms_obj = new sql_query();
     $sql_perms_obj->string = "SELECT * FROM `permissions_staff`";
     $sql_perms_obj->execute();
     if ($sql_perms_obj->num_rows()) {
         $sql_perms_obj->fetch_array();
         foreach ($sql_perms_obj->data as $data_perms) {
             // define the checkbox
             $structure = NULL;
             $structure["fieldname"] = $data_perms["value"];
             $structure["type"] = "checkbox";
             $structure["options"]["label"] = $data_perms["description"];
             // add checkbox
             $this->obj_form->add_input($structure);
             // add checkbox to subforms
             $this->obj_form->subforms["user_permissions_staff"][] = $data_perms["value"];
         }
     }
     // hidden fields
     $structure = NULL;
     $structure["fieldname"] = "id_user";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $this->id;
     $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
     $this->obj_form->subforms["hidden"] = array("id_user");
     $this->obj_form->subforms["submit"] = array("submit");
     /*
     	Note: We don't load from error data, since there should never
     	be any errors when using this form.
     */
 }

 function execute()
 {
     /*
     	Define form structure
     */
     $this->obj_form = new form_input();
     $this->obj_form->formname = "user_permissions";
     $this->obj_form->language = $_SESSION["user"]["lang"];
     $this->obj_form->action = "user/user-permissions-process.php";
     $this->obj_form->method = "post";
     $sql_perms_obj = new sql_query();
     $sql_perms_obj->string = "SELECT * FROM `permissions` ORDER BY value='disabled' DESC, value='admin' DESC, value";
     $sql_perms_obj->execute();
     $sql_perms_obj->fetch_array();
     foreach ($sql_perms_obj->data as $data_perms) {
         // define the checkbox
         $structure = NULL;
         $structure["fieldname"] = $data_perms["value"];
         $structure["type"] = "checkbox";
         $structure["options"]["label"] = $data_perms["description"];
         $structure["options"]["no_translate_fieldname"] = "yes";
         // check if the user has this permission
         $sql_obj = new sql_query();
         $sql_obj->string = "SELECT id FROM `users_permissions` WHERE userid='" . $this->id . "' AND permid='" . $data_perms["id"] . "'";
         $sql_obj->execute();
         if ($sql_obj->num_rows()) {
             $structure["defaultvalue"] = "on";
         }
         // add checkbox
         $this->obj_form->add_input($structure);
         // add checkbox to subforms
         $this->obj_form->subforms["user_permissions"][] = $data_perms["value"];
     }
     // user ID (hidden field)
     $structure = NULL;
     $structure["fieldname"] = "id_user";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $this->id;
     $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
     $this->obj_form->subforms["hidden"] = array("id_user");
     $this->obj_form->subforms["submit"] = array("submit");
     /*
     	Note: We don't load from error data, since there should never
     	be any errors when using this form.
     */
 }

 function execute()
 {
     // make sure tax does not belong to any invoices
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM account_items WHERE type='tax' AND customid='" . $this->id . "'";
     $sql_obj->execute();
     if ($sql_obj->num_rows()) {
         $this->locked = 1;
     }
     /*
     	Define form structure
     */
     $this->obj_form = new form_input();
     $this->obj_form->formname = "tax_delete";
     $this->obj_form->language = $_SESSION["user"]["lang"];
     $this->obj_form->action = "accounts/taxes/delete-process.php";
     $this->obj_form->method = "post";
     // general
     $structure = NULL;
     $structure["fieldname"] = "name_tax";
     $structure["type"] = "text";
     $this->obj_form->add_input($structure);
     // hidden
     $structure = NULL;
     $structure["fieldname"] = "id_tax";
     $structure["type"] = "hidden";
     $structure["defaultvalue"] = $this->id;
     $this->obj_form->add_input($structure);
     // confirm delete
     $structure = NULL;
     $structure["fieldname"] = "delete_confirm";
     $structure["type"] = "checkbox";
     $structure["options"]["label"] = "Yes, I wish to delete this tax and realise that once deleted the data can not be recovered.";
     $this->obj_form->add_input($structure);
     // define submit field
     $structure = NULL;
     $structure["fieldname"] = "submit";
     $structure["type"] = "submit";
     $structure["defaultvalue"] = "delete";
     $this->obj_form->add_input($structure);
     // define subforms
     $this->obj_form->subforms["tax_delete"] = array("name_tax");
     $this->obj_form->subforms["hidden"] = array("id_tax");
     if ($this->locked) {
         $this->obj_form->subforms["submit"] = array();
     } else {
         $this->obj_form->subforms["submit"] = array("delete_confirm", "submit");
     }
     // fetch the form data
     $this->obj_form->sql_query = "SELECT name_tax FROM `account_taxes` WHERE id='" . $this->id . "' LIMIT 1";
     $this->obj_form->load_data();
 }

 function check_requirements()
 {
     // verify that the quote exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM account_quotes WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested quote (" . $this->id . ") does not exist - possibly the quote has been deleted or converted into an invoice.");
         return 0;
     }
     unset($sql_obj);
     return 1;
 }

 function check_requirements()
 {
     // verify that the product exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM products WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested product (" . $this->id . ") does not exist - possibly the product has been deleted.");
         return 0;
     }
     unset($sql_obj);
     return 1;
 }

 function execute()
 {
     /*
     	Fetch period data
     */
     $sql_period_obj = new sql_query();
     $sql_period_obj->string = "SELECT date_start, date_end FROM services_customers_periods WHERE id='" . $this->id_service_period . "' LIMIT 1";
     $sql_period_obj->execute();
     $sql_period_obj->fetch_array();
     /*
     	Fetch call charges for this period into table.
     */
     // establish a new table object
     $this->obj_table = new table();
     $this->obj_table->language = $_SESSION["user"]["lang"];
     $this->obj_table->tablename = "service_history_cdr";
     // define all the columns and structure
     $this->obj_table->add_column("date", "date", "");
     $this->obj_table->add_column("standard", "rate_billgroup", "cdr_rate_billgroups.billgroup_name");
     $this->obj_table->add_column("standard", "number_src", "usage1");
     $this->obj_table->add_column("standard", "number_dst", "usage2");
     $this->obj_table->add_column("standard", "billable_seconds", "usage3");
     $this->obj_table->add_column("money_float", "price", "");
     // defaults
     $this->obj_table->columns = array("date", "rate_billgroup", "number_src", "number_dst", "billable_seconds", "price");
     $this->obj_table->columns_order = array("date", "rate_billgroup", "number_src", "number_dst");
     // totals
     $this->obj_table->total_columns = array("billable_seconds", "price");
     // define SQL structure
     $this->obj_table->sql_obj->prepare_sql_settable("service_usage_records");
     $this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN cdr_rate_billgroups ON cdr_rate_billgroups.id = service_usage_records.billgroup");
     $this->obj_table->sql_obj->prepare_sql_addfield("id", "service_usage_records.id");
     $this->obj_table->sql_obj->prepare_sql_addwhere("id_service_customer = '" . $this->obj_customer->id_service_customer . "'");
     $this->obj_table->sql_obj->prepare_sql_addwhere("date >= '" . $sql_period_obj->data[0]["date_start"] . "'");
     $this->obj_table->sql_obj->prepare_sql_addwhere("date <= '" . $sql_period_obj->data[0]["date_end"] . "'");
     // acceptable filter options
     $structure = NULL;
     $structure["fieldname"] = "searchbox";
     $structure["type"] = "input";
     $structure["sql"] = "(number_src LIKE '%value%' OR number_dst LIKE '%value%')";
     $this->obj_table->add_filter($structure);
     $this->obj_table->add_fixed_option("id_customer", $this->obj_customer->id);
     $this->obj_table->add_fixed_option("id_service_customer", $this->obj_customer->id_service_customer);
     $this->obj_table->add_fixed_option("id_service_period", $this->id_service_period);
     // load settings from options form
     $this->obj_table->load_options_form();
     // run SQL query
     $this->obj_table->generate_sql();
     $this->obj_table->load_data_sql();
 }

 function check_requirements()
 {
     // verify that the account exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id, locked FROM account_gl WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested transaction (" . $this->id . ") does not exist - possibly the transaction has been deleted.");
         return 0;
     } else {
         $sql_obj->fetch_array();
         $this->locked = $sql_obj->data[0]["locked"];
     }
     unset($sql_obj);
     return 1;
 }

 function check_requirements()
 {
     // verify that the service exists
     $sql_obj = new sql_query();
     $sql_obj->string = "SELECT id FROM services WHERE id='" . $this->id . "' LIMIT 1";
     $sql_obj->execute();
     if (!$sql_obj->num_rows()) {
         log_write("error", "page_output", "The requested service (" . $this->id . ") does not exist - possibly the service has been deleted.");
         return 0;
     }
     unset($sql_obj);
     // verify that this is a bundle service
     if ($this->service_type != "bundle") {
         log_write("error", "page_output", "The requested service is not a bundle service.");
         return 0;
     }
     return 1;
 }

function config_generate_uniqueid($config_name, $check_sql)
{
    log_debug("inc_misc", "Executing config_generate_uniqueid({$config_name})");
    $config_name = strtoupper($config_name);
    $returnvalue = 0;
    $uniqueid = 0;
    // fetch the starting ID from the config DB
    $uniqueid = sql_get_singlevalue("SELECT value FROM config WHERE name='{$config_name}'");
    if (!$uniqueid) {
        die("Unable to fetch {$config_name} value from config database");
    }
    // first set the uniqueid prefix to an empty string, in case the following tests fail
    $uniqueid_prefix = '';
    if (!is_numeric($uniqueid)) {
        preg_match("/^(\\S*?)([0-9]*)\$/", $uniqueid, $matches);
        $uniqueid_prefix = $matches[1];
        $uniqueid = (int) $matches[2];
    }
    if ($check_sql) {
        // we will use the supplied SQL query to make sure this value is not currently used
        while ($returnvalue == 0) {
            $sql_obj = new sql_query();
            $sql_obj->string = str_replace("VALUE", $uniqueid_prefix . $uniqueid, $check_sql);
            $sql_obj->execute();
            if ($sql_obj->num_rows()) {
                // the ID has already been used, try incrementing
                $uniqueid++;
            } else {
                // found an avaliable ID
                $returnvalue = $uniqueid;
            }
        }
        $returnvalue = $uniqueid_prefix . $returnvalue;
    } else {
        // conducting no DB checks.
        $returnvalue = $uniqueid_prefix . $uniqueid;
    }
    // update the DB with the new value + 1
    $uniqueid++;
    $sql_obj = new sql_query();
    $sql_obj->string = "UPDATE config SET value='{$uniqueid_prefix}{$uniqueid}' WHERE name='{$config_name}'";
    $sql_obj->execute();
    return $returnvalue;
}