Change status of a virtual page (CSV)

tja · August 20, 2021, 6:52pm

I have a problem where I just can’t get ahead. I am including a CSV file as a Virtual Page and have followed the tutorial for this (Merging content sources | Kirby CMS).

Everything works so far, but everything breaks down when the status of the Virtual Page is changed, for example from listed to unlisted.

The page is visible only as draft in the content folder, but it is still listed in the panel. Also - and this is the bad thing - all previously assigned image elements are deleted for entries that use the page model that is responsible for the import. They are still present in the content folder, but they are no longer displayed in the panel and frontend. Also the respective media folder with the corresponding hash is empty.

Does anyone have an idea how I could get ahead here?

texnixe · August 20, 2021, 7:39pm

The problem probably arises from the fact that the pages have a prepended number: 'num' => 0, so they are by default listed. Do all your virtual csv pages have a folder in the filesystem?

tja · August 20, 2021, 9:06pm

Yes. Image and text elements are added to each page.

texnixe · August 20, 2021, 9:42pm

Is there are reason why you use the virtual pages approach if you have a content folder for each entry anyway?

tja · August 20, 2021, 10:03pm

Yes. It might be be easier to do it vanilla kirby and I am considering it. But the integration of a CSV-file is the first step in automating the system. So having the option would be very welcome.

texnixe · August 20, 2021, 11:37pm

You would have to extend the model to make this work, or rather, you will need a model for the single page as well, to keep the virtual stuff and the stuff in the file system in sync. But I nevertheless wonder if it wouldn’t make more sense to create the pages from the csv file programmatically and if need be to update. But I don’t know your use case for using the csv data.

tja · August 21, 2021, 12:16pm

Thank you. I think I see the challenge here and I might try another approach.

wildthing · May 24, 2024, 10:10am

Im jump in this conversation
Because have similar request

I have data placed in spreadsheet.
I have many sites that will use this data in feature based on it parts
I wish to be connected to this data and have ability create virtual pages (in some other topic I’m already write about this)

Right Now I have such model

<?php

class GamesPage extends Page
{
	static $subpages = null;

	public function subpages()
	{
		if (static::$subpages) {
			return static::$subpages;
		}

		return static::$subpages = Pages::factory($this->inventory()['children'], $this);
	}

	public function children(): Pages
	{
		if ($this->children instanceof Pages) {
			return $this->children;
		}

		$file = $this->root() . '/games.json';
		$array = json_decode(@file_get_contents($file), true);

		if ($array == null or (filemtime($file) + 3600) < time()) {
			$id = site()->google_docs_key();
			$gid = '0';

			$spreadsheet = 'https://docs.google.com/spreadsheets/d/' . $id . '/export?format=csv&gid=' . $gid;

			$file_headers = @get_headers($spreadsheet);
			if ($file_headers[0] == 'HTTP/1.1 404 Not Found' || $file_headers[0] == 'HTTP/1.0 404 Not Found') {
				$array = [];
				file_put_contents($file, json_encode($array));
			} else {
				$csv = file_get_contents($spreadsheet);
				$csv = explode("\r\n", $csv);
				$array = array_map('str_getcsv', $csv);
				array_walk($array, function(&$a) use ($array) {
					$a = array_combine($array[0], $a);
				});
				array_shift($array);
				file_put_contents($file, json_encode($array));
			}
		}

		$selectedSoftwares = $this->selectedGames()->split();
		$children = array_filter($array, function($game) use ($selectedSoftwares) {
			$software = Str::slug($game['Software']);
			return in_array($software, $selectedSoftwares);
		});

		$children = array_map(function ($game) {
			$slug = Str::slug($game['Game']);
			$software = Str::slug($game['Software']);
			$final_slug = $slug . '-' . $software;
			$page = $this->subpages()->find($final_slug);
			return [
				'slug'     => $final_slug,
				'template' => 'game',
				'model'    => 'game',
				'content'  => [
					'title'        => $game['Game'],
					'description'  => $page ? $page->description()->value() : null,
					'text'         => $page ? $page->text()->value() : null,
					'rating'       => $page ? $page->rating()->value() : null,
					'iframe_url'   => $game['iFrame'],
					'date'         => $game['Release Date'],
					'software'     => $game['Software'],
					'cover'        => $slug . '-' . $software . '-logo.jpg',
					'bg'           => $slug . '-' . $software . '-bg.jpg',
					'rtp'          => $game['RTP'],
					'volatility'   => $game['Volatility'],
					'reels'        => $game['Reels'],
					'paylines'     => $game['Paylines'],
					'min_bet'      => $game['Min Bet'],
					'max_bet'      => $game['Max Bet'],
					'category'     => $game['Category'],
					'features'     => $game['Features'],
					'themes'       => $game['Themes'],
				],
				'status'       => $page ? $page->content()->get('status')->value() : null,
			];
		}, $children);

		return $this->children = Pages::factory($children, $this);
	}

	public function gamesFromSpreadsheet()
	{
		$file = $this->root() . '/games.json';
		$array = json_decode(@file_get_contents($file), true);

		// Collect unique software options
		$softwareOptions = array_unique(array_column($array, 'Software'));
		return array_map(function ($software) {
			return [
				'text' => $software,
				'value' => Str::slug($software),
			];
		}, $softwareOptions);
	}
}

It gives me possibility to filter wich games should be imported filtered by Software
What I can’t do is to delete pages if deselect Software, I can’t change status (I’m try do it in this model but it doesn’t work)
Im also think that it must be a Game Model but didn’t understand what should be there

In total I need to have ability import page, ability to make changes inside imported pages with part of content, ability to change status for this pages even if it imported (for example Im select all games from one Software provider but for some reason don’t need some of its games), ability to delete imported pages if I deselect Software from filter

Also As you can see in my model I’m import in json all data and after that create pages based on filter, I think for speed may be better create filtered json with only selected Software

Don’t know if I wrote everything clear, but I think it would be great option to create some universal Guide to work with imported data with ubility not only import all data, but to filter it, change status or even delete

wildthing · May 25, 2024, 9:18am

Im trying to create a model for Game based on the DB example for comments but have had no luck with it

<?php

use Kirby\Cms\Page;

class GamePage extends Page
{
	public function changeStatusToDraft(): static
	{
		return $this->changeStatus('draft');
	}

	public function changeStatusToListed(int $position = null): static
	{
		$num = $this->createNum($position);

		if ($this->status() === 'listed' && $num === $this->num()) {
			return $this;
		}

		return $this->changeStatus('listed', $num);
	}

	public function changeStatusToUnlisted(): static
	{
		if ($this->status() === 'unlisted') {
			return $this;
		}

		return $this->changeStatus('unlisted');
	}

	public function changeStatus(string $status, int $num = null): static
	{
		$data = ['status' => $status];
		if ($num !== null) {
			$data['num'] = $num;
		}

		try {
			$this->update($data);
		} catch (Exception $e) {
			echo 'Error: ' . $e->getMessage();
		}

		return $this;
	}
}

Games Model

<?php

use Kirby\Cms\Page;
use Kirby\Cms\Pages;

class GamesPage extends Page
{
	static $subpages = null;

	public function subpages()
	{
		if (static::$subpages) {
			return static::$subpages;
		}

		return static::$subpages = Pages::factory($this->inventory()['children'], $this);
	}
	
	public function children(): Pages
	{
		if ($this->children instanceof Pages) {
			return $this->children;
		}

		$file = $this->root() . '/games.json';
		$array = json_decode(@file_get_contents($file), true);

		if ($array == null || (filemtime($file) + 3600) < time()) {
			$id = site()->google_docs_key();
			$gid = '0';

			$spreadsheet = 'https://docs.google.com/spreadsheets/d/' . $id . '/export?format=csv&gid=' . $gid;

			$file_headers = @get_headers($spreadsheet);
			if ($file_headers[0] == 'HTTP/1.1 404 Not Found' || $file_headers[0] == 'HTTP/1.0 404 Not Found') {
				$array = [];
				file_put_contents($file, json_encode($array));
			} else {
				$csv = file_get_contents($spreadsheet);
				$csv = explode("\r\n", $csv);
				$array = array_map('str_getcsv', $csv);
				array_walk($array, function(&$a) use ($array) {
					$a = array_combine($array[0], $a);
				});
				array_shift($array);
				file_put_contents($file, json_encode($array));
			}
		}

		$selectedSoftwares = $this->selectedGames()->split();
		$children = array_filter($array, function($game) use ($selectedSoftwares) {
			$software = Str::slug($game['Software']);
			return in_array($software, $selectedSoftwares);
		});

		$children = array_map(function ($game) {
			$slug = Str::slug($game['Game']);
			$software = Str::slug($game['Software']);
			$final_slug = $slug . '-' . $software;
			$page = $this->subpages()->find($final_slug);

			return [
				'slug'     => $final_slug,
				'template' => 'game',
				'model'    => 'game',
				'content'  => [
					'title'        => $game['Game'],
					'description'  => $page ? $page->description()->value() : null,
					'text'         => $page ? $page->text()->value() : null,
					'rating'       => $page ? $page->rating()->value() : null,
					'iframe_url'   => $game['iFrame'],
					'date'         => $game['Release Date'],
					'software'     => $game['Software'],
					'cover'        => $slug . '-' . $software . '-logo.jpg',
					'bg'           => $slug . '-' . $software . '-bg.jpg',
					'rtp'          => $game['RTP'],
					'volatility'   => $game['Volatility'],
					'reels'        => $game['Reels'],
					'paylines'     => $game['Paylines'],
					'min_bet'      => $game['Min Bet'],
					'max_bet'      => $game['Max Bet'],
					'category'     => $game['Category'],
					'features'     => $game['Features'],
					'themes'       => $game['Themes'],
				],
				'status'   => 'draft', // or 'unlisted', or 'listed' based on your logic
			];
		}, $children);

		return $this->children = Pages::factory($children, $this);
	}

	public function gamesFromSpreadsheet()
	{
		$file = $this->root() . '/games.json';
		$array = json_decode(@file_get_contents($file), true);

		// Collect unique software options
		$softwareOptions = array_unique(array_column($array, 'Software'));
		return array_map(function ($software) {
			return [
				'text' => $software,
				'value' => Str::slug($software),
			];
		}, $softwareOptions);
	}
}

Is there any solution to change status for virtual pages that works combine with real pages

wildthing · May 25, 2024, 5:16pm

I think I understand what to do) but still didn’t find how to make it works

We can create default column in spreadsheet “Status”
When I generate Json this column will be still empty
And from this we need to do 2 steps
Somehow on change status catch this status and set it inside json
in this way generated pages list should be updated
the second step is when spreadsheet will be updated merge it with created json all columns except Status column that is updates from panel

I create updated model but how to put status inside json I still unluck with this

<?php

class GamesPage extends Page
{
	static $subpages = null;

	public function subpages()
	{
		if (static::$subpages) {
			return static::$subpages;
		}
		return static::$subpages = Pages::factory($this->inventory()['children'], $this);
	}

	public function children(): Pages
	{
		if ($this->children instanceof Pages) {
			return $this->children;
		}

		$array = $this->fetchJson();

		$selectedSoftwares = $this->selectedGames()->split();
		$children = array_filter($array, function($game) use ($selectedSoftwares) {
			$software = Str::slug($game['Software']);
			return in_array($software, $selectedSoftwares);
		});

		$children = array_map(function ($game) {
			$slug = Str::slug($game['Game']);
			$software = Str::slug($game['Software']);
			$final_slug = $slug . '-' . $software;
			$page = $this->subpages()->find($final_slug);

			return [
				'slug'     => $final_slug,
				'template' => 'game',
				'model'    => 'game',
				'num' => '0',
				'content'  => [
					'title'        => $game['Game'],
					'description'  => $page ? $page->description()->value() : null,
					'text'         => $page ? $page->text()->value() : null,
					'rating'       => $page ? $page->rating()->value() : null,
					'iframe_url'   => $game['iFrame'],
					'date'         => $game['Release Date'],
					'software'     => $game['Software'],
					'cover'        => $slug . '-' . $software . '-logo.jpg',
					'bg'           => $slug . '-' . $software . '-bg.jpg',
					'rtp'          => $game['RTP'],
					'volatility'   => $game['Volatility'],
					'reels'        => $game['Reels'],
					'paylines'     => $game['Paylines'],
					'min_bet'      => $game['Min Bet'],
					'max_bet'      => $game['Max Bet'],
					'category'     => $game['Category'],
					'features'     => $game['Features'],
					'themes'       => $game['Themes'],
				],
				'status' => $game['Status'],
			];
		}, $children);

		return $this->children = Pages::factory($children, $this);
	}
	
	protected function fetchJson()
	{
		$file = $this->root() . '/games.json';
		$array = json_decode(@file_get_contents($file), true);
		
		if ($array == null || (filemtime($file) + 3600) < time()) {
			$array = $this->fetchGamesData();
			file_put_contents($file, json_encode($array));
		}
		
		return $array;
	}

	protected function fetchGamesData()
	{
		$id = site()->google_docs_key();
		$gid = '0';
		$spreadsheet = 'https://docs.google.com/spreadsheets/d/' . $id . '/export?format=csv&gid=' . $gid;
		$file_headers = @get_headers($spreadsheet);
	
		if ($file_headers[0] == 'HTTP/1.1 404 Not Found' || $file_headers[0] == 'HTTP/1.0 404 Not Found') {
			return [];
		} else {
			$csv = file_get_contents($spreadsheet);
			$csv = explode("\r\n", $csv);
			$array = array_map('str_getcsv', $csv);
			array_walk($array, function (&$a) use ($array) {
				$a = array_combine($array[0], $a);
			});
			array_shift($array);
	
			// Merge status information from existing JSON file
			$existingArray = $this->fetchJson();
			foreach ($array as &$game) {
				$slug = Str::slug($game['Game']);
				$software = Str::slug($game['Software']);
				$gameSlug = $slug . '-' . $software;
				foreach ($existingArray as $existingGame) {
					if (Str::slug($existingGame['Game']) . '-' . Str::slug($existingGame['Software']) === $gameSlug) {
						$game['status'] = $existingGame['status'] ?? 'draft'; // Set default status if not present
						break;
					}
				}
			}
	
			file_put_contents($this->root() . '/games.json', json_encode($array));
	
			return $array;
		}
	}
	
	public function gamesFromSpreadsheet()
	{
		$array = $this->fetchJson();
	
		// Collect unique software options
		$softwareOptions = array_unique(array_column($array, 'Software'));
		return array_map(function ($software) {
			return [
				'text' => $software,
				'value' => Str::slug($software),
			];
		}, $softwareOptions);
	}
	
	public function changeGameStatus($slug, $status)
	{
		$array = $this->fetchJson();
	
		foreach ($array as $game) {
			$gameSlug = Str::slug($game['Game']) . '-' . Str::slug($game['Software']);
			if ($gameSlug === $slug) {
				$game['Status'] = $status;
				break;
			}
		}
	
		file_put_contents($file, json_encode($array));
	
		// Reset children cache to reload updated data
		$this->children = null;
	}
}

Topic		Replies	Views
CSV Example without Sub Pages Kirby 3	3	344	May 7, 2022
Using CSV as Virtual Pages and Update Solved ✅	3	352	June 24, 2021
Kirby w Database - Files / Status Issue Questions 🤔	3	321	March 17, 2021
Import CSV -> Pages Plugins plugin , panel	4	966	March 20, 2020
Handling images resizing when using a CSV as a database Solved ✅	6	574	March 20, 2020

Change status of a virtual page (CSV)

Related topics