public ActionResult QueryProductSegment(string productId) { var provider = new LoyaltyDBProvider(); var query = @" with ProductPromotion as ( select ps.""Id"" as ""SegmentId"" , ps.""Name"" as ""SegmentName"", p2.""Id"" as ""PromotionId"", p2.""Name"" as ""PromotionName"" from ""ProductInSegment"" pis inner join ""ProductSegment"" ps on ps.""Id"" = pis.""SegmentId"" inner join ""PurchaseBenefit"" pb on pb.""Discount_SegmentId"" = ps.""Id"" or pb.""FixedPrice_SegmentId"" = ps.""Id"" or pb.""ChargeBonus_PercentCharge_ProductSegmentId"" = ps.""Id"" or pb.""AllowBonusPaymentParams_ProductSegmentId"" = ps.""Id"" inner join ""Promotion"" p2 on p2.""Id"" = pb.""PromotionId"" where pis.""ProductId"" = '{0}' union select ps.""Id"" as ""SegmentId"" , ps.""Name"" as ""SegmentName"", p2.""Id"" as ""PromotionId"", p2.""Name"" as ""PromotionName"" from ""ProductInSegment"" pis inner join ""ProductSegment"" ps on ps.""Id"" = pis.""SegmentId"" inner join ""BenefitCondition"" bc on bc.""SegmentId"" = ps.""Id"" inner join ""PurchaseBenefit"" pb on bc.""PurchaseBenefitId"" = pb.""Id"" inner join ""Promotion"" p2 on p2.""Id"" = pb.""PromotionId"" where pis.""ProductId"" = '{0}' ) select distinct ""SegmentId"", ""SegmentName"", ""PromotionId"", ""PromotionName"" from ProductPromotion " ; var items = provider.ExecuteSelectQuery(query, ReadProductSegment, productId); return(Ok(items)); }
private Dictionary <string, PromocodeInformation> ReadPromocodesInformation(Newtonsoft.Json.Linq.JArray promocodes) { var promocodesDictionary = new Dictionary <string, PromocodeInformation>(); var provider = new LoyaltyDBProvider(); foreach (string promocode in promocodes) { if (!promocodesDictionary.ContainsKey(promocode)) { promocodesDictionary[promocode] = provider.ExecuteSelectQuery(@"SELECT ""ContactId"", ""IsUsed"", ""PoolId"", ""Id"" FROM public.""PromoCode"" Where ""Code"" = '{0}'", ReadPromocodeInformation, promocode); } } return(promocodesDictionary); }