u010564553 于 2013.05.24 13:04 提问

6个回答

qq_39612314   2017.08.04 14:13

1.--create function 数字转日期

create or replace function FN_NUMBER_TO_DATE(timeInMillis in number) return date
is

Result date;

begin

Result := to_date('1970-01-01 00:00:00', 'yyyy-MM-dd hh24:mi:ss') +

timeInMillis / (24 * 60 * 60 * 1000);

return(Result);

end FN_NUMBER_TO_DATE;

11.--create function 取到几天以前的日期

create or replace function FN_DAYS_AGO(dest in date, days in number) return date is

Result date;

timeInMillis number;

begin

timeInMillis := FN_DATE_TO_NUMBER(dest) - days*24*60*60*1000;

Result := FN_NUMBER_TO_DATE(timeInMillis);

return(Result);

end FN_DAYS_AGO;

22.--create function 日期转数字

create or replace function FN_DATE_TO_NUMBER(inDate in date) return number is

Result number;

begin

Result := (inDate-to_date('1970-01-01', 'yyyy-MM-dd')) * 24*60*60*1000;

return(Result);

end FN_DATE_TO_NUMBER;

log4j.rootLogger=DEBUG, stdout, logfile
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=[%-5p]%d{yyyy-MM-ddHH:mm:ss,SSS} method:%l%n%m%n

# （每天产生一个日志文件）

log4j.appender.logfile=org.apache.log4j.DailyRollingFileAppender

# 设定日志文件的滚动周期 （每天）

log4j.appender.logfile.DateParent=yyyyMMdd

# 设定日志消息最终输入的文件地址

log4j.appender.logfile.File=D://ex-hujintong001\logs\myweb.log

# 设定日志信息的输出格式--（可以灵活的指定布局模式）

log4j.appender.logfile.layout=org.apache.log4j.PatternLayout

# 设定显示日志信息的格式

log4j.appender.logfile.layout.ConversionPattern=%d %p [%c] - %m%n

--视图创建
create or replace view tmsdata.tms_teacher_course_desc_vw as
--视图授权
create public synonym tms_student_class_vw for tmsdata.tms_student_class_vw;
grant select on tmsdata.tms_student_class_vw to
tmsopr,tmscde,tmsjob,R_TMSDATA_DML,R_FRETSDATA_DML;
--视图添加注释
comment on column tmsdata.tms_student_class_vw.class_name is '培训班名称';

--创建备份表
--execute dmlbak.create_bak_table_pkg.create_bak_table
('edstmsop','tmsdata','tms_reimburse_detail','tms_reimburse_detail_b');
execute dmlbak.create_bak_table_pkg.create_bak_table
('devsup01','tmsdata','tms_reimburse_detail','tms_reimburse_detail_b');
--验证是否创建成功
select 1 from dmlbak.tms_reimburse_detail_b;

--锁数据
select * from tms_reimburse_detail t where t.class_id = '00000000000001921958'
for update;
--1.备份数据
insert into dmlbak.tms_reimburse_detail_b
select t.rowid bak_rowid,t.*,null
from tms_reimburse_detail t where t.class_id = '00000000000001921958' ;
--2.修改数据
update tms_reimburse_detail t
set t.apply_status = '020001'
where t.series_id = '02'
and t.class_id = '00000000000001921958'
and t.apply_status = '020005'
and t.rowid in (select bak_rowid
 from dmlbak.tms_reimburse_detail_b
where date_dml_flag is null);
--3.加时间戳
update dmlbak.tms_reimburse_detail_b
set date_dml_flag = systimestamp
where date_dml_flag is null;

execute dmlbak.create_bak_table_pkg.pc_bktb_rename
('tms_reimburse_detail_b','tmsdata','tms_reimburse_detail','Q_NP282382');

--回滚脚本模板-DDL
--删表
declare
v_count number;
begin
select count(*)
into v_count
from user_all_tables t
where t.table_name = upper('tms_mln_person_course_match');
if v_count > 0 then
execute immediate 'drop table tms_mln_person_course_match';
delete from tms_tr_switch t where t.trigger_name like upper('tms_mln_person_course_match_%');
commit;
end if;
exception
when others then
null;
end;
/

--删字段
declare
v_count number;
begin
select count(*)
into v_count
from user_tab_columns t
where t.TABLE_NAME = upper('tms_archive_type')
if v_count > 0 then
execute immediate 'alter table tmsdata.tms_archive_type drop column
end if;
exception
when others then
null;
end;
/

--改字段名
declare
v_count number;
begin
select count(*)
into v_count
from user_tab_columns t
where t.TABLE_NAME = upper('tms_mln_person_course_match')
and t.COLUMN_NAME = upper('last_study_date');
if v_count > 0 then
execute immediate 'alter table tmsdata.tms_mln_person_course_match rename
column last_study_date to completed_date';
end if;
exception
when others then
null;
end;
/

--删序列
declare
v_count number;
begin
select count(*)
into v_count
from user_sequences t
where t.SEQUENCE_NAME = upper('tms_seq_video_room_order_id');
if v_count > 0 then
execute immediate 'drop sequence tmsdata.tms_seq_video_room_order_id';
execute immediate 'drop public synonym tms_seq_video_room_order_id';
end if;
exception
when others then
null;
end;
/

--删视图
declare
v_count number;
begin
select count(*)
into v_count
from user_views t
where t.VIEW_NAME = upper('tms_student_training_result_vw');
if v_count > 0 then
execute immediate 'drop view tmsdata.tms_student_training_result_vw';
execute immediate 'drop public synonym tms_student_training_result_vw';
end if;
exception
when others then
null;
end;
/

--删公共同义词
declare
v_count number;
begin
select count(*)
into v_count
from dba_synonyms t
where t.SYNONYM_NAME = upper('tms_interface_sync_monitor');
if v_count > 0 then
execute immediate 'drop public synonym tms_interface_sync_monitor';
end if;
exception
when others then
null;
end;
/
--drop public synonym
declare

begin
execute immediate 'drop public synonym tms_class_scan_code_info';
exception
when others then
null;
end;
/

--删字段
declare
v_count number;
begin
select count(*)
into v_count
from user_tab_columns t
where t.TABLE_NAME = upper('tms_archive_type')
if v_count > 0 then
execute immediate 'alter table tmsdata.tms_archive_type drop column
end if;
exception
when others then
null;
end;
/

declare
-- Local variables here
CURSOR C_JOB IS
SELECT * FROM USER_JOBS T WHERE T.WHAT LIKE '%tms_job_15;' and t.BROKEN='Y';
begin
-- Test statements here
for rec in c_job loop
dbms_job.run(rec.job);
end loop;
end;
/
alter table TMSDATA.TMSLIVE_TEST_PAPER_INFO add true_or_false_num number(3);

qq_39612314   2017.08.04 14:27

\$("#parentSelectCheckTable tr:gt(0)").each(function(i, obj) {
flag2 = true;
var courseId = "";
var dispatchType = "";
var dispatchTypeDays = "";
var dispatchTypeHours = "";
var remindType = "";
var remindWeekStartDay = "";
var remindWeekEndDay = "";
var remindDayHour = "";
var remindLastDays = "";

var param = "";
courseId = \$(obj).find("input[type=hidden][name=checkCourseCheckbox]").attr("courseId");
dispatchTypeDays = \$(obj).find("input[type=text][name=dispatchTypeDays]").val();
dispatchTypeHours = \$(obj).find("select[name=dispatchTypeHours]").val();

if(remindType == "0"){
remindDayHour = \$(obj).find("select[name=remindDayHourSelect]").val();
remindLastDays = \$(obj).find("input[type=text][name=remindDaysDay]").val();

param = "{'classType':'" + classType + "','courseId':'" + courseId + "','dispatchType':'" + dispatchType + "','dispatchTypeDays':'" + dispatchTypeDays
+ "','dispatchTypeHours':'" + dispatchTypeHours + "','remindType':'" + remindType + "','remindDayHour':'" + remindDayHour+ "','remindLastDays':'" + remindLastDays + "'}";

dataParamArray[dataParamArray.length] = param;
}else{
remindDayHour = \$(obj).find("select[name=remindDayHourSelectWeek]").val();
remindWeekStartDay = \$(obj).find("select[name=remindWeekStartDay]").val();
remindWeekEndDay = \$(obj).find("select[name=remindWeekEndDay]").val();
if(remindWeekEndDay < remindWeekStartDay){
flag1 = true;
}

remindDayHour = \$(obj).find("select[name=remindDayHourSelectWeek]").val();
remindLastDays = \$(obj).find("input[type=text][name=remindDaysWeek]").val()*7;
param = "{'classType':'" + classType + "','courseId':'" + courseId + "','dispatchType':'" + dispatchType + "','dispatchTypeDays':'" + dispatchTypeDays
+ "','dispatchTypeHours':'" + dispatchTypeHours + "','remindType':'" + remindType + "','remindWeekStartDay':'" + remindWeekStartDay
+ "','remindWeekEndDay':'" + remindWeekEndDay+ "','remindDayHour':'" + remindDayHour+ "','remindLastDays':'" + remindLastDays+ "'}";
dataParamArray[dataParamArray.length] = param;
}

});
dataParam += "[" + dataParamArray.join(",") + "]";
\$("#informationInfos").val(dataParam);

function selectPapers() {
classType = \$("#classType").val();
paperType = \$("#paperType").val();
selectedPapers = {};
\$("#parentSelectCheckTable tr:gt(0)").each(function(i, obj) {
selectedPapers[(\$(obj).find("input[type=hidden][name=checkPaperCheckbox]").attr("paperNo"))]
= (\$(obj).find("input[type=hidden][name=checkPaperCheckbox]").attr("paperName")+"|"+\$(obj).find("input[type=hidden][name=checkPaperCheckbox]").attr("testTime"));

});
var url = "/tmslive.paper.search.screen?&temp=" + new Date().getTime();
var height = window.screen.availHeight * 0.85;
var width = window.screen.availWidth * 0.95;
var top = window.screen.availHeight * 0.06 / 2;
var left = window.screen.availWidth * 0.04 / 2;
window.open(url, '_blank', 'top=' + top + ',left=' + left + ',height=' + height + ',width='
+ width
}

\$.ajax({
type : "POST",
url : "/tmslive.test.paper.modify.info.do",
dataType: "json",
contentType : "application/x-www-form-urlencoded;charset=utf-8",
data : paramData,
async : false,
success : function(data) {
closeWindow();
}
});

function flashQuestion(type) {
\$("#parentSelectCheckTable"+ type +" tr:gt(0)").each(function(index, obj) {
\$(obj).remove();
});

if(type == 0){
parentQuestionNum0 = 0;
}
if(type == 1){
parentQuestionNum1 = 0;
}
if(type == 2){
parentQuestionNum2 = 0;
}
for ( var questionNo in selectedQuestion) {
var score = selectedQuestion[questionNo].split("|")[1];
var questionInfo = selectedQuestion[questionNo].split("|")[0];
var checkboxTxt = ""+ questionInfo + "";
var objTxt = checkboxTxt;
var tdHtml = "

" + objTxt + "";
var isExists = false;
\$("#parentSelectCheckTable"+ type +" td:gt(0)").each(function(index, obj) {
if (questionNo == \$(obj).find("input[name=questionNo]").val()) {
isExists = true;
}
});
if(!isExists){
if(type == 0){
if (parentQuestionNum0 == 0 || parentQuestionNum0 % 3 == 0 ) {
var trHtml = "" + tdHtml + "";
\$("#parentSelectCheckTable"+ type +" tr").last().after(trHtml);
parentQuestionNum0 = parentQuestionNum0 + 1;
} else {
\$("#parentSelectCheckTable"+ type +" tr").last().append(tdHtml);
parentQuestionNum0 = parentQuestionNum0 + 1;
}
``````            }
if(type == 1){
if (parentQuestionNum1 == 0 || parentQuestionNum1 % 3 == 0 ) {
var trHtml = "<tr>" + tdHtml + "</tr>";
\$("#parentSelectCheckTable"+ type +" tr").last().after(trHtml);
parentQuestionNum1 = parentQuestionNum1 + 1;
} else {
\$("#parentSelectCheckTable"+ type +" tr").last().append(tdHtml);
parentQuestionNum1 = parentQuestionNum1 + 1;
}

}
if(type == 2){
if (parentQuestionNum2 == 0 || parentQuestionNum2 % 3 == 0 ) {
var trHtml = "<tr>" + tdHtml + "</tr>";
\$("#parentSelectCheckTable"+ type +" tr").last().after(trHtml);
parentQuestionNum2 = parentQuestionNum2 + 1;
} else {
\$("#parentSelectCheckTable"+ type +" tr").last().append(tdHtml);
parentQuestionNum2 = parentQuestionNum2 + 1;
}

}
}
}
\$("#questionTable").find("input[name=singleChoiceNum]").val(parentQuestionNum0);
\$("#questionTable").find("input[name=multipleChoiceNum]").val(parentQuestionNum1);
\$("#questionTable").find("input[name=trueOrFalseNum]").val(parentQuestionNum2);
scores = 0;
\$("#paperModify").find("input[name=questionNo]:checked").each(function(index, o) {
scores += parseInt(\$(o).attr("score"));
});
\$("#testScores").val(scores);
``````

}

function selectedQuestionList(type) {
questionBankNo = \$("#questionBankNo").val();
questionType = type;
selectedQuestion = {};
\$("#parentSelectCheckTable"+ type +" td:gt(0)").each(function(i, obj) {
if (\$(obj).find("input[name=questionNo]").attr("checked")) {
selectedQuestion[(\$(obj).find("input[name=questionNo]").val())]
= (\$(obj).find("input[name=questionNo]").next().text()+"|"+\$(obj).find("input[name=questionNo]").attr("score"));
}
})
var url = "/tmslive.test.paper.search.question.screen?&temp=" + new Date().getTime();
var height = window.screen.availHeight * 0.7;
var width = window.screen.availWidth * 0.9;
var top = window.screen.availHeight * 0.06 / 2;
var left = window.screen.availWidth * 0.04 / 2;
window.open(url, '_blank', 'top=' + top + ',left=' + left + ',height=' + height + ',width=' + width
}

/**
* 增加同步监控信息
* @return
*/
private InterfaceSyncMonitorDTO addMonitor(String interfaceName, String content) {
InterfaceSyncMonitorDTO interfaceSyncMonitor = new InterfaceSyncMonitorDTO(interfaceName,
"SALES_RECRUIT", "TMSV2-CORE", content);
return interfaceSyncMonitor;
}

``````/**
* 更新同步结果至监控表
* @param string
*/
private void updateMonitor(InterfaceSyncMonitorDTO interfaceSyncMonitor, String syncResult,
String resultMsg) {
interfaceSyncMonitor.setSyncResult(syncResult);
interfaceSyncMonitor.setResultMsg(resultMsg);
InterfaceSyncMonitor.updateMonitor(interfaceSyncMonitor);
}
``````

package com.paic.tms.common.api;

import com.paic.pafa.app.lwc.core.util.DevLog;
import com.paic.tms.common.dto.InterfaceSyncMonitorDTO;
import com.paic.tms.common.integration.CommonDAO;
import com.paic.tms.common.util.BeanNames;
import com.paic.tms.common.util.CommonIbatisSqlId;
import com.paic.tms.common.util.ContextUtil;
import com.paic.tms.common.util.RandomNumGenerator;

/**

• 接口同步监控
• @author EX-SHIMINGCHUAN001 *
• 2016年6月14日
*/
public class InterfaceSyncMonitor {

private InterfaceSyncMonitor() {
}

private static CommonDAO commonDAO;

static {
if (commonDAO == null) {
commonDAO = (CommonDAO) ContextUtil
.getContext("biz-context.xml,biz-context-common.xml").getBean(
BeanNames.TMS_COMMON_DAO);
}
}

/**

• 增加同步监控信息
• @param interfaceSyncMonitor 同步监控信息
• @return interfaceSyncMonitor 同步监控信息，添加了监控记录的主键ID，后面更新结果时会用到！！
• @author EX-SHIMINGCHUAN001
• 2017年4月7日 */ public static InterfaceSyncMonitorDTO addMonitor(InterfaceSyncMonitorDTO interfaceSyncMonitor) { String primaryKeyId = String.valueOf(RandomNumGenerator.getID20()); interfaceSyncMonitor.setPrimaryKeyId(primaryKeyId); try { commonDAO.addSomeInfoBySqlId(CommonIbatisSqlId.ADD_INTERFACE_SYNC_MONITOR, interfaceSyncMonitor); } catch (PafaDAOException e) { DevLog.error("插入接口调用信息异常！", e); } return interfaceSyncMonitor; }

/**

• 更新同步结果至监控表
• @param interfaceSyncMonitor 同步结果信息
• @author EX-SHIMINGCHUAN001
• 2016年6月17日 */ public static void updateMonitor(InterfaceSyncMonitorDTO interfaceSyncMonitor) { try { commonDAO.modifySomeInfoBySqlId(CommonIbatisSqlId.UPDATE_INTERFACE_SYNC_MONITOR, interfaceSyncMonitor); } catch (PafaDAOException e) { DevLog.error("更新接口调用结果异常！接口记录ID：" + interfaceSyncMonitor.getPrimaryKeyId(), e); } } }

package com.paic.tms.common.dto;

/**

• 接口同步监控DTO
• @author EX-SHIMINGCHUAN001 *
• 2016年6月14日
*/
public class InterfaceSyncMonitorDTO extends TmsBaseDTO {

private static final long serialVersionUID = 5460173750473004634L;

// 接口名
private String interfaceName;
// 源系统名
private String source;
// 目标系统名
private String target;
// 同步报文内容
private String content;
// 线程信息（针对多线程的情况）
// 同步结果:S-成功，F-失败，E-异常
private String syncResult;
// 同步结果信息
private String resultMsg;
//主键ID
private String primaryKeyId;

public InterfaceSyncMonitorDTO() {
}

/**

• 添加监控时使用
• @param interfaceName 接口名
• @param source 源系统名
• @param target 目标系统名
• @param content 同步报文内容 */ //NOSONAR public InterfaceSyncMonitorDTO(String interfaceName, String source, String target, String content) { this.interfaceName = interfaceName; this.source = source; this.target = target; this.content = content; }

/**

• 添加监控时使用
• @param interfaceName 接口名
• @param source 源系统名
• @param target 目标系统名
• @param content 同步报文内容
• @param threadInfo 线程信息（针对多线程的情况） */ //NOSONAR public InterfaceSyncMonitorDTO(String interfaceName, String source, String target, String content, String threadInfo) { this.interfaceName = interfaceName; this.source = source; this.target = target; this.content = content; this.threadInfo = threadInfo; }

/**

• 更新同步结果时使用
• @param interfaceName 接口名
• @param syncResult 同步结果:S-成功，F-失败，E-异常
• @param resultMsg 同步结果信息 */ public InterfaceSyncMonitorDTO(String interfaceName, String syncResult, String resultMsg){ this.interfaceName = interfaceName; this.syncResult = syncResult; this.resultMsg = resultMsg; }

public String getInterfaceName() {
return interfaceName;
}

public void setInterfaceName(String interfaceName) {
this.interfaceName = interfaceName;
}

public String getSource() {
return source;
}

public void setSource(String source) {
this.source = source;
}

public String getTarget() {
return target;
}

public void setTarget(String target) {
this.target = target;
}

public String getContent() {
return content;
}

public void setContent(String content) {
this.content = content;
}

}

}

public String getSyncResult() {
return syncResult;
}

public void setSyncResult(String syncResult) {
this.syncResult = syncResult;
}

public String getResultMsg() {
return resultMsg;
}

public void setResultMsg(String resultMsg) {
this.resultMsg = resultMsg;
}

public String getPrimaryKeyId() {
return primaryKeyId;
}

public void setPrimaryKeyId(String primaryKeyId) {
this.primaryKeyId = primaryKeyId;
}

}

private List getPaperCaseRel(PaperDispathDTO paperDispathDTO) {
List paperDispathList = new ArrayList();

String informationInfos = paperDispathDTO.getInformationInfos();

if (StringUtils.isNotEmpty(informationInfos)) {
JSONArray array = JSONArray.parseArray(informationInfos);
Iterator it =array.iterator();
while(it.hasNext()){
PaperDispathDTO dispathDTO = new PaperDispathDTO();
dispathDTO.setCreatedBy(paperDispathDTO.getCreatedBy());
JSONObject obj = JSONObject.parseObject(it.next().toString());
generatePaperList(obj,dispathDTO,paperDispathList);

}
}
return paperDispathList;
}

qq_39612314   2017.08.04 15:09

function func_get_base_point(p_invite_id in varchar2,
p_invite_type in varchar2) return number is
--基础积分
v_base_point number(4) := 0;
v_inviter_2 varchar2(10);
v_inviter_15 varchar2(10);
v_teacher_15 varchar2(10);
v_teacher_2 varchar2(10);
v_invite_id varchar2(20);
v_invite_type varchar2(10);
--查询邀约人的机构信息
cursor cur_inviter_info(p_in_invite_id varchar2) is
select t1.branch_id branchId, t2.branch_level branchLevel
from tms_merit_teacher_invite t1, tms_branch_tree_info t2
WHERE t1.invite_id = p_in_invite_id
and t1.branch_id = t2.branch_id
and t2.series_id = '02';
p_cur_inviter_info cur_inviter_info%rowtype;

--查询被邀约讲师的机构信息
cursor cur_teacher_info(p_in_invite_id varchar2) is
select t3.branch_id branchId, t2.branch_level branchLevel
from tms_merit_teacher_invite t1,
tms_branch_tree_info t2,
tms_merit_teacher_information t3
WHERE t1.invite_id = p_in_invite_id
and t3.branch_id = t2.branch_id
and t1.merit_teacher_id = t3.merit_teacher_id
and t2.series_id = '02';
p_cur_teacher_info cur_teacher_info%rowtype;
begin
v_invite_id := p_invite_id;
v_invite_type := p_invite_type;

``````  if v_invite_id is null then
return v_base_point;
end if;
for p_cur_inviter_info in cur_inviter_info(v_invite_id) loop
if v_invite_type = '0' then
if p_cur_inviter_info.branchLevel in ('1', '1.5','0') then
v_base_point := 30;
elsif p_cur_inviter_info.branchLevel >= 2 then
v_inviter_2 := func_get_level_branch(p_cur_inviter_info.branchId,
'2');
for p_cur_teacher_info in cur_teacher_info(v_invite_id) loop
if p_cur_teacher_info.branchLevel >= 2 then
v_teacher_2 := func_get_level_branch(p_cur_teacher_info.branchId,'2');
if v_inviter_2 = v_teacher_2 then
v_base_point := 10;
else
v_base_point := 20;
end if;
elsif p_cur_teacher_info.branchLevel in ('1','0') then
v_base_point := 10;
else
v_inviter_15 := func_get_level_branch(p_cur_inviter_info.branchId,
'1.5');
if p_cur_teacher_info.branchId = v_inviter_15 then
v_base_point := 10;
else
v_base_point := 20;
end if;
end if;
end loop;
end if;
else
if p_cur_inviter_info.branchLevel in ('1','0') then
v_base_point := 40;
elsif p_cur_inviter_info.branchLevel in ('1.5') then
v_base_point := 30;
else
v_base_point := 20;
end if;
end if;
end loop;
return v_base_point;
exception
when others then
v_base_point := 0;
return v_base_point;
end func_get_base_point;
``````

function func_get_level_branch(p_branch_id in varchar2,p_branch_level in varchar2) return number is
v_result varchar2(10) := 0;
begin
SELECT branch_id into v_result FROM
(SELECT t.branch_id
FROM tms_branch_tree_info t
where t.branch_level = p_branch_level
and t.is_valid = 'Y'
and t.branch_id = p_branch_id
connect by prior t.parent_branch_id = t.branch_id
and t.series_id = '02')
WHERE rownum = 1;

return v_result;
exception
when others then
v_result := null;
return v_result;
end func_get_level_branch;

procedure proc_produce_paper_student is
--查询出需要进行考试配送的培训班类型
cursor c_class_type is
select distinct t.class_type classType
from tmslive_test_paper_dis t;
--根据class_type 查询出所属的未结训的培训班中的学员
cursor c_student_info(v_class_type varchar2) is
SELECT t.studentNo,
t.paperNo,
t.classId,
t.dateStart,
t.dateEnd,
t.paperName,
t.testNum,
t.paperType,
t.dispatchTime,
t.paperDispatchId
FROM (SELECT aa.student_no studentNo,
dd.paper_no paperNo,
b.class_id classId,
b.date_start dateStart,
b.date_end dateEnd,
ff.paper_name paperName,
ff.test_num testNum,
dd.paper_type paperType,
(SELECT trunc(tt.date_start)
FROM TMSLIVE_CLASS_COURSE_INFO TT
WHERE TT.CLASS_ID = b.class_id
AND TRUNC(DATE_START) IN
(SELECT CASE
WHEN DECODE(DENSE_RANK() OVER(ORDER BY TRUNC(M.DATE_START)),DD.DISPATCH_TYPE_DAYS,1,0) = 1 THEN
TRUNC(M.DATE_START)
END FUTUREDATE
FROM TMSLIVE_CLASS_COURSE_INFO M
WHERE M.CLASS_ID = TT.CLASS_ID)
AND ROWNUM = 1) + dd.dispatch_type_hours / 24+dd.dispatch_type_mins/60/24 dispatchTime,
(SELECT trunc(tt.date_start)
FROM TMSLIVE_CLASS_COURSE_INFO TT
WHERE TT.CLASS_ID = b.class_id
AND TRUNC(DATE_START) IN
(SELECT CASE
WHEN DECODE(DENSE_RANK() OVER(ORDER BY TRUNC(M.DATE_START)),DD.DISPATCH_TYPE_DAYS,1,0) = 1 THEN
TRUNC(M.DATE_START)
END FUTUREDATE
FROM TMSLIVE_CLASS_COURSE_INFO M
WHERE M.CLASS_ID = TT.CLASS_ID)
dd.id_test_paper_dis paperDispatchId
FROM tmslive_class_student_info aa,
tmslive_class_info b,
tmslive_test_paper_dis dd,
tmslive_test_paper_info ff
WHERE b.class_type = v_class_type
and b.class_id = aa.class_id
and dd.class_type = b.class_type
and ff.paper_no = dd.paper_no
and b.class_status in ('02001', '02002')) t
WHERE t.dispatchTime <= sysdate+5/60/24
and t.dispatchTime is not null;
--根据class_type 实时查询出人卷匹配表中某个classType下不包含按照配送规则生成的人卷信息的数据（删掉）

cursor c_d_student_info(v_class_type varchar2) is
SELECT t.stu_no studentNo, t.paper_no paperNo, t.class_id classId, t.PAPER_DISPATCH_ID paperDispatchId
FROM tmslive_person_paper_detail t, tmslive_class_info b
WHERE b.class_id = t.class_id
and b.class_type = v_class_type
and not exists
(SELECT tt.studentNo, tt.paperNo, tt.classId, tt.paperDispatchId
FROM (SELECT aa.student_no studentNo,
dd.paper_no paperNo,
b.class_id classId,
(SELECT trunc(tt.date_start)
FROM TMSLIVE_CLASS_COURSE_INFO TT
WHERE TT.CLASS_ID = b.class_id
AND TRUNC(DATE_START) IN
(SELECT CASE
WHEN DECODE(DENSE_RANK() OVER(ORDER BY TRUNC(M.DATE_START)),DD.DISPATCH_TYPE_DAYS,1,0) = 1 THEN
TRUNC(M.DATE_START)
END FUTUREDATE
FROM TMSLIVE_CLASS_COURSE_INFO M
WHERE M.CLASS_ID = TT.CLASS_ID)
AND ROWNUM = 1) + dd.dispatch_type_hours / 24+dd.dispatch_type_mins/60/24 dispatchTime,
dd.id_test_paper_dis paperDispatchId
FROM tmslive_class_student_info aa,
tmslive_class_info b,
tmslive_test_paper_dis dd
WHERE b.class_type = v_class_type
and b.class_id = aa.class_id

and dd.class_type = b.class_type
and b.class_status in ('02001', '02002')) tt
WHERE tt.dispatchTime <= sysdate+5/60/24
and tt.dispatchTime is not null
and tt.studentNo = t.stu_no
and tt.paperNo = t.paper_no
and tt.classId = t.class_id
and tt.paperDispatchId = t.PAPER_DISPATCH_ID);

v_exam_start_date date;
v_exam_end_date date;
begin
for r_c_class_type in c_class_type loop
for r_c_d_student_info in c_d_student_info(r_c_class_type.classType) loop
delete from tmslive_person_paper_detail t
WHERE t.paper_no = r_c_d_student_info.paperNo
and t.stu_no = r_c_d_student_info.studentNo
and t.PAPER_DISPATCH_ID = r_c_d_student_info.paperDispatchId
and t.class_id = r_c_d_student_info.classId;
commit;
end loop;
end loop;

``````for rr_c_class_type in c_class_type loop
for rr_c_student_info in c_student_info(rr_c_class_type.classType) loop
if rr_c_student_info.dispatchTime >= rr_c_student_info.dateStart  then
v_exam_start_date  := rr_c_student_info.dispatchTime;
end if;
if rr_c_student_info.dispatchTime < rr_c_student_info.dateStart  then
v_exam_start_date  := rr_c_student_info.dateStart;
end if;
v_exam_end_date  := rr_c_student_info.dateEnd+1;
end if;
end if;
merge into tmslive_person_paper_detail tppd
USING DUAL
ON (tppd.stu_no = rr_c_student_info.studentNo and tppd.class_id = rr_c_student_info.classId
and tppd.paper_no = rr_c_student_info.paperNo and tppd.PAPER_DISPATCH_ID = rr_c_student_info.paperDispatchId)
When matched then
update set tppd.start_date = rr_c_student_info.dispatchTime,
tppd.exam_start_date = v_exam_start_date,
tppd.exam_end_date = v_exam_end_date
When not matched then
insert
(stu_no,
paper_no,
paper_name,
class_id,
start_date,
test_num,
is_completed,
exam_start_date,
exam_end_date,
PAPER_DISPATCH_ID,
test_paper_type)
values
(rr_c_student_info.studentNo,
rr_c_student_info.paperNo,
rr_c_student_info.paperName,
rr_c_student_info.classId,
rr_c_student_info.dispatchTime,
rr_c_student_info.testNum,
'0',
v_exam_start_date,
v_exam_end_date,
rr_c_student_info.paperDispatchId,
rr_c_student_info.paperType);
commit;
end loop;
end loop;
``````

exception
when others then
rollback;
end proc_produce_paper_student;

procedure proc_sync_student_feedback is
begin
merge into tms_student_feedback feedback_info
using (select distinct CLASS_ID,
EMPNO,
REVIEW_TYPE,
REVIEW_ID,
REVIEW_NAME
from SALES_ETRAIN_CLASS_REVIEW
where DATE_UPDATED > sysdate - 11/60/24) class_info
on (feedback_info.CLASS_ID = class_info.CLASS_ID and feedback_info.student_no = class_info.EMPNO)
when not matched then
insert
(SERIES_ID, CLASS_ID, STUDENT_NO, FAVORITE_TYPE, FAVORITE_ID, FAVORITE_NAME)
values
('02', class_info.class_id, class_info.EMPNO, class_info.REVIEW_TYPE, class_info.REVIEW_ID, class_info.REVIEW_NAME);
commit;
end proc_sync_student_feedback;

function func_get_sequence(p_seq_name varchar2) return varchar2 is
v_sql varchar2(4000) ;
v_seq varchar2(20) ;
begin
v_seq := null ;

``````  v_sql := 'select '||p_seq_name||'.nextval from dual' ;
execute immediate(v_sql) into v_seq ;

return v_seq ;
``````

end ;

`````` --******************************************************************************
--* 程序说明: 通过指定分割符分割字符串
--* 输入参数: p_source_str被分割字符串
--*           p_split_char分割符
--*
--* 输出参数: str_array 字符串数组
--* 修改历史:
--*   日期       姓名             修改原因
--*   2012-08-13 ex-helichao001   新建
--******************************************************************************
``````

function string_to_spilt(p_source_str varchar2,
p_split_char varchar2) return tms_pub_sys_package.str_array is
--存放分割后的字符数组
result_array tms_pub_sys_package.str_array;
--临时存放字符串
v_temp_str varchar2(4000);

``````--分割符
v_split_char varchar2(4000);

--截取字符串位置索引
v_split_index integer := 0;

--临时字符串长度
v_temp_length integer := 0;

--存放截取后需要存储到返回数组值
v_sub_temp varchar2(4000);
``````

begin

``````v_temp_str    := p_source_str;
v_split_char  := p_split_char;
v_temp_length := length(v_temp_str);

if v_temp_length <= 0 then
return result_array;
end if;

loop
--获取第一次出现分割符下标位置
v_split_index := instr(v_temp_str, v_split_char,1,1);

--如果分割下标大于0，截取字符串。否则直接赋值返回
if v_split_index > 0 then
v_sub_temp := substr(v_temp_str, 1, v_split_index - 1);
if v_sub_temp!= v_split_char then
result_array(result_array.count) := v_sub_temp;
end if;
else
if v_temp_str != v_split_char then
result_array(result_array.count) := v_temp_str;
end if;
v_split_index := 0;
end if;
exit when v_split_index = 0;
v_temp_str    := substr(v_temp_str, v_split_index + 1);
v_split_index := instr(v_temp_str, v_split_char);
end loop;

return result_array;
``````

end string_to_spilt;

qq_39612314   2017.08.04 15:11

<?xml version="1.0" encoding="GB2312" ?>
<!DOCTYPE beans PUBLIC "-//LWC//DTD BEAN//EN" "lwc-beans.dtd">

<!-- 报表EXECL导出配置 begin-->

reportExport

<!-- 报表EXECL导出配置 end-->

``````<!--主管辅导统计报表 -->
<bean name="/report.charge.intervention.statistics.screen"
class="com.paic.tms.common.web.controller.TmsParameterizableViewController">
<property name="viewName">
<value>life.report.charge.intervention.statistics.view</value>
</property>
</bean>
``````

package com.paic.tms.report.web.controller;

import java.io.PrintWriter;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;

import com.paic.pafa.app.dto.ServiceRequest;
import com.paic.pafa.app.dto.ServiceResponse;
import com.paic.pafa.app.web.exception.PafaWebException;
import com.paic.pafa.app.web.servlet.ModelAndView;
import com.paic.tms.common.exception.TmsAppErrorMessageBaseException;
import com.paic.tms.common.web.controller.TmsMultiActionController;
import com.paic.tms.common.web.util.WebContextNames;
import com.paic.tms.report.util.ReportExportKeyName;
import com.paic.tms.report.web.util.ReportExportServiceRequestID;

/**

• 报表导出
• @author EX-YEYIJIE001
*/
public class ReportExportController extends TmsMultiActionController {

public ModelAndView reportExport(HttpServletRequest request,
HttpServletResponse response) throws Exception {

``````ServiceRequest serviceRequest = getServiceRequest(request);
serviceRequest.setRequestedServiceID(ReportExportServiceRequestID.REPORT_EXPORT_SERVICE_REQUEST_ID);
Map param = bindMap(request);
serviceRequest.setCurrentRequestObject(param);
ServiceResponse serviceResponse=null;
try{
serviceResponse =  dispatchService(serviceRequest, WebContextNames.PAFA_AC);
}catch(PafaWebException e){
if(null!=ex){
response.setContentType("text/html; charset=GBK");
PrintWriter out = response.getWriter();
out.println("<script>\n");
out.println("</script>\n");
return null;
}
throw new PafaWebException("ReportExportController is error",e);
}
Map model = serviceResponse.getModel();
List list = (List)model.get(ReportExportKeyName.REPORT_EXPORT_RESULT);

String contentType = "application/vnd.ms-excel; charset=gbk";
String fileName = null;
String postfixStr = ".xls";
if(StringUtils.isNotEmpty(request.getParameter("reportFilePostfix"))){
postfixStr = request.getParameter("reportFilePostfix");
}
if(StringUtils.isNotEmpty(request.getParameter("excelExportName"))){
fileName = request.getParameter("excelExportName")+postfixStr;
}else{
fileName = "export_data"+postfixStr;
}
exportFile(contentType,fileName,response,list);

return null;
``````

}

/*** Eclipse Class Decompiler plugin, copyright (c) 2012 Chao Chen (cnfree2000@hotmail.com) ***/
package com.paic.pafa.app.web.servlet.mvc.multiaction;

import com.paic.pafa.app.lwc.core.beans.factory.InitializingBean;
import com.paic.pafa.app.lwc.core.util.PathMatcher;
import java.util.Iterator;
import java.util.Properties;
import java.util.Set;

public class PropertiesMethodNameResolver extends AbstractUrlMethodNameResolver implements
InitializingBean {
private Properties mappings;

``````public void setMappings(Properties mappings) {
this.mappings = mappings;
}

public void afterPropertiesSet() {
if ((this.mappings == null) || (this.mappings.isEmpty()))
throw new IllegalArgumentException("'mappings' property is required");
}

protected String getHandlerMethodNameForUrlPath(String urlPath) {
String name = this.mappings.getProperty(urlPath);
if (name != null) {
return name;
}
for (Iterator it = this.mappings.keySet().iterator(); it.hasNext();) {
String registeredPath = (String) it.next();
if (PathMatcher.match(registeredPath, urlPath)) {
return ((String) this.mappings.get(registeredPath));
}
}
return null;
}
``````

}

package com.paic.tmslive.report.biz.action;

import java.util.Map;

import com.paic.pafa.app.dto.ServiceRequest;
import com.paic.pafa.app.dto.ServiceResponse;
import com.paic.tms.common.biz.action.TmsMultiMethodAction;
import com.paic.tms.common.util.KeyNames;
import com.paic.tms.common.util.TmsSeriesMappingUtils;
import com.paic.tmslive.report.biz.service.ReportExportService;

/**

• @author EX-CHENXIANXI001
*
*/
public class ReportExportAction extends TmsMultiMethodAction {

public ServiceResponse reportExport(ServiceRequest serviceRequest)
ReportExportService reportExportService = getReportExportService(serviceRequest
.getParameter(KeyNames.SERIES)==null?"02":serviceRequest
.getParameter(KeyNames.SERIES).toString());
Map param = (Map) serviceRequest.getCurrentRequestObject();
Map model = reportExportService.reportExport(param);
ServiceResponse serviceReponse = new ServiceResponse();
serviceReponse.setModel(model);
return serviceReponse;
}

private ReportExportService getReportExportService(String series)
ReportExportService reportExportService = (ReportExportService) context
.getBean(serviceName + pix
+ TmsSeriesMappingUtils.getSeriesMappingVaule(series));
return reportExportService;
}

}
}

package com.paic.tmslive.report.biz.service.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.paic.pafa.app.biz.service.BaseService;
import com.paic.pafa.app.lwc.service.persistence.dao.jdbc.UncategorizedSQLException;
import com.paic.tms.common.exception.TmsAppErrorMessageBaseException;
import com.paic.tms.common.util.CommonUtil;
import com.paic.tms.common.util.logger.TracerLogger;
import com.paic.tms.report.util.ReportExportKeyName;
import com.paic.tmslive.report.biz.service.ReportExportService;
import com.paic.tmslive.report.dto.ReportExportDTO;
import com.paic.tmslive.report.integration.dao.ReportExportDAO;

public class ReportExportServiceImpl extends BaseService implements ReportExportService {
private long rowMaxCount;

``````public void setRowMaxCount(long rowMaxCount) {
this.rowMaxCount = rowMaxCount;
}

private ReportExportDAO reportExportDAO;

public void setReportExportDAO(ReportExportDAO reportExportDAO) {
this.reportExportDAO = reportExportDAO;
}

@Override
@SuppressWarnings("unchecked")
public Map reportExport(Map param) throws BusinessServiceException {
// TODO Auto-generated method stub
TracerLogger.debug(CommonUtil.getUserID(), "ReportExportServiceImpl", "reportExport", "报表导出信息查询service");
Map model = new HashMap();
List reportExportResult=null;
try {
ReportExportDTO reportExportDTO = reportExportDAO.findSQLConfigInfo(param);
param.put(ReportExportKeyName.SQL_MAP_ID, reportExportDTO.getSqlMapId());
reportExportDAO.processReportData(param);

//如果查询结果集大于ibatis配置最大数量需要分页获取
``````

//

// if(resultCount.longValue()>rowMaxCount){

// }
try{
if(ex.getBaseException() instanceof UncategorizedSQLException){
throw new TmsAppErrorMessageBaseException("查询结果超过"+rowMaxCount+"行，请缩小查询条件范围！");
}

}
model.put(ReportExportKeyName.REPORT_EXPORT_RESULT, reportExportResult);
}else{ //通过SQL直接获取报表数据
param.put(ReportExportKeyName.SQL_MAP_ID, reportExportDTO.getSqlMapId());

``````            //如果查询结果集大于ibatis配置最大数量需要分页获取
``````

// resultCount = reportExportDAO.findReportExportDateForCount(param);
// if(resultCount.longValue()>rowMaxCount){

// }
try{
reportExportResult = reportExportDAO.findReportExportDate(param);
if(ex.getBaseException() instanceof UncategorizedSQLException){
throw new TmsAppErrorMessageBaseException("查询结果超过最大数量的限制，请缩小查询条件范围！");
}

}
model.put(ReportExportKeyName.REPORT_EXPORT_RESULT, reportExportResult);
}
} catch (Exception ex) {
}
return model;
}

}

--*********************************************************
--实现功能：把报表数据添加至临时表中
--输入参数：
-- p_line_id ： 报表行数(排序使用)
-- p_text ： 报表主要信息内容
-- p_message ： 提示信息
--*********************************************************
p_line_id in number,
p_text in varchar2,
p_message in varchar2) is
begin
insert into TMS_REP_TMP
values

procedure duality_teacher_info_listing(p_branch_id in varchar2,
p_withSubRegion in varchar2,
p_teacher_type in varchar2,
p_teacher_rank in varchar2,
p_channel_type in varchar2,
p_date_start in varchar2,
p_date_end in varchar2,
p_class_time_start in varchar2,
p_class_time_end in varchar2,
p_score_start in varchar2,
p_score_end in varchar2,
p_class_score_start in varchar2,
p_class_score_end in varchar2,
p_is_valid in varchar2,
--查询讲师基本信息
cursor cur_teacher_base_info(c_branch_id varchar2, c_withSubRegion varchar2, c_teacher_type varchar2, c_channel_type varchar2, c_is_valid varchar2) is
select tei.emp_name emp_name,
tei.empno empno,
(select tbti.branch_name
from tms_branch_tree_info tbti
where tbti.branch_level = '2'
and tbti.series_id = tti.series_id
connect by prior tbti.parent_branch_id = tbti.branch_id
and tbti.series_id = tti.series_id) branch_name2,
(select tbti3.branch_name
from tms_branch_tree_info tbti3
where tbti3.branch_id = tti.branch_id
and tbti3.series_id = tti.series_id) branch_name3,
trci.description region_name,
decode(tei.channel_type,
'0201',
'营销',
'0207',
'区拓',
'个险') channel_type_desc,
tei.channel_type channel_type,
trci.region_code region_code,
round(months_between(sysdate, tei.date_hire) / 12, 1) || '年' hire_year,
tms_life_report_package.func_get_employee_rank(tei.empno,
tei.channel_type) emp_rank_desc,
tti.series_id series_id
from tms_teacher_information tti,
tms_employee_info tei,
tms_region_code_info trci,
tms_child_branch_synch tcbs
where tei.empno = tti.teacher_no
and tei.series_id = tti.series_id
and trci.region_code = tei.region_code
and trci.series_id = tei.series_id
and tti.branch_id = tcbs.child_branch_id
and tti.series_id = tcbs.series_id
and tti.teacher_type || '' = c_teacher_type
and tti.is_valid = c_is_valid
and trci.market_type in ('03', '04')
and tei.channel_type || '' = nvl(c_channel_type, tei.channel_type)
and tcbs.parent_branch_id =
decode(c_withSubRegion,
'1',
tcbs.parent_branch_id,
tcbs.child_branch_id)
and tcbs.series_id = '02'
and tcbs.parent_branch_id = c_branch_id;
r_teacher_base_info cur_teacher_base_info%rowtype;

``````--查询讲师职级
cursor cur_teacher_rank_info(c_teacher_no varchar2, c_series_id varchar2) is
select trt.description rank_desc, trt.rank rank
from tms_teacher_rank_info ttri, tms_rank_type trt
where trt.rank = ttri.rank
and trt.teacher_type = ttri.teacher_type
and trt.series_id = ttri.series_id
and ttri.teacher_no = c_teacher_no
and ttri.series_id = c_series_id
order by ttri.date_start desc;
r_teacher_rank_info cur_teacher_rank_info%rowtype;

-- 讲师的课时
cursor cur_teacher_course_hour(c_series_id varchar2, c_teacher_no varchar2, c_date_start varchar2, c_date_end varchar2) is
select nvl(sum((tcci.date_end - tcci.date_start) * 24), 0) course_hour
from tms_class_course_info tcci
where tcci.series_Id = c_series_id
and tcci.teacher_no = c_teacher_no
and tcci.date_start >= to_date(c_date_start, 'yyyy-mm-dd')
and tcci.date_start < (to_date(c_date_end, 'yyyy-mm-dd') + 1);

--讲师授课积分
cursor cur_teacher_course_score(c_series_id varchar2, c_teacher_no varchar2, c_score_type varchar2, c_date_start varchar2, c_date_end varchar2) is
select nvl(sum(ttsd.score), 0) course_score
from tms_teacher_score_detail ttsd
where ttsd.series_id = c_series_id
and ttsd.teacher_no = c_teacher_no
and ttsd.score_type = c_score_type
and ttsd.date_score >= to_date(c_date_start, 'yyyy-mm-dd')
and ttsd.date_score < (to_date(c_date_end, 'yyyy-mm-dd') + 1);

--讲师嘉奖积分
cursor cur_teacher_score_adjust(c_series_id varchar2, c_teacher_no varchar2, c_date_start varchar2, c_date_end varchar2) is
where ttsa.series_id = c_series_id
and ttsa.teacher_no = c_teacher_no
and ttsa.date_adjust < (to_date(c_date_end, 'yyyy-mm-dd') + 1);

v_teacher_course_hores     number(20, 4);
v_teacher_course_scores    number(20, 4);
v_teacher_no_course_scores number(20, 4);
v_score_rate               number(20, 4);
v_text                     tms_rep_tmp.text%type;

v_branch_id         tms_branch_tree_info.branch_id%type;
v_withSubRegion     varchar2(2);
v_teacher_type      tms_teacher_type.teacher_type%type;
v_teacher_rank      tms_rank_type.rank%type;
v_channel_type      tms_channel_type.channel_type%type;
v_date_start        varchar2(20);
v_date_end          varchar2(20);
v_class_time_start  varchar2(50);
v_class_time_end    varchar2(50);
v_score_start       varchar2(20);
v_score_end         varchar2(20);
v_class_score_start varchar2(50);
v_class_score_end   varchar2(50);
v_is_valid          varchar2(2);
v_commit_num        number(8);
``````

begin
v_text := '工号' || chr(9) || '姓名' || chr(9) || '二级机构' || chr(9) ||
'三级机构' || chr(9) || '网点名称' || chr(9) || '模式' || chr(9) ||
'区域码' || chr(9) || '讲师职级' || chr(9) || '业务职级' || chr(9) || '司龄' ||
chr(9) || '课时' || chr(9) || '积分' || chr(9) || '授课积分' ||
chr(9) || '非授课积分' || chr(9) || '授课积分占比';

``````v_task_id := tms_pub_sys_package.func_get_sequence('tms_seq_task_id');
-1,
v_text,
null);

v_branch_id         := p_branch_id;
v_withSubRegion     := p_withSubRegion;
v_teacher_type      := p_teacher_type;
v_teacher_rank      := p_teacher_rank;
v_channel_type      := p_channel_type;
v_date_start        := p_date_start;
v_date_end          := p_date_end;
v_class_time_start  := p_class_time_start;
v_class_time_end    := p_class_time_end;
v_score_start       := p_score_start;
v_score_end         := p_score_end;
v_class_score_start := p_class_score_start;
v_class_score_end   := p_class_score_end;
v_is_valid          := p_is_valid;
v_commit_num        := 0;
open cur_teacher_base_info(v_branch_id,
v_withSubRegion,
v_teacher_type,
v_channel_type,
v_is_valid);
loop
fetch cur_teacher_base_info
into r_teacher_base_info;
exit when cur_teacher_base_info%notfound;

open cur_teacher_rank_info(r_teacher_base_info.empno,
r_teacher_base_info.series_id);
fetch cur_teacher_rank_info
into r_teacher_rank_info;
close cur_teacher_rank_info;

if v_teacher_rank is null or
v_teacher_rank = r_teacher_rank_info.rank then
--讲师授课课时
open cur_teacher_course_hour(r_teacher_base_info.series_id,
r_teacher_base_info.empno,
v_date_start,
v_date_end);
fetch cur_teacher_course_hour
into v_teacher_course_hores;
close cur_teacher_course_hour;

--讲师授课积分
open cur_teacher_course_score(r_teacher_base_info.series_id,
r_teacher_base_info.empno,
'02001',
v_date_start,
v_date_end);
fetch cur_teacher_course_score
into v_teacher_course_scores;
close cur_teacher_course_score;

--讲师嘉奖积分
r_teacher_base_info.empno,
v_date_start,
v_date_end);
into v_teacher_no_course_scores;

select decode((v_teacher_course_scores + v_teacher_no_course_scores),
0,
0,
round(v_teacher_course_scores /
(v_teacher_course_scores +
v_teacher_no_course_scores),
4) * 100)
into v_score_rate
from dual;

if (v_class_time_start is null or
(v_class_time_start <= v_teacher_course_hores and
v_teacher_course_hores <= v_class_time_end)) and
(v_score_start is null or
(v_score_start <=
(v_teacher_course_scores + v_teacher_no_course_scores) and
(v_teacher_course_scores + v_teacher_no_course_scores) <=
v_score_end)) and (v_class_score_start is null or
(v_class_score_start <= v_score_rate and
v_score_rate <= v_class_score_end)) then

v_text := r_teacher_base_info.empno || chr(9) ||
r_teacher_base_info.emp_name || chr(9) ||
r_teacher_base_info.branch_name2 || chr(9) ||
r_teacher_base_info.branch_name3 || chr(9) ||
r_teacher_base_info.region_name || chr(9) ||
r_teacher_base_info.channel_type_desc || chr(9) || '[' ||
r_teacher_base_info.region_code || ']' || chr(9) ||
r_teacher_rank_info.rank_desc || chr(9) ||
r_teacher_base_info.emp_rank_desc || chr(9) ||
r_teacher_base_info.hire_year || chr(9) ||
v_teacher_course_hores || chr(9) ||
(v_teacher_course_scores + v_teacher_no_course_scores) ||
chr(9) || v_teacher_course_scores || chr(9) ||
v_teacher_no_course_scores || chr(9) || v_score_rate || '%';
v_commit_num,
v_text,
null);
v_text       := '';
v_commit_num := v_commit_num + 1;
end if;
end if;
end loop;
close cur_teacher_base_info;
``````

end duality_teacher_info_listing;

<%@ include file="../../../common/common.jsp" %>

 ·当前位置:报表中心>> 培训班类报表 >> 主管辅导统计表

onclick="branchCheck('021002120000')"/>

descColumnNmae="description" fieldName="channelType" fieldId="channelType"
whereClause="channel_type != '0200'" style="background-color: #FFFFFF;" type="select"/>

width="100%" height="100%" marginwidth=″0″ marginheight=″0″
frameborder="0" scrolling="no" space="0" vspace="0"/>

<br> window.onload=function(){<br> Calendar.setup({<br> inputField : &quot;dateStart&quot;,<br> ifFormat : &quot;%Y-%m-%d&quot;,<br> showsTime : true,<br> button : &quot;f_start_trigger&quot;,<br> singleClick : true,<br> step : 1<br> });</p> <pre><code> Calendar.setup({ inputField : &quot;dateEnd&quot;, ifFormat : &quot;%Y-%m-%d&quot;, showsTime : true, button : &quot;f_end_trigger&quot;, singleClick : true, step : 1 }); } //下载 function exportChargeIntervention(){ if(!formValidate(&quot;chargeInterventionReport&quot;)) return; if(\$(&quot;#channelType&quot;).val() == null || \$(&quot;#channelType&quot;).val() == &quot;&quot;){ alert(&quot;请选择所属系列！&quot;); return; } var dateStart = \$(&quot;#dateStart&quot;).val(); var dateEnd = \$(&quot;#dateEnd&quot;).val(); if(addMonths(new Date(Date.parse(dateStart.replace(&quot;-&quot;,&quot;/&quot;))),12,&quot;-&quot;)&lt;dateEnd){ alert(&quot;查询时间段不能超过1年，请重新选择结束日期！&quot;); \$(&quot;#dateEnd&quot;).focus(); return; } var eventElement = event.srcElement; eventElement.disabled=true; \$(&quot;#chargeInterventionReport&quot;).attr(&quot;target&quot;,&quot;iframe1&quot;); \$(&quot;#chargeInterventionReport&quot;).submit(); eventElement.disabled=false; } &lt;/script&gt; </code></pre> <p></html></p>
qq_39612314   2017.08.04 15:14

form name="chargeInterventionReport" target="_blank" id="chargeInterventionReport" method="post" action='<c:url value="/report.export.data.do"/

input type="hidden" id="exportId" name="exportId" value="2017061901"

bean name="/report.export.data.do" class="com.paic.tms.report.web.controller.ReportExportController"
property name="methodNameResolver"
ref bean="ReportExportControllerMethodNameResolver" /
/property>

/bean>
bean id="ReportExportControllerMethodNameResolver" class="com.paic.pafa.app.web.servlet.mvc.multiaction.PropertiesMethodNameResolver"
property name="mappings"
props
prop key="/report.export.data.do">reportExport</prop
/props
/property
/bean

qq_39612314   2017.08.04 15:16

select '片区' || chr(9) ||
'二级机构' || chr(9) ||
'三级机构' || chr(9) ||
'营业部/组' || chr(9) ||
'步步高结训人数' || chr(9) ||
'完成辅导人数（9个单元全部完成）' || chr(9) ||
'主管一对一辅导九单元执行率'

from dual
union all
select firstBranchName || chr(9) ||
secondBranchName || chr(9) ||
threeBranchName || chr(9) ||
fourBranchName || chr(9) ||
BBGTrainFinishedNum || chr(9) ||
interventionFinishedNum || chr(9) ||
interventionExecutionRate

from (with T_ORG AS
(select tcbs.child_branch_id branch_id, tcbs.series_id
from tms_child_branch_synch tcbs,tms_branch_tree_info tbti
where tcbs.parent_branch_id = #branchId#
and tcbs.series_id = '02'
and tbti.series_id = tcbs.series_id
and tcbs.child_branch_id = tbti.branch_id
<![CDATA[
and tbti.branch_level < '5'
]]>

and tcbs.parent_branch_id = tcbs.child_branch_id

),
T_CLASS AS
(select tcsd.student_no, tcbs.branch_id
from tms_class_student_detail tcsd,
tms_class_information tci,
T_ORG tcbs
where tci.branch_id = tcbs.branch_id
and tci.class_type in (SELECT t.class_type
FROM tms_item_class_type_mapping t
WHERE t.item_type = '020005')
and tcsd.exam_result = '02002'
and tci.series_id = '02'
and tci.channel_type = #channelType#
and tci.class_id = tcsd.class_id
and tci.series_id = tcsd.series_id
<![CDATA[
and tci.date_finish >= to_date(#dateStart#, 'yyyy-mm-dd')
and tci.date_finish <= to_date(#dateEnd#,'yyyy-mm-dd')
]]>
union all
select tcsi.student_no, tcbs.branch_id
from tmslive_class_student_info tcsi,
tmslive_class_info tlci,
tmslive_class_detail tcd,
tmslive_plan_reported_info tpri,
T_ORG tcbs
where tpri.branch_id = tcbs.branch_id
and tlci.class_id = tcd.class_id

and tcd.plan_reported_id = tpri.plan_reported_id
and tcd.teacher_no = tcsi.assistant_no
and tlci.class_type in (SELECT t.class_type
FROM tms_item_class_type_mapping t
WHERE t.item_type = '020005')
and tcsi.exam_result = '02002'
and tlci.channel_type = #channelType#
and tlci.class_id = tcsi.class_id
<![CDATA[
and tlci.date_finish >= to_date(#dateStart#,'yyyy-mm-dd')
and tlci.date_finish <= to_date(#dateEnd#,'yyyy-mm-dd')),
]]>

t_emp AS
(select TPI.ID_NO, TC.branch_id
from T_CLASS TC, tms_personnel_info tpi
where TC.student_no = tpi.empno
union
select tei.ID_NO, TC.branch_id
from T_CLASS TC, tms_employee_info tei
where TC.student_no = tei.empno),
T_RTF AS
(select temp.branch_id, count(temp.ID_NO) cnt
from t_emp temp, RECRUIT_TUTOR_FLOW rtf
where rtf.idno = temp.id_no
and rtf.unit_one = '2'
and rtf.unit_two = '2'
and rtf.unit_three = '2'
and rtf.unit_four = '2'
and rtf.unit_five = '2'
and rtf.unit_six = '2'
and rtf.unit_seven = '2'
and rtf.unit_eight = '2'
and rtf.unit_nine = '2'
and rtf.delete_flag = 'N'
group by temp.branch_id),
T_STUDENT AS
(select branch_id, count(distinct id_no) cnt from t_emp group by branch_id)
select nvl((select t.branch_name
from tms_branch_tree_info t, tms_child_branch_synch cbs
where t.branch_id = cbs.parent_branch_id
and t.series_id = cbs.series_id
and cbs.child_branch_id = t0.branch_id
and cbs.series_id = t0.series_id
and t.branch_level = '1.5'),
(select t.branch_name
from tms_branch_tree_info t
where t.branch_id = t0.branch_id
and t.series_id = '02')||'总部') firstBranchName,
(select t.branch_name
from tms_branch_tree_info t, tms_child_branch_synch cbs
where t.branch_id = cbs.parent_branch_id
and t.series_id = cbs.series_id
and cbs.child_branch_id = t0.branch_id
and cbs.series_id = t0.series_id
and t.branch_level = '2') secondBranchName,
(select t.branch_name
from tms_branch_tree_info t, tms_child_branch_synch cbs
where t.branch_id = cbs.parent_branch_id
and t.series_id = cbs.series_id
and cbs.child_branch_id = t0.branch_id
and cbs.series_id = t0.series_id
and t.branch_level = '3') threeBranchName,
(select t.branch_name
from tms_branch_tree_info t, tms_child_branch_synch cbs
where t.branch_id = cbs.parent_branch_id
and t.series_id = cbs.series_id
and cbs.child_branch_id = t0.branch_id
and cbs.series_id = t0.series_id
and t.branch_level = '4') fourBranchName,
nvl(t1.cnt, 0) BBGTrainFinishedNum,
nvl(t2.cnt, 0) interventionFinishedNum,
round(nvl(nvl(t2.cnt, 0)*100 / nullif(t1.cnt, 0), 0),2) || '%' interventionExecutionRate
FROM T_ORG t0, T_STUDENT t1, T_RTF t2, tms_branch_order t3
where t0.branch_id = t1.branch_id(+)
and t0.branch_id = t2.branch_id(+)
and t0.branch_id = t3.branch_id
and t0.series_id = t3.series_id
order by t3.order_no
)