Clock

Khmer Web

Gateway to web application
Creating Category Database
Edit 
#models/categorydb.py
import os
import psycopg2

def createTable(): 
  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()

  SQL = '''CREATE TABLE IF NOT EXISTS CATEGORY(
  ID TEXT,
  TITLE TEXT,
  AUTHOR TEXT,
  POSTDATE DATE,
  POSTTIME TIME,
  CONTENT TEXT
  )'''

  cursor.execute(SQL)
  
  conn.commit()
  conn.close()

def insert(*post):
  createTable()
  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()

  cursor.execute("INSERT INTO CATEGORY (ID, TITLE, AUTHOR, POSTDATE, POSTTIME, CONTENT) VALUES %s ", (post,))
  
  conn.commit()
  conn.close()

def select(amount, id=None, page=0):
  createTable()

  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()
  if id and (amount == 1):
    cursor.execute("SELECT * FROM CATEGORY WHERE ID = '" + str(id) +"'")
  elif page:
    SQL = "SELECT * FROM CATEGORY ORDER BY POSTDATE DESC, POSTTIME DESC OFFSET %s ROWS FETCH NEXT %s ROWS ONLY"
    cursor.execute(SQL, (amount*page, amount))
  elif amount == 'all':
    cursor.execute("SELECT * FROM CATEGORY ORDER BY POSTDATE DESC, POSTTIME DESC")
  else:
    cursor.execute("SELECT * FROM CATEGORY ORDER BY POSTDATE DESC, POSTTIME DESC LIMIT " + str(amount))
    
  result = cursor.fetchall()
  return result

def check(username):
  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()

  cursor.execute("SELECT USERNAME FROM USERS WHERE USERNAME = '"+ username + "' LIMIT 1")
  result = cursor.fetchone()
  if result:
    return True
  else:
    return False

def delete(id):
  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()

  cursor.execute("DELETE FROM CATEGORY WHERE ID = '" + str(id) + "'")

  conn.commit()
  conn.close()

def update(*args):
  if 'DYNO' in os.environ:
    DATABASE_URL = os.environ['DATABASE_URL']
    conn = psycopg2.connect(DATABASE_URL, sslmode='require')
    cursor = conn.cursor()
  else: 
    conn = psycopg2.connect(
      database="postgres", 
      user="postgres", 
      password="sokhavuth", 
      host="localhost", 
      port="5432"
    )

    cursor = conn.cursor()

  sql = "UPDATE CATEGORY SET TITLE = %s, POSTDATE = %s, POSTTIME = %s, CONTENT = %s WHERE ID = '%s' "
  
  cursor.execute(sql, args)
  
  conn.commit()
  conn.close()

#controllers/category.py
import config, lib, datetime, uuid
from pytz import timezone
from bottle import route, template, request, redirect, response
from models import postdb, categorydb

def getTimeZone():
  khtz = timezone('Asia/Phnom_Penh')
  date = datetime.datetime.now().astimezone(tz=khtz).strftime('%d-%m-%Y')
  time = datetime.datetime.now().astimezone(tz=khtz).strftime('%H:%M:%S')
  return (date, time)

@route('/category')
def post():
  config.kargs['blogTitle'] = "ទំព័រ​ជំពូក"
  config.kargs['posts'] = categorydb.select(config.kargs['dashboardPostLimit'])
  config.kargs['thumbs'] = lib.getPostThumbs(config.kargs['posts'])
  config.kargs['datetime'] = getTimeZone()
  config.kargs['page'] = 1
  author = request.get_cookie("logged-in", secret=config.kargs['secretKey'])
  if author:
    config.kargs['author'] = author
    config.kargs['showEdit'] = True

  return template('dashboard/category', data=config.kargs)

@route('/category/<id:int>')
def post(id):
  config.kargs['blogTitle'] = "ទំព័រ​ការផ្សាយ"
  config.kargs['post'] = categorydb.select(1, id)
  config.kargs['posts'] = categorydb.select(config.kargs['frontPagePostLimit'])
  config.kargs['thumbs'] = lib.getPostThumbs(config.kargs['posts'])
  config.kargs['page'] = 1
  author = request.get_cookie("logged-in", secret=config.kargs['secretKey'])
  if author:
    config.kargs['showEdit'] = True

  return template('dashboard/category', data=config.kargs)

@route('/categorizing', method="POST")
def posting():
  author = request.get_cookie("logged-in", secret=config.kargs['secretKey'])
  if ((author != "Guest") and categorydb.check(author)):
    title = request.forms.getunicode('fpost-title')
    if title == "":
      title = "untitled"

    postdate = request.forms.getunicode('fpost-date')
    posttime = request.forms.getunicode('fpost-time')
    content = request.forms.getunicode('fcontent')

    try:
      postdate = datetime.datetime.strptime(postdate, "%d-%m-%Y")
    except ValueError:
      config.kargs['message'] = 'ទំរង់​កាលបរិច្ឆេទ​មិន​ត្រឹមត្រូវ!'
      return template('dashboard/category', data=config.kargs)

    try:
      posttime = datetime.datetime.strptime(posttime, "%H:%M:%S")
    except ValueError:
      config.kargs['message'] = 'ទំរង់​ពេល​វេលា​មិន​ត្រឹមត្រូវ!'
      return template('dashboard/category', data=config.kargs)

    if 'postId' in config.kargs:
      id = config.kargs['postId']
      categorydb.update(title, postdate, posttime, content, id)
      del config.kargs['postId']
    else:
      categorydb.insert(str(uuid.uuid4().int), title, author, postdate, posttime, content)
  
  redirect('/category')

@route('/category/delete/<id:int>')
def delete(id):
  author = request.get_cookie("logged-in", secret=config.kargs['secretKey'])
  if ((author != "Guest") and postdb.check(author)):
    categorydb.delete(id)

  redirect('/category')

@route('/category/edit/<id:int>')
def edit(id):
  author = request.get_cookie("logged-in", secret=config.kargs['secretKey'])
  if ((author != "Guest") and categorydb.check(author)):
    config.kargs['blogTitle'] = "ទំព័រ​កែ​តំរូវ"
    config.kargs['posts'] = categorydb.select(config.kargs['dashboardPostLimit'])
    config.kargs['thumbs'] = lib.getPostThumbs(config.kargs['posts'])
    config.kargs['post'] = categorydb.select(1, id)
    config.kargs['edit'] = True
    config.kargs['postId'] = id
    config.kargs['page'] = 1
    return template('dashboard/category', data=config.kargs)
  
  redirect('/category')

@route('/category/paginate')
def paginate():
  postLimit = config.kargs['dashboardPostLimit']
  posts = categorydb.select(postLimit, page=config.kargs['page'])
  
  def toString(post):
    post[3] = post[3].strftime('%d-%m-%Y')
    post[4] = post[4].strftime('%H:%M:%S')

  if posts:
    config.kargs['page'] += 1
    posts = [list(obj) for obj in posts ]

    [toString(obj) for obj in posts]
    thumbs = lib.getPostThumbs(posts)
    print(posts)
    return {'json':posts, 'thumbs':thumbs}
  else:
    return {'json':0}

<!--views/dashboard/home.tpl-->
%include('./dashboard/partials/header.tpl')

<style>
  #content{
    min-height: 350px;
    background: white;
    padding: 0;
    box-sizing: border-box;
  }

  #content .ck-editor__editable {
    min-height: 350px !important;
  }

  #content #post-title{
    width: 100%;
    box-sizing: border-box;
    padding: 5px 10px;
    font: 16px/1.5 Koulen;
  }

  #content ::placeholder{
    opacity: .4;
  }

  #content #bottombar{
    background: #ebebeb;
    padding: 5px;
    border: 1px solid #bebbbb;
  }

  #content #bottombar .bottom-widget{
    font: 14px/1.5 OdorMeanChey;
    height: 30px;
  }

  #content #bottombar input:hover{
    cursor: pointer;
  }

  #content #bottombar #category{
    min-width: 80px;
    font:bold 14px/1.5 'Lucida Sans' !important;
  }

  #content #bottombar #category option{
    font:bold 14px/1.5 'Lucida Sans' !important;
  }

  #content .post-time{
    height: 24px !important;
    font:bold 14px/1.5 'Lucida Sans' !important;
    width: 100px;
  }

</style>

<div  id='main' class='main region'>

  %include('./dashboard/partials/sidebar.tpl')

  <section id='content' class='content'>
    
    <form action="/posting" method="post">
      %if 'edit' in data:
      <input id="post-title" name="fpost-title" value="{{data['post'][0][1]}}" type="text" placeholder="ចំណង​ជើង​អត្ថបទ" required />
      <textarea name="fcontent" id="editor">{{data['post'][0][6]}}</textarea>
      <div id="bottombar">
        <input id="submit" class="bottom-widget" type="submit" value="ចុះ​ផ្សាយ">
        <select class="bottom-widget" id="category" name="fcategory">
          %if data['categories']:
            %for category in data['categories']:
              <option>{{category[1]}}</option>
            %end
          %end
        </select>
        <script>$("#category").val("{{data['post'][0][5]}}").change();</script>
        <input id="post-date" value="{{data['post'][0][3].strftime('%d-%m-%Y')}}" class="bottom-widget post-time" type="text" name="fpost-date" />
        <input id="post-time" value="{{data['post'][0][4].strftime('%H:%M:%S')}}" class="bottom-widget post-time" type="text" name="fpost-time" />
        <input disabled style="background:white;text-align:center;" type='text' value="{{data['post'][0][2]}}" id="post-author" class="bottom-widget post-time" />
      </div>
      %del data['edit']
      %else:
      <input id="post-title" name="fpost-title" type="text" placeholder="ចំណង​ជើងអត្ថបទ" required />
      <textarea name="fcontent" id="editor"></textarea>
      <div id="bottombar">
        <input id="submit" class="bottom-widget" type="submit" value="ចុះ​ផ្សាយ">
        <select class="bottom-widget" id="category" name="fcategory">
          %if data['categories']:
            %for category in data['categories']:
              <option>{{category[1]}}</option>
            %end
          %end
        </select>
        <input id="post-date" value="{{data['datetime'][0]}}" class="bottom-widget post-time" type="text" name="fpost-date" />
        <input id="post-time" value="{{data['datetime'][1]}}" class="bottom-widget post-time" type="text" name="fpost-time" />
        <input disabled style="background:white;text-align:center;" type='text' value="{{data['author']}}" id="post-author" class="bottom-widget post-time" />
      </div>
      %end
    </form>
    <div style="text-align: center;">{{data["message"]}}</div>
    %data['message'] = ""
    
    <script src="/static/scripts/ckeditor/config.js"></script>

  </section><!--content-->
</div><!--main-->

%include('./dashboard/partials/footer.tpl')

GitHub: https://github.com/Sokhavuth/kwblog
Heroku: https://khmerweb-kwblog.herokuapp.com/