Crystal Statements in CSV/Excel format.

Discussions relating to plugin development, and the Jiwa API.

Crystal Statements in CSV/Excel format.

Postby Ernst » Wed Feb 19, 2020 12:41 pm

A common request these days from the customer, is not to get a PDF statement, (which is difficult to edit), but to get the statement as Excel.

Probably CSV could be in that category, as its easy to open with Excel.

So. Is it possible, or has anybody been able to email report in formats other then PDF from JIWA.

And would it be possible to change the standard debtor statement send, to change the format from PDF to Excel(data)/ CSV as part of the statement run, for certain customers?

Thanks,
User avatar
Ernst
Frequent Contributor
Frequent Contributor
 
Posts: 114
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 7

Re: Crystal Statements in CSV/Excel format.

Postby Scott.Pearce » Wed Feb 19, 2020 12:54 pm

There is a standard plugin called "Email Report" which allows a report that has been printed to screen to be emailed on. This plugin could be modified to change the format that the report will be emailed in. The latest version of the plugin can be found at viewtopic.php?f=27&t=664&p=5094&hilit=email+report#p5094

How the exported report renders as far as CSV or Excel is concerned is dependent on the design of the report. Your mileage may vary given any particular report.

As for Debtor Statements, it appears that the export format is hard-coded to be PDF, and I do not see a way of jumping in with a plugin to change that. I suggest logging a DEV issue to at least allow a plugin to manipulate the export format for operations such as Debtor Statement and Sales Order emailing.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Jiwa Senpai
Jiwa Senpai
 
Posts: 568
Joined: Tue Feb 12, 2008 11:27 am
Location: North Sydney, New South Wales
Topics Solved: 169

Re: Crystal Statements in CSV/Excel format.

Postby SBarnes » Wed Feb 19, 2020 3:50 pm

The following nuget package can have it's DLL embedded in a plugin if you want excel

https://www.nuget.org/packages/EPPlus/

The other option is with the web api you can output to csv, so people could get it on demand and ORM Lite is easy enough to use to call the same stored proc that the statement uses.
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 819
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 71

Re: Crystal Statements in CSV/Excel format.

Postby Ernst » Wed Feb 19, 2020 6:57 pm

Thanks for prompt response, will check those plugins out, and give it a go..Cheers
User avatar
Ernst
Frequent Contributor
Frequent Contributor
 
Posts: 114
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 7

Re: Crystal Statements in CSV/Excel format.

Postby Mike.Sheen » Wed Feb 19, 2020 7:46 pm

SBarnes wrote:The other option is with the web api you can output to csv, so people could get it on demand and ORM Lite is easy enough to use to call the same stored proc that the statement uses.


This is the way.

mandalorianthisistheway.jpg
mandalorianthisistheway.jpg (11.15 KiB) Viewed 188 times
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1650
Joined: Tue Feb 12, 2008 11:12 am
Location: North Sydney
Topics Solved: 522

Re: Crystal Statements in CSV/Excel format.

Postby SBarnes » Thu Feb 20, 2020 11:45 am

If you are going to go the api route the following is the ORMLite parts you need, generated using T4, you'd just need to setup a response class to deserialise into and then create a service to execute the code

Code: Select all

      public static OrmLiteSPStatement usp_JIWA_Debtors_StatementsAsAt(this IDbConnection db, string @sP_StartingDebtorAccountNo = null, string @sP_EndingDebtorAccountNo = null, DateTime? @sP_AsAtDate = null)
      {
         var dbCmd = (DbCommand)OrmLiteConfig.ExecFilter.CreateCommand(db).ToDbCommand();
         dbCmd.CommandText = "usp_JIWA_Debtors_StatementsAsAt";
         dbCmd.CommandType = CommandType.StoredProcedure;
         dbCmd.Parameters.Add(CreateNewParameter(dbCmd,"SP_StartingDebtorAccountNo",@sP_StartingDebtorAccountNo,ParameterDirection.Input,DbType.AnsiString));
         dbCmd.Parameters.Add(CreateNewParameter(dbCmd,"SP_EndingDebtorAccountNo",@sP_EndingDebtorAccountNo,ParameterDirection.Input,DbType.AnsiString));
         dbCmd.Parameters.Add(CreateNewParameter(dbCmd,"SP_AsAtDate",@sP_AsAtDate,ParameterDirection.Input,DbType.DateTime));
          dbCmd.Parameters.Add(CreateNewParameter(dbCmd,"__ReturnValue",0,ParameterDirection.ReturnValue,DbType.Int32));
         return new OrmLiteSPStatement(db, dbCmd);
      }
      
      
      
      private static DbParameter CreateNewParameter(DbCommand dbCommand, string paramName, object paramValue, ParameterDirection paramDirection, DbType paramType)
      {
         DbParameter param = dbCommand.CreateParameter();
         param.Direction = paramDirection;
         param.DbType = paramType;
         param.ParameterName = paramName;
         param.Value = paramValue ?? DBNull.Value;
         return param;
      }
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 819
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 71

Re: Crystal Statements in CSV/Excel format.

Postby SBarnes » Sun Mar 01, 2020 9:38 am

Hi Ernst,

Attached is a plugin for the Rest API that will export the invoice and statement, either to pdf, csv or xml.

There are a number of system settings that you need to set before starting the API that are fairly self explanatory.

Once enabled and with a debtor with an api you call the routes as follows:

Code: Select all
//To get the statement
http://localhost:81/Export/Statement?apikey=DebtorAPIKeyHere


//to get the statement as a pdf
http://localhost:81/ExportPDF/StatementPDF?apikey=DebtorAPIKeyHere

//to get an invoice
http://localhost:81/Export/Invoice?apikey=DebtorAPIKeyHere&InvoiceNo=InvoiceNumberHere&HistoryNo=HistoryNumberHere


//to get an invoice as pdf
http://localhost:81/ExportPDF/InvoicePDF?apikey=DebtorAPIKeyHere&InvoiceNo=InvoiceNumberHere&HistoryNo=HistoryNumberHere


To get csv add &format=csv and to get XML add &format=XML to the exports that are not pdf related.

Note the stored procedures and reports are the Jiwa Demo ones but in theory any stored procedures and reports that take parameters the same as the out of the box ones should work.
Attachments
Plugin REST API Export Invoice and Statement.xml
Plugin to export Statement and Invoice
(58.48 KiB) Downloaded 13 times
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 819
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 71

Re: Crystal Statements in CSV/Excel format.

Postby Mike.Sheen » Sun Mar 01, 2020 12:33 pm

Hi Stuart,

Thanks for the generous sharing of the plugin and the explanation of usage.

I'm curious - how many hours would you say you spent doing this?

Oh, and I really like how you invoked our service here - that was elegantly clever!

Code: Select all
using (ReportsServices rs = base.ResolveService<ReportsServices>())
{
   ReportsPDFGETRequest pdfreq = new ReportsPDFGETRequest();
   pdfreq.ReportID = RESTAPIPlugin.InvoiceReportID;
   pdfreq.ReportParameters = new List<ReportParameter>();
   pdfreq.AsAttachment = true;
   pdfreq.ReportParameters.Add(new ReportParameter(){Name = "Pass_SP_InvoiceHistoryID", Value = HistoryID});
   return rs.Get(pdfreq);               
}
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Jiwa Shihan
Jiwa Shihan
 
Posts: 1650
Joined: Tue Feb 12, 2008 11:12 am
Location: North Sydney
Topics Solved: 522

Re: Crystal Statements in CSV/Excel format.

Postby SBarnes » Sun Mar 01, 2020 4:26 pm

Hi Mike,

It only took me about an hour or so to put it together it was a glorified exercise in cut and paste as I would say I had over 90% of the code already, it was one of the "perfect storm" moments, for instance the look ups for the reports and stored procedures just involved cut and paste and changing the names of the system setting fields in the if statements.

The calling of your reporting service again was a cut and paste of production code from an extension of the rest api that I sent you a copy of to test the work on caching against that you were doing at the end of last year. It's used by a customer's web store to get the statement and invoice. It's also mentioned on the forums before see viewtopic.php?f=32&t=993 although the using clause is necessary to stop any memory leak.

The testing of the api key for the debtor is in an export to file plugin that is at present being tested for production.

The only new bit is getting the Invoice History ID from the Invoice Number, History Number and Debtor ID and that pretty well fell out of your ORM Lite statement in the custom route plugin.

The ORM Lite stuff is thanks to the T4 templates and the response classes based upon a script that will turn a table into a c# class, so I just made the temporary tables real tables in the database for a couple of minutes to get that.

If I'd had to do it from scratch it wouldn't exist I'm happy to be generous but not that generous as I'd say from scratch you'd probably be looking at about six hours work :D

I also probably wouldn't of done it for a one off issue but I figure there is probably multiple sites that could use it.
Regards
Stuart Barnes
stuart@attkey.com.au
SBarnes
Jiwa Sensei
Jiwa Sensei
 
Posts: 819
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 71


Return to Technical / Programming

Who is online

Users browsing this forum: No registered users and 9 guests

cron