2014-03-19, 17:11
2014-03-19, 18:02
Hi there....
The two files are back up ....
The Powershell script and
the xls file for output
You might have to Play around with it abit :-)
But it works for me.
Haven't done much with it for a while now, as I've been woring on a PHP - MYSQL Version of the script.
Looking good so far - just Need to sort out the resizing for the JPGs - See how I go with that.
Once I've got it Setup will post my results ;-)
Skipman
The two files are back up ....
The Powershell script and
the xls file for output
You might have to Play around with it abit :-)
But it works for me.
Haven't done much with it for a while now, as I've been woring on a PHP - MYSQL Version of the script.
Looking good so far - just Need to sort out the resizing for the JPGs - See how I go with that.
Once I've got it Setup will post my results ;-)
Skipman
2014-03-25, 22:50
Hi Skipman,
Thanks for the super fast reply. I hadn't had much of a chance to fiddle with your script until now. While my special folder structure posted a bit of a challenge (I have some multi disc movies as video_ts folders and they caused a bit of trouble), I have the script running now without errors.
I've also made a few changes to what data is presented, including a link to a YouTube trailer if available. I've also moved the covers to a subfolder.
In case anyone is interested in my formatting changes, I put up the xsl file here.
Note that for any of the additional data to work, you also have to add it to the ps1 script. Here's what the relevant line looks like in my script:
I'll be watching the thread for your new version of the script. Sounds interesting!
All the best,
Sebastian
Thanks for the super fast reply. I hadn't had much of a chance to fiddle with your script until now. While my special folder structure posted a bit of a challenge (I have some multi disc movies as video_ts folders and they caused a bit of trouble), I have the script running now without errors.
I've also made a few changes to what data is presented, including a link to a YouTube trailer if available. I've also moved the covers to a subfolder.
In case anyone is interested in my formatting changes, I put up the xsl file here.
Note that for any of the additional data to work, you also have to add it to the ps1 script. Here's what the relevant line looks like in my script:
Code:
Get-Content $filenamefull -Encoding UTF8 | select-string "<id>" , "<year>" , "<title>" , "<plot>" , "<originaltitle>" , "<sorttitle>" , "<genre>" , "<director>" , "<dateadded>" , "<runtime>" , "<mpaa>" , "<rating>" , "<trailer>" , "<name>" | Out-File -Append -Width 1500 "$ziel" -Encoding UTF8
I'll be watching the thread for your new version of the script. Sounds interesting!
All the best,
Sebastian
2014-03-28, 14:58
For anyone that's bothered by articles (the, a, an, der, die, das) at the beginning of movie titles getting in the way of sort, there's an easy to use xlsort expression you can use to fix that so articles are ignored when it comes to sort. You can also extend it to include articles of your choice.
2015-06-02, 20:01
2015-06-12, 14:47
Hi Skipman,
I'd like to give it a try. While the Powershell script worked, I haven't bothered running it again to update my existing html output. With sql and php I could perhaps have a script on my linux server handle this in a more automated fashion.
Thank you.
Cheers,
Sebastian
I'd like to give it a try. While the Powershell script worked, I haven't bothered running it again to update my existing html output. With sql and php I could perhaps have a script on my linux server handle this in a more automated fashion.
Thank you.
Cheers,
Sebastian
2015-06-19, 22:53
Hi Sebastian,
Well I've got it working for me. It#s doing just what I want :-)
I've got it set up the way you want it. Running as a script on my Server.
Hier is the Python script I wrote. It probably isn't the cleanest bit of python but I'm still low on the learning curve
And hier the php for the Webpage
Hope that helps.
Works for me ....
Still need to some cleaning up and build some checks into it - just need the time and the Motivation.
I run the python script once a day to update neu files....
Well I've got it working for me. It#s doing just what I want :-)
I've got it set up the way you want it. Running as a script on my Server.
Hier is the Python script I wrote. It probably isn't the cleanest bit of python but I'm still low on the learning curve
Code:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Getting infos form the Kodi sql database
# Before use - two rows need to be added to the Table files - one is size the other myDir
# Still lots of work to do......
import MySQLdb as mdb
import shutil
import os.path
import os
import urllib
from wand.image import Image
source = "/media/daten/alle/DVDs and BlueRays/"
destination = "/var/www/html/media/filme/images/"
con = mdb.connect('localhost', 'kodi', 'kodi', 'MyVideos90' , charset='utf8' , use_unicode=True);
with con:
cur = con.cursor(mdb.cursors.DictCursor)
cur.execute("SELECT * FROM movie JOIN files USING (idFile)")
rowsmovie = cur.fetchall()
for row in rowsmovie:
id = str(row["idMovie"]) # Changing the Movie id to a String
id_path = str(row["idPath"]) # Changing the Path id to a String
idstr = id + ".jpg" # Renaming the -poster.jpg to id.jpg for the web site
smb = row["c22"] # Getting the SMB path from KODI
spl = smb.split("/") # Spliting the SMB path using /
dir = spl[5] # Gets the folder name for the movie
folder = os.path.join(source, dir) # Getting the full path to the movie folder
file = row["strFilename"] # The file name of the movie
fileext = os.path.splitext(os.path.basename(file))[0] # The file name without extensions
poster = fileext + "-poster.jpg" # The file name of the -poster.jpg
pathposter = os.path.join(source, dir, poster) # Gives the full path of the -poster.jpg
fullpath = os.path.join(source, dir, file ) # Gives the full path to the movie file
jpg = os.path.join(source,folder , poster) # Gets the full path to the -poster.jpg
sdir = os.path.join(destination , idstr) # Where the resized jpg should be stored
# Getting the size of the movie file and saving it to file table in row = size ---- needs to be created
# Saving the folder name to file table in row = myDir ---- needs to be created
# Sould be checking if these rows exist and if not creating them ----- will get around to it
if os.path.exists(folder):
size1= os.path.getsize(fullpath) # Gives me the siz of the movie file
cur.execute ("UPDATE files SET size = %s WHERE idPath = %s", (size1, id_path))
cur.execute ("UPDATE files SET myDir = %s WHERE idPath = %s", (dir, id_path))
# Checking if there is a -poster.jpg in the movie folder.
# If not getting the url from the table art using the media_id.
# Then downloading it using urlretrieve and saving it.
# Once saved resizing it with wand Image
if not os.path.exists(pathposter):
cur.execute ("SELECT url FROM art WHERE media_id = %s AND media_type = 'movie' AND TYPE = 'poster' LIMIT 0 , 30", (id))
rowsposter = cur.fetchall()
for row in rowsposter:
urlposter = str(row["url"]) # The url to the poster
urllib.urlretrieve(urlposter, pathposter) # Getting the -poster from the url
with Image(filename = jpg ) as img: # Telling image which file to handle - named img
img.transform('', '480') # Transforming the Image - img
img.save(filename=jpg) # Saving the file to destination
# Checking if there is a id.jpg for the web site in the images Folder
# If not resizing the -poster.jpg and saving it to the Images Folder
if not (os.path.exists(sdir)):
with Image(filename = jpg ) as img: # Telling image which file to handle - named img
img.transform('', '150') # Transforming the Image - img
img.save(filename=sdir) # Saving the file to destination
con.commit()
con.close()
And hier the php for the Webpage
Code:
<?php
header("Content-Type: text/html; charset=utf-8");
include("../secure/dbaccess.php");
if (isset($_GET['sortby']) && in_array($_GET['sortby'], $orderBy)) {
$order = $_GET['sortby'];
}
$sort=$_GET[sort];
if($order==idFile){$sort=" desc";}
if($order==c07){$sort=" desc";}
if($order==dateAdded){$sort=" desc";}
if($order==idSet)
{
$order=" idSet desc, c07";
$sort=" asc";
}
if(!$order){$order=" c00";}
if(!$sort){$sort=" asc";}
$sql = "SELECT * FROM $tablemovie INNER JOIN $moviefiles USING (idFile) ORDER BY ";
$sql .= $order;
$sql .= $sort;
$verbindung = mysql_connect("$host", "$benutzer", "$passwort");
mysql_select_db($dbname, $verbindung);
mysql_query("SET NAMES 'utf8'");
$sql1 = 'SELECT COUNT(*) FROM movie';
$totalmovies = mysql_query($sql1);
$max=mysql_result($totalmovies,0,0);
$result = mysql_query($sql, $verbindung);
for($i=0;$i<mysql_num_rows($result);$i++)
{
$ergebniss[$i]=mysql_fetch_array($result);
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Skipmans Dvds and BlueRays.....</title>
</head>
<body bgcolor="#101020">
<?php
echo "<table frame='void' width='80%' align='center' border='25' cellspacing='0' cellpadding='5'>". "\n";
echo "<tr bgcolor='#ffffcc'>". "\n";
echo "<td bgcolor='#ffffcc' align='left' valign='top' colspan='3'>". "\n";
echo "<h3>Neugie's DVD Collection - Insgesammt ". $max. " Filme.</h3>". "\n";
echo "</td color='#0000ff'>" . "\n";
echo "</tr>". "\n";
echo "<td bgcolor='#ccffff' colspan='3'>" . "\n";
echo "<hr color='#0000ff'>" . "\n";
echo "</td>" . "\n";
echo "</tr>" . "\n";
echo "<tr bgcolor='#ffffcc'>". "\n";
echo "<td bgcolor='#ffffcc' align='left' valign='top' colspan='3'>". "\n";
echo nl2br('<a href="' . $_SERVER['PHP_SELF'] . '?sortby=c00">Title</a>'. "\n");
echo nl2br('<a href="' . $_SERVER['PHP_SELF'] . '?sortby=c16 ">Orginaltitle </a>'. "\n");
echo nl2br('<a href="' . $_SERVER['PHP_SELF'] . '?sortby=c07 ">Erscheinungs Jahr </a>'. "\n");
echo nl2br('<a href="' . $_SERVER['PHP_SELF'] . '?sortby=dateAdded ">Neu Hinzugefuegt </a>'. "\n");
echo nl2br('<a href="' . $_SERVER['PHP_SELF'] . '?sortby=idSet ">Film Reihe </a>'. "\n");
echo "</td color='#0000ff'>" . "\n";
echo "</tr>". "\n";
echo "<td bgcolor='#ccffff' colspan='3'>" . "\n";
echo "<hr color='#0000ff'>" . "\n";
echo "</td>" . "\n";
echo "</tr>" . "\n";
for($i=0;$i<count($ergebniss);$i++)
{
$id = $ergebniss[$i][idMovie];
$sql3 = "SELECT * FROM actorlinkmovie INNER JOIN actors USING (idActor) where idMovie = $id LIMIT 5";
$actor = mysql_query($sql3, $verbindung);
for($a=0;$a<mysql_num_rows($actor);$a++)
{
$actorarr[$a]=mysql_fetch_array($actor);
}
$neuplot = nl2br($ergebniss[$i][c01]);
$bild = $ergebniss[$i][idMovie];
$dateDE = $ergebniss[$i][dateAdded];
$rating = $ergebniss[$i][c05];
$neurating = number_format($rating, 2, '.', '');
$movielenghtsec = $ergebniss[$i][c11];
$movielengthmin = ($movielenghtsec / 60);
$size = $ergebniss[$i][size];
$sizegb = $size / 1073741824;
$neusize = number_format($sizegb, 2, '.', '');
$dir = $ergebniss[$i][myDir];
$file = $ergebniss[$i][strFilename];
echo "<tr bgcolor='#ffffcc'>". "\n";
echo "<td bgcolor='#ffffcc' align='left' valign='top' colspan='3'>". "\n";
echo "<h4>|- ". $ergebniss[$i][c00]. " - ". $ergebniss[$i][c07]. " -|- " . $ergebniss[$i][c03] . " -|</h4>"."\n";
echo "</td color='#0000ff'>" . "\n";
echo "</tr>". "\n";
echo "<td bgcolor='#000000' style='padding:0px' valign='center' rowspan='2'>". "\n";
echo "<img width='150' src='/media/filme/images/". $bild.".jpg'>". "\n";
echo "</td>". "\n";
echo "<td bgcolor='#ffffcc' align='left' valign='top'>". "\n";
echo "<span style='font-size:95%'><b>Orginal Title - ". $ergebniss[$i][c16]. " - ". $ergebniss[$i][c07]. "</b></span><br>". "\n";
echo "<span style='font-size:95%'><b>Director - ". $ergebniss[$i][c15]. "</b></span><br>". "\n";
echo "<span style='font-size:95%'><b>Hinzugefügt am : ". date("d.m.Y", strtotime($dateDE)). "</b></span><br>". "\n";
echo "<span style='font-size:95%'><b><a href='http://www.imdb.com/title/".$ergebniss[$i][c09]."' target='_blank'>Movie Infos from IMDB COM</a></b><br></span>". "\n";
echo "<span style='font-size:80%'> </span><br>". "\n";
echo "<span style='font-size:80%'><b>Rating - ". $neurating . "</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>Film Länge - ". $movielengthmin . " minuten</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>File Größe - ". $neusize . " GB - </b>";
echo "<b><a href='". $ftpmovies ."/".$dir."/".$file. "' target='_blank'>FTP</a></b></span><br>". "\n";
echo "</td>". "\n";
echo "<td bgcolor='#ffffcc' width='350' align='left' valign='top'>". "\n";
echo "<span style='font-size:95%'><b>Actors -</b></span><br>". "\n";
echo "<span style='font-size:40%'> </span><br>". "\n";
echo "<span style='font-size:80%'><b>- ". $actorarr[0][strActor] . " as " . $actorarr[0][strRole] ."</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>- ". $actorarr[1][strActor] . " as " . $actorarr[1][strRole] ."</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>- ". $actorarr[2][strActor] . " as " . $actorarr[2][strRole] ."</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>- ". $actorarr[3][strActor] . " as " . $actorarr[3][strRole] ."</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>- ". $actorarr[4][strActor] . " as " . $actorarr[4][strRole] ."</b></span>". "\n";
echo "</td>". "\n";
echo "</tr>". "\n";
echo "<tr>". "\n";
echo "<td bgcolor='#ffffcc' height='120' align='left' valign='top' colspan='2'>". "\n";
echo $neuplot. "\n";
echo "</td>" . "\n";
echo "</tr>" . "\n";
echo "<tr>" . "\n";
echo "<td bgcolor='#ccffff' colspan='3'>" . "\n";
echo "<hr color='#0000ff'>" . "\n";
echo "</td>" . "\n";
echo "</tr>" . "\n";
}
mysql_close($verbindung);
?>
</table>
</body>
</html>
Hope that helps.
Works for me ....
Still need to some cleaning up and build some checks into it - just need the time and the Motivation.
I run the python script once a day to update neu files....
2015-06-20, 11:26
EDIT
Changed the line...
to
as it wasn't sorting the Actors properly .
Probably more mistakes in there.
But so far I'm happy with the results.
Changed the line...
Code:
$sql3 = "SELECT * FROM actorlinkmovie INNER JOIN actors USING (idActor) where idMovie = $id LIMIT 5";
to
Code:
$sql3 = "SELECT * FROM actorlinkmovie INNER JOIN actors USING (idActor) where idMovie = $id ORDER BY iOrder LIMIT 5";
as it wasn't sorting the Actors properly .
Probably more mistakes in there.
But so far I'm happy with the results.