CDbCriteria CArrayDataProvider zii.widgets.grid （1）

商品汇总/////在数据库新建表GoodsSummary实现view得稍作改变

  /*  public function actionGoodslist() {

        $connection=Yii::app()->db;
        $table="CREATE  TABLE temtabl_e
        (
           GoodsName varchar(255),
           GoodsSn varchar(255),
           GoodsBref varchar(255),
           CreateTime int,
           ShopPrice float,
           NUM int
        )";////创建的数据库 

        set_time_limit(0);
       $connection=Yii::app()->db;
       $sqldelete="DELETE  FROM GoodsSummary";
       $command=$connection->createCommand($sqldelete);
       $command->execute();
       $connection=Yii::app()->db;
       $sqltable= " insert into   GoodsSummary
           SELECT FROM_UNIXTIME( kps_ordergoods.CreateTime,'%Y-%m-%d' )as CreateTime,
           kps_goods.GoodsName,
           kps_goods.GoodsSn,
           kps_goods.GoodsBref,
           kps_goods.ShopPrice,
           kps_ordergoods.Num
           FROM kps_ordergoods
           LEFT JOIN kps_goods ON kps_goods.GoodsId=kps_ordergoods.GoodsId GROUP BY kps_ordergoods.CreateTime ";
        $command=$connection->createCommand($sqltable);
        $command->execute();


        $criteria = new CDbCriteria();


        $StartTime = getValue("StartTime");
        $StartTime = $StartTime ? $StartTime :('1970-01-01');
        $StartTime=substr($StartTime,0,10);


        $EndTime = getValue("EndTime");
        $EndTime = $EndTime ? ($EndTime) : date('Y-m-d',time());
        $EndTime=substr($EndTime,0,10);


        $GoodsName = trim(getValue('GoodsName'));
        $GoodsSn = trim(getValue('GoodsSn'));
        $GoodsBref= trim(getValue('GoodsBref'));


        $condition='1=1';
        if ($GoodsName) {
            $condition.=' AND  GoodsName="'.$GoodsName.'"';
        }
        if ($GoodsSn) {
            $condition.=' AND   GoodsSn="'.$GoodsSn.'"';
        }
        if($GoodsBref){
            $condition.=' AND  GoodsBref="'.$GoodsBref.'"';
        }
        if ($StartTime){
            $condition.=' AND  CreateTime>="'.$StartTime.'"';
        }
        if ($EndTime){
            $condition.=' AND  CreateTime<="'.$EndTime.'"';
        }
        print_r($condition);
            $sql="SELECT CreateTime ,GoodsName  ,GoodsSn ,GoodsBref ,ShopPrice ,SUM(Num) as num,ShopPrice*SUM(Num) as totalmoney
            FROM GoodsSummary  where $condition GROUP BY CreateTime ,GoodsName limit 10000 ";
            $command=$connection->createCommand($sql);
            $num=$command->queryAll();
            $connection->active = false;
            $dataProvider = new CArrayDataProvider($num,array('id'=>'user',));
            $this->render('goodslist',array('dataProvider' => $dataProvider,) );

    }

view：

<?php
/* @var $this GoodsController */
/* @var $dataProvider CActiveDataProvider */

$this->breadcrumbs=array(
'Goods',
);
?>
<table align="left" width='100%' class='AdminMenuSys'>
    <tr>
        <td align='left'><h1>商品汇总</h1></td>
        <td align="right">
        </td>
        <td width='50'></td>
    </tr>
    <tr>
        <td colspan="3">
            <form action="" method="get">
                <?php
                echo CHtml::label('日期：', 'CreateTime');
            echo   CHtml::textField('StartTime',date('Y-m-d',$StartTime),array('class' => 'ShortTxt', 'onclick' => 'WdatePicker()','readOnly' => 'readOnly'));?>-<?php echo CHtml::textField('EndTime',date('Y-m-d',$EndTime),array('class' => 'ShortTxt', 'onclick' => 'WdatePicker()', 'readOnly' => 'readOnly'));?>
              <?php  echo CHtml::label('商品名称', 'GoodsName');?>
              <?php  echo CHtml::textField('GoodsName',$GoodsName);?>
              <?php  echo CHtml::label('货号', 'GoodsSn');?>
              <?php  echo CHtml::textField('GoodsSn',$GoodsSn);?>
              <?php  echo CHtml::label('备注', 'GoodsBref');?>
              <?php  echo CHtml::textField('GoodsBref',$GoodsBref);?>
              <?php  echo CHtml::submitButton('搜索');?>
            </form>
        </td>
    </tr>
</table>
<?php

cs()->registerScriptFile(resBu('js/DatePicker/WdatePicker.js'));
?>
<?php $this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
));

?>