コード例 #1
0
 public function actionExport()
 {
     //$custDataMTI=Yii::$app->db_esm->createCommand("CALL ERP_MASTER_CUSTOMER_export('CUSTOMER_MTI')")->queryAll();
     /* 22=NKA*/
     $cusDataProviderNKA = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\tSELECT CUST_KD,CUST_NM,(SELECT CUST_KTG_NM FROM c0001k WHERE CUST_KTG=CUST_TYPE limit 1) AS TYPE_NM, ALAMAT,TLP1,PIC\r\n\t\t\t\tFROM c0001\r\n\t\t\t\tWHERE CUST_KTG='1' AND CUST_TYPE='22' #AND CUST_KD<>CUST_GRP\r\n\t\t\t\tORDER BY CUST_GRP ASC\t\t\t\r\n\t\t\t")->queryAll()]);
     $aryCusDataProviderNKA = $cusDataProviderNKA->allModels;
     /*15=MTI*/
     $cusDataProviderMTI = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\tSELECT CUST_KD,CUST_NM,(SELECT CUST_KTG_NM FROM c0001k WHERE CUST_KTG=CUST_TYPE limit 1) AS TYPE_NM, ALAMAT,TLP1,PIC\r\n\t\t\t\tFROM c0001\r\n\t\t\t\tWHERE CUST_KTG='1' AND CUST_TYPE='15' #AND CUST_KD<>CUST_GRP\r\n\t\t\t\tORDER BY CUST_GRP ASC\t\t\t\r\n\t\t\t")->queryAll()]);
     $aryCusDataProviderMTI = $cusDataProviderMTI->allModels;
     /*OTHERS*/
     $cusDataProvideOTHER = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\tSELECT CUST_KD,CUST_NM,(SELECT CUST_KTG_NM FROM c0001k WHERE CUST_KTG=CUST_TYPE limit 1) AS TYPE_NM, ALAMAT,TLP1,PIC\r\n\t\t\t\tFROM c0001\r\n\t\t\t\tWHERE CUST_KTG='1' AND CUST_TYPE<>'15' AND CUST_TYPE<>22\r\n\t\t\t\tORDER BY CUST_GRP ASC\t\t\t\r\n\t\t\t")->queryAll()]);
     $aryCusDataProviderOTHER = $cusDataProvideOTHER->allModels;
     /*SOURCE NKA*/
     $excel_dataNKA = Export2ExcelBehavior::excelDataFormat($aryCusDataProviderNKA);
     //$excel_title = $excel_dataNKA['excel_title'];
     $excel_ceilsNKA = $excel_dataNKA['excel_ceils'];
     /*SOURCE MTI*/
     $excel_dataMTI = Export2ExcelBehavior::excelDataFormat($aryCusDataProviderMTI);
     $excel_ceilsMTI = $excel_dataMTI['excel_ceils'];
     /*SOURCE OTHERS*/
     $excel_dataOTHER = Export2ExcelBehavior::excelDataFormat($aryCusDataProviderOTHER);
     $excel_ceilsOTHER = $excel_dataOTHER['excel_ceils'];
     $excel_content = [['sheet_name' => 'NKA CUSTOMER', 'sheet_title' => ['CUST_ID', 'CUST_NM', 'TYPE', 'ALAMAT', 'TLP', 'PIC'], 'ceils' => $excel_ceilsNKA, 'headerColor' => Export2ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['CUST_KD' => Export2ExcelBehavior::getCssClass('header'), 'CUST_NM' => Export2ExcelBehavior::getCssClass('header'), 'TYPE_NM' => Export2ExcelBehavior::getCssClass('header'), 'ALAMAT' => Export2ExcelBehavior::getCssClass('header'), 'TLP1' => Export2ExcelBehavior::getCssClass('header'), 'PIC' => Export2ExcelBehavior::getCssClass('header')], 'oddCssClass' => Export2ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Export2ExcelBehavior::getCssClass("even")], ['sheet_name' => 'MTI CUSTOMER', 'sheet_title' => ['CUST_ID', 'CUST_NM', 'TYPE', 'ALAMAT', 'TLP', 'PIC'], 'ceils' => $excel_ceilsMTI, 'headerColor' => Export2ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['CUST_KD' => Export2ExcelBehavior::getCssClass('header'), 'CUST_NM' => Export2ExcelBehavior::getCssClass('header'), 'TYPE_NM' => Export2ExcelBehavior::getCssClass('header'), 'ALAMAT' => Export2ExcelBehavior::getCssClass('header'), 'TLP1' => Export2ExcelBehavior::getCssClass('header'), 'PIC' => Export2ExcelBehavior::getCssClass('header')], 'oddCssClass' => Export2ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Export2ExcelBehavior::getCssClass("even")], ['sheet_name' => 'OTHERS', 'sheet_title' => ['CUST_ID', 'CUST_NM', 'TYPE', 'ALAMAT', 'TLP', 'PIC'], 'ceils' => $excel_ceilsOTHER, 'headerColor' => Export2ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['CUST_KD' => Export2ExcelBehavior::getCssClass('header'), 'CUST_NM' => Export2ExcelBehavior::getCssClass('header'), 'TYPE_NM' => Export2ExcelBehavior::getCssClass('header'), 'ALAMAT' => Export2ExcelBehavior::getCssClass('header'), 'TLP1' => Export2ExcelBehavior::getCssClass('header'), 'PIC' => Export2ExcelBehavior::getCssClass('header')], 'oddCssClass' => Export2ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Export2ExcelBehavior::getCssClass("even")]];
     $excel_file = "PostmanCustomer";
     $this->export2excel($excel_content, $excel_file);
 }
コード例 #2
0
 public function actionExport_format()
 {
     $data_format = Yii::$app->db_esm->createCommand("CALL ESM_SALES_IMPORT_format()")->queryAll();
     $DataProviderFormat = new ArrayDataProvider(['key' => 'ID', 'allModels' => $data_format, 'pagination' => ['pageSize' => 10]]);
     $aryDataProviderFormat = $DataProviderFormat->allModels;
     /* PR
      * $model->field dan $model['field']
      */
     $searchModelX = new TempDataSearch();
     $dataProviderX = $searchModelX->search(Yii::$app->request->queryParams);
     $dataProvider1 = $dataProviderX->getModels();
     $dataProvider2 = $dataProviderX->getModels();
     $dataProvider3 = $dataProviderX->getModels();
     //if (Yii::$app->request->isAjax) {
     /* echo  \moonland\phpexcel\Excel::widget([
     				'id'=>'export',
     				'isMultipleSheet' => true,
     				 'models' => [
     					'sheet1' => $dataProvider1,
     					//'sheet2' => $dataProvider2,
     					//'sheet3' => $dataProvider3
     				],
     				'mode' => 'export',
     				'fileName'=>'FORMAT IMPORT STOCK',
     				'setFirstTitle'=>true,//'IMPORT STOCK',
     				//default value as 'export'
     				'properties'=>[
     					//'sheet1.name'=>'ere'
     				],
     				'columns' => [
     					'sheet1' => [
     						[
     							'attribute'=>'TGL',
     							'header' => 'DATE',
     							'format' => 'date',
     
     						],
     						[
     							'attribute'=>'CUST_KD_ALIAS',
     							'header' => 'CUST_KD',
     							'format' => 'text',
     						],
     						[
     							'attribute'=>'ITEM_ID_ALIAS',
     							'header' => 'SKU_KD',
     							'format' => 'text',
     						],
     					],
     				],
     				// 'columns' => [
     					// 'sheet1' => [
     						// 'column1'=>'TGL',
     						// 'column2'=>'CUST_KD_ALIAS',
     						// 'column3'=>'ITEM_ID_ALIAS'
     					// ],
     					//'sheet1' => ['column1'=>'TGL','column2'=>'CUST_KD_ALIAS','column3'=>'ITEM_ID_ALIAS'],
     					//'sheet2' => ['column1'=>'TGL','column2'=>'CUST_KD_ALIAS','column3'=>'ITEM_ID_ALIAS'],
     					//'sheet3' => ['column1'=>'TGL','column2'=>'CUST_KD_ALIAS','column3'=>'ITEM_ID_ALIAS']
     				// ],
     					//without header working, because the header will be get label from attribute label.
     				//'header' => [
     					// 'sheet1' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3']
     					// 'sheet2' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3'],
     					// 'sheet3' => ['column1' => 'Header Column 1','column2' => 'Header Column 2', 'column3' => 'Header Column 3']
     				 //],
     				////'sheet1' => ['TGL','CUST_KD_ALIAS','ITEM_ID_ALIAS'],
     
     			]);	 */
     //return true;
     //return $this->redirect('index');
     //echo $data1;
     //if(Yii::$app->request->referrer){
     //			return $this->redirect(Yii::$app->request->referrer);
     //		}else{
     //			return $this->goHome();
     //		}
     //}
     $excel_data = Export2ExcelBehavior::excelDataFormat($aryDataProviderFormat);
     $excel_title = $excel_data['excel_title'];
     $excel_ceils = $excel_data['excel_ceils'];
     $excel_content = [['sheet_name' => 'IMPORT FORMAT STOCK', 'sheet_title' => ['DATE', 'CUST_KD', 'CUST_NM', 'SKU_ID', 'SKU_NM', 'QTY_PCS', 'DIS_REF'], 'ceils' => $excel_ceils, 'headerColor' => Export2ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['TGL' => Export2ExcelBehavior::getCssClass('header'), 'CUST_KD' => Export2ExcelBehavior::getCssClass('header'), 'CUST_NM' => Export2ExcelBehavior::getCssClass('header'), 'SKU_ID' => Export2ExcelBehavior::getCssClass('header'), 'SKU_NM' => Export2ExcelBehavior::getCssClass('header'), 'QTY_PCS' => Export2ExcelBehavior::getCssClass('header'), 'DIS_REF' => Export2ExcelBehavior::getCssClass('header')], 'oddCssClass' => Export2ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Export2ExcelBehavior::getCssClass("even")], ['sheet_name' => 'IMPORTANT NOTE ', 'sheet_title' => ["Important Note For Import Stock Customer"], 'ceils' => [["1.pastikan tidak merubah format hanya menambahkan data, karena import versi 1.2 masih butuhkan pengembangan validasi"], ["2.Berikut beberapa format nama yang tidak di anjurkan di ganti:"], ["  A. Nama dari Sheet1: IMPORT FORMAT STOCK "], ["  B. Nama Header seperti column : DATE,CUST_KD,CUST_NM,SKU_ID,SKU_NM,QTY_PCS,DIS_REF"], ["3.Refrensi."], ["  'IMPORT FORMAT STOCK'= Nama dari Sheet1 yang aktif untuk di import "], ["  'DATE'= Tanggal dari data stok yang akan di import "], ["  'CUST_KD'= Kode dari customer, dimana setiap customer memiliki kode sendiri sendiri sesuai yang mereka miliki "], ["  'CUST_NM'= Nama dari customer "], ["  'SKU_ID'=  Kode dari Item yang mana customer memiliku kode items yang berbeda beda "], ["  'SKU_NM'=  Nama dari Item, sebaiknya disamakan dengan nama yang dimiliki lukisongroup"], ["  'QTY_PCS'= Quantity dalam unit PCS "], ["  'DIS_REF'= Kode dari pendistribusian, contoh pendistribusian ke Distributor, Subdisk, Agen dan lain-lain"]]]];
     $excel_file = "StockImportFormat";
     $this->export2excel($excel_content, $excel_file);
 }