/**
* Returns the data model based on the primary key given in the GET variable.
* If the data model is not found, an HTTP exception will be raised.
* @param integer the ID of the model to be loaded
*/
public function loadModel($id)
{
$model = BoxItem::model()->findByPk((int) $id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}
<td class="itemValue">
<?php
echo CHtml::textField('bc[' . $key . '][item_value]', $BoxItemsContent->item_value, array('class' => 'currency'));
?>
<?php
echo CHtml::dropDownList('bc[' . $key . '][item_unit]', $BoxItemsContent->item_unit, SnapUtil::config('boxomatic/itemUnits'));
?>
</td>
<?php
$key2 = 0;
foreach ($DeliveryDateBoxes as $DeliveryDateBoxMerged) {
$dateBoxIds = explode(',', $DeliveryDateBoxMerged->box_ids);
foreach ($dateBoxIds as $dateBoxId) {
$Box = Box::model()->findByPk($dateBoxId);
$BoxItem = BoxItem::model()->with('Box')->find('item_name=:itemName AND
supplier_id=:supplierId AND
item_unit=:itemUnit AND
item_value=:itemValue AND
Box.delivery_date_id=:dateId AND
Box.size_id=:sizeId AND
t.box_id = ' . $Box->box_id, array(':itemName' => $BoxItemsContent->item_name, ':supplierId' => $BoxItemsContent->supplier_id, ':itemUnit' => $BoxItemsContent->item_unit, ':itemValue' => $BoxItemsContent->item_value, ':dateId' => $Box->delivery_date_id, ':sizeId' => $Box->size_id));
?>
<td><?php
if ($BoxItem) {
echo CHtml::textField('bc[' . $key . '][BoxItem][' . $key2 . '][item_quantity]', $BoxItem->item_quantity, array('class' => 'decimal', 'min' => 0, 'title' => 'Retail: ' . SnapFormat::currency($BoxItem->retail_price) . ' Wholesale: ' . SnapFormat::currency($BoxItem->wholesale_price)));
echo CHtml::hiddenField('bc[' . $key . '][BoxItem][' . $key2 . '][box_item_id]', $BoxItem->box_item_id);
echo CHtml::hiddenField('bc[' . $key . '][BoxItem][' . $key2 . '][box_id]', $Box->box_id);
} else {
echo CHtml::textField('bc[' . $key . '][BoxItem][' . $key2 . '][item_quantity]', 0, array('class' => 'decimal', 'min' => 0));
echo CHtml::hiddenField('bc[' . $key . '][BoxItem][' . $key2 . '][box_id]', $Box->box_id);
/**
* Generate a packing list spreadsheet for a given date_id
*/
public function actionGenerateOrderList($date)
{
$tablePrefix = SnapUtil::config('boxomatic/tablePrefix');
$sql = '
SELECT
SUM(item_quantity) as total,
SUM(item_quantity * item_value) as total_price,
item_value,
GROUP_CONCAT(DISTINCT t.box_id ORDER BY t.box_id DESC) AS box_ids,
GROUP_CONCAT(DISTINCT `box_item_id` ORDER BY `BoxItems`.box_id DESC) as box_item_ids,
`BoxItems`.`box_item_id`,
`BoxItems`.`item_name`,
`BoxItems`.`item_unit`,
`Supplier`.`name`
FROM `' . $tablePrefix . 'boxes` `t`
LEFT OUTER JOIN `' . $tablePrefix . 'user_boxes` `UserBoxes`
ON (`UserBoxes`.`box_id`=`t`.`box_id`)
LEFT OUTER JOIN `' . $tablePrefix . 'box_items` `BoxItems`
ON (`BoxItems`.`box_id`=`t`.`box_id`)
LEFT OUTER JOIN `' . $tablePrefix . 'suppliers` `Supplier`
ON (`BoxItems`.`supplier_id`=`Supplier`.`id`)
WHERE (
delivery_date_id=' . $date . '
AND user_box_id is not null
AND
(
UserBoxes.status=' . UserBox::STATUS_APPROVED . ' OR
UserBoxes.status=' . UserBox::STATUS_DELIVERED . '
)
)
GROUP BY name,item_name
ORDER BY name;
';
$connection = Yii::app()->db;
$command = $connection->createCommand($sql);
$dataReader = $command->query();
$items = $dataReader->readAll();
if (empty($items)) {
echo 'No customer orders!';
exit;
}
$phpExcelPath = Yii::getPathOfAlias('boxomatic.external.PHPExcel');
$DateBoxes = Box::model()->with('BoxSize')->findAll(array('condition' => 'delivery_date_id = ' . $date, 'order' => 'box_size_name DESC'));
//disable Yii's Autoload because it messes with PHPExcel's autoloader
spl_autoload_unregister(array('YiiBase', 'autoload'));
include $phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->SetCellValue('A1', 'Supplier');
$objPHPExcel->getActiveSheet()->SetCellValue('B1', 'Item');
$objPHPExcel->getActiveSheet()->SetCellValue('C1', 'Total Quantity');
$objPHPExcel->getActiveSheet()->SetCellValue('D1', 'Unit');
$objPHPExcel->getActiveSheet()->SetCellValue('E1', 'Unit Price');
$objPHPExcel->getActiveSheet()->SetCellValue('F1', 'Total Price');
$alpha = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
$boxIds = explode(',', $items[0]['box_ids']);
$pos = 6;
spl_autoload_register(array('YiiBase', 'autoload'));
foreach ($DateBoxes as $n => $Box) {
$custCount = $Box->customerCount;
$objPHPExcel->getActiveSheet()->SetCellValue($alpha[$pos] . '1', $Box->BoxSize->box_size_name . ' (' . $custCount . ')');
$objPHPExcel->getActiveSheet()->getColumnDimension($alpha[$pos])->setAutoSize(true);
$pos++;
}
$objPHPExcel->getActiveSheet()->getStyle("A1:" . $alpha[$pos] . '1')->applyFromArray(array("font" => array("bold" => true)));
$row = 2;
foreach ($items as $item) {
$objPHPExcel->getActiveSheet()->SetCellValue('A' . $row, $item['name']);
$objPHPExcel->getActiveSheet()->SetCellValue('B' . $row, $item['item_name']);
$objPHPExcel->getActiveSheet()->SetCellValue('C' . $row, $item['total']);
$objPHPExcel->getActiveSheet()->SetCellValue('D' . $row, $item['item_unit']);
$objPHPExcel->getActiveSheet()->SetCellValue('E' . $row, $item['item_value']);
$objPHPExcel->getActiveSheet()->SetCellValue('F' . $row, $item['total_price']);
$boxIds = explode(',', $item['box_ids']);
$boxItemIds = explode(',', $item['box_item_ids']);
$pos = 6;
foreach ($DateBoxes as $Box) {
$BoxItem = null;
$biPos = false;
$biPos = array_search($Box->box_id, $boxIds);
if ($biPos !== false) {
$BoxItem = BoxItem::model()->findByAttributes(array('box_id' => $Box->box_id, 'box_item_id' => $boxItemIds[$biPos]));
}
$quantity = $BoxItem && !empty($BoxItem->item_quantity) ? $BoxItem->item_quantity : '0';
$objPHPExcel->getActiveSheet()->SetCellValue($alpha[$pos] . $row, $quantity);
$pos++;
}
$row++;
}
$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("E")->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension("F")->setAutoSize(true);
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Packing List');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="ordering-list-' . date('Ymd') . '"');
$objWriter->save('php://output');
exit;
}
