<?php
include 'dbTool.php';
date_default_timezone_set('Asia/Taipei');

if(!empty($_SERVER['HTTP_CLIENT_IP'])){
   $myip = $_SERVER['HTTP_CLIENT_IP'];
}else if(!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
   $myip = $_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
   $myip= $_SERVER['REMOTE_ADDR'];
}
if(substr($myip,0,7)=='192.168'){
}
else{
	$f=fopen('./log/log.txt','a');
	fwrite($f,date('Y/m/d H:i:s -- '.$myip));
	fclose($f);
}

$company=strtoupper($_GET['x']);
$dept=(isset($_GET['y']))?($_GET['y']):(" ");
$folder=$_GET['folder'];
$month=(isset($_GET['m']))?($_GET['m']):("");
$day=(isset($_GET['d']))?($_GET['d']):("");
$year=(isset($_GET['Y']))?($_GET['Y']):("");

$mysqlconn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql');
$sql="SELECT createdatetime FROM alldetails WHERE createdatetime LIKE '".date("Ym")."%' ORDER BY createdatetime DESC LIMIT 2";
$table=sqlquery($mysqlconn,$sql,'mysql');

/*$sql='SELECT finaltime from userlogin where dept="'.strtoupper($company).'"';
$final=sqlquery($mysqlconn,$sql,'mysql');*/

if(file_exists("../../DB/".$folder."/SALES_".$year.$month.".DB")){
	$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".$year.$month.".DB","","","","sqlite");
	$sql="SELECT CREATEDATETIME,BIZDATE FROM CST012 ORDER BY CREATEDATETIME DESC LIMIT 1";
	$lasttime=sqlquery($sqliteconn,$sql,'sqlite');
	sqlclose($sqliteconn,'sqlite');
}
else{
	$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".date("Ym",strtotime($year.$month." -1 month")).".DB","","","","sqlite");
	$sql="SELECT CREATEDATETIME,BIZDATE FROM CST012 ORDER BY CREATEDATETIME DESC LIMIT 1";
	$lasttime=sqlquery($sqliteconn,$sql,'sqlite');
	sqlclose($sqliteconn,'sqlite');
}
/*
以下年、月、日變數主要用於連假情況，否則一般情況會與傳來之變數相同
*/
$diff=date_diff(date_create(date($year."-".$month."-".$day)),date_create(date(substr($lasttime[0]['CREATEDATETIME'],0,4)."-".substr($lasttime[0]['CREATEDATETIME'],4,2)."-".substr($lasttime[0]['CREATEDATETIME'],6,2))));
if($diff->format("%R")=='-'){
	$year=substr($lasttime[0]['BIZDATE'],0,4);
	while(strlen($year)<4){
		$year="0".$year;
	}
	$month=substr($lasttime[0]['BIZDATE'],4,2);
	if(strlen($month)==1){
		$month="0".$month;
	}
	$day=substr($lasttime[0]['BIZDATE'],6,2);
	if(strlen($day)==1){
		$day="0".$day;
	}
}


/*if($final[0]['finaltime']==''){//紀錄最後更新時間，此區塊是使用最新一筆記錄之時間，修改成當下新增時間
	$sql='UPDATE userlogin SET finaltime="'.date(substr($lasttime[0]['CREATEDATETIME'],0,4)."-".substr($lasttime[0]['CREATEDATETIME'],4,2)."-".substr($lasttime[0]['CREATEDATETIME'],6,2)." ".substr($lasttime[0]['CREATEDATETIME'],8,2).":".substr($lasttime[0]['CREATEDATETIME'],10,2).":".substr($lasttime[0]['CREATEDATETIME'],12,2)).'" WHERE dept="'.strtoupper($company).'"';
	$table1=sqlnoresponse($mysqlconn,$sql,'mysql');
}
else{
	$diff=date_diff(date_create($final[0]['finaltime']),date_create(date(substr($lasttime[0]['CREATEDATETIME'],0,4)."-".substr($lasttime[0]['CREATEDATETIME'],4,2)."-".substr($lasttime[0]['CREATEDATETIME'],6,2)." ".substr($lasttime[0]['CREATEDATETIME'],8,2).":".substr($lasttime[0]['CREATEDATETIME'],10,2).":".substr($lasttime[0]['CREATEDATETIME'],12,2))));
	//echo $diff->format("%R");
	if($diff->format("%R")=='+'){
		$sql='UPDATE userlogin SET finaltime="'.date(substr($lasttime[0]['CREATEDATETIME'],0,4)."-".substr($lasttime[0]['CREATEDATETIME'],4,2)."-".substr($lasttime[0]['CREATEDATETIME'],6,2)." ".substr($lasttime[0]['CREATEDATETIME'],8,2).":".substr($lasttime[0]['CREATEDATETIME'],10,2).":".substr($lasttime[0]['CREATEDATETIME'],12,2)).'" WHERE dept="'.strtoupper($company).'"';
		$table1=sqlnoresponse($mysqlconn,$sql,'mysql');
	}
}*/
$sql='UPDATE userlogin SET finaltime="'.date('Y-m-d H:i:s').'" WHERE dept="'.strtoupper($company).'"';
$table1=sqlnoresponse($mysqlconn,$sql,'mysql');

if(strlen($month)>0){
	$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".$year.$month.".DB","","","","sqlite");
	//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CASE CST012.DTLMODE WHEN CST012.DTLMODE='' THEN NULL ELSE CST012.DTLMODE END,CASE CST012.DTLTYPE WHEN CST012.DTLTYPE='' THEN NULL ELSE CST012.DTLTYPE END,CASE CST012.DTLFUNC WHEN CST012.DTLFUNC='' THEN NULL ELSE CST012.DTLFUNC END,CASE CST012.ITEMCODE WHEN CST012.ITEMCODE='' THEN NULL ELSE CST012.ITEMCODE END,CASE CST012.ITEMNAME WHEN CST012.ITEMNAME='' THEN NULL ELSE CST012.ITEMNAME END,CASE CST012.ITEMGRPCODE WHEN CST012.ITEMGRPCODE='' THEN NULL ELSE CST012.ITEMGRPCODE END, CASE CST012.ITEMGRPNAME WHEN CST012.ITEMGRPNAME='' THEN NULL ELSE CST012.ITEMGRPNAME END,CASE CST012.ITEMDEPTCODE WHEN CST012.ITEMDEPTCODE='' THEN NULL ELSE CST012.ITEMDEPTCODE END,CASE CST012.ITEMDEPTNAME WHEN CST012.ITEMDEPTNAME='' THEN NULL ELSE CST012.ITEMDEPTNAME END,CASE CST012.WEIGHT WHEN CST012.WEIGHT='' THEN NULL ELSE CST012.WEIGHT END,CASE CST012.QTY WHEN CST012.QTY='' THEN NULL ELSE CST012.QTY END,CASE CST012.UNITPRICE WHEN CST012.UNITPRICE='' THEN NULL ELSE CST012.UNITPRICE END,CASE CST012.AMT WHEN CST012.AMT='' THEN NULL ELSE CST012.AMT END,CASE CST011.CUSTGPCODE WHEN CST011.CUSTGPCODE='' THEN NULL ELSE CST011.CUSTGPCODE END,CASE CST011.CUSTGPNAME WHEN CST011.CUSTGPNAME='' THEN NULL ELSE CST011.CUSTGPNAME END,CASE CST012.ZCOUNTER WHEN CST012.ZCOUNTER='' THEN NULL ELSE CST012.ZCOUNTER END,CASE CST012.CREATEDATETIME WHEN CST012.CREATEDATETIME='' THEN NULL ELSE CST012.CREATEDATETIME END FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.CONSECNUMBER=CST012.CONSECNUMBER ORDER BY CST012.CREATEDATETIME DESC";
	//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CST012.DTLMODE,CST012.DTLTYPE,CST012.DTLFUNC,CST012.ITEMCODE,CST012.ITEMNAME,CST012.ITEMGRPCODE,CST012.ITEMGRPNAME,CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.WEIGHT,CST012.QTY,CST012.UNITPRICE,CST012.AMT,CST011.CUSTGPCODE,CST011.CUSTGPNAME,CST012.ZCOUNTER,CST012.CREATEDATETIME,CST011.SALESTTLAMT,CST011.INVOICENUMBER FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03') AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) OR (CST012.DTLMODE='1' AND CST012.DTLTYPE='5' AND CST012.DTLFUNC='14')) ORDER BY CST012.CREATEDATETIME DESC";
	$sql="SELECT CST012.CONSECNUMBER AS CONSECNUMBER,CST012.LINENUMBER AS LINENUMBER,CST012.BIZDATE AS BIZDATE,CST012.DTLMODE AS DTLMODE,CST012.DTLTYPE AS DTLTYPE,CST012.DTLFUNC AS DTLFUNC,CST012.ITEMCODE AS ITEMCODE,CST012.ITEMNAME AS ITEMNAME,CST012.ITEMGRPCODE AS ITEMGRPCODE,CST012.ITEMGRPNAME AS ITEMGRPNAME,CST012.ITEMDEPTCODE AS ITEMDEPTCODE,CST012.ITEMDEPTNAME AS ITEMDEPTNAME,CST012.WEIGHT AS WEIGHT,CST012.QTY AS QTY,CST012.UNITPRICE AS UNITPRICE,CST012.AMT AS AMT,CST011.CUSTGPCODE AS CUSTGPCODE,CST011.CUSTGPNAME AS CUSTGPNAME,CST012.ZCOUNTER AS ZCOUNTER,CST012.CREATEDATETIME AS CREATEDATETIME,CST011.SALESTTLAMT AS SALESTTLAMT,CST011.INVOICENUMBER AS INVOICENUMBER FROM CST012 JOIN (SELECT BIZDATE,CONSECNUMBER,ZCOUNTER,CUSTGPCODE,CUSTGPNAME,SALESTTLAMT,INVOICENUMBER FROM CST011 WHERE BIZDATE='".$year.$month.$day."') AS CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03') AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3'))) ORDER BY CST012.CREATEDATETIME DESC";//過濾1.5.14品項
	$table=sqlquery($sqliteconn,$sql,'sqlite');
	for($i=0;$i<sizeof($table);$i++){
		$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$table[$i]['CONSECNUMBER']."',".$table[$i]['LINENUMBER'].",'".$table[$i]['BIZDATE']."','".$table[$i]['DTLMODE']."','".$table[$i]['DTLTYPE']."','".$table[$i]['DTLFUNC']."','".$table[$i]['ITEMCODE']."','".$table[$i]['ITEMNAME']."','".$table[$i]['ITEMGRPCODE']."','".$table[$i]['ITEMGRPNAME']."','".$table[$i]['ITEMDEPTCODE']."','".$table[$i]['ITEMDEPTNAME']."',".$table[$i]['WEIGHT'].",".$table[$i]['QTY'].",".$table[$i]['UNITPRICE'].",".$table[$i]['AMT'].",'".$table[$i]['CUSTGPCODE']."','".$table[$i]['CUSTGPNAME']."',".$table[$i]['SALESTTLAMT'].",".$table[$i]['INVOICENUMBER'].",".$table[$i]['ZCOUNTER'].",'".$table[$i]['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
		$tt=mysqli_query($mysqlconn,$sql);
		if($tt){
		}
		else{
			//echo "SQL語法錯誤";
			//break;
		}
	}

	/*foreach($table as $temp){
		$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$temp['CONSECNUMBER']."',".$temp['LINENUMBER'].",'".$temp['BIZDATE']."','".$temp['DTLMODE']."','".$temp['DTLTYPE']."','".$temp['DTLFUNC']."','".$temp['ITEMCODE']."','".$temp['ITEMNAME']."','".$temp['ITEMGRPCODE']."','".$temp['ITEMGRPNAME']."','".$temp['ITEMDEPTCODE']."','".$temp['ITEMDEPTNAME']."',".$temp['WEIGHT'].",".$temp['QTY'].",".$temp['UNITPRICE'].",".$temp['AMT'].",'".$temp['CUSTGPCODE']."','".$temp['CUSTGPNAME']."',".$temp['SALESTTLAMT'].",".$temp['INVOICENUMBER'].",".$temp['ZCOUNTER'].",'".$temp['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
		$tt=mysqli_query($mysqlconn,$sql);
		if($tt){
		}
		else{
			//echo "SQL語法錯誤";
			//break;
		}
	}*/
	$sql="SELECT * FROM CST011 WHERE BIZDATE='".$year.$month.$day."' AND REGTYPE='1' AND REGFUNC='1'";
	$list=sqlquery($sqliteconn,$sql,'sqlite');
	for($i=0;$i<sizeof($list);$i++){
		$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$list[$i]['TERMINALNUMBER']."','".$list[$i]['BIZDATE']."','".$list[$i]['CONSECNUMBER']."','".$list[$i]['INVOICENUMBER']."','".$list[$i]['INVOICEDATE']."','".$list[$i]['INVOICETIME']."','".$list[$i]['OPENCLKCODE']."','".$list[$i]['CLKCODE']."','".$list[$i]['CLKNAME']."','".$list[$i]['REGMODE']."','".$list[$i]['REGTYPE']."','".$list[$i]['REGFUNC']."',".$list[$i]['SALESTTLQTY'].",".$list[$i]['SALESTTLAMT'].",".$list[$i]['TAX1'].",".$list[$i]['TAX2'].",".$list[$i]['TAX3'].",".$list[$i]['TAX4'].",".$list[$i]['TAX5'].",".$list[$i]['TAX6'].",".$list[$i]['TAX7'].",".$list[$i]['TAX8'].",".$list[$i]['TAX9'].",".$list[$i]['TAX10'].",".$list[$i]['TA1'].",".$list[$i]['TA2'].",".$list[$i]['TA3'].",".$list[$i]['TA4'].",".$list[$i]['TA5'].",".$list[$i]['TA6'].",".$list[$i]['TA7'].",".$list[$i]['TA8'].",".$list[$i]['TA9'].",".$list[$i]['TA10'].",".$list[$i]['EX1'].",".$list[$i]['EX2'].",".$list[$i]['EX3'].",".$list[$i]['EX4'].",".$list[$i]['EX5'].",".$list[$i]['EX6'].",".$list[$i]['EX7'].",".$list[$i]['EX8'].",".$list[$i]['EX9'].",".$list[$i]['EX10'].",".$list[$i]['NONTAX'].",".$list[$i]['PROFITAMT'].",".$list[$i]['COVER'].",'".$list[$i]['CUSTGPCODE']."','".$list[$i]['CUSTGPNAME']."','".$list[$i]['CUSTCODE']."','".$list[$i]['CUSTNAME']."',".$list[$i]['POINTTARGET'].",".$list[$i]['POINTPREVIOUS'].",".$list[$i]['POINTGOT'].",".$list[$i]['POINTUSED'].",'".$list[$i]['OPENCHKDATE']."','".$list[$i]['OPENCHKTIME']."','".$list[$i]['NBCHKDATE']."','".$list[$i]['NBCHKTIME']."','".$list[$i]['NBCHKNUMBER']."','".$list[$i]['TABLENUMBER']."','".$list[$i]['RELINVOICEDATE']."','".$list[$i]['RELINVOICETIME']."','".$list[$i]['RELINVOICENUMBER']."','".$list[$i]['ZCOUNTER']."','".$list[$i]['REMARKS']."','".$list[$i]['CREATEDATETIME']."','".$list[$i]['UPDATEDATETIME']."')";
		$tt=mysqli_query($mysqlconn,$sql);
	}

	/*foreach($list as $a){
		$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$a['TERMINALNUMBER']."','".$a['BIZDATE']."','".$a['CONSECNUMBER']."','".$a['INVOICENUMBER']."','".$a['INVOICEDATE']."','".$a['INVOICETIME']."','".$a['OPENCLKCODE']."','".$a['CLKCODE']."','".$a['CLKNAME']."','".$a['REGMODE']."','".$a['REGTYPE']."','".$a['REGFUNC']."',".$a['SALESTTLQTY'].",".$a['SALESTTLAMT'].",".$a['TAX1'].",".$a['TAX2'].",".$a['TAX3'].",".$a['TAX4'].",".$a['TAX5'].",".$a['TAX6'].",".$a['TAX7'].",".$a['TAX8'].",".$a['TAX9'].",".$a['TAX10'].",".$a['TA1'].",".$a['TA2'].",".$a['TA3'].",".$a['TA4'].",".$a['TA5'].",".$a['TA6'].",".$a['TA7'].",".$a['TA8'].",".$a['TA9'].",".$a['TA10'].",".$a['EX1'].",".$a['EX2'].",".$a['EX3'].",".$a['EX4'].",".$a['EX5'].",".$a['EX6'].",".$a['EX7'].",".$a['EX8'].",".$a['EX9'].",".$a['EX10'].",".$a['NONTAX'].",".$a['PROFITAMT'].",".$a['COVER'].",'".$a['CUSTGPCODE']."','".$a['CUSTGPNAME']."','".$a['CUSTCODE']."','".$a['CUSTNAME']."',".$a['POINTTARGET'].",".$a['POINTPREVIOUS'].",".$a['POINTGOT'].",".$a['POINTUSED'].",'".$a['OPENCHKDATE']."','".$a['OPENCHKTIME']."','".$a['NBCHKDATE']."','".$a['NBCHKTIME']."','".$a['NBCHKNUMBER']."','".$a['TABLENUMBER']."','".$a['RELINVOICEDATE']."','".$a['RELINVOICETIME']."','".$a['RELINVOICENUMBER']."','".$a['ZCOUNTER']."','".$a['REMARKS']."','".$a['CREATEDATETIME']."','".$a['UPDATEDATETIME']."')";
		$tt=mysqli_query($mysqlconn,$sql);
	}*/
	sqlclose($sqliteconn,'sqlite');
}
else{
	if(sizeof($table)>0){//只新增當月DB
		$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".$year.$month.".DB","","","","sqlite");
		//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CASE CST012.DTLMODE WHEN CST012.DTLMODE='' THEN NULL ELSE CST012.DTLMODE END,CASE CST012.DTLTYPE WHEN CST012.DTLTYPE='' THEN NULL ELSE CST012.DTLTYPE END,CASE CST012.DTLFUNC WHEN CST012.DTLFUNC='' THEN NULL ELSE CST012.DTLFUNC END,CASE CST012.ITEMCODE WHEN CST012.ITEMCODE='' THEN NULL ELSE CST012.ITEMCODE END,CASE CST012.ITEMNAME WHEN CST012.ITEMNAME='' THEN NULL ELSE CST012.ITEMNAME END,CASE CST012.ITEMGRPCODE WHEN CST012.ITEMGRPCODE='' THEN NULL ELSE CST012.ITEMGRPCODE END, CASE CST012.ITEMGRPNAME WHEN CST012.ITEMGRPNAME='' THEN NULL ELSE CST012.ITEMGRPNAME END,CASE CST012.ITEMDEPTCODE WHEN CST012.ITEMDEPTCODE='' THEN NULL ELSE CST012.ITEMDEPTCODE END,CASE CST012.ITEMDEPTNAME WHEN CST012.ITEMDEPTNAME='' THEN NULL ELSE CST012.ITEMDEPTNAME END,CASE CST012.WEIGHT WHEN CST012.WEIGHT='' THEN NULL ELSE CST012.WEIGHT END,CASE CST012.QTY WHEN CST012.QTY='' THEN NULL ELSE CST012.QTY END,CASE CST012.UNITPRICE WHEN CST012.UNITPRICE='' THEN NULL ELSE CST012.UNITPRICE END,CASE CST012.AMT WHEN CST012.AMT='' THEN NULL ELSE CST012.AMT END,CASE CST011.CUSTGPCODE WHEN CST011.CUSTGPCODE='' THEN NULL ELSE CST011.CUSTGPCODE END,CASE CST011.CUSTGPNAME WHEN CST011.CUSTGPNAME='' THEN NULL ELSE CST011.CUSTGPNAME END,CASE CST012.ZCOUNTER WHEN CST012.ZCOUNTER='' THEN NULL ELSE CST012.ZCOUNTER END,CASE CST012.CREATEDATETIME WHEN CST012.CREATEDATETIME='' THEN NULL ELSE CST012.CREATEDATETIME END FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.CONSECNUMBER=CST012.CONSECNUMBER ORDER BY CST012.CREATEDATETIME DESC";
		//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CST012.DTLMODE,CST012.DTLTYPE,CST012.DTLFUNC,CST012.ITEMCODE,CST012.ITEMNAME,CST012.ITEMGRPCODE,CST012.ITEMGRPNAME,CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.WEIGHT,CST012.QTY,CST012.UNITPRICE,CST012.AMT,CST011.CUSTGPCODE,CST011.CUSTGPNAME,CST012.ZCOUNTER,CST012.CREATEDATETIME,CST011.SALESTTLAMT,CST011.INVOICENUMBER FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER AND CST011.ZCOUNTER=CST012.ZCOUNTER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) OR (CST012.DTLMODE='1' AND CST012.DTLTYPE='5' AND CST012.DTLFUNC='14')) ORDER BY CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";
		$sql="SELECT CST012.CONSECNUMBER AS CONSECNUMBER,CST012.LINENUMBER AS LINENUMBER,CST012.BIZDATE AS BIZDATE,CST012.DTLMODE AS DTLMODE,CST012.DTLTYPE AS DTLTYPE,CST012.DTLFUNC AS DTLFUNC,CST012.ITEMCODE AS ITEMCODE,CST012.ITEMNAME AS ITEMNAME,CST012.ITEMGRPCODE AS ITEMGRPCODE,CST012.ITEMGRPNAME AS ITEMGRPNAME,CST012.ITEMDEPTCODE AS ITEMDEPTCODE,CST012.ITEMDEPTNAME AS ITEMDEPTNAME,CST012.WEIGHT AS WEIGHT,CST012.QTY AS QTY,CST012.UNITPRICE AS UNITPRICE,CST012.AMT AS AMT,CST011.CUSTGPCODE AS CUSTGPCODE,CST011.CUSTGPNAME AS CUSTGPNAME,CST012.ZCOUNTER AS ZCOUNTER,CST012.CREATEDATETIME AS CREATEDATETIME,CST011.SALESTTLAMT AS SALESTTLAMT,CST011.INVOICENUMBER AS INVOICENUMBER FROM CST012 JOIN (SELECT BIZDATE,CONSECNUMBER,ZCOUNTER,CUSTGPCODE,CUSTGPNAME,SALESTTLAMT,INVOICENUMBER FROM CST011 WHERE BIZDATE='".$year.$month.$day."') AS CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER AND CST011.ZCOUNTER=CST012.ZCOUNTER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) ORDER BY CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";//過濾1.5.14品項
		$index=0;
		$table=sqlquery($sqliteconn,$sql,'sqlite');
		for($i=0;$i<sizeof($table);$i++){
			$index++;
			$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$table[$i]['CONSECNUMBER']."',".$table[$i]['LINENUMBER'].",'".$table[$i]['BIZDATE']."','".$table[$i]['DTLMODE']."','".$table[$i]['DTLTYPE']."','".$table[$i]['DTLFUNC']."','".$table[$i]['ITEMCODE']."','".$table[$i]['ITEMNAME']."','".$table[$i]['ITEMGRPCODE']."','".$table[$i]['ITEMGRPNAME']."','".$table[$i]['ITEMDEPTCODE']."','".$table[$i]['ITEMDEPTNAME']."',".$table[$i]['WEIGHT'].",".$table[$i]['QTY'].",".$table[$i]['UNITPRICE'].",".$table[$i]['AMT'].",'".$table[$i]['CUSTGPCODE']."','".$table[$i]['CUSTGPNAME']."',".$table[$i]['SALESTTLAMT'].",".$table[$i]['INVOICENUMBER'].",".$table[$i]['ZCOUNTER'].",'".$table[$i]['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
			$tt=mysqli_query($mysqlconn,$sql);
			if($tt){
			}
			else{
				//echo "SQL語法錯誤".$sql;
				//break;
			}
			/*if($index==1000){
				sqlclose($mysqlconn,'mysql');
				$mysqlconn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql');
			}*/
		}

		/*foreach($table as $temp){
			$index++;
			$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$temp['CONSECNUMBER']."',".$temp['LINENUMBER'].",'".$temp['BIZDATE']."','".$temp['DTLMODE']."','".$temp['DTLTYPE']."','".$temp['DTLFUNC']."','".$temp['ITEMCODE']."','".$temp['ITEMNAME']."','".$temp['ITEMGRPCODE']."','".$temp['ITEMGRPNAME']."','".$temp['ITEMDEPTCODE']."','".$temp['ITEMDEPTNAME']."',".$temp['WEIGHT'].",".$temp['QTY'].",".$temp['UNITPRICE'].",".$temp['AMT'].",'".$temp['CUSTGPCODE']."','".$temp['CUSTGPNAME']."',".$temp['SALESTTLAMT'].",".$temp['INVOICENUMBER'].",".$temp['ZCOUNTER'].",'".$temp['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
			$tt=mysqli_query($mysqlconn,$sql);
			if($tt){
			}
			else{
				//echo "SQL語法錯誤".$sql;
				//break;
			}
			//if($index==1000){
			//	sqlclose($mysqlconn,'mysql');
			//	$mysqlconn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql');
			//}
		}*/
		$sql="SELECT * FROM CST011 WHERE BIZDATE='".$year.$month.$day."' AND REGTYPE='1' AND REGFUNC='1'";
		$list=sqlquery($sqliteconn,$sql,'sqlite');
		for($i=0;$i<sizeof($list);$i++){
			$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$list[$i]['TERMINALNUMBER']."','".$a['BIZDATE']."','".$list[$i]['CONSECNUMBER']."','".$list[$i]['INVOICENUMBER']."','".$list[$i]['INVOICEDATE']."','".$list[$i]['INVOICETIME']."','".$list[$i]['OPENCLKCODE']."','".$list[$i]['CLKCODE']."','".$list[$i]['CLKNAME']."','".$list[$i]['REGMODE']."','".$list[$i]['REGTYPE']."','".$list[$i]['REGFUNC']."',".$list[$i]['SALESTTLQTY'].",".$list[$i]['SALESTTLAMT'].",".$list[$i]['TAX1'].",".$list[$i]['TAX2'].",".$list[$i]['TAX3'].",".$list[$i]['TAX4'].",".$list[$i]['TAX5'].",".$list[$i]['TAX6'].",".$list[$i]['TAX7'].",".$list[$i]['TAX8'].",".$list[$i]['TAX9'].",".$list[$i]['TAX10'].",".$list[$i]['TA1'].",".$list[$i]['TA2'].",".$list[$i]['TA3'].",".$list[$i]['TA4'].",".$list[$i]['TA5'].",".$list[$i]['TA6'].",".$list[$i]['TA7'].",".$list[$i]['TA8'].",".$list[$i]['TA9'].",".$list[$i]['TA10'].",".$list[$i]['EX1'].",".$list[$i]['EX2'].",".$list[$i]['EX3'].",".$list[$i]['EX4'].",".$list[$i]['EX5'].",".$list[$i]['EX6'].",".$list[$i]['EX7'].",".$list[$i]['EX8'].",".$list[$i]['EX9'].",".$list[$i]['EX10'].",".$list[$i]['NONTAX'].",".$list[$i]['PROFITAMT'].",".$list[$i]['COVER'].",'".$list[$i]['CUSTGPCODE']."','".$list[$i]['CUSTGPNAME']."','".$list[$i]['CUSTCODE']."','".$list[$i]['CUSTNAME']."',".$list[$i]['POINTTARGET'].",".$list[$i]['POINTPREVIOUS'].",".$list[$i]['POINTGOT'].",".$list[$i]['POINTUSED'].",'".$list[$i]['OPENCHKDATE']."','".$list[$i]['OPENCHKTIME']."','".$list[$i]['NBCHKDATE']."','".$list[$i]['NBCHKTIME']."','".$list[$i]['NBCHKNUMBER']."','".$list[$i]['TABLENUMBER']."','".$list[$i]['RELINVOICEDATE']."','".$list[$i]['RELINVOICETIME']."','".$list[$i]['RELINVOICENUMBER']."','".$list[$i]['ZCOUNTER']."','".$list[$i]['REMARKS']."','".$list[$i]['CREATEDATETIME']."','".$list[$i]['UPDATEDATETIME']."')";
			$tt=mysqli_query($mysqlconn,$sql);
		}

		/*foreach($list as $a){
			$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$a['TERMINALNUMBER']."','".$a['BIZDATE']."','".$a['CONSECNUMBER']."','".$a['INVOICENUMBER']."','".$a['INVOICEDATE']."','".$a['INVOICETIME']."','".$a['OPENCLKCODE']."','".$a['CLKCODE']."','".$a['CLKNAME']."','".$a['REGMODE']."','".$a['REGTYPE']."','".$a['REGFUNC']."',".$a['SALESTTLQTY'].",".$a['SALESTTLAMT'].",".$a['TAX1'].",".$a['TAX2'].",".$a['TAX3'].",".$a['TAX4'].",".$a['TAX5'].",".$a['TAX6'].",".$a['TAX7'].",".$a['TAX8'].",".$a['TAX9'].",".$a['TAX10'].",".$a['TA1'].",".$a['TA2'].",".$a['TA3'].",".$a['TA4'].",".$a['TA5'].",".$a['TA6'].",".$a['TA7'].",".$a['TA8'].",".$a['TA9'].",".$a['TA10'].",".$a['EX1'].",".$a['EX2'].",".$a['EX3'].",".$a['EX4'].",".$a['EX5'].",".$a['EX6'].",".$a['EX7'].",".$a['EX8'].",".$a['EX9'].",".$a['EX10'].",".$a['NONTAX'].",".$a['PROFITAMT'].",".$a['COVER'].",'".$a['CUSTGPCODE']."','".$a['CUSTGPNAME']."','".$a['CUSTCODE']."','".$a['CUSTNAME']."',".$a['POINTTARGET'].",".$a['POINTPREVIOUS'].",".$a['POINTGOT'].",".$a['POINTUSED'].",'".$a['OPENCHKDATE']."','".$a['OPENCHKTIME']."','".$a['NBCHKDATE']."','".$a['NBCHKTIME']."','".$a['NBCHKNUMBER']."','".$a['TABLENUMBER']."','".$a['RELINVOICEDATE']."','".$a['RELINVOICETIME']."','".$a['RELINVOICENUMBER']."','".$a['ZCOUNTER']."','".$a['REMARKS']."','".$a['CREATEDATETIME']."','".$a['UPDATEDATETIME']."')";
			$tt=mysqli_query($mysqlconn,$sql);
		}*/
		sqlclose($sqliteconn,'sqlite');
	}
	else{//新增上月與當月DB
		for($i=0;$i<2;$i++){
			if($i){
				$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".$year.$month.".DB","","","","sqlite");
			}
			else{
				$sqliteconn=sqlconnect("../../DB/".$folder,"SALES_".date( "Ym", strtotime( date("Ym")." -1 month" ) ).".DB","","","","sqlite");
			}
			//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CASE CST012.DTLMODE WHEN CST012.DTLMODE='' THEN NULL ELSE CST012.DTLMODE END,CASE CST012.DTLTYPE WHEN CST012.DTLTYPE='' THEN NULL ELSE CST012.DTLTYPE END,CASE CST012.DTLFUNC WHEN CST012.DTLFUNC='' THEN NULL ELSE CST012.DTLFUNC END,CASE CST012.ITEMCODE WHEN CST012.ITEMCODE='' THEN NULL ELSE CST012.ITEMCODE END,CASE CST012.ITEMNAME WHEN CST012.ITEMNAME='' THEN NULL ELSE CST012.ITEMNAME END,CASE CST012.ITEMGRPCODE WHEN CST012.ITEMGRPCODE='' THEN NULL ELSE CST012.ITEMGRPCODE END, CASE CST012.ITEMGRPNAME WHEN CST012.ITEMGRPNAME='' THEN NULL ELSE CST012.ITEMGRPNAME END,CASE CST012.ITEMDEPTCODE WHEN CST012.ITEMDEPTCODE='' THEN NULL ELSE CST012.ITEMDEPTCODE END,CASE CST012.ITEMDEPTNAME WHEN CST012.ITEMDEPTNAME='' THEN NULL ELSE CST012.ITEMDEPTNAME END,CASE CST012.WEIGHT WHEN CST012.WEIGHT='' THEN NULL ELSE CST012.WEIGHT END,CASE CST012.QTY WHEN CST012.QTY='' THEN NULL ELSE CST012.QTY END,CASE CST012.UNITPRICE WHEN CST012.UNITPRICE='' THEN NULL ELSE CST012.UNITPRICE END,CASE CST012.AMT WHEN CST012.AMT='' THEN NULL ELSE CST012.AMT END,CASE CST011.CUSTGPCODE WHEN CST011.CUSTGPCODE='' THEN NULL ELSE CST011.CUSTGPCODE END,CASE CST011.CUSTGPNAME WHEN CST011.CUSTGPNAME='' THEN NULL ELSE CST011.CUSTGPNAME END,CASE CST012.ZCOUNTER WHEN CST012.ZCOUNTER='' THEN NULL ELSE CST012.ZCOUNTER END,CASE CST012.CREATEDATETIME WHEN CST012.CREATEDATETIME='' THEN NULL ELSE CST012.CREATEDATETIME END FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.CONSECNUMBER=CST012.CONSECNUMBER ORDER BY CST012.CREATEDATETIME DESC";
			if($i){
				//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CST012.DTLMODE,CST012.DTLTYPE,CST012.DTLFUNC,CST012.ITEMCODE,CST012.ITEMNAME,CST012.ITEMGRPCODE,CST012.ITEMGRPNAME,CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.WEIGHT,CST012.QTY,CST012.UNITPRICE,CST012.AMT,CST011.CUSTGPCODE,CST011.CUSTGPNAME,CST012.ZCOUNTER,CST012.CREATEDATETIME,CST011.SALESTTLAMT,CST011.INVOICENUMBER FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) OR (CST012.DTLMODE='1' AND CST012.DTLTYPE='5' AND CST012.DTLFUNC='14')) ORDER BY CST012.BIZDATE DESC,CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";
				$sql="SELECT CST012.CONSECNUMBER AS CONSECNUMBER,CST012.LINENUMBER AS LINENUMBER,CST012.BIZDATE AS BIZDATE,CST012.DTLMODE AS DTLMODE,CST012.DTLTYPE AS DTLTYPE,CST012.DTLFUNC AS DTLFUNC,CST012.ITEMCODE AS ITEMCODE,CST012.ITEMNAME AS ITEMNAME,CST012.ITEMGRPCODE AS ITEMGRPCODE,CST012.ITEMGRPNAME AS ITEMGRPNAME,CST012.ITEMDEPTCODE AS ITEMDEPTCODE,CST012.ITEMDEPTNAME AS ITEMDEPTNAME,CST012.WEIGHT AS WEIGHT,CST012.QTY AS QTY,CST012.UNITPRICE AS UNITPRICE,CST012.AMT AS AMT,CST011.CUSTGPCODE AS CUSTGPCODE,CST011.CUSTGPNAME AS CUSTGPNAME,CST012.ZCOUNTER AS ZCOUNTER,CST012.CREATEDATETIME AS CREATEDATETIME,CST011.SALESTTLAMT AS SALESTTLAMT,CST011.INVOICENUMBER AS INVOICENUMBER FROM CST012 JOIN (SELECT BIZDATE,CONSECNUMBER,ZCOUNTER,CUSTGPCODE,CUSTGPNAME,SALESTTLAMT,INVOICENUMBER FROM CST011 WHERE BIZDATE='".$year.$month.$day."') AS CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND CST012.BIZDATE='".$year.$month.$day."' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3'))) ORDER BY CST012.BIZDATE DESC,CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";//過濾1.5.14品項
			}
			else{
				//$sql="SELECT CST012.CONSECNUMBER,CST012.LINENUMBER,CST012.BIZDATE,CST012.DTLMODE,CST012.DTLTYPE,CST012.DTLFUNC,CST012.ITEMCODE,CST012.ITEMNAME,CST012.ITEMGRPCODE,CST012.ITEMGRPNAME,CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.WEIGHT,CST012.QTY,CST012.UNITPRICE,CST012.AMT,CST011.CUSTGPCODE,CST011.CUSTGPNAME,CST012.ZCOUNTER,CST012.CREATEDATETIME,CST011.SALESTTLAMT,CST011.INVOICENUMBER FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) OR (CST012.DTLMODE='1' AND CST012.DTLTYPE='5' AND CST012.DTLFUNC='14')) ORDER BY CST012.BIZDATE DESC,CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";
				$sql="SELECT CST012.CONSECNUMBER AS CONSECNUMBER,CST012.LINENUMBER AS LINENUMBER,CST012.BIZDATE AS BIZDATE,CST012.DTLMODE AS DTLMODE,CST012.DTLTYPE AS DTLTYPE,CST012.DTLFUNC AS DTLFUNC,CST012.ITEMCODE AS ITEMCODE,CST012.ITEMNAME AS ITEMNAME,CST012.ITEMGRPCODE AS ITEMGRPCODE,CST012.ITEMGRPNAME AS ITEMGRPNAME,CST012.ITEMDEPTCODE AS ITEMDEPTCODE,CST012.ITEMDEPTNAME AS ITEMDEPTNAME,CST012.WEIGHT AS WEIGHT,CST012.QTY AS QTY,CST012.UNITPRICE AS UNITPRICE,CST012.AMT AS AMT,CST011.CUSTGPCODE AS CUSTGPCODE,CST011.CUSTGPNAME AS CUSTGPNAME,CST012.ZCOUNTER AS ZCOUNTER,CST012.CREATEDATETIME AS CREATEDATETIME,CST011.SALESTTLAMT AS SALESTTLAMT,CST011.INVOICENUMBER AS INVOICENUMBER FROM CST012 JOIN (SELECT BIZDATE,CONSECNUMBER,ZCOUNTER,CUSTGPCODE,CUSTGPNAME,SALESTTLAMT,INVOICENUMBER FROM CST011) AS CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.ZCOUNTER=CST012.ZCOUNTER AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.CONSECNUMBER<>'000000' AND ((CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03')) AND CST012.ITEMDEPTCODE<>'000008') OR (CST012.DTLMODE='1' AND (CST012.DTLTYPE='2' OR CST012.DTLTYPE='3')) ORDER BY CST012.BIZDATE DESC,CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";//過濾1.5.14品項
			}
			$table=sqlquery($sqliteconn,$sql,'sqlite');
			for($j=0;$j<sizeof($table);$j++){
				$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$table[$j]['CONSECNUMBER']."',".$table[$j]['LINENUMBER'].",'".$table[$j]['BIZDATE']."','".$table[$j]['DTLMODE']."','".$table[$j]['DTLTYPE']."','".$table[$j]['DTLFUNC']."','".$table[$j]['ITEMCODE']."','".$table[$j]['ITEMNAME']."','".$table[$j]['ITEMGRPCODE']."','".$table[$j]['ITEMGRPNAME']."','".$table[$j]['ITEMDEPTCODE']."','".$table[$j]['ITEMDEPTNAME']."',".$table[$j]['WEIGHT'].",".$table[$j]['QTY'].",".$table[$j]['UNITPRICE'].",".$table[$j]['AMT'].",'".$table[$j]['CUSTGPCODE']."','".$table[$j]['CUSTGPNAME']."',".$table[$j]['SALESTTLAMT'].",".$table[$j]['INVOICENUMBER'].",".$table[$j]['ZCOUNTER'].",'".$table[$j]['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
				$tt=mysqli_query($mysqlconn,$sql);
				if($tt){
				}
				else{
					//echo "SQL語法錯誤";
					//break;
				}
			}

			/*foreach($table as $temp){
				$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,dtltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,salesttlamt,invoicenumber,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$temp['CONSECNUMBER']."',".$temp['LINENUMBER'].",'".$temp['BIZDATE']."','".$temp['DTLMODE']."','".$temp['DTLTYPE']."','".$temp['DTLFUNC']."','".$temp['ITEMCODE']."','".$temp['ITEMNAME']."','".$temp['ITEMGRPCODE']."','".$temp['ITEMGRPNAME']."','".$temp['ITEMDEPTCODE']."','".$temp['ITEMDEPTNAME']."',".$temp['WEIGHT'].",".$temp['QTY'].",".$temp['UNITPRICE'].",".$temp['AMT'].",'".$temp['CUSTGPCODE']."','".$temp['CUSTGPNAME']."',".$temp['SALESTTLAMT'].",".$temp['INVOICENUMBER'].",".$temp['ZCOUNTER'].",'".$temp['CREATEDATETIME']."')";//$temp['AMT']因CASIO程式邏輯關係，此欄位不能使用，所以改成($temp['QTY']*$temp['UNITPRICE'])
				$tt=mysqli_query($mysqlconn,$sql);
				if($tt){
				}
				else{
					//echo "SQL語法錯誤";
					//break;
				}
			}*/
			$sql="SELECT * FROM CST011 WHERE BIZDATE='".$year.$month.$day."' AND REGTYPE='1' AND REGFUNC='1'";
			$list=sqlquery($sqliteconn,$sql,'sqlite');
			for($j=0;$j<sizeof($list);$j++){
				$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$list[$j]['TERMINALNUMBER']."','".$list[$j]['BIZDATE']."','".$list[$j]['CONSECNUMBER']."','".$list[$j]['INVOICENUMBER']."','".$list[$j]['INVOICEDATE']."','".$list[$j]['INVOICETIME']."','".$list[$j]['OPENCLKCODE']."','".$list[$j]['CLKCODE']."','".$list[$j]['CLKNAME']."','".$list[$j]['REGMODE']."','".$list[$j]['REGTYPE']."','".$list[$j]['REGFUNC']."',".$list[$j]['SALESTTLQTY'].",".$list[$j]['SALESTTLAMT'].",".$list[$j]['TAX1'].",".$list[$j]['TAX2'].",".$list[$j]['TAX3'].",".$list[$j]['TAX4'].",".$list[$j]['TAX5'].",".$list[$j]['TAX6'].",".$list[$j]['TAX7'].",".$list[$j]['TAX8'].",".$list[$j]['TAX9'].",".$list[$j]['TAX10'].",".$list[$j]['TA1'].",".$list[$j]['TA2'].",".$list[$j]['TA3'].",".$list[$j]['TA4'].",".$list[$j]['TA5'].",".$list[$j]['TA6'].",".$list[$j]['TA7'].",".$list[$j]['TA8'].",".$list[$j]['TA9'].",".$list[$j]['TA10'].",".$list[$j]['EX1'].",".$list[$j]['EX2'].",".$list[$j]['EX3'].",".$list[$j]['EX4'].",".$list[$j]['EX5'].",".$list[$j]['EX6'].",".$list[$j]['EX7'].",".$list[$j]['EX8'].",".$list[$j]['EX9'].",".$list[$j]['EX10'].",".$list[$j]['NONTAX'].",".$list[$j]['PROFITAMT'].",".$list[$j]['COVER'].",'".$list[$j]['CUSTGPCODE']."','".$list[$j]['CUSTGPNAME']."','".$list[$j]['CUSTCODE']."','".$list[$j]['CUSTNAME']."',".$list[$j]['POINTTARGET'].",".$list[$j]['POINTPREVIOUS'].",".$list[$j]['POINTGOT'].",".$list[$j]['POINTUSED'].",'".$list[$j]['OPENCHKDATE']."','".$list[$j]['OPENCHKTIME']."','".$list[$j]['NBCHKDATE']."','".$list[$j]['NBCHKTIME']."','".$list[$j]['NBCHKNUMBER']."','".$list[$j]['TABLENUMBER']."','".$list[$j]['RELINVOICEDATE']."','".$list[$j]['RELINVOICETIME']."','".$list[$j]['RELINVOICENUMBER']."','".$list[$j]['ZCOUNTER']."','".$list[$j]['REMARKS']."','".$list[$j]['CREATEDATETIME']."','".$list[$j]['UPDATEDATETIME']."')";
				$tt=mysqli_query($mysqlconn,$sql);
			}

			/*foreach($list as $a){
				$sql="INSERT INTO CST011 (company,terminalnumber,bizdate,consecnumber,invoicenumber,invoicedate,invoicetime,openclkcode,clkcode,clkname,regmode,regtype,regfunc,salesttlqty,salesttlamt,tax1,tax2,tax3,tax4,tax5,tax6,tax7,tax8,tax9,tax10,ta1,ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ex1,ex2,ex3,ex4,ex5,ex6,ex7,ex8,ex9,ex10,nontax,profitamt,cover,custgpcode,custgpname,custcode,custname,pointtarget,pointprevious,pointgot,pointused,openchkdate,openchktime,nbchkdate,nbchktime,nbchknumber,tablenumber,relinvoicedate,relinvoicetime,relinvoicenumber,zcounter,remarks,createdatetime,updatedatetime) VALUES ('".$company."','".$a['TERMINALNUMBER']."','".$a['BIZDATE']."','".$a['CONSECNUMBER']."','".$a['INVOICENUMBER']."','".$a['INVOICEDATE']."','".$a['INVOICETIME']."','".$a['OPENCLKCODE']."','".$a['CLKCODE']."','".$a['CLKNAME']."','".$a['REGMODE']."','".$a['REGTYPE']."','".$a['REGFUNC']."',".$a['SALESTTLQTY'].",".$a['SALESTTLAMT'].",".$a['TAX1'].",".$a['TAX2'].",".$a['TAX3'].",".$a['TAX4'].",".$a['TAX5'].",".$a['TAX6'].",".$a['TAX7'].",".$a['TAX8'].",".$a['TAX9'].",".$a['TAX10'].",".$a['TA1'].",".$a['TA2'].",".$a['TA3'].",".$a['TA4'].",".$a['TA5'].",".$a['TA6'].",".$a['TA7'].",".$a['TA8'].",".$a['TA9'].",".$a['TA10'].",".$a['EX1'].",".$a['EX2'].",".$a['EX3'].",".$a['EX4'].",".$a['EX5'].",".$a['EX6'].",".$a['EX7'].",".$a['EX8'].",".$a['EX9'].",".$a['EX10'].",".$a['NONTAX'].",".$a['PROFITAMT'].",".$a['COVER'].",'".$a['CUSTGPCODE']."','".$a['CUSTGPNAME']."','".$a['CUSTCODE']."','".$a['CUSTNAME']."',".$a['POINTTARGET'].",".$a['POINTPREVIOUS'].",".$a['POINTGOT'].",".$a['POINTUSED'].",'".$a['OPENCHKDATE']."','".$a['OPENCHKTIME']."','".$a['NBCHKDATE']."','".$a['NBCHKTIME']."','".$a['NBCHKNUMBER']."','".$a['TABLENUMBER']."','".$a['RELINVOICEDATE']."','".$a['RELINVOICETIME']."','".$a['RELINVOICENUMBER']."','".$a['ZCOUNTER']."','".$a['REMARKS']."','".$a['CREATEDATETIME']."','".$a['UPDATEDATETIME']."')";
				$tt=mysqli_query($mysqlconn,$sql);
			}*/
			sqlclose($sqliteconn,'sqlite');
		}
	}
}

sqlclose($mysqlconn,'mysql');
echo "finish";

?>