def SoustraireQuantitePieces(entree_piece):
numero_piece = entree_piece['numero']
if 'quantiteneuf' in entree_piece:
quantiteneuf = entree_piece['quantiteneuf']
db.DBConnection().pieces.update({'numero': numero_piece}, {'$inc': {'quantiteneuf': -quantiteneuf}})
if 'quantiteusage' in entree_piece:
quantiteusage = entree_piece['quantiteusage']
db.DBConnection().pieces.update({'numero': numero_piece}, {'$inc': {'quantiteusage': -quantiteusage}})
def test_check_write_entry(postgresql):
"""Check if writing to DB is succesful"""
cursor = postgresql.cursor()
conn = db.DBConnection()
conn.db_connection = postgresql
table_name = "test"
conn.create_table(table_name)
url = "http://test.com"
response_code = 200
response_time = 0.123
response_result = True
timestamp = datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
conn.write_entry(url, response_code, response_time, response_result,
timestamp)
stmt = """
SELECT * FROM {};
""".format(table_name)
cursor.execute(stmt)
result = cursor.fetchone()
assert result[0] == url
assert result[1] == response_code
assert result[2] == response_time
assert result[3] == response_result
assert str(result[4]) == timestamp
def PostFactures():
facture = {}
headers = {'Content-type': 'application/json'}
result = {}
status = httplib.CREATED
try:
facture = ParseIncoming(request.form, 'factures')
facture['numero'] = ObtenirProchainNumeroDeFacture()
facture['pieces'] = []
facture['complete'] = False
facture['prixtotal'] = 0
if 'date' not in facture:
facture['date'] = datetime.datetime.now()
ValidationFactures(facture)
db.DBConnection().factures.insert(facture)
headers['Location'] = url_for('GetFacturesNumero', numero=facture['numero'])
result = facture
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status, headers
def update_or_insert(self):
statements = []
with db.DBConnection() as dbconn:
row = dbconn.fetchone(
'SELECT name, id, modified_time, urllink, container_id, workspace_id FROM urls WHERE id = ?',
(self.id, ))
if row:
if (row[0], row[2], row[3], row[4], row[5]) != (
self.name, self.modified_time, self.urllink,
self.container_id, self.workspace_id):
logger.info('Updating Url %s', self)
statements.append((
'UPDATE urls SET name = ?, container_id = ?, modified_time = ?, workspace_id = ?, urllink = ? WHERE id = ?',
(self.name, self.container_id, self.modified_time,
self.workspace_id, self.urllink, self.id)))
else:
logger.info('Inserting Url %s', self)
statements.append((
'INSERT INTO urls (name, id, modified_time, urllink, container_id, workspace_id) VALUES (?, ?, ?, ?, ?, ?)',
(self.name, self.id, self.modified_time, self.urllink,
self.container_id, self.workspace_id)))
return statements
def PutFactures(numero):
print("PutFactures")
result = {}
status = httplib.NO_CONTENT
try:
if not FactureExiste(numero):
raise RequestError(httplib.NOT_FOUND,
"Cette facture n'existe pas.")
facture = ParseIncoming(request.form, 'factures', False)
ValidationFactures(facture)
update_result = db.DBConnection().factures.update(
{'numero': numero},
{'$set': facture},
)
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status
def reset_status(indexerid, season, episode):
""" Revert episode history to status from download history,
if history exists """
my_db = db.DBConnection()
history_sql = 'SELECT h.action, h.showid, h.season, h.episode, t.status' \
' FROM history AS h' \
' INNER JOIN tv_episodes AS t' \
' ON h.showid = t.showid AND h.season = t.season AND h.episode = t.episode' \
' WHERE t.showid = ? AND t.season = ? AND t.episode = ?' \
' GROUP BY h.action' \
' ORDER BY h.date DESC' \
' LIMIT 1'
sql_history = my_db.select(
history_sql, [str(indexerid),
str(season), str(episode)])
if 1 == len(sql_history):
history = sql_history[0]
# update status only if status differs
# FIXME: this causes issues if the user changed status manually
# replicating refactored behavior anyway.
if history['status'] != history['action']:
undo_status = 'UPDATE tv_episodes SET status = ?' \
' WHERE showid = ? AND season = ? AND episode = ?'
my_db.action(undo_status, [
history['action'], history['showid'], history['season'],
history['episode']
])
def render_html(self, verbose=False):
pbar = None
if verbose:
print('Rendering HTML for workspaces')
with db.DBConnection() as dbconn:
workspace_rows = dbconn.fetchall(
'SELECT name, id FROM workspaces ORDER BY name ASC')
workspaces = [
models.workspace.Workspace(*row) for row in workspace_rows
]
if verbose:
pbar = tqdm(total=len(workspaces))
for workspace in workspaces:
workspace.render_html()
if pbar is not None:
pbar.update(1)
if pbar is not None:
pbar.close()
sdk.html.render_index_page(workspaces)
if verbose:
import os
print('HTML has been rendered and can be found by opening %s' %
os.path.join(config.conf.FILESTORAGE_PATH, 'html',
'index.html'))
def on_media(message: telebot.types.Message):
dbc = db.DBConnection()
if not message.from_user.is_bot:
if message.photo is None:
received_images = None
else:
received_images = message.photo[1::2]
channel = dbc.get_discord_channel(message.chat.id)
if message.text is None:
message.text = ''
try_create_dir(f"{config.temp_dir}/telegram/photos")
try_create_dir(f"{config.temp_dir}/telegram/videos")
try_create_dir(f"{config.temp_dir}/telegram/documents")
self.__send_media_list__(received_images,
message.from_user.username,
channel,
msg=message.text)
self.__send_media__(message.document,
message.from_user.username,
channel,
msg=message.text)
self.__send_media__(message.audio,
message.from_user.username,
channel,
msg=message.text)
def PutPieces(numero):
result = {}
status = httplib.NO_CONTENT
try:
if not PieceExiste(numero):
raise RequestError(httplib.NOT_FOUND, "Cette pièce n'existe pas.")
piece = ParseIncoming(request.form, 'pieces', False)
# Si changement de numéro, vérifier que le numéro n'est pas déjà pris
if 'numero' in piece and PieceExiste(
piece['numero']) and numero != piece['numero']:
raise RequestError(httplib.CONFLICT,
'Ce numero de piece est deja pris')
update_result = db.DBConnection().pieces.update({'numero': numero},
{'$set': piece})
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status
def PostPieces():
result = {}
status = httplib.CREATED
headers = {}
try:
piece = ParseIncoming(request.form, 'pieces')
if PieceExiste(piece['numero']):
raise RequestError(httplib.CONFLICT,
'Ce numero de piece est deja pris')
db.DBConnection().pieces.insert(piece)
headers['Location'] = url_for('GetPiecesNumero',
numero=piece['numero'])
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status, headers
def PostHeuresBenevoles(numero):
result = {}
status = httplib.CREATED
headers = {'Content-type': 'application/json'}
try:
heures = ParseIncoming(request.form, 'heuresbenevole')
if not EstBenevole(numero):
raise RequestError(httplib.UNPROCESSABLE_ENTITY,
'Ce membre n\'est pas bénévole')
if 'date' not in heures:
heures['date'] = datetime.datetime.now()
db.DBConnection().membres.update({"numero": numero},
{'$push': {
"heuresbenevole": heures
}})
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status, headers
def PostMembres():
membre = {}
headers = {}
result = None
status = httplib.CREATED
try:
membre = ParseIncoming(request.form, 'membres')
if 'numero' in membre:
# Numéro de membre fourni par le client, vérifier qu'il n'est pas pris
if ObtenirMembre(membre['numero']) != None:
raise RequestError(httplib.CONFLICT,
"Ce numéro de membre est déjà pris.")
else:
# On attribut automatiquement un # de membre
membre['numero'] = ObtenirProchainNumeroDeMembre()
db.DBConnection().membres.insert(membre)
headers['Location'] = url_for('GetMembresNumero',
numero=membre['numero'])
result = {'numero': membre['numero']}
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status, headers
def GetFactures():
result = {}
status = httplib.OK
headers = {'Content-type': 'application/json'}
try:
filters = ParseIncoming(request.args, 'getfactures')
debut = filters.pop('debut', None)
fin = filters.pop('fin', None)
if debut or fin:
filters['date'] = {}
if debut:
filters['date']['$gte'] = debut
if fin:
filters['date']['$lte'] = fin
result = list(db.DBConnection().factures.find(filters).sort(
'numero', 1))
except Exception as e:
result = str(e)
status = httplib.INTERNAL_SERVER_ERROR
return jsonify(result), status, headers
def DeleteFactures(numero):
result = ''
status = httplib.NO_CONTENT
try:
facture = ObtenirFacture(numero)
mettreAJourAbonnement = False
if not facture:
raise RequestError(httplib.NOT_FOUND, "Cette facture n'existe pas")
if 'pieces' in facture:
lignes = facture['pieces']
for ligne in lignes:
AjouterQuantitePieces(ligne)
if ligne['numero'] in abonnements:
mettreAJourAbonnement = True
# Supprimer la facture
db.DBConnection().factures.remove({'numero': numero})
# Mettre à jour l'abonnement au besoin
if mettreAJourAbonnement:
MettreAJourExpirationMembre(facture['membre'])
except RequestError as ex:
status = ex.status
result = ex.msg
except Exception as ex:
status = httplib.INTERNAL_SERVER_ERROR
result = str(ex)
return jsonify(result), status
def _logHistoryItem(action,
ep_obj,
quality,
resource,
provider,
version=-1,
proper_tags='',
manually_searched=False,
info_hash=None,
size=-1):
"""
Insert a history item in DB
:param action: action taken (snatch, download, etc)
:param showid: showid this entry is about
:param season: show season
:param episode: show episode
:param quality: media quality
:param resource: resource used
:param provider: provider used
:param version: tracked version of file (defaults to -1)
"""
logDate = datetime.datetime.today().strftime(History.date_format)
resource = ss(resource)
main_db_con = db.DBConnection()
main_db_con.action(
"INSERT INTO history "
"(action, date, indexer_id, showid, season, episode, quality, "
"resource, provider, version, proper_tags, manually_searched, info_hash, size) "
"VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [
action, logDate, ep_obj.series.indexer, ep_obj.series.series_id,
ep_obj.season, ep_obj.episode, quality, resource, provider,
version, proper_tags, manually_searched, info_hash, size
])
def latestdate_fix():
import db, logger
datefix = []
myDB = db.DBConnection()
comiclist = myDB.select('SELECT * FROM comics')
if comiclist is None:
logger.fdebug('No Series in watchlist to correct latest date')
return
for cl in comiclist:
latestdate = cl['LatestDate']
#logger.fdebug("latestdate: " + str(latestdate))
if latestdate[8:] == '':
#logger.fdebug("invalid date " + str(latestdate) + " appending 01 for day to avoid errors")
if len(latestdate) <= 7:
finddash = latestdate.find('-')
#logger.info('dash found at position ' + str(finddash))
if finddash != 4: #format of mm-yyyy
lat_month = latestdate[:finddash]
lat_year = latestdate[finddash+1:]
else: #format of yyyy-mm
lat_month = latestdate[finddash+1:]
lat_year = latestdate[:finddash]
latestdate = (lat_year) + '-' + str(lat_month) + '-01'
datefix.append({"comicid": cl['ComicID'],
"latestdate": latestdate})
#logger.info('latest date: ' + str(latestdate))
#now we fix.
if len(datefix) > 0:
for df in datefix:
newCtrl = {"ComicID": df['comicid']}
newVal = {"LatestDate": df['latestdate']}
myDB.upsert("comics", newVal, newCtrl)
return
def by_pending_download(cls):
with db.DBConnection() as dbconn:
document_rows = dbconn.fetchall(
'SELECT name, id, modified_time, container_id, workspace_id, modified_by_id FROM documents WHERE downloaded = 0'
)
return [Document(*row) for row in document_rows]
def getDBcompare(self):
try:
self.updater.need_update()
cur_hash = str(self.updater.get_newest_commit_hash())
assert len(
cur_hash) is 40, "Commit hash wrong length: %s hash: %s" % (
len(cur_hash), cur_hash)
check_url = "http://cdn.rawgit.com/%s/%s/%s/sickbeard/databases/mainDB.py" % (
sickbeard.GIT_ORG, sickbeard.GIT_REPO, cur_hash)
response = helpers.getURL(check_url, session=self.session)
assert response, "Empty response from %s" % check_url
match = re.search(r"MAX_DB_VERSION\s=\s(?P<version>\d{2,3})",
response)
branchDestDBversion = int(match.group('version'))
myDB = db.DBConnection()
branchCurrDBversion = myDB.checkDBVersion()
if branchDestDBversion > branchCurrDBversion:
return 'upgrade'
elif branchDestDBversion == branchCurrDBversion:
return 'equal'
else:
return 'downgrade'
except Exception as e:
return repr(e)
def _logHistoryItem(action, showid, season, episode, quality, resource, provider):
logDate = datetime.datetime.today().strftime(dateFormat)
myDB = db.DBConnection()
myDB.action("INSERT INTO history (action, date, showid, season, episode, quality, resource, provider) VALUES (?,?,?,?,?,?,?,?)",
[action, logDate, showid, season, episode, quality, resource, provider])
def MettreAJourExpirationMembre(numero):
exp = CalculerExpirationMembre(numero)
db.DBConnection().membres.update({'numero': numero},
{'$set': {
'expiration': exp
}})
def _logHistoryItem(action,
showid,
season,
episode,
quality,
resource,
provider,
version=-1):
"""
Insert a history item in DB
:param action: action taken (snatch, download, etc)
:param showid: showid this entry is about
:param season: show season
:param episode: show episode
:param quality: media quality
:param resource: resource used
:param provider: provider used
:param version: tracked version of file (defaults to -1)
"""
logDate = datetime.datetime.today().strftime(History.date_format)
resource = ss(resource)
main_db_con = db.DBConnection()
main_db_con.action(
"INSERT INTO history (action, date, showid, season, episode, quality, resource, provider, version) VALUES (?,?,?,?,?,?,?,?,?)",
[
action, logDate, showid, season, episode, quality, resource,
provider, version
])
def render_html(self):
with db.DBConnection() as dbconn:
container_rows = dbconn.fetchall(
'SELECT id, name, container_id, workspace_id FROM containers WHERE container_id = ?', (self.id,)
)
containers = [
models.container.Container(row[1], row[0], row[2], row[3]) for row in container_rows
]
document_rows = dbconn.fetchall(
'SELECT id, name, container_id, workspace_id, modified_time FROM documents WHERE container_id = ?',
(self.id,)
)
documents = [
models.document.Document(row[1], row[0], row[4], row[2], row[3]) for row in document_rows
]
for container in containers:
container.render_html()
with open(self.html_file_path, 'w') as fp:
fp.write(self.html_header)
fp.write(self.html_container_content(containers))
fp.write(self.html_document_content(documents))
fp.write(self.html_footer)
def annual_update():
import db, logger
myDB = db.DBConnection()
annuallist = myDB.select('SELECT * FROM annuals')
if annuallist is None:
logger.info('no annuals to update.')
return
cnames = []
#populate the ComicName field with the corresponding series name from the comics table.
for ann in annuallist:
coms = myDB.selectone('SELECT * FROM comics WHERE ComicID=?', [ann['ComicID']]).fetchone()
cnames.append({'ComicID': ann['ComicID'],
'ComicName': coms['ComicName']
})
#write in a seperate loop to avoid db locks
i=0
for cns in cnames:
ctrlVal = {"ComicID": cns['ComicID']}
newVal = {"ComicName": cns['ComicName']}
myDB.upsert("annuals", newVal, ctrlVal)
i+=1
logger.info(str(i) + ' series have been updated in the annuals table.')
return
def get_by_id(cls, _id):
with db.DBConnection() as dbconn:
document_row = dbconn.fetchone(cls.SQL_SELECT_BY_ID, (_id,))
if document_row:
return Document(*document_row)
return None
def launch(self):
print('Starting discord bot...', end='\t\t\t\t')
try:
self.__db__ = db.DBConnection()
self.start()
print(ConsoleColors.OKGREEN + 'DONE' + ConsoleColors.ENDC)
except:
print(ConsoleColors.FAIL + 'FAILED' + ConsoleColors.ENDC)
def get_by_id(cls, _id):
with db.DBConnection() as dbconn:
user_row = dbconn.fetchone(
'SELECT id, name, email FROM users WHERE id = ?', (_id, ))
if user_row:
return User(*user_row)
return None
def ObtenirProchainNumeroDeFacture():
"""Retourne le prochain numero de facture disponible."""
d = db.DBConnection()
if d.factures.count() == 0:
return 1
else:
return d.factures.find().sort('numero', pymongo.DESCENDING).limit(1)[0]['numero'] + 1
def get_in_container(cls, container_id):
with db.DBConnection() as dbconn:
container_rows = dbconn.fetchall(
'SELECT id, name, container_id, workspace_id FROM containers WHERE container_id = ?', (container_id,)
)
return [
Container(row[1], row[0], row[2], row[3]) for row in container_rows
]
def FacturesFermees():
factures = list(db.DBConnection().factures.find({'complete': True}))
membres = {}
for facture in factures:
if facture['membre'] not in membres:
membres[facture['membre']] = ObtenirMembre(facture['membre'])
return render_template('factures-fermees.html', factures = factures, membres = membres)
def get_in_container(cls, container_id):
with db.DBConnection() as dbconn:
document_rows = dbconn.fetchall(
'SELECT name, id, modified_time, container_id, workspace_id, modified_by_id FROM documents WHERE container_id = ? ORDER BY name ASC',
(container_id,)
)
return [
Document(*row) for row in document_rows
]
