EAMS/2.make_att_record.py at master · MohanSha/EAMS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
#import libraries
import xlrd
import MySQLdb
from array import *
from datetime import datetime, time, tzinfo, timedelta
import ConfigParser

#reading config file
Config = ConfigParser.ConfigParser()
Config.read("config.ini")

#function for left substring
def left(s, amount = 1, substring = ""):
    if (substring == ""):
        return s[:amount]
    else:
        if (len(substring) > amount):
            substring = substring[:amount]
        return substring + s[:-amount]

#function for right substring
def right(s, amount = 1, substring = ""):
    if (substring == ""):
        return s[-amount:]
    else:
        if (len(substring) > amount):
            substring = substring[:amount]
        return s[:-amount] + substring

# Establish a MySQL connection
database = MySQLdb.connect (host=Config.get("attenddb","host"), port=int(Config.get("attenddb","port")),
                               user=Config.get("attenddb","user"), passwd=Config.get("attenddb","password"), db=Config.get("attenddb","db"))

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()
insert_cursor = database.cursor()

tbl_filename="ac_data"
#uid="02074"
# Create the Select sql query
query = "SELECT * FROM "+tbl_filename
print query

# Execute sql Query
cursor.execute(query)

count = cursor.rowcount
print count
c=0
insert_tbl = "att_stage01"      ##raw_input("Enter staging table name: ")
queryBOL = """INSERT INTO `"""+insert_tbl+"""`(`ID`, `Date`, `Leave_status`) VALUES ("""
insert_query=""

#read each record and normailize
row = cursor.fetchone()
while row is not None:
  if c==10:
      pass
      #exit()
  else:
      pass
  #print(row)
  #print "ID = "+row[0]
  #Remove RI in empolyee code and format it to 5 digits
  if left(row[0],2).upper()=="RI":
      raw_ri_id = str(right(row[0],(len(row[0])-2)))
      #ri_id = '{:05d}'.format(raw_ri_id)
      ri_id = raw_ri_id.rjust(5,'0')
      print "RI -id "+ str(ri_id)
  else:
      ri_id = row[0].rjust(5,'0')
      #ri_id = row[0]

  #print "Name = "+row[1]
  #print "Date ="+row[2]
  #string to datetime format conversion
  if row[3]=="":
    intime = datetime.strptime('0:0:0', '%H:%M:%S')
  else:
    intime = datetime.strptime(row[3], '%H:%M:%S')
  if row[4]=="":
    outime = datetime.strptime('0:0:0', '%H:%M:%S')
  else:
    outime = datetime.strptime(row[4], '%H:%M:%S')

  #outime=  datetime.strptime(row[4], '%H:%M:%S')
  #calculate working time
  uptime = outime - intime
  if uptime.seconds==0:
        # print "Absent"
        leavestatus="A"
  else:
        leavestatus="P"
        #print "Present"
  #print str(uptime.seconds)
  insert_query = queryBOL+"""'"""+str(ri_id)+"""','"""+row[2]+"""','"""+leavestatus+"""');"""
  print insert_query
  insert_cursor.execute(insert_query)

  c=c+1
  #print "Leave Status ="+str(time.timedelta(seconds=int(uptime)))
#  break
  row = cursor.fetchone()

#exit()
# Close the cursor
cursor.close()
insert_cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

# Print results
print ""
print "All Done!"
print "I just imported "  + str(c) + " rows to MySQL!"