PeopleSoft-差异分析报表代码

发表于 更新于 分类于 本文字数： 16k 阅读时长 ≈ 14 分钟
差异分析报表AE报表，设计思路：将表中的每一条数据先插入一维数组中，然后将一维数组放入二维数组，再对比二维数组的相邻两条。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
/**================================================
任务:C_GP_RPT_0010
描述：差异分析表打上标记，然后分为两个部分
设计思路：将表中的每一条数据先插入一维数组中，然后将一维数组放入二维数组，再对比二维数组的相邻两条；
--------------------------------------------------------
作者：            操作时间：                操作：                     
SIE_ZL			2020-07-03                  创建
=================================================*/


/* 导入公共函数包 */
import C_HRS_FUNC:*;

REM import C_COMMON_FUNC:UTILITY:*;

/* 声明变量 */
Local array of array of any &arrRptPar; /*定义报表参数列表*/
Local array of any &arrRptHead; /*定义报表标题*/

Local number &seqNo; /*序号*/
Local string &gpPayGroup;
Local string &calPrdId;
Local string &calPrdId1;

rem Local string &orderId;

/* 变量初始化 */
&arrRptPar = CreateArrayRept(CreateArrayAny(), 0).Clone();
&arrRptHead = CreateArrayAny();
&arrData = CreateArrayAny();

&oReport = create C_HRS_FUNC:REPORT();
REM &oUtility = create C_HRS_FUNC:UTILITY();
REM &oUtility2 = create C_COMMON_FUNC:UTILITY();

/* 取得查询页面输入的查询参数 */
&seqNo = 1; /*序号*/
&gpPayGroup = C_RPT_0010_AET.GP_PAYGROUP; /*薪资组*/
&calPrdId = C_RPT_0010_AET.CAL_PRD_ID; /*开始月份*/
&calPrdId1 = C_RPT_0010_AET.CAL_PRD_ID1; /*结束月份*/
Warning (&calPrdId | "---" | &calPrdId1);

/*定义输出文件*/
&strTmplateId = "差异分析表";
Local File &rptFile = GetTempFile(%FilePath | &strTmplateId | "_" | %Date | ".xls", "w", "u", %FilePath_Absolute);
/*打印log*/
Local File &log = GetTempFile(%FilePath | "log" | %Date | ".txt", "w", "u", %FilePath_Absolute);


/**
================================================================
生成报表头信息
================================================================
*/
Rem 求出报表描述名称;
&arrRptPar.Push(CreateArrayAny("报表名称", "差异分析表"));

Rem 取报表的制表人(OPRID+空格+OPRID对应员工的NAME_FORMAL);
SQLExec("SELECT NAME_DISPLAY FROM PS_PERSONAL_DATA WHERE EMPLID = :1", %EmployeeId, &strRptPersonNm);
&strRptPersonNm = %OperatorId | " " | &strRptPersonNm;

&arrRptPar.Push(CreateArrayAny("制表人", &strRptPersonNm));
&arrRptPar.Push(CreateArrayAny("制表日期", String(%Date)));
rem Local string &cPrdId = C_RPT_0010_AET.CAL_PRD_ID.Value;
&arrRptPar.Push(CreateArrayAny("开始月份", &calPrdId));
&arrRptPar.Push(CreateArrayAny("对比月份", &calPrdId1));


/**
================================================================
生成报表数据列头信息
================================================================
*/

Rem 设置报表格式信息;
&oReport.AddXlsHeader(&rptFile);
Rem 打印报表头信息;
&oReport.WritePara(&rptFile, &arrRptPar);

Rem 初始化报表标题信息 8 ;
&arrRptHead.Push("序号");
&arrRptHead.Push("工号");
rem &arrRptHead.Push("员工记录");
&arrRptHead.Push("姓名");
&arrRptHead.Push("组织架构");
&arrRptHead.Push("岗位");
&arrRptHead.Push("入职时间");
&arrRptHead.Push("离职时间");
&arrRptHead.Push("薪资组");
&arrRptHead.Push("项目");

REM 17;
<*
&arrRptHead.Push("应出勤");
&arrRptHead.Push("实际出勤");
&arrRptHead.Push("入离职缺勤");
&arrRptHead.Push("事假");
&arrRptHead.Push("病假");
&arrRptHead.Push("产假");
&arrRptHead.Push("旷工");
&arrRptHead.Push("缺卡次数");
&arrRptHead.Push("迟到早退次数");
&arrRptHead.Push("严重迟到早退（次）");
&arrRptHead.Push("延时/夜班次数");
&arrRptHead.Push("出差天数");
&arrRptHead.Push("平日加班(H)");
&arrRptHead.Push("周末加班(H)");
&arrRptHead.Push("节假日加班(H)");
&arrRptHead.Push("缺卡扣款");
&arrRptHead.Push("迟到扣款");
*>
REM 25;

&arrRptHead.Push("基本工资");
&arrRptHead.Push("岗位工资");
&arrRptHead.Push("月度绩效工资");
&arrRptHead.Push("计件工资");
&arrRptHead.Push("销售提成");
&arrRptHead.Push("加班工资");
&arrRptHead.Push("奖金");
&arrRptHead.Push("证书补贴");
&arrRptHead.Push("延时/夜班补贴");
&arrRptHead.Push("驻外津贴");
&arrRptHead.Push("特殊岗位津贴");
&arrRptHead.Push("笔记本电脑津贴");
&arrRptHead.Push("私车公用津贴");
&arrRptHead.Push("工龄津贴");
&arrRptHead.Push("高温津贴");
&arrRptHead.Push("通讯津贴");
&arrRptHead.Push("餐费津贴");
&arrRptHead.Push("探亲津贴");
&arrRptHead.Push("住房津贴");
&arrRptHead.Push("交通津贴");
&arrRptHead.Push("应发工资");
&arrRptHead.Push("个人社保合计");
&arrRptHead.Push("个人公积金合计");
&arrRptHead.Push("当月个税");
&arrRptHead.Push("实发工资");



&oReport.WriteColName(&rptFile, &arrRptHead);


/**
===============================================
============   生成报表主题   =================
===============================================
*/
rem 获取主要数据;
Local string &sqlStr;
&sqlStr = "  SELECT emplid ,CAL_PRD_ID, C_AMT_002, C_AMT_003, C_AMT_004, C_AMT_008, C_AMT_009, C_AMT_011, C_AMT_010, ";
&sqlStr = &sqlStr | " C_AMT_006, C_AMT_012, C_AMT_013, C_AMT_015, C_AMT_016, C_AMT_018, C_AMT_017, C_AMT_020,  ";
&sqlStr = &sqlStr | "  C_AMT_019, C_AMT_021, C_AMT_022, C_AMT_023, C_AMT_024, C_AMT_027, C_AMT_090, C_AMT_091, C_AMT_047, C_AMT_028  ";
&sqlStr = &sqlStr | "    FROM PS_C_WA_PAY_RSLT P WHERE P.GP_PAYGROUP = :1  AND P.CAL_PRD_ID IN (:2, :3 )  ORDER BY P.EMPLID, P.CAL_PRD_ID DESC, P.RSLT_SEG_NUM  ";
Local SQL &rowSql = CreateSQL(&sqlStr, &gpPayGroup, &calPrdId, &calPrdId1);

Local array &data1;

Local array of array of any &data2;

&data1 = CreateArrayAny().Clone();

rem &data2 = CreateArray(CreateArrayAny(), 0).Clone();
&data2 = CreateArrayRept(CreateArrayAny(), 0).Clone();

Local string &Name;
Local string &deptPath;
Local string &stdPosnDescr;
Local string &lastHireDt;
Local string &endDt;
Local string &gpGroup;
Local string &tmpEmplid;


While &rowSql.Fetch(&data1)
   &data2.Push(CreateArrayAny(&data1));
End-While;

If &data2.Len > 0 Then
   Warning "debug2" | &data2.Len;
   For &i = 1 To &data2.Len
      Warning "debug3" | &data2 [&i][1];
      SQLExec(" SELECT  JOB.NAME,JOB.c_short_dept_path,JOB.descr,JOB.last_hire_dt,JOB.termination_dt , job.gp_paygroup FROM PS_C_JOB_CURR_VW2 JOB WHERE JOB.emplid = :1 ", &data2 [&i][1], &Name, &deptPath, &stdPosnDescr, &lastHireDt, &endDt, &gpGroup);
      If &i = 2 Then
         If &data2 [2][1] <> &data2 [1][1] Then
            &rptFile.WriteLine("<tr>");
            &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
            rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
            &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
            
            For &j = 3 To &data2 [&i].Len
               &oReport.WriteCellData(&rptFile, "0", "30"); /* 差异为0*/
            End-For;
            &rptFile.WriteLine("</tr>");
            &seqNo = &seqNo + 1;
         Else
            &rptFile.WriteLine("<tr>");
            &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
            rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
            &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
            
            For &j = 3 To &data2 [&i].Len
               &oReport.WriteCellData(&rptFile, &data2 [1][&j] - &data2 [2][&j], "30"); /* 计算差异*/
            End-For;
            &rptFile.WriteLine("</tr>");
            &seqNo = &seqNo + 1;
         End-If;
      End-If;
      If &i > 2 And
            &tmpEmplid <> &data2 [&i][1] Then
         
         
         If &tmpEmplid <> &data2 [&i - 2][1] Then
            &rptFile.WriteLine("<tr>");
            &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
            rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
            &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
            
            For &j = 3 To &data2 [&i].Len
               &oReport.WriteCellData(&rptFile, "0", "30"); /* 差异为0*/
            End-For;
            &rptFile.WriteLine("</tr>");
            &seqNo = &seqNo + 1;
            
         Else
            
            &rptFile.WriteLine("<tr>");
            &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
            rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
            &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
            &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
            
            For &j = 3 To &data2 [&i].Len
               &oReport.WriteCellData(&rptFile, &data2 [&i - 2][&j] - &data2 [&i - 1][&j], "30"); /* 计算差异*/
            End-For;
            &rptFile.WriteLine("</tr>");
            &seqNo = &seqNo + 1;
         End-If;
         
         
      End-If;
      
      &rptFile.WriteLine("<tr>");
      &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
      &oReport.WriteCellData(&rptFile, &data2 [&i][1], "30"); /* 工号*/
      rem &oReport.WriteCellData(&rptFile, &data2 [&i][2], "30"); /* 员工记录*/
      &oReport.WriteCellData(&rptFile, &Name, "30"); /* 姓名*/
      &oReport.WriteCellData(&rptFile, &deptPath, "30"); /* 组织架构*/
      &oReport.WriteCellData(&rptFile, &stdPosnDescr, "30"); /* 岗位*/
      &oReport.WriteCellData(&rptFile, &lastHireDt, "30"); /* 入职时间*/
      &oReport.WriteCellData(&rptFile, &endDt, "30"); /* 离职时间*/
      &oReport.WriteCellData(&rptFile, &gpGroup, "30"); /* 薪资组*/
      &oReport.WriteCellData(&rptFile, &data2 [&i][2], "30"); /* 项目(期间)*/
      
      For &j = 3 To &data2 [&i].Len
         &oReport.WriteCellData(&rptFile, &data2 [&i][&j], "30");
      End-For;
      &rptFile.WriteLine("</tr>");
      &tmpEmplid = &data2 [&i][1];
      
   End-For;
   
   
   
   If &data2.Len = 1 Then
      &rptFile.WriteLine("<tr>");
      &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
      rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
      &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
      &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
      
      For &j = 3 To &data2 [&i].Len
         &oReport.WriteCellData(&rptFile, "0", "30"); /* 差异为0*/
      End-For;
      &rptFile.WriteLine("</tr>");
   End-If;
   If &data2.Len = 2 Then
      rem  ;
   End-If;
   If &data2.Len > 2 Then
      If &data2 [&data2.Len - 1][1] <> &data2 [&data2.Len][1] Then
         &rptFile.WriteLine("<tr>");
         &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
         rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
         &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
         
         For &j = 3 To &data2 [&data2.Len].Len
            &oReport.WriteCellData(&rptFile, "0", "30"); /* 差异为0*/
         End-For;
         &rptFile.WriteLine("</tr>");
      Else
         &rptFile.WriteLine("<tr>");
         &oReport.WriteCellData(&rptFile, &seqNo, "30"); /* 序号*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 工号*/
         rem  &oReport.WriteCellData(&rptFile, " ", "30"); /* 员工记录*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 姓名*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 组织架构*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 岗位*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 入职时间*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 离职时间*/
         &oReport.WriteCellData(&rptFile, " ", "30"); /* 薪资组*/
         &oReport.WriteCellData(&rptFile, "差异", "30"); /* 项目(期间)*/
         
         For &j = 3 To &data2 [&data2.Len].Len
            &oReport.WriteCellData(&rptFile, &data2 [&data2.Len - 1][&j] - &data2 [&data2.Len][&j], "30"); /* 计算差异*/
         End-For;
         &rptFile.WriteLine("</tr>");
         
      End-If;
      
      
   End-If;
End-If;


/*打印结尾信息*/
&oReport.WriteEnding(&rptFile);


&log.Close();
&rptFile.Close();