etms/WEB-INF/reportlets/queryTrainFlow.cpt

<?xml version="1.0" encoding="UTF-8"?>
<WorkBook xmlVersion="20170720" releaseVersion="9.0.0">
<TableDataMap>
<TableData name="ds1" class="com.fr.data.impl.DBTableData">
<Parameters>
<Parameter>
<Attributes name="searchkey"/>
<O>
<![CDATA[]]></O>
</Parameter>
</Parameters>
<Attributes maxMemRowCount="-1"/>
<Connection class="com.fr.data.impl.NameDatabaseConnection">
<DatabaseName>
<![CDATA[trainms]]></DatabaseName>
</Connection>
<Query>
<![CDATA[
        select md5(uuid()) as id,kcmc,coursestatus,coursefinish, jhly,bjmc,classfinish,bjlb,addgroupname,jhrs,starttime,endtime,sfbfzs,pxxsh,
        pxxs,sfkh,khlx,khfs,teacher,realteacher,price,ccstatus,pgwjmc,pgstatus,name,startdate,enddate,state,title,uploadtime,teacher_code,teacher_department
    from (
        select
        cc.kcmc,
				IF(yxx.status is not null,(case yxx.status when -1 then "未完成" when 0 then "未完成" when 1 then "未完成" else "完成" end),(case pg.status when 1 then "完成" else "未完成" end))
         coursestatus,
        (case when tab1.nofinishcount>0 then "未完成" else "完成" end) coursefinish,
        (case cc.jhly when "bmn" then "部门级计划内" when "bmw" then "部门级计划外" when "sj" then "院级" end) jhly,
        cc.bjmc,
        (case when cc.realteacher is not null and cc.realteacher!="" then "完成" else "未完成" end) classfinish,
        cc.bjlb,
        cc.addgroupname,
        uc.jhrs,
        cc.starttime,
        cc.endtime,
        cc.sfbfzs,
        cc.pxxsh,
        cc.pxxs,
        cc.sfkh,
        (case cc.sfkh when "是" then cc.khlx when "否" then "无" end) khlx,
        (case cc.sfkh when "是" then cc.khfs when "否" then "无" end) khfs,
        cc.teacher,
        cc.realteacher,
        cc.teacher_code,
        cc.teacher_department,
        cc.price,
        (case cc.status when -1 then "被驳回" when 0 then "未开班" when 1 then "已下发" when 2 then "已开班" when 3 then "待审核" end) ccstatus,
        pg.pgwjmc,
        (case pg.status when 0 then "未发布" when 1 then "已发布" end) pgstatus,
        ecc.name,
        ecc.startdate,
        ecc.enddate,
        ecc.state,
        fcc.title,
        fcc.uploadtime
        from et_train_pd_cc cc left join et_train_pd_cc_pg pg on cc.id=pg.ccid
				left join et_train_pd_cc_pgwjyxx yxx on pg.id = yxx.pgID
        left join (select kcmcid,count(*) nofinishcount from et_train_pd_cc where realteacher is null or realteacher="" group by kcmcid) tab1 on tab1.kcmcid=cc.kcmcid
        left join (select exam.name,exam.startdate,exam.enddate,exam.state,class.class_id as c_id from et_exam_exampaper_and_editexampaper exam
                    inner join et_exam_limitation_class class on exam.id = class.exam_id group by exam.id
                    union all
                    select exam.name,exam.startdate,exam.enddate,exam.state,course.course_id as c_id from et_exam_exampaper_and_editexampaper exam
                    inner join et_exam_limitation_course course on exam.id = course.exam_id group by exam.id
                    ) ecc on ecc.c_id = cc.id or ecc.c_id = cc.kcmcid
        left join (
        SELECT
	file.title,
	file.uploadtime,
	course.course_id AS f_id
FROM
	et_resource_file file
	inner JOIN (select a.file_id,b.course_id from et_resource_limitation a left join et_resource_limitation_course b on a.id=b.limitation_id GROUP BY b.course_id) course on course.file_id = file.id
	union all
SELECT
	file.title,
	file.uploadtime,
	class.class_id AS f_id
FROM
	et_resource_file file
	inner JOIN (select a.file_id,b.class_id from et_resource_limitation a left join et_resource_limitation_class b on a.id=b.limitation_id GROUP BY b.class_id) class on class.file_id = file.id
                    ) fcc on fcc.f_id = cc.id or fcc.f_id = cc.kcmcid
        left join (select cc.id,count(*) jhrs FROM et_train_pd_cc cc  left join et_train_pd_cc_user ccu on cc.id = ccu.ccid group by cc.id) uc on cc.id = uc.id
        where 1=1
        ${IF(len(searchkey)=0,""," and (kcmc like '%"+searchkey+"%' or bjmc like '%"+searchkey+"%' or cc.addgroupname like '%"+searchkey+"%' or cc.teacher like '%"+searchkey+"%' or cc.realteacher like '%"+searchkey+"%' or cc.teacher_code like '%"+searchkey+"%' or starttime like '%"+searchkey+"%' or endtime like '%"+searchkey+"%')")}
       order by starttime desc
        ) a
    ]]></Query>
</TableData>
</TableDataMap>
<Report class="com.fr.report.worksheet.WorkSheet" name="sheet1">
<ReportPageAttr>
<HR/>
<FR/>
<HC/>
<FC/>
</ReportPageAttr>
<ColumnPrivilegeControl/>
<RowPrivilegeControl/>
<RowHeight defaultValue="723900">
<![CDATA[1143000,723900,723900,723900,723900,723900,723900,723900,723900,723900,723900]]></RowHeight>
<ColumnWidth defaultValue="2743200">
<![CDATA[2743200,0,6972300,4724400,2743200,3581400,2819400,7886700,2743200,3848100,2743200,2743200,3733800,3619500,3619500,2743200,2743200,2743200,2743200,2743200,2743200,2743200,3276600,3200400,2743200,6743700,2743200,4076700,3733800,3467100,2743200,4381500,4800600,2743200]]></ColumnWidth>
<CellElementList>
<C c="0" r="0" s="0">
<O>
<![CDATA[序号]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="1" r="0" s="0">
<O>
<![CDATA[id]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="2" r="0" s="0">
<O>
<![CDATA[课程名称]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="3" r="0" s="0">
<O>
<![CDATA[部门]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="4" r="0" s="0">
<O>
<![CDATA[课程状态]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="5" r="0" s="0">
<O>
<![CDATA[补充课程信息]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="6" r="0" s="0">
<O>
<![CDATA[课程所属]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="7" r="0" s="0">
<O>
<![CDATA[班级名称]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="8" r="0" s="0">
<O>
<![CDATA[班级状态]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="9" r="0" s="0">
<O>
<![CDATA[补充班级信息]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="10" r="0" s="0">
<O>
<![CDATA[课程序列]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="11" r="0" s="0">
<O>
<![CDATA[参训人数]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="12" r="0" s="0">
<O>
<![CDATA[培训开始时间]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="13" r="0" s="0">
<O>
<![CDATA[培训结束时间]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="14" r="0" s="0">
<O>
<![CDATA[是否颁发证书]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="15" r="0" s="0">
<O>
<![CDATA[培训学时]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="16" r="0" s="0">
<O>
<![CDATA[培训形式]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="17" r="0" s="0">
<O>
<![CDATA[是否考核]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="18" r="0" s="0">
<O>
<![CDATA[考核类型]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="19" r="0" s="0">
<O>
<![CDATA[考核方式]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="20" r="0" s="0">
<O>
<![CDATA[计划授课人]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="21" r="0" s="0">
<O>
<![CDATA[实际授课人]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="22" r="0" s="1">
<O>
<![CDATA[授课人工资号]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="23" r="0" s="1">
<O>
<![CDATA[授课人单位]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="24" r="0" s="0">
<O>
<![CDATA[培训费用]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="25" r="0" s="0">
<O>
<![CDATA[评估问卷名称]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="26" r="0" s="0">
<O>
<![CDATA[评估状态]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="27" r="0" s="0">
<O>
<![CDATA[考试试卷名称]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="28" r="0" s="0">
<O>
<![CDATA[考试开始时间]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="29" r="0" s="0">
<O>
<![CDATA[考试结束时间]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="30" r="0" s="0">
<O>
<![CDATA[考试状态]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="31" r="0" s="0">
<O>
<![CDATA[培训资料名称]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="32" r="0" s="0">
<O>
<![CDATA[培训资料上传时间]]></O>
<PrivilegeControl/>
<Expand/>
</C>
<C c="0" r="1" s="0">
<O t="Formula" class="Formula">
<Attributes>
<![CDATA[=seq()]]></Attributes>
</O>
<PrivilegeControl/>
<Expand dir="0" leftParentDefault="false" left="B2"/>
</C>
<C c="1" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="id"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="2" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="kcmc"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="3" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="addgroupname"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="4" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="coursestatus"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="5" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="coursefinish"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="6" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="jhly"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="7" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="bjmc"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="8" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="ccstatus"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="9" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="classfinish"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="10" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="bjlb"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="11" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="jhrs"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="12" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="starttime"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="13" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="endtime"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="14" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="sfbfzs"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="15" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="pxxsh"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="16" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="pxxs"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="17" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="sfkh"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="18" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="khlx"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="19" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="khfs"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="20" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="teacher"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="21" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="realteacher"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="22" r="1" s="1">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="teacher_code"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="23" r="1" s="1">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="teacher_department"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="24" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="price"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="25" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="pgwjmc"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="26" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="pgstatus"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="27" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="name"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="28" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="startdate"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="29" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="enddate"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="30" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="state"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="31" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="title"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
<C c="32" r="1" s="2">
<O t="DSColumn">
<Attributes dsName="ds1" columnName="uploadtime"/>
<Complex/>
<RG class="com.fr.report.cell.cellattr.core.group.FunctionGrouper"/>
<Parameters/>
</O>
<PrivilegeControl/>
<Expand dir="0"/>
</C>
</CellElementList>
<ReportAttrSet>
<ReportSettings headerHeight="0" footerHeight="0">
<PaperSetting>
<PaperSize width="216000000" height="144000000"/>
<Margin top="986400" left="2743200" bottom="986400" right="2743200"/>
</PaperSetting>
</ReportSettings>
<Header reportPageType="0">
<Background name="NullBackground"/>
<LeftList/>
<CenterList/>
<RightList/>
</Header>
<Footer reportPageType="0">
<Background name="NullBackground"/>
<LeftList/>
<CenterList/>
<RightList/>
</Footer>
</ReportAttrSet>
<PrivilegeControl/>
</Report>
<ReportParameterAttr>
<Attributes showWindow="true" delayPlaying="true" windowPosition="1" align="0" useParamsTemplate="false"/>
<PWTitle>
<![CDATA[参数]]></PWTitle>
<Parameter>
<Attributes name="searchkey"/>
<O>
<![CDATA[]]></O>
</Parameter>
</ReportParameterAttr>
<StyleList>
<Style horizontal_alignment="0" imageLayout="1">
<FRFont name="SimSun" style="0" size="72"/>
<Background name="NullBackground"/>
<Border>
<Top style="1"/>
<Bottom style="1"/>
<Left style="1"/>
<Right style="1"/>
</Border>
</Style>
<Style imageLayout="1">
<FRFont name="SimSun" style="0" size="72"/>
<Background name="NullBackground"/>
<Border>
<Top style="1"/>
<Bottom style="1"/>
<Left style="1"/>
<Right style="1"/>
</Border>
</Style>
<Style horizontal_alignment="2" imageLayout="1">
<FRFont name="SimSun" style="0" size="72"/>
<Background name="NullBackground"/>
<Border>
<Top style="1"/>
<Bottom style="1"/>
<Left style="1"/>
<Right style="1"/>
</Border>
</Style>
</StyleList>
<DesignerVersion DesignerVersion="JAA"/>
<PreviewType PreviewType="0"/>
<TemplateID/>
<TemplateIdAttMark class="com.fr.base.iofileattr.TemplateIdAttrMark">
<TemplateIdAttMark TemplateId="9ca7f9de-ef5f-42ce-a335-d635237533af"/>
</TemplateIdAttMark>
</WorkBook>