def wa_sub_services_post():
if 'full_name' in session:
_entity = subServiceEntity()
_entity.id = request.form.get("idtxtSubService")
_id_service = request.form.get("iSlServicio")
_entity.full_name = request.form.get("idTxtFullName")
_entity.in_filter = request.form.get("idSlEnFiltro")
_status = request.form.get("idSlEstado")
if _entity.id is None:
serviceController().add_sub_service(_entity,_id_service,_status)
else:
serviceController().update_sub_service(_entity,_id_service,_status)
return redirect('/wa_list_sub_services')
else:
return redirect('/wa_login')
def get_sub_services(self, id_service):
_db = None
_status = 1
_data_row = []
_id_service = id_service
try:
_db = Database()
_db.connect(self.host, self.port, self.user, self.password,
self.database)
print('Se conecto a la bd')
_con_client = _db.get_client()
_sql = """SELECT ss.id,
s.full_name AS service,
ss.full_name sub_service,
ss.status,
ss.in_filter
FROM main.sub_service ss
INNER JOIN main.service s
ON ss.id_service = s.id
WHERE ss.id_service = CASE
WHEN %s = 0 THEN ss.id_service
ELSE %s
END
ORDER BY 1; """
_cur = _con_client.cursor()
_cur.execute(_sql, (
_id_service,
_id_service,
))
_rows = _cur.fetchall()
for row in _rows:
_entity = subServiceEntity()
_entity.id = row[0]
_entity.service = row[1]
_entity.full_name = row[2]
_entity.status = row[3]
_entity.in_filter = row[4]
_data_row.append(_entity)
_cur.close()
except (Exception) as e:
self.add_log(str(e), type(self).__name__)
finally:
if _db is not None:
_db.disconnect()
print("Se cerro la conexion")
return _data_row
def get_sub_services_by_id_service_and_id_user(self, id_service, id_user):
_db = None
_data = []
try:
_id_service = id_service
_id_user = id_user
_db = Database()
_db.connect(self.host, self.port, self.user, self.password,
self.database)
print('Se conecto a la bd')
_con_client = _db.get_client()
_sql = """SELECT ss.id,
ss.full_name,
uss.charge,
ss.id_service,
uss.id_user
FROM main.user_sub_service uss
INNER JOIN main.sub_service ss
ON uss.id_sub_service = ss.id
WHERE uss.id_service = %s
AND id_user = %s;"""
_cur = _con_client.cursor()
_cur.execute(_sql, (
_id_service,
_id_user,
))
_rows = _cur.fetchall()
for row in _rows:
_entity = subServiceEntity()
_entity.id = row[0]
_entity.full_name = row[1]
_entity.charge = row[2]
_data.append(_entity)
_cur.close()
except (Exception) as e:
self.add_log(str(e), type(self).__name__)
finally:
if _db is not None:
_db.disconnect()
print("Se cerro la conexion")
return _data
def get_sub_services_by_id(self, id_sub_service):
_db = None
_id_sub_service = id_sub_service
_entity = None
try:
_db = Database()
_db.connect(self.host, self.port, self.user, self.password,
self.database)
print('Se conecto a la bd')
_con_client = _db.get_client()
_sql = """SELECT ss.id,
ss.full_name AS sub_service,
s.id AS id_service,
s.full_name AS service,
ss.status,
ss.in_filter
FROM main.sub_service ss
INNER JOIN main.service s
ON ss.id_service = s.id
WHERE ss.id = %s;"""
_cur = _con_client.cursor()
_cur.execute(_sql, (_id_sub_service, ))
_rows = _cur.fetchall()
if len(_rows) >= 1:
_entity = subServiceEntity()
_entity.id = _rows[0][0]
_entity.full_name = _rows[0][1]
_entity.id_service = _rows[0][2]
_entity.service = _rows[0][3]
_entity.status = _rows[0][4]
_entity.in_filter = _rows[0][5]
_cur.close()
except (Exception) as e:
self.add_log(str(e), type(self).__name__)
finally:
if _db is not None:
_db.disconnect()
print("Se cerro la conexion")
return _entity
def get_services_by_user(self, id_user):
_db = None
_status = 1
_data_row = []
_id_user = id_user
try:
_db = Database()
_db.connect(self.host, self.port, self.user, self.password,
self.database)
print('Se conecto a la bd')
_con_client = _db.get_client()
_sql = """SELECT s.id,
s.full_name,
s.color,
Encode(s.file_image, 'base64') AS file_image,
b.enable,
ss.id AS id_sub_service,
ss.full_name AS full_name_sub_service,
c.charge,
c."enable" AS enable_sub_service
FROM main.service s
LEFT JOIN (SELECT s.id AS id_service,
us.enable
FROM main.service s
LEFT JOIN main.user_service us
ON s.id = us.id_service
WHERE us.id_user = %s) b
ON s.id = b.id_service
LEFT JOIN main.sub_service ss
ON s.id = ss.id_service
LEFT JOIN (SELECT uss.id_service,
uss.id_sub_service,
uss.charge,
uss."enable"
FROM main.user_sub_service uss
WHERE uss.id_user = %s) c
ON c.id_service = s.id
AND c.id_sub_service = ss.id
ORDER BY s.id; """
_cur = _con_client.cursor()
_cur.execute(_sql, (
_id_user,
_id_user,
))
_rows = _cur.fetchall()
_id_service_old = None
for row in _rows:
_serviceEntity = serviceEntity()
_serviceEntity.id = row[0]
_serviceEntity.full_name = row[1]
_serviceEntity.color = row[2]
_file_image = row[3]
if _file_image is None:
_serviceEntity.file_image = _file_image
else:
_serviceEntity.file_image = _file_image.replace('\n', '')
_enable = row[4]
if _enable is None:
_enable = 0
_serviceEntity.enable = _enable
_sub_service = row[5]
_sub_services = []
if _id_service_old != _serviceEntity.id:
for se in _rows:
if row[0] == se[0] and _sub_service is not None:
_subServiceEntity = subServiceEntity()
_subServiceEntity.id = se[5]
_subServiceEntity.full_name = se[6]
_subServiceEntity.charge = se[7]
_enable_s = se[8]
if _enable_s is None:
_enable_s = 0
_subServiceEntity.enable = _enable_s
_subServiceEntity.id_service = _serviceEntity.id
_sub_services.append(_subServiceEntity)
_serviceEntity.sub_services = _sub_services
_data_row.append(_serviceEntity)
_id_service_old = _serviceEntity.id
_cur.close()
except (Exception) as e:
print('error: ' + str(e))
finally:
if _db is not None:
_db.disconnect()
print("Se cerro la conexion")
return _data_row
def get_load(self, id_customer):
_db = None
_status = 1
_data_row = []
_data_documents = []
_data_users = []
_data_delivery_costs = []
_data_banks = []
_id_customer = id_customer
_loadEntity = None
try:
_loadEntity = loadEntity()
_db = Database()
_db.connect(self.host, self.port, self.user, self.password,
self.database)
print('Se conecto a la bd')
_con_client = _db.get_client()
_sql_services = """SELECT s.id,
s.full_name,
s.color,
encode(s.file_image , 'base64') AS file_image,
ss.id AS id_sub_service,
ss.full_name AS sub_service_name,
ss.in_filter
FROM main.service s
LEFT JOIN main.sub_service ss
ON s.id = ss.id_service
ORDER BY 1;"""
_cur = _con_client.cursor()
_cur.execute(_sql_services)
_rows = _cur.fetchall()
_id_service_old = None
for row in _rows:
_serviceEntity = serviceEntity()
_serviceEntity.id = row[0]
_serviceEntity.full_name = row[1]
_serviceEntity.color = row[2]
_serviceEntity.file_image = row[3].replace('\n', '')
_sub_service = row[4]
_sub_services = []
if _id_service_old != _serviceEntity.id:
for se in _rows:
if row[0] == se[0] and _sub_service is not None:
_subServiceEntity = subServiceEntity()
_subServiceEntity.id = se[4]
_subServiceEntity.full_name = se[5]
_subServiceEntity.in_filter = se[6]
_subServiceEntity.id_service = se[0]
_sub_services.append(_subServiceEntity)
_serviceEntity.sub_services = _sub_services
_data_row.append(_serviceEntity)
_id_service_old = _serviceEntity.id
_loadEntity.services = _data_row
_sql_services = """SELECT id, full_name FROM main.type_document WHERE status = 1;"""
_cur.execute(_sql_services)
_rows = _cur.fetchall()
for row in _rows:
_typeDocumentoEntity = typeDocumentEntity()
_typeDocumentoEntity.id = row[0]
_typeDocumentoEntity.full_name = row[1]
_data_documents.append(_typeDocumentoEntity)
_loadEntity.type_documents = _data_documents
_sql_delivery = """SELECT from_km, to_km, fixed_cost, delivery FROM main.delivery_cost;"""
_cur.execute(_sql_delivery)
_rows = _cur.fetchall()
for row in _rows:
_delivery_cost = deliveryCost()
_delivery_cost.from_km = row[0]
_delivery_cost.to_km = row[1]
_delivery_cost.fixed_cost = row[2]
_delivery_cost.delivery = row[3]
_data_delivery_costs.append(_delivery_cost)
_loadEntity.delivery_costs = _data_delivery_costs
_sql_banks = """SELECT id, full_name FROM main.bank WHERE status = 1;"""
_cur.execute(_sql_banks)
_rows = _cur.fetchall()
for row in _rows:
_bankEntity = bankEntity()
_bankEntity.id = row[0]
_bankEntity.full_name = row[1]
_data_banks.append(_bankEntity)
_loadEntity.banks = _data_banks
if (_id_customer != 0):
_sql_users = """SELECT u.id,
u.mail,
u.social_name,
u.full_name,
u.id_type_document,
u.document_number,
u.type_user,
u.photo,
u.cellphone,
u.about,
us.id AS id_user_store,
us.full_name AS name_user_store,
us.address,
us.longitude,
us.latitude,
us.main,
b.avg_rate::float4
FROM main.user_p u
INNER JOIN main.user_store us
ON u.id = us.id_user
INNER JOIN main.customer_user_favorite cf
ON u.id = cf.id_user
LEFT JOIN (SELECT cr.id_user,
Avg(rate) avg_rate,
Count(*) count_rate
FROM main.customer_rate cr
GROUP BY 1) b
ON u.id = b.id_user
WHERE cf.id_customer = %s and u.status = %s and cf."enable" = 1 AND us.status = 1
ORDER BY 1;"""
_cur.execute(_sql_users, (
_id_customer,
_status,
))
_rows = _cur.fetchall()
_id_user_old = None
for row in _rows:
_userEntity = userEntity()
_userEntity.id = row[0]
_userEntity.mail = row[1]
_userEntity.social_name = row[2]
_userEntity.full_name = row[3]
_userEntity.id_type_document = row[4]
_userEntity.document_number = row[5]
_userEntity.type_user = row[6]
_userEntity.photo = row[7]
_userEntity.cellphone = row[8]
_userEntity.about = row[9]
_avg_rate = row[16]
if _avg_rate is None:
_avg_rate = 0
_userEntity.avg_rate = _avg_rate
_user_stores = []
if _id_user_old != _userEntity.id:
for se in _rows:
if row[0] == se[0] and _userEntity is not None:
_userStoreEntity = userStoreEntity()
_userStoreEntity.id = se[10]
_userStoreEntity.full_name = se[11]
_userStoreEntity.address = se[12]
_userStoreEntity.longitude = se[13]
_userStoreEntity.latitude = se[14]
_userStoreEntity.main = se[15]
_userStoreEntity.id_user = _userEntity.id
_user_stores.append(_userStoreEntity)
_userEntity.user_store = _user_stores
_data_users.append(_userEntity)
_id_user_old = _userEntity.id
_loadEntity.preferred_users = _data_users
_cur.close()
except (Exception) as e:
self.add_log(str(e), type(self).__name__)
finally:
if _db is not None:
_db.disconnect()
print("Se cerro la conexion")
return _loadEntity