class bc6010(object):
def __init__(self, **opts):
self.opts = opts
if self.setVariables():
if "args" in self.opts:
self.oldtab = self.opts["args"][0]
self.newtab = self.opts["args"][1]
self.doProcess()
else:
self.mainProcess()
if "wait" in self.opts:
self.df.mstFrame.wait_window()
else:
self.opts["mf"].startLoop()
def setVariables(self):
self.tables = (("bwldrt", "bdt_cono", "bdt_tab", "bdt_team1",
"bdt_team2", "bdt_team3"), ("bwlent", "bce_cono",
"bce_scod"),
("bwlflm", "bfm_cono", "bfm_captain"),
("bwlflt", "bft_cono", "bft_skip", "bft_player"),
("bwlgme", "bcg_cono", "bcg_scod",
"bcg_ocod"), ("bwltab", "btb_cono", "btb_tab"))
tabs = []
for tab in self.tables:
if tab[0] not in tabs:
tabs.append(tab[0])
self.sql = Sql(self.opts["mf"].dbm, tabs, prog=self.__class__.__name__)
if self.sql.error:
return
gc = GetCtl(self.opts["mf"])
bwlctl = gc.getCtl("bwlctl", self.opts["conum"])
if not bwlctl:
return
self.mlint = bwlctl["ctb_mlint"]
self.samen = bwlctl["ctb_samen"]
if self.mlint == "Y" and self.samen == "Y":
return
self.mstart = bwlctl["ctb_mstart"]
self.fstart = bwlctl["ctb_fstart"]
self.nstart = bwlctl["ctb_nstart"]
return True
def mainProcess(self):
tab = {
"stype":
"R",
"tables": ("bwltab", ),
"cols": (("btb_tab", "", 0, "Tab-No"),
("btb_surname", "", 0, "Surname", "Y"), ("btb_names", "",
0, "Names")),
"where": [("btb_cono", "=", self.opts["conum"])],
"order":
"btb_tab"
}
fld = [(["T", 0, 0, 0], "I@btb_tab", 0, "Old Tab", "Old Tab Number",
"", "Y", self.doOldTab, tab, None, ("notzero", )),
(["T", 0, 0, 18], "ONA", 30, ""),
(["T", 0, 1, 0], "I@btb_tab", 0, "New Tab", "New Tab Number",
"", "Y", self.doNewTab, None, None, ("notzero", ))]
tnd = ((self.doProcess, "y"), )
txt = (self.doExit, )
but = [("Generate", None, self.doGenerate, 0, ("T", 0, 1), ("T", 0, 2),
"Auto Generate New Tab Numbers Based on Names and Gender")]
self.df = TartanDialog(self.opts["mf"],
eflds=fld,
tend=tnd,
txit=txt,
butt=but)
def doOldTab(self, frt, pag, r, c, p, i, w):
acc = self.sql.getRec("bwltab",
where=[("btb_cono", "=", self.opts["conum"]),
("btb_tab", "=", w)],
limit=1)
if not acc:
return "Invalid Tab Number, Does Not exist"
self.oldtab = w
self.gender = acc[self.sql.bwltab_col.index("btb_gender")]
if acc[4]:
self.df.loadEntry(frt,
pag,
p + 1,
data="%s, %s" % (acc[3], acc[4].split()[0]))
else:
self.df.loadEntry(frt, pag, p + 1, data=acc[3])
def doNewTab(self, frt, pag, r, c, p, i, w):
if self.oldtab < self.nstart:
if self.mstart < self.fstart:
if self.gender == "M" and w >= self.fstart:
return "Invalid Male Tab Number"
elif self.gender == "F" and w < self.fstart:
return "Invalid Female Tab Number"
elif self.gender == "M" and w >= self.mstart:
return "Invalid Male Tab Number"
elif self.gender == "F" and w < self.mstart:
return "Invalid Female Tab Number"
elif w < self.nstart:
return "Invalid Non-Member Tab Number"
acc = self.sql.getRec("bwltab",
where=[("btb_cono", "=", self.opts["conum"]),
("btb_tab", "=", w)],
limit=1)
if acc:
return "Invalid Tab Number, Already Exists"
self.newtab = w
def doGenerate(self):
self.opts["mf"].updateStatus("")
but = (("Members", "M"), ("Non Members", "N"), ("All", "A"), ("None",
"X"))
ok = askChoice(self.opts["mf"].body, "ARE YOU SURE???",
"Are you Certain this is what you want To Do? This will "\
"Automatically Generate New Tab Numbers For Selected Range "\
"Based On Member's Names and Gender!", butt=but, default="None")
if ok == "X":
self.df.focusField("T", 0, 1)
return
self.df.closeProcess()
splash = SplashScreen(self.opts["mf"].body,
"Generating New Tab Numbers ... Please Wait")
# Create temporary tables
for ot in self.tables:
tt = "%s_temp" % ot[0]
self.sql.sqlRec("Create table %s as Select * from %s "\
"where %s = %s" % (tt, ot[0], ot[1], self.opts["conum"]))
self.sql.delRec(ot[0], where=[(ot[1], "=", self.opts["conum"])])
mem = self.sql.sqlRec("Select * from bwltab_temp where btb_cono = %s "\
"and btb_tab < %s order by btb_surname, btb_names" %
(self.opts["conum"], self.nstart))
non = self.sql.sqlRec("Select * from bwltab_temp where btb_cono = %s "\
"and btb_tab >= %s order by btb_surname, btb_names" %
(self.opts["conum"], self.nstart))
mstart = self.mstart
fstart = self.fstart
nstart = self.nstart
key = {}
for tab in mem:
otb = tab[self.sql.bwltab_col.index("btb_tab")]
if ok in ("A", "M"):
gdr = tab[self.sql.bwltab_col.index("btb_gender")]
if gdr == "M":
new = mstart
mstart += 1
else:
new = fstart
fstart += 1
key[otb] = new
else:
key[otb] = otb
for tab in non:
otb = tab[self.sql.bwltab_col.index("btb_tab")]
if ok in ("A", "N"):
key[otb] = nstart
nstart += 1
else:
key[otb] = otb
for ot in self.tables:
tt = "%s_temp" % ot[0]
cc = getattr(self.sql, "%s_col" % ot[0])
recs = self.sql.sqlRec("Select * from %s" % tt)
for rec in recs:
for k in ot[2:]:
c = rec[cc.index(k)]
if c not in key:
continue
rec[cc.index(k)] = key[c]
self.sql.insRec(ot[0], data=rec)
self.sql.sqlRec("Drop table %s" % tt)
self.opts["mf"].dbm.commitDbase(ask=True)
splash.closeSplash()
if "wait" not in self.opts:
self.opts["mf"].closeLoop()
def doProcess(self):
for tab in self.tables:
for x in range(len(tab[2:])):
whr = [(tab[1], "=", self.opts["conum"]),
(tab[x + 2], "=", self.oldtab)]
dat = [self.newtab]
col = [tab[x + 2]]
self.sql.updRec(tab[0], where=whr, data=dat, cols=col)
if "args" not in self.opts:
self.df.focusField("T", 0, 1)
def doExit(self):
self.df.closeProcess()
self.opts["mf"].dbm.commitDbase(ask=True)
if "wait" not in self.opts:
self.opts["mf"].closeLoop()
class bc2060(object):
def __init__(self, **opts):
self.opts = opts
if self.setVariables():
self.mainProcess()
self.opts["mf"].startLoop()
def setVariables(self):
t = time.localtime()
self.today = ((t[0] * 10000) + (t[1] * 100) + t[2])
self.sql = Sql(self.opts["mf"].dbm,
["bwlcmp", "bwltab", "bwlgme", "bwltyp", "bwlent"],
prog=self.__class__.__name__)
if self.sql.error:
return
return True
def mainProcess(self):
com = {
"stype":
"R",
"tables": ("bwlcmp", ),
"cols":
(("bcm_code", "", 0, "Cod"), ("bcm_name", "", 0, "Name", "Y"),
("bcm_date", "", 0, "Date")),
"where": [("bcm_cono", "=", self.opts["conum"])]
}
skp = {
"stype":
"R",
"tables": ("bwltab", "bwlgme"),
"cols":
(("btb_tab", "", 0, "Cod"), ("btb_surname", "", 0, "Surname",
"Y"), ("btb_names", "", 0, "Names"),
("bcg_rink", "", 0, "RK"), ("bcg_ocod", "", 0, "Opp")),
"where": [("btb_cono", "=", self.opts["conum"]),
("bcg_cono=btb_cono", ), ("bcg_scod=btb_tab", )],
"whera": [("T", "bcg_ccod", 0, 0), ("T", "bcg_game", 2, 0)],
"group":
"btb_tab",
"order":
"btb_tab"
}
fld = (
(("T",0,0,0),"I@bcm_code",0,"","",
"","Y",self.doCmpCod,com,None,("notzero",)),
(("T",0,0,0),"ONA",30,""),
(("T",0,1,0),"I@bcg_game",0,"Game Number","",
1,"N",self.doGame,None,None,("efld",)),
(("T",0,1,0),"I@bcg_date",0,"Date","",
self.today,"N",self.doDate,None,None,("efld",)),
(("T",0,2,0),"IUA",30,"Greens","Greens (A,B,C)",
"","N",self.doGreens,None,None,("efld",),None,"Available "\
"Greens in the format A,B or A,B345 showing Green Code and "\
"Rinks. If the Rinks are Not Entered they will Default to 6."),
(("C",0,0,0),"I@bcg_scod",0,"","",
"","N",self.doSkpCod,skp,None,("notzero",)),
(("C",0,0,0),"ONA",30,"Skp-Name"),
(("C",0,0,0),"I@bcg_ocod",0,"","",
"","N",self.doOppCod,skp,None,("notzero",)),
(("C",0,0,0),"ONA",30,"Opp-Name"),
(("C",0,0,0),"I@bcg_rink",0,"","",
"","N",self.doRink,None,None,("notblank",)))
but = (("Quit", None, self.doQuit, 1, None, None), )
tnd = ((self.doEnd, "y"), )
txt = (self.doExit, )
cnd = ((self.doEnd, "y"), )
cxt = (self.doExit, )
self.df = TartanDialog(self.opts["mf"],
tops=False,
eflds=fld,
butt=but,
tend=tnd,
txit=txt,
cend=cnd,
cxit=cxt)
def doCmpCod(self, frt, pag, r, c, p, i, w):
com = self.sql.getRec("bwlcmp",
cols=["bcm_name", "bcm_date", "bcm_type"],
where=[("bcm_cono", "=", self.opts["conum"]),
("bcm_code", "=", w)],
limit=1)
if not com:
return "Invalid Competition Code"
name, self.sdat, ctyp = com
self.df.loadEntry(frt, pag, p + 1, data=name)
chk = self.sql.getRec("bwltyp",
cols=["bct_cfmat"],
where=[("bct_cono", "=", self.opts["conum"]),
("bct_code", "=", ctyp)],
limit=1)
if chk[0] in ("D", "K", "R"):
return "Knockout and R/Robin Draws Cannot be Changed"
chk = self.sql.getRec("bwlgme",
cols=[
"bcg_game", "bcg_date", "bcg_aflag",
"sum(bcg_ocod)", "sum(bcg_sfor)"
],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", w)],
group="bcg_game, bcg_date, bcg_aflag")
self.ccod = w
self.game = 0
self.draws = {}
self.manual = False
for ck in chk:
if ck[2] == "A" or ck[4]:
continue
if ck[4]:
continue
if ck[2] in ("", "D", "S") and ck[3]:
self.draws[ck[0]] = ck[1:]
if not self.draws:
ok = askQuestion(self.opts["mf"].body, "Manual Draw",
"Is this the First Game and is it going to be a "\
"Manual Draw?", default="no")
if ok == "no":
return "rf"
self.manual = True
self.game = 1
self.df.loadEntry(frt, pag, p + 2, data=self.game)
self.totskp = self.sql.getRec("bwlent",
cols=["count(*)"],
where=[("bce_cono", "=",
self.opts["conum"]),
("bce_ccod", "=", self.ccod)],
limit=1)[0]
return "sk2"
def doGame(self, frt, pag, r, c, p, i, w):
if w not in self.draws:
return "Invalid Game Number"
self.game = w
self.date = self.draws[w][0]
self.df.loadEntry(frt, pag, p + 1, data=self.date)
self.totskp = self.sql.getRec("bwlent",
cols=["count(*)"],
where=[("bce_cono", "=",
self.opts["conum"]),
("bce_ccod", "=", self.ccod)],
limit=1)[0]
def doDate(self, frt, pag, r, c, p, i, w):
if w < self.today or w < self.sdat:
return "Invalid Date, in the Past or Before the Starting Date"
self.date = w
def doGreens(self, frt, pag, r, c, p, i, w):
if not w:
return "Invalid Greens"
self.rinks = []
rinks = 0
grns = w.split(",")
for gr in grns:
if len(gr) == 1:
for x in range(1, 7):
self.rinks.append("%s%s" % (gr[0], x))
rinks += 6
elif len(gr) == 2 and gr[1] == "7":
for x in range(1, 8):
self.rinks.append("%s%s" % (gr[0], x))
rinks += 7
else:
for rk in gr[1:]:
self.rinks.append("%s%s" % (gr[0], rk))
rinks += 1
if int(self.totskp / 2) > rinks:
return "Not Enough Rinks"
def doSkpCod(self, frt, pag, r, c, p, i, w):
skp = self.sql.getRec(
tables=["bwlgme", "bwltab"],
cols=["btb_surname", "btb_names", "bcg_ocod", "bcg_rink"],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod), ("bcg_scod", "=", w),
("bcg_game", "=", self.game), ("btb_cono=bcg_cono", ),
("btb_tab=bcg_scod", )],
limit=1)
if not skp:
return "Invalid Skip Code"
self.skip = w
self.old_opp = skp[2]
self.old_rink = skp[3]
if skp[1]:
name = "%s, %s" % tuple(skp[:2])
else:
name = skp[0]
self.df.loadEntry(frt, pag, p + 1, data=name)
if self.old_opp:
self.df.loadEntry(frt, pag, p + 2, data=self.old_opp)
opp = self.sql.getRec("bwltab",
cols=["btb_surname", "btb_names"],
where=[("btb_cono", "=", self.opts["conum"]),
("btb_tab", "=", skp[2])],
limit=1)
if opp[1]:
name = "%s, %s" % tuple(opp[:2])
else:
name = opp[0]
self.df.loadEntry(frt, pag, p + 3, data=name)
if self.old_rink:
self.df.loadEntry(frt, pag, p + 4, data=self.old_rink)
def doOppCod(self, frt, pag, r, c, p, i, w):
opp = self.sql.getRec(
tables=["bwlgme", "bwltab"],
cols=["btb_surname", "btb_names", "bcg_ocod", "bcg_rink"],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod), ("bcg_scod", "=", w),
("bcg_game", "=", self.game), ("btb_cono=bcg_cono", ),
("btb_tab=bcg_scod", )],
limit=1)
if not opp:
return "Invalid Opponents Code"
self.new_opp = w
self.chg_skp = opp[2]
if opp[1]:
name = "%s, %s" % tuple(opp[:2])
else:
name = opp[0]
self.df.loadEntry(frt, pag, p + 1, data=name)
def doRink(self, frt, pag, r, c, p, i, w):
if w not in self.rinks:
return "Invalid Rink"
self.new_rink = w
def doEnd(self):
if self.df.frt == "T":
self.df.focusField("C", 0, 1)
else:
self.df.advanceLine(0)
self.sql.updRec("bwlgme",
cols=["bcg_date", "bcg_ocod", "bcg_rink"],
data=[self.date, self.new_opp, self.new_rink],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_scod", "=", self.skip),
("bcg_game", "=", self.game)])
self.sql.updRec("bwlgme",
cols=["bcg_date", "bcg_ocod", "bcg_rink"],
data=[self.date, self.skip, self.new_rink],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_scod", "=", self.new_opp),
("bcg_game", "=", self.game)])
def doExit(self):
if self.df.frt == "C":
chk = self.sql.getRec("bwlgme",
cols=["bcg_ocod", "count(*)"],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_game", "=", self.game)],
group="bcg_ocod",
order="bcg_ocod")
for c in chk:
if c[0] and c[1] != 1:
skp = self.sql.getRec("bwlgme",
cols=["bcg_scod"],
where=[("bcg_cono", "=",
self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_game", "=", self.game),
("bcg_ocod", "=", c[0])],
order="bcg_scod")
err = "Opponent %s is Drawn Against %s Skips\n" % tuple(c)
for s in skp:
err = err + "\nSkip %s" % s[0]
skp = self.sql.sqlRec("Select bce_scod from bwlent where "\
"bce_ccod = %s and bce_scod not in (select bcg_ocod "\
"from bwlgme where bcg_ccod = %s and bcg_game = %s)" %
(self.ccod, self.ccod, self.game))
err = err + "\n\nSkips Without Opponents\n"
for s in skp:
err = err + "\nSkip %s" % s[0]
showError(self.opts["mf"].body, "Skip Error", err)
self.df.focusField(self.df.frt, self.df.pag, self.df.col)
return
chk = self.sql.getRec("bwlgme",
cols=["bcg_rink", "count(*)"],
where=[("bcg_cono", "=", self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_game", "=", self.game)],
group="bcg_rink",
order="bcg_rink")
for c in chk:
if c[1] != 2:
skp = self.sql.getRec("bwlgme",
cols=["bcg_scod", "bcg_ocod"],
where=[("bcg_cono", "=",
self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_game", "=", self.game),
("bcg_rink", "=", c[0])],
group="bcg_scod, bcg_ocod, bcg_rink",
order="bcg_scod")
skps = []
for s in skp:
if [s[1], s[0]] not in skps:
skps.append(s)
err = "Rink %s Shows %s time(s)\n" % (c[0], int(c[1] / 2))
for s in skps:
err = err + "\nSkip %2s Opp %2s" % (s[0], s[1])
rnk = self.sql.getRec("bwlgme",
cols=["bcg_rink", "count(*)"],
where=[("bcg_cono", "=",
self.opts["conum"]),
("bcg_ccod", "=", self.ccod),
("bcg_game", "=", self.game)],
group="bcg_rink",
order="bcg_rink")
rnks = []
for r in rnk:
rnks.append(r[0])
mis = ""
for r in self.rinks:
if r not in rnks:
if not mis:
mis = r
else:
mis = "%s, %s" % (mis, r)
err = err + "\n\nAvailable Rink(s)\n\n%s" % mis
showError(self.opts["mf"].body, "Rink Error", err)
self.df.focusField(self.df.frt, self.df.pag, self.df.col)
return
self.opts["mf"].dbm.commitDbase(ask=True)
self.df.closeProcess()
self.opts["mf"].closeLoop()
def doQuit(self):
self.opts["mf"].dbm.rollbackDbase()
self.df.closeProcess()
self.opts["mf"].closeLoop()
class tb1020(object):
def __init__(self, **opts):
self.opts = opts
if self.setVariables():
if "tab" in self.opts and self.opts["tab"]:
self.tabs = self.opts["tab"]
self.old = []
tab = self.sql.getRec("ftable",
cols=["ft_tabl"],
where=[("ft_seq", "=", 1)],
order="ft_tabl")
for t in tab:
if t[0] not in ("ftable", "ffield"):
self.old.append(t[0])
self.old.sort()
else:
self.doGetTables()
self.doProcessTables()
if self.opts["cln"]:
self.doCleanDatabase()
if self.opts["ver"]:
sql = Sql(self.dbm,
"verupd",
error=False,
prog=self.__class__.__name__)
if not sql.getRec("verupd"):
sql.insRec("verupd", data=[self.opts["ver"], self.sysdtw])
else:
sql.updRec("verupd", data=[self.opts["ver"], self.sysdtw])
self.dbm.commitDbase()
self.doFixAge()
self.doFinal()
if self.dbm.dbase == "SQLite":
self.dbm.commitDbase()
self.dbm.cu.execute("PRAGMA JOURNAL_MODE=DELETE")
self.dbm.cu.execute("PRAGMA SYNCHRONOUS=FULL")
def setVariables(self):
t = time.localtime()
self.sysdtw = ((t[0] * 10000) + (t[1] * 100) + t[2])
if not self.opts["mf"]:
try:
rcdic = loadRcFile(rcfile=self.opts["rcf"])
if rcdic == "error":
raise Exception
self.dbm = Dbase(rcdic=rcdic)
if self.dbm.err:
raise Exception
self.dbm.openDbase()
except:
print("DB or RC File Error", "DB Error or RC File Not Found")
return
else:
self.dbm = self.opts["mf"].dbm
self.sql = Sql(self.dbm, ["ffield", "ftable", "verupd"],
error=False,
prog=self.__class__.__name__)
if "verupd" in self.sql.error:
self.oldver = (0, 0)
elif self.sql.error:
return
else:
v = self.sql.getRec("verupd", cols=["ver_version"], limit=1)
if v[0]:
ov = v[0].split(".")
self.oldver = (int(ov[0]), int(ov[1].rstrip()))
else:
self.oldver = (0, 0)
if self.oldver[0] and self.oldver[:2] < (5, 5):
txt = "Your current version must be 5.5 or later before you "\
"can upgrade to this version. If you need help to "\
"upgrade please contact [email protected]"
if self.opts["mf"] and self.opts["mf"].window:
showError(self.opts["mf"].body, "Version Error", txt)
else:
print(txt)
return
self.topts = [("-u", self.opts["usr"]), ("-c", "t"), ("-w", False),
("-x", False)]
if self.opts["pwd"]:
self.topts.append(("-p", self.opts["pwd"]))
self.tabord = {
"asstrn": ["ast_capdt", "ast_seq"],
"bkmrtt": ["brt_capdt", "brt_seq"],
"bkmtrn": ["bkt_capdt", "bkt_seq"],
"chglog": ["chg_dte", "chg_seq"],
"crsage": ["cra_curdt", "cra_seq"],
"crstrn": ["crt_capdt", "crt_seq"],
"cshana": ["can_date", "can_seq"],
"ctlnot": ["not_date", "not_seq"],
"ctlvtf": ["vtt_capdt", "vtt_seq"],
"drsage": ["dra_curdt", "dra_seq"],
"drstrn": ["drt_capdt", "drt_seq"],
"genrct": ["grt_seq", "grt_date"],
"gentrn": ["glt_capdt", "glt_seq"],
"ibttrn": ["ibt_capdt", "ibt_seq"],
"lontrn": ["lnt_capdt", "lnt_seq"],
"memage": ["mta_curdt", "mta_seq"],
"memsta": ["mls_date", "mls_seq"],
"memtrn": ["mlt_capdt", "mlt_seq"],
"memtrs": ["mst_trdt", "mst_seq"],
"rcaowt": ["rot_capdt", "rot_seq"],
"rcatnt": ["rtu_capdt", "rtu_seq"],
"rtltrn": ["rtt_capdt", "rtt_seq"],
"slsiv3": ["si3_seq"],
"strpot": ["pot_capdt", "pot_seq"],
"strtrn": ["stt_capdt", "stt_seq"],
"tpldet": ["tpd_name", "tpd_detseq"],
"wagcap": ["wcp_capdt", "wcp_seq"],
"wagltf": ["wlt_capdt", "wlt_seq"],
"wagtf1": ["wt1_capdt", "wt1_seq"]
}
if self.dbm.dbase == "PgSQL":
self.lmt = 5000
elif self.dbm.dbase == "SQLite":
self.lmt = 500
self.dbm.commitDbase()
self.dbm.cu.execute("PRAGMA JOURNAL_MODE=OFF")
self.dbm.cu.execute("PRAGMA SYNCHRONOUS=OFF")
else:
return
return True
def doProcessTables(self):
txt = "Updating Database ... Please Wait"
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
if self.opts["bar"]:
self.p1 = ProgressBar(self.opts["mf"].body,
typ=txt,
mxs=len(self.tabs))
elif self.opts["bar"]:
print(txt)
for num, self.table in enumerate(self.tabs):
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
self.p1.displayProgress(num)
recs = False
chk = self.doReadTables("idx")
if not chk:
chk = self.doReadTables("fld")
if chk or self.opts["upd"]:
if chk and chk == "skip":
continue
recs = True
self.doNewTable()
elif self.oldfld != self.newfld:
if len(self.oldfld) != len(self.newfld):
recs = True
else:
for seq, fld in enumerate(self.oldfld):
for s, f in enumerate(fld):
if f != self.newfld[seq][s]:
if s in (1, 2, 3, 4):
recs = True
break
if recs:
break
if recs:
self.doNewTable()
else:
self.doUpdateTable()
elif self.table == "strpom" and self.strpom:
recs = True
self.doNewTable()
if not recs and self.oldidx != self.newidx:
old = self.sql.getRec("ftable",
where=[("ft_tabl", "=", self.table)],
order="ft_seq")
for rec in old:
nam = "%s_key%s" % (self.table, str(rec[2]))
if self.doCheckIndex(self.table, nam):
self.sql.sqlRec("Drop index %s" % nam)
DBCreate(dbm=self.dbm,
opts=[("-u", self.opts["usr"]), ("-c", "p"),
("-t", self.table), ("-w", True),
("-x", False)])
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
self.p1.closeProgress()
def doGetTables(self):
try:
self.tabs = list(tabdic.keys())
if "ffield" in self.tabs:
self.tabs.remove("ffield")
if "ftable" in self.tabs:
self.tabs.remove("ftable")
self.tabs.sort()
except:
if self.opts["mf"] and self.opts["mf"].window:
showError(self.opts["mf"].body, "Error",
"Missing Tables Dictionary")
else:
print("Missing Tables Dictionary")
return
self.old = []
tab = self.sql.getRec("ftable",
cols=["ft_tabl"],
where=[("ft_seq", "=", 1)],
order="ft_tabl")
for t in tab:
if t[0] not in ("ftable", "ffield"):
self.old.append(t[0])
self.old.sort()
return True
def doReadTables(self, tipe=None):
if self.table not in tabdic:
return "skip"
if tipe == "idx":
tab = "ftable"
old = self.sql.getRec("ftable",
where=[("ft_tabl", "=", self.table)],
order="ft_seq")
else:
tab = "ffield"
old = self.sql.getRec("ffield",
where=[("ff_tabl", "=", self.table)],
order="ff_seq")
if old and self.table == "strpom":
self.strpom = False
tmp = copyList(old)
for num, rec in enumerate(tmp):
if rec[1] == 13 and rec[2] == "pom_mess":
self.strpom = True
old[num][1] = 14
elif rec[1] == 14 and rec[2] == "pom_delno":
old[num][1] = 15
elif rec[1] == 15 and rec[2] == "pom_deldt":
old[num][1] = 16
elif rec[1] == 16 and rec[2] == "pom_capnm":
old[num][1] = 17
elif rec[1] == 17 and rec[2] == "pom_capdt":
old[num][1] = 18
if not old:
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("Table %s Not Found in %s" % \
(self.table, tab))
else:
print("Table %s Not Found in %s" % (self.table, tab))
return tipe
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
setattr(self, "old%s" % tipe, old)
new = []
for t in tabdic[self.table][tipe]:
n = copy.copy(t)
n.insert(0, self.table)
if tipe == "idx":
n[2] = int(n[2])
for _ in range(len(n), 14):
n.append("")
else:
n[1] = int(n[1])
n[4] = float(n[4])
new.append(n)
setattr(self, "new%s" % tipe, new)
def doUpdateTable(self):
for seq, fld in enumerate(self.newidx):
self.sql.updRec("ftable",
where=[("ft_tabl", "=", fld[0]),
("ft_seq", "=", fld[2])],
cols=["ft_desc"],
data=[fld[1]])
for seq, fld in enumerate(self.newfld):
self.sql.updRec("ffield",
where=[("ff_tabl", "=", fld[0]),
("ff_seq", "=", fld[1])],
cols=["ff_desc", "ff_head"],
data=[fld[5], fld[6]])
self.dbm.commitDbase()
def doNewTable(self):
sql = Sql(self.dbm,
self.table,
error=False,
prog=self.__class__.__name__)
if sql.error:
data = None
else:
# Read Existing Table
cols = getattr(sql, "%s_col" % self.table)
order = ""
if self.table in self.tabord:
if self.table == "slsiv3" and "si3_seqnum" in cols:
ords = ["si3_seqnum"]
else:
ords = self.tabord[self.table]
for fld in ords:
if fld in cols:
if not order:
order = fld
else:
order = "%s, %s" % (order, fld)
data = sql.getRec(tables=self.table, order=order)
# Create New Table
opts = copyList(self.topts)
opts.append(("-t", self.table))
DBCreate(dbm=self.dbm, opts=opts)
ndata = []
if data:
sql = Sql(self.dbm,
self.table,
error=False,
prog=self.__class__.__name__)
# Create Dictionaries
olddic = {}
for f in self.oldfld:
olddic[f[2]] = f[1:]
newdic = {}
for f in self.newfld:
newdic[f[2]] = f[1:]
# Populate New Table
mx1 = len(data)
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
pbar = "w"
p2 = ProgressBar(self.opts["mf"].body,
inn=self.p1,
typ="Converting Table %s" % self.table,
mxs=mx1)
else:
pbar = False
p2 = None
for num, old in enumerate(data):
# Convert old data to new format
if pbar:
p2.displayProgress(num)
dat = []
for csf in self.newfld:
nam = csf[2]
typ = csf[3]
if typ == "US":
continue
siz = csf[4]
if nam in olddic and typ == olddic[nam][2]:
new = old[olddic[nam][0]]
elif nam in olddic:
try:
ccd = CCD(old[olddic[nam][0]], typ, siz)
if ccd.err:
raise Exception
new = ccd.work
except:
if typ[1].lower() in ("a", "x"):
new = ""
else:
new = 0
elif self.table == "cshana" and nam == "can_gflag":
new = "N"
elif self.table == "ctlrep" and nam == "rep_cono":
new = old[olddic["rpm_cono"][0]]
elif self.table == "ctlrep" and nam == "rep_code":
new = old[olddic["rpm_rep"][0]]
elif self.table == "ctlrep" and nam == "rep_name":
new = old[olddic["rpm_name"][0]]
elif self.table == "bksmst" and nam == "bmf_cono":
new = 1
elif self.table == "bksown" and nam == "bof_cono":
new = 1
elif self.table == "bwlctl" and nam == "ctb_mscat":
new = 0
elif self.table == "bwlctl" and nam == "ctb_mixed":
new = "N"
elif self.table == "bwlgme" and nam == "bcg_sfor":
new = old[olddic["bcg_shots_for"][0]]
elif self.table == "bwlgme" and nam == "bcg_sagt":
new = old[olddic["bcg_shots_agt"][0]]
elif self.table == "bwlgme" and nam == "bcg_a_sfor":
new = old[olddic["bcg_a_shots_for"][0]]
elif self.table == "bwlgme" and nam == "bcg_a_sagt":
new = old[olddic["bcg_a_shots_agt"][0]]
elif self.table == "crsmst" and nam == "crm_stat":
new = "N"
elif self.table == "drsmst" and nam == "drm_stat":
new = "N"
elif self.table == "slsiv2" and nam == "si2_line":
new = old[olddic["si2_seq"][0]]
elif self.table == "slsiv3" and nam == "si3_line":
new = old[olddic["si3_seq"][0]]
elif self.table == "slsiv3" and nam == "si3_seq":
new = 0
elif typ[1].lower() in ("a", "x"):
new = ""
else:
new = 0
dat.append(new)
ndata.append(dat)
if ndata:
# Populate new table
if pbar:
txt = "Populating Table %s" % self.table
p2.mxp = 1
p2.mxs = len(ndata)
p2.txtlab.configure(text=txt)
p2.displayProgress()
clm = ""
fmt = ""
for csf in self.newfld:
nam = csf[2]
typ = csf[3]
if typ == "US":
continue
if not clm:
clm = nam
fmt = self.dbm.dbf
else:
clm += ",%s" % nam
fmt = "%s,%s" % (fmt, self.dbm.dbf)
nlim = int(self.lmt / len(self.newfld))
for x in range(0, len(ndata), nlim):
if pbar:
p2.displayProgress(x)
y = x + nlim
tft = ",".join("(%s)" % fmt for _ in ndata[x:y])
dat = list(chain.from_iterable(ndata[x:y]))
cmd = "Insert into %s (%s) values %s" % (self.table, clm,
tft)
sql.sqlRec((cmd, dat))
#self.dbm.commitDbase()
if pbar:
p2.closeProgress()
# Update Report Writer for missing columns
cols = []
for csf in self.oldfld:
if csf[2] not in newdic:
cols.append(csf[2])
if cols:
tabs = (("rptcol", "rpc_rnam",
"rpc_detail"), ("rptexc", "rpx_rnam", "rpx_exc"),
("rptjon", "rpj_rnam",
"rpj_col"), ("rptord", "rpo_rnam", "rpo_column"))
for tab in tabs:
sql = Sql(self.dbm,
tab[0],
error=False,
prog=self.__class__.__name__)
if sql.error:
continue
dat = sql.getRec(tables=tab[0], cols=[tab[1], tab[2]])
for d in dat:
for col in cols:
if d.count(col):
sql.delRec(tab[0],
where=[(tab[1], "=", d[0]),
(tab[2], "=", d[1])])
self.dbm.commitDbase()
def doCleanDatabase(self):
txt = "Cleaning Database ... Please Wait"
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(txt)
elif self.opts["bar"]:
print(txt)
# Removing unused tables
self.doGetTables()
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
pb = ProgressBar(self.opts["mf"].body,
mxs=(len(self.old) + len(self.tabs)),
typ=txt)
for n1, t in enumerate(self.old):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1)
if t not in self.tabs:
self.dbm.dropTable(t, True)
self.dbm.commitDbase()
# Creating new tables and/or indexes
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
for n2, t in enumerate(self.tabs):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1 + n2)
if not self.dbm.checkTable(t):
opts = copyList(self.topts)
opts.append(("-t", t))
DBCreate(dbm=self.dbm, opts=opts)
self.tabs.append(t)
else:
self.doCreateIndexes(t)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
if self.opts["bar"]:
pb.closeProgress()
# Fix ctlmst and Stores Markup
tabs = ["ctlmst", "strctl", "strgrp", "strgmu", "strcmu"]
sql = Sql(self.dbm, tabs)
coys = sql.getRec("ctlmst", cols=["ctm_cono"])
for coy in coys:
mods = sql.getRec("ctlmst",
cols=["ctm_modules"],
where=[("ctm_cono", "=", coy[0])],
limit=1)
newm = ""
for x in range(0, len(mods[0]), 2):
if mods[0][x:x + 2] == "PS":
continue
newm += mods[0][x:x + 2]
sql.updRec("ctlmst",
cols=["ctm_modules"],
data=[newm],
where=[("ctm_cono", "=", coy[0])])
sctl = sql.getRec("strctl",
cols=["cts_plevs", "cts_automu"],
where=[("cts_cono", "=", coy[0])],
limit=1)
if sctl:
if sctl[1] == "N":
sql.delRec("strgmu", where=[("smg_cono", "=", coy[0])])
sql.delRec("strcmu", where=[("smc_cono", "=", coy[0])])
else:
if sctl[1] == "Y":
sql.updRec("strctl",
cols=["cts_automu"],
data=["L"],
where=[("cts_cono", "=", coy[0])])
sctl[1] = "L"
grps = sql.getRec("strgrp",
cols=["gpm_group"],
where=[("gpm_cono", "=", coy[0])])
for grp in grps:
for lvl in range(1, sctl[0] + 1):
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", 0)])
gmu = sql.getRec("strgmu",
cols=["smg_markup"],
where=[("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)],
limit=1)
if gmu:
if not gmu[0]:
sql.delRec("strgmu",
where=[
("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)
])
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", gmu[0])])
# Standard Records
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Creating Standard Records\n\nPlease Wait")
self.opts["mf"].updateStatus("Creating Standard Records")
elif self.opts["bar"]:
print("Creating Standard Records .... Please Wait")
sql = Sql(self.dbm, ["ffield", "frelat"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from frelat")
self.doPopulate(sql, "frelat", "_cono")
sql = Sql(self.dbm,
"wagrcv",
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from wagrcv")
self.doPopulate(sql, "wagrcv")
sql = Sql(self.dbm, ["tplmst", "tpldet"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
for tpl in stdtpl:
sql.sqlRec("Delete from tplmst where tpm_tname = '%s'" % tpl)
sql.sqlRec("Delete from tpldet where tpd_tname = '%s'" % tpl)
for dat in datdic["tplmst"]:
sql.insRec("tplmst", data=dat)
for dat in datdic["tpldet"]:
sql.insRec("tpldet", data=dat)
sql = Sql(self.dbm, ["wagtxa", "wagtxr"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
try:
from tartanWork import payeRates, siteRates, uifRates, sdlRates
for t in ("wagtxa", "wagtxr"):
if t == "wagtxa":
for year in payeRates:
sql.delRec(t, where=[("wta_year", "=", int(year))])
data = [int(year)]
for n, d in enumerate(payeRates[year]["allow"]):
data.append(d)
if n == 1 and \
len(payeRates[year]["allow"]) == 3:
data.append(d)
data.append(siteRates[year])
data.extend(uifRates[year])
data.extend(sdlRates[year])
sql.insRec("wagtxa", data=data)
elif t == "wagtxr":
for year in payeRates:
sql.delRec(t, where=[("wtr_year", "=", int(year))])
to = 9999999
for rate in payeRates[year]["rates"]:
dat = [int(year)]
if rate[0]:
dat.append(rate[0] + 1)
dat.append(to)
dat.extend(rate[1:])
to = rate[0]
else:
dat.append(0)
dat.append(to)
dat.extend(rate[1:])
sql.insRec("wagtxr", data=dat)
except:
pass
# Close Splash
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
spl.closeSplash()
self.dbm.commitDbase()
def doPopulate(self, sql, table, cono=None):
if cono:
recs = sql.getRec("ffield",
cols=["ff_tabl", "ff_name"],
where=[("ff_name", "like", "%_cono")])
for rec in recs:
sql.insRec(table, data=[rec[0], rec[1], "ctlmst", "ctm_cono"])
if table in datdic:
for dat in datdic[table]:
sql.insRec(table, data=dat)
def doFixAge(self):
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Checking Age Records\n\nPlease Wait")
self.opts["mf"].updateStatus("Checking Age Records")
elif self.opts["bar"]:
print("Checking Age Records .... Please Wait")
for sss in ("crs", "drs"):
sql = Sql(self.dbm, ["%smst" % sss, "%sage" % sss], prog=__name__)
if sss == "drs":
col = ["dra_cono", "dra_chain", "dra_acno"]
grp = "dra_cono, dra_chain, dra_acno"
else:
col = ["cra_cono", "cra_acno"]
grp = "cra_cono, cra_acno"
recs = sql.getRec("%sage" % sss, cols=col, group=grp)
for rec in recs:
whr = []
for n, c in enumerate(col):
whr.append((c.replace("a_", "m_"), "=", rec[n]))
if not sql.getRec("%smst" % sss, where=whr):
sql.delRec("%sage" % sss, cols=col, data=rec)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
spl.closeSplash()
def doFinal(self):
chg = False
for tab in ("ffield", "ftable"):
chks = self.sql.getRec("ffield", where=[("ff_tabl", "=", tab)])
for num, chk in enumerate(chks):
if chk[1:] != tabdic[tab]["fld"][num]:
chg = True
break
for tab in ("ffield", "ftable"):
chks = self.sql.getRec("ftable", where=[("ft_tabl", "=", tab)])
for num, chk in enumerate(chks):
sch = chk[1:4]
for ft in chk[4:]:
if ft:
sch.append(ft)
if sch != tabdic[tab]["idx"][num]:
chg = True
break
if not chg:
return
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Finishing Off, Please Wait ....")
self.opts["mf"].updateStatus("")
elif self.opts["bar"]:
print("Finishing Off, Please Wait ....")
# Drop Indexes
for tab in ("ffield", "ftable"):
self.doDropIndex(tab)
# Delete Record
self.sql.delRec("ffield",
where=[("ff_tabl", "in", ("ffield", "ftable"))])
self.sql.delRec("ftable",
where=[("ft_tabl", "in", ("ffield", "ftable"))])
# Populate and Create Indexes
for tab in ("ffield", "ftable"):
self.dbm.populateTable(tab)
self.doCreateIndexes(tab)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
spl.closeSplash()
def doCreateIndexes(self, table):
keys = self.sql.getRec("ftable", where=[("ft_tabl", "=", table)])
if not keys:
return
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("Creating New Indexes for %s" % table)
for key in keys:
nam = "%s_key%s" % (table, str(key[2]))
idx = ""
skip = False
for k in key[4:]:
if k:
if self.dbm.dbase == "SQLite":
if self.checkBlob(table, k):
skip = True
break
if idx:
idx = "%s,%s" % (idx, k)
else:
idx = k
if skip:
continue
chk = self.doCheckIndex(table, nam)
if chk != idx:
if chk:
self.sql.sqlRec("Drop index %s" % nam)
if key[3] == "N":
sql = "Create index"
else:
sql = "Create unique index"
sql = "%s %s on %s (%s)" % (sql, nam, table, idx)
self.sql.sqlRec(sql)
def doDropIndex(self, tab):
old = self.sql.getRec("ftable",
where=[("ft_tabl", "=", tab)],
order="ft_seq")
for rec in old:
nam = "%s_key%s" % (tab, str(rec[2]))
if self.doCheckIndex(tab, nam):
self.sql.sqlRec("Drop index %s" % nam)
def doCheckIndex(self, table, name):
if self.dbm.dbase == "PgSQL":
sel = "Select indexdef from pg_indexes where schemaname='public' "\
"and tablename='%s' and indexname='%s'" % (table, name)
else:
sel = "Select sql from sqlite_master where type='index' "\
"and tbl_name='%s' and name='%s'" % (table, name)
dat = self.sql.sqlRec(sel, limit=1)
if dat:
return dat[0].split("(")[1].split(")")[0].replace(" ", "")
def checkBlob(self, table, key):
cols = tabdic[table]["fld"]
for col in cols:
if col[1] == key and col[2] == "TX":
return True
def doCleanDatabase(self):
txt = "Cleaning Database ... Please Wait"
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(txt)
elif self.opts["bar"]:
print(txt)
# Removing unused tables
self.doGetTables()
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
pb = ProgressBar(self.opts["mf"].body,
mxs=(len(self.old) + len(self.tabs)),
typ=txt)
for n1, t in enumerate(self.old):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1)
if t not in self.tabs:
self.dbm.dropTable(t, True)
self.dbm.commitDbase()
# Creating new tables and/or indexes
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
for n2, t in enumerate(self.tabs):
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus(t)
if self.opts["bar"]:
pb.displayProgress(n1 + n2)
if not self.dbm.checkTable(t):
opts = copyList(self.topts)
opts.append(("-t", t))
DBCreate(dbm=self.dbm, opts=opts)
self.tabs.append(t)
else:
self.doCreateIndexes(t)
self.dbm.commitDbase()
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
if self.opts["bar"]:
pb.closeProgress()
# Fix ctlmst and Stores Markup
tabs = ["ctlmst", "strctl", "strgrp", "strgmu", "strcmu"]
sql = Sql(self.dbm, tabs)
coys = sql.getRec("ctlmst", cols=["ctm_cono"])
for coy in coys:
mods = sql.getRec("ctlmst",
cols=["ctm_modules"],
where=[("ctm_cono", "=", coy[0])],
limit=1)
newm = ""
for x in range(0, len(mods[0]), 2):
if mods[0][x:x + 2] == "PS":
continue
newm += mods[0][x:x + 2]
sql.updRec("ctlmst",
cols=["ctm_modules"],
data=[newm],
where=[("ctm_cono", "=", coy[0])])
sctl = sql.getRec("strctl",
cols=["cts_plevs", "cts_automu"],
where=[("cts_cono", "=", coy[0])],
limit=1)
if sctl:
if sctl[1] == "N":
sql.delRec("strgmu", where=[("smg_cono", "=", coy[0])])
sql.delRec("strcmu", where=[("smc_cono", "=", coy[0])])
else:
if sctl[1] == "Y":
sql.updRec("strctl",
cols=["cts_automu"],
data=["L"],
where=[("cts_cono", "=", coy[0])])
sctl[1] = "L"
grps = sql.getRec("strgrp",
cols=["gpm_group"],
where=[("gpm_cono", "=", coy[0])])
for grp in grps:
for lvl in range(1, sctl[0] + 1):
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", 0)])
gmu = sql.getRec("strgmu",
cols=["smg_markup"],
where=[("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)],
limit=1)
if gmu:
if not gmu[0]:
sql.delRec("strgmu",
where=[
("smg_cono", "=", coy[0]),
("smg_group", "=", grp[0]),
("smg_level", "=", lvl)
])
sql.delRec("strcmu",
where=[("smc_cono", "=", coy[0]),
("smc_group", "=", grp[0]),
("smc_level", "=", lvl),
("smc_markup", "=", gmu[0])])
# Standard Records
if self.opts["mf"] and self.opts["mf"].window:
spl = SplashScreen(self.opts["mf"].body,
"Creating Standard Records\n\nPlease Wait")
self.opts["mf"].updateStatus("Creating Standard Records")
elif self.opts["bar"]:
print("Creating Standard Records .... Please Wait")
sql = Sql(self.dbm, ["ffield", "frelat"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from frelat")
self.doPopulate(sql, "frelat", "_cono")
sql = Sql(self.dbm,
"wagrcv",
error=False,
prog=self.__class__.__name__)
if not sql.error:
sql.sqlRec("Delete from wagrcv")
self.doPopulate(sql, "wagrcv")
sql = Sql(self.dbm, ["tplmst", "tpldet"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
for tpl in stdtpl:
sql.sqlRec("Delete from tplmst where tpm_tname = '%s'" % tpl)
sql.sqlRec("Delete from tpldet where tpd_tname = '%s'" % tpl)
for dat in datdic["tplmst"]:
sql.insRec("tplmst", data=dat)
for dat in datdic["tpldet"]:
sql.insRec("tpldet", data=dat)
sql = Sql(self.dbm, ["wagtxa", "wagtxr"],
error=False,
prog=self.__class__.__name__)
if not sql.error:
try:
from tartanWork import payeRates, siteRates, uifRates, sdlRates
for t in ("wagtxa", "wagtxr"):
if t == "wagtxa":
for year in payeRates:
sql.delRec(t, where=[("wta_year", "=", int(year))])
data = [int(year)]
for n, d in enumerate(payeRates[year]["allow"]):
data.append(d)
if n == 1 and \
len(payeRates[year]["allow"]) == 3:
data.append(d)
data.append(siteRates[year])
data.extend(uifRates[year])
data.extend(sdlRates[year])
sql.insRec("wagtxa", data=data)
elif t == "wagtxr":
for year in payeRates:
sql.delRec(t, where=[("wtr_year", "=", int(year))])
to = 9999999
for rate in payeRates[year]["rates"]:
dat = [int(year)]
if rate[0]:
dat.append(rate[0] + 1)
dat.append(to)
dat.extend(rate[1:])
to = rate[0]
else:
dat.append(0)
dat.append(to)
dat.extend(rate[1:])
sql.insRec("wagtxr", data=dat)
except:
pass
# Close Splash
if self.opts["mf"] and self.opts["mf"].window:
self.opts["mf"].updateStatus("")
spl.closeSplash()
self.dbm.commitDbase()
def doNewTable(self):
sql = Sql(self.dbm,
self.table,
error=False,
prog=self.__class__.__name__)
if sql.error:
data = None
else:
# Read Existing Table
cols = getattr(sql, "%s_col" % self.table)
order = ""
if self.table in self.tabord:
if self.table == "slsiv3" and "si3_seqnum" in cols:
ords = ["si3_seqnum"]
else:
ords = self.tabord[self.table]
for fld in ords:
if fld in cols:
if not order:
order = fld
else:
order = "%s, %s" % (order, fld)
data = sql.getRec(tables=self.table, order=order)
# Create New Table
opts = copyList(self.topts)
opts.append(("-t", self.table))
DBCreate(dbm=self.dbm, opts=opts)
ndata = []
if data:
sql = Sql(self.dbm,
self.table,
error=False,
prog=self.__class__.__name__)
# Create Dictionaries
olddic = {}
for f in self.oldfld:
olddic[f[2]] = f[1:]
newdic = {}
for f in self.newfld:
newdic[f[2]] = f[1:]
# Populate New Table
mx1 = len(data)
if self.opts["bar"] and self.opts["mf"] and self.opts["mf"].window:
pbar = "w"
p2 = ProgressBar(self.opts["mf"].body,
inn=self.p1,
typ="Converting Table %s" % self.table,
mxs=mx1)
else:
pbar = False
p2 = None
for num, old in enumerate(data):
# Convert old data to new format
if pbar:
p2.displayProgress(num)
dat = []
for csf in self.newfld:
nam = csf[2]
typ = csf[3]
if typ == "US":
continue
siz = csf[4]
if nam in olddic and typ == olddic[nam][2]:
new = old[olddic[nam][0]]
elif nam in olddic:
try:
ccd = CCD(old[olddic[nam][0]], typ, siz)
if ccd.err:
raise Exception
new = ccd.work
except:
if typ[1].lower() in ("a", "x"):
new = ""
else:
new = 0
elif self.table == "cshana" and nam == "can_gflag":
new = "N"
elif self.table == "ctlrep" and nam == "rep_cono":
new = old[olddic["rpm_cono"][0]]
elif self.table == "ctlrep" and nam == "rep_code":
new = old[olddic["rpm_rep"][0]]
elif self.table == "ctlrep" and nam == "rep_name":
new = old[olddic["rpm_name"][0]]
elif self.table == "bksmst" and nam == "bmf_cono":
new = 1
elif self.table == "bksown" and nam == "bof_cono":
new = 1
elif self.table == "bwlctl" and nam == "ctb_mscat":
new = 0
elif self.table == "bwlctl" and nam == "ctb_mixed":
new = "N"
elif self.table == "bwlgme" and nam == "bcg_sfor":
new = old[olddic["bcg_shots_for"][0]]
elif self.table == "bwlgme" and nam == "bcg_sagt":
new = old[olddic["bcg_shots_agt"][0]]
elif self.table == "bwlgme" and nam == "bcg_a_sfor":
new = old[olddic["bcg_a_shots_for"][0]]
elif self.table == "bwlgme" and nam == "bcg_a_sagt":
new = old[olddic["bcg_a_shots_agt"][0]]
elif self.table == "crsmst" and nam == "crm_stat":
new = "N"
elif self.table == "drsmst" and nam == "drm_stat":
new = "N"
elif self.table == "slsiv2" and nam == "si2_line":
new = old[olddic["si2_seq"][0]]
elif self.table == "slsiv3" and nam == "si3_line":
new = old[olddic["si3_seq"][0]]
elif self.table == "slsiv3" and nam == "si3_seq":
new = 0
elif typ[1].lower() in ("a", "x"):
new = ""
else:
new = 0
dat.append(new)
ndata.append(dat)
if ndata:
# Populate new table
if pbar:
txt = "Populating Table %s" % self.table
p2.mxp = 1
p2.mxs = len(ndata)
p2.txtlab.configure(text=txt)
p2.displayProgress()
clm = ""
fmt = ""
for csf in self.newfld:
nam = csf[2]
typ = csf[3]
if typ == "US":
continue
if not clm:
clm = nam
fmt = self.dbm.dbf
else:
clm += ",%s" % nam
fmt = "%s,%s" % (fmt, self.dbm.dbf)
nlim = int(self.lmt / len(self.newfld))
for x in range(0, len(ndata), nlim):
if pbar:
p2.displayProgress(x)
y = x + nlim
tft = ",".join("(%s)" % fmt for _ in ndata[x:y])
dat = list(chain.from_iterable(ndata[x:y]))
cmd = "Insert into %s (%s) values %s" % (self.table, clm,
tft)
sql.sqlRec((cmd, dat))
#self.dbm.commitDbase()
if pbar:
p2.closeProgress()
# Update Report Writer for missing columns
cols = []
for csf in self.oldfld:
if csf[2] not in newdic:
cols.append(csf[2])
if cols:
tabs = (("rptcol", "rpc_rnam",
"rpc_detail"), ("rptexc", "rpx_rnam", "rpx_exc"),
("rptjon", "rpj_rnam",
"rpj_col"), ("rptord", "rpo_rnam", "rpo_column"))
for tab in tabs:
sql = Sql(self.dbm,
tab[0],
error=False,
prog=self.__class__.__name__)
if sql.error:
continue
dat = sql.getRec(tables=tab[0], cols=[tab[1], tab[2]])
for d in dat:
for col in cols:
if d.count(col):
sql.delRec(tab[0],
where=[(tab[1], "=", d[0]),
(tab[2], "=", d[1])])
self.dbm.commitDbase()
