|
anandbe21
Starting Member
4 Posts |
 Posted - 2009-09-09 : 14:57:26
|
| Hi All,Actually regarding cancel my leaves application whenever user click Cancel button inmy dotnet application my cancelstoredprocedure works here my procedureisAlter Proc SP_Get_LeaveDetails ( @EmpId uniqueidentifier = null, @CompareDate datetime = null, @LeaveId uniqueidentifier = null, @Flag int = 1, @ProcessLevel int =-3, @UserTypeId uniqueidentifier = null, @FromDate datetime = null, @ApplyingDate datetime= null, @NoofDays float = -1, @Reason Varchar(800) = '', @NameofShip varchar(25) = '', @LeaveTypeId uniqueidentifier =null, @Prefix varchar(50)= '', @Suffix varchar(50) = '', @NoofDaysToEncash float = -1, @PermissionToLeave varchar(25) ='', @LeaveAddress varchar(800) = '', @RIK_CLR varchar(25) = '', @LTC varchar(25) = '', @PreLeaveDetails varchar(800) = '', @ModifiedBy uniqueidentifier = null, @ModifiedDate Datetime = null, @SecondaryLeaveTypeId uniqueidentifier = null, @SecondaryNoofDays float = -1, @SecondaryFromDate datetime = null, @ThirdLeaveTypeId uniqueidentifier = null, @ThirdNoofDays float = -1, @ThirdFromDate datetime = null, @PreviousLeaveId uniqueidentifier = null, @Cancel int = -1 ) As Declare @qrystr varchar(8000) Begin Set @qrystr = 'Select tbl_OrgHierarchy.NodeText,tbl_EmpMaster.UserTypeId,tbl_EmpMaster.Gender,tbl_EmpMaster.PersonnelNo ''Personnel No'',tbl_EmpUserLogin.UserId,tbl_LeaveDetails.EmpId, replace(cast(ltPrimary.LeaveType +'' & ''+ isnull(ltSecondary.LeaveType,'' '') +'' & ''+ isnull(ltThird.LeaveType,'' '') as varchar(100)),'' & '','''') ''LeaveType'', case: when tbl_UserTypeMaster.UserType = ''Service'' then tbl_RankMaster.RankName +'' ''+tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname else tbl_EmpMaster.EmpName + '' ''+ tbl_Empmaster.Surname +'' ''+tbl_RankMaster.RankName end ''Name'',tbl_DesignationMaster.DesignationName, tbl_OrgHierarchy.LevelNo, replicate(''0'',2-len(datepart(dd,tbl_LeaveDetails.FromDate)))+cast(datepart(dd,tbl_LeaveDetails.FromDate) as nvarchar(5))+''/''+ replicate(''0'',2-len(datepart(mm,tbl_LeaveDetails.FromDate)))+cast(datepart(mm,tbl_LeaveDetails.FromDate) as nvarchar(5))+''/''+ cast(datepart(yyyy,tbl_LeaveDetails.FromDate) as nvarchar(5)) ''FromDate'', tbl_LeaveDetails.FromDate ''sortFromDate'' , cast(tbl_LeaveDetails.FromDate as varchar(25)) ''FromDateAMPM'',cast(tbl_LeaveDetails.SecondaryFromDate as varchar(25)) ''SecondaryFromDateAMPM'', cast(tbl_LeaveDetails.ThirdFromDate as varchar(25)) ''ThirdFromDateAMPM'', replicate(''0'',2-len(datepart(dd,tbl_LeaveDetails.SecondaryFromDate)))+cast(datepart(dd,tbl_LeaveDetails.SecondaryFromDate) as nvarchar(5))+''/''+ replicate(''0'',2-len(datepart(mm,tbl_LeaveDetails.SecondaryFromDate)))+cast(datepart(mm,tbl_LeaveDetails.SecondaryFromDate) as nvarchar(5))+''/''+ cast(datepart(yyyy,tbl_LeaveDetails.SecondaryFromDate) as nvarchar(5)) ''SecondaryFromDate'', tbl_LeaveDetails.SecondaryFromDate ''sortSecondaryFromDate'' , replicate(''0'',2-len(datepart(dd,tbl_LeaveDetails.ThirdFromDate)))+cast(datepart(dd,tbl_LeaveDetails.ThirdFromDate) as nvarchar(5))+''/''+ replicate(''0'',2-len(datepart(mm,tbl_LeaveDetails.ThirdFromDate)))+cast(datepart(mm,tbl_LeaveDetails.ThirdFromDate) as nvarchar(5))+''/''+ cast(datepart(yyyy,tbl_LeaveDetails.ThirdFromDate) as nvarchar(5)) ''ThirdFromDate'', tbl_LeaveDetails.ThirdFromDate ''sortThirdFromDate'' , replicate(''0'',2-len(datepart(dd,tbl_LeaveDetails.ApplyingDate)))+cast(datepart(dd,tbl_LeaveDetails.ApplyingDate) as nvarchar(5))+''/''+ replicate(''0'',2-len(datepart(mm,tbl_LeaveDetails.ApplyingDate)))+cast(datepart(mm,tbl_LeaveDetails.ApplyingDate) as nvarchar(5))+''/''+ cast(datepart(yyyy,tbl_LeaveDetails.ApplyingDate) as nvarchar(5)) ''ApplyingDate'', replicate(''0'',2-len(datepart(dd,dateadd(day,tbl_LeaveDetails.NoofDays,tbl_LeaveDetails.FromDate))))+cast(datepart(dd,dateadd(day,tbl_LeaveDetails.NoofDays,tbl_LeaveDetails.FromDate)) as nvarchar(5))+''/''+ replicate(''0'',2-len(datepart(mm,dateadd(day,tbl_LeaveDetails.NoofDays,tbl_LeaveDetails.FromDate))))+cast(datepart(mm,dateadd(day,tbl_LeaveDetails.NoofDays,tbl_LeaveDetails.FromDate)) as nvarchar(5))+''/''+ cast(datepart(yyyy,dateadd(day,tbl_LeaveDetails.NoofDays,tbl_LeaveDetails.FromDate)) as nvarchar(5)) ''DateTo'', ltPrimary.LeaveType ''LeaveTypeDemo'', ltSecondary.LeaveType ''SecondaryLeaveType'' ,ltThird.LeaveType ''ThirdLeaveType'', tbl_LeaveDetails.PreviousLeaveId, tbl_LeaveDetails.LeaveTypeId, tbl_leavestatics,tbl_LeaveDetails.SecondaryLeaveTypeId,tbl_LeaveDetails.ThirdLeaveTypeId, tbl_LeaveDetails.Clamed, tbl_LeaveDetails.NoofDays + tbl_LeaveDetails.SecondaryNoofDays + tbl_LeaveDetails.ThirdNoofDays ''NoofDays'', tbl_LeaveDetails.NoofDays ''NoofDaysDemo'', tbl_LeaveDetails.SecondaryNoofDays,tbl_LeaveDetails.ThirdNoofDays, tbl_LeaveDetails.NoofDaysToEncash, tbl_LeaveDetails.Reason, tbl_LeaveDetails.NameofShip, tbl_LeaveDetails.LeaveId, tbl_LeaveDetails.Prefix, tbl_LeaveDetails.Suffix, tbl_LeaveDetails.LeaveAddress,tbl_LeaveDetails.PermissionToLeave,tbl_LeaveDetails.RIK_CLR, tbl_LeaveDetails.LTC, tbl_LeaveDetails.PreLeaveDetails, tbl_LeaveDetails.ProcessLevel, tbl_LeaveDetails.ModifiedBy, tbl_LeaveDetails.Previous,tbl_LeaveDetails.Cancel, replicate(''0'',2-len(datepart(dd,tbl_LeaveDetails.ModifiedDate)))+cast(datepart(dd,tbl_LeaveDetails.ModifiedDate) as nvarchar(5))+''-''+ replicate(''0'',2-len(datepart(mm,tbl_LeaveDetails.ModifiedDate)))+cast(datepart(mm,tbl_LeaveDetails.ModifiedDate) as nvarchar(5))+''-''+ cast(datepart(yyyy,tbl_LeaveDetails.ModifiedDate) as nvarchar(5)) ''ModifiedDate'', tbl_LeaveDetails.ModifiedDate ''strModifiedDate'', tbl_LeaveDetails.Flag,tbl_LeaveDetails.PrintTaken,tbl_LeaveDetails.AmendorCancel,tbl_LeaveDetails.CanceledId,isnull(tbl_LeaveDetails.Hold,''-1'') ''Hold'',tbl_LeaveDetails.Query, tbl_LeaveDetails.Explanation,tbl_LeaveDetails.RecallType From tbl_LeaveDetails,tbl_LeaveTypeMaster ltPrimary,tbl_LeaveTypeMaster ltThird,tbl_EmpMaster,tbl_RankMaster,tbl_DesignationMaster,tbl_EmpUserLogin, tbl_OrgHierarchy ,tbl_LeaveTypeMaster ltSecondary,tbl_UserTypeMaster where tbl_RankMaster.RankId =* tbl_EmpMaster.RankId and tbl_DesignationMaster.DesignationId =* tbl_EmpMaster.DesignationId and tbl_LeaveDetails.EmpId = tbl_EmpMaster.EmpId and tbl_EmpUserLogin.EmpId = tbl_LeaveDetails.EmpId and tbl_LeaveDetails.EmpId in (select tbl_EmpUserLogin.EmpId from tbl_EmpUserLogin where tbl_OrgHierarchy.UserId = tbl_EmpUserLogin.UserId) and ltPrimary.LeaveTypeId =* tbl_LeaveDetails.LeaveTypeId and ltSecondary.LeaveTypeId =* tbl_LeaveDetails.SecondaryLeaveTypeId and ltThird.LeaveTypeId =* tbl_LeaveDetails.ThirdLeaveTypeId and tbl_OrgHierarchy.Flag =1 and tbl_LeaveDetails.Cancel <> 0 or tbl_LeaveDetails.Cancel <> 1 and tbl_LeaveDetails.Flag = '+cast(@Flag as varchar(2)) +' and NodeText=(select min(cast(org1.NodeText as varchar(25))) from tbl_OrgHierarchy org1 where org1.UserId=tbl_OrgHierarchy.UserId and org1.Flag = 1) and tbl_UserTypeMaster.UserTypeId = tbl_EmpMaster.UserTypeId' if isnull(Convert(varchar(100),@UserTypeId),'') <> '' set @qrystr = @qrystr + ' and tbl_LeaveDetails.LeaveTypeId in (Select tbl_LeaveTypeMaster.LeaveTypeId from tbl_LeaveTypeMaster where tbl_LeaveTypeMaster.UserTypeId = '''+convert(varchar(100),@UserTypeId)+''')' if isnull(@processLevel,'') <> -3 set @qrystr=@qrystr+' and ProcessLevel = '+cast(@processLevel as varchar(2)) if isnull(@Cancel,'') <> -1 set @qrystr=@qrystr+' and Cancel <> '+cast(@Cancel as varchar(2))if isnull(@Cancel,'') <> 0 set @qrystr=tbl_leavestatistics-Granted -isnull(tbl_leavestatics.Alreadyused,0)'noofdays' +tbl_leavedetails.noofdays if isnull(convert(varchar(100),@LeaveId),'') <> '' set @qrystr = @qrystr+' and LeaveId='''+ convert(varchar(100), @LeaveId ) +'''' if isnull(convert(varchar(100),@EmpId),'') <> '' set @qrystr = @qrystr+' and tbl_LeaveDetails.EmpId='''+ convert(varchar(100), @EmpId ) +'''' if isnull(convert(varchar(100),@LeaveTypeId),'') <> '' set @qrystr = @qrystr+' and LeaveTypeId='''+ convert(varchar(100), @LeaveTypeId ) +'''' if isnull(cast(@fromDate as varchar(100)),'') <> '' set @qrystr = @qrystr + ' and ''' +cast(@FromDate as varchar(100))+ ''' between FromDate and dateadd(dd,NoOfDays,FromDate)' if isnull(cast(@CompareDate as varchar(100)),'') <> '' set @qrystr = @qrystr +' and DatePart(yyyy,FromDate) >= DatePart(yyyy,'''+cast(@CompareDate as varchar(100))+''')' Exec (@qrystr) --print @qrystr --select Len (@qrystr) End -- SP_Get_LeaveDetails '3','1','AE0F1488-1D93-4D78-A796-E62D7051C164' Actually i needto when ever user press cancel button it was cancelling inmy dotnet applicaton but as per in sqlserver i had to add in my total leaves in my prev stored procedure bug is comingi highlighted with Red see procedure above what shouldi need whenever cancel leave it should get update tototal leaves (tbl_leavestatics).RegardsANAND. |
|
Cancellation issue in my stored procedure