def historystats():
data_providers = database.execute(
"SELECT DISTINCT provider FROM table_history WHERE provider IS NOT null "
"UNION SELECT DISTINCT provider FROM table_history_movie WHERE provider "
"IS NOT null")
data_providers_list = []
for item in data_providers:
data_providers_list.append(item['provider'])
data_languages = database.execute(
"SELECT DISTINCT language FROM table_history WHERE language IS NOT null "
"AND language != '' UNION SELECT DISTINCT language FROM table_history_movie "
"WHERE language IS NOT null AND language != ''")
data_languages_list = []
for item in data_languages:
splitted_lang = item['language'].split(':')
item = {
"name": language_from_alpha2(splitted_lang[0]),
"code2": splitted_lang[0],
"code3": alpha3_from_alpha2(splitted_lang[0]),
"forced": True if len(splitted_lang) > 1 else False
}
data_languages_list.append(item)
return render_template('historystats.html',
data_providers=data_providers_list,
data_languages=sorted(data_languages_list,
key=lambda i: i['name']))
def confirmdelievery(saon):
try:
cuna=database.query("select cuna from saoncustomertable where saon=%s",saon)[0]['cuna']
ds=getonetobedealwithtable(saon)
l=len(ds)
i=0
while i<l:
#ds.ix[i]['banu'] ds.ix[i]['quan'] ds.ix[i]['wana']
sqld="delete from stocktable where banu=%s and wana=%s"
sqlu="update stocktable set quan=%s where banu=%s and wana=%s"
sqlq="select quan from stocktable where banu=%s and wana=%s"
sqli="insert into delieverytable (saon,cuna,mana,banu,exda,past,quan,wana,sape,mape,sapr,owda,insf) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
try:
cq=database.query(sqlq,ds.ix[i]['banu'],ds.ix[i]['wana'])[0]['quan']
if cq==0:
database.execute(sqld,ds.ix[i]['banu'],ds.ix[i]['wana'])
else:
pass
except:
pass
###write to delieverytable
database.execute(sqli,saon,cuna,ds.ix[i]['mana'],ds.ix[i]['banu'],ds.ix[i]['exda'],ds.ix[i]['past'],ds.ix[i]['quan'],ds.ix[i]['wana'],ds.ix[i]['sape'],ds.ix[i]['mape'],ds.ix[i]['sapr'],strtoday(),ds.ix[i]['insf'])
i=i+1
return True
except:
return False
def update_notifier():
# define apprise object
a = apprise.Apprise()
# Retrieve all of the details
results = a.details()
notifiers_new = []
notifiers_old = []
notifiers_current_db = database.execute(
"SELECT name FROM table_settings_notifier")
notifiers_current = []
for notifier in notifiers_current_db:
notifiers_current.append(notifier['name'])
for x in results['schemas']:
if x['service_name'] not in notifiers_current:
notifiers_new.append(x['service_name'])
logging.debug('Adding new notifier agent: ' + x['service_name'])
else:
notifiers_old.append(x['service_name'])
notifier_current = [i for i in notifiers_current]
notifiers_to_delete = list(set(notifier_current) - set(notifiers_old))
for notifier_new in notifiers_new:
database.execute(
"INSERT INTO table_settings_notifier (name, enabled) VALUES (?, ?)",
(notifier_new, 0))
for notifier_to_delete in notifiers_to_delete:
database.execute("DELETE FROM table_settings_notifier WHERE name=?",
(notifier_to_delete, ))
def history_log(action, sonarr_series_id, sonarr_episode_id, description, video_path=None, language=None, provider=None,
score=None, subs_id=None, subtitles_path=None):
database.execute("INSERT INTO table_history (action, sonarrSeriesId, sonarrEpisodeId, timestamp, description,"
"video_path, language, provider, score, subs_id, subtitles_path) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
(action, sonarr_series_id, sonarr_episode_id, time.time(), description, video_path, language,
provider, score, subs_id, subtitles_path))
event_stream(type='episodeHistory')
def update_notifier():
# define apprise object
a = apprise.Apprise()
# Retrieve all of the details
results = a.details()
notifiers_new = []
notifiers_old = []
notifiers_current_db = database.execute("SELECT name FROM table_settings_notifier")
notifiers_current = []
for notifier in notifiers_current_db:
notifiers_current.append([notifier['name']])
for x in results['schemas']:
if [x['service_name']] not in notifiers_current:
notifiers_new.append([x['service_name'], 0])
logging.debug('Adding new notifier agent: ' + x['service_name'])
else:
notifiers_old.append([x['service_name']])
notifiers_to_delete = [item for item in notifiers_current if item not in notifiers_old]
database.execute("INSERT INTO table_settings_notifier (name, enabled) VALUES (?, ?)", notifiers_new, execute_many=True)
database.execute("DELETE FROM table_settings_notifier WHERE name=?", notifiers_to_delete, execute_many=True)
def post(self):
saon=self.get_argument('saon')
cuna=self.get_argument('cuna')
wana=self.get_argument('wana')
sape=self.get_argument("sape")
mape=self.get_argument("mape")
sql="insert into saoncustomertable (saon,cuna,date) values (%s,%s,%s)"
sqlc="select saon from saoncustomertable where saon=%s"
sqlcs="select * from customerinfotable where cuna=%s"
recs=database.query(sqlcs,cuna)
if recs:
if not len(saon)==0:
try:
rec=database.query(sqlc,saon)
if len(rec)>0:
self.write("<center><h1>出货文件号重复。</h1></center>")
elif len(rec)==0:
database.execute(sql,saon,cuna,strtoday())
self.render("static/saoncustomerc.html",saon=saon,wana=wana,sape=sape,mape=mape)
except:
self.render("static/msg.html",msg="数据库连结失败。请联系管理员。")
else:
self.render("static/msg.html",msg="请输入正确的出货文件号")
else:self.render("static/msg.html",msg="没有这个客户")
def blacklist_log(sonarr_series_id, sonarr_episode_id, provider, subs_id,
language):
database.execute(
"INSERT INTO table_blacklist (sonarr_series_id, sonarr_episode_id, timestamp, provider, "
"subs_id, language) VALUES (?,?,?,?,?,?)",
(sonarr_series_id, sonarr_episode_id, time.time(), provider, subs_id,
language))
event_stream(type='episodeBlacklist')
def create_languages_dict():
global languages_dict
#replace chinese by chinese simplified
database.execute(
"UPDATE table_settings_languages SET name = 'Chinese Simplified' WHERE code3 = 'zho'"
)
languages_dict = database.execute(
"SELECT name, code2, code3, code3b FROM table_settings_languages")
def updated(restart=True):
if settings.general.getboolean('update_restart') and restart:
try:
from main import restart
restart()
except:
logging.info('BAZARR Restart failed, please restart Bazarr manualy')
updated(restart=False)
else:
database.execute("UPDATE system SET updated='1'")
def get_blacklist(media_type):
if media_type == 'series':
blacklist_db = database.execute("SELECT provider, subs_id FROM table_blacklist")
else:
blacklist_db = database.execute("SELECT provider, subs_id FROM table_blacklist_movie")
blacklist_list = []
for item in blacklist_db:
blacklist_list.append((item['provider'], item['subs_id']))
return blacklist_list
def history_log_movie(action,
radarrId,
description,
video_path=None,
language=None,
provider=None,
score=None,
forced=False):
database.execute(
"INSERT INTO table_history_movie (action, radarrId, timestamp, description, video_path, language, "
"provider, score) VALUES (?,?,?,?,?,?,?,?)",
(action, radarrId, time.time(), description, video_path, language,
provider, score))
def inorupdatestock(dfe):
ewda=function.strtoday()
sqli="insert into stocktable (wana,mana,banu,exda,past,quan,pagw,ewda,pana) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sqlu="update stocktable set quan=%s where banu=%s and wana=%s"
sqlq="select * from stocktable where banu=%s and wana=%s"
pagw=findpagw(dfe.banu,dfe.past)
rs=database.query(sqlq,dfe.banu,dfe.wana)
if len(rs)>0:
nwquan=getnewwanaquan(dfe.banu,dfe.wana)
squan=float(nwquan)+float(dfe.quan)
database.execute(sqlu,squan,dfe.banu,dfe.wana)
else:
database.execute(sqli,dfe.wana,dfe.mana,dfe.banu,dfe.exda,dfe.past,dfe.quan,pagw,ewda,"公斤/桶".decode('utf8'))
def prereducestock(d): if len(d)>0: df=d.reset_index(drop=True) i=0 while i<len(d): sqlr='select quan from stocktable where banu=%s and wana=%s' sql='update stocktable set quan= %s where banu = %s and wana = %s' rquan=database.query(sqlr,df.ix[i]["banu"],df.ix[i]["wana"]) equan=rquan[0]["quan"]-df.ix[i]["quan"] database.execute(sql,equan,df.ix[i]["banu"],df.ix[i]["wana"]) i=i+1 return True else: return False
def history_log(action,
sonarrSeriesId,
sonarrEpisodeId,
description,
video_path=None,
language=None,
provider=None,
score=None,
forced=False):
database.execute(
"INSERT INTO table_history (action, sonarrSeriesId, sonarrEpisodeId, timestamp, description,"
"video_path, language, provider, score) VALUES (?,?,?,?,?,?,?,?,?)",
(action, sonarrSeriesId, sonarrEpisodeId, time.time(), description,
video_path, language, provider, score))
def load_language_in_db():
# Get languages list in langs tuple
langs = [[lang.alpha_3, lang.alpha_2, lang.name]
for lang in pycountry.languages if hasattr(lang, 'alpha_2')]
# Insert languages in database table
database.execute(
"INSERT OR IGNORE INTO table_settings_languages (code3, code2, name) VALUES (?, ?, ?)",
langs,
execute_many=True)
database.execute(
"INSERT OR IGNORE INTO table_settings_languages (code3, code2, name) "
"VALUES ('pob', 'pb', 'Brazilian Portuguese')")
database.execute(
"INSERT OR IGNORE INTO table_settings_languages (code3, code2, name) "
"VALUES ('zht', 'zt', 'Chinese Traditional')")
langs = [[lang.bibliographic, lang.alpha_3] for lang in pycountry.languages
if hasattr(lang, 'alpha_2') and hasattr(lang, 'bibliographic')]
# Update languages in database table
database.execute(
"UPDATE table_settings_languages SET code3b=? WHERE code3=?",
langs,
execute_many=True)
# Create languages dictionary for faster conversion than calling database
create_languages_dict()
def history_log(action,
sonarr_series_id,
sonarr_episode_id,
description,
video_path=None,
language=None,
provider=None,
score=None):
from database import database
database.execute(
"INSERT INTO table_history (action, sonarrSeriesId, sonarrEpisodeId, timestamp, description,"
"video_path, language, provider, score) VALUES (?,?,?,?,?,?,?,?,?)",
(action, sonarr_series_id, sonarr_episode_id, time.time(), description,
video_path, language, provider, score))
def updated(restart=True):
if settings.general.getboolean('update_restart') and restart:
try:
requests.get('http://127.0.0.1:' + settings.general.port +
settings.general.base_url + 'restart')
except requests.ConnectionError:
pass
except (requests.ConnectTimeout, requests.HTTPError,
requests.ReadTimeout, requests.Timeout):
logging.info(
'BAZARR Restart failed, please restart Bazarr manually')
updated(restart=False)
else:
database.execute("UPDATE system SET updated='1'")
def history_log_movie(action,
radarr_id,
description,
video_path=None,
language=None,
provider=None,
score=None,
subs_id=None,
subtitles_path=None):
database.execute(
"INSERT INTO table_history_movie (action, radarrId, timestamp, description, video_path, language, "
"provider, score, subs_id, subtitles_path) VALUES (?,?,?,?,?,?,?,?,?,?)",
(action, radarr_id, time.time(), description, video_path, language,
provider, score, subs_id, subtitles_path))
event_stream(type='movieHistory')
def movies_full_scan_subtitles():
movies = database.execute("SELECT path FROM table_movies")
for i, movie in enumerate(movies, 1):
store_subtitles_movie(movie['path'], path_mappings.path_replace_movie(movie['path']))
gc.collect()
def get_episode_name(sonarrEpisodeId):
data = database.execute(
"SELECT title, season, episode FROM table_episodes WHERE sonarrEpisodeId=?",
(sonarrEpisodeId, ),
only_one=True)
return data['title'], data['season'], data['episode']
def get_series_name(sonarrSeriesId):
data = database.execute(
"SELECT title FROM table_shows WHERE sonarrSeriesId=?",
(sonarrSeriesId, ),
only_one=True)
return data['title'] or None
def catch_all(path):
auth = True
if settings.auth.type == 'basic':
auth = request.authorization
if not (auth and check_credentials(request.authorization.username,
request.authorization.password)):
return ('Unauthorized', 401, {
'WWW-Authenticate': 'Basic realm="Login Required"'
})
elif settings.auth.type == 'form':
if 'logged_in' not in session:
auth = False
try:
updated = database.execute("SELECT updated FROM system",
only_one=True)['updated']
except:
updated = False
inject = dict()
inject["baseUrl"] = base_url
inject["canUpdate"] = not args.no_update
inject["hasUpdate"] = updated != '0'
if auth:
inject["apiKey"] = settings.auth.apikey
template_url = base_url
if not template_url.endswith("/"):
template_url += "/"
return render_template("index.html",
BAZARR_SERVER_INJECT=inject,
baseUrl=template_url)
def series_full_scan_subtitles():
episodes = database.execute("SELECT path FROM table_episodes")
for i, episode in enumerate(episodes, 1):
store_subtitles(episode['path'], path_mappings.path_replace(episode['path']))
gc.collect()
def series_scan_subtitles(no):
episodes = database.execute(
"SELECT path FROM table_episodes WHERE sonarrSeriesId=? ORDER BY sonarrEpisodeId",
(no, ))
for episode in episodes:
store_subtitles(episode['path'],
path_mappings.path_replace(episode['path']))
def movies_scan_subtitles(no):
movies = database.execute(
"SELECT path FROM table_movies WHERE radarrId=? ORDER BY radarrId",
(no, ))
for movie in movies:
store_subtitles_movie(movie['path'],
path_mappings.path_replace_movie(movie['path']))
def template_variable_processor():
restart_required = database.execute(
"SELECT configured, updated FROM system", only_one=True)
return dict(restart_required=restart_required['configured'],
update_required=restart_required['updated'],
settings=settings,
args=args)
def api_movies_wanted():
data = database.execute("SELECT table_movies.title, table_movies.missing_subtitles FROM table_movies "
"WHERE table_movies.missing_subtitles != '[]' ORDER BY table_movies._rowid_ DESC LIMIT 10")
wanted_subs = []
for item in data:
wanted_subs.append([item['title'], item['missing_subtitles']])
return dict(subtitles=wanted_subs)
def template_variable_processor():
updated = None
try:
updated = database.execute("SELECT updated FROM system",
only_one=True)['updated']
except:
pass
finally:
return dict(settings=settings, args=args, updated=updated)
def save(self, elements, propertiesValues = {}):
if elements:
if isinstance(elements, list):
for element in elements:
self.save(element, propertiesValues)
else:
if propertiesValues:
fieldsToBeStored = self.propertiesMapping.copy()
fieldsToBeStored.update(propertiesValues)
else:
fieldsToBeStored = self.propertiesMapping
feature = elements
fieldValues = []
for (fieldName, fieldValue) in fieldsToBeStored.iteritems():
value = getattr(feature, fieldName, None)
if not value:
if isinstance(fieldValue, types.FunctionType):
value = fieldValue(feature)
elif isinstance(fieldValue, basestring):
if fieldValue.startswith("$"):
value = getattr(feature, fieldValue[1:], None)
if value == None:
value = getattr(feature, fieldValue, None)
if value == None:
value = fieldValue
if value <> None:
if isinstance(value, basestring):
fieldValue = "'%s'" % value
else:
fieldValue = str(value)
else:
fieldValue = "null"
fieldValues.append(fieldValue)
fieldNames = ", ".join(fieldsToBeStored.keys())
fieldValuesStr = ",".join(fieldValues)
sql = "insert into m3_cartographicmodel.%s (%s, %s) values (%s, ST_GeomFromText('%s', 5514));\n" % (self.tableName, fieldNames, self.geometryFieldName, fieldValuesStr, feature.wkt)
if sql.find('$happenies') >= 0:
print sql
database.execute(sql)
def movies_full_scan_subtitles():
movies = database.execute("SELECT path FROM table_movies")
count_movies = len(movies)
for i, movie in enumerate(movies, 1):
notifications.write(msg='Updating all movies subtitles from disk...',
queue='list_subtitles_movies',
item=i,
length=count_movies)
store_subtitles_movie(movie['path'], path_replace_movie(movie['path']))
gc.collect()
def series_full_scan_subtitles():
episodes = database.execute("SELECT path FROM table_episodes")
count_episodes = len(episodes)
for i, episode in enumerate(episodes, 1):
notifications.write(msg='Updating all episodes subtitles from disk...',
queue='list_subtitles_series',
item=i,
length=count_episodes)
store_subtitles(path_replace(episode['path']))
gc.collect()
def api_movies_history():
data = database.execute("SELECT table_movies.title, strftime('%Y-%m-%d', "
"datetime(table_history_movie.timestamp, 'unixepoch')) as date, "
"table_history_movie.description FROM table_history_movie "
"INNER JOIN table_movies on table_movies.radarrId = table_history_movie.radarrId "
"WHERE table_history_movie.action != '0' ORDER BY id DESC LIMIT 10")
history_subs = []
for item in data:
history_subs.append([item['title'], item['date'], item['description']])
return dict(subtitles=history_subs)
def get_language_set():
languages = database.execute("SELECT code3 FROM table_settings_languages WHERE enabled=1")
language_set = set()
for lang in languages:
if lang['code3'] == 'pob':
language_set.add(Language('por', 'BR'))
else:
language_set.add(Language(lang['code3']))
return language_set
def api_wanted():
data = database.execute("SELECT table_shows.title as seriesTitle, table_episodes.season || 'x' || "
"table_episodes.episode as episode_number, table_episodes.title as episodeTitle, "
"table_episodes.missing_subtitles FROM table_episodes INNER JOIN table_shows on "
"table_shows.sonarrSeriesId = table_episodes.sonarrSeriesId WHERE "
"table_episodes.missing_subtitles != '[]' ORDER BY table_episodes._rowid_ DESC LIMIT 10")
wanted_subs = []
for item in data:
wanted_subs.append([item['seriesTitle'], item['episode_number'], item['episodeTitle'],
item['missing_subtitles']])
return dict(subtitles=wanted_subs)
