randomphoto-piwigo: fixed problem with NULL fields in MySQL
authorSabine Engelhardt (Becker) <frosch@atari-frosch.de>
Sun, 20 Oct 2019 15:42:49 +0000 (17:42 +0200)
committerSabine Engelhardt (Becker) <frosch@atari-frosch.de>
Sun, 20 Oct 2019 15:42:49 +0000 (17:42 +0200)
CHANGELOG
modules/randomphoto-piwigo.py

index a916657..a595cf5 100644 (file)
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -1,3 +1,20 @@
+2019-10-20 frosch@atari-frosch.de
+
+    * module randomphoto-piwigo.py: Problem was still there, additionally
+      the same happened when author was None (NULL in MySQL). Now reading
+      both fields using COALESCE to set a default value if the field content
+      is NULL. Thanks to Refurio Anarcho <https://pluspora.com/people/a2efef60d6c70136b502005056264835>
+      for showing me this solution.
+
+
+2019-10-08 frosch@atari-frosch.de
+
+    * module randomphoto-piwigo.py: Ended up with wrong title, links etc.
+      and wrong photo ID if license in Piwigo's table pwg_copyrights_media
+      is set to None; now checking first whether it is None and if, change
+      to 0.
+
+
 2019-09-06 frosch@atari-frosch.de
 
     * module randomphoto-piwigo.py: Links to all Piwigo albums are listed
index 8669cdb..585aea0 100755 (executable)
@@ -3,6 +3,15 @@
 
 from sys import argv
 
+def fetchalbumname(cursor, album):
+    getalbumname = "SELECT name, id_uppercat FROM pwg_categories WHERE id = " + str(album)
+    cursor.execute(getalbumname)
+    albumdata = cursor.fetchone()
+    albumname = albumdata[0]
+    upper = albumdata[1]
+    return albumname, upper 
+
+
 def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, randomsnippet):
     import pymysql
     import random
@@ -58,7 +67,8 @@ def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, r
         picdata['photoid'] = str(photo) 
 
         # request the chosen photo with additional information
-        getinfo = "SELECT path, date_creation, name, comment, author FROM pwg_images where id = " + str(photo)
+        getinfo = "SELECT path, date_creation, name, comment, COALESCE(author, 'unknown'), file "
+        getinfo += "FROM pwg_images where id = " + str(photo)
         db = pymysql.connect(dbhost, dbuser, dbpass, dbname)
         cursor = db.cursor()
         cursor.execute(getinfo)
@@ -67,6 +77,7 @@ def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, r
         # ('./upload/2017/12/26/20171226004242-fc764b7e.jpg',
         # datetime.datetime(2009, 4, 28, 20, 45, 17),
         # '20090428-204517', None, 'Atari-Frosch')
+        db.close()
 
         # 0 = path; example: ./upload/2018/08/12/20180812185450-b8b65c68.jpg
         filepath = picinfo[0][1:]
@@ -85,13 +96,49 @@ def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, r
             img = img.resize((wsize, baseheight), Image. ANTIALIAS)
             img.save(pictargetfile)
 
+        pictimestamp = ""
+        # 1 = date_creation; 5 = original file name
         pictime = picinfo[1]
-        # 1 = date_creation; this field has the format datetime
+        origfile = picinfo[5]
         if pictime != None:
             if pictime.strftime("%H:%M:%S") == "00:00:00":
-                pictimestamp = pictime.strftime("%d.%m.%Y")
+                pictimestamp = "am " + pictime.strftime("%d.%m.%Y")
             else:
-                pictimestamp = pictime.strftime("%d.%m.%Y, %H:%M")
+                pictimestamp = "am " + pictime.strftime("%d.%m.%Y") + " um " + pictime.strftime("%H:%M") + " Uhr"
+        elif origfile.count("-") == 2:
+            origfile = origfile.replace(".jpg", "")
+            origfileparts = origfile.split("-")
+            origfiledate = origfileparts[2]
+            if len(origfiledate) == 6:
+                origfileyear = origfiledate[0] + origfiledate[1] + origfiledate[2] + origfiledate[3]
+                origfilemonth = int(origfiledate[4] + origfiledate[5])
+                if origfilemonth == 1:
+                    filemonth = "Januar"
+                elif origfilemonth == 2:
+                    filemonth = "Februar"
+                elif origfilemonth == 3:
+                    filemonth = "März"
+                elif origfilemonth == 4:
+                    filemonth = "April"
+                elif origfilemonth == 5:
+                    filemonth = "Mai"
+                elif origfilemonth == 6:
+                    filemonth = "Juni"
+                elif origfilemonth == 7:
+                    filemonth = "Juli"
+                elif origfilemonth == 8:
+                    filemonth = "August"
+                elif origfilemonth == 9:
+                    filemonth = "September"
+                elif origfilemonth == 10:
+                    filemonth = "Oktober"
+                elif origfilemonth == 11:
+                    filemonth = "November"
+                elif origfilemonth == 12:
+                    filemonth = "Dezember"
+                else:
+                    filemonth = ""
+                pictimestamp = "im " + filemonth + " " + origfileyear
         else:
             pictimestamp = ""
         picdata['phototime'] = pictimestamp
@@ -108,17 +155,59 @@ def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, r
         # 4 = author name
         picdata['author'] = picinfo[4]
 
-        getcat = "SELECT category_id FROM pwg_image_category where image_id = " + str(photo)
+        # https://media.atari-frosch.de/picture.php?/1648/category/738        
+        if not piwigosite.endswith("/"):
+             piwigosite += "/"
+
+        getcat = "SELECT category_id FROM pwg_image_category WHERE image_id = " + str(photo)
+        # These are the albums the photo is in, not the tags for the photo!
         db = pymysql.connect(dbhost, dbuser, dbpass, dbname)
         cursor = db.cursor()
         cursor.execute(getcat)
-        category = cursor.fetchone()
+        albumlist = cursor.fetchall()
+        db.close()
 
-        if not piwigosite.endswith("/"):
-             piwigosite += "/"
+        piwigoalbums = []
+        for album in albumlist:
+            curalbum = str(album)
+            curalbum = curalbum.replace("(", "")
+            curalbum = curalbum.replace(")", "")
+            curalbum = curalbum[:-1]
+            albumlink = piwigosite + "picture.php?/" + str(photo) + "/category/" + curalbum
+            db = pymysql.connect(dbhost, dbuser, dbpass, dbname)
+            cursor = db.cursor()
+            a, upper = fetchalbumname(cursor, curalbum)
+            db.close()
+            albumname = str(a)
+            while upper != None:
+                db = pymysql.connect(dbhost, dbuser, dbpass, dbname)
+                cursor = db.cursor()
+                a, upper = fetchalbumname(cursor, upper)
+                db.close()
+                albumname = str(a) + " / " + albumname
+            piwigoalbums.append('<li><a href="' + albumlink + '">' + albumname + '</a></li>')
 
-        picsource = piwigosite + "picture.php?/" + str(photo) + "/category/" + str(category[0])
-        picdata['piclink'] = picsource
+        getlicense = "SELECT COALESCE(cr_id, 0) FROM pwg_copyrights_media WHERE media_id = " + str(photo)
+        db = pymysql.connect(dbhost, dbuser, dbpass, dbname)
+        cursor = db.cursor()
+        cursor.execute(getlicense)
+        l = cursor.fetchone()
+        db.close()
+
+        if lic == 0:
+            licensename = "keine freie Lizenz"
+        elif l[0] == 1:
+            licensename = "CC-BY"
+        elif l[0] == 2:
+            licensename = "CC-BY-SA"
+        elif l[0] == 3:
+            licensename = "CC-BY-ND"
+        elif l[0] == 4:
+            licensename = "CC-BY-NC"
+        elif l[0] == 5:
+            licensename = "CC-BY-NC-SA"
+        elif l[0] == 6:
+            licensename = "CC-BY-NC-ND"
 
         curtime = datetime.now()
         logtime = curtime.strftime("%b %d %H:%M:%S")
@@ -127,18 +216,23 @@ def randomphoto(piwigoconfig, basewidth, baseheight, randompiclog, piwigosite, r
         log.write(out)
         log.close()
 
-
         f = open(randomsnippet, "r")
         snipcontent = f.read()
         f.close()
 
-        snipcontent = snipcontent.replace("[PICSOURCE]", picdata['piclink'])
         snipcontent = snipcontent.replace("[TITLE]", picdata['title'])
         snipcontent = snipcontent.replace("[PICTIME]", picdata['phototime'])
         snipcontent = snipcontent.replace("[AUTHOR]", picdata['author'])
-        snipcontent = snipcontent.replace("[DESCR]", picdata['descr'])
         snipcontent = snipcontent.replace("[PHOTOID]", picdata['photoid'])
         snipcontent = snipcontent.replace("[NUM]", picdata['number'])
+        snipcontent = snipcontent.replace("[LICENSE]", licensename)
+
+        allalbums = ""
+        for album in piwigoalbums:
+            allalbums += '\n              ' + album
+        snipcontent = snipcontent.replace("[ALBUM]", allalbums)
+
+        snipcontent = snipcontent.replace("[DESCR]", picdata['descr'])
 
         from subprocess import call
         contentfile = myfilename.replace(".py", ".htm")