コード例 #1
6
ファイル: Store.php プロジェクト: renciebautista/pcount2
 public static function upload($filepath)
 {
     \DB::beginTransaction();
     try {
         $reader = ReaderFactory::create(Type::XLSX);
         // for XLSX files
         $reader->open($filepath);
         $cnt = 0;
         Store::where('active', 1)->update(['active' => 0]);
         foreach ($reader->getSheetIterator() as $sheet) {
             foreach ($sheet->getRowIterator() as $row) {
                 if ($cnt > 0) {
                     // dd($row);
                     $area = Area::firstOrCreate(['area' => strtoupper($row[0])]);
                     $enrollment = Enrollment::firstOrCreate(['enrollment' => strtoupper($row[1])]);
                     $distributor = Distributor::firstOrCreate(['distributor_code' => strtoupper($row[2]), 'distributor' => strtoupper($row[3])]);
                     $client = Client::firstOrCreate(['client_code' => strtoupper($row[8]), 'client_name' => strtoupper($row[9])]);
                     $channel = Channel::firstOrCreate(['channel_code' => strtoupper($row[10]), 'channel_desc' => strtoupper($row[11])]);
                     $agency = Agency::firstOrCreate(['agency_code' => strtoupper($row[19]), 'agency_name' => strtoupper($row[20])]);
                     $region = Region::firstOrCreate(['region_code' => strtoupper($row[16]), 'region' => strtoupper($row[15]), 'region_short' => strtoupper($row[14])]);
                     $customer = Customer::firstOrCreate(['customer_code' => strtoupper($row[12]), 'customer_name' => strtoupper($row[13])]);
                     $user = User::where('username', strtoupper($row[22]))->first();
                     if (empty($user) && !empty($row[22])) {
                         // dd($row);
                         $user = User::firstOrCreate(['username' => strtoupper($row[22]), 'name' => strtoupper($row[22]), 'email' => strtoupper($row[22]) . '@pcount.com', 'password' => \Hash::make($row[22])]);
                         $user->roles()->attach(2);
                     }
                     $storeExist = Store::where('store_code', strtoupper($row[5]))->first();
                     if (empty($storeExist) && !empty($row[22])) {
                         $store = Store::create(['storeid' => strtoupper($row[4]), 'store_code' => strtoupper($row[5]), 'store_code_psup' => strtoupper($row[6]), 'store_name' => strtoupper($row[7]), 'area_id' => $area->id, 'enrollment_id' => $enrollment->id, 'distributor_id' => $distributor->id, 'client_id' => $client->id, 'channel_id' => $channel->id, 'customer_id' => $customer->id, 'region_id' => $region->id, 'agency_id' => $agency->id, 'active' => 1]);
                         if (!empty($row[22])) {
                             StoreUser::insert(['store_id' => $store->id, 'user_id' => $user->id]);
                         }
                     } else {
                         $storeExist->storeid = strtoupper($row[4]);
                         $storeExist->store_code = strtoupper($row[5]);
                         $storeExist->store_code_psup = strtoupper($row[6]);
                         $storeExist->store_name = strtoupper($row[7]);
                         $storeExist->area_id = $area->id;
                         $storeExist->enrollment_id = $enrollment->id;
                         $storeExist->distributor_id = $distributor->id;
                         $storeExist->client_id = $client->id;
                         $storeExist->channel_id = $channel->id;
                         $storeExist->customer_id = $customer->id;
                         $storeExist->region_id = $region->id;
                         $storeExist->agency_id = $agency->id;
                         $storeExist->active = 1;
                         $storeExist->save();
                         StoreUser::where('store_id', $storeExist->id)->delete();
                         StoreUser::insert(['store_id' => $storeExist->id, 'user_id' => $user->id]);
                     }
                 }
                 $cnt++;
             }
         }
         \DB::commit();
     } catch (Exception $e) {
         dd($e);
         \DB::rollback();
     }
 }
コード例 #2
4
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('divisions')->truncate();
     DB::table('categories')->truncate();
     DB::table('sub_categories')->truncate();
     DB::table('brands')->truncate();
     DB::table('skus')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = 'database/seeds/seed_files/Items.xlsx';
     $reader->open($filePath);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'SKU Data') {
             $rowcnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($rowcnt > 1) {
                     if (!is_null($row[0])) {
                         $division = Division::firstOrCreate(['division' => $row[8]]);
                         $category = Category::firstOrCreate(['category_short' => strtoupper($row[1]), 'category_long' => strtoupper($row[0])]);
                         $sub_category = SubCategory::firstOrCreate(['subcategory' => strtoupper($row[6])]);
                         $brand = Brand::firstOrCreate(['brand' => strtoupper($row[7])]);
                         $sku = Sku::firstOrCreate(['division_id' => $division->id, 'category_id' => $category->id, 'sub_category_id' => $sub_category->id, 'brand_id' => $brand->id, 'sku_code' => $row[2], 'item_desc' => $row[3], 'sku_desc' => $row[4], 'conversion' => $row[5]]);
                     }
                 }
                 $rowcnt++;
             }
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #3
2
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('customers')->truncate();
     DB::table('areas')->truncate();
     DB::table('premises')->truncate();
     DB::table('stores')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = 'database/seeds/seed_files/Store Mapping.xlsx';
     $reader->open($filePath);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $rowcnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($rowcnt > 1) {
                     if (!is_null($row[0])) {
                         $customer = Customer::firstOrCreate(['customer_code' => $row[8], 'customer' => strtoupper($row[9])]);
                         $area = Area::firstOrCreate(['area_code' => $row[2], 'area' => strtoupper($row[3])]);
                         $premise = Premise::firstOrCreate(['premise_code' => $row[4], 'premise' => strtoupper($row[5])]);
                         Store::firstOrCreate(['customer_id' => $customer->id, 'area_id' => $area->id, 'premise_id' => $premise->id, 'store_code' => strtoupper($row[0]), 'store' => strtoupper($row[1])]);
                     }
                 }
                 $rowcnt++;
             }
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #4
2
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     //
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     $folderpath = base_path() . '/database/seeds/templates/';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", str_replace('\\', '/', $value));
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $filePath = $folderpath . $latest . '/Masterfile.xlsx';
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $reader->open($filePath);
     DB::table('channel_items')->truncate();
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'MKL Mapping') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($row[0] != '') {
                     if ($cnt > 0) {
                         // dd($row);
                         if (!ctype_digit(trim($row[4])) || !ctype_digit(trim($row[5])) || !ctype_digit(trim($row[6]))) {
                             InvalidMapping::create(['premise_code' => trim($row[0]), 'customer_code' => trim($row[1]), 'store_code' => trim($row[2]), 'sku_code' => trim($row[3]), 'ig' => trim($row[4]), 'multiplier' => trim($row[5]), 'minstock' => trim($row[6]), 'type' => 'MKL Mapping', 'remarks' => 'Invalid mapping']);
                         } else {
                             $channel = '';
                             if (trim($row[0]) != '') {
                                 $channel = Channel::where('channel_code', trim($row[0]))->first();
                             }
                             $item = Item::where('sku_code', trim($row[3]))->first();
                             if (!empty($item)) {
                                 $item_type = ItemType::where('type', "MKL")->first();
                                 $osa_tagging = 0;
                                 if (isset($row[7])) {
                                     $osa_tagging = trim($row[7]);
                                 }
                                 $npi_tagging = 0;
                                 if (isset($row[8])) {
                                     $npi_tagging = trim($row[8]);
                                 }
                                 ChannelItem::firstOrCreate(['channel_id' => $channel->id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => $osa_tagging, 'npi_tagged' => $npi_tagging]);
                             }
                         }
                     }
                     $cnt++;
                 }
             }
         }
     }
     $reader->close();
 }
 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Secondary Display.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('secondary_display_lookups')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = $file_path;
     $reader->open($filePath);
     // Accessing the sheet name when reading
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($cnt > 0) {
                     // dd($row);
                     $store = Store::where('store_code', trim($row[1]))->first();
                     $brands = array();
                     if (!empty($store)) {
                         $x = 1;
                         for ($i = 3; $i < 29; $i++) {
                             if ($row[$i] == "1.0") {
                                 $brands[] = $x;
                             }
                             $x++;
                         }
                         foreach ($brands as $value) {
                             SecondaryDisplayLookup::create(['store_id' => $store->id, 'secondary_display_id' => $value]);
                         }
                     }
                 }
                 $cnt++;
             }
         } else {
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #6
1
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     $folderpath = base_path() . '/database/seeds/seed_files/';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", str_replace('\\', '/', $value));
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $filePath = $folderpath . $latest . '/Masterfile.xlsx';
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $reader->open($filePath);
     echo 'Seeding ' . $filePath . PHP_EOL;
     // DB::table('other_barcodes')->truncate();
     Item::where('active', 1)->update(['cleared' => 0]);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Other Codes') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if (!is_null($row[0]) && trim($row[0]) != '') {
                     if ($cnt > 0) {
                         $item = Item::where('sku_code', trim($row[0]))->first();
                         if (!empty($item)) {
                             if ($item->cleared == 0) {
                                 OtherBarcode::where('item_id', $item->id)->delete();
                                 $item->cleared = 1;
                                 $item->save();
                             }
                             $area = Area::where('area', strtoupper($row[1]))->first();
                             if (!empty($item) && !empty($area)) {
                                 OtherBarcode::firstOrCreate(['item_id' => $item->id, 'area_id' => $area->id, 'other_barcode' => trim($row[2])]);
                             }
                         } else {
                             // dd($row);
                         }
                     }
                     $cnt++;
                 }
             }
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #7
1
 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Store SOS.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('store_sos_tags')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = $file_path;
     $reader->open($filePath);
     // Accessing the sheet name when reading
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if (!empty($row[0])) {
                     // dd($row);
                     if ($cnt > 0) {
                         // dd($row);
                         $store = Store::where('store_code', $row[0])->first();
                         // dd($store);
                         $category = FormCategory::where('category', strtoupper($row[2]))->first();
                         // dd($category);
                         $sos = SosTagging::where('sos_tag', strtoupper($row[3]))->first();
                         // dd($sos);
                         StoreSosTag::insert(array('store_id' => $store->id, 'form_category_id' => $category->id, 'sos_tag_id' => $sos->id));
                         // echo (string)$row[0], "\n";
                     }
                     $cnt++;
                 }
             }
         } else {
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Secondary Display.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('secondary_displays')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = $file_path;
     $reader->open($filePath);
     // Accessing the sheet name when reading
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet2') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if (!is_null($row[0])) {
                     if ($cnt > 0) {
                         $category = FormCategory::firstOrCreate(['category' => strtoupper($row[0])]);
                         $category->secondary_display = 1;
                         $category->update();
                         if (!empty($category)) {
                             SecondaryDisplay::create(array('category_id' => $category->id, 'brand' => $row[1]));
                         }
                     }
                     $cnt++;
                 }
             }
         } else {
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #9
1
 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/Sub Form.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('temp_forms')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = $file_path;
     $reader->open($filePath);
     // Accessing the sheet name when reading
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($cnt > 0) {
                     if (!is_null($row[2])) {
                         $prompt = addslashes($row[1]);
                         DB::statement('INSERT INTO temp_forms (code, prompt, required, type, choices, expected_answer) VALUES ("' . $row[0] . '","' . $prompt . '","' . $row[2] . '","' . $row[3] . '","' . $row[4] . '","' . $row[6] . '");');
                     }
                 }
                 $cnt++;
             }
         } else {
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #10
1
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('users')->truncate();
     DB::table('store_user')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = 'database/seeds/seed_files/User.xlsx';
     $reader->open($filePath);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'File') {
             $rowcnt = 0;
             $errorCnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($rowcnt > 0) {
                     if (!is_null($row[0])) {
                         $user = User::where('email', strtolower($row[19]) . '@unilever.com')->first();
                         if (count($user) == 0) {
                             $user = User::firstOrCreate(['name' => strtoupper($row[18]), 'email' => strtolower($row[19]) . '@unilever.com', 'username' => $row[19], 'password' => Hash::make('password')]);
                         }
                         $store = Store::where('store_code', $row[5])->first();
                         if (!empty($store)) {
                             $store->users()->attach($user->id);
                         } else {
                             $errorCnt++;
                         }
                     }
                 }
                 $rowcnt++;
             }
         }
     }
     $reader->close();
     echo $errorCnt;
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #11
1
 /**
  * Run the database seeds.
  *
  * @return void
  */
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('store_sku')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = 'database/seeds/seed_files/SKU Mapping.xlsx';
     $reader->open($filePath);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $rowcnt = 0;
             $errorCnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($rowcnt > 0) {
                     if (!is_null($row[0])) {
                         $premise = Premise::where('premise_code', $row[0])->first();
                         if (!empty($premise)) {
                             $stores = Store::where('premise_id', $premise->id)->get();
                             $sku = Sku::where('sku_code', $row[3])->first();
                             if (!empty($stores) && !empty($sku)) {
                                 foreach ($stores as $store) {
                                     $store->skus()->attach($sku, array('ig' => $sku->conversion + 20));
                                 }
                             }
                         }
                     }
                 }
                 $rowcnt++;
             }
         }
     }
     $reader->close();
     echo $errorCnt;
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #12
1
ファイル: ImportCommand.php プロジェクト: rizabudi/plansys
 public function actionIndex()
 {
     if (is_null($this->params) || !isset($this->params['file'])) {
         $this->failed('You should run this from `Import Controller`');
         return;
     }
     $paramsModel = explode(".", $this->params['model']);
     $modelClass = @$paramsModel[0];
     $modelConfig = @$paramsModel[1];
     if (!class_exists($modelClass)) {
         $this->failed('Model ' . $modelClass . ' does not exist!');
         return;
     }
     $file = $this->params['file'];
     $errors = [];
     $excelColumns = [];
     $config = $this->getConfig($modelClass);
     $modelColumns = $config['columns'];
     $this->setView('title', '<h3 style="margin-top:5px;">Importing ' . $modelClass . '</h3>');
     $this->msg('Opening Excel File...');
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($file);
     $transaction = Yii::app()->db->beginTransaction();
     ## get first sheet
     $import = new Import($this->params['model']);
     foreach ($reader->getSheetIterator() as $sheet) {
         ## loop each row in first sheet
         $rowCount = count($sheet->getRowIterator());
         foreach ($sheet->getRowIterator() as $r => $row) {
             ## first row is always column name, assign it then skip it
             if ($r == 1) {
                 foreach ($row as $k => $v) {
                     $excelColumns[$v] = $k;
                 }
                 continue;
             }
             foreach ($row as $k => $v) {
                 $row[$k] = (string) $v;
             }
             ## do import
             $rowImport = [];
             foreach ($import->columns as $c => $v) {
                 $col = is_string($v) ? ['type' => $v] : $v;
                 if ($col['type'] == 'function') {
                     continue;
                 }
                 $rowImport[$c] = @$row[$excelColumns[$c]];
             }
             try {
                 $res = $import->importRow($rowImport);
             } catch (Exception $e) {
                 $res = [['error' => $e->getMessage()]];
             }
             ## mark errors
             if ($res !== true) {
                 if (count($errors) <= ImportCommand::MAX_ERRORS) {
                     $errors[$r - 1] = $res;
                 } else {
                     $this->failed('Import Failed');
                     break;
                 }
             }
             $this->msg('Importing ' . ($r - 1) . ' Row...<br/><br/>' . $this->formatErrors($errors));
         }
         break;
     }
     if (empty($errors)) {
         $this->msg('<a style="margin-top:10px;" href="' . $import->saveExcel() . '" class="btn btn-success btn-sm">
                         <i class="fa fa-download"></i> Download Excel
                     </a>');
         $transaction->commit();
         $this->finished("Done");
     } else {
         $this->failed('Import Failed');
         $transaction->rollback();
     }
     $reader->close();
 }
コード例 #13
1
 public function run()
 {
     $folderpath = 'database/seeds/seed_files';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", $value);
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $file_path = $folderpath . "/" . $latest . "/OSA Target.xlsx";
     echo (string) $file_path, "\n";
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     DB::table('osa_lookups')->truncate();
     DB::table('osa_lookup_targets')->truncate();
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $filePath = $file_path;
     $reader->open($filePath);
     // Accessing the sheet name when reading
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Sheet1') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if (!is_null($row[0])) {
                     if ($cnt > 0) {
                         // dd($row);
                         $customer_id = 0;
                         $customer = Customer::where('customer_code', $row[0])->first();
                         if (!empty($customer)) {
                             $customer_id = $customer->id;
                         }
                         $region_id = 0;
                         $region = Region::where('region_code', $row[1])->first();
                         if (!empty($region)) {
                             $region_id = $region->id;
                         }
                         $distributor_id = 0;
                         $distributor = Distributor::where('distributor_code', $row[2])->first();
                         if (!empty($distributor)) {
                             $distributor_id = $distributor->id;
                         }
                         $store_id = 0;
                         $store = Store::where('store_code', $row[3])->first();
                         if (!empty($store)) {
                             $store_id = $store->id;
                         }
                         $template_id = 0;
                         $template = AuditTemplate::where('template_code', $row[4])->first();
                         if (!empty($template)) {
                             $template_id = $template->id;
                         }
                         $category = FormCategory::where('category', $row[5])->first();
                         if (!empty($category)) {
                             $osalookup_id = 0;
                             $osalookup = OsaLookup::where('customer_id', $customer_id)->where('region_id', $region_id)->where('distributor_id', $distributor_id)->where('store_id', $store_id)->where('template_id', $template_id)->first();
                             if (empty($osalookup)) {
                                 $osalookup = new OsaLookup();
                                 $osalookup->customer_id = $customer_id;
                                 $osalookup->region_id = $region_id;
                                 $osalookup->distributor_id = $distributor_id;
                                 $osalookup->store_id = $store_id;
                                 $osalookup->template_id = $template_id;
                                 $osalookup->save();
                             }
                             $osalookup_id = $osalookup->id;
                             OsaLookupTarget::create(array('osa_lookup_id' => $osalookup_id, 'category_id' => $category->id, 'target' => $row[8], 'total' => $row[9]));
                         }
                     }
                     $cnt++;
                 }
             }
         } else {
         }
     }
     $reader->close();
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #14
1
 public function run()
 {
     Model::unguard();
     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     $folderpath = base_path() . '/database/seeds/seed_files/';
     $folders = File::directories($folderpath);
     $latest = '11232015';
     foreach ($folders as $value) {
         $_dir = explode("/", str_replace('\\', '/', $value));
         $cnt = count($_dir);
         $name = $_dir[$cnt - 1];
         $latest_date = DateTime::createFromFormat('mdY', $latest);
         $now = DateTime::createFromFormat('mdY', $name);
         if ($now > $latest_date) {
             $latest = $name;
         }
     }
     $filePath = $folderpath . $latest . '/Masterfile.xlsx';
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $reader->open($filePath);
     // DB::table('store_items');
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Assortment Mapping') {
             $cnt = 0;
             foreach ($sheet->getRowIterator() as $row) {
                 if ($row[0] != '') {
                     if ($cnt > 0) {
                         if (!ctype_digit(trim($row[4]))) {
                             InvalidMapping::create(['premise_code' => trim($row[0]), 'customer_code' => trim($row[1]), 'store_code' => trim($row[2]), 'sku_code' => trim($row[3]), 'ig' => trim($row[4]), 'multiplier' => trim($row[5]), 'minstock' => trim($row[6]), 'type' => 'Assortment Mapping', 'remarks' => 'Invalid mapping']);
                         } else {
                             $channel = '';
                             $customer = '';
                             $store = '';
                             if (trim($row[0]) != '') {
                                 $channel = Channel::where('channel_code', trim($row[0]))->get();
                             }
                             if (trim($row[1]) != '') {
                                 $customer = Customer::where('customer_code', trim($row[1]))->get();
                             }
                             if (trim($row[2]) != '') {
                                 $store = Store::where('store_code', trim($row[2]))->first();
                             }
                             // dd($store);
                             $stores = Store::where(function ($query) use($channel) {
                                 if (!empty($channel)) {
                                     $channel_id = [];
                                     foreach ($channel as $value) {
                                         $channel_id[] = $value->id;
                                     }
                                     $query->whereIn('channel_id', $channel_id);
                                 }
                             })->where(function ($query) use($customer) {
                                 if (!empty($customer)) {
                                     $customer_id = [];
                                     foreach ($customer as $value) {
                                         $customer_id[] = $value->id;
                                     }
                                     $query->whereIn('customer_id', $customer_id);
                                 }
                             })->where(function ($query) use($store) {
                                 if (!empty($store)) {
                                     $query->where('store', $store->id);
                                 }
                             })->get();
                             // dd($stores);
                             $item = Item::where('sku_code', trim($row[3]))->first();
                             if (!empty($item)) {
                                 $item_type = ItemType::where('type', "ASSORTMENT")->first();
                                 foreach ($stores as $store) {
                                     $w_mkl = StoreItem::where('store_id', $store->id)->where('item_id', $item->id)->get();
                                     $cw_mkl = ChannelItem::where('channel_id', $store->channel_id)->where('item_id', $item->id)->get();
                                     if (count($w_mkl) == 0) {
                                         StoreItem::firstOrCreate(['store_id' => $store->id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => 0, 'npi_tagged' => 0]);
                                     }
                                     if (count($cw_mkl) == 0) {
                                         ChannelItem::firstOrCreate(['channel_id' => $store->channel_id, 'item_id' => $item->id, 'item_type_id' => $item_type->id, 'ig' => trim($row[4]), 'fso_multiplier' => trim($row[5]), 'min_stock' => trim($row[6]), 'osa_tagged' => 0, 'npi_tagged' => 0]);
                                     }
                                 }
                             }
                         }
                     }
                     $cnt++;
                 }
             }
         }
     }
     $reader->close();
     $hash = UpdateHash::find(1);
     if (empty($hash)) {
         UpdateHash::create(['hash' => \Hash::make(date('Y-m-d H:i:s'))]);
     } else {
         $hash->hash = md5(date('Y-m-d H:i:s'));
         $hash->update();
     }
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     Model::reguard();
 }
コード例 #15
0
 /**
  * @param string $path
  * @param string $fileType
  * @param array  $config
  *
  * @return array
  */
 protected function getActualLines($path, $fileType, array $config)
 {
     $reader = ReaderFactory::create($fileType);
     if (Type::CSV === $fileType && $reader instanceof CsvReader) {
         $reader->setFieldDelimiter($config['delimiter'])->setFieldEnclosure($config['enclosure']);
     }
     $reader->open($path);
     $sheet = current(iterator_to_array($reader->getSheetIterator()));
     $lines = iterator_to_array($sheet->getRowIterator());
     $reader->close();
     return $lines;
 }
コード例 #16
0
ファイル: SheetTest.php プロジェクト: nekulin/spout
 /**
  * @param string $fileName
  * @return Sheet[]
  */
 private function openFileAndReturnSheets($fileName)
 {
     $resourcePath = $this->getResourcePath($fileName);
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($resourcePath);
     $sheets = [];
     foreach ($reader->getSheetIterator() as $sheet) {
         $sheets[] = $sheet;
     }
     $reader->close();
     return $sheets;
 }
コード例 #17
0
ファイル: ImportCommand.php プロジェクト: reggi49/plansys
 public function actionIndex()
 {
     if (is_null($this->params) || !isset($this->params['file'])) {
         $this->failed('You should run this from `Import Controller`');
         return;
     }
     if (!class_exists($this->params['model'])) {
         $this->failed('Model ' . $this->params['model'] . ' does not exist!');
         return;
     }
     $file = $this->params['file'];
     $modelClass = $this->params['model'];
     $errors = [];
     $excelColumns = [];
     $config = $this->getConfig($modelClass);
     $modelColumns = $config['columns'];
     $this->setView('title', '<h3 style="margin-top:5px;">Importing ' . $modelClass . '</h3>');
     $this->msg('Opening Excel File...');
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($file);
     ## get first sheet
     foreach ($reader->getSheetIterator() as $sheet) {
         foreach ($sheet->getRowIterator() as $r => $row) {
             var_dump($row);
         }
     }
     die;
     $transaction = Yii::app()->db->beginTransaction();
     ## get first sheet
     foreach ($reader->getSheetIterator() as $sheet) {
         ## loop each row in first sheet
         $rowCount = count($sheet->getRowIterator());
         foreach ($sheet->getRowIterator() as $r => $row) {
             ## first row is always column name, assign it then skip it
             if ($r == 1) {
                 foreach ($row as $k => $v) {
                     $excelColumns[$v] = $k;
                 }
                 continue;
             }
             ## assign value to model attribute
             $model = new $modelClass();
             foreach ($modelColumns as $c => $v) {
                 $model->{$c} = $row[$excelColumns[$c]];
             }
             ## before lookup
             foreach ($modelColumns as $c => $v) {
                 if (is_array($config['columns'][$c]) && $config['columns'][$c]['type'] == 'function' && is_string($config['columns'][$c]['value'])) {
                     $model->{$c} = Helper::evaluate($config['columns'][$c]['value'], ['row' => $model->attributes]);
                 }
             }
             ## process lookup
             ## after lookup
             foreach ($modelColumns as $c => $v) {
                 if (is_array($config['columns'][$c]) && $config['columns'][$c]['type'] == 'function' && is_string($config['columns'][$c]['value'])) {
                     $model->{$c} = Helper::evaluate($config['columns'][$c]['value'], ['row' => $model->attributes]);
                 }
             }
             ## save model
             $model->save();
             if (count($model->errors) > 0) {
                 if (count($errors) <= ImportCommand::MAX_ERRORS) {
                     $errors[$r] = $model->errors;
                 } else {
                     $this->failed('Too Many Errors...');
                     break;
                 }
             }
             $this->msg('Importing ' . $r . ' Row...<br/><br/>' . $this->formatErrors($errors));
         }
         break;
     }
     if (empty($errors)) {
         $transaction->commit();
     } else {
         $transaction->rollback();
     }
     $reader->close();
 }
コード例 #18
-1
ファイル: Spout.php プロジェクト: nyamsprod/csv-benchmarks
 /**
  * {@inheritdoc}
  */
 public function readerTest()
 {
     $csv = ReaderFactory::create(Type::CSV);
     $csv->open($this->path);
     foreach ($csv->getSheetIterator() as $sheet) {
         foreach ($sheet->getRowIterator() as $row) {
         }
     }
     $csv->close();
 }
コード例 #19
-1
ファイル: SheetTest.php プロジェクト: ankitpokhrel/spout
 /**
  * @param string $fileName
  * @return Sheet[]
  */
 private function openFileAndReturnSheets($fileName)
 {
     $resourcePath = $this->getResourcePath($fileName);
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($resourcePath);
     $sheets = [];
     while ($reader->hasNextSheet()) {
         $sheets[] = $reader->nextSheet();
     }
     $reader->close();
     return $sheets;
 }
コード例 #20
-1
 /**
  * @param string $type
  * @param string $filePath
  * @param array  $options
  *
  * @throws UnsupportedTypeException
  * @throws FileNotFoundException
  */
 public function __construct($type, $filePath, array $options = [])
 {
     $this->type = $type;
     $this->filePath = $filePath;
     $this->fileInfo = new \SplFileInfo($filePath);
     if (!$this->fileInfo->isFile()) {
         throw new FileNotFoundException(sprintf('File "%s" could not be found', $this->filePath));
     }
     $mimeType = finfo_file(finfo_open(FILEINFO_MIME_TYPE), $this->filePath);
     if ('application/zip' === $mimeType && Type::XLSX !== $this->fileInfo->getExtension()) {
         $this->extractZipArchive();
     }
     $this->reader = ReaderFactory::create($type);
     if (isset($options['reader_options'])) {
         $this->setReaderOptions($options['reader_options']);
     }
     $this->reader->open($this->filePath);
     $this->reader->getSheetIterator()->rewind();
     $sheet = $this->reader->getSheetIterator()->current();
     $sheet->getRowIterator()->rewind();
     $this->headers = $sheet->getRowIterator()->current();
     $this->rows = $sheet->getRowIterator();
 }
コード例 #21
-1
} else {
    require_once __DIR__ . '/../../../autoload.php';
}
if (2 !== $argc) {
    die('Please provide the input file as argument.');
}
$inputFile = $argv[1];
if (!is_readable($inputFile)) {
    die('Please provide a readable CSV input file as argument.');
}
$inputFileInfo = pathinfo($inputFile);
if ('csv' !== strtolower($inputFileInfo['extension'])) {
    die('Please provide a readable CSV input file as argument.');
}
$outputFile = sprintf('%s/%s_update.%s', $inputFileInfo['dirname'], $inputFileInfo['filename'], $inputFileInfo['extension']);
$reader = \Box\Spout\Reader\ReaderFactory::create(\Box\Spout\Common\Type::CSV);
$reader->setFieldDelimiter(';');
$reader->open($inputFile);
$writer = new \Akeneo\Catalog\CsvWriterWithoutBOM();
$writer->setFieldDelimiter(';');
$writer->openToFile($outputFile);
$previousRow = null;
foreach ($reader->getSheetIterator() as $sheet) {
    $headerWritten = false;
    foreach ($sheet->getRowIterator() as $row) {
        if ($headerWritten) {
            if (null !== $previousRow) {
                $rowToWrite = $previousRow;
                $rowToWrite[0] = $row[0];
                $writer->addRow($rowToWrite);
            }
コード例 #22
-1
ファイル: CSVTest.php プロジェクト: ankitpokhrel/spout
 /**
  * @param string $fileName
  * @param string|void $fieldDelimiter
  * @param string|void $fieldEnclosure
  * @return array All the read rows the given file
  */
 private function getAllRowsForFile($fileName, $fieldDelimiter = ",", $fieldEnclosure = '"')
 {
     $allRows = [];
     $resourcePath = $this->getResourcePath($fileName);
     $reader = ReaderFactory::create(Type::CSV);
     $reader->setFieldDelimiter($fieldDelimiter);
     $reader->setFieldEnclosure($fieldEnclosure);
     $reader->open($resourcePath);
     while ($reader->hasNextRow()) {
         $allRows[] = $reader->nextRow();
     }
     $reader->close();
     return $allRows;
 }
コード例 #23
-1
 /**
  * @Given /^the category order in the xlsx file "([^"]*)" should be following:$/
  */
 public function theCategoryOrderInTheXlsxFileShouldBeFollowing($fileName, TableNode $table)
 {
     $fileName = $this->replacePlaceholders($fileName);
     if (!file_exists($fileName)) {
         throw $this->createExpectationException(sprintf('File %s does not exist.', $fileName));
     }
     $categories = [];
     foreach (array_keys($table->getRowsHash()) as $category) {
         $categories[] = $category;
     }
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($fileName);
     $sheet = current(iterator_to_array($reader->getSheetIterator()));
     $actualLines = iterator_to_array($sheet->getRowIterator());
     array_shift($actualLines);
     $reader->close();
     foreach ($actualLines as $row) {
         $category = array_shift($categories);
         assertSame($category, $row[0], sprintf('Expecting category "%s", saw "%s"', $category, $row[0]));
     }
 }
コード例 #24
-1
ファイル: ReaderTest.php プロジェクト: nekulin/spout
 /**
  * @param string $fileName
  * @return array All the read rows the given file
  */
 private function getAllRowsForFile($fileName)
 {
     $allRows = [];
     $resourcePath = $this->getResourcePath($fileName);
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($resourcePath);
     foreach ($reader->getSheetIterator() as $sheetIndex => $sheet) {
         foreach ($sheet->getRowIterator() as $rowIndex => $row) {
             $allRows[] = $row;
         }
     }
     $reader->close();
     return $allRows;
 }
コード例 #25
-1
ファイル: ReaderTest.php プロジェクト: sfichera/spout
 /**
  * @dataProvider dataProviderForTestReadCustomEOL
  *
  * @param string $fileName
  * @param string $customEOL
  * @return void
  */
 public function testReadCustomEOLs($fileName, $customEOL)
 {
     $allRows = [];
     $resourcePath = $this->getResourcePath($fileName);
     /** @var \Box\Spout\Reader\CSV\Reader $reader */
     $reader = ReaderFactory::create(Type::CSV);
     $reader->setEndOfLineCharacter($customEOL)->open($resourcePath);
     foreach ($reader->getSheetIterator() as $sheet) {
         foreach ($sheet->getRowIterator() as $row) {
             $allRows[] = $row;
         }
     }
     $reader->close();
     $expectedRows = [['csv--11', 'csv--12', 'csv--13'], ['csv--21', 'csv--22', 'csv--23'], ['csv--31', 'csv--32', 'csv--33']];
     $this->assertEquals($expectedRows, $allRows);
 }
コード例 #26
-1
ファイル: ReaderTest.php プロジェクト: nekulin/spout
 /**
  * @param string $fileName
  * @param string|void $fieldDelimiter
  * @param string|void $fieldEnclosure
  * @param string|void $encoding
  * @return array All the read rows the given file
  */
 private function getAllRowsForFile($fileName, $fieldDelimiter = ',', $fieldEnclosure = '"', $encoding = EncodingHelper::ENCODING_UTF8)
 {
     $allRows = [];
     $resourcePath = $this->getResourcePath($fileName);
     /** @var \Box\Spout\Reader\CSV\Reader $reader */
     $reader = ReaderFactory::create(Type::CSV);
     $reader->setFieldDelimiter($fieldDelimiter)->setFieldEnclosure($fieldEnclosure)->setEncoding($encoding)->open($resourcePath);
     foreach ($reader->getSheetIterator() as $sheetIndex => $sheet) {
         foreach ($sheet->getRowIterator() as $rowIndex => $row) {
             $allRows[] = $row;
         }
     }
     $reader->close();
     return $allRows;
 }
コード例 #27
-1
ファイル: XLSXTest.php プロジェクト: ankitpokhrel/spout
 /**
  * @param string $fileName
  * @return array All the read rows the given file
  */
 private function getAllRowsForFile($fileName)
 {
     $allRows = [];
     $resourcePath = $this->getResourcePath($fileName);
     $reader = ReaderFactory::create(Type::XLSX);
     $reader->open($resourcePath);
     while ($reader->hasNextSheet()) {
         $reader->nextSheet();
         while ($reader->hasNextRow()) {
             $allRows[] = $reader->nextRow();
         }
     }
     $reader->close();
     return $allRows;
 }
コード例 #28
-1
 public function testXLSXImplementsInterface()
 {
     $consumer = new ReaderInterfaceConsumer();
     $result = $consumer->handleFile(ReaderFactory::create(Type::XLSX), self::XLSX_FILE);
     $this->assertEquals(self::EXPECTED, $result);
 }
コード例 #29
-1
ファイル: test_r.php プロジェクト: nedy13/AgilityContest
<?php

/**
 * Created by PhpStorm.
 * User: jantonio
 * Date: 30/11/15
 * Time: 13:42
 */
require_once __DIR__ . '/Spout/Autoloader/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;
$reader = ReaderFactory::create(Type::XLSX);
// for XLSX files
//$reader = ReaderFactory::create(Type::CSV); // for CSV files
//$reader = ReaderFactory::create(Type::ODS); // for ODS files
$reader->open(__DIR__ . "/test.xlsx");
echo "<html><body>";
foreach ($reader->getSheetIterator() as $sheet) {
    echo "<hr /><table>\n";
    echo "<tr><th>" . $sheet->getName() . "</th></tr>\n";
    foreach ($sheet->getRowIterator() as $row) {
        echo "<tr>";
        // do stuff with the row
        foreach ($row as $item) {
            echo "<td>";
            print_r($item);
            echo "</td>";
        }
        echo "</tr>\n";
    }
    echo "</table>";
コード例 #30
-2
 public function import($pathToFile)
 {
     $reader = ReaderFactory::create(Type::XLSX);
     // for XLSX files
     $reader->open($pathToFile);
     foreach ($reader->getSheetIterator() as $sheet) {
         if ($sheet->getName() == 'Сотрудники') {
             $this->parseSheet($sheet);
             break;
         }
         //
     }
     $reader->close();
 }