What can you personally get from 54-FZ, username, or DIY-automation of cost accounting

From July 1, 2017, the use of cash registers (CCMs), which send all of their transactions directly to the Federal Tax Service, became obligatory on the territory of the Russian Federation. The so-called online cash registers. The introduction of this innovation has already been discussed from all sides, or at least from all serious sides. Can the federal law have not very serious sides and what kind of room for creativity this gives us - about it under the cut.


All KKM now report to the FTS about our transactions, and the FTS, wanting to ensure the integrity of each online ticket office, has released a special mobile application ( Google Play , App Store ) that allows the user to check any check provided to him and report to the competent authorities if the check failed.


When I hear that someone begins to collect large amounts of information, I think not about the potential profit from this and about the costs of implementation. And not even that Big Brother is watching. My head immediately draws signs that you can filter and join, cubes that you can rotate, diagrams that you can build, etc. Can we somehow stick to the flow of our own data coming from the seller to the state? We can. (In the form of a small bow, the FTS application also makes it possible to obtain an electronic version of this same check.)
')
When I found out about this opportunity, I caught fire. A closer look at the possibilities of the application did not disappoint me: a check can be obtained not only in html or png, but also in json.


The fact is that I try to keep track of all my expenses. I try to do it regularly, but, of course, it constantly turns out that I forgot to do this for several weeks. And the session of Ericksonian hypnosis begins with immersion into the past and filling of white spots.

It is from this painful process that the FTS will help us get rid of.

It turns out, theoretically, instead of analyzing extracts from several banks and a pile of paper checks at the end of a month (or two), trying to figure out where all the money has merged again, you can analyze a mountain of letters in a json box in attachments. The prospect is also so-so, but unlike the first, it is much better automated.

So, making a purchase, we can receive our check in the mail in JSON format. Moreover, in theory, when buying, for this we do not even need an application from the FTS: we can generate in advance in the application a business card with the qr-code of our mail and present it to the cashier at the time of purchase. Then we can request an email instead of a paper check, which should immediately fall into the mail. As with online purchase.


But even the Federal Tax Service directly writes about this feature - "Do not flatter yourself." It is not supported by all CCM. And even where it is supported by KKM, it is not at all the fact that it is supported by the cashiers themselves. So while it is for the strong in spirit.

Having received a check to the post office, we can only automate its parsing and unloading in a convenient storage for us.

It is necessary to choose where to unload all this so that it will be convenient to work with it further.

I don’t know about you, but personally, I use these or other state employees, as a result, I always export the data from them for analysis into good old Excel. Whatever graphs and diagrams developers have included in their product beforehand, they will never be able to predict everything I want to extract from the data. Which transactions to take into account, which ones - not to take into account, compare with the phases of the moon, group, divide into today's number ... Excel can do it all.

Moreover, if we put our Excel in OneDrive, it will be perfectly accessible through the web, including from mobile OS. And, unlike GoogleDrive, formulas will work fine here, including those that allow us to organize cascading drop-down lists for the category and subcategory of each purchase.


Things are easy - deal with the parser. We need a mechanism that receives a letter with an attachment from the mailbox, parses the json attachments and writes the result to the Excel table in OneDrive.

In an ideal world, I see this parser as something of an IFTTT tap . Then our solution would live completely online and did not require anything from the user (since we are talking about the ideal world, then the FTS “business cards” work perfectly in it).

However, to understand right away how to write to a person from the street his step for IFTTT, as well as to understand whether it is possible at all. I did not succeed. If someone can tell a sensible guide or an alternative platform, I would be very grateful.

Since the full networking solution didn’t work, we implement one link of the parser on the desktop. At NORBIT, I deal with the implementation of Dynamics CRM, so Microsoft’s stack is the most familiar to me. Yes, and we have already begun to use it, choosing Excel and OneDrive. We implement a plugin for Outlook, where we put all the logic we need.

Here I took the path of least resistance.

For a start I took the JSON received from the FTS and fed it to json2csharp.com . Got the class structure for deserialization:

public class Item {    public string name { get; set; }    public int nds18 { get; set; }    public int price { get; set; }    public double quantity { get; set; }    public int sum { get; set; }    public int? nds10 { get; set; } } public class Receipt {    public List<Item> items { get; set; }    public string retailPlaceAddress { get; set; }    public string userInn { get; set; }    public int requestNumber { get; set; }    public int nds18 { get; set; }    public string fiscalDriveNumber { get; set; }    public string user { get; set; }    public string @operator { get; set; }    public int fiscalDocumentNumber { get; set; }    public int taxationType { get; set; }    public int ecashTotalSum { get; set; }    public string kktRegId { get; set; }    public DateTime dateTime { get; set; }    public int operationType { get; set; }    public int cashTotalSum { get; set; }    public int receiptCode { get; set; }    public int nds10 { get; set; }    public int totalSum { get; set; }    public int shiftNumber { get; set; }    public int fiscalSign { get; set; } } 

Now you need to get a letter, deserialize the attachment into the classes obtained above and turn them into XLSX.

For deserialization, I took Newtonsoft.Json, and for writing to Excel - ClosedXML. Later it turned out that it was possible not to bother with writing directly to XLSX, but quietly writing to CSV - for some reason, ClosedXML crashes when working with files that have pivot tables. So the statistics so far had to be put into a separate Excel file, and the slip from the plug-in to him as a source of data.

The plugin itself is very simple. So that he did not process all incoming letters, I started a separate box for checks and check that the letter came to him. Next you need, of course, to make it into custom settings, like all folder addresses.

         void Items_ItemAdd(object Item)       {           Outlook.MailItem mail = (Outlook.MailItem)Item;           if (Item != null)           {               if (mail.Attachments.Count == 1 & mail.To.Equals("my@mail.address"))               {                                     Outlook.Attachment attach = mail.Attachments[1];                   string path = "C:\\_Data\\_tmp\\" + attach.FileName;                   attach.SaveAsFile(path);                   string text = System.IO.File.ReadAllText(path);                   System.IO.File.Delete(path);                   List<TableRow> objectList = GetBillsData(text);                   WriteBillsToXLSX(objectList);               }           }       }       private static void WriteBillsToXLSX(List<TableRow> objectList)       {           var workbook = new XLWorkbook(tablePath);           var ws = workbook.Worksheet("Products");           int startrow = ws.LastRowUsed().RowNumber();           if (ws.CellsUsed().Count() != 0)               startrow = ws.CellsUsed().Last().Address.RowNumber;            foreach (var item in objectList)           {               startrow++;               ws.Cell(startrow, 1).Value = item.dateTime;               ws.Cell(startrow, 2).Value = item.sum;               ws.Cell(startrow, 3).Value = item.quantity;               ws.Cell(startrow, "J").Value = item.user;               ws.Cell(startrow, "S").Value = item.name;           }           workbook.SaveAs(tablePath);       }       private static List<TableRow> GetBillsData(string bill)       {           Receipt doc = JsonConvert.DeserializeObject<Receipt>(bill);           List<TableRow> objectList = new List<TableRow>();           foreach (var item in doc.items)           {               objectList.Add(new TableRow(item, doc));           }           return objectList;       } 

All data transformation from JSON to a table row is removed in the TableRow constructor.

Now, having made a purchase, it is enough for us to scan the check, and all its lines will get into our file in the cloud. View this file we can immediately, without departing from the cash register. Viewing is available to us on any operating system that supports the work of modern browsers.

It remains only to put down categories and subcategories for the loaded lines and you can build any analyst that pleases our soul. Another couple of lines of code, and the plugin will learn to put down categories / subcategories for lines if the file already has a line with the same description and the category filled earlier.


Here is a circulation of data in nature.