?>
<td class="value <?php
echo $retail > $DateBox->box_price ? 'red' : '';
?>
"><?php
echo SnapFormat::currency($retail);
?>
</td>
<?php
}
?>
<?php
}
?>
<td class="value"><strong><?php
echo SnapFormat::currency(BoxItem::dateRetail($SelectedDeliveryDate->id));
?>
</strong></td>
<td></td>
</tr>
</tfoot>
</table>
<?php
$this->endWidget();
?>
</div>
<div id="customers" class="tab-pane">
<?php
$this->beginWidget('bootstrap.widgets.BsPanel');
?>
/**
* 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;
}
/**
* 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;
}
/**
* Duplicate a Box and all its items
* @return boolean
*/
public function duplicate()
{
$newBox = new Box();
$newBox->attributes = $this->attributes;
$newBox->box_id = null;
$newBox->save();
foreach ($this->BoxItems as $BoxItem) {
$newBoxItem = new BoxItem();
$newBoxItem->attributes = $BoxItem->attributes;
$newBoxItem->box_item_id = null;
$newBoxItem->box_id = $newBox->box_id;
$newBoxItem->save();
}
return true;
}
