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);
}