def insertNew(self, FilmItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
num = cursor.execute("select 1 from tf_film where website_id='%s' and website_film_id='%s' " % \
(FilmItem.websiteId,FilmItem.websiteFilmId))
if num > 0:
return
if FilmItem.grade.isdigit() == False:
FilmItem.grade = 0.0
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_film (film_name, website_id, website_film_id,grade, show_time, show_state, img, ver, actor, country, direct,film_type,init_date,clob,state) VALUES ('%s', '%s', '%s','%s','%s', '%s', '%s','%s', '%s','%s', '%s', '%s','%s','%s','%s')" % \
(FilmItem.filmName,FilmItem.websiteId,FilmItem.websiteFilmId,str(FilmItem.grade),
str(FilmItem.showTime),
str(FilmItem.showState),
FilmItem.img,
FilmItem.ver,
FilmItem.actor,
FilmItem.country,
FilmItem.direct,
FilmItem.filmType,
now,FilmItem.clob,FilmItem.state)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def deleteOneDay(self, dateInt, websiteId):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
num = cursor.execute("delete from tf_daily_film_cinema where website_id='%s' and date_no='%s' " % \
(websiteId,dateInt))
baseDao.commitCloseDb(db)
def updateArea(self,id,area):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "update tf_merge_cinema set area='%s' where id='%s'" % (area,id)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def updateFilmMergeId(self, id, mergeId):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "update tf_film set merge_id='%s' where id='%s' " % (mergeId, id)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def deleteFailed(self, dateNo, websiteId, logType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "delete from tf_daily_log where website_id='%s' and date_no='%s' and state in (-1,0) and log_type='%s'" % (
websiteId, dateNo, logType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def deleteByDateNo(self, dateNo, calcType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "delete from tf_calc where date_no='%s' and calc_type='%s' " % (
dateNo, calcType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def insert(self, MessageItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_message (date_no, content, ms_type,state,init_date) VALUES ('%s', '%s', '%s', '%s', '%s')" % \
(str(MessageItem.dateNo),str(MessageItem.content),str(MessageItem.msType),'1',now)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def hasDailyLog(self, dateNo, websiteId, logType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "select 1 from tf_daily_log where website_id='%s' and date_no='%s' and log_type='%s' " % (
websiteId, dateNo, logType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
return num > 0
def insert(self, CalcItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_calc (date_no, calc_type, website_id, merge_cinema_id, merge_film_id, init_date) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')" % \
(str(CalcItem.dateNo),str(CalcItem.calcType),str(CalcItem.websiteId),str(CalcItem.mergeCinemaId),str(CalcItem.mergeFilmId),now)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def insertStart(self, dateNo, websiteId, logType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_daily_log (date_no, website_id, start_date, state,log_type) VALUES ('%s', '%s', '%s', '%s','%s')" % \
(str(dateNo),str(websiteId),now,'0',logType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def updateEnd(self, dateNo, websiteId, state, msg, logType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
msg = msg.replace('\'', '\\\'')
sql = "update tf_daily_log set end_date='%s' ,state='%s',msg='%s' where date_no='%s' and website_id = '%s' and log_type = '%s'" % \
(now,str(state),msg,str(dateNo),str(websiteId),logType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def insertNew(self,CinemaMergeItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_merge_cinema (cinema_name, area, addr, state, init_date) VALUES ('%s', '%s', '%s', %s, '%s')" % \
(CinemaMergeItem.cinemaName,CinemaMergeItem.area,CinemaMergeItem.addr,str(CinemaMergeItem.state),now)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
return self.getByCinemaName(CinemaMergeItem.cinemaName)
def insertNew(self, FilmMergeItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sql = "INSERT INTO tf_merge_film (film_name, grade, show_time, show_state, img, film_type, ver, actor, country, direct, init_date,state) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');" % \
(FilmMergeItem.filmName,str(FilmMergeItem.grade),str(FilmMergeItem.showTime),str(FilmMergeItem.showState),FilmMergeItem.img,FilmMergeItem.filmType,FilmMergeItem.ver,FilmMergeItem.actor,FilmMergeItem.country,FilmMergeItem.direct,now,str(FilmMergeItem.state))
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
return self.getByFilmName(FilmMergeItem.filmName)
def insertNew(self, CinemaItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
num = cursor.execute("select 1 from tf_cinema where website_id='%s' and website_cinema_id='%s' " % \
(CinemaItem.websiteId,CinemaItem.websiteCinemaId))
if num > 0:
return
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
print("INSERT INTO tf_cinema (cinema_name, website_cinema_id, website_id, area, addr, state, init_date) VALUES ('%s', '%s', '%s', %s, '%s', '%s', '%s')" % \
(CinemaItem.cinemaName,str(CinemaItem.websiteCinemaId),str(CinemaItem.websiteId),CinemaItem.area,CinemaItem.addr,str(CinemaItem.state),now))
num = cursor.execute("INSERT INTO tf_cinema (cinema_name, website_cinema_id, website_id, area, addr, state, init_date) VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
(CinemaItem.cinemaName,CinemaItem.websiteCinemaId,CinemaItem.websiteId,str(CinemaItem.area),CinemaItem.addr,CinemaItem.state,now))
baseDao.commitCloseDb(db)
def insert(self, DailyFilmCinemaItem):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = db.cursor()
now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
try:
DailyFilmCinemaItem.grade = float(DailyFilmCinemaItem.grade)
except:
DailyFilmCinemaItem.grade = 0.0
sql = "INSERT INTO tf_daily_film_cinema (website_id, website_film_id, website_cinema_id, grade, date_no, show_state, price, init_date,show_cinema_num,show_round_num) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s','%s', '%s', '%s')" % \
(str(DailyFilmCinemaItem.websiteId),str(DailyFilmCinemaItem.websiteFilmId),str(DailyFilmCinemaItem.websiteCinemaId),str(DailyFilmCinemaItem.grade),str(DailyFilmCinemaItem.dateNo),str(DailyFilmCinemaItem.showState),str(DailyFilmCinemaItem.price),now,str(DailyFilmCinemaItem.showCinemaNum),str(DailyFilmCinemaItem.showRoundNum))
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def getNoMergeFilm(self):
filmItems = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
num = cursor.execute(
"select * from tf_film where merge_id is null and state=1 ")
rows = cursor.fetchall()
for row in rows:
filmItem = FilmItem()
filmItem.id = row['id']
filmItem.filmName = row['film_name']
filmItem.websiteId = row['website_id']
filmItem.websiteFilmId = row['website_film_id']
filmItem.grade = row['grade']
filmItem.showTime = row['show_time']
filmItem.showState = row['show_state']
filmItem.img = row['img']
filmItem.filmType = row['film_type']
filmItem.ver = row['ver']
filmItem.actor = row['actor']
filmItem.country = row['country']
filmItem.direct = row['direct']
filmItem.initDate = row['init_date']
filmItem.clob = row['clob']
filmItem.duration = row['duration']
filmItem.mergeId = row['merge_id']
filmItems.append(filmItem)
baseDao.commitCloseDb(db)
return filmItems
def getByWebsiteFilmId(self, websiteFilmId):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
num = cursor.execute(
"select * from tf_film where website_film_id = '%s' ",
websiteFilmId)
rows = cursor.fetchall()
row = rows[0]
filmItem = FilmItem()
filmItem.id = row['id']
filmItem.filmName = row['film_name']
filmItem.websiteId = row['website_id']
filmItem.websiteFilmId = row['website_film_id']
filmItem.grade = row['grade']
filmItem.showTime = row['show_time']
filmItem.showState = row['show_state']
filmItem.img = row['img']
filmItem.filmType = row['film_type']
filmItem.ver = row['ver']
filmItem.actor = row['actor']
filmItem.country = row['country']
filmItem.direct = row['direct']
filmItem.initDate = row['init_date']
filmItem.clob = row['clob']
filmItem.duration = row['duration']
filmItem.mergeId = row['merge_id']
baseDao.commitCloseDb(db)
return filmItem
def getByFilmName(self, filmName):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
num = cursor.execute(
"select * from tf_merge_film where film_name ='%s' " % (filmName))
if (num == 0):
return None
rows = cursor.fetchall()
row = rows[0]
filmMergeItem = FilmMergeItem()
filmMergeItem.id = row['id']
filmMergeItem.filmName = row['film_name']
filmMergeItem.grade = row['grade']
filmMergeItem.showTime = row['show_time']
filmMergeItem.showState = row['show_state']
filmMergeItem.img = row['img']
filmMergeItem.filmType = row['film_type']
filmMergeItem.ver = row['ver']
filmMergeItem.actor = row['actor']
filmMergeItem.country = row['country']
filmMergeItem.direct = row['direct']
filmMergeItem.initDate = row['init_date']
filmMergeItem.duration = row['duration']
filmMergeItem.state = row['state']
baseDao.commitCloseDb(db)
return filmMergeItem
def deleteOneDay(self, dateNo, msType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "delete from tf_message where date_no='%s' and ms_type='%s'" % (
dateNo, msType)
print(sql)
num = cursor.execute(sql)
baseDao.commitCloseDb(db)
def getPersonName(self, sendType):
personNames = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "select * from tf_message_send where state=1 and send_type='%s' order by id desc" % (
sendType)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
personNames.append(row['person_name'])
baseDao.commitCloseDb(db)
return personNames
def getByCinemaName(self,cinemaName):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
num = cursor.execute("select * from tf_merge_cinema where cinema_name ='%s' " % (cinemaName))
if(num == 0):
return None
rows = cursor.fetchall()
row = rows[0]
cinemaMergeItem = CinemaMergeItem()
cinemaMergeItem.id = row['id']
cinemaMergeItem.cinemaName = row['cinema_name']
cinemaMergeItem.area = row['area']
cinemaMergeItem.addr = row['addr']
cinemaMergeItem.state = row['state']
baseDao.commitCloseDb(db)
return cinemaMergeItem
def getGradeHiFilm(self, dateNo):
mergeFilmIds = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "select distinct(b.merge_id) merge_film_id from \
tf_daily_film_cinema a left join tf_film b on a.website_film_id=b.website_film_id \
left join tf_merge_film c on b.merge_id=c.id \
where a.date_no='%s' \
order by a.grade desc limit 3" % (dateNo)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
mergeFilmIds.append(row['merge_film_id'])
baseDao.commitCloseDb(db)
return mergeFilmIds
def getMostRoundFilms(self, dateNo):
mergerFilmIds = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "select b.merge_id merge_id\
from tf_daily_film_round a left join tf_film b on a.website_film_id=b.website_film_id \
left join tf_merge_film c on b.merge_id = c.id \
where a.date_no='%s' \
group by b.merge_id \
order by (a.show_round_num) desc limit 3" % (dateNo)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
mergerFilmIds.append(row['merge_id'])
baseDao.commitCloseDb(db)
return mergerFilmIds
def doSelect(self,sql):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
cinemaMergeItems = []
for row in rows:
cinemaMergeItem = CinemaMergeItem()
cinemaMergeItem.id = row['id']
cinemaMergeItem.cinemaName = row['cinema_name']
cinemaMergeItem.area = row['area']
cinemaMergeItem.addr = row['addr']
cinemaMergeItem.state = row['state']
cinemaMergeItems.append(cinemaMergeItem)
baseDao.commitCloseDb(db)
return cinemaMergeItems
def doSelect(self, sql):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
items = []
for row in rows:
item = MessageItem()
item.id = row['id']
item.dateNo = row['date_no']
item.msType = row['ms_type']
item.content = row['content']
item.state = row['state']
item.initDate = row['init_date']
items.append(item)
baseDao.commitCloseDb(db)
return items
def doSelect(self, sql):
items = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
item = CalcItem()
item.id = row['id']
item.dateNo = row['date_no']
item.calcType = row['calc_type']
item.websiteId = row['website_id']
item.websiteCinemaId = row['website_cinema_id']
item.websiteFilmId = row['website_film_id']
item.initDate = row['init_date']
items.append(item)
baseDao.commitCloseDb(db)
return items
def getCheapCimane(self, filmMergeId, dateNo):
calcItems = []
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "select a.website_id website_id, b.merge_id cinema_merge_id,d.merge_id film_merge_id \
from tf_daily_film_cinema a left join tf_cinema b on a.website_cinema_id = b.website_cinema_id \
left join tf_film d on a.website_film_id = d.website_film_id left join tf_merge_cinema f on b.merge_id=f.id \
where d.merge_id='%s' and a.date_no='%s' and f.area in ('红桥区','南开区') \
order by a.price limit 1" % (str(filmMergeId), str(dateNo))
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
for row in rows:
calcItem = CalcItem()
calcItem.dateNo = dateNo
calcItem.websiteId = row['website_id']
calcItem.mergeCinemaId = row['cinema_merge_id']
calcItem.mergeFilmId = row['film_merge_id']
calcItems.append(calcItem)
baseDao.commitCloseDb(db)
return calcItems
def getCalcMessage(self, dateNo, calcType):
baseDao = BaseDao()
db = baseDao.getDB()
# 使用cursor()方法获取操作游标
cursor = baseDao.getDictCursor(db)
sql = "select b.film_name,c.cinema_name,d.website_name,b.film_type,b.actor \
from tf_calc a left join tf_merge_film b on a.merge_film_id=b.id left join tf_merge_cinema c on a.merge_cinema_id=c.id \
left join tf_website d on a.website_id=d.id \
where a.date_no='%s' and a.calc_type='%s'" % (dateNo, calcType)
print(sql)
num = cursor.execute(sql)
rows = cursor.fetchall()
items = []
for row in rows:
messageContentItem = MessageContentItem()
messageContentItem.filmName = row['film_name']
messageContentItem.cinemaName = row['cinema_name']
messageContentItem.websiteName = row['website_name']
messageContentItem.filmType = row['film_type']
messageContentItem.actor = row['actor']
items.append(messageContentItem)
baseDao.commitCloseDb(db)
return items