Using Python to generate reports in a single company
This story happened in a real company, despite the fact that some names and events are fictitious.
Slava was an ordinary developer in a small firm in the city of N. The firm was engaged in providing services to educational organizations. In the presence of several applications that need to be maintained, modifying gradually, 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 are not so good, but they wanted to understand what the employees are doing and how efficient the sales department (which must be said was one and a half people).
The administration looked with a sly eye on the sales manager and asked: what did you do today for the Motherland? The seller answered: he had so many meetings, offered services to so many people. The management has come to Glory and says: tell me if the employees say the truth or so, the gag is carried. Need statistics.
A dialogue has taken place:
')
- Users pay for the service through a third-party service for receiving online payments? So?
- So.
- Does the management of the company have access to this service?
- There is.
- So, probably, there is an unloading of payments.
- There is.
- But you need to have an additional report?
- Needed.
- Why?
- Because it is more convenient because you have to, because “so and so”.
In fairness, I must say that we needed additional data.
Without hesitation, Slava executed a simple query and then selected in Excel.
The report was a single column data collection on one page. At first, they approached Slava 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 it was necessary to do something beautiful and unified that met the needs of the requesters.
There was a small script that allowed the mailing of interested people:
Made, of course, not really thought Glory. But as the saying goes: “Ah, and so it will come down.”
Every morning, Slava came to work, ran the script, and sent a letter to the interested parties.
The next step was to configure automatic sending. It worked like this:
docker build --tag = reports.
docker run -it --rm reports
And in words in the crown:
0 8 * * * docker run --rm foo
Every day at 8 am the letter went away. The management was pretty and even started to think that Slava came to work every day at exactly 8 am, wrote reports to Excel, and then sent them to the post office. And he did it on Saturday, and on Sunday, and in the snow, and in the rain, and in the polar night.
Github
For the time being, everything was fine and the management was patient, but not for long. Once there was a meeting at which a new order was announced. Listen, listen and do not say that you have not heard. Sim declare that every employee, every day must report to the higher rank, and that to the higher one. Well, in general, you understand.
“Every day?” Glory asked.
- Everyday. - replied the head of the technical department.
- Maybe not?
- It is necessary Glory, it is necessary.
Glory has a few questions about this situation. The immediate responsibility was to refine the software and its reports should consist of something like this:
feat (Module) Fixed a bug in NoteLineCount ... not seriously ...
upd (Module2) behind the curtain
fixed (Module3) I was told, OCD, you see
* Note: the name of the commit messages is taken from here.
Slava decided that he would draw up a report based on them. A week has passed. There was a new conversation about this content:
- Here you send reports every day, but progress is not visible. What for example is: “a mask was added when entering a phone number on the front end”.
- Well, look here. Previously, you had to enter your phone number and check the number of dials and was uncomfortable. And so - it is convenient.
- Hmm. Good. Clear. And how do you explain this: “Refactoring the code for the Counterparty module. The function for validation is in a separate method. ”
- Well, you see. There is such a thing - version control system, which indicates who did what, when and what. Look here. Here are commits, and here is the code that has been changed. Here is the code added, here is the code deleted.
- Good. Let us then so. Make a report on the basis of these your commits, and then we will gather and check how you wrote there.
In the end - it turned out this:
Github
The script took changes in the repository and sent a letter about what was done and what was not.
Earlier, Glory was ashamed to report that those who will check nothing at all do not understand in writing the code. Maybe the management understands sales, purchases and other stuff and pays money, but there will be certain lines of code in a not very familiar language, to watch 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 these days when the code was being finalized with difficulty or was too lazy. Anyway, in general, the system worked without failures and did not want to add another 15 layers of abstraction, but reports needed to be sent every day.
There was nothing to do how to become a new Mayakovsky and write a gag with a ladder. But every day I didn’t want to invent something new. As a result, Vyacheslav began to develop the first solution, which came to mind and turned out to be a simple code, where, in the absence of any kind of work performed and no changes started, a report was compiled. It looked like this:
Github
It was necessary to make so that the fake code with the subsequent kommita was formed. Do I need to do this? Time will tell.
PS: The story of Glory is not over. There were several other events that influenced his fate, but this is a completely different story. It is interesting to read in the comments similar life stories and decisions that were created based on them. It is possible that the project will be further developed on the basis of these comments.
Slava was an ordinary developer in a small firm in the city of N. The firm was engaged in providing services to educational organizations. In the presence of several applications that need to be maintained, modifying gradually, 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 are not so good, but they wanted to understand what the employees are doing and how efficient the sales department (which must be said was one and a half people).
The administration looked with a sly eye on the sales manager and asked: what did you do today for the Motherland? The seller answered: he had so many meetings, offered services to so many people. The management has come to Glory and says: tell me if the employees say the truth or so, the gag is carried. Need statistics.
A dialogue has taken place:
')
- Users pay for the service through a third-party service for receiving online payments? So?
- So.
- Does the management of the company have access to this service?
- There is.
- So, probably, there is an unloading of payments.
- There is.
- But you need to have an additional report?
- Needed.
- Why?
- Because it is more convenient because you have to, because “so and so”.
In fairness, I must say that we needed additional data.
Without hesitation, Slava executed a simple query and then selected in Excel.
The report was a single column data collection on one page. At first, they approached Slava 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 it was necessary to do something beautiful and unified that met the needs of the requesters.
There was a small script that allowed the mailing of 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, not really thought Glory. But as the saying goes: “Ah, and so it will come down.”
Every morning, Slava came to work, ran the script, and sent a letter to the interested parties.
The next step was to configure automatic sending. It worked like this:
docker build --tag = reports.
docker run -it --rm reports
And in words in the crown:
0 8 * * * docker run --rm foo
Every day at 8 am the letter went away. The management was pretty and even started to think that Slava came to work every day at exactly 8 am, wrote reports to Excel, and then sent them to the post office. And he did it on Saturday, and on Sunday, and in the snow, and in the rain, and in the polar night.
Github
For the time being, everything was fine and the management was patient, but not for long. Once there was a meeting at which a new order was announced. Listen, listen and do not say that you have not heard. Sim declare that every employee, every day must report to the higher rank, and that to the higher one. Well, in general, you understand.
“Every day?” Glory asked.
- Everyday. - replied the head of the technical department.
- Maybe not?
- It is necessary Glory, it is necessary.
Glory has a few questions about this situation. The immediate responsibility was to refine the software and its reports should consist of something like this:
feat (Module) Fixed a bug in NoteLineCount ... not seriously ...
upd (Module2) behind the curtain
fixed (Module3) I was told, OCD, you see
* Note: the name of the commit messages is taken from here.
Slava decided that he would draw up a report based on them. A week has passed. There was a new conversation about this content:
- Here you send reports every day, but progress is not visible. What for example is: “a mask was added when entering a phone number on the front end”.
- Well, look here. Previously, you had to enter your phone number and check the number of dials and was uncomfortable. And so - it is convenient.
- Hmm. Good. Clear. And how do you explain this: “Refactoring the code for the Counterparty module. The function for validation is in a separate method. ”
- Well, you see. There is such a thing - version control system, which indicates who did what, when and what. Look here. Here are commits, and here is the code that has been changed. Here is the code added, here is the code deleted.
- Good. Let us then so. Make a report on the basis of these your commits, and then we will gather and check how you wrote there.
In the end - it turned out this:
Github
The script took changes in the repository and sent a letter about what was done and what was not.
Earlier, Glory was ashamed to report that those who will check nothing at all do not understand in writing the code. Maybe the management understands sales, purchases and other stuff and pays money, but there will be certain lines of code in a not very familiar language, to watch 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 these days when the code was being finalized with difficulty or was too lazy. Anyway, in general, the system worked without failures and did not want to add another 15 layers of abstraction, but reports needed to be sent every day.
There was nothing to do how to become a new Mayakovsky and write a gag with a ladder. But every day I didn’t want to invent something new. As a result, Vyacheslav began to develop the first solution, which came to mind and turned out to be a simple code, where, in the absence of any kind of work performed and no changes started, a report was compiled. It looked like this:
Github
It was necessary to make so that the fake code with the subsequent kommita was formed. Do I need to do this? Time will tell.
PS: The story of Glory is not over. There were several other events that influenced his fate, but this is a completely different story. It is interesting to read in the comments similar life stories and decisions that were created based on them. It is possible that the project will be further developed on the basis of these comments.
Source: https://habr.com/ru/post/446740/
All Articles