<?php
include 'dbTool.php';

$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']):("");
date_default_timezone_set('Asia/Taipei');

$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,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'))) ORDER BY CST012.CREATEDATETIME DESC";//過濾1.5.14品項
	$table=sqlquery($sqliteconn,$sql,'sqlite');
	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');
	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,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'))) ORDER BY CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";//過濾1.5.14品項
		$index=0;
		$table=sqlquery($sqliteconn,$sql,'sqlite');
		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');
		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,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'))) 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,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'))) ORDER BY CST012.BIZDATE DESC,CST012.CREATEDATETIME DESC,CST012.ZCOUNTER DESC";//過濾1.5.14品項
			}
			$table=sqlquery($sqliteconn,$sql,'sqlite');
			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');
			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";

/*foreach($dbarray as $tempdb){
	$sqliteconn=sqlconnect("../DB/".$folder,$tempdb,"","","","sqlite");
	$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 FROM CST012 JOIN CST011 ON CST011.BIZDATE=CST012.BIZDATE AND CST011.CONSECNUMBER=CST012.CONSECNUMBER ORDER BY CREATETIME DESC";
	$table=sqlquery($sqliteconn,$sql,'sqlite');
	foreach($table as $temp){
		$sql="INSERT INTO alldetails (company,dept,consecnumber,linenumber,bizdate,dtlmode,ctltype,dtlfunc,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,weight,qty,unitprice,amt,custgpcode,custgpname,zcounter,createdatetime) VALUES ('".$company."','".$dept."','".$temp['CONSECNUMBER']."',".$temp['LINENUMBER'].",'".$temp['BIZDATE']."','".$temp['DTLMODE']."','".$temp['DTLTYPE']."','".$temp['DTLFUNC']."','".$temp['ITEMCODE']."','".$temp['ITEMNAME']."','".$temp['ITEMGRPCODE']."','".$temp['ITEMDEPTCODE']."','".$temp['ITEMDEPTNAME']."',".$temp['WEIGHT'].",".$temp['QTY'].",".$temp['UNITPRICE'].",".$temp['AMT'].",".$temp['CUSTGPCODE'].",'".$temp['CUSTGPNAME']."',".$temp['ZCOUNTER'].",'".$temp['CREATEDATETIME']."')";
		$tt=sqlquery($mysqlconn,$sql,'mysql');
		if(sizeof($tt)>0&&$tt[0]=="SQL語法錯誤"){
			break;
		}
	}
}*/
?>