CREATE VIEW dbo.dispayreceiver
--with ENCRYPTION 
AS
SELECT f.*, r.Title AS Title, r.TopicWord AS TopicWord, a.UserName AS sendname, 
      e.ExigenceDegreeName AS ExigenceDegreeName, 
      e.ExigenceDegreeID AS ExigenceDegreeID, 
      s.SecretGreadName AS SecretGreadName
FROM dbo.FileGive f INNER JOIN
      dbo.RecordFile r ON f.RecordFileID = r.RecordFileID INNER JOIN
      dbo.Accounts_Users a ON r.UserID = a.UserID INNER JOIN
      dbo.ExigenceDegree e ON r.ExigenceDegreeID = e.ExigenceDegreeID INNER JOIN
      dbo.SecretGread s ON r.SecretGreadID = s.SecretGreadID




--*********************
CREATE VIEW dbo.RecordFileView
--with    ENCRYPTION 
AS
SELECT dbo.RecordFile.*, dbo.Accounts_Users.UserName AS UserName
FROM dbo.RecordFile INNER JOIN
      dbo.Accounts_Users ON dbo.RecordFile.UserID = dbo.Accounts_Users.UserID




--**********************
CREATE VIEW dbo.V_Document
--with    ENCRYPTION 
AS
SELECT dbo.Document.DocumentID, dbo.Document.Title, dbo.Document.CurrentStepID, 
      dbo.Document.UserID, dbo.Document.StreamID, dbo.Document.RecordID, 
      dbo.Document.Subject, dbo.Document.Author, dbo.Document.StreamStatus, 
      dbo.SecretGread.SecretGreadName, dbo.ExigenceDegree.ExigenceDegreeName, 
      dbo.Stream.StreamName, dbo.Document.CompleteSign, 
      dbo.Document.DraftDepartment, dbo.Document.FileDate, dbo.Stream.Stream, 
      dbo.Document.FileType, dbo.DocumentChildType.DocumentTypeName, 
      dbo.DocumentPrimaryType.DocumentTypeDescription
FROM dbo.Document INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.Document.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID INNER JOIN
      dbo.Stream ON dbo.Document.StreamID = dbo.Stream.StreamID INNER JOIN
      dbo.SecretGread ON 
      dbo.Document.SecretGreadID = dbo.SecretGread.SecretGreadID INNER JOIN
      dbo.DocumentChildType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentChildType.DocumentTypeID INNER JOIN
      dbo.DocumentPrimaryType ON 
      dbo.DocumentChildType.PrimaryType = dbo.DocumentPrimaryType.PrimaryType
	  


--**********************
CREATE VIEW dbo.V_Document2
--with    ENCRYPTION 
AS
SELECT dbo.Document.DocumentID, dbo.Document.Title, dbo.Document.CurrentStepID, 
      dbo.Document.UserID, dbo.Document.StreamID, dbo.Document.RecordID, 
      dbo.Document.Subject, dbo.Document.Author, dbo.Document.StreamStatus, 
      dbo.DocumentType.DocumentTypeDescription, dbo.SecretGread.SecretGreadName, 
      dbo.ExigenceDegree.ExigenceDegreeName, dbo.Stream.StreamName, 
      dbo.Document.CompleteSign, dbo.Document.DraftDepartment, 
      dbo.Document.FileDate, dbo.Stream.Stream, 
      dbo.DocumentChildType.DocumentTypeName
FROM dbo.Document INNER JOIN
      dbo.DocumentType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentType.DocumentTypeID INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.Document.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID INNER JOIN
      dbo.Stream ON dbo.Document.StreamID = dbo.Stream.StreamID INNER JOIN
      dbo.SecretGread ON 
      dbo.Document.SecretGreadID = dbo.SecretGread.SecretGreadID INNER JOIN
      dbo.DocumentChildType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentChildType.DocumentTypeID





--	  **********************
CREATE VIEW dbo.VIEW_DOCUMENT
--with    ENCRYPTION 
AS
SELECT dbo.Document.DocumentID, dbo.Document.SecretGreadID, 
      dbo.Document.DocumentTypeID, dbo.Document.ExigenceDegreeID, 
      dbo.Document.DocumentHead, dbo.Document.DocumentNumber, dbo.Document.Title, 
      dbo.Document.MainSendComp, dbo.Document.CopySendComp, 
      dbo.Document.DraftDepartment, dbo.Document.SendDepartment, 
      dbo.Document.TopicWord, dbo.Document.Content, dbo.Document.Range, 
      dbo.Document.CurrentStepID, dbo.Document.CurrentStepName, 
      dbo.Document.CompleteSign, dbo.Document.UserID, dbo.Document.SendDate, 
      dbo.Document.StreamID, dbo.Document.StapleSign, dbo.Document.DeleteSign, 
      dbo.ExigenceDegree.ExigenceDegreeName, dbo.Accounts_Users.UserName, 
      dbo.SecretGread.SecretGreadName, 
      dbo.IshaveAccessories(dbo.Document.DocumentID) AS IshaveAccessories, 
      dbo.Document.Author, dbo.Document.Template, dbo.Document.RecordID, 
      dbo.Document.Subject, dbo.Document.FileDate, dbo.Document.FileType, 
      dbo.Document.HtmlPath, dbo.Document.TemplateID, dbo.Document.StreamStatus, 
      dbo.Document.Status, dbo.DocumentChildType.DocumentTypeName, 
      dbo.Document.DrawMan
FROM dbo.Document INNER JOIN
      dbo.Accounts_Users ON 
      dbo.Document.UserID = dbo.Accounts_Users.UserID INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.Document.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID INNER JOIN
      dbo.SecretGread ON 
      dbo.Document.SecretGreadID = dbo.SecretGread.SecretGreadID INNER JOIN
      dbo.DocumentChildType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentChildType.DocumentTypeID






--	  **********************
CREATE VIEW dbo.VIEW_DocumentType
--with    ENCRYPTION 
AS
SELECT dbo.DocumentChildType.DocumentTypeID, 
      dbo.DocumentChildType.DocumentTypeName, 
      dbo.DocumentPrimaryType.DocumentTypeDescription, 
      dbo.DocumentPrimaryType.PrimaryType
FROM dbo.DocumentChildType INNER JOIN
      dbo.DocumentPrimaryType ON 
      dbo.DocumentChildType.PrimaryType = dbo.DocumentPrimaryType.PrimaryType





--	  **********************
CREATE VIEW dbo.VIEW_ReceiveArchives
--with    ENCRYPTION 
AS
SELECT dbo.ExigenceDegree.ExigenceDegreeName, dbo.Departments.DepartmentID, 
      dbo.ReceiveDocument.ReDocumentID, dbo.ReceiveDocument.ReceiveFileNumber, 
      dbo.ReceiveDocument.ReceiveDate, dbo.ReceiveDocument.SendDepartment, 
      dbo.ReceiveDocument.DocumentTitle, dbo.ReceiveDocument.DocumentCount, 
      dbo.ReceiveDocument.UserID, dbo.ReceiveDocument.UpfilePath, 
      dbo.ReceiveDocument.IsComplete, dbo.ReceiveDocument.WaitID, 
      dbo.ReceiveDocument.Receivedeclare, dbo.ReceiveDocument.BackDate, 
      dbo.ReceiveDocument.workDepartment, dbo.Departments.DepartmentName
FROM dbo.ReceiveDocument INNER JOIN
      dbo.Accounts_Users ON 
      dbo.ReceiveDocument.UserID = dbo.Accounts_Users.UserID INNER JOIN
      dbo.Departments ON 
      dbo.Accounts_Users.DepartmentID = dbo.Departments.DepartmentID INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.ReceiveDocument.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID	  

--	  **********************
CREATE VIEW dbo.VIEWAccounts_Users
--with    ENCRYPTION 
AS
SELECT dbo.Accounts_Users.UserID, dbo.Accounts_Users.ProfessionalLevelID, 
      dbo.Accounts_Users.JobLevelID, dbo.Accounts_Users.Password, 
      dbo.Accounts_Users.UserName, dbo.Accounts_Users.Sex, 
      dbo.Accounts_Users.Photo, dbo.Accounts_Users.School, 
      dbo.Accounts_Users.Speciality, dbo.Accounts_Users.IsMarried, 
      dbo.Accounts_Users.Nationality, dbo.Accounts_Users.NativePlace, 
      dbo.Accounts_Users.PoliticalFeature, dbo.Accounts_Users.IDCard, 
      dbo.Accounts_Users.Birthday, dbo.Accounts_Users.Telephone, 
      dbo.Accounts_Users.Mobile, dbo.Accounts_Users.Email, 
      dbo.Accounts_Users.ZipCode, dbo.Accounts_Users.Address, 
      dbo.Accounts_Users.LastLoginDate, dbo.Accounts_Users.IsPublic, 
      dbo.Accounts_Users.State, dbo.Accounts_Users.ExitDate, 
      dbo.Accounts_Users.Content, dbo.Accounts_Users.UnitID, 
      dbo.Departments.DepartmentName, dbo.ProfessionalLevel.ProfessionalLevelName, 
      dbo.JobLevels.Description
FROM dbo.Accounts_Users INNER JOIN
      dbo.Departments ON 
      dbo.Accounts_Users.DepartmentID = dbo.Departments.DepartmentID INNER JOIN
      dbo.JobLevels ON 
      dbo.Accounts_Users.JobLevelID = dbo.JobLevels.JobLevelID INNER JOIN
      dbo.ProfessionalLevel ON 
      dbo.Accounts_Users.ProfessionalLevelID = dbo.ProfessionalLevel.ProfessionalLevelID	  




--	  **********************
CREATE  VIEW dbo.VIEWdocument
--with    ENCRYPTION 
AS
SELECT dbo.Stream.Stream, dbo.Document.DocumentID, dbo.Document.Title, 
      dbo.ExigenceDegree.ExigenceDegreeName, dbo.SecretGread.SecretGreadName, 
      dbo.DocumentTransact.StepID, dbo.DocumentTransact.TransactSign, 
      dbo.Document.Author, dbo.Document.CompleteSign, dbo.Stream.StreamName, 
      dbo.Document.Subject, dbo.Document.MainSendComp, 
      dbo.Document.CopySendComp, dbo.Document.TopicWord, 
      dbo.Document.CurrentStepID, dbo.Document.StreamID, 
      dbo.DocumentTransact.BackState, dbo.Document.StreamStatus, 
      dbo.DocumentTransact.UserID, dbo.DocumentTransact.TransactDate, 
      dbo.Accounts_Users.UserName, dbo.Document.FileDate, 
      dbo.Departments.DepartmentName, dbo.Document.DraftDepartment, dbo.Document.DrawMan, 
      dbo.Document.SendDepartment, dbo.DocumentChildType.DocumentTypeName, 
      dbo.DocumentPrimaryType.DocumentTypeDescription, 
      dbo.DocumentTransact.transtype
FROM dbo.Accounts_Users LEFT OUTER JOIN
      dbo.Document INNER JOIN
      dbo.Stream ON dbo.Document.StreamID = dbo.Stream.StreamID INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.Document.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID INNER JOIN
      dbo.SecretGread ON 
      dbo.Document.SecretGreadID = dbo.SecretGread.SecretGreadID ON 
      dbo.Accounts_Users.UserID = dbo.Document.UserID RIGHT OUTER JOIN
      dbo.DocumentTransact ON 
      dbo.Document.DocumentID = dbo.DocumentTransact.DocumentID INNER JOIN
      dbo.Departments ON 
      dbo.Departments.DepartmentID = dbo.Document.SendDepartment INNER JOIN
      dbo.DocumentChildType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentChildType.DocumentTypeID INNER JOIN
      dbo.DocumentPrimaryType ON 
      dbo.DocumentChildType.PrimaryType = dbo.DocumentPrimaryType.PrimaryType	  




--	  **********************
	  
CREATE VIEW dbo.ViewDocumentno
--with    ENCRYPTION 
AS
SELECT dbo.Document.DocumentID, dbo.Document.Title, 
      dbo.ExigenceDegree.ExigenceDegreeName, dbo.Stream.Stream, 
      dbo.Document.CompleteSign, dbo.Document.CurrentStepID, 
      dbo.Document.CurrentStepName, dbo.SecretGread.SecretGreadName, 
      dbo.Document.SendDepartment, dbo.Document.FileDate, 
      dbo.DocumentChildType.DocumentTypeName, dbo.Document.UserID
FROM dbo.Document INNER JOIN
      dbo.ExigenceDegree ON 
      dbo.Document.ExigenceDegreeID = dbo.ExigenceDegree.ExigenceDegreeID INNER JOIN
      dbo.Stream ON dbo.Document.StreamID = dbo.Stream.StreamID INNER JOIN
      dbo.SecretGread ON 
      dbo.Document.SecretGreadID = dbo.SecretGread.SecretGreadID INNER JOIN
      dbo.DocumentChildType ON 
      dbo.Document.DocumentTypeID = dbo.DocumentChildType.DocumentTypeID
	  



--**********************
CREATE VIEW dbo.VIEWDocumentTransact
--with    ENCRYPTION 
AS
SELECT dbo.Document.DocumentID, dbo.DocumentTransact.Idea, 
      dbo.Accounts_Users.UserName, dbo.Departments.DepartmentName, 
      dbo.DocumentTransact.StepID, dbo.Stream.Stream, dbo.Document.Author, 
      dbo.DocumentTransact.ManageID
FROM dbo.Document INNER JOIN
      dbo.DocumentTransact ON 
      dbo.Document.DocumentID = dbo.DocumentTransact.DocumentID INNER JOIN
      dbo.Accounts_Users ON 
      dbo.DocumentTransact.UserID = dbo.Accounts_Users.UserID INNER JOIN
      dbo.Departments ON 
      dbo.Accounts_Users.DepartmentID = dbo.Departments.DepartmentID INNER JOIN
      dbo.Stream ON dbo.Document.StreamID = dbo.Stream.StreamID
	
	
--**********************
CREATE VIEW dbo.VIEWsendArchives
--with    ENCRYPTION 
AS
SELECT d.DepartmentName, a.UserName, ds.RegisterID, ds.DocumentID, 
      ds.DocumentTitle, ds.DocumentFileNumber, ds.userid, ds.DepartmentID, 
      ds.SendDate, ds.ExigenceDegreeID, ds.draftDepartment, ds.DocumentNumber, 
      ds.sendNumber, ds.documentword, ds.WriterID, ds.SecretGreadid
FROM dbo.documentSendR ds INNER JOIN
      dbo.Departments d ON ds.DepartmentID = d.DepartmentID INNER JOIN
      dbo.Accounts_Users a ON ds.userid = a.UserID	  




	  

CREATE PROCEDURE Addb_unit_info(@Linkphone   varchar(20),@Operateman   varchar(20),@UnitRank   varchar(20),@UnitAddress   varchar(50),@UnitManager   varchar(20),@UnitLinkman   varchar(20),@UnitName   varchar(50),@ShortName   varchar(20),@UnitID Varchar(20)=null OutPut)
--with    ENCRYPTION 
AS
--获取ID号
--ID号为7位ID加3位顺机文字
Declare @MaxID varchar(20)
Declare @codeid integer
Declare @RadString char(3)
select  @RadString= substring(convert(char(36),newid()),1,3)
Begin Tran
select @MaxID=max(UnitID) from b_unit_info WITH(TABLOCK HOLDLOCK)
select @MaxID=isnull(@MaxID,'00000000000000000000')
select @codeid=convert(integer,substring(@MaxID,1,17))+1
/*信息ID*/

select @UnitID= case
	when @codeid<10 			then '0000000000000000'+str(@codeid,1)+@RadString
	when @codeid<100 			then '000000000000000'+str(@codeid,2)+@RadString
	when @codeid<1000 			then '00000000000000'+str(@codeid,3)+@RadString
	when @codeid<10000 			then '0000000000000'+str(@codeid,4)+@RadString
	when @codeid<100000 			then '000000000000'+str(@codeid,5)+@RadString
	when @codeid<1000000 			then '00000000000'+str(@codeid,6)+@RadString
	when @codeid<10000000			then '0000000000'+str(@codeid,7)+@RadString
	when @codeid<100000000 			then '000000000'+str(@codeid,8)+@RadString
	when @codeid<1000000000 		then '00000000'+str(@codeid,9)+@RadString
	when @codeid<10000000000 		then '0000000'+str(@codeid,10)+@RadString
	when @codeid<100000000000 		then '000000'+str(@codeid,11)+@RadString
	when @codeid<1000000000000 		then '00000'+str(@codeid,12)+@RadString
	when @codeid<10000000000000 		then '0000'+str(@codeid,13)+@RadString
	when @codeid<100000000000000 		then '000'+str(@codeid,14)+@RadString
	when @codeid<1000000000000000 		then '00'+str(@codeid,15)+@RadString
	when @codeid<10000000000000000 		then '0'+str(@codeid,16)+@RadString
	when @codeid<100000000000000000 	then ''+str(@codeid,17)+@RadString
	end
insert Into b_unit_info(Linkphone,Operateman,UnitRank,UnitAddress,UnitManager,UnitLinkman,UnitID,UnitName,ShortName) values (@Linkphone,@Operateman,@UnitRank,@UnitAddress,@UnitManager,@UnitLinkman,@UnitID,@UnitName,@ShortName)
if @@RowCount<>1 
Begin
    RollBack Tran
    return -1
End
Commit Tran
    return 0
	  



--*********************
CREATE   PROCEDURE departmentadmin   
@parentdepartmentID int,
@departmentname varchar(40),
@tel1 varchar(30),
@tel2  varchar(30),
@fax   varchar(30),
@ispublic bit,
@departmentid  int OUTPUT,
@IsStepPass bit
--with    ENCRYPTION 
AS
DECLARE @id  int
select @id=departmentid from departments where parentdepartmentid=@parentdepartmentID and
departmentname=@departmentname 
if @id is null

begin

insert into departments (parentdepartmentid,departmentname,tel1,tel2,fax,ispublic,IsStepPass)
values( @parentdepartmentID, @departmentname,@tel1, @tel2 ,@fax,@ispublic,@IsStepPass)
set @departmentid=@@identity

end

else 
begin
 set  @departmentid=-1
end	  
	  


--**********************
CREATE PROCEDURE insertnulldepartment  
@departmentname varchar(20),
@departmentid  int OUTPUT,
@ispublic  bit
--with    ENCRYPTION 
as insert into departments(departmentname ,ispublic)values(@departmentname,@ispublic)
set @departmentid=@@identity

 

--**********************
CREATE  proc transact @userid int, @stepid int
--with    ENCRYPTION 
as
/*
创建人:蒲丰
功能:列出当前节点中未处理信息
创建时间:2004.1.4
参数:@userid,当前用户,@stepid 文档正流向哪一步
*/
select d.documentid ,ty.documenttypename,sg.secretgreaddescription,ed.exigencedegreename 

from document d,transactusers t,documenttype ty,secretgread sg,exigencedegree ed

where 
d.DocumentTypeID=ty.DocumentTypeID
and d.secretgreadid=sg.secretgreadid
and d.exigencedegreeid=ed.exigencedegreeid
and t.userid=@userid  --当前用户
and d.documentid=t.documentid
and d.CurrentStepID=t.stepid
and t.stepid=@stepid   --当前正处于哪步
and CompleteSign=0
and t.userid not in 
( select userid from documenttransact 
       where documentid=d.documentid and stepid=@stepid and backstate=
  (select max(backstate) from documenttransact where documentid=d.documentid and stepid=@stepid)
)	  
	  



--**********************
CREATE FUNCTION IshaveAccessories(@DocumentID int)
RETURNS bit 
--with    ENCRYPTION 
as
BEGIN 
	if exists(select top 1   AccessoriesID FROM Accessories)
		return 1
	return 0
END