Page 1 of 1
Modifying CommerceBuilder classes (and DB logic question)
Posted: Thu Sep 11, 2008 4:34 pm
by catalyst
I've taken several passes through the board looking for a subject similar to mine but haven't found anything. Apologies if it's there and I just didn't see it.
Anyway, I'm adding the ability to support royalty payments on certain types of products and I've run into a situation where I need to add another property to the SalesSummary and OrderSummary classes. (Well, and others, too.) So as to avoid getting my changes stomped the next time AC releases a patch/update, I'm subclassing them into custom classes. But this causes another problem, namely that I need to roll my own version of (for example) ReportDataSource.GetMonthlySales().
Rather than continue playing detective in the database in order to figure out how each of the SalesSummary properties are calculated and where that data comes from, I'm hoping someone here (maybe Logan or someone from AC?) can tell me which tables and fields (and/or calculations) are used to determine:
- OrderCount
- ProductTotal
- ShippingTotal
- TaxTotal
- DiscountTotal
- CouponTotal
- OtherTotal
- ProfitTotal
- GrandTotal
I'm hoping that information will give me enough detail to figure out the rest on my own.
Thanks,
Steve
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 1:23 am
by mazhar
Following are the rules
The amount field is calculated from two order item fields as below
Code: Select all
amount = OrderItem.Price * OrderItem.Quantity;
Similarly the others will be
Code: Select all
* OrderCount: Total Number of order in ac_Orders table matching the search criteria
* ProductTotal: ProductTotal += amount;
* ShippingTotal: ShippingTotal+= amount;
* TaxTotal: summary.TaxTotal += amount;
* DiscountTotal: summary.DiscountTotal += amount;
* CouponTotal summary.CouponTotal: += amount;
* GrandTotal: summary.GrandTotal += amount;
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 1:13 pm
by catalyst
Thank you for the reply, but I'm looking for the data for an order as stored in the database, not the logic used in the code to calculate totals for a particular order.
It looks like, for example, I'll need to do joins between (at least) ac_OrderItems, ac_TaxRuleTaxCodes, and ac_TaxRules. Is that the only way to get tax information for an order after the fact? To recalculate it on the fly? And what about discounts, coupons, shipping charges, etc? For example, what's the logic used to determine profit? Is it just something like
Code: Select all
(ac_OrderItems.Price * ac_OrderItems.Quantity) - ac_OrderItems.CostOfGoods - [discount] - [coupon]
Basically, I need to know what tables/fields are used and the logic applied to completely re-create that part of an order.
Thanks again.
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 3:30 pm
by AbleMods
It's not that simple.
Volume discounts, coupons and the like all have some programming logic involved to enforce the design rules of the software. Just establishing relationship queries from the various tables won't give you enough to accurately reproduce the same numbers created within the data classes.
You might be able to accomplish it with stored procedures, but the complexity would make it a significant project.
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 5:20 pm
by catalyst
And that's the second part of my query: I need to know 1)where the data is stored and 2)what that business logic is. This is not information that's AC-proprietary, in the sense that it would give away a competetive advantage, it's just understanding the database fields and logic that's used so that I can accurately subclass the things I need to WITHOUT having to go through and empirically deduce what's going on... a much, much harder project.
Anyone? Li'l help?
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 6:11 pm
by afm
I would give the sales number a call and ask about the CommerceBuilder source code. I don't think it is very expensive and it would answer all of your questions.
Keep in mind that AC has committed to maintaining the API exposed by CommerceBuilder, not the interface between CommerceBuilder and the database. They may change that (and probably do) from time to time.
I think I remember an article on their wiki about extended the data classes.
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Fri Sep 12, 2008 6:18 pm
by AbleMods
I don't think anyone is trying to protect something propriety. It's simply a matter of most of us don't have full source and cannot speak accurately as to the actual programming code involved to generate the numbers.
Up until the past few weeks, full source wasn't even available so nobody but Able knew how it worked.
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Tue Sep 23, 2008 2:28 pm
by johnco
Giving this a bump as AC development may look at this.
We're doing a customization of AbleCommerce 7.0 to handle a store with royalty-based sales model (think consignment - and you have an idea). This has meant requirements to alter some of the Admin tools, including adding royalty information to reports. We're trying to avoid major changes to the libraries to allow for relatively painless future patches and upgrades, but we are running into some issues.
We're looking for the tables, fields, and calculations used by the ReportDataSource.GetMonthlySales() and ReportDataSource.GetDailySales() functions to populate the SalesSummary and OrderSummary classes. Also, if any of the properties of either of those two objects are the results of calculations by the classes themselves, We’d need to know what those are, too.
Thanks for any assistance you can provide.
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Tue Sep 23, 2008 3:23 pm
by jmestep
Code: Select all
sql.Append("SELECT O.OrderId, OI.OrderItemTypeId, SUM(OI.Price * OI.Quantity), SUM(OI.CostOfGoods * OI.Quantity)");
sql.Append(" FROM ac_Orders O INNER JOIN ac_OrderItems OI ON O.OrderId = OI.OrderId");
sql.Append(" WHERE O.StoreId = @storeId");
sql.Append(" AND " + GetStatusFilter(reportStatuses, "O"));
sql.Append(" AND O.OrderDate >= @startDate");
sql.Append(" AND O.OrderDate < @endDate");
sql.Append(" GROUP BY O.OrderId, OI.OrderItemTypeId");
sql.Append(" ORDER BY O.OrderId");
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Tue Sep 23, 2008 9:01 pm
by mazhar
Giving this a bump as AC development may look at this.
We're looking for the tables, fields, and calculations used by the ReportDataSource.GetMonthlySales() and ReportDataSource.GetDailySales() functions to populate the SalesSummary and OrderSummary classes. Also, if any of the properties of either of those two objects are the results of calculations by the classes themselves, We’d need to know what those are, too.
Thanks for any assistance you can provide.
GetMonthlySales()
Makes use of the
GetSalesSummary method which in turn makes use of the ac_Orders and ac_OrderItems tables.
GetDailySales()
Makes use of ac_Orders and ac_OrderItems tables
SalesSummary
Only one property
ProfitTotal which returns a value based upon a calculation.
Code: Select all
get{
return (ProductTotal - (-DiscountTotal) - (-CouponTotal) - CostOfGoodTotal);
}
Only one property
ProfitTotal which returns a value based upon a calculation.
Code: Select all
get { return (ProductTotal - (-DiscountTotal) - (-CouponTotal) - CostOfGoodTotal); }
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Wed Sep 24, 2008 11:17 am
by catalyst
That's helpful, thank you, but where does information like SalesSummary.TaxTotal come from? Is it calculated on the fly using the ac_OrderItems.TaxCodeId to do a lookup, for example? What about the .CouponTotal, .ShippingTotal, .DiscountTotal (again, for example) fields on both SalesSummary and OrderSummary? This is the kind of non-obvious information I would prefer NOT to play "detective" with, if I can avoid it.
(And where is the GetSalesSummary function defined? I couldn't seem to find it?)
Re: Modifying CommerceBuilder classes (and DB logic question)
Posted: Wed Sep 24, 2008 12:02 pm
by nickc
Here's a component of some aggregated views that I use for reporting via Excel pivots -maybe will help you out a little.
* Caution - specific to my app with enums hard-coded and custom date functions to deal with inconsistent application of UTC in ac_ tables (note that Coupons are flipped to positive value, e.g.)*
Code: Select all
ALTER VIEW [dbo].[vw_salesvolumesbydate]
AS
SELECT
dbo.fn_GetBusinessDate(dbo.fn_ModDate(O.OrderDate,'hh',-7), 6, 0) AS [Period],
SUM(CASE WHEN I.OrderItemTypeId IN (0, 1, 2, 3) THEN I.Quantity * I.Price ELSE 0 END) AS [Total$],
SUM(CASE WHEN I.OrderItemTypeId IN (0) THEN I.Quantity * I.Price ELSE 0 END) AS [Total$Product],
SUM(CASE WHEN I.OrderItemTypeId IN (1, 2) THEN I.Quantity * I.Price ELSE 0 END) AS [Total$Shipping],
SUM(CASE WHEN I.OrderItemTypeId IN (3) THEN I.Quantity * I.Price ELSE 0 END) AS [Total$Taxes],
SUM(CASE WHEN I.OrderItemTypeId IN (5) THEN abs(I.Quantity * I.Price) ELSE 0 END) AS [Total$Coupon],
COUNT(DISTINCT O.OrderId) AS [Total#Orders],
SUM(CASE WHEN I.OrderItemTypeId IN (0) THEN I.Quantity ELSE 0 END) AS [Total#OrderItems],
SUM(CASE WHEN I.OrderItemTypeId IN (5) THEN I.Quantity ELSE 0 END) AS [Total#Coupon],
SUM(CASE WHEN I.OrderItemTypeId IN (4, 6) THEN (I.Quantity * I.Price) ELSE 0 END) AS [Total$Adjustment]
FROM
dbo.ac_Orders AS O LEFT OUTER JOIN dbo.ac_OrderItems AS I ON O.OrderId = I.OrderId
WHERE
(O.OrderStatusId IN (1, 2, 3))
GROUP BY
dbo.fn_GetBusinessDate(dbo.fn_ModDate(O.OrderDate,'hh',-7), 6, 0)