User932259438 posted
Check code:
ALTER PROCEDURE [dbo].[ImportSuggestion]
@SuggestionXML AS XML,
@Result AS NVARCHAR(100) OUTPUT
AS
BEGIN
BEGIN TRY
----DECLARE @SuggestionTable table
----(
---- MatchId BIGINT,
---- OutComeId NVARCHAR(50),
---- OddsType INT,
---- RemainingLimit DECIMAL(18,2),
---- SetUpFirstLimit DECIMAL(18,2)
----)
----INSERT INTO @SuggestionTable(MatchId,OutComeId,OddsType,RemainingLimit,SetUpFirstLimit)
----SELECT MatchId,OutComeId,OddsType,RemainingLimit,SetUpFirstLimit
----FROM tbl_Suggestions TS
----INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
----ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
---- (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND
---- TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND
---- TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND
---- (TS.SpecialBetValue IS NOT NULL)
--INSERT INTO tbl_Suggestions_DeleteRecords(SuggestionId, MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail)
--SELECT SuggestionId,MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail
--FROM tbl_Suggestions TS
--INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
--ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
-- (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND
-- TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND
-- TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND
-- (TS.SpecialBetValue IS NOT NULL)
--DELETE TS
--FROM tbl_Suggestions TS
--INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
--ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
-- (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND
-- TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND
-- TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND
-- (TS.SpecialBetValue IS NOT NULL)
------UPDATE TS
------SET TS.Value = C.Cou.value('(Value)[1]','NVARCHAR(50)'),
------LastUpdatedDate = GETDATE()
------FROM tbl_Suggestions TS
------INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
------ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
------ (TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND
------ TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND
------ TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND
------ (TS.SpecialBetValue IS NULL)
INSERT INTO tbl_Suggestions(MatchId,OutCome,OutComeId,OddsType,[Value],SpecialBetValue,CreatedDate,IsLatest,RemainingLimit,IsOpen,SetUpFirstLimit,IsSendEmail)
SELECT DISTINCT C.Cou.value('(MatchId)[1]','bigint') As MatchId,C.Cou.value('(OutCome)[1]','NVARCHAR(50)') As OutCome,
C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') As OutComeId,C.Cou.value('(OddsType)[1]','int') As OddsType,
C.Cou.value('(Value)[1]','NVARCHAR(50)') As OddValue,C.Cou.value('(SpecialBetValue)[1]','NVARCHAR(50)') As SpecialBetValue,GETDATE(),
1,COALESCE(200,
200
, 200
), CASE WHEN C.Cou.value('(Value)[1]','NVARCHAR(50)')='OFF' THEN 'FALSE' ELSE 'TRUE' END,COALESCE(200,
200
, 200
)
,'FALSE'
FROM @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou)
INNER JOIN tbl_Matches TM ON TM.MatchId=C.Cou.value('(MatchId)[1]','bigint')
--LEFT JOIN tbl_Limits LM ON LM.SportId=TM.SportId AND LM.CategoryId = TM.CategoryId
-- AND (LM.UniqueTournamentId = TM.UniqueTournamentId OR LM.UniqueTournamentId IS NULL)
-- AND (LM.TournamentId = TM.TournamentId OR LM.TournamentId IS NULL)
--LEFT JOIN tbl_LimitsMarketsManual LMM ON LMM.SportId=TM.SportId AND LMM.CategoryId = TM.CategoryId
-- AND (LMM.UniqueTournamentId = TM.UniqueTournamentId OR LMM.UniqueTournamentId IS NULL)
--AND (LMM.TournamentId = TM.TournamentId OR LMM.TournamentId IS NULL)
LEFT JOIN tbl_Suggestions TS ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
(TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') OR TS.OutComeId IS NULL) AND
TS.OddsType = C.Cou.value('(OddsType)[1]','int') AND TS.IsLatest=1 AND TS.OutCome = C.Cou.value('(OutCome)[1]','NVARCHAR(50)') AND
(TS.SpecialBetValue = C.Cou.value('(SpecialBetValue)[1]','NVARCHAR(50)')OR TS.SpecialBetValue IS NULL)
WHERE TS.MatchId IS NULL AND TS.OutComeId IS NULL AND TS.OddsType IS NULL
--Update Suggestion
UPDATE TM
SET TM.CountSuggestion = T.OddsTypeCount
FROM tbl_Matches TM
INNER JOIN
(
SELECT COUNT(1) AS OddsTypeCount,T.MatchId
FROM tbl_Matches TM
INNER JOIN
(
SELECT 1 AS MatchidCount, TM.MatchId AS MatchId
FROM [dbo].[tbl_Suggestions] TS
INNER JOIN tbl_Matches TM ON TS.MatchId=TM.MatchId AND TS.IsLAtest=1
INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND TS.OutComeId = C.Cou.value('(OutComeId)[1]','NVARCHAR(50)') AND
TS.OddsType = C.Cou.value('(OddsType)[1]','int')
GROUP BY TM.MatchId, TS.OddsType
) AS T
ON T.MatchId = TM.MatchId
GROUP BY T.MatchId
) AS T
ON T.MatchId = TM.MatchId
UPDATE TS
SET TS.IsOpen='FALSE'
FROM tbl_Suggestions TS
INNER JOIN
(
SELECT DISTINCT TS.MatchId,TS.OddsType
FROM [dbo].[tbl_Suggestions] TS
INNER JOIN
(
SELECT MatchId,OddsType
FROM [dbo].[tbl_Suggestions]
WHERE [Value]='OFF'
GROUP BY MatchId,OddsType
) AS T ON TS.MatchId=T.MatchId AND TS.OddsType=T.OddsType
INNER JOIN @SuggestionXML.nodes('/ArrayOfSuggestions/Suggestions') C(Cou) ON TS.MatchId = C.Cou.value('(MatchId)[1]','bigint') AND
TS.OddsType = C.Cou.value('(OddsType)[1]','int')
) AS OFFVALUE ON TS.MatchId=OFFVALUE.MatchId AND TS.OddsType=OFFVALUE.OddsType
SET @Result='Sucess'
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE ( )
SET @Result='Fail'
END CATCH
END
