--自定义函数 CREATEORREPLACEFUNCTION fn_WFTemplateIDGet ( TemplateCategoryID NUMBER, OrganID NUMBER, TemplateMode NUMBER ) RETURNNUMBER IS TemplateID NUMBER; ItemCount NUMBER; BEGIN --取模板中指定机构,指定分类的工作流模板记录 SELECTCOUNT(*) INTO ItemCount FROM t_WFTemplate WHERE f_OrganID = OrganID AND f_TemplateCategoryID = TemplateCategoryID; IF ItemCount =1THEN SELECT f_TemplateID INTO TemplateID FROM t_WFTemplate WHERE f_OrganID = OrganID AND f_TemplateCategoryID = TemplateCategoryID; ELSE TemplateID :=0; ENDIF; RETURN(TemplateID); END fn_WFTemplateIDGet;
包头
--包头 CREATEORREPLACE PACKAGE pkg_TEMP is TYPE curRecordset IS REF CURSOR; --功能:由人员获取数据 PROCEDURE up_ModuleShowByEmployeeID ( EmployeeID NUMBER, objRs OUT curRecordset ); END pkg_TEMP;
包体
--包体 CREATE OR REPLACE PACKAGE BODY pkg_TEMP is --功能:由人员获取数据 PROCEDURE up_ModuleShowByEmployeeID ( EmployeeID NUMBER, objRs OUT curRecordset ) IS BEGIN OPEN objRs FOR SELECT a.f_ApplicationID, a.f_ApplicationName FROM t_Application a JOIN t_ApplicationEmployee d ON d.f_RoleID = a.f_RoleID AND d.f_EmployeeID = EmployeeID UNION SELECT b.f_ApplicationID, b.f_ApplicationName FROM t_Application b WHERE BitAnd(f_RowFlag,3) >0 OR f_AppCode ='20' OR f_AppCode ='2040'; ORDER BY f_ApplicationID ASC ; END up_ModuleShowByEmployeeID; --用户登录验证 PROCEDURE up_LoginValidate ( EmployeeID OUT NUMBER, LoginName VARCHAR2, Password VARCHAR2 ) IS RecordCount NUMBER; BEGIN SELECT COUNT(*) INTO RecordCount FROM vw_Employee WHERE f_LoginName = LoginName; IF RecordCount =0 THEN --登录用户不存在 EmployeeID :=-1; RAISE_APPLICATION_ERROR(-20000, '登录用户不存在!'); RETURN; END IF; SELECT COUNT(*) INTO RecordCount FROM vw_Employee WHERE f_LoginName = LoginName AND f_RowFlag =0; IF RecordCount =0 THEN --用户没有访问权限 EmployeeID :=-2; RAISE_APPLICATION_ERROR(-20000, '用户没有访问权限!'); RETURN; END IF; SELECT COUNT(*) INTO RecordCount FROM vw_Employee WHERE f_LoginName = LoginName AND f_Password = Password AND f_RowFlag =0; IF RecordCount =0 THEN --登录名称或密码错误 EmployeeID :=-3; RAISE_APPLICATION_ERROR(-20000, '登录名称或密码错误!'); RETURN; END IF; SELECT f_EmployeeID INTO EmployeeID FROM vw_Employee WHERE f_LoginName = LoginName AND f_Password = Password AND f_RowFlag =0; END up_LoginValidate; --功能:获取记录集 PROCEDURE up_VisitLogShow ( VisitYear NUMBER, --年 VisitMonth NUMBER, --月 objRs OUT curRecordset ) IS BEGIN OPEN objRs FOR SELECT c.f_CustomerName AS f_OrganName, a.f_VisitLogID, a.f_EmployeeID, a.f_EmployeeName, a.f_IPAddress, to_Char(a.f_VisitTime,'yyyy-MM-dd HH24:mi:ss') AS f_VisitTime, a.f_VisitPage FROM vw_VisitLog a LEFT JOIN vw_Employee b ON b.f_EmployeeID = a.f_EmployeeID LEFT JOIN vw_customer c ON c.f_CustomerID = b.f_OrganID WHERE EXTRACT(YEAR FROM a.f_VisitTime) = VisitYear AND EXTRACT(MONTH FROM a.f_VisitTime) = VisitMonth ORDER BY f_VisitLogID DESC; END up_VisitLogShow; --获取查询结果 PROCEDURE up_QueryShow ( OrganID NUMBER, LineKey VARCHAR2, objRs OUT curRecordset ) IS BEGIN OPEN objRs FOR SELECT a.*, CASE nvl(b.f_TypeID ,0) WHEN 0 THEN '无类型' WHEN 1 THEN '类型1' ELSE '类型2' END AS f_TypeName, b.f_DoubleLineCode FROM vw_LineOrganDNShow a LEFT JOIN vw_LineDistributionGroup b ON a.f_LineGroupID = b.f_LineGroupID WHERE (f_LineKey LIKE LineKey||'%' OR f_LineName LIKE LineKey ||'%') AND a.f_OrganID = OrganID AND ROWNUM <20; END up_LineQueryShow; --功能:游标循环 PROCEDURE up_WFConfigCopy ( TemplateID NUMBER, --源模版ID NewTemplateName VARCHAR2, --新模版名称 NewRemark VARCHAR2, --新模版说明 NewOrganID NUMBER --新模版机构ID ) IS TemplateCategoryID NUMBER; --源模版分类ID NewTemplateID NUMBER; --新模版ID --流程步骤 StepName VARCHAR2(50); StepOrder NUMBER; MaxHour NUMBER; StepFlag NUMBER; --源模板对应的流程步骤集 CURSOR WFSteps IS SELECT a.f_StepName, a.f_StepOrder, a.f_StepFlag FROM vw_WFStep a WHERE a.f_TemplateID = TemplateID; BEGIN --StepID := fn_WFNextStepIDGet(StepID); --获取被复制模版分类ID SELECT f_TemplateCategoryID INTO TemplateCategoryID FROM vw_WFTemplate WHERE f_TemplateID = TemplateID; --新增模板 up_WFTemplateAdd ( NewTemplateID, NewTemplateName, TemplateCategoryID, NewRemark, NewOrganID ); --复制源模板的流程步骤 OPEN WFSteps; LOOP FETCH WFSteps INTO StepName,StepOrder,StepFlag; EXIT WHEN WFSteps%NOTFOUND; --添加步骤 INSERT INTO t_WFStep ( f_TemplateID, f_StepID, f_StepName, f_StepOrder, f_MaxDate, f_StepFlag ) VALUES ( NewTemplateID, Seq_WFStep.NEXTVAL, --步骤ID StepName, StepOrder, SYSDATE, --当前日期 StepFlag ); END LOOP; CLOSE WFSteps; END up_WFConfigCopy; --功能:新增 PROCEDURE up_WFRoleAdd ( TemplateCategoryID NUMBER, --模板分类ID WFRoleName VARCHAR2 --角色名称 ) IS WFRoleID NUMBER; BEGIN SELECT Seq_WFRole.NEXTVAL INTO WFRoleID FROM dual; --流程角色表 INSERT INTO t_WFRole ( f_RoleID, f_RoleName ) VALUES ( WFRoleID, WFRoleName ); END up_WFRoleAdd; --功能:修改 PROCEDURE up_WFRoleEdit ( WFRoleID NUMBER, --角色ID WFRoleName VARCHAR2 --角色名称 ) IS BEGIN --流程角色表 UPDATE t_WFRole SET f_RoleName = WFRoleName WHERE f_RoleID = WFRoleID; END up_WFRoleEdit; --功能:删除 PROCEDURE up_WFRoleDelete ( WFRoleID NUMBER ) IS CountRole NUMBER; BEGIN SELECT COUNT(*) INTO CountRole FROM vw_WFRoleEmployee WHERE f_RoleID = WFRoleID; IF CountRole1 =0 THEN --流程模板分类角色对应表 DELETE FROM t_WFTemplateCategoryRole WHERE f_RoleID = WFRoleID; --流程角色表 DELETE FROM t_WFRole WHERE f_RoleID = WFRoleID; ELSE RAISE_APPLICATION_ERROR(-20000, '该角色已设置到流程中,不能删除!'); END IF; END up_WFRoleDelete; END pkg_TEMP;