<commit_before>/*
SELECT * FROM GiftDepositories
SELECT * FROM Members
SELECT * FROM ItemLists
SELECT * FROM MemberItems
SELECT * FROM MemberGameInfoes
SELECT * FROM MemberItemPurchases
SELECT * FROM Adminmembers
SELECT * FROM Coupon
SELECT * FROM CouponMember
SELECT * FROM GameEvents
SELECT * FROM GameEventMember
SELECT * FROM Notices
SELECT * FROM MemberGameInfoStages
SELECT * FROM MemberAccountBlockLog

SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon

"̺ DupeYN  ó

 ¥  ¥ ȿ ְ
  coupon ̺ DeleteYN = N & DupeYN = Y̸ 
    CouponMember  Member  insert Ѵ
  coupon ̺ DeleteYN = N & DupeYN = N̸ 
    coupon ̺ DeleteYN Y ٲٰ CouponMember  Member  insert Ѵ
  coupon ̺ DeleteYN = Y 
      Ұ ˸ ش.
 
   insert or updateѴ"


SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon
SELECT CouponMemberID, CouponID, MemberID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10
FROM CouponMember

update Coupon	--     ÷ ó datetime  ó ʿ 
set CouponDurationFrom = '2015-01-01', CouponDurationTo = '2016-01-01', HideYN = 'N', DeleteYN = 'N', DupeYN = 'Y', ItemListID = '7BF74075-FC72-4FC1-A1B9-7A0DFAD2E0DE', ItemCount = '10', Itemstatus = '±'

--NOT EXISTS  ó
SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon
WHERE NOT EXISTS 
(
  SELECT 1 
    FROM CouponMember
    WHERE CouponMember.CouponID = Coupon.CouponID
	and CouponMember.MemberID like 'aaa'
);

*/

-----------------------------------------------------------------------
-- uspSelCoupons ν 
--DROP PROC uspSelCoupons
CREATE PROC uspSelCoupons
@MemberID varchar(MAX)
--CouponDurationFrom  Ϸ  κ ȣȭ   ̾ .
--CouponDurationFrom ÷ datetime ·  Ǿ .
AS 
select CouponID, CouponCategory1, CouponCategory2, CouponCategory3, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, Title, Content, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10
FROM Coupon with(nolock)
WHERE NOT EXISTS 
(
  SELECT 1 
    FROM CouponMember  with(nolock)
    WHERE CouponMember.CouponID = Coupon.CouponID
	and CouponMember.MemberID like @MemberID
		--  ǿ ´  and HideYN, DeleteYN N ΰ
)
and sysutcdatetime() between CouponDurationFrom and CouponDurationTo
and Coupon.HideYN like 'N'
and Coupon.DeleteYN like 'N'
order by OrderNumber asc, CreatedAt desc		--   ÷  asc,   ֽ 
GO

/*
-----------------------------------------------------------------------
EXEC uspSelCoupons 'aaa'	--get valid coupon
EXEC uspSelCoupons 'bbb'	
-----------------------------------------------------------------------
SELECT * FROM Coupon
SELECT * FROM CouponMember
*/
<commit_msg>updated uspSelCoupons stored procedure - comment out<commit_after>/*
***************************************************************************************************************
This stored procedure is deplicated as business logic issue in CloudBread 2.0.1 release
https://github.com/CloudBreadProject/CloudBread/issues/26
***************************************************************************************************************

SELECT * FROM GiftDepositories
SELECT * FROM Members
SELECT * FROM ItemLists
SELECT * FROM MemberItems
SELECT * FROM MemberGameInfoes
SELECT * FROM MemberItemPurchases
SELECT * FROM Adminmembers
SELECT * FROM Coupon
SELECT * FROM CouponMember
SELECT * FROM GameEvents
SELECT * FROM GameEventMember
SELECT * FROM Notices
SELECT * FROM MemberGameInfoStages
SELECT * FROM MemberAccountBlockLog

SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon

"̺ DupeYN  ó

 ¥  ¥ ȿ ְ
  coupon ̺ DeleteYN = N & DupeYN = Y̸ 
    CouponMember  Member  insert Ѵ
  coupon ̺ DeleteYN = N & DupeYN = N̸ 
    coupon ̺ DeleteYN Y ٲٰ CouponMember  Member  insert Ѵ
  coupon ̺ DeleteYN = Y 
      Ұ ˸ ش.
 
   insert or updateѴ"


SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon
SELECT CouponMemberID, CouponID, MemberID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10
FROM CouponMember

update Coupon	--     ÷ ó datetime  ó ʿ 
set CouponDurationFrom = '2015-01-01', CouponDurationTo = '2016-01-01', HideYN = 'N', DeleteYN = 'N', DupeYN = 'Y', ItemListID = '7BF74075-FC72-4FC1-A1B9-7A0DFAD2E0DE', ItemCount = '10', Itemstatus = '±'

--NOT EXISTS  ó
SELECT CouponID, CouponCategory1, CouponCategory2, CouponCategory3, DupeYN, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, OrderNumber, CouponDurationFrom, CouponDurationTo, title, content, CreateAdminID, HideYN, DeleteYN, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10 
FROM Coupon
WHERE NOT EXISTS 
(
  SELECT 1 
    FROM CouponMember
    WHERE CouponMember.CouponID = Coupon.CouponID
	and CouponMember.MemberID like 'aaa'
);

*/

/*
-----------------------------------------------------------------------
-- uspSelCoupons ν 
--DROP PROC uspSelCoupons
CREATE PROC uspSelCoupons
@MemberID varchar(MAX)
--CouponDurationFrom  Ϸ  κ ȣȭ   ̾ .
--CouponDurationFrom ÷ datetime ·  Ǿ .
AS 
select CouponID, CouponCategory1, CouponCategory2, CouponCategory3, ItemListID, ItemCount, ItemStatus, TargetGroup, TargetOS, TargetDevice, Title, Content, sCol1, sCol2, sCol3, sCol4, sCol5, sCol6, sCol7, sCol8, sCol9, sCol10
FROM Coupon with(nolock)
WHERE NOT EXISTS 
(
  SELECT 1 
    FROM CouponMember  with(nolock)
    WHERE CouponMember.CouponID = Coupon.CouponID
	and CouponMember.MemberID like @MemberID
		--  ǿ ´  and HideYN, DeleteYN N ΰ
)
and sysutcdatetime() between CouponDurationFrom and CouponDurationTo
and Coupon.HideYN like 'N'
and Coupon.DeleteYN like 'N'
order by OrderNumber asc, CreatedAt desc		--   ÷  asc,   ֽ 
GO
*/

/*
-----------------------------------------------------------------------
EXEC uspSelCoupons 'aaa'	--get valid coupon
EXEC uspSelCoupons 'bbb'	
-----------------------------------------------------------------------
SELECT * FROM Coupon
SELECT * FROM CouponMember
*/

{'fraction_non_alphanumeric': 0.16810344827586207, 'fraction_numerical': 0.029338153503893214, 'mean_word_length': 3.6647211413748377, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4879560', 'n_tokens_mistral': 2546, 'n_tokens_neox': 2522, 'n_words': 715}