public function getOrders()
{
/**
* 第一个参数为要关联的字表模型类名称，
*第二个参数指定 通过子表的 customer_id 去关联主表的 id 字段
*/
//return $this->hasMany(Orders::className(), ['action_user' => 'id']);
return $this->hasMany(Orders::className(), ['action_user' => 'id'])->inverseOf('customer');
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Orders::find();
$dataProvider = new ActiveDataProvider(['query' => $query]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id, 'firm_id' => $this->firm_id, 'price_with_vat' => $this->price_with_vat, 'price_without_vat' => $this->price_without_vat, 'increment_price' => $this->increment_price, 'total_goods' => $this->total_goods, 'total_types' => $this->total_types, 'created_at' => $this->created_at, 'updated_at' => $this->updated_at]);
return $dataProvider;
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = Orders::find()->select('orders.*')->leftJoin('orders_item', 'orders_item.order_id = orders.id')->leftJoin('reservationinfo', 'reservationinfo.id = orders_item.reservationinfo_id')->leftJoin('objreservation', 'objreservation.id = reservationinfo.objreservation_id')->leftJoin('customers', 'objreservation.customer_id = customers.id')->leftJoin('user', 'customers.user_id = user.id')->where(['user.id' => Yii::$app->user->id]);
// $query = Orders::find();
// add conditions that should always apply here
$dataProvider = new ActiveDataProvider(['query' => $query]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to return any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
// grid filtering conditions
$query->andFilterWhere(['id' => $this->id, 'consumer_id' => $this->consumer_id, 'qty' => $this->qty, 'sum' => $this->sum, 'paid' => $this->paid, 'order_status_id' => $this->order_status_id, 'created_at' => $this->created_at, 'updated_at' => $this->updated_at]);
$query->andFilterWhere(['like', 'comment', $this->comment]);
return $dataProvider;
}
/**
* Поиск размещенных заказов
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function searchForfile($params)
{
if (Yii::$app->user->can('operatorSQL')) {
$query = Orders::find()->where(['orders.status' => 'Размещен']);
} else {
throw new ForbiddenHttpException('Только оператор базы и администратор могут выгружать данные');
}
$dataProvider = new ActiveDataProvider(['query' => $query]);
$this->load($params);
if (!$this->validate()) {
// uncomment the following line if you do not want to any records when validation fails
// $query->where('0=1');
return $dataProvider;
}
$query->joinWith('customersCustomer');
if (Yii::$app->user->can('admin')) {
$query->joinWith('usersUser');
}
$query->andFilterWhere(['order_id' => $this->order_id, 'order_timestamp' => $this->order_timestamp, 'order_amount' => $this->order_amount])->andFilterWhere(['like', 'customers.customer_name', $this->customers_customer_id])->andFilterWhere(['like', 'user.username', $this->user_id])->andFilterWhere(['like', 'orders.status', $this->status]);
return $dataProvider;
}
/**
* @return \yii\db\ActiveQuery
*/
public function getOrders()
{
return $this->hasMany(Orders::className(), ['objreservation_id' => 'id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getOrders()
{
return $this->hasMany(Orders::className(), ['customers_customer_id' => 'customer_id']);
}
public function actionCreateExcel()
{
define('EOL', PHP_SAPI == 'cli' ? PHP_EOL : '<br />');
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("PHPExcel Test Document")->setSubject("PHPExcel Test Document")->setDescription("Test document for PHPExcel, generated using PHP classes.")->setKeywords("office PHPExcel php")->setCategory("Test result file");
//设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
//设置工作簿默认的样式
$objPHPExcel->getDefaultStyle()->getFont()->setName('宋体');
//设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle('Nagoya');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15.75);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15.75);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15.4);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(13.75);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(11.15);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(14.13);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(8.25);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(8.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(8.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(8.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(8.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(8.5);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(16.75);
$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'IMPRESSION France');
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('D1:F1');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Chiffre d\'affaires en');
//$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('G1:H1');
$objPHPExcel->getActiveSheet()->setCellValue('G1', '10-2014');
//$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle('G1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('A3:A4');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'Date Facture');
//$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('B3:B4');
$objPHPExcel->getActiveSheet()->setCellValue('B3', 'Nom de Client');
//$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setSize(11);
$objPHPExcel->getActiveSheet()->getStyle('B3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('B3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('C3:C4');
$objPHPExcel->getActiveSheet()->setCellValue('C3', 'Numéro Facture');
//$objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setSize(9);
$objPHPExcel->getActiveSheet()->getStyle('C3')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('C3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('D3:F3');
$objPHPExcel->getActiveSheet()->setCellValue('D3', 'HT € ');
$objPHPExcel->getActiveSheet()->getStyle('D3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('D3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('D4', 'Ventes hors UE');
$objPHPExcel->getActiveSheet()->setCellValue('E4', 'Ventes UE');
$objPHPExcel->getActiveSheet()->setCellValue('F4', 'Vente en Fr');
//$objPHPExcel->getActiveSheet()->getStyle('D4')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('D4')->getFont()->setSize(8);
//$objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setSize(8);
//$objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setSize(8);
$objPHPExcel->getActiveSheet()->getStyle('D4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('F4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('G3:G4');
$objPHPExcel->getActiveSheet()->setCellValue('G3', 'TVA €');
$objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('H3:H4');
$objPHPExcel->getActiveSheet()->setCellValue('H3', 'TTC €');
$objPHPExcel->getActiveSheet()->getStyle('H3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('H3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('I3:L3');
$objPHPExcel->getActiveSheet()->setCellValue('I3', 'Mode de règlement');
$objPHPExcel->getActiveSheet()->getStyle('I3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('I3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->setCellValue('I4', 'CHQ');
$objPHPExcel->getActiveSheet()->setCellValue('J4', 'VER');
$objPHPExcel->getActiveSheet()->setCellValue('K4', 'TRAIT');
$objPHPExcel->getActiveSheet()->setCellValue('L4', 'ESP');
$objPHPExcel->getActiveSheet()->getStyle('I4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('J4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('K4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('L4')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->mergeCells('M3:M4');
$objPHPExcel->getActiveSheet()->setCellValue('M3', 'Date Règlement');
$objPHPExcel->getActiveSheet()->getStyle('M3')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('M3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//数据库数据
$data = Yii::$app->request->get();
$firstday = isset($data['firstday']) ? $data['firstday'] : 0;
$lastday = isset($data['lastday']) ? $data['lastday'] : 0;
if ($firstday != 0 && $lastday != 0) {
$orders = Orders::find()->where('created_at >= :start AND created_at < :end AND fac_no != 0 ', [':start' => $firstday, ':end' => $lastday])->all();
} else {
echo "参数错误";
exit;
}
foreach ($orders as $i => $order) {
$j = $i + 5;
if ($order->remise > 0) {
$remise_html = "(remise-" . $order->remise . "%)";
$price = $order->sum_price * (100 - $order->remise) / 100;
} else {
$remise_html = "";
$price = $order->sum_price;
}
$price = $order->type == 1 ? $price : '-' . $price;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $j, Date('Y/m/d', $order->created_at));
$objPHPExcel->getActiveSheet()->setCellValue('B' . $j, $order->customer->username);
$objPHPExcel->getActiveSheet()->setCellValue('C' . $j, $order->fac_no);
$objPHPExcel->getActiveSheet()->setCellValue('D' . $j, '');
$objPHPExcel->getActiveSheet()->setCellValue('F' . $j, $price . $remise_html);
$objPHPExcel->getActiveSheet()->setCellValue('G' . $j, Helper::getTVA($price) . $remise_html);
$objPHPExcel->getActiveSheet()->setCellValue('H' . $j, Helper::getTTC($price) . $remise_html);
/*$objPHPExcel->getActiveSheet()->getStyle('A' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('B' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('C' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('D' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('E' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('F' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('G' . $j)->getFont()->setName('宋体');
$objPHPExcel->getActiveSheet()->getStyle('H' . $j)->getFont()->setName('宋体');*/
}
/*$objWriteHTML = new \PHPExcel_Writer_HTML($objPHPExcel); //输出网页格式的对象
$objWriteHTML->save("php://output");
exit;*/
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', Date("Y", $firstday) . "_" . Date("m", $firstday) . '.xlsx', __FILE__));
echo "Success !<br>";
echo "The EXCEL FILE is in :" . dirname(__FILE__);
exit;
}
/**
* Finds the Orders model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param string $id
* @return Orders the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = Orders::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
/**
* Deletes an existing Listofgoods model.
* If deletion is successful, the browser will be redirected to the 'index' page.
* @param integer $id
* @return mixed
*/
public function actionDelete($id)
{
$model = $this->findModel($id);
$order_id = $model->orders_order_id;
$order = Orders::findOne($order_id);
$amount = $order->order_amount / 100;
$price = $model->goodsGoodId->good_price / 100;
$count = $model->good_count;
$amount2 = $amount - $price * $count;
$order->order_amount = $amount2 * 100;
$order->save();
$model->delete();
return $this->redirect(['orders/view', 'id' => $order_id]);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getOrders()
{
return $this->hasMany(Orders::className(), ['order_status_id' => 'id']);
}
/**
* Ajax
*/
public function actionSubmittedOrdersByAjax()
{
if (Yii::$app->request->isAjax) {
/* Yii::$app->request->post() = {goodsID : quantity} */
$goodsIdList = Yii::$app->request->post();
if (!empty($goodsIdList)) {
$orderID = Orders::saveOrder($goodsIdList);
OrderItems::saveOrderItems($orderID, $goodsIdList);
$link = \Yii::$app->homeUrl . '/index.php?r=orders/view&id=' . $orderID;
$data = array('status' => 'ok', 'link' => $link);
echo \yii\helpers\Json::encode($data);
} else {
$data = array('status' => 'unok');
echo \yii\helpers\Json::encode($data);
}
}
}
/**
* goodsIdList = [goodID => quantity]
* @param array $goodsIdList
* @return int $orderID
*/
public static function saveOrder($goodsIdList)
{
/* all the goods in $goodsIdList are ordered from the same firm */
$goodID = key($goodsIdList);
$firmID = Goods::findOne($goodID)->firm_id;
$totalGoods = $priceWithVat = $priceWithoutVat = $incrementPrice = 0;
foreach ($goodsIdList as $id => $quantity) {
$totalGoods += $quantity;
$good = Goods::findOne($id);
$priceWithVat += $good->price_with_vat * $quantity;
$priceWithoutVat += $good->price_without_vat * $quantity;
$incrementPrice += $good->increment_price * $quantity;
}
$order = new Orders();
$order->firm_id = $firmID;
$order->price_with_vat = $priceWithVat;
$order->price_without_vat = $priceWithoutVat;
$order->increment_price = $incrementPrice;
$order->total_types = count($goodsIdList);
$order->total_goods = $totalGoods;
$order->save();
$orderID = $order->getPrimaryKey();
return $orderID;
}
public function getOrders()
{
return $this->hasMany(Orders::className(), ['id' => 'orders_id'])->viaTable('orders_item', ['objreservation_id' => 'id'])->viaTable('objreservation', ['customer_id' => 'id'])->viaTable('customers', ['user_id' => 'id']);
}
return ['class' => 'success', 'onclick' => 'window.location.href="' . Url::toRoute(['orders/view', 'id' => $model->order_id]) . '"; return false', 'style' => 'cursor:pointer'];
} elseif ($model->status == 'Черновик') {
return ['class' => 'danger', 'onclick' => 'window.location.href="' . Url::toRoute(['orders/view', 'id' => $model->order_id]) . '"; return false', 'style' => 'cursor:pointer'];
} else {
return ['onclick' => 'window.location.href="' . Url::toRoute(['orders/view', 'id' => $model->order_id]) . '"; return false', 'style' => 'cursor:pointer'];
}
}, 'columns' => [['class' => 'yii\\grid\\SerialColumn'], 'order_id', 'order_timestamp', ['attribute' => 'user_id', 'value' => 'usersUser.fullname'], ['attribute' => 'customers_customer_id', 'value' => 'customersCustomer.customer_name'], ['attribute' => 'order_amount', 'value' => function ($model) {
return $model->order_amount / 100;
}], 'status']]);
?>
<?php
Pjax::end();
?>
<?php
//тут я тренируюсь выгружать данные в файл
$order = new Orders();
$orders = $order->find()->where(['status' => 'Размещен'])->all();
foreach ($orders as $dt) {
//print_r($dt);
$sm = new ListofgoodsSearch();
$dp = $sm->search(Yii::$app->request->queryParams, $dt['order_id']);
foreach ($dp->models as $mod) {
echo ' ' . $dt['order_id'];
echo ' ' . $dt['customersCustomer']['customer_name'];
echo ' ' . $mod['goodsGoodId']['good_name'];
$good_price = $mod['goodsGoodId']['good_price'] / 100;
echo ' ' . $good_price;
echo '<br>';
}
}
?>
