def get_previous_rate(id_owner, owner_product, product_id, **kwargs):
gst_ = kwargs.get('gst_', '')
if gst_:
with conn() as cursor:
cursor.execute(
sql.SQL(
"select rate from {} where id_product = %s and id_owner = %s and rate is not null order by timestamp_ desc "
).format(sql.Identifier(owner_product)),
(product_id, id_owner))
result = cursor.fetchone()
print('non-gst is {}: '.format(result))
with conn() as cursor:
cursor.execute(
sql.SQL(
"select gst_rate from {} where id_product = %s and id_owner = %s and gst_rate is not null order by timestamp_ desc "
).format(sql.Identifier(owner_product)),
(product_id, id_owner))
result = cursor.fetchone()
else:
with conn() as cursor:
cursor.execute(
sql.SQL(
"select rate from {} where id_product = %s and id_owner = %s and rate is not null order by timestamp_ desc "
).format(sql.Identifier(owner_product)),
(product_id, id_owner))
result = cursor.fetchone()
if result:
return result[0]
else:
return None
def set_pricelist_discount(owner_pricelist, id_owner, id_pricelist, discount,
**kwargs):
gst_ = kwargs.get('gst_', '')
try:
if gst_:
cf.cursor_(sql.SQL(
"insert into {} (id_owner, id_pricelist, gst_discount) values (%s, %s, %s) returning discount"
).format(sql.Identifier(owner_pricelist)),
arguments=(id_owner, id_pricelist, discount))
else:
cf.cursor_(sql.SQL(
"insert into {} (id_owner, id_pricelist, discount) values (%s, %s, %s) returning discount"
).format(sql.Identifier(owner_pricelist)),
arguments=(id_owner, id_pricelist, discount))
except Exception as e:
if e.pgcode == '23505':
print("Updating ...")
if gst_:
with conn() as cursor:
cursor.execute(
sql.SQL(
"update {} set ( gst_discount ) = (%s) where id_owner = %s and id_pricelist = %s"
).format(sql.Identifier(owner_pricelist)),
(discount, id_owner, id_pricelist))
else:
with conn() as cursor:
cursor.execute(
sql.SQL(
"update {} set ( discount ) = (%s) where id_owner = %s and id_pricelist = %s"
).format(sql.Identifier(owner_pricelist)),
(discount, id_owner, id_pricelist))
else:
print(e)
def update_stock(table_, saved_id_table_tuple):
return None
assert table_ in ["sale_invoice", "purchase_invoice"]
if table_ == "sale_invoice":
detail_table = "si_detail"
elif table_ == "purchase_invoice":
detail_table = "pi_detail"
print('Updating date_ in {}...'.format(detail_table))
date_sq = "update {} as dt set date_ = (select date_ from {} as t where t.id = dt.id_invoice)".format(
detail_table, table_)
with conn() as cursor:
cursor.execute(date_sq)
print("Inserting {} values in stock table...".format(detail_table))
if detail_table == "si_detail":
# <<<<<<< HEAD
# =======
# # table_ = sql.SQL('master.') + sql.identifier("stock")
# >>>>>>> 5bcc23706a8fd2a3ca03f355d28ce66ab4c84f0b
sq = "insert into master.stock (id_si_detail, id_product, product_name, product_unit, qty_sale, date_) select id, id_product, product_name, product_unit, product_qty, date_ from si_detail where si_detail.id_invoice in %s"
with conn() as cursor:
cursor.execute(sq, (saved_id_table_tuple, ))
elif detail_table == "pi_detail":
sq = "insert into master.stock (id_pi_detail, id_product, product_name, product_unit, qty_purchase, date_) select id, id_product, product_name, product_unit, product_qty, date_ from pi_detail where pi_detail.id_invoice in %s"
with conn() as cursor:
cursor.execute(sq, (saved_id_table_tuple, ))
def assign_pricelist_to_product():
pl_last_name = ''
while True:
product_name = cf.prompt_("Enter product name: ",
cf.get_completer_list("name", "product"),
history_file="product_name.txt")
if product_name == "quit": return "quit"
if product_name == "back": return "back"
id_product = product.get_product_details(product_name)[0]
old_name = ''
with conn() as cursor:
cursor.execute(
"select name, value from pricelist as pl join product_pricelist ppl on pl.id=ppl.id_pricelist where ppl.id_product = %s",
(id_product, ))
old_name_result = cursor.fetchone()
if old_name_result:
old_name = old_name_result[0]
print("Price List Name is {}".format(old_name))
print("You cannot edit name or value from here")
return "back"
pricelist_name = cf.prompt_("Enter pricelist name: ",
cf.get_completer_list("name", "pricelist"),
default_=pl_last_name)
pl_last_name = pricelist_name
if pricelist_name == "back": return "back"
if pricelist_name == "quit": return "quit"
pricelist_value = cf.prompt_("Enter pricelist value: ", [])
if pricelist_value == "quit": return "quit"
if pricelist_value == "back": return "back"
id_pricelist = get_id_pricelist_by_name(pricelist_name)
with conn() as cursor:
cursor.execute(
"insert into product_pricelist (id_product, id_pricelist, value) values (%s, %s, %s) returning id",
(id_product, id_pricelist, pricelist_value))
def get_gst_customer_balance(**kwargs):
place = kwargs.get('place', '')
if place:
with conn() as cursor:
cursor.execute(
"select name, place, sum(coalesce(invoice_amount, 0)) - sum(coalesce(money_amount,0)) + customer.gst_opening_balance as balance from gst_sale_ledger_view join customer on customer.id = gst_sale_ledger_view.id_owner where customer.place = %s group by name, place, customer.gst_opening_balance order by balance desc",
(place, ))
result = cursor.fetchall()
else:
owner_ = get_owner("customer") # owner object
id_owner = owner_.id
with conn() as cursor:
cursor.execute(
"select name, place, sum(invoice_amount) - sum(money_amount) + master.customer.opening_balance as balance from master.sale_ledger_view join master.customer on master.customer.id = master.sale_ledger_view.id_owner where master.customer.id = %s group by name, place, customer.opening_balance order by balance desc",
(id_owner, ))
result = cursor.fetchall()
columns = ['name', 'place', 'balance']
cf.pretty_(columns, result, align_right=['balance'])
right_align_columns = ['balance']
# left_align_columns = ['name', 'place']
pt = PrettyTable(columns)
# print(result)
for a in result:
if a[2] is None:
a2 = ''
else:
a2 = a[2]
pt.add_row([a[0], a[1], a2])
pt.align = 'l'
for r in right_align_columns:
pt.align[r] = 'r'
def update_owner_product(self, owner_product, rate, **kwargs):
gst_ = kwargs.get('gst_', '')
previous_rate = get_previous_rate(self.invoice_.owner.id,
owner_product, self.product_id,
**kwargs)
rate_date = str(datetime.datetime.today())
if previous_rate == rate:
with conn() as cursor:
cursor.execute(
sql.SQL(
"update {} set (timestamp_) = (%s) where id_owner = %s and id_product = %s and rate = %s"
).format(sql.Identifier(owner_product)),
(rate_date, self.invoice_.owner.id, self.product_id, rate))
cf.log_("Updated customer product timestamp_")
else:
if gst_:
with conn() as cursor:
cursor.execute(
sql.SQL(
"insert into {} (gst_rate, timestamp_, id_owner, id_product) values (%s, %s, %s, %s)"
).format(sql.Identifier(owner_product)),
(rate, rate_date, self.invoice_.owner.id,
self.product_id))
else:
with conn() as cursor:
cursor.execute(
sql.SQL(
"insert into {} (rate, timestamp_, id_owner, id_product) values (%s, %s, %s, %s)"
).format(sql.Identifier(owner_product)),
(rate, rate_date, self.invoice_.owner.id,
self.product_id))
cf.log_("Inserted customer product rate and timestamp_")
def set_owner_product_rate(owner_product, id_product, id_owner, rate,
**kwargs):
rate_date = str(datetime.datetime.today())
gst_ = kwargs.get('gst_', '')
if gst_:
usq = "update {} set (gst_rate, timestamp_) = (%s, %s) where id_owner = %s and id_product = %s returning id".format(
owner_product)
sq = "insert into {} (id_owner, id_product, gst_rate, timestamp_) values (%s, %s, %s, %s) returning id".format(
owner_product)
else:
usq = "update {} set (rate, timestamp_) = (%s, %s) where id_owner = %s and id_product = %s returning id".format(
owner_product)
sq = "insert into {} (id_owner, id_product, rate, timestamp_) values (%s, %s, %s, %s) returning id".format(
owner_product)
try:
with conn() as cursor:
print("rechaeasf")
cursor.execute(usq, (rate, rate_date, id_owner, id_product))
result = cursor.fetchall()
print(result)
if len(result) == 0:
print("Update failed, so inserting...")
with conn() as cursor:
cursor.execute(sq, (id_owner, id_product, rate, rate_date))
except Exception as e:
print(e)
def edit_property(self, property_, **kwargs):
if property_ in ["packed", "unpack"]:
old_value = getattr(self, property_)
if old_value:
new_value = None
setattr(self, property_, new_value)
else:
new_value = "yes"
setattr(self, property_, new_value)
cf.cursor_(sql.SQL(
"update {} set {} = %s where id = %s returning id").format(
sql.Identifier(self.invoice_detail_type),
sql.Identifier(property_)),
arguments=(new_value, self.id))
return
if property_ in ["id", "product_gst_rate"]:
cf.log_("You cannot change 'id' value")
return None
old_value = getattr(self, property_)
new_value = cf.prompt_(
"Enter new {} [{}] for {}: ".format(property_, old_value,
self.product_name),
cf.get_completer_list(property_, self.invoice_detail_type))
if new_value == "quit": return "quit"
setattr(self, property_, new_value)
cf.cursor_(
sql.SQL("update {} set {} = %s where id = %s returning id").format(
sql.Identifier(self.invoice_detail_type),
sql.Identifier(property_)),
arguments=(new_value, self.id))
if property_ in ['product_gst_name']:
confirm_ = cf.prompt_(
"Do you want to update the name in Product table?: ",
['y', 'n'],
default_='y',
unique_='existing')
if confirm_ == 'y':
with conn() as cursor:
cursor.execute(
"update product set gst_name = %s where id = %s",
(new_value, self.product_id))
if property_ in ["product_rate", "product_qty", "product_discount"]:
sub_total = self.get_sub_total(property_=property_,
property_value=Decimal(new_value))
gst_amount = (Decimal(sub_total) * Decimal(self.product_gst_rate) *
Decimal(0.01)).quantize(Decimal("1.00"))
with conn() as cursor:
cursor.execute(
sql.SQL(
"update {} set ({}, sub_total, gst_amount) = (%s, %s, %s) where id = %s"
).format(sql.Identifier(self.invoice_detail_type),
sql.Identifier(property_)),
(new_value, sub_total, gst_amount, self.id))
setattr(self, "sub_total", sub_total)
owner_product = cf.owner_product_from_invoice_type_d[
self.invoice_.invoice_type]
self.invoice_.update_invoice_with_sub_total()
self.update_owner_product(owner_product, self.product_rate,
**kwargs)
self.view_()
def get_master_totals():
with conn() as cursor:
cursor.execute(
"select sum(invoice_amount), sum(money_amount) from master.sale_ledger_view "
)
result = cursor.fetchone()
with conn() as cursor:
cursor.execute("select sum(opening_balance) from master.customer")
total_opening_balance = cursor.fetchone()[0]
return result[0], result[1], total_opening_balance
def get_filter_result(filter_type, invoice_type, **kwargs):
owner_type = cf.owner_type_d[invoice_type]
owner_nickname = kwargs.get('nickname', '')
if filter_type == "All Invoices":
result = cf.cursor_(
sql.SQL(
"select s.invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where s.gst_invoice_no is not null order by s.id desc"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)))
elif filter_type == "Unsaved Invoices":
sq = 'select invoice_no, date_, owner_name, owner_place, amount_after_freight, id from {} where id not in (select id_invoice from sale_transaction where id_invoice is not null)'.format(
invoice_type)
with conn() as cursor:
cursor.execute(sq)
result = cursor.fetchall()
elif filter_type == "Search By Nickname":
if not owner_nickname:
with conn() as cursor:
cursor.execute(
"select distinct id_owner from {}".format(invoice_type))
id_list = cursor.fetchall()
nickname_list = []
for a in id_list:
nickname_list.append(get_nickname_from_id(owner_type, a))
owner_nickname = cf.prompt_("Enter {} Name: ".format(owner_type),
nickname_list,
unique_="existing")
# owner_nickname = cf.prompt_("Enter {} Name: ".format(owner_type), cf.get_completer_list("nickname", owner_type))
if invoice_type in ["receipt", "payment"]:
result = cf.cursor_(sql.SQL(
"select r.id, r.date_, c.name, r.amount from {} as r join {} as c on c.id = r.id_owner where c.nickname = %s"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)),
arguments=(owner_nickname, ))
else:
result = cf.cursor_(sql.SQL(
"select s.invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where o.nickname = %s order by s.id desc"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)),
arguments=(owner_nickname, ))
elif filter_type == "All Estimates":
result = cf.cursor_(
sql.SQL(
"select s.invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where s.gst_invoice_no is null order by s.id desc limit 10"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)))
elif filter_type == "Last 10 Invoices":
result = cf.cursor_(
sql.SQL(
"select s.invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner order by s.id desc limit 10"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)))
elif filter_type == "All Owner Invoices":
result = cf.cursor_(sql.SQL(
"select s.invoice_no, s.date_, o.nickname, s.amount_before_freight, s.id from {} as s join {} as o on o.id = s.id_owner where o.nickname = %s order by s.id desc"
).format(sql.Identifier(invoice_type), sql.Identifier(owner_type)),
arguments=(owner_nickname, ))
return result
def insert_records(self, t):
if self.owner_product == "customer_product":
with conn() as cursor:
execute_values(
cursor,
"insert into si_detail (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_hsn, product_gst_rate, sub_total, product_print_name, product_gst_name, gst_amount, product_cost, cost_sub_total) values %s",
t)
if self.owner_product == "vendor_product":
with conn() as cursor:
execute_values(
cursor,
"insert into pi_detail (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_hsn, product_gst_rate, sub_total, product_print_name, product_gst_name, gst_amount) values %s",
t)
def get_invoice_properties(self, **kwargs):
master_ = kwargs.get("master_", '')
invoice_type = sql.Identifier(self.invoice_type)
if master_:
cf.log_("reached master_")
if self.invoice_type == "receipt":
invoice_type = sql.SQL("master.") + sql.Identifier("receipt")
if self.invoice_type == "payment":
invoice_type = sql.SQL("master.") + sql.Identifier("payment")
with conn() as cursor:
cursor.execute(
sql.SQL("select {} from {} where id = %s").format(
sql.SQL(', ').join(map(sql.Identifier, sq_properties)),
invoice_type), (self.id, ))
self.invoice_properties = cursor.fetchone()
# self.invoice_properties = cf.execute_("select {} from {} where {} = %s", sq_properties, table_=invoice_type, where_="id", arg_=(self.id, ), fetch_="y")
#print("invoice_properties: {}".format(self.invoice_properties))
self.id_owner = self.invoice_properties[0]
self.date_ = self.invoice_properties[1]
self.amount = self.invoice_properties[2]
self.type = self.invoice_properties[3]
self.recipient = self.invoice_properties[4]
self.detail = self.invoice_properties[5]
self.place = self.invoice_properties[6]
self.invoice_no = self.invoice_properties[7]
self.id_transaction = self.invoice_properties[8]
self.gst_invoice_no = self.invoice_properties[9]
self.owner = owner.get_existing_owner_by_id(self.owner_type,
self.id_owner)
cf.log_("Finished Money.get_invoice_properties()")
def insert_data():
conn = db.conn()
cur = conn.cursor()
csv_tables = {
'Master.csv': 'master',
'TeamsFranchises.csv': 'team_franchises',
'teams.csv': 'teams',
'managers.csv': 'managers',
'awardssharemanagers.csv': 'awardssharemanagers',
'awardsmanagers.csv': 'awardsmanagers',
'awardsplayers.csv': 'awardsplayers',
'awardsshareplayers.csv': 'awardsshareplayers',
'batting.csv': 'batting',
'salaries.csv': 'salaries',
'fieldingOF.csv': "fieldingOF",
'halloffame.csv': 'halloffame',
'seriespost.csv': 'series_post',
'battingpost.csv': 'batting_post',
'fielding.csv': 'fielding',
'pitching.csv': 'pitching',
'pitchingpost.csv': 'pitching_post',
'allstarfull.csv': 'allstarfull'
}
for i, k in csv_tables.items():
path = join_path("{i}".format(i=i))
with open(path, 'r') as f:
next(f) # Skip the header row.
cur.copy_from(f, '{k}'.format(k=k), sep=',', null='')
conn.commit()
def main(args=None):
if not args:
return render_template_string(page, title=u'Common interwiki articles')
c = conn('p50380g50592__interwikis_p', 's1.labsdb')
c.execute('SELECT ell_title, ell_links FROM enwiki_langlinks WHERE ell_langs NOT LIKE ? LIMIT 200', ('% {} %'.format(args),))
r = c.fetchall()
return render_template_string(page, title=u'Common interwiki articles missing in {}.wiki'.format(args), prefix=args, query=r)
def main(wiki=None):
if not wiki:
wiki = u'Wikipédia'
c = conn(wiki)
if c:
c.execute('''SELECT
SUBSTR(rc_timestamp, 1, 10) AS HORA,
SUM(rc_user = 0),
SUM(rc_user = 0 AND rc_patrolled),
SUM(rc_comment LIKE ? OR rc_comment LIKE ? OR rc_comment LIKE ?)
FROM recentchanges
WHERE rc_namespace = 0 AND rc_type != 5
GROUP BY HORA
ORDER BY rc_id DESC
LIMIT 168''', ('Foram [[WP:REV|%', u'Reversão de uma ou mais edições de%', u'bot: revertidas edições de%'))
r1 = c.fetchall()
c.execute('''SELECT
SUBSTR(rc_timestamp, 1, 8) AS DIA,
SUM(rc_user = 0),
SUM(rc_user = 0 AND rc_patrolled),
SUM(rc_comment LIKE ? OR rc_comment LIKE ? OR rc_comment LIKE ?)
FROM recentchanges
WHERE rc_namespace = 0 AND rc_type != 5
GROUP BY DIA
ORDER BY rc_id DESC
LIMIT 30''', ('Foram [[WP:REV|%', u'Reversão de uma ou mais edições de%', u'bot: revertidas edições de%'))
r2 = c.fetchall()
r = {'wiki': wiki, 'link': link(wiki)}
r['iphquery'] = ','.join([(x in r1[6::6] and '\n[{},{},{},{}]' or '[{},{},{},{}]').format(*x) for x in r1])
r['ipdquery'] = ','.join([(x in r2[6::6] and '\n[{},{},{},{}]' or '[{},{},{},{}]').format(*x) for x in r2][1:-1])
else:
r = {}
return render_template_string(page, title=u'Patrulhamento de IPs' + (wiki and u': ' + wiki or u''), **r)
def fetch_invoice_details(self, **kwargs):
master_ = kwargs.get("master_", '')
gst_ = kwargs.get("gst_", '')
if master_:
cf.log_("reached master_")
if self.invoice_type == "sale_invoice":
detail_table = sql.SQL("master.") + sql.Identifier("si_detail")
elif self.invoice_type == "purchase_invoice":
detail_table = sql.SQL("master.") + sql.Identifier("pi_detail")
elif gst_:
cf.log_("reached gst")
if self.invoice_type == "sale_invoice":
detail_table = sql.SQL("gst.") + sql.Identifier("si_detail")
if self.invoice_type == "purchase_invoice":
detail_table = sql.SQL("gst.") + sql.Identifier("pi_detail")
else:
# self.set_amount_before_freight()
# self.set_amount_after_freight()
detail_table = sql.Identifier(self.detail_table)
with conn() as cursor:
cursor.execute(
sql.SQL(
"select product_name, product_qty, product_unit, product_rate, product_discount, sub_total, product_print_name, packed from {} where id_invoice = %s order by id"
).format(detail_table), (self.id, ))
result = cursor.fetchall()
cf.log_("fetch_invoice_details completed")
return result
def main(cat=None):
if cat:
wiki = u'Wikipédia'
c = conn(wiki)
c.execute(u"""SELECT
redir,
COUNT(tl_from) marca,
redireciona_para
FROM (SELECT
CONCAT(IF(r.page_namespace = 4, 'Wikipédia', 'Predefinição'), ':', r.page_title) redir,
r.page_namespace ns,
r.page_title title,
CONCAT(IF(m.page_namespace = 4, 'Wikipédia', 'Predefinição'), ':', m.page_title) redireciona_para
FROM categorylinks
INNER JOIN page m ON cl_from = m.page_id
INNER JOIN redirect ON (rd_namespace, rd_title) = (m.page_namespace, m.page_title)
INNER JOIN page r ON rd_from = r.page_id
WHERE cl_to = ? AND m.page_namespace IN (4, 10)
) marcas
LEFT JOIN templatelinks ON (tl_namespace, tl_title) = (ns, title)
GROUP BY redir
ORDER BY marca""", (cat,))
r = c.fetchall()
r = [(redir.decode('utf-8'), int(num), predef.decode('utf-8')) for redir, num, predef in r]
if r:
resp = {'wiki': wiki, 'link': link(wiki), 'lista': r}
else:
resp = {'aviso': u'A consulta não retornou resultados'}
return render_template_string(page, title=u'Transclusões de redirecionamentos para predefinições da categoria ' + cat.replace(u'_', u' '), **resp)
else:
return render_template_string(page, title=u'Transclusões de redirecionametos para predefinições de uma categoria')
def update_opening_balance(type_):
owner_ = cf.prompt_("Enter Owner Name: ", cf.get_completer_list("nickname", type_.lower()), unique_="existing")
balance_ = cf.prompt_("Enter amount: ", [])
with conn() as cursor:
cursor.execute("update {} set opening_balance = %s where nickname = %s returning name, opening_balance".format("master."+type_), (balance_, owner_))
result = cursor.fetchall()
cf.pretty_(['name', 'balance'], result)
def main(cat=None):
if cat:
wiki = u'Wikipédia'
c = conn(wiki)
c.execute(u"""SELECT
CONCAT(IF(page_namespace = 4, 'Wikipédia', 'Predefinição'), ':', page_title) p,
COUNT(tl_title) t
FROM (SELECT
page_namespace,
page_title
FROM categorylinks
INNER JOIN page ON cl_from = page_id
WHERE cl_to = ? AND page_namespace IN (4, 10)
) predefs
LEFT JOIN templatelinks ON (page_namespace, page_title) = (tl_namespace, tl_title)
GROUP BY p
ORDER BY t""", (cat,))
r = c.fetchall()
r = [(u.decode('utf-8'), int(n)) for u, n in r]
if r:
resp = {'wiki': wiki, 'link': link(wiki), 'lista': r}
else:
resp = {'aviso': u'A consulta não retornou resultados'}
return render_template_string(page, title=u'Transclusões de predefinições da categoria ' + cat.replace(u'_', u' '), **resp)
else:
return render_template_string(page, title=u'Transclusões de predefinições de uma categoria')
def create_new_invoice_detail_in_db(invoice_detail_):
invoice_detail_.gst_amount = (Decimal(invoice_detail_.sub_total) *
Decimal(invoice_detail_.product_gst_rate) *
Decimal(0.01)).quantize(Decimal("1.00"))
cf.log_("db: create_new_invoice_detail_in_db")
sq = "insert into {} (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_discount, product_hsn, product_gst_rate, sub_total, product_print_name, packed, gst_amount, product_gst_name) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) returning id".format(
invoice_detail_.invoice_detail_type)
# sq = sql.SQL("insert into {} ({}) values ({}) returning id").format(sql.Identifier(invoice_detail_.invoice_detail_type), sql.SQL(', ').join(map(sql.Identifier, properties)), sql.SQL(', ').join(sql.Placeholder() * len(properties)))
with conn() as cursor:
cursor.execute(
sq,
(
invoice_detail_.invoice_.id, # 0
invoice_detail_.product_id, # 1
invoice_detail_.product_name, # 2
invoice_detail_.product_qty, # 3
invoice_detail_.product_unit, # 4
invoice_detail_.product_rate, # 5
invoice_detail_.product_discount, # 6
invoice_detail_.product_hsn, # 7
invoice_detail_.product_gst_rate, #8
invoice_detail_.sub_total, # 9
invoice_detail_.product_print_name, # 10
invoice_detail_.packed, # 11
invoice_detail_.gst_amount,
invoice_detail_.product_gst_name))
return cursor.fetchall()[0]
def view_summary():
with conn() as cursor:
cursor.execute(
"select name, place, sum(invoice_amount)-sum(money_amount)+master.customer.opening_balance as balance from master.sale_ledger_view join master.customer on master.customer.id = master.sale_ledger_view.id_owner group by name, place, customer.opening_balance order by balance desc"
)
result = cursor.fetchall()
columns = ['name', 'place', 'balance']
cf.pretty_(columns, result, right_align=['balance'])
# ob_list = [r[3] for r in result]
# print(ob_list)
# print("Frist OB: {}".format(result[3]))
# t = 0
# for a in ob_list:
# t = t + a
# print(t)
# import csv
# with open('total_check.csv', 'wt') as csv_file:
# writer = csv.writer(csv_file, delimiter = ',')
# for a in result:
# # csv_file.write(str(a))
# writer.writerow(a)
right_align_columns = ['balance']
# left_align_columns = ['name', 'place']
pt = PrettyTable(columns)
for a in result:
if a[2] is None:
a2 = ''
else:
a2 = a[2]
pt.add_row([a[0], a[1], a2])
pt.align = 'l'
for r in right_align_columns:
pt.align[r] = 'r'
print(pt)
def get_last_invoice_id(invoice_type):
with conn() as cursor:
cursor.execute(
sql.SQL("select max(id) from {}").format(
sql.Identifier(invoice_type)), ())
last_invoice_id = cursor.fetchone()[0]
cf.log_("last invoice id is {}".format(last_invoice_id))
return last_invoice_id
def delete_(self):
with conn() as cursor:
sq = sql.SQL(
"with deleted as (delete from {} where id = %s returning *) select count(*) from deleted"
).format(sql.Identifier(self.invoice_detail_type))
cursor.execute(sq, (self.id, ))
result = cursor.fetchone()
cf.log_("No of deletions: {}".format(result[0]))
def send_receipt_to_sale_led():
# one time use only
result = cf.cursor_(
sql.SQL("select date_, name_place, amount from bank.receipt"))
for a in result:
sq = "insert into bank.sale_led (type_, date_, name_place, amount) values (%s, %s, %s, %s) returning id"
with conn() as cursor:
cursor.execute(sq, ("receipt", a[0], a[1], a[2]))
def insert_records(self, t):
if self.owner_product == "customer_product":
with conn() as cursor:
execute_values(
cursor,
"insert into si_detail (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_hsn, product_gst_rate, sub_total, product_print_name, product_gst_name, gst_amount, product_cost, cost_sub_total) values %s returning id",
t)
result = cursor.fetchall()
if self.owner_product == "vendor_product":
print("Inserting pi_detail records...")
with conn() as cursor:
execute_values(
cursor,
"insert into pi_detail (id_invoice, id_product, product_name, product_qty, product_unit, product_rate, product_hsn, product_gst_rate, sub_total, product_print_name, product_gst_name, gst_amount) values %s returning id",
t)
result = cursor.fetchall()
return result
def get_invoice_detail_properties_from_db(invoice_detail_):
cf.log_("db: get_invoice_detail_properties_from_db")
with conn() as cursor:
cursor.execute(
sql.SQL("select {} from {} where id = %s").format(
sql.SQL(', ').join(map(sql.Identifier, properties)),
sql.Identifier(invoice_detail_.invoice_detail_type)),
(invoice_detail_.id, ))
return cursor.fetchone()
def psql_(query, **kwargs):
arg_ = kwargs.get('arg_', '')
with conn() as cursor:
cursor.execute(query, arg_)
try:
return cursor.fetchall()
except Exception as e:
print(e)
print('Nothing was returned from psql_ function')
def update_timestamp_(self):
rate_date = str(datetime.datetime.today())
with conn() as cursor:
cursor.execute(
sql.SQL(
"update {} set (timestamp_) = (%s) where id_owner = %s and id_product = %s and rate = %s"
).format(sql.Identifier(self.owner_product)),
(rate_date, self.id_owner, self.id_product, self.rate))
cf.print_('Updated owner_product timestamp_')
def insert_owner_product(self, **kwargs):
gst_ = kwargs.get('gst_', '')
rate_date = str(datetime.datetime.today())
if gst_:
update_fail_pass = self.update_timestamp_gst_rate()
if update_fail_pass == 'fail':
with conn() as cursor:
cursor.execute(
"insert into {} (id_owner, id_product, gst_rate, timestamp_) values (%s, %s, %s, %s) returning id"
.format(self.owner_product),
(self.id_owner, self.id_product, self.rate, rate_date))
else:
update_fail_pass = self.update_timestamp_non_gst_rate()
if update_fail_pass == 'fail':
with conn() as cursor:
cursor.execute(
"insert into {} (id_owner, id_product, rate, timestamp_) values (%s, %s, %s, %s) returning id"
.format(self.owner_product),
(self.id_owner, self.id_product, self.rate, rate_date))
def get_buy_rate(product_name):
with conn() as cursor:
cursor.execute(
"select * from temp_vendor_product where lower(product_name) like %s order by timestamp_ desc",
(product_name.lower(), ))
r = cursor.fetchall()
pt = PrettyTable(['name', 'owner', 'date', 'rate', 'discount'])
for a in r:
pt.add_row(a)
print(pt)
def create_new_invoice_in_db(invoice_):
cf.log_("db: create_new_invoice_in_db")
sq = "insert into {} (invoice_no, id_owner, owner_name, owner_place, date_, gst_owner_name) values (%s, %s, %s, %s, %s, %s) returning id".format(
invoice_.invoice_type)
with conn() as cursor:
cursor.execute(
sq,
(invoice_.no_, invoice_.owner.id, invoice_.owner.name,
invoice_.owner.place, invoice_.date_, invoice_.gst_owner_name))
return cursor.fetchone()[0]
def get_last_invoice_no_from_db(invoice_type, **kwargs):
gst_ = kwargs.get('gst_', '')
if gst_:
field_ = "gst_invoice_no"
else:
field_ = "invoice_no"
#TODO: lock table here and unlock after inserting in db
with conn() as cursor:
cursor.execute("select max({}) from {}".format(field_, invoice_type))
return cursor.fetchone()[0] # (None, ) is result when its null
def main(arg=None):
c = conn(u'Wikipédia')
c.execute(u"""SELECT
SUM(SUBSTR(cl_to, 23, 1) = '1') Q1,
SUM(SUBSTR(cl_to, 23, 1) = '2') Q2,
SUM(SUBSTR(cl_to, 23, 1) = '3') Q3,
SUM(SUBSTR(cl_to, 23, 1) = '4') Q4,
SUM(SUBSTR(cl_to, 10, 4) = 'bons') Q5,
SUM(SUBSTR(cl_to, 10, 10) = 'destacados') Q6
FROM (SELECT
cl_from,
cl_to
FROM categorylinks
WHERE cl_to LIKE '!Artigos\_de\_qualidade\__\_sobre\_%' OR cl_to LIKE '!Artigos\_destacados\_sobre\_%' OR cl_to LIKE '!Artigos\_bons\_sobre\_%'
GROUP BY cl_from) cl""")
r = c.fetchall()
if r:
r = map(int, r[0])
resp = {'qualidade': [u'{} ({}%)'.format(q, round(float(q) * 100 / sum(r), 2)) for q in r], 'soma': sum(r)}
else:
resp = {'aviso': u'Erro'}
return render_template_string(page, title=u'Qualidade das páginas', **resp)
def main(artigo=None):
if not artigo:
artigo = u'Wikipédia'
cat = artigo.split(u':')[0].lower() == u'categoria'
c = conn(u'Wikipédia')
if c:
if cat:
c.execute("SELECT cl_from FROM categorylinks WHERE cl_to = ? AND cl_type = 'page'", (artigo.split(u':')[1],))
ids = [int(l[0]) for l in c.fetchall()]
if len(ids) > 300:
return render_template_string(page, title=u'Evolução dos artigos da ' + artigo,
aviso=u'Muitos artigos na categoria, consulta abortada')
c.execute('''SELECT
rev_page,
SUBSTR(rev_timestamp, 1, 6) mes,
FLOOR(AVG(rev_len)) tamanho
FROM revision
WHERE rev_page IN (%s)
GROUP BY rev_page, mes''' % ','.join(str(i) for i in ids))
r = c.fetchall()
r = [i for p in {l[0] for l in r} for i in complete([(l[1], l[2]) for l in r if l[0] == p])]
r = [(m, sum(l[1] for l in r if l[0] == m)) for m in sorted({l[0] for l in r})]
r = {'artigo': artigo, 'query': [map(int, l) for l in r], 'cat': cat}
else:
c.execute('''SELECT
SUBSTR(rev_timestamp, 1, 6) mes,
FLOOR(AVG(rev_len)) tamanho
FROM page
INNER JOIN revision ON rev_page = page_id
WHERE page_namespace = 0 AND page_title = ?
GROUP BY mes''', (artigo,))
r = c.fetchall()
r = {'artigo': artigo, 'query': [map(int, l) for l in complete(r)], 'cat': cat}
else:
r = {}
title = u'Evolução ' + (cat and u'dos artigos da ' or u'do artigo ') + artigo
return render_template_string(page, title=title, **r)
def main(predef=None):
if predef:
wiki = u'Wikipédia'
c = conn(wiki)
c.execute(u"""SELECT
CONCAT('Predefinição', ':', page_title) p,
COUNT(tl_title) t
FROM (SELECT
page_title
FROM page
WHERE page_namespace = 10 AND page_title LIKE ?
) predefs
LEFT JOIN templatelinks ON page_title = tl_title AND tl_namespace = 10
GROUP BY p
ORDER BY t, p""", (predef + u'%',))
r = c.fetchall()
r = [(u.decode('utf-8'), int(n)) for u, n in r]
if r:
resp = {'wiki': wiki, 'link': link(wiki), 'lista': r}
else:
resp = {'aviso': u'A consulta não retornou resultados'}
return render_template_string(page, title=u'Transclusões de predefinições com o prefixo "' + predef.replace(u'_', u' ') + u'..."', **resp)
else:
return render_template_string(page, title=u'Transclusões de predefinições com um prefixo')
def main(args=None):
if not args:
wiki, filter = u'Wikipédia', None
elif args.replace(u'&', u'').replace(u'_', u'').isdigit():
wiki, filter = u'Wikipédia', args
elif args.split(':')[0] not in (u'Wikipédia', u'Wikilivros', u'Wikinotícias', u'Wikicionário', u'Wikiversidade', u'Wikiquote', u'Wikisource', u'Wikivoyage'):
return redirect('https://tools.wmflabs.org/ptwikis/Filters:' + args)
else:
args = args.split(u':')
wiki, filter = args[0], len(args) > 1 and args[1]
c = conn(wiki)
if c and not filter: # Todos os filtros
c.execute('''SELECT
F,
af_public_comments,
N,
A,
E,
D
FROM (
SELECT
afl_filter AS F,
SUM(afl_actions = '') AS N,
SUM(afl_actions = 'warn') AS A,
SUM(afl_actions = 'tag') AS E,
SUM(afl_actions = 'disallow') AS D
FROM abuse_filter_log
WHERE afl_timestamp > DATE_FORMAT(SUBDATE(NOW(), INTERVAL 30 DAY), '%Y%m%d%H%i%s')
GROUP BY afl_filter) AS stats
LEFT JOIN abuse_filter
ON F = af_id''')
r = c.fetchall()
r = [(f, t and t.decode('utf8') or u'', int(n), int(a), int(e), int(d)) for f, t, n, a, e, d in r]
r = {'wiki': wiki, 'link': link(wiki), 'filters': r, 'max': max(map(max, [f[2:] for f in r]))}
return render_template_string(allfilters, title='Filtros', **r)
elif c and filter: # Um ou mais filtros ao longo do tempo
query = '''SELECT
SUBSTR(afl_timestamp, 1, 8) dia,
SUM(afl_actions = '') nada,
SUM(afl_actions = 'tag') etiq,
SUM(afl_actions = 'warn') aviso,
SUM(afl_actions = 'disallow') desaut
FROM abuse_filter_log
WHERE afl_filter = ? AND afl_timestamp > DATE_FORMAT(SUBDATE(NOW(), INTERVAL 1 YEAR), '%Y%m%d%H%i%s')
GROUP BY WEEK(afl_timestamp)
ORDER BY dia'''
filter = [f for f in filter.split('&') if f][0:10]
filters, names = {}, {}
for f in filter:
c.execute(query, (f,))
r = c.fetchall()
filters[f] = []
for l in r:
if l[0][4:] == '0101' and filters[f] and int(str(filters[f][-1][0])[4:]) > 1225:
filters[f][-1] = [filters[f][-1][0]] + [int(l[i]) + filters[f][-1][i] for i in range(1, 5)]
else:
filters[f].append(map(int, l))
c.execute('SELECT af_public_comments FROM abuse_filter WHERE af_id = ?', (f,))
names[f] = 'Filtro ' + f + u' – ' + c.fetchone()[0].decode('utf-8').replace(u'"', u'\\"')
title = len(filter) == 1 and names[filter[0]] or ' + '.join(['Filtro ' + f for f in filter])
r = {'wiki': wiki, 'link': link(wiki), 'filters': filters, 'names': names}
return render_template_string(filtergraph, title=title, **r)
else:
r = {}
return render_template_string(allfilters, title=u'Filtros', **r)
def main(args=None):
if not args:
return render_template_string(page, title=u'Listas')
args = args.split(u'&')
# Se não for página especial
if u':' in args[0]:
c = conn('p50380g50592__pt', 's2.labsdb')
c.execute('DESC acessos')
mes = c.fetchall()[-1][0]
params = {}
ordens = {u'mais_visitas': 'ac_201405 DESC, page_len DESC',
u'menos_visitas': 'ac_201405, page_len DESC',
u'maior_tamanho': 'page_len DESC, ac_201405 DESC',
u'menor_tamanho': 'page_len, ac_201405 DESC'}
for arg in args:
if arg.startswith(u'cat:'):
params['cat' in params and 'cat2' or 'cat'] = arg[4].upper() + arg[5:]
elif arg.startswith(u'predef:'):
params['predef' in params and 'predef2' or 'predef'] = arg[7].upper() + arg[8:].capitalize()
elif arg.startswith(u'marca:'):
params['marca' in params and 'marca2' or 'marca'] = arg[6:]
elif arg.startswith(u'ordenar:'):
params['ordenar'] = arg[8:]
r = False
ordenar = 'ordenar' in params and params['ordenar'] in ordens and ordens[params['ordenar']] or ordens['mais_visitas']
# Marca nos parâmetros
if 'marca' in params:
if 'cat' in params:
marca2, filtro2, textofiltro = u'', u"INNER JOIN ptwiki_p.categorylinks cc ON a.page_id = cc.cl_from AND cc.cl_to = ?", (params['cat'],)
title = u'Lista de páginas com marca sobre ' + params['marca'].replace(u'_', u' ') + u' e a categoria ' + params['cat'].replace(u'_', u' ')
elif 'predef' in params:
marca2, filtro2, textofiltro = u'', u"INNER JOIN ptwiki_p.templatelinks ON a.page_id = tl_from AND tl_namespace = 10 AND tl_title = ?", (params['predef'],)
title = u'Lista de páginas com marca sobre ' + params['marca'].replace(u'_', u' ') + u' e a predefinição ' + params['predef'].replace(u'_', u' ')
elif 'marca2' in params:
marca2, filtro2, textofiltro = u"INNER JOIN ptwiki_p.categorylinks cc ON c.cl_from = cc.cl_from AND cc.cl_to LIKE ?", u'', (u'!Artigos_de_importância_%_sobre_' + params['marca2'],)
title = u'Lista de páginas com marcas sobre ' + params['marca'].replace(u'_', u' ') + u' e sobre ' + params['marca2'].replace(u'_', u' ')
else:
marca2, filtro2, textofiltro = u'', u'', ()
title = u'Lista de páginas com marca sobre ' + params['marca'].replace(u'_', u' ')
c = conn('p50380g50592__pt', 's2.labsdb')
c.execute(u"""SELECT a.page_namespace, a.page_title, {mes}, a.page_len
FROM ptwiki_p.categorylinks c
{0}
INNER JOIN ptwiki_p.page d ON c.cl_from = page_id
INNER JOIN ptwiki_p.page a ON a.page_namespace = (d.page_namespace - 1) AND d.page_title = a.page_title
{1}
LEFT JOIN acessos ON a.page_id = ac_page AND ac_wiki = 'w'
WHERE NOT page_is_redirect AND c.cl_to LIKE ?
ORDER BY {2}
LIMIT 200""".format(marca2, filtro2, ordenar, mes=mes), textofiltro + (u'!Artigos_de_importância_%_sobre_' + params['marca'],))
r = c.fetchall()
r = [((i[0] in ns and ns[i[0]] or u'') + i[1].decode('utf-8'), u'{} visita{}'.format(i[2] or u'Nenhuma', i[2] and i[2] > 0 and u's' or u''), u'{} bytes'.format(i[3])) for i in r]
# Categoria nos parâmetros
elif 'cat' in params:
if 'predef' in params:
filtro2, textofiltro = u"INNER JOIN ptwiki_p.templatelinks ON c.cl_from = tl_from AND tl_namespace = 10 AND tl_title = ?", (params['predef'],)
title = u'Lista de páginas com a categoria ' + params['cat'].replace(u'_', u' ') + u' e a predefinição ' + params['predef'].replace(u'_', u' ')
if 'cat2' in params:
filtro2, textofiltro = u"INNER JOIN ptwiki_p.categorylinks cc ON c.cl_from = cc.cl_from AND cc.cl_to = ?", (params['cat'],)
title = u'Lista de páginas com a categoria ' + params['cat'].replace(u'_', u' ') + u' e a categoria ' + params['cat'].replace(u'_', u' ')
else:
filtro2, textofiltro = u'', ()
title = u'Lista de páginas com a categoria ' + params['cat'].replace(u'_', u' ')
c = conn('p50380g50592__pt', 's2.labsdb')
c.execute(u"""SELECT page_namespace, page_title, {mes}, page_len
FROM ptwiki_p.categorylinks c
{0}
INNER JOIN ptwiki_p.page ON c.cl_from = page_id
LEFT JOIN acessos ON page_id = ac_page AND ac_wiki = 'w'
WHERE NOT page_is_redirect AND c.cl_to = ? AND c.cl_type = 'page'
ORDER BY {1}
LIMIT 200""".format(filtro2, ordenar, mes=mes), textofiltro + (params['cat'],))
r = c.fetchall()
r = [((i[0] in ns and ns[i[0]] or u'') + i[1].decode('utf-8'), u'{} visita{}'.format(i[2] or u'Nenhuma', i[2] and i[2] > 0 and u's' or u''), u'{} bytes'.format(i[3])) for i in r]
# Predefinição nos parâmetros
elif 'predef' in params:
if 'predef2' in params:
predef2, textopredef2 = u"INNER JOIN ptwiki_p.templatelinks b ON a.tl_from = b.tl_from AND b.tl_namespace = 10 AND b.tl_title = ?", (params['predef2'],)
title = u'Lista de páginas com a predefinição ' + params['predef'].replace(u'_', u' ') + u' e a predefinição ' + params['predef2'].replace(u'_', u' ')
else:
predef2, textopredef2 = u'', ()
title = u'Lista de páginas com a predefinição ' + params['predef'].replace(u'_', u' ')
c = conn('p50380g50592__pt', 's2.labsdb')
c.execute(u"""SELECT page_namespace, page_title, {mes}, page_len
FROM ptwiki_p.templatelinks a
{0}
INNER JOIN ptwiki_p.page ON a.tl_from = page_id
LEFT JOIN acessos ON page_id = ac_page AND ac_wiki = 'w'
WHERE NOT page_is_redirect AND a.tl_namespace = 10 AND a.tl_title = ?
ORDER BY {1}
LIMIT 200""".format(predef2, ordenar, mes=mes), (predef2 and (params['predef2'],) or ()) + (params['predef'],))
r = c.fetchall()
r = [((i[0] in ns and ns[i[0]] or u'') + i[1].decode('utf-8'), u'{} visita{}'.format(i[2] or u'Nenhuma', i[2] and i[2] > 0 and u's' or u''), u'{} bytes'.format(i[3])) for i in r]
if r:
if 'ordenar' in params and params['ordenar'] in (u'maior_tamanho', u'menor_tamanho'):
r = [(i[0], i[2], i[1]) for i in r]
return render_template_string(page, title=title, query=r, mes=u'{} de {}'.format(meses[mes[7:]], mes[3:7]))
else:
return render_template_string(page, title=title, aviso=u'<span style="color:#555555"><b>Nenhuma página com esses parâmetros</b></span>')
# Páginas com mais reversões
elif args[0] == u'Páginas_com_mais_reversões':
c = conn('ptwiki')
c.execute(u"""SELECT
rc_namespace,
rc_title,
SUM(rc_comment LIKE 'Foram [[WP:REV|%' OR rc_comment LIKE 'bot: revertidas edições de%' OR rc_comment LIKE 'Reversão de uma ou mais edições de%') rev
FROM recentchanges
WHERE TIMESTAMPDIFF(DAY, rc_timestamp, CURDATE()) <= 10
GROUP BY rc_namespace, rc_title
HAVING rev > 0
ORDER BY rev DESC
LIMIT 100""")
r = c.fetchall()
r = [((i[0] in ns and ns[i[0]] or u'') + i[1].decode('utf-8'), u'{} reversões'.format(i[2])) for i in r]
return render_template_string(page, title=u'Lista das páginas com mais reversões nos últimos 10 dias', query=r)
# Usuários mais revertidos
elif args[0] == u'Usuários_mais_revertidos':
import re
user = re.compile(r'pecial:Contrib.+?/(.+?)\|')
c = conn('ptwiki')
c.execute(u"""SELECT
SUBSTR(rc_comment, 1, 100) user,
COUNT(*)
FROM recentchanges
WHERE TIMESTAMPDIFF(DAY, rc_timestamp, CURDATE()) <= 5 AND rc_type = 0 AND (rc_comment LIKE 'bot: revertidas edições de%' OR rc_comment LIKE 'Foram [[WP:REV|%' OR rc_comment LIKE 'Reversão de uma ou mais edições de%')
GROUP BY user""")
r = c.fetchall()
r = [(user.search(i[0]), int(i[1])) for i in r if ':CITE|' not in i[0]]
r = [(i[0].group(1).decode('utf-8'), i[1]) for i in r if i[0]]
r2 = {}
for i in r:
r2[i[0]] = i[0] in r2 and r2[i[0]] + i[1] or i[1]
r = sorted(r2.items(), key=lambda i:i[1], reverse=True)[0:70]
c.execute(u"""SELECT
afl_user_text user,
COUNT(*)
FROM abuse_filter_log
WHERE TIMESTAMPDIFF(DAY, afl_timestamp, CURDATE()) <= 5 AND afl_user_text IN ({})
GROUP BY user""".format(u','.join(u'?' * len(r))), tuple(i[0] for i in r))
r2 = c.fetchall()
r2 = dict((i[0].decode('utf-8'), int(i[1])) for i in r2)
c.execute(u"""SELECT
ipb_address
FROM ipblocks
WHERE ipb_address IN ({}) AND (ipb_expiry = 'ininity' OR ipb_expiry > DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'))""".format(u','.join(u'?' * len(r))), tuple(i[0] for i in r))
r3 = [i[0].decode('utf-8') for i in c.fetchall()]
r = [(i[0], u'revertido {} vezes'.format(i[1]), i[0] in r2 and u'disparou filtros {} {}'.format(r2[i[0]], r2[i[0]] == 1 and u'vez' or u'vezes') or u'', i[0] in r3 and u'Bloqueado' or u'') for i in r]
return render_template_string(page, title=u'Lista dos usuários mais revertidos nos últimos 5 dias', query=r, user=True)
else:
return render_template_string(page, title=u'Listas', aviso=u'Erro')
def main(user=None):
if not user:
return render_template_string(nouserpage, title=u'Edições e grupos de usuários')
# 'nome da wiki no banco de dados': (tempo-voto, edições-voto, tempo-administrador, edições-administrador, outro-nome, outro-tempo, outro-edições)
ptwikis = {'ptwiki': (90, 300, 182, 2000, 'eliminador', 182, 1000),
'ptwikibooks': (30, 50),
'ptwikiversity': (45, 0),
'ptwiktionary': (30, 200, 30, 100),
'ptwikinews': (30, 50),
'ptwikiquote': (30, 100),
'ptwikisource': (45, 100),
'ptwikivoyage': None,
'commonswiki': None,
'wikidatawiki': None,
'specieswiki': None}
groups = {'autoreviewer': 'autorrevisor', 'rollbacker': 'reversor', 'bureaucrat': 'burocrata', 'checkuser': '******' ,'oversight': 'supervisor',
'reviewer': 'revisor', 'import': 'importador'}
uploads = {'ptwiki': 0, 'commonswiki': 0}
response = {}
user = user.replace(u'_', u' ')
for wiki in ptwikis:
c = conn(wiki)
if not c:
response[wiki] = {'time': u'Erro', 'total': u'?', 'main': u'?', 'created': u'?', 'vote': u'', 'sysop': u'', 'others': u''}
continue
#Consulta edições totais, páginas criadas e primeira edição, separando em domínio principal (main) e outros domínios (others)
c.execute('''SELECT
(CASE page_namespace WHEN 0 THEN "main" ELSE "others" END) AS namespace,
COUNT(*),
SUM(CASE WHEN rev_parent_id = 0 AND page_is_redirect = 0 THEN 1 ELSE 0 END),
MIN(rev_timestamp)
FROM revision_userindex
FULL JOIN page
ON page_id = rev_page
WHERE rev_user != 0 AND rev_user_text = ?
GROUP BY namespace''', (user,))
r = c.fetchall()
if not r:
response[wiki] = {'time': u'Nunca editou', 'total': u'0', 'main': u'0', 'created': u'0', 'vote': u'—', 'sysop': u'—', 'others': u'—', 'uploads': u'0'}
continue
c.execute('SELECT ug_group FROM user LEFT JOIN user_groups ON user_id = ug_user WHERE user_name = ?', (user,))
g = c.fetchall()
u = None
if wiki in uploads:
c.execute('SELECT COUNT(*) FROM image WHERE img_user_text = ?', (user,))
u = c.fetchall()
g = g and [i in groups and groups[i] or i for i in map(lambda i:i[0], g) if i] or []
u = u and int(u[0][0]) or u'0'
# Tempo desde a primeira edição
t = len(r) == 2 and min(r[0][3], r[1][3]) or r[0][3]
days = (date.today() - date(int(t[0:4]), int(t[4:6]), int(t[6:8]))).days
wikitime = u'{}/{}/{}<br />{}'.format(t[6:8], t[4:6], t[0:4], days >= 365 and (days/365 > 1 and str(days/365) + ' anos' or '1 ano') or
days == 1 and '1 dia' or str(days) + ' dias')
# Edições totais
total = len(r) == 2 and r[0][1] + r[1][1] or r[0][1]
# Edições e páginas criadas no domínio principal
main, created = r[0][0] == u'main' and r[0][1:3] or len(r) == 2 and r[1][1:3] or [0,0]
# Direito ao voto
vote = ptwikis[wiki] and (days >= ptwikis[wiki][0] and (main >= ptwikis[wiki][1] and u'<span style="color:#080"><b>Sim</b></span>' or
u'<span style="color:#800">Não</span><br/><small>menos de {} edições</small>'.format(ptwikis[wiki][1])) or
u'<span style="color:#800">Não</span><br/><small>menos de {} dias{}</small>'.format(ptwikis[wiki][0],
main < ptwikis[wiki][1] and u' e de {} edições'.format(ptwikis[wiki][1]) or u'')) or u'—'
# Administrador
sysop = 'sysop' in g and u'<span style="color:#080"><b>É administrador</b></span>' or ptwikis[wiki] and len(ptwikis[wiki]) > 2 and (
days >= ptwikis[wiki][2] and (main >= ptwikis[wiki][3] and u'Pode candidatar-se' or
u'<span style="color:#800">Não pode</span><br/><small>menos de {} edições</small>'.format(ptwikis[wiki][3])) or
u'<span style="color:#800">Não pode</span><br/><small>menos de {} dias{}</small>'.format(ptwikis[wiki][2],
main < ptwikis[wiki][1] and u' e de {} edições'.format(ptwikis[wiki][1]))) or u'—'
# Outros direitos
others = ptwikis[wiki] and len(ptwikis[wiki]) == 7 and 'sysop' not in g and ptwikis[wiki][4] not in g and (days >= ptwikis[wiki][5] and
(main >= ptwikis[wiki][6] and u'Pode candidatar-se a {}'.format(ptwikis[wiki][4]) or
u'<span style="color:#800">Não pode candidatar-se a {}</span><br/><small>menos de {} edições</small>'.format(ptwikis[wiki][4], ptwikis[wiki][6])) or
u'<span style="color:#800">Não pode candidatar-se a {}</span><br/><small>menos de {} dias{}</small>'.format(ptwikis[wiki][4], ptwikis[wiki][5],
total < ptwikis[wiki][1] and u' e de {} edições'.format(ptwikis[wiki][6]))) or None
others = g and u'<br />'.join((others and [others] or []) + [u'<span style="color:#080"><b>{}</b></span>'.format(i) for i in g if i != 'sysop']) or others or u'—'
response[wiki] = {'time': wikitime, 'total': str(total), 'main': str(main), 'created': str(created), 'vote': vote, 'sysop': sysop, 'others': others, 'uploads': u}
variables = dict([('{}_{}'.format(item, wiki), response[wiki][item])for wiki in response for item in response[wiki]])
variables['user'] = user
return render_template_string(page, title=u'Edições e grupos de ' + user, **variables)
def network_conn(nettype):
netname = resolve_network(nettype)
conn = database.conn(netname)
conn.nettype = nettype
return conn
