Changing Order Report

Apr 1, 2010 at 5:24 PM

I would like to change the fields shown in the daily Order Report. Instead of Date, Ref and Total, I would like to have Date, OrderID, Itemdesc and Quantity. I searched my complete NB_Store Module as well as the DNN but did not find the script responsible for this report. Can anyone give me a tip please.

Thanks

René

Apr 2, 2010 at 8:10 AM

Hi René,

The report is produced using xsl, have a look at the "ordersreport.xsl" file in the module folder.

Regards,

Dave.

Apr 3, 2010 at 3:15 PM

Hi Dave,

Thank you for your information. I must say, I am not a professional. I did find the ordersreport.xsl and changed the fields to my needs. However, the Quantity Field returns no data. As it is set up by you, it draws the information from the NB_Store_Orders table (I guess) but in the NB_Store_Orders table their is no Quantity field, I would need the data from the NB_Store_OrderDetails table, but I cannot find out where in the ordersreport.xsl or in the ordersreport_de-CH.xsl table the information is drawn from.

Thanks for more information

Regards

René

Apr 5, 2010 at 8:26 AM

The xsl takes it's data from an xml build by the code.  Unfortunatly the only data included in this at the moment is the order and status data ,not the detail.  So you've only got access to these field from the order..

[OrderID]
      ,[UserID]
      ,[PortalID]
      ,[OrderNumber]
      ,[OrderDate]
      ,[ShipDate]
      ,[ShippingAddressID]
      ,[BillingAddressID]
      ,[AppliedTax]
      ,[ShippingCost]
      ,[OrderIsPlaced]
      ,[OrderStatusID]
      ,[PayType]
      ,[CalculatedTax]
      ,[NoteMsg]
      ,[VATNumber]
      ,[Discount]
      ,[PromoCode]
      ,[Total]
      ,[Email]
      ,[BankAuthCode]
      ,[ShipMethodID]
      ,[TrackingCode]

 

Regards,

Dave.

Apr 5, 2010 at 4:09 PM

Hi Dave,

Thanks for the reply.

This means for me as a non programmer, there is probably no way how I could change my orders report to my needs for the time being.

Regards,

René

 

 

Apr 6, 2010 at 8:14 AM

Hi,

...the only other option you have as a non-programmer is to create a SQL report and put it in the reporting system.  You could do something like this...

----------------------------------------------

    select O.*,
    (select sum(Quantity) from dbo.NB_Store_OrderDetails as OD where OD.OrderID = O.OrderID ) as QTY
    from dbo.NB_Store_Orders as O    
    where portalid = @PortalID

----------------------------------------------

Pu the @PortalID in as a parameter, the qty field in this example will only give you the total qty of the all detail records, not a breakdown of which details where chosen... maybe not what you want?

Of course this does mean you need some basic SQL knowledge.

Regards,

Dave.

Apr 6, 2010 at 3:58 PM

Hi Dave,

thanks for the reply. The SQL solution is also what I had in mind. I will try your suggestion. From the SQL point of view, I can do that, as I have some database knowledge mainly in MSAccess. However, until now I have no idea where to put that SQL query in order to get it into the reporting system.

I made a query where I could get the table I wanted. I used this one from tom hernes in this discussion group and left out the datepart:

SELECT A1.orderdate, A2.quantity, itemdesc
FROM nb_store_orders A1, nb_store_orderdetails A2
WHERE A1.OrderId = A2.OrderId
AND DATEPART(yyyy,A1.orderdate) = '2010' AND DATEPART(mm,A1.orderdate) = '01'
ORDER BY A2.ItemDesc ASC

I changed it as follows:

SELECT A1.orderdate, A2.quantity, itemdesc
FROM nb_store_orders A1, nb_store_orderdetails A2
WHERE A1.OrderID- A2.OrderID
ORDER BY A2.ItemDesc ASC

This worked well in the DNN SQL Module, but my problem now is how to get it in the Schedule E-Mail.

I have some experience in SQL, as I was creating a MSAccess Database in my Firm, which I linked to a SQL database on an internal Server. Even though I used the MSAccess Queries to get the data out of the SQL Server I have also looked at the SQL queries and can understand how they work.

The problem here is, that my Web Hoster does not allow external linking to the SQ 
Server, otherwise I could get the data directly into an Access Database or even better directly to Excel. Second Problem, my Admin does not have too much Knowledge, but he is the one who takes in the orders. Therefore the easiest thing is to send him an e-mail with the report details.

Therefore the NB_Store orders report is the ideal thing with the DNN Scheduling, if only the data needed could be produced.

Kind regards

René

 

 

Apr 6, 2010 at 4:17 PM

Hi,

If your running v2.0.11 then the reporting system included in NB_Store can put your sql reports on the scheduler and send an automatic email, but you have to enable the class in the DNN Scheduler...

-----------------------------------------------

NEvoWeb.Modules.NB_Store.SchedulerReport,NEvoweb.DNN.Modules.NB_Store
The purpose of this schedule job is to run any reports that have been schduled from the reporting system.


Time Lapse: 1 hour*
Retry Frequency: 2 hours

*The functionality to time reports to an hours has been implemented, but this relies on the DNN scheduler and therefore differences in the times when reports run will happen.

-------------------------------------------------------------

 

Regards,

Dave.

Apr 6, 2010 at 4:35 PM

Hi Dave,

I run 2.0.07. I will upgrade to 2.0.11 and see how it works.

Thanks for your help

regards

René

Apr 6, 2010 at 7:45 PM

Hi Dave,

I have done it and it works. I use the following SQL statement:

SELECT A1.orderdate as [Auftragsdatum], itemdesc as [Produkt], A2.quantity as [Menge]
FROM nb_store_orders A1, nb_store_orderdetails A2
WHERE A1.OrderId = A2.OrderId
ORDER BY A2.ItemDesc ASC

This way, I can add more details if my Administrator wishes so.

Thanks again for your help

Regards,

René

Apr 7, 2010 at 8:03 AM

Hi René,

Thanks for passing back the SQL, I'm trying to create a collection of useful SQL statement, so if you create any others please pass them on.

Although I did notice that your SQL is not looking at the order status, I don;t know if this is intentional?  But orders in NB_Store are created before processing, so in effect your SQL can pass back orders that will never be completed.

You could try something like this...

 

SELECT
A1.OrderStatusid,
OS.OrderStatusText,
A1.ordernumber as [Order Number],
A1.orderdate as [Order Date],
itemdesc as [Product],
A2.quantity as [Qty]
FROM nb_store_orders A1
inner join nb_store_orderdetails as A2 on A1.OrderId = A2.OrderId
inner join NB_Store_OrderStatus as OS on OS.OrderStatusID = A1.OrderStatusID and Lang = 'fr-FR'
ORDER BY A2.ItemDesc ASC

 

Regards,

Dave.

Apr 7, 2010 at 11:48 AM

Hi Dave,

I will keep you informed if I create other SQL statements. Yes I wanted to include the order status but somehow did not get the statement correct. We are a small Whisky Society and therefore our shop is not really a web shop. We use it for collective orders of our society, so each member can place an order and then our local Whisky shop which initiated the Society, is ordering a bulk order. Therefore we do not need all the statuses which are in the nb_store. I could change all the templates to get the cheque payment out of them and now I have only two problems left. First, I want to change the wording of the items in the status list and second I want to change the default shipment method to "no shipment" as we usually fetch our delivery in the local Whisky shop. Therefore, the "Waiting for cheque" Status should read something like "Neuer Auftrag erhalten" or in English "New Order received".

But I will change the SQL statement as you suggested above, as we of course know, that "Waiting for cheque" means "New Order received. I could of course change the wording in the NB_store_OrderStatus table, but I am afraid, that it might influence the functioning of the shop, that's why I did not do it until now.

Regards,

René

Apr 7, 2010 at 2:19 PM
Edited Apr 7, 2010 at 4:14 PM

Hi Dave,

I changed your SQL statement a little bit, it did not work with the language fr-FR it did not even work with de-CH therefore I deleted that statement and then it worked. I also added a selection for the Status ID, as I only want to see open orders and those which have been shipped so I indicated that I only want to see order ID 80 or 50. See my statement below:

SELECT
A1.OrderStatusID,
OS.OrderStatusText,
A2.OrderID as [Auftragsnummer],
A1.orderdate as [Auftragsdatum],
itemdesc as [Produkt] ,
A2.quantity as [Menge]
FROM nb_store_orders A1
inner join nb_store_orderdetails as A2 on A1.OrderId = A2.OrderId
inner join NB_Store_OrderStatus as OS on OS.OrderStatusID = A1.OrderStatusID
WHERE A1.OrderStatusID = 80 or A1.OrderStatusID = 50
ORDER BY A2.ItemDesc ASC

Then I did a second statement, because what is important for me, I also want to see the Quantity per Product at the end of the day, so I created the following simple statement:

SELECT
itemdesc as [Produkt], SUM(quantity) as [Menge]
FROM nb_store_orderdetails
GROUP BY itemdesc
ORDER BY ItemDesc ASC

At the moment these are two different E-Mails, but do you think, I could somehow amalgamate the two reports and put them into the same e-mail?

 

Regards,

René

 

Apr 8, 2010 at 8:41 AM

Hi René,

This is possible, but you'll need to change the statements to return the data in an xml format and then use xsl to create your report......might be easier to just have 2 reports.

Regards,

Dave.