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_()#!/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>