D2QAwUV8

1# coding: Cp1252
2import os
3import sys
4import glob
5import arcpy
6import sqlite3
7import numpy as np
8import cx_Oracle as cxo
9from datetime import datetime
10from time import mktime
11
12os.chdir(r'F:\GEO\DATA\GeoPGV\P2\Ajourføring_kontroldata')
13
14def ora_prod():
15    """ define TNS to access VALIDERING schema at PROD"""
16    dsn_tns = cxo.makedsn('blahblah', 1521,
17                          service_name='blahblah')
18    connection = cxo.connect('blahblah', 'blahblah', dsn_tns)
19    return connection
20
21def ora_test_vali():
22    """ define TNS to access VALIDERING schema at TEST"""
23    dsn_tns = cxo.makedsn('blahblah', 1521,
24                          service_name='blahblah')
25    connection = cxo.connect('blahblah', 'blahblah', dsn_tns)
26    return connection 
27
28def ora_test_udpe():
29    """ define TNS to access UDPEGNING schema at TEST"""
30    dsn_tns = cxo.makedsn('blahblah', 1521,
31                          service_name='blahblah')
32    connection = cxo.connect('blahblah', 'blahblah', dsn_tns)
33    return connection
34
35def sqlite_connection(db_file):
36    """ Create SQLite DB if it doesn't already exist."""
37    try:
38        conn = sqlite3.connect(db_file)
39        c = conn.cursor()
40        c.execute("PRAGMA encoding='Cp1252';")
41        gen_tbl = """CREATE TABLE IF NOT EXISTS processed(
42        id integer PRIMARY KEY AUTOINCREMENT,
43        name VARCHAR(400) NOT NULL,
44        proc_date text NOT NULL);"""
45        c.execute(gen_tbl)
46    except sqlite3.Error as e:
47        print(e)
48    finally:
49        conn.close()
50                                 
51def shp_move(i_file):
52    cwd = os.path.abspath(os.curdir)
53    ct = datetime.now()
54    shp_date_lst = [i_file,ct.strftime('%d-%m-%Y %H:%M:%S')]
55    timestamp_now = str(mktime(ct.timetuple()))[:-2]
56    for f_obj in glob.glob(i_file[:-3]+'*'):
57        n_obj = f_obj[:-4]+'_'+timestamp_now+f_obj[-4:]
58        os.rename(f_obj, os.path.join(cwd,'Old',os.path.basename(n_obj)))
59        
60    if not os.path.exists('behandlet_old.sqlite'):
61        sqlite_connection('behandlet_old.sqlite')
62    sql_con = sqlite3.connect('behandlet_old.sqlite')
63    sql_con.text_factory = str
64    new_line = "INSERT INTO processed (name, proc_date) VALUES (?, ?)"
65    sql_con.execute(new_line, shp_date_lst)
66    sql_con.commit()
67    
68
69def loc_type(i_file):
70    """ Figure out if data are located at 'prod' or 'test' Oracle server"""
71    test_lst = ['smeltevandssoe','fiskedam','ikkefiskedam','bassin','plantebaelte','skovnedlagt',
72                'sportype','jernbaneslet','jernbaneprivat','spaending','hoejspaendingslet',
73                'vejklasseberegnet','midtebreddekrone','midtebreddevaad','vandloebnedlagt',
74                'roerlagt','vindmoelleposition','vindmoellenedlagt','vindmoellenomatchbbr',
75                'togstationnedlagt','framotorvej','tilmotorvej','motortrafikvej_attribut',
76                'Vejklasseanalyse']
77    t_layer = arcpy.MakeFeatureLayer_management(i_file, 't_lay')
78    o_count = 0
79    with arcpy.da.SearchCursor(t_layer, ['VALIDERING']) as s_cur:
80        for row in s_cur:
81            while o_count == 0:
82                if row[0] in test_lst:
83                    loc = 'TEST'
84                else:
85                    loc = 'PROD'
86                o_count =+ 1
87            break
88    arcpy.Delete_management('t_lay')
89    return loc
90
91lst_shp = [os.path.abspath(f_obj) for f_obj in glob.glob(os.path.join('Behandlet','*.shp'))]
92# check if any shapefile was found and if process the single features
93if len(lst_shp) > 0:
94    for shp in lst_shp:
95        print(shp)
96        """ check type of adjustment: validering or udpegning
97            check geometry type: Point, Polyline, Polygon
98            get count number of features
99            check where to find: PROD or TEST"""
100        atype = ()
101        
102        if len(arcpy.ListFields(shp, 'VALIDERING')) == 1:
103            loc = loc_type(shp)
104            lst_fld = ['FEATURETYP','FOTLOKALID', 'VALIDERING']
105            atype = ('vali', arcpy.Describe(shp).shapeType, arcpy.GetCount_management(shp)[0], loc)
106            f_layer = arcpy.MakeFeatureLayer_management(shp, 'c_lay')
107            np_arry = arcpy.da.TableToNumPyArray(f_layer, lst_fld)
108            np_arry.dtype.names = 'FEATURETYPE','FOTLOKALID', 'VALIDERINGSBESKRIVELSE'
109            arcpy.Delete_management('c_lay')
110        elif len(arcpy.ListFields(shp, 'UDPEGNINGS')) == 1:
111            lst_fld = ['GEODANMARK','KILDEID', 'UDPEGNINGS']
112            atype = ('udpe', arcpy.Describe(shp).shapeType, arcpy.GetCount_management(shp)[0], 'TEST')
113            f_layer = arcpy.MakeFeatureLayer_management(shp, 'c_lay',)
114            np_arry = arcpy.da.TableToNumPyArray(f_layer, lst_fld)
115            np_arry.dtype.names = 'GEODANMARKTYPE','KILDEID', 'UDPEGNINGSBESKRIVELSE'
116            arcpy.Delete_management('c_lay')
117        else:
118            # write to log file
119            sys.exit(1)
120        
121        """ create connection to Oracle database and iterate over rows in array
122            to see if they exist in Oracle table;
123            update value in Oracle table and increment sucess count
124            if success count matches atype[2] then move shp from 'Behandlet'"""
125            
126        query = """UPDATE {db_schema}.{db_table} SET MARKERINGSHANDLING = 'behandlet' 
127        WHERE MARKERINGSHANDLING = 'ubehandlet' AND {tbl_colt} = '{colt_val}' 
128        AND {tbl_coli} = {coli_val}"""
129        
130        if atype[0] == 'vali':
131            dict_type = {'Point':'GEODANMARK_FORRETNINGMARKERING',
132                         'Polyline':'GEODANMARK_FORRETNING_LINJE',
133                         'Polygon':'GEODANMARK_FORRETNING_AREAL'}
134            if atype[3] == 'PROD':
135                ora_con = ora_prod()
136                cursor = ora_con.cursor()
137                for feat_data in np.nditer(np_arry):
138                    l_query = query.format(db_schema='VALIDERING',
139                                           db_table=dict_type[atype[1]],
140                                           tbl_colt='VALIDERINGSBESKRIVELSE',
141                                           colt_val=feat_data.item(0)[2].encode('Cp1252'),
142                                           tbl_coli='FOTLOKALID',
143                                           coli_val=int(feat_data.item(0)[1]))
144                    cursor.execute(l_query)
145                cursor.close()
146            else:
147                ora_con = ora_test_vali()
148                cursor = ora_con.cursor()
149                for feat_data in np.nditer(np_arry):
150                    print(feat_data.item(0)[1])
151                    l_query = query.format(db_schema='VALIDERING',
152                                           db_table=dict_type[atype[1]],
153                                           tbl_colt='VALIDERINGSBESKRIVELSE',
154                                           colt_val=feat_data.item(0)[2].encode('Cp1252'),
155                                           tbl_coli='FOTLOKALID',
156                                           coli_val=int(feat_data.item(0)[1]))
157                    cursor.execute(l_query)
158                cursor.close()
159        elif atype[0] == 'udpe':
160            dict_type = {'Point':'UDPEGNINGPUNKT',
161                         'Polyline':'UDPEGNINGLINJE',
162                         'Polygon':'UDPEGNINGFLADE'}
163            ora_con = ora_test_udpe()
164            for feat_data in np.nditer(np_arry):
165                cursor = ora_con.cursor()
166                if isinstance(feat_data.item(0)[1], int) or isinstance(feat_data.item(0)[1], float):
167                    u_kilde = int(feat_data.item(0)[1])
168                elif isinstance(feat_data.item(0)[1], unicode):
169                    u_kilde = "'" + feat_data.item(0)[1].encode('Cp1252') + "'"
170                l_query = query.format(db_schema='UDPEGNING_TIL_GEODANMARK',
171                                        db_table=dict_type[atype[1]],
172                                        tbl_colt='UDPEGNINGSBESKRIVELSE',
173                                        colt_val=feat_data.item(0)[2].encode('Cp1252'),
174                                        tbl_coli='KILDEID',
175                                        coli_val=u_kilde)
176                cursor.execute(l_query)
177            cursor.close()
178        ora_con.commit()
179        ora_con.close()
180        shp_move(shp)
181        print('Moved shapefile: '+shp)