function insertKineVisit($kine)
{
    $mysqli = getMysqli();
    $sql = "INSERT INTO kine_test(id_patient, date_kine, tinettiPoma, getupandgo, slow_walk, fast) VALUES(" . $kine['id_patient'] . ", " . $kine['date_kine'] . ", " . $kine['tinettiPoma'] . ", " . $kine['getupandgo'] . ", " . $kine['slow_walk'] . ", " . $kine['fast_walk'] . ");";
    $result = $mysqli->query($sql);
    return $result;
}

function insertmedicalCheck($mediVisit)
{
    $mysqli = getMysqli();
    $sql = "INSERT INTO medical_check(id_patient, date_visit, height, weight, bmi, albumin, crp, vitamin_d, frequency, pressure, gir) VALUES(" . $mediVisit['id_patient'] . ", " . $mediVisit['date_visit'] . ", " . $mediVisit['height'] . ", " . $mediVisit['weight'] . ", " . $mediVisit['bmi'] . ", " . $mediVisit['albumin'] . ", " . $mediVisit['crp'] . ", " . $mediVisit['vitamin_d'] . ", " . $mediVisit['frequency'] . ", " . $mediVisit['pressure'] . ", " . $mediVisit['gir'] . ");";
    $result = $mysqli->query($sql);
    return $result;
}

function insertPsychoVisit($psychoVisit)
{
    $mysqli = getMysqli();
    $sql = "INSERT INTO psychological_test VALUES(" . $psychoVisit['id_patient'] . ", " . $psychoVisit['date_psycho'] . ", " . $psychoVisit['minibesttest_score'] . ", " . $psychoVisit['greco_global'] . ", " . $psychoVisit['greco_immediat'] . ", " . $psychoVisit['greco_differe'] . ");";
    $result = $mysqli->query($sql);
    return $result;
}

function getPatientPage($user)
{
    $mysqli = getMysqli();
    $sql = "SELECT * FROM user INNER JOIN patient_account ON 'user.id_user' = 'patient_account.id_user' WHERE 'user.id_user' = " . $user . ";";
    $result = array();
    $query = $mysqli->query($sql);
    while ($row = $query->fetch_assoc()) {
        array_push($result, $row);
    }
    return $result;
}

function getUserByEmail($email)
{
    $mysqli = getMysqli();
    $sql = "SELECT * FROM User WHERE Name = " . $email . ";";
    $result = array();
    $query = $mysqli->query($sql);
    while ($row = $query->fetch_assoc()) {
        array_push($result, $row);
    }
    return $result;
}

function l_log($pinkieID, $user, $msg, $lvl)
{
    $_db = getMysqli();
    $_sql = "INSERT INTO Log (PinkieID, User, Level, Message) VALUES (?,?,?,?)";
    $_stmt = $_db->prepare((string) $_sql);
    $_stmt->bind_param('isss', $pinkieID, $user, $lvl, $msg);
    $_stmt->execute();
    if ($_stmt->errno) {
        onError("Error in Logger.php::log()", $_stmt->error);
    }
    $_stmt->close();
    // Close up the database connection.
    $_db->close();
}

function is_allowed($userId, $targetId)
{
    $mysqli = getMysqli();
    $sql = "SELECT * FROM patientToPro WHERE 'idPro' = " . $userId . " AND 'idPatient' = " . $targetId . ";";
    $result = array();
    $query = $mysqli->query($sql);
    while ($row = $query->fetch_assoc()) {
        array_push($result, $row);
    }
    if (sizeof($result) == 0) {
        return false;
    } else {
        return true;
    }
}

function printAllVendors()
{
    $_db = getMysqli();
    $_stmt = $_db->prepare("SELECT VendorName, VendorID FROM Vendors");
    $_stmt->execute();
    $_stmt->bind_result($s_VendorName, $i_VendorID);
    while ($_stmt->fetch()) {
        $_value = "<tr>\n                <td>\n                  " . $s_VendorName . "\n                </td>\n                <td>";
        if (canEditVendors()) {
            $_value = $_value . "<a href='./vendor.php?reason=edit&vid=" . $i_VendorID . "' class='btn btn-info' role='button'><span class='glyphicon glyphicon-pencil'></span> Update/Change</a> ";
        }
        $_value = $_value . "<a href='./vendor.php?reason=view&vid=" . $i_VendorID . "' class='btn btn-primary' role='button'><span class='glyphicon glyphicon-search'></span> View</a>";
        $_value = $_value . "</td></tr>";
        echo $_value;
    }
    $_stmt->free_result();
    $_db->close();
}

function isExist($key, $value, &$errmsg)
{
    if ($key == "email" || $key == "username") {
        $q1 = "SELECT {$key} FROM userlist WHERE {$key} = '{$value}'";
        $q2 = "SELECT {$key} FROM nonactivatedUser WHERE {$key} = '{$value}'";
        $q = $q1 . " UNION " . $q2;
        $mysqli = getMysqli();
        if ($rs = $mysqli->query($q)) {
            if ($rs->fetch_assoc()) {
                return true;
            }
        } else {
            $errmsg = "error";
            trigger_error("Failed to {$q} caused by : \r\n\t" . $mysqli->error);
        }
    }
    return false;
}

function get($uid, &$data, &$errmsg)
{
    if (isX($uid, XUSERID)) {
        if ($mysqli = getMysqli()) {
            $q = "SELECT id, money, type, CONVERT(date, DATE) AS date FROM tallybook_bill WHERE userid = {$uid}";
            if ($rs = $mysqli->query($q)) {
                $data = json_encode(new RsValue($rs));
                return true;
            } else {
                trigger_error("Failed to {$q} caused by : \r\n\t" . $mysqli->error);
                $errmsg = "error";
            }
        } else {
            trigger_error("Failed to connect to the database");
            $errmsg = "error";
        }
    }
    return false;
}

function printSubmittedByYouTable()
{
    $_db = getMysqli();
    $statement = $_db->prepare("SELECT * FROM Submitted_By WHERE Submitter=? && Status!=? && Status !=? && Status!=?");
    $a = Archived;
    $c = Cancelled;
    $d = Done;
    $statement->bind_param('ssss', $_SESSION['Username'], $a, $c, $d);
    $statement->execute();
    // Error running the statement.
    if ($statement->errno != 0) {
        $_tmp = $statement->error;
        $statement->close();
        $_db->close();
        onError("Home::printSubmittedToYouTable()", 'There was an error running the query [' . $_tmp . '] Could not fetch Pinkies submitted to: ' . $_SESSION['Username']);
    }
    $statement->store_result();
    if ($statement->num_rows <= 0) {
        echo '<tr>
                <td> No Pinkies submitted by you!</td>
                <td></td>
                <td></td>
                <td></td>
                <td></td>
              </tr>';
        return;
    }
    // We have a result, lets bind the result to the variables.
    $statement->bind_result($pinkieID, $timestamp, $submitterUser, $submittedFor, $title, $status, $totalvalue, $OriginalSubmitter, $SupervisorApprove, $AdminApprove, $TransProcess);
    while ($statement->fetch()) {
        printf('<tr>
                <td>%s</td>
                <td>%s</td>
                <td>%s</td>
                <td>%.2f</td>
                <td><a href="./viewpinkie.php?pid=%d" class="btn btn-primary" role="button"><span class="glyphicon glyphicon-search"></span> View</a></td>
              </tr>', $title, $submittedFor, $timestamp, $totalvalue, $pinkieID);
    }
    // Cleanup.
    $statement->free_result();
    $statement->close();
    $_db->close();
}

function InquryByID($_id)
{
    $mysqli = getMysqli();
    $sql = "SELECT * FROM `Soccer` WHERE _index=?";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('i', $_id);
    $stmt->execute();
    $stmt->bind_result($index, $date, $content);
    $dataArray = [];
    $row = null;
    while ($stmt->fetch()) {
        $row = [];
        $row['_index'] = $index;
        $row['_date'] = $date;
        $row['_content'] = html_entity_decode(htmlspecialchars($content));
        array_push($dataArray, $row);
    }
    $stmt->close();
    return json_encode($dataArray);
}

function printAllFunds()
{
    $_db = getMysqli();
    if (canEditFunds()) {
        $_stmt = $_db->prepare("SELECT FundName, FundID, Balance, Timestamp FROM Funds");
    } else {
        $_stmt = $_db->prepare("SELECT FundName, FundID, Balance, Timestamp FROM Funds WHERE Active=1");
    }
    $_stmt->execute();
    $_stmt->bind_result($s_FundName, $i_FundID, $s_Balance, $s_Timestamp);
    while ($_stmt->fetch()) {
        $_value = "<tr>\n                <td>" . $s_FundName . "</td>\n                <td>" . $s_Balance . "</td>\n                <td>" . $s_Timestamp . "</td>\n                <td>";
        if (canEditFunds()) {
            $_value = $_value . "<a href='./fund.php?reason=edit&fid=" . $i_FundID . "' class='btn btn-info' role='button'><span class='glyphicon glyphicon-pencil'></span> Update/Change</a> ";
        }
        $_value = $_value . "<a href='./fund.php?reason=view&fid=" . $i_FundID . "' class='btn btn-primary' role='button'><span class='glyphicon glyphicon-search'></span> View</a>";
        $_value = $_value . "</td></tr>";
        echo $_value;
    }
    $_stmt->free_result();
    $_db->close();
}

function saveUser($user)
{
    $mysqli = getMysqli();
    $sql_common = "INSERT INTO user(email, password, gender, name, firstname, street, zip, city, phone, mobile, account) VALUES ('" . $user['email'] . "' ,'" . $user['pass'] . "' ,'" . $user['gender'] . "' ,'" . $user['name'] . "' ,'" . $user['firstname'] . "', '" . $user['street'] . "' ,'" . $user['zip'] . "' ,'" . $user['city'] . "' ,'" . $user['phone'] . "' ,'" . $user['mobile'] . "', '" . $user['isPro'] . "');";
    $result = $mysqli->query($sql_common) or QueryError("Invalid query registering user", $sql_common, $mysqli->error);
    $id = $mysqli->insert_id;
    switch ($user['isPro']) {
        case 0:
            $sql_patient = "INSERT INTO patient_account VALUES ('" . $id . "', '" . $user['physicianName'] . "' ,'" . $user['physicianMail'] . "' ,'" . $user['birthday'] . "' ,'" . $user['emergency'] . "', '" . $user['status'] . "' ,'" . $user['accompaniment'] . "' ,'" . $user['residency'] . "' , '" . $user['isFinancial'] . "' ,'" . $user['isHelp'] . "');";
            $resultPatient = $mysqli->query($sql_patient) or QueryError("Invalid query registering patient", $sql_patient, $mysqli->error);
            return $result && $resultPatient;
            break;
        case 1:
            $sql_pro = "INSERT INTO professional_account VALUES ('" . $id . "', '" . $user['officeName'] . "')";
            $resultPro = $mysqli->query($sql_pro) or QueryError("Invalid query registering pro", $sql_pro, $mysqli->error);
            return $result && $resultPro;
            break;
        default:
            return false;
            break;
    }
}

function extractResult($filterType, $param)
{
    $arr = array();
    $query = "select word_geo from words where word_eng " . $filterType . " ? ";
    $mysqli = getMysqli();
    $stmt = getStatementByQueryFromMysqli($query, $mysqli);
    $param = Converter::ToLatin($param);
    $stmt->bind_param("s", $param);
    /* execute statement */
    $stmt->execute();
    // bind result variables
    $stmt->bind_result($word_value);
    $counter = 0;
    //fetch values
    while ($stmt->fetch()) {
        if ($counter++ > 999) {
            break;
        }
        array_push($arr, array('id' => $counter, 'value' => $word_value));
    }
    closeConnections($stmt, $mysqli);
    return $arr;
}

function getRankSet($type = '')
{
    global $gActivityStart, $gActivityEnd, $gCurrDate;
    switch ($type) {
        case 1:
            // 日排行榜，按当前日期，活动开始时间，活动结束时间查询，存储过程中计算日期和统计
            $sql = "CALL rank_list('{$gCurrDate}','{$gActivityStart}','{$gActivityEnd}','{$type}')";
            break;
        case 2:
            // 周排行榜，按当前日期，活动开始时间，活动结束时间查询，存储过程中计算日期和统计
            $sql = "CALL rank_list('{$gCurrDate}','{$gActivityStart}','{$gActivityEnd}','{$type}')";
            break;
        case 3:
            // 周排行榜，按当前日期，活动开始时间，活动结束时间查询，存储过程中计算日期和统计
            $sql = "CALL rank_list('{$gCurrDate}','{$gActivityStart}','{$gActivityEnd}','{$type}')";
            break;
        case 4:
            // 日排行榜，按活动起止日期时间查询，可重复数据
            $sql = "CALL rank_list_daily('{$gCurrDate}','{$gActivityStart}','{$gActivityEnd}')";
            break;
        default:
            return;
    }
    $mysqli = getMysqli();
    $resultOne = $mysqli->query($sql);
    // 把排名榜结果集加到数组
    $rankSet = array();
    // 整个排名榜的数组
    while ($row = $resultOne->fetch_assoc()) {
        $rankSet[] = $row;
    }
    mysqli_close($mysqli);
    if (!$rankSet) {
        return;
    }
    return $rankSet;
}

function dbPush()
{
    $arg_list = func_get_args();
    $mysqli = getMysqli();
    $sql = array_shift($arg_list);
    $query = $mysqli->prepare($sql);
    $Args = array();
    foreach ($arg_list as $k => &$arg) {
        $Args[$k] =& $arg;
    }
    call_user_func_array(array($query, "bind_param"), $Args);
    $out;
    if ($query->execute()) {
        $out = true;
    } else {
        $out = false;
    }
    $query->close();
    $mysqli->close();
    return $out;
}

function printLogsTable()
{
    global $_pinkie;
    $_db = getMysqli();
    $statement = $_db->prepare("SELECT * FROM Log WHERE PinkieID=?");
    $statement->bind_param('i', $_pinkie->i_PinkieID);
    $statement->execute();
    // Error running the statment.
    if ($statement->errno != 0) {
        $_tmp = $statement->error;
        $statement->close();
        $_db->close();
        onError("viewPinkie.php::printLogsTable()", 'There was an error running the query [' . $_tmp . '] Could not fetch the logs.');
    }
    $statement->store_result();
    if ($statement->num_rows <= 0) {
        echo '<tr>
            <td>No Files attached to this Pinkie!</td>
            <td></td>
          </tr>';
        $statement->free_result();
        $statement->close();
        $_db->close();
        return;
    }
    // We have a result, lets bind the result to the variables.
    $statement->bind_result($logID, $pinkieID, $user, $timestamp, $lvl, $msg);
    while ($statement->fetch()) {
        echo '<tr>
            <td>' . $msg . '</td>
            <td>' . $timestamp . '</td>
          </tr>';
    }
    // Cleanup.
    $statement->free_result();
    $statement->close();
    $_db->close();
}

 function addNew()
 {
     // Everything all good, lets insert in to the table.
     $_db = getMysqli();
     $_sql = "INSERT INTO Objects (PinkieID, Quantity, StockNumber, Description, BC, AccountNumber, UnitPrice) VALUES (?,?,?,?,?,?,?)";
     $_stmt = $_db->prepare((string) $_sql);
     $_stmt->bind_param('iissssd', $this->i_PinkieID, $this->i_Quantity, $this->s_StockNumber, $this->s_Description, $this->s_BC, $this->s_AccountNumber, $this->d_UnitPrice);
     $_stmt->execute();
     if ($_stmt->errno) {
         onError("PurchaseObject::addNew()", $_stmt->error);
     }
     $_stmt->close();
     // Close up the database connection.
     $_db->close();
 }

function checkFbUser($fbId, $fbFirstName, $fbLastName, $fbEmail, $userId)
{
    $mysqli = getMysqli();
    $image = "//graph.facebook.com/{$fbId}/picture";
    $timestamp = date('Y-m-d H:i:s');
    if ($mysqli->query("SELECT * FROM users WHERE fb_id = '{$fbId}'")->num_rows !== 0) {
        // we have this user. just log him in, and update his info
        if ($userId) {
            $respCode = 1;
        } else {
            $updateQuery = "UPDATE users SET fb_fname='{$fbFirstName}', fb_lname='{$fbLastName}' WHERE fb_id='{$fbId}'";
            //$updateQuery = "UPDATE users SET fb_name='$fbFullName' WHERE fb_id='$fbId'";
            mysqli_query($mysqli, $updateQuery);
            $userId = mysqli_fetch_assoc($mysqli->query("SELECT id, username FROM users WHERE fb_id='{$fbId}'"));
            mysqli_query($mysqli, "INSERT INTO session_history VALUES ({$userId['id']}, 'fb_login', '{$timestamp}')");
            onLogin($userId['id']);
            if (strlen($userId['username']) > 0) {
                session_start();
                $_SESSION['user'] = $userId['username'];
            }
        }
    } else {
        if ($userId) {
            // we have a user, but no facebook information. update it
            $updateQuery = "UPDATE users SET fb_id='{$fbId}', fb_fname='{$fbFirstName}', fb_lname='{$fbLastName}', image='{$image}'" . (strlen($fbEmail) > 0 ? ", email='{$fbEmail}'" : "") . " WHERE id={$userId}";
            mysqli_query($mysqli, $updateQuery);
            mysqli_query($mysqli, "INSERT INTO session_history VALUES ({$userId}, 'fb_login', '{$timestamp}')");
            $respCode = 2;
        } else {
            // we don't have this user. Add him to the DB, make him active
            $fbEmail = strlen($fbEmail) > 0 ? "'{$fbEmail}'" : "NULL";
            $hash = md5(rand(0, 1000));
            $createUserQuery = "INSERT INTO users VALUES (NULL, NULL, NULL, '{$fbId}', '{$fbFirstName}', '{$fbLastName}', {$fbEmail}, '{$image}', 0, 1, 1, '{$hash}', '', '', 1)";
            mysqli_query($mysqli, $createUserQuery);
            $userId = mysqli_fetch_assoc($mysqli->query("SELECT id FROM users WHERE fb_id='{$fbId}'"))['id'];
            mysqli_query($mysqli, "INSERT INTO session_history VALUES ({$userId}, 'fb_signup', '{$timestamp}')");
            //echo $createUserQuery;
        }
    }
    return $respCode ? $respCode : 0;
}