use inventory

alter table tbHospitalinfo
add IsUpdateWDPostingDate bit not null default(0)

alter table tbinvusersettings
add isViewMarkup bit not null default(0)


alter table tbinvmaster
add Indication varchar(100),
	Dosage varchar(100), 
	SpecPrec varchar(100), 
	AdvReac varchar(100),
	Interaction varchar(100), 
	Preg varchar(100), 
	PrePostAdv varchar(100),
	Reconstitution varchar(100),
	Stability varchar(100),
	Storage varchar(100), 
	PrescriptionID varchar(100)

GO

alter table tbInvWithdrawHeader
add PostingDate datetime


GO

Alter table tbInvLocation
add WDPrefix varchar(20)

SP

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go












ALTER PROCEDURE [dbo].[sp_Inv_UpdateUserProfile] 

   @EmployeeID as varchar(10),
   @intOption as int,
   @bitValue as bit

AS

If not Exists( Select * from tbInvUserSettings Where UserID = @EmployeeID)
   Begin
      Insert into tbInvUserSettings Select @EmployeeID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', 0,0,0,0,0,0,0,0,0,0,0,0,0,0;
   End;
  
If @intOption = 1
  Begin
     Update tbInvUserSettings
        Set isPOTransaction = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 2
  Begin
     Update tbInvUserSettings
        Set isPODelete = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 3
  Begin
     Update tbInvUserSettings
        Set isPU = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 4
  Begin
     Update tbInvUserSettings
        Set isWD = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 5
  Begin
     Update tbInvUserSettings
        Set isRT = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 6
  Begin
     Update tbInvUserSettings
        Set isAD = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 7
  Begin
     Update tbInvUserSettings
        Set isMaster = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 8
  Begin
     Update tbInvUserSettings
        Set isMasterIPM = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 9
  Begin
     Update tbInvUserSettings
        Set isMasterOPM = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 10
  Begin
     Update tbInvUserSettings
        Set isMasterLC = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 11
  Begin
     Update tbInvUserSettings
        Set isMasterAC = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 12
  Begin
     Update tbInvUserSettings
        Set isMasterPacking = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 13
  Begin
     Update tbInvUserSettings
        Set isLocation = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 14
  Begin
     Update tbInvUserSettings
        Set isLocationIPM = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 15
  Begin
     Update tbInvUserSettings
        Set isLocationOPM = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 16
  Begin
     Update tbInvUserSettings
        Set isLocationIPP = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 17
  Begin
     Update tbInvUserSettings
        Set isLocationOPP = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 18
  Begin
     Update tbInvUserSettings
        Set isLocationTable = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 19
  Begin
     Update tbInvUserSettings
        Set isSupplierTable = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 20
  Begin
     Update tbInvUserSettings
        Set isProdClassTable = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 21
  Begin
     Update tbInvUserSettings
        Set isTermsTable = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 22
  Begin
     Update tbInvUserSettings
        Set isUOMTable = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 23
  Begin
     Update tbInvUserSettings
        Set isUpdateIPP = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 24
  Begin
     Update tbInvUserSettings
        Set isUpdateOPP = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 25
  Begin
     Update tbInvUserSettings
        Set isRecomputeInvBalance = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 26
  Begin
     Update tbInvUserSettings
        Set isUpdateStockCardBalance = @bitValue

     Where UserID = @EmployeeID;
  End;

If @intOption = 27
  Begin
     Update tbInvUserSettings
        Set isSystemSettings = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 28
  Begin
     Update tbInvUserSettings
        Set isRIV = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 29
  Begin
     Update tbInvUserSettings
        Set isRIVDelete = @bitValue
     Where UserID = @EmployeeID;
  End;

If @intOption = 30
	Begin
		Update tbInvUserSettings
			Set isUpdateLocationTable =@bitValue
	Where UserID = @EmployeeID;
	End;

If @intOption = 31
	Begin
		Update tbinvUserSettings
			Set isViewCompanyPrice = @bitValue
		where UserID = @EmployeeID;
	End;

If @intOption = 32
	Begin
		Update tbinvUserSettings
			Set isVerifyInventoryRequest = @bitValue
		where UserID = @EmployeeID;
	End;


If @intOption = 33
	Begin
		Update tbinvUserSettings
			Set isViewMarkUp = @bitValue
		where UserID = @EmployeeID;
	End;

If @intOption = 34
	Begin
		Update tbinvUserSettings
			Set isViewMarkUp = @bitValue
		where UserID = @EmployeeID;
	End;
if @intOption = 35
	Begin
		Update tbinvUserSettings
			Set isPOCreate = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 36
	Begin
		Update tbinvUserSettings
			Set isPOEdit = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 37
	Begin
		Update tbinvUserSettings
			Set isRIVCreate = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 38
	Begin
		Update tbinvUserSettings
			Set isRIVEdit = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 39
	Begin
		Update tbinvUserSettings
			Set isRRCreate = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 40
	Begin
		Update tbinvUserSettings
			Set isRREdit = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 41
	Begin
		Update tbinvUserSettings
			Set isRRDelete = @bitValue
		where UserID = @EmployeeID;
	End;
if @intOption = 42 
	Begin
		Update tbinvUserSettings
			Set isApproveRRCorrection = @bitValue
		where UserID = @EmployeeID;
	End;

if @intOption = 43 
	Begin
		Update tbinvUserSettings
			Set isApproveWDCorrection = @bitValue
		where UserID = @EmployeeID;
	End;


------------------------------------------------------------
go



CREATE Procedure [dbo].[sp_Update_tbinvMaster_RR_New]

	@Recordnumber as integer,
	@LocationID as varchar(4)

as



		Update tbInvMaster  Set ListCost = A.ListCost, Packing = A.Packing, NetCost = A.NetCost,     
		 AverageCost = 
				Case When B.OnHand <= 0 Then 
						(A.TotalNetCost/A.Qty)
					 Else 
						(( B.OnHand * C.AverageCost ) +  A.TotalNetCost ) / (B.OnHand + A.Qty )
					 End 
		from tbInvMaster C, tbInvent B,    
			(Select ItemID, ListCost, NetCost, Quantity * Packing As Qty, Packing, TotalNetCost     
			from tbInvRRDetails 
			Where RecordNumber =  @Recordnumber ) as A  
		Where C.ItemID = B.ItemID and B.ItemID = A.ItemID and B.LocationID = @LocationID



----------------------------------------------------------------------


GO


CREATE procedure [dbo].[sp_Inv_SaveWDHeader]

	@RecordNumber int,
	@LocationID int,
	@TargetLocationID int,
	@TransDate datetime,
	@TransNum varchar(20),
	@CorrectionEntry bit,
	@PostingDate datetime

as


		Insert Inventory..tbInvWithdrawHeader 
						(RecordNumber, 
						 LocationID, 
						 TargetLocationID, 
						 TransDate, 
						 Transnum, 
						 CorrectionEntry,
						 PostingDate ) 
			Values (@RecordNumber,
					@LocationID,
					@TargetLocationID,
					@TransDate,
					@TransNum,
					@CorrectionEntry,
					@PostingDate)


---------------------------------------------------------------

GO


CREATE procedure [dbo].[sp_Inv_SaveWDHeader_New]

	@RecordNumber float,
	@LocationID int,
	@TargetLocationID int,
	@TransDate datetime,
	@CorrectionEntry bit,
	@PostingDate datetime,
	@tempWDNumber varchar(50)
as




Declare @intWDNumber as varchar(50);
Declare @intWDYear as varchar(50);
Declare @WDNumber as varchar(20);
Declare @intWDMonth as varchar(10);
Declare @LocationPrex as varchar(5);


if @CorrectionEntry  = 1 
			Insert Inventory..tbInvWithdrawHeader 
						(RecordNumber, 
						 LocationID, 
						 TargetLocationID, 
						 TransDate, 
						 Transnum, 
						 CorrectionEntry,
						 PostingDate ) 
			Values (@RecordNumber,
					@LocationID,
					@TargetLocationID,
					@TransDate,
					@tempWDNumber,
					@CorrectionEntry,
					@PostingDate )
else

    Set @intWDNumber = IsNull( ( Select IsNull(WDCounter,0) from tbInvLocation Where LocationID = @LocationID ), 0) + 1;
    Set @intWDYear = IsNull( ( select Year(getdate()) ), 0);
	set @intWDMonth = IsNull( ( select Month(getdate()) ), 0);
	Set @LocationPrex = IsNull( ( Select WDPrefix from tbInvLocation where LocationId = @LocationID),0);

    Set @WDNumber = Right(Cast(@intWDYear as varchar(4)),2) + Right(cast(@intWDMonth as varchar(4)),2) + 
                     Replicate('0',7-Len(Cast(@intWDNumber as varchar(7))))+Cast(@intWDNumber as varchar(7)) + '-' + @LocationPrex;


		Insert Inventory..tbInvWithdrawHeader 
						(RecordNumber, 
						 LocationID, 
						 TargetLocationID, 
						 TransDate, 
						 Transnum, 
						 CorrectionEntry,
						 PostingDate ) 
			Values (@RecordNumber,
					@LocationID,
					@TargetLocationID,
					@TransDate,
					@WDNumber,
					@CorrectionEntry,
					@PostingDate )


    Update tbInvLocation
       Set WDCounter = Cast(IsNull(WDCounter,0) as int) + 1 
	Where LocationID = @LocationID;


select isWDNumberEntered, ispoentered,iswdentered, * from 


update tbhospitalinfo
set isWDNumberEntered = 0