Export Movies to Html
#16
(2013-02-28, 20:40)Skipman Wrote: Links are back up......
I'm also interested in your script. Would you be able to put it up once more?

Thank you.
Reply
#17
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
Reply
#18
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:
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
Reply
#19
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 xlsConfusedort 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.
Reply
#20
Well don't know if anyone is still interested in getting a HTML Output from the KODI files....
If so, I've now, got everything up and running, using SQL and PHP....
If someone still has use for it. Let me know and I'll post the scripts related. :-)

Image

The Skipman
Reply
#21
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
Reply
#22
Wink 
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 Wink
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&uuml;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%'>&nbsp;</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&auml;nge - ". $movielengthmin . " minuten</b></span><br>". "\n";
echo "<span style='font-size:80%'><b>File Größe - ". $neusize . " GB&nbsp;&nbsp; - &nbsp;&nbsp;&nbsp;&nbsp;</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%'>&nbsp;</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.Cool
I run the python script once a day to update neu files....
Reply
#23
EDIT

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 .Rolleyes

Probably more mistakes in there.
But so far I'm happy with the results.
Reply

Logout Mark Read Team Forum Stats Members Help
Export Movies to Html0