PHP execSQL函数代码示例

 function load($file)
 {
     try {
         execSQL($file, $this->db, PRE);
     } catch (PDOException $e) {
         throw new Exception($e->getMessage() . '<pre>' . $e . '</pre>');
     }
 }

function chgPwdphpBBuser($nick, $pwd)
{
    $cryptpass = phpbb_hash($pwd);
    $sql = "UPDATE `" . $_SESSION['Application_host'] . "`.`forum_users` ";
    $sql .= "SET `user_password` = '" . $cryptpass . "' ";
    $sql .= "WHERE `username` = '" . $nick . "'";
    echo "<!--" . $sql . "-->";
    $req = execSQL($sql, "function", __FILE__, __FUNCTION__);
}

function getVersionForModule($dbh, $name)
{
    $stmt = execSQL($dbh, "SELECT version FROM updates WHERE modulename='{$name}'");
    $row = $stmt->fetch();
    if ($row) {
        return $row["version"];
    } else {
        return 0;
    }
}

function runMatching($contacts)
{
    // Read data from jva_contacts table and store it in a object
    $jva_data = array();
    $jvaQuery = "SELECT * FROM jva_contacts";
    $DBResource = getDBConnection();
    $jva_resultSet = $resultSet = execSQL($DBResource, $jvaQuery);
    if (mysqli_num_rows($jva_resultSet) > 0) {
        while ($jva_r = mysqli_fetch_row($jva_resultSet)) {
            $jva_row = new TempContact();
            $jva_row->jva_id = $jva_r[0];
            $jva_row->jva_first_name = $jva_r[1];
            $jva_row->jva_middle_name = $jva_r[2];
            $jva_row->jva_last_name = $jva_r[3];
            $jva_row->jva_salutation = $jva_r[4];
            $jva_row->jva_phone_no = $jva_r[5];
            $jva_row->jva_fax_no = $jva_r[6];
            $jva_row->jva_country = $jva_r[7];
            $jva_row->jva_zipcode = $jva_r[8];
            $jva_row->jva_email = $jva_r[9];
            array_push($jva_data, $jva_row);
        }
        closeDBConnection($DBResource);
        // To compare each record in jva_contacts with all records in the file
        foreach ($jva_data as $jva_row) {
            foreach ($contacts as $contacts_row) {
                $c_last = $contacts_row->con_last_name;
                $j_last = $jva_row->jva_last_name;
                $DBResource = getDBConnection();
                if ($jva_row->jva_last_name == $contacts_row->con_last_name && $jva_row->jva_country == $contacts_row->con_country && $jva_row->jva_email == $contacts_row->con_email) {
                    $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "'," . $jva_row->jva_id . ",'Perfect',1)";
                    $resultSet = execSQL($DBResource, $nQuery);
                } else {
                    if ($jva_row->jva_country == $contacts_row->con_country && $jva_row->jva_email == $contacts_row->con_email) {
                        if (strpos($j_last, $c_last) !== FALSE || strpos($c_last, $j_last) !== FALSE) {
                            $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "'," . $jva_row->jva_id . ",'Partial',1)";
                            $resultSet = execSQL($DBResource, $nQuery);
                        }
                    } else {
                        $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "',0,'New',1)";
                        $resultSet = execSQL($DBResource, $nQuery);
                    }
                }
                closeDBConnection($DBResource);
            }
            // end of inner for each loop
        }
        // end of outer for each loop
    } else {
        foreach ($contacts as $contacts_row) {
            $nQuery = "INSERT INTO notifications(src_id,src_con_id,jva_id,match_case,pending_notification) values('" . $contacts_row->src_id . "','" . $contacts_row->src_con_id . "',0,'New',1)";
            execSQL($DBResource, $nQuery);
        }
    }
}

 function db_page_delete($page_id)
 {
     $param_arr = _init_db_params();
     LOG_MSG('INFO', "db_page_delete(): START { page_id=[{$page_id}]");
     // For the where clause
     $where_clause = " WHERE page_id=? AND shop_id=" . SHOP_ID;
     $param_arr = _db_prepare_param($param_arr, "i", "page_id", $page_id, true);
     $resp = execSQL("DELETE FROM  \n\t\t\t\t\t\t\ttPage" . $where_clause, $param_arr['params'], true);
     LOG_MSG('INFO', "db_page_delete(): END");
     return $resp;
 }

function getUser($username)
{
    $result = execSQL("SELECT * FROM users WHERE username='" . strtolower($username) . "'");
    $user = array("id" => 0, "username" => "", "about" => "", "profileimage" => "");
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $user = array("id" => $row["id"], "username" => $row["username"], "about" => $row["about"], "profileimage" => $row["profileimage"]);
        }
    }
    return $user;
}

function db_room_select($room_id = "", $room_name = "")
{
    LOG_MSG('INFO', "db_wallet_select: START { \n\t\t\t\t\t\t\troom_id=[{$room_id}],\n\t\t\t\t\t\t\troom_name=[{$room_name}] \n}");
    $param_arr = _init_db_params();
    $where_clause = "WHERE room_name={$room_name} AND room_id = {$room_id}";
    $param_arr['params'] = array();
    LOG_MSG('INFO', "db_room_select(): WHERE CLAUSE = [{$where_clause}]");
    $resp = execSQL("SELECT \n\t\t\t\t\t\troom_name,\n\t\t\t\t\t\troom_id,\n\t\t\t\t\t\tis_booked\n\t\t\t\t\tFROM \n\t\t\t\t\t\tbookrooms\n\t\t\t\t\t" . $where_clause, $param_arr['params'], false);
    LOG_MSG('INFO', "db_room_select(): END");
    return $resp;
}

function db_do_login($email_id, $password)
{
    LOG_MSG('INFO', "db_do_login(): START {\n\t\t\t\t\t\t\t\t\t\temail_id=[{$email_id}],\n\t\t\t\t\t\t\t\t\t\tpassword=[{$password}]\n}");
    $param_arr = _init_db_params();
    $param_arr = _db_prepare_param($param_arr, "s", "email_id", $email_id, true);
    $param_arr = _db_prepare_param($param_arr, "s", "password", $password, true);
    // Create Query
    $resp = execSQL("SELECT \n\t\t\t\t\t\tu.user_id,\n\t\t\t\t\t\tu.name,\n\t\t\t\t\t\tu.email_id,\n\t\t\t\t\t\tu.password,\n\t\t\t\t\t\tu.phone_no,\n\t\t\t\t\t\tu.address,\n\t\t\t\t\t\tu.type,\n\t\t\t\t\t\tu.is_active,\n\t\t\t\t\t\tu.created_dt,\n\t\t\t\t\t\ts.supervisor_id,\n\t\t\t\t\t\tt.travel_id,\n\t\t\t\t\t\tt.name travel_name,\n\t\t\t\t\t\tt.domain\n\t\t\t\t\tFROM\n\t\t\t\t\t\ttUser u\n\t\t\t\t\t\tLEFT OUTER JOIN tSupervisor s ON(s.user_id=u.user_id)\n\t\t\t\t\t\tLEFT OUTER JOIN tTravel t ON(u.travel_id=t.travel_id)\n\t\t\t\t\tWHERE \n\t\t\t\t\t\tu.email_id=? \n\t\t\t\t\t\tAND u.password=? AND\n\t\t\t\t\t\tt.travel_id=" . TRAVEL_ID, $param_arr['params'], false);
    LOG_MSG('INFO', "db_do_login(): END");
    return $resp;
}

function login()
{
    if (empty($_POST['username'])) {
        echo "Username is empty";
        return false;
    }
    if (empty($_POST['password'])) {
        echo "Password is empty";
        return false;
    }
    $username = trim($_POST['username']);
    $password = trim($_POST['password']);
    $conn = getDBConnection();
    $query = "select password from login_details where username = '{$username}'";
    $res = execSQL($conn, $query);
    while ($row = $res->fetch_assoc()) {
        $pwdhash = $row['password'];
    }
    return password_verify($password, $pwdhash);
}

function get_unique_field($table)
{
    LOG_MSG("INFO", "####### GETTING UNIQUE KEY COLUMN NAME : for " . DB_NAME . $table);
    $row = execSQL("\n\t\t\t\tSELECT \n\t\t\t\t\tcolumn_name\n\t\t\t\tFROM \n\t\t\t\t\tinformation_schema.COLUMNS\n\t\t\t\tWHERE \n\t\t\t\t\ttable_name = '" . $table . "' AND\n\t\t\t\t\ttable_schema = '" . DB_NAME . "' AND\n\t\t\t\t\tcolumn_key='UNI';", array(), false);
    // If no unique key was found above
    if (!isset($row[0]['column_name'])) {
        LOG_MSG("INFO", "####### GETTING PRIMARY KEY COLUMN NAME : for " . DB_NAME . $table);
        $row = execSQL("\n\t\t\t\tSELECT \n\t\t\t\t\tcolumn_name\n\t\t\t\tFROM \n\t\t\t\t\tinformation_schema.COLUMNS\n\t\t\t\tWHERE \n\t\t\t\t\ttable_name = '" . $table . "' AND\n\t\t\t\t\ttable_schema = '" . DB_NAME . "' AND\n\t\t\t\t\tcolumn_key='PRI';", array(), false);
    }
    return $row[0]['column_name'];
}

function modulesetting_db_get($name, $category = '')
{
    LOG_MSG('INFO', "modulesetting_db_get(): START { name=[{$name}] category=[{$category}] \n}");
    $param_arr = _init_db_params();
    // Where Clause
    $where_clause = "WHERE name = ? AND travel_id=" . TRAVEL_ID;
    $param_arr = _db_prepare_param($param_arr, "s", "name", $name, true);
    if ($category != '') {
        $where_clause .= " AND category=? ";
        $param_arr = _db_prepare_param($param_arr, "s", "category", $category, true);
    }
    $resp = execSQL("SELECT \n\t\t\t\t\t\tvalue\n\t\t\t\t\tFROM \n\t\t\t\t\t\ttSetting \n\t\t\t\t\t{$where_clause} ", $param_arr['params'], false);
    LOG_MSG('INFO', "modulesetting_db_get(): END");
    if ($resp[0]['STATUS'] != 'OK') {
        LOG_MSG('ERROR', "modulesetting_db_get(): There was an error loading name=[{$name}]");
        return false;
    }
    if ($resp[0]['NROWS'] != 1) {
        LOG_MSG('INFO', "modulesetting_get(): No value found for [{$name}]");
        return false;
    }
    return $resp[0]['value'];
}

if (isset($_POST['send_pwd'])) {
    $login = isset($_POST['f_caver_login']) ? $_POST['f_caver_login'] : '';
    $contact = isset($_POST['f_caver_contact']) ? $_POST['f_caver_contact'] : '';
    $password = generatePassword(10, 8);
    $string = isset($_POST['f_caver_check']) ? $_POST['f_caver_check'] : '';
    $user_check = isset($_SESSION['userCheck']) ? $_SESSION['userCheck'] : '';
    if (!$_SESSION['do_check'] || md5(getIp() . strtolower($string)) == $user_check) {
        $sql = "SELECT * FROM `" . $_SESSION['Application_host'] . "`.`T_caver`";
        $sql .= " WHERE Login = '" . $login . "' AND Contact ='" . $contact . "'";
        $data = getDataFromSQL($sql, __FILE__, $frame, __FUNCTION__);
        if ($data['Count'] > 0) {
            if ($data[0]['Activated'] == 'YES') {
                $sql = "UPDATE `" . $_SESSION['Application_host'] . "`.`T_caver`";
                $sql .= " SET Password ='" . getCryptedPwd($login, $password) . "'";
                $sql .= " WHERE Id = " . $data[0]['Id'];
                $req = execSQL($sql, $frame, __FILE__, __FUNCTION__);
                if (!defined('NO_PHPBB_INSTALLED')) {
                    chgPwdphpBBuser($login, $password);
                }
                sendNewPwdMail($data[0], $password);
                //echo $password;
                trackAction("pwd_user", $data[0]['Id'], "T_caver");
                $_SESSION['user_pwd_sent'] = true;
            } else {
                $activated = false;
                $_SESSION['user_pwd_sent'] = false;
            }
        } else {
            $_SESSION['user_pwd_sent'] = false;
            $_SESSION['send_retry'] += 1;
        }

/**
 *
 *
 * @param mixed $colNames
 * @param mixed $recID
 * @param mixed $rt
 */
function updateReportSchedule($colNames, $recID, $values)
{
    global $db, $sys_usrReportSchedule_ColumnNames;
    $ret = null;
    if (count($colNames) && count($values)) {
        $db = mysqli_connection_overwrite(DATABASE);
        $isInsert = $recID < 0;
        $query = "";
        $fieldNames = "";
        $parameters = array("");
        $fieldNames = join(",", $colNames);
        foreach ($colNames as $colName) {
            $val = array_shift($values);
            if (array_key_exists($colName, $sys_usrReportSchedule_ColumnNames)) {
                if ($query != "") {
                    $query = $query . ",";
                }
                if ($isInsert) {
                    $query = $query . "?";
                } else {
                    $query = $query . "{$colName} = ?";
                }
                $parameters[0] = $parameters[0] . $sys_usrReportSchedule_ColumnNames[$colName];
                //take datatype from array
                array_push($parameters, $val);
            }
        }
        //for columns
        if ($query != "") {
            if ($isInsert) {
                $query = "insert into usrReportSchedule (" . $fieldNames . ") values (" . $query . ")";
            } else {
                $query = "update usrReportSchedule set " . $query . " where rps_ID = {$recID}";
            }
            $rows = execSQL($db, $query, $parameters, true);
            if ($rows == 0 || is_string($rows)) {
                $oper = $isInsert ? "inserting" : "updating";
                $ret = "error {$oper} in updateReportSchedule - " . $rows;
                //$msqli->error;
            } else {
                if ($isInsert) {
                    $recID = $db->insert_id;
                    $ret = -$recID;
                } else {
                    $ret = $recID;
                }
            }
        }
        $db->close();
    }
    //if column names
    if ($ret == null) {
        $ret = "no data supplied for updating report - {$recID}";
    }
    return $ret;
}

/** Run the module install/uninstall scripts
 * @param string  The name of the module
 * @param string  The action to perform, either 'install' or 'uninstall'
 * @return boolean  If the action was succesful
 */
function _module_runscripts($modulename, $type)
{
    global $amp_conf;
    $db_engine = $amp_conf["AMPDBENGINE"];
    $moduledir = $amp_conf["AMPWEBROOT"] . "/admin/modules/" . $modulename;
    if (!is_dir($moduledir)) {
        return false;
    }
    switch ($type) {
        case 'install':
            // install sql files
            $sqlfilename = "install.sql";
            $rc = true;
            if (is_file($moduledir . '/' . $sqlfilename)) {
                $rc = execSQL($moduledir . '/' . $sqlfilename);
            }
            // then run .php scripts
            return _modules_doinclude($moduledir . '/install.php', $modulename) && $rc;
            break;
        case 'uninstall':
            // run uninstall .php scripts first
            $rc = _modules_doinclude($moduledir . '/uninstall.php', $modulename);
            $sqlfilename = "uninstall.sql";
            // then uninstall sql files
            if (is_file($moduledir . '/' . $sqlfilename)) {
                return $rc && execSQL($moduledir . '/' . $sqlfilename);
            } else {
                return $rc;
            }
            break;
        default:
            return false;
    }
    return true;
}

         `pabrik02`, `pabrik03`, `pabrik04`, `pabrik05`, `satuan_kirim`, `jenis_obat`, `kategori_obat`, `golongan`, 
         `kode_guna`, `kode_persediaan`, `kode_pendapatan`, `kode_reduksi`, `kode_biaya`, `kode_ppn_k`, `kode_ppn_m`, 
         `expire_date`, `ex_date`, `ex_month`, `ex_year`, `tipe_obat`, `obat_tunai`, `hna`, `harga_dosp`, `discount`, 
         `ppn`, `averange_sale`, `stok_max`, `stok_min`, `stok`, `isi`, `kemasan`, `status`, `flags`, 
         `created_datetime`, `created_user`, `update_datetime`, `update_user`, `no_batch`, `no_rak`, `fld01`, `fld02`, 
         `fld03`) SELECT a.group_barang, a.kd_barang, a.nama, a.satuan, $suppID, 
         a.pabrik02, a.pabrik03, a.pabrik04, a.pabrik05, b.satuan_po, a.jenis_obat, a.kategori_obat, a.golongan, 
         a.kode_guna, a.kode_persediaan, a.kode_pendapatan, a.kode_reduksi, a.kode_biaya, a.kode_ppn_k, a.kode_ppn_m,
         b.fld05, day(b.fld05), month(b.fld05), year(b.fld05), a.tipe_obat, a.obat_tunai, a.hna, b.harga_po, b.discount,
         (b.harga_po *0.1), a.averange_sale, a.stok_max, a.stok_min, b.qty_po, a.isi, a.kemasan, 'Aktif', 1, 
         now(), '$userLoged', now(), '$userLoged', a.fld06, a.no_rak, b.no_po, b.no_spb, b.id FROM ms_barang a 
         INNER JOIN purchase_orderdetail b on a.id = b.barang_id WHERE b.id =$val  ";
         execSQL($strSQLUpMs);
         //print $strSQLUpMs; 
         $strSQLUpFlags = "UPDATE ms_barang set flags = 1 where id = $val";
         execSQL ($strSQLUpFlags);
         }
     }
     die("<meta http-equiv='refresh' content='0;url=home.php?hal=content/list_po'>");
 break;
 case 3:
     //die(print "$noPO   ::   $noReq    ::    $lastNO   ::   $items   ::   $dbPO  ::   $flags   :: $suppID  :: 3");
     /* CHANGED PO STATUS TO (APPROVED) */
     /* IF IT's ALREADY ON (APPROVED) STATUS THEN CHANGED TO (RECEIVING) */
     $items  = $_POST['param'];
     if(empty($noPO)){$noPO              = $_POST['pono'];}
     if(empty($noReq)){$noReq            = $_POST['no_req'];}
     if(empty($suppID)){$suppID          = $_POST['posupplier'];}
     if(empty($poID)){$poID              = $_POST['idPO'];}
     /*for($i=0;$i<=$items;$i++){
         $toDBDetSatuan      = $_POST['txtSat'.$i];