'; $GLOBALS['broke']=''; } else{ echo ''; } break; default: if(file_exists('./log')){ } else{ mkdir('./log'); } $f=fopen('./log/error.txt','a'); fwrite($f,date('Y/m/d H:i:s').' ---- Unknown error type: ['.$errno.'] '.$errstr.'['.$errfile.' in line '.$errline.']'.PHP_EOL); fclose($f); break; } // Don't execute PHP internal error handler return true; } $old_error_handler = set_error_handler("myErrorHandler"); function content($ID,$DB,$usergroup,$startdate,$enddate){ date_default_timezone_set('Asia/Taipei'); //echo $usergroup."
"; if(isset($_POST['conttype'])&&$_POST['conttype']=='bord'){ if(isset($_SESSION['bord'])){ } else{ $_SESSION['bord']=1; } if(isset($_POST['dept'])||isset($_POST['group'])){ $channel=$_POST['company'].",".$_POST['channel']; $story=""; if($_POST['type']==2){ $channel=$channel.",".$_POST['file']; $story=$_POST['story']; } else{ $temp=preg_split("/,/",$_POST['dept']); $channel=$channel.",".$temp[0]; $story=$temp[1]; } echo ""; echo ""; } else{ if($_SESSION['usergroup']=='boss'){ $_SESSION['bosscode']=1; $_SESSION['total']=2; } else{ $_SESSION['bosscode']=2; $_SESSION['total']=1; } $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(isset($_SESSION['bosscode'])&&$_SESSION['bosscode']==1){ $sql='SELECT dept,deptname,usedb,channel,usergroup,userlogin.company,deptgroup FROM userlogin JOIN (SELECT company FROM userlogin WHERE id="'.$_SESSION['ID'].'") AS a ON userlogin.company=a.company WHERE (userlogin.usergroup<>"boss" OR userlogin.usergroup IS NULL OR userlogin.usergroup="bord") ORDER BY id ASC'; @$table=sqlquery($conn,$sql,'mysql'); $sql='SELECT groupcode,groupname FROM deptgroupmap JOIN (SELECT company FROM userlogin WHERE id="'.$_SESSION['ID'].'") AS a ON deptgroupmap.company=a.company WHERE tag="1"'; @$group=sqlquery($conn,$sql,'mysql'); } else{ $sql='SELECT dept,deptname,userlogin.usedb,channel,usergroup,userlogin.company,deptgroup FROM userlogin JOIN (SELECT company,usedb FROM userlogin WHERE id="'.$_SESSION['ID'].'") AS a ON userlogin.company=a.company AND userlogin.usedb=a.usedb WHERE (userlogin.usergroup<>"boss" OR userlogin.usergroup IS NULL OR userlogin.usergroup="bord") ORDER BY id ASC'; @$table=sqlquery($conn,$sql,'mysql'); } sqlclose($conn,'mysql'); if(isset($_SESSION['bosscode'])&&$_SESSION['bosscode']==2&&sizeof($table)==2){ $_SESSION['total']=1; echo ""; echo ""; } else{ echo ""; echo ""; echo "

"; /*if(isset($_SESSION['bosscode'])&&$_SESSION['bosscode']==1){ echo ""; }*/ echo "

門市 "; echo "
群組

"; /*if(isset($_SESSION['bosscode'])&&$_SESSION['bosscode']==1){ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); $sql='SELECT groupcode,groupname FROM deptgroupmap JOIN (SELECT company FROM userlogin WHERE id="'.$_SESSION['ID'].'") AS a ON a.company=deptgroupmap.company'; $groupname=sqlquery($conn,$sql,'mysql'); $sql='SELECT id,deptname,deptgroup FROM userlogin JOIN (SELECT company FROM userlogin WHERE id="'.$_SESSION['ID'].'") AS a ON a.company=userlogin.company WHERE function LIKE "bord" AND usedb IS NOT NULL AND usergroup="bord"'; $deptlist=sqlquery($conn,$sql,'mysql'); sqlclose($conn,'mysql'); if(sizeof($table)==0){ echo "體系中尚未使用廣告機。"; } else{ echo ""; } }*/ } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='setting'){ echo "

"; if($_SESSION['ID']=='admin'){ echo ""; } else if($usergroup=='boss'){ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); $sql="SELECT usedb,deptname FROM UserLogin WHERE company=(SELECT company FROM UserLogin WHERE id='".$_SESSION['ID']."') AND usergroup IS NULL AND function LIKE '%pos%'"; @$temp=sqlquery($conn,$sql,"mysql"); sqlclose($conn,"mysql"); echo ""; } else{ } echo "

資料庫
資料庫
時間區間	0)echo " value=".$startdate;echo ">～0)echo " value=".$enddate;echo ">

"; } else if(isset($_POST['conttype'])&&$_POST['conttype']=='sort'){ echo "建構中..."; } else if(isset($_POST['conttype'])&&$_POST['conttype']=='numberofday'){ if(empty($year)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $list=array();//點餐之餐點明細 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 //$conn=sqlconnect(".","SALES_201610.DB","","","","sqlite"); $conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ //因為sqlite沒有支援if的語法，只好先查詢折扣的數量，分為有折扣與沒有折扣兩種情況 $sql="SELECT ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY ZCOUNTER"; @$condition=sqlquery($conn,$sql,'sqlite'); if(sizeof($condition)>0){//有折扣//問題sql $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,D.DISCOUNT FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER JOIN (SELECT ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY ZCOUNTER) AS D ON D.ZCOUNTER=B.ZCOUNTER WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } else{//沒折扣 $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,E.NUMBER FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY BIZDATE,ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER AND C.BIZDATE=B.BIZDATE JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS E ON E.ZCOUNTER=B.ZCOUNTER AND E.BIZDATE=B.BIZDATE WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } @$list=sqlquery($conn,$sql,'sqlite'); if(sizeof($list)==0){ echo "查無資料。"; } else if($list[0]=="SQL語法錯誤"||$list[0]=="連線失敗"){ if($dubug==1){ echo $list[0]."(select)".$sql; } else{ echo $list[0]."(select)"; } } else{ $a=$list[0]['CREATEDATETIME']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); //$sql="SELECT DISTINCT ITEMCODE,ITEMNAME,SUM(AMT) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMCODE"; $sql="SELECT DISTINCT ITEMDEPTCODE,ITEMCODE,ITEMNAME,SUM(QTY) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMDEPTCODE,ITEMCODE ORDER BY ITEMDEPTCODE ASC,ITEMCODE ASC"; @$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存菜單；產品編號與產品名稱 $sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 @$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 $mtotal=0;//單月總銷售額(不含折扣) $mdiscount=0;//單月總折扣 $ptotal="";//單月總銷售金額折線圖變數數值(字串表示) //$mitemdept=array();//單月產品分類統計；$mitemdept[產品分類編號]=此分類的銷售數量 $menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['ITEMDEPTCODE']); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[產品類別編號][total]=該類別中的商品數量,$menu[產品類別編號][itemcode1~N]=類別中的商品編號,$menu[產品類別編號][itemname1~N]=類別中的商品名稱,$menu[產品類別編號][itemtotal1~N]=類別中的商品總數 if(isset($menu[intval($b['ITEMDEPTCODE'])]['total'])){ $menu[intval($b['ITEMDEPTCODE'])]['total']=$menu[intval($b['ITEMDEPTCODE'])]['total']+1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemtotal'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['TOTAL']; } else{ $menu[intval($b['ITEMDEPTCODE'])]['total']=1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode1']=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname1']=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemtotal1']=$b['TOTAL']; } } $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['ZCOUNTER'])<$nowcounter){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } } } } foreach($list as $c){//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 //$paper[intval($c['ZCOUNTER'])][intval($c['ITEMCODE'])]=$c['AMT']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]=$c['QTY']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['ztotal']=$c['ZTOTAL']; if(isset($c['DISCOUNT'])){ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=$c['DISCOUNT']; } else{ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=0; } $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['itemdeptcode']=intval($c['ITEMDEPTCODE']); //$paper[intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])]['qty']=$c['QTY']; /*if(isset($mitemdept[$c['ITEMDEPTCODE']])){ $mitemdept[$c['ITEMDEPTCODE']]=$mitemdept[$c['ITEMDEPTCODE']]+$c['QTY']; } else{ $mitemdept[$c['ITEMDEPTCODE']]=$c['QTY']; }*/ } echo "

"; echo "

營業日期

商品名稱

小計

折扣

總計

".$date."

".$zmoney."

".$ztotal."

".$zdiscount."

".($ztotal+$zdiscount)."

".$date."

小計

".$menu[$i]['itemtotal'.$code]."

".$mtotal."

".$mdiscount."

".($mtotal+$mdiscount)."

"; } $conn->close(); /*echo "

"; echo " "; echo "

";*/ } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='moneyofday'){ if(empty($year)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $list=array();//點餐之餐點明細 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 //$conn=sqlconnect(".","SALES_201610.DB","","","","sqlite"); $conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ //因為sqlite沒有支援if的語法，只好先查詢折扣的數量，分為有折扣與沒有折扣兩種情況 $sql="SELECT ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY ZCOUNTER"; @$condition=sqlquery($conn,$sql,'sqlite'); if(sizeof($condition)>0){//有折扣//問題sql $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,D.DISCOUNT,E.NUMBER FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY BIZDATE,ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER AND C.BIZDATE=B.BIZDATE JOIN (SELECT BIZDATE,ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY BIZDATE,ZCOUNTER) AS D ON D.ZCOUNTER=B.ZCOUNTER AND D.BIZDATE=B.BIZDATE JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS E ON E.ZCOUNTER=B.ZCOUNTER AND E.BIZDATE=B.BIZDATE WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } else{//沒折扣 $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,E.NUMBER FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY BIZDATE,ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER AND C.BIZDATE=B.BIZDATE JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS E ON E.ZCOUNTER=B.ZCOUNTER AND E.BIZDATE=B.BIZDATE WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } @$list=sqlquery($conn,$sql,'sqlite'); if(sizeof($list)==0){ echo "查無資料。"; } else if($list[0]=="SQL語法錯誤"||$list[0]=="連線失敗"){ if($dubug==1){ echo $list[0]."(select)".$sql; } else{ echo $list[0]."(select)"; } } else{ $a=$list[0]['CREATEDATETIME']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); $sql="SELECT DISTINCT ITEMDEPTCODE,ITEMCODE,ITEMNAME,SUM(AMT) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMDEPTCODE,ITEMCODE ORDER BY ITEMDEPTCODE ASC,ITEMCODE ASC"; @$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存菜單；產品編號與產品名稱 $sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 @$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 $mtotal=0;//單月總銷售額(不含折扣) $mdiscount=0;//單月總折扣 $mnumber=0;//單月帳單總數 $ptotal="";//單月總銷售金額折線圖變數數值(字串表示) //$mitemdept=array();//單月產品分類統計；$mitemdept[產品分類編號]=此分類的銷售數量 $menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['ITEMDEPTCODE']); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[產品類別編號][total]=該類別中的商品數量,$menu[產品類別編號][itemcode1~N]=類別中的商品編號,$menu[產品類別編號][itemname1~N]=類別中的商品名稱 if(isset($menu[intval($b['ITEMDEPTCODE'])]['total'])){ $menu[intval($b['ITEMDEPTCODE'])]['total']=$menu[intval($b['ITEMDEPTCODE'])]['total']+1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemtotal'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['TOTAL']; } else{ $menu[intval($b['ITEMDEPTCODE'])]['total']=1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode1']=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname1']=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemtotal1']=$b['TOTAL']; } } $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['ZCOUNTER'])<$nowcounter){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } } } } foreach($list as $c){//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]=$c['AMT']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['ztotal']=$c['ZTOTAL']; if(isset($c['DISCOUNT'])){ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=$c['DISCOUNT']; } else{ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=0; } $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['itemdeptcode']=intval($c['ITEMDEPTCODE']); $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])]['qty']=$c['QTY']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['number']=$c['NUMBER']; /*if(isset($mitemdept[$c['ITEMDEPTCODE']])){ $mitemdept[$c['ITEMDEPTCODE']]=$mitemdept[$c['ITEMDEPTCODE']]+$c['QTY']; } else{ $mitemdept[$c['ITEMDEPTCODE']]=$c['QTY']; }*/ } echo "

"; echo "

"; echo ""; echo ""; $index=1; $bgcolor1='#ffffff'; $bgcolor2='#f0f0f0'; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ if(isset($menu[$i]['total'])){ for($code=1;$code<=$menu[$i]['total'];$code++){ echo "".$menu[$i]['itemname'.$code].""; $index++; } } else{ } } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; for($date=1;$date<=$maxDay;$date++){ echo ""; $index=1; $temparray=array();//暫存陣列 $temparray[zcounter][total]=此班別的總營收；$temparray[zcounter][number]=此班別的帳單數 $ztotal=0;//單日總銷售額(不含折扣) $zdiscount=0;//單日總折扣 $znumber=0;//帳單數量 if(isset($zcounter[$date]['total'])){ echo ""; for($deptcode=1;$deptcode<=$menu[0]['value'];$deptcode++){ if(isset($menu[$deptcode]['total'])){ //$zmoney=0; for($code=1;$code<=$menu[$deptcode]['total'];$code++){ $zmoney=0; if($zcounter[$date]['total']==1){ if(isset($paper[$date][$zcounter[$date]['counter1']][$deptcode][$menu[$deptcode]['itemcode'.$code]])){ $zmoney=$paper[$date][$zcounter[$date]['counter1']][$deptcode][$menu[$deptcode]['itemcode'.$code]]; if(in_array($zcounter[$date]['counter1'],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter1']]['total']=$paper[$date][$zcounter[$date]['counter1']]['footer']['ztotal']; $temparray[$zcounter[$date]['counter1']]['number']=$paper[$date][$zcounter[$date]['counter1']]['footer']['number']; } //$ztotal=$paper[$date][$zcounter[$date]['counter1']]['footer']['ztotal']; $zdiscount=$paper[$date][$zcounter[$date]['counter1']]['footer']['discount']; //$znumber=$paper[$date][$zcounter[$date]['counter1']]['footer']['number']; } else{ } } else{ for($z=1;$z<=$zcounter[$date]['total'];$z++){ if(isset($paper[$date][$zcounter[$date]['counter'.$z]][$deptcode][$menu[$deptcode]['itemcode'.$code]])){ $zmoney=$zmoney+$paper[$date][$zcounter[$date]['counter'.$z]][$deptcode][$menu[$deptcode]['itemcode'.$code]]; if(in_array($zcounter[$date]['counter'.$z],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter'.$z]]['total']=$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['ztotal']; $temparray[$zcounter[$date]['counter'.$z]]['number']=$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['number']; } //$ztotal=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['ztotal']; $zdiscount=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['discount']; //$znumber=$znumber+$paper[$date][$zcounter[$date]['counter1']]['footer']['number']; } else{ } } } echo ""; $index++; } } else{ } } if(sizeof($temparray)>0){ $ztotal=0; $znumber=0; } else{ } foreach($temparray as $ZTOTAL){ $ztotal=$ztotal+$ZTOTAL['total']; $znumber=$znumber+$ZTOTAL['number']; } echo ""; echo ""; echo ""; echo ""; echo ""; $mtotal=$mtotal+$ztotal; $mdiscount=$mdiscount+$zdiscount; $mnumber=$mnumber+$znumber; if(strlen($ptotal)==0){ $ptotal=$ptotal.($ztotal+$zdiscount); } else{ $ptotal=$ptotal.",".($ztotal+$zdiscount); } } else{ echo ""; for($deptcode=1;$deptcode<=$menu[0]['value'];$deptcode++){ if(isset($menu[$deptcode]['total'])){ for($code=1;$code<=$menu[$deptcode]['total'];$code++){ echo ""; $index++; } } else{ } } echo ""; echo ""; echo ""; echo ""; echo ""; if(strlen($ptotal)==0){ $ptotal=$ptotal."0"; } else{ $ptotal=$ptotal.",0"; } } echo ""; } echo ""; $index=1; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ for($code=1;$code<=$menu[$i]['total'];$code++){ if(isset($menu[$i]['total'])){ echo ""; $index++; } else{ } } } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo "

營業日期

商品名稱

小計

折扣

總計

帳單數量

平均金額

".$date."

".$zmoney."

".$ztotal."

".$zdiscount."

".($ztotal+$zdiscount)."

".$znumber."

".round((($ztotal+$zdiscount)/$znumber),2)."

".$date."

小計

".$menu[$i]['itemtotal'.$code]."

".$mtotal."

".$mdiscount."

".($mtotal+$mdiscount)."

".$mnumber."

".round((($mtotal+$mdiscount)/$mnumber),2)."

"; } $conn->close(); } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='numberoftypeofday'){ if(empty($year)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $table=array();//商品類別統計 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $sql="SELECT A.*,B.NUMBER FROM (SELECT BIZDATE,SUM(QTY) AS QTY,SUM(AMT) AS AMT,ITEMDEPTCODE,ITEMDEPTNAME,ITEMGRPCODE,ITEMGRPNAME,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ITEMDEPTCODE,ITEMGRPCODE,ZCOUNTER) AS A JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS B ON B.ZCOUNTER=A.ZCOUNTER AND B.BIZDATE=A.BIZDATE"; @$table=sqlquery($conn,$sql,"sqlite"); if(sizeof($table)==0){ echo "查無資料。"; } else if($table[0]=="SQL語法錯誤"||$table[0]=="連線失敗"){ if($dubug==1){ echo $table[0]."(select)".$sql; } else{ echo $table[0]."(select)"; } } else{ $a=$table[0]['BIZDATE']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); $sql="SELECT DISTINCT ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMDEPTCODE"; @$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存類別菜單；產品編號與產品名稱 $sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 @$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 $mtotal=0;//單月總銷售額(不含折扣) $mdiscount=0;//單月總折扣 $mnumber=0;//單月帳單總數 $ptotal="";//單月總銷售金額折線圖變數數值(字串表示) //$mitemdept=array();//單月產品分類統計；$mitemdept[產品分類編號]=此分類的銷售數量 $menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['ITEMDEPTCODE']); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[產品編號][itemname]=產品名稱,$menu[產品編號][total]=產品總銷售 $menu[intval($b['ITEMDEPTCODE'])]['itemname']=$b['ITEMDEPTNAME']; $menu[intval($b['ITEMDEPTCODE'])]['total']=$b['TOTAL']; } $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['ZCOUNTER'])<$nowcounter){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } } } } foreach($table as $c){//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])]=$c['QTY']; //$paper[intval($c['ZCOUNTER'])]['ztotal']=$c['ZTOTAL']; if(isset($c['DISCOUNT'])){ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['discount']=$c['DISCOUNT']; } else{ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['discount']=0; } //$paper[intval($c['ZCOUNTER'])]['itemdeptcode']=intval($c['ITEMDEPTCODE']); $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['qty']=$c['QTY']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['number']=$c['NUMBER']; /*if(isset($mitemdept[$c['ITEMDEPTCODE']])){ $mitemdept[$c['ITEMDEPTCODE']]=$mitemdept[$c['ITEMDEPTCODE']]+$c['QTY']; } else{ $mitemdept[$c['ITEMDEPTCODE']]=$c['QTY']; }*/ } echo "

"; echo "

"; echo ""; echo ""; $index=1; $bgcolor1='#ffffff'; $bgcolor2='#f0f0f0'; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ if(isset($menu[$i]['itemname'])){ echo "".$menu[$i]['itemname'].""; $index++; } else{ } } } //echo ""; //echo ""; //echo ""; echo ""; //echo ""; echo ""; for($date=1;$date<=$maxDay;$date++){ echo ""; $index=1; $temparray=array();//暫存陣列 $temparray[zcounter][number]=此班別的帳單數 //$ztotal=0;//單日總銷售額(不含折扣) //$zdiscount=0;//單日總折扣 $znumber=0;//帳單數量 if(isset($zcounter[$date]['total'])){ echo ""; for($code=1;$code<=$menu[0]['value'];$code++){ if(isset($menu[$code]['itemname'])){ $zmoney=0; if($zcounter[$date]['total']==1){ if(isset($paper[$date][$zcounter[$date]['counter1']][$code])){ $zmoney=$paper[$date][$zcounter[$date]['counter1']][$code]; if(in_array($zcounter[$date]['counter1'],$temparray,true)){ } else{ //$temparray[$zcounter[$date]['counter1']]['total']=$paper[$date][$zcounter[$date]['counter1']]['footer']['ztotal']; $temparray[$zcounter[$date]['counter1']]['number']=$paper[$date][$zcounter[$date]['counter1']]['number']; } //$ztotal=$paper[$date][$zcounter[$date]['counter1']]['ztotal']; //$zdiscount=$paper[$date][$zcounter[$date]['counter1']]['discount']; $znumber=$paper[$date][$zcounter[$date]['counter1']]['number']; } else{ } } else{ for($z=1;$z<=$zcounter[$date]['total'];$z++){ if(isset($paper[$date][$zcounter[$date]['counter'.$z]][$code])){ $zmoney=$zmoney+$paper[$date][$zcounter[$date]['counter'.$z]][$code]; if(in_array($zcounter[$date]['counter'.$z],$temparray,true)){ } else{ //$temparray[$zcounter[$date]['counter'.$z]]['total']=$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['ztotal']; $temparray[$zcounter[$date]['counter'.$z]]['number']=$paper[$date][$zcounter[$date]['counter'.$z]]['number']; } //$ztotal=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['ztotal']; //$zdiscount=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['discount']; $znumber=$znumber+$paper[$date][$zcounter[$date]['counter1']]['number']; } else{ } } } echo ""; $index++; } else{ } } if(sizeof($temparray)>0){ //$ztotal=0; $znumber=0; } else{ } foreach($temparray as $ZTOTAL){ //$ztotal=$ztotal+$ZTOTAL['total']; $znumber=$znumber+$ZTOTAL['number']; } //echo ""; //echo ""; //echo ""; echo ""; //echo ""; //$mtotal=$mtotal+$ztotal; //$mdiscount=$mdiscount+$zdiscount; $mnumber=$mnumber+$znumber; /*if(strlen($ptotal)==0){ $ptotal=$ptotal.($ztotal+$zdiscount); } else{ $ptotal=$ptotal.",".($ztotal+$zdiscount); }*/ } else{ echo ""; for($code=1;$code<=$menu[0]['value'];$code++){ if(isset($menu[$code]['itemname'])){ echo ""; $index++; } else{ } } //echo ""; //echo ""; //echo ""; echo ""; //echo ""; /*if(strlen($ptotal)==0){ $ptotal=$ptotal."0"; } else{ $ptotal=$ptotal.",0"; }*/ } echo ""; } echo ""; $index=1; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ if(isset($menu[$i]['total'])){ echo ""; $index++; } else{ } } } //echo ""; //echo ""; //echo ""; echo ""; //echo ""; echo ""; echo "

營業日期

商品名稱

小計

折扣

總計

帳單數量

平均金額

".$date."

".$zmoney."

".$ztotal."

".$zdiscount."

".($ztotal+$zdiscount)."

".$znumber."

".round((($ztotal+$zdiscount)/$znumber),2)."

".$date."

小計

".$menu[$i]['total']."

".$mtotal."

".$mdiscount."

".($mtotal+$mdiscount)."

".$mnumber."

".round((($mtotal+$mdiscount)/$mnumber),2)."

"; } $conn->close(); } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='moneyoftypeofday'){ if(empty($year)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $list=array();//點餐之餐點明細 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 //$conn=sqlconnect(".","SALES_201610.DB","","","","sqlite"); $conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ //因為sqlite沒有支援if的語法，只好先查詢折扣的數量，分為有折扣與沒有折扣兩種情況 $sql="SELECT ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY ZCOUNTER"; @$condition=sqlquery($conn,$sql,'sqlite'); if(sizeof($condition)>0){//有折扣//問題sql $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,D.DISCOUNT,E.NUMBER FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY BIZDATE,ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER AND C.BIZDATE=B.BIZDATE JOIN (SELECT BIZDATE,ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY BIZDATE,ZCOUNTER) AS D ON D.ZCOUNTER=B.ZCOUNTER AND D.BIZDATE=B.BIZDATE JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS E ON E.ZCOUNTER=B.ZCOUNTER AND E.BIZDATE=B.BIZDATE WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } else{//沒折扣 $sql="SELECT DISTINCT B.BIZDATE,B.ITEMCODE,B.ITEMGRPCODE,B.ITEMDEPTCODE,B.QTY,B.UNITPRICE,B.AMT,B.ZCOUNTER,B.CREATEDATETIME,C.ZTOTAL,E.NUMBER FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) AS B JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(AMT) AS ZTOTAL FROM (SELECT DISTINCT BIZDATE,ITEMCODE, ITEMNAME,FUNCKEYCODE,FUNCKEYNAME,FUNCTIONCODE,ITEMGRPCODE,ITEMGRPNAME,ITEMDEPTCODE,ITEMDEPTNAME,SUM(QTY) AS QTY,UNITPRICE,SUM(AMT) AS AMT,ZCOUNTER,CREATEDATETIME FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ORDER BY ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER DESC) AS A GROUP BY BIZDATE,ITEMCODE,ZCOUNTER ORDER BY ZCOUNTER) GROUP BY BIZDATE,ZCOUNTER) AS C ON C.ZCOUNTER=B.ZCOUNTER AND C.BIZDATE=B.BIZDATE JOIN (SELECT DISTINCT BIZDATE,ZCOUNTER,SUM(NUMBER) AS NUMBER FROM (SELECT BIZDATE,CONSECNUMBER,SALESTTLAMT,ZCOUNTER,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM CST011 WHERE CAST(INVOICENUMBER AS INT)>0) AS A GROUP BY BIZDATE,ZCOUNTER) AS E ON E.ZCOUNTER=B.ZCOUNTER AND E.BIZDATE=B.BIZDATE WHERE QTY>0";//只抓當天銷售明細需在最後面加上過濾條件AND ZCOUNTER=(SELECT ZCOUNTER FROM CST012 WHERE ITEMORDERDATE='20161018' ORDER BY ZCOUNTER DESC LIMIT 1) } @$list=sqlquery($conn,$sql,'sqlite'); if(sizeof($list)==0){ echo "查無資料。"; } else if($list[0]=="SQL語法錯誤"||$list[0]=="連線失敗"){ if($dubug==1){ echo $list[0]."(select)".$sql; } else{ echo $list[0]."(select)"; } } else{ $a=$list[0]['BIZDATE']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); $sql="SELECT DISTINCT ITEMDEPTCODE,ITEMDEPTNAME,SUM(AMT) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMDEPTCODE"; @$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存菜單；產品編號與產品名稱 $sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 @$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 $mtotal=0;//單月總銷售額(不含折扣) $mdiscount=0;//單月總折扣 $mnumber=0;//單月帳單總數 $ptotal="";//單月總銷售金額折線圖變數數值(字串表示) //$mitemdept=array();//單月產品分類統計；$mitemdept[產品分類編號]=此分類的銷售數量 $menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['ITEMDEPTCODE']); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[產品編號][itemname]=產品名稱,$menu[產品編號][total]=產品總銷售 $menu[intval($b['ITEMDEPTCODE'])]['itemname']=$b['ITEMDEPTNAME']; $menu[intval($b['ITEMDEPTCODE'])]['total']=$b['TOTAL']; } $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['ZCOUNTER'])<$nowcounter){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } } } } foreach($list as $c){//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])]=$c['AMT']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['ztotal']=$c['ZTOTAL']; if(isset($c['DISCOUNT'])){ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['discount']=$c['DISCOUNT']; } else{ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['discount']=0; } $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['itemdeptcode']=intval($c['ITEMDEPTCODE']); $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['qty']=$c['QTY']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['number']=$c['NUMBER']; /*if(isset($mitemdept[$c['ITEMDEPTCODE']])){ $mitemdept[$c['ITEMDEPTCODE']]=$mitemdept[$c['ITEMDEPTCODE']]+$c['QTY']; } else{ $mitemdept[$c['ITEMDEPTCODE']]=$c['QTY']; }*/ } echo "

"; echo "

"; echo ""; echo ""; $index=1; $bgcolor1='#ffffff'; $bgcolor2='#f0f0f0'; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ if(isset($menu[$i]['itemname'])){ echo "".$menu[$i]['itemname'].""; $index++; } else{ } } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; for($date=1;$date<=$maxDay;$date++){ echo ""; $index=1; $temparray=array();//暫存陣列 $temparray[zcounter][total]=此班別的總營收；$temparray[zcounter][number]=此班別的帳單數 $ztotal=0;//單日總銷售額(不含折扣) $zdiscount=0;//單日總折扣 $znumber=0;//帳單數量 if(isset($zcounter[$date]['total'])){ echo ""; for($code=1;$code<=$menu[0]['value'];$code++){ if(isset($menu[$code]['itemname'])){ $zmoney=0; if($zcounter[$date]['total']==1){ if(isset($paper[$date][$zcounter[$date]['counter1']][$code])){ $zmoney=$paper[$date][$zcounter[$date]['counter1']][$code]; if(in_array($zcounter[$date]['counter1'],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter1']]['total']=$paper[$date][$zcounter[$date]['counter1']]['ztotal']; $temparray[$zcounter[$date]['counter1']]['number']=$paper[$date][$zcounter[$date]['counter1']]['number']; } //$ztotal=$paper[$date][$zcounter[$date]['counter1']]['ztotal']; $zdiscount=$paper[$date][$zcounter[$date]['counter1']]['discount']; //$znumber=$paper[$date][$zcounter[$date]['counter1']]['number']; } else{ } } else{ for($z=1;$z<=$zcounter[$date]['total'];$z++){ if(isset($paper[$date][$zcounter[$date]['counter'.$z]][$code])){ $zmoney=$zmoney+$paper[$date][$zcounter[$date]['counter'.$z]][$code]; if(in_array($zcounter[$date]['counter'.$z],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter'.$z]]['total']=$paper[$date][$zcounter[$date]['counter'.$z]]['ztotal']; $temparray[$zcounter[$date]['counter'.$z]]['number']=$paper[$date][$zcounter[$date]['counter'.$z]]['number']; } //$ztotal=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['ztotal']; $zdiscount=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['discount']; //$znumber=$znumber+$paper[$date][$zcounter[$date]['counter1']]['number']; } else{ } } } echo ""; $index++; } else{ } } if(sizeof($temparray)>0){ $ztotal=0; $znumber=0; } else{ } foreach($temparray as $ZTOTAL){ $ztotal=$ztotal+$ZTOTAL['total']; $znumber=$znumber+$ZTOTAL['number']; } echo ""; echo ""; echo ""; echo ""; echo ""; $mtotal=$mtotal+$ztotal; $mdiscount=$mdiscount+$zdiscount; $mnumber=$mnumber+$znumber; if(strlen($ptotal)==0){ $ptotal=$ptotal.($ztotal+$zdiscount); } else{ $ptotal=$ptotal.",".($ztotal+$zdiscount); } } else{ echo ""; for($code=1;$code<=$menu[0]['value'];$code++){ if(isset($menu[$code]['itemname'])){ echo ""; $index++; } else{ } } echo ""; echo ""; echo ""; echo ""; echo ""; if(strlen($ptotal)==0){ $ptotal=$ptotal."0"; } else{ $ptotal=$ptotal.",0"; } } echo ""; } echo ""; $index=1; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; } else{ if(isset($menu[$i]['total'])){ echo ""; $index++; } else{ } } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; echo "

營業日期

商品名稱

小計

折扣

總計

帳單數量

平均金額

".$date."

".$zmoney."

".$ztotal."

".$zdiscount."

".($ztotal+$zdiscount)."

".$znumber."

".round((($ztotal+$zdiscount)/$znumber),2)."

".$date."

小計

".$menu[$i]['total']."

".$mtotal."

".$mdiscount."

".($mtotal+$mdiscount)."

".$mnumber."

".round((($mtotal+$mdiscount)/$mnumber),2)."

"; } $conn->close(); } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='stototal'){//暫時沒有使用 if(empty($year)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $table1=array();//商品類別統計 $menu=array();//$menu[類別編號]=類別名稱 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();// $conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $sql="SELECT BIZDATE,ITEMCODE,ITEMNAME,SUM(QTY) AS QTY,SUM(AMT) AS AMT,ITEMDEPTCODE,ITEMDEPTNAME,ITEMGRPCODE,ITEMGRPNAME,ZCOUNTER,CREATEDATETIME FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ITEMCODE,ITEMDEPTCODE,ITEMGRPCODE,ZCOUNTER"; @$table1=sqlquery($conn,$sql,'sqlite'); if(sizeof($table1)==0){ echo "查無資料。"; } else if($table1[0]=="SQL語法錯誤"||$table1[0]=="連線失敗"){ if($dubug==1){ echo $table1[0]."(select)".$sql; } else{ echo $table1[0]."(select)"; } } else{ $a=$table1[0]['CREATEDATETIME']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); $sql="select CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.ITEMCODE,CST012.ITEMNAME FROM CST012 JOIN (SELECT DISTINCT ITEMDEPTCODE,ITEMCODE FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008')) as a ON a.ITEMDEPTCODE=CST012.ITEMDEPTCODE WHERE CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03') AND CST012.ITEMDEPTCODE<>'000008' GROUP BY CST012.ITEMDEPTCODE,CST012.ITEMCODE"; @$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存類別對照表；類別編號與類別名稱 $sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 @$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[類別編號][產品編號][dept]=類別名稱,$menu[類別編號][產品編號][item]=產品名稱 if(isset($menu[intval($b['ITEMDEPTCODE'])]['total'])){ $menu[intval($b['ITEMDEPTCODE'])]['total']=$menu[intval($b['ITEMDEPTCODE'])]['total']+1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['ITEMNAME']; } else{ $menu[intval($b['ITEMDEPTCODE'])]['total']=1; $menu[intval($b['ITEMDEPTCODE'])]['name']=$b['ITEMDEPTNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemcode1']=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname1']=$b['ITEMNAME']; } } $menu[0]['value']=max(array_keys($menu)); $zcounterList=array(); $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); $zcounterList[]=intval($b['ZCOUNTER']); } else{ if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); $zcounterList[]=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])<=$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); $zcounterList[]=intval($b['ZCOUNTER']); } } } } foreach($table1 as $c){//轉存成$paper[類別編號][產品編號][qty]=銷售數量,$paper[類別編號][產品編號][amt]=銷售金額 if(in_array(intval($c['ZCOUNTER']),$zcounterList)){ if(isset($paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['qty'])){ $paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['qty']=$paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['qty']+$c['QTY']; $paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['amt']=$paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['amt']+$c['AMT']; } else{ $paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['qty']=$c['QTY']; $paper[intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])]['amt']=$c['AMT']; } } else{ } } //print_r($menu); echo "

"; $pdept=""; $pnumber=""; $pmoney=""; for($dept=1;$dept<=$menu[0]['value'];$dept++){ if(isset($menu[$dept]['name'])){ $tempqty=0; $tempamt=0; if(isset($menu[$dept]['total'])&&$menu[$dept]['total']==1){ $tempqty=$paper[$dept][$menu[$dept]['itemcode1']]['qty']; $tempamt=$paper[$dept][$menu[$dept]['itemcode1']]['amt']; } else if(isset($menu[$dept]['total'])&&$menu[$dept]['total']>1){ for($i=1;$i<=$menu[$dept]['total'];$i++){ $tempqty=$tempqty+$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $tempamt=$tempamt+$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } } else{ } echo ""; echo ""; echo ""; if(strlen($pdept)==0){ $pdept=$pdept.$menu[$dept]['name']; } else{ $pdept=$pdept.",".$menu[$dept]['name']; } if(strlen($pnumber)==0){ $pnumber=$pnumber.$tempqty; } else{ $pnumber=$pnumber.",".$tempqty; } if(strlen($pmoney)==0){ $pmoney=$pmoney.$tempamt; } else{ $pmoney=$pmoney.",".$tempamt; } } else{ } } echo "

商品類別統計
商品類別	數量	金額
".$menu[$dept]['name']."	".$tempqty."	".$tempamt."

"; echo " "; echo "

"; $pnumber=""; //$pmoney=""; $pdept=""; $dept=1; if(isset($menu[$dept]['total'])&&$menu[$dept]['total']==1){ echo ""; $pdept=$pdept.$menu[$dept]['itemname1']; $pnumber=$pnumber.$paper[$dept][$menu[$dept]['itemcode1']]['qty']; } else if(isset($menu[$dept]['total'])&&$menu[$dept]['total']>1){ for($i=1;$i<=$menu[$dept]['total'];$i++){ echo ""; if(strlen($pdept)==0){ $pdept=$pdept.$menu[$dept]['itemname'.$i]; } else{ $pdept=$pdept.",".$menu[$dept]['itemname'.$i]; } if(strlen($pnumber)==0){ $pnumber=$pnumber.$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; } else{ $pnumber=$pnumber.",".$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; } } } else{ } echo "

湯頭數量佔比
名稱	數量
".$menu[$dept]['itemname1']."	".$paper[$dept][$menu[$dept]['itemcode1']]['qty']."
".$menu[$dept]['itemname'.$i]."	".$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']."

"; echo " "; echo "

"; $pnumber=""; $pmoney=""; $pdept=""; $dept=7; if(isset($menu[$dept]['total'])&&$menu[$dept]['total']==1){ echo ""; $pdept=$pdept.$menu[$dept]['itemname1']; $pmoney=$pmoney.$paper[$dept][$menu[$dept]['itemcode1']]['amt']; $pnumber=$pnumber.$paper[$dept][$menu[$dept]['itemcode1']]['qty']; } else if(isset($menu[$dept]['total'])&&$menu[1]['total']>1){ for($i=1;$i<=$menu[$dept]['total'];$i++){ echo ""; if(strlen($pdept)==0){ $pdept=$pdept.$menu[$dept]['itemname'.$i]; } else{ $pdept=$pdept.",".$menu[$dept]['itemname'.$i]; } if(strlen($pmoney)==0){ $pmoney=$pmoney.$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } else{ $pmoney=$pmoney.",".$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } if(strlen($pnumber)==0){ $pnumber=$pnumber.$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; } else{ $pnumber=$pnumber.",".$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; } } } else{ } echo "

套餐組佔比
套餐名稱	數量	金額
".$menu[$dept]['itemname1']."	".$paper[$dept][$menu[$dept]['itemcode1']]['qty']."	".$paper[$dept][$menu[$dept]['itemcode1']]['amt']."
".$menu[$dept]['itemname'.$i]."	".$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']."	".$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']."

"; echo " "; echo "

"; $pnumber=""; $pmoney=""; $pdept=""; $temp7andeither=array(); for($dept=1;$dept<=$menu[0]['value'];$dept++){ if($dept==7){ if(isset($menu[$dept]['total'])&&$menu[$dept]['total']==1){ if(isset($temp7andeither['group']['qty'])){ $temp7andeither['group']['qty']=$temp7andeither['group']['qty']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['group']['amt']=$temp7andeither['group']['amt']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } else{ $temp7andeither['group']['qty']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['group']['amt']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } } else if(isset($menu[$dept]['total'])&&$menu[$dept]['total']>1){ for($i=1;$i<=$menu[$dept]['total'];$i++){ if(isset($temp7andeither['group']['qty'])){ $temp7andeither['group']['qty']=$temp7andeither['group']['qty']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['group']['amt']=$temp7andeither['group']['amt']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } else{ $temp7andeither['group']['qty']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['group']['amt']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } } } else{ } } else if($dept!=7&&$dept!=1){ if(isset($menu[$dept]['total'])&&$menu[$dept]['total']==1){ if(isset($temp7andeither['single']['qty'])){ $temp7andeither['single']['qty']=$temp7andeither['single']['qty']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['single']['amt']=$temp7andeither['single']['amt']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } else{ $temp7andeither['single']['qty']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['single']['amt']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } } else if(isset($menu[$dept]['total'])&&$menu[$dept]['total']>1){ for($i=1;$i<=$menu[$dept]['total'];$i++){ if(isset($temp7andeither['single']['qty'])){ $temp7andeither['single']['qty']=$temp7andeither['single']['qty']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['single']['amt']=$temp7andeither['single']['amt']+$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } else{ $temp7andeither['single']['qty']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['qty']; $temp7andeither['single']['amt']=$paper[$dept][$menu[$dept]['itemcode'.$i]]['amt']; } } } else{ } } } echo ""; echo ""; echo ""; echo ""; echo ""; echo ""; $pdept="套餐,單點"; $pnumber=$temp7andeither['group']['qty'].",".$temp7andeither['single']['qty']; $pmoney=$temp7andeither['group']['amt'].",".$temp7andeither['single']['amt']; echo "

套餐與單點佔比
	數量	金額
套餐	".$temp7andeither['group']['qty']."	".$temp7andeither['group']['amt']."
單點	".$temp7andeither['single']['qty']."	".$temp7andeither['single']['amt']."

"; echo " "; echo "

"; } $conn->close(); } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='monnumber'){ if(strlen($enddate)==10){ $enddate=substr($enddate,0,7); } echo ""; if(empty($enddate)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $table=array();//儲存月營業匯總表 $story=array();//儲存所有門市 $csv=array();//匯出檔案內容 $searenddate=str_replace("-","",$enddate); $enddate=str_replace("-","/",$enddate); //$sql="SELECT company AS dept,bizdate AS bizdate,SUM(salesttlamt) AS amt,SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end)) AS number,(CASE WHEN (count(0) = 0) THEN 0 ELSE CAST((SUM(salesttlamt) / SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end))) AS SIGNED) END) AS aver FROM cst011 WHERE (consecnumber <> '000000') AND bizdate BETWEEN '".$searenddate."01' AND '".$searenddate.date("t",strtotime($searenddate.'01'))."' GROUP BY company"; $sql="SELECT company as dept,bizdate,sum(amt) as amt,sum(qty) as number FROM 3goodpreday WHERE bizdate BETWEEN '".$searenddate."01' AND '".$searenddate.date("t",strtotime($searenddate.'01'))."' GROUP BY company"; @$table=sqlquery($conn,$sql,'mysql'); $sql2="SELECT id,dept,deptname FROM userlogin WHERE company='3good' AND function LIKE '%ourpos%' ORDER BY id"; @$dblist=sqlquery($conn,$sql2,'mysql'); $sql="SELECT dept,deptname FROM UserLogin WHERE dept<>'0' AND (function='pos' OR function LIKE 'pos,%') AND company=(SELECT company FROM UserLogin WHERE id='".$ID."')"; @$story=sqlquery($conn,$sql,'mysql'); if(sizeof($table)==0){ if(isset($dblist[0]['id'])){ $tabledata=''; array_push($csv,array(substr($enddate,5,2)."月營業匯總表")); array_push($csv,array("編號","名稱","總營業額","帳單均額","總帳單數")); for($i=0;$i ".$dblist[$i]['dept']." ".$dblist[$i]['deptname']." ".$list[0]['amt']." ".round($list[0]['amt']/$list[0]['qty'])." ".$list[0]['qty']." "; array_push($csv,array($dblist[$i]['dept'],$dblist[$i]['deptname'],$list[0]['amt'],round($list[0]['amt']/$list[0]['qty']),$list[0]['qty'])); } else{ array_push($csv,array($temp['dept'],$temp['deptname'],0,0,0)); } } sqlclose($connlite,'sqlite'); } else{ } } if($tabledata==''){ echo "查無資料。"; } else{ echo "

"; echo ""; echo $tabledata; echo "

(".$enddate.")門市月營業匯總表
編號	名稱	總營業額	帳單均額	總帳單數

"; } } else{ echo "查無資料。"; } } else if($table[0]=="SQL語法錯誤"||$table[0]=="連線失敗"){ if($dubug==1){ echo $table[0]."(select)".$sql; } else{ echo $table[0]."(select)"; } } else{ $mondetail=array();//月營業匯總表 foreach($table as $temp){ $mondetail[$temp['dept']]['amt']=$temp['amt']; $mondetail[$temp['dept']]['number']=$temp['number']; } echo "

"; echo ""; array_push($csv,array(substr($enddate,5,2)."月營業匯總表")); array_push($csv,array("編號","名稱","總營業額","帳單均額","總帳單數")); foreach($story as $temp){ if(isset($mondetail[$temp['dept']]['amt'])&&isset($mondetail[$temp['dept']]['number'])&&$mondetail[$temp['dept']]['number']>0){ echo ""; echo ""; array_push($csv,array($temp['dept'],$temp['deptname'],$mondetail[$temp['dept']]['amt'],round($mondetail[$temp['dept']]['amt']/$mondetail[$temp['dept']]['number']),$mondetail[$temp['dept']]['number'])); } else{ /*echo "";*/ array_push($csv,array($temp['dept'],$temp['deptname'],0,0,0)); } echo ""; } for($i=0;$i "; array_push($csv,array($dblist[$i]['dept'],$dblist[$i]['deptname'],$list[0]['amt'],round($list[0]['amt']/$list[0]['qty']),$list[0]['qty'])); } else{ array_push($csv,array($temp['dept'],$temp['deptname'],0,0,0)); } } sqlclose($connlite,'sqlite'); } else{ } } echo "

(".$enddate.")門市月營業匯總表
編號	名稱	總營業額	帳單均額	總帳單數
".$temp['dept']."	".$temp['deptname']."	".$mondetail[$temp['dept']]['amt']."	".round($mondetail[$temp['dept']]['amt']/$mondetail[$temp['dept']]['number'])."	".$mondetail[$temp['dept']]['number']."	0	0	0
".$dblist[$i]['dept']."	".$dblist[$i]['deptname']."	".$list[0]['amt']."	".round($list[0]['amt']/$list[0]['qty'])."	".$list[0]['qty']."

"; echo "

"; } //用session的方式傳遞檔案匯出陣列 if(isset($_SESSION['array'])){ unset($_SESSION['array']); $_SESSION['array']=$csv; } else{ $_SESSION['array']=$csv; } } sqlclose($conn,"mysql"); } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='daynumber'){ if(strlen($enddate)==10){ $datetime1 = new DateTime($enddate); $datetime2 = new DateTime(date("Y-m-d", strtotime(date('Y-m-d').' - 1 day'))); $interval = $datetime1->diff($datetime2); if($interval->format("%R")=="-"){ $enddate=date("Y-m-d", strtotime(date('Y-m-d').' - 1 day')); } else{ $enddate=$enddate; } } else{ $enddate=date("Y-m-d", strtotime(date('Y-m-d').' - 1 day')); } $errorlist=array(); /*$datetime1 = new DateTime('2009-10-11'); $datetime2 = new DateTime('2009-10-13'); $interval = $datetime1->diff($datetime2); echo "";*/ /*if(strlen($enddate)<10){ $enddate=date("Y-m-").(intval(date("d"))-1); }*/ echo " "; if(empty($enddate)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $table=array();//儲存日營業匯總表 $story=array();//儲存所有門市 $csv=array();//匯出檔案內容 $searenddate=str_replace("-","",$enddate); $enddate=str_replace("-","/",$enddate); if($DB=='all'){ //$sql="SELECT company AS dept,bizdate,SUM(salesttlamt) AS amt,SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end)) AS qty,(CASE WHEN (count(0) = 0) THEN 0 ELSE CAST((SUM(salesttlamt) / SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end))) AS SIGNED) END) AS aver FROM cst011 WHERE (consecnumber <> '000000') AND bizdate='".$searenddate."' GROUP BY company,bizdate"; //$sql="SELECT company as dept,bizdate,amt,qty,aver FROM 3goodpreday WHERE bizdate='".$searenddate."'"; $sqlca="SELECT id,dept,deptname FROM userlogin WHERE company='3good' AND (function='pos' OR function LIKE '%,pos' OR function LIKE 'pos%' OR function LIKE '%,pos,%') ORDER BY id"; $casiolist=sqlquery($conn,$sqlca,'mysql'); $sql2="SELECT id,dept,deptname FROM userlogin WHERE company='3good' AND function LIKE '%ourpos%' AND function NOT LIKE '%stopourpos%' ORDER BY id"; $dblist=sqlquery($conn,$sql2,'mysql'); include_once './tool/dbTool.php'; for($i=0;$i 0) THEN 1 WHEN (SALESTTLAMT = 0) THEN 0 ELSE -1 END)) AS qty,(CASE WHEN (COUNT(0) = 0) THEN 0 ELSE ROUND(CAST(SUM(SALESTTLAMT) AS DOUBLE) / SUM((CASE WHEN (SALESTTLAMT > 0) THEN 1 WHEN (SALESTTLAMT = 0) THEN 0 ELSE -1 END))) END) AS aver FROM CST011 WHERE (CONSECNUMBER <> '000000') AND BIZDATE='".$searenddate."' AND REGTYPE='1' AND REGFUNC='1' GROUP BY bizdate"; if(!isset($table)||sizeof($table)==0){ $table=sqlquery($connlite,$sqlite,'sqlite'); if(isset($table[0]['dept'])){ } else{ //echo $casiolist[$i]['dept'].'
'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$casiolist[$i]['dept'],'name'=>$casiolist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } } else{ $temptable=sqlquery($connlite,$sqlite,'sqlite'); if(isset($temptable[0]['dept'])){ array_push($table,['dept'=>$temptable[0]['dept'],'amt'=>$temptable[0]['amt'],'qty'=>$temptable[0]['qty'],'aver'=>$temptable[0]['aver']]); } else{ if($info['basic']['LogTime']!=''&&(intval(preg_replace('/\//','',substr($info['basic']['LogTime'],0,10)))-intval($searenddate))<=0){ array_push($errorlist,['code'=>$casiolist[$i]['dept'],'name'=>$casiolist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } } } else{ //$table=array(); } sqlclose($connlite,'sqlite'); } else{ if(file_exists('../db/'.$casiolist[$i]['dept'])){ //echo $casiolist[$i]['dept'].'
'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$casiolist[$i]['dept'],'name'=>$casiolist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } else{ } $sqlite=""; //$table=array(); } } $sqlite=""; if(!isset($table)||sizeof($table)==0){ $table=array(); } else{ } } else{ if(file_exists('../db/'.$DB.'/SALES_'.substr($searenddate,0,6).'.db')){ include_once './tool/dbTool.php'; $connlite=sqlconnect('../db/'.$DB,'SALES_'.substr($searenddate,0,6).'.db','','','','sqlite'); $sqlite='SELECT name FROM sqlite_master WHERE type="table" AND name="CST011"'; $res=sqlquery($connlite,$sqlite,'sqlite'); if(isset($res[0]['name'])){ $sqlite="SELECT '".$DB."' AS dept,BIZDATE,SUM(SALESTTLAMT) AS amt,SUM((CASE WHEN (SALESTTLAMT > 0) THEN 1 WHEN (SALESTTLAMT = 0) THEN 0 ELSE -1 END)) AS qty,(CASE WHEN (COUNT(0) = 0) THEN 0 ELSE ROUND(CAST(SUM(SALESTTLAMT) AS DOUBLE) / SUM((CASE WHEN (SALESTTLAMT > 0) THEN 1 WHEN (SALESTTLAMT = 0) THEN 0 ELSE -1 END))) END) AS aver FROM CST011 WHERE (CONSECNUMBER <> '000000') AND BIZDATE='".$searenddate."' AND REGTYPE='1' AND REGFUNC='1' GROUP BY bizdate"; $table=sqlquery($connlite,$sqlite,'sqlite'); } else{ $table=array(); } sqlclose($connlite,'sqlite'); } else{ $sqlite=""; $table=array(); } //$sql="SELECT company AS dept,bizdate,SUM(salesttlamt) AS amt,SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end)) AS qty,(CASE WHEN (count(0) = 0) THEN 0 ELSE CAST((SUM(salesttlamt) / SUM((CASE WHEN (salesttlamt > 0) THEN 1 WHEN (salesttlamt = 0) THEN 0 ELSE -(1) end))) AS SIGNED) END) AS aver FROM cst011 WHERE (consecnumber <> '000000') AND bizdate='".$searenddate."' AND company='".$DB."' GROUP BY company,bizdate"; //$sql="SELECT company as dept,bizdate,amt,qty,aver FROM 3goodpreday WHERE bizdate='".$searenddate."' AND company='".$DB."'"; $dbNO=$DB; $sql2="SELECT id,dept,deptname FROM userlogin WHERE company='3good' AND function LIKE '%ourpos%' AND id='".$DB."'"; $dblist=sqlquery($conn,$sql2,'mysql'); if(isset($dblist[0]['deptname'])){ $GLOBALS['broke']=$dblist[0]['deptname']; } else{ $GLOBALS['broke']=$DB; } } echo ''; if(isset($sqlite)){ //$table=sqlquery($conn,$sql,'mysql'); } else{ $table=sqlquery($conn,$sql,'mysql'); } if($DB=='all'){ $sql="SELECT dept,deptname FROM UserLogin WHERE dept<>'0' AND (function='pos' OR function LIKE 'pos,%') AND company=(SELECT company FROM UserLogin WHERE id='".$ID."')"; } else{ $sql="SELECT dept,deptname FROM UserLogin WHERE dept<>'0' AND (function='pos' OR function LIKE 'pos,%') AND dept='".$DB."' AND company=(SELECT company FROM UserLogin WHERE id='".$ID."')"; } $story=sqlquery($conn,$sql,'mysql'); if(sizeof($table)==0){ if(isset($dblist[0]['id'])){ $tabledata=''; array_push($csv,array(date("Y/m/").(intval(date("d"))-1)."營業匯總表")); array_push($csv,array("編號","名稱","總營業額","帳單均額","總帳單數")); for($i=0;$i'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } else{ $sql='SELECT SUM(SALESTTLAMT) AS amt,COUNT(*) AS qty FROM CST011 WHERE NBCHKNUMBER IS NULL AND BIZDATE="'.$searenddate.'"'; $list=sqlquery($connlite,$sql,'sqlite'); if(isset($list[0]['amt'])){ $tabledata=$tabledata." ".$dblist[$i]['dept']." ".$dblist[$i]['deptname']." ".$list[0]['amt']." ".round($list[0]['amt']/$list[0]['qty'])." ".$list[0]['qty']." "; array_push($csv,array($dblist[$i]['dept'],$dblist[$i]['deptname'],$list[0]['amt'],round($list[0]['amt']/$list[0]['qty']),$list[0]['qty'])); } else{ array_push($csv,array($dblist[$i]['dept'],$dblist[$i]['deptname'],0,0,0)); //echo $dblist[$i]['id'].'
'; if($info['basic']['LogTime']!=''&&(intval(preg_replace('/\//','',substr($info['basic']['LogTime'],0,10)))-intval($searenddate))<=0){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } } sqlclose($connlite,'sqlite'); } else{ if(file_exists('../outposandorder/ourpos/3good/'.$dblist[$i]['id'])){ //echo $dblist[$i]['id'].'
'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } else{ } } } if($tabledata==''){ echo "查無資料。"; } else{ echo "

"; echo ""; echo $tabledata; echo "

(".$enddate.")門市日營業匯總表
編號	名稱	總營業額	帳單均額	總帳單數

"; } } else{ echo "查無資料。"; } } else if($table[0]=="SQL語法錯誤"||$table[0]=="連線失敗"){ if($dubug==1){ echo $table[0]."(select)".$sql; } else{ echo $table[0]."(select)"; } } else{ $daydetail=array();//日營業匯總表 foreach($table as $temp){ /*$daydetail[$temp['dept']]['amt']=$temp['amt']; $daydetail[$temp['dept']]['number']=$temp['number'];*/ $daydetail[$temp['dept']]['amt']=$temp['amt']; $daydetail[$temp['dept']]['aver']=$temp['aver']; $daydetail[$temp['dept']]['qty']=$temp['qty']; } echo "

"; echo ""; array_push($csv,array(date("Y/m/").(intval(date("d"))-1)."營業匯總表")); array_push($csv,array("編號","名稱","總營業額","帳單均額","總帳單數")); foreach($story as $temp){ if(isset($daydetail[$temp['dept']]['amt'])&&isset($daydetail[$temp['dept']]['qty'])&&$daydetail[$temp['dept']]['qty']>0){ echo ""; echo ""; array_push($csv,array($temp['dept'],$temp['deptname'],$daydetail[$temp['dept']]['amt'],$daydetail[$temp['dept']]['aver'],$daydetail[$temp['dept']]['qty'])); } else{ /*echo "";*/ array_push($csv,array($temp['dept'],$temp['deptname'],0,0,0)); } echo ""; } for($i=0;$i'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } else{ $sql='SELECT SUM(SALESTTLAMT) AS amt,COUNT(*) AS qty FROM CST011 WHERE NBCHKNUMBER IS NULL AND BIZDATE="'.$searenddate.'"'; $list=sqlquery($connlite,$sql,'sqlite'); if(isset($list[0]['amt'])){ echo ""; array_push($csv,array($dblist[$i]['dept'],$dblist[$i]['deptname'],$list[0]['amt'],round($list[0]['amt']/$list[0]['qty']),$list[0]['qty'])); } else{ array_push($csv,array($temp['dept'],$temp['deptname'],0,0,0)); //echo $dblist[$i]['id'].'
'; if($info['basic']['LogTime']!=''&&(intval(preg_replace('/\//','',substr($info['basic']['LogTime'],0,10)))-intval($searenddate))<=0){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } } } sqlclose($connlite,'sqlite'); } else{ if(file_exists('../outposandorder/ourpos/3good/'.$dblist[$i]['id'])){ //echo $dblist[$i]['id'].'
'; if($info['basic']['LogTime']!=''){ array_push($errorlist,['code'=>$dblist[$i]['dept'],'name'=>$dblist[$i]['deptname'],'time'=>$info['basic']['LogTime']]); } else{ } } else{ } } } echo "

(".$enddate.")門市日營業匯總表
編號	名稱	總營業額	帳單均額	總帳單數
".$temp['dept']."	".$temp['deptname']."	".$daydetail[$temp['dept']]['amt']."	".$daydetail[$temp['dept']]['aver']."	".$daydetail[$temp['dept']]['qty']."	0	0	0
".$dblist[$i]['dept']."	".$dblist[$i]['deptname']."	".$list[0]['amt']."	".round($list[0]['amt']/$list[0]['qty'])."	".$list[0]['qty']."

"; echo '

'; echo ''; echo ''; echo ''; foreach($errorlist as $d){ echo ''; } echo '

異常門市清單
編號	名稱	最後上線時間
'.$d['code'].'	'.$d['name'].'	'.$d['time'].'

'; echo "

"; if($_SESSION['ID']=='admin'){ echo ""; } /*else if($usergroup=='boss'){ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); $sql="SELECT usedb,deptname FROM UserLogin WHERE company=(SELECT company FROM UserLogin WHERE id='".$_SESSION['ID']."') AND usergroup IS NULL"; $temp=sqlquery($conn,$sql,"mysql"); sqlclose($conn,"mysql"); echo ""; } else{ }*/ echo "

資料庫
資料庫
時間區間	0)echo " value=".$startdate;echo ">～0)echo " value=".$enddate;echo ">

"; if(empty($startdate)&&empty($enddate)){ echo "請先至基本選項設定想瀏覽的時間。"; } else{ $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $table=array();//暫存店家商品明細 $tabarray=array();//將上方陣列整理成>>$tabarray[company][itemdeptcode][itemcode][qty]=商品銷售量；$tabarray[company][itemdeptcode][itemcode][amt]=商品銷售金額 $menu=array();//儲存所有門市所使用之商品 $menuarray=array();//將上方陣列整理成>>$menuarray['deptnumber']=分類數量；$menuarray[itemdeptcode]['totalnumber']=分類中數量；$menuarray[itemdeptcode]['money']=分類銷售總額；$menuarray[itemdeptcode][itemcode0~N]['name']=商品名(編號0為分類名)；$menuarray[itemdeptcode][itemcode1~N]['code']=商品編號；$menuarray[itemdeptcode][itemcode1~N]['number']=商品銷售量 $story=array();//儲存所有門市 $stoarray=array();//將上方陣列整理成>>$stoarray[company]=companyname; $discount=array();//暫存所有店家折扣資訊 $cond=array();//將上方陣列整理成>>$cond[company]=門市折扣金額 $totalcond=0;//總折扣 $totalsingle=0;//單點總額 $totalmoney=0;//總營收 $totalnumber=0;//總帳單數 $csv=array();//匯出檔案內容 echo "

"; //表格B>>各門市之商品銷售量與金額；表格C>>各班別之營業額；表格E>>各班別之帳單數 $sql="SELECT DISTINCT A.company,A.itemcode,A.itemname,A.itemgrpcode,A.itemdeptcode,A.itemdeptname,A.qty,A.amt,B.number FROM ( SELECT DISTINCT company,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,SUM(qty) AS qty,SUM(amt) AS amt FROM alldetails WHERE bizdate BETWEEN '".$startdate."' AND '".$enddate."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' GROUP BY company,itemcode ORDER BY company,itemcode ) AS A JOIN ( SELECT company,SUM(CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END) AS number FROM alldetails WHERE bizdate BETWEEN '".$startdate."' AND '".$enddate."' AND CAST(invoicenumber AS INT)>0 GROUP BY company ) AS B ON B.company=A.company WHERE qty>0"; @$table=sqlquery($conn,$sql,"mysql"); foreach($table as $temp){ if(!isset($tabarray[strtoupper($temp['company'])]['number'])){ $tabarray[strtoupper($temp['company'])]['number']=$temp['number']; } $tabarray[strtoupper($temp['company'])][intval($temp['itemdeptcode'])][intval($temp['itemcode'])]['qty']=$temp['qty']; $tabarray[strtoupper($temp['company'])][intval($temp['itemdeptcode'])][intval($temp['itemcode'])]['amt']=$temp['amt']; } $sql="SELECT dept,deptname FROM UserLogin WHERE dept<>'0' AND (function='pos' OR function LIKE 'pos,%') AND company=(SELECT company FROM UserLogin WHERE id='".$ID."')"; @$story=sqlquery($conn,$sql,'mysql'); $condition=""; foreach($story as $temp){ $stoarray[$temp['dept']]=$temp['deptname']; if(strlen($condition)==0){ $condition='"'.$temp['dept'].'"'; } else{ $condition=$condition.",".'"'.$temp['dept'].'"'; } } $sql="SELECT DISTINCT itemcode,itemname,itemdeptcode,itemdeptname FROM alldetails WHERE dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' AND company IN (".$condition.") AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' ORDER BY itemdeptcode,itemcode"; @$menu=sqlquery($conn,$sql,"mysql"); $menuarray['deptnumber']=intval($menu[sizeof($menu)-1]['itemdeptcode']); foreach($menu as $temp){ if(isset($menuarray[intval($temp['itemdeptcode'])])){ $menuarray[intval($temp['itemdeptcode'])]['totalnumber']=$menuarray[intval($temp['itemdeptcode'])]['totalnumber']+1; $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['name']=$temp['itemname']; $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['code']=intval($temp['itemcode']); $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['number']=0; } else{ $menuarray[intval($temp['itemdeptcode'])]['money']=0; $menuarray[intval($temp['itemdeptcode'])]['totalnumber']=1; $menuarray[intval($temp['itemdeptcode'])]['itemcode0']['name']=$temp['itemdeptname']; $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['name']=$temp['itemname']; $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['code']=intval($temp['itemcode']); $menuarray[intval($temp['itemdeptcode'])]['itemcode'.$menuarray[intval($temp['itemdeptcode'])]['totalnumber']]['number']=0; } } $sql="SELECT company,SUM(amt) AS discount FROM alldetails WHERE dtlmode='1' AND (dtltype='2' OR dtltype='3') AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY company"; @$discount=sqlquery($conn,$sql,'mysql'); foreach($discount as $temp){ $cond[strtoupper($temp['company'])]=$temp['discount']; } if(sizeof($table)==0){ echo "查無資料。"; } else if($table[0]=="SQL語法錯誤"||$table[0]=="連線失敗"){ if($dubug==1){ echo $table[0]."(select)".$sql; } else{ echo $table[0]."(select)"; } } else{ echo "

"; echo "

"; echo ""; array_push($csv,array('門市')); array_push($csv,array('商品')); for($dept=1;$dept<=$menuarray['deptnumber'];$dept++){ if(isset($menuarray[$dept])){ for($i=0;$i<=$menuarray[$dept]['totalnumber'];$i++){ echo ""; array_push($csv,array($menuarray[$dept]['itemcode'.$i]['name'])); } } else{ } } echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv,array('折扣')); array_push($csv,array('單點')); array_push($csv,array('總營收')); array_push($csv,array('帳單數量')); array_push($csv,array('平均金額')); echo ""; foreach($story as $s){ $ss=3; $startdept=2; $single=0;//各門市單點金額 $combine=0;//各門市套餐金額 echo ""; echo ""; array_push($csv[0],$s['deptname']); for($dept=1;$dept<=$menuarray['deptnumber'];$dept++){ if(isset($menuarray[$dept])){ $HTMLstring=""; $deptmoney=0;//分類項目總銷售額 for($i=1;$i<=$menuarray[$dept]['totalnumber'];$i++){ if(isset($tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']])){ $HTMLstring=$HTMLstring.""; $deptmoney=$deptmoney+$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['amt']; array_push($csv[$ss],$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['qty']); if($menuarray[$dept]['itemcode0']['name']=="套餐"){ $combine=$combine+$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['amt']; } else{ $single=$single+$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['amt']; } $menuarray[$dept]['money']=$menuarray[$dept]['money']+$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['amt'];//分類銷售總額 $menuarray[$dept]['itemcode'.$i]['number']=$menuarray[$dept]['itemcode'.$i]['number']+$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['qty'];//商品銷售總數 } else{ $HTMLstring=$HTMLstring.""; array_push($csv[$ss],"0"); } $ss++; } $HTMLstring="".$HTMLstring; array_push($csv[$startdept],$deptmoney); echo $HTMLstring; $startdept=$ss; $ss++; } else{ } } if(isset($tabarray[strtoupper($s['dept'])]['number'])){ if(isset($cond[strtoupper($s['dept'])])){ echo ""; array_push($csv[$ss-1],$cond[strtoupper($s['dept'])]); $totalcond=$totalcond+$cond[strtoupper($s['dept'])]; } else{ echo ""; array_push($csv[$ss-1],"0"); } echo ""; array_push($csv[$ss],$single); $totalsingle=$totalsingle+$single; if(isset($cond[strtoupper($s['dept'])])){ $perdatmoney=($single+$combine+$cond[strtoupper($s['dept'])]); echo ""; array_push($csv[$ss+1],($single+$combine+$cond[strtoupper($s['dept'])])); $totalmoney=$totalmoney+($single+$combine+$cond[strtoupper($s['dept'])]); } else{ $perdatmoney=($single+$combine); echo ""; array_push($csv[$ss+1],($single+$combine)); $totalmoney=$totalmoney+($single+$combine); } echo ""; $totalnumber=$totalnumber+$tabarray[strtoupper($s['dept'])]['number']; echo ""; array_push($csv[$ss+2],$tabarray[strtoupper($s['dept'])]['number']); array_push($csv[$ss+3],round(($perdatmoney/$tabarray[strtoupper($s['dept'])]['number']),2)); } else{ echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv[$ss-1],"0"); array_push($csv[$ss],"0"); array_push($csv[$ss+1],"0"); array_push($csv[$ss+2],"0"); array_push($csv[$ss+3],"0"); } echo ""; } echo ""; echo ""; array_push($csv[0],'小計'); $ss=2; for($dept=1;$dept<=$menuarray['deptnumber'];$dept++){ if(isset($menuarray[$dept])){ for($i=0;$i<=$menuarray[$dept]['totalnumber'];$i++){ if($i==0){ echo ""; array_push($csv[$ss],$menuarray[$dept]['money']); } else{ echo ""; array_push($csv[$ss],$menuarray[$dept]['itemcode'.$i]['number']); } $ss++; } } else{ } } echo ""; array_push($csv[$ss],$totalcond); echo ""; array_push($csv[$ss+1],$totalsingle); echo ""; array_push($csv[$ss+2],$totalmoney); echo ""; array_push($csv[$ss+3],""); echo ""; array_push($csv[$ss+4],""); echo ""; echo ""; echo ""; array_push($csv[0],'合計'); $ss=2; $deptnumber=0;//各分類總銷售量 $deptcount=1; for($dept=1;$dept<=$menuarray['deptnumber'];$dept++){ if(isset($menuarray[$dept])){ for($i=0;$i<=$menuarray[$dept]['totalnumber'];$i++){ if($i==0){ echo ""; array_push($csv[$ss],$menuarray[$dept]['money']); $ss++; $deptcount++; } else{ $deptnumber=$deptnumber+$menuarray[$dept]['itemcode'.$i]['number']; } } echo ""; array_push($csv[$ss],$deptnumber); $ss++; for($cells=1;$cells<$menuarray[$dept]['totalnumber'];$cells++){ echo ""; array_push($csv[$ss],""); $ss++; } } else{ } } echo ""; array_push($csv[$ss],$totalcond); echo ""; array_push($csv[$ss+1],$totalsingle); echo ""; array_push($csv[$ss+2],$totalmoney); echo ""; array_push($csv[$ss+3],$totalnumber); echo ""; array_push($csv[$ss+4],round(($totalmoney/$totalnumber),2)); echo ""; echo "

門市

商品

"; } else{ echo ">"; } echo $menuarray[$dept]['itemcode'.$i]['name']."

折扣

單點

總營收

帳單數量

平均金額

".$s['deptname']."

".$tabarray[strtoupper($s['dept'])][$dept][$menuarray[$dept]['itemcode'.$i]['code']]['qty']."

".$deptmoney."

".$cond[strtoupper($s['dept'])]."

".$single."

".($single+$combine+$cond[strtoupper($s['dept'])])."

".($single+$combine)."

".$tabarray[strtoupper($s['dept'])]['number']."

".round(($perdatmoney/$tabarray[strtoupper($s['dept'])]['number']),2)."

小計

".$menuarray[$dept]['money']."

".$menuarray[$dept]['itemcode'.$i]['number']."

".$totalcond."

".$totalsingle."

".$totalmoney."

合計

".$menuarray[$dept]['money']."

".$deptnumber."

".$totalcond."

".$totalsingle."

".$totalmoney."

".$totalnumber."

".round(($totalmoney/$totalnumber),2)."

"; } echo "

"; if(isset($_SESSION['array'])){ unset($_SESSION['array']); $_SESSION['array']=$csv; } else{ $_SESSION['array']=$csv; } } sqlclose($conn,"mysql"); } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='newnumberofday'){ if(strlen($enddate)<10){ $enddate=$enddate."-".date("t",mktime('0','0','0','1',substr($enddate,5,2),substr($enddate,0,4))); } echo "

資料庫
資料庫
時間區間	0)echo " value=".$startdate;echo ">～0)echo " value=".$enddate;echo ">

"; if(empty($startdate)&&empty($enddate)){ echo "請先設定想瀏覽的時間。"; } else{ //$conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $sql='SELECT function FROM UserLogin WHERE dept="'.$DB.'"'; @$function=sqlquery($conn,$sql,'mysql'); if(isset($function[0]['function'])&&preg_match('/ourpos/',$function[0]['function'])){ $connm=sqlconnect('../outposandorder/ourpos/3good/'.$DB,'menu.db','','','','sqlite'); if(!$connm){ echo '菜單資料遺失。'; } else{ $sql='SELECT fronttype,reartype,inumber FROM itemsdata ORDER BY replace(replace(substr(quote(zeroblob((10 + 1) / 2)), 3, (10 - length(fronttype))), "\'", ""), "0", "0")||fronttype,replace(replace(substr(quote(zeroblob((10 + 1) / 2)), 3, (10 - length(rearsq))), "\'", ""), "0", "0")||rearsq,replace(replace(substr(quote(zeroblob((10 + 1) / 2)), 3, (10 - length(inumber))), "\'", ""), "0", "0")||inumber'; @$menuarray=sqlquery($connm,$sql,'sqlite'); if(sizeof($menuarray)==0){ echo '菜單資料尚未新增。'; } else{ $itemname=parse_ini_file('../outposandorder/ourpos/3good/'.$DB.'/3good-menu.ini',true); $rearname=parse_ini_file('../outposandorder/ourpos/3good/'.$DB.'/3good-front.ini',true); $tastename=parse_ini_file('../outposandorder/ourpos/3good/'.$DB.'/3good-taste.ini',true); $init=parse_ini_file('../outposandorder/ourpos/3good/'.$DB.'/initsetting.ini',true); $saletype=parse_ini_file('../outposandorder/demopos/syspram/buttons-1.ini',true); $front=array();//產品編號反查目前類別編號 $menu=array();//暫存menu產品陣列 $taste=array();//暫存加料與備註陣列 $list=array();//暫存帳單資料 $orderitemlist=''; $orderitemarray=array(); foreach($menuarray as $i){ if(isset($itemname[intval($i['inumber'])])){ $front[intval($i['inumber'])]=intval($i['fronttype']); $menu[intval($i['fronttype'])]['name1']=$rearname[intval($i['fronttype'])]['name1']; $menu[intval($i['fronttype'])]['name2']=$rearname[intval($i['fronttype'])]['name2']; $menu[intval($i['fronttype'])][intval($i['inumber'])]['name1']=$itemname[intval($i['inumber'])]['name1']; $menu[intval($i['fronttype'])][intval($i['inumber'])]['name2']=$itemname[intval($i['inumber'])]['name2']; $menu[intval($i['fronttype'])][intval($i['inumber'])]['state']=$itemname[intval($i['inumber'])]['state']; } else{ } if($i['reartype']=='2'){ if($orderitemlist==''){ $orderitemlist=$i['inumber']; } else{ $orderitemlist=$orderitemlist.','.$i['inumber']; } } else{ } } foreach($tastename as $index => $t){ $menu['td'.$index]['name1']=$t['name1']; $menu['td'.$index]['name2']=$t['name2']; } $diff=date_diff(date_create(substr($startdate,0,8).'01'),date_create(substr($enddate,0,8).'01')); for($mon=0;$mon<=intval($diff->format("%m"));$mon++){ $connlist=sqlconnect('../outposandorder/ourpos/3good/'.$DB,'SALES_'.date('Ym',strtotime($startdate.' +'.$mon.' month')).'.db','','','','sqlite'); if(!$connlist){ //echo '資料庫尚未上傳資料。'; } else{ $sql='SELECT BIZDATE,DTLMODE,DTLTYPE,DTLFUNC,ITEMCODE,ITEMDEPTCODE,SUM(QTY) AS QTY,UNITPRICELINK,UNITPRICE,SUM(AMT) AS AMT,REMARKS FROM CST012 WHERE ((DTLMODE<>"9" AND DTLTYPE<>"9" AND DTLFUNC<>"99") OR (DTLMODE<>"4" AND DTLTYPE<>"1" AND DTLFUNC<>"01") OR (DTLMODE<>"3" AND DTLTYPE<>"1" AND DTLFUNC<>"01")) AND ITEMCODE<>"0000000000000000" AND BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'" AND CONSECNUMBER IN (SELECT CONSECNUMBER FROM CST011 WHERE NBCHKNUMBER IS NULL AND BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'") GROUP BY BIZDATE,DTLMODE,DTLTYPE,DTLFUNC,UNITPRICE,ITEMCODE,ITEMDEPTCODE,REMARKS ORDER BY BIZDATE ASC,CREATEDATETIME ASC,ZCOUNTER ASC,CONSECNUMBER ASC,LINENUMBER ASC'; @$first=sqlquery($connlist,$sql,'sqlite'); $sql='SELECT BIZDATE,SELECTIVEITEM1,SELECTIVEITEM2,SELECTIVEITEM3,SELECTIVEITEM4,SELECTIVEITEM5,SELECTIVEITEM6,SELECTIVEITEM7,SELECTIVEITEM8,SELECTIVEITEM9,SELECTIVEITEM10,QTY,UNITPRICE,AMT,REMARKS FROM CST012 WHERE ((DTLMODE<>"9" AND DTLTYPE<>"9" AND DTLFUNC<>"99") OR (DTLMODE<>"4" AND DTLTYPE<>"1" AND DTLFUNC<>"01") OR (DTLMODE<>"3" AND DTLTYPE<>"1" AND DTLFUNC<>"01")) AND ITEMCODE<>"0000000000000000" AND SELECTIVEITEM1 IS NOT NULL AND BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'" AND CONSECNUMBER IN (SELECT CONSECNUMBER FROM CST011 WHERE NBCHKNUMBER IS NULL AND BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'")'; @$second=sqlquery($connlist,$sql,'sqlite'); $sql='SELECT SUM(persons) AS QTY,BIZDATE,SUM(SALESTTLAMT) AS AMT,SUM(TAX2) AS cashmoney,SUM(TAX3) AS cash FROM (SELECT CASE WHEN (TAX6+TAX7+TAX8)=0 THEN 1 ELSE (TAX6+TAX7+TAX8) END AS persons,BIZDATE,SALESTTLAMT,TAX2,TAX3,REMARKS FROM CST011 WHERE BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'" AND NBCHKNUMBER IS NULL) GROUP BY BIZDATE'; @$listarray=sqlquery($connlist,$sql,'sqlite'); $sql='SELECT CST012.BIZDATE,SUM(CST012.AMT) as amt FROM CST012 JOIN CST011 ON CST011.NBCHKNUMBER IS NULL AND CST011.CONSECNUMBER=CST012.CONSECNUMBER WHERE CST012.BIZDATE BETWEEN "'.preg_replace('/-/','',$startdate).'" AND "'.preg_replace('/-/','',$enddate).'" AND CAST(CST012.ITEMCODE AS INT) IN ('.$orderitemlist.') GROUP BY CST012.BIZDATE'; @$temporderitemarray=sqlquery($connlist,$sql,'sqlite'); for($d=0;$d

商品銷售數量統計

'; if(strtotime(date('Ymd',strtotime($enddate)))>strtotime(date('Ymd'))){ $ENDDATE=strtotime(date('Ymd')); } else{ $ENDDATE=strtotime(date('Ymd',strtotime($enddate))); } $type=preg_split('/,/',$init['init']['orderlocation']); for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ echo ""; //echo ''; } echo ""; echo ''; $detaste=array(); $discount=array(); $dissum=0; $tastetitle='';//加料與備註合計欄位 $tasteitems1='';//加料與備註品項 $tasteitems2='';//加料與備註品項 $cashmoney=array(); $cash=array(); $tasteitemsum=0; //$tasteindex=0; $sum1=0; $sum2=0; $sum3=0; $sum4=0; //print_r($menu); echo ''; if(isset($menu)&&sizeof($menu)){ foreach($menu as $k => $i){//類別 if((string)$k!='list'&&(string)$k!='item'&&(string)$k!='autodis'&&substr($k,0,1)!='t'&&isset($i['amt'])){ $temptype=""; /*for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ $presubtotal=0; echo ''; }*/ //echo ""; $thtml1=''; $thtml2=''; $tqty=array(); $tamt=array(); $temphtml=''; $temphtml2=''; $computindex=0; foreach($i as $di){//商品 if(is_array($di)){ foreach($di as $ddindex=>$ddi){//價格名稱 $subqty=0; $subamt=0; if(isset($ddi['qty'])&&intval($ddi['qty'])>0){ if($temphtml==''){ $temphtml=''; } else{ $temphtml2=$temphtml2.''; } for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ if(isset($ddi[date('Ymd',$d)]['qty'])){ if($computindex==0){ $temphtml=$temphtml.''; } else{ $temphtml2=$temphtml2.''; } $subqty=floatval($subqty)+floatval($ddi[date('Ymd',$d)]['qty']); } else{ if($computindex==0){ $temphtml=$temphtml.''; } else{ $temphtml2=$temphtml2.''; } } if(isset($ddi[date('Ymd',$d)]['amt'])){ //$temphtml=$temphtml.''; //$subamt=floatval($subamt)+floatval($ddi[date('Ymd',$d)]['amt']); if(isset($tamt[substr(date('Ymd',$d),2,6)])){ //$tqty[0]=intval($tqty[0])+intval($ddi['qty']); $tamt[substr(date('Ymd',$d),2,6)]=intval($tamt[substr(date('Ymd',$d),2,6)])+intval($ddi[date('Ymd',$d)]['amt']); } else{ //$tqty[0]=intval($ddi['qty']); $tamt[substr(date('Ymd',$d),2,6)]=intval($ddi[date('Ymd',$d)]['amt']); } } else{ //$temphtml=$temphtml.''; } } if($computindex==0){ $temphtml=$temphtml.''; } else{ $temphtml2=$temphtml2.''; } //echo ''; //echo $temphtml; //echo ''; if(isset($tqty[0])){ $tqty[0]=intval($tqty[0])+intval($ddi['qty']); $tamt[0]=intval($tamt[0])+intval($ddi['amt']); } else{ $tqty[0]=intval($ddi['qty']); $tamt[0]=intval($ddi['amt']); } $computindex++; } else{ } } } else{ } } for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ if(isset($tamt[substr(date('Ymd',$d),2,6)])){ $temptype=$temptype.''; } else{ $temptype=$temptype.''; } } echo $temptype.''; echo $temphtml.''.$temphtml2; } else if((string)$k!='list'&&(string)$k!='item'&&(string)$k!='autodis'&&substr($k,0,1)!='t'){ } /*else if(substr($k,0,2)=='td'){ $subtasqty=[0,0,0,0,0]; $subtasamt=[0,0,0,0,0]; $temptasteitem=''; if(isset($i['qty'])&&intval($i['qty'])>0){ $tasteitems1=$tasteitems1.''; for($d=strtotime(date('Ymd',strtotime($start)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ for($saletypelen=0;$saletypelen'.number_format($i[date('Ymd',$d)][intval($type[$saletypelen])]['qty']).''; $temptasteitem=$temptasteitem.''; } else{ $temptasteitem=$temptasteitem.''; $temptasteitem=$temptasteitem.''; } } } for($saletypelen=0;$saletypelen'.number_format($subtasqty[intval($type[$saletypelen])]).''; $tasteitems1=$tasteitems1.''; } $tasteitems1=$tasteitems1.$temptasteitem.''; } else{ } } else if((string)$k=='taste'){ $sum=0; $tastetitle=''; for($d=strtotime(date('Ymd',strtotime($start)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ for($saletypelen=0;$saletypelen'; $tastetitle=$tastetitle.''; if(isset($i[date('Ymd',$d)][intval($type[$saletypelen])]['amt'])){ $sum=floatval($sum)+floatval($i[date('Ymd',$d)][intval($type[$saletypelen])]['amt']); } else{ } } } for($saletypelen=0;$saletypelen'; $tastetitle=$tastetitle.''; } $tastetitle=$tastetitle.''; }*/ else if((string)$k=='list'||(string)$k=='item'||(string)$k=='autodis'){ for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ for($saletypelen=0;$saletypelen0){ $tempdiscount=''; echo ''; $index=1; for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ if(isset($discount[date('Ymd',$d)][intval($type[$saletypelen])])){ $index++; //$discounttype[intval($type[$saletypelen])]=floatval($discounttype[intval($type[$saletypelen])])+floatval($discount[date('Ymd',$d)][intval($type[$saletypelen])]); $tempdiscount=$tempdiscount.'"; $dissum=floatval($dissum)+floatval($discount[date('Ymd',$d)][intval($type[$saletypelen])]['amt']); } else{ $index++; $tempdiscount=$tempdiscount.''; } } echo $tempdiscount.''; } else{ $tempdiscount=''; echo ''; $index=1; for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ $tempdiscount=$tempdiscount.''; } echo $tempdiscount.''; } $predaymoney=''; $predaynumber=''; $predaypmoney=''; //$predaycashmoney=''; //$predaycharge=''; //$predaycash=''; $temppredaymoney=''; $temppredaynumber=''; $temppredaypmoney=''; $temppredaycashmoney=''; $temppredaycharge=''; $temppredaycash=''; $chargetype=0; //$cashmoneytype=0; //$cashtype=0; $daytype=0; $numbertype=0; if(sizeof($list)>0){ //$predaycharge=''; //$predaycashmoney=''; //$predaycash=''; $predaymoney=''; $predaynumber=''; $predaypmoney=''; $index=1; for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ //$temppredaycharge=$temppredaycharge.''; //$temppredaycashmoney=$temppredaycashmoney.''; //$temppredaycash=$temppredaycash.''; //$temppredaymoney=$temppredaymoney.''; //$temppredaynumber=$temppredaynumber.''; //$temppredaypmoney=$temppredaypmoney.''; $index++; if(isset($list[date('Ymd',$d)]['money'])){ //$temppredaycharge=$temppredaycharge.''; //$cashmoneytype=floatval($cashmoneytype)+floatval($list[date('Ymd',$d)]['cashmoney']); //$temppredaycashmoney=$temppredaycashmoney.''; //$cashtype=floatval($cashtype)+floatval($list[date('Ymd',$d)]['cash']); //$temppredaycash=$temppredaycash.''; $submoney=$list[date('Ymd',$d)]['money']; $daytype=floatval($daytype)+floatval($submoney); $temppredaymoney=$temppredaymoney.''; $numbertype=floatval($numbertype)+floatval($list[date('Ymd',$d)]['qty']); $temppredaynumber=$temppredaynumber.''; $temppredaypmoney=$temppredaypmoney.''; $sum1=floatval($sum1)+floatval($submoney); $sum2=floatval($sum2)+floatval($list[date('Ymd',$d)]['qty']); $sum3=floatval($sum3)+floatval($list[date('Ymd',$d)]['cashmoney']); $sum4=floatval($sum4)+floatval($list[date('Ymd',$d)]['cash']); } else{ //$temppredaycharge=$temppredaycharge.''; //$temppredaycashmoney=$temppredaycashmoney.''; //$temppredaycash=$temppredaycash.''; $temppredaymoney=$temppredaymoney.''; $temppredaynumber=$temppredaynumber.''; $temppredaypmoney=$temppredaypmoney.''; } $index++; } //$predaycharge=$predaycharge.$temppredaycharge.''; //$predaycashmoney=$predaycashmoney.$temppredaycashmoney.''; //$predaycash=$predaycash.$temppredaycash.''; $predaymoney=$predaymoney.$temppredaymoney.''; $predaynumber=$predaynumber.$temppredaynumber.''; if(intval($sum2)==0){ $predaypmoney=$predaypmoney.$temppredaypmoney.''; } else{ $predaypmoney=$predaypmoney.$temppredaypmoney.''; } } else{ } //echo $predaycharge.$predaycashmoney.$predaycash; if(isset($orderitemarray['amt'])){ echo ''; for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ if(isset($orderitemarray[date('Ymd',$d)])){ echo ''; } else{ echo ''; } } echo ''; } else{ echo ''; for($d=strtotime(date('Ymd',strtotime($startdate)));$d<=$ENDDATE;$d=strtotime(date('Ymd',$d).' +1 day')){ echo ''; } echo ''; } echo $predaymoney.$predaynumber.$predaypmoney; echo '

營業日期

商品名稱

".substr(date('Ymd',$d),2,6)."

'.substr(date('Ymd',$d),2,6).'

小計

合計

".$i['name1']."

'.$di['name1'].'

'.$ddi[date('Ymd',$d)]['qty'].'

'.$ddi[date('Ymd',$d)]['amt'].'

'.$ddi['qty'].'

'.$ddi['amt'].'

'.number_format($tamt[substr(date('Ymd',$d),2,6)]).'

'.number_format($tamt[0]).'

'.number_format($tqty[0]).'

'.$i['name1'].'

'.number_format($i[date('Ymd',$d)][intval($type[$saletypelen])]['amt']).'

'.number_format($subtasamt[intval($type[$saletypelen])]).'

加料與備註

折扣

'.number_format($discount[date('Ymd',$d)][intval($type[$saletypelen])])."

'.number_format($dissum).'

折扣

'.number_format($dissum).'

';if($interface!='-1'&&isset($interface['name']['totalcharge']))$predaycharge=$predaycharge.$interface['name']['totalcharge'];else $predaycharge=$predaycharge.'總服務費';$predaycharge=$predaycharge.'

';if($interface!='-1'&&isset($interface['name']['totalmoney']))$predaycashmoney=$predaycashmoney.$interface['name']['totalmoney'];else $predaycashmoney=$predaycashmoney.'現金收入';$predaycashmoney=$predaycashmoney.'

';if($interface!='-1'&&isset($interface['name']['totalcash']))$predaycash=$predaycash.$interface['name']['totalcash'];else $predaycash=$predaycash.'信用卡收入';$predaycash=$predaycash.'

每日營收

帳單數量

平均金額

';if(isset($charge[date('Ymd',$d)][$type[$saletypelen]]['tax1'])){$temppredaycharge=$temppredaycharge.number_format($charge[date('Ymd',$d)][$type[$saletypelen]]['tax1']);$chargetype[$type[$saletypelen]]=floatval($chargetype[$type[$saletypelen]])+floatval($charge[date('Ymd',$d)][$type[$saletypelen]]['tax1']);}else $temppredaycharge=$temppredaycharge.'0';$temppredaycharge=$temppredaycharge.'

'.number_format($list[date('Ymd',$d)][$type[$saletypelen]]['cashmoney']).'

'.number_format($list[date('Ymd',$d)][$type[$saletypelen]]['cash']).'

'.number_format($submoney).'

'.number_format($list[date('Ymd',$d)]['qty']).'

'.round($submoney/$list[date('Ymd',$d)]['qty'],2).'

'.number_format($totalcharge).'

'.number_format($sum3).'

'.number_format($sum4).'

'.number_format($sum1).'

'.number_format($sum2).'

'.round($sum1/$sum2,2).'

單點

'.number_format($orderitemarray[date('Ymd',$d)]).'

'.number_format($orderitemarray['amt']).'

單點

'; } } sqlclose($connm,'sqlite'); } else{ $list=array();//點餐之餐點明細 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $csv=array();//匯出檔案內容 //因為sqlite沒有支援if的語法，只好先查詢折扣的數量，分為有折扣與沒有折扣兩種情況 //$sql="SELECT BIZDATE,ZCOUNTER,SUM(AMT) AS DISCOUNT FROM CST012 WHERE DTLMODE='1' AND (DTLTYPE='2' OR DTLTYPE='3') GROUP BY ZCOUNTER"; $sql="SELECT bizdate,zcounter,SUM(amt) AS discount FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND (dtltype='2' OR dtltype='3') AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY zcounter"; //$condition=sqlquery($conn,$sql,'sqlite'); @$condition=sqlquery($conn,$sql,'mysql'); $cond=array(); foreach($condition as $value){ //$cond[$value['BIZDATE']][$value['ZCOUNTER']]=$value['DISCOUNT']; $cond[$value['bizdate']][$value['zcounter']]=$value['discount']; } $sql="SELECT DISTINCT B.company,B.bizdate,B.itemcode,B.itemgrpcode,B.itemdeptcode,B.qty,B.unitprice,B.amt,B.zcounter,B.createdatetime,C.ztotal,E.number FROM ( SELECT DISTINCT company,bizdate,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,SUM(qty) AS qty,unitprice,SUM(amt) AS amt,zcounter,createdatetime FROM ( SELECT * FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' ORDER BY zcounter ASC,consecnumber ASC,linenumber DESC ) AS A GROUP BY bizdate,itemcode,zcounter ORDER BY zcounter ) AS B JOIN ( SELECT DISTINCT bizdate,zcounter,SUM(amt) AS ztotal FROM ( SELECT DISTINCT bizdate,itemcode,itemname,itemgrpcode,itemgrpname,itemdeptcode,itemdeptname,SUM(qty) AS qty,unitprice,SUM(amt) AS amt,zcounter,createdatetime FROM ( SELECT * FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' ORDER BY zcounter ASC,consecnumber ASC,linenumber DESC ) AS A GROUP BY bizdate,itemcode,zcounter ORDER BY zcounter ) AS table1 GROUP BY bizdate,zcounter ) AS C ON C.zcounter=B.zcounter AND C.bizdate=B.bizdate JOIN ( SELECT bizdate,zcounter,SUM(number) AS number FROM ( SELECT DISTINCT bizdate,company,consecnumber,salesttlamt,zcounter,CASE WHEN salesttlamt>0 THEN 1 WHEN salesttlamt=0 THEN 0 ELSE -1 END AS number FROM alldetails WHERE company='".$DB."' AND CAST(invoicenumber AS INT)>0 ) AS A GROUP BY bizdate,zcounter ) AS E ON E.zcounter=B.zcounter AND E.bizdate=B.bizdate WHERE B.company='".$DB."' AND qty>0 AND B.bizdate BETWEEN '".$startdate."' AND '".$enddate."'"; @$list=sqlquery($conn,$sql,'mysql'); $sql='SELECT finaltime FROM userlogin WHERE dept="'.strtoupper($DB).'" AND function LIKE "%pos%"'; @$finaltime=sqlquery($conn,$sql,'mysql'); if(sizeof($list)==0){ if(sizeof($finaltime)==0){ //echo "查無資料。
"; } else{ echo "查無資料。
最後更新時間：".$finaltime[0]['finaltime'].""; } } else if($list[0]=="SQL語法錯誤"||$list[0]=="連線失敗"){ if($dubug==1){ echo $list[0]."(select)".$sql; } else{ echo $list[0]."(select)"; } } else{ //$a=$list[0]['CREATEDATETIME']; $a=$list[0]['createdatetime']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); //$sql="SELECT DISTINCT ITEMCODE,ITEMNAME,SUM(AMT) AS TOTAL FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMCODE"; //$sql="SELECT DISTINCT ITEMDEPTCODE,ITEMDEPTNAME,ITEMCODE,ITEMNAME,SUM(QTY) AS QTY,SUM(AMT) AS AMT FROM (SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008') GROUP BY ITEMDEPTCODE,ITEMCODE ORDER BY ITEMDEPTCODE ASC,ITEMCODE ASC"; $sql="SELECT DISTINCT A.itemdeptcode,A.itemdeptname,A.itemcode,A.itemname,SUM(A.qty) AS qty,SUM(A.amt) AS amt FROM (SELECT * FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008') AS A WHERE bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY itemdeptcode,itemcode ORDER BY itemdeptcode ASC,itemcode ASC"; //$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存菜單；產品編號與產品名稱 @$tempmenu=sqlquery($conn,$sql,'mysql'); //$sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 $sql="SELECT bizdate,zcounter FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY bizdate,zcounter"; //$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 @$tempzcounter=sqlquery($conn,$sql,'mysql'); $mtotal=0;//單月總銷售額(不含折扣) $mdiscount=0;//單月總折扣 $mnumber=0;//單月帳單總數 $msingle=0;//單月單點 $mcombine=0;//單月套餐 $ptotal="";//單月總銷售金額折線圖變數數值(字串表示) //$mitemdept=array();//單月產品分類統計；$mitemdept[產品分類編號]=此分類的銷售數量 //$menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['ITEMDEPTCODE']); $menu[0]['value']=intval($tempmenu[sizeof($tempmenu)-1]['itemdeptcode']); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[產品類別編號][total]=該類別中的商品數量,$menu[產品類別編號][name]=類別名稱,$menu[產品類別編號][itemcode1~N]=類別中的商品編號,$menu[產品類別編號][itemname1~N]=類別中的商品名稱,$menu[產品類別編號][itemqty1~N]=類別中的商品銷售數量,$menu[產品類別編號][itemamt1~N]=類別中的商品銷售金額 /*if(isset($menu[intval($b['ITEMDEPTCODE'])]['total'])){ $menu[intval($b['ITEMDEPTCODE'])]['total']=$menu[intval($b['ITEMDEPTCODE'])]['total']+1; $menu[intval($b['ITEMDEPTCODE'])]['itemcode'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemqty'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['QTY']; $menu[intval($b['ITEMDEPTCODE'])]['itemamt'.$menu[intval($b['ITEMDEPTCODE'])]['total']]=$b['AMT']; } else{ $menu[intval($b['ITEMDEPTCODE'])]['total']=1; $menu[intval($b['ITEMDEPTCODE'])]['name']=$b['ITEMDEPTNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemcode1']=intval($b['ITEMCODE']); $menu[intval($b['ITEMDEPTCODE'])]['itemname1']=$b['ITEMNAME']; $menu[intval($b['ITEMDEPTCODE'])]['itemqty1']=$b['QTY']; $menu[intval($b['ITEMDEPTCODE'])]['itemamt1']=$b['AMT']; }*/ if(isset($menu[intval($b['itemdeptcode'])]['total'])){ $menu[intval($b['itemdeptcode'])]['total']=$menu[intval($b['itemdeptcode'])]['total']+1; $menu[intval($b['itemdeptcode'])]['itemcode'.$menu[intval($b['itemdeptcode'])]['total']]=intval($b['itemcode']); $menu[intval($b['itemdeptcode'])]['itemname'.$menu[intval($b['itemdeptcode'])]['total']]=$b['itemname']; $menu[intval($b['itemdeptcode'])]['itemqty'.$menu[intval($b['itemdeptcode'])]['total']]=$b['qty']; $menu[intval($b['itemdeptcode'])]['itemamt'.$menu[intval($b['itemdeptcode'])]['total']]=$b['amt']; } else{ $menu[intval($b['itemdeptcode'])]['total']=1; $menu[intval($b['itemdeptcode'])]['name']=$b['itemdeptname']; $menu[intval($b['itemdeptcode'])]['itemcode1']=intval($b['itemcode']); $menu[intval($b['itemdeptcode'])]['itemname1']=$b['itemname']; $menu[intval($b['itemdeptcode'])]['itemqty1']=$b['qty']; $menu[intval($b['itemdeptcode'])]['itemamt1']=$b['amt']; } } $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ /*$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']);*/ $zcounter[$b['bizdate']]['total']=1; $zcounter[$b['bizdate']]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); } else{ /*if(isset($zcounter[intval(substr($b['BIZDATE'],6,2))]['total'])){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']+1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter'.$zcounter[intval(substr($b['BIZDATE'],6,2))]['total']]=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{ if(intval($b['ZCOUNTER'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['ZCOUNTER'])<$nowcounter){ $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['BIZDATE'],6,2))]['total']=1; $zcounter[intval(substr($b['BIZDATE'],6,2))]['counter1']=intval($b['ZCOUNTER']); $nowcounter=intval($b['ZCOUNTER']); } }*/ if(isset($zcounter[$b['bizdate']]['total'])){ $zcounter[$b['bizdate']]['total']=$zcounter[$b['bizdate']]['total']+1; $zcounter[$b['bizdate']]['counter'.$zcounter[$b['bizdate']]['total']]=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); } else{ if(intval($b['zcounter'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['zcounter'])<$nowcounter){ $zcounter[$b['bizdate']]['total']=1; $zcounter[$b['bizdate']]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[$b['bizdate']]['total']=1; $zcounter[$b['bizdate']]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); } } } } foreach($list as $c){//點餐之餐點明細轉成新的陣列；$paper[班別][產品類別編號][產品編號]=該班別此產品的總銷售金額,$paper[班別][footer][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][footer][discount]=該班別的總折扣(所有產品),$paper[班別][footer][itemdeptcode]=產品分類編號,$paper[班別][footer][qty]=該班別此產品的總銷售數量 //$paper[intval($c['ZCOUNTER'])][intval($c['ITEMCODE'])]=$c['AMT']; /*$paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])."qty"]=$c['QTY']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])][intval($c['ITEMCODE'])."amt"]=$c['AMT']; $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['ztotal']=$c['ZTOTAL']; if(sizeof($condition)>0&&isset($cond[$c['BIZDATE']][$c['ZCOUNTER']])){ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=$cond[$c['BIZDATE']][$c['ZCOUNTER']]; } else{ $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['discount']=0; } $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['itemdeptcode']=intval($c['ITEMDEPTCODE']); $paper[intval(substr($c['BIZDATE'],6,2))][intval($c['ZCOUNTER'])]['footer']['number']=$c['NUMBER'];*/ $paper[$c['bizdate']][intval($c['zcounter'])][intval($c['itemdeptcode'])][intval($c['itemcode'])."qty"]=$c['qty']; $paper[$c['bizdate']][intval($c['zcounter'])][intval($c['itemdeptcode'])][intval($c['itemcode'])."amt"]=$c['amt']; $paper[$c['bizdate']][intval($c['zcounter'])]['footer']['ztotal']=$c['ztotal']; if(isset($cond[$c['bizdate']][$c['zcounter']])){ $paper[$c['bizdate']][intval($c['zcounter'])]['footer']['discount']=$cond[$c['bizdate']][$c['zcounter']]; } else{ $paper[$c['bizdate']][intval($c['zcounter'])]['footer']['discount']=0; } $paper[$c['bizdate']][intval($c['zcounter'])]['footer']['itemdeptcode']=intval($c['itemdeptcode']); $paper[$c['bizdate']][intval($c['zcounter'])]['footer']['number']=$c['number']; //$paper[intval($c['ZCOUNTER'])][intval($c['ITEMDEPTCODE'])]['qty']=$c['QTY']; /*if(isset($mitemdept[$c['ITEMDEPTCODE']])){ $mitemdept[$c['ITEMDEPTCODE']]=$mitemdept[$c['ITEMDEPTCODE']]+$c['QTY']; } else{ $mitemdept[$c['ITEMDEPTCODE']]=$c['QTY']; }*/ } echo "

"; echo "

"; echo ""; echo ""; $index=1; $bgcolor1='#ffffff'; $bgcolor2='#ffffff'; for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ array_push($csv,array("營業日期")); array_push($csv,array("商品名稱")); echo ""; } else{ if(isset($menu[$i]['total'])){ echo ""; array_push($csv,array($menu[$i]['name'])); for($code=1;$code<=$menu[$i]['total'];$code++){ echo "".$menu[$i]['itemname'.$code].""; array_push($csv,array($menu[$i]['itemname'.$code])); } $index++; } else{ } } } //echo ""; echo ""; //echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv,array("折扣")); array_push($csv,array("單點")); array_push($csv,array("每日營收")); array_push($csv,array("帳單數量")); array_push($csv,array("平均金額")); echo ""; /*$date=intval(substr($startdate,strlen($startdate)-2,2)); for(;$date<=$maxDay;$date++){*/ for($i=0;date( "Y-m-d", strtotime( $startdate." +".$i." day" ))!=date( "Y-m-d", strtotime( $enddate." +1 day" ) );$i++){ $date=date( "Y-m-d", strtotime( $startdate." +".$i." day" )); $tempdate=preg_replace("/-/","",$date); $index=1; $temparray=array();//暫存陣列 $temparray[zcounter][total]=此班別的總營收；$temparray[zcounter][number]=此班別的帳單數 $ztotal=0;//單日總銷售額(不含折扣) $zdiscount=0;//單日總折扣 $znumber=0;//帳單數量 $money=0;//每日營收 $single=0;//單點數量 $combine=0;//套餐數量 $csvstart=2;//匯出檔案陣列之開始row $aaa=1; if(isset($zcounter[$date]['total'])){ echo ""; echo ""; array_push($csv[0],$date); for($deptcode=1;$deptcode<=$menu[0]['value'];$deptcode++){ $HTMLstring=""; if(isset($menu[$deptcode]['total'])){ $dmoney=0;//類別加總金額 for($code=1;$code<=$menu[$deptcode]['total'];$code++){ $zmoney=0;//單一商品加總數量 $tempdmoney=0;//單一商品加總金額 if($zcounter[$date]['total']==1){ if(isset($paper[$date][$zcounter[$date]['counter1']][$deptcode][$menu[$deptcode]['itemcode'.$code]."qty"])){ $zmoney=$paper[$date][$zcounter[$date]['counter1']][$deptcode][$menu[$deptcode]['itemcode'.$code]."qty"]; if(in_array($zcounter[$date]['counter1'],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter1']]['total']=$paper[$date][$zcounter[$date]['counter1']]['footer']['ztotal']; $temparray[$zcounter[$date]['counter1']]['number']=$paper[$date][$zcounter[$date]['counter1']]['footer']['number']; } //$ztotal=$paper[$date][$zcounter[$date]['counter1']]['footer']['ztotal']; //$zdiscount=$paper[$date][$zcounter[$date]['counter1']]['footer']['discount']; $tempdmoney=$paper[$date][$zcounter[$date]['counter1']][$deptcode][$menu[$deptcode]['itemcode'.$code]."amt"]; //$znumber=$paper[$date][$zcounter[$date]['counter1']]['footer']['number']; } else{ } } else{ for($z=1;$z<=$zcounter[$date]['total'];$z++){ if(isset($paper[$date][$zcounter[$date]['counter'.$z]][$deptcode][$menu[$deptcode]['itemcode'.$code]."qty"])){ $zmoney=$zmoney+$paper[$date][$zcounter[$date]['counter'.$z]][$deptcode][$menu[$deptcode]['itemcode'.$code]."qty"]; if(in_array($zcounter[$date]['counter'.$z],$temparray,true)){ } else{ $temparray[$zcounter[$date]['counter'.$z]]['total']=$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['ztotal']; $temparray[$zcounter[$date]['counter'.$z]]['number']=$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['number']; } //$ztotal=$ztotal+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['ztotal']; //$zdiscount=$zdiscount+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['discount']; $tempdmoney=$tempdmoney+$paper[$date][$zcounter[$date]['counter'.$z]][$deptcode][$menu[$deptcode]['itemcode'.$code]."amt"]; //$znumber=$znumber+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['number']; } else{ } } } if(sizeof($temparray)>0){ $ztotal=0; $znumber=0; } else{ } foreach($temparray as $ZTOTAL){ $ztotal=$ztotal+$ZTOTAL['total']; $znumber=$znumber+$ZTOTAL['number']; } //echo ""; $dmoney=$dmoney+$tempdmoney; array_push($csv[$csvstart+$code],$zmoney); $HTMLstring=$HTMLstring.""; } } else{ } if(strlen($HTMLstring)>0){ array_push($csv[$csvstart],$dmoney); $tempstring=""; $HTMLstring=$tempstring.$HTMLstring; $index++; $csvstart=$csvstart+$menu[$deptcode]['total']+1; echo $HTMLstring; if($menu[$deptcode]['name']=="套餐"){ $combine=$combine+$dmoney; } else{ $single=$single+$dmoney; } } else{ } } for($z=1;$z<=$zcounter[$date]['total'];$z++){//因為在原本位置計算時，如果有交班，會重複計算 $zdiscount=$zdiscount+$paper[$date][$zcounter[$date]['counter'.$z]]['footer']['discount']; } //echo ""; echo ""; //echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv[$csvstart],$zdiscount); array_push($csv[$csvstart+1],$single); array_push($csv[$csvstart+2],($single+$combine+$zdiscount)); array_push($csv[$csvstart+3],$znumber); array_push($csv[$csvstart+4],round((($ztotal+$zdiscount)/$znumber),2)); $mtotal=$mtotal+$ztotal; $mdiscount=$mdiscount+$zdiscount; $mnumber=$mnumber+$znumber; $msingle=$msingle+$single; $mcombine=$mcombine+$combine; if(strlen($ptotal)==0){ $ptotal=$ptotal.($ztotal+$zdiscount); } else{ $ptotal=$ptotal.",".($ztotal+$zdiscount); } echo ""; } else if($date"; echo ""; array_push($csv[0],$date); for($deptcode=1;$deptcode<=$menu[0]['value'];$deptcode++){ if(isset($menu[$deptcode]['total'])){ echo ""; array_push($csv[$csvstart],"0"); for($code=1;$code<=$menu[$deptcode]['total'];$code++){ echo ""; array_push($csv[$csvstart+$code],"0"); } $csvstart=$csvstart+$menu[$deptcode]['total']+1; $index++; } else{ } } //echo ""; echo ""; //echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv[$csvstart],"0"); array_push($csv[$csvstart+1],"0"); array_push($csv[$csvstart+2],"0"); array_push($csv[$csvstart+3],"0"); array_push($csv[$csvstart+4],"0"); if(strlen($ptotal)==0){ $ptotal=$ptotal."0"; } else{ $ptotal=$ptotal.",0"; } echo ""; } } echo ""; $index=1; $csvstart=2;//匯出檔案陣列之開始row for($i=0;$i<=$menu[0]['value'];$i++){ $HTMLstring=""; $tempamt=0;//類別加總金額 $tempqty=0;//類別加總數量 if($i==0){ echo ""; array_push($csv[0],"小計"); } else{ if(isset($menu[$i]['total'])){ for($code=1;$code<=$menu[$i]['total'];$code++){ $HTMLstring=$HTMLstring.""; array_push($csv[$csvstart+$code],$menu[$i]['itemqty'.$code]); $tempamt=$tempamt+$menu[$i]['itemamt'.$code]; $tempqty=$tempqty+$menu[$i]['itemqty'.$code]; } } else{ } } $tempstring=""; if(strlen($HTMLstring)>0){ array_push($csv[$csvstart],$tempamt); $tempstring=$tempstring.""; $csvstart=$csvstart+$menu[$i]['total']+1; $index++; } else{ } echo $tempstring.$HTMLstring; $menu[$i]['tempamt']=$tempamt; $menu[$i]['tempqty']=$tempqty; } echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv[$csvstart],$mdiscount); array_push($csv[$csvstart+1],$msingle); array_push($csv[$csvstart+2],($msingle+$mcombine+$mdiscount)); array_push($csv[$csvstart+3],""); array_push($csv[$csvstart+4],""); echo ""; echo ""; $index=0; $dept=1;//類別計數器 $csvstart=2;//匯出檔案陣列之開始row for($i=0;$i<=$menu[0]['value'];$i++){ if($i==0){ echo ""; array_push($csv[0],"合計"); $index++; } else{ if(isset($menu[$i]['total'])){ echo ""; array_push($csv[$csvstart],$menu[$i]['tempamt']); //echo ""; echo ""; array_push($csv[$csvstart+1],$menu[$i]['tempqty']); for($cells=1;$cells<$menu[$i]['total'];$cells++){ echo ""; array_push($csv[$csvstart+1+$cells],""); } $dept++; $csvstart=$csvstart+$menu[$i]['total']+1; $index++; } else{ } /*echo ""; for($code=1;$code<=$menu[$i]['total'];$code++){ if(isset($menu[$i]['total'])){ echo ""; } else{ } }*/ } } //echo ""; echo ""; //echo ""; echo ""; echo ""; echo ""; echo ""; array_push($csv[$csvstart],$mdiscount); array_push($csv[$csvstart+1],$msingle); array_push($csv[$csvstart+2],($msingle+$mcombine+$mdiscount)); array_push($csv[$csvstart+3],$mnumber); array_push($csv[$csvstart+4],round((($mtotal+$mdiscount)/$mnumber),2)); echo ""; echo "

營業日期

商品名稱

".$menu[$i]['name']."

小計

折扣

總計

單點

每日營收

帳單數量

平均金額

".substr($tempdate,2)."

".$zmoney."

".$dmoney."

".$ztotal."

".$zdiscount."

".($ztotal+$zdiscount)."

".$single."

".($single+$combine+$zdiscount)."

".$znumber."

".round((($ztotal+$zdiscount)/$znumber),2)."

".substr($tempdate,2)."

小計

".$menu[$i]['itemqty'.$code]."

".$tempamt."

".$mdiscount."

".$msingle."

".($msingle+$mcombine+$mdiscount)."

合計

".$menu[$i]['tempamt']."

".$menu[$i]['tempqty']."

".$menu[$i]['itemtotal'.$code]."

".$mtotal."

".$mdiscount."

".($mtotal+$mdiscount)."

".$msingle."

".($msingle+$mcombine+$mdiscount)."

".$mnumber."

".round((($mtotal+$mdiscount)/$mnumber),2)."

"; } } $conn->close(); //用session的方式傳遞檔案匯出陣列 if(isset($_SESSION['array'])){ unset($_SESSION['array']); $_SESSION['array']=$csv; } else{ $_SESSION['array']=$csv; } } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='newstototal'){ if(strlen($enddate)<10){ $enddate=$enddate."-".date("t",mktime('0','0','0','1',substr($enddate,5,2),substr($enddate,0,4))); } echo "

資料庫
資料庫
時間區間	0)echo " value=".$startdate;echo ">～0)echo " value=".$enddate;echo ">

"; if(empty($startdate)&&empty($enddate)){ echo "請先設定想瀏覽的時間。"; } else{ $table1=array();//商品類別統計 $menu=array();//$menu[類別編號]=類別名稱 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();// //$conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ echo "

"; $sql="SELECT bizdate,itemcode,itemname,SUM(qty) AS qty,SUM(amt) AS amt,itemdeptcode,itemdeptname,itemgrpcode,itemgrpname,zcounter,createdatetime FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY bizdate,itemcode,itemdeptcode,itemgrpcode,zcounter"; //$table1=sqlquery($conn,$sql,'sqlite'); @$table1=sqlquery($conn,$sql,'mysql'); if(sizeof($table1)==0){ echo "查無資料。"; } else if($table1[0]=="SQL語法錯誤"||$table1[0]=="連線失敗"){ if($dubug==1){ echo $table1[0]."(select)".$sql; } else{ echo $table1[0]."(select)"; } } else{ //$a=$table1[0]['CREATEDATETIME']; $a=$table1[0]['createdatetime']; //$maxDay=cal_days_in_month(CAL_GREGORIAN,substr($a,4,2),substr($a,0,4));//自動判斷某年某月的天數 $maxDay=date("t"); /*$sql="SELECT CST012.ITEMDEPTCODE,CST012.ITEMDEPTNAME,CST012.ITEMCODE,CST012.ITEMNAME FROM CST012 JOIN ( SELECT DISTINCT ITEMDEPTCODE,ITEMCODE FROM ( SELECT * FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' ) ) as a ON a.ITEMDEPTCODE=CST012.ITEMDEPTCODE WHERE CST012.DTLMODE='1' AND CST012.DTLTYPE='1' AND (CST012.DTLFUNC='01' OR CST012.DTLFUNC='03') AND CST012.ITEMDEPTCODE<>'000008' GROUP BY CST012.ITEMDEPTCODE,CST012.ITEMCODE";*/ $sql="SELECT DISTINCT itemdeptcode,itemdeptname,itemcode,itemname FROM alldetails WHERE company='".$DB."' AND dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' AND bizdate BETWEEN '".$startdate."' AND '".$enddate."'"; //$tempmenu=sqlquery($conn,$sql,'sqlite');//暫存類別對照表；類別編號與類別名稱 @$tempmenu=sqlquery($conn,$sql,'mysql'); //$sql="SELECT BIZDATE,ZCOUNTER FROM CST012 WHERE DTLMODE='1' AND DTLTYPE='1' AND (DTLFUNC='01' OR DTLFUNC='03') AND ITEMDEPTCODE<>'000008' GROUP BY BIZDATE,ZCOUNTER";//之前使用 $sql="SELECT bizdate,zcounter FROM alldetails WHERE dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03') AND itemdeptcode<>'000008' GROUP BY bizdate,zcounter";//之前使用 //$tempzcounter=sqlquery($conn,$sql,'sqlite');//班別；營業日期與當天班別 @$tempzcounter=sqlquery($conn,$sql,'mysql'); foreach($tempmenu as $b){//將暫存菜單轉成新的陣列；$menu[類別編號][產品編號][dept]=類別名稱,$menu[類別編號][產品編號][item]=產品名稱 if(isset($menu[intval($b['itemdeptcode'])]['total'])){ $menu[intval($b['itemdeptcode'])]['total']=$menu[intval($b['itemdeptcode'])]['total']+1; $menu[intval($b['itemdeptcode'])]['itemcode'.$menu[intval($b['itemdeptcode'])]['total']]=intval($b['itemcode']); $menu[intval($b['itemdeptcode'])]['itemname'.$menu[intval($b['itemdeptcode'])]['total']]=$b['itemname']; } else{ $menu[intval($b['itemdeptcode'])]['total']=1; $menu[intval($b['itemdeptcode'])]['name']=$b['itemdeptname']; $menu[intval($b['itemdeptcode'])]['itemcode1']=intval($b['itemcode']); $menu[intval($b['itemdeptcode'])]['itemname1']=$b['itemname']; } } $menu[0]['value']=max(array_keys($menu)); $zcounterList=array(); $nowcounter=0; foreach($tempzcounter as $b){//將zcounter陣列改成非方正陣列;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter;這樣能夠同時解決一天1個班別以上以及跨夜的業績歸屬前一天的兩個問題 if(sizeof($zcounter)==0){ $zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['total']=1; $zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); $zcounterList[]=intval($b['zcounter']); } else{ if(isset($zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['total'])){ $zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['total']=$zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['total']+1; $zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['counter'.$zcounter[intval(substr($b['bizdate'],strlen($b['bizdate'])-2,2))]['total']]=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); $zcounterList[]=intval($b['zcounter']); } else{ if(intval($b['zcounter'])==$nowcounter){ //基本上ZCOUNTER隔天的值至少是大於等於，如果是等於則表示跨夜班，業績歸屬前一天 //由於會發生人為疏忽忘記交班的情況，需要由我們幫忙重設交班計數器，如此一來，大於等於的狀況就不再為正常判斷標準 //因此小於的情況，與大於的情況類似 } else if(intval($b['zcounter'])<$nowcounter){ $zcounter[intval(substr($b['bizdate'],6,2))]['total']=1; $zcounter[intval(substr($b['bizdate'],6,2))]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); $zcounterList[]=intval($b['zcounter']); } else{//新的ZCOUNTER如果大於，則表示為新的一天 $zcounter[intval(substr($b['bizdate'],6,2))]['total']=1; $zcounter[intval(substr($b['bizdate'],6,2))]['counter1']=intval($b['zcounter']); $nowcounter=intval($b['zcounter']); $zcounterList[]=intval($b['zcounter']); } } } } foreach($table1 as $c){//轉存成$paper[類別編號][產品編號][qty]=銷售數量,$paper[類別編號][產品編號][amt]=銷售金額 if(in_array(intval($c['zcounter']),$zcounterList)){ if(isset($paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['qty'])){ $paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['qty']=$paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['qty']+$c['qty']; $paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['amt']=$paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['amt']+$c['amt']; } else{ $paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['qty']=$c['qty']; $paper[intval($c['itemdeptcode'])][intval($c['itemcode'])]['amt']=$c['amt']; } } else{ } } //print_r($menu); $width=500; $height=300; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; echo "

"; echo " "; echo "

"; } $conn->close(); echo "

"; } } } else if(isset($_POST['conttype'])&&$_POST['conttype']=='atmoment'){ echo "

資料庫
資料庫

"; $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","","mysql"); if(!$conn){ echo "資料庫發生錯誤。"; } else{ $sql="SELECT atmoment FROM UserLogin WHERE usedb='".$_SESSION['DB']."' AND function LIKE '%pos%'"; @$temp=sqlquery($conn,$sql,"mysql"); if(sizeof($temp)==0){ $table=1; } else{ $company=substr($temp[0]['atmoment'],0,4); $dept=substr($temp[0]['atmoment'],5,4); $table=array(); $sql="SELECT DISTINCT company,dept,filename,itemcode,itemname,itemdeptcode,itemdeptname,itemgrpcode,itemgrpname,SUM(qty) AS qty,unitprice,SUM(amt) AS amt FROM AtMoment WHERE company='".$company."' AND dept='".$dept."' GROUP BY filename,itemcode,itemdeptcode"; @$table=sqlquery($conn,$sql,"mysql"); $sql="SELECT COUNT(filename) AS number FROM (SELECT DISTINCT filename FROM AtMoment WHERE company='".$company."' AND dept='".$dept."') AS A"; @$table2=sqlquery($conn,$sql,"mysql"); } if($table==1){ } else if(sizeof($table)==0){ echo "查無資料。"; } else if($table[0]=="SQL語法錯誤"||$table[0]=="連線失敗"){ if($dubug==1){ echo $table[0]."(select)".$sql; } else{ echo $table[0]."(select)"; } } else{ $item=array(); foreach($table as $a){ if(isset($item[intval($a['itemcode'])]['name'])){ $item[intval($a['itemcode'])]['qty']=$item[intval($a['itemcode'])]['qty']+$a['qty']; $item[intval($a['itemcode'])]['amt']=$item[intval($a['itemcode'])]['amt']+$a['amt']; } else{ $item[intval($a['itemcode'])]['name']=$a['itemname']; $item[intval($a['itemcode'])]['qty']=$a['qty']; $item[intval($a['itemcode'])]['amt']=$a['amt']; } if(isset($item[0]['total'])){ $item[0]['total']=$item[0]['total']+$a['amt']; } else{ $item[0]['total']=$a['amt']; } } echo "

目前營業額

".$item[0]['total']."

"; echo "

目前帳單數

".$table2[0]['number']."

"; echo ""; echo "商品銷售統計"; echo ""; for($i=1;$i<=max(array_keys($item));$i++){ if(isset($item[$i])){ echo ""; echo ""; echo ""; echo ""; echo ""; } else{ } } echo "

商品名稱	數量	金額
".$item[$i]['name']."	".$item[$i]['qty']."	".$item[$i]['amt']."

"; } $conn->close(); } } else if(isset($_GET['conttype'])&&$_GET['conttype']=='editpsw'){ echo ""; echo ""; } else if(isset($_POST['conttype'])&&$_POST['conttype']=='hourssaleofday'){ if(strlen($enddate)<10){ $enddate=$enddate."-".date("t",mktime('0','0','0','1',substr($enddate,5,2),substr($enddate,0,4))); } echo "

資料庫
資料庫
時間區間	0)echo " value=".$startdate;echo ">～0)echo " value=".$enddate;echo ">

"; if(empty($startdate)&&empty($enddate)){ echo "請先設定想瀏覽的時間。"; } else{ $list=array();//時段銷售總額 $data=array();//分類營業日與時段 $menu=array();//產品編號與產品名稱;A[產品編號][itemname]=產品名稱,A[產品編號][total]=產品在所選時段內的總銷售額 $zcounter=array();//每日的班別;a[bizdate][total]=n,a[bizdate][counter1]~a[bizdate][counterN]=counter; $paper=array();//點餐之餐點明細轉成新的陣列；$paper[班別][產品編號]=該班別此產品的總銷售金額,$paper[班別][ztotal]=該班別的總銷售金額(所有產品),$paper[班別][discount]=該班別的總折扣(所有產品),$paper[班別][itemdeptcode]=產品分類編號,$paper[班別][qty]=該班別此產品的總銷售數量 $csv=array();//匯出檔案內容 //$conn=sqlconnect("../DB/".$DB,"SALES_".$year.$month.".DB","","","","sqlite"); $conn=sqlconnect("localhost","papermanagement","paperadmin","1qaz2wsx","utf-8",'mysql'); if(!$conn){ echo "資料庫發生錯誤或尚未上傳資料。"; } else{ $sql="SELECT bizdate,SUM(amt) AS amt,SUBSTRING(createdatetime,1,13) AS saletime FROM alldetails WHERE company='".$DB."' AND ((dtlmode='1' AND (dtltype='2' OR dtltype='3')) OR (dtlmode='1' AND dtltype='1' AND (dtlfunc='01' OR dtlfunc='03'))) AND bizdate BETWEEN '".$startdate."' AND '".$enddate."' GROUP BY SUBSTRING(createdatetime,1,13)"; @$list=sqlquery($conn,$sql,'mysql'); $sql='SELECT finaltime FROM userlogin WHERE dept="'.strtoupper($DB).'" AND function LIKE "%pos%"'; @$finaltime=sqlquery($conn,$sql,'mysql'); if(sizeof($list)==0){ if(sizeof($finaltime)==0){ //echo "查無資料。
"; } else{ echo "查無資料。
最後更新時間：".$finaltime[0]['finaltime'].""; } } else if($list[0]=="SQL語法錯誤"||$list[0]=="連線失敗"){ if($dubug==1){ echo $list[0]."(select)".$sql; } else{ echo $list[0]."(select)"; } } else{ $maxhour=0; foreach($list as $l){ if($l['bizdate']==substr($l['saletime'],0,10)){ if(isset($data[substr($l['saletime'],11,2)])){ if(isset($data[substr($l['saletime'],11,2)][$l['bizdate']])){ $data[substr($l['saletime'],11,2)][$l['bizdate']]=intval($data[substr($l['saletime'],11,2)][$l['bizdate']])+intval($l['amt']); } else{ $data[substr($l['saletime'],11,2)][$l['bizdate']]=$l['amt']; } } else{ $data[substr($l['saletime'],11,2)][$l['bizdate']]=$l['amt']; } if(intval($maxhour)

時段銷售金額統計

"; echo ""; echo ''; $index=1; $check=0; $csvindex=2; $bgcolor1='#ffffff'; $bgcolor2='#ffffff'; $pretotal=array(); array_push($csv,array("營業日期")); array_push($csv,array("營業時段")); for($t=-1;$t<=$maxhour;$t++){ if($t==-1){ echo ""; echo ""; for($i=strtotime(date($startdate));$i<=strtotime(date($enddate)),$i<=strtotime(date("Y-m-d").' -1 days');$i=strtotime(date("Y-m-d",$i).' +1 days')){ echo ""; array_push($csv[0],substr(date("Ymd",$i),2,6)); $index++; } echo ''; } else{ if($check==1){ if(isset($data[$t])){ echo ""; echo ""; array_push($csv,array($t.":00~".(intval($t)+1).":00")); for($i=strtotime(date($startdate));$i<=strtotime(date($enddate)),$i<=strtotime(date("Y-m-d").' -1 days');$i=strtotime(date("Y-m-d",$i).' +1 days')){ echo ""; $index++; $csvindex++; } echo ''; } else{ echo ""; echo ""; array_push($csv,array($t.":00~".(intval($t)+1).":00")); for($i=strtotime(date($startdate));$i<=strtotime(date($enddate)),$i<=strtotime(date("Y-m-d").' -1 days');$i=strtotime(date("Y-m-d",$i).' +1 days')){ echo ""; $index++; $csvindex++; } echo ''; } } else{ if(isset($data[$t])){ echo ""; echo ""; array_push($csv,array($t.":00~".(intval($t)+1).":00")); for($i=strtotime(date($startdate));$i<=strtotime(date($enddate)),$i<=strtotime(date("Y-m-d").' -1 days');$i=strtotime(date("Y-m-d",$i).' +1 days')){ echo ""; $index++; $csvindex++; } echo ''; $check=1; } else{ } } } } echo ''; for($i=strtotime(date($startdate));$i<=strtotime(date($enddate)),$i<=strtotime(date("Y-m-d").' -1 days');$i=strtotime(date("Y-m-d",$i).' +1 days')){ echo ""; } echo ''; echo '

最後更新時間:'.$finaltime[0]['finaltime'].'

營業日期

營業時段

".substr(date("Ymd",$i),2,6)."

".str_pad($t%24, 2, "0", STR_PAD_LEFT).":00~".str_pad((intval($t)+1)%24, 2, "0", STR_PAD_LEFT).":00

"; if(isset($data[$t][date("Y-m-d",$i)])){ echo $data[$t][date("Y-m-d",$i)]; array_push($csv[sizeof($csv)-1],$data[$t][date("Y-m-d",$i)]); if(isset($pretotal[date("Y-m-d",$i)])){ $pretotal[date("Y-m-d",$i)]=floatval($pretotal[date("Y-m-d",$i)])+floatval($data[$t][date("Y-m-d",$i)]); } else{ $pretotal[date("Y-m-d",$i)]=$data[$t][date("Y-m-d",$i)]; } } else{ echo '0'; array_push($csv[sizeof($csv)-1],'0'); } echo "

".str_pad($t%24, 2, "0", STR_PAD_LEFT).":00~".str_pad((intval($t)+1)%24, 2, "0", STR_PAD_LEFT).":00

"; echo '0'; array_push($csv[sizeof($csv)-1],'0'); echo "

".str_pad($t%24, 2, "0", STR_PAD_LEFT).":00~".str_pad((intval($t)+1)%24, 2, "0", STR_PAD_LEFT).":00

合計

"; if(isset($pretotal[date("Y-m-d",$i)])){ echo $pretotal[date("Y-m-d",$i)]; } else{ echo '0'; } echo "

'; } $conn->close(); /*echo "

"; echo " "; echo "

";*/ //用session的方式傳遞檔案匯出陣列 if(isset($_SESSION['array'])){ unset($_SESSION['array']); $_SESSION['array']=$csv; } else{ $_SESSION['array']=$csv; } } } } else{ echo "

歡迎回來！

"; } } ?>

登入帳號	".$_SESSION['ID']."
原始密碼
新密碼
重新輸入

(".$enddate.")門市月營業匯總表

(".$enddate.")門市月營業匯總表

(".$enddate.")門市日營業匯總表

(".$enddate.")門市日營業匯總表

商品銷售數量統計

時段銷售金額統計

歡迎回來！