/ 
WWW-Salesforce-Report-0.03
River stage zero No dependents
/ WWW::Salesforce::Report

NAME

WWW::Salesforce::Report - The poor man's Salesforce report API in Perl!

VERSION

Version 0.02

SYNOPSIS

Create a rough API for Salesforce.com reports. Reports are downloaded in CSV format from Salesforce and can then be cached locally in a SQLite database. Regular SQL queries can then be ran on the data.

The reports can also be downloaded in Excel format, which will not be cache in a local database but can be sent as an attachment to users for example.

Perhaps a little code snippet.

use WWW::Salesforce::Report;

my $sfr = WWW::Salesforce::Report->new(
                id=> "000000068AxXd",
                user=> "myuser",
                password => "mypassword" );
    
$sfr->login();
$sfr->get_report();

my @data = $sfr->query( query => "select * from report" );

Save a report to an Excel file

use WWW::Salesforce::Report;

$sfr = WWW::Salesforce::Report->new(
    id=> "000000068AxXd",
    user=> "myuser",
    password => "mypassword" );
    
$sfr->login();
my $xls_data = $sfr->get_report(format => "xls");
my $name = $sfr->write(file=> "report.xls", compress => 0);

Attach a compressed version of the Excel report to an email and send it to a user or group of users:

use WWW::Salesforce::Report;
use Net::SMTP::TLS;
use Mime::Lite;

$sfr = WWW::Salesforce::Report->new(
    id=> "000000068AxXd",
    user=> "myuser",
    password => "mypassword" );
    
$sfr->login();
my $xls_data = $sfr->get_report(format => "xls");
my $name = $sfr->write(file=> "report.xls");

# using TLS to send the e-mail
my $mailer = new Net::SMTP::TLS(
    "mail.domain.com",
    Hello   => "mail.domain.com",
    Port    =>  25,
    User    => "my_user_name",
    Password=> "my_password");

# email of the sender
$mailer->mail("reports@domain.com");

# email of the recipient
$mailer->to("user@domain.com");

$mailer->data;

my $message = MIME::Lite->new(
        From    => "reports@domain.com",
        To      => "user@domain.com",
        Subject => "REPORT: Quarter Forecast by Region",
        Type    =>'multipart/mixed'
);

# Message body
$message->attach(
        Type => "TEXT",
        Data => "Here are the latest forecast numbers.",
);

# Attach the zip file
$message->attach(
        Type => "application/zip",
        Filename => $name,
        Path => $name,
        Encoding => "base64",
        Disposition => 'attachment',
);  

$mailer->datasend($message->as_string);

$mailer->dataend;
$mailer->quit;

DEPENDS

The WWW::Salesforce::Report depends on the following CPAN modules

  • HTTP::Cookies
  • HTTP::Headers
  • HTTP::Request::Common
  • LWP::UserAgent
  • Digest::MD5
  • DBI
  • Carp

METHODS

PUBLIC METHODS

This module uses a named paramter convetion. That means that all methods expect to be called like

  $obj->method( param_name => value, param2_name => value_2 );

and not like

$obj->method( value_1, value_2 );
new( OPTIONS )

Class constructor. You must call new() before using the object, and manipulating report data.

OPTIONS are passed in a hash like fashion, using key and value pairs. Possible options are:

Mandatory parameters:

user => user_name

password => password

User name and password to login to Salesforce.com.

Optional parameters:

id => salesforce_id

Salesforce.com Id of the report you want to download. Please make sure this is a valid report Id from Salesforce.com otherwise this module will fail. You can get the Id from the Salesfoce.com URL that points to the report you want to use with the script. Although you can set the report ID using report_id() it is a best practice to set it in the object constructor, and if you need to work with multiple reports create different objects for each one.

format => "csv"| "xls"

See format()

verbose => 0 | 1

If true a "lot" of debug or tracing messages will be printed. Enabled by default.

convert_dates => 0 | 1

If true convert dates from Salesforce.com format to SQLite format. Enabled by default.

login_url => URL

This must be a Salesforce.com direct login URL. See login. Defaults to:

login_url => https://login.salesforce.com/?un=USER&pw=PASS

csv_report_url => URL

Url to export the desired report as a CSV file. Defaults to:

csv_report_url => https://SERVER.salesforce.com/REPORTID?export=1&enc=UTF-8&xf=csv

xls_report_url => URL

Url to export the desired report as a CSV file. Defaults to:

csv_report_url => https://SERVER.salesforce.com/REPORTID?export=1&enc=UTF-8&xf=xls

In the above URLs the words SERVER, USER and PASS represent the Salesforce.com login server, user name and password.

Generally you do not need to supply your own URLs, these parameters are here just to ensure future compatibility if Salesfroce.com changes them.

erase_db => 0 | 1

If true the database file will be deleted and recreated anew. Defaults to 1.

allow_duplicates => 0 | 1

If true the database will allow duplicate records. For every report line a new field is added which equals the MD5 hash of said report line. This is done because not all Salesforce reports may contain Salesforce.com IDs. If you do not want duplicates in your database a UNIQUE index will be created on the __hash field. Defaults to 1, to allow duplicates.

primary_key_type => sqlite_type

The type of the primary key. This must be a valid SQLite type. Defaults to TEXT.

primary_key => name_of_primary_key

The name of the primary key. Defaults to __id

erase_reports_table => 0 | 1

If true erase the reports table. Defaults to 1. If the database already exists, it will not be created but you have the opportunity of erasing all data in the reports table.

erase_notifications_table => 0 | 1

If true erase the notifications table. Defaults to 0. If the database already exists, it will not be created but you have the opportunity of erasing all data in the notifications table.

clean_on_destroy => 0 | 1

If true clean up local files when destroying object. Defauts to true (1)

login ( )

Login to Salesforce.com via the URL method, which includes the user's credentials in a URL instead of the login form the user usually sees.

If successful returns 1, otherwise triggers an error.

The URL method of login is sort of a hack since it does not use an approved API call. Unfortunately the fact that Salesforce.com does not have a report API available we must use this method to get the report data.

As of this witting you can login to Salesforce.com directly, i.e., not using the Login page, by using the following URL:

https://login.salesforce.com/?un=USER&pw=PASS

where USER is the user name, and PASS the user's password.

login_server ( )

If the login was successful return the server to which the user logged on, otherwise return 0.

my $server;
if( $sforce->login() ) {
    $server = $sforce->login_server();
}

print "Logged to : $server \n";
report_id( [id => salesforce_id] )

Set or get the Salesforce.com Id of report you want to download.

$sforce->report_id()                      # return the current Salesforce.com Id.
$sforce->report_id( id=> salesforce_id )  # set the current Salesforce.com Id.
name ( )

Get the name of the local cache file or database.

format( format => "csv" | "xls" )

Get or set the report format

format => "csv"| "xls"

The report format. Possible values are "csv", or "xls". As of this writing Salesforce.com only supports CSV and XLS formats for exported reports. Comma Separated Value (CSV) is the only format that can be cached to a local SQLite database. If you set the local file, via the file parameter to new(), a local CSV file will be read from disk instead of downloading the report data from Salesforce.com. The local file must be in CSV format.

To return the current format:

my $format = $sforce->format();

To set the current format:

$sforce->format( format=> "csv" );
cache( cache => 0 | 1 )

Get or set the local cache flag

To return the current format:

my $cache = $sforce->cache();

To set the current format:

$sforce->cache( cache => 0 );
primary_key( key => field )

Get the primary key field of the database. Defaults to '__id'

clean ( )

Clean the local cache and report data. This method will delete the local cache file (SQLite database) if it exists. Downloaded report data will also be erased from memory.

get_report ( format => string, cache => 0 | 1, force => 0 | 1 )

Download a report from Salesforce.com or read it from a local file Returns a string with the report data.

OPTIONS are passed in a hash like fashion, using key and value pairs. Possible options are:

format => "csv"| "xls"

The report format. Possible values are csv, or xls As of this writing Salesforce.com only supports CSV and XLS formats for exported reports. Comma Separated Value (CSV) is the only format that can be cached to a local SQLite database. If you set the local file, via the file parameter to new(), a local CSV file will be read from disk instead of downloading the report data from Salesforce.com. The local file must be in CSV format.

cache => 0 | 1

If true create a local cache for the report data, using a SQLite database. cache is on by default but only used if report format is CSV or local. The database name is the report Id, with a 'db3' extension. Or local filename.db3 in case of local format.

force => 0 | 1

If called multiple times get_report() will return the cached data without downloading it again, and again. If you want to re-download the data from Salesforce.com, or re-read the local file, set force to 1. By default force is 0.

Example:

my $report = $sf->get_report( id => "000006889AAD", format => "xls");
write( file => file_name, compress => 0 | 1 )

Write report data to local file. If compress is 1 create a Zip file, with the compressed contents of the downloaded report. This is the default.

If a specific file name is not passed via file the report data will be saved to the default name returned by name().

Example:

Write report data to a Zipped Excel file

use PMP::Salesforce;

$sf = PMP::Salesforce->new(
    id=> "000000068AxXd",
    format => "xls",
    user=> "myuser",
    password => "mypassword" );
    
$sf->login();
$sf->get_report();
my $name = $sf->write();
query( query => sqlite_query, hash => 0 | 1 )

Run query on the cached report database. This method can only be called on CSV and local report formats that have been cached locally. If the database does not exist the mothod will fail and croak.

query => sqlite_query

Mandatory SQLite query string to execute.

hash => 0 | 1

Optional parameter that defaulst to 1, and controls how data is returned. If hash => 1 query result is returned as an array of hash references. If hash => 0 query result is returned as an array of array references. See DBI module documentation for fetchrow_hashref(), and fetchrow_arrayref().

Example:

$sf = PMP::Salesforce->new( user=> "myuser", password => "mypassword" );
$sf->login();
$sf->report_id(id=> "000000068AxXd");
$sf->get_report();
my @data = $sf->query(query => "select * from reports");
dbh ( )

Returns the DBI handle of the cached report database. Use this method if you need direct access to the local database DBI handle.

my $dbh = $sforce->dbh();
my $sth = $dbh->prepare( "select * from reports ) or
    croak "Couldn't prepare statement: " . $sth->errstr;

Just like you're accessing SQLite through DBI, a bit low level but allows you to manipulate the report data in a very flexible way.

PRIVATE METHODS

Internal methods are not to be used directly in your code. Documented in case you need to hack this module...

_request( URL )

Request Salesforce.com URL after the user has already been authenticated Returns the page content as a string. Can be used to get any URL

_db_check_create( fields => fields_str )

Check if the database exists and if it needs to be deleted/created before the report is imported to the database.

If the database file does not exist a new one is created and the two tables are created within it.

fields => list_of_field_names

String with the list of all the report fields (comma separated).

Database Schema

This is the table where all the report data will be stored. The fields of the table are the same as the ones used in the Salesforce.com report, but with all the characters that do not match 0-9, a-z or A-Z removed. The database will have two tables (report and notifications) and optionally an index (__report_index).

Reports Table

Table to store all report data. Fields are determined by the fields parameter all the types are TEXT. A __hash fields is always added to the report table. This field will store a MD5 hash of each report line, and can be used to create a UNIQUE index if you do not want repeated report lines in your local database.

Notifications Table

Table to store notification data, for instance emails sent to users notifying them about report data.

Unique index on __hash on reports

If allow_duplicates is false then an index will be created on the __hash field of the reports table in order to guarantee no duplicate report line data is stored in the database.

Example:

$self->_db_check_create( fields => "OpportunityOwner, Amount, Probability" );
_dd($self, $mon, $day, $year)

Convert the dates from mm/dd/yyyy to yyyy-mm-dd

_cache_report_to_sqlite( $data )

Take the CSV formated $data and import it into the SQLite database. The CSV must have all the fields in the first line of the file, be comma separated data and all fields should be quoted with "

Report data is imported into the reports table in the database.

_set_name ( name => file_name, delete => 0 | 1)

Set the name of the local cache database or file to which the report data is saved.

Optional Parameters:

name => file_name

Name of file to use. If name is not given the file's name is determined by using the current report format, and local file, or id that were passed to new().

As in other methods the file option takes precedence over Salesforce.com report id. Teh name is constructed by appending and extension to either the file or id as follows

Extensions and formats: CSV -> name.db3 XLS -> name.xls

delete => 0 | 1

If delete is true the report data, and local cache file will be deleted before the name change. Otherwise the file is left on disk. Default is true (1).

Destructor

Object destructor will clean the local cache files format is CSV or XLS.

AUTHOR

Pedro Paixao, <paixaop at gmail.com>

BUGS

Please report any bugs or feature requests to bug-www-salesforce-report at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=WWW-Salesforce-Report. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SEE ALSO

WWW::Salesforce

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc WWW::Salesforce::Report

You can also look for information at:

ACKNOWLEDGEMENTS

LICENSE AND COPYRIGHT

Copyright 2010 Pedro Paixao.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.