OA electronic form design-Annual Leave application form-data verification

It has been six months since the launch of OA at the beginning of the year.

At the end of last month, the Administrative Clerk found me and said that some new department clerks had filled out the "Annual Leave Application Form" and asked me to solve the problem.

I checked it out. The clerk was a male and filled seven documents for the same person on the same day. 4 sheets were returned by the supervisor and 3 others were √.

Therefore, I give the administrative clerk an idea. If the first step of the process is set to your approval, and the documents are filled in normally, they will be approved by their respective supervisors. Otherwise, you will go back and fill in the documents, the Administrative Clerk agreed at the time, and the paper was also approved by her.

The next day, the situation has changed. The Administrative Manager does not agree with the practice of the clerk's approval on the first level. I did not say why. Well, I heard from the official team, you can only try to avoid such errors from the perspective of the program.

Therefore, the following SQL code is available:

Declare @ date char (8), -- Date of entry @ today char (8), -- Date of the server @ year_yn int, -- Initial life (not sure whether it is full year) @ year int, -- actual annual leave life @ start_date char (8), -- Annual leave start date @ end_date char (8 ), -- Annual leave end date @ day int -- annual leave days set @ date = '000000' set @ today = convert (char (8), getdate (), 20111212) set @ year_yn = convert (INT, left (@ today, 4)-convert (INT, left (@ date, 4 )) set @ year = (select case when right (@ today, 4)> = right (@ date, 4) Then @ year_yn else @ year_yn-1 end) set @ start_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year-1) + right (@ date, 4) set @ end_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year) + right (@ date, 4) -- Annual Leave description, 5 days under 5 years, 5-9 years, 7 days, set @ day = (select case when @ year <= 4 then 5 when @ year> 4 and @ year <10 then 7 when @ year> = 10 then 10 end) print @ date print @ yearprint @ start_dateprint @ end_dateprint @ day -- result20112 → employment date 2 → initial life 20121212 → annual leave start date 20131212 → annual leave end date 5 → annual leave days

Since the OA window to obtain data mimj program does not support the above T-SQL statements, so the first reaction is to write an SQL Table value function, so the following function code:

Create Function intime (@ worktime varchar (20), @ servertoday char (8) returns @ temptable table (date1 char (8), date2 char (8), daycount INT) as begin declare @ date varchar (20), -- Date of entry @ today char (8), -- Date of the server @ year_yn int, -- Initial life (not sure whether it is full year) @ year int, -- actual annual leave duration @ start_date char (8), -- Annual leave start date @ end_date char (8 ), -- Annual leave end date @ day int -- annual leave days set @ [email protected] Set @ today = @ servertoday set @ year_yn = convert (INT, left (@ today, 4 )) -convert (INT, left (@ date, 4) set @ year = (select case when right (@ today, 4)> = right (@ date, 4) then @ year_yn else @ year_yn-1 end) set @ start_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year )) + right (@ date, 4) set @ end_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year-1 )) + right (@ date, 4) -- Annual Leave description, 5 days under 5 years, 7 days from 5 to 9 years, set @ day = (select case when @ year <= 4 then 5 when @ year> 4 and @ year <10 then 7 when @ year> = 10 then 10 end) insert into @ temptable select @ start_date, @ end_date, @ day return end

Note: Why do two parameters need to be added to a function? Can a [date of entry] parameter be used, because the SQL Table value function does not support dynamic data.

I think this function has two parameters which are difficult to use. If you forget it, you should change the stored procedure. So we have the following Stored Procedure Code:

Create proc worktime @ worktime varchar (20) As declare @ date varchar (20), -- Date of entry @ today char (8), -- Date of the server @ year_yn int, -- initial years (not sure whether the year is full) @ year int, -- actual annual years @ start_date char (8), -- Annual leave start date @ end_date char (8 ), -- Annual leave end date @ day int -- annual leave days set @ [email protected] Set @ today = convert (char (8), getdate (), 112) set @ year_yn = convert (INT, left (@ today, 4)-convert (INT, left (@ date, 4 )) set @ year = (select case when right (@ today, 4)> = right (@ date, 4) Then @ year_yn else @ year_yn-1 end) set @ start_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year) + right (@ date, 4) set @ end_date = convert (char (4), (convert (INT, left (@ date, 4) + @ year-1) + right (@ date, 4) -- Annual Leave description, 5 days under 5 years, 5-9 years, 7 days, set @ day = (select case when @ year <= 4 then 5 when @ year> 4 and @ year <10 then 7 when @ year> = 10 then 10 end) select @ start_date start date, @ end_date end date, @ day annual leave days

Thinking about the stored procedure is not easy. If the leader wants to look at the annual leave schedule of everyone, then it is not necessary to make the effort. Therefore, the following view code is available:

Create view userworktimeas select employee ID a, name B, entry date C, start date D, end day e, case when actual years> 0 then annual leave days else 0 end F, isnull (days summary, 0) g, case when actual years> 0 then annual leave days else 0 end-isnull (days summary, 0) hfrom (select *, convert (char (4 ), (convert (INT, left (onboarding day, 4) + actual life-1) + right (onboarding day, 4) start day, convert (char (4 ), (convert (INT, left (Employment day, 4) + actual life) + right (Employment day, 4) end day, case when actual years <= 4 then 5 when actual years> 4 and actual years <10 then 7 when actual years> = 10 then 10 end annual leave days from (select *, case when right (system day, 4)> = right (Employment day, 4) then initial years else initial years-1 end actual years from (select *, convert (INT, left (system day, 4)-convert (INT, left (Employment day, 4) initial life from (select empno employee ID, empname name, convert (char (8 ), worktime, 112) onboarding day, convert (char (8), getdate (), 112) system day from doorsv. imsdb. DBO. employeewhere worktime <> '') AAA) BBB) CCC) dddleft join (select number, annual leave start date, annual leave end date, sum (current days) days summary from (select newopenquery1 number, newopenquery1c name, datetime1 employment date, text4 annual leave days, text1 invited days, datetime5 leave, datetime6 leave, text6 current days, datetime2 annual leave start date, datetime3 annual leave end date, case when resda021 = '1' then' unfinished 'when resda021 = '2' then' agree 'when resda021 = '3' then' disagree 'when resda021 = '4' then' the 'end signature result is extracted from hr07join resda on resda002 = hr07002 and resda001 = 'hr07' where resda021 = '2' and text6 <> 0) xxxgroup by no., annual leave start date, annual leave end date) YYY on NO. = employee No. collate province and start day = annual leave start date collate chinese_prc_ci_as and end day = annual leave end date collate chinese_prc_ci _

Final result:

After the SQL code is confirmed, modify the mimj window and associate the hr07_mimj.aspx value with the Javascript verification code.

 

Conclusion: The design of electronic form principles-Data Control (read-only, Numerical range) and historical data interaction are very important. We should follow the ERP process and program design ideas, "rigorous, standardized, scientific, and efficient ".

OA electronic form design-Annual Leave application form-data verification