|
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/industry/ |
Upload File : |
<?php
session_start();
set_time_limit(0); # ไม่ให้ time out
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";
# Variables - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
$xlsname = "industry.xls";
$fname = tempnam("./tmp", "tmp_demo.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');
# End Header Styles - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# Write heading =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# Set Column
$xlsRow = 0; # เริ่มต้นแถวที่ 0
/*$worksheet->set_column('A:B', 16.43);
$worksheet->set_column('B:C', 8.29);
$worksheet->set_column('C:D', 8.29);
$worksheet->set_column('D:E', 8.29);
$worksheet->set_column('E:F', 8.29);
$worksheet->set_column('F:G', 8.29);
$worksheet->set_column('G:H', 8.29);
$worksheet->set_column('H:I', 8.29);
$worksheet->set_column('I:J', 8.29);
# Set Header I
$xlsRow++;
$worksheet->write('A'.$xlsRow , "ตัวอย่างการส่งออกข้อมูลในรูปแบบ Excel", $title);
$worksheet->write_blank('B'.$xlsRow, $title_child_cell);
$worksheet->write_blank('C'.$xlsRow, $title_child_cell);
$worksheet->write_blank('D'.$xlsRow, $title_child_cell);
$worksheet->write_blank('E'.$xlsRow, $title_child_cell);
$worksheet->write_blank('F'.$xlsRow, $title_child_cell);
$worksheet->write_blank('G'.$xlsRow, $title_child_cell);
$worksheet->write_blank('H'.$xlsRow, $title_child_cell);
$worksheet->write_blank('I'.$xlsRow, $title_child_cell);*/
//echo chr(90);
$sqlCOLUMNS_1=" SELECT
`COLUMNS`.COLUMN_NAME,
`COLUMNS`.COLUMN_COMMENT
FROM COLUMNS
WHERE `COLUMNS`.TABLE_NAME = 'industry_register' AND `COLUMNS`.TABLE_SCHEMA = 'pathumthani_integration' ";
$rsCOLUMNS_1=mysql_db_query("information_schema",$sqlCOLUMNS_1);
$start=65;
$start1=65;
$end=90;
$run=$start;
$first1=$start;
while($resCOLUMNS_1=mysql_fetch_assoc($rsCOLUMNS_1)){
if($resCOLUMNS_1['COLUMN_NAME']!='gpsx' && $resCOLUMNS_1['COLUMN_NAME']!='gpsy' && $resCOLUMNS_1['COLUMN_NAME']!='import_status' && $resCOLUMNS_1['COLUMN_NAME']!='update_time' && $resCOLUMNS_1['COLUMN_NAME']!='type_id'){
if($start==65){
$arrCha[]=chr($run);
$run++;
if($run>90){
$start++;
$run=$start1;
}
}
if($start>65){
$arrCha[]=chr($first1).chr($run);
$run++;
if($run>90){
$start++;
$run=$start1;
$first1++;
}
}
$colname[]=$resCOLUMNS_1['COLUMN_NAME'];
}
}
/*echo '<pre>';
print_r($arrCha);
echo '<pre>';
print_r($colname);*/
$countNum=count($colname);
$xlsRow=1;
for($i=0;$i<$countNum;$i++){
$tem=$arrCha[$i];
/*echo $tem.$xlsRow.":";
echo $colname[$i];
echo "<br/>";*/
$worksheet->write($tem.$xlsRow ,$colname[$i], $heading_merge_no_bottom);
}
//exit();
# Set Header II
/*$xlsRow++;
$worksheet->write('A'.$xlsRow , "no.", $heading_merge_no_bottom);
$worksheet->write('B'.$xlsRow, "type_id", $heading_merge);
$worksheet->write('C'.$xlsRow, "tcode", $heading_merge);
$worksheet->write('D'.$xlsRow, "tsic", $heading_merge);
$worksheet->write('E'.$xlsRow, "full_code", $heading_merge);
$worksheet->write('F'.$xlsRow, "number", $heading_merge);
$worksheet->write('G'.$xlsRow, "fyear", $heading_merge);
$worksheet->write('H'.$xlsRow, "eyear", $heading_merge);
$worksheet->write('I'.$xlsRow, "code", $heading_merge);*/
# End Write heading =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# Body Information =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
$strSQL = "SELECT *
FROM `industry_register`
WHERE code IS NOT NULL
";
if($_GET['code']){
$strSQL.=" and code LIKE('%".$_GET['code']."%') ";
}
if($_GET['fname']){
$strSQL.=" and fname LIKE('%".$_GET['fname']."%') ";
}
if($_GET['location']){
$strSQL.=" and location LIKE('%".$_GET['location']."%') ";
}
if($_GET['ampur']){
$sqlAmpur=" SELECT ccaa.ccDigi,ccaa.ccName,ccaa.ccType FROM ccaa WHERE ccaa.ccType = 'Aumpur' AND ccaa.ccDigi LIKE '".substr($_GET['ampur'],0,4)."%' ";
$rsAmpur=mysql_db_query($dbname,$sqlAmpur);
$resultAmpur=mysql_fetch_assoc($rsAmpur);
$strSQL.=" and ampur like '%".str_replace('อำเภอ','',$resultAmpur['ccName'])."%' ";
}
if($_GET['tambol']){
$sqlTambol=" SELECT ccaa.ccDigi,ccaa.ccName,ccaa.ccType FROM ccaa WHERE ccaa.ccType = 'Tamboon' AND ccaa.ccDigi LIKE '".substr($_GET['tambol'],0,6)."%' ";
$rsTambol=mysql_db_query($dbname,$sqlTambol);
$resultTambol=mysql_fetch_assoc($rsTambol);
$strSQL.=" and tambol like '%".str_replace('ตำบล','',$resultTambol['ccName'])."%' ";
$strSearch.="&tambol=".$resultTambol['ccName'];
}
if($_GET['muban']){
$strSQL.=" and muban LIKE('%".$_GET['muban']."%') ";
}
if($_GET['type_id']){
$sqlTypeID1=" SELECT * FROM industry_type WHERE type_id='".$_GET['type_id']."' ";
$rsTypeID1=mysql_db_query($dbname,$sqlTypeID1);
$resultTypeID1=mysql_fetch_assoc($rsTypeID1);
$sqlTypeID=" SELECT * FROM industry_type WHERE parent_id='".$resultTypeID1['runid']."' ";
$rsTypeID=mysql_db_query($dbname,$sqlTypeID);
$str_no1='';
while($resultTypeID=mysql_fetch_assoc($rsTypeID)){
$str_no1.="'".$resultTypeID['type_id']."'".',';
}
$str_no1=substr($str_no1,0,strlen($str_no1)-1);
if($str_no1==''){
$strSQL.=" and type_id ='-999' ";
}else{
$strSQL.=" and type_id IN($str_no1) ";
}
}
if($_GET['fyear']){
$strSQL.=" and fyear = '".$_GET['fyear']."' ";
}
if($_GET['eyear']){
$strSQL.=" and eyear = '".$_GET['eyear']."' ";
}
if($_GET['import_status']){
$strSQL.=" and import_status = '".($_GET['import_status'])."' ";
}
if($_GET['import_status1']){
if($_GET['import_status1']!='-1'){
$strSQL.=" and import_status = '".($_GET['import_status1'])."' ";
}else{
$strSQL.=" and import_status != '4' ";
}
}
if($_GET['fact_class']){
$strSQL.=" and fact_class = '".$_GET['fact_class']."' ";
}
$intA = 1;
$rsConn = mysql_db_query($dbname,$strSQL);
while( $Result = mysql_fetch_assoc($rsConn) ) {
$xlsRow++;
for($i=0;$i<$countNum;$i++){
$tem=$arrCha[$i];
/*echo $tem.$xlsRow.":";
echo $colname[$i];
echo "<br/>";*/
$worksheet->write($tem.$xlsRow, $Result[$colname[$i]], $body);
}
/*$worksheet->write('A'.$xlsRow, $intA, $body);
$worksheet->write('B'.$xlsRow, $Result->type_id, $body);
$worksheet->write('C'.$xlsRow, $Result->tcode, $body);
$worksheet->write('D'.$xlsRow, $Result->tsic, $body);
$worksheet->write('E'.$xlsRow, $Result->full_code, $body);
$worksheet->write('F'.$xlsRow, $Result->number, $body);
$worksheet->write('G'.$xlsRow, $Result->fyear, $body);
$worksheet->write('H'.$xlsRow, $Result->eyear, $body);
$worksheet->write('I'.$xlsRow, $Result->code, $body);
$intA++;*/
}
# End Body Information =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
# Send "Excel File" to "Browser" . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
$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);
// $worksheet->set_paper(5); #Paper Type : Legal
$workbook->close();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment; filename=".basename($xlsname).";");
header("Content-Transfer-Encoding: binary ");
header("Content-Length: ".filesize($fname));
readfile($fname);
unlink($fname);
exit();
# End Send "Excel File" to "Browser" . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
?>