--- 轉換 上傳profit excel date 42351 -> 20190901
25569=1970/01/01
UPDATE des_upfile SET BegDat=from_unixtime((CONCAT(LEFT(BegDat,3),RIGHT(BegDat,2))-25569)*86400,"%Y%m%d"),EndDat=from_unixtime((CONCAT(LEFT(EndDat,3),RIGHT(EndDat,2))-25569)*86400,"%Y%m%d") where LENGTH(BegDat)=6 ;
UPDATE svr_profit SET BilDat=from_unixtime((CONCAT(LEFT(BilDat,3),RIGHT(BilDat,2))-25569)*86400,"%Y%m%d") where LENGTH(BilDat)=6 ;
影響列數:  817815 (查詢需時 56.8922 秒)
UPDATE svr_wip SET BilDat=from_unixtime((CONCAT(LEFT(BilDat,3),RIGHT(BilDat,2))-25569)*86400,"%Y%m%d") where LENGTH(BilDat)=6 ;
影響列數:  52033 (查詢需時 0.7398 秒)
UPDATE svr_suggest SET SugDat=from_unixtime((SugDat-25569)*86400,"%Y%m%d") where LENGTH(SugDat)=5 ;
影響列數:  73059 (查詢需時 1.1123 秒)


SELECT PartNo FROM svr_profit WHERE PartNo='999990020200' AND FrNo7='B299188'
SELECT * FROM car_base WHERE PlateDat>'20180800' AND PlateDat<'20180832' ORDER BY PlateDat LIMIT 500 
INSERT INTO tmp_fr7 SELECT FrNo7 FROM svr_profit WHERE PartNo='999990020200'

================ YD RD BD SQL 新建 4 table ===================================

CREATE TABLE car_insur_desc LIKE car_insur;
CREATE TABLE car_insur_desc LIKE bd_car_insur;

CREATE TABLE car_base (
  FrNo7 varchar(7) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  DataSrcCat varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  FrNo10 varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PrdcMon varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarBrand varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarSeries varchar(2) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarClass varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarModel varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  EngCat varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  BmwCode varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  SaleMan varchar(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PlateNo varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PlateDat varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarColor varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarInColor varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  CarOptList varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `car_base_insur` (
  `FrNo7` varchar(7) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `DataSrcCat` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsCdCo` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsCdNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsCdDat` varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsCo` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `InsEndDat` varchar(8) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`FrNo7`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE car_man (
  ManId int(10) unsigned NOT NULL,
  FrNo7 varchar(7) DEFAULT NULL,
  ManCat varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  DataSrcCat varchar(20) DEFAULT NULL,
  ManIdNo varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  ManName varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManCity varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManAdr varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManTel varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManCell varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManBrDat varchar(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  ManBrMon varchar(2) DEFAULT NULL,
  VoidFlag varchar(2) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

================ 車源 轉出SQL ===================================
?? 5L85849,LM19597,LR56085 -> 依/鎔重覆
//續保多年 WBA3D1506DNN94549 NN94549
1.) Copy SQL table car_base, car_base_insur, car_man to YD,RD,BD KO
2.) Create subdir tmp on YD,RD,BD KO
3.) Copy car-src-XX-ud-to-pc.php.php to YD,RD,BD KO

//======================= connect to SQL =====================
mysql_connect('localhost', 'root', 'revres88') or die('connect fail!!');
//========= YD ============
//$tmpdb="b-work-yd";$rptnm="YD";
//========= RD ============
//$tmpdb="b-work";   $rptnm="RD";
//========= BD ============
//$tmpdb="b-work-bd";$rptnm="BD";
//========= PC ============
//$tmpdb="b-work-pc";$rptnm="PC";

//========= YD TEST ============
$tmpdb="b-work-yd-tmp";$rptnm="YD";

mysql_select_db($tmpdb);

$wkflg=true; //default false -> debug not work
$tmpi=0;

//--- clean car_man
$sql="
TRUNCATE TABLE car_man
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- car_pur + car_contract -> car_man
$sql="
INSERT INTO car_man 
(FrNo7,ManCat  ,DataSrcCat,ManIdNo           ,ManName,ManCity,ManAdr,ManTel,ManCell,ManBrDat,ManBrMon         ) SELECT 								
 FrNo7,'購車人','$rptnm'  ,CONCAT(IdNo,LicNo),Name   ,City   ,Adr   ,Tel   ,Cell   ,BirthDay,MID(BirthDay,4,2)
FROM car_pur INNER JOIN car_contract ON car_pur.SaleId=car_contract.SaleId
WHERE Status=81 AND FrNo7<>''
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- clean car_base
$sql="
TRUNCATE TABLE car_base
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- car_pur + car_pur_a + des_cat + car_contract -> car_base
$sql="
INSERT INTO car_base 
(FrNo7,DataSrcCat,FrNo10,PrdcMon,CarBrand,CarSeries,CarClass,CarModel,EngCat  ,BmwCode ,SaleMan,PlateNo,PlateDat,CarColor,CarInColor,CarOptList) SELECT 								
 FrNo7,'$rptnm'  ,FrNo10,PrdcMon,'BMW'   ,Series   ,Class   ,Model   ,EngCat  ,BmwCode ,SaleMan,PlateNo,PlateDat,Color   ,InColor   ,OptList
FROM ((car_pur INNER JOIN des_cat ON car_pur.CatId=des_cat.CatId) INNER JOIN car_pur_a ON car_pur.CarId=car_pur_a.CarId) LEFT JOIN car_contract ON car_pur.SaleId=car_contract.SaleId
WHERE Status=81 AND FrNo7<>'' AND car_pur.SaleId<>''
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- clean car_insur_desc
$sql="
TRUNCATE TABLE car_insur_desc;
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- reverse car_insur -> car_insur_desc
$sql="
INSERT INTO car_insur_desc 
(InsId,CarFrNo         ,InsCdNo,InsCdDat,InsCo,InsNo,InsEndDat) SELECT
 InsId,RIGHT(CarFrNo,7),InsCdNo,InsCdDat,InsCo,InsNo,InsEndDat 
FROM car_insur ORDER BY InsId DESC
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- clean car_base_insur
$sql="
TRUNCATE TABLE car_base_insur
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

//--- group car_insur_desc -> car_base_insur
$sql="
INSERT INTO car_base_insur
(FrNo7  ,DataSrcCat,InsCdCo,InsCdNo,InsCdDat,InsCo,InsNo,InsEndDat) SELECT
 CarFrNo,'$rptnm'  ,InsCo  ,InsCdNo,InsCdDat,InsCo,InsNo,InsEndDat 
FROM car_insur_desc GROUP BY CarFrNo
";echo $tmpi++."---->".date("Ymd H:i:s")."<br>".$sql."<br>";
if($wkflg)mysql_query($sql);

echo $tmpi++."---->".date("Ymd H:i:s")."<br>"."OKOK!!!<br>";

================ 從依德KO 抓 鎔德KO文件 ===================================
$SU="http://www.bmw-ld.com/tmp/qq-2.csv";
$TP="tmp/qq-xxx.csv";
// get file from rd to yd
downCurl($SU,$TP);
function downCurl($url,$filePath){
//初始化
$curl = curl_init();
//设置抓取的url
curl_setopt($curl, CURLOPT_URL, $url);
//打开文件描述符
$fp = fopen ($filePath, 'w+');
curl_setopt($curl, CURLOPT_FILE, $fp);
//这个选项是意思是跳转，如果你访问的页面跳转到另一个页面，也会模拟访问。
//curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
curl_setopt($curl,CURLOPT_TIMEOUT,50);

//执行命令
curl_exec($curl);
//关闭URL请求
curl_close($curl);
//关闭文件描述符
fclose($fp);
}

================ SQL -> CSV -> ZIP -> 解壓縮 -> CSV -> SQL ===================================
***** !!! 車源 MySQL 文字編碼: cp1252 West European (latin1) *****

$lccn=mysql_connect('localhost', 'root', 'revres88') or die('connect fail!!');
$lcdb="b_work_yd_tmp";
$lcdbh=mysql_select_db($lcdb);

$TP="/home/httpd/www/html/bpc/";
$TF="tmp/qq-1.csv";

$sql="
SELECT * FROM car_base  
INTO OUTFILE '".$TP.$TF."'
CHARACTER SET latin1 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\\n' ";
mysql_query($sql);

$TF2="tmp/qq-1.zip";
//system('zip -P 123abc '.$TP.$TF2.' '.$TP.$TF);

$zip = new ZipArchive();
$zip->open($TF2,ZipArchive::CREATE);   //打开压缩包
$zip->addFile($TF,basename($TF));   //向压缩包中添加文件
//$zip->setEncryptionName($TF2, ZipArchive::EM_AES_256, '123abc');
//$zip->withNewPassword('123abc');
$zip->close(); 
unlink($TF); // source qq-1.csv


$TF3="tmp/";
$openRes = $zip->open($TF2);
if ($openRes === TRUE) {
//	$zip->withReadPassword('123abc');
//	$zip->setPassword('123abc');
  $zip->extractTo($TF3);
  $zip->close();
}
unlink($TF2); // source qq-1.zip

$sql2="
LOAD DATA LOCAL INFILE '".$TP.$TF."'
INTO TABLE car_base_tmp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\\n' ;";
mysql_query($sql2);

echo "OK!";

================ 產生 + 比對 萬年曆 ===================================
$lccn=mysql_connect('localhost', 'root', 'revres88') or die('connect fail!!');
$lcdb="b-work-pc";
$lcdbh=mysql_select_db($lcdb);

//--- create des_calendar 2010-2030
for($i=0;$i<(365*20+5+1);$i++){
	$tmk = date('Ymd',strtotime("+".$i." day",strtotime("2010-01-01")));
	$sql="INSERT INTO des_calendar SET Dat=$tmk ";
	mysql_query($sql);
	echo $tmk."-";
}

xxx --------------
SELECT * FROM des_calendar LEFT JOIN svr_wip ON des_calendar.Dat=svr_wip.BilDat WHERE Dat>'20190320' AND Dat<'20190510' AND BilDat IS NULL
SELECT * FROM svr_wip LEFT JOIN svr_invoice ON svr_wip.Wip=svr_invoice.Wip WHERE BilDat>'20190409' AND BilDat<'20191009' AND BilNo IS NULL
SELECT * FROM svr_wip INNER JOIN svr_invoice ON svr_wip.Wip=svr_invoice.Wip WHERE BilDat>'20190320' AND BilDat<'20190510'
xxx ----------------

DELETE xxx FROM svr_invoice AS xxx INNER JOIN svr_wip ON xxx.Wip=svr_wip.Wip WHERE BilDat>'20190420' AND BilDat<'20191009'

SELECT * FROM (des_calendar LEFT JOIN svr_wip ON des_calendar.Dat=svr_wip.BilDat) LEFT JOIN svr_invoice ON svr_wip.Wip=svr_invoice.Wip WHERE Dat>'20190320' AND Dat<'20190510' AND BilNo IS NULL GROUP BY Dat

SELECT * FROM (des_calendar LEFT JOIN svr_wip ON des_calendar.Dat=svr_wip.BilDat) LEFT JOIN svr_profit ON svr_wip.Wip=svr_profit.Wip WHERE Dat>'20190320' AND Dat<'20190510' AND BilNo IS NULL GROUP BY Dat

SELECT * FROM svr_invoice INNER JOIN svr_wip ON svr_invoice.Wip=svr_wip.Wip WHERE FrNo7='NR17068' ORDER BY svr_invoice.BilDat DESC LIMIT 1

SELECT * FROM svr_profit WHERE PartNo='999990020200' AND  BilDat LIKE '2019%' ORDER BY BilDat LIMIT 500 
SELECT * FROM svr_profit INNER JOIN svr_wip ON svr_wip.Wip=svr_profit.Wip WHERE PartNo='999990020200' AND svr_profit.BilDat LIKE '2019%' ORDER BY svr_profit.BilDat LIMIT 500 

SELECT FrNo7 FROM svr_profit LEFT JOIN svr_wip ON svr_wip.Wip=svr_profit.Wip WHERE FrNo7='NR17068' AND PartDesc LIKE '%保養%'

SELECT FrNo7 FROM svr_wip LEFT JOIN svr_profit ON svr_wip.Wip=svr_profit.Wip WHERE FrNo7='NR17068' AND PartDesc LIKE '%保養%'
SELECT * FROM svr_profit WHERE (PartDesc LIKE '%保養%' OR PartNo='999990020200') AND BilDat LIKE '2019%'
SELECT * FROM svr_profit WHERE PartNo LIKE '999990020200' AND BilDat LIKE '2019%'

SELECT MAX(BilDat),MIN(BilDat),COUNT(BilDat) FROM svr_rda WHERE DataSrcTmk='20191010112026'
SELECT MAX(SrvDat),MIN(SrvDat),COUNT(SrvDat) FROM svr_rda WHERE DataSrcTmk='20191010112026'
SELECT BilDat FROM svr_invoice WHERE BilDat>='20190101' AND BilDat<='20191231' AND DataSrcCat='YS' GROUP BY BilDat ORDER BY BilDat

SELECT * FROM (car_base INNER JOIN car_man ON car_base.FrNo7=car_man.FrNo7) INNER JOIN svr_rda ON car_base.FrNo7=svr_rda.FrNo7 

BD PrdcMon->IF(LENGTH(PrdcMon)=7,CONCAT(MID(PrdcMon,3,2),RIGHT(PrdcMon,2)),PrdcMon)

================ 在鎔德同時連依德SQL ===================================
$lccn=mysql_connect('localhost', 'root', 'revres88') or die('connect fail!!');
$lcdb="b-work-dv";
$lcdbh=mysql_select_db($lcdb);

$pcip="118.163.15.73";
$pcid="vip_pc";
$pcpsw="Aa@2#3Zz";
$pcdb="b-work-pc";
$pccn = mysql_connect($pcip,$pcid,$pcpsw) or die('connect fail!!');
$pcdbh=mysql_select_db($pcdb);

$sql="SELECT * FROM des_cat";
$result=mysql_query($sql,$pccn);
$num=mysql_num_rows($result);
echo "---->".date("Ymd H:i:s")."---->".$num."<br>";

$sql="SELECT * FROM des_cat";
$result=mysql_query($sql,$lccn);
$num=mysql_num_rows($result);
echo "---->".date("Ymd H:i:s")."---->".$num."<br>";