|
Server : Apache/2.2.2 (Fedora) System : Linux App1.pathumtani.go.th 2.6.20-1.2320.fc5smp #1 SMP Tue Jun 12 19:40:16 EDT 2007 i686 User : apache ( 48) PHP Version : 5.2.9 Disable Function : NONE Directory : /home/pathumthani_integration/integration/application/import_export/ |
Upload File : |
<?php
set_time_limit(600000);
session_start();
require_once('../../config/config_epm.inc.php');
require_once "../../common/classExcel/xls_class/class.writeexcel_workbook.inc.php";
require_once "../../common/classExcel/xls_class/class.writeexcel_worksheet.inc.php";
require_once '../../common/classExcel/xls_reader/reader.php';
$complete=0;
$noncom=0;
$ProfileID=$ProfileID;
$sql="SELECT tbl_prename.prename_th,tbl_prename.id FROM `tbl_prename`";
$result=mysql_query($sql)or die(mysql_error());
while($row=mysql_fetch_array($result)){
$val1=str_replace(' ','',($row[prename_th]));
if($val1!=""){
$pre_arr[$row[id]]=$val1;
}
}
# <function>
function get_address($strname,$type,$con=""){
if($con!=""){$xcon=" and areaid like '$con%'";}
$sql="SELECT ccName, ccDigi, ccType FROM `ccaa` where ccType='$type' and ccName like '%$strname%' $xcon";
$result=@mysql_query($sql);
if($result){
$row=mysql_fetch_array($result);
$id=$row[ccDigi];
mysql_free_result($result);
}
return $id;
}
function get_idaddress($x_address){
$arr_type[0]=array('type'=>"Changwat",'length'=>'2');
$arr_type[1]=array('type'=>"Aumpur",'length'=>'4');
$arr_type[2]=array('type'=>"Tamboon",'length'=>'6');
if(is_array($x_address)){
foreach($x_address as $index=>$val){
$id= get_address($val['name'],$arr_type[$index]['type'],$id_area);
$id_area=substr($id,0,$arr_type[$index]['length']);
$arr_addrress[$val['col']]=$id;
}
}
return $arr_addrress;
}
function phone($values){
$findme="begin";
$pos = strpos($values, $findme);
if ($pos === false) {
}else{
$values=substr($values,0,$pos);
}
return $values;
}
function split_name($value){
global $pre_arr;
$arr_name=explode(' ',$value);
$name=str_replace($pre_arr,'',$arr_name[0]);
$pre_name=trim(substr( trim($arr_name[0]),0,strlen(trim($arr_name[0]))-strlen(trim($name))));
$pre_id= array_search("$pre_name",$pre_arr);
return array('prename'=>"$pre_id",'prenames'=>"$pre_name",'fname'=>"$name",'lname'=>"$arr_name[1]");
}
function contact_name($value){
global $pre_arr;
$arr_name=explode(' ',$value);
$name=str_replace($pre_arr,'',$arr_name[0]);
$pre_name=trim(substr( trim($arr_name[0]),0,strlen(trim($arr_name[0]))-strlen(trim($name))));
$pre_id= array_search("$pre_name",$pre_arr);
return array('prename'=>"$pre_id",'prenames'=>"$pre_name",'fname'=>"$name",'lname'=>"$arr_name[1]",'contact_name'=>"$value");
}
function excel2array($filename_xml,$arr_column,$filter_col="",$filter_val=""){
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('UTF-8');
$data->read($filename_xml);
$ix=0;
foreach($data->sheets as $xid_sheet=>$sheet ){
for ($i = 1 ; $i <= $sheet['numRows'] ; $i++) { //rows
unset( $arr_addrress) ;
if(trim($data->sheets[0]['cells'][$i][2])!=""){
if(($filter_col==""&&$filter_val=="")||trim($sheet['cells'][$i][$filter_col]=="$filter_val")){
$ix++;
for ($f = 1 ; $f <= $sheet['numCols'] ; $f++) { //rows
if($i==1){//cloumn
}else{//data
$xkey=array();
$xkey=$arr_column[$f]['key_db'];
$values=iconv('utf-8','tis-620',$sheet['cells'][$i][$f] );
$values=($values=='null')?"":$values;
$substop=$arr_column[$f]['substop'];
$substart=$arr_column[$f]['substart'];
if($substop>0){
$values=substr($values,$substart,$substop);
}
if($arr_column[$f]['gofiunction']=="mege"){
if(is_array($arr_column[$f]['key_db'])){
foreach($arr_column[$f]['key_db'] as $in =>$xcol){
$values[$xcol]=$values;
}
}
}else if($arr_column[$f]['gofiunction']=="phone"){
$values=phone($values);
}else if($arr_column[$f]['gofiunction']=="split_name"){
$values=split_name($values);
}
else if($arr_column[$f]['gofiunction']=="contact_name"){
$values=contact_name($values);
}else if($arr_column[$f]['key_excel']=="tambol_name"||$arr_column[$f]['key_excel']=="amphor_name"||$arr_column[$f]['key_excel']=="province_name"){
$values="";
if(!isset($arr_addrress)){
$x_address=NULL;
if(is_array($arr_column[$f]['refcol'])){
foreach($arr_column[$f]['refcol'] as $in =>$xcol){
$name=iconv('utf-8','tis-620',$sheet['cells'][$i][$xcol] );
$x_address[]=array('col'=>"$xcol" ,'name'=>"$name") ;
}
}
$arr_addrress= get_idaddress($x_address);
}
$values=$arr_addrress[$f];
}
if(is_array($xkey)){
foreach($xkey as $index=>$val){
$strdata[$ix][$val].= (is_array($values))?$values[$val]:$values;
}
}
}
}
}
}
}
}
return $strdata;
}
function export2excel($ProfileID,$arr_data){
$fname = "save_temp/gen_".date('YmdHisuG').".xls";
$workbook = new writeexcel_workbook($fname);
$worksheet = $workbook->addworksheet($report_date); # à¾ÔèÁ work sheet
# End Variables - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$colors = array(
'black'=>0x08,
'blue'=>0x0C,
'brown'=>0x10,
'cyan'=>0x0F,
'gray'=>0x17,
'green'=>0x11,
'lime'=>0x0B,
'magenta'=>0x0E,
'navy'=>0x12,
'orange'=>0x35,
'purple'=>0x14,
'red'=>0x0A,
'silver'=>0x16,
'white'=>0x09,
'yellow'=>0x0D
);
# Header Styles - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$title =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$title =& $workbook->addformat();
$title->set_bold(1);
$title->set_size(10);
$title->set_top(0);
$title->set_left(0);
$title->set_bottom(0);
$title->set_align('center');
$title->set_align('vcenter');
$title->set_merge(); # This is the key feature
$title_child_cell =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$title_child_cell =& $workbook->addformat();
$title_child_cell->set_size(10);
$title_child_cell->set_top(0);
$title_child_cell->set_right(0);
$title_child_cell->set_left(0);
$title_child_cell->set_align('center');
$title_child_cell->set_align('vcenter');
$title_child_cell->set_merge(); # This is the key feature
$heading_merge =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$heading_merge =& $workbook->addformat();
$heading_merge->set_bold(1);
$heading_merge->set_size(10);
$heading_merge->set_top(1);
$heading_merge->set_left(1);
$heading_merge->set_right(1);
$heading_merge->set_bottom(1);
$heading_merge->set_align('center');
$heading_merge->set_align('vcenter');
$heading_merge->set_merge();
$heading_merge_no_top =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$heading_merge_no_top =& $workbook->addformat();
$heading_merge_no_top->set_bold(1);
$heading_merge_no_top->set_size(10);
$heading_merge_no_top->set_top(0);
$heading_merge_no_top->set_left(1);
$heading_merge_no_top->set_right(1);
$heading_merge_no_top->set_bottom(1);
$heading_merge_no_top->set_align('center');
$heading_merge_no_top->set_align('vcenter');
$heading_merge_no_top->set_merge();
$heading_merge_no_bottom =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$heading_merge_no_bottom =& $workbook->addformat(array('fg_color' =>$colors['blue']));
$heading_merge_no_bottom->set_bold(1);
$heading_merge_no_bottom->set_size(10);
$heading_merge_no_bottom->set_top(1);
$heading_merge_no_bottom->set_left(1);
$heading_merge_no_bottom->set_right(1);
$heading_merge_no_bottom->set_bottom(0);
$heading_merge_no_bottom->set_align('center');
$heading_merge_no_bottom->set_align('vcenter');
$heading_merge_no_bottom->set_merge();
$heading_merge_no_top_bottom =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$heading_merge_no_top_bottom =& $workbook->addformat();
$heading_merge_no_top_bottom->set_bold(1);
$heading_merge_no_top_bottom->set_size(10);
$heading_merge_no_top_bottom->set_top(0);
$heading_merge_no_top_bottom->set_left(1);
$heading_merge_no_top_bottom->set_right(1);
$heading_merge_no_top_bottom->set_bottom(0);
$heading_merge_no_top_bottom->set_align('center');
$heading_merge_no_top_bottom->set_align('vcenter');
$heading_merge_no_top_bottom->set_merge();
$body =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$body =& $workbook->addformat();
$body->set_size(10);
$body->set_top(1);
$body->set_left(1);
$body->set_right(1);
$body->set_bottom(1);
$body->set_align('center');
$body->set_align('vcenter');
$body_left =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$body_left =& $workbook->addformat();
$body_left->set_size(10);
$body_left->set_top(1);
$body_left->set_left(1);
$body_left->set_right(1);
$body_left->set_bottom(1);
$body_left->set_align('vcenter');
$body_left_bold =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$body_left_bold =& $workbook->addformat();
$body_left_bold->set_bold(1);
$body_left_bold->set_size(10);
$body_left_bold->set_top(1);
$body_left_bold->set_left(1);
$body_left_bold->set_right(1);
$body_left_bold->set_bottom(1);
$body_left_bold->set_align('vcenter');
$body_bold =& $workbook->addformat(array('font'=>'CordiaUPC','color'=>'black','size'=>14,'text_wrap'=>1));
$body_bold =& $workbook->addformat();
$body_bold->set_bold(1);
$body_bold->set_size(10);
$body_bold->set_top(1);
$body_bold->set_left(1);
$body_bold->set_right(1);
$body_bold->set_bottom(1);
$body_bold->set_align('center');
$body_bold->set_align('vcenter');
####################################################START write header column
$sql="SELECT tbl_xmldata.PROFILE_ID, tbl_xmldata.ELEMENT,tbl_xmldata.SQL_FIELD FROM `tbl_xmldata` where SQL_FIELD<>'' and PROFILE_ID='$ProfileID' and ELEMENT<>''";
$result=mysql_query($sql);
$col=0;
while($row=mysql_fetch_array($result)){
$arr_column[$row[ELEMENT]]="$col";
$col++;
}
//$arr_column=$arr_data[1];
if(is_array($arr_column)){
foreach ($arr_column as $index=>$col){
$worksheet->write($xlsRow,$col , $index, $heading_merge_no_bottom);
}
}
################################################### END write header column
$xlsRow++;
if(is_array($arr_data)){
foreach ($arr_data as $indexdata=>$datalist){
foreach ($datalist as $index=>$values){
$col_index=$arr_column[$index];
if($col_index!=""){
if(trim($values)!=""){
$values= $values;
}
$worksheet->write($xlsRow,$col_index , $values, $body);
}
}
$xlsRow++;
}
}
$worksheet->set_landscape(1); #Lanscape Page
$worksheet->set_margin_left(0.35);
$worksheet->set_margin_right(0.35);
$worksheet->set_margin_top(0.35);
$worksheet->set_margin_bottom(0.35);
$workbook->close();
echo "<meta http-equiv=\"refresh\" content=\"1;url=$fname\">";
die();
}
# </function>
// $ProfileID="3";
$run=0;
if($ProfileID=="4"){ //·ÐàºÕ¹¤¹ÇèÒ§§Ò¹
$run=1;
$arr_column[2]=array('colno'=>'2','key_excel'=>"txDate",'key_db'=>array("add_date"));
$arr_column[3]=array('colno'=>'3','key_excel'=>"pid",'key_db'=>array("id_card"));
$arr_column[4]=array('colno'=>'4','key_excel'=>"name",'key_db'=>array("prename","fname","lname"),'gofiunction'=>"split_name");
$arr_column[5]=array('colno'=>'5','key_excel'=>"sex",'key_db'=>array("sex"));
$arr_column[6]=array('colno'=>'6','key_excel'=>"birthDate",'key_db'=>array("birthdate"));
$arr_column[7]=array('colno'=>'7','key_excel'=>"education_code",'key_db'=>array("education_id"));
$arr_column[8]=array('colno'=>'7','key_excel'=>"education_name",'key_db'=>array("branch"));
$arr_column[9]=array('colno'=>'9','key_excel'=>"position_code",'key_db'=>array("position_id"));
$arr_column[11]=array('colno'=>'11','key_excel'=>"txType",'filter'=>"0");
$arr_column[15]=array('colno'=>'15','key_excel'=>"tambol_name",'key_db'=>array("p_tambol_id"),'refcol'=>array(17,16,15));
$arr_column[16]=array('colno'=>'16','key_excel'=>"amphor_name",'key_db'=>array("p_ampur_id"),'refcol'=>array(17,16,15));
$arr_column[17]=array('colno'=>'17','key_excel'=>"province_name",'key_db'=>array("p_province_id"),'refcol'=>array(17,16,15));
$arr_column[18]=array('colno'=>'18','key_excel'=>"zipCode",'key_db'=>array("p_zipcode"));
$arr_column[19]=array('colno'=>'19','key_excel'=>"telNo",'key_db'=>array("p_tel_h"),'gofiunction'=>"phone");
$xfilter='11';
$xfilter_val='0';
}else if($ProfileID=="6"){ //µÓá˹觧ҹÇèÒ§
$run=1;
$arr_column[2]=array('colno'=>'2','key_excel'=>"position_code",'key_db'=>array("position_id"));
$arr_column[5]=array('colno'=>'5','key_excel'=>"repQty",'key_db'=>array("posion_qty"));
$arr_column[8]=array('colno'=>'8','key_excel'=>"sex",'key_db'=>array("gendor"));
$arr_column[10]=array('colno'=>'10','key_excel'=>"ageStart",'key_db'=>array("age_from"));
$arr_column[11]=array('colno'=>'11','key_excel'=>"ageStop",'key_db'=>array("age_to"));
$arr_column[13]=array('colno'=>'13','key_excel'=>"wage",'key_db'=>array("salary_from","salary_to"),'gofiunction'=>"mege");
$arr_column[14]=array('colno'=>'14','key_excel'=>"wageUnit",'key_db'=>array("unit_id"));
$arr_column[18]=array('colno'=>'18','key_excel'=>"eduLevelStart",'key_db'=>array("education_from"));
$arr_column[19]=array('colno'=>'19','key_excel'=>"eduLevelStop",'key_db'=>array("education_to"));
$arr_column[20]=array('colno'=>'20','key_excel'=>"eid",'key_db'=>array("company_id"));
$arr_column[29]=array('colno'=>'29','key_excel'=>"zipCode",'key_db'=>array("d_zipcode"));
$arr_column[30]=array('colno'=>'30','key_excel'=>"telNo",'key_db'=>array("tel_p"),'gofiunction'=>"phone");
$arr_column[31]=array('colno'=>'31','key_excel'=>"contact_pid",'key_db'=>array("contact_pid "));
$arr_column[32]=array('colno'=>'32','key_excel'=>"contact_name",'key_db'=>array("prename","fname","lname","contact_name"),'gofiunction'=>"contact_name");
$arr_column[33]=array('colno'=>'33','key_excel'=>"contact_position",'key_db'=>array("contact_position"));
$arr_column[34]=array('colno'=>'34','key_excel'=>"expireDate",'key_db'=>array("date_expire"));
$arr_column[22]=array('colno'=>'22','key_excel'=>"name",'key_db'=>array("company_name"));
$arr_column[39]=array('colno'=>'39','key_excel'=>"welfare1",'key_db'=>array("welfare"));
$arr_column[40]=array('colno'=>'40','key_excel'=>"welfare2",'key_db'=>array("welfare"));
$arr_column[41]=array('colno'=>'41','key_excel'=>"welfare3",'key_db'=>array("welfare"));
$arr_column[42]=array('colno'=>'42','key_excel'=>"welfare4",'key_db'=>array("welfare"));
$arr_column[43]=array('colno'=>'43','key_excel'=>"welfare5",'key_db'=>array("welfare"));
$arr_column[26]=array('colno'=>'15','key_excel'=>"tambol_name",'key_db'=>array("d_tambol_id"),'refcol'=>array(28,27,26));
$arr_column[27]=array('colno'=>'16','key_excel'=>"amphor_name",'key_db'=>array("d_ampur_id"),'refcol'=>array(28,27,26));
$arr_column[28]=array('colno'=>'17','key_excel'=>"province_name",'key_db'=>array("d_province_id"),'refcol'=>array(28,27,26));
}
if($run){
$arr_data=excel2array($xmlfile,$arr_column,$xfilter,$xfilter_val);
export2excel($ProfileID,$arr_data);
}else{
echo "¢ÍÍÀÑÂâ»Ãä¿Åì·ÕèàÅ×èÍ¡ÂѧäÁèä´é¶Ù¡ÊÃéÒ§";
}
// echo"<pre>";
// print_r($arr);
// echo"</pre>";
?>