Viewing file: testdatabase2.php (20.84 KB) -rw-r--r-- Select action/file-type: (+) | (+) | (+) | Code (+) | Session (+) | (+) | SDB (+) | (+) | (+) | (+) | (+) | (+) |
/*
กำหนดค่า DSN หากฐานข้อมูลต้องการ User และ Password ก็ให้กำหนดด้วย
เช่นจะใช้ DSN ชื่อ phptest ส่วน user และ password ไม่ใช้ก็ดังนี้
$dsn="asset";
$uid="";
$pwd="";
ฟังก์ชันติดต่อ ต้องการให้ผ่านค่าทั้ง 3 เข้าไป และจะคืนค่าออกมา เราก็รับค่าที่คืนออกมาลงตัวแปร
เช่นลงที่ hdn โดยที่หากติดต่อสำเร็จ hdn จะมีค่าเป็นหมายเลขบวกเพื่อนำใชกับฟังก์ชันอื่นต่อไป
หากติดต่อไม่ได้ hdn จะเป็นเท็จ ก็ควรจะมีการตรวจสอบว่า เป็นเท็จหรือไม่
หากใช่ก็จะได้ให้ลิกงานเลย แต่ในที่นี้ผมไม่ได้เขียนเอาไว้ ให้ทำเองครับ
$hdn = odbc_connect($dsn,$uid,$pwd);
// กำหนดคำสั่ง SQL ที่จะใช้ และใช้
$sql="select * from TAsset";
$fdn= odbc_exec($hdn, $sql);
//หาจำนวนคอลัมน์และนำชื่อคอลัมน์ออกมาแสดงไปทีละคอลัมน์
$nf= odbc_num_fields($fdn);
echo "";
for($i=1;$i<=$nf;$i++){
echo "",odbc_field_name($fdn,$i)," | ";
}
echo " ";
// แสดงรายการไปทีละรายการ รายการละทีคอลัมน์จนกว่าจะหมด
while (odbc_fetch_row($fdn)){
for($i=1;$i<=$nf;$i++){
echo " ",odbc_result($fdn,$i)," | ";
}
echo "";
}
echo " ";
// ยกเลิก ปิดการติดต่อ
odbc_close($hdn);
*/
?>
//$dsn="staff";
//$sql="select * from TEmployee, TWorkStatus where work_status.TEmployee=id.TWorkStatus";
?>
มาตราฐาน 3-1
$dsn_name = "staff";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT *,TEmployee.id as tempid,TPrefix.name as prefixname FROM TEmployee,TWorkStatus,TPosScale,TPrefix WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TEmployee.Prefix_id=TPrefix.id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT *,TEmployee.id as tempid,TPrefix.name as prefixname,TPosition.name as positionname FROM TEmployee,TWorkStatus,TPosScale,TPrefix,TPosition WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TEmployee.Prefix_id=TPrefix.id) and (TWorkStatus.id = '0') and (TPosScale.TPos_id=TPosition.id) and (TPosScale.TPos_id = '0014' or TPosScale.TPos_id = '0017') order by TEmployee.id";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
รหัส
|
เลขบัตร
|
เลขตำแหน่ง
|
ชื่อตำแหน่ง
|
คำนำหน้า
|
name
|
surname
|
วันที่เริ่มงาน
|
อายุการทำงาน
|
$i=1;
$num1=0;
while(odbc_fetch_row($execute,$i))
{
$num1++;
$id=odbc_result($execute,"temp_id");
$idx=odbc_result($execute,"idx");
$TPos_id=odbc_result($execute,"TPos_id");
$positionname=odbc_result($execute,"positionname");
$prefixname=odbc_result($execute,"prefixname");
$name=odbc_result($execute,"fname");
$surname=odbc_result($execute,"lname");
$in_date=odbc_result($execute,"trans_date");
$datenow=date("Y-m");
$datenow_Month = substr($datenow, 5, 2);
$datenow_Month = sprintf("%01d", $datenow_Month);
$datenow_Year = substr($datenow, 0, 4);
$datenow="$datenow_Year-$datenow_Month";
$datecheck=$in_date;
$in_date_num=0;
if($in_date!="")
{
$i2=1;
$n=3;
while ($i2!=$n)
{
$datecheck_Date = substr($datecheck, 8, 2);
$datecheck_Date = sprintf("%01d", $datecheck_Date);
$datecheck_Month = substr($datecheck, 5, 2);
$datecheck_Month = sprintf("%01d", $datecheck_Month);
$datecheck_Year = substr($datecheck, 0, 4);
$datecheck = mktime(0,0,0,$datecheck_Month+1,$datecheck_Date,$datecheck_Year);
$datecheck = date("Y-m-d",$datecheck);
$datecheck_Month = substr($datecheck, 5, 2);
$datecheck_Month = sprintf("%01d", $datecheck_Month);
$datecheck_Year = substr($datecheck, 0, 4);
$datecheck2="$datecheck_Year-$datecheck_Month";
//echo"$datecheck $datecheck2 $datenow ";
$in_date_num++;
if($datecheck2==$datenow)
{
$n=1;
}
}
}
?>
echo "$id"; ?>
|
echo "$idx"; ?>
|
echo "$TPos_id"; ?>
|
echo "$positionname"; ?>
|
echo "$prefixname"; ?>
|
echo "$name"; ?>
|
echo "$surname"; ?>
|
echo "$in_date"; ?>
|
echo "$in_date_num"; ?>
|
$i++;
}
?>
echo "$num $num1";
?>
odbc_close($connect);
?>
//-------------------------------------โครงการ
$dsn_name = "project";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//2008-06-01,2009-04-31
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT TProject.id as a, TProject.NameT ,TProject.NameE ,TProject.Type_id, TProject.TypeSub_id FROM TProject, TTypeProject, TTypeProjectSub WHERE (TProject.Type_id=TTypeProject.id) and (TProject.TypeSub_id=TTypeProjectSub.id)and (TProject.Type_id='1') and (TProject.TypeSub_id='01')";
//$sql = "SELECT * FROM TProject";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
//$num = odbc_num_rows($execute);odbc_num_fields
$num = odbc_num_fields($execute);
?>
รหัสกิจกรรม
|
ชื่อกิจกรรม ไทย
|
ชื่อกิจกรรม อังกฤษ
|
ชื่อประเ้ภทกิจกรรม
|
ชื่อประเ้ภทกิจกรรมย่อย
|
วันที่เริ่มกิจกรรม
|
$i=1;
$num1=0;
while($row = odbc_fetch_array($execute))
{
$TProject_id=$row['a'];
$TProject_NameT=$row["NameT"];
$TProject_NameE=$row["NameE"];
$TProject_Type_id=$row["Type_id"];
$TProject_TypeSub_id=$row["TypeSub_id"];
$TProject_sdate=$row["sdate"];
$sql2 = "SELECT * FROM TTypeProject WHERE (id='$TProject_Type_id')";
//$sql = "SELECT * FROM TProject";
$execute2 = odbc_exec($connect, $sql2) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row2 = odbc_fetch_array($execute2))
{
$TTypeProject_id=$row2["id"];
$TTypeProject_name=$row2["name"];
}
$sql3 = "SELECT * FROM TTypeProjectSub WHERE (id='$TProject_TypeSub_id')";
//$sql = "SELECT * FROM TProject";
$execute3 = odbc_exec($connect, $sql3) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row3 = odbc_fetch_array($execute3))
{
$TTypeProjectSub_id=$row3["id"];
$TTypeProjectSub_name=$row3["name"];
}
$num1++;
//$name_t=odbc_result($execute,"NameT");
//$name_e=odbc_result($execute,"NameE");
//$type_id=odbc_result($execute,"TTypeProject.id");
//$type_name=odbc_result($execute,"TTypeProject.name");
//$typesub_id=odbc_result($execute,"TTypeProjectSub.id");
//$typesub_name=odbc_result($execute,"TTypeProjectSub.name");
?>
echo "$TProject_id"; ?>
|
echo "$TProject_NameT"; ?>
|
echo "$TProject_NameE"; ?>
|
echo "$TTypeProject_id $TTypeProject_name"; ?>
|
echo "$TTypeProjectSub_id $TTypeProjectSub_name"; ?>
|
echo "$TProject_sdate"; ?>
|
$i++;
}
?>
echo "$num $num1";
?>
odbc_close($connect);
?>
มาตราฐาน 3-2
//-------------------------------------------------------------------------มาตราฐาน 3.2
$dsn_name = "staff";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TFileCommittee, TEmployee WHERE TFileCommittee.TEmp_id=TEmployee.Id";
$sql = "SELECT *,TEmployee.id as tempid FROM TFileCommittee, TEmployee,TWorkStatus,TPosScale WHERE TFileCommittee.TEmp_id=TEmployee.Id and (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT *,TEmployee.id as temp_id FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TProject";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
รหัสอาจารย์
|
ชื่ออาจารย์
|
นามสกุลอาจารย์
|
ลำดับ
|
ชื่อเรื่อง
|
$i=1;
$num1=0;
while($row = odbc_fetch_array($execute))
{
$TEmployee_id=$row['tempid'];
$TEmployee_fname=$row["fname"];
$TEmployee_lname=$row["lname"];
$TFileCommittee_order_no=$row["order_no"];
$TFileCommittee_ttile=$row["TTitle"];
//$name_t=odbc_result($execute,"NameT");
//$name_e=odbc_result($execute,"NameE");
//$type_id=odbc_result($execute,"TTypeProject.id");
//$type_name=odbc_result($execute,"TTypeProject.name");
//$typesub_id=odbc_result($execute,"TTypeProjectSub.id");
//$typesub_name=odbc_result($execute,"TTypeProjectSub.name");
if($TEmployee_id!=$TEmployee_id_check)
{
$num1++;
?>
echo "$TEmployee_id"; ?>
|
echo "$TEmployee_fname"; ?>
|
echo "$TEmployee_lname"; ?>
|
echo "$TFileCommittee_order_no"; ?>
|
echo "$TFileCommittee_ttile"; ?>
|
$TEmployee_id_check=$TEmployee_id;
}
//------------------------------------------------------------------------------------
else
{
?>
echo ""; ?>
|
echo ""; ?>
|
echo ""; ?>
|
echo "$TFileCommittee_order_no"; ?>
|
echo "$TFileCommittee_ttile"; ?>
|
$TEmployee_id_check=$TEmployee_id;
}
$i++;
}
?>
echo "$num1";
?>
odbc_close($connect);
?>
มาตราฐาน 3-4
//-------------------------------------------------------------------------มาตราฐาน 3.4
$dsn_name = "staff";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
$i=1;
$num1=0;
while(odbc_fetch_row($execute,$i))
{
$num1++;
$id=odbc_result($execute,"id");
$name=odbc_result($execute,"fname");
$surname=odbc_result($execute,"lname");
?>
echo "$id"; ?>
|
echo "$name"; ?>
|
echo "$surname"; ?>
|
$i++;
}
echo "$num1";
?>
odbc_close($connect);
?>
//-------------------------------------โครงการ
$dsn_name = "project";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT TProject.id as a, TProject.NameT ,TProject.NameE ,TProject.Type_id, TProject.TypeSub_id, TProject.budget3 , TProject.budget4 FROM TProject, TTypeProject, TTypeProjectSub WHERE (TProject.Type_id=TTypeProject.id) and (TProject.TypeSub_id=TTypeProjectSub.id)and (TProject.Type_id='1') and (TProject.TypeSub_id='01') ";
//$sql = "SELECT * FROM TProject";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
รหัสกิจกรรม
|
ชื่อกิจกรรม ไทย
|
ชื่อกิจกรรม อังกฤษ
|
ชื่อประเ้ภทกิจกรรม
|
ค่าใช้จ่าย
|
มูลค่า
|
$i=1;
$num1=0;
$price_budget3=0;
$price_budget4=0;
while($row = odbc_fetch_array($execute))
{
$TProject_id=$row['a'];
$TProject_NameT=$row["NameT"];
$TProject_NameE=$row["NameE"];
$TProject_Type_id=$row["Type_id"];
$TProject_TypeSub_id=$row["TypeSub_id"];
$TProject_budget3=$row['budget3'];
$TProject_budget4=$row['budget4'];
$price_budget3=$price_budget3+$TProject_budget3;
$price_budget4=$price_budget4+$TProject_budget4;
$sql2 = "SELECT * FROM TTypeProject WHERE (id='$TProject_Type_id')";
//$sql = "SELECT * FROM TProject";
$execute2 = odbc_exec($connect, $sql2) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row2 = odbc_fetch_array($execute2))
{
$TTypeProject_id=$row2["id"];
$TTypeProject_name=$row2["name"];
}
$sql3 = "SELECT * FROM TTypeProjectSub WHERE (id='$TProject_TypeSub_id')";
//$sql = "SELECT * FROM TProject";
$execute3 = odbc_exec($connect, $sql3) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row3 = odbc_fetch_array($execute3))
{
$TTypeProjectSub_id=$row3["id"];
$TTypeProjectSub_name=$row3["name"];
}
$num1++;
//$name_t=odbc_result($execute,"NameT");
//$name_e=odbc_result($execute,"NameE");
//$type_id=odbc_result($execute,"TTypeProject.id");
//$type_name=odbc_result($execute,"TTypeProject.name");
//$typesub_id=odbc_result($execute,"TTypeProjectSub.id");
//$typesub_name=odbc_result($execute,"TTypeProjectSub.name");
?>
echo "$TProject_id"; ?>
|
echo "$TProject_NameT"; ?>
|
echo "$TProject_NameE"; ?>
|
echo "$TTypeProject_id $TTypeProject_name"; ?>
|
echo "$TProject_budget3"; ?>
|
echo "$TProject_budget4"; ?>
|
$i++;
}
echo "$num1 ราคาค่าใช้จ่าย$price_budget3 มูลค่า$price_budget4";
?>
odbc_close($connect);
?>
//-------------------------------------------------------------------------มาตราฐาน 3.6
$dsn_name = "staff";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
$i=1;
$num1=0;
while(odbc_fetch_row($execute,$i))
{
$num1++;
$id=odbc_result($execute,"id");
$name=odbc_result($execute,"fname");
$surname=odbc_result($execute,"lname");
?>
echo "$id"; ?>
|
echo "$name"; ?>
|
echo "$surname"; ?>
|
$i++;
}
echo "$num1";
?>
odbc_close($connect);
?>
//-------------------------------------โครงการ
$dsn_name = "project";
$username ="";
$password ="";
$connect= odbc_connect($dsn_name, $username, $password) or die("ติดต่อ DSN ไม่ได้");
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
//$sql = "SELECT * FROM TEmployee,TWorkStatus,TPosScale WHERE (TEmployee.work_status=TWorkStatus.id) and (TEmployee.Id=TPosScale.TEmp_id) and (TWorkStatus.id = '0') and (TEmployee.id like '01%') and (TPosScale.TDep_id = '0702')";
$sql = "SELECT TProject.id as a, TProject.NameT ,TProject.NameE ,TProject.Type_id, TProject.TypeSub_id, TProject.budget3 , TProject.budget4, TProject.budget2 FROM TProject, TTypeProject, TTypeProjectSub WHERE (TProject.Type_id=TTypeProject.id) and (TProject.TypeSub_id=TTypeProjectSub.id)and (TProject.Type_id='1') and (TProject.TypeSub_id='01') ";
//$sql = "SELECT * FROM TProject";
$execute = odbc_exec($connect, $sql) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
$num = odbc_num_rows($execute);
?>
รหัสกิจกรรม
|
ชื่อกิจกรรม ไทย
|
ชื่อกิจกรรม อังกฤษ
|
ชื่อประเ้ภทกิจกรรม
|
รายรับ
|
$i=1;
$num1=0;
$price_budget2=0;
$price_budget3=0;
$price_budget4=0;
while($row = odbc_fetch_array($execute))
{
$TProject_id=$row['a'];
$TProject_NameT=$row["NameT"];
$TProject_NameE=$row["NameE"];
$TProject_Type_id=$row["Type_id"];
$TProject_TypeSub_id=$row["TypeSub_id"];
$TProject_budget2=$row['budget2'];
$TProject_budget3=$row['budget3'];
$TProject_budget4=$row['budget4'];
$price_budget2=$price_budget2+$TProject_budget2;
$price_budget3=$price_budget3+$TProject_budget3;
$price_budget4=$price_budget4+$TProject_budget4;
$sql2 = "SELECT * FROM TTypeProject WHERE (id='$TProject_Type_id')";
//$sql = "SELECT * FROM TProject";
$execute2 = odbc_exec($connect, $sql2) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row2 = odbc_fetch_array($execute2))
{
$TTypeProject_id=$row2["id"];
$TTypeProject_name=$row2["name"];
}
$sql3 = "SELECT * FROM TTypeProjectSub WHERE (id='$TProject_TypeSub_id')";
//$sql = "SELECT * FROM TProject";
$execute3 = odbc_exec($connect, $sql3) or die ("เอ็กซิคิวส์คำสั่งไม่ได้");
while($row3 = odbc_fetch_array($execute3))
{
$TTypeProjectSub_id=$row3["id"];
$TTypeProjectSub_name=$row3["name"];
}
$num1++;
//$name_t=odbc_result($execute,"NameT");
//$name_e=odbc_result($execute,"NameE");
//$type_id=odbc_result($execute,"TTypeProject.id");
//$type_name=odbc_result($execute,"TTypeProject.name");
//$typesub_id=odbc_result($execute,"TTypeProjectSub.id");
//$typesub_name=odbc_result($execute,"TTypeProjectSub.name");
?>
echo "$TProject_id"; ?>
|
echo "$TProject_NameT"; ?>
|
echo "$TProject_NameE"; ?>
|
echo "$TTypeProject_id $TTypeProject_name"; ?>
|
echo "$TProject_budget2"; ?>
|
$i++;
}
?>
echo "$num1 รายรับ$price_budget2";
?>
odbc_close($connect);
?>
|