USE kalidb
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChangeUserPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChangeUserPassword]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CheckUserCredentials]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CheckUserCredentials]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewCustomer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewInternalLabel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewInternalLabel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewLabel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewLabel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewProduct]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CreateNewUserRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CreateNewUserRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerExist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CustomerExist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteCustomer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteProduct]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentExist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DepartmentExist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GerUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GerUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCategoryForDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCategoryForDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNamesByUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNamesByUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetProductsForCust]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetProductsForCust]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRolePermission]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRolePermission]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRolePermission1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRolePermission1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRolesByUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRolesByUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InternalLabelDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InternalLabelDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LabelApproveUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[LabelApproveUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RoleExist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RoleExist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateCustomer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateInternalLabel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateInternalLabel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateLabel]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateLabel]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateProduct]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateUserRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateUserRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserApproveUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserApproveUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserExist]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserExist]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UserUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addMsgLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addMsgLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetAllLog]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetAllLog]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetCustomer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetCustomer]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetDepartment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetDepartment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetProducts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetProducts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ds_GetRoles]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ds_GetRoles]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getInternalLabelListByCustID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getInternalLabelListByCustID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getLabelList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getLabelList]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getLabelListByCustID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getLabelListByCustID]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO






create  PROCEDURE ChangeUserPassword
(
	@UserName nvarchar(50),
	@NewPassword nvarchar(50)
)
AS

UPDATE
	Users
SET
	Password = @NewPassword
WHERE
	Username = @UserName



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




create procedure CheckUserCredentials
(
	@UserName nvarchar(50),
	@Password nvarchar(20)
)
 AS
	IF EXISTS(SELECT UserName FROM Users WHERE UserName = @UserName AND Password = @Password AND Approved=1)
	BEGIN
		-- update the time the user last logged in
		UPDATE Users
			SET LastLogin = getdate()
		WHERE UserName = @UserName
		SELECT 1
	END
	ELSE
		SELECT 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE CreateNewCategory
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr nvarchar(128),
	@Label nvarchar(50),
	@Internal bit
)
AS
    IF NOT EXISTS(SELECT ProductID FROM Category (nolock) WHERE ProductID = @ProdID)
        BEGIN
            INSERT INTO Category (ProductID,CustID, Description, Label, Active, DtCreated, InternalUse)
            VALUES(@ProdID, @CustID, @Descr, @Label, 1, getdate(), @Internal)
        END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE CreateNewCustomer
(
    	@CustID char(10),
    	@Name nvarchar(128)
)
AS
    IF  EXISTS(SELECT CustID FROM Customer (nolock) WHERE CustID = @CustID)
        BEGIN
	DELETE FROM Customer  WHERE CustID = @CustID
        END
  
            INSERT INTO Customer (CustID, Name, Active)
            VALUES(@CustID, @Name, 1 )


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE CreateNewDepartment
(
    	@CustID char(10),
    	@Name nvarchar(128)
)
AS
    IF  EXISTS(SELECT CustID FROM Department (nolock) WHERE CustID = @CustID)
        BEGIN
	DELETE FROM Department  WHERE CustID = @CustID
        END
  
            INSERT INTO Department (CustID, Name, Active)
            VALUES(@CustID, @Name, 1 )
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE CreateNewInternalLabel
(
    	@ProdID char(10),
    	@CustID char(10),
	@Data  nvarchar(1024)
)
AS
     /* IF NOT EXISTS(SELECT ProductID FROM Label (nolock) WHERE ProductID = @ProdID AND CustID=@CustID) */
        BEGIN
            INSERT INTO InternalLabel (ProductID, CustID, DtCreated, Data, Active)
            VALUES(@ProdID, @CustID, getdate(), @Data, 1)
	SELECT 1
        END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE CreateNewLabel
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr char(128),
	@NDC   char(10),
	@Qty   char(4),
	@Misc  nvarchar(256)
)
AS
     /* IF NOT EXISTS(SELECT ProductID FROM Label (nolock) WHERE ProductID = @ProdID AND CustID=@CustID) */
        BEGIN
            INSERT INTO Label (ProductID, CustID, Description, NDC, Quantity, Misc, Active, DtCreated)
            VALUES(@ProdID, @CustID, @Descr, @NDC, @Qty, @Misc, 1, getdate())
	SELECT 1
        END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE CreateNewProduct
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr nvarchar(128),
	@Label nvarchar(50),
	@Internal bit
)
AS
    IF NOT EXISTS(SELECT ProductID FROM Products (nolock) WHERE ProductID = @ProdID)
        BEGIN
            INSERT INTO Products (ProductID,CustID, Description, Label, Active, DtCreated, InternalUse)
            VALUES(@ProdID, @CustID, @Descr, @Label, 1, getdate(), @Internal)
        END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE CreateNewRole
(
    	@RoleName nvarchar(32),
    	@Description nvarchar(512),
	@AddLabel bit,
	@AddCustomer bit,
	@AddProduct bit,
	@Printing bit,
	@UpdateLabel bit,
	@UpdateCustomer bit,
	@UpdateProduct bit,
	@LPSStatus bit,
	@Permission int
)
AS
    IF NOT EXISTS(SELECT Rolename FROM UserRoles (nolock) WHERE Rolename = @RoleName)
        BEGIN
            INSERT INTO UserRoles (Rolename, Description,AddLabel, AddCustomer, AddProduct, Printing, 
				   UpdateLabel, UpdateCustomer, UpdateProduct, LPSStatus, Permission, Active)
            VALUES(@Rolename, @Description, @AddLabel, @AddCustomer, @AddProduct, @Printing, 
				  @UpdateLabel, @UpdateCustomer, @UpdateProduct, @LPSStatus, @Permission, 1 )
        END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO






create procedure CreateNewUser
(
@userName  nvarchar(50),
@FName nvarchar(50),
@LName nvarchar(50),
@pwd      nvarchar(50),
@email    nvarchar(75),
@approved bit,
@Occ      nvarchar(100)
)
 AS
	-- this sproc returns various error/success codes
		-- a return value of 1 means success
		-- a return value of 2 means a dup username
		-- a return value of 3 means a dup email address
	-- first, we need to check if the username is a dup
	IF EXISTS(SELECT Username FROM Users (nolock) WHERE Username = @UserName)
		SELECT 2
	ELSE
	-- everything's peachy if we get this far - insert the user
	  BEGIN
		INSERT INTO Users (UserName, FirstName, Lastname, Password, Email, Approved, DateCreated, Occupation)
		VALUES(@userName,@FName, @LName, @pwd, @email, 1, getdate(), @occ)
		SELECT 1	-- return Everything's fine status code
	  END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE CreateNewUserRole
(
    	@UserName nvarchar(50),
    	@RoleName nvarchar(32)
)
AS
    IF NOT EXISTS(SELECT Username FROM UsersInRoles (nolock) WHERE Username = @UserName)
        BEGIN
            INSERT INTO UsersInRoles (Username, Rolename)
            VALUES(@UserName, @RoleName)

	SELECT 1
        END

   ELSE
	SELECT 0



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure CustomerExist
(
	@cust nvarchar(50)
)
 AS
	IF EXISTS(SELECT CustID FROM Customer WHERE CustID = @cust AND Active=1)
		SELECT 1
	ELSE
		SELECT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE DeleteCategory
(
    	@ProdID char(10),
    	@CustID char(10)
)
AS
    BEGIN
            UPDATE Category SET 
		Active = 0
	    WHERE ProductID=@ProdID AND CustID=@CustID
    END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE DeleteCustomer
(
    	@CustID char(10)
 )
AS
    BEGIN
            UPDATE Customer SET 
		Active=0 
	    WHERE CustID=@CustID
    END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE DeleteDepartment
(
    	@CustID char(10)
 )
AS
    BEGIN
            UPDATE Department SET 
		Active=0 
	    WHERE CustID=@CustID
    END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE DeleteProduct
(
    	@ProdID char(10),
    	@CustID char(10)
)
AS
    BEGIN
            UPDATE Products SET 
		Active = 0
	    WHERE ProductID=@ProdID AND CustID=@CustID
    END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE DeleteRole
(
    	@RoleName nvarchar(32)
)
AS
UPDATE UserRoles SET
 	Active=0 
WHERE RoleName = @Rolename



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure DepartmentExist
(
	@cust nvarchar(50)
)
 AS
	IF EXISTS(SELECT CustID FROM Department WHERE CustID = @cust AND Active=1)
		SELECT 1
	ELSE
		SELECT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE GerUsers  AS
BEGIN
	SELECT     UserId, UserName, FirstName, LastName, Password, Email, Approved, LastLogin, Occupation
		    FROM         Users
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE GetCategoryForDepartment
( 
@CustID char(10),
@Internal bit
) AS
SELECT     ProductID, Label
FROM         Category
WHERE     (Active = 1 AND CustID=@CustID AND InternalUse=@Internal)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE procedure GetNamesByUser
(
@UserName nvarchar(50)
)
AS
BEGIN
	SELECT 
		FirstName,LastName 
	FROM 
		Users
	WHERE
		Username = @UserName
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE GetProductsForCust 
( 
@CustID char(10),
@Internal bit
) AS
SELECT     ProductID, Label
FROM         Products
WHERE     (Active = 1 AND CustID=@CustID AND InternalUse=@Internal)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO





create procedure GetRolePermission
(
@RoleName nvarchar(10)
)
AS
BEGIN
	SELECT     AddLabel, AddCustomer, AddProduct, Printing, UpdateLabel, UpdateCustomer, UpdateProduct, LPSStatus
FROM         UserRoles
WHERE     (RoleName =@RoleName)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO





create procedure GetRolePermission1
(
@RoleName nvarchar(10)
)
AS
BEGIN
	SELECT     Permission
FROM         UserRoles
WHERE     (RoleName =@RoleName)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO







CREATE procedure GetRolesByUser
(
@UserName nvarchar(32)
)
AS
BEGIN
	SELECT 
		Rolename 
	FROM 
		UsersInRoles
	WHERE
		Username = @UserName
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE GetUsers  AS
BEGIN
	SELECT     Users.UserId, Users.UserName, Users.FirstName, Users.LastName, Users.Password, Users.Email, Users.Approved, Users.Trusted, 
                      Users.LastLogin, Users.Occupation, UsersInRoles.Rolename
FROM         UsersInRoles INNER JOIN
                      Users ON Users.UserName = UsersInRoles.Username 
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE InternalLabelDelete
(
	@ID        int
)
AS

BEGIN

UPDATE InternalLabel
	SET Active   = 0
WHERE ID=@ID
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE LabelApproveUpdate
(
   	@ProdID char(10),
    	@CustID char(10),
	@ID        int
)
AS

BEGIN

UPDATE Label
	SET Active   = 0
WHERE ID=@ID
end


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


create procedure RoleExist
(
	@Role nvarchar(50)
)
 AS
	IF EXISTS(SELECT RoleName FROM UserRoles WHERE RoleName = @Role AND Active=1)
		SELECT 1
	ELSE
		SELECT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE UpdateCategory
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr nvarchar(128),
	@Label nvarchar(50),
	@Internal bit
)
AS
    BEGIN
            UPDATE Category SET 
		Description = @Descr,
		Label       = @label,
		InternalUse = @Internal
	    WHERE ProductID=@ProdID AND CustID=@CustID
    END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UpdateCustomer
(
    	@CustID char(10),
    	@Name nvarchar(128)
)
AS
    BEGIN
            UPDATE Customer SET 
		Name=@Name 
	    WHERE CustID=@CustID
    END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE UpdateDepartment
(
    	@CustID char(10),
    	@Name nvarchar(128)
)
AS
    BEGIN
            UPDATE Department  SET 
		Name=@Name 
	    WHERE CustID=@CustID
    END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE UpdateInternalLabel
(
    	@ProdID char(10),
    	@CustID char(10),
	@Data  nvarchar(1024),
	@ID      int
)
AS

BEGIN
   UPDATE  InternalLabel SET 
	 Data=@Data WHERE ID= @ID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE UpdateLabel
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr char(128),
	@NDC   char(10),
	@Qty   char(4),
	@Misc  nvarchar(256),
	@ID      int
)
AS

BEGIN
   UPDATE  Label SET 
	 Description=@Descr,
	 NDC=@NDC,
	 Quantity=@Qty,
	 Misc=@Misc WHERE ID= @ID
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UpdateProduct
(
    	@ProdID char(10),
    	@CustID char(10),
	@Descr nvarchar(128),
	@Label nvarchar(50),
	@Internal bit
)
AS
    BEGIN
            UPDATE Products SET 
		Description = @Descr,
		Label       = @label,
		InternalUse = @Internal
	    WHERE ProductID=@ProdID AND CustID=@CustID
    END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UpdateRole
(
    	@RoleName nvarchar(32),
    	@Description nvarchar(512),
	@AddLabel bit,
	@AddCustomer bit,
	@AddProduct bit,
	@Printing bit,
	@UpdateLabel bit,
	@UpdateCustomer bit,
	@UpdateProduct bit,
	@LPSStatus bit,
	@Permission int
)
AS
UPDATE UserRoles SET
    	Description=@Description,
	AddLabel=@AddLabel, 
	AddCustomer=@AddCustomer,
 	AddProduct=@AddProduct, 
	Printing=@Printing, 
	UpdateLabel=@UpdateLabel, 
	UpdateCustomer=@UpdateCustomer,
 	UpdateProduct=@UpdateProduct,
 	LPSStatus=@LPSStatus,
 	Permission=@Permission 
WHERE RoleName = @Rolename
          




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UpdateUserRole
(
    	@UserName nvarchar(50),
    	@RoleName nvarchar(32)
)
AS
	BEGIN
	    UPDATE UsersInRoles SET
	    Rolename = @RoleName 
	    WHERE Username = @UserName
	END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UserApproveUpdate
(
@userName  nvarchar(50)
)
 AS

BEGIN

UPDATE Users
	SET Approved   = 0
WHERE UserName = @userName

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE procedure UserExist
(
	@user nvarchar(50)
)
 AS
	IF EXISTS(SELECT UserName FROM Users WHERE UserName = @user /* AND Approved=1*/)
		SELECT 1
	ELSE
		SELECT 0
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE UserUpdate
(
@userName  nvarchar(50),
@FName nvarchar(50),
@LName nvarchar(50),
@pwd      nvarchar(50),
@email    nvarchar(75),
@approved bit,
@Occ      nvarchar(100)
)
 AS

BEGIN


UPDATE 
Users
SET
FirstName = @FName,
LastName = @LName,
Password  = @pwd,
Email        = @email,
Approved   = @approved,
Occupation = @Occ

WHERE UserName = @userName

end




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE addMsgLog
(
@Module  char(4),
@MsgType char(1),
@UserName nvarchar(50),
@ServerName nvarchar(50),
@UserAgent nvarchar(100),
@MachineDNS nvarchar(32),
@UserIP     nvarchar(32),
@Auth       nvarchar(10),
@Msg        nvarchar(1000)
)AS
BEGIN
 INSERT INTO MsgLog( ModuleID, LogTime, MsgType, UserName, ServerName, UserAgent, UserMachineDNS, UserIP, Authentication, Message)
        VALUES( @Module,  getdate(), @MsgType, @UserName, @ServerName, @UserAgent, @MachineDNS, @UserIP, @Auth, @Msg );
SELECT @@IDENTITY
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE ds_GetAllLog 
(
    	@MsgType int 
)AS
BEGIN
            IF( @MsgType = -1 )
		SELECT     LogTime, ModuleID, MsgType, UserName, UserMachineDNS, Message
		FROM         MsgLog  ORDER BY LogTime DESC
	ELSE
		SELECT     LogTime, ModuleID, MsgType, UserName, UserMachineDNS, Message
		FROM         MsgLog    WHERE MsgType = @MsgType ORDER BY LogTime DESC
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE ds_GetCategory  AS
SELECT     CustID, ProductID, Description, Label,  InternalUse, ProductKey
FROM         Category
WHERE     (Active = 1)
ORDER BY CustID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE ds_GetCustomer  AS
SELECT     CustID, Name, CustKey
FROM         Customer
WHERE     (Active = 1)
ORDER BY CustID



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE ds_GetDepartment  AS
SELECT     CustID, Name, CustKey
FROM         Department
WHERE     (Active = 1)
ORDER BY CustID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE ds_GetProducts  AS
SELECT     CustID, ProductID, Description, Label,  InternalUse, ProductKey
FROM         Products
WHERE     (Active = 1)
ORDER BY CustID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE ds_GetRoles  AS

SELECT     RoleKey, RoleName, Description, AddLabel, AddCustomer, AddProduct, Printing, UpdateLabel, UpdateCustomer, UpdateProduct, LPSStatus
FROM         UserRoles WHERE Active=1


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



CREATE PROCEDURE getInternalLabelListByCustID
(
	@CustID char(10),
	@Dept    char(10)
) AS

SELECT InternalLabel.ID, InternalLabel.ProductID, InternalLabel.CustID, InternalLabel.Data, Category.Label
FROM   InternalLabel INNER JOIN
                      Category ON InternalLabel.ProductID = Category.ProductID AND InternalLabel.CustID = Category.CustID
AND InternalLabel.CustID=@CustID AND InternalLabel.ProductID=@Dept AND InternalLabel.Active=1

/*
SELECT InternalLabel.ID, InternalLabel.ProductID, InternalLabel.CustID, InternalLabel.Data, Products.Label
FROM   InternalLabel INNER JOIN
                      Products ON InternalLabel.ProductID = Products.ProductID AND InternalLabel.CustID = Products.CustID
AND InternalLabel.CustID='LAB' AND InternalLabel.Active=1
*/
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO




CREATE PROCEDURE getLabelList  AS

select ID, ProductID, Description, NDC, Quantity, misc  from label ORDER BY ProductID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE getLabelListByCustID
(
	@CustID char(10)
) AS

select ID, ProductID, Description, NDC, Quantity, misc  from label 
WHERE CustID=@CustID AND Active=1
ORDER BY ProductID


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

