Guest Posted May 15, 2006 Share Posted May 15, 2006 (edited) The purpose of this contribution is to provide general reports of the database contents to the store owner in both PDF and plain SQL formats. A script configuration facility offers various options for storing SQL scripts that can be used to instantiate reports with the single click of a button. A tooltip facility is also present that allows instant information to an sql table structure. The package includes an enhanced fpdf library along with a new class to process reports in PDF format. A PDF example is included with the archive. Furthermore the contribution processes sql scripts and generates the sql output. Note with this first version only the select command has been tested. http://www.oscommerce.com/community/contributions,4238 Edited May 15, 2006 by enigma1 Quote Link to comment Share on other sites More sharing options...
Guest Posted May 16, 2006 Share Posted May 16, 2006 There are 2 items missing from this contribution 1. Modifications to the catalog\admin\includes\column_left.php Just before the last closing ?> add the following: //-MS- SQL Scripts Box added require(DIR_WS_BOXES . FILENAME_SQL_SCRIPTS); //-MS- SQL Scripts Box added EOM This will include the sql scripts on the left panel. 2. The button_new.gif image is missing from the catalog\admin\includes\languages\english\images\buttons folder. There is incorrect numbering of the steps in the readme.htm These minor changes will be added with the next version (1.01) once I'll confirm the contribution's operation. Quote Link to comment Share on other sites More sharing options...
Guest Posted May 16, 2006 Share Posted May 16, 2006 Released v1.01 (complete package) that includes the missing files and instructions. Quote Link to comment Share on other sites More sharing options...
sawad Posted May 16, 2006 Share Posted May 16, 2006 Hi, Great contribution. Just one small thing. The javascript code to automatically fill in the execute query textarea box when you select a pre-defined query does not work in Firefox. Thanks, Sherif Quote Sherif Awad Technical Support Specialist Link to comment Share on other sites More sharing options...
azer Posted May 16, 2006 Share Posted May 16, 2006 interessting contirbution, maybe we could post more exemple , or even predefine some general reports people would like , like list of costumers , list of orders , list of manufacturer What about the security ? could this contribution be used to by some hackers to get info on the database , this is always what im a afraid of when i install contribtuion that give info on the database best regards for your share Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 16, 2006 Share Posted May 16, 2006 for the FF jscript issue replace the comboToArea with this: function comboToArea(object, area_name) { for (var i = 0; i < object.options.length; i++) { if (object.options[i].selected) { object.selectedIndex = i; break; } } obj_content = object.options[object.selectedIndex].value; mytextarea = document.getElementsByName(area_name); mytextarea[0].value = obj_content; } It should work with FF and IE. Still the mouse relocation is unfortunately browser specific so I need to enhance the jscript for the tooltip. There is no problem with IE. For more examples basically you could combine queries there're plenty in the forums here is a simple I use to concatanate customer's names and emails select CONCAT(c.customers_lastname, ' ', c.customers_firstname) as full_name, customers_email_address as email from customers c order by c.customers_lastname select products_name as name, products_description as details from products_description where language_id=1 select orders_id, title, text, value from orders_total where title='Total:' I am sure overtime, more examples will emerge that could be useful for a store. As of the security this module it's on the admin end. And your osc admin should always be protected. There other tools if someone enters your osc Admin will likely use (database or file managers). Quote Link to comment Share on other sites More sharing options...
Guest Posted May 16, 2006 Share Posted May 16, 2006 Here is the fix for the tooltip jscript code. It should work now on both FF and IE. function moveToMouseLoc(e) { var ns4 = document.layers; var ns6 = document.getElementById && !document.all; if(ns4||ns6) { x = e.pageX; y = e.pageY; } else { x = event.x + document.body.scrollLeft; y = event.y + document.body.scrollTop; } toolTipSTYLE.left = x + offsetX; toolTipSTYLE.top = y + offsetY; return true; } I will include both fixes with the next release. Quote Link to comment Share on other sites More sharing options...
azer Posted May 17, 2006 Share Posted May 17, 2006 select products_name as name, products_description as details from products_description where language_id=1 if i got language id 4 and 5 how ot adjust select products_name as name, products_description as details from products_description where language_id=1, products_description as details from products_description where language_id=4, products_description as details from products_description where language_id=5 doesnt work ... I think really it make me earn a lot of time to check all the data in the database in easy way I hope you'll put in the next release in the sql a list of the query members will add here :-) Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 17, 2006 Share Posted May 17, 2006 if i got language id 4 and 5 how ot adjust select products_name as name, products_description as details from products_description where language_id=1, products_description as details from products_description where language_id=4, products_description as details from products_description where language_id=5 doesnt work ... I think really it make me earn a lot of time to check all the data in the database in easy way I hope you'll put in the next release in the sql a list of the query members will add here :-) your query is incorrect. Place a single where clause and use "or" for the languages. select products_name as name, products_description as details from products_description where language_id=1 or language_id=4 or language_id=5 Quote Link to comment Share on other sites More sharing options...
azer Posted May 17, 2006 Share Posted May 17, 2006 your query is incorrect. Place a single where clause and use "or" for the languages. your solution works thanks , but it mae one new line for each language and the same product , i wanted to have for instance the name in several language and the description in the 3 languages ,and if it s not possibile then the name is the defaut langauge and then the descritpion in the 3 languages Thanks in advance :P Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 17, 2006 Share Posted May 17, 2006 use the if statement and if the language id is set to say 1 you display the name otherwise you do not. But the number of columns that return will be the same for each row. So select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 Quote Link to comment Share on other sites More sharing options...
azer Posted May 17, 2006 Share Posted May 17, 2006 use the if statement and if the language id is set to say 1 you display the name otherwise you do not. But the number of columns that return will be the same for each row. So select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 sorry this code doenst work it gives name Adidas Y-3 Shoes products_description -------------------------------------------------------------------------------- name 4 products_description -------------------------------------------------------------------------------- name 5 os instaed of name it gives the number id of the language and no porduct description Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 17, 2006 Share Posted May 17, 2006 this is because I set the language id so you will see how it works you could replace it with '' As of the description seems you have no description for that entry. Quote Link to comment Share on other sites More sharing options...
Guest Posted May 20, 2006 Share Posted May 20, 2006 i am getting this error in admin and nothing is showing up on site. i am thinking it might be a connect problem. We changed servers and it worked fine then last week it just blew up. 1064 - You have an error in your SQL syntax near 'MAX_DISPLAY_NEW_PRODUCTS' at line 1 select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' and TO_DAYS(NOW()) - TO_DAYS(p.products_date_added) < 'NEW_PRODUCT_DAYS' order by p.products_date_added desc limit MAX_DISPLAY_NEW_PRODUCTS [TEP STOP] I am also getting an error message saying lanuage isn ot defined and corruency is not set and this error above is in most of the admin boxes. Any one know what happened? I am thinking a connection to the db but it is not saying that on the main page. If it were not connecting it would send an error message saying it failed to connect to database server. Thanks Quote Link to comment Share on other sites More sharing options...
Guest Posted May 20, 2006 Share Posted May 20, 2006 i am getting this error in admin and nothing is showing up on site. i am thinking it might be a connect problem. We changed servers and it worked fine then last week it just blew up. 1064 - You have an error in your SQL syntax near 'MAX_DISPLAY_NEW_PRODUCTS' at line 1 select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' and TO_DAYS(NOW()) - TO_DAYS(p.products_date_added) < 'NEW_PRODUCT_DAYS' order by p.products_date_added desc limit MAX_DISPLAY_NEW_PRODUCTS [TEP STOP] I am also getting an error message saying lanuage isn ot defined and corruency is not set and this error above is in most of the admin boxes. Any one know what happened? I am thinking a connection to the db but it is not saying that on the main page. If it were not connecting it would send an error message saying it failed to connect to database server. Thanks Did you install the osC mySQL contribution and you got that error? or you simply got it? If that's not related with the contribution try the general forum. Otherwise post the contribution details problem. Quote Link to comment Share on other sites More sharing options...
lazylex Posted May 21, 2006 Share Posted May 21, 2006 Hi, Great contribution. Only one thing is not working for me: When i select a query and the option SQL it works fine When i select PDF i get this error: Warning: Division by zero in /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/includes/classes/pdf_mysql_report.php on line 418 Warning: Cannot modify header information - headers already sent by (output started at /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/includes/classes/pdf_mysql_report.php:418) in /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/pdf/fpdf/fpdf.php on line 1071 FPDF error: Some data has already been output to browser, can't send PDF file Regards, Alex Quote Link to comment Share on other sites More sharing options...
Guest Posted May 22, 2006 Share Posted May 22, 2006 Ok I know where the error is happening from your report but I don't know why is happening. Can you tell me a couple of things: 1. The sql query you inserted in the edit box to generate the PDF; or the table name you selected to generate the PDF. 2. What happens when you select a table like countries and then you click the pdf button on the right side. Do you still see the same error? 3. Also try this simple query in the edit box and generate a PDF to see if the error is still there. select products_id from products There is a limit based on the width of the paper type. If you have lets say 50 columns the width of each one will likely be too short and may cause this problem with the code because it adjusts the column width based on the number of columns. Quote Link to comment Share on other sites More sharing options...
azer Posted May 22, 2006 Share Posted May 22, 2006 this is because I set the language id so you will see how it works you could replace it with '' As of the description seems you have no description for that entry. True i got no description for a lot of my product im sorry i dont understand how the query has to be change : select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 Cannot modify header information - headers already sent by (output started sometimes it s because there are so empty ligne containing space after the last ?> Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 22, 2006 Share Posted May 22, 2006 True i got no description for a lot of my product im sorry i dont understand how the query has to be change : select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 Cannot modify header information - headers already sent by (output started sometimes it s because there are so empty ligne containing space after the last ?> the query will be like this select if(language_id=1, products_name, '') as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 Quote Link to comment Share on other sites More sharing options...
azer Posted May 22, 2006 Share Posted May 22, 2006 select if(language_id=1, products_name, '') as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5 I really got no luck , i got 3 language and so 3 translation of the name product , here here the result of the code u gave me name products_description -------------------------------------------------------------------------------- name Adidas Y-3 - Vest - Blue products_description -------------------------------------------------------------------------------- name products_description -------------------------------------------------------------------------------- name products_description -------------------------------------------------------------------------------- name Adidas Y-3 - Vest - Green products_description while in fact i'd like to have: name en Adidas Y-3 - Vest - Blue name fr Adidas Y-3 - Veste - bleue name de Adidas Y-3 - Vest - blaue products_description en english descritpion products_description fr french descritpion products_description de german descritpion :( Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 22, 2006 Share Posted May 22, 2006 (edited) then use the original query and append the language details select concat(l.code, ' - ', pd.products_name) as name, pd.products_description as details from products_description pd left join languages l on (l.languages_id=pd.language_id) where pd.language_id=1 or pd.language_id=4 or pd.language_id=5 Edited May 22, 2006 by enigma1 Quote Link to comment Share on other sites More sharing options...
azer Posted May 22, 2006 Share Posted May 22, 2006 then use the original query and append the language details it s very close to what i was trying to do , exept i make a new paragraph separated by the horizontal line for each language , while i would like to have for the same product the 3 names and the 3 description like this : name fr- product1_french_name name en- product1_english_name name de- product1_german_name details fr- product1_french_details details en- product1_english_details details de product1_german_details ------------------------------------------------------------------- name fr- product2_french_name name en- product2_english_name name de- product2_german_name details fr- product2_french_details details en- product2_english_details details de product2_german_details --------------------------------------------------------- I know you could tell me " go learn more mysl then come back" and you would be right ;-) im abusing your kindness , but it don't know if it s a solution that could be done by the sql query , or it s more something the contrib doesnt do because the report is not made this way Any solution near to this one would be so welcome :blush: Quote MS2 Link to comment Share on other sites More sharing options...
Guest Posted May 22, 2006 Share Posted May 22, 2006 that would require a different layout on the pdf level. Or a template system to drive the pdf report. For instance the way the layout of the sql query is much closer to what you want than the pdf report. Of course in your case you could customize the pdf_mysql_report.php class file to change the layout. Perhaps you could also use a single column and concatanate everything there. Like for example if I wanted to have the products name and description on the same column I could do select concat(products_name, ' - ', products_description, ' - - - ', products_viewed) as everything from products_description where language_id = 1 and you could append more fields and add separators in the query to generate the effect you want Quote Link to comment Share on other sites More sharing options...
lazylex Posted May 22, 2006 Share Posted May 22, 2006 Ok I know where the error is happening from your report but I don't know why is happening. Can you tell me a couple of things: 1. The sql query you inserted in the edit box to generate the PDF; or the table name you selected to generate the PDF. It happens with every query also the ones from the readme file. 2. What happens when you select a table like countries and then you click the pdf button on the right side. Do you still see the same error? When I create a PDF from the tables .... it works fine. 3. Also try this simple query in the edit box and generate a PDF to see if the error is still there. Tried it... same error... I can give you access to the admin section if necessary... Alex Quote Link to comment Share on other sites More sharing options...
Guest Posted May 22, 2006 Share Posted May 22, 2006 It happens with every query also the ones from the readme file. have you implement the changes in the database.php file as of the later documentation of v1.01? Because it seems the numfields is causing the problem and that is set from the database wrapper function I included in the file. If you have deployed those changes do this as a test. open your catalog\admin\includes\classes\pdf_mysql_report.php locate this code: $this->numFields = tep_db_num_fields($this->results); add below: if($this->numFields <= 0 ) die('Invalid Fields Number returned'); and see if you get this error printed. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.