def GetCustomerConnect(cid):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
logger.info("inside get customer connection details")
cur.execute(
"Select s.dbusername ,s.dbpassword ,s.dbname, d.dbserverip from customer s INNER JOIN dbservers d on s.dbserverid = d.dbserverid where s.custid = (%s)",
[cid])
objcon.commit()
print('Data from joins fetched successfully.')
rows = cur.fetchall()
dname = rows[0][2]
logger.info(dname)
uname = rows[0][0]
pwd = rows[0][1]
host = rows[0][3]
return psycopg2.connect(database=dname,
user=uname,
password=pwd,
host=host,
port="5432")
except Exception as e:
logger.error(e)
return False
def SELECT_QUERY():
objcon = con.dbDetail()
cur = objcon.cursor()
cur.execute("SELECT * from user_details ")
print('Data entered successfully.')
rows = cur.fetchall()
rowarray_list = []
for row in rows:
t = (row[0], row[1], row[2], row[3], row[4], row[5])
rowarray_list.append(t)
# logger.info(rowarray_list)
j = json.dumps(rowarray_list)
# logger.info(j)
objects_list = []
for row in rows:
d = collections.OrderedDict()
d['userid'] = row[0]
d['employee_id'] = row[1]
d['Firstname'] = row[2]
d['Lastname'] = row[3]
d['Address'] = row[4]
objects_list.append(d)
j = json.dumps(objects_list)
logger.info("logged j value")
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return j
print('Data displayed successfully.')
def SELECT_DB():
objcon=con.dbDetail()
cur=objcon.cursor()
cur.execute("SELECT dbserverid , dbserverip from dbservers ")
rows = cur.fetchall()
rowarray_list = []
for row in rows:
t = (row[0],row[1])
rowarray_list.append(t)
# logger.info(rowarray_list)
j = json.dumps(rowarray_list)
# logger.info(j)
objects_list = []
for row in rows:
d = collections.OrderedDict()
d['dbseverid'] = row[0]
d['dbseverip'] = row[1]
# d[row[0]] =row[1]
objects_list.append(d)
j = json.dumps(objects_list)
logger.info(j)
# logger.info("logged j value")
if(objcon):
objcon.close()
print("\nThe connection is closed.")
return j
print ( 'Data displayed successfully.' )
def Validate_Login(userid, password):
try:
user = dob.User()
objcon = con.dbDetail()
cur = objcon.cursor()
# query_select = ("SELECT * from user_details where user_id = '"+str(userid)+"'")
logger.info("entered validate_login")
cur.execute("SELECT * from user_details where user_id = (%s)",
[userid])
# cur.execute(query_select)
rows = cur.fetchall()
logger.info(rows)
logger.info("data fetched")
rowarray_list = []
for row in rows:
if (ut.compare_hash(password, row[5])):
user.firstname = row[2]
user.lastname = row[3]
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return user
except Exception as e:
logger.error(e)
return False
def CUSTOMER_ADD_QUERY (daobj) :
try :
objcon=con.dbDetail()
cur=objcon.cursor()
cur.execute("INSERT INTO customer(custname,custaddress,dbserverid,dbname,dbusername,dbpassword) VALUES(%s, %s,%s, %s, %s,%s)",(daobj.custname, daobj.custaddress,daobj.dbserverid,daobj.dbname,daobj.dbusername,daobj.dbpassword) )
objcon.commit()
print ( 'Data entered successfully.' )
cur.execute("SELECT custid from customer where custname = (%s)",[daobj.custname])
rows =cur.fetchall()
logger.info(rows)
logger.info(str(rows))
for row in rows:
customer_id = row[0]
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return customer_id
except Exception as e :
logger.error(e)
return False
def get_last_access(token):
try:
logger.info("entered get last access time")
objcon = con.dbDetail()
cur = objcon.cursor()
logger.info("get last accessed")
logger.info(token)
# cur.execute("Select last_accessed_time from session_details where token =(%s)",(str(token)))
cur.execute(
"Select last_accessed_time from session_details where token_issued = '"
+ str(token) + "'")
rows = cur.fetchall()
logger.info("fetched time")
for row in rows:
logger.info(row[0])
if (objcon):
objcon.close()
print("\nThe connection is closed.")
logger.info("returning time")
return row[0]
except Exception as e:
logger.error(e)
return False
def delete_token(token):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
logger.info("entered delete token")
objcon = con.dbDetail()
cur = objcon.cursor()
cur.execute("Delete from session_details where token_issued = '" +
str(token) + "'")
objcon.commit()
logger.info("deleted")
if (objcon):
objcon.close()
print("\nThe connection is closed.")
except Exception as e:
logger.error(e)
return False
def CreateCustomerDB(cid):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
#fetch db details of customer
logger.info("inside get customer connection details")
cur.execute(
"Select s.dbusername ,s.dbpassword ,s.dbname, d.dbserverip from customer s INNER JOIN dbservers d on s.dbserverid = d.dbserverid where s.custid = (%s)",
[cid])
objcon.commit()
print('Data from joins fetched successfully.')
rows = cur.fetchall()
dname = rows[0][2]
logger.info(dname)
uname = rows[0][0]
pwd = rows[0][1]
host = rows[0][3]
objcon.autocommit = True
sqlCreateDatabase = "create database " + dname + ";"
query = " CREATE USER " + uname + " WITH ENCRYPTED PASSWORD '" + pwd + "' ; GRANT ALL PRIVILEGES ON DATABASE " + dname + " TO " + uname + ";"
cur.execute(sqlCreateDatabase)
cur.execute(query)
conn = psycopg2.connect(database=dname,
user=uname,
password=pwd,
host=host,
port="5432")
# objact = dob.Action()
# objact.status= "processed"
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cursor = conn.cursor()
conn.autocommit = True
cursor.execute(
"CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL)"
)
logger.info("table created")
if (rows.count == 0):
return None
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return
except Exception as e:
logger.error(e)
return None
def test_CustomerAdd(self):
daobj = dob.Customer()
daobj.custid = 115
daobj.custname = 'customertest'
daobj.custaddress = 'india'
daobj.dbserverid = 1
daobj.dbname = 'testcustdb'
daobj.dbusername = '******'
daobj.dbpassword = '******'
objcon = con.dbDetail()
cur = objcon.cursor()
# cur.execute("INSERT INTO customer(custname,custaddress,dbserverid,dbname,dbusername,dbpassword) VALUES(%s, %s,%s, %s, %s,%s)",(daobj.custname, daobj.custaddress,daobj.dbserverid,daobj.dbname,daobj.dbusername,daobj.dbpassword) )
self.assertTrue(cf.CUSTOMER_ADD_QUERY(daobj))
objcon.commit()
if (objcon):
objcon.close()
print("\nThe connection is closed.")
def DBCreation(customer_id):
#fetch
#update
#query for db creation
try:
logger.info("inside DBCreation")
objcon= con.dbDetail()
cur=objcon.cursor()
objcon.autocommit = True
objact = dob.Action()
objact.status = "processing"
Customer_Info.UPDATE_ACTIONS_STATUS(objact.status,customer_id)
connect.CreateCustomerDB(customer_id)
objact.status = "processed"
Customer_Info.UPDATE_ACTIONS_STATUS(objact.status,customer_id)
return True
except Exception as e :
logger.error(e)
return False
def update_last_access(token):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
logger.info("entered update last access")
last_access = datetime.now()
cur.execute("Update session_details set last_accessed_time = '" +
str(last_access) + "' where token_issued ='" +
str(token) + "'")
objcon.commit()
logger.info("updated successfully")
if (objcon):
objcon.close()
print("\nThe connection is closed.")
except Exception as e:
logger.error(e)
return False
def UPDATE_SERVER_QUERY(server_status,sid):
try :
objcon=con.dbDetail()
cur=objcon.cursor()
logger.info("inside update server info method")
cur.execute("UPDATE dbservers set status = %s where dbserverid = (%s)",(server_status,sid) )
objcon.commit()
print ( 'Data Updated successfully in dbservers.' )
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e :
logger.error(e)
return False
def ACTION_ADD_QUERY (objact) :
try :
objcon=con.dbDetail()
cur=objcon.cursor()
logger.info("inside action add query method")
cur.execute("INSERT INTO actions(customerid,status,remarks)VALUES(%s,%s, %s)",(objact.cid ,objact.status,objact.remarks) )
objcon.commit()
print ( 'Data entered successfully in actions.' )
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e :
logger.error(e)
return False
def UPDATE_ACTIONS_STATUS(status,cid):
try :
objcon=con.dbDetail()
cur=objcon.cursor()
logger.info("inside update actions status method")
cur.execute("UPDATE actions set status = %s where customerid = (%s)",(status,cid) )
objcon.commit()
print ( 'Data Updated successfully in actions.' )
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e :
logger.error(e)
return False
def Reset_password(userid, password):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
# Sql = "UPDATE login_credentials SET p_word= (%s) WHERE username = (%s)"
# data = (password , username)
# cur.execute(Sql, data)
cur.execute(
"UPDATE user_details SET pwd= (%s) WHERE user_id = (%s)",
(password.decode('ascii'), userid))
# cur.execute("UPDATE login_credentials SET p_word= '"+str(password).replace("'","''")+"' WHERE username = '******'")
objcon.commit()
print('Data updated successfully.')
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e:
logger.error(e)
return False
def session_entries(userid, token, time, last_time):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
cur.execute(
"INSERT INTO session_details(u_id,token_issued,issued_time,last_accessed_time) VALUES(%s, %s,%s,%s)",
(userid, token, time, last_time))
objcon.commit()
print('Data entered successfully.')
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e:
logger.error(e)
return False
def EMPLOYEE_ADD_QUERY(daobj):
try:
objcon = con.dbDetail()
cur = objcon.cursor()
cur.execute(
"INSERT INTO user_details(user_id,e_id,first_name,last_name,address,pwd) VALUES(%s, %s,%s, %s, %s,%s)",
(daobj.user_id, daobj.e_id, daobj.first_name, daobj.last_name,
daobj.address, daobj.pwd.decode('ascii')))
objcon.commit()
print('Data entered successfully.')
if (objcon):
objcon.close()
print("\nThe connection is closed.")
return True
except Exception as e:
logger.error(e)
return False