nelson.kai
/
OPMS
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
9 Commits
1 Branch
0 Tags
1.0 GiB
 
 
 
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
OPMS/02-系統分析、設計階段/DBScript/20140930/20140928~30異動和新增...

2763 lines
181 KiB
Raw Permalink Blame History

alter table OTB_SYS_Organization add LoginURL varchar(100) not null DEFAULT ''
alter table OTB_CRM_Customers add CustomerboName nvarchar(50) DEFAULT ''
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Customers_Add] Script Date: 09/28/2014 10:49:47 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Customers_Add]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Customers_Add]
GO
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Customers_Add]
@OrganizationID VarChar(50),
@CustomerID VarChar(50),
@CustomerName NVarChar(100),
@CustomerboName NVarChar(50),
@Email NVarChar(500),
@Principal NVarChar(10),
@Telephone VarChar(20),
@EXT VarChar(10),
@FAX VarChar(20),
@Address NVarChar(200),
@TaxNumber VarChar(20),
@Memo NTEXT,
@CreateUser VarChar(50),
@CreateDate DateTime,
@ModifyUser VarChar(50),
@ModifyDate DateTime
AS
INSERT INTO dbo.OTB_CRM_Customers
( OrganizationID ,
CustomerID ,
CustomerName ,
CustomerboName ,
Email ,
Principal ,
Telephone ,
EXT ,
FAX ,
Address ,
TaxNumber ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
VALUES ( @OrganizationID ,
@CustomerID ,
@CustomerName ,
@CustomerboName,
@Email ,
@Principal ,
@Telephone ,
@EXT ,
@FAX ,
@Address ,
@TaxNumber ,
@Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
)
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Customers_Upd] Script Date: 09/28/2014 10:50:08 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Customers_Upd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Customers_Upd]
GO
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Customers_Upd]
@OrganizationID VarChar(50),
@CustomerID VarChar(50),
@CustomerName NVarChar(100),
@CustomerboName NVarChar(50),
@Email NVarChar(500),
@Principal NVarChar(10),
@Telephone VarChar(20),
@EXT VarChar(10),
@FAX VarChar(20),
@Address NVarChar(200),
@TaxNumber VarChar(20),
@Memo NTEXT,
@CreateUser VarChar(50),
@CreateDate DateTime,
@ModifyUser VarChar(50),
@ModifyDate DateTime
AS
UPDATE dbo.OTB_CRM_Customers SET
CustomerName = @CustomerName ,
CustomerboName = @CustomerboName ,
Email = @Email ,
Principal = @Principal ,
Telephone = @Telephone ,
EXT = @EXT ,
FAX = @FAX ,
Address = @Address ,
TaxNumber = @TaxNumber ,
Memo = @Memo ,
CreateUser = @CreateUser ,
CreateDate = @CreateDate ,
ModifyUser = @ModifyUser ,
ModifyDate= @ModifyDate
WHERE
OrganizationID=@OrganizationID AND
CustomerID = @CustomerID
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Customers_GetModel] Script Date: 09/28/2014 10:50:33 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Customers_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Customers_GetModel]
GO
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Customers_GetModel]
@CustomerID VARCHAR(50) ,
@OrganizationID VarChar(50)
AS
SELECT* FROM dbo.OTB_CRM_Customers WHERE
CustomerID=@CustomerID AND OrganizationID=@OrganizationID
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Customers_GetListByID] Script Date: 09/28/2014 10:51:22 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Customers_GetListByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Customers_GetListByID]
GO
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Customers_GetListByID]
@StartRecordIndex int,
@EndRecordIndex int,
@CustomerName varchar(100),
@TaxNumber nvarchar(20),
--@CustomerFullName nvarchar(100),
@Telephone nvarchar(20),
@ContacterName nvarchar(10),
@Principal nvarchar(10),
@SortExpression nvarchar(500)
, @OrganizationID varchar(50)
AS
WITH CusList AS
(
select
CASE @SortExpression
WHEN N'CustomerName' THEN
(ROW_NUMBER() OVER(Order BY CustomerName))
WHEN N'CustomerName DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerName DESC))
WHEN N'CustomerboName' THEN
(ROW_NUMBER() OVER(Order BY CustomerboName))
WHEN N'CustomerboName DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerboName DESC))
WHEN N'CustomerID' THEN
(ROW_NUMBER() OVER(Order BY CustomerID))
WHEN N'CustomerID DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerID DESC))
--WHEN N'ContacterName' THEN
-- (ROW_NUMBER() OVER(Order BY ContacterName))
--WHEN N'ContacterName DESC' THEN
-- (ROW_NUMBER() OVER(Order BY ContacterName DESC))
--WHEN N'Title' THEN
-- (ROW_NUMBER() OVER(Order BY Title))
--WHEN N'Title DESC' THEN
-- (ROW_NUMBER() OVER(Order BY Title DESC))
WHEN N'Telephone' THEN
(ROW_NUMBER() OVER(Order BY Telephone))
WHEN N'Telephone DESC' THEN
(ROW_NUMBER() OVER(Order BY Telephone DESC))
WHEN N'Principal' THEN
(ROW_NUMBER() OVER(Order BY Principal))
WHEN N'Principal DESC' THEN
(ROW_NUMBER() OVER(Order BY Principal DESC))
WHEN N'FAX' THEN
(ROW_NUMBER() OVER(Order BY FAX))
WHEN N'FAX DESC' THEN
(ROW_NUMBER() OVER(Order BY FAX DESC))
WHEN N'TaxNumber' THEN
(ROW_NUMBER() OVER(Order BY TaxNumber))
WHEN N'TaxNumber DESC' THEN
(ROW_NUMBER() OVER(Order BY TaxNumber DESC))
WHEN N'Email' THEN
(ROW_NUMBER() OVER(Order BY Email))
WHEN N'Email DESC' THEN
(ROW_NUMBER() OVER(Order BY Email DESC))
ELSE
(ROW_NUMBER() OVER(Order BY CustomerID ))
END AS
RowId,
CustomerName,
CustomerboName,
CustomerID,
CAST('' AS NVARCHAR(10)) AS ContacterName ,
CAST('' AS VARCHAR(50)) AS Title,
Telephone,
Email,
TaxNumber
,OrganizationID
,FAX
,Principal
,0 AS orderByValue
,CAST('' AS VARCHAR(50))AS ContacterID
from OTB_CRM_Customers AS cus
where OrganizationID = @OrganizationID
AND (CustomerName LIKE @CustomerName OR @CustomerName='%%' OR @CustomerName IS NULL)
AND (TaxNumber LIKE @TaxNumber OR @TaxNumber='%%' OR @TaxNumber IS NULL)
AND (Telephone LIKE @Telephone OR @Telephone='%%' OR @Telephone IS NULL)
AND (Principal LIKE @Principal OR @Principal IS NULL OR @Principal='%%')
AND
(
@ContacterName='%%'
OR
EXISTS(
SELECT 0
FROM dbo.OTB_CRM_Contacters
WHERE CustomerID= cus.CustomerID
AND (ContacterName LIKE @ContacterName)
)
)
)
,ConList AS
(
SELECT
NULL AS CustomerName,
CustomerID AS CustomerID,
aa.ContacterName AS ContacterName,
aa.Title AS Title,
aa.Telephone AS Telephone,
CAST(aa.Email AS NVARCHAR(500)) AS Email,
NULL AS TaxNumber
,aa.OrganizationID AS OrganizationID
,aa.FAX AS FAX
,NULL AS Principal
,1 AS orderByValue
, aa.ContacterID
FROM dbo.OTB_CRM_Contacters AS aa
WHERE OrganizationID = @OrganizationID AND (aa.ContacterName LIKE @ContacterName OR @ContacterName IS NULL OR @ContacterName='%%')
)
SELECT
RowId,
CustomerName,
CustomerboName,
CustomerID,
ContacterName ,
Title,
Telephone,
Email,
TaxNumber
,OrganizationID
,FAX
,Principal
,orderByValue
,ContacterID
,PageType
FROM
(
SELECT
RowId,
cus.CustomerName,
cus.CustomerboName,
cus. CustomerID,
con.ContacterName ,
con. Title,
cus. Telephone,
cus. Email,
cus. TaxNumber
,cus.OrganizationID
,cus.FAX
,cus.Principal
,con.orderByValue
,con.ContacterID
--,COUNT(0) OVER ( PARTITION RowId ORDER BY RowId ) AS RowsCount
,'n'AS PageType
FROM
CusList AS cus
INNER JOIN
ConList AS con ON cus.CustomerID=con.CustomerID
UNION ALL
SELECT
RowId,
CustomerName,
CustomerboName,
CustomerID,
ContacterName ,
Title,
Telephone,
Email,
TaxNumber
,OrganizationID
,FAX
,Principal
,orderByValue
,ContacterID
,'m'AS PageType
FROM
CusList
)AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId,orderByValue
;
WITH CusListEXP AS
(
select
CASE @SortExpression
WHEN N'CustomerName' THEN
(ROW_NUMBER() OVER(Order BY CustomerName))
WHEN N'CustomerName DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerName DESC))
WHEN N'CustomerboName' THEN
(ROW_NUMBER() OVER(Order BY CustomerboName))
WHEN N'CustomerboName DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerboName DESC))
WHEN N'CustomerID' THEN
(ROW_NUMBER() OVER(Order BY CustomerID))
WHEN N'CustomerID DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerID DESC))
--WHEN N'ContacterName' THEN
-- (ROW_NUMBER() OVER(Order BY ContacterName))
--WHEN N'ContacterName DESC' THEN
-- (ROW_NUMBER() OVER(Order BY ContacterName DESC))
--WHEN N'Title' THEN
-- (ROW_NUMBER() OVER(Order BY Title))
--WHEN N'Title DESC' THEN
-- (ROW_NUMBER() OVER(Order BY Title DESC))
WHEN N'Telephone' THEN
(ROW_NUMBER() OVER(Order BY Telephone))
WHEN N'Telephone DESC' THEN
(ROW_NUMBER() OVER(Order BY Telephone DESC))
WHEN N'Principal' THEN
(ROW_NUMBER() OVER(Order BY Principal))
WHEN N'Principal DESC' THEN
(ROW_NUMBER() OVER(Order BY Principal DESC))
WHEN N'FAX' THEN
(ROW_NUMBER() OVER(Order BY FAX))
WHEN N'FAX DESC' THEN
(ROW_NUMBER() OVER(Order BY FAX DESC))
WHEN N'TaxNumber' THEN
(ROW_NUMBER() OVER(Order BY TaxNumber))
WHEN N'TaxNumber DESC' THEN
(ROW_NUMBER() OVER(Order BY TaxNumber DESC))
WHEN N'Email' THEN
(ROW_NUMBER() OVER(Order BY Email))
WHEN N'Email DESC' THEN
(ROW_NUMBER() OVER(Order BY Email DESC))
ELSE
(ROW_NUMBER() OVER(Order BY CustomerID ))
END AS
RowId,
CustomerName,
CustomerboName,
CustomerID,
CAST('' AS NVARCHAR(10)) AS ContacterName ,
CAST('' AS VARCHAR(50)) AS Title,
Telephone,
Email,
TaxNumber
,OrganizationID
,FAX
,Principal
,0 AS orderByValue
,CAST('' AS VARCHAR(50))AS ContacterID
from OTB_CRM_Customers AS cus
where OrganizationID = @OrganizationID
AND (CustomerName LIKE @CustomerName OR @CustomerName='%%' OR @CustomerName IS NULL)
AND (TaxNumber LIKE @TaxNumber OR @TaxNumber='%%' OR @TaxNumber IS NULL)
AND (Telephone LIKE @Telephone OR @Telephone='%%' OR @Telephone IS NULL)
AND (Principal LIKE @Principal OR @Principal IS NULL OR @Principal='%%')
AND
(
@ContacterName='%%'
OR
EXISTS(
SELECT 0
FROM dbo.OTB_CRM_Contacters
WHERE CustomerID= cus.CustomerID
AND (ContacterName LIKE @ContacterName)
)
)
)
,ConListEXP AS
(
SELECT
NULL AS CustomerName,
CustomerID AS CustomerID,
aa.ContacterName AS ContacterName,
aa.Title AS Title,
aa.Telephone AS Telephone,
CAST(aa.Email AS NVARCHAR(500)) AS Email,
NULL AS TaxNumber
,aa.OrganizationID AS OrganizationID
,aa.FAX AS FAX
,NULL AS Principal
,1 AS orderByValue
, aa.ContacterID
FROM dbo.OTB_CRM_Contacters AS aa
WHERE OrganizationID = @OrganizationID AND (aa.ContacterName LIKE @ContacterName OR @ContacterName IS NULL OR @ContacterName='%%')
)
SELECT
RowId AS '項次',
CustomerName AS '公司名稱',
CustomerboName AS '公司簡稱',
ContacterName AS '聯絡人姓名',
Title AS '職稱',
Telephone '公司電話',
Email 'E-mail',
TaxNumber AS '公司統編'
,FAX AS '傳真號碼'
,Principal AS '負責人姓名'
FROM
(
SELECT
RowId,
cus.CustomerName,
cus.CustomerboName,
cus. CustomerID,
con.ContacterName ,
con. Title,
cus. Telephone,
cus. Email,
cus. TaxNumber
,cus.OrganizationID
,cus.FAX
,cus.Principal
,con.orderByValue
,con.ContacterID
--,COUNT(0) OVER ( PARTITION RowId ORDER BY RowId ) AS RowsCount
,'n'AS PageType
FROM
CusListEXP AS cus
INNER JOIN
ConListEXP AS con ON cus.CustomerID=con.CustomerID
UNION ALL
SELECT
RowId,
CustomerName,
CustomerboName,
CustomerID,
ContacterName ,
Title,
Telephone,
Email,
TaxNumber
,OrganizationID
,FAX
,Principal
,orderByValue
,ContacterID
,'m'AS PageType
FROM
CusListEXP
)AS AA
--WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId,orderByValue
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_SYS_Organization_GetModel] Script Date: 09/28/2014 15:21:21 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SYS_Organization_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_SYS_Organization_GetModel]
GO
------------------------------------
--用途：得到實體對象的詳細信息
--項目名稱：
--Create：Jack
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_GetModel]
@OrganizationID NVARCHAR(200)
AS
SELECT
OrganizationID ,--組織編號
OrganizationName,--組織名稱
OwnerName ,--負責人姓名
Email ,--使用者E-Mail
ContectTEL ,--聯絡電話
ContectExt ,--聯絡分機
ContectFax ,--聯絡傳真
ContectCell ,--聯絡手機
[Address] ,--住址
Files ,--附件
EffectiveSD ,--生效開始日期
EffectiveED ,--生效結束日期
Effective ,--是否有效
Memo ,--備註
CreateUser ,--建立人員帳號
CreateDate ,--建立日期
ModifyUser ,--修改人員帳號
ModifyDate ,--修改日期
IsProxy,
ParentID,
ProxyED,
ProxySD,
Url,
LoginURL,
(SELECT Email FROM dbo.OTB_SYS_Members WHERE OrganizationID=@OrganizationID AND MemberID='admin') AS Email_PM,
(SELECT DISTINCT ModuleID+';' FROM dbo.OTB_SYS_ModuleList WHERE OrganizationID=@OrganizationID AND Effective='Y' FOR XML PATH('')) AS ModuleIDList,
(SELECT DISTINCT ProgramID+';' FROM dbo.OTB_SYS_ProgramList WHERE OrganizationID=@OrganizationID AND Effective='Y' FOR XML PATH('')) AS ProgramIDList
from dbo.OTB_SYS_Organization
where OrganizationID=@OrganizationID
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_SYS_Organization_GetModelByOrganizationName] Script Date: 09/28/2014 15:27:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SYS_Organization_GetModelByOrganizationName]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_SYS_Organization_GetModelByOrganizationName]
GO
------------------------------------
--用途：得到實體對象的詳細信息
--項目名稱：
--Create：Jack
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_GetModelByOrganizationName]
@OrganizationName NVARCHAR(200)
AS
SELECT
OrganizationID ,--組織編號
OrganizationName,--組織名稱
OwnerName ,--負責人姓名
Email ,--使用者E-Mail
ContectTEL ,--聯絡電話
ContectExt ,--聯絡分機
ContectFax ,--聯絡傳真
ContectCell ,--聯絡手機
[Address] ,--住址
Files ,--附件
LoginURL,
EffectiveSD ,--生效開始日期
EffectiveED ,--生效結束日期
Effective ,--是否有效
Memo ,--備註
CreateUser ,--建立人員帳號
CreateDate ,--建立日期
ModifyUser ,--修改人員帳號
ModifyDate --修改日期
from dbo.OTB_SYS_Organization
where OrganizationName=@OrganizationName
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Contacters_Update] Script Date: 09/28/2014 15:57:37 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Contacters_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Contacters_Update]
GO
------------------------------------
--用途：修改一???
--?目名?：
--?明：
--??：2014/8/27 下午 04:56:54
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Contacters_Update]
@OrganizationID varchar(50),
@CustomerID varchar(50),
@ContacterID varchar(50),
@ContacterAccound varchar(50),
@Password varchar(100),
@ContacterName nvarchar(10),
@Telephone varchar(20),
@TEL varchar(20),
@EXT varchar(10),
@FAX varchar(20),
@Email varchar(200),
@Title varchar(50),
@Birthday datetime,
@Memo ntext,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime,
@Status char(3)
AS
IF @Status='Add'
BEGIN
INSERT INTO [OTB_CRM_Contacters](
[OrganizationID],[CustomerID],[ContacterID],[ContacterAccound],[Password],[ContacterName],[Telephone],[EXT],[FAX],[Email],[Title],[Birthday],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],TEL
)VALUES(
@OrganizationID,@CustomerID,@ContacterID,@ContacterAccound,@Password,@ContacterName,@Telephone,@EXT,@FAX,@Email,@Title,@Birthday,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),@TEL
)
END
ELSE
BEGIN
UPDATE [OTB_CRM_Contacters] SET
[ContacterAccound] = @ContacterAccound,[ContacterName] = @ContacterName,[Telephone] = @Telephone,[EXT] = @EXT,[FAX] = @FAX,[Email] = @Email,[Title] = @Title,[Birthday] = @Birthday,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),TEL=@TEL
WHERE OrganizationID=@OrganizationID and CustomerID=@CustomerID and ContacterID=@ContacterID
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_SYS_Organization_ADD] Script Date: 09/28/2014 16:36:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SYS_Organization_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_SYS_Organization_ADD]
GO
--用途：新增一筆資料
--項目名稱：
--Create：Jack
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_ADD]
@OrganizationID Varchar (36 ),--組織編號
@OrganizationName NVarchar(200),--組織名稱
@OwnerName NVarchar(50 ),--負責人姓名
@Email NVarchar(200),--組織E-Mail
@ContectTEL Varchar (50 ),--聯絡電話
@ContectExt Varchar (20 ),--聯絡分機
@ContectFax Varchar (50 ),--聯絡傳真
@ContectCell Varchar (50 ),--聯絡手機
@Address NVarchar(500),--住址
@Files Varchar (36 ),--附件
@EffectiveSD DateTime ,--生效開始日期
@EffectiveED DateTime ,--生效結束日期
@Effective Char (1 ),--是否有效
@Memo Text ,--備註
@CreateUser Varchar (50 ),--建立人員帳號
@CreateDate DateTime ,--建立日期
@ModifyUser Varchar (50 ),--修改人員帳號
@ModifyDate DateTime ,--修改日期
@MemberID VARCHAR(50), --登錄帳號
@MemberPwd VARCHAR(50), --登錄密碼
@ProxyED DateTime, --代理結束日期
@ProxySD DateTime, --代理開始日期
@ParentID VARCHAR(50), --上級組織
@ModuleIDList NVarChar(max),--模組代碼
@ProgramIDList NVarChar(max),--程式代碼
@IsProxy CHAR(1), --是否代理商
@OrganizationIDOld VarChar(50), --
@MemberIDNow VarChar(50), --當前登錄者
@Url VarChar(200), --當前登錄者
@LoginURL VarChar(200), --當前登錄者
@Email_PM NVarchar(200) --使用者E-Mail
AS
Begin TRANSACTION
INSERT INTO dbo.OTB_SYS_Organization
(
OrganizationID
,OrganizationName
,OwnerName
,Email
,ContectTEL
,ContectExt
,ContectFax
,ContectCell
,Address
,Files
,EffectiveSD
,EffectiveED
,Effective
,Memo
,CreateUser
,CreateDate
,ModifyUser
,ModifyDate
,ProxyED
,ProxySD
,ParentID
,IsProxy
,Url
,LoginURL
)
VALUES (
@OrganizationID
,@OrganizationName
,@OwnerName
,@Email
,@ContectTEL
,@ContectExt
,@ContectFax
,@ContectCell
,@Address
,@Files
,@EffectiveSD
,@EffectiveED
,@Effective
,@Memo
,@CreateUser
,GETDATE()
,@CreateUser
,GETDATE()
,@ProxyED
,@ProxySD
,@ParentID
,@IsProxy
,@Url
,@LoginURL
)
INSERT INTO dbo.OTB_SYS_FilterDataSql
( OrganizationID ,
SQLGuid ,
Name ,
SQL ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
Memo
)
SELECT
@OrganizationID ,
SQLGuid ,
Name ,
SQL ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
Memo
FROM dbo.OTB_SYS_FilterDataSql WHERE OrganizationID=@OrganizationIDOld
INSERT INTO dbo.OTB_SYS_FilterDataSqlDetail
( OrganizationID ,
Guid ,
SQLGuid ,
DBFeild ,
ShowFeild ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
IsPk ,
OrderByValue
)
SELECT
@OrganizationID ,
NEWID() ,
SQLGuid ,
DBFeild ,
ShowFeild ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
IsPk ,
OrderByValue
FROM dbo.OTB_SYS_FilterDataSqlDetail WHERE OrganizationID=@OrganizationIDOld
--IF REPLACE(@ModuleIDList,';','')!=''
--BEGIN
INSERT INTO dbo.OTB_SYS_ModuleList
( OrganizationID ,
ModuleID ,
LanguageID ,
ModuleName ,
ParentID ,
OrderByValue ,
BackgroundCSS ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
ModuleID ,
LanguageID ,
ModuleName ,
ParentID ,
OrderByValue ,
BackgroundCSS ,
Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_ModuleList
WHERE OrganizationID=@OrganizationIDOld --AND CHARINDEX(';'+ModuleID+';',@ModuleIDList)>0
--END
IF REPLACE(@ProgramIDList,';','')!=''
BEGIN
INSERT INTO dbo.OTB_SYS_ProgramList
( OrganizationID ,
ProgramID ,
LanguageID ,
ProgramName ,
ModuleID ,
FilePath ,
ImgPath ,
AllowRight ,
OrderByValue ,
ProgramType ,
BackgroundCSS ,
GroupTag ,
Effective ,
ShowInList ,
MainTableName ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
ProgramID ,
LanguageID ,
ProgramName ,
ModuleID ,
FilePath ,
ImgPath ,
AllowRight ,
OrderByValue ,
ProgramType ,
BackgroundCSS ,
GroupTag ,
Effective ,
ShowInList ,
MainTableName ,
Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_ProgramList
WHERE CHARINDEX(';'+ProgramID+';',@ProgramIDList)>0 AND OrganizationID=@OrganizationIDOld
INSERT INTO dbo.OTB_SYS_Authorize
( OrganizationID ,
RuleID ,
ProgramID ,
AllowRight ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
'admin' ,
ProgramID ,
(select dbo.OFN_AVA_RemoveStringduplicate((SELECT (SELECT ltrim(rtrim(AllowRight)) +'|' FROM dbo.OTB_SYS_Authorize WHERE OrganizationID=@OrganizationIDOld
AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow AND ProgramID=A.ProgramID) FOR XML PATH(''))),'|',1) )
,
'' ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_Authorize AS A
WHERE OrganizationID=@OrganizationIDOld
AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow)
GROUP BY A.ProgramID
ORDER BY ProgramID
END
INSERT INTO dbo.OTB_SYS_Members
( OrganizationID ,
MemberID ,
Password ,
MemberName ,
Email ,
GoogleAccount ,
ContectTEL ,
ContectExt ,
ContectFax ,
ContectCell ,
Address ,
BirthDate ,
ArriveDate ,
EmergencyContect ,
EmergencyTEL ,
EmergencyExt ,
EmergencyFax ,
EmergencyCell ,
EmergencyEMail ,
JobTitle ,
JobClass ,
ChiefID ,
DepartmentID ,
LeaveDate ,
Effective ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
CalColor
)
VALUES ( @OrganizationID , -- OrganizationID - varchar(50)
@MemberID , -- MemberID - varchar(50)
@MemberPwd , -- Password - varchar(100)
'系統超級管理員' , -- MemberName - nvarchar(100)
@Email_PM , -- Email - nvarchar(200)
'' , -- GoogleAccount - varchar(200)
'' , -- ContectTEL - varchar(50)
'' , -- ContectExt - varchar(50)
'' , -- ContectFax - varchar(50)
'' , -- ContectCell - varchar(50)
N'' , -- Address - nvarchar(500)
null , -- BirthDate - datetime
null , -- ArriveDate - datetime
N'' , -- EmergencyContect - nvarchar(100)
'' , -- EmergencyTEL - varchar(50)
'' , -- EmergencyExt - varchar(20)
'' , -- EmergencyFax - varchar(50)
'' , -- EmergencyCell - varchar(50)
N'' , -- EmergencyEMail - nvarchar(200)
N'' , -- JobTitle - nvarchar(10)
'' , -- JobClass - varchar(200)
'' , -- ChiefID - varchar(50)
'' , -- DepartmentID - varchar(10)
null , -- LeaveDate - datetime
'Y' , -- Effective - char(1)
N'' , -- Memo - nvarchar(max)
@CreateUser, -- CreateUser - varchar(50)
GETDATE() , -- CreateDate - datetime
@CreateUser , -- ModifyUser - varchar(50)
GETDATE() , -- ModifyDate - datetime
N'#00008B' -- CalColor - nvarchar(20)
)
INSERT INTO dbo.OTB_SYS_MembersToRule
( OrganizationID ,
MemberID ,
RuleID ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
VALUES ( @OrganizationID , -- OrganizationID - varchar(50)
@MemberID , -- MemberID - varchar(50)
'admin' , -- RuleID - varchar(20)
'' , -- Memo - text
@CreateUser , -- CreateUser - varchar(50)
GETDATE() , -- CreateDate - datetime
@CreateUser , -- ModifyUser - varchar(50)
GETDATE() -- ModifyDate - datetime
)
INSERT INTO dbo.OTB_SYS_Rules
( OrganizationID ,
RuleID ,
RuleName ,
DelStatus ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
VALUES ( @OrganizationID , -- OrganizationID - varchar(50)
@MemberID , -- RuleID - varchar(20)
'Apadmin' , -- RuleName - nvarchar(200)
'N' , -- DelStatus - char(1)
'' , -- Memo - text
@CreateUser , -- CreateUser - varchar(50)
GETDATE() , -- CreateDate - datetime
@CreateUser, -- ModifyUser - varchar(50)
GETDATE() -- ModifyDate - datetime
)
INSERT INTO dbo.OTB_SYS_ArgumentClass
( OrganizationID ,
ArgumentClassID ,
LanguageID ,
ArgumentClassName ,
OrderByValue ,
Effective ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate ,
DelStatus
)
SELECT
@OrganizationID ,
ArgumentClassID ,
LanguageID ,
ArgumentClassName ,
OrderByValue ,
Effective ,
Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE() ,
DelStatus
FROM dbo.OTB_SYS_ArgumentClass WHERE OrganizationID=@OrganizationIDOld
INSERT INTO dbo.OTB_SYS_Arguments
( OrganizationID ,
ArgumentClassID ,
ArgumentID ,
LanguageID ,
ArgumentValue ,
OrderByValue ,
LevelOfArgument ,
ParentArgument ,
DelStatus ,
Effective ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
ArgumentClassID ,
ArgumentID ,
LanguageID ,
ArgumentValue ,
OrderByValue ,
LevelOfArgument ,
ParentArgument ,
DelStatus ,
Effective ,
Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_Arguments WHERE OrganizationID=@OrganizationIDOld
INSERT INTO dbo.OTB_SYS_SystemSetting
( OrganizationID ,
SettingItem ,
SettingDescription ,
SettingValue ,
Memo ,
Effective ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
SettingItem ,
SettingDescription ,
SettingValue ,
Memo ,
Effective ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_SystemSetting WHERE OrganizationID=@OrganizationIDOld
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_SYS_Organization_Update] Script Date: 09/28/2014 16:37:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_SYS_Organization_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_SYS_Organization_Update]
GO
------------------------------------
--用途：修改資料
--項目名稱：
--Create：Jack
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_SYS_Organization_Update]
@OrganizationID Varchar (36 ),--組織編號
@OrganizationName NVarchar(200),--組織名稱
@OwnerName NVarchar(50 ),--負責人姓名
@Email NVarchar(200),--使用者E-Mail
@ContectTEL Varchar (50 ),--聯絡電話
@ContectExt Varchar (20 ),--聯絡分機
@ContectFax Varchar (50 ),--聯絡傳真
@ContectCell Varchar (50 ),--聯絡手機
@Address NVarchar(500),--住址
@Files Varchar (36 ),--附件
@EffectiveSD DateTime ,--生效開始日期
@EffectiveED DateTime ,--生效結束日期
@Effective Char (1 ),--是否有效
@Memo Text ,--備註
@CreateUser Varchar (50 ),--建立人員帳號
@CreateDate DateTime ,--建立日期
@ModifyUser Varchar (50 ),--修改人員帳號
@ModifyDate DateTime ,--修改日期
@MemberID VARCHAR(50), --登錄帳號
@MemberPwd VARCHAR(50), --登錄密碼
@ProxyED DateTime, --代理結束日期
@ProxySD DateTime, --代理開始日期
@ParentID VARCHAR(50), --上級組織
@ModuleIDList NVarChar(max),--模組代碼
@ProgramIDList NVarChar(max),--程式代碼
@IsProxy CHAR(1), --是否代理商
@OrganizationIDOld VarChar(50), --
@MemberIDNow VarChar(50), --當前登錄者
@Url VarChar(200), --當前登錄者
@LoginURL VarChar(200), --當前登錄者
@Email_PM NVarchar(200) --使用者E-Mail
AS
Begin TRANSACTION
IF @OrganizationID!=@ParentID
BEGIN
UPDATE dbo.OTB_SYS_Organization SET
OrganizationName=@OrganizationName,
OwnerName =@OwnerName ,
Email =@Email ,
ContectTEL =@ContectTEL ,
ContectExt =@ContectExt ,
ContectFax =@ContectFax ,
ContectCell =@ContectCell ,
Address =@Address ,
Files =@Files ,
EffectiveSD =@EffectiveSD ,
EffectiveED =@EffectiveED ,
Effective =@Effective ,
Memo =@Memo ,
CreateUser =@CreateUser ,
CreateDate =@CreateDate ,
ModifyUser =@ModifyUser ,
ModifyDate =@ModifyDate ,
ProxyED=@ProxyED,
ProxySD=@ProxySD,
ParentID=@ParentID,
IsProxy=@IsProxy,
Url=@Url,
LoginURL=@LoginURL
WHERE
OrganizationID=@OrganizationID
END
ELSE
BEGIN
UPDATE dbo.OTB_SYS_Organization SET
OrganizationName=@OrganizationName,
OwnerName =@OwnerName ,
Email =@Email ,
ContectTEL =@ContectTEL ,
ContectExt =@ContectExt ,
ContectFax =@ContectFax ,
ContectCell =@ContectCell ,
Address =@Address ,
Files =@Files ,
EffectiveSD =@EffectiveSD ,
EffectiveED =@EffectiveED ,
Effective =@Effective ,
Memo =@Memo ,
CreateUser =@CreateUser ,
CreateDate =@CreateDate ,
ModifyUser =@ModifyUser ,
ModifyDate =@ModifyDate ,
ProxyED=@ProxyED,
ProxySD=@ProxySD,
ParentID='',
IsProxy=@IsProxy,
Url=@Url,
LoginURL=@LoginURL
WHERE
OrganizationID=@OrganizationID
END
IF REPLACE(@ProgramIDList,';','')!='' AND @OrganizationID!=@OrganizationIDOld
BEGIN
DELETE dbo.OTB_SYS_ProgramList WHERE OrganizationID=@OrganizationID
INSERT INTO dbo.OTB_SYS_ProgramList
( OrganizationID ,
ProgramID ,
LanguageID ,
ProgramName ,
ModuleID ,
FilePath ,
ImgPath ,
AllowRight ,
OrderByValue ,
ProgramType ,
BackgroundCSS ,
GroupTag ,
Effective ,
ShowInList ,
MainTableName ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
ProgramID ,
LanguageID ,
ProgramName ,
ModuleID ,
FilePath ,
ImgPath ,
AllowRight ,
OrderByValue ,
ProgramType ,
BackgroundCSS ,
GroupTag ,
Effective ,
ShowInList ,
MainTableName ,
Memo ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_ProgramList
WHERE CHARINDEX(';'+ProgramID+';',@ProgramIDList)>0 AND OrganizationID=@OrganizationIDOld
END
IF @MemberPwd!=''
BEGIN
UPDATE dbo.OTB_SYS_Members SET [Password]=@MemberPwd,Email=@Email_PM WHERE MemberID=@MemberID
END
ELSE
BEGIN
UPDATE dbo.OTB_SYS_Members SET Email=@Email_PM WHERE MemberID=@MemberID
END
DELETE dbo.OTB_SYS_Authorize WHERE OrganizationID=@OrganizationID AND RuleID='admin'
IF REPLACE(@ProgramIDList,';','')!=''
BEGIN
INSERT INTO dbo.OTB_SYS_Authorize
( OrganizationID ,
RuleID ,
ProgramID ,
AllowRight ,
Memo ,
CreateUser ,
CreateDate ,
ModifyUser ,
ModifyDate
)
SELECT
@OrganizationID ,
'admin' ,
ProgramID ,
(select dbo.OFN_AVA_RemoveStringduplicate((SELECT (SELECT ltrim(rtrim(AllowRight)) +'|' FROM dbo.OTB_SYS_Authorize WHERE OrganizationID=@OrganizationIDOld
AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow AND ProgramID=A.ProgramID) FOR XML PATH(''))),'|',1) )
,
'' ,
@CreateUser ,
GETDATE() ,
@CreateUser ,
GETDATE()
FROM dbo.OTB_SYS_Authorize AS A
WHERE OrganizationID=@OrganizationIDOld
AND RuleID IN (SELECT RuleID FROM dbo.OTB_SYS_MembersToRule WHERE OrganizationID=@OrganizationIDOld AND MemberID=@MemberIDNow)
GROUP BY A.ProgramID
ORDER BY ProgramID
END
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetList] Script Date: 09/28/2014 17:20:26 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetList]
GO
------------------------------------
--功能：查詢記錄信息
--項目名稱：
--創建人： Jack
--時間：2014/07/23
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@ProjectNumber varchar(50),
@CustomerID nvarchar(50),
@ProjectEName nvarchar(200),
@ProjectCName nvarchar(200),
@OrganizationID NVarChar(50), --客戶編號
@ProjectManager nvarchar(50),
@SortExpression nvarchar(500),
@ProjectStatus varchar(5)
AS
SELECT
RowId,
ProjectNumber ,--專案編號
CustomerID ,--客戶編號
ProjectEName ,--專案名稱
ProjectCName ,--專案中文名稱
ProjectManager,--專案經理
PStartDate ,--預估開始日期
PDeadLine ,--預估截止日期
ProjectStatus ,--專案狀態
ModifyDate --最新修改時間
,CustomerName
,OrganizationID
,PrjAvgProgress--平均程式開發進度
,PrjWorkCount --工作數量
FROM
(
SELECT
CASE @SortExpression WHEN N'ProjectNumber' THEN
(ROW_NUMBER() OVER(Order BY ProjectNumber))
WHEN N'ProjectNumber DESC' THEN
(ROW_NUMBER() OVER(Order BY ProjectNumber DESC))
WHEN N'CustomerID' THEN
(ROW_NUMBER() OVER(Order BY a.CustomerID))
WHEN N'CustomerID DESC' THEN
(ROW_NUMBER() OVER(Order BY a.CustomerID DESC))
WHEN N'CustomerName' THEN
(ROW_NUMBER() OVER(Order BY CustomerName))
WHEN N'CustomerName DESC' THEN
(ROW_NUMBER() OVER(Order BY CustomerName DESC))
WHEN N'ProjectCName' THEN
(ROW_NUMBER() OVER(Order BY ProjectCName))
WHEN N'ProjectCName DESC' THEN
(ROW_NUMBER() OVER(Order BY ProjectCName DESC))
WHEN N'ProjectEName' THEN
(ROW_NUMBER() OVER(Order BY ProjectEName))
WHEN N'ProjectEName DESC' THEN
(ROW_NUMBER() OVER(Order BY ProjectEName DESC))
WHEN N'ProjectManager' THEN
(ROW_NUMBER() OVER(Order BY ProjectManager))
WHEN N'ProjectManager DESC' THEN
(ROW_NUMBER() OVER(Order BY ProjectManager DESC))
WHEN N'PStartDate' THEN
(ROW_NUMBER() OVER(Order BY PStartDate))
WHEN N'PStartDate DESC' THEN
(ROW_NUMBER() OVER(Order BY PStartDate DESC))
WHEN N'PDeadLine' THEN
(ROW_NUMBER() OVER(Order BY PDeadLine))
WHEN N'PDeadLine DESC' THEN
(ROW_NUMBER() OVER(Order BY PDeadLine DESC))
WHEN N'PrjAvgProgress' THEN
(ROW_NUMBER() OVER(Order BY PrjAvgProgress))
WHEN N'PrjAvgProgress DESC' THEN
(ROW_NUMBER() OVER(Order BY PrjAvgProgress DESC))
WHEN N'PrjWorkCount' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkCount))
WHEN N'PrjWorkCount DESC' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkCount DESC))
WHEN N'ProjectStatus' THEN
(ROW_NUMBER() OVER(Order BY ProjectStatus))
WHEN N'ProjectStatus DESC' THEN
(ROW_NUMBER() OVER(Order BY ProjectStatus DESC))
ELSE
(ROW_NUMBER() OVER(Order BY a.ModifyDate DESC))
END AS RowId,
ProjectNumber ,--專案編號
a.CustomerID AS CustomerID ,--客戶編號
ProjectEName ,--專案名稱
ProjectCName ,--專案中文名稱
b.MemberName AS ProjectManager,--專案經理
PStartDate ,--預估開始日期
PDeadLine ,--預估截止日期
ProjectStatus ,--專案狀態
a.ModifyDate AS ModifyDate --最新修改時間
,CustomerName
,a.OrganizationID AS OrganizationID
,PrjAvgProgress--平均程式開發進度
,PrjWorkCount --工作數量
FROM dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_SYS_Members AS b ON a.ProjectManager=b.MemberID--AS A ON A.DepartmentID=B.DepartmentID
LEFT JOIN dbo.OTB_CRM_Customers AS c ON a.CustomerID=c.CustomerID
--LEFT JOIN dbo.OTB_SYS_Arguments AS e ON d.PrgWorkType=e.ArgumentID
WHERE
(ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
AND (CustomerName LIKE @CustomerID OR @CustomerID='%%')
AND (ProjectEName LIKE @ProjectEName OR @ProjectEName='%%')
AND (ProjectManager = @ProjectManager OR @ProjectManager='')
AND (ProjectCName LIKE @ProjectCName OR @ProjectCName='%%')
AND (ProjectStatus = @ProjectStatus OR @ProjectCName='')
AND a.OrganizationID=@OrganizationID
--AND (e.ArgumentClassID='PrgWKTP' )
) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId
SELECT
ProjectNumber AS'專案編號' ,--專案編號
--a.CustomerID AS CustomerID ,--客戶編號
ProjectEName AS '專案名稱',--專案名稱
ProjectCName AS '專案中文名稱' ,--專案中文名稱
b.MemberName AS '專案經理',--專案經理
PStartDate AS '預估開始日期',--預估開始日期
PDeadLine AS '預估截止日期',--預估截止日期
a.ActualStartDate AS '實際開始日期',--預估開始日期
a.ActualDeadLine AS '實際截止日期',--預估截止日期
ProjectStatus AS '專案狀態',--專案狀態
--a.ModifyDate AS ModifyDate --最新修改時間
CustomerName AS '客戶名稱'
--,a.OrganizationID AS OrganizationID
,CONVERT(nvarchar(2000), ISNULL(PrjAvgProgress,0))+'%' AS '平均程式開發進度'--平均程式開發進度
,PrjWorkCount AS'工作數量'--工作數量
FROM dbo.OTB_PRJ_ProjectInfo AS a LEFT JOIN dbo.OTB_SYS_Members AS b ON a.ProjectManager=b.MemberID--AS A ON A.DepartmentID=B.DepartmentID
LEFT JOIN dbo.OTB_CRM_Customers AS c ON a.CustomerID=c.CustomerID
--LEFT JOIN dbo.OTB_SYS_Arguments AS e ON d.PrgWorkType=e.ArgumentID
WHERE
(ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
AND (CustomerName LIKE @CustomerID OR @CustomerID='%%')
AND (ProjectEName LIKE @ProjectEName OR @ProjectEName='%%')
AND (ProjectManager = @ProjectManager OR @ProjectManager='')
AND (ProjectCName LIKE @ProjectCName OR @ProjectCName='%%')
AND (ProjectStatus = @ProjectStatus OR @ProjectStatus='')
AND a.OrganizationID=@OrganizationID
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_ProjectInfo_GetListCount] Script Date: 09/28/2014 17:20:57 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_ProjectInfo_GetListCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetListCount]
GO
------------------------------------
--功能：查詢記錄筆數
--項目名稱：
--創建人： Jack
--時間：2014/07/23
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_ProjectInfo_GetListCount]
@ProjectNumber NVarChar(50), --專案編號
@CustomerID NVarChar(50), --客戶編號
@ProjectEName NVarChar(200), --專案名稱
@ProjectCName NVarChar(200), --專案中文名稱
@OrganizationID NVarChar(50), --客戶編號
@ProjectManager NVarChar(50), --專案經理
@ProjectStatus VarChar(5) --專案狀態
AS
SELECT COUNT(0)FROM dbo.OTB_PRJ_ProjectInfo AS a
LEFT JOIN dbo.OTB_CRM_Customers AS csm ON a.CustomerID = csm.CustomerID
WHERE
(ProjectNumber LIKE @ProjectNumber OR @ProjectNumber='%%')
AND (CustomerName LIKE @CustomerID OR @CustomerID='%%')
AND (ProjectEName LIKE @ProjectEName OR @ProjectEName='%%')
AND (ProjectManager = @ProjectManager OR @ProjectManager='')
AND (ProjectCName LIKE @ProjectCName OR @ProjectCName='%%')
AND (ProjectStatus = @ProjectStatus OR @ProjectStatus='')
AND a.OrganizationID=@OrganizationID
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_CRM_Contacters_UpdatePassWord] Script Date: 09/29/2014 11:34:38 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_CRM_Contacters_UpdatePassWord]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_CRM_Contacters_UpdatePassWord]
GO
CREATE PROCEDURE [dbo].[OSP_OTB_CRM_Contacters_UpdatePassWord]
@OrganizationID VarChar(50),
@ContacterID VarChar(50),
@PassWord VarChar(50)
AS
UPDATE dbo.OTB_CRM_Contacters
SET
[Password] = @PassWord
WHERE
ContacterID =@ContacterID AND OrganizationID=@OrganizationID
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjRule] Script Date: 09/30/2014 09:05:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjRule]') AND type in (N'U'))
DROP TABLE [dbo].[OTB_PRJ_PrjRule]
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjRuleWorkType] Script Date: 09/30/2014 09:05:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjRuleWorkType]') AND type in (N'U'))
DROP TABLE [dbo].[OTB_PRJ_PrjRuleWorkType]
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjType] Script Date: 09/30/2014 09:05:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjType]') AND type in (N'U'))
DROP TABLE [dbo].[OTB_PRJ_PrjType]
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjWorkType] Script Date: 09/30/2014 09:05:15 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjWorkType]') AND type in (N'U'))
DROP TABLE [dbo].[OTB_PRJ_PrjWorkType]
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjWorkType] Script Date: 09/30/2014 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjWorkType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OTB_PRJ_PrjWorkType](
[OrganizationID] [varchar](50) NOT NULL,
[PrjWorkTypeID] [char](36) NOT NULL,
[PrjWorkTypeEName] [nvarchar](20) NULL,
[PrjWorkTypeCName] [nvarchar](20) NULL,
[ParentID] [char](36) NULL,
[IsAutoTask] [char](1) NULL,
[PrjWorkTypeRange] [nvarchar](20) NULL,
[Memo] [text] NULL,
[CreateUser] [varchar](50) NULL,
[CreateDate] [datetime] NULL,
[ModifyUser] [varchar](50) NULL,
[ModifyDate] [datetime] NULL,
[PrjTypeID] [char](36) NULL,
CONSTRAINT [PK_OTB_PRJ_PrjWorkType] PRIMARY KEY CLUSTERED
(
[OrganizationID] ASC,
[PrjWorkTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjType] Script Date: 09/30/2014 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OTB_PRJ_PrjType](
[OrganizationID] [varchar](50) NOT NULL,
[PrjTypeID] [char](36) NOT NULL,
[PrjTypeEName] [nvarchar](20) NULL,
[PrjTypeCName] [nvarchar](20) NULL,
[ParentID] [char](36) NULL,
[Memo] [text] NULL,
[CreateUser] [varchar](50) NULL,
[CreateDate] [datetime] NULL,
[ModifyUser] [varchar](50) NULL,
[ModifyDate] [datetime] NULL,
CONSTRAINT [PK_OTB_PRJ_PrjtType] PRIMARY KEY CLUSTERED
(
[OrganizationID] ASC,
[PrjTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjRuleWorkType] Script Date: 09/30/2014 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjRuleWorkType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OTB_PRJ_PrjRuleWorkType](
[OrganizationID] [varchar](50) NOT NULL,
[PrjRuleID] [char](36) NOT NULL,
[PrjWorkTypeID] [char](36) NOT NULL,
CONSTRAINT [PK_OTB_PRJ_PrjRuleWorkType] PRIMARY KEY CLUSTERED
(
[OrganizationID] ASC,
[PrjRuleID] ASC,
[PrjWorkTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[OTB_PRJ_PrjRule] Script Date: 09/30/2014 09:05:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OTB_PRJ_PrjRule]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OTB_PRJ_PrjRule](
[OrganizationID] [varchar](50) NOT NULL,
[PrjRuleID] [char](36) NOT NULL,
[PrjRuleEName] [nvarchar](20) NULL,
[PrjRuleCName] [nvarchar](20) NULL,
[ParentID] [char](36) NULL,
[Memo] [text] NULL,
[CreateUser] [varchar](50) NULL,
[CreateDate] [datetime] NULL,
[ModifyUser] [varchar](50) NULL,
[ModifyDate] [datetime] NULL,
[PrjTypeID] [char](36) NULL,
CONSTRAINT [PK_OTB_PRJ_PrjRule] PRIMARY KEY CLUSTERED
(
[OrganizationID] ASC,
[PrjRuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_ADD] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_ADD]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Delete] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Delete]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Exists] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Exists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Exists]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetList] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetList]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetListCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetListCount]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetModel] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetModel]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Update] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Update]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_ADD] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_ADD]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Delete] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Delete]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Exists] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Exists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Exists]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetList] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetList]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetListCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetListCount]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetModel] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetModel]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Update] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Update]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Delete] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Delete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Delete]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Exists] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Exists]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Exists]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Update] Script Date: 09/30/2014 09:08:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Update]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Update]
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Update] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：修改一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Update]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@PrjWorkTypeEName nvarchar(20),
@PrjWorkTypeCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@IsAutoTask char(1),
@PrjWorkTypeRange nvarchar(20),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
UPDATE [OTB_PRJ_PrjWorkType] SET
[PrjWorkTypeEName] = @PrjWorkTypeEName,[PrjWorkTypeCName] = @PrjWorkTypeCName,[ParentID] = @ParentID,[IsAutoTask] = @IsAutoTask,[PrjWorkTypeRange] = @PrjWorkTypeRange,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID
WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：得到实体对象的详细信息
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetModel]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36)
AS
SELECT
OrganizationID,PrjWorkTypeID,PrjWorkTypeEName,PrjTypeID,PrjWorkTypeCName,ParentID,IsAutoTask,PrjWorkTypeRange,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
FROM [OTB_PRJ_PrjWorkType]
WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetListCount]
@OrganizationID varchar(50),
@PrjWorkTypeEName varchar(50),
@PrjWorkTypeCName varchar(50),
@ParentID varchar(36),
@PrjWorkTypeRange varchar(36)
AS
SELECT COUNT(0)
FROM OTB_PRJ_PrjWorkType
WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName=''%%'')
AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName=''%%'')
AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='''')
AND (ParentID = @ParentID OR @ParentID='''')
AND OrganizationID=@OrganizationID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--用途：查詢資料信息
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjWorkTypeEName varchar(50),
@PrjWorkTypeCName varchar(50),
@ParentID varchar(36),
@PrjWorkTypeRange varchar(36),
@SortExpression nvarchar(500)
AS
SELECT
RowId
,PrjWorkTypeID,PrjWorkTypeEName,PrjTypeID,PrjWorkTypeCName,ParentID,IsAutoTask,PrjWorkTypeRange
FROM
(
SELECT
CASE @SortExpression WHEN N''PrjWorkTypeEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName))
WHEN N''PrjWorkTypeEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName DESC))
WHEN N''PrjWorkTypeCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName))
WHEN N''PrjWorkTypeCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''PrjTypeID'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeID))
WHEN N''PrjTypeID DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeID DESC))
WHEN N''IsAutoTask'' THEN
(ROW_NUMBER() OVER(Order BY IsAutoTask))
WHEN N''IsAutoTask DESC'' THEN
(ROW_NUMBER() OVER(Order BY IsAutoTask DESC))
WHEN N''PrjWorkTypeRange'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeRange))
WHEN N''PrjWorkTypeRange DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeRange DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName))
END AS RowId
,PrjWorkTypeID,PrjWorkTypeEName,PrjWorkTypeCName
,(SELECT PrjWorkTypeCName FROM dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.ParentID) AS ParentID,
IsAutoTask,PrjWorkTypeRange
,(SELECT PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.PrjTypeID) AS PrjTypeID
FROM OTB_PRJ_PrjWorkType AS a
WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName=''%%'')
AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName=''%%'')
AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='''')
AND (ParentID = @ParentID OR @ParentID='''')
AND OrganizationID=@OrganizationID
) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId
SELECT
CASE @SortExpression WHEN N''PrjWorkTypeEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName))
WHEN N''PrjWorkTypeEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName DESC))
WHEN N''PrjWorkTypeCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName))
WHEN N''PrjWorkTypeCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjWorkTypeCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''PrjTypeID'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeID))
WHEN N''PrjTypeID DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeID DESC))
WHEN N''IsAutoTask'' THEN
(ROW_NUMBER() OVER(Order BY IsAutoTask))
WHEN N''IsAutoTask DESC'' THEN
(ROW_NUMBER() OVER(Order BY IsAutoTask DESC))
WHEN N''PrjWorkTypeRange'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), PrjWorkTypeRange)))
WHEN N''PrjWorkTypeRange DESC'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), PrjWorkTypeRange) DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjWorkTypeEName))
END AS ''項次''
,PrjWorkTypeEName AS ''專案工作類型英文''
,PrjWorkTypeCName AS ''專案工作類型中文''
,(SELECT PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.PrjTypeID) AS ''專案類型''
,(select PrjWorkTypeCName FROM dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.ParentID) AS ''父層編號''
,IsAutoTask AS ''是否自動產生任務''
,PrjWorkTypeRange AS ''任務範圍''
FROM OTB_PRJ_PrjWorkType AS a
WHERE (PrjWorkTypeEName LIKE @PrjWorkTypeEName OR @PrjWorkTypeEName=''%%'')
AND (PrjWorkTypeCName LIKE @PrjWorkTypeCName OR @PrjWorkTypeCName=''%%'')
AND (PrjWorkTypeRange = @PrjWorkTypeRange OR @PrjWorkTypeRange='''')
AND (ParentID = @ParentID OR @ParentID='''')
AND OrganizationID=@OrganizationID
ORDER BY ''項次''
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Exists] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Exists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：是否已经存在
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Exists]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36)
AS
DECLARE @TempID int
SELECT @TempID = count(1) FROM [OTB_PRJ_PrjWorkType] WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID
IF @TempID = 0
RETURN 0
ELSE
RETURN 1
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_Delete] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：删除一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_Delete]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36)
AS
DELETE [OTB_PRJ_PrjWorkType]
WHERE OrganizationID=@OrganizationID and PrjWorkTypeID=@PrjWorkTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：增加一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:38:59
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjWorkType_ADD]
@OrganizationID varchar(50),
@PrjWorkTypeID char(36),
@PrjWorkTypeEName nvarchar(20),
@PrjWorkTypeCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@IsAutoTask char(1),
@PrjWorkTypeRange nvarchar(20),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
INSERT INTO [OTB_PRJ_PrjWorkType](
[OrganizationID],[PrjWorkTypeID],[PrjWorkTypeEName],[PrjWorkTypeCName],[ParentID],[IsAutoTask],[PrjWorkTypeRange],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID
)VALUES(
@OrganizationID,NEWID(),@PrjWorkTypeEName,@PrjWorkTypeCName,@ParentID,@IsAutoTask,@PrjWorkTypeRange,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),@PrjTypeID
)
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Update] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：修改一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Update]
@OrganizationID varchar(50),
@PrjTypeID char(36),
@PrjTypeEName nvarchar(20),
@PrjTypeCName nvarchar(20),
@ParentID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
UPDATE [OTB_PRJ_PrjType] SET
[PrjTypeEName] = @PrjTypeEName,[PrjTypeCName] = @PrjTypeCName,[ParentID] = @ParentID,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE()
WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetModel] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetModel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：得到实体对象的详细信息
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:01
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetModel]
@OrganizationID varchar(50),
@PrjTypeID char(36)
AS
SELECT
OrganizationID,PrjTypeID,PrjTypeEName,PrjTypeCName,ParentID,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
FROM [OTB_PRJ_PrjType]
WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetListCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetListCount]
@OrganizationID varchar(50),
@PrjTypeEName varchar(50),
@PrjTypeCName varchar(50),
@ParentID varchar(36)
AS
SELECT COUNT(0)
FROM OTB_PRJ_PrjType
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName=''%%'')
AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName=''%%'')
AND OrganizationID=@OrganizationID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_GetList] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--用途：查詢資料信息
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjTypeEName varchar(50),
@PrjTypeCName varchar(50),
@ParentID varchar(36),
@SortExpression nvarchar(500)
AS
SELECT
RowId
,PrjTypeID,PrjTypeEName,PrjTypeCName,ParentID,Memo
FROM
(
SELECT
CASE @SortExpression WHEN N''PrjTypeEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeEName))
WHEN N''PrjTypeEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeEName DESC))
WHEN N''PrjTypeCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeCName))
WHEN N''PrjTypeCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''Memo'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
WHEN N''Memo DESC'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjTypeEName))
END AS RowId
,PrjTypeID,PrjTypeEName,PrjTypeCName,(select PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.ParentID) AS ParentID,Memo
FROM OTB_PRJ_PrjType AS a
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName=''%%'')
AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName=''%%'')
AND OrganizationID=@OrganizationID
) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId
SELECT
CASE @SortExpression WHEN N''PrjTypeEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeEName))
WHEN N''PrjTypeEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeEName DESC))
WHEN N''PrjTypeCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeCName))
WHEN N''PrjTypeCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjTypeCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''Memo'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
WHEN N''Memo DESC'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjTypeEName))
END AS ''項次''
,PrjTypeEName AS ''專案類型英文''
,PrjTypeCName AS ''專案類型中文''
,(select PrjTypeCName FROM dbo.OTB_PRJ_PrjType WHERE PrjTypeID=a.ParentID) AS ''父層編號''
,Memo AS ''備註''
FROM OTB_PRJ_PrjType AS a
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjTypeEName LIKE @PrjTypeEName OR @PrjTypeEName=''%%'')
AND (PrjTypeCName LIKE @PrjTypeCName OR @PrjTypeCName=''%%'')
AND OrganizationID=@OrganizationID
ORDER BY ''項次''
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Exists] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Exists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：是否已经存在
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Exists]
@OrganizationID varchar(50),
@PrjTypeID char(36)
AS
DECLARE @TempID int
SELECT @TempID = count(1) FROM [OTB_PRJ_PrjType] WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID
IF @TempID = 0
RETURN 0
ELSE
RETURN 1
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_Delete] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：删除一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_Delete]
@OrganizationID varchar(50),
@PrjTypeID char(36)
AS
DELETE [OTB_PRJ_PrjType]
WHERE OrganizationID=@OrganizationID and PrjTypeID=@PrjTypeID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjType_ADD] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjType_ADD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--用途：增加一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:33:58
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjType_ADD]
@OrganizationID varchar(50),
@PrjTypeID char(36),
@PrjTypeEName nvarchar(20),
@PrjTypeCName nvarchar(20),
@ParentID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
INSERT INTO [OTB_PRJ_PrjType](
[OrganizationID],[PrjTypeID],[PrjTypeEName],[PrjTypeCName],[ParentID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate]
)VALUES(
@OrganizationID,NEWID(),@PrjTypeEName,@PrjTypeCName,@ParentID,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE()
)
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：查询记录信息
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_GetList]
@OrganizationID varchar(50),
@PrjRuleID char(36)
AS
SELECT
OrganizationID,PrjRuleID,PrjWorkTypeID,(SELECT PrjWorkTypeCName FROM dbo.OTB_PRJ_PrjWorkType WHERE PrjWorkTypeID=a.PrjWorkTypeID) AS PrjRuleWorkType
FROM [OTB_PRJ_PrjRuleWorkType] AS a WHERE OrganizationID=@OrganizationID AND PrjRuleID=@PrjRuleID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：删除一条记录
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_Delete]
@OrganizationID varchar(50),
@PrjRuleID char(36)
AS
DELETE [OTB_PRJ_PrjRuleWorkType]
WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：增加一条记录
--项目名称：
--说明：
--时间：2014/9/29 下午 07:24:36
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRuleWorkType_ADD]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjWorkTypeID char(36)
AS
INSERT INTO [OTB_PRJ_PrjRuleWorkType](
[OrganizationID],[PrjRuleID],[PrjWorkTypeID]
)VALUES(
@OrganizationID,@PrjRuleID,@PrjWorkTypeID
)
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Update] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Update]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：修改一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Update]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjRuleEName nvarchar(20),
@PrjRuleCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
UPDATE [OTB_PRJ_PrjRule] SET
[PrjRuleEName] = @PrjRuleEName,[PrjRuleCName] = @PrjRuleCName,[ParentID] = @ParentID,[Memo] = @Memo,[CreateUser] = @CreateUser,[CreateDate] = @CreateDate,[ModifyUser] = @ModifyUser,[ModifyDate] = GETDATE(),PrjTypeID=@PrjTypeID
WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetModel] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetModel]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：得到实体对象的详细信息
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetModel]
@OrganizationID varchar(50),
@PrjRuleID char(36)
AS
SELECT
OrganizationID,PrjRuleID,PrjTypeID,PrjRuleEName,PrjRuleCName,ParentID,Memo,CreateUser,CreateDate,ModifyUser,ModifyDate
FROM [OTB_PRJ_PrjRule]
WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetListCount] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetListCount]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--功能：查詢資料總筆數
--項目名稱：Web_CRM
--Create：John
--時間：2014/07/25
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetListCount]
@OrganizationID varchar(50),
@PrjRuleEName varchar(50),
@PrjRuleCName varchar(50),
@ParentID varchar(36)
AS
SELECT COUNT(0)
FROM OTB_PRJ_PrjRule
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjRuleEName LIKE @PrjRuleEName OR @PrjRuleEName=''%%'')
AND (PrjRuleCName LIKE @PrjRuleCName OR @PrjRuleCName=''%%'')
AND OrganizationID=@OrganizationID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_GetList] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_GetList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
------------------------------------
--用途：查詢資料信息
--項目名稱：MidWeb
--Create：Ted
--時間：2014/05/26
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_GetList]
@StartRecordIndex int,
@EndRecordIndex int,
@OrganizationID varchar(50),
@PrjRuleEName varchar(50),
@PrjRuleCName varchar(50),
@ParentID varchar(36),
@SortExpression nvarchar(500)
AS
SELECT
RowId
,PrjRuleID,PrjRuleEName,PrjRuleCName,ParentID,Memo
FROM
(
SELECT
CASE @SortExpression WHEN N''PrjRuleEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleEName))
WHEN N''PrjRuleEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleEName DESC))
WHEN N''PrjRuleCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleCName))
WHEN N''PrjRuleCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''Memo'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
WHEN N''Memo DESC'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjRuleEName))
END AS RowId
,PrjRuleID,PrjRuleEName,PrjRuleCName,(SELECT PrjRuleCName FROM dbo.OTB_PRJ_PrjRule WHERE PrjRuleID=a.ParentID) AS ParentID,Memo
FROM OTB_PRJ_PrjRule AS a
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjRuleEName LIKE @PrjRuleEName OR @PrjRuleEName=''%%'')
AND (PrjRuleCName LIKE @PrjRuleCName OR @PrjRuleCName=''%%'')
AND OrganizationID=@OrganizationID
) AS AA
WHERE AA.RowId>=@StartRecordIndex AND AA.RowId<=@EndRecordIndex
ORDER BY RowId
SELECT
CASE @SortExpression WHEN N''PrjRuleEName'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleEName))
WHEN N''PrjRuleEName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleEName DESC))
WHEN N''PrjRuleCName'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleCName))
WHEN N''PrjRuleCName DESC'' THEN
(ROW_NUMBER() OVER(Order BY PrjRuleCName DESC))
WHEN N''ParentID'' THEN
(ROW_NUMBER() OVER(Order BY ParentID))
WHEN N''ParentID DESC'' THEN
(ROW_NUMBER() OVER(Order BY ParentID DESC))
WHEN N''Memo'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo)))
WHEN N''Memo DESC'' THEN
(ROW_NUMBER() OVER(Order BY CONVERT(nvarchar(2000), Memo) DESC))
ELSE
(ROW_NUMBER() OVER(Order BY PrjRuleEName))
END AS ''項次''
,PrjRuleEName AS ''專案角色英文''
,PrjRuleCName AS ''專案角色中文''
,(SELECT PrjRuleCName FROM dbo.OTB_PRJ_PrjRule WHERE PrjRuleID=a.ParentID) AS ''父層編號''
,Memo AS ''備註''
FROM OTB_PRJ_PrjRule AS a
WHERE (ParentID = @ParentID OR @ParentID='''')
AND (PrjRuleEName LIKE @PrjRuleEName OR @PrjRuleEName=''%%'')
AND (PrjRuleCName LIKE @PrjRuleCName OR @PrjRuleCName=''%%'')
AND OrganizationID=@OrganizationID
ORDER BY ''項次''
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Exists] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Exists]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：是否已经存在
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Exists]
@OrganizationID varchar(50),
@PrjRuleID char(36)
AS
DECLARE @TempID int
SELECT @TempID = count(1) FROM [OTB_PRJ_PrjRule] WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID
IF @TempID = 0
RETURN 0
ELSE
RETURN 1
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_Delete] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_Delete]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：删除一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_Delete]
@OrganizationID varchar(50),
@PrjRuleID char(36)
AS
DELETE [OTB_PRJ_PrjRule]
WHERE OrganizationID=@OrganizationID and PrjRuleID=@PrjRuleID
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_OTB_PRJ_PrjRule_ADD] Script Date: 09/30/2014 09:08:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_OTB_PRJ_PrjRule_ADD]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'------------------------------------
--用途：增加一条记录
--项目名称：
--说明：
--时间：2014/9/29 上午 08:37:33
------------------------------------
CREATE PROCEDURE [dbo].[OSP_OTB_PRJ_PrjRule_ADD]
@OrganizationID varchar(50),
@PrjRuleID char(36),
@PrjRuleEName nvarchar(20),
@PrjRuleCName nvarchar(20),
@ParentID char(36),
@PrjTypeID char(36),
@Memo text,
@CreateUser varchar(50),
@CreateDate datetime,
@ModifyUser varchar(50),
@ModifyDate datetime
AS
INSERT INTO [OTB_PRJ_PrjRule](
[OrganizationID],[PrjRuleID],[PrjRuleEName],[PrjRuleCName],[ParentID],[Memo],[CreateUser],[CreateDate],[ModifyUser],[ModifyDate],PrjTypeID
)VALUES(
@OrganizationID,NEWID(),@PrjRuleEName,@PrjRuleCName,@ParentID,@Memo,@CreateUser,GETDATE(),@ModifyUser,GETDATE(),@PrjTypeID
)
'
END
GO
/****** Object: StoredProcedure [dbo].[OSP_Common_GetWorkTypeList] Script Date: 09/30/2014 14:55:28 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_Common_GetWorkTypeList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_Common_GetWorkTypeList]
GO
------------------------------------
--功能：獲取人員名稱列表
--項目名稱：MidWeb
--Create：John
--時間：2014/09/30
------------------------------------
CREATE PROCEDURE [dbo].[OSP_Common_GetWorkTypeList]
@PrjTypeID varchar(50),
@OrganizationID NVARCHAR(50)
AS
SELECT DISTINCT PrjWorkTypeID AS ID, PrjWorkTypeCName as NAME
FROM OTB_PRJ_PrjWorkType
WHERE PrjTypeID =@PrjTypeID AND OrganizationID=@OrganizationID
ORDER BY NAME
GO
/****** Object: StoredProcedure [dbo].[OSP_Common_GetProgramNameListByModuleID] Script Date: 09/30/2014 14:56:53 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OSP_Common_GetProgramNameListByModuleID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[OSP_Common_GetProgramNameListByModuleID]
GO
------------------------------------
--用途：查詢資料信息
--項目名稱：內部管理系統
--Create：John
--時間：2014/09/30
-- exec OSP_Common_GetProgramListByRuleId 'admin','AA'
------------------------------------
CREATE PROCEDURE [dbo].[OSP_Common_GetProgramNameListByModuleID]
@ModuleID VARCHAR(50)
,@OrganizationID VARCHAR(50)
AS
SELECT ProgramID,ProgramName FROM OTB_PRJ_ProgramList WHERE ModuleID=@ModuleID AND OrganizationID=@OrganizationID ORDER BY ProgramName
GO