text: from .db import get_connection, get_data_dict
  from .config import is_debug, is_printer_debug
  from .fulfillable import get_all_receipts
  from flask import render_template, url_for, make_response
  
  import logging
  logger = logging.getLogger()
  
  def do_home():
      context = {'home': True}
      conn = get_connection()
      cur = conn.cursor()
      
      query2 = """
          SELECT * 
            FROM PRINTER_QUEUE
           WHERE status IN (0, 1) /* PENDING or PRINTING */
             AND printer_id IS NOT NULL
           ORDER BY printer_id, priority
      """
      rows = get_data_dict(cur, query2)
              
      query = """
          SELECT P.id,
                 P.name,
                 P.printer_ip,
                 P.status,
                 P.ui_change,
                 P.color,
                 P.gcode,
                 P.waterplate_only,
                 CASE WHEN (P.status = 0 AND COALESCE(IP.sku, P.gcode) is NULL) THEN 'online'
                      WHEN (P.status = 0 AND COALESCE(IP.sku, P.gcode) is not NULL) THEN 'away'
                      ELSE 'busy'
                 END css_status,
                 CASE WHEN (P.status = 0 AND COALESCE(IP.sku, P.gcode) is NULL) THEN 'ready'
                      WHEN (P.status = 0 AND COALESCE(IP.sku, P.gcode) is not NULL) THEN 'printing'
                      ELSE 'offline'
                 END str_status,
                 COALESCE(PQ.num, 0) num,
                 COALESCE(IP.sku, P.gcode) current_sku,
                 IP.receipt_id current_receipt,
                 COALESCE(IP.progress, P.progress) progress,
                 COALESCE(IP.is_printing, 0) is_printing
            FROM PRINTERS P
            LEFT JOIN (
                          SELECT COUNT(1) num, printer_id
                            FROM PRINTER_QUEUE
                           WHERE status = 0
                           GROUP BY printer_id
                      ) PQ
                 ON P.id = PQ.printer_id
            LEFT JOIN (
                          SELECT sku, 
                                 receipt_id, 
                                 progress,
                                 printer_id,
                                 1 is_printing
                            FROM PRINTER_QUEUE
                           WHERE status = 1 /* PRINTING */
                      ) IP /* IN_PROGRESS */
                 ON P.id = IP.printer_id
      """
      printers = get_data_dict(cur, query)
      
      assigned_queue_items = set()
  
      printer_queue = {}
      for row in rows:
          key = row['printer_id']
          if key not in printer_queue:
              printer_queue[key] = []
          if row['status'] == 0:
              printer_queue[key].append(row)
          assigned_queue_items.add(row['id'])
      for printer in printers:
          if printer['id'] in printer_queue:
              printer['queue'] = printer_queue[printer['id']]
          else:
              printer['queue'] = []
      
      completed_receipts, pending_receipts = get_all_receipts(cur)
      
      unassigned_queue = []
      for receipt in pending_receipts:
          receipt['num'] = sum(receipt['order_info_dict'].values())
          for sku in receipt['order_info_dict']:
              for printer_queue_id in receipt['queue_items_dict'][sku]:
                  if printer_queue_id not in assigned_queue_items:
                      item = {
                          'receipt_id': receipt['receipt_id'],
                          'sku': sku,
                          'id': printer_queue_id
                      }
                      unassigned_queue.append(item)
      
  
      context['printers'] = printers
      context['receipt_list'] = pending_receipts
      context['receipts'] = "['%s']" % "','".join([receipt['receipt_id'] for receipt in pending_receipts])
      context['completed_receipts'] = completed_receipts
      context['unassigned_queue'] = unassigned_queue
      
      return render_template('home.html', **context)
      
  def do_queue_update(queue_item_id, from_printer_id, to_printer_id, priority, old_priority):
      response = None
      conn = get_connection()
      cur = conn.cursor()
      if from_printer_id in ('None',):
          from_printer_id = 'NULL'
      if to_printer_id in ('None',):
          to_printer_id = 'NULL'
  
      data_dict = { 'from_printer_id': from_printer_id,
                    'to_printer_id': to_printer_id,
                    'priority': priority,
                    'old_priority': old_priority,
                    'queue_item_id': queue_item_id
                  }
      validation_query = """
          SELECT CASE WHEN pt.color is NULL
                      THEN 1
                      WHEN pt.color = pd.color
                      THEN 1
                      ELSE 0
                  END valid_color
            FROM PRINTER_QUEUE pq
                 JOIN PRODUCTS pd ON pq.sku = pd.sku
            LEFT JOIN PRINTERS pt ON pt.id = %(to_printer_id)s
           WHERE pq.id = %(queue_item_id)s
      """ % data_dict
      query_0 = """
                  UPDATE PRINTER_QUEUE
                     SET printer_id = NULL,
                         priority = 0,
                         status = 0
                   WHERE id = %(queue_item_id)s
                """ % data_dict
      query_1 = """
                  UPDATE PRINTER_QUEUE
                     SET priority = priority - 1
                   WHERE printer_id = %(from_printer_id)s
                     AND priority >= %(old_priority)s
                     AND status = 0 /* PENDING */
                """ % data_dict
      query_2 = """
                  UPDATE PRINTER_QUEUE
                     SET priority = priority + 1
                   WHERE printer_id = %(to_printer_id)s
                     AND priority >= %(priority)s
                     AND status = 0 /* PENDING */
                """ % data_dict
      query_3 = """
                  UPDATE PRINTER_QUEUE
                     SET printer_id = %(to_printer_id)s,
                         priority = %(priority)s
                   WHERE id = %(queue_item_id)s
                     AND status = 0 /* PENDING */
                """ % data_dict
      try:
          cur.execute(validation_query)
          valid_color = cur.fetchone()[0]
          if valid_color == 0:
              raise Exception('printer cannot print this color')
          cur.execute(query_0)
          if from_printer_id not in ('NULL',):
              cur.execute(query_1)
          if to_printer_id not in ('NULL',):
              cur.execute(query_2)
          cur.execute(query_3)
          conn.commit()
          response = make_response('success', 200)
      except Exception as e:
          logger.error("Error: "+str(e))
          response = make_response(str(e), 400)
      return response
  
  