def post_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zoleav.dbf")
f_ank = open(directory + 'oleav.csv', 'w')
f_ank.write("ID; ORGBASE_RN; FCAC_RN; DAYTYPE_RN; STARTDATE; ENDDATE\n")
#print len(ank)
for i in ank:
if i.deleted or i["OLEAV_RN"] == None or i["FCAC_RN"] == None or i[
"DAYTYPE_RN"] == None or i["ORGBASE_RN"] == None or i[
"STARTDATE"] == None or i["ENDDATE"] == None:
continue
if not i["ORGBASE_RN"] or not i["DAYTYPE_RN"] or not i[
"OLEAV_RN"] or not i[
"FCAC_RN"] or parus_id_to_odoo.parusIndexToOdoo(
i["FCAC_RN"].decode('cp1251').encode('utf-8').decode(
'utf-8')) in [
33, 1939, 1317, 3132, 3372, 3127, 257, 313
]:
continue
if i["STARTDATE"] < datetime.date(2014, 12, 01):
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["OLEAV_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["ORGBASE_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["FCAC_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["DAYTYPE_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(i["STARTDATE"]) + "\"" + "; " + "\"" +
str(i["ENDDATE"]) + "\"" + "\n")
def person_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zpost.dbf")
f_ank = open(directory + 'post.csv', 'w')
f_ank.write("ID; STARTDATE; ENDDATE; POST_NUM; NAME\n")
#print len(ank)
for i in ank:
if i.deleted or i["POST_CODE"] == None or i["ENDDATE"] == None or i[
"STARTDATE"] == None:
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["POST_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(i["STARTDATE"]) + "\"" + "; ")
f_ank.write("\"" + str(i["ENDDATE"]) + "\"" + "; ")
f_ank.write("\"" + i["POST_NUM"] + "\"" + "; ")
f_ank.write("\"" + i["POST_CODE"].decode('cp1251').encode('utf-8') +
"\"" + "\n")
f_ank.close()
print "zpost.dbf to post.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_post;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'post.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int unique, STARTDATE date, ENDDATE date, NUM int, NAME text);"
)
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute(
"UPDATE tabel_post SET STARTDATE=tmp_z.STARTDATE, ENDDATE=tmp_z.ENDDATE, NUM=tmp_z.NUM, NAME=tmp_z.NAME FROM tmp_z WHERE tabel_post.id = tmp_z.id;"
)
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute(
"INSERT INTO tabel_post (id, startdate, enddate, num, name) SELECT T.id, T.STARTDATE, T.ENDDATE, T.NUM, T.NAME FROM tmp_z AS T LEFT JOIN tabel_post AS P ON T.id = P.id WHERE P.id IS NULL ;"
)
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table post [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zoleav.dbf")
f_ank = open(directory+'oleav.csv', 'w')
f_ank.write ("ID; ORGBASE_RN; FCAC_RN; DAYTYPE_RN; STARTDATE; ENDDATE\n")
#print len(ank)
for i in ank:
if i.deleted or i["OLEAV_RN"] == None or i["FCAC_RN"] == None or i["DAYTYPE_RN"] == None or i ["ORGBASE_RN"] == None or i ["STARTDATE"] == None or i ["ENDDATE"] == None:
continue
if not i ["ORGBASE_RN"] or not i["DAYTYPE_RN"] or not i["OLEAV_RN"] or not i["FCAC_RN"] or parus_id_to_odoo.parusIndexToOdoo ( i ["FCAC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ) in [33,1939,1317,3132,3372,3127,257,313]:
continue
if i["STARTDATE"] < datetime.date(2014,12,01):
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["OLEAV_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\"" +"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["ORGBASE_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCAC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["DAYTYPE_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str( i["STARTDATE"] ) +"\""+"; "+"\""+ str (i["ENDDATE"] )+"\""+"\n")
print "zoleav.dbf to oleav.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'oleav.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, ORGBASE_RN int, FCAC_RN int, DAYTYPE_RN int, STARTDATE date, ENDDATE date);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_oleav SET DAYTYPE_RN=tmp_z.DAYTYPE_RN, FCAC_RN=tmp_z.FCAC_RN, ORGBASE_RN=tmp_z.ORGBASE_RN, STARTDATE=tmp_z.STARTDATE, ENDDATE=tmp_z.ENDDATE FROM tmp_z WHERE tabel_oleav.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_oleav (id, orgbase_rn, fcac_rn, daytype_rn, startdate, enddate) SELECT T.id, T.ORGBASE_RN, T.FCAC_RN, T.DAYTYPE_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_oleav AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for oleav table [ ok ]"
def pfcac_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zdaytype.dbf")
f_ank = open(directory + 'daytype.csv', 'w')
f_ank.write("ID; NICK; NAME;\n")
#print len(ank)
for i in ank:
if i.deleted or i["NICK"] == None or i["NAME"] == None or i[
"DAYTYPE_RN"] == None:
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["DAYTYPE_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(i["NICK"].decode('cp1251').encode('utf-8')) +
"\"" + ";")
f_ank.write("\"" + str(i["NAME"].decode('cp1251').encode('utf-8')) +
"\"" + "\n")
print "daytype.dbf to daytype.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_fcacwth;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'daytype.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int unique, NICK text, NAME text);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#UPDATE DATA
cur.execute(
"UPDATE tabel_daytype SET NICK=tmp_z.NICK, NAME=tmp_z.NAME FROM tmp_z WHERE tabel_daytype.id = tmp_z.id;"
)
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks.
#add those records that are in the table tmp.z and not in the table fcacwth. And check that all the fields in the table fcac_rn existed fcac
cur.execute(
"INSERT INTO tabel_daytype (id, nick, name) SELECT G.id, G.NICK, G.NAME FROM (SELECT T.id, T.NICK, T.NAME FROM tmp_z AS T LEFT JOIN tabel_daytype AS P ON T.id = P.id WHERE P.id IS NULL) AS G ;"
)
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table daytype [ ok ]"
def pfcac_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zfcacwth.dbf")
f_ank = open(directory+'fcacwth.csv', 'w')
f_ank.write ("ID; FCAC_RN; HRTYPE_RN; HOURQNT; DATE;\n")
#print len(ank)
for i in ank:
if i.deleted or i["DATE"] == None or i ["FCAC_RN"] == None or i ["HOURQNT"] == None or i ["HRTYPE_RN"] == None or i ["FCACWTH_RN"] == None :
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCACWTH_RN"].decode('cp1251').encode('utf-8').decode('utf-8'))) +"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCAC_RN"].decode('cp1251').encode('utf-8').decode('utf-8'))) +"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["HRTYPE_RN"].decode('cp1251').encode('utf-8').decode('utf-8'))) +"\""+"; ")
f_ank.write ("\""+ str ( i["HOURQNT"] ) +"\""+ "; ")
f_ank.write ("\""+ str( i["DATE"] ) +"\""+ "\n")
print "zfcacwth.dbf to fcacwth.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_fcacwth;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'fcacwth.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int unique, FCAC_RN int, HRTYPE_RN int, HOURQNT double precision, DATE date);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_fcacwth SET FCAC_RN=tmp_z.FCAC_RN, HRTYPE_RN=tmp_z.HRTYPE_RN, HOURQNT=tmp_z.HOURQNT, DATE=tmp_z.DATE FROM tmp_z WHERE tabel_fcacwth.id = tmp_z.id;")
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks.
#add those records that are in the table tmp.z and not in the table fcacwth. And check that all the fields in the table fcac_rn existed fcac
cur.execute("INSERT INTO tabel_fcacwth (id, fcac_rn, hrtype_rn, hourqnt, date) SELECT G.id, G.FCAC_RN, G.HRTYPE_RN, G.HOURQNT, G.DATE FROM (SELECT T.id, T.FCAC_RN, T.HRTYPE_RN, T.HOURQNT, T.DATE FROM tmp_z AS T LEFT JOIN tabel_fcacwth AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_fcac AS H where G.fcac_rn = H.id ;")
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table fcacwth [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zank.dbf")
f_ank = open(directory+'ank.csv', 'w')
f_ank.write ("ID; ORGBASE_RN; TAB_NUM; JOBBEGIN; JOBEND\n")
#print len(ank)
for i in ank:
if i.deleted or i["JOBBEGIN"] == None or i["JOBEND"] == None or i ["ANK_RN"] == None or i ["ORGBASE_RN"] == None:
continue
try:
int(i["TAB_NUM"])
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["ANK_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\"" +"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["ORGBASE_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str ( i["TAB_NUM"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str( i["JOBBEGIN"] ) +"\""+"; "+"\""+ str (i["JOBEND"] )+"\""+"\n")
except ValueError:
print "fixed error in database [ ok ]"
print "zank.dbf to ank.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'ank.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, ORGBASE_RN int, TAB_NUM int, JOBBEGIN date, JOBEND date);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_ank SET TAB_NUM=tmp_z.TAB_NUM, JOBBEGIN=tmp_z.JOBBEGIN, JOBEND=tmp_z.JOBEND FROM tmp_z WHERE tabel_ank.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_ank (id, orgbase_rn, tab_num, jobend,jobbegin) SELECT T.id, T.ORGBASE_RN, T.TAB_NUM, T.JOBEND, T.JOBBEGIN FROM tmp_z AS T LEFT JOIN tabel_ank AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for ank table [ ok ]"
def person_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zfcacch.dbf")
f_ank = open(directory+'fcacch.csv', 'w')
f_ank.write ("ID; FCACBS_RN; STQNT\n")
#print len(ank)
for i in ank:
if i.deleted or i["FCACBS_RN"] == None or i["STQNT"] == None or i["FCACBS_RN"] == None or i["GRRBDC_RN"] == None:
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCACCH_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCACBS_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRRBDC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+ str ( i ["STQNT"]).decode('cp1251').encode('utf-8') +"\""+"\n")
f_ank.close()
print "zfcacch.dbf to zfcacch.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_fcacch;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'fcacch.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int unique, FCACBS_RN int, GRRBDC_RN int, STQNT double precision);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute("UPDATE tabel_fcacch SET FCACBS_RN=tmp_z.FCACBS_RN, GRRBDC_RN=tmp_z.GRRBDC_RN, STQNT=tmp_z.STQNT FROM tmp_z WHERE tabel_fcacch.id = tmp_z.id;")
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_fcacch (id, fcacbs_rn, grrbdc_rn, stqnt) SELECT T.id, T.FCACBS_RN, T.GRRBDC_RN, T.STQNT FROM tmp_z AS T LEFT JOIN tabel_fcacch AS P ON T.id = P.id WHERE P.id IS NULL ;")
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table fcacch [ ok ]"
def pfcac_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zfcac.dbf")
f_ank = open(directory+'fcac.csv', 'w')
f_ank.write ("ID; TIPDOL_RN; KATPER_RN; ANK_RN; POST_RN; SUBDIV_RN; VIDISP_RN; STARTDATE; ENDDATE\n")
#print len(ank)
for i in ank:
if i.deleted or i["STARTDATE"] == None or i["ENDDATE"] == None or i ["ANK_RN"] == None or i ["POST_RN"] == None or i ["SUBDIV_RN"] == None:
continue
if(i ["POST_RN"].decode('cp1251').encode('utf-8') == "" ) or (i ["FCAC_NUM"].decode('cp1251').encode('utf-8') == "" ):
continue
if i ["VIDISP_RN"] == None or i ["FCAC_RN"] == None or i ["TIPDOL_RN"] == None or i ["TIPDOL_RN"] == 0:
continue
if parus_id_to_odoo.parusIndexToOdoo ( i ["TIPDOL_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ) == 0:
continue
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["FCAC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["TIPDOL_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["KATPER_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["ANK_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["POST_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["SUBDIV_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["VIDISP_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+ "; ")
f_ank.write ( "\""+ str( i["STARTDATE"] ) +"\""+"; "+"\""+ str (i["ENDDATE"] )+"\""+ "\n")
print "zfcac.dbf to fcac.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_fcac;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'fcac.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int unique, TIPDOL_RN int, KATPER_RN int, ANK_RN int, POST_RN int, SUBDIV_RN int, VIDISP_RN int, STARTDATE date, ENDDATE date);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_fcac SET TIPDOL_RN=tmp_z.TIPDOL_RN, KATPER_RN=tmp_z.KATPER_RN, ANK_RN=tmp_z.ANK_RN, POST_RN=tmp_z.POST_RN, SUBDIV_RN=tmp_z.SUBDIV_RN, VIDISP_RN=tmp_z.VIDISP_RN, STARTDATE=tmp_z.STARTDATE, ENDDATE=tmp_z.ENDDATE FROM tmp_z WHERE tabel_fcac.id = tmp_z.id;")
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_fcac (id, tipdol_rn, katper_rn, ank_rn, post_rn, subdiv_rn, vidisp_rn, startdate, enddate) SELECT G.id, G.TIPDOL_RN, G.KATPER_RN, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.TIPDOL_RN, T.KATPER_RN, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table fcac [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zgrmonth.dbf")
f_ank = open(directory+'grmonth.csv', 'w')
f_ank.write ("ID; GRRBDC_RN; YEAR; MONTH; DAYALL; HOURALL \n")
#print len(ank)
for i in ank:
if i.deleted or i["GRMONTH_RN"] == None or i["GRRBDC_RN"] == None or i["YEAR"] == None or i["MONTH"] == None or i["DAYALL"] == None or i["HOURALL"] == None:
continue
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRMONTH_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+ "; ")
f_ank.write ( "\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRRBDC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+ "; ")
f_ank.write ( "\""+ str ( i["YEAR"] ).decode('cp1251').encode('utf-8')+"\""+ "; ")
f_ank.write ( "\""+ str ( i["MONTH"] ).decode('cp1251').encode('utf-8')+"\""+ "; ")
f_ank.write ( "\""+ str ( i["DAYALL"] ).decode('cp1251').encode('utf-8')+"\""+ "; ")
f_ank.write ( "\""+ str ( i["HOURALL"] ).decode('cp1251').encode('utf-8')+"\""+ "\n")
print "zgrmonth.dbf to grmonth.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'grmonth.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, GRRBDC_RN int, YEAR int, MONTH int, DAYALL int, HOURALL double precision);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_grmonth SET GRRBDC_RN=tmp_z.GRRBDC_RN, YEAR=tmp_z.YEAR, MONTH=tmp_z.MONTH, DAYALL=tmp_z.DAYALL, HOURALL=tmp_z.HOURALL FROM tmp_z WHERE tabel_grmonth.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_grmonth (id, grrbdc_rn, year, month, dayall, hourall) SELECT T.id, T.GRRBDC_RN, T.YEAR, T.MONTH, T.DAYALL, T.HOURALL FROM tmp_z AS T LEFT JOIN tabel_grmonth AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for grmonth table [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zgrday.dbf")
f_ank = open(directory+'grday.csv', 'w')
f_ank.write ("ID; GRMONTH_RN; MONTHDAY; HOURINDAY \n")
#print len(ank)
for i in ank:
if i.deleted or i["GRDAY_RN"] == None or i["GRMONTH_RN"] == None or i ["MONTHDAY"] == None or i ["HOURINDAY"] == None:
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRDAY_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRMONTH_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str ( i["MONTHDAY"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["HOURINDAY"] ).decode('cp1251').encode('utf-8')+"\""+"\n")
print "zgrday.dbf to grday.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'grday.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, GRMONTH_RN int, MONTHDAY int, HOURINDAY double precision);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_grday SET GRMONTH_RN=tmp_z.GRMONTH_RN, MONTHDAY=tmp_z.MONTHDAY, HOURINDAY=tmp_z.HOURINDAY FROM tmp_z WHERE tabel_grday.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_grday (id, grmonth_rn, monthday, hourinday) SELECT T.id, T.GRMONTH_RN, T.MONTHDAY, T.HOURINDAY FROM tmp_z AS T LEFT JOIN tabel_grday AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for grday table [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zkatper.dbf")
f_ank = open(directory+'katper.csv', 'w')
f_ank.write ("ID; CODE; NAME; NICK; NUM\n")
#print len(ank)
for i in ank:
if i.deleted or i["KATPER_RN"] == None or i["CODE"] == None or i ["NAME"] == None or i ["NICK"] == None or i ["NUM"] == None:
continue
try:
int(i["NUM"])
f_ank.write ( "\""+str ( parus_id_to_odoo.parusIndexToOdoo ( i ["KATPER_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ( "\""+str ( i["CODE"]).decode('cp1251').encode('utf-8') +"\""+"; ")
f_ank.write ( "\""+str ( i["NAME"]).decode('cp1251').encode('utf-8') +"\""+"; ")
f_ank.write ( "\""+str ( i["NICK"]).decode('cp1251').encode('utf-8') +"\""+"; ")
f_ank.write ( "\""+str ( i["NUM"]).decode('cp1251').encode('utf-8') +"\""+"\n")
except ValueError:
print "fixed error in database katper int(i[\"NUM\"]) [ ok ]"
print "zank.dbf to katper.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'katper.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, CODE text, NAME text, NICK text, NUM int);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_katper SET CODE=tmp_z.CODE, NAME=tmp_z.NAME, NICK=tmp_z.NICK FROM tmp_z WHERE tabel_katper.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_katper (id, code, name, nick, num) SELECT T.id, T.CODE, T.NAME, T.NICK, T.NUM FROM tmp_z AS T LEFT JOIN tabel_katper AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for katper table [ ok ]"
def post_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zgrrbdc.dbf")
f_ank = open(directory+'grrbdc.csv', 'w')
f_ank.write ("ID; CODE; NAME; DAYSMEAN; HOURMEAN; WORKWEEK; DAYSMIN; HOURMIN \n")
#print len(ank)
for i in ank:
if i.deleted or i["GRRBDC_RN"] == None or i["CODE"] == None or i["NAME"] == None or i["DAYSMEAN"] == None or i["HOURMEAN"] == None or i["WORKWEEK"] == None or i["DAYSMIN"] == None:
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["GRRBDC_RN"].decode('cp1251').encode('utf-8').decode('utf-8') ))+"\""+"; ")
f_ank.write ("\""+ str ( i["CODE"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["NAME"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["DAYSMEAN"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["HOURMEAN"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( int(i["WORKWEEK"]) ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["DAYSMIN"] ).decode('cp1251').encode('utf-8')+"\""+"; ")
f_ank.write ("\""+ str ( i["HOURMIN"] ).decode('cp1251').encode('utf-8')+"\""+"\n")
print "zgrrbdc.dbf to grrbdc.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'grrbdc.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int, CODE text, NAME text, DAYSMEAN double precision, HOURMEAN double precision, WORKWEEK int, DAYSMIN double precision, HOURMIN double precision);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#UPDATE DATA
cur.execute("UPDATE tabel_grrbdc SET CODE=tmp_z.CODE, NAME=tmp_z.NAME, DAYSMEAN=tmp_z.DAYSMEAN, HOURMEAN=tmp_z.HOURMEAN, WORKWEEK=tmp_z.WORKWEEK, DAYSMIN=tmp_z.DAYSMIN, HOURMIN=tmp_z.HOURMIN FROM tmp_z WHERE tabel_grrbdc.id = tmp_z.id;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_grrbdc (id, code, name, daysmean, hourmean, workweek, daysmin, hourmin) SELECT T.id, T.CODE, T.NAME, T.DAYSMEAN, T.HOURMEAN, T.WORKWEEK, T.DAYSMIN, T.HOURMIN FROM tmp_z AS T LEFT JOIN tabel_grrbdc AS P ON T.id = P.id WHERE P.id IS NULL ;")
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for grrbdc table [ ok ]"
def person_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"zpost.dbf")
f_ank = open(directory+'post.csv', 'w')
f_ank.write ("ID; STARTDATE; ENDDATE; POST_NUM; NAME\n")
#print len(ank)
for i in ank:
if i.deleted or i["POST_CODE"] == None or i["ENDDATE"] == None or i["STARTDATE"] == None:
continue
f_ank.write ("\""+str ( parus_id_to_odoo.parusIndexToOdoo ( i ["POST_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+str (i ["STARTDATE"]) +"\""+"; ")
f_ank.write ("\""+str (i ["ENDDATE"]) +"\""+"; ")
f_ank.write ("\""+i ["POST_NUM"] +"\""+"; ")
f_ank.write ("\""+i ["POST_CODE"].decode('cp1251').encode('utf-8') +"\""+"\n")
f_ank.close()
print "zpost.dbf to post.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_post;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'post.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int unique, STARTDATE date, ENDDATE date, NUM int, NAME text);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute("UPDATE tabel_post SET STARTDATE=tmp_z.STARTDATE, ENDDATE=tmp_z.ENDDATE, NUM=tmp_z.NUM, NAME=tmp_z.NAME FROM tmp_z WHERE tabel_post.id = tmp_z.id;")
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_post (id, startdate, enddate, num, name) SELECT T.id, T.STARTDATE, T.ENDDATE, T.NUM, T.NAME FROM tmp_z AS T LEFT JOIN tabel_post AS P ON T.id = P.id WHERE P.id IS NULL ;")
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table post [ ok ]"
def person_func(directory,dbname,userr,pas,hostt,portt):
ank = dbf.Dbf(directory+"person.dbf")
f_ank = open(directory+'person.csv', 'w')
f_ank.write ("ID; SURNAME; FIRSTNAME; SECONDNAME\n")
#print len(ank)
for i in ank:
if i.deleted or i["SURNAME"] == None or i["FIRSTNAME"] == None or i ["SECONDNAME"] == None:
continue
f_ank.write ("\""+ str ( parus_id_to_odoo.parusIndexToOdoo ( i ["ORBASE_RN"].decode('cp1251').encode('utf-8').decode('utf-8') )) +"\""+"; ")
f_ank.write ("\""+i ["SURNAME"].decode('cp1251').encode('utf-8').replace(' ', '') +"\""+"; ")
f_ank.write ("\""+i ["FIRSTNAME"].decode('cp1251').encode('utf-8') +"\""+"; ")
f_ank.write ("\""+i ["SECONDNAME"].decode('cp1251').encode('utf-8') +"\""+"\n")
f_ank.close()
print "person.dbf to person.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname, user=userr,password=pas,host=hostt,port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_person;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory+'person.csv')
#CREATE TEMP TABLE
cur.execute("CREATE TEMP TABLE tmp_z (ID int unique, SURNAME text, FIRSTNAME text, SECONDNAME text);")
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;", my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute("UPDATE tabel_person SET SURNAME=tmp_z.SURNAME, FIRSTNAME=tmp_z.FIRSTNAME, SECONDNAME=tmp_z.SECONDNAME, FULL_NAME=CONCAT(tmp_z.SURNAME,tmp_z.FIRSTNAME,tmp_z.SECONDNAME) FROM tmp_z WHERE tabel_person.id = tmp_z.id;")
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute("INSERT INTO tabel_person (id, surname, firstname, secondname, full_name) SELECT T.id, T.SURNAME, T.FIRSTNAME, T.SECONDNAME, CONCAT(T.SURNAME,T.FIRSTNAME,T.SECONDNAME) FROM tmp_z AS T LEFT JOIN tabel_person AS P ON T.id = P.id WHERE P.id IS NULL ;")
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table person [ ok ]"
def post_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zgrrbdc.dbf")
f_ank = open(directory + 'grrbdc.csv', 'w')
f_ank.write(
"ID; CODE; NAME; DAYSMEAN; HOURMEAN; WORKWEEK; DAYSMIN; HOURMIN \n")
#print len(ank)
for i in ank:
if i.deleted or i["GRRBDC_RN"] == None or i["CODE"] == None or i[
"NAME"] == None or i["DAYSMEAN"] == None or i[
"HOURMEAN"] == None or i["WORKWEEK"] == None or i[
"DAYSMIN"] == None:
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["GRRBDC_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(i["CODE"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" + str(i["NAME"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" +
str(i["DAYSMEAN"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" +
str(i["HOURMEAN"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" +
str(int(i["WORKWEEK"])).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" + str(i["DAYSMIN"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" + str(i["HOURMIN"]).decode('cp1251').encode('utf-8') +
"\"" + "\n")
print "zgrrbdc.dbf to grrbdc.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'grrbdc.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int, CODE text, NAME text, DAYSMEAN double precision, HOURMEAN double precision, WORKWEEK int, DAYSMIN double precision, HOURMIN double precision);"
)
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#UPDATE DATA
cur.execute(
"UPDATE tabel_grrbdc SET CODE=tmp_z.CODE, NAME=tmp_z.NAME, DAYSMEAN=tmp_z.DAYSMEAN, HOURMEAN=tmp_z.HOURMEAN, WORKWEEK=tmp_z.WORKWEEK, DAYSMIN=tmp_z.DAYSMIN, HOURMIN=tmp_z.HOURMIN FROM tmp_z WHERE tabel_grrbdc.id = tmp_z.id;"
)
#INSERT DATA add something which lacks
cur.execute(
"INSERT INTO tabel_grrbdc (id, code, name, daysmean, hourmean, workweek, daysmin, hourmin) SELECT T.id, T.CODE, T.NAME, T.DAYSMEAN, T.HOURMEAN, T.WORKWEEK, T.DAYSMIN, T.HOURMIN FROM tmp_z AS T LEFT JOIN tabel_grrbdc AS P ON T.id = P.id WHERE P.id IS NULL ;"
)
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for grrbdc table [ ok ]"
def post_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zank.dbf")
f_ank = open(directory + 'ank.csv', 'w')
f_ank.write("ID; ORGBASE_RN; TAB_NUM; JOBBEGIN; JOBEND\n")
#print len(ank)
for i in ank:
if i.deleted or i["JOBBEGIN"] == None or i["JOBEND"] == None or i[
"ANK_RN"] == None or i["ORGBASE_RN"] == None:
continue
try:
int(i["TAB_NUM"])
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["ANK_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["ORGBASE_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" +
str(i["TAB_NUM"]).decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" + str(i["JOBBEGIN"]) + "\"" + "; " + "\"" +
str(i["JOBEND"]) + "\"" + "\n")
except ValueError:
print "fixed error in database [ ok ]"
print "zank.dbf to ank.csv [ ok ]"
f_ank.close()
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_ank;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'ank.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int, ORGBASE_RN int, TAB_NUM int, JOBBEGIN date, JOBEND date);"
)
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#UPDATE DATA
cur.execute(
"UPDATE tabel_ank SET TAB_NUM=tmp_z.TAB_NUM, JOBBEGIN=tmp_z.JOBBEGIN, JOBEND=tmp_z.JOBEND FROM tmp_z WHERE tabel_ank.id = tmp_z.id;"
)
#INSERT DATA add something which lacks
cur.execute(
"INSERT INTO tabel_ank (id, orgbase_rn, tab_num, jobend,jobbegin) SELECT T.id, T.ORGBASE_RN, T.TAB_NUM, T.JOBEND, T.JOBBEGIN FROM tmp_z AS T LEFT JOIN tabel_ank AS P ON T.id = P.id WHERE P.id IS NULL ;"
)
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for ank table [ ok ]"
def person_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "person.dbf")
f_ank = open(directory + 'person.csv', 'w')
f_ank.write("ID; SURNAME; FIRSTNAME; SECONDNAME\n")
#print len(ank)
for i in ank:
if i.deleted or i["SURNAME"] == None or i["FIRSTNAME"] == None or i[
"SECONDNAME"] == None:
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["ORBASE_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write(
"\"" +
i["SURNAME"].decode('cp1251').encode('utf-8').replace(' ', '') +
"\"" + "; ")
f_ank.write("\"" + i["FIRSTNAME"].decode('cp1251').encode('utf-8') +
"\"" + "; ")
f_ank.write("\"" + i["SECONDNAME"].decode('cp1251').encode('utf-8') +
"\"" + "\n")
f_ank.close()
print "person.dbf to person.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_person;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'person.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int unique, SURNAME text, FIRSTNAME text, SECONDNAME text);"
)
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute(
"UPDATE tabel_person SET SURNAME=tmp_z.SURNAME, FIRSTNAME=tmp_z.FIRSTNAME, SECONDNAME=tmp_z.SECONDNAME, FULL_NAME=CONCAT(tmp_z.SURNAME,tmp_z.FIRSTNAME,tmp_z.SECONDNAME) FROM tmp_z WHERE tabel_person.id = tmp_z.id;"
)
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute(
"INSERT INTO tabel_person (id, surname, firstname, secondname, full_name) SELECT T.id, T.SURNAME, T.FIRSTNAME, T.SECONDNAME, CONCAT(T.SURNAME,T.FIRSTNAME,T.SECONDNAME) FROM tmp_z AS T LEFT JOIN tabel_person AS P ON T.id = P.id WHERE P.id IS NULL ;"
)
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table person [ ok ]"
def person_func(directory, dbname, userr, pas, hostt, portt):
ank = dbf.Dbf(directory + "zfcacch.dbf")
f_ank = open(directory + 'fcacch.csv', 'w')
f_ank.write("ID; FCACBS_RN; STQNT\n")
#print len(ank)
for i in ank:
if i.deleted or i["FCACBS_RN"] == None or i["STQNT"] == None or i[
"FCACBS_RN"] == None or i["GRRBDC_RN"] == None:
continue
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["FCACCH_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["FCACBS_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(
parus_id_to_odoo.parusIndexToOdoo(i["GRRBDC_RN"].decode(
'cp1251').encode('utf-8').decode('utf-8'))) + "\"" + "; ")
f_ank.write("\"" + str(i["STQNT"]).decode('cp1251').encode('utf-8') +
"\"" + "\n")
f_ank.close()
print "zfcacch.dbf to zfcacch.csv [ ok ]"
#CONNECT TO DATABASE
con = psycopg2.connect(database=dbname,
user=userr,
password=pas,
host=hostt,
port=portt)
cur = con.cursor()
#cur.execute ("DELETE from tabel_fcacch;")
#OPEN CSV FILE GENERATED BY syncronize.py script
my_file = open(directory + 'fcacch.csv')
#CREATE TEMP TABLE
cur.execute(
"CREATE TEMP TABLE tmp_z (ID int unique, FCACBS_RN int, GRRBDC_RN int, STQNT double precision);"
)
cur.copy_expert("COPY tmp_z FROM STDIN WITH DELIMITER ';' CSV HEADER;",
my_file)
#cur.execute ("DELETE from tabel_fcac;")
#UPDATE DATA
cur.execute(
"UPDATE tabel_fcacch SET FCACBS_RN=tmp_z.FCACBS_RN, GRRBDC_RN=tmp_z.GRRBDC_RN, STQNT=tmp_z.STQNT FROM tmp_z WHERE tabel_fcacch.id = tmp_z.id;"
)
#cur.execute("SELECT G.id, G.ANK_RN, G.POST_RN, G.SUBDIV_RN, G.VIDISP_RN, G.STARTDATE, G.ENDDATE FROM (SELECT T.id, T.ANK_RN, T.POST_RN, T.SUBDIV_RN, T.VIDISP_RN, T.STARTDATE, T.ENDDATE FROM tmp_z AS T LEFT JOIN tabel_fcac AS P ON T.id = P.id WHERE P.id IS NULL) AS G, tabel_ank AS H where G.ank_rn = H.id ;")
#INSERT DATA add something which lacks
cur.execute(
"INSERT INTO tabel_fcacch (id, fcacbs_rn, grrbdc_rn, stqnt) SELECT T.id, T.FCACBS_RN, T.GRRBDC_RN, T.STQNT FROM tmp_z AS T LEFT JOIN tabel_fcacch AS P ON T.id = P.id WHERE P.id IS NULL ;"
)
#rows = cur.fetchall()
#for i in rows:
# print i
#DROP TEMP TABLE or auto drop after session
cur.execute("DROP TABLE tmp_z;")
#CLOSE CONNECTION
con.commit()
cur.close()
con.close()
print "sql requests for table fcacch [ ok ]"
