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()