报表的数据源,员工异动信息(新) 是个java数据集。

SELECT distinct PERSON.FName_l2 AS personName, PERSON.FNumber AS personNumber, PERSON.FID AS Ppersonid,

personnew.FID AS personid,

t0.FName_l2 AS adminOrgUnitName ,

ADMINORGUNIT.FDisplayName_l2 AS adminOrgUnitDisplayName ,

POSITION.FName_l2 AS positionName ,

POSITION.FNumber AS positionNumber ,

employeeType.fname_l2 AS empTypeName,

employeeState.fname_l2 AS empFenleiName,

EMPFLOWINOUT.FEFFDT AS empflowinoutEFFDT ,

EMPFLOWINOUT.FActionID AS actionid ,

hrBizDefine.FName_l2 as affairName,

hrBizDefine.FBIZDEFINETYPE as affairType,

EMPFLOWINOUT.FLEFFDT AS leffdt ,

EMPFLOWINOUT.FPositionStatus AS positionstatus ,

ACTIONTYPE.fname_l2 AS actionTypeName ,

EMPFLOWINOUT.FActionReasonID AS actionReasonID ,

varReason.FName_l2 as actionReasonName,

EMPFLOWINOUT.FAssignType AS assignType ,

orgFunction.fname_l2 AS orgfunctionname ,

orgFunctionType.fname_l2 AS orgfunctiontypename ,

jobGrade.fname_l2 AS jobGrade,

job.Fnumber AS jobnumber,

job.fname_l2 AS jobname,

jobSubCategory.fnumber AS jobSubNumber,

jobSubCategory.fname_l2 AS jobSubName,

jobCategory.fnumber AS jobCategoryNumber,

jobCategory.fname_l2 AS jobCategoryName,

jobFamily.fnumber AS jobFamilyNumber,

jobFamily.fname_l2 AS jobFamilyName,

YEAR(EMPFLOWINOUT.FEFFDT) AS year,

MONTH(EMPFLOWINOUT.FEFFDT) AS month,

DAYOFMONTH(EMPFLOWINOUT.FEFFDT) AS day,

ACTIONTYPE.FDisplayName_l2 AS actiontypeDisplayname

FROM T_HR_EMPFLOWINOUT AS EMPFLOWINOUT

LEFT JOIN T_BD_PERSONHIS AS PERSON

ON PERSON.FhistoryRelateID=EMPFLOWINOUT.FPERSONID AND PERSON.FEFFDT<=EMPFLOWINOUT.FBizDate AND PERSON.FLEFFDT>=EMPFLOWINOUT.FBizDate

LEFT JOIN T_BD_PERSON AS personnew

ON personnew.FID=EMPFLOWINOUT.FPERSONID

LEFT JOIN T_HR_EMPORGRELATION AS EMPORGRELATION

ON EMPORGRELATION.FPersonID=PERSON.FhistoryRelateID AND EMPORGRELATION.FIsInner=1

LEFT JOIN T_HR_EMPLABORRELATIONHIS AS LABORRELA

ON LABORRELA.FPersonID=EMPORGRELATION.FPERSONID AND LABORRELA.FEFFDT<=EMPFLOWINOUT.FBizDate AND LABORRELA.FLEFFDT>=EMPFLOWINOUT.FBizDate

LEFT JOIN T_BD_EmployeeModle AS employeeType

ON employeeType.fid = LABORRELA.FEmployeeModleID

LEFT JOIN T_HR_BDEmployeeType AS employeeState

ON employeeState.fid = LABORRELA.FLaborRelationStateID

LEFT JOIN T_ORG_Admin AS t0

ON t0.fid = empflowinout.fadminorgid

LEFT JOIN T_ORG_OrgFunction AS orgFunction

ON orgFunction.fid = t0.forgfunctionid

LEFT JOIN T_ORG_OrgFunctionType AS orgFunctionType

ON orgFunctionType.fid = orgFunction.FOrgFunctionTypeId

LEFT JOIN T_HR_BDEMPLOYEETYPE AS EMPTYPE

ON EMPTYPE.FID=LABORRELA.FLABORRELATIONSTATEID

LEFT JOIN T_HR_PersonPositionHis AS personposition

ON personposition.FPersonId = personnew.FID AND personposition.FEFFDT<=EMPFLOWINOUT.FBizDate AND personposition.FLEFFDT>=EMPFLOWINOUT.FBizDate

LEFT JOIN T_HR_JobGrade AS jobGrade

ON jobGrade.fid = personposition.FJobGrade

LEFT JOIN T_HR_EmployeeFenLei AS EMPFENLEI

ON EMPFENLEI.FID=EMPTYPE.FEmployeeFenLeiID

LEFT OUTER JOIN t_org_adminorgtreehis ADMINORGUNIT

ON ADMINORGUNIT.FADMINORGID=EMPFLOWINOUT.FADMINORGID

AND ADMINORGUNIT.FEFFDT<=EMPFLOWINOUT.FLEFFDT AND ADMINORGUNIT.FLEFFDT>=EMPFLOWINOUT.FLEFFDT

LEFT JOIN T_ORG_POSITIONHIS AS POSITION

ON POSITION.FhistoryRelateID=EMPFLOWINOUT.FPOSITIONID

AND POSITION.FEFFDT<=EMPFLOWINOUT.FLEFFDT AND POSITION.FLEFFDT>=EMPFLOWINOUT.FLEFFDT

LEFT JOIN T_HR_AFFAIRACTIONREASON AS ACTIONTYPE

ON ACTIONTYPE.FID=EMPFLOWINOUT.FACTIONTYPEID

LEFT JOIN T_ORG_Job AS job

ON job.fid = POSITION.FJobID

LEFT JOIN T_HR_JobSubCategory AS jobSubCategory

ON jobSubCategory.fid = job.FJobSubCategoryID

LEFT JOIN T_ORG_JobCategory AS jobCategory

ON jobCategory.fid = job.FJobCategoryID

LEFT JOIN T_HR_JobFamily AS jobFamily

ON jobFamily.fid = job.FJobFamilyID

LEFT JOIN T_HR_HRBIZDEFINE as hrBizDefine

ON hrBizDefine.fid = EMPFLOWINOUT.FActionID

LEFT JOIN T_HR_VariationReason AS varReason

ON varReason.FID = EMPFLOWINOUT.FActionReasonID

WHERE ((exists(select 1 from t_org_admin t1 where t1.fid = ADMINORGUNIT.FADMINORGID and (t1.flongnumber = '组织长编码'

OR t1.flongnumber LIKE '组织长编码!%' ) ))

and ( EMPFLOWINOUT.FEFFDT>= {ts'开始日期'}

and EMPFLOWINOUT.FEFFDT<= {ts'结束日期'} ) )

order by PERSON.FNumber,EMPFLOWINOUT.FEFFDT,EMPFLOWINOUT.FAssignType