Downloading only SALES recording from certain Campaign

[bondkmf]

Ok, so here is my dilemma. I have a client that JUST informed me that they need every SALE recording that has have been created for their campaign. I have searched everywhere and I cannot find a SQL script of cron job or any help for this matter on the forums. I am manually downloading (searching the lead and downloading the recordings) 1 by 1 for over 3,000 SALE records.

This will take weeks if it is the only thing I do.

Can anyone please if possible guide me or point me in the right direction to setup an automated process for getting this job accomplished? If it is even possible....

I am very familiar with the asterisk side of Vici but I haven't had to do much SQL work.

3 server cluster

1 x DB 1.4.21.2
2 x SIP 1.8.29.0-vici

Please advise....may even be willing to pay if this can be accomplished.

[mflorell]

/usr/share/astguiclient/AST_VDsales_export.pl --help
allowed run time options:
[--date=YYYY-MM-DD] = date override
[--hour-offset=X] = print datetime strings with this hour offset
[--filename=XXX] = Name to be used for file, variables: YYYY=year, MM=month, DD=day, HH=hour, II=minute, SS=second
[--campaign=XXX] = Campaign that sales will be pulled from
[--without-camp=XXX] = Campaign that will be excluded from ALL
[--sale-statuses=XXX-XXY] = Statuses that are deemed to be "Sales". Default SALE
NOTE: To include all statuses in the export, use "--sale-statuses=---ALL---"
[--output-format=XXX] = Format of file. Default "pipe-standard"
[--with-inbound=XXX-XXY] = include the following inbound groups
[--without-in=XXX-XXY] = inbound groups that will be excluded from ALL
[--calltime=XXX] = filter results to only include those calls during this call time
[--outbound-calltime-ignore] = for outbound calls ignores call time
[--totals-only] = print totals of time and calls only
[--ftp-transfer] = Send results file by FTP to another server
[--ftp-audio-transfer] = Send associated audio files to FTP server, dated directories
[--ftp-norun] = Stop program when you get to the FTP transfer
[--ftp-server=XXXXXXXX] = FTP server to send file to
[--ftp-login=XXXXXXXX] = FTP user
[--ftp-pass=XXXXXXXX] = FTP pass
[--ftp-dir=XXXXXXXX] = remote FTP server directory to post files to
[--nodatedir] = do not put into dated directories
[--with-transfer-audio] = Different method for finding audio, also grabs transfer audio filenames
[--skip-rec-xtra] = Do not perform additional recording lookups beyond vicidial_id
[--temp-dir=XXX] = If running more than one instance at a time, specify a unique temp directory suffix
[--http-user=XXX] = If using tranfer audio, this is the HTTP user needed to grab the recording files
[--http-pass=XXX] = If using tranfer audio, this is the HTTP password needed to grab the recording files
[--with-did-lookup] = Looks up the DID pattern and name the call came in on if possible
[--email-list=test.com:test2.com] = send email results to these addresses
[--email-sender=vicidial] = sender for the email results
[--email-post-audio] = send an email after sending audio over FTP
[--quiet] = quiet
[--test] = test
[--debug] = debugging messages
[--debugX] = Super debugging messages

format options:
pipe-standard|csv-standard|tab-standard|pipe-triplep|pipe-vici|html-rec|fixed-as400|tab-QMcustomUSA|tab-SCcustomUSA

[bondkmf]

Ok so I looked in Winscp and found that. Mine has "print" before each line though.

What's the best way to run this? Replace the X's with the information I need and restart asterisk? This is a live server and I cannot make a mistake. This is a huge learning curve and am very excited to figure this out.

[Vince-0]

You need to run that script from the command line.

[bondkmf]

Here is what I have changed the file too and I am still getting 0 results.

print "allowed run time options:\n";
print " [--date=2016-01-12] = date override\n";
print " [--hour-offset=X] = print datetime strings with this hour offset\n";
print " [--filename=TESTFILE] = Name to be used for file, variables: YYYY=year, MM=month, DD=day, HH=hour, II=minute, SS=second\n";
print " [--campaign=10000] = Campaign that sales will be pulled from\n";
print " [--without-camp=XXX] = Campaign that will be excluded from ALL\n";
print " [--sale-statuses=SALE] = Statuses that are deemed to be \"Sales\". Default SALE\n";
print " NOTE: To include all statuses in the export, use \"--sale-statuses=---ALL---\"\n";
print " [--output-format=pipe-standard] = Format of file. Default \"pipe-standard\"\n";
print " [--with-inbound=XXX-XXY] = include the following inbound groups\n";
print " [--without-in=XXX-XXY] = inbound groups that will be excluded from ALL\n";
print " [--calltime=XXX] = filter results to only include those calls during this call time\n";
print " [--outbound-calltime-ignore] = for outbound calls ignores call time\n";
print " [--totals-only] = print totals of time and calls only\n";
print " [--ftp-transfer] = Send results file by FTP to another server\n";
print " [--ftp-audio-transfer] = Send associated audio files to FTP server, dated directories\n";
print " [--ftp-norun] = Stop program when you get to the FTP transfer\n";
print " [--ftp-server=XXXXXXXX] = FTP server to send file to\n";
print " [--ftp-login=XXXXXXXX] = FTP user\n";
print " [--ftp-pass=XXXXXXXX] = FTP pass\n";
print " [--ftp-dir=XXXXXXXX] = remote FTP server directory to post files to\n";
print " [--with-transfer-audio] = Different method for finding audio, also grabs transfer audio filenames\n";
print " [--skip-rec-xtra] = Do not perform additional recording lookups beyond vicidial_id\n";
print " [--temp-dir=XXX] = If running more than one instance at a time, specify a unique temp directory suffix\n";
print " [--http-user=XXX] = If using tranfer audio, this is the HTTP user needed to grab the recording files\n";
print " [--http-pass=XXX] = If using tranfer audio, this is the HTTP password needed to grab the recording files\n";
print " [--with-did-lookup] = Looks up the DID pattern and name the call came in on if possible\n";
print " [--email-list=kfagan@excelnetworkllc.com:bondkmf@gmail.com] = send email results to these addresses\n";
print " [--email-sender=vicidial@localhost] = sender for the email results\n";
print " [--email-post-audio] = send an email after sending audio over FTP\n";
print " [--quiet] = quiet\n";
print " [--test] = test\n";
print " [--debug] = debugging messages\n";
print " [--debugX] = Super debugging messages\n";
print "\n";
print " format options:\n";
print " pipe-standard|csv-standard|tab-standard|pipe-triplep|pipe-vici|html-rec|fixed-as400|tab-QMcustomUSA|tab-SCcustomUSA\n";
print "\n";

[Vince-0]

This is how you run a perl script from Linux command line:

Code: Select all

perl /usr/share/astguiclient/AST_VDsales_export.pl --date=2016-01-12 --filename=TESTFILE --campaign=10000 --sale-statuses=SALE

[bondkmf]

That seems to work. This is what I got:

-- AST_VDsales_export.pl --

This program is designed to gather sales from a VICIDIAL outbound-only campaign and post them to a file.

Campaign: 10000 vicidial_log.campaign_id IN('10000')
Sale Statuses: SALE and vicidial_log.status IN('SALE')
Output Format: pipe-standard
With Inbound:


----- SQL CONVERT_TZ: -5:00|-5:00 '-5:00','-5:00' -----

SALES EXPORT FOR 2016-01-12: TESTFILE
TOTAL SALES: 0
TOTAL EXCLUDED BY CALLTIME:
script execution time in seconds: 0 minutes: 0


So now I have one last question. I need to set the parameters to gather the SALE recordings from the past year and MULTIPLE campaigns. How would you write that out?
For example above you have: --campaign=10000 and the date --date=2016-01-12

How do I broaden this so I can just run one giant search? Once again thank you so much.

[bondkmf]

Also I got it working however this is what it shows in the FTP:

~$Rushmore Sales Report DUQ 20161117.xlsx with 0 bytes as the size....so close.

[Vince-0]

You probably need --ftp-audio-transfer and the associated options.

I can't remember how to do it off hand.

[bondkmf]

Yes you were correct with that option. Another option I discovered was this.

You can add a prefix to the recording in "Campaign Rec Filename" like CAMP1_FULLDATE_CUSTPHONE and vicidial will save the file with CAMP1_201702151046.mp3. Then use rsync to only read files starting with CAMP1_.

This can be done when using the rsync method.

Cheers.