Using Python for reporting in a single company
This story happened in a real company, despite the fact that some names and events are fictitious.
Glory was an ordinary developer in a small company in the city of N. The company was engaged in the provision of services to educational organizations. There were several applications that needed to be supported, gradually developing little by little. But the authorities of Glory did not believe in his efforts and the fact that he eats his bread for a reason. In addition, the bosses in information technology did not really want to, but wanted to understand what the employees were doing and what kind of productivity the sales department (which I must say consisted of one and a half people).
The authorities looked at the sales manager with a cunning eye and asked: what did you do today for the Motherland. The seller answered: held so many meetings, offered services to so many people. The leadership came to Glory and said: tell me whether the employees are telling the truth or so, they are carrying a gag. Need statistics.
A dialogue took place:
- Do users pay for the service through a third-party service for receiving online payments? So?
- So.
- Do the company management have access to this service?
- There is.
- So, probably, there is an unloading of payments there.
- There is.
“But do you need an additional report?”
- Needed.
- Why?
- Because it’s more convenient, because you have to, because "so and so."
In fairness, it must be said that additional data was needed.
Without hesitation, Glory fulfilled a simple query with subsequent selection in Excel.
The report was a data set in one column on one page. At first they approached Glory in a chaotic manner, in the middle of the day and asked. But give me a report on this organization, and now on this one. Without hesitation, Slava decided that it was enough to endure this and that something beautiful and unified should be done that would satisfy the needs of those who requested.
A small script arose that allowed sending newsletters to interested people:
Made, of course, did not really think Glory. But as they say: “Ah, and so it will do.”
Every morning, Slava came to work, ran a script and sent an email to interested parties.
The next step was setting up automatic sending. It worked something like this:
docker build --tag = reports.
docker run -it --rm reports
And in words in kroon:
0 8 * * * docker run --rm foo
Every day at 8 in the morning the letter left. The management was quite and even began to think that Slava came to work at exactly 8 am every day, drafted a report in Excel, and then sent it by mail to his hands. And he did it on Saturday, and Sunday, and in the snow, and in the rain, and on the polar night.
Github
For the time being, for a while everything was fine and the leadership endured, but not for long. Once there was a meeting at which a new order was announced. Listen, listen and do not say that you did not hear. Sim declare that every employee, every day must report to a higher ranking, and even higher. Well, in general, you understand.
“Every day?” Asked Glory.
- Everyday. - answered the head of the technical department.
- Or maybe not?
- It is necessary Glory, it is necessary.
Slava had several questions regarding this situation. The immediate responsibility was to finalize the software and its reports should consist of something like this:
feat (Module) Fixed a bug in NoteLineCount ... not seriously ...
upd (Module2) Pay no attention to the man behind the curtain
fixed (Module3) I was told to leave it alone, but I have this thing called OCD, you see
* Note: the name of the messages to the commits are taken from here
Slava decided that he would make report based on them. Week later. A new conversation took place about the following content:
- So you send reports every day, but no progress is visible. What for example is: “a mask has been added when entering a phone number on the front end”.
- Well, look. Previously, you had to enter your phone number and check the number of digits and it was inconvenient. And so it’s convenient.
- Hm. Good. Clear. And how do you explain this: “Code refactoring for the Counterparties module. The function for validation is in a separate method. ”
- Well, you see. There is such a thing - version control system, which indicates who, when and what did. Look here. Here are the commits, and here is the code that has been changed. Here is the code added, here is the code deleted.
- Good. Let's do it then. Make a report based on these of your commits, and then we will get together and check how yes what you wrote there.
As a result, this is what happened:
GitHub
The script took changes in the repository and sent a letter about what was done and what was not.
Earlier, Slava was embarrassed to report that those who will check nothing at all do not understand how to write code. Maybe the management understands sales, purchases and all sorts of things and pays money, but you will see certain lines of code in a not very familiar language, observe how the green and red lines flicker when switching from commit to commit.
Yes, Slava is a responsible person and the work worked, but there were those days when the code was hardly finalized or it was laziness. Anyway, in general, the system worked without failures and did not want to add another 15 layers of abstraction, but reports had to be sent every day.
There was nothing left to do but to become a new Mayakovsky and write a gag with a ladder. But every day I didn’t really want to invent a new one. As a result, Vyacheslav began to develop the first solution that came to mind and turned out to be a simple code, where, in the absence of any work performed and pushed changes, a report was compiled. It looked something like this:
GitHub
It remained to be done so that a fake code was formed with subsequent commits. Is there any need to do this? Time will tell.
PS: The story of Glory did not end there. There were several more events that influenced his fate, but this is a completely different story. It is interesting to read in the comments similar stories from life and the decisions that were created on their basis. It is possible that the project will be further developed on the basis of these comments.
Glory was an ordinary developer in a small company in the city of N. The company was engaged in the provision of services to educational organizations. There were several applications that needed to be supported, gradually developing little by little. But the authorities of Glory did not believe in his efforts and the fact that he eats his bread for a reason. In addition, the bosses in information technology did not really want to, but wanted to understand what the employees were doing and what kind of productivity the sales department (which I must say consisted of one and a half people).
The authorities looked at the sales manager with a cunning eye and asked: what did you do today for the Motherland. The seller answered: held so many meetings, offered services to so many people. The leadership came to Glory and said: tell me whether the employees are telling the truth or so, they are carrying a gag. Need statistics.
A dialogue took place:
- Do users pay for the service through a third-party service for receiving online payments? So?
- So.
- Do the company management have access to this service?
- There is.
- So, probably, there is an unloading of payments there.
- There is.
“But do you need an additional report?”
- Needed.
- Why?
- Because it’s more convenient, because you have to, because "so and so."
In fairness, it must be said that additional data was needed.
Without hesitation, Glory fulfilled a simple query with subsequent selection in Excel.
The report was a data set in one column on one page. At first they approached Glory in a chaotic manner, in the middle of the day and asked. But give me a report on this organization, and now on this one. Without hesitation, Slava decided that it was enough to endure this and that something beautiful and unified should be done that would satisfy the needs of those who requested.
A small script arose that allowed sending newsletters to interested people:
import openpyxl, pymysql, os
from smtplib import SMTP_SSL
from email.mime.multipart import MIMEMultipart
from email.mime.base import MIMEBase
from email import encoders
import datetime
# Excel Settings
today = datetime.date.today().strftime('%d.%m.%Y')
excel_file = 'Oplata_polzovateley_' + today + '.xlsx'
# SQL settings
host = ''
user = ''
passwd = ''
db = ''
port=0000
headers = ['id', 'email', 'Контрагент', 'Тариф', 'дата оплаты', 'дата окончания', 'Группа', 'Кол-во входов', 'тип оплаты']
# SMTP Mail settings
smtp_server = 'smtp.gmail.com'
mail_login = ''
mail_passwd = ''
receiver = ['']
cc = ['']
def main():
# Fetch Data from SQL server
conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db, port=port)
cursor = conn.cursor()
cursor.execute('''select * from table''')
data = cursor.fetchall()
conn.close()
# Write Data to Excel file
wb = openpyxl.Workbook()
contractors = {}
for item in data:
diff = item[6] - item[5]
item = list(item)
# print(item)
if diff.days > 10:
item.append('полный')
else:
item.append('триальный')
item = tuple(item)
if item[0][:30] in contractors:
contractors[item[0][:30]] += 1
else:
wb.create_sheet(item[0][:30])
contractors[item[0][:30]] = 2
for i in range(1, len(headers) + 1):
letter = openpyxl.utils.get_column_letter(i)
wb[item[0][:30]][letter + '1'] = headers[i - 1]
wb[item[0][:30]]['A' + str(contractors[item[0][:30]])] = contractors[item[0][:30]] - 1
for i in range(2, len(headers) + 1):
letter = openpyxl.utils.get_column_letter(i)
wb[item[0][:30]][letter + str(contractors[item[0][:30]])] = item[i]
wb.save(excel_file)
wb.remove(wb['Sheet'])
wb.save(excel_file)
# Compose attachment
part = MIMEBase('application', "octet-stream")
part.set_payload(open(excel_file, "rb").read())
encoders.encode_base64(part)
part.add_header('Content-Disposition', 'attachment; filename="%s"' % os.path.basename(excel_file))
# Compose message
msg = MIMEMultipart()
msg['From'] = mail_login
msg['To'] = ', '.join(receiver)
msg['Cc'] = ', '.join(cc)
msg['Subject'] = excel_file
msg.attach(part)
# Send mail
tosend = receiver + cc
smtp = SMTP_SSL('smtp.gmail.com')
smtp.connect(smtp_server)
smtp.login(mail_login, mail_passwd)
smtp.sendmail(mail_login, tosend, msg.as_string())
smtp.quit()
# Wipe file
os.remove(excel_file)
if __name__ == '__main__':
main()
Made, of course, did not really think Glory. But as they say: “Ah, and so it will do.”
Every morning, Slava came to work, ran a script and sent an email to interested parties.
The next step was setting up automatic sending. It worked something like this:
docker build --tag = reports.
docker run -it --rm reports
And in words in kroon:
0 8 * * * docker run --rm foo
Every day at 8 in the morning the letter left. The management was quite and even began to think that Slava came to work at exactly 8 am every day, drafted a report in Excel, and then sent it by mail to his hands. And he did it on Saturday, and Sunday, and in the snow, and in the rain, and on the polar night.
Github
For the time being, for a while everything was fine and the leadership endured, but not for long. Once there was a meeting at which a new order was announced. Listen, listen and do not say that you did not hear. Sim declare that every employee, every day must report to a higher ranking, and even higher. Well, in general, you understand.
“Every day?” Asked Glory.
- Everyday. - answered the head of the technical department.
- Or maybe not?
- It is necessary Glory, it is necessary.
Slava had several questions regarding this situation. The immediate responsibility was to finalize the software and its reports should consist of something like this:
feat (Module) Fixed a bug in NoteLineCount ... not seriously ...
upd (Module2) Pay no attention to the man behind the curtain
fixed (Module3) I was told to leave it alone, but I have this thing called OCD, you see
* Note: the name of the messages to the commits are taken from here
Slava decided that he would make report based on them. Week later. A new conversation took place about the following content:
- So you send reports every day, but no progress is visible. What for example is: “a mask has been added when entering a phone number on the front end”.
- Well, look. Previously, you had to enter your phone number and check the number of digits and it was inconvenient. And so it’s convenient.
- Hm. Good. Clear. And how do you explain this: “Code refactoring for the Counterparties module. The function for validation is in a separate method. ”
- Well, you see. There is such a thing - version control system, which indicates who, when and what did. Look here. Here are the commits, and here is the code that has been changed. Here is the code added, here is the code deleted.
- Good. Let's do it then. Make a report based on these of your commits, and then we will get together and check how yes what you wrote there.
As a result, this is what happened:
GitHub
The script took changes in the repository and sent a letter about what was done and what was not.
Earlier, Slava was embarrassed to report that those who will check nothing at all do not understand how to write code. Maybe the management understands sales, purchases and all sorts of things and pays money, but you will see certain lines of code in a not very familiar language, observe how the green and red lines flicker when switching from commit to commit.
Yes, Slava is a responsible person and the work worked, but there were those days when the code was hardly finalized or it was laziness. Anyway, in general, the system worked without failures and did not want to add another 15 layers of abstraction, but reports had to be sent every day.
There was nothing left to do but to become a new Mayakovsky and write a gag with a ladder. But every day I didn’t really want to invent a new one. As a result, Vyacheslav began to develop the first solution that came to mind and turned out to be a simple code, where, in the absence of any work performed and pushed changes, a report was compiled. It looked something like this:
GitHub
It remained to be done so that a fake code was formed with subsequent commits. Is there any need to do this? Time will tell.
PS: The story of Glory did not end there. There were several more events that influenced his fate, but this is a completely different story. It is interesting to read in the comments similar stories from life and the decisions that were created on their basis. It is possible that the project will be further developed on the basis of these comments.