def search_list_one(user_id):
#Назначим кнопки
markup = types.InlineKeyboardMarkup()
markup.add(
types.InlineKeyboardButton(
text=u'\U0001F30D Искать каждый товар отдельно',
callback_data='locallist_one:'), )
#Проверим что в списке есть товары
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
SQL = 'select count(distinct(product_id)) from user_product_list where chat_id = %s'
cursor.execute(SQL, (user_id, ))
products = cursor.fetchone()
if products[0] == 0:
bot.send_message(user_id,
'Сначала добавьте товары в список для поиска')
cursor.close()
conn.close()
else:
#Ищем аптеки с поответствием по списку товара
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
SQL = """\
select r.name, r.producer, p3.name, p3.address, p3.mode, p3.latitude, p3.longitude, p3.phone, t.way, t.price from user_product_list p
inner join product r on r.nommodif = p.product_id
inner join users u on u.chat_id = p.chat_id
inner join store p3 on p3.city = u.city and r.company = p3.company
inner join
(
select distinct(pl.product_id) product_id, p2.price, min(get_way(p3.latitude ,p3.longitude,u.latitude,u.longitude)) way from user_product_list pl
inner join users u on u.chat_id = pl.chat_id
inner join stock p2 on p2.product_id = pl.product_id
inner join store p3 on p3.company = p2.company and p3.name = p2.store and p3.city = u.city
where pl.chat_id = %s
group by pl.product_id, p2.price
) t
where p.chat_id = %s
and get_way(p3.latitude ,p3.longitude,u.latitude,u.longitude)=t.way and r.nommodif = t.product_id
group by r.name, r.producer, p3.name, p3.address, p3.mode, p3.latitude, p3.longitude, p3.phone, t.way, t.price
"""
cursor.execute(SQL, (
user_id,
user_id,
))
stores = cursor.fetchall()
for store in stores:
try:
bot.send_venue(user_id, store[5], store[6],
store[2] + ' (' + str(store[8]) + ' м.)',
store[3])
bot.send_message(
user_id,
'*' + store[0] + '*\n' + store[1] + '\n' + 'Цена: ' +
str(store[9]) + ' тенге\n\n' + store[4] + '\n' + 'Тел: ' +
store[7],
parse_mode='markdown',
)
except Exception as e:
print(e)
cursor.close()
conn.close()
j = json.load(open(f, 'r'))
jsons.append(j)
#############
### MYSQL ###
#############
# Connection
config = {
'user': '******',
'password': '******',
'host': 'localhost',
'raise_on_warnings': True,
}
try:
cnx = MySQLConnection(**config)
except Error as e:
print "[ERROR] Could not connect to MySQL database."
exit(1)
ctrl = Controller(cnx) # Instantiate controller
# Create database
ctrl.get_db('ocervell')
# Create and populate table with each json file
for f in files:
name = basename(f).lower().replace('.json','')
args = ("`doc` json DEFAULT NULL",
"`updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
)
def update_table(table_name):
db_config = read_db_config()
data_dir = '{0}/save-on-data'.format(cwd)
valid_name = table_name.replace(',', '')
files = os.listdir('{0}/{1}/'.format(data_dir, table_name))
if not files:
print('{0} directory is empty...'.format(table_name))
return
query = """
DROP TABLE IF EXISTS `{0}`;
CREATE TABLE {0} (brand VARCHAR(40), name VARCHAR(50),
category VARCHAR (200),
current_price VARCHAR(20),
regular_price VARCHAR(20), size VARCHAR(20),
current_unit_price VARCHAR(20),
description VARCHAR(200), sku VARCHAR(20));
INSERT INTO {0} VALUES
""".format(valid_name)
for file in files:
file_name = '{0}/{1}/{2}'.format(data_dir, table_name, file)
with open(file_name) as r:
table_data = json.load(r)
for pe in table_data:
query += '("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}"),'.format(
pe['brand'].replace('\'', '').replace('\"', ''),
pe['name'].replace('\'', '').replace('\"', ''),
pe['category'].replace('\'', '').replace('\"', ''),
pe['current_price'].replace('\'', '').replace('\"', ''),
pe['regular_price'].replace('\'', '').replace('\"', ''),
pe['size'].replace('\'', '').replace('\"', ''),
pe['current_unit_price'].replace('\'', '').replace('\"', ''),
pe['description'].replace('\'', '').replace('\"', ''),
pe['sku'].replace('\'', '').replace('\"', ''))
try:
query = query[:-1]
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
results = cursor.execute(query, multi=True)
i = 1
for result in results:
if i % 3 == 0:
print('Inserting into saveon_db.`{0}`'.format(valid_name))
i += 1
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
api = "tm"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "190143")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
inventory_source = x['row'][0]
media = x['row'][1]
ad_opportunities = x['row'][2]
market_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_errors = x['row'][6]
ad_revenue = x['row'][7]
aol_cost = x['row'][7]
epiphany_gross_revenue = x['row'][7]
tm_revenue = x['row'][7]
clicks = x['row'][8]
iab_viewability_measurable_ad_impressions = x['row'][9]
iab_viewable_ad_impressions = x['row'][10]
platform = '5'
list =(inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, tm_revenue, clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
# print(list)
sql = """INSERT INTO tm_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \
"%s"*.64, "%s"*.24, "%s", "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, \
market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \
epiphany_gross_revenue, tm_revenue, clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed')
except Error as error:
print(error)
finally:
conn.close()
print('Connection Closed')
def __init__(self):
self.configPath = "./util/config/db_config_inner.ini"
self.dbconfig = cu.read_db_config(self.configPath)
self.connector = MySQLConnection(charset='utf8', **self.dbconfig)
def getUsers(self):
connect = MySQLConnection(**config)
cursor = connect.cursor()
cursor.execute("select * from users")
return cursor.fetchall()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS v3_core_yesterday_media"
cursor.execute(sql)
sql = "CREATE TABLE v3_core_yesterday_media (date varchar(25), inventory_source varchar(255), media varchar(255) \
CHARACTER SET 'utf8',ad_opportunities bigint, market_opportunities bigint, ad_attempts bigint, \
ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), clicks int, \
iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(
"daf5fa63-56c4-4279-842e-639c9af75750",
"C5eBl8aErmCMO2+U85LGpw")
print logintoken
# Make Calls And Write To DB:
result = aol_api.run_existing_report(logintoken, "143992")
#print(result)
info = json.loads(result)
#print(info)
# Make Calls And Write To DB:
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
media = x['row'][2].replace('"', " ")
ad_opportunities = x['row'][3]
market_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_errors = x['row'][7]
ad_revenue = x['row'][8]
clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
list = (date, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions)
#print(list)
sql = """INSERT INTO v3_core_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s")""" % (date, inventory_source, media, ad_opportunities, market_opportunities, \
ad_attempts, ad_impressions, ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def searchhn() :
global aa7
aaa = aa7.get()
if aaa.isdigit():
print("is number")
else:
print("is not number")
sv1 = StringVar(root,value="HN ต้องใส่ตัวเลขเท่านั้น")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
initkri()
return
khn2 = ""
try:
print('Connecting to MySQL database...')
print(db_config)
conn = MySQLConnection(**db_config)
# conn = MySQLdb.connect (charset='utf8',host = "",user = "",passwd =,db = "")
print('Connection Successful!!!')
cursor = conn.cursor ()
curex ="SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and hn="+aaa.strip()+";"
cursor.execute (curex)
acount = 0
while True:
rows = cursor.fetchmany ()
if not rows:
cursor.close ()
conn.close ()
if acount == 0:
print("ไม่พบid13 นี้")
sv1 = StringVar(root,value="ไม่พบid13 นี้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
initkri()
if acount >= 2:
print("มีid13 จำนวน"+str(acount))
sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount))
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
break
for row in rows:
khn = row[0]
khn2 = khn2+","+str(khn)
print("HN =:", row[0])
sv1 = StringVar(root,value=khn)
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
# Label(root,text=row[5]).grid(row=5,column=4) #Creating label
Label(root,text=" "+row[5].strip()+" ").grid(row=12,column=6) #Creating label
print(row[5])
sv3 = StringVar(root,value=khn2)
aa3=Entry(root,textvariable=sv3) #creating entry box
aa3.grid(row=6,column=6)
sv4 = StringVar(root,value=row[1]+" "+row[2])
aa4=Entry(root,textvariable=sv4) #creating entry box
aa4.grid(row=7,column=1)
sv5 = StringVar(root,value='XN:'+row[3])
aa5=Entry(root,textvariable=sv5) #creating entry box
aa5.grid(row=7,column=4)
sv6 = StringVar(root,value=str(row[4]))
aa6=Entry(root,textvariable=sv6) #creating entry box
aa6.grid(row=7,column=6)
print(khn)
print(row[1]+" "+row[2])
acount += 1
print(acount)
# except MySQLdb.Error as e:
except Exception as e:
print (e)
sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
print("ไม่สามารถเชื่อม mysql ได้")
except :
print("Unknow error occured")
def searchnamebirth():
khn2 = ""
try:
print('Connecting to MySQL database...')
print(db_config)
conn = MySQLConnection(**db_config)
print('Connection Successful!!!')
cursor = conn.cursor ()
myfile = open('../Dropbox/krifoxone/kriid.csv','r')
data=myfile.readline()
list=data.split(",")
kid13=list[0]
print(kid13)
name = str(list[8],"tis-620")
# name1 = list[8].encode('tis-620')
surname = str(list[9],"tis-620")
# surname1 = list[9].encode('tis-620')
year1 = str(int(list[2][0:4])-543)
birth = year1+"-"+list[2][4:6]+"-"+list[2][6:8]
print(name)
print(surname)
print(birth)
cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+name.strip()+"' and surname='"+surname.strip()+"' and brdate='"+birth+"';")
acount = 0
while True:
rows = cursor.fetchmany ()
if not rows:
cursor.close ()
conn.close ()
if acount == 0:
print("ไม่พบชื่อ นี้")
sv1 = StringVar(root,value="ไม่พบชื่อ นี้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
if acount >= 2:
print("มีid13 จำนวน"+str(acount))
sv1 = StringVar(root,value="มีชื่อ จำนวน"+str(acount))
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
sv = StringVar(root,value='ค้นชื่อ เรียบร้อย')
a=Entry(root,textvariable=sv) #creating entry box
a.grid(row=5,column=1)
print("Find name Ready")
break
for row in rows:
# if row is None and acount>0: # row ==None
khn = row[0]
khn2 = khn2+","+str(khn)
print("HN =:", row[0])
sv1 = StringVar(root,value=khn)
aa=Entry(root,textvariable=sv1) #HN
aa.grid(row=5,column=6)
Label(root,text=kid13).grid(row=5,column=4) #id13
# sv2 = StringVar(root,value=kid13)
# aa2=Entry(root,textvariable=sv2) #creating entry box
# aa2.grid(row=6,column=1)
sv3 = StringVar(root,value=khn2)
aa3=Entry(root,textvariable=sv3) #HN2
aa3.grid(row=6,column=6)
sv4 = StringVar(root,value=row[1]+" "+row[2])
aa4=Entry(root,textvariable=sv4) #name
aa4.grid(row=7,column=1)
sv5 = StringVar(root,value='XN:'+row[3])
aa5=Entry(root,textvariable=sv5) #XN
aa5.grid(row=7,column=4)
sv6 = StringVar(root,value=str(row[4]))
aa6=Entry(root,textvariable=sv6) #brdate
aa6.grid(row=7,column=6)
# sv8 = StringVar(root,value=name+' '+surname)
# aa8=Entry(root,textvariable=sv8)
# aa8.grid(row=9,column=1)
print(khn)
print(row[1]+" "+row[2])
# +" "+row[2]
acount += 1
except Exception as e:
print (e)
sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
print("ไม่สามารถเชื่อม mysql ได้")
except :
print("Unknow error occured")
def searchid13():
global aa2
aaa9 = aa2.get()
if aaa9.find('?;600764') > 0:
aaa = aaa9[aaa9.find('?;600764')+8:aaa9.find('?;600764')+21]
print(aaa)
aa2.delete(0,250) # aa8.destroy() to delete block aa8
aa2.insert(0,aaa)
else:
aaa = aaa9
print(aaa)
if aaa.isdigit():
print("is number")
else:
print("is not number")
sv1 = StringVar(root,value="ต้องใส่ตัวเลขเท่านั้น")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
initkri()
return
if len(aaa)!= 13:
print("ใส่เลขไม่ครบ 13 หลัก")
sv1 = StringVar(root,value="ใส่เลขไม่ครบ 13 หลัก")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
return
khn2 = ""
try:
print(db_config)
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
#conn = MySQLdb.connect(charset='utf8', init_command='SET NAMES UTF8')
print('Connection Successful!!!')
cursor = conn.cursor ()
cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and id13='"+aaa.strip()+"';")
acount = 0
while True:
rows = cursor.fetchmany ()
if not rows:
cursor.close ()
conn.close ()
if acount == 0:
print("ไม่พบid13 นี้")
sv1 = StringVar(root,value="ไม่พบid13 นี้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
initkri()
if acount >= 2:
print("มีid13 จำนวน"+str(acount))
sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount))
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
messagebox.showinfo("Information","มี id13 ซ้ำกันจำนวน "+str(acount)+" ให้แก้ไขด้วย") # An information box
break
for row in rows:
khn = row[0]
khn2 = khn2+","+str(khn)
print("HN =:", row[0])
sv1 = StringVar(root,value=khn)
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
# Label(root,text=aaa.strip()).grid(row=5,column=4) #Creating label
Label(root,text=row[5]).grid(row=12,column=6) #Creating label
sv3 = StringVar(root,value=khn2)
aa3=Entry(root,textvariable=sv3) #creating entry box
aa3.grid(row=6,column=6)
sv4 = StringVar(root,value=row[1]+" "+row[2])
aa4=Entry(root,textvariable=sv4) #creating entry box
aa4.grid(row=7,column=1)
sv5 = StringVar(root,value='XN:'+row[3])
aa5=Entry(root,textvariable=sv5) #creating entry box
aa5.grid(row=7,column=4)
sv6 = StringVar(root,value=str(row[4]))
aa6=Entry(root,textvariable=sv6) #creating entry box
aa6.grid(row=7,column=6)
print(khn)
print(row[1]+" "+row[2])
acount += 1
print(acount)
except Exception as e:
print (e)
sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
print("ไม่สามารถเชื่อม mysql ได้")
except :
print("Unknow error occured")
def copyandfindid13():
khn2 = ""
try:
print('Connecting to MySQL database...')
print(db_config)
conn = MySQLConnection(**db_config)
print('Connection Successful!!!')
cursor = conn.cursor ()
myfile = open('./kriid.txt','r')
data=myfile.readline()
list=data.split(",")
kid13=list[0]
print(kid13)
cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where id13='"+kid13+"';")
acount = 0
while True:
# row = cursor.fetchone ()
rows = cursor.fetchmany ()
if not rows:
cursor.close ()
conn.close ()
if acount == 0:
print("ไม่พบid:"+kid13+" นี้")
sv1 = StringVar(root,value="ไม่พบid:"+kid13+" นี้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
if acount >= 2:
print("มีid13 จำนวน"+str(acount))
sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount))
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
sv = StringVar(root,value='copy เรียบร้อย')
a=Entry(root,textvariable=sv) #creating entry box
a.grid(row=5,column=1)
print("Copy Ready")
break
for row in rows:
# if row is None and acount>0: # row ==None
khn = row[0]
khn2 = khn2+","+str(khn)
print("HN =:", row[0])
sv1 = StringVar(root,value=khn)
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
Label(root,text=kid13).grid(row=5,column=4) #Creating label
# sv2 = StringVar(root,value=kid13)
# aa2=Entry(root,textvariable=sv2) #creating entry box
# aa2.grid(row=6,column=1)
sv3 = StringVar(root,value=khn2)
aa3=Entry(root,textvariable=sv3) #creating entry box
aa3.grid(row=6,column=6)
sv4 = StringVar(root,value=row[1]+" "+row[2])
aa4=Entry(root,textvariable=sv4) #creating entry box
aa4.grid(row=7,column=1)
sv5 = StringVar(root,value='XN:'+row[3])
aa5=Entry(root,textvariable=sv5) #creating entry box
aa5.grid(row=7,column=4)
sv6 = StringVar(root,value=str(row[4]))
aa6=Entry(root,textvariable=sv6) #creating entry box
aa6.grid(row=7,column=6)
print(khn)
print(row[1]+" "+row[2])
# +" "+row[2]
acount += 1
except Exception as e:
print (e)
sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
print("ไม่สามารถเชื่อม mysql ได้")
except :
print("Unknow error occured")
def searchname() :
global aa8
aaa = aa8.get()
kname = aaa.split()
print(kname[0])
print(kname[1])
khn2 = ""
try:
print(db_config)
print('Connecting to MySQL database...')
# conn = MySQLConnection(**db_config)
conn = MySQLConnection(**db_config)
#conn = MySQLdb.connect(charset='utf8', init_command='SET NAMES UTF8')
print('Connection Successful!!!')
cursor = conn.cursor ()
# cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+kname[0].strip()+"';")
cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+kname[0].strip()+"' and surname='"+kname[1].strip()+"';")
acount = 0
while True:
rows = cursor.fetchmany ()
if not rows:
cursor.close ()
conn.close ()
if acount == 0:
print("ไม่พบชื่อ นี้")
sv1 = StringVar(root,value="ไม่พบชื่อ นี้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
initkri()
if acount >= 2:
print("มีชื่อ จำนวน"+str(acount))
sv1 = StringVar(root,value="มีชื่อ จำนวน"+str(acount))
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
break
for row in rows:
khn = row[0]
khn2 = khn2+","+str(khn)
print("HN =:", row[0])
sv9 = StringVar(root,value=khn)
aa9=Entry(root,textvariable=sv9) #HN
aa9.grid(row=9,column=6)
iiiddd = 'ID13:'+str(row[5])
Label(root,text=iiiddd).grid(row=10,column=6) #id13
sv10 = StringVar(root,value=khn2)
aa10=Entry(root,textvariable=sv10) #HN2
aa10.grid(row=9,column=8)
# sv4 = StringVar(root,value=row[1]+" "+row[2])
# aa4=Entry(root,textvariable=sv4) #creating entry box
# aa4.grid(row=7,column=1)
# sv5 = StringVar(root,value='XN:'+row[3])
# aa5=Entry(root,textvariable=sv5) #XN
# aa5.grid(row=7,column=4)
Label(root,text='XN:'+row[3]).grid(row=10,column=4) #id13
bbbrrr = str(row[4])
Label(root,text='birth:'+bbbrrr).grid(row=10,column=8) # birth
print(khn)
print(row[1]+" "+row[2])
acount += 1
print(acount)
# sv6 = StringVar(root,value=str(row[4]))
# aa6=Entry(root,textvariable=sv6) #Brith
# aa6.grid(row=7,column=6)
except Exception as e:
print (e)
sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้")
aa=Entry(root,textvariable=sv1) #creating entry box
aa.grid(row=5,column=6)
print("ไม่สามารถเชื่อม mysql ได้")
except :
print("Unknow error occured")
from splinter import Browser
import time
import re
from mysql.connector import MySQLConnection, Error
from splinter.exceptions import ElementDoesNotExist
import sys
browser = Browser('phantomjs')
time.sleep(1)
try:
conn = MySQLConnection(host='localhost',database='gujarat_test',user='******',password='******')
cursor = conn.cursor()
cursor1 = conn.cursor()
cursor2 = conn.cursor()
cursor3 = conn.cursor()
cursor4 = conn.cursor()
cursor5 = conn.cursor()
cursor6 = conn.cursor()
except Error as e:
print(e)
t=""
# 4 for loops to hierarchically accessing state -> district -> tehsil -> village, if execution stops at a certain point due to some
# error replace 0 in range of i with current state id, 0 of x with current district id, 0 of y with current tehsil id and so on for z and village id
# to resume execution but reset them after on going tehsil or district is done to access next tehsil or district from the first element.
i = 11
district_id=1
village_id=1
tehsil_id=1
FLAGprime = True
def connect():
#"""Gets Springserve data and writes to MySQL table"""
db = "mysql_sl"
api = "springs"
page = 1
db_updated = False
# Connect to DB:
db_config = read_db_config(db)
try:
#print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
#print('Connection established.')
cursor = conn.cursor()
# call to get logintoken
logintoken = springs_api.get_logintoken(api)
#print(logintoken)
while True:
jsoninfo = {
"date_range": "Yesterday",
"interval": "hour",
"timezone": "America/Los_Angeles",
"dimensions": ["supply_tag_id"],
#"start_date": Today,
#"endDate": Today,
#"sort": [{ "field": "ad_revenue", "order": "desc"}]
"page": str(page)
}
result = springs_api.get_data(logintoken, jsoninfo)
#print(result.text)
info = json.loads(result.text)
if len(info) == 0:
break
if len(info) >= 1:
if db_updated == False:
sql = "DROP TABLE IF EXISTS springserve_core_yesterday"
cursor.execute(sql)
sql = "CREATE TABLE springserve_core_yesterday (date varchar(25), hour int(2), \
source_id varchar(10), supply_source varchar(255), total_requests bigint, \
usable_requests bigint, opportunities bigint, ad_impressions bigint, \
revenue decimal(15, 5), clicks bigint)"
cursor.execute(sql)
db_updated = True
print(
str(todaytime) +
" Running springserve_core_yesterday. Page # " +
str(page) + " Count " + str(len(info)))
# use default to populate null data
default = '0'
for x in info:
date1 = x['date']
date = date1[:10]
time1 = x['date']
time2 = time1[11:-8].replace("00", "*0").lstrip("0")
hour = time2.replace("*0", "0")
source_id = x['supply_tag_id']
supply_source = x['supply_tag_name']
total_requests = x['total_requests']
usable_requests = x['usable_requests']
opportunities = x['opportunities']
ad_impressions = x['total_impressions']
revenue = x['revenue']
clicks = x['clicks']
list = (date, hour, source_id, supply_source, total_requests, \
usable_requests, opportunities, ad_impressions, revenue, clicks)
#print(list)
sql = """INSERT INTO springserve_core_yesterday VALUES ("%s", "%s", "%s", \
"%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, hour, source_id, \
supply_source, total_requests, usable_requests, opportunities, \
ad_impressions, revenue, clicks)
cursor.execute(sql)
cursor.execute('commit')
page += 1
else:
print('Connection failed.')
except Error as Error:
print(error)
finally:
conn.close()
from mysql.connector import MySQLConnection, Error
from util import xstr
cnx = MySQLConnection(user='******', password='******', database='project')
cursor = cnx.cursor()
def view_groups(user):
print("-----------------Viewing Your Groups------------------")
cursor.callproc("get_groups", [user])
for result in cursor.stored_results():
row = result.fetchone()
if row is None:
print("You are not a member of any groups")
else:
while row is not None:
print("group-id: " + xstr(row[0]))
print("group-name: " + row[1])
print("Description: " + row[2])
print("---------------------------------------------------")
row = result.fetchone()
print("You are not a member of any other groups")
print("------------------------------------------------")
break
while True:
print(
"Options:\n1.Go back to home\n2.View group page\n3.Suggest groups\n4.Create a group"
)
option = raw_input("Enter a number:\n")
if option == '1':
return
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS dc_core_today_media"
cursor.execute(sql)
sql = "CREATE TABLE dc_core_today_media (inventory_source varchar(255), media varchar(255), ad_opportunities bigint, \
market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, \
ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), dc_revenue decimal(15, 5), \
clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(
"0e30062d-6746-4a9b-882a-3f61185479c7",
"9O7SnFq/yDbNK+4M2bkSqg")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "143816")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
inventory_source = x['row'][0]
media = x['row'][1]
ad_opportunities = x['row'][2]
market_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_errors = x['row'][6]
ad_revenue = x['row'][7]
aol_cost = x['row'][7]
epiphany_gross_revenue = x['row'][7]
dc_revenue = x['row'][7]
clicks = x['row'][8]
iab_viewability_measurable_ad_impressions = x['row'][9]
iab_viewable_ad_impressions = x['row'][10]
platform = '2'
list =(inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
sql = """INSERT INTO dc_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \
"%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, \
market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \
epiphany_gross_revenue, dc_revenue, clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed')
except Error as error:
print(error)
finally:
conn.close()
print('Connection Closed')
def run_execute(self, sorgu):
connection = MySQLConnection(**config)
cursor = connection.cursor(dictionary=True)
cursor.execute(sorgu)
connection.commit()
connection.close()
def removeSala():
error = None
if request.method == 'GET':
Funcionario = []
g.db = MySQLConnection(**db_config)
cursor = g.db.cursor()
query = "SELECT ID,Nome,Sobrenome,CPF,RG,DataNascimento,Endereco,Sexo,data_admissao FROM Funcionario"
cursor.execute(query)
for (ID, Nome, Sobrenome, CPF, RG, DataNascimento, Endereco, Sexo,
data_admissao) in cursor:
print(Nome)
Funcionario.append({
'ID': ID,
'Nome': Nome,
'Sobrenome': Sobrenome,
'CPF': CPF,
'RG': RG,
'DataNascimento': DataNascimento,
'Endereco': Endereco,
'Sexo': Sexo,
'data_admissao': data_admissao
})
cursor.close()
g.db.close()
session['Funcionario'] = Funcionario
if request.method == 'POST':
ID = request.form['ID']
Nome = request.form['Nome']
dados_demissao = (ID, Nome)
try:
g.db = MySQLConnection(**db_config)
cursor = g.db.cursor()
consulta1 = "SELECT * FROM Sala WHERE ID=(%s) AND Nome=(%s)"
cursor.execute(consulta1, (ID, Nome))
rows = cursor.fetchall()
for r in rows:
print(r)
if cursor:
try:
cursor = g.db.cursor(prepared=True)
consulta = "UPDATE Sala SET Ativo:='0' WHERE ID=(%s) AND Nome=(%s)"
cursor.execute(consulta, (ID, Nome))
g.db.commit()
cursor.close()
g.db.close()
return render_template('Sala.html')
except:
return render_template('removeSala.html',
title='removeSala')
else:
cursor.close()
g.db.close()
return render_template('removeSala.html', title='removeSala')
except Exception as e:
# return (str(e))
return render_template('removeSala.html',
title='removeSala',
error=e)
return render_template('removeSala.html', title='removeSala')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dp"
api = "adsym"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS adsym_market_public"
cursor.execute(sql)
sql = "CREATE TABLE adsym_market_public (date varchar(25), inventory_source varchar(255), geo_country varchar(50), \
buyer_organization varchar(100), ad_opportunities varchar(2), ad_attempts bigint, ad_impressions bigint, \
ad_revenue decimal(15, 5), ecpm decimal(6, 4), media_spend decimal(15, 5), completed_views int, clicks int, \
platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api_R.get_access_token(api)
print(logintoken)
result = aol_api_R.run_existing_report(logintoken, "161182")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1].replace("'", " -")
geo_country = x['row'][2].replace(",", " -")
buyer_organization = x['row'][3].replace('"', " ")
ad_opportunities = '0'
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_revenue = x['row'][7]
ecpm = x['row'][8]
media_spend = x['row'][9]
completed_views = x['row'][10]
clicks = x['row'][11].replace(" ", "0")
platform = '4'
list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, media_spend, completed_views, clicks, platform)
#print(list)
sql = """INSERT INTO adsym_market_public VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s")""" % (date, inventory_source, geo_country, buyer_organization, ad_opportunities, \
ad_attempts, ad_impressions, ad_revenue, ecpm, media_spend, completed_views, clicks, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dp"
report_type = "market_private"
p_name = sys.argv[1]
p_id = platforms[p_name]["id"]
m_id = platforms[p_name]["market_id"]["private"]
db_updated = False
# Connect To DB:
db_config = read_db_config(db)
try:
#print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
#print('Connected established.')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(p_name)
#print(logintoken)
for report in report_book[report_type][p_name]:
result = aol_api.run_existing_report(logintoken, str(report))
#print(result)
if len(result) == 0:
break
if len(result) >= 1:
if db_updated == False:
sql = "DROP TABLE IF EXISTS " + p_name + "_market_private"
cursor.execute(sql)
sql = "CREATE TABLE " + p_name + "_market_private (date varchar(25), inventory_source varchar(255), \
geo_country varchar(50), buyer_organization varchar(100), ad_opportunities varchar(2), \
ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15, 5), media_spend decimal(15, 5), \
ecpm decimal(6, 4), completed_views int, clicks int, platform int, market_id int)"
cursor.execute(sql)
db_updated = True
print(
str(todaysdate) + " Running " + p_name +
"_market_private id_" + str(p_id) + " with report # " +
str(report))
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1].replace("'", " -")
geo_country = x['row'][2].replace(",", " -")
buyer_organization = x['row'][3].replace('"', " ")
ad_opportunities = '0'
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_revenue = x['row'][7]
media_spend = x['row'][8]
ecpm = x['row'][9]
completed_views = x['row'][10]
clicks = x['row'][11].replace(" ", "0")
platform = str(p_id)
market_id = str(m_id)
list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, \
ad_impressions, ad_revenue, media_spend, ecpm, completed_views, clicks, platform, market_id)
#print(list)
sql = """INSERT INTO """ + p_name + """_market_private VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, inventory_source, geo_country, \
buyer_organization, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, media_spend, \
ecpm, completed_views, clicks, platform, market_id)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
api = "dc"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS dc_market_yesterday"
cursor.execute(sql)
sql = "CREATE TABLE dc_market_yesterday (date varchar(25), hour int, buyer_organization varchar(255), \
inventory_source varchar(255), market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \
ad_errors bigint, ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), \
dc_revenue decimal(15, 5), total_clicks int, iab_viewability_measurable_ad_impressions bigint, \
iab_viewable_ad_impressions bigint, platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print logintoken
result = aol_api.run_existing_report(logintoken, "143986")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
hour = x['row'][1]
buyer_organization = x['row'][2]
inventory_source = x['row'][3]
market_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_errors = x['row'][7]
ad_revenue = x['row'][8]
aol_cost = x['row'][8]
epiphany_gross_revenue = x['row'][8]
dc_revenue = x['row'][8]
total_clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
platform = '2'
list = (date, hour, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, total_clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
sql = """INSERT INTO dc_market_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \
"%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, hour, buyer_organization, inventory_source, \
market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \
epiphany_gross_revenue, dc_revenue, total_clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
users = [] while(count!=0): count-=1 user = "" user += (names[random.randint(0,len(names)-1)][1]+ " " + surnames[random.randint(0,len(surnames)-1)][1]+";") tmp = random.randint(0,len(addresses)-1) user+=(addresses[tmp][4]+";") user+=(addresses[tmp][5]+" "+addresses[tmp][3]+" "+ addresses[tmp][2]+" "+addresses[tmp][1]+";") user+=phones[random.randint(0,len(phones)-1)][1] users.append(user) return users region = sys.argv[1] eps = int(sys.argv[3]) db = MySQLConnection(host='localhost', user='******', passwd='1773002153904il', db='task1') cursor = db.cursor() tables = formTables(region) symbols = [] for i in range(32,58): symbols.append(chr(i)) for i in range(60,128): symbols.append(chr(i)) users = [] if(region == "ru" or region == "by"): users = generateByRu(cursor,tables,int(sys.argv[2])) else: users = generateUSA(cursor,tables,int(sys.argv[2])) db.close() new_users = [] if(eps == 0):
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dl"
api = "adsym"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "190596")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
rownum = ''
date = x['row'][0]
inventory_source = x['row'][1].replace("'",
" -").replace('"', "")
geo_country = x['row'][2].replace("'", "")
media = x['row'][3].replace('"', "").replace("'", "")
ad_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_revenue = x['row'][7]
ecpm = x['row'][8]
ad_errors = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
market_ops = x['row'][12]
clicks = x['row'][13].replace(" ", "0")
list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
#print(list)
sql = """INSERT INTO adsym_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dp"
report_type = "inventoryreport"
p_name = sys.argv[1]
p_id = platforms[p_name]["id"]
db_updated = False
# Connect To DB:
db_config = read_db_config(db)
try:
#print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
#print('Connection established.')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(p_name)
#print(logintoken)
for report in report_book[report_type][p_name]:
result = aol_api.run_existing_report(logintoken, str(report))
#print(result)
if len(result) == 0:
break
if len(result) >= 1:
if db_updated == False:
sql = "DROP TABLE IF EXISTS " + p_name + "_inventoryreport"
cursor.execute(sql)
sql = "CREATE TABLE " + p_name + "_inventoryreport (date varchar(50), inventory_source varchar(255), \
geo_country varchar(50), ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \
ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)"
cursor.execute(sql)
db_updated = True
print(
str(todaysdate) + " Running " + p_name +
"_inventoryreport id_" + str(p_id) + " with report # " +
str(report))
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
geo_country = x['row'][2]
ad_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_revenue = x['row'][6]
ecpm = x['row'][7]
platform = str(p_id)
list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, \
ecpm, platform)
#print(list)
sql = """INSERT INTO """ + p_name + """_inventoryreport VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s")""" % (date, inventory_source, geo_country, ad_opportunities, ad_attempts, \
ad_impressions, ad_revenue, ecpm, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_eom"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS tm_inventoryreport_EOM"
cursor.execute(sql)
sql = "CREATE TABLE tm_inventoryreport_EOM (date varchar(50), inventory_source varchar(255), geo_country varchar(50), \
ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \
ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(
"9fadb507-01e9-4a2a-b9c5-c9d65d93396e",
"y0QxY3DC7vbczeW7nBOsZg")
print(logintoken)
result = aol_api.run_existing_report(logintoken, "193899")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
geo_country = x['row'][2]
ad_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_revenue = x['row'][6]
ecpm = x['row'][7]
platform = '5'
list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
#print(list)
sql = """INSERT INTO tm_inventoryreport_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \
(date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def find_part(part_num):
dbconfig = read_db_config()
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
bean = False
if (part_num[:6]) in resistors:
quality = "Resistance"
variant = "Resistance Tolerance"
bean = True
if (part_num[:6]) in capacitors:
quality = "Capacitance"
variant = "Dielectric Characteristic"
bean = True
if (bean):
component = part_num.split('_')
if (len(component)) <= 2:
print(
"Insufficient parameters (Needs 3 or 4) e.g. R_0805_100K(_±5%)"
)
return ("0")
c_case = component[1]
c_value = convert_units(component[2])
if (len(component)) == 4:
c_characteristics = component[3]
# A fully specified 'bean'
sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name
FROM Part P
JOIN PartParameter R ON R.part_id = P.id
JOIN StorageLocation S ON S.id = P.storageLocation_id
LEFT JOIN PartManufacturer PM on PM.part_id = P.id
LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id
WHERE
(R.name = 'Case/Package' AND R.stringValue='{}') OR
(R.name = '{}' AND R.normalizedValue = '{}') OR
(R.name = '{}' AND R.stringValue = '%{}')
GROUP BY P.id, M.id, S.id
HAVING
COUNT(DISTINCT R.name)=3""".format(
c_case, quality, c_value, variant, c_characteristics)
else:
# A partially specified 'bean'
sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name
FROM Part P
JOIN PartParameter R ON R.part_id = P.id
JOIN StorageLocation S ON S.id = P.storageLocation_id
LEFT JOIN PartManufacturer PM on PM.part_id = P.id
LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id
WHERE
(R.name = 'Case/Package' AND R.stringValue='{}') OR
(R.name = '{}' AND R.normalizedValue = '{}')
GROUP BY P.id, M.id, S.id
HAVING
COUNT(DISTINCT R.name)=2""".format(
c_case, quality, c_value)
else:
sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name
FROM Part P
JOIN StorageLocation S ON S.id = P.storageLocation_id
LEFT JOIN PartManufacturer PM on PM.part_id = P.id
LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id
WHERE P.name LIKE '%{}%'""".format(part_num)
cursor.execute(sql)
components = cursor.fetchall()
return (components, bean)
except UnicodeEncodeError as err:
print(err)
finally:
cursor.close()
conn.close()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
report_type = "core_yesterday"
p_name = sys.argv[1]
p_id = platforms[p_name]["id"]
gross_rev = platforms[p_name]["fee"]
r = fees["aol_platform"]
a_cost = fees["aol_cost"]
platform_rev = p_name + "_revenue"
db_updated = False
# Connect To DB:
db_config = read_db_config(db)
try:
#print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
#print('Connection established')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(p_name)
#print logintoken
for report in report_book[report_type][p_name]:
result = aol_api.run_existing_report(logintoken, str(report))
#print(result)
if len(result) == 0:
break
row_count_value = json.loads(result)['row_count']
if int(row_count_value) >= 1:
if db_updated == False:
sql = "DROP TABLE IF EXISTS " + p_name + "_core_yesterday"
cursor.execute(sql)
sql = "CREATE TABLE " + p_name + "_core_yesterday (date varchar(25), hour int, inventory_source varchar(255), ad_opportunities bigint, \
market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \
aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), " + p_name + "_revenue decimal(15, 5), total_clicks int, \
iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)"
cursor.execute(sql)
db_updated = True
print(
str(todaysdate) + " Running " + p_name +
"_core_yesterday report # " + str(report))
for x in json.loads(result)['data']:
date = x['row'][0]
hour = x['row'][1]
inventory_source = x['row'][2]
ad_opportunities = x['row'][3]
market_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_errors = x['row'][7]
ad_revenue = x['row'][8]
aol_cos = x['row'][8]
epiphany_gross_rev = x['row'][8]
platform_rev = x['row'][8]
total_clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
platform = str(p_id)
list = (date, hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cos, epiphany_gross_rev, platform_rev, total_clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
if p_name == 'dna':
aol_cost = '0'
epiphany_gross_revenue = '0'
platform_revenue = '0'
else:
aol_cost = float(float(aol_cos) * float(a_cost))
epiphany_gross_revenue = float(
float(epiphany_gross_rev) * float(gross_rev))
platform_revenue = float(
float(platform_rev) * float(r))
sql = """INSERT INTO """ + p_name + """_core_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, hour, inventory_source, \
ad_opportunities, market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, \
aol_cost, epiphany_gross_revenue, platform_revenue, total_clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
mode='w')
handler.setFormatter(
logging.Formatter('%(asctime)s:%(levelname)s:%(name)s: %(message)s'))
logger.addHandler(handler)
# Set up Intents
intents = discord.Intents.all()
# Load the bot's Discord Token
load_dotenv()
TOKEN = os.getenv('DISCORD_TOKEN')
print(f'{TOKEN} has connected to Discord!')
# Setup a mysql DB Connection
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
if conn.is_connected():
print("Database Connection Successful.")
else:
print("Database Connection Failed.")
exit
conn.autocommit = True
cursor = conn.cursor()
# Setup Command Prefix
bot = commands.Bot(command_prefix='!sb ', intents=intents)
@bot.command(name='echo', help='Echos the message back to a user')
async def echo(ctx, *args):
# Echo the message back to the user
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
api = "aol"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
# Create Table:
sql = "DROP TABLE IF EXISTS v3_core_today"
cursor.execute(sql)
sql = "CREATE TABLE v3_core_today (hour int, inventory_source varchar(255), ad_opportunities bigint, \
market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \
total_clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "142557")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
hour = x['row'][0]
inventory_source = x['row'][1]
ad_opportunities = x['row'][2]
market_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_errors = x['row'][6]
ad_revenue = x['row'][7]
total_clicks = x['row'][8]
iab_viewability_measurable_ad_impressions = x['row'][9]
iab_viewable_ad_impressions = x['row'][10]
list = (hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions)
# print(list)
sql = """INSERT INTO v3_core_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s")""" % (hour, inventory_source, ad_opportunities, market_opportunities, \
ad_attempts, ad_impressions, ad_errors, ad_revenue, total_clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed')
def search_list(user_id):
#Назначим кнопки
markup = types.InlineKeyboardMarkup()
markup.add(
types.InlineKeyboardButton(
text=u'\U0001F30D Искать каждый товар отдельно',
callback_data='locallist_one:'), )
markup.add(
types.InlineKeyboardButton(text=u'\U0001F50D Продолжить поиск',
switch_inline_query_current_chat=""), )
#Проверим что в списке есть товары
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
SQL = 'select count(distinct(product_id)) from user_product_list where chat_id = %s'
cursor.execute(SQL, (user_id, ))
products = cursor.fetchone()
if products[0] == 0:
bot.send_message(user_id,
'Сначала добавьте товары в список для поиска')
cursor.close()
conn.close()
else:
#Ищем аптеки с поответствием по списку товара
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
SQL = """\
SELECT s.name, s.address, s.mode, s.phone, s.latitude ,s.longitude, t.way FROM (
SELECT count(p2.product_id) kol, p1.name, get_way(p1.latitude ,p1.longitude,u.latitude,u.longitude) way FROM users u
inner join store p1 on p1.city = u.city
inner join stock p2 on p2.company = p1.company and p1.name = p2.store
WHERE u.chat_id = %s and p2.product_id in (select distinct(product_id) from user_product_list where chat_id = %s)
group by p1.name, p1.latitude ,p1.longitude,u.latitude,u.longitude having count(p2.product_id)=(select count(distinct(product_id)) from user_product_list where chat_id = %s)
) t
inner join store s on s.name = t.name
order by t.way asc
LIMIT 3
"""
cursor.execute(SQL, (
user_id,
user_id,
user_id,
))
stores = cursor.fetchall()
for store in stores:
try:
bot.send_venue(user_id, store[4], store[5],
store[0] + ' (' + str(store[6]) + ' м.)',
store[1])
bot.send_message(
user_id,
store[2] + '\n' + 'Тел: ' + store[3] +
'\nЕсть все по списку',
parse_mode='markdown',
)
except Exception as e:
print(e)
cursor.close()
conn.close()
bot.send_message(
user_id,
'Если вас не устроили эти аптеки, вы можете поискать отдельно каждый товар из списка в ближайших аптеках',
parse_mode='markdown',
reply_markup=markup,
)
