自定义表单取审批意见含主部门列

--自定义函数

function pageload()
{
sys_getApproveIdea_New('销售合同评审表2',pubdjbh,dataset2);
}

--js文件中加sys_getApproveIdea_New方法调用新PT

--修改pt

USE [C6]
GO
/****** 对象:  StoredProcedure [dbo].[pt_AppGetInstanceInfo_New]    脚本日期: 08/08/2012 09:18:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from users a
----left join Department b
----on a.
----select * from relationship
--select * from Department
--select * from RelationshipUsers

--select c.DeptName from users a 
--left join RelationshipUsers b
--on a.UserID=b.UserID
--left join Department c
--on b.DeptID=c.DeptID









/*
 
 获得流程实例的审批意见
 
*/
ALTER PROCEDURE [dbo].[pt_AppGetInstanceInfo_New]
@FormID varchar(256),					--表单ID
@FormObjectValues varchar(256)			--表单实例ID
AS

declare @Version varchar(100)
declare @Instance varchar(256)
select @Version=Version,@Instance=Instance_ID from jhoa_approve_instance where instance_id in 
(
select Instance_id from jhoa_approve where appt_id in (
select tem_id from jhoa_approve_template where form_id= @FormID
) and appo_values=@FormObjectValues
)


declare @tmpapprove table ( maxappid int)
insert into @tmpapprove(maxappid)
select app_id from 
(
select appd_id, reg_code,max(app_id) as app_id from jhoa_approve 
where instance_id=@Instance and del_flag=0 and app_state!=3
group by appd_id,reg_code
) as tb

SELECT distinct jhoa_approve_template.Tem_Name AS AppTypeName, 
jhoa_approve_template.Tem_ID AS AppTypeID, 
JHOA_Approve_Temp_Dispose.AppD_ID AS DisopsID, 
case  
--拟办是返回按钮名称作为步骤名称
when  jhoa_approve.appd_name IS  NULL then  (JHOA_Approve_Temp_Dispose.AppD_Name)
else  jhoa_approve.appd_name end  AS DisopseName, 
JHOA_Approve.App_IdeaFlag AS IdeaFlag,
JHOA_Approve_Idea.APPI_CONTENT AS Idea,
a.UserID as AppRegCode, 
a.UserName AS AppRegName, 
b.UserID as AppDeputyCode,
b.UserName as AppDeputyName,
m.DeptName as AppDeputyDeptName,--
JHOA_Approve.App_ID AS AppID,
JHOA_Approve.App_Time AS AppTime
FROM JHOA_Approve INNER JOIN
JHOA_Approve_Temp_Dispose ON 
JHOA_Approve.AppD_ID = JHOA_Approve_Temp_Dispose.AppD_ID and JHOA_Approve_Temp_Dispose.Version = @Version
inner join @tmpapprove ta on ta.maxappid=JHOA_Approve.app_id
 INNER JOIN
JHOA_Approve_Idea ON 
JHOA_Approve.App_ID = JHOA_Approve_Idea.App_ID INNER JOIN
--JHOA_Approve_Type ON 
jhoa_approve_template on 
JHOA_Approve.AppT_ID = jhoa_approve_template.Tem_ID INNER JOIN
Users a ON JHOA_Approve_Idea.Reg_Code = a.UserID LEFT OUTER JOIN 
Users b ON JHOA_Approve.App_DeputyCode = b.UserID
left join RelationshipUsers r--
on b.UserID=r.UserID --
left join Department m--
on r.DeptID=m.DeptID --
WHERE 
(JHOA_Approve.Del_Flag=0) and (r.RelaPrimary=1) and --
JHOA_Approve.Instance_ID = @Instance
 and
(JHOA_Approve_Temp_Dispose.AppD_Order<>-1) and
(not JHOA_Approve.App_Time is null)
and JHOA_Approve_Temp_Dispose.Version = @Version
ORDER BY
JHOA_Approve.App_ID



--select c.DeptName from users a 
--left join RelationshipUsers b
--on a.UserID=b.UserID
--left join Department c
--on b.DeptID=c.DeptID