def run():
query = pd.read_sql(sql.get_codes(), sql_connect.connect())
folder = query.DocumentCode.unique()
for document in folder:
# ----------------DIRECTORY PATH ----------------------------
directory_path = f'/Users/kommas/OneDrive/Business_Folder/Slack/Supplier_Check/history/{document}'
target = query[query.DocumentCode == document]
# -------------------- MAKE DIRECTORY IF DOES NOT EXISTS --------------------
try:
print('\n03: Path Check: ', end='')
if not os.path.exists(directory_path):
os.makedirs(directory_path)
print('!NEW!\t [✔️]')
else:
print('\t\t [✔️]')
except OSError:
print("!ERROR! [❌️]")
sys.exit(1)
for i, barcode in enumerate(target.BarCode):
percent = int((100 * (i + 1)) / len(target.BarCode))
filler = "█" * (percent // 2)
remaining = '-' * ((100 - percent) // 2)
df = pd.read_sql(sql.get_product_cost(barcode),
sql_connect.connect())
plot.run(df, barcode, directory_path)
print(f'\rDONE:[{filler}{remaining}]{percent}%',
end='',
flush=True)
slack.run(document)
def run(input_param):
# ----------------STATEMENTS HERE----------------------------
output_file = "Order{}.xlsx".format(input_param)
type_of_forma = 'ΠΠΡ'
def katastima():
if answer_02.ID[0] in ['00', '01']:
return 'ΚΕΝΤΡΙΚΑ ΕΔΡΑΣ (ΣΧΙΣΜΑ ΕΛΟΥΝΤΑΣ)'
elif answer_02.ID[0] == '10':
return 'ΛΑΤΟ 01 (ΑΚΤΗ ΙΩΣΗΦ ΚΟΥΝΔΟΥΡΟΥ 11)'
elif answer_02.ID[0] == '20':
return 'ΛΑΤΟ 02 (28ΗΣ ΟΚΤΩΒΡΙΟΥ 6)'
elif answer_02.ID[0] == '30':
return 'ΛΑΤΟ 03 (ΑΓ. ΙΩΑΝΝΗΣ 29)'
# -------------ANSWERS----------------------------
answer_01 = pd.read_sql_query(sql.sql_query(input_param, type_of_forma),
sql_connect.connect())
# print(answer_01, end='\n')
answer_02 = pd.read_sql_query(sql.data_query(input_param, type_of_forma),
sql_connect.connect())
# print(answer_02, end='\n')
supplier = answer_02.Name[0]
# -------------ΑΝΑΘΕΣΗ ΤΙΜΗΣ: ΤΟ ΥΠΟΚΑΤΑΣΤΗΜΑ ----------------------------
store = katastima()
name = f'Νέα Παραγγελία: {store}'
# -------------DATAFRAME ΤΟΥ ΠΡΟΜΗΘΕΥΤΗ ΜΑΙΛ - ΤΗΛΕΦΩΝΟ ----------------------------
person = pd.read_sql_query(sql.extract_mail(input_param, type_of_forma),
sql_connect.connect())
# -------------ΒΡΕΣ ΤΟ ΤΗΛΕΦΩΝΟ ΤΟΥ ΠΡΟΜΗΘΕΥΤΗ ----------------------------
phone_number = person.Telephone1[0]
# ----------------FILE PATH----------------------------
file_path = f'/Users/kommas/OneDrive/Business_Folder/Slack/Orders/{store}/{supplier}/{output_file}'
# ----------------DIRECTORY PATH ----------------------------
directory_path = f'/Users/kommas/OneDrive/Business_Folder/Slack/Orders/{store}/{supplier}'
# -------------------- MAKE DIRECTORY IF DOES NOT EXISTS --------------------
try:
if not os.path.exists(directory_path):
os.makedirs(directory_path)
print('!NEW! file path created')
else:
print('file path EXISTS')
except OSError:
print("!ERROR! Creation of the directory FAILED")
sys.exit(1)
# -------------OPEN FILE | WRITE ----------------------------
excel.export(file_path, answer_01, answer_02, store)
# ----------------SLACK BOT----------------------------
slack.run(output_file, supplier, store, answer_02, person, phone_number,
file_path)
def run():
# DATAFRAME
df = pd.read_sql(sql.run(), sql_connect.connect())
detailed_df = pd.read_sql(sql.detailed(), sql_connect.connect())
# PLOT
plot.run(df, detailed_df)
# SLACK
slack.run()
plt.show()
def run():
# DATAFRAME
df = pd.read_sql(sql.run(), sql_connect.connect())
df2 = pd.read_sql(sql.run2(), sql_connect.connect())
#PLOT
name1 = 'images/new_items_per_user.png'
name2 = 'images/new_items_per_user_all.png'
plot.run(df, name1, dt.now().year )
plot.run(df2, name2, f'FROM: 2012 TO: {dt.now().year}')
# SLACK
slack.run()
def run():
file_path = '/Users/kommas/OneDrive/Business_Folder/Slack/Private_Analytics/Φρέσκο Γάλα Δέλτα POS.xlsx'
# -------------Pandas GET Answer ------------
answer = pd.read_sql_query(sql.query_01(), sql_connect.connect())
answer_2 = pd.read_sql_query(sql.query_02(), sql_connect.connect())
# ------------- PLOT ------------
plot.run(answer_2)
pl = 12
# ------------- EXCEL ------------
excel.run(file_path, answer, answer_2, pl)
# ------------- SLACK ------------
slack.run(file_path)
def run():
file_path = '/Users/kommas/OneDrive/Business_Folder/Slack/Private_Analytics/Κερούλης.xlsx'
# -------------PANDAS ------------
answer = pd.read_sql_query(sql.query_01(), sql_connect.connect())
answer_2 = pd.read_sql_query(sql.query_02(), sql_connect.connect())
answer_3 = pd.read_sql_query(sql.query_03(), sql_connect.connect())
pl = answer_3.values.min()
# -------------PLOT ------------
plot.run(answer, answer_2)
# ------------- EXCEL ------------
excel.run(file_path, answer, answer_2, pl)
# ------------- SLACK ------------
slack.run(file_path)
def run():
output_file = 'Pendings.xlsx'
path_to_file = f'/Users/kommas/OneDrive/Business_Folder/Slack/Multiple_emails/{output_file}'
# Assign the SQL Query Answer
sql_answer_1 = pd.read_sql_query(sql.query_01(), sql_connect.connect())
sql_answer_2 = pd.read_sql_query(sql.query_02(), sql_connect.connect())
sql_answer_3 = pd.read_sql_query(sql.query_03(), sql_connect.connect())
tabs = ['Bazaar', 'Elounda', 'Lato']
answers = [sql_answer_1, sql_answer_2, sql_answer_3]
# Εισαγωγή Δεομένων στο EXCEL
excel.run(path_to_file, tabs, answers)
# ---------------- SLACK ----------------------------
slack.run(output_file, path_to_file)
def run():
# ------- ELOUNDA MARKET -----------
ems = pd.read_sql_query(sql.elounda_market_sales(), sql_connect.connect())
print(
f' 01: Προμηθευτές: SQL Ερώτημα: ELOUNDA MARKET SALES: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}'
)
ems_pivot = ems.pivot(index='YEAR', columns='MONTH', values='TurnOver')
ems_pivot = ems_pivot.fillna(0)
print(
f' 02: Δημιουργία Dataframe: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}'
)
plot.heatmap(ems_pivot)
print(
f' 03: PLOT HEATMAP: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}'
)
plot.box(ems)
print(
f' 04: PLOT BOX: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}')
ems_pivot['ΣΥΝΟΛΑ'] = ems_pivot.sum(axis=1)
plot.elounda_market_sales(ems, ems_pivot)
print(
f' 05: PLOT BAR: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}')
ems_pivot.loc['ΣΥΝΟΛΑ'] = ems_pivot.sum(axis=0)
print(
f' 06: ADD PIVOT SUMS: Ολοκληρώθηκε: {datetime.now().strftime("%H:%M:%S")}'
)
slack.run(ems_pivot)
# ------- CONSTRUCTORS -----------
emspb = pd.read_sql_query(sql.sales_per_brand(), sql_connect.connect())
for name, brand in zip(names, brands):
print(f'checking: {name}')
df = emspb[emspb.SubCategory.isin(brand)]
df_pivot = df.pivot(index='SubCategory',
columns='YEAR',
values='TurnOver')
df_pivot = df_pivot.fillna(0)
plot.heatmap_blue(df_pivot, name)
df_pivot['ΣΥΝΟΛΑ'] = df_pivot.sum(axis=1)
df_pivot.loc['ΣΥΝΟΛΑ'] = df_pivot.sum(axis=0)
ccplot = df.groupby('YEAR').sum().reset_index()
plot.kataskevastes(ccplot, name)
slack.kat_run(df_pivot, name)
def run():
class Supplier:
def __init__(self, poliseis, pistotika, dates, price, money_back,
tziros, ypoloipo):
self.poliseis = poliseis
self.pistotika = pistotika
self.dates = dates
self.price = price
self.money_back = money_back
self.tziros = tziros
self.ypoloipo = ypoloipo
def __str__(self):
return f"""
>:python: : ΜΗΝΙΑΙΟ ΔΗΜΟΣΙΕΥΜΑ [ΕΜΜΑΝΟΥΗΛ ΒΑΡΔΑΣ (ΠΕΚ)]
> ΥΠΟΛΟΓΙΣΜΟΣ (0.06 EUR * ΠΟΣΟΤΗΤΑ_ΠΩΛΗΣΕΩΝ) - (ΑΘΡΟΙΣΜΑ ΠΙΣΤΩΤΙΚΩΝ)
>ΠΩΛΗΣΕΙΣ:
```{self.poliseis}```
>ΠΙΣΤΩΤΙΚΑ:
```{pistotika}```
>ΚΑΘΑΡΟ ΥΠΟΛΟΙΠΟ:
` (0.06 * {self.poliseis.SalesQuantity[0]} ) - ({self.money_back})`
` {self.tziros} - {self.money_back} = {self.ypoloipo} EUR`
>:fbwow:
"""
today = datetime.now()
dates = pd.date_range('2018-11-1', today)
dates = dates[(dates.strftime('%a') == 'Mon') |
(dates.strftime('%a') == 'Tue')]
x = []
for date in dates:
date = date.strftime('%Y-%m-%d')
x.append(date)
poliseis = pd.read_sql_query(sql.get_vardas_sale(tuple(x)),
sql_connect.connect())
pistotika = pd.read_sql_query(sql.pistotiko(), sql_connect.connect())
price = 0.06
money_back = round(sum(pistotika['NET VALUE']), 2)
tziros = round(price * poliseis.SalesQuantity[0], 2)
ypoloipo = round(tziros - money_back, 2)
vardas = Supplier(poliseis, pistotika, dates, price, money_back, tziros,
ypoloipo)
slack.run(vardas)
def run():
# DATAFRAME
df = pd.read_sql(sql.run(), sql_connect.connect())
#PLOT
plot.run(df)
# SLACK
slack.run(df)
def run():
file_path = '/Users/kommas/OneDrive/Business_Folder/Slack/Private_Analytics/Θαλασσινά.xlsx'
# -------------Pandas GET Answer ------------
answer = pd.read_sql_query(sql.query_01(), sql_connect.connect())
answer_2 = pd.read_sql_query(sql.query_02(), sql_connect.connect())
answer_3 = pd.read_sql_query(sql.query_03(), sql_connect.connect())
pl = answer_3.values.min()
# ------------- PLOT ------------
plot.figure(answer_2, answer)
# ------------- EXCEL ------------
excel.export(file_path, answer, answer_2, pl)
# ------------- SLACK ------------
slack.send(file_path)
def run():
def katastima(x):
if x in ['00', '01']:
return 'ΚΕΝΤΡΙΚΑ ΕΔΡΑΣ (ΣΧΙΣΜΑ ΕΛΟΥΝΤΑΣ)'
elif x == '10':
return 'ΛΑΤΟ 01 (ΑΚΤΗ ΙΩΣΗΦ ΚΟΥΝΔΟΥΡΟΥ 11)'
elif x == '20':
return 'ΛΑΤΟ 02 (28ΗΣ ΟΚΤΩΒΡΙΟΥ 6)'
elif x == '30':
return 'ΛΑΤΟ 03 (ΑΓ. ΙΩΑΝΝΗΣ 29)'
answer_01 = pd.read_sql_query(sql.available_orders(),
sql_connect.connect())
answer_01['Store'] = answer_01['PdaId'].apply(katastima)
if len(answer_01) == 0:
return 'ΔΕΝ ΥΠΑΡΧΟΥΝ ΕΓΓΡΑΦΕΣ (ΠΑΡΑΓΓΕΛΙΕΣ)'
else:
return f'{answer_01}'
def run():
# ---------------- FILE PATH ----------------
path = '/Users/kommas/OneDrive/Business_Folder/Slack/Private_Analytics/sql.xlsx'
# ---------------- ANSWERS ----------------
answer_01 = pd.read_sql_query(sql.query_01, sql_connect.connect())
answer_02 = pd.read_sql_query(sql.query_02, sql_connect.connect())
answer_03 = pd.read_sql_query(sql.query_03, sql_connect.connect())
answer_04 = pd.read_sql_query(sql.query_04, sql_connect.connect())
answer_05 = pd.read_sql_query(sql.query_05, sql_connect.connect())
answer_06 = pd.read_sql_query(sql.query_06, sql_connect.connect())
# ---------------- PLOT ----------------
plot.app(answer_03, answer_04, answer_05)
# ---------------- EXCEL EXPORT ----------------
excel.export(path, answer_01, answer_02, answer_03, answer_04, answer_05, answer_06)
# ---------------- SLACK BOT ----------------
slack.app(path)
def run(name, year):
df = pd.read_sql(sql.run(name, year), sql_connect.connect())
# print(df)
grouped_by_store = df.groupby(by='ΥΠΟΚΑΤΑΣΤΗΜΑ').sum().reset_index()
column_list = ['ΔΕΥΤΕΡΑ', 'ΤΡΙΤΗ', 'ΤΕΤΑΡΤΗ', 'ΠΕΜΠΤΗ', 'ΠΑΡΑΣΚΕΥΗ', 'ΣΑΒΒΑΤΟ', 'ΚΥΡΙΑΚΗ']
grouped_by_store['ΠΑΡΑΣΤΑΤΙΚΑ'] = grouped_by_store[column_list].sum(axis=1)
print(grouped_by_store)
# ΣΕ ΠΟΙΑ ΥΠΟΚΑΤΑΣΤΗΜΑΤΑ ΠΑΡΑΛΑΜΒΑΝΩ ΕΜΠΡΕΥΜΑΤΑ (ΛΙΣΤΑ)
supply_stores = df['ΥΠΟΚΑΤΑΣΤΗΜΑ'].unique()
# ΓΙΑ ΚΑΘΕ ΥΠΟΚΑΤΑΣΤΗΜΑ ΒΡΕΣ ΜΟΥ ΤΗΝ ΜΕΣΗ ΤΙΜΗ ΣΤΙΣ ΓΡΑΜΜΕΣ ΚΑΙ ΤΗΝ ΜΕΣΗ ΤΙΜΗ ΓΙΑ ΤΙΣ ΠΟΣΟΤΗΤΕΣ
supply_lines_mean = []
supply_quantity_mean = []
for i in supply_stores:
supply_lines_mean.append(int((df[df['ΥΠΟΚΑΤΑΣΤΗΜΑ'] == i]['ΓΡΑΜΜΕΣ'].mean())))
supply_quantity_mean.append(int(df[df['ΥΠΟΚΑΤΑΣΤΗΜΑ'] == i]['ΠΟΣΟΤΗΤΑ'].mean()))
# print(list(zip(supply_stores, supply_lines_mean)))
# print(list(zip(supply_stores, supply_quantity_mean)))
# plot
plot.plot_sum_values(grouped_by_store['ΥΠΟΚΑΤΑΣΤΗΜΑ'], grouped_by_store['ΧΡΕΩΣΗ'], f'ΥΠΟΚΑΤΑΣΤΗΜΑ ΧΡΕΩΣΗ ΕΤΟΣ:{year}')
return round(grouped_by_store['ΧΡΕΩΣΗ'].sum(), 2)
# Copyright (c) 2020. Ioannis E. Kommas. All Rights Reserved
from Private import send_mail, sql_connect
from C_MONTHLY_REPORTS.EXCEL import epitages_to_excel
import pandas as pd
output_file = 'epitages.xlsx'
path_to_file = f'/Users/kommas/OneDrive/Business_Folder/Slack/Multiple_emails/{output_file}'
mail_lst = [
'*****@*****.**', '*****@*****.**', '*****@*****.**'
]
mail_names = [
'Επιταγές (Κομμάς Ιωάννης)', 'Επιταγές (Λογιστήριο)',
'Επιταγές (Κατάστημα)'
]
program_title = 'Επιταγές'
# -------------------------------- READ HTML FILE ---------------------------------------
with open("../HTML/6. Επιταγές.html", 'r') as html_file:
word = html_file.read()
with open("../SQL/epitages.sql", 'r') as sql_file:
sql = sql_file.read()
# -------------------------------- DATA ---------------------------------------
data = pd.read_sql(sql, sql_connect.connect())
# -------------------------------- EXCEL ---------------------------------------
epitages_to_excel.run(path_to_file, data)
# -------------------------------- MAIL ---------------------------------------
send_mail.send_mail(mail_lst, mail_names, word, path_to_file, output_file)
import pandas as pd
from Private import send_mail, sql_connect
from C_MONTHLY_REPORTS.EXCEL import pe_00_to_excel
output_file = 'Extra_Timokatalogos.xlsx'
path_to_file = f'/Users/kommas/OneDrive/Business_Folder/Slack/Multiple_emails/{output_file}'
mail_lst = [
'*****@*****.**', '*****@*****.**', '*****@*****.**'
]
mail_names = [
'Αλλαγή Τιμοκαταλόγου: Κομμάς', 'Αλλαγή Τιμοκαταλόγου: Λογιστήριο',
'Αλλαγή Τιμοκαταλόγου: Κατάστημα'
]
program_title = 'Επιπλέον Τιμοκατάλογος'
# -------------------------------- READ HTML FILE ---------------------------------------
with open('../HTML/0. Προσφορές Επιπλέον.html', 'r') as html_file:
word = html_file.read()
# -------------------------------- READ SQL FILE ---------------------------------------
with open('../SQL/0. Προσφορές Επιπλέον', 'r') as sql_file:
sql_querry = sql_file.read()
# -------------------------------- SQL ---------------------------------------
sql_answer = pd.read_sql_query(sql_querry, sql_connect.connect())
# -------------------------------- EXCEL ---------------------------------------
pe_00_to_excel.run(path_to_file, sql_answer)
# -------------------------------- MAIL ---------------------------------------
send_mail.send_mail(mail_lst, mail_names, word, path_to_file, output_file)
mail_lst = [
'*****@*****.**', '*****@*****.**', '*****@*****.**'
]
mail_names = [
'Τιμολόγιο Atzoulatos (Κομμάς)', 'Τιμολόγιο Atzoulatos (Λογιστήριο)',
'Τιμολόγιο Atzoulatos (Κατάστημα)'
]
main_name = 'ΑΝΤΖΟΥΛΑΤΟΣ ΑΝΑΣΤΑΣΙΟΣ & ΧΛΟΗ Ο.Ε.'
# -------------------- Open HTML File for the BODY MAIL --------------------
with open('HTML/2. Import || Antoulatos.html', 'r') as html_file:
word = html_file.read()
# -------------------- Assign the SQL Query Answer --------------------
sql_answer = pd.read_sql_query(
sql_import_report.private_database_query(main_name), sql_connect.connect())
# -------------------- ASSIGN VALUES HERE MARKUP / QUARTILES --------------------
markup = round(sql_answer['ΚΕΡΔΟΦΟΡΙΑ'] * 100, 2)
quartiles = np.quantile(markup, [.25, .5, .75])
order_id = sql_answer['ΠΑΡΑΣΤΑΤΙΚΟ'].unique()
retail_price = sql_answer['ΤΙΜΗ ΛΙΑΝΙΚΗΣ']
retail_q = np.quantile(retail_price, [.25, .5, .75])
# -------------------- FIND BARCODES WHO ARE IN EVERY QUARTILE --------------------
codes_in_q1 = sql_answer[markup <= quartiles[0]]
codes_in_q2 = sql_answer[(markup > quartiles[0]) & (markup <= quartiles[1])]
codes_in_q3 = sql_answer[(markup > quartiles[1]) & (markup <= quartiles[2])]
codes_in_q4 = sql_answer[markup > quartiles[2]]
if input('Press 1 continue:') != '1':
from datetime import date
from ΔΗΜΙΟΥΡΓΙΑ_EXCEL_ΓΙΑ_ΕΙΣΑΓΩΓΗ_ΤΙΜΟΚΑΤΑΛΟΓΟΥ import sql_select, excel_export
from Private import sql_connect
# ----------------MAKE DF REPORT VIEWABLE----------------------------
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
# -------------------- STATEMENTS HERE --------------------
id = 1881 # ΑΥΤΗ Η ΠΑΡΑΜΕΤΡΟΣ ΚΑΘΕ ΦΟΡΑ ΑΛΛΑΖΕΙ
how_many_weeks = 2 # ΑΥΤΗ Η ΠΑΡΑΜΕΤΡΟΣ ΚΑΘΕ ΦΟΡΑ ΑΛΛΑΖΕΙ
price_or_discount = ['Πελάτες Τιμή Πώλησης', 'Πωλήσεις Έκπτωση 1']
output_file = 'Timokatalogos.xlsx'
path_to_file = f'/Users/kommas/OneDrive/{output_file}'
# -------------------- READ PDA SCANNED ITEMS --------------------
sql = pd.read_sql_query(sql_select.pda_results(id), sql_connect.connect())
df = pd.DataFrame()
today = date.today().strftime('%d/%m/%y')
period = date.today() + relativedelta(weeks=+how_many_weeks)
repeatable_list = [price_or_discount[1], today, period.strftime('%d/%m/%y'), 1]
sql['Τιμ/γος'] = [repeatable_list[0] for i in range(len(sql))]
sql['Aπό'] = [repeatable_list[1] for i in range(len(sql))]
sql['Εως'] = [repeatable_list[2] for i in range(len(sql))]
sql['Υποκ/μα'] = [repeatable_list[3] for i in range(len(sql))]
# -------------------- IMPORT DATA TO EXCEL --------------------
excel_export.export(path_to_file, sql)
type_of_documents = ['ΑΠ_ΜΟΒ', 'ΔΕΑ', 'ΑΔΠ']
choose = type_of_documents[1]
id = 4002 # ΑΥΤΗ Η ΠΑΡΑΜΕΤΡΟΣ ΚΑΘΕ ΦΟΡΑ ΑΛΛΑΖΕΙ
output_file = 'Output.xlsx'
path_to_file = f'/Users/kommas/OneDrive/Business_Folder/Slack/Multiple_emails/{output_file}'
mail_lst = [
'*****@*****.**', '*****@*****.**', '*****@*****.**'
]
mail_names = [
'Ανάλυση Τιμών (Κομμάς)', 'Ανάλυση Τιμών (Λογιστήριο)',
'Ανάλυση Τιμών (Κατάστημα)'
]
# -------------------- READ PDA SCANNED ITEMS --------------------
barcodes = pd.read_sql_query(sql_select.pda_results(id, choose),
sql_connect.connect())
# -------------------- GET LATEST COST FOR EVERY BARCODE --------------------
sql_answer = pd.DataFrame()
for barcode in barcodes['BARCODE']:
sql_answer = sql_answer.append(
pd.read_sql_query(sql_select.get_product_cost(barcode),
sql_connect.connect()))
# -------------------- RESET INDEX --------------------
sql_answer = sql_answer.reset_index(drop=True)
print(sql_answer)
# -------------------- FIND PRICES IN THE WEB --------------------
lista = sql_answer['BARCODE']
scrap.shops = [scrap.a, scrap.b, scrap.e]
out = scrap.calculate_prices(lista)
# -------------------- Statements Here --------------------
main_name = 'Astor Hellas A.E.'
output_file = 'Astor.xlsx'
path_to_file = f'/Users/kommas/OneDrive/Business_Folder/Slack/Multiple_emails/{output_file}'
mail_lst = ['*****@*****.**', '*****@*****.**', '*****@*****.**']
mail_names = ['Τιμολόγιο Astor (Κομμάς)', 'Τιμολόγιο Astor (Λογιστήριο)', 'Τιμολόγιο Astor (Κατάστημα)']
# -------------------- Open HTML File for the BODY MAIL --------------------
with open('HTML/2. Import || Astor.html', 'r') as html_file:
word = html_file.read()
# -------------------- Assign the SQL Query Answer --------------------
sql_answer = pd.read_sql_query(sql_import_report.private_database_query(main_name), sql_connect.connect())
# -------------------- ASSIGN VALUES HERE MARKUP / QUARTILES --------------------
markup = round(sql_answer['ΚΕΡΔΟΦΟΡΙΑ']*100, 2)
quartiles = np.quantile(markup, [.25, .5, .75])
order_id = sql_answer['ΠΑΡΑΣΤΑΤΙΚΟ'].unique()
retail_price = sql_answer['ΤΙΜΗ ΛΙΑΝΙΚΗΣ']
retail_q = np.quantile(retail_price, [.25, .5, .75])
# -------------------- FIND BARCODES WHO ARE IN EVERY QUARTILE --------------------
codes_in_q1 = sql_answer[markup <= quartiles[0]]
codes_in_q2 = sql_answer[(markup > quartiles[0]) & (markup <= quartiles[1])]
codes_in_q3 = sql_answer[(markup > quartiles[1]) & (markup <= quartiles[2])]
codes_in_q4 = sql_answer[markup > quartiles[2]]
# Copyright (c) 2020. Ioannis E. Kommas. All Rights Reserved
import matplotlib.pyplot as plt
import seaborn as sns
from Private import sql_connect
import pandas as pd
from PLOT_EXAMPLES import sql
from datetime import datetime as dt
current_year = dt.now().year + 1
past_years = current_year - 5
df = pd.read_sql_query(sql.query_01(), sql_connect.connect())
# Setting styles: darkgrid, whitegrid, dark, white,
sns.set_style("dark")
sns.set_palette("pastel")
for i in range(past_years, current_year):
year = df[df.YEAR == f'{i}'].TurnOver
print(year)
sns.kdeplot(year, shade=True, label=f'{i}')
plt.legend()
plt.show()
sns.barplot(data=df[df.YEAR >= str(past_years)],
x='MONTH',
y='TurnOver',
hue='YEAR')
plt.show()
# Copyright (c) 2020. Ioannis E. Kommas. All Rights Reserved
import pandas as pd
from Private import sql_connect
from ΑΓΟΡΕΣ_ΠΩΛΗΣΕΙΣ import sql, excel
# ----------------MAKE DF REPORT VIEWABLE----------------------------
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
brand = ('Aphrodite', 'Aphrodite')
year = 2019
sales = pd.read_sql(sql.sales(brand, year), sql_connect.connect())
cost = pd.read_sql(sql.cost(brand, year), sql_connect.connect())
print(sales)
print(cost)
sales_cost = pd.merge(left=sales, right=cost, left_on='BARCODE', right_on='BARCODE').sort_values(
by=['BARCODE']).reset_index()
output = sales_cost[['BARCODE',
'DESCRIPTION',
'ΑΓΟΡΕΣ',
'ΠΩΛΗΣΕΙΣ',
'ΑΓΟΡΕΣ EM',
'ΠΩΛΗΣΕΙΣ EM',
'ΑΓΟΡΕΣ L1',
'ΠΩΛΗΣΕΙΣ L1',
'ΑΓΟΡΕΣ L2',
'ΠΩΛΗΣΕΙΣ L2',
output = f"""
SELECT IMP_MobileDocumentLines.BarCode as 'BARCODE'
FROM IMP_MobileDocumentLines
LEFT JOIN IMP_MobileDocumentHeaders ON
IMP_MobileDocumentLines.fDocGID = IMP_MobileDocumentHeaders.GID
WHERE IMP_MobileDocumentHeaders.Code = {id}
AND IMP_MobileDocumentHeaders.OrderType like 'ΑΠ_ΜΟΒ'
"""
return output
# ----------------STATEMENTS HERE----------------------------
# 3/6/2020 '1875' DONE
# 4/6/2020 '1877' DONE.
id = '1877'
# ----------------DATAFRAME----------------------------
df = pd.read_sql_query(pda_results(id), sql_connect.connect())
# ----------------FILE WRITE INLINE WITH \,----------------------------
with open('file.csv', 'w') as file:
x = [str(df['BARCODE'].iloc[i]) for i in range(len(df))]
print(x)
file.write('\,'.join(x))
# ----------------SLACK BOT----------------------------
slack_app.send_text(
f"""
>H Αλλαγή Φ.Π.Α. Ολοκληρώθηκε
`PDA Αρχείο: {id}`
""", slack_app.channels[1])
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
# -------------------- STATEMENTS HERE --------------------
pick_timokatalogo = timokatalogos.lista_2020[-1]
to_date = pick_timokatalogo.end
# Build paths inside the project like this: BASE_DIR / 'subdir'.
BASE_DIR = Path(__file__).resolve(strict=True).parents[1]
path_to_file = BASE_DIR / f'A_DAILY_ΠΟΡΕΙΑ_ΤΙΜΟΚΑΤΑΛΟΓΟΥ_ΠΩΛΗΣΕΩΝ/excel/{to_date.strftime("%d-%m")}.xlsx'
# -------------------- TAKE TIMESTAMP --------------------
start_timestamp = dt.now().strftime('%d-%m %H:%M:%S')
# -------------------- ΔΙΑΒΑΖΩ ΤΟΝ ΤΙΜΟΚΑΤΑΛΟΓΟ SQL DB--------------------
timokatalogos = pd.read_sql_query(sql_select.get_ending_pricelist_products(to_date), sql_connect.connect())
# -------------OPEN FILE | WRITE ----------------------------
end_of_pricelist_excel_export.export(path_to_file, timokatalogos[['ΠΕΡΙΓΡΑΦΗ', 'ΚΩΔΙΚΟΣ', 'BRAND']])
# -------------------- SLACK BOT ADD TEXT --------------------
report = f"""
>EKTAKTO ΔΗΜΟΣΙΕΥΜΑ
>ΛΗΞΗ ΤΙΜΟΚΑΤΑΛΟΓΟΥ: {to_date.strftime('%d-%m')}
>
>Data Science Tools Used:
>:slack: :github: :docker: :kubernetes: :python: :javascript: :nodejs: :react: :vue: :fbwow:
"""
slack_app.send_text(report, slack_app.channels[4])
# -------------------- SLACK BOT ADD FILES --------------------
def run(input_param, get_year):
order_types = ['ΔΕΑ', 'ΑΔΠ', 'ΑΤΔ', 'ΠΠΡ', 'ΑΠ_ΜΟΒ']
order_type = order_types[0] # 0 = ΔΕΑ / 1 = ΑΔΠ ...
detailed = 'detailed_{}.xlsx'.format(input_param)
# ------------- ΕΠΙΛΟΓΗ ΥΠΟΚΑΤΑΣΤΗΜΑΤΟΣ ΜΕ ΒΑΣΗ ΤΟ ID ΤΟΥ SCANNER ----------------------------
def katastima():
if answer_02.ID[0] in ['00', '01']:
return 'ΚΕΝΤΡΙΚΑ ΕΔΡΑΣ (ΣΧΙΣΜΑ ΕΛΟΥΝΤΑΣ)'
elif answer_02.ID[0] == '10':
return 'ΛΑΤΟ 01 (ΑΚΤΗ ΙΩΣΗΦ ΚΟΥΝΔΟΥΡΟΥ 11)'
elif answer_02.ID[0] == '20':
return 'ΛΑΤΟ 02 (28ΗΣ ΟΚΤΩΒΡΙΟΥ 6)'
elif answer_02.ID[0] == '30':
return 'ΛΑΤΟ 03 (ΑΓ. ΙΩΑΝΝΗΣ 29)'
# ------------- ANSWERS ----------------------------
answer_01 = pd.read_sql_query(
sql_select.sql_query(input_param, order_type), sql_connect.connect())
answer_02 = pd.read_sql_query(
sql_select.data_querry(input_param, order_type), sql_connect.connect())
# -------------------- BARCODES --------------------
barcodes = answer_01['BarCode']
# -------------------- SUPPLIER --------------------
supplier = answer_02.Name[0]
# Αν δεν βρεθεί όνομα να οριστεί το order_type για να εκτελεστεί στην συνέχεια άλλο ερώτημα στην DB
if not supplier:
order_type = 'ΑΠ_ΜΟΒ'
# -------------------- GET TOP 1 COST FOR EVERY BARCODE --------------------
answer_03 = pd.DataFrame()
for counter, barcode in enumerate(barcodes):
percent = int((100 * (counter + 1)) / len(barcodes))
filler = "█" * percent
remaining = '-' * (100 - percent)
print(
f'\r01: CHECKING BARCODE: [{barcode}] \t{counter + 1}/{len(barcodes)} \tComplete:[{filler}{remaining}]{percent}%',
end='',
flush=True)
if order_type == 'ΑΠ_ΜΟΒ':
temp = pd.read_sql_query(
sql_select.get_product_cost_with_no_name(barcode, get_year),
sql_connect.connect())
if temp.empty:
temp = pd.read_sql_query(
sql_select.get_product_cost_with_no_name(
barcode,
dt.now().year), sql_connect.connect())
answer_03 = answer_03.append(temp)
else:
temp = pd.read_sql_query(
sql_select.get_product_cost_with_no_name(barcode, get_year),
sql_connect.connect())
if temp.empty:
temp = pd.read_sql_query(
sql_select.get_product_cost_with_no_name(
barcode,
dt.now().year), sql_connect.connect())
answer_03 = answer_03.append(temp)
# -------------------- MERGE RESULTS --------------------
final_result = pd.merge(left=answer_01,
right=answer_03,
left_on='BarCode',
right_on='BarCode',
how='left').sort_values(by=['Περιγραφή'])
# -------------------- QUANTITY * PRICE --------------------
final_result[
'SUM'] = final_result['Ποσότητα'] * final_result['ΚΑΘΑΡΗ ΤΙΜΗ']
print('\n\n02: Dataframe Merge:\t [✔️]')
# ----------------FILE PATHS----------------------------
detailed_file_path = '/Users/kommas/OneDrive/Business_Folder/Slack/Orders/{k}/{s}/{f}'.format(
s=supplier, f=detailed, k=katastima())
# ----------------DIRECTORY PATH ----------------------------
directory_path = f'/Users/kommas/OneDrive/Business_Folder/Slack/Orders/{katastima()}/{supplier}'
# -------------------- MAKE DIRECTORY IF DOES NOT EXISTS --------------------
try:
print('\n03: Path Check: ', end='')
if not os.path.exists(directory_path):
os.makedirs(directory_path)
print('!NEW!\t [✔️]')
else:
print('\t\t [✔️]')
except OSError:
print("!ERROR! [❌️]")
sys.exit(1)
# -------------OPEN FILE | WRITE ----------------------------
excel_export.export(detailed_file_path, final_result, answer_02, katastima)
print('\n04: Export To Excel:\t [✔️]')
# ----------------SLACK BOT CHAT----------------------------
slack.run(supplier, katastima, final_result, get_year, detailed,
detailed_file_path)
