Welcome, Guest. Please Login.
03/28/24 at 07:58:32
News:
Home Help Search Login


Pages: 1 
Send Topic Print
DBA Report-to-Web (Read 11738 times)
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
DBA Report-to-Web
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  Wink 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  Grin
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Lynn_Pantic
Administrator
*****


evolution (n) -
gradual change to a
different form

Posts: 5681
Re: DBA Report-to-Web
Reply #1 - 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).
Back to top
 
 

Lynn Pantic
IS Tech Support
lynn@istechsupport.com
Email   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #2 - 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.
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Danimal
Member
**




Posts: 59
Gender: male
Re: DBA Report-to-Web
Reply #3 - 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.  Wink
 
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.
Back to top
 
 
Email   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #4 - 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...
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Danimal
Member
**




Posts: 59
Gender: male
Re: DBA Report-to-Web
Reply #5 - 03/07/05 at 15:04:22
 
Quote from 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?

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)
Back to top
 
 
Email   IP Logged
Danimal
Member
**




Posts: 59
Gender: male
DBA Demo Online
Reply #6 - 11/21/05 at 07:00:54
 
Back to top
 
 
Email   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: DBA Report-to-Web
Reply #7 - 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.
 
 
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
JaySchmitendorf
Browser
*


EVO-ERP, Electronic
Assembly Contract
Manufacturer

Posts: 31
Gender: male
Re: DBA Report-to-Web
Reply #8 - 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.
Back to top
 
 
WWW   IP Logged
rlincolna
Browser
*


hello?

Posts: 1
Gender: male
Re: DBA Report-to-Web
Reply #9 - 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
Back to top
 
 
Email WWW   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #10 - 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?
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
shadowcaster
Active Member
*****


I'm not here because
I know what to do.

Posts: 678
Gender: male
Re: DBA Report-to-Web
Reply #11 - 05/04/06 at 18:46:08
 
I would Buy that capability
 
JW
Back to top
 
 
WWW   IP Logged
fast
Member
**




Posts: 61
Gender: male
Re: DBA Report-to-Web
Reply #12 - 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.
Back to top
 
 



  IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #13 - 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/
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
fast
Member
**




Posts: 61
Gender: male
Re: DBA Report-to-Web
Reply #14 - 08/28/07 at 07:51:32
 
Firefox can't find the server at www.evoerpwiki.com
Back to top
 
 



  IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #15 - 08/28/07 at 07:59:01
 
The site has been having DNS problems since yesterday. Hopefully it will be fixed soon.
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
kkmfg
Senior Member
****


Ghost of the code

Posts: 411
Gender: male
Re: DBA Report-to-Web
Reply #16 - 08/28/07 at 10:37:12
 
Quote from GasGiant on 08/28/07 at 07:59:01:
The site has been having DNS problems since yesterday. Hopefully it will be fixed soon.

 
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.
Back to top
 
 

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10
Email WWW   IP Logged
GasGiant
Administrator
*****


How can I help?

Posts: 1523
Gender: male
Re: DBA Report-to-Web
Reply #17 - 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.
Back to top
 
 


Email WWW GasGiant GasGiant 31012781 swordworlder swordworlder   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: DBA Report-to-Web
Reply #18 - 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
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: DBA Report-to-Web
Reply #19 - 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
 
 
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
kkmfg
Senior Member
****


Ghost of the code

Posts: 411
Gender: male
Re: DBA Report-to-Web
Reply #20 - 11/26/07 at 07:45:11
 
Quote from Kelloggs on 11/23/07 at 11:49:07:


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.


 
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.
Back to top
 
 

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10
Email WWW   IP Logged
Kelloggs
Active Member
*****


Do crazy people know
they are crazy?

Posts: 785
Gender: male
Re: DBA Report-to-Web
Reply #21 - 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
Back to top
 
 

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)
Email WWW   IP Logged
kkmfg
Senior Member
****


Ghost of the code

Posts: 411
Gender: male
Re: DBA Report-to-Web
Reply #22 - 11/26/07 at 09:54:31
 
Quote from 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

 
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.
Back to top
 
 

Collin
K & K Manufacturing, Inc

EvoERP Version 1-22-10 SP3
5 User Workgroup Pervasive 10
Email WWW   IP Logged
Pages: 1 
Send Topic Print