def remove_query(usernames):
for username in usernames:
query = f"""SET NOCOUNT ON; delete from ConnectionInformation
where UserName = '******'
and ConnectionID = '{username[1]}'
select 1
"""
connect_to_db(query, 'admin')
def set_headers():
results = {}
result, headers = connect_to_db(query_headers(), 'guest')
fields = [column[0] for column in headers]
for row in result:
results[row.product_code] = row.description
return results
def get_sessions():
query = """select * from ConnectionInformation
where UserName not like '$%'
and UserName <> 'sa'
and RecordDate < CONVERT(VARCHAR(10), getdate(), 111)
"""
return connect_to_db(query, 'guest')
on il.item = i.item
left outer join whse w
on il.whse = w.whse
left outer join location l
on il.loc = l.loc
left outer join (select il.item, il.loc, il.whse, (SELECT SUM (itemwhse.qty_on_hand)FROM itemwhse WHERE itemwhse.item = il.item) AS qty_sum from itemloc il
left outer join itemwhse iw
on il.item = iw.item
AND il.whse = iw.whse
where (SELECT SUM (itemwhse.qty_on_hand) as sum_qty FROM itemwhse WHERE itemwhse.item = il.item) < 0
group by il.item, il.loc, il.whse) r
on r.item = il.item
and r.loc = il.loc
and r.whse = il.whse
where r.qty_sum < 0
order by il.whse, il.item, il.NewRank
"""
return select_query
if __name__ == '__main__':
result, headlines = connect_to_db(get_query(), 'guest')
if result:
filename = f"../../negative_stock_report_{get_dates_today()}.xlsx"
filename = update_path(filename)
workbook, filename = create_new_workbook(filename, 'ItemStockRoomLocation')
worksheet = insert_data(workbook.worksheets[0], result, headlines)
get_sum(worksheet)
save_new_workbook(workbook, filename)
send_email(set_date_weekly(), 'Larry')
from db.connect_to_db import connect_to_db
def query():
return 'select username, EmailAddress, UserDesc from usernames where status = 0 and EmailAddress IS NOT NULL'
def update_query(result):
for i in result:
if i[1].split('@')[1] == 'website':
if i[0][:-1] == i[2].split(" ")[0].lower():
name = i[1].split(" ")[0][:1].lower() + i[2].split(
" ")[1].lower()
else:
name = i[1].split('@')[0]
email_address = name + '@website'
query = f"""update usernames
set EmailAddress = '{email_address}'
where username = '******'"""
print(query)
if __name__ == '__main__':
result, _ = connect_to_db(query(), 'admin')
update_query(result)
def query_report():
connect_to_db(task_query(), 'admin')
while not check_file():
time.sleep(10)