2014年12月18日 星期四

【PHP】PHP寄信 PHPMailer

1.一般寄信
※ $header 詳細說明: 連結

ini_set('SMTP','mail.threewell.com.tw');

$to ="wsxrtgvbuikm@gmail.com"; // 收件者
$subject = "發送測試"; //信件標題
$msg = '<!doctype html><html><head><meta charset="utf-8"></head><body><div style="background-color:#F00;color:#000" align="center">wsxrtgvbuikm@gmail.com</div></body></html>'; // 信件內容

$name = '賴俊銘'; // 寄件人
$sendMail = 'steven@threewell.com.tw'; // 寄件人信箱 要 smtp 允許的信箱

$headers  = "From: ".$name ."<".$sendMail.">". "\r\n"; // 寄件人資料
$headers .= "Content-Type: text/html; charset=utf-8; format=flowed". "\r\n"; // 編碼方式 有用到html要用text/html
$headers .= "MIME-Version: 1.0". "\r\n"; // MIME版本
$headers .= "Content-Transfer-Encoding: 8bit". "\r\n"; // 8位元ASCII碼。
$headers .= "X-Mailer: PHP". "\r\n"; // 哪邊發送的

if(mail($to, $subject, $msg, $headers)):
 echo '發送成功';
else:
 echo '發送失敗';
endif;
  
2.PHPMailer-master 使用套件
  
ini_set('SMTP','mail.threewell.com.tw');

require 'PHPMailer-master/PHPMailerAutoload.php';

$mail = new PHPMailer;
$mail->setLanguage('zh'); // 設定語系 沒用的語言可刪掉
$mail->CharSet = 'UTF-8'; // 編碼
$mail->setFrom('steven@threewell.com.tw', '賴俊銘'); // 寄件人信箱及寄件人
$mail->addAddress('wsxrtgvbuikm@gmail.com', 'wsxrtgvbuikm'); // 收件人信箱及收件人
$mail->Subject = '信件標題'; // 標題
$mail->msgHTML('<a href="#">wsxrtgvbuikm</a>中文中文'); // 內容
$mail->addAttachment('HDT8GN.jpg'); // 附件 可多個
$mail->addAttachment('HDT8GN2.jpg');

if (!$mail->send()) {
    echo "寄信錯誤: " . $mail->ErrorInfo;
} else {
    echo "寄信成功";
}  
  

2014年12月10日 星期三

【PHP】串接 Yahoo 商城 API


1.API 文件
網址:
http://tw.ews.mall.yahooapis.com/handbook_v2/webservice_guide/index.html
這份文件用google搜尋就可以找到
2.準備
首先要向 yahoo 提出申請 API,申請成功後會拿到 API Key 和 Secret,
API Key 就像帳號一樣,Shared Secret 是將資料編碼。
3.製作簽章及使用API
製作簽章的方法在 Yahoo API 文件就有說明,
直接來實作抓取 Yahoo 商城分類,
在文件中找到 API > Product API > /v1/MallCategory/Get
固定的網址是 http://tw.ews.mall.yahooapis.com/stauth (後面加上功能名稱)
YahooAPI class
/* 
 * @author wsxrtgvbuikm@gmail.com
 * 首先要先 define 以下幾個變數:
 * 1.YAHOO_API_KEY : YAHOO申請的KEY
 * 2.YAHOO_API_SECRET : YAHOO申請的SECRET
 * 3.YAHOO_API_ID : YAHOO的帳號 (測試過其實亂打都過)
 * 4.YAHOO_API_NAME : YAHOO的名稱 (通常是中文)
 * 5.YAHOO_API_URL : http://tw.ews.mall.yahooapis.com/stauth
 */
header("Content-Type: text/html; charset=utf-8");
define('YAHOO_API_KEY','輸入API KEY');
define('YAHOO_API_SECRET','輸入API_SECRET');
define('YAHOO_API_ID','輸入ID');
define('YAHOO_API_NAME','輸入名稱');
define('YAHOO_API_URL','http://tw.ews.mall.yahooapis.com/stauth');

class YahooAPI {
  public function yahoo_api($url,$arr=array(),$other_val=array()){
    $url = YAHOO_API_URL.$url;
    
    $auth_arr = array('ApiKey'=>YAHOO_API_KEY,
                       'TimeStamp'=>time(),
                       'Id'=>YAHOO_API_ID,
                       'Name'=>YAHOO_API_NAME,
                       'Format'=>'json'
                       );
    $request_arr = array_merge($auth_arr,$arr);
    
    // 會有編碼的問題所以用foreach
    //$request = urldecode(http_build_query($request_arr));

    $request = '';
    foreach($request_arr as $key=>$val){
      $request .= $key.'='.$val.'&';
    }
    $request = substr($request,0,-1);
    // $request用SECRET sha1加密
    $signature = hash_hmac('sha1',$request,YAHOO_API_SECRET);
    $request_arr['Signature'] = $signature;
    
    // 加入其他參數像是上傳圖片就不包含在加密範圍
    $request_arr = array_merge($request_arr,$other_val);
    
    $this->request = $request_arr;
    
    $r = $this->curl_file_get_contents($url,$request_arr);
    $arr = json_decode($r,true);
    
    return $arr;
  }  

  public function curl_file_get_contents($url,$request){
    $ch = curl_init();
    $timeout = 5; 
    
    $cookie = "cookieLangId=zh_tw;";
    curl_setopt ($ch, CURLOPT_COOKIE, $cookie);
    
    curl_setopt($ch, CURLOPT_ENCODING, "UTF-8");
    
    curl_setopt ($ch, CURLOPT_URL, $url);
    curl_setopt ($ch, CURLOPT_RETURNTRANSFER, 1); 
    curl_setopt ($ch, CURLOPT_CONNECTTIMEOUT, $timeout);
    curl_setopt ($ch, CURLOPT_POST, true); // 啟用POST
    curl_setopt ($ch, CURLOPT_POSTFIELDS, $request); 
    curl_setopt ($ch, CURLOPT_FOLLOWLOCATION, true);
    
    $r = curl_exec($ch);
    
    curl_close($ch);
  
    return $r;
  }
}
  
取得分類
include("YahooAPI.php");
$Yahoo = new YahooAPI();

// 取得 YAHOO 商城分類
$url = '/v1/MallCategory/Get';
$arr = array('CategoryId'=>$CategoryId);
$r = $Yahoo->yahoo_api($url,$arr);
print_r($r);
  
上傳商品範例
在文件上有標記(Require)一定要填,
有些是要用API先去查代碼,其他(Optional)沒有傳值就不要出現不然會失敗..
$url = '/v2/Product/SubmitMain';
$arr = array('SaleType'=>'Normal', // 商品銷售方式
          'SaleTypeInfo'=>'Normal', // 商品銷售資訊
          'ProductName'=>$rs['NAME'], // 商品名稱
          'MallCategoryId'=>$rs['YAHOO_CAT'], // 商城商品分類代號
          'StoreCategoryId'=>$rs['YAHOO_STORE_CAT'], // 商品歸類
          'MarketPrice'=>$rs['MARKET_PRICE'], // 商品市價
          'SalePrice'=>$rs['PRICE'], // 網路價
          'CostPrice'=>$rs['COST'], // 成本價
          'MaxBuyNum'=>$rs['LIMIT_NUM'], // 最高購買數量
          'ShortDescription'=>$rs['DESC'], // 商品簡述
          'LongDescription'=>$rs['CONTENT'], // 商品介紹
          'PayTypeId'=>'1', // 付款方式代號
          'PayTypeId'=>'2', // 付款方式代號
          'PayTypeId'=>'4', // 付款方式代號
          'PayTypeId'=>'8', // 付款方式代號
          'PayTypeId'=>'9', // 付款方式代號
          'PayTypeId'=>'10', // 付款方式代號
          'ShippingId'=>'1', // 物流方式代號
          'ShippingId'=>'2', // 物流方式代號
          'IsTaxFree'=>'0', // 此商品是否為免稅商品
          'SpecTypeDimension'=>'0', // 商品規格
          'CustomizedMainProductId'=>$rs['PRODUCT_ID'], // 商品主貨號
          'Stock'=>$rs['STOCK'], // 商品庫存量 
          'SaftyStock'=>$rs['SAFYSTOCK'], // 商品庫存最低警告量
             
           );
$return = $Yahoo->yahoo_api($url,$arr);
  
上傳圖片
$url = '/v1/Product/UploadImage';
$arr = array('ProductId'=>$productId, // 商品ID
             'MainImage'=>'ImageFile1', // 第一張圖 可一次傳多張
             'Purge'=>'true' // 是否保留原圖
             );
             
// 商品圖系統路徑 - 因為這個不加入簽章所以要另外放
// 前面加上@ CURL 就會當成檔案上傳
$request_arr['ImageFile1'] = '@'.HTTP_REAL_PATH.$rs['IMAGE'];
$return = $this->yahoo_api($url,$arr,$request_arr);
  

2014年11月11日 星期二

【PHP】phpExcel範例

require_once("PHPExcel.php");
 $objPHPExcel = new PHPExcel();
 $objPHPExcel->getProperties()->setTitle(" List");
 $objPHPExcel->getProperties()->setCategory(" List");
 $objPHPExcel->setActiveSheetIndex(0);
 
 $objPHPExcel->getActiveSheet()->setCellValue('A1', '商品編號');
 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(13);
 $objPHPExcel->getActiveSheet()->setCellValue('B1', '商品名稱');
 $objPHPExcel->getActiveSheet()->setCellValue('C1', '醒目標題');
 $objPHPExcel->getActiveSheet()->setCellValue('D1', '頁面標題');
 $objPHPExcel->getActiveSheet()->setCellValue('E1', '頁面關鍵字');
 $objPHPExcel->getActiveSheet()->setCellValue('F1', '頁面描述');
 $objPHPExcel->getActiveSheet()->setCellValue('G1', '中類屬性');
 $objPHPExcel->getActiveSheet()->setCellValue('H1', '小類屬性');
 $objPHPExcel->getActiveSheet()->setCellValue('I1', '市價');
 $objPHPExcel->getActiveSheet()->setCellValue('J1', '售價');
 $objPHPExcel->getActiveSheet()->setCellValue('K1', '成本');
 $objPHPExcel->getActiveSheet()->setCellValue('L1', '庫存數');
 $objPHPExcel->getActiveSheet()->setCellValue('M1', '安全庫存數');
 $objPHPExcel->getActiveSheet()->setCellValue('N1', '上架日');
 $objPHPExcel->getActiveSheet()->setCellValue('O1', '下架日');
 $objPHPExcel->getActiveSheet()->setCellValue('P1', '商品介紹');
 $objPHPExcel->getActiveSheet()->setCellValue('Q1', '功能一標題');
 $objPHPExcel->getActiveSheet()->setCellValue('R1', '功能一內容');
 $objPHPExcel->getActiveSheet()->setCellValue('S1', '功能二標題');
 $objPHPExcel->getActiveSheet()->setCellValue('T1', '功能二內容');
 $objPHPExcel->getActiveSheet()->setCellValue('U1', '功能三標題');
 $objPHPExcel->getActiveSheet()->setCellValue('V1', '功能三內容');
 $objPHPExcel->getActiveSheet()->setCellValue('W1', '功能四標題');
 $objPHPExcel->getActiveSheet()->setCellValue('X1', '功能四內容');
 $objPHPExcel->getActiveSheet()->setCellValue('Y1', '功能五標題');
 $objPHPExcel->getActiveSheet()->setCellValue('Z1', '功能五內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AA1', '功能六標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AB1', '功能六內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AC1', '功能七標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AD1', '功能七內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AE1', '功能八標題');
 $objPHPExcel->getActiveSheet()->setCellValue('AF1', '功能八內容');
 $objPHPExcel->getActiveSheet()->setCellValue('AG1', '商品圖片');

 $i = 1;
 foreach($datas['data'] as $rs)
 {
  $i++;
  $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $a);
  $objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $b);
  $objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $c);
  $objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $d);
  $objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $e);
  $objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $f);
  $objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $g);
  $objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $h);
  $objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $i);
  $objPHPExcel->getActiveSheet()->setCellValue('J'.$i, $j);
  $objPHPExcel->getActiveSheet()->setCellValue('K'.$i, $k);
  $objPHPExcel->getActiveSheet()->setCellValue('L'.$i, $l);
  $objPHPExcel->getActiveSheet()->setCellValue('M'.$i, $m);
  $objPHPExcel->getActiveSheet()->setCellValue('N'.$i, $n);
  $objPHPExcel->getActiveSheet()->setCellValue('O'.$i, $o;
  $objPHPExcel->getActiveSheet()->setCellValue('P'.$i, $p);
  $objPHPExcel->getActiveSheet()->setCellValue('Q'.$i, $q);
  $objPHPExcel->getActiveSheet()->setCellValue('R'.$i, $r);
  $objPHPExcel->getActiveSheet()->setCellValue('S'.$i, $s);
  $objPHPExcel->getActiveSheet()->setCellValue('T'.$i, $t);
  $objPHPExcel->getActiveSheet()->setCellValue('U'.$i, $u);
  $objPHPExcel->getActiveSheet()->setCellValue('V'.$i, $v);
  $objPHPExcel->getActiveSheet()->setCellValue('W'.$i, $w);
  $objPHPExcel->getActiveSheet()->setCellValue('X'.$i, $x);
  $objPHPExcel->getActiveSheet()->setCellValue('Y'.$i, $y);
  $objPHPExcel->getActiveSheet()->setCellValue('Z'.$i, $z);
  $objPHPExcel->getActiveSheet()->setCellValue('AA'.$i, $aa);
  $objPHPExcel->getActiveSheet()->setCellValue('AB'.$i, $ab);
  $objPHPExcel->getActiveSheet()->setCellValue('AC'.$i, $ac);
  $objPHPExcel->getActiveSheet()->setCellValue('AD'.$i, $ad);
  $objPHPExcel->getActiveSheet()->setCellValue('AE'.$i, $ae);
  $objPHPExcel->getActiveSheet()->setCellValue('AF'.$i, $af);
  $objPHPExcel->getActiveSheet()->setCellValue('AG'.$i, $ag);
 }


 // OUTPUT
 header('Content-Type: application/vnd.ms-excel');
 header('Content-Disposition: attachment;filename="output'.date("YmdHis").'.xls"');
 header('Cache-Control: max-age=0');
 
 $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
 $objWriter->save('php://output');







實用筆記整理

error_reporting(E_ALL);
date_default_timezone_set(‘Asia/Taipei’);
/** PHPExcel */
require_once ‘Classes/PHPExcel.php';
// 新增Excel物件
$objPHPExcel = new PHPExcel();
// 設定屬性
$objPHPExcel->getProperties()->setCreator(“PHP")
->setLastModifiedBy(“PHP")
->setTitle(“Title")
->setSubject(“Subject")
->setDescription(“Description")
->setKeywords(“Keywords")
->setCategory(“Category");
//設定操作中的工作表
$objPHPExcel->setActiveSheetIndex(0);
//將工作表命名
$objPHPExcel->getActiveSheet(0)->setTitle(‘第一張表’);
//合併儲存格
$objPHPExcel->getActiveSheet(0)->mergeCells(‘A1:D2′);
//儲存格內容
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A1′,’PHPEXCEL TEST’); //合併後的儲存格,設定時指定左上角那個。
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘D3′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C4′,’test’);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘D4′,’test’);
//設定背景顏色單色
$objPHPExcel->getActiveSheet(0)->getStyle(‘A3:D3′)->applyFromArray(
array(‘fill’     => array(
‘type’ => PHPExcel_Style_Fill::FILL_SOLID,
‘color’ => array(‘argb’ => ‘D1EEEE’)
),
)
);
//設定漸層背景顏色雙色(灰/白)   經測試,Excel2007才有漸層
$objPHPExcel->getActiveSheet(0)->getStyle(‘A1:D2′)->applyFromArray(
array(
‘font’   => array(‘bold’ => true,
‘size’ => ’24’),
‘alignment’ => array(‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER),
‘borders’  => array(‘top’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)),
‘fill’   => array(‘type’ => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR,
‘rotation’   => 90,
‘startcolor’ => array(‘rgb’ => ‘DCDCDC’),
‘endcolor’   => array(‘rgb’ => ‘FFFFFF’))
));
//框線 方法一:使用 setBorderStyle() 函數
$objPHPExcel->getActiveSheet(0)->getStyle(‘A5′)->getBorders()->getTop()  ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B5′)->getBorders()->getBottom() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘C5′)->getBorders()->getleft() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘D5′)->getBorders()->getright() ->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A7:C10′)->getBorders()->getAllborders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
//框線 方法二:使用applyFromArray()函數
$styleArray = array(
‘borders’ => array(
‘allborders’ => array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘000000’),
),
),
);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A12:C15′)->applyFromArray($styleArray);
//框線 方法三:使用物件 + applyFromArray()函數
$style_obj = new PHPExcel_Style();
$style_array = array( ‘borders’ => array(‘allborders’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)),
‘alignment’ => array(‘wrap’=> true,
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
‘font’ => array(‘size’ => ‘8’)
);
$style_obj->applyFromArray($style_array);
$objPHPExcel->getActiveSheet(0)->setSharedStyle($style_obj, ‘E7:G10′);
//斜線  方法一
$styleArray = array(‘borders’ => array(‘diagonal’ => array(‘style’ => PHPExcel_Style_Border::BORDER_THICK,
‘color’ => array(‘argb’ => ‘FFFF0000′),
),
‘diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_UP
//’diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_DOWN
//’diagonaldirection’ => PHPExcel_Style_Borders::DIAGONAL_BOTH
),
);
$objPHPExcel->getActiveSheet()->getStyle(“E1″)->applyFromArray($styleArray);
//斜線  方法二
$objPHPExcel->getActiveSheet()->getStyle(‘F1′)->getBorders()->getDiagonal()->applyFromArray(array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘FFFF0000′)
)
);
$objPHPExcel->getActiveSheet()->getStyle(‘F1′)->getBorders()->setDiagonalDirection(PHPExcel_Style_Borders::DIAGONAL_DOWN);
/*
註:
PHPExcel_Style_Borders::DIAGONAL_UP
PHPExcel_Style_Borders::DIAGONAL_DOWN
PHPExcel_Style_Borders::DIAGONAL_BOTH
*/
//設定一個範圍後套用相同格式
$objPHPExcel->getActiveSheet(0)->mergeCells(‘E12:F13′);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘E12′,"Hello \n World");
$style_obj = new PHPExcel_Style();
$style_array = array( ‘borders’ => array(‘allborders’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)),
‘alignment’ => array(‘wrap’=> true,
‘horizontal’ => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
‘vertical’ => PHPExcel_Style_Alignment::VERTICAL_CENTER
),
‘font’ => array(‘size’ => ‘8’)
);
$style_obj->applyFromArray($style_array);
$objPHPExcel->getActiveSheet(0)->setSharedStyle($style_obj, “E12:G14″);
//設定字型(粗細、顏色)  也可參照上面的方法,用陣列的方式設定。
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getFont()->setName(‘Candara’);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getFont()->setSize(16);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLUE); //藍色
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); //紅色
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4′)->getFont()->getColor()->setARGB(‘FF0000′); //紅色
$objPHPExcel->getActiveSheet(0)->setCellValue(‘G2′, ‘2008-12-31′);
$objPHPExcel->getActiveSheet(0)->getStyle(‘G2′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
/*
註:這裡有列出可用英文單字表示的顏色,並不多。
COLOR_BLACK
COLOR_BLUE
COLOR_DARKBLUE
COLOR_DARKGREEN
COLOR_DARKRED
COLOR_DARKYELLOW
COLOR_GREEN
COLOR_RED
COLOR_WHITE
COLOR_YELLOW
*/
//使用函數
$objPHPExcel->getActiveSheet(0)->setCellValue(‘A5′,’3′);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘B5′,’4′);
$objPHPExcel->getActiveSheet(0)->setCellValue(‘C5′, ‘=SUM(A5:B5)’);
//設定A3內容為00123,並指定為文字型態。這樣在顯示的時候不會自動把0去掉。$objPHPExcel->getActiveSheet(0)->getCell(“A4″)->setValueExplicit(‘00123′, PHPExcel_Cell_DataType::TYPE_STRING);
//分離儲存格
//$objActSheet->unmergeCells(‘B1:C22′);
//設定欄寬
$objPHPExcel->getActiveSheet(0)->getColumnDimension(‘A’)->setWidth(20);
//設定欄寬(自動欄寬)
//$objPHPExcel->getActiveSheet(0)->getColumnDimension(“A")->setAutoSize(true);
//設定高度
$objPHPExcel->getActiveSheet(0)->getRowDimension(‘1′)->setRowHeight(150);
//下底線
$objPHPExcel->getActiveSheet(0)->getStyle(“D3″)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
//旋轉文字
$objPHPExcel->getActiveSheet(0)->getStyle(‘A4′)->getAlignment()->setTextRotation(-90);
//對齊  //注意是 setVertiacl 還是 setHorizontal
$objPHPExcel->getActiveSheet(0)->getStyle(‘B4′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet(0)->getStyle(‘C4′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
/*
VERTICAL_CENTER 垂直置中
VERTICAL_TOP
HORIZONTAL_CENTER
HORIZONTAL_RIGHT
HORIZONTAL_LEFT
HORIZONTAL_JUSTIFY
*/
//add comment ———————————————–
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->setAuthor(‘PHPExcel’);
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->getText()->createTextRun(‘comment1 comment1 comment1 ‘);
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->setWidth(‘200pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->setHeight(‘100pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->setMarginLeft(‘150pt’);
$objPHPExcel->getActiveSheet()->getComment(‘A6′)->getFillColor()->setRGB(‘dea66e’); //背景顏色
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(‘A6′)->getText()->createTextRun(‘comment2 comment2 comment2 ‘);
$objCommentRichText->getFont()->setBold(true); //文字加粗
$objCommentRichText->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); //文字顏色
———————————————————————————–
———————————————————————————–
// 設定格式:使用物件的方式
$style_obj = new PHPExcel_Style();
$styleArray = array(‘borders’ => array(‘left’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THICK),
‘top’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN),
‘right’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN),
‘bottom’=> array(‘style’ => PHPExcel_Style_Border::BORDER_THIN)
));
$style_obj->applyFromArray($styleArray);
$letter = PHPExcel_Cell::stringFromColumnIndex(0); //A
$cellname1 = $letter.’1′; // A1
$cellname2 = $letter.7;  // A7
$cell_range = “$cellname1:$cellname2″;
$sheet->setSharedStyle($style_obj, “$cell_range");
// 設定格式:使用陣列
$styleArray = array(
‘borders’ => array(
‘allborders’ => array(
‘style’ => PHPExcel_Style_Border::BORDER_THIN,
‘color’ => array(‘argb’ => ‘000000’)
),
),
‘font’   => array(‘bold’ => true,
‘size’ => ’12’,
‘color’ => array(‘argb’ => ‘FF0000′)
)
);
$objPHPExcel->getActiveSheet(0)->getStyle(‘A12:C15′)->applyFromArray($styleArray);
// 註解
$comment = “This is comment";
$sheet->getComment(“F1″)->getFillColor()->setRGB(‘FFFAD9′); //背景顏色
$sheet->getComment(“F1″)->setWidth(‘320pt’);
$objCommentRichText = $sheet->getComment(“F1″)->getText()->createTextRun(“$comment");
$objCommentRichText->getFont()->getColor()->setRGB(‘008080′); //文字顏色
$objCommentRichText->getFont()->setBold(true); //文字加粗
———————————————————————————–
// 設定其它工作表
$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle(‘第二張表’);
$objPHPExcel->getActiveSheet()->setCellValue(‘A3′,"test1″);
$objPHPExcel->getActiveSheet()->setCellValue(‘B3′,’test2′);
$objPHPExcel->setActiveSheetIndex(0);
//若要在 2003 跟 2007 之間切換,選然下面兩段其中一段即可。
//Excel 2007
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
header(‘Content-Disposition: attachment;filename="01simple.xlsx"‘);
header(‘Cache-Control: max-age=0′);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel2007′);
/*
//Excel 2003
header(‘Content-Type: application/vnd.ms-excel’);
header(‘Content-Disposition: attachment;filename="01simple.xls"‘);
header(‘Cache-Control: max-age=0′);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, ‘Excel5′); //Excel 2003 = Excel 5
*/
//========================================================
$objWriter->save(‘php://output’);
exit;

2014年11月3日 星期一

【jquery】post 使用 json 傳值

post 使用 json 傳值
js
$.post("userorder_v2.php",{mod:'detail',orderid:$orderid},function ($data){
// 判斷CSS是否要隱藏
for($key in $data.data){
  if (!$data.data[$key]){
    $("#ar-"+$key).hide();
  }else{
    $("#ar-"+$key).show();
  }
  
  $thisValue = $(".od-" + $key);

  // 加上另外的處理
  if ($thisValue.hasClass("plus")){ // +
    $data.data[$key] = '+'+$data.data[$key];
  }else if ($thisValue.hasClass("less")){
    $data.data[$key] = '-'+$data.data[$key];
  }
  
  // 將值放入對應的CSS中
  $thisValue.html($data.data[$key]);
}
},"json");
  
HTML:

<table border="0" cellspacing="2" cellpadding="5" width="760">
	<tr>
		<th colspan="4" align="center">訂單資料</th>
	</tr>
	<tbody>
	<tr>
		<td width="100" align="center">訂單編號</td>
		<td width="200" class="od-orderid"></td>
		<td width="100" align="center">付款方式</td>
		<td class="od-payWayName"></td>
	</tr>
	<tr>
		<td align="center">訂單金額</td>
		<td class="od-order_total"></td>
		<td align="center">取貨方式</td>
		<td class="od-get_goods_name"></td>
	</tr>
	<tr>
		<td align="center">訂單日期</td>
		<td class="od-order_time"></td>
		<td align="center"><span id="ar-identity">訂購身分</span></td>
		<td class="od-identity"></td>
	</tr>
	<tr>
		<td align="center">訂單狀態</td>
		<td colspan="3" class="od-shipName"></td>
	</tr>
	<tr>
		<td align="center">收貨地址</td>
		<td colspan="3" class="od-address"></td>
	</tr>
	<tr>
		<td align="center">gt;備註</td>
		<td colspan="3" class="od-comp_note"></td>
	</tr>
	</tbody>
</table>
  

2014年10月8日 星期三

【CSS】顯示表格空白(empty-cells)、框線合併(border-collapse)

empty-cells
empty-cells:show -> 顯示空白表格
empty-cells:hide -> 不顯示空白表格 - 預設
empty-cells:inherit -> 繼承父屬性
1 2
3
border-collapse
border-collapse:collapse -> 框限線會合併
border-collapse:separate -> 框線分開
1 2
3
<style>
#wsxrtgvbuikm14010082 .t1{
	color:#1A6811;
	font-family: "微軟正黑體";
	font-size:24px;
	padding: 0 10px;
}

#wsxrtgvbuikm14010082 .t2{
	color:#1F1C97;
	font-family: "微軟正黑體";
	font-size:16px;
	padding: 0 10px;
	margin-top:8px;
	margin-bottom:8px;
}

#wsxrtgvbuikm14010082 .t3{
	color:#000;
	font-family: "微軟正黑體";
	font-size:16px;
	padding: 0 5px;	
}

#wsxrtgvbuikm14010082 .table-1 {
	width:300px;
	margin-left:10px;
}

#wsxrtgvbuikm14010082 .table-1 td{
	border:1px solid #0088dd;
	padding:15px;
	empty-cells:hide;
	
}

#wsxrtgvbuikm14010082 .table-2 {
	width:300px;
	border-collapse:collapse;
	margin-left:10px;
}

#wsxrtgvbuikm14010082 .table-2 td{
	border:1px solid #0088dd;
	padding:15px;
	empty-cells:hide;
	
}
</style>
<div id="wsxrtgvbuikm14010082">
  <div class="t1">empty-cells</div>
  <div class="t2">empty-cells:show -> 顯示空白表格</div>
  <div class="t2">empty-cells:hide -> 不顯示空白表格 - 預設</div>
  <div class="t2">empty-cells:inherit -> 繼承父屬性</div>
  <div>
    <table width="100%" border="0" cellspacing="0" cellpadding="0" class="table-1">
      <tr>
        <td>1</td>
        <td>2</td>
      </tr>
      <tr>
        <td>3</td>
        <td></td>
      </tr>
    </table>
  </div>
  
  <div class="t1" style="margin-top:30px;">border-collapse</div>
  <div class="t2">border-collapse:collapse -> 框限線會合併</div>
  <div class="t2">border-collapse:separate -> 框線分開</div>
  <div>
    <table width="100%" border="1" cellspacing="0" cellpadding="0" class="table-2">
      <tr>
        <td>1</td>
        <td>2</td>
      </tr>
      <tr>
        <td>3</td>
        <td></td>
      </tr>
    </table>
  </div>
  

【CSS】list-style-position

項目符號的位置:list-style-position
list-style-position:outside -> 不縮排
list-style-position:inherit -> 如果有第二排會縮排 預設值
  • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
  • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
  • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
    • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
    • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
    • SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC
      
    // CSS
    .demo{
     width:300px;
     margin:0px;
     padding-left:30px;
     list-style-position:inside;
    }
    
    .demo li{
     margin-top:10px;
     list-style:circle;
    }
    
    ul.demo2{ /* 一定要用ul的樣子 */
     width:300px;
     list-style-position:outside;
     margin:0px;
     padding-left:30px;
     padding-top:30px;
    }
    
    .demo2 li{
     margin-top:10px;
     list-style:circle;
    }
    
    // HTML
    <div class="demo">
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
    </div>
    
    <ul class="demo2">
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
      <li>SELECT TOP 20 text FROM sys.dm_exec_query_stats as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st ORDER BY qs.creation_time DESC</li>
    </ul>
      
      

    2014年9月17日 星期三

    【SQL】if else


    基本
    PHP
       if ($score >= 90)
         $str = 'A';
        elseif ($score >= 70 && $score < 90) 
         $str = 'B';
        elseif ($score >= 60 && $score < 70) 
         $str = 'C';  
        else{
         $str = 'D';  
        }
         
      
    SQL
       CASE  
         WHEN score >= 90 THEN 'A'
          WHEN score >= 90 THEN 'B'
          WHEN score >= 90 THEN 'C'
          ELSE 'D'
        END AS str
      
    欄位值
    Name Answer
    張一 1
    王二 2
    李三 3

        SELECT Name, 
         case Answer
           when 1 then '喜歡'
           when 2 then '不喜歡'
           when 3 then '一般'
          END
        FROM table;
        
        OR
        
        SELECT Name, 
         case
           when Answer=1 then '喜歡'
           when Answer=2 then '不喜歡'
           when Answer=3 then '一般'
          END AS Answer
          FROM table;
      

    2014年8月27日 星期三

    【MySQL】LIKE 搜尋


    一、一般
    1.name LIKE '豬%' -> 找豬開頭的文字後面不論字數,例如:豬八戒、豬頭
    2.name LIKE '%豬%' -> 只有出現豬就算找到,如豬、神豬、豬頭
    3.name LIKE '豬_' -> 限定字數只找豬開頭兩個字,底線在那邊就一定要有字
    4.name LIKE '%豬%戒%' -> 只要字串包含順序有豬跟界就符合
    二、應用搜尋
    
    // 將字切開並加上%
    $arr = $this->utf8_str_split($keywords);
    $keywordSplit = implode('%',$arr);
    
    // 這樣比 '%$keywords%' 搜尋到更多結果
    $sql = "SELECT * FROM user WHERE name LIKE '%$keywordSplit%'";  
    $rs = $pdo->query($sql);
    
    // 用UTF8切文字  
    function utf8_str_split($str, $split_len = 1){
     if (!preg_match('/^[0-9]+$/', $split_len) || $split_len < 1)
       return FALSE;
    
     $len = mb_strlen($str, 'UTF-8');
     if ($len <= $split_len)
       return array($str);
    
     preg_match_all('/.{'.$split_len.'}|[^\x00]{1,'.$split_len.'}$/us', $str, $ar);
    
     return $ar[0];
    }  
      

    2014年8月26日 星期二

    【PHP】縮圖不失真


    最近要做縮圖的工作,試了幾個function,
    發現縮圖後的圖都失真的,變得比較模糊,
    最後終於找不到不失真的function。
    /*  Convert image size. true color*/ 
    //$src        來源檔案 
    //$dest        目的檔案 
    //$maxWidth    縮圖寬度 
    //$maxHeight    縮圖高度 
    //$quality    JPEG品質 
    function ImageCopyResizedTrue($src,$dest,$maxWidth,$maxHeight,$quality=100) { 
      //檢查檔案是否存在 
      if (file_exists($src)  && isset($dest)) { 
          // 目錄是否存在
          $dir = dirname($dest);
          if (!is_dir($dir)){
            mkdir($dir,0777);
          }
          
          $destInfo  = pathinfo($dest); 
          $srcSize   = getimagesize($src); //圖檔大小 
          $srcRatio  = $srcSize[0]/$srcSize[1]; // 計算寬/高 
          $destRatio = $maxWidth/$maxHeight; 
          if ($destRatio > $srcRatio) { 
              $destSize[1] = $maxHeight; 
              $destSize[0] = $maxHeight*$srcRatio; 
          } 
          else { 
              $destSize[0] = $maxWidth; 
              $destSize[1] = $maxWidth/$srcRatio; 
          } 
    
          //GIF 檔不支援輸出,因此將GIF轉成JPEG 
          if ($destInfo['extension'] == "gif") $dest = substr_replace($dest, 'jpg', -3); 
    
          //建立一個 True Color 的影像 
          $destImage = imagecreatetruecolor($destSize[0],$destSize[1]); 
    
          //根據副檔名讀取圖檔 
          switch ($srcSize[2]) { 
              case 1: $srcImage = imageCreateFromGif($src); break; 
              case 2: $srcImage = imageCreateFromJpeg($src); break; 
              case 3: $srcImage = imageCreateFromPng($src); break; 
              default: return false; break; 
          } 
    
          //取樣縮圖 
          imagecopyresampled($destImage, $srcImage, 0, 0, 0, 0,$destSize[0],$destSize[1], 
                              $srcSize[0],$srcSize[1]); 
    
          //輸出圖檔 
          switch ($srcSize[2]) { 
              case 1: case 2: imagejpeg($destImage,$dest,$quality); break; 
              case 3: imagepng($destImage,$dest); break; 
          } 
          return true; 
      } 
      else { 
          return false; 
      } 
    } 
      
    將它寫成Class方便之後使用
    此class是可將一個目錄裡所有的圖片縮圖,
    並存到另一個目錄,且可設定多個size。
    // 使用方法
    set_time_limit(0);
    $imgPath = $_SERVER['DOCUMENT_ROOT'].'/data/images'; // 原始目錄
    
    $thumb_150 = $_SERVER['DOCUMENT_ROOT'].'/data/images_150'; // 縮圖目錄
    $thumb_110 = $_SERVER['DOCUMENT_ROOT'].'/data/images_110';
    
    $igtb = new ImgThumb();
    $igtb->oriPath = $imgPath;
    $igtb->target = array(
             array($thumb_150,150,150),
             array($thumb_110,110,110), // 縮圖種類及大小
            );
    $igtb->startThumb();
    
          
    // @author - wsxrtgvbuikm@gmail.com
    class ImgThumb {
    
     public $oriPath, // 要複製的路徑
         $target = array(
               array('路徑1',150,150) // 可多個 @parameter 路徑,width,height
             ),
         $imgArr;
    
     public function __construct(){
      $this->imgArr = array('jpg','jpeg','png','gif','bmp');
     }
    
     public function startThumb(){
      $this->img_thumb($this->oriPath);
      
     }
    
     public function img_thumb($dir){
      
      $dirArr = scandir($dir);
      foreach($dirArr as $val){
       if ($val == '.' || $val == '..') continue;
       
       $file = $dir.'/'.$val;
       $ext = strtolower(pathinfo($val, PATHINFO_EXTENSION));
       
       if (in_array($ext,$this->imgArr)){ 
        $extend = str_replace($this->oriPath,'',dirname($file));
        foreach($this->target as $arr){
         $target = $arr[0].$extend;
         if (!is_file($target.'/'.basename($file))) // 沒圖縮圖再跑
    
          $this->ImageCopyResizedTrue($file,$target.'/'.basename($file),$arr[1],$arr[2]);
    
        }
       }
       
       if (is_dir($file)){ // 是目錄就繼續找
        $this->img_thumb($file);
       }
       
      } // end foreach
      
     }
     
     
     /*  Convert image size. true color*/ 
     //$src        來源檔案 
     //$dest        目的檔案 
     //$maxWidth    縮圖寬度 
     //$maxHeight    縮圖高度 
      //$quality    JPEG品質 
      public function ImageCopyResizedTrue($src,$dest,$maxWidth,$maxHeight,$quality=100) { 
       //檢查檔案是否存在 
       if (file_exists($src)  && isset($dest)) { 
         // 目錄是否存在
         $dir = dirname($dest);
         if (!is_dir($dir)){
          mkdir($dir,0777);
         }
         
         $destInfo  = pathinfo($dest); 
         $srcSize   = getimagesize($src); //圖檔大小 
         $srcRatio  = $srcSize[0]/$srcSize[1]; // 計算寬/高 
         $destRatio = $maxWidth/$maxHeight; 
         if ($destRatio > $srcRatio) { 
           $destSize[1] = $maxHeight; 
           $destSize[0] = $maxHeight*$srcRatio; 
         } 
         else { 
           $destSize[0] = $maxWidth; 
           $destSize[1] = $maxWidth/$srcRatio; 
         } 
    
         //GIF 檔不支援輸出,因此將GIF轉成JPEG 
         if ($destInfo['extension'] == "gif") $dest = substr_replace($dest, 'jpg', -3); 
    
         //建立一個 True Color 的影像 
         $destImage = imagecreatetruecolor($destSize[0],$destSize[1]); 
    
         //根據副檔名讀取圖檔 
         switch ($srcSize[2]) { 
           case 1: $srcImage = imageCreateFromGif($src); break; 
           case 2: $srcImage = imageCreateFromJpeg($src); break; 
           case 3: $srcImage = imageCreateFromPng($src); break; 
           default: return false; break; 
         } 
    
         //取樣縮圖 
         imagecopyresampled($destImage, $srcImage, 0, 0, 0, 0,$destSize[0],$destSize[1], 
                   $srcSize[0],$srcSize[1]); 
    
         //輸出圖檔 
         switch ($srcSize[2]) { 
           case 1: case 2: imagejpeg($destImage,$dest,$quality); break; 
           case 3: imagepng($destImage,$dest); break; 
         } 
         return true; 
       } 
       else { 
         return false; 
       } 
     } 
    }
      

    2014年8月25日 星期一

    【PHP】JSON 使用


    json(JavaScript Object Notation):JSON 是個以純文字來儲存資料,
    更好用的是將陣列或物件轉換成文字。
    規則
    陣列用[],如:
    array('php','javacript','jquery') 會變成:
    ["php","javacript","jquery"]

    物件用{},如
    arry('name'=>'steven',tel=>'1234') 變成:
    {"name":"steven,"tel":1234}
    jquery 使用json
    使用: $.parseJSON() 將json解譯
    範例
      var str = '{"prodcut_id":"1234",
            "name":"鉛筆",
                  "spec":["紅","綠","藍"]
                  }';
      var result = $.pasrseJSON(str);
      alert(result.prodcut_id); // 印出1234
      alert(result.spec[0]); // 印出 紅
      
    用$.post時可以指定回傳格視為json
      $.post("test.php",{name:$("#name").val()},function (result){
       alert(result.prodcut_id);
      },json);
      
    PHP 使用json
    編譯解譯分別為:json_encode、json_decode,
    但為了操作方便可將物件解譯成array,
    此時就要用json_decode($str,true);
      $arr = array("product"=>"1234","name"=>"鉛筆","spec"=>array("紅","綠","藍"));
      $str = json_encode($arr);
      
      $obj = json_decode($str); // 此時會變成 stdClass Object ( [product] => 1234 [name] => 鉛筆 [spec] => Array ( [0] => 紅 [1] => 綠 [2] => 藍 ) )
      $arr = json_decode($str,true); // 結果:Array ( [product] => 1234 [name] => 鉛筆 [spec] => Array ( [0] => 紅 [1] => 綠 [2] => 藍 ) )
      

    【CSS】Reset CSS 將CSS歸零,符合各樣瀏覽器


    css(Cascading Style Sheets)是用來設定網頁的樣式
    但在不同瀏覽器有時相同語法呈現方式卻會不同
    對於css有時候真的會很麻煩
    這時可以用 Reset CSS 將css歸零
    在css開始前加上:
       /* http://meyerweb.com/eric/tools/css/reset/ 
       v2.0 | 20110126
       License: none (public domain)
        */
        
        html, body, div, span, applet, object, iframe,
        h1, h2, h3, h4, h5, h6, p, blockquote, pre,
        a, abbr, acronym, address, big, cite, code,
        del, dfn, em, img, ins, kbd, q, s, samp,
        small, strike, strong, sub, sup, tt, var,
        b, u, i, center,
        dl, dt, dd, ol, ul, li,
        fieldset, form, label, legend,
        table, caption, tbody, tfoot, thead, tr, th, td,
        article, aside, canvas, details, embed, 
        figure, figcaption, footer, header, hgroup, 
        menu, nav, output, ruby, section, summary,
        time, mark, audio, video {
          margin: 0;
          padding: 0;
          border: 0;
          font-size: 100%;
          font: inherit;
          vertical-align: baseline;
        }
        /* HTML5 display-role reset for older browsers */
        article, aside, details, figcaption, figure, 
        footer, header, hgroup, menu, nav, section {
          display: block;
        }
        body {
          line-height: 1;
        }
        ol, ul {
          list-style: none;
        }
        blockquote, q {
          quotes: none;
        }
        blockquote:before, blockquote:after,
        q:before, q:after {
          content: '';
          content: none;
        }
        table {
          border-collapse: collapse;
          border-spacing: 0;
        }
      
    來源:http://meyerweb.com/eric/tools/css/reset/

    2014年8月19日 星期二

    【GitHub】windows 入門


    GitHub 是一個線上版本控制的服務,
    版本管控簡單的說就是防止別人改到你的程式和做程式還原,
    寫程式很怕越寫越糟,想找上一個版本或是還原到昨天寫的內容,
    這時就要使用版本控制。

    1.申請帳號
    先到 https://github.com/ 申請一個帳號

    2.建立一個 repository
    要勾選Publiec,Private要收費

    3.下載 GitHub windows 的工具
    找到 Clone in Desktop 並下載安裝

    4.開啟剛剛安裝的GitHub,輸入帳號密碼並連線

    5.新增一個目錄並建立檔案


    6.當有新增或修改檔案時要給予備註及上傳

    2014年8月15日 星期五

    【MSSQL】最近執行的SQL


    查詢目前正在執行的SQL語法
    SELECT TOP 200 qs.creation_time,last_execution_time-creation_time as time,text 
    FROM sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    ORDER BY qs.creation_time DESC
    
    
    SELECT      r.scheduler_id as 排程器識別碼,
                status         as 要求的狀態,
                r.session_id   as SPID,
                r.blocking_session_id as BlkBy,
                substring(
        ltrim(q.text),
        r.statement_start_offset/2+1,
        (CASE
                     WHEN r.statement_end_offset = -1
                     THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                     ELSE r.statement_end_offset
                     END - r.statement_start_offset)/2)
                     AS [正在執行的 T-SQL 命令],
                r.cpu_time      as [CPU Time(ms)],
                r.start_time    as [開始時間],
                r.total_elapsed_time as [執行總時間],
                r.reads              as [讀取數],
                r.writes             as [寫入數],
                r.logical_reads      as [邏輯讀取數],
                -- q.text, /* 完整的 T-SQL 指令碼 */
                d.name               as [資料庫名稱]
    FROM        sys.dm_exec_requests r 
       CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
       LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
    WHERE       r.session_id > 50 AND r.session_id <> @@SPID
    ORDER BY    r.total_elapsed_time desc
    
    
    
    
    
    
    
    
    SELECT
    A.SESSION_ID,DB_NAME(A.DATABASE_ID) AS DATABASE_NAME,
    A.START_TIME,A.COMMAND,A.CPU_TIME,
    A.PERCENT_COMPLETE,A.ESTIMATED_COMPLETION_TIME,B.TEXT
    
    FROM SYS.DM_EXEC_REQUESTS A
    CROSS APPLY
    SYS.DM_EXEC_SQL_TEXT(A.SQL_HANDLE)AS B
    WHERE SESSION_ID > 50
      
    看哪個語法最花時間
    SELECT TOP 10 total_worker_time, last_worker_time, 
    max_worker_time, min_worker_time,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
    WHEN -1 THEN DATALENGTH(st.text)
    ELSE qs.statement_end_offset END 
    - qs.statement_start_offset)/2) + 1) as statement_text
    FROM sys.dm_exec_query_stats as qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
    ORDER BY max_worker_time DESC   
    
    
    
    
    
    
    
    
    
    
    
    SELECT TOP 50
     [Average CPU used] = total_worker_time / qs.execution_count,
     [Total CPU used] = total_worker_time,
     [Execution count] = qs.execution_count,
     [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, 
             (CASE WHEN qs.statement_end_offset = -1 
                THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
              ELSE qs.statement_end_offset END - 
    qs.statement_start_offset)/2)
    ,[Parent Query] = qt.text
    ,DatabaseName = DB_NAME(qt.dbid)
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    ORDER BY [Average CPU used] DESC;
    WITH DB_CPU_Stats
    AS
    (SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], 
      SUM(total_worker_time) AS [CPU_Time_Ms]
     FROM sys.dm_exec_query_stats AS qs
     CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
                  FROM sys.dm_exec_plan_attributes(qs.plan_handle)
                  WHERE attribute = N'dbid') AS F_DB
     GROUP BY DatabaseID)
    SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
           DatabaseName, [CPU_Time_Ms], 
           CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) 
           OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
    FROM DB_CPU_Stats
    WHERE DatabaseID > 4 -- system databases
    AND DatabaseID <> 32767 -- ResourceDB
    ORDER BY row_num OPTION (RECOMPILE);
    SELECT substring(text,qs.statement_start_offset/2 ,(CASE WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,qs.plan_generation_num as recompiles ,qs.execution_count as execution_count ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time ,qs.total_worker_time as cpu_time ,qs.total_logical_reads as reads ,qs.total_logical_writes as writes ,last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st LEFT JOIN sys.dm_exec_requests r ON qs.sql_handle = r.sql_handle ORDER BY total_wait_time DESC

    2014年8月11日 星期一

    【PHP、Jquery】廣告計數

    計算一個廣告被被點擊次數有兩種狀況
    1.站內廣告
    原理:當點擊時用ajax計數,範例:
    <a href="7311.tw" class="st-ad" adid="10"></a>
    
    <script>
    // 廣告計數
    $("a.st-ad").click(function (e){
      e.preventDefault();
      $this = $(this);
      $.post("aj_ad.php",
        {mod:'ad_statistics',ad_id:$(this).attr('adid')},
        function (){
          window.location = $this.attr('href');
      });
    });
    </script>
    
    2.站外廣告
    原理:先連結到一頁專門計數的頁面,再轉到連結頁面

    2014年8月8日 星期五

    【PHP】 取得圖片資訊 getimagesize

    取得圖片資訊:
    $size = getimagesize("圖片網址");
    print_r($size);
    // 結果
    Array ( [0] => 414 [1] => 262 [2] => 2 [3] => width="414" height="262" [bits] => 8 [channels] => 3 [mime] => image/jpeg )
    
    說明:
    0:寬度 1:長度 2:檔案類型 3:寬度及高度
    2:檔案類型有:
    1 = GIF,2 = JPG,3 = PNG,4 = SWF,5 = PSD,6 = BMP,7 = TIFF(intel byte order),8 = TIFF(motorola byte order),9 = JPC,10 = JP2,11 = JPX,12 = JB2,13 = SWC,14 = IFF,15 = WBMP,16 = XBM

    【SQL】優化搜尋


    今天發現網站變慢,查了一下原因
    1.量大需限定範圍:
    資料大約50萬筆,抓最近5筆資料,多加了範圍限制快了10倍
    $sql = "SELECT TOP(5) *
    FROM page_log pl 
    ORDER BY time DESC";
    
    $s = microtime(time);
    $result = $this->db->queryAll($sql);
    $e = microtime(time);
    echo $e - $s;
    
    結果:0.605秒
    $sql = "SELECT TOP(5) *
    FROM page_log pl 
    WHERE time > '20140807000000'
    ORDER BY time DESC";
    
    $s = microtime(time);
    $result = $this->db->queryAll($sql);
    $e = microtime(time);
    echo $e - $s;
    
    結果:0.07秒

    2014年8月5日 星期二

    【CSS】寫完新版型的CSS心得


    1.數字、錢字符號:
    color: #ed3729;
    font-size: 15px;
    font-weight: bold;
    font-family:Century Gothic, Arial, Helvetica, sans-serif,新細明體;
    
    2.一般字體可用:
    font-family: "微軟正黑體";
    font-family:Arial, Helvetica, sans-serif,新細明體;
    
    3.商品名稱如果有帶型號最好不要使用英文單字換行,會不整齊,文字太常可用css將他隱藏
    word-break:break-all; 
    height:36px;
    overflow:hidden;
    
    4.可用簡單標籤來簡化html像是 i b 但要先消除他的屬性:
    i { font-style: normal; }
    b { font-weight:normal; }
    
    5.css3 漸漸普及可多用,例如圓角、陰影:
    box-shadow:2px 2px 5px #999; // 陰影
    border-radius:3px; // 圓角
    
    6.用line-height可以將文字垂直置中不用padding:
    height:36px;
    line-height:36px;
    7.圖片加文字圖片會太高可將圖片加上position:
    position:relative;
    top:9px;
    
    8.連結標籤可當div用但要加上display:
    a { display:block;}
    
    9.除了ul li可以做選單多層選單可以再利用dl dt dd:
    dl{
     margin:0px;
     padding:0px;
    }
    dt{
     margin:0px;
     padding:0px;
    }
    dd{
     margin:0px;
     padding:0px;
    }
    
    10.考慮SEO要加上H1、H2....:
    h1,h2,h3 {
     padding:0px;
     margin:0px;
     font-weight:normal; 
    }
    
    11.可將好看的色碼記下來以後可以用
    12.適度的用icon可讓版面更好看

    2014年8月4日 星期一

    【Jquery】canvas繪圖1 - 矩形

    畫矩形
    Html:
    <canvas id="myCanvas" width="100" height="100"></canvas>
    
    Javascript:
    $ele = $('#myCanvas').css({border: '1px solid #333'}).get(0);
    // 不能使用CSS的width跟height 或在canvas標籤上寫長寬 也可不限制
    $ele.width = 100; 
    $cv = $ele.getContext('2d'); // 畫布環境 用Jquery要取得物件要加上get(0)
    //$cv = document.getElementById('myCanvas').getContext("2d"); // 用document.getElementById的方法
    $cv.fillStyle = "rgba(0,0,255,0.5)";
    $cv.fillRect(0,0,50,50);
    $cv.fillStyle = "rgba(255,0,0,0.8)";
    $cv.fillRect(50,50,100,100);
    
    結果: