J-M DESMETTRE 0 Newbie Poster

Hi,

I've posted two scripts that I would like to improve.
The first one (SparePartsDemo.py) is a little utility that takes a .csv (.txt) data file exported from another application, builds a SQLite database,performs a request to select and sort records and write the results to a sqlite database file, a .csv file, a .xls file and barcode labels to a .pdf file.

I want to change the way I build my date acquisition and my query string (lines 482 to 520 and 580 to 989) to a GUI style.

I've been using PySide (Qt) for making an attempt to design a SQL query builder (see ListBox(5).py).
At your advice, what's the best way to integrate this approach with the original script, without having to rewrite the whole script ?
We might also consider using WxPython instead of PySide.

Tested with Python 2.7

Maybe you will have to download and install a few libraries and an OCR font I'm using (See attached files).


Thanks in advance for your advice and remarks.

#!/usr/bin/python
#-*- coding: utf-8 -*-
#
# ==============================================================================
#
# File     : SparePartsDemo.py
# Release  : alpha
# Author   : J-M Desmettre
# Purpose  : CSV utility
# Created  : 20100212
# Revision : 20110422
# Python   : 2.7
# License  : Distributed under the terms of the GNU General Public License
#            You may redistribute this software and/or modify it under the
#            terms of the GNU General Public License, as published by the
#            Free Software Foundation; either version 2 of the License,
#            or (at your option) any later version.
#            This software is distributed in the hope that it will be useful,
#            but WITHOUT ANY WARRANTY; without even the implied warranty of
#            MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#            See the GNU General Public License for more details.
#            You should have received a copy of the GNU General Public License
#            along with this. If not, write to:
#
#               The Free Software Foundation, Inc.,
#               51 Franklin Street, Fifth Floor
#               Boston, MA  02110-1301, USA.
#
#            Permission is granted to anyone to use this software for any
#            purpose, Including commercial applications, and to alter it and
#            redistribute it freely, subject to the following restrictions:
#
#            1. The origin of this software must not be misrepresented.
#               You must not claim that you wrote the original software.
#               If you use this software in a product, an acknowledgment
#               in the product documentation would be appreciated but is not
#               required.
#            2. Altered source versions must be plainly marked as such,
#               and must not be misrepresented as being the original software.
#            3. This notice may not be removed or altered from any source
#               distribution.
#
#
# ==============================================================================
#
#            Modified PIL library for Python 2.7
#            -----------------------------------
#            _imagingft.pyd
#
#            The manifest descriptor embedded with _imagingft.pyd
#            contains dependency on DEBUG version of C++ runtime:
#
#            ...
#              <dependency>
#                <dependentAssembly>
#                  <assemblyIdentity type="win32" name="Microsoft.VC90.DebugCRT"
#                   version="9.0.21022.8"
#                   processorArchitecture="x86"
#                   publicKeyToken="1fc8b3b9a1e18e3b">
#                  </assemblyIdentity>
#                </dependentAssembly>
#              </dependency>
#            ...
#
#            Edit with a hex editor and remove above
#            mentioned lines/dependency from manifest
#
# ==============================================================================
#
# Credits  : *** mxDateTime ***
#
#            Date and time handling routines and types library
#            Copyright (c) 1998-2000, Marc-Andre Lemburg; mailto:mal@lemburg.com
#            Copyright (c) 2000-2007, eGenix.com Software GmbH; mailto:info@egenix.com
#            See the documentation for further information on copyrights,
#            or contact the author. All Rights Reserved.
#            eGenix.com Software, Skills and Services GmbH
#            Pastor-Loeh-Str. 48
#            D-40764 Langenfeld, Germany
#
#            *** pyExcelerator ***
#
#            Excel utilities library
#            This product includes software developed by
#            Roman V. Kiseliov <roman@kiseliov.ru>.
#
#            *** huBarcode ***
#
#            huBarcode 0.57 - 1D and 2D barcode library
#            Copyright (c) Hudora GMBH  Remscheid, Germany
#            Hudora Cybernetics  http://cybernetics.hudora.biz/
#            If you worry about copyright you might consider this Software BSD-Licensed.
#            If you are still worried, you might consider it GPL1/2/3 compatible.
#            But don’t worry.
#
#
#
# ==============================================================================

# --- Imports ------------------------------------------------------------------

# --- Standard libraries -------------------------------------------------------
import csv
import os
from os.path import getsize, getmtime
from PIL import Image
import string
import sys
import time
import datetime
import socket
import sqlite3 as sqlite
import uuid

# --- External libraries -------------------------------------------------------

""" Maybe you will have to download and install (easy_install) the following libraries """
""" See PIL remark above !!! """

from mx.DateTime import *                       

from pyExcelerator import *
from pyExcelerator.Worksheet import *

from hubarcode.code128 import Code128Encoder
from hubarcode.datamatrix import DataMatrixEncoder
from hubarcode.qrcode import QRCodeEncoder

from reportlab.pdfgen.canvas import Canvas
from reportlab.lib.pagesizes import letter, A4
from reportlab.lib.units import cm, mm, inch, pica
from reportlab.lib.colors import black, white, red, blue
from reportlab.platypus import Paragraph, SimpleDocTemplate, Spacer, Image
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.graphics.barcode.code128 import Code128
import reportlab.rl_config
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont

#from PySide.QtCore import *
#from PySide.QtGui import *

from tdict import *

# --- Constants and variables --------------------------------------------------

title_1 = "Spare parts .csv utility"
title_2 = "Int. code    "+\
          "Description                "+\
          "Price Location   "+\
          "Quant. "+\
          "Found       "+\
          "Total    "

iso_date = now().iso_week
time_stamp = ARPA.str(now())

iso_stamp = str(iso_date[0])+'W'+\
            str(iso_date[1]).rjust(2,'0')+\
            str(iso_date[2]).replace("0","")

iso_stamp_h = str(hex(iso_date[0]))+\
              str(hex(iso_date[1]))+\
              str(hex(iso_date[2]))

#MyIPaddress = socket.gethostbyname_ex(socket.gethostname())
MyComputername = socket.gethostbyname_ex(socket.gethostname())[0]
MyIPaddress = socket.gethostbyname_ex(socket.gethostname())[2]
uid =str(uuid.uuid4())
hit = 0

familie_list = []
maingroup_list = []
group_list = []
subgroup_list = []
location_list = []
brandList = ['ARCELIK',
             'BEKO',
             'WHIRLPOOL',
             'ZANKER',
             'PHILIPS',
             'SONY'
             ]

tableDict ={}
reportlab.rl_config.warnOnMissingFontGlyphs = 0
pdfmetrics.registerFont(TTFont('ocrb', 'OCRB10.TTF'))
pdfmetrics.registerFont(TTFont('Times', 'TIMES.TTF'))

# --- pyExcelerator / workbook and worksheet definition ------------------------

workbook = Workbook()
worksheet = workbook.add_sheet("Sheet_1")
worksheet.col(0).width = 0x24E1
worksheet.left_margin = 0.787                   # Define page format
worksheet.right_margin = 0.394
worksheet.top_margin = 0.590
worksheet.bottom_margin = 0.394
worksheet.print_centered_horz = False
worksheet.header_str = ""
worksheet.footer_str = ""
worksheet.print_scaling = 80

# --- Functions, Procedures and Classes ----------------------------------------

def numFormat(string):
    if string[-2::2]=='.':
        string=string+'0'
    string=string.split('.')
    string=','.join(string)
    if len(string)>6:
        if len(string)==7 and string[:1] =='-':
            return string
        elif len(string)>9:
            if len(string)==10 and string[:1] =='-':
                return string
            mil=string[0:len(string)-9]
            tho=string[1:len(string)-6]
            hun=string[len(string)-6:]
            t=[mil, tho, hun]
            string='.'.join(t)
            return string
        tho=string[0:len(string)-6]
        hun=string[len(string)-6:]
        t=[tho, hun]
        string='.'.join(t)
    return string

def maxlength(lst):
    return len(max(lst, key=len))

def find_substring(string_1, string_2):
    if ' '+string_1+' 'in ' '+string_2+' ':
        return True
    return False

def add_unique_postfix(fn):
    '''    Function 'add_unique_postfix'.

    Function for making unique non-exixtent file name
    with saving source file extension.

    __author__ = 'Denis Barmenkov <denis.barmenkov@gmail.com>'
    __source__ = 'http://code.activestate.com/recipes/577200-make-unique-file-name/'

    '''
    if not os.path.exists(fn):
        return fn
    path, name = os.path.split(fn)
    name, ext = os.path.splitext(name)
    make_fn = lambda i: os.path.join(path, '%s(%d)%s' % (name, i, ext))
    for i in xrange(2, sys.maxint):
        uni_fn = make_fn(i)
        if not os.path.exists(uni_fn):
            return uni_fn
    return None

def create_barcode_128(barcode):
    encoder = Code128Encoder(barcode,{"ttf_font":"ocrb10.ttf",  # load a specific font
                                       "ttf_fontsize":50,       # with a specific size
                                       "bottom_border":15       # and adjust the footer border in pixels
                                      }
                             )
    encoder.save("%s.png" % barcode)       # generate a Code 128 barcode

def create_barcode_EAN13(barcode):
    encoder = EAN13Encoder(barcode)
    encoder.save("EAN%s.png" % barcode)     # generate an EAN13 barcode
    pass

def create_barcode_QR(barcode, name):
    encoder = QRCodeEncoder(barcode)
    encoder.save("QR%s.png" % name)           # generate a QR Code barcode
    #print encoder.get_ascii()

def create_barcode_DataMatrix(barcode, name):
    encoder = DataMatrixEncoder(barcode)
    encoder.save("DMTX%s.png" % name)         # generate a Data Matrix barcode
    #print encoder.get_ascii()

def create_barcode_DataMatrix_UID(barcode, name):
    encoder = DataMatrixEncoder(barcode)
    encoder.save("UID%s.png" %name)
    #image = Image()
    #img = Image.open("C:/test/Barcodes/DMTX/UID/%s.png" %name)
    #img2 = img.convert("RGB")
    #img2.save("C:/test/Barcodes/DMTX/UID/%s.bmp" %name)

def set_label_text(col):
    label.drawCentredString(mm*(col+30), mm*(firstline), line_1)
    label.drawCentredString(mm*(col+30), mm*(firstline-3), line_2)
    label.drawCentredString(mm*(col+30), mm*(firstline-6), line_3)
    label.drawCentredString(mm*(col+30), mm*(firstline-9), line_4)
    label.drawCentredString(mm*(col+30), mm*(firstline-12), line_5)
    label.drawCentredString(mm*(col+30), mm*(firstline-15), line_6)
    label.setFont('ocrb',12)
    label.drawCentredString(mm*(col+30), mm*(firstline-30), line_7)
    label.setFont('ocrb',8)
    #barcode.drawOn(label,(col+23)*mm,(firstline-28)*mm)

def set_label_barcode(col, dimension):
    if dimension == 1:
        barcode.drawOn(label,(col+10)*mm,(firstline-28)*mm)  # for 1D barcodes (128, EAN13)
    else:
        barcode.drawOn(label,(col+23)*mm,(firstline-25)*mm)  # for 2D barcodes (Data Matrix, QR)

class SQL_Commands:

    def __init__(self, db_file):
        db_exists = os.path.exists(db_file)
        self.con = sqlite.connect(db_file)
        if not db_exists:
            print 'Creating database file', db_file
            self.cur= self.con.cursor()
            self.CreateTable()
            self.con.commit()
        #pass
        #self.parent = parent

    def CreateDatabase(self, db_name):
        try:
            # Create database connection
            self.con = sqlite.connect("%s" %(db_name))
        except Exception, err:
            print "Connection failed. Error : %s" %err
            #print "Program aborted."
            #raise SystemExit
            #self.abort = 1
        else:
            # Create database cursor
            self.cur = self.con.cursor()
            #self.abort = 0

    def CreateTable(self):
        # Create database tables
        for t_name, value in DynTableDictionary.items():
            request = 'CREATE TABLE IF NOT EXISTS %s ' %(t_name)
            print request
            tableDict[t_name]= len(value)
            row = '('
            for f in value:
                row += str(f)+","
            row = row[:-1]+")"
            request += row
            try:
                self.cur.execute(request)
            except Exception, err:
                print "Table %s creation failed. Error : %s" %(t_name),err
                #print "Program aborted."
                Db.con.close()
                #raise SystemExit

    def InsertRecord(self,t_name,recList):
        # Insert record into table
        columns ='('+ (','.join(list('?'*(tableDict[t_name]))))+')'
        try:
            self.cur.execute('INSERT INTO %s VALUES %s' %(t_name,columns), recList)
        except Exception, err:
            print "Record insertion failed. Error : %s" % err
            #Db.cur.close()
            #Db.con.close()

    def ListRecords(self, t_name):
        # List table
        self.cur.execute('SELECT * FROM %s' %(t_name))
        recList = self.cur.fetchall()
        for record in recList:
            print record

    def ShowTable(self):
        # Show the table(s)
        request = 'SELECT name, type, sql \
                    FROM sqlite_master \
                    WHERE sql NOT NULL \
                    AND type = "table"'
        self.cur.execute(request)
        #for row in Db.cur:
            #print "Table %s created." %((row[0]).upper())


# --- Main ---------------------------------------------------------------------

print "\n","-"*len(title_1)
print title_1
print "-"*len(title_1),"\n"

# --- Sniffer ------------------------------------------------------------------

fi = open(InputFile)
date_ = getmtime(InputFile)
size = getsize(InputFile)
firstline = fi.readline()
fi_dialect = csv.Sniffer().sniff(firstline)
FieldDelimiter = fi_dialect.delimiter
fi.seek(0)
rec=0
for line in fi.readlines():
    rec += 1
fi.seek(0)
print "Computer name            :", MyComputername
print "localhost                :", MyIPaddress[0]
print "UID                      :", uid
print "ISO week stamp           :", iso_stamp, iso_stamp_h
print "File name                :", InputFile
print "Date modified            :", TimestampFromTicks(date_)
print "Size                     :",
if len(str(size)) > 3 and size >= 1024:
    print str(int(round(size/1024))),"Kb."
else:
    print str(size).rjust(10,' '),"bytes."
print "Dialect                  :", fi_dialect
print "Field delimiter          :", FieldDelimiter

file_info = MyIPaddress[0]+"  "+\
            MyComputername+"  "+\
            InputFile+"  "+\
            str(TimestampFromTicks(date_))[:19]\

try:
    # check whether the first record is a header
    header = csv.Sniffer().has_header(fi.read(len(firstline)))
    reader = csv.reader(fi, delimiter = FieldDelimiter)
    #fi.seek(0)
    #firstLine = fi.readline()
    if header:
        hdr = 1                                 # header found
        rec -=1
        fi.seek(0)
        header = reader.next()
        print "Header length            :",
        print str(len(firstline)).rjust(10,' '),
        print 'bytes.'
        print "Header fields            :",
        print str(len(header)).rjust(10,' ')
    else:
        hdr = 0                                 # no header found
        print "First record lengt       :",
        print str(len(firstline)).rjust(10,' '),
        print 'bytes.'
        print "Record fields            :",
        print str(len(firstline.split(","))).rjust(10,' ')
        header = []
        # build a custom header with column No's.
        for f in range(len(firstline.split(","))):
            header.append('COL_' + str(f))
finally:
    fi.seek(0)

print "Records                  : %s\n" % (str(rec).rjust(10, ' '))
reader = csv.reader(fi, delimiter = FieldDelimiter)
for row in reader:
    if row[1] not in familie_list:
        familie_list.append(row[1])
    if row[13] not in maingroup_list:
        maingroup_list.append(row[13])
    if row[14] not in group_list:
        group_list.append(row[14])
    if row[15] not in subgroup_list:
        subgroup_list.append(row[15])
    if row[17] not in location_list:
        row[17] = row[17].replace(" ","")
        location_list.append(row[17])
print "Families                 :",str(len(familie_list[1:])).rjust(10,' ')
print "Main groups              :",str(len(maingroup_list[1:])).rjust(10,' ')
print "Groups                   :",str(len(group_list[1:])).rjust(10,' ')
print "Sub groups               :",str(len(subgroup_list[1:])).rjust(10,' ')
print "Locations                :",str(len(location_list[1:])).rjust(10,' ')
print

# --- User input --- Get a threshold date --------------------------------------

while True:
    inactive_years = raw_input("Enter inactivity YEARS   : ")
    if inactive_years == "":
        inactive_years= "0"
        break
    elif inactive_years.isdigit():
        break
    else:
        print ">>> Integers only !"
        continue
inactive_years = int(inactive_years)

while True:
    inactive_months = raw_input("Enter inactivity MONTHS  : ")
    if inactive_months == "":
        inactive_months= "0"
        break
    elif inactive_months.isdigit():
        break
    else:
        print ">>> Integers only !"
        continue
inactive_months = int(inactive_months)

inactivity = now()+RelativeDateTime(years=-inactive_years,
                                    months=-inactive_months,
                                    days=1
                                    )
#inactivity = now()+RelativeDateTime(years=-inactive_years,months=-inactive_months)
inactivityISO = str(inactivity.iso_week[0])+'W'+\
                str(inactivity.iso_week[1]).rjust(2,'0')+\
                str(inactivity.iso_week[2]).replace("0","")
print "Inactivity set to        :",inactivity,"/", inactivityISO,"/", ARPA.str(inactivity)
print "Relative Date Difference :",RelativeDateDiff(now(),inactivity)
relativeDate = str(inactivity)[:10]
#relativeDate = '2007-02-11'                     # set a defined date
print "Threshold date           : %s\n" % relativeDate

# --- SQL stuff ----------------------------------------------------------------

Db = SQL_Commands(db_file)                      # build database
Db.CreateDatabase(DbName)                       # ':memory:'
Db.CreateTable()
#Db.ShowTable()

try:                                            # Populate the 'brand' table
    t_name = DynTableDictionary.keys()[0]
    print "Inserting records in table %s." %(t_name)
    Id=0
    for f in brandList:
        if f == '':
            f = 'All brands'
        else:
            f = f.upper()
            recList = []
            recList.append(f)
            Db.InsertRecord(t_name, recList)
            Id +=1
except:
    print "Error in data processing.\n"
    pass

brands = ""
Db.cur.execute('SELECT * FROM brand ORDER BY brand_sel')
for row in Db.cur:
    brands +=(row[0])+"/"
brands = brands[:-1]

fi.seek(0)
record = csv.reader(fi, delimiter = FieldDelimiter)
try:                                            # Populate the 'stock' table
    t_name = DynTableDictionary.keys()[1]
    print "Inserting records in table %s." %(t_name)
    Id = 0
    for row in list(record)[hdr:]:
        if "\xc3" in row[2]:
            print row[0],row[2]
        if " " in row[17]:
            hit +=1
            row[17] = row[17].replace(" ","")   # Remove unwanted spaces from 'location' field
        if row[64] == "":
            row[64] = 0
        if row[65] == "":
            row[65] = 0
        recList = []
        recList = [Id] + row[:19] + row[62:66]  # Slicing
        Db.InsertRecord(t_name, recList)
        Id +=1
except:
    print "Error in data processing.\n"
    print recList
    pass
if hit:
    print hit,"locations corrected."
Db.con.commit()

# --- SQL request sketch -------------------------------------------------------

table_name = 'stock'
subtable_name = 'brand'

# Family
family_name = 'B'

# MAINGROUP
maingroup_name = ''
maingroup_tuple ='("",)'
#maingroup_tuple ='("","","","","","","")'
maingroup_like = '%'

# Groep
group_name =''
group_tuple = '("","")'
group_like = '%'

# Ondergroup
subgroup_name =''
subgroup_tuple ='("","","","")'
subgroup_like = '%'

# Stuk
stuk_nummer = '14'                              # Part No. without leading zeros !!!

# Locatie
location_name = 'BOXMALRUN'
#location_tuple = '("KB","KH")'
location_tuple = '("","----")'
location_like = 'KB%'
location_like0 = 'B18%'
location_like1 = 'B19%'

# Argument dictionary
ArgDict ={"thresholdDate":relativeDate}

# --- Build SQL request string -------------------------------------------------

request = 'SELECT * FROM %s' %table_name

#request += ' WHERE STOCK > -100'
request += ' WHERE STOCK > 0'
#request += ' WHERE ('+ (' <> 0 OR '.join(stocklist))+')'

#request += ' AND FAM = "%s"' % family_name
#request += ' AND (DESCRIPTION LIKE "%D%" OR DESCRIPTION LIKE "%T%" OR DESCRIPTION LIKE "%P%")'

request += ' AND ((DATEOUT <:thresholdDate AND DATEOUT !="")'
request += ' OR (DATEOUT = "" AND DATEIN <:thresholdDate AND DATEIN !="")'
request += ' OR (DATEOUT ="" AND DATEIN =""))'
#request += ' AND DATEOUT < "2010-12-21"'

#request += ' AND MAINGROUP = "%s"' %maingroup_name.upper()
#request += ' AND MAINGROUP IN %s' %maingroup_tuple

#request += ' AND GROUP_ = "%s"' %group_name.upper()
#request += ' AND GROUP_ IN %s' %group_tuple
#request += ' AND GROUP_ != "%s"' %group_name.upper()

#request += ' AND SUBGROUP = "%s"' %subgroup_name.upper()
#request += ' AND SUBGROUP IN %s' %subgroup_tuple
#request += ' AND SUBGROUP NOT LIKE "%s"' %subgroup_name.upper()

#request += ' AND STUK = "%s"' %stuk_nummer

#request += ' AND LOCATION != "%s"' %location_name
#request += ' AND LOCATION LIKE "%s"' %location_like.upper()
#request += ' AND LOCATION NOT LIKE "%s"' %location_like
#request += ' AND SUBSTR(LOCATION,1,3) IN ("B1", "B19")'
#request += ' AND SUBSTR(LOCATION,1,3) IN %s' % location_tuple.upper()
#request += ' AND SUBSTR(LOCATION,1,2) IN ("WD", "WE", "WF")'
#request += ' AND SUBSTR(LOCATION,1,2) NOT IN ("ZT","ZU")'
#request += ' AND LOCATION IN %s' %location_tuple
#request += ' AND LOCATION NOT IN %s' %location_tuple
#request += ' AND LOCATION LIKE "%s" AND SUBSTR(LOCATION,3,1) NOT IN("X",)' %location_like
#request += ' AND SUBSTR(LOCATION,1,2) IN ("AO","BO") AND SUBSTR(LOCATION,3,1) NOT IN ("X",)'
#request += ' AND (LOCATION LIKE "%s" OR LOCATION LIKE "%s")' %(location_like0, location_like1)
#request += ' AND LOCATION LIKE "A1A%" OR LOCATION LIKE "A1B%" OR LOCATION LIKE "A1C%"'
#request += ' OR LOCATION LIKE "A1D%" OR LOCATION LIKE "A1E%" OR LOCATION LIKE "A1F%"'
#request += ' AND SUBSTR(LOCATION,1,3) IN ("A1A","A1B","A1C","A1D","A1E","A1F")
#request += ' AND LOCATION LIKE "A2A%" OR LOCATION LIKE "A2B%" OR LOCATION LIKE "A2C%"'
#request += ' OR LOCATION LIKE "A2D%" OR LOCATION LIKE "A2E%" OR LOCATION LIKE "A2F%"'
#request += ' AND SUBSTR(LOCATION,1,3) IN ("A2A","A2B","A2C","A2D","A2E","A2F")
#request += ' AND LOCATION LIKE "A3A%" OR LOCATION LIKE "A3B%" OR LOCATION LIKE "A3C%"'
#request += ' OR LOCATION LIKE "A3D%" OR LOCATION LIKE "A3E%" OR LOCATION LIKE "A3F%"'
#request += ' AND SUBSTR(LOCATION,1,3) IN ("A3A","A3B","A3C",A3D","A3E","A3F")

#request += ' AND MERK IN (SELECT * FROM %s)' %subtable_name

selection = " selected by "
if find_substring("MAINGROUP",request):
    selection += "MMM /"
if find_substring("GROUP_",request):
    selection += " GGG /"
if find_substring("SUBGROUP",request):
    selection += " SSS /"
if find_substring("LOCATION",request):
    selection += " LOC /"
if find_substring("BRAND",request):
    selection += " BRAND /"
selection = selection[:-1]

sort_order = ''
sort_order += ' ORDER BY NRFABR, LOCATION'
request += sort_order

# --- Execute request ----------------------------------------------------------

print "\nExecuting the following SQL request :\n%s , %s \n" %(request, ArgDict)
Db.cur.execute(request, ArgDict)                # execute a request
if len(Db.cur.fetchall())==0:                   # abort if no results
    print " >>> No results corresponding to your request!\n"
else:
    outputFile = 'CSV%s.csv' %(iso_stamp_h)
    outputFile = add_unique_postfix(outputFile) # .csv file
    fo = open(outputFile, "wb")
    writer = csv.writer(fo, delimiter = FieldDelimiter)
    writer.writerow([uid])                      # UID
    create_barcode_DataMatrix_UID(uid, uid)     # DataMatrix UID

    labelPDF = add_unique_postfix('Code128 by quant.pdf')
    label = Canvas(labelPDF, pagesize = A4)     # define a ReportLab canvas  .pdf file

    xlsFile = 'Worksheet%s.xls' % iso_stamp_h
    xlsFile = add_unique_postfix(xlsFile)       # .xls file
    # Best define workbook, worksheet and styles at the begining of the script
    #workbook = Workbook()
    #worksheet = workbook.add_sheet("Sheet_1")   # define a worksheet
    topmargin = 3
    firstline = 297 - topmargin - 5

    Db.cur.execute(request, ArgDict)            # execute a request
    print title_2,\
          " Date IN  "," Date OUT","  Time Gap","    Rel. IN",\
          "   Rel. OUT","   IN ","  OUT "," BAL. "
    print "-"*len(title_2),"-"*10,"-"*10 ,"---Y----M  ","---Y----M  ",\
          "---Y----M","------","------","------"

    # --- pyExcelerator preamble -----------------------------------------------

    row_count_xls = 0                           # pyExcelerator row counter
    col = 0                                     # pyExcelerator column counter
    row_count_pdf = 0                           # ReportLab row counter
    page = 1                                    # ReportLab page counter
    title_line = 'Spare parts list on %s' % now().date
    worksheet.write_merge(0 ,2 ,0 ,8,
                          title_line + selection,
                          style2
                          )
    row_count_xls +=3
    header_line=[' Orig. code',                  # column headers
                 ' Description',
                 ' Internal code',
                 ' Location',
                 ' Date IN',
                 'Date UIT',
                 ' Price',
                 ' Quant.',
                 ' Value',
                 '',                            # empty column
                 ' Cumulative IN',
                 ' Quant. sold',
                 ' Value purchased',
                 ' Value sold     ',
                 ' Balance',
                 ' Rate'
                 ]

    for row in header_line:
        if row == "":
            worksheet.write(row_count_xls, col,
                            row,
                            style_info
                            )
        else:
            worksheet.write(row_count_xls, col,
                            row,
                            style_bg
                            )
        col +=1
    row_count_xls+=1
    overhead = row_count_xls+1

    # --- End of pyExcelerator preamble script ---------------------------------

    d_now = str(now())[:10].replace("-","")
    curTotal = 0
    field3Lst = []
    # Write row to stdout
    for field in Db.cur:
        field3Lst.append(field[3])
        di = field[20].replace("-","")
        do = field[21].replace("-","")
        if di == "" :
            di = d_now
        if do == "":
            do = d_now
        di_y = int(di[:4])
        di_m = int(di[4:6])
        di_d = int(di[6:8])
        do_y = int(do[:4])
        do_m = int(do[4:6])
        do_d = int(do[6:8])
        di = Date(di_y,di_m,di_d)
        do = Date(do_y,do_m,do_d)
        delta = RelativeDateDiff(do, di)        # Time gap between last output an last input / a negative value shows a stock return
        delta_i = RelativeDateDiff(now(), di)   # Relative difference between today and last input date
        delta_o = RelativeDateDiff(now(), do)   # Relative difference between today and last output date
        fieldTotal = field[8] * field[19]
        balance = -(int(field[22])-int(field[23])-int(field[19]))

        print str(field[1]).rjust(3,' '), \
              (field[3])[0:25].ljust(25,'.'), \
              numFormat(str(field[8])).rjust(6,'.'), \
              (field[18])[0:20].ljust(10,'.'), \
              str(field[19]).rjust(6,'.'), \
              '......... ', \
                numFormat(str(fieldTotal)).rjust(10,'.'),\
              field[20].rjust(10,'-'),\
              field[21].rjust(10,'-'),
        print str(delta.years).rjust(4,'.'),\
              str(delta.months).rjust(4,'.')," ",\
              str(delta_i.years).rjust(4,'.'),\
              str(delta_i.months).rjust(4,'.')," ",\
              str(delta_o.years).rjust(4,'.'),\
              str(delta_o.months).rjust(4,'.'),
        print numFormat(str(field[22])).rjust(6,'.'),\
              numFormat(str(field[23])).rjust(6,'.'),\
              numFormat(str(balance)).rjust(6,'.')

        curTotal += fieldTotal

        # --- Barcode generation -----------------------------------------------

        # Code 128
        create_barcode_128(field[1])

        # DataMatrix
        dmtx = (field[1]+"\n",
                field[3][:12]+"\n",
                field[13][:12]+"\n",
                field[18]+"\n",
                field[21].replace("-","")
                )
        dmtx = "".join(dmtx)
        create_barcode_DataMatrix(dmtx, field[1])

        """
        # QR Code
        qr = (field[1]+"\n",
              field[3][:12]+"\n",
              field[13][:12]+"\n",
              field[18]
              )
        qr = "".join(qr)
        create_barcode_QR(qr, field[1])
        """

        # Write row to .csv file
        csv_row_out = [field[1],
                       field[3],
                       field[8],
                       field[19],
                       field[18],
                       field[20],
                       field[21]
                       ]
        writer.writerow(csv_row_out)

        # --- pyExcelerator script ---------------------------------------------

        # Write row to worksheet
        xls_row_out = [field[13],
                       field[3],
                       field[12],
                       field[18],
                       field[20],
                       field[21],
                       field[8],
                       field[19]
                       ]

        col = 0
        for row in xls_row_out:
            if type(row) == float:
                worksheet.write(row_count_xls, col,
                                row,
                                style1
                                )
            else:
                worksheet.write(row_count_xls, col,
                                row,
                                style0
                                )
            col +=1

        worksheet.write(row_count_xls, col,
                        Formula("G%s*H%s"
                        %(row_count_xls+1, row_count_xls+1)),
                        style1
                        )                       # add a total 'Waarde' cell AKP*HOEV
        worksheet.write(row_count_xls, col+2,
                        field[22],
                        style0
                        )                       # add a 'IN' (Kumul IN) cell
        worksheet.write(row_count_xls, col+3,
                        field[23],
                        style0
                        )                       # add a 'OUT' (Verkopen) cell
        worksheet.write(row_count_xls, col+4,
                        Formula("G%s*K%s"
                        %(row_count_xls+1, row_count_xls+1)),
                        style1
                        )                       # add a 'PURCHASE' cell
        worksheet.write(row_count_xls, col+5,
                        Formula("G%s*L%s"
                        %(row_count_xls+1, row_count_xls+1)),
                        style1
                        )                       # add a 'SOLD' cell
        worksheet.write(row_count_xls, col+6,
                        Formula("H%s-K%s+L%s"
                        %(row_count_xls+1, row_count_xls+1, row_count_xls+1)),
                        style0
                        )                       # add a 'Balance' cell
        worksheet.write(row_count_xls, col+7,
                        Formula("IF(ISERR(L%s/K%s);"";L%s/K%s)"
                        %(row_count_xls+1, row_count_xls+1, row_count_xls+1, row_count_xls+1)),
                        style3
                        )                       # add a 'Rate' cell
        row_count_xls +=1

        # --- ReportLab script -------------------------------------------------

        barcode = Image('%s.png'
                        %field[1],
                        width=39*mm,
                        height=15*mm
                        )                       # load a Code128 barcode image

        """
        barcode = Image('%s.png' %field[1],
                        width=15*mm,
                        height=15*mm
                        )                       # load a Data Matrix barcode
        """
        """
        barcode = Image('%s.png' %field[1],
                        width=15*mm,
                        height=15*mm
                        )                       # load a QR barcode image
        """

        label_font ='ocrb'
        label.setFont(label_font, 8)
        line_1 = "BROLTRONICS Spare Parts"
        line_2 = field[3][0:30]
        line_3 = field[13]
        line_4 = "Loc: "+field[18]
        line_5 = ""
        line_6 = ""
        line_7 = "" #field[1] for 2D barcodes

        # --- 8 by 3 label array -----------------------------------------------

        nbr_of_labels = field[19]               # Print labels according the quantity
        #nbr_of_labels = 1                       # To print a ref. where quant. < 0 set quantity to 1 ***
        label_row = divmod(field[19],3)         # and number of labels on a row (3)
        #label_row = divmod(1,3)                 # print three labels on a row for quant. < 0 ***
        if label_row[1] == 0:
            label_row = int(label_row[0])
        else:
            label_row = int(label_row[0]+1)
        nbr_of_labels = label_row * 3
        while label_row > 0:
            for label_col in [3, 75, 147]:      # Print three labels a row
                set_label_text(label_col)
                set_label_barcode(label_col, 1) # the 2nd arg being the barcode 'dimension' (1 or 2)
                nbr_of_labels -=1
            label_row -=1
            row_count_pdf += 1                  # Next row
            if row_count_pdf == 8:              # Page break
                row_count_pdf = 0
                topmargin = 3
                firstline = 297-topmargin-5
                firstline += 37
                label.showPage()                # Stop drawing on the current page (next page)
                label.setFont(label_font, 8)
                page +=1
            firstline -= 37                     # Next row

        # --- End of ReportLab script ------------------------------------------

    label.showPage()                            # Stop drawing on the current page (close page)
    label.save()                                # Save the label and closes the canvas

    row_count_xls+=1
    worksheet.write(row_count_xls, col-8,
                    Formula('IF(COUNTA(C5:C%s)>0;" Items : "&COUNTA(C5:C%s);"")'
                    %(row_count_xls,row_count_xls)),
                    style_info
                    )                           # 1st style
    worksheet.write(row_count_xls, col-8,
                    Formula('COUNTA(C5:C%s)&" items selected."'
                    %row_count_xls),
                    style_info
                    )                           # 2nd style
    worksheet.write(row_count_xls, col-1,
                    Formula("SUM(H5:H%s)"
                    %(row_count_xls)),
                    style0
                    )                           # add total 'Items' cell
    worksheet.write(row_count_xls, col,
                    Formula("SUM(I5:I%s)"
                    %(row_count_xls)),
                    style1_bold
                    )                           # add total 'Value' cell
    worksheet.write(row_count_xls, col+2,
                    Formula("SUM(K5:K%s)"
                    %(row_count_xls)),
                    style0
                    )                           # add total 'Cumulative' cell
    worksheet.write(row_count_xls, col+3,
                    Formula("SUM(L5:L%s)"
                    %(row_count_xls)),
                    style0
                    )                           # add total 'Verkopen' cell
    worksheet.write(row_count_xls, col+4,
                    Formula("SUM(M5:M%s)"
                    %(row_count_xls)),
                    style1
                    )                           # add total 'Purchased' cell
    worksheet.write(row_count_xls, col+5,
                    Formula("SUM(N5:N%s)"
                    %(row_count_xls)),
                    style1
                    )                           # add total 'Sold' cell
    worksheet.write(row_count_xls, col+6,
                    Formula("SUM(O5:O%s)"
                    %(row_count_xls)),
                    style0
                    )                           # add total 'Balance' cell
    worksheet.write(row_count_xls, col+7,
                    Formula("IF(ISERR(L%s/K%s);"";L%s/K%s)"
                    %(row_count_xls+1, row_count_xls+1, row_count_xls+1, row_count_xls+1)),
                    style3
                    )                           # add total 'Rate' cell
    
    worksheet.insert_bitmap('SQLite logo.bmp',
                            0, 0,
                            x=5, y=4,
                            scale_x=0.3,
                            scale_y=0.5
                            )                   # add a logo top left
    
    worksheet.write_merge(row_count_xls+2, row_count_xls+2, 0, 8,
                          (' Data source : %s' % file_info),
                          style_info
                          )                     # add some info about the .csv data source
    worksheet.write_merge(row_count_xls+3, row_count_xls+3, 0, 8,
                          (' This file : %s' % xlsFile),
                          style_info
                          )                     # the name of the generated .xls file
    worksheet.write_merge(row_count_xls+4, row_count_xls+4, 0, 8,
                          (' UID : %s' %uid),
                          style_info
                          )
    worksheet.write_merge(0 ,2, 0 , 8,
                          title_line + selection,
                          style2
                          )                     # Worksheet title line
    worksheet.write_merge(row_count_xls+6, row_count_xls+11, 0, 8,
                          (request+"   "+str(ArgDict)),
                          style4
                          )                     # add SQL request
    """
    worksheet.insert_bitmap('Test.bmp',
                            row_count_xls+4, 4,
                            x=2, y=2,
                            scale_x=0.2,
                            scale_y=0.2
                            )
    """

    colWidth = maxlength(field3Lst)*0X100       # trick to automaticaly adjust the column width
    worksheet.col(0).width = 0X1400
    worksheet.col(1).width = colWidth
    worksheet.col(2).width = 0XF00
    for f in range(3,9):
        worksheet.col(f).width = 0XA00
    worksheet.col(9).width = 0X200
    for f in range(10,16):
        worksheet.col(f).width = 0XA00

    worksheet.panes_frozen = True
    worksheet.horz_split_pos = 4                # freeze row
    #worksheet.vert_split_pos = 9                # freeze column

    worksheet.protect = True                    # add some protection
    worksheet.wnd_protect = True
    worksheet.obj_protect = True
    worksheet.scen_protect = True
    worksheet.password = "1234"
    workbook.protect = True
    workbook.wnd_protect = True
    workbook.obj_protect = True
    workbook.password = "1234"

    workbook.save(xlsFile)

    # --- End of pyExcelerator script ------------------------------------------

    print " "*(len(title_2)-len(numFormat(str(curTotal))))+"-"*len(numFormat(str(curTotal)))
    print "Total value :", numFormat(str(curTotal)).rjust(71,' ')
    print " "*(len(title_2)-len(numFormat(str(curTotal))))+"="*len(numFormat(str(curTotal)))
    print "Total items found :",str(row_count_xls - overhead).rjust(6,'.')
    print "Printed by %s on %s" %(MyIPaddress[0], time_stamp[:-6])
    print "Writing results to %s" % outputFile
    print "Writing results to %s" % xlsFile
    print "Writing %s labels sheets to %s" %(page,labelPDF)

    fo.close()
Db.cur.close()
Db.con.close()
fi.close()

# <EOF>

And here's the PySide script

#!/usr/bin/python
#-*- coding: utf-8 -*-
#
# ==============================================================================
#
# File     : ListBox(5).py
# Release  : alpha
# Author   : J-M Desmettre
# Purpose  : To build SQL requests
# Created  : 20110412
# Revision : 20110412
# Python   : 2.5 / 2.7
# License  : Distributed under the terms of the GNU General Public License
#            You may redistribute this software and/or modify it under the
#            terms of the GNU General Public License, as published by the
#            Free Software Foundation; either version 2 of the License,
#            or (at your option) any later version.
#            This software is distributed in the hope that it will be useful,
#            but WITHOUT ANY WARRANTY; without even the implied warranty of
#            MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#            See the GNU General Public License for more details.
#            You should have received a copy of the GNU General Public License
#            along with this. If not, write to:
#
#               The Free Software Foundation, Inc.,
#               51 Franklin Street, Fifth Floor
#               Boston, MA  02110-1301, USA.
#
#            Permission is granted to anyone to use this software for any
#            purpose, Including commercial applications, and to alter it and
#            redistribute it freely, subject to the following restrictions:
#
#            1. The origin of this software must not be misrepresented.
#               You must not claim that you wrote the original software.
#               If you use this software in a product, an acknowledgment
#               in the product documentation would be appreciated but is not
#               required.
#            2. Altered source versions must be plainly marked as such,
#               and must not be misrepresented as being the original software.
#            3. This notice may not be removed or altered from any source
#               distribution.
#
#
# History  : 12/04/2011 ListBox.py
# a simple window using PySide
# with a button and a listbox to load and select from
# Inspired by Vegaseat (See PyQt ListBox.py)
#

import sys
from PySide.QtCore import *
from PySide.QtGui import *

from tdict import *

request = ""

class MyFrame(QWidget):
    def __init__(self,
                 name_list1,
                 name_list2,
                 name_list3,
                 name_list4,
                 name_list5,
                 name_list6,
                 name_list7):
        QWidget.__init__(self)
        # setGeometry(x_pos, y_pos, width, height)
        self.setGeometry(100, 150, 900, 440)
        self.setWindowTitle("SQL query builder")

        # make lists available for methods
        self.name_list1 = name_list1
        self.name_list2 = name_list2
        self.name_list3 = name_list3
        self.name_list4 = name_list4
        self.name_list5 = name_list5
        self.name_list6 = name_list6
        self.name_list7 = name_list7

        self.querystring = LEditLab(self,"Request: ",'top')

        # use a grid layout for the widgets
        grid = QGridLayout()

        btn_go = QPushButton("Execute request")

        # bind the button click to a function reference
        self.connect(btn_go, SIGNAL("clicked()"), self.on_click)
        self.label1= QLabel()
        self.label2= QLabel()
        self.label3= QLabel()
        self.label4= QLabel()
        self.label5= QLabel()
        self.label6= QLabel()
        self.label7= QLabel()

        # Display labels in HTML or normal text
        #self.label1.setText("<font color=black size=20>SQL keywords</font>")
        self.label1.setText("SQL keywords")
        #self.label2.setText("<font color=black size=20>Fields</font>")
        self.label2.setText("Fields")
        #self.label3.setText("<font color=black size=20>Operators</font>")
        self.label3.setText("Operators")
        self.label4.setText("Maingroup")
        self.label5.setText("Group")
        self.label6.setText("Subgroup")
        self.label7.setText("Location")

        self.listbox1 = QListWidget()
        self.listbox2 = QListWidget()
        self.listbox3 = QListWidget()
        self.listbox4 = QListWidget()
        self.listbox5 = QListWidget()
        self.listbox6 = QListWidget()
        self.listbox7 = QListWidget()

        self.listbox1.addItems(self.name_list1)
        self.listbox2.addItems(self.name_list2)
        self.listbox3.addItems(self.name_list3)
        self.listbox4.addItems(self.name_list4)
        self.listbox5.addItems(self.name_list5)
        self.listbox6.addItems(self.name_list6)
        self.listbox7.addItems(self.name_list7)

        self.connect(self.listbox1,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select1
                     )
        self.connect(self.listbox2,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select2
                     )
        self.connect(self.listbox3,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select3
                     )
        self.connect(self.listbox4,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select4
                     )
        self.connect(self.listbox5,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select5
                     )
        self.connect(self.listbox6,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select6
                     )
        self.connect(self.listbox7,
                     SIGNAL("itemSelectionChanged()"),
                     self.on_select7
                     )

        # addWidget(widget, row, column, rowSpan, columnSpan)
        grid.addWidget(btn_go, 10, 3, 1, 1)

        # listbox spans over 5 rows and 2 columns
        grid.addWidget(self.label1, 0, 0)
        grid.addWidget(self.label2, 0, 1)
        grid.addWidget(self.label3, 0, 2)
        grid.addWidget(self.label4, 0, 3)
        grid.addWidget(self.label5, 0, 4)
        grid.addWidget(self.label6, 0, 5)
        grid.addWidget(self.label7, 0, 6)

        grid.addWidget(self.listbox1, 1, 0, 5, 1)
        grid.addWidget(self.listbox2, 1, 1, 5, 1)
        grid.addWidget(self.listbox3, 1, 2, 5, 1)
        grid.addWidget(self.listbox4, 1, 3, 5, 1)
        grid.addWidget(self.listbox5, 1, 4, 5, 1)
        grid.addWidget(self.listbox6, 1, 5, 5, 1)
        grid.addWidget(self.listbox7, 1, 6, 5, 1)

        grid.addWidget(self.querystring,8,0,1,7)
        self.setLayout(grid)

    def on_select1(self):
        """an item in the listbox has been clicked/selected"""
        global request
        request +=self.listbox1.selectedItems()[0].text()+ " "
        self.querystring.setText(request)
        #self.listbox1.clear()
        #self.listbox1.takeItem()
        #self.listbox1.removeItemWidget(self.listbox1.selectedItems()[0])
        #print request

    def on_select2(self):
        global request
        request +=self.listbox2.selectedItems()[0].text()+ " "
        self.querystring.setText(request)
        #print request

    def on_select3(self):
        global request
        request +=self.listbox3.selectedItems()[0].text()+ " "
        self.querystring.setText(request)
        #print request

    def on_select4(self):
        global request
        request +='"'+self.listbox4.selectedItems()[0].text()+ '" '
        self.querystring.setText(request)
        #print request

    def on_select5(self):
        global request
        request +='"'+self.listbox5.selectedItems()[0].text()+ '" '
        self.querystring.setText(request)
        #print request

    def on_select6(self):
        global request
        request +='"'+self.listbox6.selectedItems()[0].text()+ '" '
        self.querystring.setText(request)
        #print request

    def on_select7(self):
        global request
        request +='"'+self.listbox7.selectedItems()[0].text()+ '" '
        #self.listbox7.openPersistentEditor(self.listbox7.selectedItems()[0])
        self.querystring.setText(request)
        #self.listbox7.closePersistentEditor(self.listbox7.selectedItems()[0])
        #print request

    def on_click(self):
        print request

class LEditLab(QWidget):
    """label QLineEdit data entry/display to the left or on top"""
    def __init__(self, parent, mytext=QStringListModel(), pos='left'):
        QWidget.__init__(self, parent)
        self.label = QLabel(mytext)
        self.edit = QLineEdit()
        label_pos = QBoxLayout.LeftToRight if pos == 'left' \
            else QBoxLayout.TopToBottom
        layout = QBoxLayout(label_pos)
        layout.addWidget(self.label)
        layout.addWidget(self.edit)
        self.setLayout(layout)

    def text(self):
        """create QLineEdit() like method to get text"""
        return self.edit.text()

    def setText(self, text):
        """create QLineEdit() like method to set text"""
        return self.edit.setText(text)


FieldsList = ['stock',
              'FAMILIE',
              'HOOFDGROEP',
              'GROEP',
              'ONDERGROEP',
              'STUK',
              'LOCATIE'
              ]

OperatorsList = ['',
                 '(',
                 ')',
                 '[',
                 ']',
                 '<',
                 '>',
                 '=',
                 '<=',
                 '>=',
                 '!=',
                 '%',
                 'NOT LIKE'
                 ]

HoofdgroepList = ['MAL',
                  'LVS',
                  'SEC',
                  'FRG',
                  'CMB',
                  'DFR'
                  ]

GroepList = ['AEG',
             'ARC',
             'BAU',
             'CAN',
             'VER',
             'WHI',
             'ZAN',
             'ZER',
             'ZNS'
             ]

OndergroepList = ['ACC',
                  'BOU',
                  'CAR',
                  'ELT',
                  'MOT',
                  'THT'
                  ]

LocatieList = ['A%',
               'B%',
               'M%',
               'S%',
               'WA%',
               'WB%',
               'WC%',
               'WD%',
               'WE%',
               'WF%',
               'WG%',
               'KB%',
               'KH%'
               ]

app =  QApplication(sys.argv)
form = MyFrame(SQLiteKeywordsList,
                FieldsList,
                OperatorsList,
                HoofdgroepList,
                GroepList,
                OndergroepList,
                LocatieList)
form.show()
app.exec_()

tdict.py

#!/usr/bin/python
#-*- coding: utf-8 -*-
#
# ======================================================================
#
# File      : tdict.py
# Release   : Alpha
# Author    : J-M Desmettre
# Purpose   : 
# Created   : 20100916
# Revision  : 20110422
# Python    : 2.5 / 2.7
# License   : Distributed under the terms of the GNU General Public License
#             You may redistribute this software and/or modify it under the
#             terms of the GNU General Public License, as published by the
#             Free Software Foundation; either version 2 of the License,
#             or (at your option) any later version.
#             This software is distributed in the hope that it will be useful,
#             but WITHOUT ANY WARRANTY; without even the implied warranty of
#             MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
#             See the GNU General Public License for more details.
#             You should have received a copy of the GNU General Public License
#             along with this. If not, write to:
#
#            	 The Free Software Foundation, Inc.,
#            	 51 Franklin Street, Fifth Floor
#            	 Boston, MA  02110-1301, USA.
#
#            Permission is granted to anyone to use this software for any purpose,
#            including commercial applications, and to alter it and redistribute it
#            freely, subject to the following restrictions:
#
#            1. The origin of this software must not be misrepresented.
#               You must not claim that you wrote the original software.
#               If you use this software in a product, an acknowledgment
#               in the product documentation would be appreciated but is not required.
#            2. Altered source versions must be plainly marked as such,
#               and must not be misrepresented as being the original software.
#            3. This notice may not be removed or altered from any source distribution.
#
#
#
# ======================================================================


from pyExcelerator import *
from pyExcelerator.Worksheet import *

InputFile = 'Sample db.txt'
db_file = 'sample_data.sqlite'

DbName = ':memory:'                             # Create a database in memory
#DbName = db_file                                # Create a persistant database file
tableName = 'stock'
#tableName = 'loc'
tableList =[]
User = 'User'
Password = 'Password'
Host = 'localhost'


# A4 labels sheet layout :

# label size is 65mm x 35mm (width x height)
# 24 labels per sheet
# 3 labels across
# 8 labels down

#Default page size is A4.

# A4 = 210mm x 297mm or 595.27 x 841.89 points (1/72")
# Start coordinates botom left (0, 0)
# (n cm/2.54)*72 = value in points.
# 1cm = (1/2.54)*72 = 28.35 points
# 25mm = (25/25.40)*72 = 70.87 points
# 210mm =(210/25.40)*72 = 595.28 points
# 29.7cm = (29.7/2.54)*72 = 841.89 points



# --- pyExcelerator ------------------------------------------------------------

workbook = Workbook()
worksheet = workbook.add_sheet("Sheet_1")

#worksheet.col(0).width = 0x24E1
worksheet.left_margin = 0.787                   # Define page format
worksheet.right_margin = 0.394
worksheet.top_margin = 0.590
worksheet.bottom_margin = 0.394
worksheet.print_centered_horz = False
worksheet.header_str = ""
worksheet.footer_str = ""
worksheet.print_scaling = 80

borders = Borders()                             # Style definitions
borders.left = 7                                # borders thickness (0x00 to 0x07)
borders.right = 7
borders.top = 7
borders.bottom = 7
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
fnt = Font()
fnt.height = 8*20
num_format0 = '#,##0;[Red]-#,##0'               # -1,000        integer
num_format1 = '#,##0.00;[Red]-#,##0.00'         # -1,000.00     float
num_format3 = '#,##0.00%;[Red]-#,##0.00%'       # -0.00%        percent

style0 = XFStyle()
style0.borders = borders
style0.font.name = 'ARIAL'
style0.font.height = 8*20
style0.num_format_str = num_format0

style1 = XFStyle()
style1.borders = borders
style1.font.name = 'ARIAL'
style1.font.height = 8*20
style1.num_format_str = num_format1
style1_bold = XFStyle()
style1_bold.borders = borders
style1_bold.font.name = 'ARIAL'
style1_bold.font.height = 8*20
style1_bold.num_format_str = num_format1
style1_bold.font.bold = True

style2 = XFStyle()
style2.borders = borders
style2.alignment = al
style2.font.name = 'ARIAL'
style2.font.height = 12*20
style2.num_format_str = num_format0
style2.font.bold = False

style3 = XFStyle()
style3.borders = borders
style3.font.name = 'ARIAL'
style3.font.height = 8*20
style3.num_format_str = num_format3

style4 = XFStyle()
style4.borders = borders
style4.font.name = 'ARIAL'
style4.font.height = 8*20
style4.alignment.vert = Alignment.VERT_TOP
style4.alignment.wrap = Alignment.WRAP_AT_RIGHT
borders = Borders()
borders.left = 0
borders.right = 0
borders.top = 0
borders.bottom = 0
style4.borders = borders

style5 = XFStyle()
style5.borders = borders
style5.font.name = "ARIAL"
style5.font.height = 8*20
style5.alignment.orie = Alignment.ORIENTATION_90_CC

style_info = XFStyle()
style_info.font.name = 'ARIAL'
style_info.font.height = 8*20
borders = Borders()
borders.left = 0
borders.right = 0
borders.top = 0
borders.bottom = 0
style_info.borders = borders

style_bg = XFStyle()
p = Pattern()
style_bg.font.name = 'ARIAL'
style_bg.font.height = 8*20
fore_colour = style_bg.pattern.pattern_fore_colour
back_colour = style_bg.pattern.pattern_back_colour
p.pattern_fore_colour = 43                      # pale yellow
#p.pattern_fore_colour = 150                      # light grey
p.pattern = style_bg.pattern.SOLID_PATTERN
style_bg.pattern = p
borders = Borders()
borders.left = 7                                # borders thickness (0x00 to 0x07)
borders.right = 7
borders.top = 7
borders.bottom = 7
style_bg.borders = borders


SQLiteKeywordsList = ['ABORT','ACTION','ADD','AFTER','ALL','ALTER',
                      'ANALYZE','AND','AS','ASC','ATTACH','AUTOINCREMENT',
                      'BEFORE','BEGIN','BETWEEN','BY',
                      'CASCADE','CASE','CAST','CHECK','COLLATE','COLUMN',
                      'COMMIT','CONFLICT','CONSTRAINT','CREATE','CROSS',
                      'CURRENT_DATE','CURRENT_TIME','CURRENT_TIMESTAMP',
                      'DATABASE','DEFAULT','DEFERRABLE','DEFERRED','DELETE',
                      'DESC','DETACH','DISTINCT','DROP',
                      'EACH','ELSE','END','ESCAPE','EXCEPT','EXCLUSIVE',
                      'EXISTS','EXPLAIN',
                      'FAIL','FOR','FOREIGN','FROM','FULL',
                      'GLOB','GROUP',
                      'HAVING',
                      'IF','IGNORE','IMMEDIATE','IN','INDEX','INDEXED',
                      'INITIALLY','INNER','INSERT','INSTEAD','INTERSECT',
                      'INTO','IS','ISNULL',
                      'JOIN',
                      'KEY',
                      'LEFT','LIKE','LIMIT',
                      'MATCH',
                      'NATURAL','NO','NOT','NOTNULL','NULL',
                      'OF','OFFSET','ON','OR','ORDER','OUTER',
                      'PLAN','PRAGMA','PRIMARY',
                      'QUERY',
                      'RAISE','REFERENCES','REGEXP','REINDEX','RELEASE',
                      'RENAME','REPLACE','RESTRICT','RIGHT','ROLLBACK','ROW',
                      'SAVEPOINT','SELECT','SELECT * FROM','SET',
                      'TABLE','TEMP','TEMPORARY','THEN','TO','TRANSACTION',
                      'TRIGGER',
                      'UNION','UNIQUE','UPDATE','USING',
                      'VACUUM','VALUES','VIEW','VIRTUAL',
                      'WHEN',
                      'WHERE'
                     ]

DynTableDictionary = {'stock':[('Id INT PRIMARY KEY'),
                               ('ARTNR CHAR'),
                               ('FAM CHAR'),
                               ('DESCRIPTION CHAR'),
                               ('TYPE CHAR'),
                               ('BRAND CHAR'),
                               ('LEVNR CHAR'),
                               ('VAT CHAR'),
                               ('PRICE1 REAL'),
                               ('PRICE2 REAL'),
                               ('PRICE3 REAL'),
                               ('PRICE4 REAL'),
                               ('NRFABR CHAR'),
                               ('LEVTYPE CHAR'),
                               ('MAINGROUP CHAR'),
                               ('GROUP_ CHAR'),
                               ('SUBGROUP CHAR'),
                               ('STUK CHAR'),
                               ('LOCATION CHAR'),
                               ('STOCK INTEGER'),
                               ('DATEIN CHAR'),
                               ('DATEOUT CHAR'),
                               ('KUMUL INTEGER'),
                               ('VERKOPEN INTEGER')
                              ],
                      'brand':[('brand_sel CHAR')]
                     }

# <EOF>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.