public function createUserSerialCode($personStatus)
{
$pdo = new DbConnection();
$pdo->conn = $pdo->open();
//SELECT LPAD(CONVERT(RIGHT(`code`, 4),UNSIGNED INTEGER),4,0) as newnumber FROM `person`
$sql = ' SELECT RIGHT((LEFT(CURDATE(),4)+543),2) as year_ad,';
// 58
$sql .= ' CASE status ';
$sql .= ' WHEN 1 THEN \'EMP\'';
$sql .= ' WHEN 2 THEN \'ONW\'';
$sql .= ' WHEN 3 THEN \'CUS\'';
$sql .= ' WHEN 4 THEN \'DRI\'';
$sql .= ' WHEN 0 THEN \'GEN\'';
$sql .= ' ELSE \'ERR\'';
$sql .= ' END prefix_status,';
$sql .= ' LEFT(`code`,3) as prefix,';
// DRI,EMP,CUS,ONW
$sql .= ' LPAD(CONVERT(RIGHT(`code`, 4),UNSIGNED INTEGER)+1,4,0) as new_runnumber,';
$sql .= ' RIGHT(`code`, 4) as runnumber';
$sql .= ' FROM user WHERE status =:status';
$sql .= ' ORDER BY RIGHT(`code`, 4) DESC LIMIT 0,1 ';
//echo 'sql ::=='.$sql;
$stmt = $pdo->conn->prepare($sql);
$stmt->execute(array(':status' => $personStatus));
$result = $stmt->fetch(PDO::FETCH_OBJ);
if (empty($result)) {
return '';
}
$prefix = $result->prefix;
$runnumber = $result->runnumber;
$year_ad = $result->year_ad;
$newrunnumber = $result->new_runnumber;
return $prefix . $year_ad . $newrunnumber;
}
<?php
require_once '../connDb/DbConnection.php';
$pdo = new DbConnection();
$pdo->conn = $pdo->open();
$id = '';
$fname = '';
$lname = '';
$username = '';
$password = '';
$card_id = '';
$nation_id = '';
$tel = '';
$sex = '';
$age = 0;
$email = '';
$address = '';
$updatedate = '';
$picture = PICTURE_DEFAULT;
$type = empty($_GET['user_type']) ? '' : $_GET['user_type'];
$code = $pdo->createUserSerialCode($type);
if (empty($code)) {
$code = getDataList($type, listUserPrefixStatus()) . '00001';
}
$validatePicture = 'required data-bv-notempty-message="กรุณาเลือกภาพส่วนตัว"';
/*
* ตรวจสอบ id เพื่อดูว่ากำลังแก้ไขหรือสร้างใหม่ด้วย ฟังชั่น empty() = ว่าง , !empty = ไม่ว่าง
*/
if (!empty($_GET['id'])) {
$stmt = $pdo->conn->prepare('SELECT * FROM user WHERE user_id =:id');
$stmt->execute(array(':id' => $_GET['id']));
