"区域名称",
"web_qy.qydm"=>"区域代码",
"web_qy.qyzb"=>"区域金额指标",
"web_qy.qyzb_js"=>"区域件数指标",
"web_dl.dlmc"=>"代理名称",
"web_dl.dldm"=>"代理代码",
"web_dl.dlzb"=>"代理金额指标",
"web_dl.dlzb_js"=>"代理件数指标",
"web_client.title"=>"店铺名称",
"web_client.username"=>"店铺代码",
"web_client.dpsx"=>"店铺属性",
"web_client.dj"=>"店铺等级",
"web_client.zb"=>"店铺金额指标",
"web_client.zb_js"=>"店铺件数指标",
"web_client_brand.zb"=>"店铺品牌金额指标",
"web_client_brand.zb_js"=>"店铺品牌件数指标",
"web_order.no"=>"款号",
"web_order.color"=>"颜色",
"COUNT(DISTINCT web_client.username)"=>"订货店铺数",
"COUNT(DISTINCT web_order.no)"=>"订货款数",
"SUM(web_order.num)"=>"订货数量",
"SUM(web_order.je)"=>"订货吊牌金额",
"SUM(web_order.jsje)"=>"订货折后金额"
);
public $arrFields=array(); #字段数组
public $resNum=0; #结果行数
public $page=0; #当前显示第几页，从0开始
public $pageNum=0; #总页数
public $pageRow=1000; #单页显示行数
public $withRollup=false; #是否分组显示，如果分组显示分页将不起作用
public $condition="where web_order.ka<>'y'"; #sql条件
protected $db; #类内部数据库类引用
protected $fieldsSqlStr=""; #类内部查询语句
protected $fromTableSqlStr=""; #类内部表关系语句
protected $groupBySqlStr=""; #类内部分组语句
protected $keyWordArr=array(); #类内部属性语句
//析构函数，初如化数据库
public function __construct(){
global $dbhost,$dbname,$dbuser,$dbpw;
$this->db=new DB_Sql;
$this->db->connect($dbhost,$dbuser,$dbpw,$dbname);
}
//组合SQL语句，并查询获取结果
public function getRes(){
$res=array();
$arrUsedFields=self::getUsedFields();
self::getFields();
self::getGroupInfo();
self::getTableRelation();
$arrKeyWord=self::getKeyWordArr();
if($this->withRollup==false){
$sqlStr=$this->fieldsSqlStr.$this->fromTableSqlStr." ".$this->condition.$this->groupBySqlStr.$this->getPageInfo();
}else{
$sqlStr=$this->fieldsSqlStr.$this->fromTableSqlStr.$this->groupBySqlStr." with rollup";
}
$r=$this->db->query($sqlStr);
#echo $sqlStr;
$count=0;
while($m=$this->db->fetch_array($r)){
foreach ($m as $key => $value) {
if(in_array($key,$arrUsedFields)){
$res[$count][$key]=$arrKeyWord[$key][$value];
}else{
$res[$count][$key]=$value;
}
};
$count++;
}
return $res;
}
#获取分页
public function getPageInfo(){
$pageSql="";
if($this->pageRow!=="max"){
self::getGroupInfo();
self::getTableRelation();
$sqlStr=$this->fieldsSqlStr.$this->fromTableSqlStr.$this->groupBySqlStr;
#echo $sqlStr;
$res=$this->db->query($sqlStr);
$resNum=$this->db->num_rows($res);
if($resNum)
$this->resNum=$resNum;
if(($this->resNum)>(($this->pageRow)*(($this->page)+1))){
$pageSql=" limit ".($this->pageRow)*($this->page).",".($this->pageRow) ;
}else{
$pageSql=" limit ".($this->pageRow)*($this->page).",".($this->resNum);
}
}
return $pageSql;
}
#获取KEYWORD表字段
public function getUsedFields(){
$q="select no,title from web_keyword where flag_ka='y' and cate='arr_select' and no<>'zd1' and no<>'zd2' and no<>'zd3'";
$arrUsedFields=array("color");
$r=$this->db->query($q);
while($m=$this->db->fetch_array($r)){
array_push($arrUsedFields,$m["no"]);
$this->useableFields["web_order.".$m["no"]]=$m["title"];
}
return $arrUsedFields;
}
#获取KEYWORD表属性，以通过数据进行匹配款式属性值
public function getKeyWordArr(){
$q="select cate,no,title from web_keyword where flag_ka='n' order by cate,no";
$arrKeyWord=array();
$r=$this->db->query($q);
while($m=$this->db->fetch_array($r)){
$arrKeyWord[$m["cate"]][$m["no"]]=$m["title"];
}
return $arrKeyWord;
}
#获取查询语句SELECT部分
public function getFields(){
$arrFields=$this->arrFields;
$fieldsSqlStr="";
if($arrFields){
foreach ($arrFields as $fieldName) {
$fieldsSqlStr.=$fieldName.",";
}
$fieldsSqlStr="SELECT ".rtrim($fieldsSqlStr,",");
}
$this->fieldsSqlStr=$fieldsSqlStr;
}
#获取查询语句FROM部分
public function getTableRelation(){
$tableArr=$replaceFieldsArr=array();
$arrFields=$this->arrFields;
$fromTableSqlStr=$orderTableFields=$orderTableGroupFields="";
if($arrFields){
foreach ($arrFields as $fieldName) {
$resFieldName=str_replace("SUM(","",$fieldName);
$resFieldName=str_replace("COUNT(","",$resFieldName);
$resFieldName=str_replace(")","",$resFieldName);
$resFieldName=str_replace("DISTINCT","",$resFieldName);
$resFieldName=str_replace(" ","",$resFieldName);
$fieldInfoArr=explode(".",$resFieldName);
array_push($tableArr,$fieldInfoArr[0]);
#优化ORDER表查询，先分组再连接，使性能提升；
#获取子表字段及分组条件
if((substr($fieldName,0,4)=="SUM(" || substr($fieldName,0,6)=="COUNT(") && $fieldInfoArr[0]=="web_order"){
$orderTableFields.=$fieldName." AS ".$fieldInfoArr[1].",";
$replaceFieldsArr[$fieldName]="SUM(orderTable.".$fieldInfoArr[1].") AS ".$fieldInfoArr[1];
}else if($fieldInfoArr[0]=="web_order"){
$orderTableFields.=$fieldName." AS ".$fieldInfoArr[1].",";
$orderTableGroupFields.=$fieldName.",";
$replaceFieldsArr[$fieldName]="orderTable.".$fieldInfoArr[1];
}
}
$tableArr=array_unique($tableArr);
if(count($tableArr)>1){
//优化ORDER表查询，先分组再连接，使性能提升；
#客户表及代理表
if(in_array("web_dl",$tableArr)){
$fromTableSqlStr=" (select id,username as dldm,path,title as dlmc,zb as dlzb,zb_js as dlzb_js from web_client where face=3) web_dl right join (select * from web_client where web_client.face='4') web_client on web_client.face=4 and (concat(web_dl.path,'-',web_dl.id)=web_client.path or web_client.path like concat(web_dl.path,'-',web_dl.id,'-%'))";
}
#客户表及区域表
if(in_array("web_qy",$tableArr)){
if($fromTableSqlStr)
$fromTableSqlStr.=" left join (select id,username as qydm,path,title as qymc,zb as qyzb,zb_js as qyzb_js from web_client where face=9) web_qy on web_client.face=4 and (concat(web_qy.path,'-',web_qy.id)=web_client.path or web_client.path like concat(web_qy.path,'-',web_qy.id,'-%'))";
else
$fromTableSqlStr=" (select id,username as qydm,path,title as qymc,zb as qyzb,zb_js as qyzb_js from web_client where face=9) web_qy right join (select * from web_client where web_client.face='4') web_client on web_client.face=4 and (concat(web_qy.path,'-',web_qy.id)=web_client.path or web_client.path like concat(web_qy.path,'-',web_qy.id,'-%'))";
}
#客户表及订单表
if(($fromTableSqlStr || in_array("web_client",$tableArr)) && in_array("web_order",$tableArr)){
$orderTableCondition=$this->getConditionSql();
if($fromTableSqlStr)
$fromTableSqlStr.=" INNER JOIN (SELECT ".$orderTableFields."web_order.uid FROM web_order".$orderTableCondition." GROUP BY ".$orderTableGroupFields."web_order.uid) orderTable ON web_client.id=orderTable.uid";
else
$fromTableSqlStr=" web_client INNER JOIN (SELECT ".$orderTableFields."web_order.uid FROM web_order".$orderTableCondition." GROUP BY ".$orderTableGroupFields."web_order.uid) orderTable ON web_client.id=orderTable.uid";
#替换原查询字段
foreach($replaceFieldsArr as $key=>$value){
$this->fieldsSqlStr=str_replace($key,$value,$this->fieldsSqlStr);
$this->groupBySqlStr=str_replace($key,$value,$this->groupBySqlStr);
}
}
#客户表及品牌表
if(($fromTableSqlStr || in_array("web_client",$tableArr)) && in_array("web_client_brand",$tableArr)){
if($fromTableSqlStr){
$fromTableSqlStr.=" INNER JOIN web_client_brand ON web_client.id=web_client_brand.cid";
}else{
$fromTableSqlStr=" web_client INNER JOIN web_client_brand ON web_client.id=web_client_brand.cid";
}
}
#品牌表和订单表
if(in_array("web_client_brand",$tableArr) && in_array("web_order",$tableArr)){
if($fromTableSqlStr){
$fromTableSqlStr.=" INNER JOIN web_client_brand ON orderTable.uid=web_client_brand.cid and orderTable.brand=web_client_brand.brand";
}else{
$fromTableSqlStr=" web_client_brand INNER JOIN web_order ON web_client_brand.cid=web_order.uid and web_client_brand.brand=web_order.brand";
}
}
$fromTableSqlStr=" FROM ".$fromTableSqlStr;
}else{
$fromTableSqlStr=" FROM ".$tableArr[0];
if($tableArr[0]=="web_dl")
$fromTableSqlStr=" FROM (select id,username as dldm,path,title as dlmc,zb as dlzb,zb_js as dlzb_js from web_client where face=3) web_dl";
if($tableArr[0]=="web_qy")
$fromTableSqlStr=" FROM (select id,username as qydm,path,title as qymc,zb as qyzb,zb_js as qyzb_js from web_client where face=9) web_qy";
}
}
$this->fromTableSqlStr=$fromTableSqlStr;
}
#获取SQL条件部分
public function getConditionSql(){
$orderTableCondition="";
$conditionSql=$this->condition;
$conditionArr=explode(" ",$conditionSql);
foreach($conditionArr as $condition){
if(stripos($condition,"web_order.")!==false){
$this->condition=str_replace($condition,"",$this->condition);
$this->condition=str_replace(" and","",$this->condition);
$orderTableCondition.=" ".$condition." and";
}
}
if(rtrim($this->condition," ")=="where")
$this->condition="";
if($orderTableCondition)
$orderTableCondition=" WHERE ".rtrim($orderTableCondition," and");
return $orderTableCondition;
}
#获取查询语句GROUP BY部分
public function getGroupInfo(){
$groupBySqlStr="";
$arrFields=$this->arrFields;
$isGroup=0;
if($arrFields){
foreach($arrFields as $fieldName){
if(substr($fieldName,0,4)=="SUM(" || substr($fieldName,0,6)=="COUNT("){
$isGroup=1;
}else{
$groupBySqlStr.=$fieldName.",";
}
}
if($isGroup==1 && $groupBySqlStr)
$groupBySqlStr=" GROUP BY ".rtrim($groupBySqlStr,",");
else
$groupBySqlStr="";
}
$this->groupBySqlStr=$groupBySqlStr;
}
}
#Demo
/*
$page=$_GET["page"]?$_GET["page"]:0;
$myReport=new edtMdReport();
$myReport->arrFields=array("web_qy.qymc","web_dl.dlmc","web_client.title","web_client.dpsx","web_order.sxz","web_order.brand","SUM(web_order.num)");
$myReport->condition="where web_order.brand='11' and web_dl.dlmc='云南' and web_order.ka<>'y'";
$myReport->pageRow=100;
$myReport->page=$page;
$r=$myReport->getRes();
header("Content-type: text/html; charset=gb2312");
echo "previousPage nextPage
";
for($i=0;$i";
foreach($r[$i] as $key=>$value){
echo "| ".$value." | ";
}
echo "";
}
echo "";
*/
?>