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();
}
}
/**
* 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();
}
/**
* 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();
}
/**
* 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();
}
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();
}
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();
}
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();
}
/**
* 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();
}
/**
* 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();
}
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();
}
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();
}
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();
}
/**
* @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;
}
/**
* @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;
}
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();
}
/**
* {@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();
}
/**
* @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;
}
/**
* @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();
}
} 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);
}
/**
* @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;
}
/**
* @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]));
}
}
/**
* @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;
}
/**
* @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);
}
/**
* @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;
}
/**
* @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;
}
public function testXLSXImplementsInterface()
{
$consumer = new ReaderInterfaceConsumer();
$result = $consumer->handleFile(ReaderFactory::create(Type::XLSX), self::XLSX_FILE);
$this->assertEquals(self::EXPECTED, $result);
}
<?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>";
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();
}