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);
}
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);
}