· 6 years ago · Oct 04, 2019, 06:20 AM
1<?php
2
3namespace App\Http\Controllers;
4
5use App\Models\CsvModel;
6use App\Models\ProductModel;
7use Illuminate\Http\Request;
8use Prestashop;
9use Mail;
10use DB;
11use Illuminate\Support\Facades\Storage;
12use League\Csv\Reader;
13use League\Csv\Statement;
14use League\Csv\Writer;
15
16
17
18
19
20
21class ProductCtl extends Controller
22{
23
24 private $product = [];
25 private $data_sycn = [];
26 private $error;
27
28 private $source_file;
29 private $email_to;
30 private $email_cc;
31 private $email_from;
32
33 private $csv_sku;
34 private $csv_price;
35 private $csv_stock;
36 private $data_db= [];
37 private $proses = false;
38 private $proses_update = 'false';
39 private $proses_insert = 'false';
40 private $proses_row;
41
42
43 public function __construct()
44 {
45 $this->source_file = 'https://kasir.net/beb3e5857e321a80d60af9d3a0d29b31/bebiburp.csv';
46 $this->email_to = 'daniel@drifesolution.com';
47 $this->email_cc = 'daniel@drife.co.id';
48 $this->email_from = 'noreplay@bebiburp.com';
49 $this->proses_row = env('PROSES_ROW',20);
50
51 ini_set('max_execution_time', 0);
52 }
53
54 public function index(){
55 return view('upload');
56 }
57
58
59 /**
60 * @return \Illuminate\Http\RedirectResponse
61 */
62 public function store(){
63 $filename = date('His').'dumpfile.csv';
64
65 $uploadedFile = tempnam(sys_get_temp_dir(), $filename);
66 $userAgent = 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13';
67
68 $ch = curl_init();
69 curl_setopt($ch, CURLOPT_URL, $this->source_file);
70 curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_ANY);
71 curl_setopt($ch, CURLOPT_USERAGENT, $userAgent);
72 curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
73 // curl_setopt($ch, CURLOPT_FILE, $uploadedFile);
74 curl_setopt($ch, CURLOPT_USERPWD, "bebisync:d60af9d3a0d29b31");
75 curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
76 // curl_setopt($ch, CURLOPT_COOKIEJAR, '-');
77 // curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_BASIC);
78
79 //This is the file where we save the information
80 // $fp = fopen ($uploadedFile, 'w+');
81 // //Here is the file we are downloading, replace spaces with %20
82 // $ch = curl_init(str_replace(" ","%20",$this->source_file));
83 // curl_setopt($ch, CURLOPT_TIMEOUT, 50);
84 // curl_setopt($ch, CURLOPT_FILE, $fp);
85 // curl_setopt($ch, CURLOPT_USERAGENT, $userAgent);
86 // curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
87
88 $output = curl_exec($ch);
89 curl_close($ch);
90 // fclose($fp);
91 $rows = explode("\n",$output);
92 $s = array();
93 $t = array();
94 $i = 1;
95 foreach($rows as $row) {
96 $s[] = str_getcsv($row);
97 }
98 foreach($s as $key) {
99 if($i>1 && count($key) == 10){
100 $t[$i-2]['sku'] = $key[0];
101 $t[$i-2]['name'] = $key[1];
102 $t[$i-2]['description'] = $key[2];
103 $t[$i-2]['model'] = $key[3];
104 $t[$i-2]['barcode'] = $key[4];
105 $t[$i-2]['weight'] = $key[5];
106 $t[$i-2]['price'] = $key[6];
107 $t[$i-2]['parent'] = $key[7];
108 $t[$i-2]['child'] = $key[8];
109 $t[$i-2]['stock'] = $key[9];
110 }
111 $i++;
112 }
113
114 if ($output){
115 $records = $t;
116 // $reader = Reader::createFromPath($uploadedFile, 'r');
117 // $reader->setHeaderOffset(0);
118 // $records = (new Statement())->process($reader);
119
120 if (count($records) > 0){
121 foreach ($records as $value) {
122 $is_valid = 1;
123 if(preg_match('(<|>|;|=|#|{|})', $value['name']) === 1) {
124 $is_valid = 0;
125 }
126 $data_csv[] = [
127 'sku' => $value['sku'],
128 'name' => $value['name'],
129 'description' => $value['description'],
130 'model' => $value['model'],
131 'barcode' => $value['barcode'],
132 'weight' => $value['weight'],
133 'price' => $value['price'],
134 'parent' => $value['parent'],
135 'child' => $value['child'],
136 'stock' => $value['stock'],
137 'is_valid' => $is_valid,
138 ];
139 }
140 foreach (array_chunk($data_csv,1000) as $data_csvs) {
141 $in = CsvModel::insert($data_csvs);
142 }
143
144 if($in){
145 $data_email = [
146 'title' => 'Logs',
147 'content' => 'Berhasil Insert data csv ke db.'
148 ];
149 $this->send_email($data_email, false, 'Log sycn data csv');
150 return $data_email['content'];
151 }else{
152 $data_email = [
153 'title' => 'Logs',
154 'content' => 'gagal Insert data csv ke db.'
155 ];
156 $this->send_email($data_email, false, 'Log sycn data csv');
157 return $data_email['content'];
158 }
159 }else{
160 $data_email = [
161 'title' => 'Logs',
162 'content' => 'Data csv kosong semua. Proses insert data ke csv ke db gagal'
163 ];
164 $this->send_email($data_email, false);
165 return $data_email['content'];
166 }
167 }else{
168 $data_email = [
169 'title' => 'Logs',
170 'content' => 'Gagal copy data to temp folder. Please check permission your folder.'
171 ];
172 $this->send_email($data_email, false);
173 return $data_email['content'];
174 }
175
176
177
178 }
179
180 public function proses()
181 {
182 $records = CsvModel::unprocessed()->take($this->proses_row)->get();
183 $record_arr = $records->toArray();
184 $i = 1;
185 if(!$records){
186 return false;
187 }
188 //Header csv
189 $hasil[0] = ['SKU','Update Stock','Update Product','Insert Product','process',];
190 foreach ($record_arr as $value) {
191 $this->csv_sku = $value['sku'];
192 $this->csv_stock = $value['stock'];
193 $this->csv_price = $value['price'];
194 $this->proses = true;
195 if ($value['is_valid'] == 1) {
196 $cek_null = $this->validate_null_data($value);
197 if ($cek_null == 'true'){
198 $this->data_db = ProductModel::where('sku','=', $this->csv_sku)->first(); //CEK SKU CSV SUDAH ADA DI ATAU BELUM DI DB
199 if (count($this->data_db) > 0){
200 //UPDATE PRODUCT
201 $stock = '0';
202 $prod = '0';
203
204 if ($this->data_db['stock'] != $this->csv_stock){
205 $stock = $this->update_stock($value);
206 }else{
207 $this->proses = true;
208 }
209 if ($this->data_db['price'] != $this->csv_price){
210 $prod = $this->update_product($value);
211 }else{
212 $this->proses = true;
213 }
214 if ($stock != '0' || $prod != '0'){
215 //Update price dan stock ke db
216 $this->proses_update = 'true';
217 $this->proses = true;
218 ProductModel::where('sku', '=',$this->csv_sku)->update(['stock' => $this->csv_stock, 'price' => $this->csv_price]);
219 }else{
220 $this->proses_update = 'false';
221 }
222 $hasil[$i] = $this->logs($this->csv_sku, $stock,$prod,'false',$this->proses_update);
223 }else{
224 //INSERT PRODUCT
225 $in = $this->insert_product($value);
226 if ($in){
227 $this->proses = true;
228 $this->proses_insert = 'true';
229 }else{
230 $this->proses = true;
231 $this->proses_insert = 'false';
232 }
233 $hasil[$i] = $this->logs($this->csv_sku, 'false','false',$in,$this->proses_insert);
234 }
235 }else{
236 //DATA ADA YG KOSONG
237 $hasil[$i] = $this->logs($this->csv_sku, 'false','false','false','false');
238 }
239
240 }else{
241 //DATA ADA YG tidak valid
242 $not_valid_msg = 'Title tidak boleh mengandung < > ; = # { }';
243 $hasil[$i] = $this->logs($this->csv_sku, 'false','false','false',$not_valid_msg);
244 }
245 if ($this->proses){
246 CsvModel::where('id','=',$value['id'])->update(['is_proses'=> 1]);
247 }
248 $i++;
249 }
250 $data_email = [
251 'title' => 'Logs',
252 'content' => 'Logs email product <br>'.$this->error,
253 ];
254 $file_export = storage_path('app/logs_'.date('Y-m-d H-i-s').'.csv');
255 $writer = Writer::createFromPath($file_export, 'w+');
256 $writer->insertAll($hasil); //using an array
257 if(count($record_arr) > 0){
258 $this->send_email($data_email, $file_export);
259 }
260
261 }
262
263 public function sycn_data()
264 {
265 $status = '';
266 $delete = false;
267 $q_insert = false;
268 $get_prod = $this->get_product_presta();
269 if ($get_prod){
270 $status .= 'Berhasil Get product dari Api <br/>';
271 if (isset($this->data_sycn)){
272 DB::beginTransaction();
273
274 if (ProductModel::count() > 0){
275 //Delete jika ada isi table
276 try{
277 $delete = DB::table('product')->delete();
278 $status .= 'Berhasil remove semua product dari database <br/>';
279 }catch (\Exception $e){
280 $status .= 'Gagal remove semua product dari database '. $e->getMessage().' <br/>';
281 }
282 }else{
283 $delete = true;
284 }
285
286 try{
287 $q_insert = ProductModel::insert($this->data_sycn);
288 $status .= 'Berhasil insert semua product ke database <br/>';
289 }catch (\Exception $e){
290 $status .= 'Gagal insert semua product dari database '. $e->getMessage().' <br/>';
291 }
292 if ($delete && $q_insert){
293 DB::commit();
294 $status .= 'Proses Berhasil <br/>';
295
296 }else{
297 DB::rollBack();
298 $status .= 'Proses Gagal Data dikembalikan seperti semula <br/>';
299 }
300
301 }else{
302 $status .= 'Gagal ambil data dari Var data_sycn <br/>';
303 $status .= 'Proses Gagal <br/>';
304 }
305
306 }else{
307 $status .= 'Gagal Get product dari Api <br/>'. $this->error;
308 }
309
310 $data_email = [
311 'title' => 'Logs',
312 'content' => $status
313 ];
314 $this->send_email($data_email, false, 'Log Data sycn');
315 return $data_email['content'];
316 }
317
318 private function send_email($data_email=array(), $path_csv, $subject = 'Logs'){
319
320 if ($path_csv){
321 Mail::send('emails.email_tpl', $data_email, function ($message) use ($path_csv)
322 {
323 $message->from($this->email_from, 'BebiBurp');
324 $message->to($this->email_to);
325 $message->cc($this->email_cc);
326 $message->subject('Logs');
327 $message->attach($path_csv);
328 });
329 unlink($path_csv);
330
331 }else{
332 Mail::send('emails.email_tpl', $data_email, function ($message) use ($subject)
333 {
334 $message->from($this->email_from, 'BebiBurp');
335 $message->to($this->email_to);
336 $message->cc($this->email_cc);
337 $message->subject($subject);
338 });
339 }
340
341 return true;
342
343 }
344
345 /**
346 * @return bool
347 */
348 private function get_product_presta(){
349 try{
350 //GET STOCK
351 $opt['resource'] = 'stock_availables';
352 $opt['display'] = 'full';
353 $xml=Prestashop::get($opt);
354 $resources_stock = $xml->stock_availables->children();
355 $arr_stock = json_decode(json_encode($resources_stock), true);
356 foreach ($arr_stock['stock_available'] as $key){
357 $stock[$key['id_product']] = $key;
358 }
359
360 //GET PRODUCT
361 $opt['resource'] = 'products';
362 $opt['display'] = '[id,name,description,weight,price,reference,link_rewrite,active]';
363 $xml=Prestashop::get($opt);
364 $resources = $xml->products->children();
365 if (count($resources) > 1){
366 $arr_prod = json_decode(json_encode($resources), true);
367 foreach ($arr_prod['product'] as $resource){
368 if ($resource['reference'] != NULL){
369 $data_in[] = [
370 'id_prod' => $resource['id'],
371 'active' => $resource['active'],
372 'weight' => $resource['weight'],
373 'price' => $resource['price'],
374 'name' => $resource['name']['language'],
375 'sku' => $resource['reference'],
376 'description' => $resource['description']['language'],
377 'link_rewrite' => $resource['link_rewrite']['language'],
378 'id_stock' => $stock[$resource['id']]['id'],
379 'stock' => $stock[$resource['id']]['quantity'],
380 ];
381 $this->data_sycn = $data_in;
382 }
383 }
384 }elseif (count($resources) == 1){
385 $arr_prod = json_decode(json_encode($resources), true);
386 $resource = $arr_prod['product'];
387 if ($resource['reference'] != NULL){
388 $data_in[] = [
389 'id_prod' => $resource['id'],
390 'active' => $resource['active'],
391 'weight' => $resource['weight'],
392 'price' => $resource['price'],
393 'name' => $resource['name']['language'],
394 'sku' => $resource['reference'],
395 'description' => isset($resource['description']['language']['@attributes']) ? '' : $resource['description']['language'],
396 'link_rewrite' => $resource['link_rewrite']['language'],
397 'id_stock' => $stock[$resource['id']]['id'],
398 'stock' => $stock[$resource['id']]['quantity'],
399 ];
400 $this->data_sycn = $data_in;
401 }
402 }
403
404 return true;
405 }catch(\Exception $e){
406 $this->error = $e->getMessage();
407 return false;
408 }
409 }
410
411 /**
412 * @param $data_csv
413 * @return bool|string
414 */
415 private function insert_product($data_csv){
416 try {
417 $xmlSchema=Prestashop::getSchema('products');
418
419 $data=[
420 'name' => $data_csv['name'],
421 'description' => $data_csv['description'],
422 'price' => $data_csv['price'],
423 'weight' => $data_csv['weight'],
424 'reference' => $data_csv['sku'],
425 'link_rewrite' => '',
426 ];
427
428 $postXml=Prestashop::fillSchema($xmlSchema,$data);
429 $response=Prestashop::add([
430 'resource'=>'products',
431 'postXml'=>$postXml->asXml()
432 ]);
433
434
435 $stok_id = $response->product->associations->stock_availables->stock_available->id;
436 $prod_id = $response->product->id;
437
438 if($stok_id != ''){
439 //PUSH DATA TO VAR PRODUCT
440 $arr_prod = json_decode(json_encode($response), true);
441 $prod = [
442 'id_prod' => $arr_prod['product']['id'],
443 'active' => ($arr_prod['product']['active'] == null) ? '0' : $arr_prod['product']['active'],
444 'weight' => $arr_prod['product']['weight'],
445 'price' => $arr_prod['product']['price'],
446 'name' => $arr_prod['product']['name']['language'],
447 'sku' => $arr_prod['product']['reference'],
448 'description' => $arr_prod['product']['description']['language'],
449 'link_rewrite' => $arr_prod['product']['link_rewrite']['language'],
450 'id_stock' => $arr_prod['product']['associations']['stock_availables']['stock_available']['id'],
451 'stock' => $data_csv['stock'],
452 ];
453
454 //END
455 //insert Stock
456 $xmlSchema2=Prestashop::getSchema('stock_availables'); //returns a SimpleXMLElement instance with the desired schema
457
458 $data2=[
459 'id' => $stok_id,
460 'id_product' => $prod_id,
461 'id_product_attribute' => 0,
462 'id_shop' => 1,
463 'id_shop_group' => 0,
464 'quantity' => $data_csv['stock'],
465 'depends_on_stock' => 0,
466 'out_of_stock' => 2
467 ];
468
469 $postXml2=Prestashop::fillSchema($xmlSchema2,$data2);
470
471 $response2=Prestashop::edit(
472 [
473 'resource' =>'stock_availables',
474 'putXml' =>$postXml2->asXml(),
475 'id' =>$stok_id
476 ]
477 );
478 if ($response2){
479 ProductModel::insert($prod); //Insert ke new prod ke database
480 return true;
481 }else{
482 return false;
483 }
484 }
485 } catch(\Exception $e) {
486 $this->error = $e->getMessage();
487 return false;
488 }
489 }
490
491 /**
492 * @param $value
493 * @return bool|string
494 */
495 private function update_product($value){
496 try {
497 $xmlSchema=Prestashop::getSchema('products');
498
499 $data=[
500 'id' => $this->data_db['id_prod'],
501 'name' => $this->data_db['name'],
502 'description' => $this->data_db['description'],
503 'price' => $this->csv_price,
504 'weight' => $this->data_db['weight'],
505 'reference' => $this->csv_sku,
506 'active' => $this->data_db['active'],
507 'link_rewrite' =>$this->data_db['link_rewrite'],
508 ];
509
510 $postXml2=Prestashop::fillSchema($xmlSchema,$data);
511
512 $response2=Prestashop::edit(
513 [
514 'resource' => 'products',
515 'putXml' => $postXml2->asXml(),
516 'id' => $this->data_db['id_prod']
517 ]
518 );
519 if ($response2){
520 return true;
521 }else{
522 return false;
523 }
524 } catch(\Exception $e) {
525 $this->error = $e->getMessage();
526 return false;
527 }
528 }
529
530 /**
531 * @param $value
532 * @return bool|string
533 */
534 private function update_stock($value){
535 try {
536 $xmlSchema2=Prestashop::getSchema('stock_availables'); //returns a SimpleXMLElement instance with the desired schema
537 $data2=[
538 'id' => $this->data_db['id_stock'],
539 'id_product' => $this->data_db['id_prod'],
540 'id_product_attribute' => 0,
541 'id_shop' => 1,
542 'id_shop_group' => 0,
543 'quantity' => ($this->csv_stock != '') ? $this->csv_stock : '0' ,
544 'depends_on_stock' => 0,
545 'out_of_stock' => 2,
546 ];
547
548 $postXml2=Prestashop::fillSchema($xmlSchema2,$data2);
549
550 $response2=Prestashop::edit(
551 [
552 'resource' => 'stock_availables',
553 'putXml' => $postXml2->asXml(),
554 'id' => $this->data_db['id_stock'],
555 ]
556 );
557 if ($response2){
558 return true;
559 }else{
560 return false;
561 }
562 } catch(\Exception $e) {
563 $this->error = $e->getMessage();
564 return false;
565 }
566 }
567
568 /**
569 * @param $values
570 * @return string
571 */
572 private function validate_null_data($values){
573 foreach ($values as $key => $value){
574 if (is_null($value)){
575 return 'false';
576 }
577 }
578 return 'true';
579 }
580
581 /**
582 * @param string $sku
583 * @param string $update_stock
584 * @param string $update_product
585 * @param string $insert_product
586 * @return array
587 */
588 private function logs($sku = '', $update_stock = '', $update_product = '', $insert_product = '', $proses = ''){
589 $data = [
590 'SKU' => $sku,
591 'Update_stock' => ($update_stock == '1') ? 'true' : 'false',
592 'Update_product' => ($update_product == '1') ? 'true' : 'false',
593 'Insert_product' => ($insert_product == '1') ? 'true' : 'false',
594 'process' => $proses,
595
596 ];
597 return $data;
598 }
599
600 public function test(){
601 return env('EMAIL_TO');
602 }
603
604
605}