0822周五

 

oracle中新建：

 

create or replace function F_reprot_reviewCount(
      as_date0              in varchar2, --开始日期
      as_date1              in varchar2,  --结束日期
      as_customer_type_code in varchar2, --客户类型
      as_Product_Type_Code  in varchar2, --产品类型
      as_Servdoc_Type_Code  in varchar2,  --单据类型
      as_serv_Doc_Id          in varchar2   --服务单号
      )
   return SYS_REFCURSOR is
   Result SYS_REFCURSOR;
   ls_yearmonth varchar2(20);
begin

/*6.1.1.  服务单数据导出:1.回访/服务质量统计
时间段、客户类型、服务单号、产品类型、单据类型
--GJ
--2008-08-21
--省、市、客户名称、服务机构编号、维修单编号、报修日期、完修日期、修复周期
*/
  open Result for
  SELECT DISTINCT area.area_name,
                  province.province_name,
                  city.city_name,
                  fixStation.Fix_Station_Id,
                  fixStation.Fix_Station_Name,
                  --fixStation.Notebookpc_Dist,
                  --decode(fixStation.Notebookpc_Dist,'N','否','Y','维修站','Y0','窗口站',fixStation.Notebookpc_Dist) Notebookpc_Dist_desc,
                  --fixStation.Monitor_Dist,
                  --decode(fixStation.Monitor_Dist,'N','否','Y','快修','Y0','非快修',fixStation.Monitor_Dist) monitor_dist_desc,
                  --fixStation.Server_Dist,
                  --decode(fixStation.Server_Dist,'N','否','Y','是',fixStation.Server_Dist) Server_Dist_desc,
                  decode(servDoc.Product_Type_Code,'02',decode(fixStation.Monitor_Dist,'N','否','Y','快修','Y0','非快修',fixStation.Monitor_Dist) ,'03',decode(fixStation.Notebookpc_Dist,'N','否','Y','维修站','Y0','窗口站',fixStation.Notebookpc_Dist),'04',decode(fixStation.Server_Dist,'N','否','Y','是',fixStation.Server_Dist),'台式机') fixStaion_Dist_desc,
                  servDoc.Serv_Doc_Id,
                  servDoc.Move_Flag,
                  decode(servDoc.Move_Flag, '2', '是', '否') is_Prev_Station,
                  servDoc.Prev_Serv_Doc_Id,
                  servDoc.Next_Serv_Doc_Id,
                  servDoc.User_Id,
                  servDoc.Customer_Name,
                  servDoc.Customer_Send_Name,
                  servDoc.Customer_Send_Tel,
                  servDoc.Customer_Service_Add,
                  servDoc.Servdoc_Type_Code,
                  servDoc.Wxlb,
                  servDoc.Customer_Type_Code,
                  customerType.Customer_Type_Name,
                  USERINFO.Name create_user,
                  fixType.Fix_Type_Name,
                  servDocType.Servdoc_Type_Name,
                  faultType.Fault_Type_Name,
                  docState.State_Name,
                  productType.Product_Type_Name,
                  applyCrowd.Apply_Fix_Crowd_Name,
                  customerInfo.Customer_Dept_Id,
                  customerInfo.Tel1 customer_tel1,
                  customerInfo.Tel2 customer_tel2,
                  servProductInfo.Product_Id,
                  TO_CHAR(servProductInfo.Buy_Date,'yyyy-MM-dd') product_buy_date,
                  servProductInfo.Fault_Proc_Code,
                  faultProc.Fault_Proc_Name,
                  monitorInfo.Monitor_No,
                  monitorInfo.Monitor_Desc,
                  monitorInfo.Monitor_Sn,
                  prodcutFaultInfo.Fault_Proc_Code,
                  procFaultProc.Fault_Proc_Name,
                  prodcutFaultInfo.Materiel_Id,
                  baseMaterInfo.Materiel_Desc,
                  appDocInfo.App_Time app_date,
                  appDocInfo.Spare_Part_Id,
                  appMaterInfo.Materiel_Id app_materiel_id,
                  decode(appMaterInfo.Is_Service_Term,'Y','是','N','否') Is_Service_Term,
                  conMaterInfo.Confirm_Materiel_Id,
                  decode(matchingMaterInfo.Doa_Count, '0', '否', '是') has_DOA,
                  matchingBoxInfo.Accept_Date materiel_accept_date,
                  backMain.Fix_Engineer,
                  allotType.Allot_Type_Name
    FROM OP_SERVDOCINFO servDoc
   INNER JOIN  (select hs.doc_id, hs.state_date
            from op_history_doc_stateinfo hs
           where hs.doc_type_code = '01'
             and hs.doc_state_code = '05'
             and TO_CHAR(hs.state_date,'yyyy-MM-dd') BETWEEN as_date0 AND as_date1
             ) hst ON servDoc.Serv_Doc_Id = hst.doc_id
   INNER JOIN FIX_STATIONINFO fixStation ON servDoc.Service_Station_Id = fixStation.Fix_Station_Id
   INNER JOIN CODE_CITY city ON fixStation.City_Code = city.city_code
   INNER JOIN CODE_AREA area ON area.area_code = city.area_code
   INNER JOIN CODE_PROVINCE province ON province.province_code = city.province_code
   INNER JOIN CODE_PRODUCT_TYPE productType ON servDoc.Product_Type_Code = productType.Product_Type_Code
   INNER JOIN CUSTOMER_BASEINFO customerInfo ON servDoc.Customer_Id = customerInfo.Customer_Id
   INNER JOIN CODE_CUSTOMER_TYPE customerType ON customerInfo.Customer_Prop_Code = customerType.Customer_Type_Code
   INNER JOIN CODE_APPLY_FIX_CROWD applyCrowd ON applyCrowd.Apply_Fix_Crowd_Code = servDoc.Apply_Fix_Crowd_Code
   INNER JOIN CODE_DOC_STATE docState ON docState.State_Code = servDoc.Serv_Doc_State_Code
   INNER JOIN CODE_SERVDOC_TYPE servDocType ON servDocType.Servdoc_Type_Code = servDoc.Servdoc_Type_Code
   INNER JOIN USERINFO ON USERINFO.CODE = servDoc.User_Id
   INNER JOIN CODE_FIX_TYPE fixType ON fixType.Fix_Type_Code = servDoc.Wxlb
   INNER JOIN CODE_FAULT_TYPE faultType ON servDoc.Fault_Type_Code = faultType.Fault_Type_Code
   INNER JOIN OP_SERVDOCINFO_PRODUCTINFO servProductInfo ON servDoc.Serv_Doc_Id = servProductInfo.Serv_Doc_Id
    LEFT JOIN CODE_FAULT_PROC faultProc ON servProductInfo.Fault_Proc_Code = faultProc.Fault_Proc_Code
    LEFT JOIN OP_PRODUCTINFO_MONITORINFO monitorInfo ON servProductInfo.Serv_Doc_Product_Id = monitorInfo.Serv_Doc_Product_Id
    LEFT JOIN Op_Productinfo_Faultinfo prodcutFaultInfo ON servProductInfo.Serv_Doc_Product_Id = prodcutFaultInfo.Serv_Doc_Product_Id
   INNER JOIN CODE_FAULT_PROC procFaultProc ON prodcutFaultInfo.Fault_Proc_Code = procFaultProc.Fault_Proc_Code
   INNER JOIN BASE_MATERIELINFO baseMaterInfo ON baseMaterInfo.Materiel_Id = prodcutFaultInfo.Materiel_Id
    LEFT JOIN Op_Sparepartinfo_Materiel appMaterInfo ON servDoc.Serv_Doc_Id = appMaterInfo.Serv_Doc_Id
    LEFT JOIN OP_APP_SPAREPARTINFO appDocInfo ON appDocInfo.Spare_Part_Id = appMaterInfo.Spare_Part_Id
    LEFT JOIN OP_CONFIRMINFO_MATERIEL conMaterInfo ON conMaterInfo.App_Sparepart_Materiel_Id = appMaterInfo.App_Sparepart_Materiel_Id
    LEFT JOIN OP_MATCHINGINFO_BOX matchingMaterInfo ON matchingMaterInfo.Matching_Id = conMaterInfo.Matching_Id
    LEFT JOIN OP_BOXINFO matchingBoxInfo ON matchingBoxInfo.Matching_Id = conMaterInfo.Matching_Id
    LEFT JOIN OP_SPAREPARTINFO_BACKMAIN backMain ON backMain.Serv_Doc_Id = servDoc.Serv_Doc_Id 
   INNER JOIN CODE_ALLOT_TYPE allotType ON allotType.Allot_Type_Code = servDoc.Allot_Type_Code                                  
   WHERE servDoc.Product_Type_Code = as_Product_Type_Code
     AND servDoc.Customer_Type_Code = as_customer_type_code
     AND servDoc.Servdoc_Type_Code = as_Servdoc_Type_Code
     AND servDoc.Serv_Doc_Id = as_serv_Doc_Id
    
     ORDER BY servDoc.Serv_Doc_Id;
  return(Result);
end F_reprot_reviewCount;

 

 

ibtis的sqlMap.xml中调用：

   <!-- 6.1.1.  服务单数据导出:1.回访/服务质量统计 -->
   <parameterMap id="paramReivewCount" class="java.util.Map" >
          <parameter property="result" jdbcType="ORACLECURSOR"  javaType="java.sql.ResultSet"  mode="OUT" />
          <parameter property="creationDate1"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
          <parameter property="creationDate2"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
          <parameter property="customerTypeCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
          <parameter property="productTypeCode"  jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
          <parameter property="docTypeCode" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
          <parameter property="servDocId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    </parameterMap>
    <procedure id="getServDocReivewCount" parameterMap="paramReivewCount"  resultClass="java.util.HashMap" >
        {? = call F_reprot_reviewCount(?,?,?,?,?,?)}
     </procedure> 

 

 

 

java中的Manager中调用即可this.getSqlTempeleClient().queryForList(entiyClass.getName()+".getServDocReivewCount",paramHM);