public async Task Add(Customer customer)
{
using (var conn = OpenConnection()) {
using (var t = conn.BeginTransaction()) {
// Insert customer parent record
var customerBillingReference = new BillingReferenceRow()
{
Id = Guid.NewGuid(),
BillingId = customer.BillingReference.BillingId,
Type = BillingReferenceType.Customer
};
await conn.ExecuteAsync(
@"insert into billing_references (id, billing_id, type) values (@Id, @BillingId, @Type);",
customerBillingReference
);
await conn.ExecuteAsync(
@"insert into customers (id, user_id, billing_reference_id) values (@Id, @UserId, @BillingReferenceId);",
new CustomerRow()
{
Id = customer.Id,
UserId = customer.UserId,
BillingReferenceId = customerBillingReference.Id
}
);
await InsertPaymentMethods(conn, customer.Id, customer.PaymentMethods);
await InsertSubscription(conn, customer.Id, customer.Subscription);
t.Commit();
}
}
}
async Task InsertPaymentMethods(IDbConnection conn, Guid customerId, List <PaymentMethod> paymentMethods, bool deleteExisting = false)
{
if (deleteExisting)
{
var oldPaymentMethods = await conn.QueryAsync <PaymentMethodRow>("select * from payment_methods where customer_id = @Id;", new { Id = customerId });
await conn.ExecuteAsync("delete from payment_methods where customer_id = @Id", new { Id = customerId });
await conn.ExecuteAsync("delete from billing_references where id = @BillingReferenceId;", oldPaymentMethods.ToList());
}
if (paymentMethods.Count > 0)
{
var pmbrs = new List <BillingReferenceRow>();
var pms = new List <PaymentMethodRow>();
foreach (PaymentMethod pm in paymentMethods)
{
var brModel = new BillingReferenceRow()
{
Id = Guid.NewGuid(),
BillingId = pm.BillingReference.BillingId,
Type = BillingReferenceType.PaymentMethod
};
var pModel = new PaymentMethodRow()
{
Id = pm.Id,
BillingReferenceId = brModel.Id,
CustomerId = customerId,
Brand = pm.Brand,
Last4 = pm.Last4,
ExpirationMonth = pm.Expiration.Month,
ExpirationYear = pm.Expiration.Year,
IsDefault = pm.IsDefault
};
pmbrs.Add(brModel);
pms.Add(pModel);
}
await conn.ExecuteAsync(
@"insert into billing_references (id, billing_id, type) values (@Id, @BillingId, @Type);",
pmbrs
);
await conn.ExecuteAsync(
@"insert into payment_methods (
id,
customer_id,
brand,
last_4,
expiration_month,
expiration_year,
billing_reference_id,
is_default
) values (
@Id,
@CustomerId,
@Brand,
@Last4,
@ExpirationMonth,
@ExpirationYear,
@BillingReferenceId,
@IsDefault
);",
pms
);
}
}
async Task InsertSubscription(IDbConnection conn, Guid customerId, Subscription?subscription, bool deleteExisting = false)
{
if (deleteExisting)
{
var oldSubscription = await conn.QueryFirstOrDefaultAsync <SubscriptionRow>("select * from subscriptions where customer_id = @Id;", new { Id = customerId });
await conn.ExecuteAsync(@"delete from subscriptions where id = @Id;", oldSubscription);
await conn.ExecuteAsync(@"delete from billing_references where id = @BillingReferenceId;", oldSubscription);
}
// Insert subscription (if applicable)
if (subscription != null)
{
var subBillingReference = new BillingReferenceRow()
{
Id = Guid.NewGuid(),
BillingId = subscription.BillingReference.BillingId,
Type = BillingReferenceType.Subscription
};
await conn.ExecuteAsync(
@"insert into billing_references (id, billing_id, type) values (@Id, @BillingId, @Type);",
subBillingReference
);
await conn.ExecuteAsync(
@"insert into subscriptions (
id,
plan_id,
price_billing_id,
customer_id,
billing_reference_id,
status,
trial_start,
trial_end,
period_start,
period_end,
cancelling_at_period_end
)
values (
@Id,
@PlanId,
@PriceBillingId,
@CustomerId,
@BillingReferenceId,
@Status,
@TrialStart,
@TrialEnd,
@PeriodStart,
@PeriodEnd,
@CancellingAtPeriodEnd
);",
new SubscriptionRow()
{
Id = subscription.Id,
PlanId = subscription.PlanReference.PlanId,
PriceBillingId = subscription.PlanReference.PriceBillingId,
CustomerId = customerId,
BillingReferenceId = subBillingReference.Id,
Status = subscription.Status.ToBillingString(),
PeriodStart = subscription.Period.Start,
PeriodEnd = subscription.Period.End,
TrialStart = subscription.TrialPeriod.Start,
TrialEnd = subscription.TrialPeriod.End,
CancellingAtPeriodEnd = subscription.CancellingAtPeriodEnd
}
);
}
}
public async Task Add(SubscriptionPlan entity)
{
using (var conn = OpenConnection()) {
using (var t = conn.BeginTransaction()) {
// insert plan billing ref first
var planBillingRef = new BillingReferenceRow()
{
Id = Guid.NewGuid(),
BillingId = entity.BillingReference.BillingId,
Type = entity.BillingReference.Type
};
await conn.ExecuteAsync(
@"insert into billing_references (id, billing_id, type) values (@Id, @BillingId, @Type);",
planBillingRef,
t
);
// insert plan
await conn.ExecuteAsync(
@"insert into subscription_plans (id, name, description, role_id, billing_reference_id) values (@Id, @Name, @Description, @RoleId, @BillingReferenceId);",
new SubscriptionPlanRow()
{
Id = entity.Id,
Name = entity.Name,
Description = entity.Description,
BillingReferenceId = planBillingRef.Id,
RoleId = entity.RoleId
},
t
);
var prices = new List <SubscriptionPlanPriceRow>();
var billingReferences = new List <BillingReferenceRow>();
foreach (var p in entity.Prices)
{
var billingRefModel = new BillingReferenceRow()
{
Id = Guid.NewGuid(),
BillingId = p.BillingReference.BillingId,
Type = p.BillingReference.Type
};
var priceModel = new SubscriptionPlanPriceRow()
{
Id = Guid.NewGuid(),
Interval = p.Interval,
Price = p.Amount,
PlanId = entity.Id,
BillingReferenceId = billingRefModel.Id
};
prices.Add(priceModel);
billingReferences.Add(billingRefModel);
}
await conn.ExecuteAsync(
@"insert into billing_references (id, billing_id, type) values (@Id, @BillingId, @Type);",
billingReferences,
t
);
// insert price s
await conn.ExecuteAsync(
@"insert into subscription_plan_prices (id, plan_id, billing_reference_id, interval, price) values (@Id, @PlanId, @BillingReferenceId, @Interval, @Price);",
prices,
t
);
t.Commit();
}
}
}
