WITHAAAS(/*零活工资*/SELECT
#{startTime}AS startTime,
#{endTime}AS endTime,O.OFFICE_CODEASFDEPTID,O.OFFICE_NAMEASOFFICENAME,E.fcode,E.fname,NVL(detail.fwage,0)AS wage
FROM
yg_pr_timesheetreport a
LEFTJOINYG_PR_TIMESHEETENTRYDETAIL detail ON detail.fparentid = a.id
LEFTJOINYG_BS_EMPLOYEEEON detail.fempid =E.id
left join JS_SYS_OFFICEO on O.OFFICE_CODE=e.fdepartmentid
WHERE a.status =3
and a.fsrcinterid =0<if test="startTime !=null">
and trunc(a.fdate)>= #{startTime}</if><if test="endTime !=null">
and trunc(a.fdate)<= #{endTime}</if><if test='fcountsource !=null and fcountsource !="" and fcountsource =="2"'>/*结卡工资*/UNIONALLSELECT
#{startTime}AS startTime,
#{endTime}AS endTime,TM.FDEPTID,O.OFFICE_NAMEASOFFICENAME,PE.fcode,PE.FNAME,NVL(TE.FJKWAGE,0)ASWAGEFROMYG_PR_TIMESHEETTMLEFTJOINYG_PR_TIMESHEETREPORTTONTM.ID=T.FSRCINTERIDINNERJOINYG_PR_TIMESHEETENTRYDETAILTEONT.ID=TE.FPARENTIDLEFTJOINJS_SYS_OFFICEOONO.OFFICE_CODE=TM.FDEPTIDLEFTJOINYG_BS_EMPLOYEEPEONPE.ID=TE.FEMPID/*left join JS_SYS_OFFICE O on O.OFFICE_CODE =Pe.fdepartmentid*/WHERETM.ftimesheettype IN('1026','1028')ANDTM.STATUS=5<if test="startTime !=null">
and TRUNC(TM.update_date)>= #{startTime}</if><if test="endTime !=null">
and TRUNC(TM.update_date)<= #{endTime}</if></if><if test='fcountsource !=null and fcountsource !="" and fcountsource =="1"'>/*汇报工资*/UNIONALLSELECT
#{startTime}AS startTime,
#{endTime}AS endTime,TM.FDEPTID,O.OFFICE_NAMEASOFFICENAME,PE.fcode,PE.FNAME,/*round(NVL( TE.FWORKLOAD, 0 ) * NVL( TE.FQUOTA, 0 ),2) AS WAGE*/round(NVL(TE.FRPWAGE,0),2)ASWAGEFROMYG_PR_TIMESHEETENTRYDETAILTEINNERJOINYG_PR_TIMESHEETREPORTTONT.ID=TE.FPARENTIDLEFTJOINYG_PR_TIMESHEETTMONTM.ID=T.FSRCINTERIDLEFTJOINJS_SYS_OFFICEOONO.OFFICE_CODE=TM.FDEPTIDLEFTJOINYG_BS_EMPLOYEEPEONPE.ID=TE.FEMPID/*left join JS_SYS_OFFICE O on O.OFFICE_CODE =Pe.fdepartmentid*/WHERE1=1
and t.fsrcinterid !=0<if test="startTime !=null">
and TRUNC(T.fdate)>= #{startTime}</if><if test="endTime !=null">
and TRUNC(T.fdate)<= #{endTime}</if></if>)SELECT* from (SELECTA.startTime,A.endTime,<if test='(summaryConditions !=null and summaryConditions =="2") or (summaryConditions !=null and summaryConditions =="1,2") '>A.FDEPTID,A.OFFICENAMEAS"fdeptid.officeName",</if><if test='(summaryConditions !=null and summaryConditions =="1") or (summaryConditions !=null and summaryConditions =="1,2") '>A.FNAME"fname",A.fcode "fcode",/*A.OFFICENAME AS "fdeptid.officeName",*/</if>SUM(A.WAGE) as "wage"FROMAAA
where 1=1<if test="fname != null and fname != ''">ANDA.FNAME like '%'|| #{fname}||'%'</if><if test="fdeptid != null and fdeptid.officeName != '' and fdeptid.officeName != null">ANDA.OFFICENAME like '%'|| #{fdeptid.officeName}||'%'</if>GROUPBYA.startTime,A.endTime
<if test='(summaryConditions !=null and summaryConditions =="2") or (summaryConditions !=null and summaryConditions =="1,2") '>,A.FDEPTID,A.OFFICENAME</if><if test='(summaryConditions !=null and summaryConditions =="1") or (summaryConditions !=null and summaryConditions =="1,2") '>,A.FNAME,A.fcode
/*,A.OFFICENAME*/</if>ORDERBYA.startTime
<if test='(summaryConditions !=null and summaryConditions =="2") or (summaryConditions !=null and summaryConditions =="1,2") '>,A.OFFICENAME</if><if test='(summaryConditions !=null and summaryConditions =="1") or (summaryConditions !=null and summaryConditions =="1,2") '>,A.fcode
</if>)UNIONALLSELECTNULLAS startTime,NULL as endTime,<if test='(summaryConditions !=null and summaryConditions =="2") or (summaryConditions !=null and summaryConditions =="1,2") '>'-' as FDEPTID,N'-'AS"fdeptid.officeName",</if><if test='(summaryConditions !=null and summaryConditions =="1") or (summaryConditions !=null and summaryConditions =="1,2") '>'-'AS"fname",'-'AS"fcode",/*N'-' AS "fdeptid.officeName",*/</if>SUM(A.WAGE) as "wage"FROMAAA
where 1=1<if test="fname != null and fname != ''">ANDA.FNAME like '%'|| #{fname}||'%'</if><if test="fdeptid != null and fdeptid.officeName != '' and fdeptid.officeName != null">ANDA.OFFICENAME like '%'|| #{fdeptid.officeName}||'%'</if>