/******************************************************************************
** Name: usp_biz_ContractGetByID
** Desc: 删除项目（删除所有子表)
**
**
** Return Values:
**
** Parameters:
** Auth:
** Date:2008-7-13
*******************************************************************************/
ALTER proc usp_biz_ContractGetByID
@contractid uniqueidentifier
as
begin
select dbo.uf_GetContractPartyName(a.PartyA) PartyAName,
dbo.uf_GetContractPartyName(a.PartyB) PartyBName,
dbo.uf_GetContractPartyName(a.PartyC) PartyCName,
IsNull(P.code,'') + '.' + IsNull(p.PackageName,'') + IsNull(dbo.uf_GetDictName(69,c.ProjProperty),'') ProjectNAME,
dbo.uf_GetDictName(1,InvestMoneyType) InvestMoneyTypeName,
c.ProjectID,bidType.TypeID,c.Name ProjectName,c.Code ProjectCode,
dbo.uf_GetContractPartyDutyPersonName(PartyA) PartyADutyPersonName,
dbo.uf_GetContractPartyDutyPersonName(PartyB) PartyBDutyPersonName,
dbo.uf_GetDictName(1,FeeType) FeeTypeName,
*
from tbl_biz_contract a
left join tbl_Biz_InviteBidProjDetail b
on a.InviteBidTypeID = B.InviteBidTypeID
left join tbl_Biz_InviteBidType bidType
on bidType.InviteBidTypeID = a.InviteBidTypeID
left join tbl_cfg_Project c
on bidType.ProjectID = c.ProjectID
left join tbl_cfg_ProjectPackage p
on c.PackageID = p.PackageID
where a.contractid=@contractid
END
ALTER FUNCTION [dbo].[uf_GetContractPartyName] (
@ContractPartyID as uniqueidentifier
) RETURNS varchar(300)
AS
BEGIN
DECLARE @Ret varchar(300)
SELECT @Ret =PartyName
FROM tbl_biz_ContractPartyInfo
WHERE ContractPartyID = @ContractPartyID
RETURN @Ret
END
/*
获取字典数据名称
*/
ALTER FUNCTION [dbo].[uf_GetDictName] (
@ParentID AS int,
@id as int
) RETURNS varchar(100)
AS
BEGIN
DECLARE @Ret varchar(100)
SELECT @Ret =Name
FROM tblCommonDict
WHERE id = @id
RETURN @Ret
END
ALTER FUNCTION [dbo].[uf_GetContractPartyDutyPersonName] (
@ContractPartyID as uniqueidentifier
) RETURNS varchar(300)
AS
BEGIN
DECLARE @Ret varchar(300)
SELECT @Ret =DutyPersonName
FROM tbl_biz_ContractPartyInfo
WHERE ContractPartyID = @ContractPartyID
RETURN @Ret
END
