Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, July 16, 2020 2:28 PM
Hello All,
Could you please help me out with this error , I have a SP which calling many SP inside(Nested SP), In Error Error handling i am getting this error message , I have tried to troubleshoot it but unable to find the exact issue. Please help me.
Br
ChetanV
Thursday, July 16, 2020 6:29 PM ✅Answered
So, the problem is that our top procedure started a transaction and the inner procedure started a transaction and we cannot actually rollback just the inner transaction. It would be nice if we don't have to use transaction in the inner procedure and just let the outer procedure to handle all the logic and try/catch.
Since we probably would want to call the second procedure by its own too, it's a bit tricky. We can essentially check trancount at the very top and only start transaction if it's 0.
Try this modification for now and see if it helps. You can also try to read this very long article
http://www.sommarskog.se/error_handling/Part1.html
I think it will provide better way of dealing with your situation.
BTW, by just quickly reviewing this article you do need to make sure to add this line at the top of both of your procedures:
SET XACT_ABORT, NOCOUNT ON;
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, July 16, 2020 9:53 PM ✅Answered
Could you please help me out with this error , I have a SP which calling many SP inside(Nested SP), In Error Error handling i am getting this error message , I have tried to troubleshoot it but unable to find the exact issue. Please help me.
This error message is not very fun. Naomi found the part in my article where I discuss a workaround which is just ludicrous.
What I usually do when I encounter this error is to use Profiler and add the events SP:StmtStarting and Error:Exception to see what the actual error message is and what statemetn that causes it.
But that presumes that you can repro the error at will. As I understood one of your other posts, the error only appear if there are two calls in parallel.
Maybe the best is to replace INSERT-EXEC with something else. After all, thi is not the only pain with INSERT-EXEC. I discuss alternatives in my article How to Share Data between Stored Procedures, http://www.sommarskog.se/share_data.html
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, July 17, 2020 6:11 AM ✅Answered
Hi Chetan Vishwakarma,
The reason is that when running to the stored procedure Proc2, there are two transactions,
and an error occurs when executing ROLLBACK, because Proc2 is executed by EXEC.
The limitation in SQL Server is that there can only be one INSERT-EXEC activity at a time
Please refer to the below sql ,it may be helpful to you.
CREATE PROC p1
AS
-- Initialize transaction and return value setting
DECLARE
@Trancount int,
@re int
SELECT
@re = 0,
@Trancount = @@ROWCOUNT
-- If the outer layer (caller) has no transaction, open the transaction directly, otherwise save the transaction point (so that the transaction can be processed later according to the situation)
IF @Trancount = 0
BEGIN TRAN
ELSE
SAVE TRAN TRAN_Save_Point
.... Your treatment
-- An error handling statement like this should be included after the statement that may be wrong
IF @@ERROR <> 0
GOTO lb_Error
-- Successfully committed the transaction
lb_Succeed:
IF @Trancount = 0
COMMIT TRAN
GOTO lb_Return
-- Failed rollback transaction
lb_Error:
IF @Trancount = 0
ROLLBACK TRAN
ELSE
ROLLBACK TRAN TRAN_Save_Point
SET @re = -1
-- Exit processing
lb_Return:
RETURN @re
Besides, you can refer to the article --How to Share Data Between Stored Procedures.This is a very detailed article about patterns to solve this type of problem.
Best Regards
Echo
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Thursday, July 16, 2020 3:09 PM
If you can show code of your SP it may help. In general, if you're trying to use INSERT EXEC command to insert into a temp table or actual table, try using table variable instead of the actual table or temp table and see if your problem will be fixed.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, July 16, 2020 4:46 PM
Hello Naomi,
I have already used table variable and temp table but no luck , Below is the SP , Please help me.
ALTER PROC [dbo].[Proc1]
( @MobileRefNo VARCHAR(50)
, @CustomerName VARCHAR(100)
, @CustomerEmail VARCHAR(100) = ''
, @CustomerMobileNo VARCHAR(30)
, @PickupAddress VARCHAR(500)
, @PickupCity VARCHAR(50)
, @PickupLatitude REAL
, @PickupLongitude REAL
, @PickupTime VARCHAR(30)
, @DropAddress VARCHAR(500)
, @DropCity VARCHAR(50)
, @DropLatitude REAL
, @DropLongitude REAL
, @TotalDistance NUMERIC(10,2) = 0
, @TotalFare NUMERIC(10,2) = 0
, @VerificationCode VARCHAR(15) = ''
, @FlightNumber VARCHAR(15) = ''
, @IsTollIncluded BIT = 0
, @IsParkingIncluded BIT = 0
, @ExtraKMFare NUMERIC(10,2) = ''
, @WaitTimeSeconds INT = 0
, @IsPartPayment BIT = 1
, @Brand VARCHAR(15)
, @IsAirportPickup BIT = 1
, @IsAirportDrop BIT = 1
, @AmountToBeCollected NUMERIC(10,2) = 0
, @SourceChannel VARCHAR(30)
, @CreatedBy VARCHAR(30) = ''
, @Service_Type VARCHAR(50) = ''
, @WayPointsTable dbo.tblXYZ READONLY
, @Walletbalance money=0
)
--WITH RECOMPILE
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @Ack VARCHAR(150)
INSERT INTO TimeOut_Issue(WebBookingReferenceNo,STARTDATETIME,SPName)
VALUES(@MobileRefNo,GETDATE(),'Proc1')
IF NOT EXISTS (SELECT OrderRefNo FROM Tbl WITH (NOLOCK) WHERE OrderRefNo = @MobileRefNo)
BEGIN
DECLARE @SourceIPAddress VARCHAR(70) = ''
DECLARE @bookingType VARCHAR(50) = 'personal'
DECLARE @paymentType TINYINT = 0
DECLARE @ProviderID INT = 0
DECLARE @AppVersion VARCHAR(20) = ''
--print 'step 0'
INSERT INTO Tbl (OrderRefNo, PassengerName, PassengerEmail, PassengerPhoneNumber, SourceName, SourceCity, SourceLatitude, SourceLongitude, StartTime,
DestinationName, DestinationCity, DestinationLatitude, DestinationLongitude, TotalDistance, TotalFare, VerificationCode, FlightNumber, IsTollIncluded,
IsParkingIncluded, ExtraKMFare, WaitTimeSeconds, IsPartPayment, VehicleType, IsAirportPickup, IsAirportDrop, AmountToBeCollected, Partner_Name, Service_Type, Record_Created_DateTime)
VALUES (@MobileRefNo, @CustomerName,@CustomerEmail, @CustomerMobileNo, @PickupAddress, @PickupCity, @PickupLatitude, @PickupLongitude, @PickupTime,
@DropAddress, @DropCity, @DropLatitude, @DropLongitude, @TotalDistance, @TotalFare, @VerificationCode, @FlightNumber, @IsTollIncluded,
@IsParkingIncluded, @ExtraKMFare, @WaitTimeSeconds, @IsPartPayment, @Brand, @IsAirportPickup, @IsAirportDrop, @AmountToBeCollected, @CreatedBy, @Service_Type, GETDATE() )
SET @dropaddress = REPLACE(@dropaddress,'|',',')
SET @PickupAddress = REPLACE(@PickupAddress,'|',',')
--print 'step 1'
IF @SourceChannel = 'ABC'
BEGIN
--print 'step 1A'
INSERT INTO tbl_Way_Points (Order_Ref_No, Sequence_ID ,Latitude, Longitude )
SELECT Order_Ref_No, Sequence_ID, Latitude, Longitude FROM @WayPointsTable
End
--print 'step 2'
--print @SourceChannel
IF @SourceChannel = 'XYZ'
BEGIN
--print 'step 2A'
SET @AppVersion = 'VER-1.0'
END
--CREATE TABLE #tempRSoutput(Ack VARCHAR(500)) Changes Temp Table to table variable
DECLARE @tempRSoutput TABLE (Ack VARCHAR(500))
INSERT INTO @tempRSoutput(Ack)
EXEC [Proc2]
@MobileRefNo,@PickupTime,@PickupAddress,@CustomerMobileNo,@CustomerName,@PickupLatitude,@PickupLongitude,@DropLatitude,
@DropLongitude,@DropAddress,@Brand,@SourceIPAddress,@BookingType,@CustomerEmail,'',@SourceChannel,
0,0,0,'','','','',
@PaymentType,'','',0,0,'','',0,@ProviderID,@Walletbalance,@AppVersion,@Walletbalance,0,0,@PickupCity
--print 'step 3'
DECLARE @Confirm VARCHAR(150)
DECLARE @JobID INT
SELECT @Ack = Ack FROM @tempRSoutput
SELECT @Confirm = Data FROM dbo.fn_Split(@Ack,'|') WHERE Id = 1
--print 'step 4'
IF (@Confirm = 'Accepted')
BEGIN
--print 'step 5'
SELECT @JobID = CAST(Data AS INT) FROM dbo.fn_Split(@Ack,'|') WHERE Id = 2
UPDATE tbl2
SET jobid = @JobID,
jobstatusid = 0,
booking_confirmed_sent = 1,
responsemsg = @Ack,
record_updated_datetime = Getdate()
WHERE orderrefno = @MobileRefNo
IF @SourceChannel = 'PQR'
BEGIN
UPDATE tbl_Way_Points SET JobId = @JobID WHERE Order_Ref_No = @MobileRefNo
END
END
ELSE
BEGIN
--print 'step 6'
SET @JobID = null
UPDATE Tbl
SET responsemsg = @Ack,
record_updated_datetime = Getdate()
WHERE orderrefno = @MobileRefNo
END
END
ELSE
BEGIN
SET @Ack = 'Booking with OrderRefNo already exists'
END
SELECT @ack AS Acknowledgement
COMMIT TRANSACTION
UPDATE TimeOut_Issue
SET ENDDATETIME=GETDATE()
WHERE WebBookingReferenceNo=@MobileRefNo and SPName='Proc1'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
--print 'step 7'
DECLARE @ErrorMessage NVARCHAR(4000)
SET @ErrorMessage = ERROR_MESSAGE()
INSERT INTO dbo.tblErrorMsgForBusiness ( ErrorMsg, StoredProcedureName, JobID )
VALUES ( @ErrorMessage, 'Proc1', @MobileRefNo )
INSERT INTO dbo.DB_Errors
VALUES
(
@MobileRefNo,
SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE())
UPDATE TimeOut_Issue
SET ENDDATETIME=GETDATE()
WHERE WebBookingReferenceNo=@MobileRefNo and SPName='Proc1'
END CATCH
END
Thursday, July 16, 2020 5:06 PM
What is the exact error you're getting now with this code? I think this procedure code is OK as is, we now need to examine PROC2 code.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, July 16, 2020 5:16 PM
What is the exact error you're getting now with this code? I think this procedure code is OK as is, we now need to examine PROC2 code.
For every expert, there is an equal and opposite expert. - Becker's Law
This Proc is working fine all the time but once in 2 days or 3 days i am getting below error in Error handling table (tblErrorMsgForBusiness and DB_Errors)
Cannot use the ROLLBACK statement within an INSERT-EXEC statement
As per the developer team If 2 requests with same SP's are hitting at same time (parallelly), the error is coming, but i have created 2 job in SQL Server with the SP parameter and executed at same time then i have received the same error but if i executed same SP again multiple time then it executed successfully without any error, hence i am totally helpless that why this issue is happening randomly.
I am unable to troubleshoot the exact error hence i posted here.
Thursday, July 16, 2020 5:45 PM
Try to simulate first.
In SSMS try two instances:
Instance one
BEGIN TRANSACTION
do mySP
Instance 2 (different tab)
do mySP
See you get the same error or something different and if it will point you to the line with the error.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, July 16, 2020 5:58 PM
I have tried the same thing earlier and it was working fine , the issue occurs once in 2-3 days not always at any random time , that's the reason it is very difficult to find the solution as i am not able to understand the behavior of error.
Br
ChetanV
Thursday, July 16, 2020 6:20 PM
This is Proc2
ALTER PROCEDURE [dbo].[Proc2] @webBookingReferenceNo varchar(50)
, @pickupDateTime varchar(30)
, @pickupAddress varchar(500)
, @mobile varchar(15)
, @customerName varchar(50)
, @pickupLatitude real
, @pickupLongitude real
, @dropLatitude real
, @dropLongitude real
, @DropAddress VARCHAR(500) = ''
, @brand varchar(10)
,
@sourceIPAddress varchar(70)
, @bookingType varchar(50) = ''
,
@customerEmail varchar(100) = ''
, @trackMobileNo varchar(30) = ''
, @sourceChannel varchar(25)
, @returnTrip bit = 0
, @retentionTime int = 0
, @redeemPoints int = 0
, @adminEmailid nvarchar(100) = ''
, @adminMobileno varchar(12) = ''
, @corporateCode varchar(50) = ''
, @corporateName varchar(100) = ''
, @paymentType tinyint = 0
,
@promoCode varchar(20) = ''
, @iMEINumber varchar(200) = ''
, @IsRideShare TINYINT = 0
, @RideShareDiscount MONEY = 0
, @RiderName VARCHAR(100)=''
, @RiderNumber varchar(40)=''
, @isInterCity TINYINT = 0
, @ProviderID INT=99
, @Walletbalance money=0
, @AppVersion varchar(20)=''
, @CustomerWalletBalance Money=0
, @PackageId smallint=0
, @FixedhhhLogId INT = 0
, @City_Name Varchar(50) = ''
, @WayPointsTable dbo.tP_Table READONLY
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ack varchar(150)
DECLARE @SCN Varchar(50)
DECLARE @DCN Varchar(50)
Declare @CI int
declare @PZ int
Declare @DZ int
Declare @CS Varchar(30) = NULL
SELECT @CI = CityID, @PZ = ZoneID FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
SELECT @DZ = ZoneID FROM Dbo.Function_test(@dropLatitude,@dropLongitude,'DELHI')
select @SCN = SourceCity, @DCN = DestinationCity from Tbl where OrderRefNo = @webBookingReferenceNo
If
(
(@pickupDateTime <= '2020-12-31 23:59:59.998')
AND
(@CI ) in (33)
AND
@sourceChannel != 'XYZ'
)
OR
(
(@pickupDateTime <= '2020-12-31 23:59:59.998')
AND @sourceChannel = 'XYZ'
and (@SCN = 'Kolkata' or @DCN = 'Kolkata')
)
Below code added to block Banglore bookings on Saturday evening ,Sunday full day ,Monday morning by Mahesh on 10-Jul-2020
OR
(
(@pickupDateTime <= '2020-07-22 23:59:59.998')
AND
(@CI ) in (5)
AND
@sourceChannel != 'XYZ'
)
OR
(
(@pickupDateTime <= '2020-07-23 23:59:59.998')
AND
(@CI ) in (6)
)
BEGIN
SET @ack = 'Sorry, We can not serve your request due to India LOCKDOWN'
END
ELSE
BEGIN
set @DropAddress = replace(@DropAddress,'|',',')
set @PickupAddress = replace(@PickupAddress,'|',',')
IF(Len(ltrim(rtrim(@CorporateCode))) > 0)
BEGIN
SET @Walletbalance = 0
END
DECLARE @timeDiff smallint
DECLARE @jobTypeDesc varchar(50)
DECLARE @processStatusID smallint
DECLARE @quotaCount smallint
DECLARE @mPC bit
DECLARE @is_Active bit
SET @mPC = 0
DECLARE @pickupLat real
DECLARE @pickupLong real
DECLARE @localityID int
DECLARE @pickupZone varchar(100)
DECLARE @pickupZoneID int
DECLARE @dropLat real
DECLARE @dropLong real
DECLARE @cancelledStageID varchar(5)
DECLARE @customerCancellations varchar(10)
DECLARE @totalBookings varchar(10)
DECLARE @count int
DECLARE @cabsAvailableOutput int
DECLARE @prevQuotaOutput varchar(10)
DECLARE @nextQuotaOutput varchar(10)
DECLARE @travelDistance float
DECLARE @cityID smallint
DECLARE @pickupSubLocalityID int
DECLARE @dropLocalityID int
DECLARE @pickupCityID int
DECLARE @pickupCity varchar(30)
DECLARE @pickupArea varchar(50)
DECLARE @pickupAreaID int
DECLARE @pickupSubArea varchar(50)
DECLARE @pickupSubAreaID int
DECLARE @destinationAddress varchar(500)
DECLARE @dropArea varchar(50)
DECLARE @dropAreaID int
DECLARE @dropSubArea varchar(50)
DECLARE @dropSubAreaID int
DECLARE @dropZoneID int
DECLARE @dropCityID int
DECLARE @dropCity varchar(40)
DECLARE @coreCity varchar(50)
DECLARE @userID int
DECLARE @customerID int
DECLARE @jobID int
DECLARE @timeSlotID tinyint
DECLARE @redeemAmount money
DECLARE @isnigi tinyint
DECLARE @timetoDispatch smallint
DECLARE @tagAs varchar(10)
DECLARE @address varchar(500)
DECLARE @couponEndDate datetime
DECLARE @couponStartDate datetime
DECLARE @reason varchar(100)
DECLARE @couponValue money
DECLARE @isAssured bit
DECLARE @startHour varchar(30)
DECLARE @endHour varchar(30)
DECLARE @jobTypeID tinyint
DECLARE @ranCouponStartDate datetime
DECLARE @ranCouponEndDate datetime
DECLARE @couponCityDeleted int
DECLARE @onRoadDistance float
DECLARE @isShortTrip bit
DECLARE @isRetentionTrip bit
DECLARE @isGoldiPickup bit
--DECLARE @isInterCity bit
DECLARE @isGoldiDrop varchar(10)
DECLARE @couponID int
/*Added for Referral*/
DECLARE @referredCustomerName varchar(30)
DECLARE @referredmobile varchar(30)
DECLARE @referralAmount varchar(30)
DECLARE @blacklistedNumber bit
DECLARE @pendingAmount money
DECLARE @transactionDate varchar(10)
DECLARE @usageCount tinyint
DECLARE @pickupMessage varchar(100)
/*For corporate Flag*/
DECLARE @corporateCustomerID int
DECLARE @corporateJob bit
DECLARE @BusinessCategoryID tinyint
Declare @StartUniqueId varchar(6) = 0
Declare @EndUniqueId varchar(6) = 0
SET @BusinessCategoryID = 1
SET @corporateJob=0
SET @customerID = 0
SET @cancelledStageID = 0
SET @onRoadDistance = 0
SET @isShortTrip = 0
SET @isRetentionTrip = 0
SET @isGoldiPickup = 0
SET @isInterCity = 0
SET @isGoldiDrop = 0
SET @ack = 'Error'
IF(@sourceChannel = 'XYZ')
BEGIN
DECLARE @DISTANCE INT
DECLARE @cityname varchar(30)
DECLARE @Dest_CityName Varchar(30)
select @DISTANCE = TotalDistance, @cityname= SourceCity, @Dest_CityName= DestinationCity from Tbl where OrderRefNo = @webBookingReferenceNo
IF @cityname in ('sjhgs')
BEGIN
SET @PackageId = 7
END
ELSE
BEGIN
IF(@Dest_CityName = 'gunda')
set @cityname = 'gunda'
select @cityID=CityID from CM where CityName = @cityname
IF(@cityID = 1)
BEGIN
IF(@DISTANCE <= 10)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=10 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
END
IF(@cityID = 3)
BEGIN
SET @PackageId = 6
END
IF(@cityID = 4)
BEGIN
IF(@DISTANCE <= 15)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=15 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 15 and @DISTANCE <= 25)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=25 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=30 and PackageName like 'TMM D2K%'
END
IF(@cityID = 5)
BEGIN
IF(@DISTANCE <= 20)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 20 and @DISTANCE <= 30)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=30 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 30 and @DISTANCE <= 35)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=35 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=45 and PackageName like 'TMM D2K%'
END
IF(@cityID = 14)
BEGIN
IF(@DISTANCE <= 10)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=10 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
END
IF(@cityID = 37)
BEGIN
SET @PackageId = 2
END
IF(@cityID = 6)
BEGIN
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=5 and PackageName like 'TMM D2K%'
END
IF(@cityID = 10)
BEGIN
select @PackageId = PackageId from tblPkg where CityId=@CityID and BaseKM=5 and PackageName like 'TMM D2K%'
END
END
END
IF((@sourceChannel = 'XYZ' AND @PackageId != 0) OR @sourceChannel != 'XYZ')
BEGIN
SET @promoCode = replace(replace(@promoCode,' ',''),'.','')
IF @SourceChannel ='ashg' AND GETDATE() <= '2016-10-31 23:59:59.991'
SET @Promocode = 'BRO'
DECLARE @BrandTypeId TINYINT
IF @Brand IN ('aaa','bbb')
BEGIN
SELECT @BrandTypeId = BrandTypeID
FROM tblBrandTypeMaster
WHERE BrandTypeName = @Brand
SET @Brand = 'Brand'
END
SELECT @pickupCityID=CityID
FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
DECLARE @NoOfBookings TINYINT
DECLARE @NoOfDays TINYINT
If @sourceChannel = 'WebMultiple'
BEGIN
SET @NoOfBookings = 30
SET @NoOfDays = 30
END
else If @sourceChannel in ('XYZ')
BEGIN
SET @NoOfBookings = 100
SET @NoOfDays=90
END
else If @sourceChannel in ('PQR')
BEGIN
SET @NoOfBookings = 100
SET @NoOfDays=60
END
ELSE
BEGIN
IF (
( SELECT count(JB.JobID)
FROM BJD JB WITH(NOLOCK)
INNER JOIN tblWallettripstartsendingDetails wts ON JB.JobID = wts.JobID
INNER JOIN DST JSD ON JB.JobID = JSD.JobID
INNER JOIN MSJ JSM ON JSD.JobstatusID = JSM.JobstatusID
WHERE JB.mobile = @mobile
AND JB.PickupTime BETWEEN dateadd(Minute,-240,getdate())
AND dateadd(day,7,getdate()) AND JSM.Is_Pending = 1 AND wts.ProviderID > 999
) >= 1
AND @ProviderId > 999
)
SET @NoOfBookings = 1
else
SET @NoOfBookings = 5
SET @NoOfDays = 30
END
IF ((@pickupDateTime > getdate()AND (@pickupDateTime BETWEEN getdate() AND cast(convert(varchar,dateadd(day,@NoOfDays,getdate()),101)+' '+'23:59:59' AS datetime))))
AND (@pickupCityID!=19)
BEGIN
IF NOT EXISTS (SELECT WebBookingReferenceNo
FROM refBndf WITH (NOLOCK)
WHERE WebBookingReferenceNo=@webBookingReferenceNo)
BEGIN
SELECT @pickupCityID=CityID
, @pickupCity = CityName
, @pickupZoneID = ZoneID
, @pickupAreaID = LocalityID
, @pickupArea = LocalityName
, @pickupSubArea = AddressPointName
, @pickupSubAreaID = AddressPointID
, @pickupZone = PickupZone
FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
if (select cityid from CM where cityname = @city_name) != @pickupCityID
set @pickupAreaID = 0
IF (@pickupAreaID = 0)
BEGIN
IF @City_Name in ('Faridabad','Gurgaon','Ghaziabad','Noida')
SET @City_Name = 'Delhi'
IF @City_Name in ('Navi gunda','Panvel','Thane')
SET @City_Name = 'gunda'
SELECT @pickupCityID=c.CityID
, @pickupCity = c.CityName
, @pickupZoneID = z.ZoneID
, @pickupAreaID = l.LocalityID
, @pickupArea = l.LocalityName
, @pickupSubArea = a.AddressPointName
, @pickupSubAreaID = a.AddressPointID
, @pickupZone = z.zonename
FROM Zone Z
INNER JOIN CM C ON C.CityID = Z.CityID
RIGHT JOIN LM L ON L.ZoneID = Z.ZoneID
RIGHT JOIN PM A ON A.LocalityID = L.LocalityID
WHERE z.ZoneName = convert(varchar(100),@City_Name)+' Region' and z.Z_IsDeleted = 1
END
-- End Code below-- Added by rishab on 13 jul 2020 for handle invalid location
SELECT @dropCityID=CityID
, @dropCity = CityName
, @dropZoneID = ZoneID
, @dropAreaID = LocalityID
, @dropArea = LocalityName
, @dropSubArea = AddressPointName
, @dropSubAreaID = AddressPointID
FROM Dbo.Function_test(@dropLatitude,@dropLongitude,@pickupCity)
SELECT @isGoldiPickup = Is_Goldi
FROM PM
WHERE AddressPointID = @pickupSubAreaID
SELECT @isGoldiDrop = Is_Goldi
FROM PM
WHERE AddressPointID = @dropSubAreaID
SELECT @coreCity = CityName
FROM CM
WHERE CityID IN (SELECT CoreCityID
FROM CM
WHERE CityID = @pickupCityID
)
CREATE table #temp ( Address varchar(500) )
CREATE table #temp1 ( Address varchar(500) )
IF (@pickupAddress LIKE '%Madhya Pradesh%')
SET @pickupAddress = @coreCity
SET @pickupAddress = replace(@pickupAddress,', ',',')
IF @pickupCityID = 5 AND @promoCode = 'AIR699' AND @IsRideShare = 0
SET @IsRideShare = 2
IF (@promoCode != '')
BEGIN
SELECT @couponCityDeleted= CityDeleted
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
AND City=@pickupCity
IF EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
SELECT @couponStartDate= CouponStartDate
, @couponEndDate=CouponEndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponValue
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
IF ((@promoCode IN ('DHY','JKSH')) OR (@promocode IN ('sdlkjf') AND @sourceChannel IN ('dlfj','sdfkl')) )
BEGIN
IF EXISTS(SELECT mobile
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @promoCode=''
SET @couponValue=0
END
END
ELSE
IF ((@promocode IN ('gsf') AND @sourceChannel NOT IN ('dfdf','dfsdgf')))
BEGIN
SET @promoCode=''
SET @couponValue=0
END
ELSE
IF ((@promocode IN ('sfjkgdfkl') AND @sourceChannel NOT IN ('nsflksgsl','lkgdjg')) )
BEGIN
IF (SELECT count(1)
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode = @promoCode AND IsDeleted=0)>=3
BEGIN
SET @promoCode=''
SET @couponValue=0
END
END
ELSE
IF (@promocode='fgnklghk')
BEGIN
SET @promoCode=''
SET @couponValue=0
END
ELSE
IF (@promocode='slgslhgs' AND @sourceChannel IN ('lskgsdfhkg'))
BEGIN
SET @promoCode=''
SET @couponValue=0
END
IF EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode AND ServiceType=@brand AND City=@pickupCity)
BEGIN
SELECT @couponCityDeleted= CityDeleted
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
AND City=@pickupCity
IF (@promoCode='sdfsd')
BEGIN
SET @reason='fgkjslfg'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@promoCode IN ('sdfsf') AND @couponCityDeleted=0)
BEGIN
IF EXISTS(SELECT mobile
FROM AUM M WITH(NOLOCK)
WHERE mobile=@mobile AND TripStatus=1)
BEGIN
SET @reason='kjgs'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL)
BEGIN
IF EXISTS(SELECT IMEINumber
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND PromoCode=@promocode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
END
ELSE
IF EXISTS(SELECT mobile
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF EXISTS(SELECT CustomerEmail
FROM TCM WITH(NOLOCK)
WHERE CustomerEmail=@customerEmail AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
END
END
ELSE
IF (((@promoCode IN ('sfvsf')) OR(@promoCode IN ('AIR25') AND (@isGoldiDrop=1 OR @isGoldiPickup=1))) AND @couponCityDeleted=0)
BEGIN
SET @usageCount=(SELECT count(1)
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0 )
IF (@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL) AND @usageCount>0
BEGIN
IF @usageCount >= (CASE WHEN @promoCode = 'AIR25' THEN 10
ELSE 5 END )
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF NOT EXISTS(SELECT 1
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND Promocode=@promocode AND IsDeleted=0) AND @usageCount < (CASE WHEN @promoCode = 'AIR25' THEN 10
ELSE 5 END )
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
IF ((@promoCode='Goldi150' AND @isGoldiDrop=0) OR (@promoCode='AIR25' AND (@isGoldiDrop=0 OR @isGoldiPickup=0)))
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@promoCode IN ('SAM150','Brand150','GBrand150','CDBrand150','CHN50','BIG50','Goldi150','JAN26','VAL14','AIR25') AND @couponCityDeleted=1)
BEGIN
SET @reason='Coupon Expired'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF @promoCode IN ('TRUE','GTRUE','CDTRUE') AND @pickupCityID ! = @dropCityID
BEGIN
SET @reason='CouponNotValidforIntercity'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@pickupdateTime BETWEEN @couponStartDate AND @couponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF NOT EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode AND City=@pickupCity)
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@couponCityDeleted=1) --EXISTS(SELECT PromoCode FROM TCM WITH(NOLOCK) WHERE PromoCode=@PromoCode AND City=@PickupCity AND CityDeleted=1)
BEGIN
SET @reason='Coupon Expired'
SET @promoCode=''
SET @couponValue = 0
END
END
/*/*PART-1 E-COUPON*/*/
/*PART -2-Random COUPON*/
/*Added logic to validate Random coupons excluding Select customers*/
ELSE
IF NOT EXISTS(SELECT 1
FROM TSC WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
IF EXISTS(SELECT 1
FROM TRP WITH(NOLOCK)
WHERE PromoCode=@promoCode AND Status=0)
BEGIN
SELECT @couponID=M.ID
, @ranCouponStartDate= StartDate
, @ranCouponEndDate=EndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponAmount
FROM TRC M WITH(NOLOCK)
INNER JOIN TRP R WITH(NOLOCK) ON R.ID=M.ID
WHERE R.PromoCode=@promoCode
IF (@couponID IN (215,214,213,212,211))
BEGIN
IF (@isGoldiDrop=1 AND @pickupCityID IN (4,5))
BEGIN
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
END
ELSE
IF EXISTS(SELECT 1
FROM TSC WITH(NOLOCK)
WHERE PromoCode=@promoCode AND mobile=@mobile)
BEGIN
SELECT @couponID=M.ID
, @ranCouponStartDate= StartDate
, @ranCouponEndDate=EndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponAmount
FROM TRC M WITH(NOLOCK)
INNER JOIN TRP R WITH(NOLOCK) ON R.ID=M.ID
WHERE R.PromoCode=@promoCode
DECLARE @percentageDiscountID tinyint
SELECT @percentageDiscountID=PercentageDiscountID
FROM TSC WITH(NOLOCK)
WHERE mobile=@mobile
AND PromoCode=@promoCode
DECLARE @isTrue tinyint
SET @isTrue=1
Not Exists
SELECT @isTrue=0
Exists
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode
AND mobile=@mobile
AND IsDeleted=0
IF EXISTS(SELECT 1
FROM TRP WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
IF (@isTrue=1 AND (@percentageDiscountID IN (183,171)))
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
IF ((@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL) AND @percentageDiscountID IN (171))
BEGIN
IF NOT EXISTS(SELECT IMEINumber
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND PromoCode=@promocode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode = ''
SET @couponValue = 0
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
BEGIN
IF (@referralAmount>'0.00')
BEGIN
IF NOT EXISTS(SELECT mobile
FROM TRTM WITH(NOLOCK)
WHERE mobile=@mobile AND InUse=0 AND PromoCode='REFBALANCE')
BEGIN
SET @couponValue=(@referralAmount)
SET @promoCode='REFBALANCE'
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' Referral Balance applicable'
END
ELSE
BEGIN
SET @couponValue='0.00'
SET @reason=convert(varchar,@couponValue)
END
END
ELSE /*without any coupon */
BEGIN
SET @reason=convert(varchar,0.00)
END
END
Declare @booking_denied bit
Set @booking_denied = 0
IF @Promocode in('dsfknl','dsgsdg') and @IsRideShare != 2
BEGIN
SET @booking_denied = 1
END
If @booking_denied = 0
Begin
IF (@pickupAreaID != 0)
BEGIN
SET @blacklistedNumber = 0
If (LEN(ltrim(rtrim(@CorporateCode))) = 0 AND LEN(ltrim(rtrim(@corporateName))) = 0 and @sourcechannel not in ('XYZ','MOMD')) Not for corporate jobs, added 'XYZ' by venu
Begin
SELECT @blacklistedNumber = 1
, @pendingAmount = sum(Amount)
, @transactionDate = cast(day(max(TransactionDateTime)) AS varchar) + '-' + convert(char(3),datename(month,max(TransactionDateTime)),0)
FROM CDSBusiness.Dbo.PTD
WHERE MobileNumber = @mobile
AND WalletSMStype in ('WP', 'WS', 'PWS')
AND WPProcessStatus=0
GROUP BY MobileNumber
end
IF (@blacklistedNumber = 0)
BEGIN
IF ((SELECT count(JB.JobID)
FROM BJD JB WITH(NOLOCK)
INNER JOIN DST JSD ON JB.JobID = JSD.JobID
INNER JOIN MSJ JSM ON JSD.JobstatusID = JSM.JobstatusID
WHERE JB.mobile = @mobile AND JB.PickupTime BETWEEN dateadd(Minute,-240,getdate())
AND dateadd(day,7,getdate()) AND JSM.Is_Pending = 1 AND JB.ChannelID = 5) < @NoOfBookings) or @sourcechannel in ('XYZ','MOMD') --added 'XYZ' by venu
BEGIN /*Added by S Prasuna for Map Navigation Feature on 10 th Feb 2015*/
DECLARE @navPickupLat real
DECLARE @navPickupLong real
DECLARE @pickupSubLocLat real
DECLARE @pickupSubLocLong real
SELECT @pickupSubLocLat = AddressPointLat
, @pickupSubLocLong = AddressPointLong
FROM PM
WHERE AddressPointID = @pickupSubAreaID
SET @navPickupLat = 0
SET @navPickupLong = 0
SELECT TOP 1 @navPickupLat = CabLatitude_TS
, @navPickupLong = CabLongitude_TS
FROM BJD JB
INNER JOIN TblTripMaster TM ON JB.JobID = TM.JobID
WHERE mobile = @mobile
AND JB.PickUpAddressPointID = @pickupSubAreaID
AND TM.GPSValueSync = 'A'
ORDER BY TripEndID DESC
IF (@navPickupLat = 0 OR isnull((SELECT Dbo.FnBD(@navPickupLat,@navPickupLong,@pickupSubLocLat,@pickupSubLocLong)),0) > 2)
BEGIN
SET @navPickupLat = @pickupSubLocLat
SET @navPickupLong = @pickupSubLocLong
END
SET @isnigi = 0
SET @mPC = 0
IF EXISTS(SELECT CallerID
FROM TbCMPCustomerMaster
WHERE CallerID = @mobile)
BEGIN
SET @mPC = 1
END
IF (@dropCityID = @pickupCityID)
BEGIN
SET @dropCityID = 0
SET @returnTrip = 0
END
SELECT @userID = UserID
FROM TblUserMaster WITH(NOLOCK)
WHERE UserName = 'WebUser'
SELECT @customerID = CustomerID
, @corporateCustomerID=CorporateCustomerID
, @CS = CS PRJ_TMM_V1.R2 Added By Rishab and Venu on 17 Jun 2020 for TO check TMM Customer Start Code
FROM Dbo.MCT WITH ( NOLOCK )
WHERE MobileNo = @mobile
IF (@corporateCustomerID=2952)
BEGIN
SET @corporateJob=1
END
IF (@customerID = 0)
begin
If @sourceChannel = 'XYZ'
begin
Set @CS = @sourceChannel
end
INSERT INTO MCT ( CustomerName, MobileNo, CallDateTime, CustomerTypeID, ChannelID, CityID, CustomerStatusID, WEB, EmailID ,CS)
VALUES ( @customerName, @mobile, getdate(), 1, 5, @pickupCityID, 1, 1, @customerEmail ,@CS)
SELECT @customerID = @@identity
END
else
begin
If @CS = 'XYZ' and @sourceChannel != 'XYZ'
begin
update MCT
set @CS = NULL
where MobileNo = @mobile
and customerID = @CustomerID
and CS = 'XYZ'
end
end
PRJ_TMM_V1.R2 Added By Rishab and Venu on 17 Jun 2020 for TO check TMM Customer End Code
EXEC Usp_Web_GetInterCityJobTypeAndTTD 'Website'
, @dropCityID
, @pickupDateTime
, @pickupSubAreaID
, @pickupAreaID
, @dropAreaID
, @pickupLatitude
, @pickupLongitude
, @dropLatitude
, @dropLongitude
, @pickupCityID
, @jobTypeDesc OUTPUT
, @timetoDispatch OUTPUT
, @onRoadDistance OUTPUT
IF(@sourceChannel in ('XYZ'))
set @timetoDispatch = 60
IF(@sourceChannel in ('MOMD'))
set @timetoDispatch = 40
IF (@jobTypeDesc = '929')
BEGIN
SET @jobTypeID = 1
END
ELSE
IF (@jobTypeDesc = '727')
BEGIN
SET @jobTypeID = 2
END
SET @redeemAmount=(1*@redeemPoints)
IF (@brand = 'nigi')
BEGIN
SET @isnigi = 1
END
IF (@dropArea = @dropSubArea)
SET @destinationAddress = @dropArea
ELSE
SET @destinationAddress = @dropArea + ',' + @dropSubArea
SET @pickupMessage = ''
if @sourcechannel in ('XYZ','MOMD')
Begin
set @pickupMessage = 'ggg & JJJ included in the hhh'
End
if(@corporateCode > 0)
begin
set @corporateJob = 1
set @pickupMessage = 'sgsfs'
set @BusinessCategoryID = 5
end
INSERT INTO Dbo.BJD ( CustomerID, CustomerName, mobile, CustomerContactMobileNo, JobTypeID, PickUpTime, CustomerPickUpAddress, PickUpAddressPointID, PickUpLocalityID, PickUpZoneID, DestinationAddress, DestAddressPointID, DestLocalityId, DestZoneID, JobCreationTime, CityID, UserID, ChannelID, SiebelJobID, SmsStatus, SmsAlert, Quota, TimetoDispatch, EmailAddress, GoldiJob, CorporateJob, MPC, TrackMobile, QuotaBorrowingZoneID, RedeemPoints, PromoCode, RedeemAmount, CouponAmount, PickUpMessage, Isnigi, PickupLat, PickupLong, DropCityID, ReturnTrip, RetentionTime, IsRideShare,DropLat,DropLong,RiderName ,BrandTypeID ,BusinessCategoryID )
VALUES ( @customerID, @customerName, @mobile, @RiderNumber, @jobTypeID, @pickupDateTime, @pickupAddress, @pickupSubareaID, @pickupAreaID, @pickUpZoneID, @DropAddress, @dropSubAreaID, @dropAreaID, @dropZoneID, getdate(), @pickupCityID, @userID, 5, @webBookingReferenceNo, 0, 0, 1, @timetoDispatch, @customerEmail, 0, @corporateJob, @mPC, @trackMobileNo, 1000, @redeemPoints, @promoCode, @redeemAmount
, CASE WHEN @IsRideShare IN (1,2) THEN @RideShareDiscount ELSE @CouponValue END, @pickupMessage, @isnigi, @pickupLatitude, @pickupLongitude, @dropCityID, @returnTrip, (@retentionTime/60), @IsRideShare,@dropLatitude,@dropLongitude,@RiderName,@BrandTypeId ,@BusinessCategoryID)
SET @jobID = @@identity
INSERT INTO tP (Order_Ref_No, Sequence_ID ,Latitude, Longitude,JobId ,Address)
SELECT @webBookingReferenceNo, Sequence_ID, Latitude, Longitude,@JobID,Address FROM @WayPointsTable
IF (ltrim(rtrim(@promocode))='dfjks')
BEGIN
INSERT INTO HDTC ( JobID, CabRegistrationNo, SMSTypeID, SMSText, MobileNo, [Type] )
VALUES ( @jobID, '', 10, 'Great! Your sdfjshk code has been applied successfully. To avail 25% Cashback, please pay via Paytm wallet %26 make sure that it has sufficient balance in it. TCA', @mobile, 'TRANSACTIONAL' )
END
If LEN(ltrim(rtrim(@CorporateCode))) = 0
begin
IF @SourceChannel != 'Goldi'
INSERT INTO dbo.IABD (JobID,PackageId,FixedhhhLogId,TransactionType)
VALUES(@JobID,@PackageId,@FixedhhhLogId,Case When @ProviderID=99 then 'Cash' else null end)
end
else
begin
If @PackageId > 0
begin
SET @StartUniqueId = 0
WHILE @StartUniqueId=0
BEGIN
SET @StartUniqueId=REPLACE(CAST(CAST(RAND()*10000 AS INT) AS VARCHAR(4)),'0','9')
IF (
(
SELECT Count(1)
FROM IABD WITH(NOLOCK)
WHERE otp_fortripstart = @StartUniqueId AND IsDeleted_forTripStart=0 and OTP_ForTripStart != 0)>0
OR LEN(@StartUniqueId) < 4
)
SET @StartUniqueId=0
end
SET @EndUniqueId = 0
WHILE @EndUniqueId =0
BEGIN
SET @EndUniqueId =REPLACE(CAST(CAST(RAND()*10000 AS INT) AS VARCHAR(4)),'0','9')
IF (
(SELECT Count(1)
FROM IABD WITH(NOLOCK)
WHERE otp_fortripend = @EndUniqueId AND IsDeleted_forTripEnd=0 and OTP_ForTripEnd != 0)>0
OR LEN(@EndUniqueId ) < 4
OR @StartUniqueId = @EndUniqueId
)
SET @EndUniqueId =0
end
end
INSERT INTO dbo.IABD
(JobID,PackageId,FixedhhhLogId,OTP_ForTripStart, OTP_ForTripEnd,TransactionType)
VALUES
(@JobID,@PackageId,@FixedhhhLogId, isnull(@StartUniqueId,0), isnull(@EndUniqueId,0)
,Case When @ProviderID=99 then 'Cash' else null end)
end
IF (@onRoadDistance <= 10.0)
BEGIN
SET @isShortTrip = 1
END
IF (@retentionTime > 0)
BEGIN
SET @isRetentionTrip = 1
END
IF (@dropCityID > 0)
BEGIN
SET @isInterCity = 1
END
IF (@jobTypeDesc = '929')
BEGIN
INSERT INTO DST ( JobID, JobStatusID, IsShortTrip, IsRetentionTrip, IsGoldiPickup, IsInterCity, IsGoldiDrop, OnRoadDistance, NavPickupLat, NavPickupLong, NavDropLat, NavDropLong )
VALUES ( @jobID, 1, @isShortTrip, @isRetentionTrip, @isGoldiPickup, @isInterCity, @isGoldiDrop, @onRoadDistance, @pickupLatitude, @pickupLongitude, @dropLatitude, @dropLongitude )
END
ELSE
IF (@jobTypeDesc = '727')
BEGIN
INSERT INTO DST ( JobID, JobStatusID, IsShortTrip, IsRetentionTrip, IsGoldiPickup, IsInterCity, IsGoldiDrop, OnRoadDistance, NavPickupLat, NavPickupLong, NavDropLat, NavDropLong )
VALUES ( @jobID, 0, @isShortTrip, @isRetentionTrip, @isGoldiPickup, @isInterCity, @isGoldiDrop, @onRoadDistance, @pickupLatitude, @pickupLongitude, @dropLatitude, @dropLongitude )
END
IF @bookingType = '8s8'
begin
set @adminEmailid = NULL
set @adminMobileno = NULL
end
INSERT INTO Dbo.refBndf ( WebBookingReferenceNo, PickupDateTime, PickupAddress, PickupArea, PickupSubArea, mobile, CustomerName, DropArea, DropSubArea, City, SourceIPAddress, JobType, BookingType, Mostprivilege, CancelledStageID, CustomerEmail, SourceChannel, TrackMobile, QuotaCheckingZoneID, DropCityID, ReturnTrip, RetentionTime, RedeemPoints, AdminEmailid, AdminMobileno, CorporateName, PaymentType, PromoCode, RequestProcessStatusID, JobID, RequestProcessTime, Response, ResponseProcessStatusID, ResponseProcessTime, JobIDResponseProcessStatusID, JobIDResponse, TTD, OnRoadDistance, RequestType, IMEINumber, IsRideShare,PickupLat,PickupLong,DropLat,DropLong,DropAddress,CorporateCode,BrandTypeID )
VALUES ( @webBookingReferenceNo, @pickupDateTime, @pickupAddress, @pickupArea, @pickupSubArea, @mobile, @customerName, @dropArea, @dropSubArea, @pIckupCity, @sourceIPAddress, @jobTypeDesc, @bookingType, @mPC, @cancelledStageID, @customerEmail, @sourceChannel, @trackMobileNo, @pickupZoneID, @dropCityID, @returnTrip, (@retentionTime/60), @redeemPoints, @adminEmailid, @adminMobileno, @corporateName, @paymentType, @promoCode, 5, @jobID, getdate(), 'OneClick done', 11, getdate(), 11, 'OneClick done', @timetoDispatch, @onRoadDistance, 'LATER', @iMEINumber, @IsRideShare,@pickupLatitude,@pickupLongitude,@dropLatitude,@dropLongitude,@DropAddress,@corporateCode,@BrandTypeID )
IF (@isnigi = 0)
BEGIN
INSERT INTO dSmDES ( JobID, WebRefNo, mobile, CustomerName, Pickuptime, BookingStatus, EmailAddress, ChannelID, PickupArea, PickupSubArea, DropArea, DropSubArea, CityName, PickupAddress ,DestinationAddress )
VALUES ( @jobID, @webBookingReferenceNo, @mobile, @customerName, @pickupDateTime, 'Cab Available', @customerEmail, 5, @pickupArea, @pickupSubArea, @dropArea, @dropSubArea, @pickupCity, @pickupAddress,@DropAddress )
END
ELSE
BEGIN
INSERT INTO dSmDES_nigi ( JobID, WebRefNo, mobile, CustomerName, Pickuptime, BookingStatus, EmailAddress, ChannelID, PickupArea, PickupSubArea, DropArea, DropSubArea, CityName, PickupAddress )
VALUES ( @jobID, @webBookingReferenceNo, @mobile, @customerName, @pickupDateTime, 'Cab Available', @customerEmail, 5, @pickupArea, @pickupSubArea, @dropArea, @dropSubArea, @pickupCity, @pickupAddress )
END
SET @address = @pickupArea+','+@pickupSubArea+','+@pickupAddress
SET @isAssured = 0
IF (@mPC = 1)
BEGIN
SET @isAssured = 1
END
ELSE
IF EXISTS(SELECT DropLocalityName
FROM Dbo.GCAT WITH(NOLOCK)
WHERE PickupLocalityID = PickUpLocalityID AND DropLocalityID = @dropLocalityID)
BEGIN
SET @isAssured = 1
END
INSERT INTO QTC ( JobID, PickupTime, ZoneID, CallerID, CityID, JobTypeID, IsAssured, QuotaBorrowingZoneID )
VALUES ( @jobID, @pickupDateTime, @pickUpZoneID, @mobile, @pickupCityID, @jobTypeID, @isAssured, @pickUpZoneID )
/* Added for Coupon And Referral Checking Bookings */
IF EXISTS(SELECT ReferralCode
FROM CMTHS WITH(NOLOCK)
WHERE ReferralCode=@promoCode)
BEGIN
INSERT INTO TRTM ( JobID, CouponAmount, CustomerName, mobile, PromoCode )
VALUES ( @jobID, @couponValue, @customerName, @mobile, @promoCode )
END
ELSE
IF EXISTS(SELECT mobile
FROM CMTHS WITH(NOLOCK)
WHERE mobile=@mobile AND @promocode='REFBALANCE')
BEGIN
INSERT INTO TRTM ( JobID, CouponAmount, CustomerName, mobile, PromoCode )
VALUES ( @jobID, @couponValue, @customerName, @mobile, @promoCode )
END
ELSE
IF ((@promocode IS NOT NULL AND @promocode != '') AND @promoCode!='REFBALANCE')
BEGIN
INSERT INTO TCM ( mobile, CustomerName, JobID, PromoCode, CouponAmount, CustomerEmail, IMEINumber )
VALUES ( @mobile, @customerName, @jobID, @promoCode, @couponValue, @customerEmail, @iMEINumber )
END
IF NOT EXISTS(SELECT mobile
FROM AUM WITH(NOLOCK)
WHERE mobile=@mobile)
BEGIN
INSERT INTO AUM ( mobile, CustomerEmail )
VALUES ( @mobile, @customerEmail )
END
EXEC PROC3 @jobID
, @customerName
, @mobile
, @address
, @pickupSubArea
, @pickupArea
, @pickupZone
, @tagAs
, @pickupCityID
, @pickupLatitude
, @pickupLongitude
, ''
, @tagAs
, @pickupAddress
--IF(@Brand = 'nigi')
IF (@brand IN('nigi','BrandEve'))
BEGIN
SELECT @pickupSubAreaID = AddressPointID
FROM Dbo.fnPlay(@pickupLatitude,@pickupLongitude,'Brand')
SELECT @dropSubAreaID = AddressPointID
FROM Dbo.fnPlay(@dropLatitude,@dropLongitude,'Brand')
END
SET @ack = 'Accepted|'+convert(varchar,@jobID)+'|'+isnull(@reason,'')+'|'+convert(varchar,@pickupSubareaID)+'|'+convert(varchar,@dropSubareaID)
END
ELSE
BEGIN
SET @ack = 'Bookings Exceeded'
END
END
ELSE
BEGIN
SET @ack = 'TempBlock|'+convert(varchar,@pendingAmount)+'|'+convert(varchar,@transactionDate)
END
IF Not Exists (Select 1 from TRP T With (noLock)
Inner Join BJD J With (noLock) on J.promocode=T.Promocode
where CampaignName ='XYZ'
And T.Promocode=@PromoCode And J.JobID=@JobID
)
And ((Select Isnull(BusinessCategoryID,0) from BJD Where JOBID=@JOBID)<>2)
EXEC [Proc3] @JobID,@mobile,'','',@JobID,@WalletBalance,@ProviderID,@AppVersion,@CustomerWalletBalance,1
end
ELSE
BEGIN
INSERT INTO Dbo.refBndf ( WebBookingReferenceNo, PickupDateTime, PickupAddress, PickupArea, PickupSubArea, mobile, CustomerName, DropArea, DropSubArea, City, SourceIPAddress, RecordDattime, RequestProcessStatusID, RequestProcessTime, JobID, TaxiNo, SubscriberName, SubscriberMobileNo, ResponseProcessStatusID, JobType, BookingType, Mostprivilege, CancelledStageID, CustomerEmail, SourceChannel, TrackMobile, Response, TTD, OnRoadDistance, DropCityID, IMEINumber,PickupLat,PickupLong,DropLat,DropLong,DropAddress )
VALUES ( @webBookingReferenceNo, @pickupDateTime, @pickupAddress, @pickupArea, @pickupSubArea, @mobile, @customerName, isnull(@dropArea,'NA'), @dropSubArea, @pickupCity, @sourceIPAddress, getdate(), 9, getdate(), 0, '', '', '', 11, @jobTypeDesc, @bookingType, @mPC, @cancelledStageID, @customerEmail, @sourceChannel, @trackMobileNo, 'OneClick done', @timetoDispatch, @onRoadDistance, @dropCityID, @iMEINumber,@pickupLatitude,@pickupLongitude,@dropLatitude,@dropLongitude,@DropAddress )
SET @ack = 'Invalid Location'
END
end
else
begin
SET @ACK = 'Not Exactometer Coupon'
end
END
ELSE
BEGIN
SET @ack = 'Repeated Data'
END
END
ELSE
BEGIN
SET @ack = 'Invalid PickupTime'
IF @pickupCityID = 19 SET @ack = 'Cab not available'
END
END
ELSE
BEGIN
SET @ack = 'Package Not Found'
END
END
SELECT @ack AS Acknowledgement
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
DECLARE @errorMessage nvarchar(4000)
SET @errorMessage = error_message()
INSERT INTO Dbo.TblErrorMsgForBusiness ( ErrorMsg, StoredProcedureName, JobID )
VALUES ( @errorMessage, 'Proc2', @webBookingReferenceNo )
INSERT INTO dbo.DB_Errors
(
MobileRefNo,
UserName,
ErrorNumber,
ErrorState,
ErrorSeverity,
ErrorLine,
ErrorProcedure,
ErrorMessage,
ErrorDateTime
)
SELECT
@webBookingReferenceNo,
SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE()
END CATCH
END
Thursday, July 16, 2020 6:22 PM
This is Proc2
ALTER PROCEDURE [dbo].[Proc2] @webBookingReferenceNo varchar(50)
, @pickupDateTime varchar(30)
, @pickupAddress varchar(500)
, @mobile varchar(15)
, @customerName varchar(50)
, @pickupLatitude real
, @pickupLongitude real
, @dropLatitude real
, @dropLongitude real
, @DropAddress VARCHAR(500) = ''
, @brand varchar(10)
,
@sourceIPAddress varchar(70)
, @bookingType varchar(50) = ''
,
@customerEmail varchar(100) = ''
, @trackMobileNo varchar(30) = ''
, @sourceChannel varchar(25)
, @returnTrip bit = 0
, @retentionTime int = 0
, @redeemPoints int = 0
, @adminEmailid nvarchar(100) = ''
, @adminMobileno varchar(12) = ''
, @corporateCode varchar(50) = ''
, @corporateName varchar(100) = ''
, @paymentType tinyint = 0
,
@promoCode varchar(20) = ''
, @iMEINumber varchar(200) = ''
, @IsRideShare TINYINT = 0
, @RideShareDiscount MONEY = 0
, @RiderName VARCHAR(100)=''
, @RiderNumber varchar(40)=''
, @isInterCity TINYINT = 0
, @ProviderID INT=99
, @Walletbalance money=0
, @AppVersion varchar(20)=''
, @CustomerWalletBalance Money=0
, @PackageId smallint=0
, @FixedhhhLogId INT = 0
, @City_Name Varchar(50) = ''
, @WayPointsTable dbo.tP_Table READONLY
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @ack varchar(150)
DECLARE @SCN Varchar(50)
DECLARE @DCN Varchar(50)
Declare @CI int
declare @PZ int
Declare @DZ int
Declare @CS Varchar(30) = NULL
SELECT @CI = CityID, @PZ = ZoneID FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
SELECT @DZ = ZoneID FROM Dbo.Function_test(@dropLatitude,@dropLongitude,'DELHI')
select @SCN = SourceCity, @DCN = DestinationCity from Tbl where OrderRefNo = @webBookingReferenceNo
If
(
(@pickupDateTime <= '2020-12-31 23:59:59.998')
AND
(@CI ) in (33)
AND
@sourceChannel != 'XYZ'
)
OR
(
(@pickupDateTime <= '2020-12-31 23:59:59.998')
AND @sourceChannel = 'XYZ'
and (@SCN = 'Kolkata' or @DCN = 'Kolkata')
)
Below code added to block Banglore bookings on Saturday evening ,Sunday full day ,Monday morning by Mahesh on 10-Jul-2020
OR
(
(@pickupDateTime <= '2020-07-22 23:59:59.998')
AND
(@CI ) in (5)
AND
@sourceChannel != 'XYZ'
)
OR
(
(@pickupDateTime <= '2020-07-23 23:59:59.998')
AND
(@CI ) in (6)
)
BEGIN
SET @ack = 'Sorry, We can not serve your request due to India LOCKDOWN'
END
ELSE
BEGIN
set @DropAddress = replace(@DropAddress,'|',',')
set @PickupAddress = replace(@PickupAddress,'|',',')
IF(Len(ltrim(rtrim(@CorporateCode))) > 0)
BEGIN
SET @Walletbalance = 0
END
DECLARE @timeDiff smallint
DECLARE @jobTypeDesc varchar(50)
DECLARE @processStatusID smallint
DECLARE @quotaCount smallint
DECLARE @mPC bit
DECLARE @is_Active bit
SET @mPC = 0
DECLARE @pickupLat real
DECLARE @pickupLong real
DECLARE @localityID int
DECLARE @pickupZone varchar(100)
DECLARE @pickupZoneID int
DECLARE @dropLat real
DECLARE @dropLong real
DECLARE @cancelledStageID varchar(5)
DECLARE @customerCancellations varchar(10)
DECLARE @totalBookings varchar(10)
DECLARE @count int
DECLARE @cabsAvailableOutput int
DECLARE @prevQuotaOutput varchar(10)
DECLARE @nextQuotaOutput varchar(10)
DECLARE @travelDistance float
DECLARE @cityID smallint
DECLARE @pickupSubLocalityID int
DECLARE @dropLocalityID int
DECLARE @pickupCityID int
DECLARE @pickupCity varchar(30)
DECLARE @pickupArea varchar(50)
DECLARE @pickupAreaID int
DECLARE @pickupSubArea varchar(50)
DECLARE @pickupSubAreaID int
DECLARE @destinationAddress varchar(500)
DECLARE @dropArea varchar(50)
DECLARE @dropAreaID int
DECLARE @dropSubArea varchar(50)
DECLARE @dropSubAreaID int
DECLARE @dropZoneID int
DECLARE @dropCityID int
DECLARE @dropCity varchar(40)
DECLARE @coreCity varchar(50)
DECLARE @userID int
DECLARE @customerID int
DECLARE @jobID int
DECLARE @timeSlotID tinyint
DECLARE @redeemAmount money
DECLARE @isnigi tinyint
DECLARE @timetoDispatch smallint
DECLARE @tagAs varchar(10)
DECLARE @address varchar(500)
DECLARE @couponEndDate datetime
DECLARE @couponStartDate datetime
DECLARE @reason varchar(100)
DECLARE @couponValue money
DECLARE @isAssured bit
DECLARE @startHour varchar(30)
DECLARE @endHour varchar(30)
DECLARE @jobTypeID tinyint
DECLARE @ranCouponStartDate datetime
DECLARE @ranCouponEndDate datetime
DECLARE @couponCityDeleted int
DECLARE @onRoadDistance float
DECLARE @isShortTrip bit
DECLARE @isRetentionTrip bit
DECLARE @isGoldiPickup bit
--DECLARE @isInterCity bit
DECLARE @isGoldiDrop varchar(10)
DECLARE @couponID int
/*Added for Referral*/
DECLARE @referredCustomerName varchar(30)
DECLARE @referredmobile varchar(30)
DECLARE @referralAmount varchar(30)
DECLARE @blacklistedNumber bit
DECLARE @pendingAmount money
DECLARE @transactionDate varchar(10)
DECLARE @usageCount tinyint
DECLARE @pickupMessage varchar(100)
/*For corporate Flag*/
DECLARE @corporateCustomerID int
DECLARE @corporateJob bit
DECLARE @BusinessCategoryID tinyint
Declare @StartUniqueId varchar(6) = 0
Declare @EndUniqueId varchar(6) = 0
SET @BusinessCategoryID = 1
SET @corporateJob=0
SET @customerID = 0
SET @cancelledStageID = 0
SET @onRoadDistance = 0
SET @isShortTrip = 0
SET @isRetentionTrip = 0
SET @isGoldiPickup = 0
SET @isInterCity = 0
SET @isGoldiDrop = 0
SET @ack = 'Error'
IF(@sourceChannel = 'XYZ')
BEGIN
DECLARE @DISTANCE INT
DECLARE @cityname varchar(30)
DECLARE @Dest_CityName Varchar(30)
select @DISTANCE = TotalDistance, @cityname= SourceCity, @Dest_CityName= DestinationCity from Tbl where OrderRefNo = @webBookingReferenceNo
IF @cityname in ('sjhgs')
BEGIN
SET @PackageId = 7
END
ELSE
BEGIN
IF(@Dest_CityName = 'gunda')
set @cityname = 'gunda'
select @cityID=CityID from CM where CityName = @cityname
IF(@cityID = 1)
BEGIN
IF(@DISTANCE <= 10)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=10 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
END
IF(@cityID = 3)
BEGIN
SET @PackageId = 6
END
IF(@cityID = 4)
BEGIN
IF(@DISTANCE <= 15)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=15 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 15 and @DISTANCE <= 25)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=25 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=30 and PackageName like 'TMM D2K%'
END
IF(@cityID = 5)
BEGIN
IF(@DISTANCE <= 20)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 20 and @DISTANCE <= 30)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=30 and PackageName like 'TMM D2K%'
ELSE IF (@DISTANCE > 30 and @DISTANCE <= 35)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=35 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=45 and PackageName like 'TMM D2K%'
END
IF(@cityID = 14)
BEGIN
IF(@DISTANCE <= 10)
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=10 and PackageName like 'TMM D2K%'
ELSE
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=20 and PackageName like 'TMM D2K%'
END
IF(@cityID = 37)
BEGIN
SET @PackageId = 2
END
IF(@cityID = 6)
BEGIN
select @PackageId = PackageId from tblPkg where CityId=@cityID and BaseKM=5 and PackageName like 'TMM D2K%'
END
IF(@cityID = 10)
BEGIN
select @PackageId = PackageId from tblPkg where CityId=@CityID and BaseKM=5 and PackageName like 'TMM D2K%'
END
END
END
IF((@sourceChannel = 'XYZ' AND @PackageId != 0) OR @sourceChannel != 'XYZ')
BEGIN
SET @promoCode = replace(replace(@promoCode,' ',''),'.','')
IF @SourceChannel ='ashg' AND GETDATE() <= '2016-10-31 23:59:59.991'
SET @Promocode = 'BRO'
DECLARE @BrandTypeId TINYINT
IF @Brand IN ('aaa','bbb')
BEGIN
SELECT @BrandTypeId = BrandTypeID
FROM tblBrandTypeMaster
WHERE BrandTypeName = @Brand
SET @Brand = 'Brand'
END
SELECT @pickupCityID=CityID
FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
DECLARE @NoOfBookings TINYINT
DECLARE @NoOfDays TINYINT
If @sourceChannel = 'WebMultiple'
BEGIN
SET @NoOfBookings = 30
SET @NoOfDays = 30
END
else If @sourceChannel in ('XYZ')
BEGIN
SET @NoOfBookings = 100
SET @NoOfDays=90
END
else If @sourceChannel in ('PQR')
BEGIN
SET @NoOfBookings = 100
SET @NoOfDays=60
END
ELSE
BEGIN
IF (
( SELECT count(JB.JobID)
FROM BJD JB WITH(NOLOCK)
INNER JOIN tblWallettripstartsendingDetails wts ON JB.JobID = wts.JobID
INNER JOIN DST JSD ON JB.JobID = JSD.JobID
INNER JOIN MSJ JSM ON JSD.JobstatusID = JSM.JobstatusID
WHERE JB.mobile = @mobile
AND JB.PickupTime BETWEEN dateadd(Minute,-240,getdate())
AND dateadd(day,7,getdate()) AND JSM.Is_Pending = 1 AND wts.ProviderID > 999
) >= 1
AND @ProviderId > 999
)
SET @NoOfBookings = 1
else
SET @NoOfBookings = 5
SET @NoOfDays = 30
END
IF ((@pickupDateTime > getdate()AND (@pickupDateTime BETWEEN getdate() AND cast(convert(varchar,dateadd(day,@NoOfDays,getdate()),101)+' '+'23:59:59' AS datetime))))
AND (@pickupCityID!=19)
BEGIN
IF NOT EXISTS (SELECT WebBookingReferenceNo
FROM refBndf WITH (NOLOCK)
WHERE WebBookingReferenceNo=@webBookingReferenceNo)
BEGIN
SELECT @pickupCityID=CityID
, @pickupCity = CityName
, @pickupZoneID = ZoneID
, @pickupAreaID = LocalityID
, @pickupArea = LocalityName
, @pickupSubArea = AddressPointName
, @pickupSubAreaID = AddressPointID
, @pickupZone = PickupZone
FROM Dbo.Function_test1(@pickupLatitude,@pickupLongitude,'Brand')
if (select cityid from CM where cityname = @city_name) != @pickupCityID
set @pickupAreaID = 0
IF (@pickupAreaID = 0)
BEGIN
IF @City_Name in ('Faridabad','Gurgaon','Ghaziabad','Noida')
SET @City_Name = 'Delhi'
IF @City_Name in ('Navi gunda','Panvel','Thane')
SET @City_Name = 'gunda'
SELECT @pickupCityID=c.CityID
, @pickupCity = c.CityName
, @pickupZoneID = z.ZoneID
, @pickupAreaID = l.LocalityID
, @pickupArea = l.LocalityName
, @pickupSubArea = a.AddressPointName
, @pickupSubAreaID = a.AddressPointID
, @pickupZone = z.zonename
FROM Zone Z
INNER JOIN CM C ON C.CityID = Z.CityID
RIGHT JOIN LM L ON L.ZoneID = Z.ZoneID
RIGHT JOIN PM A ON A.LocalityID = L.LocalityID
WHERE z.ZoneName = convert(varchar(100),@City_Name)+' Region' and z.Z_IsDeleted = 1
END
-- End Code below-- Added by rishab on 13 jul 2020 for handle invalid location
SELECT @dropCityID=CityID
, @dropCity = CityName
, @dropZoneID = ZoneID
, @dropAreaID = LocalityID
, @dropArea = LocalityName
, @dropSubArea = AddressPointName
, @dropSubAreaID = AddressPointID
FROM Dbo.Function_test(@dropLatitude,@dropLongitude,@pickupCity)
SELECT @isGoldiPickup = Is_Goldi
FROM PM
WHERE AddressPointID = @pickupSubAreaID
SELECT @isGoldiDrop = Is_Goldi
FROM PM
WHERE AddressPointID = @dropSubAreaID
SELECT @coreCity = CityName
FROM CM
WHERE CityID IN (SELECT CoreCityID
FROM CM
WHERE CityID = @pickupCityID
)
CREATE table #temp ( Address varchar(500) )
CREATE table #temp1 ( Address varchar(500) )
IF (@pickupAddress LIKE '%Madhya Pradesh%')
SET @pickupAddress = @coreCity
SET @pickupAddress = replace(@pickupAddress,', ',',')
IF @pickupCityID = 5 AND @promoCode = 'AIR699' AND @IsRideShare = 0
SET @IsRideShare = 2
IF (@promoCode != '')
BEGIN
SELECT @couponCityDeleted= CityDeleted
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
AND City=@pickupCity
IF EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
SELECT @couponStartDate= CouponStartDate
, @couponEndDate=CouponEndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponValue
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
IF ((@promoCode IN ('DHY','JKSH')) OR (@promocode IN ('sdlkjf') AND @sourceChannel IN ('dlfj','sdfkl')) )
BEGIN
IF EXISTS(SELECT mobile
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @promoCode=''
SET @couponValue=0
END
END
ELSE
IF ((@promocode IN ('gsf') AND @sourceChannel NOT IN ('dfdf','dfsdgf')))
BEGIN
SET @promoCode=''
SET @couponValue=0
END
ELSE
IF ((@promocode IN ('sfjkgdfkl') AND @sourceChannel NOT IN ('nsflksgsl','lkgdjg')) )
BEGIN
IF (SELECT count(1)
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode = @promoCode AND IsDeleted=0)>=3
BEGIN
SET @promoCode=''
SET @couponValue=0
END
END
ELSE
IF (@promocode='fgnklghk')
BEGIN
SET @promoCode=''
SET @couponValue=0
END
ELSE
IF (@promocode='slgslhgs' AND @sourceChannel IN ('lskgsdfhkg'))
BEGIN
SET @promoCode=''
SET @couponValue=0
END
IF EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode AND ServiceType=@brand AND City=@pickupCity)
BEGIN
SELECT @couponCityDeleted= CityDeleted
FROM TCM
WHERE PromoCode=@promoCode
AND ServiceType=@brand
AND City=@pickupCity
IF (@promoCode='sdfsd')
BEGIN
SET @reason='fgkjslfg'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@promoCode IN ('sdfsf') AND @couponCityDeleted=0)
BEGIN
IF EXISTS(SELECT mobile
FROM AUM M WITH(NOLOCK)
WHERE mobile=@mobile AND TripStatus=1)
BEGIN
SET @reason='kjgs'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL)
BEGIN
IF EXISTS(SELECT IMEINumber
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND PromoCode=@promocode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
END
ELSE
IF EXISTS(SELECT mobile
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF EXISTS(SELECT CustomerEmail
FROM TCM WITH(NOLOCK)
WHERE CustomerEmail=@customerEmail AND PromoCode=@promoCode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
END
END
ELSE
IF (((@promoCode IN ('sfvsf')) OR(@promoCode IN ('AIR25') AND (@isGoldiDrop=1 OR @isGoldiPickup=1))) AND @couponCityDeleted=0)
BEGIN
SET @usageCount=(SELECT count(1)
FROM TCM WITH(NOLOCK)
WHERE mobile=@mobile AND PromoCode=@promoCode AND IsDeleted=0 )
IF (@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL) AND @usageCount>0
BEGIN
IF @usageCount >= (CASE WHEN @promoCode = 'AIR25' THEN 10
ELSE 5 END )
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF NOT EXISTS(SELECT 1
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND Promocode=@promocode AND IsDeleted=0) AND @usageCount < (CASE WHEN @promoCode = 'AIR25' THEN 10
ELSE 5 END )
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
IF ((@promoCode='Goldi150' AND @isGoldiDrop=0) OR (@promoCode='AIR25' AND (@isGoldiDrop=0 OR @isGoldiPickup=0)))
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@promoCode IN ('SAM150','Brand150','GBrand150','CDBrand150','CHN50','BIG50','Goldi150','JAN26','VAL14','AIR25') AND @couponCityDeleted=1)
BEGIN
SET @reason='Coupon Expired'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF @promoCode IN ('TRUE','GTRUE','CDTRUE') AND @pickupCityID ! = @dropCityID
BEGIN
SET @reason='CouponNotValidforIntercity'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@pickupdateTime BETWEEN @couponStartDate AND @couponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF NOT EXISTS(SELECT PromoCode
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode AND City=@pickupCity)
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode=''
SET @couponValue = 0
END
ELSE
IF (@couponCityDeleted=1) --EXISTS(SELECT PromoCode FROM TCM WITH(NOLOCK) WHERE PromoCode=@PromoCode AND City=@PickupCity AND CityDeleted=1)
BEGIN
SET @reason='Coupon Expired'
SET @promoCode=''
SET @couponValue = 0
END
END
/*/*PART-1 E-COUPON*/*/
/*PART -2-Random COUPON*/
/*Added logic to validate Random coupons excluding Select customers*/
ELSE
IF NOT EXISTS(SELECT 1
FROM TSC WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
IF EXISTS(SELECT 1
FROM TRP WITH(NOLOCK)
WHERE PromoCode=@promoCode AND Status=0)
BEGIN
SELECT @couponID=M.ID
, @ranCouponStartDate= StartDate
, @ranCouponEndDate=EndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponAmount
FROM TRC M WITH(NOLOCK)
INNER JOIN TRP R WITH(NOLOCK) ON R.ID=M.ID
WHERE R.PromoCode=@promoCode
IF (@couponID IN (215,214,213,212,211))
BEGIN
IF (@isGoldiDrop=1 AND @pickupCityID IN (4,5))
BEGIN
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
BEGIN
SET @reason='Invalid Coupon'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
END
ELSE
IF EXISTS(SELECT 1
FROM TSC WITH(NOLOCK)
WHERE PromoCode=@promoCode AND mobile=@mobile)
BEGIN
SELECT @couponID=M.ID
, @ranCouponStartDate= StartDate
, @ranCouponEndDate=EndDate
, @startHour = StartHour
, @endHour = EndHour
, @couponValue = CouponAmount
FROM TRC M WITH(NOLOCK)
INNER JOIN TRP R WITH(NOLOCK) ON R.ID=M.ID
WHERE R.PromoCode=@promoCode
DECLARE @percentageDiscountID tinyint
SELECT @percentageDiscountID=PercentageDiscountID
FROM TSC WITH(NOLOCK)
WHERE mobile=@mobile
AND PromoCode=@promoCode
DECLARE @isTrue tinyint
SET @isTrue=1
Not Exists
SELECT @isTrue=0
Exists
FROM TCM WITH(NOLOCK)
WHERE PromoCode=@promoCode
AND mobile=@mobile
AND IsDeleted=0
IF EXISTS(SELECT 1
FROM TRP WITH(NOLOCK)
WHERE PromoCode=@promoCode)
BEGIN
IF (@isTrue=1 AND (@percentageDiscountID IN (183,171)))
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
IF ((@iMEINumber!='' AND @iMEINumber NOT IN ('000000000000000','0000000000000000000000000000000000000000000000000000000000000000') AND @iMEINUmber IS NOT NULL) AND @percentageDiscountID IN (171))
BEGIN
IF NOT EXISTS(SELECT IMEINumber
FROM TCM WITH(NOLOCK)
WHERE IMEINumber=@iMEINumber AND PromoCode=@promocode AND IsDeleted=0)
BEGIN
SET @reason='Coupon Already Used'
SET @promoCode = ''
SET @couponValue = 0
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
IF (@pickupdateTime BETWEEN @ranCouponStartDate AND @ranCouponEndDate)
BEGIN
SET @reason=convert(varchar,@couponValue)
IF (@couponValue > 1)
BEGIN
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' E-Coupon Discount applicable'
END
ELSE
BEGIN
SET @pickupMessage = convert(varchar,cast(@couponValue * 100 AS int)) + '% Discount applicable on Running hhh'
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
END
ELSE
BEGIN
SET @reason='Coupon Invalid for PickupDateTime'
SET @promoCode = ''
SET @couponValue = 0
END
END
ELSE
BEGIN
IF (@referralAmount>'0.00')
BEGIN
IF NOT EXISTS(SELECT mobile
FROM TRTM WITH(NOLOCK)
WHERE mobile=@mobile AND InUse=0 AND PromoCode='REFBALANCE')
BEGIN
SET @couponValue=(@referralAmount)
SET @promoCode='REFBALANCE'
SET @reason=convert(varchar,@couponValue)
SET @pickupMessage ='Rs'+' '+ convert(varchar,@couponValue)+' Referral Balance applicable'
END
ELSE
BEGIN
SET @couponValue='0.00'
SET @reason=convert(varchar,@couponValue)
END
END
ELSE /*without any coupon */
BEGIN
SET @reason=convert(varchar,0.00)
END
END
Declare @booking_denied bit
Set @booking_denied = 0
IF @Promocode in('dsfknl','dsgsdg') and @IsRideShare != 2
BEGIN
SET @booking_denied = 1
END
If @booking_denied = 0
Begin
IF (@pickupAreaID != 0)
BEGIN
SET @blacklistedNumber = 0
If (LEN(ltrim(rtrim(@CorporateCode))) = 0 AND LEN(ltrim(rtrim(@corporateName))) = 0 and @sourcechannel not in ('XYZ','MOMD')) Not for corporate jobs, added 'XYZ' by venu
Begin
SELECT @blacklistedNumber = 1
, @pendingAmount = sum(Amount)
, @transactionDate = cast(day(max(TransactionDateTime)) AS varchar) + '-' + convert(char(3),datename(month,max(TransactionDateTime)),0)
FROM CDSBusiness.Dbo.PTD
WHERE MobileNumber = @mobile
AND WalletSMStype in ('WP', 'WS', 'PWS')
AND WPProcessStatus=0
GROUP BY MobileNumber
end
IF (@blacklistedNumber = 0)
BEGIN
IF ((SELECT count(JB.JobID)
FROM BJD JB WITH(NOLOCK)
INNER JOIN DST JSD ON JB.JobID = JSD.JobID
INNER JOIN MSJ JSM ON JSD.JobstatusID = JSM.JobstatusID
WHERE JB.mobile = @mobile AND JB.PickupTime BETWEEN dateadd(Minute,-240,getdate())
AND dateadd(day,7,getdate()) AND JSM.Is_Pending = 1 AND JB.ChannelID = 5) < @NoOfBookings) or @sourcechannel in ('XYZ','MOMD') --added 'XYZ' by venu
BEGIN /*Added by S Prasuna for Map Navigation Feature on 10 th Feb 2015*/
DECLARE @navPickupLat real
DECLARE @navPickupLong real
DECLARE @pickupSubLocLat real
DECLARE @pickupSubLocLong real
SELECT @pickupSubLocLat = AddressPointLat
, @pickupSubLocLong = AddressPointLong
FROM PM
WHERE AddressPointID = @pickupSubAreaID
SET @navPickupLat = 0
SET @navPickupLong = 0
SELECT TOP 1 @navPickupLat = CabLatitude_TS
, @navPickupLong = CabLongitude_TS
FROM BJD JB
INNER JOIN TblTripMaster TM ON JB.JobID = TM.JobID
WHERE mobile = @mobile
AND JB.PickUpAddressPointID = @pickupSubAreaID
AND TM.GPSValueSync = 'A'
ORDER BY TripEndID DESC
IF (@navPickupLat = 0 OR isnull((SELECT Dbo.FnBD(@navPickupLat,@navPickupLong,@pickupSubLocLat,@pickupSubLocLong)),0) > 2)
BEGIN
SET @navPickupLat = @pickupSubLocLat
SET @navPickupLong = @pickupSubLocLong
END
SET @isnigi = 0
SET @mPC = 0
IF EXISTS(SELECT CallerID
FROM TbCMPCustomerMaster
WHERE CallerID = @mobile)
BEGIN
SET @mPC = 1
END
IF (@dropCityID = @pickupCityID)
BEGIN
SET @dropCityID = 0
SET @returnTrip = 0
END
SELECT @userID = UserID
FROM TblUserMaster WITH(NOLOCK)
WHERE UserName = 'WebUser'
SELECT @customerID = CustomerID
, @corporateCustomerID=CorporateCustomerID
, @CS = CS PRJ_TMM_V1.R2 Added By Rishab and Venu on 17 Jun 2020 for TO check TMM Customer Start Code
FROM Dbo.MCT WITH ( NOLOCK )
WHERE MobileNo = @mobile
IF (@corporateCustomerID=2952)
BEGIN
SET @corporateJob=1
END
IF (@customerID = 0)
begin
If @sourceChannel = 'XYZ'
begin
Set @CS = @sourceChannel
end
INSERT INTO MCT ( CustomerName, MobileNo, CallDateTime, CustomerTypeID, ChannelID, CityID, CustomerStatusID, WEB, EmailID ,CS)
VALUES ( @customerName, @mobile, getdate(), 1, 5, @pickupCityID, 1, 1, @customerEmail ,@CS)
SELECT @customerID = @@identity
END
else
begin
If @CS = 'XYZ' and @sourceChannel != 'XYZ'
begin
update MCT
set @CS = NULL
where MobileNo = @mobile
and customerID = @CustomerID
and CS = 'XYZ'
end
end
PRJ_TMM_V1.R2 Added By Rishab and Venu on 17 Jun 2020 for TO check TMM Customer End Code
EXEC Usp_Web_GetInterCityJobTypeAndTTD 'Website'
, @dropCityID
, @pickupDateTime
, @pickupSubAreaID
, @pickupAreaID
, @dropAreaID
, @pickupLatitude
, @pickupLongitude
, @dropLatitude
, @dropLongitude
, @pickupCityID
, @jobTypeDesc OUTPUT
, @timetoDispatch OUTPUT
, @onRoadDistance OUTPUT
IF(@sourceChannel in ('XYZ'))
set @timetoDispatch = 60
IF(@sourceChannel in ('MOMD'))
set @timetoDispatch = 40
IF (@jobTypeDesc = '929')
BEGIN
SET @jobTypeID = 1
END
ELSE
IF (@jobTypeDesc = '727')
BEGIN
SET @jobTypeID = 2
END
SET @redeemAmount=(1*@redeemPoints)
IF (@brand = 'nigi')
BEGIN
SET @isnigi = 1
END
IF (@dropArea = @dropSubArea)
SET @destinationAddress = @dropArea
ELSE
SET @destinationAddress = @dropArea + ',' + @dropSubArea
SET @pickupMessage = ''
if @sourcechannel in ('XYZ','MOMD')
Begin
set @pickupMessage = 'ggg & JJJ included in the hhh'
End
if(@corporateCode > 0)
begin
set @corporateJob = 1
set @pickupMessage = 'sgsfs'
set @BusinessCategoryID = 5
end
INSERT INTO Dbo.BJD ( CustomerID, CustomerName, mobile, CustomerContactMobileNo, JobTypeID, PickUpTime, CustomerPickUpAddress, PickUpAddressPointID, PickUpLocalityID, PickUpZoneID, DestinationAddress, DestAddressPointID, DestLocalityId, DestZoneID, JobCreationTime, CityID, UserID, ChannelID, SiebelJobID, SmsStatus, SmsAlert, Quota, TimetoDispatch, EmailAddress, GoldiJob, CorporateJob, MPC, TrackMobile, QuotaBorrowingZoneID, RedeemPoints, PromoCode, RedeemAmount, CouponAmount, PickUpMessage, Isnigi, PickupLat, PickupLong, DropCityID, ReturnTrip, RetentionTime, IsRideShare,DropLat,DropLong,RiderName ,BrandTypeID ,BusinessCategoryID )
VALUES ( @customerID, @customerName, @mobile, @RiderNumber, @jobTypeID, @pickupDateTime, @pickupAddress, @pickupSubareaID, @pickupAreaID, @pickUpZoneID, @DropAddress, @dropSubAreaID, @dropAreaID, @dropZoneID, getdate(), @pickupCityID, @userID, 5, @webBookingReferenceNo, 0, 0, 1, @timetoDispatch, @customerEmail, 0, @corporateJob, @mPC, @trackMobileNo, 1000, @redeemPoints, @promoCode, @redeemAmount
, CASE WHEN @IsRideShare IN (1,2) THEN @RideShareDiscount ELSE @CouponValue END, @pickupMessage, @isnigi, @pickupLatitude, @pickupLongitude, @dropCityID, @returnTrip, (@retentionTime/60), @IsRideShare,@dropLatitude,@dropLongitude,@RiderName,@BrandTypeId ,@BusinessCategoryID)
SET @jobID = @@identity
INSERT INTO tP (Order_Ref_No, Sequence_ID ,Latitude, Longitude,JobId ,Address)
SELECT @webBookingReferenceNo, Sequence_ID, Latitude, Longitude,@JobID,Address FROM @WayPointsTable
IF (ltrim(rtrim(@promocode))='dfjks')
BEGIN
INSERT INTO HDTC ( JobID, CabRegistrationNo, SMSTypeID, SMSText, MobileNo, [Type] )
VALUES ( @jobID, '', 10, 'Great! Your sdfjshk code has been applied successfully. To avail 25% Cashback, please pay via Paytm wallet %26 make sure that it has sufficient balance in it. TCA', @mobile, 'TRANSACTIONAL' )
END
If LEN(ltrim(rtrim(@CorporateCode))) = 0
begin
IF @SourceChannel != 'Goldi'
INSERT INTO dbo.IABD (JobID,PackageId,FixedhhhLogId,TransactionType)
VALUES(@JobID,@PackageId,@FixedhhhLogId,Case When @ProviderID=99 then 'Cash' else null end)
end
else
begin
If @PackageId > 0
begin
SET @StartUniqueId = 0
WHILE @StartUniqueId=0
BEGIN
SET @StartUniqueId=REPLACE(CAST(CAST(RAND()*10000 AS INT) AS VARCHAR(4)),'0','9')
IF (
(
SELECT Count(1)
FROM IABD WITH(NOLOCK)
WHERE otp_fortripstart = @StartUniqueId AND IsDeleted_forTripStart=0 and OTP_ForTripStart != 0)>0
OR LEN(@StartUniqueId) < 4
)
SET @StartUniqueId=0
end
SET @EndUniqueId = 0
WHILE @EndUniqueId =0
BEGIN
SET @EndUniqueId =REPLACE(CAST(CAST(RAND()*10000 AS INT) AS VARCHAR(4)),'0','9')
IF (
(SELECT Count(1)
FROM IABD WITH(NOLOCK)
WHERE otp_fortripend = @EndUniqueId AND IsDeleted_forTripEnd=0 and OTP_ForTripEnd != 0)>0
OR LEN(@EndUniqueId ) < 4
OR @StartUniqueId = @EndUniqueId
)
SET @EndUniqueId =0
end
end
INSERT INTO dbo.IABD
(JobID,PackageId,FixedhhhLogId,OTP_ForTripStart, OTP_ForTripEnd,TransactionType)
VALUES
(@JobID,@PackageId,@FixedhhhLogId, isnull(@StartUniqueId,0), isnull(@EndUniqueId,0)
,Case When @ProviderID=99 then 'Cash' else null end)
end
IF (@onRoadDistance <= 10.0)
BEGIN
SET @isShortTrip = 1
END
IF (@retentionTime > 0)
BEGIN
SET @isRetentionTrip = 1
END
IF (@dropCityID > 0)
BEGIN
SET @isInterCity = 1
END
IF (@jobTypeDesc = '929')
BEGIN
INSERT INTO DST ( JobID, JobStatusID, IsShortTrip, IsRetentionTrip, IsGoldiPickup, IsInterCity, IsGoldiDrop, OnRoadDistance, NavPickupLat, NavPickupLong, NavDropLat, NavDropLong )
VALUES ( @jobID, 1, @isShortTrip, @isRetentionTrip, @isGoldiPickup, @isInterCity, @isGoldiDrop, @onRoadDistance, @pickupLatitude, @pickupLongitude, @dropLatitude, @dropLongitude )
END
ELSE
IF (@jobTypeDesc = '727')
BEGIN
INSERT INTO DST ( JobID, JobStatusID, IsShortTrip, IsRetentionTrip, IsGoldiPickup, IsInterCity, IsGoldiDrop, OnRoadDistance, NavPickupLat, NavPickupLong, NavDropLat, NavDropLong )
VALUES ( @jobID, 0, @isShortTrip, @isRetentionTrip, @isGoldiPickup, @isInterCity, @isGoldiDrop, @onRoadDistance, @pickupLatitude, @pickupLongitude, @dropLatitude, @dropLongitude )
END
IF @bookingType = '8s8'
begin
set @adminEmailid = NULL
set @adminMobileno = NULL
end
INSERT INTO Dbo.refBndf ( WebBookingReferenceNo, PickupDateTime, PickupAddress, PickupArea, PickupSubArea, mobile, CustomerName, DropArea, DropSubArea, City, SourceIPAddress, JobType, BookingType, Mostprivilege, CancelledStageID, CustomerEmail, SourceChannel, TrackMobile, QuotaCheckingZoneID, DropCityID, ReturnTrip, RetentionTime, RedeemPoints, AdminEmailid, AdminMobileno, CorporateName, PaymentType, PromoCode, RequestProcessStatusID, JobID, RequestProcessTime, Response, ResponseProcessStatusID, ResponseProcessTime, JobIDResponseProcessStatusID, JobIDResponse, TTD, OnRoadDistance, RequestType, IMEINumber, IsRideShare,PickupLat,PickupLong,DropLat,DropLong,DropAddress,CorporateCode,BrandTypeID )
VALUES ( @webBookingReferenceNo, @pickupDateTime, @pickupAddress, @pickupArea, @pickupSubArea, @mobile, @customerName, @dropArea, @dropSubArea, @pIckupCity, @sourceIPAddress, @jobTypeDesc, @bookingType, @mPC, @cancelledStageID, @customerEmail, @sourceChannel, @trackMobileNo, @pickupZoneID, @dropCityID, @returnTrip, (@retentionTime/60), @redeemPoints, @adminEmailid, @adminMobileno, @corporateName, @paymentType, @promoCode, 5, @jobID, getdate(), 'OneClick done', 11, getdate(), 11, 'OneClick done', @timetoDispatch, @onRoadDistance, 'LATER', @iMEINumber, @IsRideShare,@pickupLatitude,@pickupLongitude,@dropLatitude,@dropLongitude,@DropAddress,@corporateCode,@BrandTypeID )
IF (@isnigi = 0)
BEGIN
INSERT INTO dSmDES ( JobID, WebRefNo, mobile, CustomerName, Pickuptime, BookingStatus, EmailAddress, ChannelID, PickupArea, PickupSubArea, DropArea, DropSubArea, CityName, PickupAddress ,DestinationAddress )
VALUES ( @jobID, @webBookingReferenceNo, @mobile, @customerName, @pickupDateTime, 'Cab Available', @customerEmail, 5, @pickupArea, @pickupSubArea, @dropArea, @dropSubArea, @pickupCity, @pickupAddress,@DropAddress )
END
ELSE
BEGIN
INSERT INTO dSmDES_nigi ( JobID, WebRefNo, mobile, CustomerName, Pickuptime, BookingStatus, EmailAddress, ChannelID, PickupArea, PickupSubArea, DropArea, DropSubArea, CityName, PickupAddress )
VALUES ( @jobID, @webBookingReferenceNo, @mobile, @customerName, @pickupDateTime, 'Cab Available', @customerEmail, 5, @pickupArea, @pickupSubArea, @dropArea, @dropSubArea, @pickupCity, @pickupAddress )
END
SET @address = @pickupArea+','+@pickupSubArea+','+@pickupAddress
SET @isAssured = 0
IF (@mPC = 1)
BEGIN
SET @isAssured = 1
END
ELSE
IF EXISTS(SELECT DropLocalityName
FROM Dbo.GCAT WITH(NOLOCK)
WHERE PickupLocalityID = PickUpLocalityID AND DropLocalityID = @dropLocalityID)
BEGIN
SET @isAssured = 1
END
INSERT INTO QTC ( JobID, PickupTime, ZoneID, CallerID, CityID, JobTypeID, IsAssured, QuotaBorrowingZoneID )
VALUES ( @jobID, @pickupDateTime, @pickUpZoneID, @mobile, @pickupCityID, @jobTypeID, @isAssured, @pickUpZoneID )
/* Added for Coupon And Referral Checking Bookings */
IF EXISTS(SELECT ReferralCode
FROM CMTHS WITH(NOLOCK)
WHERE ReferralCode=@promoCode)
BEGIN
INSERT INTO TRTM ( JobID, CouponAmount, CustomerName, mobile, PromoCode )
VALUES ( @jobID, @couponValue, @customerName, @mobile, @promoCode )
END
ELSE
IF EXISTS(SELECT mobile
FROM CMTHS WITH(NOLOCK)
WHERE mobile=@mobile AND @promocode='REFBALANCE')
BEGIN
INSERT INTO TRTM ( JobID, CouponAmount, CustomerName, mobile, PromoCode )
VALUES ( @jobID, @couponValue, @customerName, @mobile, @promoCode )
END
ELSE
IF ((@promocode IS NOT NULL AND @promocode != '') AND @promoCode!='REFBALANCE')
BEGIN
INSERT INTO TCM ( mobile, CustomerName, JobID, PromoCode, CouponAmount, CustomerEmail, IMEINumber )
VALUES ( @mobile, @customerName, @jobID, @promoCode, @couponValue, @customerEmail, @iMEINumber )
END
IF NOT EXISTS(SELECT mobile
FROM AUM WITH(NOLOCK)
WHERE mobile=@mobile)
BEGIN
INSERT INTO AUM ( mobile, CustomerEmail )
VALUES ( @mobile, @customerEmail )
END
EXEC PROC3 @jobID
, @customerName
, @mobile
, @address
, @pickupSubArea
, @pickupArea
, @pickupZone
, @tagAs
, @pickupCityID
, @pickupLatitude
, @pickupLongitude
, ''
, @tagAs
, @pickupAddress
--IF(@Brand = 'nigi')
IF (@brand IN('nigi','BrandEve'))
BEGIN
SELECT @pickupSubAreaID = AddressPointID
FROM Dbo.fnPlay(@pickupLatitude,@pickupLongitude,'Brand')
SELECT @dropSubAreaID = AddressPointID
FROM Dbo.fnPlay(@dropLatitude,@dropLongitude,'Brand')
END
SET @ack = 'Accepted|'+convert(varchar,@jobID)+'|'+isnull(@reason,'')+'|'+convert(varchar,@pickupSubareaID)+'|'+convert(varchar,@dropSubareaID)
END
ELSE
BEGIN
SET @ack = 'Bookings Exceeded'
END
END
ELSE
BEGIN
SET @ack = 'TempBlock|'+convert(varchar,@pendingAmount)+'|'+convert(varchar,@transactionDate)
END
IF Not Exists (Select 1 from TRP T With (noLock)
Inner Join BJD J With (noLock) on J.promocode=T.Promocode
where CampaignName ='XYZ'
And T.Promocode=@PromoCode And J.JobID=@JobID
)
And ((Select Isnull(BusinessCategoryID,0) from BJD Where JOBID=@JOBID)<>2)
EXEC [Proc3] @JobID,@mobile,'','',@JobID,@WalletBalance,@ProviderID,@AppVersion,@CustomerWalletBalance,1
end
ELSE
BEGIN
INSERT INTO Dbo.refBndf ( WebBookingReferenceNo, PickupDateTime, PickupAddress, PickupArea, PickupSubArea, mobile, CustomerName, DropArea, DropSubArea, City, SourceIPAddress, RecordDattime, RequestProcessStatusID, RequestProcessTime, JobID, TaxiNo, SubscriberName, SubscriberMobileNo, ResponseProcessStatusID, JobType, BookingType, Mostprivilege, CancelledStageID, CustomerEmail, SourceChannel, TrackMobile, Response, TTD, OnRoadDistance, DropCityID, IMEINumber,PickupLat,PickupLong,DropLat,DropLong,DropAddress )
VALUES ( @webBookingReferenceNo, @pickupDateTime, @pickupAddress, @pickupArea, @pickupSubArea, @mobile, @customerName, isnull(@dropArea,'NA'), @dropSubArea, @pickupCity, @sourceIPAddress, getdate(), 9, getdate(), 0, '', '', '', 11, @jobTypeDesc, @bookingType, @mPC, @cancelledStageID, @customerEmail, @sourceChannel, @trackMobileNo, 'OneClick done', @timetoDispatch, @onRoadDistance, @dropCityID, @iMEINumber,@pickupLatitude,@pickupLongitude,@dropLatitude,@dropLongitude,@DropAddress )
SET @ack = 'Invalid Location'
END
end
else
begin
SET @ACK = 'Not Exactometer Coupon'
end
END
ELSE
BEGIN
SET @ack = 'Repeated Data'
END
END
ELSE
BEGIN
SET @ack = 'Invalid PickupTime'
IF @pickupCityID = 19 SET @ack = 'Cab not available'
END
END
ELSE
BEGIN
SET @ack = 'Package Not Found'
END
END
SELECT @ack AS Acknowledgement
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
DECLARE @errorMessage nvarchar(4000)
SET @errorMessage = error_message()
INSERT INTO Dbo.TblErrorMsgForBusiness ( ErrorMsg, StoredProcedureName, JobID )
VALUES ( @errorMessage, 'Proc2', @webBookingReferenceNo )
INSERT INTO dbo.DB_Errors
(
MobileRefNo,
UserName,
ErrorNumber,
ErrorState,
ErrorSeverity,
ErrorLine,
ErrorProcedure,
ErrorMessage,
ErrorDateTime
)
SELECT
@webBookingReferenceNo,
SUSER_SNAME(),
ERROR_NUMBER(),
ERROR_STATE(),
ERROR_SEVERITY(),
ERROR_LINE(),
ERROR_PROCEDURE(),
ERROR_MESSAGE(),
GETDATE()
END CATCH
END
Thursday, July 16, 2020 6:48 PM
So, I did read this part 1 right now (which is not too long) and I see at the end:
You have now learnt a general pattern for error and transaction handling in stored procedures. It is not perfect, but it should work well for 90-95 % of your code. There are a couple of limitations you should be aware of:
- As we have seen, compilation errors such as missing tables or missing columns cannot be trapped in the procedure where they occur, only in outer procedures.
- The pattern does not work for user-defined functions, since neither TRY-CATCH nor RAISERROR are permitted there.
- When you call a stored procedure on a linked server that raises an error, this error may bypass the error handler in the procedure on the local server and go to directly to the client.
- When a procedure is called by INSERT-EXEC, you will get an ugly error, because ROLLBACK TRANSACTION is not permitted in this case.
- As noted above, if you use error_handler_sp or SqlEventLog, you will lose one error message when SQL Server raises two error messages for the same error. This is not an issue with ;THROW.
So, looks like you do need to read other articles in this series to see if Erland handles the number 4.
For every expert, there is an equal and opposite expert. - Becker's Law
Thursday, July 16, 2020 6:57 PM
So, Erland does discuss this INSERT EXEC case in part 2. I simply searched that article and this is the portion you need to read
http://www.sommarskog.se/error_handling/Part2.html#INSERT-EXEC
For every expert, there is an equal and opposite expert. - Becker's Law
Friday, July 17, 2020 7:30 AM
Hello All,
I have changed in the SP below while inserting Proc output into table
DECLARE @tempRSoutput TABLE (Ack VARCHAR(500))
DECLARE @Out varchar(500)
INSERT INTO @tempRSoutput(Ack)
EXEC @Out = [usp_MobileAppOnClick_InsertAdvanceBookingRequest]
So Will that work???
Br
ChetanV
Friday, July 17, 2020 8:17 AM
DECLARE @tempRSoutput TABLE (Ack VARCHAR(500))
DECLARE @Out varchar(500)INSERT INTO @tempRSoutput(Ack)
EXEC @Out = [usp_MobileAppOnClick_InsertAdvanceBookingRequest]
Not sure what you are trying to achieve here, but I cannot see much point in it. The return value from a stored procedure is an integer value, so why is @Out varchar(500)?
And if you get the dreadful error about not being able to use ROLLBACK, all you will find that in @Out is a non-zero value telling you that the procedure failed, but not why it failed.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, July 17, 2020 9:37 AM
Not sure what you are trying to achieve here, but I cannot see much point in it. The return value from a stored procedure is an integer value, so why is @Out varchar(500)?
And if you get the dreadful error about not being able to use ROLLBACK, all you will find that in @Out is a non-zero value telling you that the procedure failed, but not why it failed.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Hello Erland,
Instead of table variable i am storing the internal Stored proc output into variable i.e @Out VARCHAR(500), after that storing that variable into table variable to avoid insert-exec , the output of the internal SP will throw single row output that will be in alphanumeric format just like either of this below 'Accepted|57218620|0.00|5786|5771','Sorry, We can not serve your request due to India LOCKDOWN','Invalid Location','already exists'.
So will that work ?
Br
ChetanV
Friday, July 17, 2020 1:34 PM
If your inner procedure just returned a single row with a single column, then changing the result to be the OUTPUT parameter can solve the problem, e.g. (this requires changing the inner procedure and making sure all calls to it are correct everywhere)
declare @ResultToInsert varchar(500);
execute dbo.usp_InnerProcedure @Param1, @Param2, @ResultToInsert = @ResultToInsert OUTPUT;
insert into myTable (ACK) values (@ResultToInsert)
For every expert, there is an equal and opposite expert. - Becker's Law
Friday, July 17, 2020 1:40 PM
I didn't see this reply when I answered before, but yes, just change your inner procedure (assuming it's not used in other places) to use OUTPUT parameter for the value it needs to return (if it was returning single value anyway). Then just get the output parameter value by calling this procedure and in a separate new statement insert this value where you need to insert it in the outer procedure (assuming it has to be inserted and can not be used as is).
For every expert, there is an equal and opposite expert. - Becker's Law
Friday, July 17, 2020 6:15 PM
I didn't see this reply when I answered before, but yes, just change your inner procedure (assuming it's not used in other places) to use OUTPUT parameter for the value it needs to return (if it was returning single value anyway). Then just get the output parameter value by calling this procedure and in a separate new statement insert this value where you need to insert it in the outer procedure (assuming it has to be inserted and can not be used as is).
Hello Naomi,
In my internal SP no Output parameter is used we have just throws the output by variable not by Output parameter, Below is the example code in short, So will my code work? in previos reply :
Create Procedure InternalProc
(
@abc Int,
@xyz VARCHAR(10)
.
.
.
--No Output Parameter
)
AS
Begin
BEGIN TRANSACTION
DECLARE @OutputOfSP VARCHAR(500)
SET @OutputOfSP = 'SomeCalculationOnTheBasisOfBusinessNeeds'
SELECT @OutputOfSP This is the Ouptput Of the SP , no Output parameter is used in SP
END TRANSACTION
BEGIN CATCH
.
--Error Handling
END CATCH
END
Br
ChetanV
Friday, July 17, 2020 6:41 PM
Unfortunately, if you really need to return this @OUTPUT value you do need to change your internal procedure to make this an output parameter. You can still keep it as select and you can add an optional parameter to not return it as select.
Adding an extra output parameter means that calls to this procedure are going to change.
If you don't want to change other calls to this procedure, you still will need to change your outer procedure and your inner procedure.
You can use the technique explained in Erland's article about sharing data between procedures. Your outer procedure will create a temp table. Your inner procedure will check if a temp table with this known name exists, if it does, it just inserts into that table and then does select from this temp table if other calls expect result to be returned that way (it can also have an optional parameter to suppress that select from temp table). If that table doesn't exist, it will probably mean that it was not called from that particular outer procedure, so it can still create that temp table and final select will be from it.
Check the article Erland pointed you to and I think the technique of sharing a temp table is either 3rd item in the list of methods or not but you'll find it at the top and go straight to that section (this is what I did last night for a quick refresher).
For every expert, there is an equal and opposite expert. - Becker's Law
Friday, July 17, 2020 9:26 PM
Instead of table variable i am storing the internal Stored proc output into variable i.e @Out VARCHAR(500), after that storing that variable into table variable to avoid insert-exec , the output of the internal SP will throw single row output that will be in alphanumeric format just like either of this below 'Accepted|57218620|0.00|5786|5771','Sorry, We can not serve your request due to India LOCKDOWN','Invalid Location','already exists'.
So will that work ?
Not with the code you posted. As Naomi says, you could make it an output parameter, but this does seem not be what you have in mind. If you say:
EXCEC @ret = some_sp
@ret will be set to a numeric value. 0 for success, and something else for errors.
And if you in the inner procedure say:
Begin
BEGIN TRANSACTION
DECLARE @OutputOfSP VARCHAR(500)
SET @OutputOfSP = 'SomeCalculationOnTheBasisOfBusinessNeeds'
SELECT @OutputOfSP This is the Ouptput Of the SP , no Output parameter is used in SP
END TRANSACTION
That @OutputOfSP is a result set, which you must capture with INSERT-EXEC if you want to use that result in the caller.
Overall, it seems messy to cram in a bunch of values in a single string. If you really would do it, XML is the best choice, but I would prefer to use a temp table.
Did you look at my article http://www.sommarskog.se/share_data.html yet?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, July 21, 2020 1:30 AM
Hi Chetan Vishwakarma,
Has your problem been solved? If it is solved, please mark the point that you
think is correct as an answer. This can help others who encounter similar problems.
Best Regards
Echo
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Tuesday, July 21, 2020 11:06 AM
Thank you Naomi & Erland for your precious reply and the article.
Had word with the business head about resolution (Remove begin transaction from internal SP and Adding Output parameter in internal SP) but unfortunately they don't allow to modify anything in the internal SP. So we have copied the internal SP logic(which was related to external SP) into external SP with single Begin/rollback Transaction. So now it is working as expected. Thank you once again.
Br
ChetanV