";
//echo "tPassword = $tPassword
";
//echo "dbNo = $dbNo
";
// echo "dbNo = $dbNo
";
//echo '
เริ่มลบข้อมูล
';
//$path_mysql = '/opt/mysql/bin/mysql';//'mysql';
if($dbNo==1) {
$pre = "$path_mysql -h '$host' -u '$tUsername' -p'$tPassword' -e ' DROP DATABASE IF EXISTS ".$dbmoreg." ; CREATE DATABASE IF NOT EXISTS ".$dbmoreg." ; ' ";
//$pre = "$path_mysql -h '$host' -u '$tUsername' -p'$tPassword' --execute=' DROP DATABASE IF EXISTS ".$dbmoreg." ; CREATE DATABASE IF NOT EXISTS ".$dbmoreg." ; '";
$last_line = system($pre, $retval);
echo "DROP & CREATE DATABASE $dbmoreg ";
if($retval){
echo "";
echo " เกิดข้อผิดพลาด ";
echo $retval.'
sql :'.$pre;
echo "
";
}else{
echo " สำเร็จ
";
}
$filename = 'mo_reg.sql';
if (!file_exists('mo_reg.sql')) {
echo "";
echo " เกิดข้อผิดพลาด ";
echo "ไม่พบไฟล์นำเข้าข้อมูลพื้นฐาน $filename ";
echo "
";
$retval = true;
die();
} else {
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -B ".$dbmoreg." < $filename";
$last_line = system($mysql, $retval);
echo "CREATE โครงสร้าง $dbmoreg ";
}
}elseif($dbNo==2){
$pre = "$path_mysql -h $host -u $tUsername -p'$tPassword' -e ' DROP DATABASE IF EXISTS ".$dbppc." ; CREATE DATABASE IF NOT EXISTS ".$dbppc." ; '";
$last_line = system($pre, $retval);
$filename = 'peoplecenter.sql';
if (!file_exists($filename)) {
echo "";
echo " เกิดข้อผิดพลาด ";
echo "ไม่พบไฟล์นำเข้าข้อมูลพื้นฐาน $filename ";
echo "
";
$retval = true;
die();
} else {
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -B ".$dbppc." < ".$filename;
$last_line = system($mysql, $retval);
echo "DROP & CREATE DATABASE $dbppc ";
}
}elseif($dbNo==3){
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -B ".$dbmoreg." < real_alter_InnoDB_mo_reg.sql";
$last_line = system($mysql, $retval);
echo $mysql;
echo "$last_line
";
echo "Update Foreign key ข้อมูลระบบทะเบียนใหม่ ";
}elseif($dbNo==32){
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -B ".$dbppc." < real_alter_InnoDB_peoplecenter.sql";
$last_line = system($mysql, $retval);
//echo $mysql;
//echo "$last_line
";
echo "Update Foreign key ข้อมูลระบบทะเบียนใหม่ ";
}elseif($dbNo==4){
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' --execute \"select concat('ALTER TABLE ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') dropfk
from information_schema.table_constraints
where constraint_type='FOREIGN KEY' AND table_schema='".$dbmoreg."'\" " ;
$retval = shell_exec($mysql);
$strpos = strpos($retval, 'dropfk ');
$alter_drop = substr($retval,$strpos+6);
//echo $mysql.'
';
$alter = "$path_mysql --default-character-set=utf8 -h $host -u $tUsername -p'$tPassword' --execute \"$alter_drop\" ";
system($alter, $retval);
//echo $alter_drop;
echo "$last_line
";
echo "
DROP Foreign Key ฐานข้อมูลระบบทะเบียนใหม่ ";
}elseif($dbNo==5){
$pre = "$path_mysql -h $host -u $tUsername -p'$tPassword' -e ' DROP DATABASE IF EXISTS ".$nums." ; CREATE DATABASE IF NOT EXISTS ".$nums." ; '";
$last_line = system($pre, $retval);
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -B ".$nums." < mo_ums.sql";
$last_line = system($mysql, $retval);
echo "CREATE DATABASE ".$nums;
if($retval){
echo "";
echo " เกิดข้อผิดพลาด ";
echo $retval;
echo "
";
}else{
echo " สำเร็จ
";
}
$insert_user = "
-- Copy script ข้างล่างนี้รัยที่ phpMyAdmin
SET foreign_key_checks = 0;
INSERT INTO $nums.umuser
(UsID ,
UsName ,
UsLogin ,
UsPassword ,
UsPsCode ,
UsWgID ,
UsQsID ,
UsAnswer ,
UsEmail ,
UsActive ,
UsAdmin ,
UsDesc ,
UsPwdExpDt ,
UsUpdDt ,
UsUpdUsID ,
UsSessionID
)
SELECT * FROM $oums.umuser WHERE 1=1;
-- update MnID in umpermission
-- ระบบทะเบียน
INSERT INTO $nums.umpermission
(pmUsID,pmMnID,pmSeq,pmX,pmC,pmR,pmU,pmD)
SELECT pmUsID,mn2,pmSeq,pmX,pmC,pmR,pmU,pmD
FROM $oums.umpermission
inner join
(
SELECT m1.MnID as mn1, m2.MnID as mn2
FROM $oums.`umpermission`
INNER JOIN $oums.ummenu m1 ON pmMnID = m1.MnID
INNER join $nums.ummenu m2
ON m1.MnNameT = m2.MnNameT
AND m1.MnLevel = m2.MnLevel
WHERE m1.MnStID IN (2,3,4) AND m2.MnStID = 70
GROUP BY pmMnID
)mnp on pmMnID = mn1;
-- ระบบประเมิน
INSERT INTO $nums.umpermission
(pmUsID,pmMnID,pmSeq,pmX,pmC,pmR,pmU,pmD)
SELECT pmUsID,mn2,pmSeq,pmX,pmC,pmR,pmU,pmD
FROM $oums.umpermission
inner join
(
SELECT m1.MnID as mn1, m2.MnID as mn2
FROM $oums.`umpermission`
INNER JOIN $oums.ummenu m1 ON pmMnID = m1.MnID
INNER join $nums.ummenu m2
ON m1.MnNameT = m2.MnNameT
AND m1.MnLevel = m2.MnLevel
WHERE m1.MnStID IN (16,17,18) AND m2.MnStID = 90
GROUP BY pmMnID
)mnp on pmMnID = mn1;
-- ระบบศิษย์เก่า
INSERT INTO $nums.umpermission
(pmUsID,pmMnID,pmSeq,pmX,pmC,pmR,pmU,pmD)
SELECT pmUsID,mn2,pmSeq,pmX,pmC,pmR,pmU,pmD
FROM $oums.umpermission
inner join
(
SELECT m1.MnID as mn1, m2.MnID as mn2
FROM $oums.`umpermission`
INNER JOIN $oums.ummenu m1 ON pmMnID = m1.MnID
INNER join $nums.ummenu m2
ON m1.MnNameT = m2.MnNameT
AND m1.MnLevel = m2.MnLevel
WHERE m1.MnStID =19 AND m2.MnStID = 190
GROUP BY pmMnID
)mnp on pmMnID = mn1;
-- เมนูที่เปลี่ยนชื่อ
INSERT INTO $nums.umpermission
(pmUsID ,pmMnID ,pmSeq,pmX,pmC,pmR,pmU,pmD)
SELECT pmUsID ,
(case pmMnID
WHEN 72 THEN '702053'
WHEN 125 THEN '702160'
WHEN 1805 THEN '1901811'
WHEN 1817 THEN '1901802'
else ''
end )pMnID,
pmSeq ,
pmX ,
pmC ,
pmR ,
pmU ,
pmD
FROM $oums.`umpermission`
WHERE pmMnID
IN ( 1805, 1817, 125, 72 ) ;
INSERT INTO $nums.umusergroup
(UgID ,
UgGpID ,
UgUsID
)
SELECT * FROM $oums.umusergroup WHERE 1=1;
-- 70078 : ภาระงานสอน , 70077 : ผู้จัดตารางสอน
UPDATE $nums.umusergroup
SET UgGpID =
CASE UgGpID
WHEN 1 THEN '10001'
WHEN 2 THEN '70070'
WHEN 3 THEN '70071'
WHEN 4 THEN '70072'
WHEN 31 THEN '90091'
WHEN 32 THEN '90090'
WHEN 33 THEN '90092'
WHEN 38 THEN '70078'
WHEN 39 THEN '70077'
WHEN 190 THEN '190190'
ELSE UgGpID
END;
-- update UsPsCode อาจารย์พิเศษ
UPDATE $nums.umuser um
INNER JOIN $dbreg.`Officer` reg
ON um.UsPsCode = reg.OfficerCode
INNER JOIN $dbmoreg.`rg_Person` upd
ON reg.officerId = upd.prsId
INNER JOIN $dbppc.`Personout` ppc
ON upd.prsUsId = ppc.psoutId
SET um.UsPsCode = concat('psout',ppc.psoutId)
WHERE reg.ofSitId = 2;
-- update UsPsCode อาจารย์ประจำ
UPDATE $nums.umuser
INNER JOIN $dbppc.Person ON UsPsCode = personCode
SET UsPsCode = personId;
";
echo $insert_user.'
';
/* scphkk
$update_url = "UPDATE $oums.ummenu upt
INNER JOIN $nums.ummenu mo ON upt.MnID=mo.MnID
SET upt.MnURL = mo.MnURL
WHERE upt.MnStID=70";
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -e '$update_url' ";
$last_line = system($mysql, $retval);
echo "UPDATE URL ".$oums;
if($retval){
echo "UPDATE MnURL MnStID=70 ";
echo " เกิดข้อผิดพลาด ";
echo $retval;
echo "
";
}else{
echo " สำเร็จ ";
}
$update_url = "UPDATE $oums.ummenu upt
INNER JOIN $nums.ummenu mo ON upt.MnID=mo.MnID
SET upt.MnURL = mo.MnURL
WHERE upt.MnStID=190";
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -e '$update_url' ";
$last_line = system($mysql, $retval);
echo "UPDATE URL ".$oums;
if($retval){
echo "UPDATE MnURL MnStID=190 ";
echo " เกิดข้อผิดพลาด ";
echo $retval;
echo "
";
}else{
echo " สำเร็จ ";
}
$update_url = "INSERT INTO $oums.ummenu
SELECT * FROM $nums.ummenu mo
WHERE mo.MnStID=190
AND mo.MnID NOT IN (SELECT MnID FROM $oums.ummenu WHERE MnStID=190)";
$mysql = "$path_mysql -h $host -u $tUsername -p'$tPassword' -e '$update_url' ";
$last_line = system($mysql, $retval);
echo "UPDATE URL ".$oums;
if($retval){
echo "INSERT Mn NOT IN MnStID=190 ";
echo " เกิดข้อผิดพลาด ";
echo $retval;
echo "
";
}else{
echo " สำเร็จ ";
}
$update_code = "
UPDATE $nums.umuser
INNER JOIN $dbppc.Person ON UsPsCode = personCode
SET UsPsCode = personId";
$mysql = "$path_mysql --default-character-set=utf8 -h $host -u $tUsername -p'$tPassword' --execute \"$update_code\" ";
$last_line = system($mysql, $retval);
echo "UPDATE UsPsCode ".$oums;
*/
}
//echo $mysql;
//exec($mysql);
// Printing additional info
//echo '
Last line of the output: ' . ($last_line)?'true':'false';
//echo '
Return value: ' . $retval;
if($retval){
echo "";
echo " เกิดข้อผิดพลาด ";
echo $retval;
echo "
";
}else{
echo " -- สำเร็จ ";
}
//infomation/script_mo_reg.sql
//echo "
ลบข้อมูลบนตารางทั้งหมด เสร็จสิ้น ";
?>
|