def reportG(self): data = {} data['infoG'] = [] data['infoU'] = [] data['infoC'] = [] data['devices'] = [] #info general cmc = conexion.search( "select name, creation_date from environments where id = %s", [self.id]) if (cmc): for i in cmc: data['infoG'].append({ 'name': i[0], 'creation_date': format(i[1]) }) else: return None #conteo 1 ccm = conexion.search( "select count(id_user) from bridge where id_environments =%s", [self.id]) if (ccm): for i in ccm: data['infoU'].append({'users': i[0]}) else: return None #conteo 2 ccc = conexion.search( "select count(enviroment) from clients where enviroment = %s", [self.id]) if (ccc): for i in ccc: data['infoC'].append({'clients': i[0]}) else: return None dt = '%Y %M' cmm = conexion.search( "SELECT DISTINCT DATE_FORMAT(admission_date, %s),SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS 'penging',SUM(CASE WHEN status = 2 THEN 1 ELSE 0 END) AS 'course',SUM(CASE WHEN status = 3 THEN 1 ELSE 0 END) AS 'finished'FROM devices where environment = %s ", [dt, self.id]) if (cmm[0][0] != None): if (cmm): for i in cmm: data['devices'].append({ 'date': format(i[0]), 'pending': int(i[1]), 'course': int(i[2]), 'finished': int(i[3]) }) return data else: return None else: return None
def manage_user_join_by_Code(self): now = datetime.now() data = {} data['manage'] = [] consult1 = conexion.search('select count(*) from bridge b, users u, environments e where b.id_user = u.id and b.id_environments = e.id and e.key_code = %s and b.id_user = %s',[self.key_code,self.id]) if(consult1[0][0] != 0): return "stError" else: consult = conexion.search('select id from environments where key_code = %s',[self.key_code]) if(consult): consult2 = conexion.Add('insert into bridge (id_user,id_environments,v_admin,linking_date,state) values (%s,%s,%s,%s,%s)',[int(self.id),int(consult[0][0]),0,now,1]) else: return "stError101"
def more_info_in_progress(self): data = {} data['device'] = [] cmm = conexion.search( 'select d.brand,d.model,d.type,d.accessories,d.conditions,d.work_to_do,d.admission_date,u.name,c.name, c.surname, c.phone, c.email, d.failure, d.diagnosis, d.solution from users u, devices d, clients c where d.user = u.id and d.client = c.identification and d.id = %s and d.environment = %s', [self.id, self.enviroment]) if (cmm): for i in cmm: data['device'].append({ 'brand': i[0], 'model': i[1], 'type': i[2], 'accessories': i[3], 'conditions': i[4], 'work_to_do': i[5], 'admission_date': format(i[6]), 'user': i[7], 'c_name': i[8], 'c_surname': i[9], 'c_phone': i[10], 'c_email': i[11], 'failure': i[12], 'diagnosis': i[13], 'solution': i[14] }) return data['device'] return None
def get_user(self): respuesta = conexion.search("select * from users where email = %s ", [self.email]) if (respuesta): return respuesta else: return None
def log_in(self): data = {} data['Lag'] = [] cmm = conexion.search("select user,password from admin where password", [self.psw]) if (cmm): for i in cmm: data['Lag'].append({"user": i[0], 'password': i[1]}) return None
def main_client_consult(self,consult,id_user,env): data = {} data['main'] = [] consult = conexion.search('select identification,name,surname from clients where identification = %s and enviroment =%s',[consult,env]) if(consult): for i in consult: data['main'].append({'consult':i[0],'name':i[1],'surname':i[2]}) return data['main'] return None
def main_bring_data(self): data = {} data['main'] = [] consult = conexion.search("select e.name, b.state from environments e, bridge b where e.id = b.id_environments and b.id_user = %s and b.id_environments = %s",[self.id, self.id_environments]) if(consult): for i in consult: data['main'].append({'name':i[0],'state':i[1]}) return data['main'] return None
def search_client(self): data = {} data['data'] = [] cmm = conexion.search("select * from clients where (enviroment = %s and identification = %s)",[self.enviroment, self.identification]) if(cmm): for i in cmm: data['data'].append({'identification':i[0],'enviroment':i[1],'name':i[2],'surname':i[3],'email':i[4],'phone':i[5],'address':i[6]}) return data['data'] else: return None
def show_history(self): data = {} data['history'] = [] cmm = conexion.search("select d.id, d.type, d.model, d.brand, u.name ,d.admission_date,d.status from devices d, users u where d.user = u.id and d.environment = %s and d.client = %s order by d.admission_date asc",[self.enviroment, self.identification]) if(cmm): for i in cmm: data['history'].append({'id':i[0],'type':i[1],'model':i[2],'brand':i[3],'user':i[4],'admission_date':format(i[5]),'status':i[6]}) return data['history'] else: return None
def consult_client(self): data = {} data['consult'] = [] data['environment'] = [] cmd = conexion.search("select e.name, e.img, c.identification, c.name , c.surname , u.name , u.surname from clients c , environments e, users u where c.identification = %s and c.enviroment= e.id and e.created_by = u.id" , [self.identification]) if(cmd): for i in cmd: data['environment'].append({'e_name':i[0],'e_img':i[1],'c_identification':i[2],'c_name': i[3],'c_surname':i[4],'u_name':i[5],'u_surname':i[6]}) else: return None cmm = conexion.search("select c.identification,d.type,d.brand,d.diagnosis,d.failure,d.solution,d.status from clients c , environments e ,devices d where c.identification = %s and c.enviroment= e.id and d.client = c.identification ", [self.identification]) if(cmm): for i in cmm: data['consult'].append({'c_identification':i[0],'d_type':i[1],'d_brand':i[2],'d_diagnosis':i[3],'d_failure':i[4],'d_solution':i[5],'d_status':i[6]}) return data else: return None
def create(self): now = datetime.datetime.now() - datetime.timedelta(hours=5) insert = conexion.Add( "insert into environments (img,name,key_code,creation_date,created_by) values (%s,%s,%s,%s,%s)", [self.img, self.name, self.key_code, now, self.created_by]) consult = conexion.search( "select id from environments where key_code = %s", [self.key_code]) insert2 = conexion.Add( "insert into bridge (id_user,id_environments,v_admin,linking_date,state) values (%s,%s,%s,%s,%s)", [self.created_by, consult[0][0], 1, now, 1])
def manage_search(self): data = {} data['manage'] = [] consult = conexion.search('select u.name, u.img,b.* from users u, environments e, bridge b where b.id_user = u.id and e.id = b.id_environments and e.id = %s and u.name like %s',[self.id, self.name]) print(consult) if(consult): for i in consult: data['manage'].append({'name':i[0],"img":i[1],'id':i[2],'v_admin':i[5],'linking_date':format(i[6]),'state':i[7]}) return data['manage'] return None
def manega_users_show(self): data = {} data['manage'] = [] consult = conexion.search("select u.name, u.img, b.* from users u, environments e, bridge b where b.id_user = u.id and e.id = b.id_environments and e.id = %s",[self.id]) if(consult): for i in consult: data['manage'].append({'name':i[0],"img":i[1] ,'id':i[2],'v_admin':i[5],'linking_date':format(i[6]),'state':i[7]}) return data['manage'] else: return None
def search_environment(self): data = {} data['environment'] = [] consult = conexion.search("select e.id,e.img,e.name,e.key_code,e.creation_date, u.name, b.id_user, b.v_admin from users u, environments e, bridge b where e.created_by = u.id and b.id_environments = e.id and b.id_user = %s and e.name like %s",[self.id,self.name]) if(consult): for i in consult: data['environment'].append({'id':i[0],'img':i[1],'name':i[2],'key_code':i[3],'creation_date':format(i[4]),'name_user':i[5],'admin':i[6]}) return data['environment'] else: return None
def count_device(self): data = {} data['device'] = [] result = conexion.search( 'select status, count(*) as amount from devices where environment = %s group by status', [self.enviroment]) if (result): for i in result: data['device'].append({"status": i[0], 'amount': i[1]}) return data['device'] return None
def show_clients(self): data = {} data['data'] = [] cmm = conexion.search("select identification,enviroment,name,surname,email from clients where enviroment = %s",[self.enviroment]) if(cmm): for i in cmm: data['data'].append({'identification':i[0],'enviroment':i[1],'name':i[2],'surname':i[3],'email':i[4]}) return data['data'] else: return None
def send_email_recover(self): respuesta = conexion.search( 'select name,email from users where email = %s', [self.email]) if respuesta: for i in respuesta: now = datetime.now() + timedelta(hours=24) resp = conexion.Add( 'update users set code=%s, recovery_date= %s where email = %s', [self.code, now, self.email]) return i[0] else: return None
def check_code(self): respuesta = conexion.search( 'select code, recovery_date from users where code = %s', [self.code]) if respuesta: for i in respuesta: now = datetime.now() if now.strftime('%Y %m %d %H %M %S') > i[1].strftime( '%Y %m %d %H %M %S'): return 'invalid' else: return 'valid' else: return None
def bring_profile(self): data = {} data['profile'] = [] consult = conexion.search( 'select name, surname, email, img from users where id = %s', [self.id]) if (consult): for i in consult: data['profile'].append({ 'name': i[0], 'surname': i[1], 'email': i[2], 'img': i[3] }) return data['profile'] else: return None
def get_data_my_dashboard(self): data = {} data['device'] = [] cmm = conexion.search( 'select id, type, model, brand, process_start_date from devices where user = %s and environment = %s and status = %s', [self.user, self.enviroment, self.status]) if (cmm): for i in cmm: data['device'].append({ 'id': i[0], 'type': i[1], 'model': i[2], 'brand': i[3], 'process_start_date': format(i[4]) }) return data['device'] return None
def get_info_repair(self): data = {} data['repair'] = [] cmm = conexion.search( "select id,environment,failure,diagnosis,solution from devices where (id = %s and environment = %s)", [self.id, self.enviroment]) if (cmm): for i in cmm: data['repair'].append({ 'id': i[0], 'environment': i[1], 'failure': i[2], 'diagnosis': i[3], 'solution': i[4] }) return data['repair'] return None
def check_repair(self): now = datetime.now() cmm = conexion.search( "select failure,diagnosis,solution from devices where (id = %s and environment = %s)", [self.id, self.enviroment]) if (self.status == 3): if (cmm[0][0] != None and cmm[0][1] != None and cmm[0][2] != None): if ((len(cmm[0][0]) != 0) and (len(cmm[0][1]) != 0) and (len(cmm[0][2]) != 0)): cod = conexion.Add( "update devices set status = %s,finish_date = %s where (id = %s and environment = %s)", [self.status, now, self.id, self.enviroment]) return "success" else: return 'error3' else: return 'error3'
def device_show_progress(self): data = {} data['device'] = [] cmm = conexion.search( 'select d.id, d.brand,d.model,d.type,d.process_start_date,u.name from users u, devices d where d.start_process = u.id and d.environment = %s and status = %s', [self.enviroment, self.status]) if (cmm): for i in cmm: data['device'].append({ 'id': i[0], 'brand': i[1], 'model': i[2], 'type': i[3], 'process_start_date': format(i[4]), 'start_process': i[5] }) return data['device'] return None
def get_device(self): data = {} data['device'] = [] cmm = conexion.search( 'select type, model, brand, serial, accessories, conditions, work_to_do from devices where id = %s', [self.id]) if (cmm): for i in cmm: data['device'].append({ 'type': i[0], 'model': i[1], 'brand': i[2], 'serial': i[3], 'accessories': i[4], 'conditions': i[5], 'work_to_do': i[6] }) return data['device'] return None
def show_device(self): data = {} data['device'] = [] cmm = conexion.search( "select d.id, d.brand,d.model,d.type,d.admission_date,u.name,c.name from users u, devices d, clients c where d.user = u.id and d.client = c.identification and d.environment = %s and status = %s order by d.admission_date asc", [self.enviroment, self.status]) if (cmm): for i in cmm: data['device'].append({ 'id': i[0], 'brand': i[1], 'model': i[2], 'type': i[3], 'admission_date': format(i[4]), 'user': i[5], 'customer_name': i[6] }) return data['device'] else: return None
def show_devices_finished(self): data = {} data['finished'] = [] consult = conexion.search( "select d.id, d.client, d.type, d.model, d.brand, d.finish_date, u.name from devices d, users u where d.start_process = u.id and d.environment = %s and d.status= %s", [self.enviroment, self.status]) if (consult): for i in consult: data['finished'].append({ 'id': i[0], 'client': i[1], 'type': i[2], 'model': i[3], 'brand': i[4], 'finish_date': format(i[5]), 'name': i[6] }) return data['finished'] else: return None
def client_info_device(self): data = {} data['info'] = [] consult = conexion.search( "select d.failure, d.diagnosis, d.solution, d.process_start_date, d.finish_date, d.departure_date, u.name from devices d, users u where d.start_process = u.id and d.id = %s and d.environment = %s", [self.id, self.enviroment]) if (consult): for i in consult: data['info'].append({ 'failure': i[0], 'diagnosis': i[1], 'solution': i[2], 'process_start_date': format(i[3]), 'finish_date': format(i[4]), 'departure_date': format(i[5]), 'name': i[6] }) return data['info'] else: return None
def manage_consult_environments(self, env): consult = conexion.search( "select name, key_code from environments where id = %s", [env]) return consult
def manage_consult_owner(self, own): consult = conexion.search("select name from users where id = %s", [own]) return consult[0][0]