public function actionExport()
{
/* DELETED CUSTOMER */
$cusDelDataProvider = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\tSELECT a.UPDATED_BY AS DELETE_BY,a.UPDATED_AT AS DELETE_AT,a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS=3 AND (a.UPDATED_AT BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 7 day) AND CURRENT_DATE())\r\n\t\t\t\tORDER BY a.UPDATED_AT DESC\t\r\n\t\t\t")->queryAll()]);
$aryCusDelDataProvider = $cusDelDataProvider->allModels;
/* UPDATE CUSTOMER */
$cusEditDataProvider = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\tSELECT a.UPDATED_BY AS UPDATE_BY,a.UPDATED_AT AS UPDATE_AT,a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS<>3 AND (a.UPDATED_AT BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 7 day) AND CURRENT_DATE())\r\n\t\t\t\tORDER BY a.UPDATED_AT DESC\t\r\n\t\t\t")->queryAll()]);
$aryCusEditDataProvider = $cusEditDataProvider->allModels;
/* NEW CUSTOMER */
$cusNewDataProvider = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\tSELECT a.CREATED_BY AS CREATE_BY,a.CREATED_AT AS CREATE_AT,a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS<>3 AND (a.CREATED_AT BETWEEN DATE_SUB(CURRENT_DATE(),INTERVAL 7 day) AND CURRENT_DATE())\r\n\t\t\t\tORDER BY a.CREATED_AT DESC\t\r\n\t\t\t")->queryAll()]);
$aryCusNewDataProvider = $cusNewDataProvider->allModels;
/* ALL CUSTOMER */
$cusAllDataProvider = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\tSELECT a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS<>3\r\n\t\t\t\tORDER BY a.CUST_NM ASC\t\t\r\n\t\t\t")->queryAll()]);
$aryCusAllDataProvider = $cusAllDataProvider->allModels;
/* 22=NKA*/
$cusDataProviderNKA = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\tSELECT a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS<>3 AND a.CUST_KTG='1' AND a.CUST_TYPE='22' #AND CUST_KD<>CUST_GRP\r\n\t\t\t\tORDER BY a.CUST_NM ASC\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 a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS <>3 AND a.CUST_KTG='1' AND a.CUST_TYPE='15' #AND CUST_KD<>CUST_GRP\r\n\t\t\t\tORDER BY a.CUST_NM ASC\t\t\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 a.CUST_KD,\r\n\t\t\t\t\t\t(CASE WHEN g.KD_ALIAS!='' THEN g.KD_ALIAS ELSE 'Kosong' END) as DIST_ID,\r\n\t\t\t\t\t\ta.CUST_NM,\r\n\t\t\t\t\t\t(CASE WHEN b.CUST_KTG_NM!='' THEN b.CUST_KTG_NM ELSE 'Kosong' END) as TYPE_NM,\t\t\r\n\t\t\t\t\t\t(CASE WHEN f.LAYER_NM!='' THEN f.LAYER_NM ELSE 'Kosong' END) as LAYER_GRADE,\r\n\t\t\t\t\t\t(CASE WHEN e.GEO_NM!='' THEN CONCAT(e.GEO_NM,'-',e.GEO_DCRIP) ELSE 'Kosong' END) as GEO_MAINTAIN,\r\n\t\t\t\t\t\t(CASE WHEN a.ALAMAT!='' THEN a.ALAMAT ELSE 'Kosong' END) as ALAMAT,\r\n\t\t\t\t\t\t(CASE WHEN c.PROVINCE!='' THEN c.PROVINCE ELSE 'Kosong' END) as PROVINCE,\r\n\t\t\t\t\t\t(CASE WHEN d.CITY_NAME!='' THEN d.CITY_NAME ELSE 'Kosong' END) as CITY_NAME,\r\n\t\t\t\t\t\t(CASE WHEN d.POSTAL_CODE!='' THEN d.POSTAL_CODE ELSE 'Kosong' END) as POSTAL_CODE,\r\n\t\t\t\t\t\t(CASE WHEN a.TLP1!='' THEN a.TLP1 ELSE 'Kosong' END) as PHONE,\r\n\t\t\t\t\t\t(CASE WHEN a.PIC!='' THEN a.PIC ELSE 'Kosong' END) as CP\r\n\t\t\t\tFROM c0001 a LEFT JOIN c0001k b ON b.CUST_KTG=a.CUST_TYPE\r\n\t\t\t\tLEFT JOIN c0001g1 c on c.PROVINCE_ID=a.PROVINCE_ID\r\n\t\t\t\tLEFT JOIN c0001g2 d on d.CITY_ID=a.CITY_ID\r\n\t\t\t\tLEFT JOIN c0002scdl_geo e on e.GEO_ID=a.GEO\r\n\t\t\t\tLEFT JOIN c0002scdl_layer f on f.LAYER_ID=a.LAYER\r\n\t\t\t\tLEFT JOIN c0002 g ON g.KD_CUSTOMERS=a.CUST_KD\r\n\t\t\t\tWHERE a.STATUS <>3 AND a.CUST_TYPE not in ('15','22') \r\n\t\t\t\tORDER BY a.CUST_NM ASC\t\r\n\t\t\t")->queryAll()]);
$aryCusDataProviderOTHER = $cusDataProvideOTHER->allModels;
/*SOURCE ALL CUSTOMER*/
$excel_dataAll = Postman4ExcelBehavior::excelDataFormat($aryCusAllDataProvider);
//$excel_title = $excel_dataNKA['excel_title'];
$excel_ceilsAll = $excel_dataAll['excel_ceils'];
/*SOURCE NKA CUSTOMER*/
$excel_dataNKA = Postman4ExcelBehavior::excelDataFormat($aryCusDataProviderNKA);
//$excel_title = $excel_dataNKA['excel_title'];
$excel_ceilsNKA = $excel_dataNKA['excel_ceils'];
/*SOURCE MTI CUSTOMER*/
$excel_dataMTI = Postman4ExcelBehavior::excelDataFormat($aryCusDataProviderMTI);
$excel_ceilsMTI = $excel_dataMTI['excel_ceils'];
/*SOURCE UNKNOWN CUSTOMER*/
$excel_dataOTHER = Postman4ExcelBehavior::excelDataFormat($aryCusDataProviderOTHER);
$excel_ceilsOTHER = $excel_dataOTHER['excel_ceils'];
/*SOURCE DELETE CUSTOMER*/
$excel_dataDEL = Postman4ExcelBehavior::excelDataFormat($aryCusDelDataProvider);
$excel_ceilsDEL = $excel_dataDEL['excel_ceils'];
/*SOURCE UPDATE CUSTOMER*/
$excel_dataEDIT = Postman4ExcelBehavior::excelDataFormat($aryCusEditDataProvider);
$excel_ceilsEDIT = $excel_dataEDIT['excel_ceils'];
/*SOURCE NEW CUSTOMER*/
$excel_dataNEW = Postman4ExcelBehavior::excelDataFormat($aryCusNewDataProvider);
$excel_ceilsNEW = $excel_dataNEW['excel_ceils'];
$excel_content = [['sheet_name' => 'ALL CUSTOMER', 'sheet_title' => [['CUST_ID', 'DIST_ID', 'CUST_NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsAll, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'left'], 'TYPE' => ['align' => 'left'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'left'], 'ALAMAT' => ['align' => 'left'], 'PROVINSI' => ['align' => 'left'], 'KOTA' => ['align' => 'left'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'left'], 'CONTACT.PERSON' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'MODERN-NKA', 'sheet_title' => [['CUST_ID', 'DIST_ID', 'CUST_NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsNKA, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'left'], 'TYPE' => ['align' => 'left'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'left'], 'ALAMAT' => ['align' => 'left'], 'PROVINSI' => ['align' => 'left'], 'KOTA' => ['align' => 'left'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'left'], 'CONTACT.PERSON' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'MODERN-MTI', 'sheet_title' => [['CUST_ID', 'DIST_ID', 'CUST_NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsMTI, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'left'], 'TYPE' => ['align' => 'left'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'left'], 'ALAMAT' => ['align' => 'left'], 'PROVINSI' => ['align' => 'left'], 'KOTA' => ['align' => 'left'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'left'], 'CONTACT.PERSON' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'UNKNOWN', 'sheet_title' => [['CUST_ID', 'DIST_ID', 'CUST_NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsOTHER, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['CUST_ID' => ['align' => 'center'], 'DIST_ID' => ['align' => 'center'], 'CUST_NM' => ['align' => 'left'], 'TYPE' => ['align' => 'left'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'left'], 'ALAMAT' => ['align' => 'left'], 'PROVINSI' => ['align' => 'left'], 'KOTA' => ['align' => 'left'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'left'], 'CONTACT.PERSON' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'DELETE', 'sheet_title' => [['DELETE.BY', 'DELETE.AT', 'CUST.ID', 'DIST.ID', 'CUST.NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsDEL, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['DELETE.BY' => ['align' => 'center'], 'DELETE.AT' => ['align' => 'center'], 'CUST.ID' => ['align' => 'center'], 'DIST.ID' => ['align' => 'center'], 'CUST.NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['DELETE.BY' => ['align' => 'center'], 'DELETE.AT' => ['align' => 'center'], 'CUST.ID' => ['align' => 'center'], 'DIST.ID' => ['align' => 'center'], 'CUST.NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'EDITING', 'sheet_title' => [['UPDATE.BY', 'UPDATE.AT', 'CUST.ID', 'DIST.ID', 'CUST.NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsEDIT, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['UPDATE.BY' => ['align' => 'center'], 'UPDATE.AT' => ['align' => 'center'], 'CUST.ID' => ['align' => 'center'], 'DIST.ID' => ['align' => 'center'], 'CUST.NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'contentStyle' => [['UPDATE.BY' => ['align' => 'center'], 'UPDATE.AT' => ['align' => 'center'], 'CUST.ID' => ['align' => 'center'], 'DIST.ID' => ['align' => 'center'], 'CUST.NM' => ['align' => 'center'], 'TYPE' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'GEO.MAINTAIN' => ['align' => 'center'], 'ALAMAT' => ['align' => 'center'], 'PROVINSI' => ['align' => 'center'], 'KOTA' => ['align' => 'center'], 'KODE.POS' => ['align' => 'center'], 'PHONE' => ['align' => 'center'], 'CONTACT.PERSON' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'ADD NEW', 'sheet_title' => [['CREATE.BY', 'CREATE.AT', 'CUST.ID', 'DIST.ID', 'CUST.NM', 'TYPE', 'LAYER', 'GEO.MAINTAIN', 'ALAMAT', 'PROVINSI', 'KOTA', 'KODE.POS', 'PHONE', 'CONTACT.PERSON']], 'ceils' => $excel_ceilsNEW, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['CREATE.AT' => Postman4ExcelBehavior::getCssClass('header'), 'CREATE.BY' => Postman4ExcelBehavior::getCssClass('header'), 'CUST_KD' => Postman4ExcelBehavior::getCssClass('header'), 'DIST_ID' => Postman4ExcelBehavior::getCssClass('header'), 'CUST_NM' => Postman4ExcelBehavior::getCssClass('header'), 'TYPE_NM' => Postman4ExcelBehavior::getCssClass('header'), 'LAYER_GRADE' => Postman4ExcelBehavior::getCssClass('header'), 'GEO_MAINTAIN' => Postman4ExcelBehavior::getCssClass('header'), 'ALAMAT' => Postman4ExcelBehavior::getCssClass('header'), 'PROVINCE' => Postman4ExcelBehavior::getCssClass('header'), 'CITY_NAME' => Postman4ExcelBehavior::getCssClass('header'), 'POSTAL_CODE' => Postman4ExcelBehavior::getCssClass('header'), 'PHONE' => Postman4ExcelBehavior::getCssClass('header'), 'CP' => Postman4ExcelBehavior::getCssClass('header')], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")]];
$tglIn = date("Y-m-d");
$excel_file = "PostmanCustomer" . "-" . $tglIn;
$this->export4excel($excel_content, $excel_file, 0);
}
public function actionExport_format()
{
$DataProviderFormat = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\n\t\t\t\t\t#CALL ESM_SALES_IMPORT_format()\n\t\t\t\t\tSELECT DATE,CUST_KD,CUST_NM,SKU_ID,SKU_NM,QTY_PCS,DIS_REF,STATUS FROM so_t2_format WHERE STATUS='stock-gudang';\n\t\t\t ")->queryAll(), '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();
$excel_data = Postman4ExcelBehavior::excelDataFormat($aryDataProviderFormat);
$excel_title = $excel_data['excel_title'];
$excel_ceils = $excel_data['excel_ceils'];
$excel_content = [['sheet_name' => 'STOCK-SALESPO', 'sheet_title' => [['DATE', 'CUST_KD', 'CUST_NM', 'SKU_ID', 'SKU_NM', 'QTY_PCS', 'DIS_REF', 'STATUS']], 'ceils' => $excel_ceils, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['DATE' => ['align' => 'center'], 'CUST_KD' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'SKU_ID' => ['align' => 'center'], 'SKU_NM' => ['align' => 'center'], 'QTY_PCS' => ['align' => 'center'], 'DIS_REF' => ['align' => 'center'], 'STATUS' => ['align' => 'center']]], 'contentStyle' => [['DATE' => ['align' => 'center'], 'CUST_KD' => ['align' => 'left'], 'CUST_NM' => ['align' => 'left'], 'SKU_ID' => ['align' => 'left'], 'SKU_NM' => ['align' => 'left'], 'QTY_PCS' => ['align' => 'right'], 'DIS_REF' => ['align' => 'left'], 'STATUS' => ['align' => 'center', 'color-font' => 'ee4343']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'CATATAN', 'sheet_title' => ["CATATAN IMPORT DATA STOCK SALES-PO"], 'ceils' => [["1.Pastikan format sesuai dengan yang sudah di download."], ["2.Format yang tidak boleh di ganti:"], [" A. NAMA SHEET1: STOCK-GUDANG "], [" B. NAMA HEADER COLUMN : DATE,CUST_KD,CUST_NM,SKU_ID,SKU_NM,QTY_PCS,DIS_REF"], ["3.Refrensi."], [" 'Sheet1 adalah data yang akan di import,sedangkan Sheet2 hanya berupa catatan format"], [" 'DATE'= Tanggal dari data stok yang akan di import "], [" 'CUST_KD'= Kode dari customer, tambahkan/edit jika kode alias customer berlum ada, sesuaikan dengan kode customer pada distributor"], [" 'CUST_NM'= Nama dari customer"], [" 'SKU_ID'= Kode dari Prodak Item ESM, tambahkan/edit jika kode alias customer berlum ada, sesuaikan dengan kode customer pada distributor "], [" 'SKU_NM'= Nama dari Prodak Item"], [" 'QTY_PCS'= Quantity dalam unit PCS "], [" 'DIS_REF'= Kode dari pendistribusian, contoh pendistribusian ke Distributor, Subdisk, Agen dan lain-lain"], ["4.Refrensi Kode."], [" 'DIS.001'= PT. Cahaya Inti Putra Sejahtera"], [" 'stock-gudang'= Import data Gudang"]]]];
$excel_file = "ImportFormat-Salespo";
$this->export4excel($excel_content, $excel_file, 0);
}
public function actionExportDataInvestasi($id)
{
$searchModelExport = new RtdetailSearch();
$dataProviderInvestasi = $searchModelExport->searchExportInvestasi(Yii::$app->request->queryParams, $id);
$investasi = $dataProviderInvestasi->getModels();
$data = ArrayHelper::toArray($investasi, ['lukisongroup\\purchasing\\models\\data_term\\Rtdetail' => ['PERIODE_START', 'PERIODE_END', 'nmcus', 'nminvest', 'hrga', 'PPN', 'PPH23', 'totals', 'INVESTASI_PROGRAM', 'NOMER_INVOCE', 'NOMER_FAKTURPAJAK']]);
$excel_data = Postman4ExcelBehavior::excelDataFormat($data);
$excel_title = $excel_data['excel_title'];
$excel_ceils = $excel_data['excel_ceils'];
$excel_content = [['sheet_name' => 'Investasi', 'sheet_title' => [['Periode Start', 'Periode end', 'Nama Toko', 'Nama Investasi', 'Harga', 'PPN', 'PPH23', 'TOTAL', 'Keterangan', 'Nomer Invoce', 'Nomer Faktur']], 'ceils' => $excel_ceils, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['PERIODE_START' => ['align' => 'center'], 'PERIODE_END' => ['align' => 'center'], 'nmcus' => ['align' => 'center'], 'nminvest' => ['align' => 'center'], 'HARGA' => ['align' => 'center'], 'PPN' => ['align' => 'center'], 'PPH23' => ['align' => 'center'], 'totals' => ['align' => 'center'], 'INVESTASI_PROGRAM' => ['align' => 'center'], 'NOMER_INVOCE' => ['align' => 'center'], 'NOMER_FAKTURPAJAK' => ['align' => 'center']]], 'contentStyle' => [['PERIODE_START' => ['align' => 'left'], 'PERIODE_END' => ['align' => 'left'], 'nmcus' => ['align' => 'center'], 'nminvest' => ['align' => 'center'], 'HARGA' => ['align' => 'center'], 'PPN' => ['align' => 'center'], 'PPH23' => ['align' => 'center'], 'totals' => ['align' => 'right'], 'INVESTASI_PROGRAM' => ['align' => 'center'], 'NOMER_INVOCE' => ['align' => 'center'], 'NOMER_FAKTURPAJAK' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")]];
$excel_file = "Data Investasi";
$this->export4excel($excel_content, $excel_file, 0);
}
public function actionExport()
{
$tglIn = date("Y-m-d");
//'2016-09-01';
/* HEADER/FIELD MONTHLY SCHADULE*/
$tanggalOfMonth = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("Call ERP_CUSTOMER_VISIT_ACTUAL_SchaduleReportHeader('" . $tglIn . "')")->queryAll()]);
//FIELD TGL & PEKAN
$attributeHeader = $tanggalOfMonth->allModels;
foreach ($attributeHeader as $key => $value) {
$cntArray[] = $value['Pekan'];
}
$counts = array_count_values($cntArray);
//get count of value array
//headeStyle1
$attHeaderName = ['Sales', 'Bulan'];
$headerStyleDinamik1a[] = ['Sales' => ['align' => 'center', 'color-font' => 'FFFFFF', 'color-background' => '519CC6', 'merge' => '0,2', 'width' => '20'], 'Bulan' => ['align' => 'center', 'color-font' => 'FFFFFF', 'color-background' => '519CC6', 'merge' => '0,2', 'width' => '20']];
//SPLIT TGL BY PRKAN
$checkPekan = '';
foreach ($attributeHeader as $key => $value) {
if ($checkPekan != $value['Pekan']) {
$mergeVal = $counts[$value['Pekan']] - 1 . ',1';
$attHeader1Dinamik[] = 'Pekan-' . $value['Pekan'];
//Color Backgroud - week genap/ganjil
if ($value['Pekan'] % 2 == 0) {
$warnaBelakang = 'FFFF99';
} elseif ($value['Pekan'] % 2 != 0) {
$warnaBelakang = 'CCFF99';
}
//headeStyle1 Row1
$headerStyleDinamik1b[] = ['Pekan-' . $value['Pekan'] => ['align' => 'center', 'color-font' => '000000', 'color-background' => $warnaBelakang, 'merge' => $mergeVal, 'width' => '20']];
$checkPekan = $value['Pekan'];
} else {
$attHeader1Dinamik[] = $value['Pekan'] . '-' . $key;
}
$val = explode(".", $value['TGL']);
$attHeader2[] = $val[1] . " " . $val[0] . "";
//headeStyle2 Row2
$headerStyleDinamik2[] = [$val[1] . " " . $val[0] => ['align' => 'center', 'color-font' => 'FFFFFF', 'color-background' => '519CC6', 'width' => '20']];
//Content Style - Array
$contentStyleDinamik[] = [$val[1] . " " . $val[0] => ['align' => 'center', 'color-font' => '0000FF']];
}
//PUT HRADAER ROW1 & ROW2
$titleHeader1 = array_merge($attHeaderName, $attHeader1Dinamik);
$titleHeader2 = array_merge($attHeaderName, $attHeader2);
//headeStyle1- Array Position parent from chaild array
foreach ($headerStyleDinamik1b as $key => $val) {
$headerStyleDinamik1a[0] = array_merge($headerStyleDinamik1a[0], $headerStyleDinamik1b[$key]);
}
//headeStyle2
$headerStyleDinamik2a[] = [];
foreach ($headerStyleDinamik2 as $key => $val) {
$headerStyleDinamik2a = array_merge($headerStyleDinamik2a, $headerStyleDinamik2[$key]);
}
//HeadeStyle - Marge Array
$headerStyleMarge = array_merge($headerStyleDinamik1a, [$headerStyleDinamik2a]);
//Content Style Array Postition
$contentStyleDinamikA[] = [];
foreach ($contentStyleDinamik as $key => $val) {
$contentStyleDinamikA = array_merge($contentStyleDinamikA, $contentStyleDinamik[$key]);
}
// print_r($contentStyleDinamikA);
// die();
//row Data
$dataScdlOfMonth = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("Call ERP_CUSTOMER_VISIT_ACTUAL_SchaduleReport('" . $tglIn . "')")->queryAll()]);
$schaduleProvider = $dataScdlOfMonth->allModels;
$excelScdlData = Postman4ExcelBehavior::excelDataFormat($schaduleProvider);
$excel_ceilsScdl = $excelScdlData['excel_ceils'];
/* GROUP SCHADULE*/
$schaduleGrpData = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("call ERP_CUSTOMER_VISIT_ACTUAL_SchaduleReportList('" . $tglIn . "')")->queryAll()]);
$schaduleGrpProvider = $schaduleGrpData->allModels;
$excelScdlGrpData = Postman4ExcelBehavior::excelDataFormat($schaduleGrpProvider);
$excel_ceilsScdlGrp = $excelScdlGrpData['excel_ceils'];
/* USER SALESMAN */
$userData = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("\t\t\t\t\r\n\t\t\t\tSELECT b.ID_USER,a.username,b.NM_FIRST, b.NM_MIDDLE, b.KTP,ALAMAT, b.GENDER, b.EMAIL, b.HP AS HANDPHONE, b.TLP_HOME \r\n\t\t\t\tFROM dbm001.user a LEFT JOIN dbm_086.user_profile b on b.ID_USER=a.id\r\n\t\t\t\tWHERE a.POSITION_SITE='CRM' AND a.POSITION_LOGIN=1 AND a.POSITION_ACCESS=2 AND a.status=10\r\n\t\t\t")->queryAll()]);
$usrProvider = $userData->allModels;
$excelUserData = Postman4ExcelBehavior::excelDataFormat($usrProvider);
$excel_ceilsUser = $excelUserData['excel_ceils'];
$excel_content = [['sheet_name' => 'SALES MD', 'sheet_title' => ['ID', 'USERNAME', 'NAMA DEPAN', 'NAMA BELAKANG', 'KTP', 'ALAMAT', 'GENDER', 'EMAIL', 'PHONE', 'TLP_HOME'], 'ceils' => $excel_ceilsUser, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['ID' => Postman4ExcelBehavior::getCssClass('header'), 'username' => Postman4ExcelBehavior::getCssClass('header'), 'NM_FIRST' => Postman4ExcelBehavior::getCssClass('header'), 'NM_MIDDLE' => Postman4ExcelBehavior::getCssClass('header'), 'KTP' => Postman4ExcelBehavior::getCssClass('header'), 'ALAMAT' => Postman4ExcelBehavior::getCssClass('header'), 'GENDER' => Postman4ExcelBehavior::getCssClass('header'), 'EMAIL' => Postman4ExcelBehavior::getCssClass('header'), 'PHONE ' => Postman4ExcelBehavior::getCssClass('header'), 'TLP_HOME' => Postman4ExcelBehavior::getCssClass('header')], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'LIST SCHADULE GROUP', 'sheet_title' => [['USER_NM', 'TGL', 'HARI', 'LAYER', 'PEKAN', 'GRP_NM ', 'GRP_DCRP', 'CUST_ID', 'CUST_NM', 'PROVINSI', 'KOTA', 'KODE POS', 'LAT', 'LAG']], 'ceils' => $excel_ceilsScdlGrp, 'freezePane' => 'B2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['USER_NM' => ['align' => 'center'], 'TGL' => ['align' => 'center'], 'LAYER' => ['align' => 'center']]], 'contentStyle' => [['TGL' => ['align' => 'center'], 'LAYER' => ['align' => 'center'], 'PEKAN' => ['align' => 'center'], 'GRP_DCRP' => ['align' => 'center'], 'CUST_ID' => ['align' => 'center'], 'KODE POS' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'WEEKLY SCHEDULE OF THE MONTH', 'sheet_title' => [$titleHeader1, $titleHeader2], 'ceils' => $excel_ceilsScdl, 'freezePane' => 'C3', 'headerStyle' => $headerStyleMarge, 'contentStyle' => [$contentStyleDinamikA], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")]];
$excel_file = "PostmanSalesSchadule" . "-" . $tglIn;
$this->export4excel($excel_content, $excel_file, 0);
}
public function actionExport()
{
//Rpt Of [selasa=2;minggu=0]
$x = date('N', strtotime(date("Y-m-d")));
if ($x != 2 or $x != 7) {
$tglIn = date("Y-m-d");
//'2016-09-07';
//$tglIn='2016-10-27';
//DAILY REPORT INVENTORY SALES
$dailySalesReport = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\t\r\n\t\t\t\t\t# x1 = Schadule Detail; x2 = Sales Order; x3 = customer;x4 = barang; x5 = Visit Memo; x6 = Alias customer; x7 = Alias barang; x8 = USER PROFILE\r\n\t\t\t\t\tSELECT x1.TGL,\r\n\t\t\t\tx8.NM_FIRST,\r\n\t\t\t\tx1.CUST_ID AS CUST_ID_ESM,\r\n\t\t\t\tx6.KD_ALIAS AS CUST_ID_DISTRIBUTOR,\r\n\t\t\t\tx1.CUST_NM,\r\n\t\t\t\tx2.KD_BARANG AS KD_BARANG_ESM, \r\n\t\t\t\tx7.KD_ALIAS As ID_BARANG_DISTRIBUTOR,\r\n\t\t\t\tx4.NM_BARANG, \r\n\t\t\t\t\t\tSUM(CASE WHEN x2.SO_TYPE=5 THEN (CASE WHEN x2.SO_QTY<>-1 THEN x2.SO_QTY ELSE 0 END) ELSE 0 END) as STOCK,\r\n\t\t\t\t\t\tSUM(CASE WHEN x2.SO_TYPE=8 THEN (CASE WHEN x2.SO_QTY<>-1 THEN x2.SO_QTY ELSE 0 END) ELSE 0 END) as RETURN_INV,\r\n\t\t\t\t\t\tSUM(CASE WHEN x2.SO_TYPE=9 THEN (CASE WHEN x2.SO_QTY<>-1 THEN x2.SO_QTY ELSE 0 END) ELSE 0 END) as REQUEST_INV,\r\n\t\t\t\t\t\tSUM(CASE WHEN x2.SO_TYPE=6 THEN (CASE WHEN x2.SO_QTY<>-1 THEN x2.SO_QTY ELSE 0 END) ELSE 0 END) as SELL_IN,\r\n\t\t\t\t\t\tSUM(CASE WHEN x2.SO_TYPE=7 THEN (CASE WHEN x2.SO_QTY<>-1 THEN x2.SO_QTY ELSE 0 END) ELSE 0 END) as SELL_OUT,\t\t\t\t\r\n\t\t\t\t\t\tx5.ISI_MESSAGES\r\n\t\t\t\t\t\tFROM c0002rpt_cc_time x1 INNER JOIN so_t2 x2 ON x2.TGL=x1.TGL AND x2.CUST_KD=x1.CUST_ID \r\n\t\t\t\t\t\tLEFT JOIN b0001 x4 on x4.KD_BARANG=x2.KD_BARANG\t\t\r\n\t\t\t\t\t\tLEFT JOIN c0014 x5 on x5.TGL=x1.TGL AND x5.KD_CUSTOMER=x1.CUST_ID AND x5.ID_USER=x1.USER_ID\r\n\t\t\t\t\t\tLEFT JOIN c0002 x6 on x6.KD_CUSTOMERS=x1.CUST_ID\r\n\t\t\t\t\t\tLEFT JOIN b0002 x7 on x7.KD_BARANG=x2.KD_BARANG\r\n\t\t\t\t\t\tLEFT JOIN dbm_086.user_profile x8 on x8.ID_USER=x1.USER_ID\r\n\t\t\t\t\t\tWHERE x1.TGL='" . $tglIn . "' AND x1.USER_ID NOT IN ('61','62')\r\n\t\t\t\t\t\tGROUP BY x1.CUST_ID,x2.KD_BARANG\t\r\n\t\t\t\t\t\tORDER BY x1.USER_ID,x1.CUST_ID\r\n\t\t\t\t")->queryAll()]);
$apDailySalesReport = $dailySalesReport->allModels;
$excel_DailySalesReport = Postman4ExcelBehavior::excelDataFormat($apDailySalesReport);
$excel_ceilsDailySalesReport = $excel_DailySalesReport['excel_ceils'];
// DAILY REPORT SALES QUALITY TIME
$dailySalesQt = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\t\t#call ERP_CUSTOMER_VISIT_CRONJOB_kwalitas_time('ALL_HEAD2','55','2016-08-18')\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t\tTGL,SALES_NM,\r\n\t\t\t\t\t\tCUST_ID,CUST_NM,\t\r\n\t\t\t\t\t\tABSEN_MASUK,ABSEN_KELUAR,\t\r\n\t\t\t\t\t\tCUST_CHKIN AS CHEKIN, CUST_CHKOUT AS CHECKOUT,LIVE_TIME AS VISIT_TIME, JRK_TEMPUH AS DISTANCE,\r\n\t\t\t\t\t\t(CASE WHEN STS=0 THEN 'CC' ELSE (CASE WHEN STATUS_EC>0 THEN 'EC' ELSE 'AC' END) END) as STATUS,\r\n\t\t\t\t\t\tSCDL_GRP_NM AS AREA,\r\n\t\t\t\t\t\t(CASE WHEN STS_CASE=0 THEN 'PLAN' ELSE 'CASE' END) as SCDL_EVENT,\r\n\t\t\t\t\t\t(CASE WHEN STS_CASE_PIC='' THEN 'SYSTEM' ELSE STS_CASE_PIC END) AS AUTHORIZED \r\n\t\t\t\t\tFROM c0002rpt_cc_time WHERE TGL='" . $tglIn . "' AND USER_ID NOT IN ('61','62')\r\n\t\t\t\t\tORDER BY USER_ID,CUST_CHKIN,CUST_CHKOUT\r\n\t\t\t\t")->queryAll()]);
$apSalesQT = $dailySalesQt->allModels;
$excel_DailySalesQT = Postman4ExcelBehavior::excelDataFormat($apSalesQT);
$excel_ceilsSalesQT = $excel_DailySalesQT['excel_ceils'];
// DAILY REPORT EXPIRED DATE
$dailySalesExpired = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\t\tSELECT \r\n\t\t\t\t\t\t\tDATE_FORMAT(x1.TGL_KJG,'%Y-%m-%d') AS TGL_KJG,\r\n\t\t\t\t\t\t\tx6.SALES_NM,\r\n\t\t\t\t\t\t\tx1.CUST_ID,\r\n\t\t\t\t\t\t\t#x4.KD_ALIAS,\r\n\t\t\t\t\t\t\tx3.CUST_NM,\r\n\t\t\t\t\t\t\tx1.BRG_ID,x5.KD_ALIAS,x2.NM_BARANG,\r\n\t\t\t\t\t\t\tx1.QTY,x1.DATE_EXPIRED\r\n\t\t\t\t\tFROM c0012 x1\r\n\t\t\t\t\tLEFT JOIN b0001 x2 on x2.KD_BARANG=x1.BRG_ID\r\n\t\t\t\t\tLEFT JOIN c0001 x3 on x3.CUST_KD=x1.CUST_ID\r\n\t\t\t\t\tLEFT JOIN c0002 x4 on x4.KD_CUSTOMERS=x1.CUST_ID\r\n\t\t\t\t\tLEFT JOIN b0002 x5 on x5.KD_BARANG=x1.BRG_ID\r\n\t\t\t\t\tLEFT JOIN (\tSELECT u1.id as USER_ID, u1.username as USER_NM, u2.NM_FIRST as SALES_NM\r\n\t\t\t\t\t\t\t\t\tFROM dbm001.user u1 \r\n\t\t\t\t\t\t\t\t\tLEFT JOIN dbm_086.user_profile u2 on u2.ID_USER=u1.id\r\n\t\t\t\t\t\t\t\t\tWHERE u1.POSITION_SITE='CRM' AND u1.POSITION_LOGIN=1 AND u1.POSITION_ACCESS=2\r\n\t\t\t\t\t\t\t\t) x6 on x6.USER_ID=x1.USER_ID\r\n\t\t\t\t\tWHERE x1.TGL_KJG like '" . $tglIn . "%' AND x1.USER_ID NOT IN ('61','62')\r\n\t\t\t\t\tGROUP BY x1.DATE_EXPIRED,x1.BRG_ID,x1.CUST_ID\r\n\t\t\t\t\tORDER BY x1.USER_ID,x1.CUST_ID,x1.BRG_ID,x1.DATE_EXPIRED\r\n\t\t\t\t")->queryAll()]);
$apSalesExpired = $dailySalesExpired->allModels;
$excel_DailySalesExpired = Postman4ExcelBehavior::excelDataFormat($apSalesExpired);
$excel_ceilsSalesExpired = $excel_DailySalesExpired['excel_ceils'];
$excel_content = [['sheet_name' => 'DAILY REPORT INVENTORY', 'sheet_title' => [['TGL', 'SALESMD', 'CUST_ID_ESM', 'CUST_ID_DIST', 'CUST_NM', 'KD_BARANG_ESM', 'ID_BARANG_DIST', 'NM_BARANG', 'STOCK/Pcs', 'RETURE/Pcs', 'REQUEST.ORDER/Pcs', 'SELL.IN/Pcs', 'SELL.OUT/Pcs', 'NOTE']], 'ceils' => $excel_ceilsDailySalesReport, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['TGL' => ['align' => 'center'], 'SALESMD' => ['align' => 'center'], 'CUST_ID_ESM' => ['align' => 'center'], 'CUST_ID_DIST' => ['align' => 'center'], 'CUST_NM' => ['align' => 'center'], 'KD_BARANG_ESM' => ['align' => 'center'], 'ID_BARANG_DIST' => ['align' => 'center'], 'NM_BARANG' => ['align' => 'center'], 'STOCK/Pcs' => ['align' => 'center'], 'RETURE/Pcs' => ['align' => 'center'], 'REQUEST.ORDER/Pcs' => ['align' => 'center'], 'SELL.IN/Pcs' => ['align' => 'center'], 'SELL.OUT/Pcs' => ['align' => 'center'], 'NOTE' => ['align' => 'center']]], 'contentStyle' => [['TGL' => ['align' => 'center'], 'SALESMD' => ['align' => 'left'], 'CUST_ID_ESM' => ['align' => 'center'], 'CUST_ID_DIST' => ['align' => 'center'], 'CUST_NM' => ['align' => 'left'], 'KD_BARANG_ESM' => ['align' => 'center'], 'ID_BARANG_DIST' => ['align' => 'center'], 'NM_BARANG' => ['align' => 'left'], 'STOCK/Pcs' => ['align' => 'right'], 'RETURE/Pcs' => ['align' => 'right'], 'REQUEST.ORDER/Pcs' => ['align' => 'right'], 'SELL.IN/Pcs' => ['align' => 'right'], 'SELL.OUT/Pcs' => ['align' => 'right'], 'NOTE' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'DAILY REPORT QUALITY TIME', 'sheet_title' => [['DATE', 'SALES.NAME', 'CUSTOMERS.ID', 'CUSTOMERS', 'START.ABSENSI', 'END.ABSENSI', 'CHECK.IN', 'CHECK.OUT', 'VISIT.TIME', 'DISTANCE', 'STATUS', 'AREA', 'SCDL.EVENT', 'SCDL.AUTHORIZED']], 'ceils' => $excel_ceilsSalesQT, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['DATE' => ['align' => 'center'], 'SALES.NAME' => ['align' => 'center'], 'CUSTOMERS.ID' => ['align' => 'center'], 'CUSTOMERS' => ['align' => 'center'], 'START.ABSENSI' => ['align' => 'center'], 'END.ABSENSI' => ['align' => 'center'], 'CHECK.IN' => ['align' => 'center'], 'CHECK.OUT' => ['align' => 'center'], 'VISIT.TIME' => ['align' => 'center'], 'DISTANCE' => ['align' => 'center'], 'STATUS' => ['align' => 'center'], 'AREA' => ['align' => 'center'], 'SCDL.EVENT' => ['align' => 'center'], 'SCDL.AUTHORIZED' => ['align' => 'center']]], 'contentStyle' => [['DATE' => ['align' => 'center'], 'SALES.NAME' => ['align' => 'left'], 'CUSTOMERS.ID' => ['align' => 'center'], 'CUSTOMERS' => ['align' => 'left'], 'START.ABSENSI' => ['align' => 'center'], 'END.ABSENSI' => ['align' => 'center'], 'CHECK.IN' => ['align' => 'center'], 'CHECK.OUT' => ['align' => 'center'], 'VISIT.TIME' => ['align' => 'center'], 'DISTANCE' => ['align' => 'center'], 'STATUS' => ['align' => 'center'], 'AREA' => ['align' => 'left'], 'SCDL.EVENT' => ['align' => 'center'], 'SCDL.AUTHORIZED' => ['align' => 'left']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")], ['sheet_name' => 'DAILY REPORT EXPIRED DATE', 'sheet_title' => [['DATE', 'SALES.NAME', 'CUSTOMERS.ID', 'CUSTOMERS', 'KD_BARANG_ESM', 'ID_BARANG_DIST', 'NM_BARANG', 'QTY', 'DATE_EXPIRED']], 'ceils' => $excel_ceilsSalesExpired, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['DATE' => ['align' => 'center'], 'SALES.NAME' => ['align' => 'center'], 'CUSTOMERS.ID' => ['align' => 'center'], 'CUSTOMERS' => ['align' => 'center'], 'KD_BARANG_ESM' => ['align' => 'center'], 'ID_BARANG_DIST' => ['align' => 'center'], 'NM_BARANG' => ['align' => 'center'], 'QTY' => ['align' => 'center'], 'DATE_EXPIRED' => ['align' => 'center']]], 'contentStyle' => [['DATE' => ['align' => 'center'], 'SALES.NAME' => ['align' => 'left'], 'CUSTOMERS.ID' => ['align' => 'center'], 'CUSTOMERS' => ['align' => 'left'], 'KD_BARANG_ESM' => ['align' => 'center'], 'ID_BARANG_DIST' => ['align' => 'center'], 'NM_BARANG' => ['align' => 'left'], 'QTY' => ['align' => 'right'], 'DATE_EXPIRED' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")]];
$excel_file = "PostmanDailySalesMd" . "-" . $tglIn;
$this->export4excel($excel_content, $excel_file, 0);
/**
* EXPORT IMAGE64 & NAME FROM ARRAY DATA PROVIDER
* SET ARRAY
* @author Piter Novian [ptr.nov@gmail.com]
*/
$imgKosong = "/9j/4AAQSkZJRgABAQAAAQABAAD/2wCEAAkGBxAQDxAPERIUFBUVFxcPFxQUFRAUFxMUFRUWFhYXFRUYHSggGBooGxQVITEhJSkrLi4uFx8zODMsNygtLisBCgoKBQUFDgUFDisZExkrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrK//AABEIAMAAwAMBIgACEQEDEQH/xAAbAAEAAgMBAQAAAAAAAAAAAAAABQYBAgQDB//EAEAQAAIBAgMEBwQGCAcBAAAAAAABAgMRBAUhEjFRcQYTIkFhkdGBobHBMjNCUnKSFSM0U4PD8PEUQ2KywtLhFv/EABQBAQAAAAAAAAAAAAAAAAAAAAD/xAAUEQEAAAAAAAAAAAAAAAAAAAAA/9oADAMBAAIRAxEAPwD6IAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAADB2YXLpz1+iuL+SA5DanSlL6Kb5JsnsPltOO9bT8fQ7ErAV6GWVX9m3No9Vk9TjH3+hOgCDeT1OMff6HlPK6q7k+TRYQBVatGUfpRa5o0LaclfLqc+6z4x0AroO3E5ZOGq7S8N/kcIGQAAAAAAAAAAAAA2p03JqKV2xSpuTUYq7ZYsFg40lxb3sDwwWWRhrLtS9yJAAAAAAAAAAAAABxY3L41NVpLjx5naAKrXoyg9mSszQs2Kw0akbS9j4FdxFCVOWzL+6A8wAAAAAAAACRybDbUnN7o7vFgd+W4Pq43f0nv8ADwO0AAAAAAAAAAAAAAAAAAc2OwqqRt3rc/E6QBU5RabT3rQwS2dYbdUXJ/JkSAAAAAAEr6ews+FoqEIx4e995CZTS2qq8O15biwgAAAAAGtSajFye5Jt8kRFLpLhZSUVJ6u2sZJa8WSWN+qqfhl8GfMoU3JSaV9lXfgrpfMD6Vj8dToR26jaV9nRN6+wYDGwrw26bbV7aprVcynYvNOuwSpyfbhOK/FGzs/k/YTHRitsYGpP7rnLyVwJDMs7o0HsybcvuxV2ufA4qXS3Dt2cakfFqL+DZXMiwixOJSqNta1JcZe3mWDpHktFUJVIRUJQV9O9aJpgT9GtGcVOLTT1TR6FR6D4l7VWl3WVReDvZ+d15FuAAAAAANKsFKLi9zVir1YOMnF707FrILO6Vqil95e9f0gI8AAAABL5DDScuUfm/kSxH5Iv1XNskAAAAAADwxv1VT8Mvgym9DqalWnGSunTaa4ptF3nFSTi9zVnyZx4LKaNGW1ThZ22b3b09oFEzjL3h6rpvVb4vjHu9pZ+itJTwU4PdJzj5qxL47L6VfZ6yO1bdvVr8jfB4OFGOxTVle9tXqBQsBXng8TecdY3jJcU+9e5kxn3SOnVoulSTblo21ay4eLLJjMBSrW6yClbc3vXJnLSyHCxd1ST53a8mBE9CsFKKnXkrKSUY+KTu3y3eRaTCRkAAAAAAEbnlO9NPg/j/SJI5M1V6M/Y/eBXQAAAAE/k31K5v4ncR2Ry/VtcH8kSIA862IhC23KMb7tppX8z0Kr063UOc/hECw/pCj+9p/nh6nRGSaundcUUXA5AquGddVLPtOzSa7N++/gb9DsVJV+rT7Mk213JrW6AuP8AjKW1s9ZC97W2o3vwtc9alSMVtSaS4tpLzZ8+qTUcc5Sdkq12+CU9WWDpBm2HqYapCFSMpO1kr8UBPUcRCd9icZW37LTtfde3I2q1YwW1KSiuLaSKr0E34j+H/MIzPMXPEYl01uUuqgu699lv2vvAuCzvCt266PDezuhNNJppp6prVMrNfojBUnszk6iV9bWk+Fu7zI/ohj5QrKje8Z93CVr3QFvePor/ADaf54eo/SFD97T/ADw9SuZl0XhGFWr1krpSqWsvF2IbIstWJqODk42jtXSv3pd/MD6DRrwmrwlGS3Xi0/geOKzGjSdqlSMXwb18iJnQ/R+FquEnJtqzaWjdo/IgMgyr/F1JucnaNnJ75Scr975PUC6YbM6FV2hUjJ8L6+TOsonSLJVhnCcG3GTtrvjJarVcvcWXo1jpV8OpS1lF7DfG1rPyaAljlzL6mfL5nUcWbytRl42XvAr4AAAACUyKp2px4pPy/uTJWcBW2KkZd258mWYAVXp1uoc5/wDEtRGZ1lEcVsXm47N3ok73t6AVXL8nxNeinCfYbfZc5JaP7u4seQ5EsM3OUtqbVrrdFeHqd2VYFUKSpJuSTbu1be7nYB86xFFTxsoPdKs4vk5EznXR6jRoTqR2rq1ru61Z3f8Azcev6/rJX2+ttZWve9iUzLBqvSlSbttd613MCu9BN+I/h/zCGxaeHxkm19Gp1nOLltfBlwyXJo4XrLTctvZ3pK2ztf8AY9s0ymliEttO60Ulo16gedfPMPGk6iqRel1FPtN9ytvRUui1BzxUH9282/Zb5k0uh9O+tWduUfiTeX5fToR2aatfVve3zYGucfs9b8EvgVboV+0S/A/ii4Yuh1lOdNu20nG/C5GZPkMcNNzU3K8dmzSXen8gNulNBzws7b42n7E9fcQPQ/MYUpVIVGo7dmm9FdXTTfdvXkXUgMZ0VoTe1Fyp+EbNexPcBH9MMyp1IwpQkpWe22ndLRpK/tJHodQccNd/bk5rlZL5GmF6J0Yu85Sn4OyXttvLBGKSSXIDJF57U7MI8W35f3JQr2bVdqq/9PZ9QOMAAAABgseW4jbpriuyyunVl2K6ud3uej9QLGDCZkAAAAAAAAAAAAAAAAAAAPDGV+rg5eXPuKy2d2bYrblsrdH3vvZwgAAAAAAAAS2U47dTk/wv5EuVImMtzK9oT37k+PMCVAAAAAAAAAAAAAAAAI3NcbsrYjve98F6mcxzFQ7MdZf7f/SDbvqwAAAAAAAAAAAGDIA78FmcodmXaj716k1QrRmrxd/67yrGac3F3i2nxQFsBCUM4ktJq/itGd9LMqUvtW56AdgNYzT3NPk0zYAAYlJLVtLmBkHLVzClH7V+WpwV84b+hG3i/QCWq1FFXk7Ih8Zmrl2YaLj3vlwI+rVlN3k234moAAAAAAAAAAAAAAAAAAADBkAYPRVprdKXmzQAbuvP70vzM0YAAAAAAAAAAAAAAB//2Q==";
//==DATA ROWs
$aryDataImg = new ArrayDataProvider(['allModels' => Yii::$app->db_esm->createCommand("\r\n\t\t\t\t\t\tSELECT x1.TGL,x1.USER_ID,x1.USER_NM,x1.SALES_NM,x1.CUST_NM ,x1.IMG_DECODE_START,x1.IMG_DECODE_END\r\n\t\t\t\t\t\tFROM c0002rpt_cc_time x1 \r\n\t\t\t\t\t\tWHERE x1.TGL='" . $tglIn . "' AND x1.USER_ID NOT IN ('61','62')\r\n\t\t\t\t\t\t#GROUP BY x1.TGL,x1.USER_ID\t\t\t\t\t\r\n\t\t\t\t\t")->queryAll(), 'pagination' => ['pageSize' => 1000]]);
//print_r($aryDataImg->getModels());
//SET GROUPING USER_ID
$dataGroup = self::array_group_by($aryDataImg->getModels(), 'USER_ID');
//FOREACH GROUPING BY USER_ID
foreach ($dataGroup as $key => $nilai) {
//FIND FILTER BY KEY USER_ID
$filterDataGroup = \yii\helpers\ArrayHelper::filter($dataGroup, [$key]);
//SET OUT KEY/PARENT FILTER
$isiDataGroup = [];
foreach ($filterDataGroup as $row => $value) {
$isiDataGroup = ArrayHelper::merge($isiDataGroup, $filterDataGroup[$row]);
}
$dataRow1 = [];
$dataRow2 = [];
$aryRslt = [];
//DATA RESULT
foreach ($isiDataGroup as $row => $value) {
$img64row1 = [['img64' => $value['IMG_DECODE_START'] != '' ? $value['IMG_DECODE_START'] : $imgKosong, 'name' => $value['TGL'] . '_' . $value['CUST_NM'] . '_1START' . '-' . $value['SALES_NM'], 'tgl' => $value['TGL'], 'sales_nm' => $value['SALES_NM']]];
$img64row2 = [['img64' => $value['IMG_DECODE_END'] != '' ? $value['IMG_DECODE_END'] : $imgKosong, 'name' => $value['TGL'] . '_' . $value['CUST_NM'] . '_2END' . '-' . $value['SALES_NM'], 'tgl' => $value['TGL'], 'sales_nm' => $value['SALES_NM']]];
$dataRow1 = ArrayHelper::merge($dataRow1, $img64row1);
$dataRow2 = ArrayHelper::merge($dataRow2, $img64row2);
}
$aryRslt = ArrayHelper::merge($dataRow1, $dataRow2);
/**
* EXPORT IMAGE64 & NAME FROM ARRAY
* PUT TO FILE & PATH
* @author Piter Novian [ptr.nov@gmail.com]
*/
//$rootPathImageZip='/var/www/advanced/lukisongroup/cronjob/tmp_cronjob/img/';
//$rootPathImageZip=Yii::getAlias('@lukisongroup').'/cronjob/tmp_cronjob/img/';
$rootPathImageZip = '/var/www/backup/salesmd/photo/';
foreach ($aryRslt as $key => $value) {
$dataImgScr = $value['img64'];
$namefile = $value['name'];
$img = str_replace('data:image/jpg;base64,', '', $dataImgScr);
$img = str_replace(' ', '+', $img);
$data = base64_decode($img);
$file = $rootPathImageZip . $namefile . '.jpg';
$success = file_put_contents($file, $data);
}
/**
* IMAGE FILE TO ZIP
* FOREACH NAME FROM ARRAY
* REMOVE ALL IMAGE
* @author Piter Novian [ptr.nov@gmail.com]
*/
$zip = new \ZipArchive();
$tgl = $aryRslt[0]['tgl'];
$sales_nm = $aryRslt[0]['sales_nm'];
$zip_name = $rootPathImageZip . $tgl . '_visit_image-' . $sales_nm . '.zip';
// Zip name
$zip->open($zip_name, \ZipArchive::CREATE | \ZipArchive::OVERWRITE);
foreach ($aryRslt as $file => $value) {
$path = $rootPathImageZip . $value['name'] . '.jpg';
if (file_exists($path)) {
$zip->addFromString(basename($path), file_get_contents($path));
} else {
echo "file does not exist";
}
unlink($path);
}
$zip->close();
}
}
}
public function actionExport_datagudang()
{
$searchModelViewImport = new ImportViewSearch();
$dataProvidergudang = $searchModelViewImport->searchViewHistoryGudang(Yii::$app->request->queryParams);
$dpGudang = $dataProvidergudang->getModels();
$aryData = [];
foreach ($dpGudang as $key => $value) {
$aryData[] = ['TGL' => $value['TGL'], 'NM_BARANG' => $value['NM_BARANG'], 'SO_QTY' => $value['SO_QTY'], 'UNIT_BARANG' => $value['UNIT_BARANG'], 'kartonqty' => $value['kartonqty'], 'beratunit' => $value['beratunit'], 'HARGA_DIS' => $value['HARGA_DIS'], 'subtotaldist' => $value['subtotaldist'], 'disNm' => $value['disNm'], 'USER_ID' => $value['USER_ID']];
}
$dataProviderAllDataImport = new ArrayDataProvider(['allModels' => $aryData, 'pagination' => ['pageSize' => 10]]);
$modelDataExport = $dataProviderAllDataImport->getModels();
//print_r($modelDataExport);
$excel_data = Postman4ExcelBehavior::excelDataFormat($modelDataExport);
$excel_title = $excel_data['excel_title'];
$excel_ceils = $excel_data['excel_ceils'];
$excel_content = [['sheet_name' => 'STOCK-GUDANG', 'sheet_title' => [['DATE', 'BARANG', 'QTY_PCS', 'UNIT_BARANG', 'QTY_KARTON', 'BERAT_GRAM', 'HARGA_DIS', 'SUB_TOTAL', 'DISTRIBUTOR', 'USER_ID']], 'ceils' => $excel_ceils, 'freezePane' => 'A2', 'headerColor' => Postman4ExcelBehavior::getCssClass("header"), 'headerStyle' => [['TGL' => ['align' => 'center'], 'NM_BARANG' => ['align' => 'center'], 'SO_QTY' => ['align' => 'center'], 'UNIT_BARANG' => ['align' => 'center'], 'kartonqty' => ['align' => 'center'], 'beratunit' => ['align' => 'center'], 'HARGA_DIS' => ['align' => 'center'], 'subtotaldist' => ['align' => 'center'], 'disNm' => ['align' => 'center'], 'USER_ID' => ['align' => 'center']]], 'contentStyle' => [['DATE' => ['align' => 'center'], 'BARANG' => ['align' => 'left'], 'QTY_PCS' => ['align' => 'right'], 'UNIT_BARANG' => ['align' => 'center'], 'QTY_KARTON' => ['align' => 'right'], 'BERAT_GRAM' => ['align' => 'right'], 'HARGA_DIS' => ['align' => 'right'], 'SUB_TOTAL' => ['align' => 'right'], 'DISTRIBUTOR' => ['align' => 'left'], 'USER_ID' => ['align' => 'center']]], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::getCssClass("even")]];
$excel_file = "ImportDataGudang";
$this->export4excel($excel_content, $excel_file, 0);
}
public function actionExport_format()
{
$DataProviderFormat = new ArrayDataProvider(['key' => 'ID', 'allModels' => Yii::$app->db_esm->createCommand("CALL ESM_SALES_IMPORT_format()")->queryAll(), '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();
$excel_data = Postman4ExcelBehavior::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' => Postman4ExcelBehavior::getCssClass("header"), 'headerColumnCssClass' => ['TGL' => Postman4ExcelBehavior::getCssClass('header'), 'CUST_KD' => Postman4ExcelBehavior::getCssClass('header'), 'CUST_NM' => Postman4ExcelBehavior::getCssClass('header'), 'SKU_ID' => Postman4ExcelBehavior::getCssClass('header'), 'SKU_NM' => Postman4ExcelBehavior::getCssClass('header'), 'QTY_PCS' => Postman4ExcelBehavior::getCssClass('header'), 'DIS_REF' => Postman4ExcelBehavior::getCssClass('header')], 'oddCssClass' => Postman4ExcelBehavior::getCssClass("odd"), 'evenCssClass' => Postman4ExcelBehavior::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->export4excel($excel_content, $excel_file, 0);
}
