kirchaj
Posts: 215
|
| Posted: 09/04/2012, 11:45 AM |
|
Has anyone had any luck generating a fixed length text file or an xml file that can be downloaded from within CCS?
I have an existing report that I need to have the user run and export to a fixed length text file or an xml file that can then be uploaded to our state for reporting purposes.
Any help will be greatly appreciated.
TK
|
 |
 |
bannedone
Posts: 273
|
| Posted: 09/04/2012, 4:15 PM |
|
Hi
This should be pretty straight forward.
Which aspect of doing this are you having issues with???
Can you let us know more detail of this requirement??
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
kirchaj
Posts: 215
|
| Posted: 09/05/2012, 7:46 AM |
|
Thanks Bannedone,
Like I say I have the ability to generate the report in CCS, can export it to excel.
But I don't even know how to start beyond that. I do have the flat file layout but I am almost wanting to look towards the XML. I don't have that layout yet but will soon.
Here is the process that needs to happen. My office people need to run the report and verify contents. Then export the xml file to their machine. Upload to another website, then verify again, and process the data.
This will happen several times throughout the year. So the question is, how do I generate the XML file and make it so they can download it?
Any help is appreciated.
TK
|
|
|
|
DataDoIT
|
| Posted: 09/05/2012, 9:23 AM |
|
Is the upload to another website a web service? Curious as to why they
want an XML file, which is typically sent out as a post to a web service
that will then process it.
It may also be possible to post the XML file anyways using PHP's cURL.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 09/05/2012, 9:34 AM |
|
XML was made an option because web services used to be an option. Those who are currently using XML can make minor changes to there process to generate the file.
They are changing the process completely and forcing user interaction. User must upload the file. User must verify the contents. User must complete the processing.
They are not allowing web services any more and requiring a change to the schema. If flat file is easier, i could care less :) I just want to be able to take advantage of the bulk upload feature that we have never been able to do before.
So if flat fixed length file is easier, I am all for it.
Thanks DataDoIT
TK
|
|
|
|
DataDoIT
|
| Posted: 09/05/2012, 12:43 PM |
|
In a nutshell....
Forget about using Report, use a Grid. Then with the combination of
CCS's events such as BeforeShow, BeforeShowRow, BeforeUnload, etc.
you'll use PHP file functions to build the XML content. When all's
done, spit it out to the browser to download with a custom header()
redirect.
We do this often, but for lots of money. The concept above is what matters.
|
|
|
|
bannedone
Posts: 273
|
| Posted: 09/05/2012, 2:34 PM |
|
Hi
Let me be sure I understand this requirement..
You will display a report generated by CCS to a user. (Assuming on this site CCS is the application)
The user validates the report.
Then after the report is approved THEN you need to create this flat file.
Download that flat file
Then upload that flat file to a different web site
Put that flat file into some table on the other site (By you or the other site's system?)
Generate the report again at the new website for verification.
Is that close to what you are trying to do??
I also wonder why download then upload. Is this flat file being used at the download site for anything?
If this is just a moving method for the flat file, why not FTP it to the other site via the CCS Application?
Let me know
Have fun 
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
kirchaj
Posts: 215
|
| Posted: 09/06/2012, 5:55 AM |
|
DataDoIT,
Thanks for the insight. When I said report, I really did mean grid. Very rarely do I actually use the report function. I put together a grid with a search function based on requirements and then include an export function to excel. I may have to ask more questions about your methods as time goes on.
Bannedone,
Yes you are correct. It is not our requirement but the state agency that we are dealing with. They do not want to upload the file through ftp, they want the user to upload a local file to their website. The outline of the steps is correct.
Either way I know I am going to have some challenges getting the flat file or the xml file generated from the grid. Expect a lot more questions :)
Thanks for all the help.
TK
|
|
|
|
bannedone
Posts: 273
|
| Posted: 09/06/2012, 7:27 AM |
|
Hi
Now that we understand in a little more detail of how you are wanting this to work and that your page is actually a grid not a report, I can describe a method for you to consider.
First create your grid page so it works the way you want and displays the data you want to create the download file from.
Then add a button or link to the page that might say "Download File". This button should re-display your grid the same as it originally displayed. (in other words uses the same search criteria originally used to display the grid. There are several methods to do this based on how the page was designed.)
Once you verify the new download button re-displays the same data, add to that button a new URL parameter. Let's say maybe &dl=yes or ?dl=yes depending where it sits on the new URL for the button.
In the BeforeShowRow event add some custom code that will build your download file. based on the value of the new URL Parameter.
One method to do this might be:
if (CCGetFromGet("dl","")=="yes") {
global $outPut;
//Build your fixed len data like so
$outPut .= $Container->GridFieldName1->GetValue().$Container->GridFieldName2->GetValue(). .... Etc all Grid Fields You want in the Download File
$outPut .= "\n\r"; // Usually Flat Files require line terminators. U need to find out what they might be in your case
}
Now I do not know how your flat file needs to delimit the different fields. You need to find that out from the state as to what they expect. Is each field just a fixed len? (in which case you will have to pad each grid field to that spec, Or they can be quoted or comma delimited etc...
Now to create the download file, you just need to add something like this in the BeforeOutput event
custom code like so:
if (CCGetFromGet("dl","")=="yes") {
global $outPut;
global $main_block;
//Set Headers for File Download
$fileName = Your File name with whatever file extension you want like maybe .txt, U could programmatically create a filename with a timestamp if you desire by maybe $fileName = "RPT_".date("mm-dd-yy") or other permutations of the date() function.
header("Content-Type: octet-stream");
header("Content-Disposition: attachment; filename=" .$fileName . ";");
header("Content-Length: " . strlen($outPut));
$main_block = $outPut;
}
Let me know if that helps
Have fun
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
DataDoIT
|
| Posted: 09/06/2012, 11:07 AM |
|
Let me reiterate that you can post the file to their web form using PHP
cURL, so you can theoretically make this work without any human
intervention with their web site.
We do this OFTEN with several state agencies here in Florida. Guv'ment
is weird like that, but we get around it.
|
|
|
|
bannedone
Posts: 273
|
| Posted: 09/06/2012, 4:38 PM |
|
Hi
I thought I read your requirements as the state mandated the user uploads from a local file.
But if not........
Here is some information on how to post the flat file using cURL
Simple changes required to the BeforeOutput Download example above as follows:
Instead of moving $outPut to $main_block, Just create a file using PHP file open and write functions with the $fileName you specified and put $outPut into that file.
Remove the header functions.
(*Note: Don't forget to develop any maintenance on the files you create like archiving them or deleteing them or re-sending them, or what ever your retention requirements may be for these report files, etc...)
It also would be possible to Download the file as well as post it using cURL. Just leave the move of $outPut to $main_block in and the header functions in but write $output to a file as well. I think you will need to use the background method for the cURL script described below to do both.
Use that file in this example of cURL post form
http://blogs.digitss.com/php/curl-php/posting-or-upload...-curl-with-php/
(easier than me typing all that cURL example myself) LOL
The cURL code should be added at the end of the BeforeOutput custom code inside the check for the value of the new dl URL parameter so it only executes when the the dl param = yes.
If the state has captcha or something like that on their upload page or it requires a login, the cURL implementation becomes a bit more complicated.
Also if the state changes the upload page (ie: field names etc.) your cURL code will need to be modified to match the new page.
BTW. I am not sure but you might need to kick this off as a background page. Your cURL stuff might cancel if you close the page or move to another page before the file is finished posting.
Here is an example of how to do that:
http://realsites.biz/codecharge_studio_forum.php?forum_...rum_topic_id=84
Again you would add this to the end of the BeforeOutput Custom code as described above. (Your cURL script would be a separate page in this case kicked off in the Background by this grid page and passing the filename parameter in the URL)
If you need more details or assitance PM me or contact me thru http://realwebdevelopment.us
_________________
John Real
CodeCharge Studio Support, Training, Consulting, Development, Web based solutions
http://realsites.biz
http://ccselite.com
Other Banned IDs on this Forum. jjrjr1, jjrjr2 |
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 09/07/2012, 1:46 AM |
|
Maybe add a button to search form with custom code and a function in common.php that uses CCGetParam to source the search parameter values, and use them in an sql script and tinker with this class for your xml needs:
http://www.ibm.com/developerworks/opensource/library/os-phpexcel/
I have done something like this solution (however mine is only as export to excel .csv) using a script that I found on this site - http://forums.yessoftware.com/posts.php?post_id=109235 b.t.w. I haven't stress tested a 1000000 records export limit).
*note*
(If using date fields in the search/sql, and these are blank/NULL parameters then I have set upper and lower default dates in the sql...otherwise the script conditions were NULL and when it couldn't find any NULL dates, the sql failed. Also, where other search parameter values were as NULL I used LIKE %)
**note** I had to remove a line from the script in the other forum post to make it work for me so the following script is my working code:
On Click Custom Code:
$siteid = CCGetFromGet("siteid");
$sitename = CCGetFromGet("sitename");
$ordertypeid = CCGetParam("s_ordertypeid");
if ($ordertypeid > 0)
$ordertypeid = CCGetParam("s_ordertypeid");
else
$ordertypeid = "%";
$supplierid = CCGetParam("s_supplierid");
if ($supplierid > 0)
$supplierid = CCGetParam("s_supplierid");
else
$supplierid = "%";
$accountcodeid = CCGetParam("s_accountcodeid");
if ($accountcodeid > 0)
$accountcodeid = CCGetParam("s_accountcodeid");
else
$accountcodeid = "%";
$duedate1 = CCGetParam("s_duedate1");
$duedate2 = CCGetParam("s_duedate2");
$duedate1 = strtotime($duedate1);
$duedate2 = strtotime($duedate2);
$duedate1= date("Y/m/d",$duedate1);
$duedate2= date("Y/m/d",$duedate2);
$added1 = CCGetParam("s_added1");
$added1 = strtotime($added1);
if ($added1 > 0)
$added1= date("Y/m/d",$added1);
else
$added1 = "0000-00-00";
$added2 = CCGetParam("s_added2");
$added2 = strtotime($added2);
if ($added2 > 0)
$added2= date("Y/m/d",$added2);
else
$added2 = "2100-01-01";
$completed1 = CCGetParam("s_completed1");
$completed1 = strtotime($completed1);
if ($completed1 > 0)
$completed1 = date("Y/m/d",$completed1);
else
$completed1 = "0000-00-00";
$completed2 = CCGetParam("s_completed2");
$completed2 = strtotime($completed2);
if ($completed2 > 0)
$completed2 = date("Y/m/d",$completed2);
else
$completed2 = "2100-01-01";
$db = new clsDBFM();
$sql = "SELECT sitename AS 'Site Name', idorder as 'Order ID', added AS 'Added', duedate AS 'Due Date',
inprogress1 AS 'In Progress', completed1 AS 'Completed', suppliername AS 'Supplier', description AS 'Description',
accountcode AS 'Account Code', cost AS 'Cost (ex gst)', invoicenumber AS 'Invoice Number', invoicecost AS 'Inv Cost (ex gst)',
ordertype AS 'Order Type'"
." FROM (((orders LEFT JOIN ordertype ON
orders.ordertypeid = ordertype.idordertype) LEFT JOIN supplier ON
orders.supplierid = supplier.idsupplier) LEFT JOIN accountcode ON
orders.accountcodeid = accountcode.idaccountcode) LEFT JOIN site ON orders.siteid = site.idsite"
." WHERE orders.siteid = '$siteid' AND ordertype.idordertype LIKE '$ordertypeid'
AND accountcode.idaccountcode LIKE '$accountcodeid'
AND orders.supplierid LIKE '$supplierid'
AND duedate >= '$duedate1' AND duedate <= '$duedate2'
AND added >= '$added1' AND added <= '$added2'
AND completed >= '$completed1' AND completed <= '$completed2'
ORDER BY duedate desc";
$db->query($sql);
$db->close();
exportMysqlToCsv($sql,'Order_Report('.date(dMYHis).').csv');
and in common.php:
function exportMysqlToCsv($sql,$filename = 'export.csv')
{
$db=new clsDBFM();
$sql_exec = $db->query($sql.' LIMIT 0, 1');
$cols = @array_keys(@mysql_fetch_assoc($sql_exec));
$table = @implode(',',$cols)."\n";
$count_col = count($cols);
// if(!$count_col) print get_text(no_work_don);
$sql_exec = $db->query($sql. ' LIMIT 0, 1000000');
while ($db->next_record())
{
$col_array = NULL;
for($i = 0 ; $i < $count_col ; $i++)
{
$col_array[] = $db->f($cols[$i]);
}
$table .= @implode(',',$col_array)."\n";
}
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($table));
header("Content-type: text/x-csv");
header("Content-Disposition: attachment; filename=$filename");
echo $table;
exit;
_________________
Central Coast, NSW, Australia.
|
|
|
|
|
