CString where="";
CString between="";
between.Format(" and 日期 between #%s# and #%s#",CPublic:
ate1,CPublic:
ate2);
UpdateData(1);
if((m_lDWID==0)&&(m_KindID=="")&&(m_lStoreID==0)&&(m_lYGID==0))
{//全部查询
between="";
where.Format("WHERE 日期 between #%s# and #%s#",CPublic:
ate1,CPublic:
ate2);
}
else if((m_lDWID>0)&&(m_KindID=="")&&(m_lStoreID==0)&&(m_lYGID==0))
{//按单位查询
where.Format("WHERE 商品排行.单位ID = %d",m_lDWID);
}
else if((m_KindID!="")&&(m_lDWID==0)&&(m_lStoreID==0)&&(m_lYGID==0))
{//按类别查询
where.Format("WHERE 商品排行.类别编码 like '%s'",m_KindID);
}
else if((m_KindID=="")&&(m_lDWID==0)&&(m_lStoreID>0)&&(m_lYGID==0))
{//按仓库查询
where.Format("WHERE 商品排行.仓库ID = %d",m_lStoreID);
}
else if((m_KindID=="")&&(m_lDWID==0)&&(m_lStoreID==0)&&(m_lYGID>0))
{//按员工查询
where.Format("WHERE 商品排行.员工ID = %d",m_lYGID);
}
else if((m_KindID!="")&&(m_lDWID!=0)&&(m_lStoreID==0)&&(m_lYGID==0))
{//按单位，类别查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.类别编码 like '%s'",m_lDWID,m_KindID);
}
else if((m_KindID!="")&&(m_lDWID==0)&&(m_lStoreID>0)&&(m_lYGID==0))
{//按仓库，类别查询
where.Format("WHERE 商品排行.仓库ID = %d AND 商品排行.类别编码 like '%s'",m_lStoreID,m_KindID);
}
else if((m_KindID!="")&&(m_lDWID==0)&&(m_lStoreID==0)&&(m_lYGID>0))
{//按员工，类别查询
where.Format("WHERE 商品排行.员工ID = %d AND 商品排行.类别编码 like '%s'",m_lYGID,m_KindID);
}
else if((m_KindID=="")&&(m_lDWID>0)&&(m_lStoreID>0)&&(m_lYGID==0))
{//按单位，仓库查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.仓库ID = %d",m_lDWID,m_lStoreID);
}
else if((m_KindID=="")&&(m_lDWID>0)&&(m_lStoreID==0)&&(m_lYGID>0))
{//按单位，员工查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.员工ID = %d",m_lDWID,m_lYGID);
}
else if((m_KindID=="")&&(m_lDWID==0)&&(m_lStoreID>0)&&(m_lYGID>0))
{//按仓库，员工查询
where.Format("WHERE 商品排行.仓库ID = %d AND 商品排行.员工ID = %d",m_lStoreID,m_lYGID);
}
else if((m_KindID!="")&&(m_lDWID>0)&&(m_lStoreID>0)&&(m_lYGID==0))
{//按单位，类别,仓库查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.类别编码 like '%s' AND 商品排行.仓库ID = %d",m_lDWID,m_KindID,m_lStoreID);
}
else if((m_KindID!="")&&(m_lDWID>0)&&(m_lStoreID==0)&&(m_lYGID>0))
{//按单位，类别,员工查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.类别编码 like '%s' AND 商品排行.员工ID = %d",m_lDWID,m_KindID,m_lYGID);
}
else if((m_KindID=="")&&(m_lDWID>0)&&(m_lStoreID>0)&&(m_lYGID>0))
{//按仓库，单位,员工查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.仓库ID = %d AND 商品排行.员工ID = %d",m_lDWID,m_lStoreID,m_lYGID);
}
else if((m_KindID!="")&&(m_lDWID==0)&&(m_lStoreID>0)&&(m_lYGID>0))
{//按仓库，类别,员工查询
where.Format("WHERE 商品排行.仓库ID = %d AND 商品排行.类别编码 like '%s' AND 商品排行.员工ID = %d",m_lStoreID,m_KindID,m_lYGID);
}
else if((m_KindID!="")&&(m_lDWID>0)&&(m_lStoreID>0)&&(m_lYGID>0))
{//按单位，类别,仓库,员工查询
where.Format("WHERE 商品排行.单位ID = %d AND 商品排行.类别编码 like '%s' AND 商品排行.仓库ID = %d AND 商品排行.员工ID = %d",m_lDWID,m_KindID,m_lStoreID,m_lYGID);
}
where+=between;
sql.Format("SELECT 商品排行.商品ID, 商品排行.商品编码, 商品排行.商品名称, \
Sum(商品排行.发生数量) AS 销售数量, \
Sum(商品排行.发生成本) AS 销售成本, \
Sum(商品排行.发生金额) AS 销售金额, \
Sum(商品排行.利润) AS 利润 ,\
IIf((SELECT Sum(商品排行.发生金额) FROM 商品排行 %s)=0,0,Sum(商品排行.发生金额)/(SELECT Sum(商品排行.发生金额) FROM 商品排行 %s)) AS 销售比重, \
IIf((SELECT Sum(商品排行.利润) FROM 商品排行 %s)=0,0,Sum(商品排行.利润)/(SELECT Sum(商品排行.利润) FROM 商品排行 %s)) AS 利润比重 \
FROM 商品排行 \
%s \
GROUP BY 商品排行.商品ID, 商品排行.商品编码, 商品排行.商品名称",where,where,where,where,where);
