UpK4vb9N

1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3"""
4author: jeff maxey
5email: jeffrey.maxey@transamerica.com
6purpose: MoSesApplication
7"""
8from __future__ import print_function
9import os
10import sys
11import glob
12import csv
13import sqlite3
14import pyodbc
15import pandas as pd
16from dbfread import DBF
17import bs4
18from bs4 import BeautifulSoup as Soup
19pd.set_option('display.expand_frame_repr', False)
20
21
22def encode_decode(x, enc='cp850', dec='utf-8'):
23    """
24    DBF returns a unicode string encoded as args.input_encoding.
25    We convert that back into bytes and then decode as args.output_encoding.
26    """
27    if not isinstance(x, str):
28        # DBF converts columns into non-str like int, float
29        x = str(x)
30
31    x = x.encode(enc).decode(dec)
32    return x
33
34
35def convertDBFtoCSV(input_file_path, outdir):
36    print("Converting %s to csv" % input_file_path)
37    dbf_basename = os.path.basename(input_file_path)
38    csv_basename = dbf_basename[:-4] + ".csv"
39    output_file_path = os.path.join(outdir, csv_basename)
40
41    with open(output_file_path, 'w') as csvfile:
42        input_reader = DBF(input_file_path, ignore_missing_memofile=True)
43        output_writer = csv.DictWriter(csvfile, delimiter=',', lineterminator='\n',
44                                       fieldnames=[x for x in input_reader.field_names])
45        output_writer.writeheader()
46
47        for record in input_reader:
48            row = {k: v for k, v in record.items()}
49            output_writer.writerow(row)
50
51
52def BatchCSVtoSQLite(db, csvDir):
53    csv.field_size_limit(sys.maxsize)
54    cnxn = sqlite3.connect(os.path.join(csvDir, "MoSesDatabase.db"))
55    cnxn.text_factory = str
56    cursor = cnxn.cursor()
57
58    for csvfile in glob.glob(os.path.join(csvDir, "*.csv")):
59        tablename = os.path.splitext(os.path.basename(csvfile))[0]
60
61        with open(csvfile, "r") as f:
62            reader = csv.reader(f)
63
64            header = True
65            for row in reader:
66                if header:
67                    header = False
68                    sqlCmd = "DROP TABLE IF EXISTS %s" % tablename
69                    cursor.execute(sqlCmd)
70                    sqlCmd = "CREATE TABLE %s (%s)" % (tablename, ", ".join(["%s text" % column for column in row]))
71                    cursor.execute(sqlCmd)
72
73                    for column in row:
74                        if column.lower().endswith("_id"):
75                            index = "%s__%s" % (tablename, column)
76                            sqlCmd = "CREATE INDEX %s on %s (%s)" % (index, tablename, column)
77                            cursor.execute(sqlCmd)
78
79                    sqlInsert = "INSERT INTO %s VALUES (%s)" % (tablename, ", ".join(["?" for column in row]))
80                    rowLength = len(row)
81                else:
82                    # skip lines that don'tursor have the right number of columns
83                    if len(row) == rowLength:
84                        cursor.execute(sqlInsert, row)
85            cnxn.commit()
86    cursor.close()
87    cnxn.close()
88
89
90def RequiredDBFs():
91    list_RequiredDBFs = ["task", "prjbat", "othbat", "taskbat", "extbat", "val", "prodval", "par", "prodpar", "prodasx"]
92    return list_RequiredDBFs
93
94
95class MoSesApplication(object):
96    def __init__(self, Path):
97        self.__Path = Path
98
99    def trim(self, df):
100        df_obj = df.select_dtypes(['object'])
101        df[df_obj.columns] = df_obj.apply(lambda x: x.str.strip())
102        return df
103
104    def get_UserStamp(self):
105        if self.__UserStamp == "":
106            return "APPMaster"
107        else:
108            return self.__UserStamp
109
110    def set_UserStamp(self, value):
111        self.__UserStamp = value
112
113    UserStamp = property(fget=get_UserStamp, fset=set_UserStamp)
114
115    def get_ProgramFolder(self):
116        return self.__ProgramFolder
117
118    def set_ProgramFolder(self, value):
119        self.__ProgramFolder = value
120
121    ProgramFolder = property(fget=get_ProgramFolder, fset=set_ProgramFolder)
122
123    def get_Path(self):
124        if self.__Path != "":
125            return self.__Path
126        if not self.__Directory is None:
127            self.__Path = self.__Directory.FullName
128        return self.__Path
129
130    Path = property(fget=get_Path)
131
132    def get_Directory(self):
133        if hasattr(self, '_MoSesApplication__Directory'):
134            return self.__Directory
135        if self.__Path != "" and os.path.exists(self.__Path):
136            self.__Directory = os.listdir(self.__Path)
137        return self.__Directory
138
139    Directory = property(fget=get_Directory)
140
141    def get_Conn(self):
142        if hasattr(self, '_MoSesApplication__Conn'):
143            return self.__Conn
144        if self.Path == "":
145            return None
146        try:
147            ConnString = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB="
148            ConnString += self.__Path
149            ConnString += ";Exclusive=Yes; Collate=Machine;NULL=NO;DELETED=YES;BACKGROUNDFETCH=NO;"
150            self.__Conn = pyodbc.connect(ConnString)
151            return self.__Conn
152        except Exception as ex:
153            return None
154
155    Conn = property(fget=get_Conn)
156
157    def get_ProgConn(self):
158        if hasattr(self, '_MoSesApplication__ProgConn'):
159            return self.__ProgConn
160        if self.ProgramFolder == "" or not os.path.exists(self.ProgramFolder):
161            return None
162        try:
163            ConnString = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB="
164            ConnString += self.ProgramFolder
165            ConnString += ";Exclusive=Yes; Collate=Machine;NULL=NO;DELETED=YES;BACKGROUNDFETCH=NO;"
166            self.__ProgConn = pyodbc.connect(ConnString)
167            return self.__ProgConn
168        except Exception as ex:
169            return None
170
171    ProgConn = property(fget=get_ProgConn)
172
173    def get_UserGroups(self):
174        if hasattr(self, '_MoSesApplication__UserGroups'):
175            return self.__UserGroups
176        try:
177            self.__UserGroups = list()
178            dt = self.SelectQuery("SELECT ugrp_id, ugrp_desc FROM ugp ORDER BY ugrp_desc", True)
179            KnownIDs = list()
180            enumerator = dt.Rows.GetEnumerator()
181            while enumerator.MoveNext():
182                Row = enumerator.Current
183                self.__UserGroups.append(Row)
184                KnownIDs.Add(self.Row("ugrp_id"))
185            dt = self.SelectQuery("SELECT ugrp_id, ugrp_id AS ugrp_desc FROM task GROUP BY ugrp_id ORDER BY ugrp_id")
186            enumerator = dt.Rows.GetEnumerator()
187            while enumerator.MoveNext():
188                Row = enumerator.Current
189                if KnownIDs.Contains(self.Row("ugrp_id")):
190                    continue
191                KnownIDs.Add("ugrp_id")
192                self.__UserGroups.Add(Row)
193            self.__UserGroups.Sort()
194        except:
195            self.__UserGroups = None
196        finally:
197            return self.__UserGroups
198
199    def Table(self, TableName):
200        if not os.path.exists(os.path.join(self.__Path, TableName + ".dbf")):
201            return None
202        return self.SelectQuery("SELECT * FROM " + TableName)
203
204    def SelectQuery(self, QueryText):
205        cnxn = self.Conn
206        cursor = cnxn.cursor()
207        cursor.execute(QueryText)
208        rows = cursor.fetchall()
209        return self.trim(pd.read_sql_query(QueryText, cnxn))
210
211    def ProductFeatureSets(self):
212        if hasattr(self, "_MoSesApplication__ProductFeatureSets"):
213            return self.__ProductFeatureSets
214        if self.Conn is None:
215            return None
216        PFSdt = self.SelectQuery("SELECT prodparid, pardesc FROM prodpar ORDER BY pardesc")
217        self.__ProductFeatureSets = PFSdt
218        return PFSdt
219
220    def AssumptionSets(self):
221        if hasattr(self, "_MoSesApplication__AssumptionSets"):
222            return self.__AssumptionSets
223        if self.Conn is None:
224            return None
225        ASdt = self.SelectQuery("SELECT prodasx.prodasxid, prodasx.parid, "
226                                "prodasx.prodparid, par.pardesc, prodasx.ugrp_id "
227                                "FROM prodasx LEFT OUTER JOIN par ON "
228                                "prodasx.parid = par.parid")
229        self.__AssumptionSets = ASdt
230        return ASdt
231
232    def Variables(self):
233        if hasattr(self, "_MoSesApplication__Variables"):
234            return self.__Variables
235        if self.Conn is None:
236            return None
237        dt = self.SelectQuery("Select * From var")
238        if dt is None:
239            return None
240        else:
241            self.__Variables = dt
242            return self.__Variables
243
244    def VariableSources(self):
245        if hasattr(self, "_MoSesApplication__VariableSources"):
246            return self.__VariableSources
247        if self.Conn is None:
248            return None
249        dt = self.SelectQuery("SELECT var.varid, varsrc.ugrp_id, varsrc.valtype "
250                              "FROM varsrc JOIN var ON varsrc.product = var.product AND varsrc.v_name = var.v_name "
251                              "ORDER BY var.varid")
252        if dt is None:
253            return None
254        else:
255            self.__VariableSources = dt
256            return self.__VariableSources
257
258    def get_ID(self):
259        if hasattr(self, "_MoSesApplication__ID"):
260            return self.__ID
261        else:
262            dt = self.Table(TableName="prodasx").to_dict(orient="records")[0]
263            self.__ID = dt
264            return self.__ID
265
266    ID = property(fget=get_ID)
267
268    def get_Description(self, dt, row):
269        self.Description = dt.loc[row, "pardesc"]
270        return self.Description
271
272    def Wildcards(self, WildcardSet):
273        if self.Conn is None:
274            return None
275        wcdt = self.SelectQuery(QueryText="SELECT pathkey, pathdir FROM pthname WHERE wildcrdset = '"
276                                          + WildcardSet + "'")
277        if wcdt is None:
278            return None
279        wcdt['pathkey'] = wcdt['pathkey'].str.strip()
280        wcdt['pathdir'] = wcdt['pathdir'].str.strip()
281        if wcdt is None:
282            return None
283        self.__Wildcards = wcdt
284        return wcdt
285
286    def WildcardSets(self):
287        if hasattr(self, "_MoSesApplication__WildcardSets"):
288            return self.__WildcardSets
289        if self.Conn is None:
290            return None
291        wcsdt = self.SelectQuery(QueryText="SELECT wildcrdset FROM pthname GROUP BY wildcrdset")
292        if wcsdt is None:
293            return None
294        self.__WildcardSets = wcsdt['wildcrdset'].values.tolist()
295        return self.__WildcardSets
296
297    def SubModels(self):
298        if hasattr(self, "_MoSesApplication__SubModels"):
299            return self.__SubModels
300        if self.Conn is None:
301            return None
302
303        # smdt = self.SelectQuery("SELECT modelid, sm_name, path_nm, modeltype FROM modelset ORDER BY sm_name")
304        smdt = self.SelectQuery("SELECT modelset.modelid, modelset.product, modelset.unique_nm, " +
305                                "modelset.modeltype, chgd.mdlid " +
306                                "FROM modelset LEFT OUTER JOIN chgd " +
307                                "ON modelset.product = chgd.product AND " +
308                                "modelset.purpose = chgd.purpose " +
309                                "ORDER BY modelset.product")
310        if smdt is None:
311            return None
312        self.__SubModels = smdt
313        return self.__SubModels
314
315    def Columns(self):
316        if hasattr(self, "_MoSesApplication__Columns"):
317            return self.__Columns
318        if self.Conn is None:
319            return None
320        cshdt = self.SelectQuery(QueryText="SELECT c_name, c_head, product, sl_window, cshid "
321                                           "FROM csh ORDER BY product, c_name")
322        if cshdt is None:
323            return None
324        self.__Columns = cshdt
325        return self.__Columns
326
327    def Scalars(self):
328        if hasattr(self, "_MoSesApplication__Scalars"):
329            return self.__Scalars
330        if self.Conn is None:
331            return None
332        calcvardt = self.SelectQuery("SELECT product, v_name, v_descrip, calcvarid "
333                                     "FROM calcvar "
334                                     "ORDER BY product, v_name")
335        if calcvardt is None:
336            return None
337        self.__Scalars = calcvardt
338        return self.__Scalars
339
340    def TemporaryTables(self):
341        if hasattr(self, "_MoSesApplication__TemporaryTables"):
342            return self.__TemporaryTables
343        if self.Conn is None:
344            return None
345        calcvardt = self.SelectQuery("SELECT product, c_name, form_name, c_descrip, mtbid "
346                                     "FROM mtb "
347                                     "ORDER BY product, c_name")
348        if calcvardt is None:
349            return None
350        self.__TemporaryTables = calcvardt
351        return self.__TemporaryTables
352
353    def ProductFeatureSets(self):
354        if hasattr(self, "_MoSesApplication__ProductFeatureSets"):
355            return self.__ProductFeatureSets
356        if self.Conn is None:
357            return None
358        PFSdt = self.SelectQuery(QueryText="SELECT prodparid, pardesc FROM prodpar ORDER BY pardesc")
359        if PFSdt is None:
360            return None
361        self.__ProductFeatureSets = PFSdt
362        return self.__ProductFeatureSets
363
364    def AssumptionSets(self):
365        if hasattr(self, "_MoSesApplication__AssumptionSets"):
366            return self.__AssumptionSets
367        if self.Conn is None:
368            return None
369        ASdt = self.SelectQuery("SELECT prodasx.prodasxid, prodasx.parid, prodasx.prodparid, "
370                                "par.pardesc, prodasx.ugrp_id "
371                                "FROM prodasx LEFT OUTER JOIN par ON prodasx.parid = par.parid")
372        if ASdt is None:
373            return None
374        self.__AssumptionSets = ASdt
375        return self.__AssumptionSets
376
377    def Formulas(self):
378        if hasattr(self, "_MoSesApplication__Formulas"):
379            return self.__Formulas
380        if self.Conn is None:
381            return None
382        dt = self.SelectQuery("SELECT fproduct, form_name, fml_desc, formula, dstamp, tstamp, ustamp, "
383                              "extfunc, cat_id, fmlid, tokens FROM fml ORDER BY fproduct, form_name")
384        if dt is None:
385            return None
386        self.__Formulas = dt
387        return self.__Formulas
388
389    def MoSesVariableDataSource(self):
390        SrcDict = {"AssumptionSet": 1,
391                   "IndexedAssumption": 2,
392                   "Data": 3,
393                   "DefaultValue": 4,
394                   "Table": 5,
395                   "TableVariable": 6,
396                   "ProductFeatureSet": 7}
397        self.MoSesVariableDataSource = SrcDict
398        return self.MoSesVariableDataSource
399
400
401def WildcardPaths(app):
402    dtPthDesc = app.Table("pthdesc")
403    dtPthname = app.Table("pthname")
404    dtProdPar = app.Table("prodpar")
405    dt = pd.merge(left=dtPthname, right=dtPthDesc, on="pathkey", how="left", suffixes=('', '_y'))
406    dt = pd.merge(left=dt, right=dtProdPar, left_on="wildcrdset", right_on="pardesc", how="inner", suffixes=('', '_y'))
407    cols = [c for c in dt.columns if not c.endswith("_y")]
408    dt = dt[cols]
409    dt['wildcardkey'] = "<*" + dt['pathkey'].str.strip() + "*>"
410    cols = ['pathkey', 'pathdir', 'wildcrdset', 'pathdesc', 'pardesc', 'prodparid', 'wildcardkey']
411    dt = dt[cols]
412    return dt
413
414
415def ReplaceWildcards(xmlstr, wcdt):
416    xmlstr = xmlstr.replace("<", "<")
417    xmlstr = xmlstr.replace(">", ">")
418
419    for wc in range(len(wcdt)):
420        pathkey = wcdt.loc[wc, "pathkey"].strip()
421        findString = r"<*" + pathkey + r"*>"
422        replaceString = wcdt.loc[wc, "pathdir"].strip()
423
424        if replaceString != "N/A":
425            xmlstr = xmlstr.replace(findString, replaceString)
426            # xmlstr = xmlstr.replace("<![CDATA[", "")
427            # xmlstr = xmlstr.replace("]]>", "")
428    return xmlstr
429
430
431def Filter(df, column, value):
432    dfFiltered = df.loc[df[column] == value]
433    dfFiltered = dfFiltered.reset_index(drop=True)
434    return dfFiltered
435
436
437def MoSesVariableDataSource(app):
438    src = app.VariableDataSource()
439    return src
440
441
442def MoSesProductFeatureSet(app):
443    # // create a PFS data table of required fields by executing a sql query on moses application on PFS.ID
444    dt = app.SelectQuery("SELECT v_value, mdlid, varid, prodparid "
445                         "FROM prodval where prodparid = " + str(app.ID.get("prodparid")))
446
447    # // iterate over rows of data table
448    for RowIndex in range(len(dt)):
449
450        # // select row from data table
451        RowData = dt.loc[RowIndex, :]
452
453        # // select Xml string from "v_value" field and objectify using BeautifulSoup
454        XmlValue = RowData["v_value"]
455        XmlSoup = Soup(XmlValue, "html.parser")
456
457        # // split XmlSoup to list by row tag and create container for collection of node data
458        NodeList = XmlSoup.findAll("row")
459        NodeCollector = []
460
461        # // increment over list of nodes
462        j = 0
463        while j < len(NodeList):
464
465            # // create dictionary to store data from node
466            d = {}
467
468            # // select node from node list and iterate of children of node
469            Node = NodeList[j]
470            for Child in Node.findChildren():
471
472                # // collect model number
473                if Child.name == "model":
474                    d['model'] = Child.text
475
476                # // collect product code
477                if Child.name == "prodcode":
478                    d['prodcode'] = Child.text.upper()
479
480                # // collect variable name
481                if Child.name == "variable":
482                    d['v_name'] = Child.text
483
484                # // check if tag.name equals "v_value"
485                if Child.name == "v_value":
486                    # determine type(v_value) so different procedures can be called
487                    # to parse Values, InternalTables and ExternalTables
488
489                    # method to check for instance of "<![CDATA[]]>" which indicates/stores external table path
490                    # this requires the use of bs4.BeautifulSoup(doc, 'html.parser') as by default
491                    # bs4.BeautifulSoup(doc, 'lxml') strips CDATA sections from the tree
492                    # and replaces them by their plain text content.
493                    try:
494                        Value = Child.find(text=lambda tag: isinstance(tag, bs4.CData)).string.strip()
495                    except AttributeError:
496                        Value = Child.next_element
497
498                    d['v_value'] = Value
499                    Value = str(Value).strip()
500
501                    if Value[:6].lower() == "<table":
502                        d['ValueType'] = "InternalTable"
503                    elif Value.endswith(".msv"):
504                        d['ValueType'] = "ExternalTable"
505                    elif "<" not in Value:
506                        d['ValueType'] = "Value"
507                    else:
508                        d['ValueType'] = "InternalTable"
509
510                    # // assign key,values for remaining columns in row of data frame
511                    d['mdlid'] = RowData['mdlid']
512                    d['varid'] = RowData['varid']
513                    d['proparid'] = RowData['prodparid']
514
515            # // append dictionary to node container and increment node list
516            NodeCollector.append(d)
517            j += 1
518
519        if RowIndex == 0:
520            # // initialize a tree container to store all node containers
521            TreeCollector = pd.DataFrame(NodeCollector)
522        else:
523            # // concatenate node container to previously existing tree container
524            frames = [TreeCollector, pd.DataFrame(NodeCollector)]
525            TreeCollector = pd.concat(frames)
526
527        print(pd.DataFrame(NodeCollector))
528
529    # // reset index on tree collection and return object
530    TreeCollector = TreeCollector.reset_index(drop=True)
531
532    return TreeCollector
533
534
535def ReplaceWildcards(xmlstr, wcdt):
536    xmlstr = xmlstr.replace("<", "<")
537    xmlstr = xmlstr.replace(">", ">")
538
539    if xmlstr[:2] == "<*":
540        for wc in range(len(wcdt)):
541            pathkey = wcdt.loc[wc, "pathkey"].strip()
542            findString = r"<*" + pathkey + r"*>"
543            replaceString = wcdt.loc[wc, "pathdir"].strip()
544
545            if replaceString != "N/A":
546                xmlstr = xmlstr.replace(findString, replaceString)
547    return xmlstr
548
549def Filter(df, column, value):
550    dfFiltered = df.loc[df[column] == value]
551    dfFiltered = dfFiltered.reset_index(drop=True)
552    return dfFiltered
553
554
555def main():
556    path_moses_app = r"U:\Users\Sam\Modernization\Data\moses\moses_apps\20180102_QE_2017Q4_FL_IUL_001"
557    path_project = r"A:\shared_by_initiative\TeamC_Equity\initiative_030\User_Folders\Jeff\MoSes2Axis"
558
559    App = MoSesApplication(Path=path_moses_app)
560
561    filepaths = [os.path.join(path_moses_app, f) for f
562                 in os.listdir(path_moses_app)
563                 if f.lower().endswith(".dbf")]
564
565    for filepath in filepaths:
566        convertDBFtoCSV(input_file_path=filepath, outdir=os.path.join(path_project, "database"))
567
568    dbName = os.path.join(path_project, "database\MoSesData.db")
569    csvDirectory = os.path.join(path_project, "database")
570    BatchCSVtoSQLite(db=dbName, csvDir=csvDirectory)
571
572    Wildcards = App.Table("pthname")
573    Wildcards["pathtag"] = "<*" + Wildcards['pathkey'] + "*>"
574    Wildcards = Wildcards.loc[Wildcards['pathkey'] != ""]
575    Wildcards = Wildcards.loc[Wildcards['wildcrdset'] != "DEFAULT"]
576    Wildcards = Wildcards.reset_index(drop=True)
577    Wildcards.to_csv(os.path.join(path_project, "docs\Wildcards.csv"), header=True, index=False)
578
579    PFS = MoSesProductFeatureSet(App).applymap(str)
580    PFS = pd.merge(left=PFS, right=App.SubModels()[['modelid', 'product']].applymap(str),
581                   left_on=['model'], right_on=['modelid'], how='left')
582    PFS = pd.merge(left=PFS, right=App.Variables()[['v_name', 'v_descrip']].applymap(str).drop_duplicates(), on='v_name', how='left')
583    PFS.to_csv(os.path.join(path_project, "docs\ProductFeatureSet.csv"), header=True, index=False)
584
585    Values = PFS.loc[PFS['ValueType'] == "Value"]
586    Values = Values.reset_index(drop=True)
587    Values.to_csv(os.path.join(path_project, "docs\Values.csv"), header=True, index=False)
588
589    InternalTables = PFS.loc[PFS['ValueType'] == "InternalTable"]
590    InternalTables = InternalTables.reset_index(drop=True)
591    InternalTables.to_csv(os.path.join(path_project, "docs\InternalTables.csv"), header=True, index=False)
592
593    ExternalTables = PFS.loc[PFS['ValueType'] == "ExternalTable"]
594    ExternalTables = ExternalTables.reset_index(drop=True)
595    ExternalTables.to_csv(os.path.join(path_project, "docs\ExternalTables.csv"), header=True, index=False)
596    App.Conn.close()
597
598
599if __name__ == '__main__':
600    main()