我现在的状况是我在代码中写了一个图片的放大缩小，但是现在的情况是，在ViewPager中的图片要放大都放大，要是缩小都缩小，请问这个事情怎么解决呢？求助大神呢

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')
and t.COLUMN_NAME = upper('is_mandatory_upload');
if v_count > 0 then
execute immediate 'alter table tmsdata.tms_archive_type drop column
is_mandatory_upload';
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')
and t.COLUMN_NAME = upper('is_mandatory_upload');
if v_count > 0 then
execute immediate 'alter table tmsdata.tms_archive_type drop column
is_mandatory_upload';
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);

$("#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");
dispatchType = $(obj).find("input[type=radio][name=dispatchType"+courseId+"]:checked").val();
dispatchTypeDays = $(obj).find("input[type=text][name=dispatchTypeDays]").val();
dispatchTypeHours = $(obj).find("select[name=dispatchTypeHours]").val();
remindType = $(obj).find("input[type=radio][name=remindType"+courseId+"]:checked").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
+ ',toolbar=no,menubar=no,scrollbars=no, resizable=no,location=no, status=no');
}

$.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) {
alert(data.SERVICE_RESPONSE_RESULT);
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
+ ',toolbar=no,menubar=no,scrollbars=yes, resizable=no,location=no, status=no');
}

/**
* 增加同步监控信息
* @return
*/
private InterfaceSyncMonitorDTO addMonitor(String interfaceName, String content) {
InterfaceSyncMonitorDTO interfaceSyncMonitor = new InterfaceSyncMonitorDTO(interfaceName,
"SALES_RECRUIT", "TMSV2-CORE", content);
InterfaceSyncMonitor.addMonitor(interfaceSyncMonitor);
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.integration.dao.PafaDAOException;
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 同步监控信息
  • @throws PafaDAOException [参数说明]
  • @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 同步结果信息
  • @throws PafaDAOException *
  • @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;
  // 线程信息（针对多线程的情况）
  private String threadInfo;
  // 同步结果: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 getThreadInfo() {
  return threadInfo;
  }

  public void setThreadInfo(String threadInfo) {
  this.threadInfo = threadInfo;
  }

  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;
}

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'
start with t.series_id = '02'
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.deadLineTime,
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)
AND ROWNUM = 1) +dd.exam_deadline_days-1 + dd.exam_deadline_hours / 24 +
dd.exam_deadline_mins / 24 / 60 deadLineTime,
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;
    if rr_c_student_info.deadLineTime >= rr_c_student_info.dateEnd  then
       v_exam_end_date  := rr_c_student_info.dateEnd+1;
    end if;
    if rr_c_student_info.deadLineTime < rr_c_student_info.dateEnd  then
       v_exam_end_date  := rr_c_student_info.deadLineTime;
    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 ;

  v_seq := (lpad(to_char(v_seq),20,'0'));
  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;

<?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){
      TmsAppErrorMessageBaseException ex = getBusinessMessageException(e);
      if(null!=ex){               
          response.setContentType("text/html; charset=GBK");
          PrintWriter out = response.getWriter();             
          out.println("<script>\n");              
          out.println("alert('"+ex.getMessage()+"');");
          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.biz.service.BusinessServiceException;
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)
  throws BusinessServiceException {
  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)
  throws BusinessServiceException {
  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.biz.service.BusinessServiceException;
import com.paic.pafa.app.integration.dao.PafaDAOException;
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);
        if("Y".equals(reportExportDTO.getProcFlag())){   //通过存储过程处理报表数据后放到临时表，然后从临时表里取数据 注：存储过程的返回值名称为:taskId
            param.put(ReportExportKeyName.SQL_MAP_ID, reportExportDTO.getSqlMapId());
            reportExportDAO.processReportData(param);

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

// resultCount = reportExportDAO.findReportExportDateForCount((String)param.get(ReportExportKeyName.TASK_ID));
//

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

// return getPagerModel(resultCount.longValue(),ReportExportKeyName.TASK_ID,param);
// }
try{
reportExportResult = reportExportDAO.findReportExportDate((String)param.get(ReportExportKeyName.TASK_ID));
}catch(PafaDAOException ex){
if(ex.getBaseException() instanceof UncategorizedSQLException){
throw new TmsAppErrorMessageBaseException("查询结果超过"+rowMaxCount+"行，请缩小查询条件范围！");
}

throw new BusinessServiceException(ex);
}
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){

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

throw new BusinessServiceException(ex);

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

}

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

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,
p_task_id out varchar2) is
--查询讲师基本信息
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'
start with tbti.branch_id = tti.branch_id
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
  select nvl(sum(ttsa.score), 0) score_adjust
    from tms_teacher_score_adjust ttsa
   where ttsa.series_id = c_series_id
     and ttsa.teacher_no = c_teacher_no
     and ttsa.date_adjust >= to_date(c_date_start, 'yyyy-mm-dd')
     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_task_id                  tms_rep_tmp.task_id%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');
p_task_id := v_task_id;
tms_life_report_package.add_tms_req_tmp_data(v_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;

    --讲师嘉奖积分
    open cur_teacher_score_adjust(r_teacher_base_info.series_id,
                                  r_teacher_base_info.empno,
                                  v_date_start,
                                  v_date_end);
    fetch cur_teacher_score_adjust
      into v_teacher_no_course_scores;
    close cur_teacher_score_adjust;

    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 || '%';
      tms_life_report_package.add_tms_req_tmp_data(v_task_id,
                                                   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"/>

<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>

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

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
)