Can someone point me in the right direction. How would I go about constructing the following statement.
The problem is the Yii generated models use this format:
And I am not that familiar with it yet… NOOBY!
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->compare('id',$this->id);
$criteria->compare('asset_id',$this->asset_id);
$criteria->compare('type_of_maintenance',$this->type_of_maintenance,true);
$criteria->compare('scheduled_maintenance',$this->scheduled_maintenance);
$criteria->compare('unscheduled_maintenance',$this->unscheduled_maintenance);
$criteria->compare('schduled_date',$this->schduled_date,true);
$criteria->compare('completion_date',$this->completion_date,true);
$criteria->compare('downtime',$this->downtime,true);
$criteria->compare('description_of_work',$this->description_of_work,true);
$criteria->compare('company_id',$this->company_id);
return new CActiveDataProvider('Pms', array(
'criteria'=>$criteria,
));
}
SELECT asset_no,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date`, '%b' ) = 'Jan' , 1, 0 ) ) AS Jan,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Feb' , 1, 0 ) ) AS Feb,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Mar' , 1, 0 ) ) AS Mar,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Apr' , 1, 0 ) ) AS Apr,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'May' , 1, 0 ) ) AS May,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Jun' , 1, 0 ) ) AS Jun,
sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Jul' , 1, 0 ) ) AS Jul,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Aug' , 1, 0 ) ) AS Aug,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Sep' , 1, 0 ) ) AS Sep,
sum( if( `completion_date` <= `schduled_date` AND date_format( `completion_date` , '%b' ) = 'Oct' , 1, 0 ) ) AS Oct,
sum( if( `completion_date` <=`schduled_date` AND date_format(`completion_date` , '%b' ) = 'Nov' , 1, 0 ) ) AS Nov,
sum( if( `completion_date` <= `schduled_date` AND date_format(`completion_date` , '%b' ) = 'Dec' , 1, 0 ) ) AS `Dec`
FROM
assets, pms
WHERE
`completion_date` <= `schduled_date` AND `completion_date` <> '0000-00-00' AND asset_parent_id = asset_id
GROUP BY
asset_no ORDER BY `completion_date
Anyway, you can adjust the criteria until you have a good dataset returned without error/CDbException. One interesting result would be if one of the complicated statements you want to use inside the SUM() function actually will pass without error.
<?php
/**
* This is the model class for table "pms".
*
* The followings are the available columns in table 'pms':
* @property integer $id
* @property integer $asset_id
* @property string $type_of_maintenance
* @property integer $scheduled_maintenance
* @property integer $unscheduled_maintenance
* @property string $schduled_date
* @property string $completion_date
* @property string $downtime
* @property string $description_of_work
* @property integer $company_id
*
* The followings are the available model relations:
* @property Assets $asset
* @property Companies $company
*/
class Pms extends CActiveRecord
{
public $alias_1;
/**
* Returns the static model of the specified AR class.
* @return Pms the static model class
*/
public static function model($className=__CLASS__)
{
return parent::model($className);
}
/**
* @return string the associated database table name
*/
public function tableName()
{
return 'pms';
}
/**
* @return array validation rules for model attributes.
*/
public function rules()
{
// NOTE: you should only define rules for those attributes that
// will receive user inputs.
return array(
array('asset_id, type_of_maintenance, scheduled_maintenance, unscheduled_maintenance, schduled_date, completion_date, downtime, description_of_work, company_id', 'required'),
array('asset_id, scheduled_maintenance, unscheduled_maintenance, company_id', 'numerical', 'integerOnly'=>true),
array('type_of_maintenance', 'length', 'max'=>255),
array('downtime', 'length', 'max'=>77),
// The following rule is used by search().
// Please remove those attributes that should not be searched.
array('id, asset_id, type_of_maintenance, scheduled_maintenance, unscheduled_maintenance, schduled_date, completion_date, downtime, description_of_work, company_id', 'safe', 'on'=>'search'),
);
}
/**
* @return array relational rules.
*/
public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(
'asset' => array(self::BELONGS_TO, 'Assets', 'asset_id'),
'company' => array(self::BELONGS_TO, 'Companies', 'company_id'),
);
}
/**
* @return array customized attribute labels (name=>label)
*/
public function attributeLabels()
{
return array(
'id' => 'ID',
'asset_id' => 'Asset',
'type_of_maintenance' => 'Type Of Maintenance',
'scheduled_maintenance' => 'Scheduled Maintenance',
'unscheduled_maintenance' => 'Unscheduled Maintenance',
'schduled_date' => 'Schduled Date',
'completion_date' => 'Completion Date',
'downtime' => 'Downtime',
'description_of_work' => 'Description Of Work',
'company_id' => 'Company',
'alias_1' => 'alias_1',
);
}
/**
* Retrieves a list of models based on the current search/filter conditions.
* @return CActiveDataProvider the data provider that can return the models based on the search/filter conditions.
*/
public function search()
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria=new CDbCriteria;
$criteria->select =array('id,asset_id,type_of_maintenance as alias_1,scheduled_maintenance,unscheduled_maintenance,schduled_date,completion_date,downtime,description_of_work,company_id');
/* $criteria->compare('id',$this->id);
$criteria->compare('asset_id',$this->asset_id);
$criteria->compare('type_of_maintenance',$this->type_of_maintenance,true);
$criteria->compare('scheduled_maintenance',$this->scheduled_maintenance);
$criteria->compare('unscheduled_maintenance',$this->unscheduled_maintenance);
$criteria->compare('schduled_date',$this->schduled_date,true);
$criteria->compare('completion_date',$this->completion_date,true);
$criteria->compare('downtime',$this->downtime,true);
$criteria->compare('description_of_work',$this->description_of_work,true);
$criteria->compare('company_id',$this->company_id); */
$criteria->group = 'id';
$result = pms::model()->find($criteria);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
));
}
}
Now this works for CGridView but no not CDetail or Anywhere else
Here is grid view or admin.php
NOTE: When I say works I have narrowed my code down to just working on getting the alias to work so I am using a known field (type_of_maintenance) and putting it to and alias called alias_1 and like I said it works in the gridview but nowhere else. Could you help?
Hint: how do you pass $data to view, $model to _view?
I guess you will have to study how the Gii generated CRUD really works, especially when search() is used or not, and when data is loaded from the controller e.g. loadModel().
There’s not very much documentation available covering the Zii widgets and their use in the CRUD code. And for customization you’ll have to search the forum for clues. You may also want to have a look at the new Yii Answers site.
I assume you already found the class reference and the guide. I’m not very familiar with the Blog Demo Tutorial (I think it has been updated since the original 1.0 edition) nor the other tutorials.
Hopefully you’ll soon have a good understanding of the different actions, views and their interaction with models. When asking for help, please tell us a little more about which of the use cases (list/view/update/admin) you are refering to.