CREATE TABLE `customer` (
`id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`last_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
...
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
CREATE TABLE `product` (
`id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`code` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`model_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`description` TEXT NULL COLLATE 'utf8_unicode_ci',
...
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
CREATE TABLE `order` (
`id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
`date` DATETIME NULL DEFAULT NULL,
`customer_id` INT(12) UNSIGNED NULL DEFAULT NULL,
`user_id` INT(12) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `FK_customer_id` (`customer_id`),
CONSTRAINT `FK_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
CREATE TABLE `order_item` (
`id` INT(12) UNSIGNED NOT NULL AUTO_INCREMENT,
`order_id` INT(12) UNSIGNED NULL DEFAULT NULL,
`product_id` INT(12) UNSIGNED NULL DEFAULT NULL,
`serial_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`lot_number` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`color` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`quantity` INT(12) UNSIGNED NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE INDEX `IDX_order_item` (`order_id`, `product_id`),
CONSTRAINT `FK_order_id` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`),
CONSTRAINT `FK_item_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)
)
COLLATE='utf8_unicode_ci', ENGINE=InnoDB, AUTO_INCREMENT=1;
== Models ==
Model ''@app/models/Order'':
class Order extends \yii\db\ActiveRecord
{
// Model constants
const MAX_EMPTY_RECS = 5;
...
public function getOrderItems()
{
// Order has_many OrderItem via OrderItem.order_id -> id
return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
}
public function getProducts()
{
// Order has_many Product via Product.id -> OrderItem->product_id and OrderItem.order_id -> id
return $this->hasMany(Product::className(), ['id' => 'product_id'])->via('orderItems');
}
public function getOrderItemsToUpdate()
{
return $this->getOrderItems();
}
public function getOrderItemsToCreate($emptyItems=-1)
{
$emptyItems = ( ($emptyItems < 0) ? self::MAX_EMPTY_RECS : $emptyItems);
$emptyOrderItems = array();
for($i=0; $i<$emptyItems; $i++) {
$emptyOrderItems[] = new OrderItem();
}
return $emptyOrderItems;
}
}
Model ''@app/models/OrderItem'':
class OrderItem extends \yii\db\ActiveRecord
{
...
public function getOrder()
{
// OrderItem has_one Order via Order.id -> order_id
return $this->hasOne(Customer::className(), ['id' => 'order_id']);
}
public function getProduct()
{
// OrderItem has_many Product via Product.product_id -> id
return $this->hasOne(Product::className(), ['id' => 'product_id']);
}
}
== Controllers ==
Controller ''@app/controllers/OrderController'':
use yii\data\ActiveDataProvider;
use yii\base\Model;
use app\models\Order;
use app\models\OrderItem;
class OrderController extends Controller
{
...
/**
* Displays a single Order model.
* @param string $id
* @return mixed
*/
public function actionView($id)
{
$dataProvider = new ActiveDataProvider([
'query' => OrderItem::find()->where(['order_id' => $id])->joinWith('product'),
'pagination' => [
'pageSize' => 20,
],
]);
/**
* Setup your sorting attributes
* Note: This is setup before the $this->load($params) statement below
*/
$dataProvider->setSort([
'attributes' => [
'serial_number',
'lot_number',
'color',
'product_name' => [
'asc' => ['product.name' => SORT_ASC],
'desc' => ['product.name' => SORT_DESC],
'label' => 'Product'
],
'product_code' => [
'asc' => ['product.code' => SORT_ASC],
'desc' => ['product.code' => SORT_DESC],
'label' => 'Product Code'
],
'product_model' => [
'asc' => ['product.model_number' => SORT_ASC],
'desc' => ['product.model_number' => SORT_DESC],
'label' => 'Product Model'
],
'upc' => [
'asc' => ['product.upc' => SORT_ASC],
'desc' => ['product.upc' => SORT_DESC],
'label' => 'UPC'
],
]
]);
return $this->render('view', [
'model' => $this->findModel($id),
'dataProvider'=> $dataProvider
]);
}
/**
* Creates a new Order model.
* If creation is successful, the browser will be redirected to the 'view' page.
* @return mixed
*/
public function actionCreate()
{
$model = new Order();
$orderItems = $model->getOrderItemsToCreate(); // empty items
if ($model->load(Yii::$app->request->post()) && $model->save()) {
// Save order (parent), then order items (children)
if (Model::loadMultiple($orderItems, Yii::$app->request->post()) &&
Model::validateMultiple($orderItems))
{
foreach($orderItems as $item) {
if (!empty($item['serial_number'])) {
$item['order_id'] = $model->id;
$item->save();
}
}
return $this->redirect(['view', 'id' => $model->id]);
}
} else {
return $this->render('create', [
'model' => $model,
'orderItems' => $orderItems,
'colors' => $model->getColors(),
'products' => $model->getProductList(),
]);
}
}
/**
* Updates an existing Order model.
* If update is successful, the browser will be redirected to the 'view' page.
* @param string $id
* @return mixed
*/
public function actionUpdate($id)
{
$model = $this->findModel($id);
$orderItems = $model->orderItemsToUpdate; // existing items
$emptyItems = (Order::MAX_EMPTY_RECS - count($orderItems));
if ($emptyItems > 0) {
// Append empty items if necessary, in case new items need to be added to current order
$orderItems = array_merge($orderItems, $model->getOrderItemsToCreate($emptyItems));
}
if ($model->load(Yii::$app->request->post()) && $model->save()) {
// Save order (parent), then order items (children)
if (Model::loadMultiple($orderItems, Yii::$app->request->post()) &&
Model::validateMultiple($orderItems))
{
foreach($orderItems as $item) {
if (!empty($item['serial_number'])) {
$item['order_id'] = $model->id;
$item->save();
}
}
return $this->redirect(['view', 'id' => $model->id]);
}
} else {
return $this->render('update', [
'model' => $model,
'orderItems' => $orderItems,
'colors' => $model->getColors(),
'products' => $model->getProductList(),
]);
}
}
}
== Views ==
View ''@app/views/order/_form.php'':
= $form->field($model, 'date')->textInput(['value' => Yii::$app->formatter->asDate('now', 'php:Y-m-d')]) ?>
= $form->field($model, 'customer_id')->label('Customer')
->dropDownList(ArrayHelper::map(Customer::find()->all(), 'id', 'fullname')) ?>
= Html::activeHiddenInput($model, 'user_id', ["value" => Yii::$app->user->getId()]) ?>
isNewRecord): ?>
Product
Serial Number
Lot Number
Color
0): ?>
$item): ?>
isNewRecord): ?>
= $form->field($item,"[$i]product_id")->label('')->dropDownList(
ArrayHelper::map($model->productList, 'id', 'name'),
['prompt'=>'--Select One--'] // options
) ?>
= $form->field($item,"[$i]serial_number")->label(''); ?>
= $form->field($item,"[$i]lot_number")->label(''); ?>
= $form->field($item,"[$i]color")->label('')->dropDownList(
$colors,
['prompt'=>'--Select One--'] // options
) ?>
N/A
00000000
N/A
None
0
= Html::submitButton($model->isNewRecord ?
Yii::t('app', 'Save') :
Yii::t('app', 'Update'), ['class' => $model->isNewRecord ? 'btn btn-success' : 'btn btn-primary']
) ?>
View ''@app/views/order/create'' and also same in ''@app/views/order/update'':
...
= $this->render('_form', [
'model' => $model,
'orderItems' => $orderItems, // <--- important
'colors' => $colors,
'products' => $products,
]) ?>
View ''@app/views/order/view'':
use yii\widgets\DetailView;
use yii\grid\GridView;
...
Order
= DetailView::widget([
'model' => $model,
'attributes' => [
[
'label' => 'Date',
'value' => substr($model->date, 0, 10),
],
[
'label' => 'Customer',
'attribute' => 'customer.fullname'
],
'customer.company_name',
[
'label' => 'Address',
'value' => $model->customer->address . ', ' . $model->customer->city . ', ' .
($model->customer->state_prov != 'N/A' ? $model->customer->state_prov : '') . ' ' .
$model->customer->postal_code . ', ' . $model->customer->country
],
'customer.account_number',
],
]) ?>
Order Items
= GridView::widget([
'dataProvider' => $dataProvider,
'columns' => [
['class' => 'yii\grid\SerialColumn'],
[
'attribute' => 'product_name',
'value' => 'product.name',
'label' => 'Product',
],
[
'attribute' => 'product_code',
'value' => 'product.code',
'label' => 'Product Code',
],
[
'attribute' => 'product_model',
'value' => 'product.model_number',
'label' => 'Model',
],
'serial_number',
'lot_number',
[
'attribute' => 'product_upc',
'value' => 'product.upc',
'label' => 'UPC',
],
'color',
],
]) ?>