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)