Universal Forms

[Jim Scott]

I'm building applications to make use of data from an application we use to track reservations at our campgrounds.  The application has a feature to print reports to a printer using universal forms.  I've been mining the reservation database by modifying these forms and redirecting the output to a disk file instead of a printer.

Is there a way to launch these Universal Forms queries using EB's database commands.  Or some other way from within an application?

[Jim Scott]

Maybe "Universal Forms" is not so universal.  The code box below shows the contents of one of these files.

Code Select Expand

UF2.1
3
Rental Arrival Report - CSV
Export
ABCDEF

Parameters
{
Date_StartDate
{
Type=HostApplication
Description="Checking In On Or After"
}
Date_CloseDate
{
Type=HostApplication
Description="Checking In On Or Before"
}
SelectedRobotID
{
Type=HostApplication
Description="Business ID Number (0 for all)"
value=23}
}
Databases
    {
    RezPack
        {
        Type=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC","ODBC","EXT_SPP2")
        Server=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC","DSN"),Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\SPP","Server"))
        Port=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC","",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\SPP","Port"))
        FilePath=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="JET",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\ROS", "DefaultPath") & "\\" & Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\ROS", "DefaultFile"), "")       
        Connect=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC", "DSN=" & Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC","DSN") & ";Database=" & Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC", "Database") & ";UID=" & Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC","UID") & ";PWD=" & Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC","PWD"), "")
        DSN=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC","",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\SPP", "DSN"))
        UID=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC", "UID"),Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\SPP", "UID"))
        PASSWORD=IIf(Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports","ConnectionType")="ODBC",Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\ODBC", "PWD"),Registry("HKEY_LOCAL_MACHINE\\Software\\Digital Rez\\RezExpertCentral\\Reports\\SPP", "PWD"))
        }
    }
Queries
{
PreserveQuery
{
Type=General
Source="select robot_name from robot_desc where robot_id = " & Parameters.SelectedRobotID
Database=RezPack
}
RezQuery
{
Type=General
Source="SELECT /* NumberSelect('SELECT Sum(Rez_Details.rval) FROM rez_details, occupants WHERE Rez_Details.ival = Occupants.Occupant_ID AND Rez_Details.Rez_ID = __PLACEHOLDER__ AND Occupants.Person <> 0', Rez_Desc.Rez_ID) As NumberOfPeople, */ Alert_Desc.Description as AlertDesc, Rez_Desc.Rez_ID, Robot_Desc.Robot_Name,  Rez_desc.Client_name, Client_desc.username, Client_Desc.Last_Name, Client_Desc.First_Name, Rez_Desc.Made_Date, Rez_Desc.Open_Date,  Rez_desc.Close_Date,  Type_Desc.Label as TypeLabel, Unit_Desc.Label as UnitLabel, Rez_Desc.Rez_Number, Alert_Desc.Label as AlertName, Discounts.Label as DiscName, CASE WHEN clienttype.clienttypeid IN (5,6,7,11) THEN ttreztomarketing.marketingcode ELSE Rate_Groups.label END AS ratename, clienttype.clienttypeid, clienttype.clienttypelabel, (NumberSelect('SELECT NVL(SUM(Transactions.Trans_Total),0) FROM Transactions WHERE Transactions.Rez_ID = __PLACEHOLDER__',Rez_Desc.Rez_ID)+ NumberSelect('SELECT NVL(SUM(Tentative_Trans.Trans_Total),0) FROM Tentative_Trans WHERE Tentative_Trans.Rez_ID = __PLACEHOLDER__',Rez_Desc.Rez_ID)) as Balance, ttreztomarketing.marketingcode, client_desc.phone FROM Rez_Desc, unit_desc, type_desc, robot_desc, client_desc, discounts, alert_desc, transactions, rate_groups, clienttype, ttreztomarketing WHERE Rez_Desc.Unit_ID = Unit_Desc.Unit_ID AND Unit_Desc.Type_ID = Type_Desc.Type_ID AND Rez_Desc.Robot_ID = Robot_Desc.Robot_ID AND Rez_Desc.Client_ID = Client_Desc.Client_ID(+) AND Rez_Desc.Discount_ID = Discounts.DIscount_ID(+) AND Rez_Desc.Alert_ID = Alert_Desc.Alert_ID(+) AND Rez_Desc.Rez_ID = Transactions.Rez_ID(+) AND Rez_Desc.Rate_group_id = Rate_groups.Rate_group_id(+) AND client_Desc.client_type = clienttype.clienttypeid and rez_desc.rez_id=ttreztomarketing.rez_id(+) and (robot_desc.Robot_ID="& Parameters.SelectedRobotID &" OR "& Parameters.SelectedRobotID &" = 0) AND (client_desc.client_type="& Cint(-1) &" OR "& Cint(-1) &" = -1) AND Rez_Desc.Cancelled = 0 and Rez_Desc.Rez_Type IN (0,2) AND Rez_Desc.Open_Date >= to_date('" & format(Parameters.Date_StartDate, "d-mmm-yyyy") & "','dd.mm.yyyy') and Rez_desc.Open_Date < to_date('" & format(Parameters.Date_CloseDate+1, "d-mmm-yyyy") & "','dd.mm.yyyy')  GROUP BY Rez_Desc.Rez_ID, Robot_Desc.Robot_Name,  clienttype.clienttypeid, clienttypelabel, Rez_desc.Client_name, Client_desc.username, Client_Desc.Last_Name, CLient_Desc.First_Name, Rez_Desc.Made_Date, Rez_Desc.Open_Date,  Rez_desc.Close_Date,  Type_Desc.Label, Unit_Desc.Label, Type_Desc.Checkin_Time, Rez_Desc.Rez_Number, Alert_Desc.Description, Alert_Desc.Label, Discounts.Label, Robot_desc.Robot_id, Rate_Groups.label, ttreztomarketing.marketingcode, client_desc.phone ORDER BY  Robot_Desc.Robot_Name, clienttype.clienttypeid, clienttypelabel, Rez_desc.Open_Date, Client_Desc.Last_Name, Client_desc.username, Client_Desc.First_name"
Database=RezPack
}
UnitNameQuery
{
Type=General
Source="SELECT tval FROM Preferences WHERE lower(Preferences.Label) = 'unit_name' AND Preferences.Robot_ID= " & Parameters.SelectedRobotID
Database=RezPack
}
CompanyInfoQuery
{
Type=General
Source="SELECT Location_desc.Legal_Name from LOcation_desc WHERE Location_Type = 3 AND Master <> NULL AND Robot_ID = "& Parameters.SelectedRobotID &" and robot_id = 0 Union SELECT Location_desc.Legal_Name from LOcation_desc WHERE Master = -1 AND Robot_ID = "& Parameters.SelectedRobotID &" and Robot_ID <> 0"
Database=RezPack
}
CompanyInfoQuery2
{
Type=General
Source="SELECT Location_desc.Legal_Name from Location_desc WHERE Master = -1 AND Robot_ID = 1"
Database=RezPack
}
}
Type=Export
OutputFile="C:\\Documents and Settings\\ranger\\My Documents\\RentalArrival.CSV"
FileType=""

Page
{
RezTable
{
Type=TableElement
Query=RezQuery
Top=Page.Title3.Bottom+12
Left=Page.Margin.Left
Right=Page.Margin.Right
Page=Page.Title3.EndPage
NumColumns=13
ColumnWidth1=3*
ColumnWidth2=3*
ColumnWidth3=3*
ColumnWidth4=3*
ColumnWidth5=4*
ColumnWidth6=2*
ColumnWidth7=3*
ColumnWidth8=3*
ColumnWidth9=3*
ColumnWidth10=3*
ColumnWidth11=3*
ColumnWidth12=3*
ColumnWidth13=3*
Header
{
Row1
{
Font{Name="Times New Roman";Height=11;Bold=True}
Contents1="Client Type" & ","
Contents2="Last Name" & ","
Contents3="First Name" & ","
Contents4="Member #" & ","
Contents5="Phone #" & ","
Contents6=IIF(ISBLANK(UnitNameQuery[tval] ),"Unit ",UnitNameQuery[tval])  & " #" & ","
Contents7="Type" & ","
Contents8="Arrive" & ","
Contents9="Depart" & ","
Contents10="Rez #" & ","
Contents11="Alert" & ","
Contents12="Rate Grp/RU" & ","
Contents13="Balance" & "\n"
Alignment=lllllllcccccr
}
}
Detail
{
Row1
{
Font{Name="Times New Roman";Height=10}
Contents1=[clienttypelabel] & ","
Contents2=ucase([Last_Name]) & ","
Contents3=ucase([First_Name]) & ","
contents4=[username] & ","
Contents5=format([phone],"(&&&)&&&-&&&&") & ","
Contents6=[UnitLabel] & ","
Contents7=[TypeLabel] & ","
Contents8=format([Open_Date],"d-mmm-yyyy") & ","
Contents9=format([Close_Date]+1,"d-mmm-yyyy") & ","
Contents10=[Rez_Number] & ","
Contents11=[AlertName] & ","
Contents12=[RateName] & ","
Contents13=ccur([Balance]) & "\n"
Alignment=lccclllcccccr
}

}
}
}


[Ionic Wind Support Team]

Unfortunately I have never heard of universal forms, but if you direct us to a site with a description we may be able to help.

[Jim Scott]

Yeah, Sorry Paul, my second post has an example of what I'm talking about.  I realized after the first post that maybe you could use more information.

[Ionic Wind Support Team]

Again I really don't know the usage of universal forms.  In other words what reads the file you posted, is it a database application?

Vikki would probably know as she is more into DB programming than I.

[Jim Scott]

Sorry, I thought maybe the commands in the file might be obvious to someone with more experience than I.  I was hoping you would say, oh yeah, just send this file to data base command xyz, with SQL command modifiers nnn and a way you go.

Yes, it's a reservation program based on ORACLE.  Here's a link to the company that makes the program.

www.digitalrez.com

Hopfully Vikki can help.  Maybe I'll try and contact Digitalrez and see if they can be any help with this.

[Rod]

Jim, based on what I've seen so far, filtered through prior experience, I think the short answer to your question is "no".

It appears that what DigitalRez is calling "universal forms" is a proprietary part of the reservation software package, a very flexible script-based report generator. That could be their own production, or a 3rd-party reporting package which they purchased and grafted into their package.

Either way, without the software to interpret and execute the forms code, there's not much EB can do with it.

However, if you know the database schema and have an ODBC driver for the Oracle database, EB can access the database directly, and do any reporting you want, or any updates.

Hope that helps.

[Jim Scott]

You're right Rod.  It is a proprietary form engine, out of Barbados, West Indies no less.

http://www.uforms.com/contactus.asp

No answer to my Email yet. 

Can't go directly at the data base because of our network security requirements.  I was hoping to get at the data in a non-intrusive way, allowing Uforms and DigitalRez to handle security etc.  I can still get to the data, just have to use a couple of extra steps using Digital Rez.

Thanks,

Jim

[Vikki]

I agree with Rod. If you had direct access to the database you could make your own forms for printing. Now that I read the code more, you could take the csv file and use it to create your own forms, however, I think your original question was about launching the forms with queries from with EB and I'm not sure how you could do that unless you could send parameters through the command line to launch it. I'm out of my depth on that but may some command line gurus could help out here.

On the other hand, they do sell the source for embedding/integrating into your own applications it looks like. It is written in c++ so I'm not sure if it could be converted to EB but I bet it could be converted to Aurora. Maybe even a dll created to make it available to EB users? (I don't know their distribution policies etc). I'm out on a limb here...  just throwing out suggestions.

For the Universal Forms web site: "Licensing the Universal Forms source code enables the limitless capabilities of embedding and extending it to meet your complex and unique challenges."

I'll keep reading....