Corporate consumption of a cartoon "political reform" in mind

A card in every company exist, not only include attendance machine, there will be access control, ordering meals, canteen consumption and so on. Our company uses the Xiamen shooter technology card system, before and after the use of several years.

Before me, the function of a cartoon mainly enabled the attendance and consumption of the two major modules.

1, the attendance machine is each subsidiary has the corresponding equipment, employees daily work card, and then each regional subsidiary of the Ministry of Personnel attendance colleagues began to queue every week, one day to download attendance data. If everyone download the attendance data together, it will be due to the huge amount of data caused by the network congestion, after all, the company's small VPN is too bad.

2, the consumption data is the IT maintenance personnel responsible, each month when the IT maintenance staff will go to download all the regional consumer offline data, at the beginning of the month in a card system to export all employees of the company's consumption data, almost a hundred thousand of, and then in Excel to make a summary table, Then the data sent to the company's QQ group card for Human resources colleagues to download manual import HR system. The "subsidy" at the beginning of the month is a task that it maintainers must do.

Then, on the above point of work, dare to pick up, can not meet many people, pit is too much, can say too many things.

I think the company card system has several drawbacks:

1, time and attendance data are to be manually downloaded by the human resources and then imported, and often because the account card in the inside and other people have not been able to enter the situation. There is no way to achieve automatic download and automatic import, increase the labor costs, it in the value of the basic is not reflected; the main reason for this is the problem of the one-card device.

2, the consumer computer data collation all is it to do, from the management and security requirements, it should not go to direct contact with the data. It takes too much time and effort to sort through the data and do things that are worthless and meaningful.

Since the beginning of this March this year, I have come to grips with the many doorways of the card system. Since the head of the information department has made so many sins, I naturally can not do without planning, has been trying to get it done to the personnel department to do things.

But in the process I need to solve is to write a program, so that personnel can freely query the consumer data, can be exported to the program to summarize the table. Then I'll write a timed job that can be automatically "subsidized".

First, the inquiry procedure

Because I don't know much about the database structure of the daemon, I still know what table the consumer watch is, as well as other related people, devices and other tables.

So I wrote the function of querying the consumption table into a stored procedure, and queried the consumption records by passing in the time and region parameters.

And the calculation of the summary table, because it takes a long time, so I put the calculation function into a stored procedure and use a table to store the results of the calculation:

Then, the rapid development of a query program through C #, the function is relatively simple, but by doing a version of the SQL Server stored procedures, once I have modified the program, I will update the new version, the previous version will automatically expire, to avoid the old program is used in the case:

The program interface is simple, as follows:

Second, set automatic schedule calculation monthly consumption summary table

As a result of the stored procedure, it is possible to use scheduling to execute it!

III. Automatic distribution of subsidies

PS: When it comes to "subsidies", I want to laugh. In fact, the employee's salary advance is only $600.

But it's harder to do it automatically.

Because the subsidy is to eliminate the turnover and non-company personnel, and the card system is not the implementation of standard stored procedures, but the SQL statement written into the program, so there is no way to know how it executes the table.

Although I know which table to update which fields can be subsidized, there is a risk because I do not know more of the logic.

Call shooter technology, tell me that this database specification is for money! (Suddenly heart 10,000 grass mud horse Pentium, the company is with shooter technology relationship how stiff ah?) ）

After you plan to use SQL Server Profiler to connect to the backend database, you are prompted to say:

I do not know the company Information Management Department led the original purchase of a card system when the end is what to think, incredibly still with such a primitive system exists!! Background database with SQL Server 2000, it is not able to spit slot!

So I had to install SQL Server 2008 Profiler tools, successfully connected to the system, in-house tracking, after a lot of analysis, finally know the "grant" The Logic:

It uses a large number of temporary tables, such as this type of statement:

IF object_id (' tempdb.. #Subsidy ') is NULL

BEGIN

SELECT A.person_id,person_no,person_name,card_no,dept_no, Dept_name,subsidy_fund,subsidy_fund[pritime_fund], Subsidy_fund[use_fund],subsidy_fund[fact_fund],person_name[type_name], Birthday[subsidy_date],person_no[type_no ],0[data_type],a.person_id[id_key],cast (0 as bit) [Is_onlysubsidy]

Into #Subsidy

From St_person A

Left JOIN st_department C on c.dept_id=a.dept_id

Left JOIN St_card B on b.person_id=a.person_id

WHERE 1<>1

END

ELSE

TRUNCATE TABLE #Subsidy

So with these logic, the next process of subsidizing the stored procedure is much better:

This stored procedure does not have any parameters passed in, and it automatically filters out all departments that cannot be granted subsidies!

Set schedule work, limited to 1th in the morning of every month to run, and once and for all to solve my manual subsidies for the difficult action!

A cartoon thing took me a lot of time. Basically there is no database specification, and many of the technology can not be mastered at all. It can be thought that if the system in the project and implementation, if IT department does not intervene or lack of professionalism, a lot of documents if not provided, then the future to maintain is how horrible, is dedicated to future generations dug pits and abuse! Think of the current encryption system, is not a big hole? Don't mention how rubbish it is!

Corporate consumption of a cartoon "political reform" in mind