$usercode = Cls::session('usercode'); if (isset($_POST["SearchName"])) { $obj = $_POST["SearchName"]; $key = '"%' . $obj['cust_name'] . '%"'; $sql = "SELECT * FROM `bo_customer` where `cust_name` like {$key} and user_id={$usercode}"; $stm = $con->prepare($sql); $stm->execute(); $result = $stm->fetchAll(PDO::FETCH_OBJ); Cls::responeJson($result); } if (Cls::post('bamount') && intval($_POST['bamount']) > 0) { $bamount = str_replace(',', '', Cls::post('bamount')); $brate = str_replace(',', '', Cls::post('brate')); $sql = "insert into bo_lending (cust_id, user_id, lend_start, lend_stop, lend_amount, lend_status, lend_rate)\n\tvalue (?,?,?,?,?,?,?)"; $stm = $con->prepare($sql); $result = $stm->execute(array(Cls::post('custId'), Cls::session('usercode'), Cls::post('startdate'), Cls::post('stopdate'), $bamount, 1, $brate)); Cls::responeJson($result); } if (Cls::post('ShowData')) { $dat = $_POST['ShowData']; //echo ("dddddddddddddddddddddddddddddd" . $dat['txtname']); //$sql= "SELECT * from bo_lending"; $sql = "SELECT\n\t(@row_number :=@row_number + 1) AS rownum,\n\tA.lend_id,\n\tA.cust_id,\n\tA.user_id,\n\tA.lend_start,\n\tA.lend_amount,\n\t(\n\t\tCASE\n\t\tWHEN (\n\t\t\tA.lend_status = 1) THEN\n\t\t\t\t'បន្ត'\n\t\t\tELSE\n\t\t\t\t'បញ្ចប់'\n\t\tend\n\t\t) AS lend_status,\n\t\tA.lend_rate,\n\t\tB.cust_name,\n\t(A.lend_amount + A.lend_rate) as total ,\n\tA.perday\n\tFROM\n\t\tbo_lending A\n\tINNER JOIN bo_customer B ON A.cust_id = B.cust_id,\n\t(SELECT @row_number := 0) AS t where A.user_id = " . $usercode . " and B.cust_name like '%" . $dat['txtname'] . "%'"; if ($dat['txtstatus'] != '') { $sql = $sql . " and A.lend_status = " . $dat['txtstatus']; } $stm = $con->prepare($sql); $stm->execute(); $result = $stm->fetchAll(PDO::FETCH_OBJ); Cls::responeJson($result); }
//========================================== Connection ============================================= try { $con = new PDO($hostname . $datanase, $username, $password); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $con->exec("SET NAMES 'utf8';"); } catch (PDOException $ex) { die('Error : ' . '</br>' . $ex->getMessage()); } //========================================== Return Result ========================================== function responeJson($obj) { header('Content-Type: application/json'); echo json_encode($obj); } //================================================ Detail information ================================ if (Cls::post('DetailUser')) { $dat = $_POST['DetailUser']; $sql = "SELECT\n\t(@row_number :=@row_number + 1) AS rownum,\n\tA.lend_id,\n\tA.cust_id,\n\tA.user_id,\n\tA.lend_start,\n\tA.lend_stop,\n\tA.lend_amount,\n\t(\n\t\tCASE\n\t\tWHEN (\n\t\t\tA.lend_status = 1) THEN\n\t\t\t\t'បន្ត'\n\t\t\tELSE\n\t\t\t\t'បញ្ចប់'\n\t\tend\n\t\t) AS lend_status,\n\t\tA.lend_rate,\n\t\tB.cust_name,\n\t\tTIMESTAMPDIFF(\n\t\t\tDAY,\n\t\t\tA.lend_start,\n\t\t\tA.lend_stop\n\t\t) AS during,\n\t(A.lend_amount + A.lend_rate) as total ,\n\t((A.lend_amount + A.lend_rate) / TIMESTAMPDIFF(\n\t\t\tDAY,\n\t\t\tA.lend_start,\n\t\t\tA.lend_stop\n\t\t)) as perday\n\tFROM\n\t\tbo_lending A\n\tINNER JOIN bo_customer B ON A.cust_id = B.cust_id,\n\t(SELECT @row_number := 0) AS t where A.cust_id = ?"; $stm = $con->prepare($sql); $stm->execute(array($dat['custId'])); $result = $stm->fetchAll(PDO::FETCH_OBJ); Cls::responeJson($result); } //==================================================================================================== if (isset($_POST['ShowData'])) { //$sql = "SELECT * FROM bo_user"; //$sql = "SELECT row_number() over (ORDER BY user_id desc) as rownum from bo_user"; $sql = "SELECT (@row_number:=@row_number + 1) AS rownum, cust_id, cust_name, cust_sex, cust_phone, cust_address, cust_photo, cust_id_card, cust_start_date FROM bo_customer,(SELECT @row_number:=0) AS t"; $sql = $sql . " where user_id = " . Cls::session('usercode'); $query = $con->prepare($sql); $query->execute();