|
ISTech Support Forum
http://www.istechforum.com/YaBB.pl Evo-ERP and DBA Classic >> Source Code and Programming in TAS >> DBA Report-to-Web http://www.istechforum.com/YaBB.pl?num=1110217029 Message started by GasGiant on 03/07/05 at 09:37:09 |
|
Title: DBA Report-to-Web Post by GasGiant on 03/07/05 at 09:37:09 Looks like several DBA users are working on or are interested in having a Web interface for DBA data. I worked for a company back in 2000 that sold a $20k product and all it did was publish mainframe greenbar reports to a web server. That company, Report2Web, was purchased by Redwood Software, whose main product was on-demand reports to a web portal. I say all that to say this: this will be a valuable tool, but it ain't easy ;) Collaboration / information sharing would benefit everyone. First things first... What are the "Use Cases" for this "DBA2Web" project? What do you, as a user, want out of DBA that you would find handy to have directly published to web format? Who would the users be? What are you doing or have you done so far? NOTE: as I understand it, we are not talking about a two-way interface. This discussion is only about retrieving information from DBA, am I correct? I mean, if you wanted a web-based ERP program you'd just switch to Compiere or webERP and save yourself the hassles, right ;D |
|
Title: Re: DBA Report-to-Web Post by Lynn Pantic on 03/07/05 at 09:45:26 I have had a lot of users interested in the ability for customers to be able to query stock and order status (one way interface, password protected so each customer can see only his own orders) but also the ability for the customer to enter an order or submit RFQ (two way interface). |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 03/07/05 at 10:18:41 Customer transaction requests would be a sticky wicket. Allowing customers to post orders or RFQs to a dummy transaction register that can be run through a verification process before posting to the live system would be a good compromise. |
|
Title: Re: DBA Report-to-Web Post by Danimal on 03/07/05 at 14:02:17 True, it is a major undertaking to switch interfaces for your ERP system. I have made a few small programs that create an interface to DBA data via our internet, and although a few adjustments had to be made, the end result was more than satisfying. Not having access to compile my own applications to run in DBA prompted me to look into alternative ways of manipulating the database. I even started writing an interface application in Visual C++, soon abandoning the project because it was getting too involved. The web interface also seemed a better method from an interactivity and deployment standpoint. Reading or updating data via SQL, anything is possible. The short demonstration application that I have created, and am about to release, will allow you complete access to DBA over a corporate intranet. I am consciously avoiding the inclusion of SQL UPDATE and other database modification routines, but for even a novice SQL programmer, should not be very difficult to figure out. Albeit slightly complicated for someone without a programming background, unlike myself, it is fundamentally simple once you can grasp the concepts. ;) NOTE: For those in need of a quick solution for strictly reporting data to the web, I would recommend Crystal Reports' Web Report Server. Works in a pinch, and easily interfaces to a corporate intranet as well. |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 03/07/05 at 14:19:03 Sounds interesting. From your earlier comments, should I assume that it is ASP? Is it all VB? Is the presentation layer straight HTML? This could be fun... |
|
Title: Re: DBA Report-to-Web Post by Danimal on 03/07/05 at 15:04:22 GasGiant wrote:
To answer your questions: yes; yes; and yes, with the exception of some JavaScript and CSS to give the demo some flavor, isn't reliant on anything else. Would work really well with one of those JavaScript grid controls, but in an effort to keep things simple, decided not to incorporate it into the project... 8) |
|
Title: DBA Demo Online Post by Danimal on 11/21/05 at 07:00:54 www.istechsupport.com/dl/dba_demo.zip |
|
Title: Re: DBA Report-to-Web Post by Kelloggs on 11/21/05 at 08:54:00 We have several reports that can be view it via internet explorer, on our Intranet site. We can query any type of information. We use php and apache server. Regarding updating data, if you are running pervasive 7, 8 I woundnt recommended. As far as I know pervasive offers record locking only on Pervasive 9. Version 7, 8 have "Table Locking". Regards, Kelloggs FYI: Apache 2.1 and PHP 5 on windows do not work!!! use php 4, also php 5 does not incluede mysql functions by default. |
|
Title: Re: DBA Report-to-Web Post by JaySchmitendorf on 01/10/06 at 07:45:55 We use vbscript and the document object model for special reports. We create html files, but ASP would work great. |
|
Title: Re: DBA Report-to-Web Post by rlincolna on 02/14/06 at 12:54:31 I usually don't respond to things like this, however, in the area of reporting: We have an entire series of reports written with Microsoft Access that run every night through use of the Windows which are then printed with Adobe Acrobat to a folder on our internal "Intranet." These reports are then accessed by pressing a button on the web page which pulls up the pdf file. Our reports include: 1) An omnibus report which shows YTD and MTD shipping and order booking by $. Backlog by month including a graphical view, List of credit hold customers, listing of all order lines effected by credit hold. and a bookings by month graph. 2) A shipping report from the previous day by market, order line by order line which includes ship to and bill to information, email address of contacts, what was shipped, left on BO, tracking info,etc. 3) A listing of all open PO's with color coding to show if it is on time (green) or late (yellow, orange, or red for REAL LATE) 4) All kits due to be picked for the next week with abbreviated picklist information 5) A total schedule with WO's and SO's linked to show status - this includes a comment field for follow up 6) A complete listing of late orders with contact and order infromation 7) A report showing Sales Orders that are expedited for a premium, the premium, order amount, and due date. 8) Special reports on bottleneck area schedules including QC and Outside Plating. The method is Access Reports each driven by a Macro which runs by use of the Microsoft Scheduler, printed by Adobe Acrobat to a specific folder which is viewed using buttons on a web page. For those of us that VPN in we can get the report when it runs about 8:30 each night, however, most call it up in the morning. At one point in time we emailed the reports at night, but this consumed too much mail server space as people tended to not erase the reports. Also, the same scheduler runs a script which puts a subset of the reports into an archive which is also available on our intranet. This is entirely automatic and we have used these reports for the past 2 years. Lincoln |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 04/25/06 at 13:08:41 That's a pretty hefty list of reports. How long did it take for someone to write those for you? Is it something that you would be willing to share with others? |
|
Title: Re: DBA Report-to-Web Post by shadowcaster on 05/04/06 at 18:46:08 I would Buy that capability JW |
|
Title: Re: DBA Report-to-Web Post by fast on 08/22/07 at 08:56:59 Very interesting topic: To allow customers to log in and check the status of orders, tracking numbers, reprint invoices, check delivery time. |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 08/22/07 at 11:44:34 I have implemented this and shared some of the code over on the Wiki in the HOWTOs under PHP Hacks. http://www.evoerpwiki.com/ |
|
Title: Re: DBA Report-to-Web Post by fast on 08/28/07 at 07:51:32 Firefox can't find the server at www.evoerpwiki.com |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 08/28/07 at 07:59:01 The site has been having DNS problems since yesterday. Hopefully it will be fixed soon. |
|
Title: Re: DBA Report-to-Web Post by kkmfg on 08/28/07 at 10:37:12 GasGiant wrote:
Yeah the DNS host is still messed up but probably later today the wiki will move to it's rightful home at the same provider that is hosting the other ISTech sites. This should make it faster and have better uptime. |
|
Title: Re: DBA Report-to-Web Post by GasGiant on 10/19/07 at 06:21:57 I'm moving into new territory and thought that I would pick the ample brains on this board. I've been hosting my data mining web application on a little WinXP box on my desk. Soon the app will move to our new Intranet server. I have never set up an ODBC client on a Linux machine before and was wondering if others have and what pitfalls there might be. This will be my chance to get it right so that it will not be an unknown factor when it comes time to configure the new web server in January. |
|
Title: Re: DBA Report-to-Web Post by Kelloggs on 10/19/07 at 14:26:04 Our Intranet site runs on Ubuntu Server 7.10 I have documented the entire installation process. Currently our Intranet is use as: - Web Server - Apache/PHP5 - Pervasive client - Pervasive.SQL-Client-8.70 (Pervasive Server runs on Windows 2003) - Windows File Server - Samba Server/Client - Dataserver - Mysql Currently, I am working on script to email information from/and to DBA Users, Customer, Supplies. Sort of "Notification Feature". Regards, Kelloggs |
|
Title: Re: DBA Report-to-Web Post by Kelloggs on 11/23/07 at 11:49:07 Here is a very simple but functional way to send notices to users Server: Ubuntu Email Server: sendmail 1.- You need to install lynx sudo apt-get install lynx 2.- Create your query. I use mysql_connect because I transfer data from Pervasive to MySQL. But you can modify it to odbc_connect() This simple php script will do "something" everytime if find a record. <? //conneccion mysql_connect("localhost", "root", "") or die("Unable to connect to SQL server"); //sql script $sql="SELECT EID, SONUM, CUSNME, PCODE, PDESC FROM uni_ship_email WHERE ESTATUS='N'"; $result = mysql_db_query("umcnet", $sql) or die("Unable to select Database"); $row = mysql_num_rows($result); while ($row = mysql_fetch_array($result)) { $EID=$row["EID"]; $SONUM=$row["SONUM"]; $CUSNME=$row["CUSNME"]; $PCODE=$row["PCODE"]; //-------------------------EMAIL IT $to = "john_doe@home.com"; $from_header = "UNI Ship/Rec Server"; $subject = "UNI SHP/REC NEW ITEM RECVD. SO: ".$SONUM; $body = "A New Item has been received.\n\nSO Number: ".$SONUM."\n\nSKU: ".$PCODE; mail($to, $subject, $body, $from_header); //-------------------------UPDATE RECORD $sq = "UPDATE uni_ship_email SET ESTATUS='Y' WHERE EID=$EID"; mysql_db_query("umcnet", $sq) or die("Unable to select Database"); } mysql_close(); ?> 3.- Add a Cron Job open /var/spool/cron/crontabs/root add the following line 0,15,30,45 * * * * lynx -dump http://intranet/uni/SHIP/send_email.php where "http://intranet/uni/SHIP/send_email.php" is the path to your script on your website. Done!! It will send an email whenever finds a record Regards, Kelloggs |
|
Title: Re: DBA Report-to-Web Post by kkmfg on 11/26/07 at 07:45:11 Kelloggs wrote:
Umm... If you are running the server and/or able to install cron jobs then why not just: 0,15,30,45 * * * * php /var/www/uni/SHIP/send_email.php That cuts lynx right out of it. PHP works fine all by itself. |
|
Title: Re: DBA Report-to-Web Post by Kelloggs on 11/26/07 at 07:59:29 if php is setup as an apache module it will not work. It will work only if php is Compiled as CGI Regards, Kelloggs |
|
Title: Re: DBA Report-to-Web Post by kkmfg on 11/26/07 at 09:54:31 Kelloggs wrote:
I do it all of the time. PHP should have both it's command line version and it's apache module both installed. I use PHP as a module on our server but I sometimes run the PHP scripts locally with the command line PHP. It works just fine. |
|
ISTech Support Forum » Powered by YaBB 2.1! YaBB © 2000-2005. All Rights Reserved. |