class User():
"""
User table
"""
def __init__(self):
self.db = DbConn()
def select(self, sql, args=None):
return self.db.select(sql, args)
def execute(self, sql, args=None):
return self.db.execute(sql, args)
def executemany(self, sql, args=None):
return self.db.executemany(sql, args)
def insert_user(self, user_id, password, user_name, sex, birth, job,
company):
sql = f"""
INSERT INTO user (user_id, password, user_name, sex, birth, job, company)
VALUES ('{user_id}', '{password}', '{user_name}', {sex}, STR_TO_DATE('{birth}', '%Y-%m-%d'), '{job}', '{company}')
"""
return self.db.execute(sql)
def idcheck(self, user_id):
sql = f"select * from user where user_id = '{user_id}'"
return self.db.select(sql)
def login_go(self, user_id, password):
sql = f"select user_idx,user_id,user_name,sex,birth,job,company from user where user_id ='{user_id}' and password='******'"
return self.db.select(sql)
# def logout(self,sessionid):
# sql =f"delet from django_session where session_key='{sessionid}'"
# return self.db.execute(sql)
def findpw(self, user_id, user_name):
sql = f"select * from user where user_id = '{user_id}' and user_name='{user_name}'"
return self.db.select(sql)
def modifypw_go(self, password, user_id, user_name):
sql = f"update user set password = '******' where user_id='{user_id}' and user_name='{user_name}'"
# print(sql)
return self.db.execute(sql)
def __init__(self):
self.db = DbConn()
class ColorList():
"""
ColorList table
"""
def __init__(self):
self.db = DbConn()
def select(self, sql, args=None):
return self.db.select(sql, args)
def execute(self, sql, args=None):
return self.db.execute(sql, args)
def executemany(self, sql, args=None):
return self.db.executemany(sql, args)
def select_all(self, start=None, end=None):
limit_sql = ""
if start is not None:
limit_sql = "limit " + str(start)
if end is not None:
limit_sql = limit_sql + ', ' + str(end)
sql = f"""
select * from color_list {limit_sql}
"""
return self.db.select(sql)
def insert_color(self, user_idx, path, color_pick):
color_pick = json.loads(color_pick)
sql = f"""
insert into color_list(user_idx, h1,s1,v1,h2,s2,v2,h3,s3,v3,h4,s4,v4, color, cp, cw, season, value, hex1, hex2, hex3, hex4, origin_url)
values ({user_idx},
'{color_pick['h1']}', '{color_pick['s1']}', '{color_pick['v1']}',
'{color_pick['h2']}', '{color_pick['s2']}', '{color_pick['v2']}',
'{color_pick['h3']}', '{color_pick['s3']}', '{color_pick['v3']}',
'{color_pick['h4']}', '{color_pick['s4']}', '{color_pick['v4']}',
'{color_pick['color']}', '{color_pick['cp']}', '{color_pick['cw']}', '{color_pick['season']}', '{color_pick['value']}',
'{color_pick['hex1']}', '{color_pick['hex2']}', '{color_pick['hex3']}', '{color_pick['hex4']}', '{path}'
)
"""
return self.db.execute(sql)
def list_filter(self,
color_type=None,
season_type=None,
cw_type=None,
cp_type=None,
value_type=None,
limit_start=0,
limit_end=100,
user_idx=None):
# sql = "select * from color_list"
sql = '''
select a.*, (
select count(*) like_btn
from like_btn
where cl_idx = a.cl_idx
) as cnt, (
select user_name from user where user_idx = a.user_idx
) as user_name
'''
if user_idx != None:
sql += f'''
, if(
(select count(*) from like_btn where user_idx = {user_idx} AND cl_idx = a.cl_idx) > 0,
'T', 'F') as mylike
'''
sql += ''' from
color_list as a'''
where = ""
if color_type or season_type or cw_type or cp_type or value_type:
where += " where "
where_value = ""
if color_type:
if len(where_value) > 0: where_value += " and "
where_value += "color in ("
for index, val in enumerate(color_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if season_type:
if len(where_value) > 0: where_value += " and "
where_value += "season in ("
for index, val in enumerate(season_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if cw_type:
if len(where_value) > 0: where_value += " and "
where_value += "cw in ("
for index, val in enumerate(cw_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if cp_type:
if len(where_value) > 0: where_value += " and "
where_value += "cp in ("
for index, val in enumerate(cp_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if value_type:
if len(where_value) > 0: where_value += " and "
where_value += "value in ("
for index, val in enumerate(value_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
where = where + where_value
sql = sql + where + f" order by create_date desc limit {limit_start}, {limit_end};"
return self.db.select(sql)
def get_user_like(self, cl_idx, user_idx):
return self.db.select(
f"select * from like_btn where cl_idx = {cl_idx} and user_idx = {user_idx};"
)
def set_like(self, cl_idx, user_idx):
return self.db.execute(
f"insert into like_btn(cl_idx, user_idx) values({cl_idx}, {user_idx});"
)
def drop_like(self, cl_idx, user_idx):
return self.db.execute(
f"delete from like_btn where cl_idx = {cl_idx} and user_idx = {user_idx};"
)
class GallaryList():
"""
GallaryList table
"""
def __init__(self):
self.db = DbConn()
def select(self, sql, args=None):
return self.db.select(sql, args)
def execute(self, sql, args=None):
return self.db.execute(sql, args)
def executemany(self, sql, args=None):
return self.db.executemany(sql, args)
def select_all(self, start=None, end=None):
limit_sql = ""
if start is not None:
limit_sql = "limit " + str(start)
if end is not None:
limit_sql = limit_sql + ', ' + str(end)
sql = f"""
select * from gallary_list {limit_sql}
"""
return self.db.select(sql)
def insert_mp_info(self, user_idx, image_name, origin_url, masterpiece_url,
artist):
sql = f"insert into gallary_list(user_idx, image_name, origin_url, masterpiece_url, artist) values({user_idx}, '{image_name}', '{origin_url}', '{masterpiece_url}', '{artist}');"
return self.db.execute(sql)
def insert_mp_info2(self, user_idx, image_name, origin_url,
masterpiece_url, hex1, hex2, hex3, hex4):
sql = f"insert into gallary_list(user_idx, image_name, origin_url, masterpiece_url, hex1,hex2,hex3,hex4, artist) values({user_idx}, '{image_name}', '{origin_url}', '{masterpiece_url}', '{hex1}', '{hex2}', '{hex3}', '{hex4}', 'color');"
# 아티스트 부분은 color 로 입력
return self.db.execute(sql)
def image_filter(self, opt_type, user_idx):
sql = '''select
a.*,
(select
count(*) like_btn
from
like_btn
where
gl_idx = a.gl_idx) as cnt'''
if user_idx != None:
sql += f''', if(
(select count(*) from like_btn where user_idx = {user_idx} AND gl_idx = a.gl_idx) > 0,
'T',
'F') as mylike
'''
sql += ''' , (SELECT user_name FROM user WHERE user_idx = a.user_idx) AS user_name from
gallary_list as a'''
where = ""
if opt_type:
where += " where "
where_value = ""
if len(where_value) > 0: where_value += " and "
where_value += "artist in ("
for index, val in enumerate(opt_type.split(',')):
if index == 0:
if val == "cd": where_value += "'color'"
else: where_value += "'" + val + "'"
else:
if val == "cd": where_value += ", 'color'"
else: where_value += ", '" + val + "'"
where_value += ")"
where = where + where_value
sql = sql + where + " order by create_date desc limit 100;"
return self.db.select(sql)
def get_user_like(self, gl_idx, user_idx):
return self.db.select(
f"select * from like_btn where gl_idx = {gl_idx} and user_idx = {user_idx};"
)
def set_like(self, gl_idx, user_idx):
return self.db.execute(
f"insert into like_btn(gl_idx, user_idx) values({gl_idx}, {user_idx});"
)
def drop_like(self, gl_idx, user_idx):
return self.db.execute(
f"delete from like_btn where gl_idx = {gl_idx} and user_idx = {user_idx};"
)
class ColorPallate():
"""
ColorPallate table
"""
def __init__(self):
self.db = DbConn()
def select(self, sql, args=None):
return self.db.select(sql, args)
def execute(self, sql, args=None):
return self.db.execute(sql, args)
def executemany(self, sql, args=None):
return self.db.executemany(sql, args)
def select_all(self, start=None, end=None):
limit_sql = ""
if start is not None:
limit_sql = "limit " + str(start)
if end is not None:
limit_sql = limit_sql + ', ' + str(end)
sql = f"""
select * from color_pallate {limit_sql}
"""
return self.db.select(sql)
def emotion_filter(self,
color_type=None,
season_type=None,
cw_type=None,
cp_type=None,
value_type=None,
limit_start=0,
limit_end=100):
sql = "select * from color_pallate"
where = ""
if color_type or season_type or cw_type or cp_type or value_type:
where += " where "
where_value = ""
if color_type:
if len(where_value) > 0: where_value += " and "
where_value += "color in ("
for index, val in enumerate(color_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if season_type:
if len(where_value) > 0: where_value += " and "
where_value += "season in ("
for index, val in enumerate(season_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if cw_type:
if len(where_value) > 0: where_value += " and "
where_value += "cw in ("
for index, val in enumerate(cw_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if cp_type:
if len(where_value) > 0: where_value += " and "
where_value += "cp in ("
for index, val in enumerate(cp_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
if value_type:
if len(where_value) > 0: where_value += " and "
where_value += "value in ("
for index, val in enumerate(value_type.split(',')):
if index == 0:
where_value += "'" + val + "'"
else:
where_value += ", '" + val + "'"
where_value += ")"
where = where + where_value
sql = sql + where + f" limit {limit_start}, {limit_end};"
return self.db.select(sql)