Madics
D-Verto - part of the Madics DART Product
The
Madics D-Verto (Data-Exchange) Console gives you complete flexibility in
extracting your data from Madics (AMOS random data files) into SQL Server or MS
Access. It allows you to access nearly any Madics data file, all or only
selected fields. The data can also be manipulated by SQL commands after import.
This is based on scripts stored on your Madics server - for easy maintenance
and backup.
There
are several types of scripts depending on the action: Extract/Export Data,
Create Views/Queries, or execute SQL commands. These all can be scheduled for
background processing.
DVerto
SQL Scripts, File layouts, Schedules and Examples
The following files and scripts are stored and
maintained on the Server.
DVT/LAY Files – Madics File Export
SQL Files – SQL Execution
UPL Files – Remote table
upload/remote SQL execution.
SCH Files – Schedule (coutains
any/all of the above)
In the following example we import the Madics A(MOS
Random data) file INVMAS.DAT into a SQL or MS Access table called Stock_File.
The .DVT file contains the file location, record size and table names to create.
The .LAY file contains the AMOS file structure. You do
not need to include all fields which is useful if you want to exclude sensitive
or unimportant fields (costs or Production Factor for example).
The format is as follows: {Starting Byte Position},{Field name},{field
type},{field length},{division value}
The optional {division value} is only used for numeric
values, and the value is divided by this before writing to the database. For
example, a price field is not stored as 50.00 as you might expect but instead
as 50000. To get the correct value when exporting these fields you will need to
set {division value} to 1000. There are some COST examples below.
INVMAS.DVT file
[SETTINGS]
TABLENAME=Stock_File
DESCR=Stock
Header
MAPFILE=INVMAS.LAY
DATAFILE=\\100.100.100.206\inhouse\miame\dsk43\241017\invmas.dat
A direct pointer to the file - note backward slashes.
or
DATAFILE=/inhouse/miame/dsk43/241017/invmas.dat
The file is FTPed locally first - note forward slashes.
RECORDSIZE=512
RECNO
FIELDNAME=Madics_Recno This is the fieldname created containing the record number
from the data file
Audit Tables
As of version 4.2.0.94 any table updates by DVerto can also update
an Audit Table “DVertoAudit” by default, this can be enabled is DVerto’s general
settings, or per a data file as the following:
AUDIT=Y
or N
If Y then file auditing is enable for this file,
even if its disabled in DVerto General Settings.
If N file auditing
is always diabled for this file, and again over rides the DVerto General
Settings.
You can also update a different Audit table rather
than the default DVertoAudit table by setting the following:
AUDITFILE={Audit
table name}
File Hook/Trigger
As of version 4.2.0.85 you are able to disable any
DISABLE_TRIGGER=Y
To execute a SQL script after each trigger add a script file to a
TRIGGERQUERY setting.
TRIGGERQUERY={SCRIPT}.SQL
Header Record
HEADER
RECORD=NONE
This will cause the complete file to be read (as it does not know
how many records there are in the file)
You many want to use the FILTER in the .LAY file to filter out
unused/black/bracket records.
HEADER
RECORD=HISSAL.DAT
Only used if the header record and record count is shared and on a
different data file.
If
the AMOS random data files
header’s In Use count is not a floating point at byte 9 (used by
default) then you will also need to set this postion in the .DVT file (unless
there is no header record at all, see HEADER RECORD=NONE above)
The following will get the header file in use count from a string field at byte 3 for 10 characters.
[HEADER]
INUSE
TYPE=STRING
INUSE
BYTE POS=3
INUSE
BYTE LENGTH=10
The following is the same as the default and get the in use
count from a floating point at byte 9.
[HEADER]
INUSE
TYPE=FLOAT
INUSE
BYTE POS=9
INUSE
BYTE LENGTH=6
Appending New records only.
APPEND
MODE=Y
New records are appended to the table instead of the table being
dropped/deleted and re-created each time.
Only useful for static record files like Sales History or Archive
Orders etc.
If any of the following rules apply then
append mode will revert back to normal update mode, and drop the table and start
a fresh.
*
If the data file changes file size. (Eg get expanded/contracted)
*
If there was import errors.
*
The table did not exist in the database.
(was manually dropped/deleted)
*
If there was less records in the data file than there was in the the previous
update. (Signs of a purge/deletion)
Import/Inserting Text files into a memo field in the table.
MEMOLOCATION1=/inhouse/miame/dsk43/241017/*.tsp See the
explanation of ASCII (memo) files below.
MEMOLOCATION2=/inhouse/miame/dsk43/241017/*.psp
INVMAS.LAY file
1,GROUP,S,15
16,LOC,S,2
FILTER,LOC,=,{blank}
The FILTER option for the LOC field excludes location records. To
get the location records, use <> instead of =
Other FILTER options are = <> > <
18,PART_NO,S,15
33,DESCR,S,40
73,PRDCAT,S,2
75,ALTITEM,S,15
90,BOFLAG,S,1
91,IREF,S,10
101,VATCODE,S,2
103,STK_TYPE,S,1
104,STK_CTRL,S,1
105,STK_STAT,S,1
106,STK_GEN,S,1
107,STK_REVENUE_CODE,S,7
114,INVMAS_OSB_DATE,D,6 Date fields are
mapped as D (for Date) and not S (for String).
If the date field is stored as a binary 3 then user a lowercase ‘d’
120,STK_VIBE_STATS,S,6
126,STK_BULK_ISSUE,S,1
127,SUPCODEMAST,S,6
133,ANALYS1,S,6
139,ANALYS2,S,6
145,UNIT,S,6
151,STDCOST,F,6,COST’DIV Costs have 5
decimal places (in this example), but stored as full values so need to be
divided, COST’DIV is set in the decimal.cfg file (or in DVerto setting to
divide by 100000.
157,AVGCOST,F,6,100000
163,AVGCOSTINC,F,6,100000
169,PRICE1,F,6,100
175,PRICE2,F,6,100 Prices have 2
decimal places (in this example), also stored as full values so also need to be
divided.
181,PRICE3,F,6,100
187,PRICE4,F,6,100
193,COSTDATE,D,6
199,PRICDATE,D,6
205,STK_INSPECT_LEVEL,S,1
206,STK_INSPECT_REF,S,6
212,STK_ABC_CODE,S,1
213,STK_APPROVAL,B,1
214,STK_COST_AGELIM,B,2
216,STK_VARIABILITY_CAT,S,1
217,REOLEVMAST,F,6
223,QTYBOX,F,6
229,ORDLEAD,F,6
235,MANLEAD,F,6
241,IWEIGHT,F,6
247,STK_STD_MARGIN,F,6
253,STK_AVG_DEMAND,F,6
259,LOCTN_NUM,F,6
265,LATEST_COST,F,6
271,STK_INSURANCE,F,6
277,STK_CARR_CLR,F,6
283,ROUTE,S,15
298,SERIAL_CTRL,S,1
299,WARRANTY_DAYS,F,6
305,BATCH_CTRL,S,1
306,KEYWORD,S,15
321,PRICE_PER,B,3
324,BUYING_LOC,S,2
326,TECH_SPEC_NO,S,6 This
is the ASCII file unique Tech Spec file number
326,TECH_SPEC_MEMO,M,6,TSP Type M creates a MEMO field type, and imports
the
ASCII file of that name plus the extension (5th parameter), for
this
example a file called {TECH_SPEC_NO}.TSP from location
MEMOLOCATION1 defined in the
INVMAS.DVT file.
326,PURCH_SPEC_MEMO,M,6,PSP
332,STK_BAR_CODE,S,14
346,STK_UNIT_
352,NEW_ITEMNO2,S,15
367,STK_DRAW_REF,S,12
379,STK_ITEM_TYPE,S,3
382,STK_ARCDTE,S,6
388,STK_COST_CODE,S,3
391,STK_EXPENSE,S,7
398,STK_BITMAP,S,20
418,STK_DEL_DATE,B,3
421,STK_NOTC,S,3
424,INVMAS_SPARE,S,1
425,STK_COMMODITY_CODE,S,12
437,DISCOUNT_CODE,S,2
439,MINIMUM_GM_GP,F,6
445,NON_STK,S,1
446,MANF_REF,S,25
471,KIT_FLAG,S,1
472,STK_AUTO_ALLOC,S,1
473,STK_ALLOC_DAYS,B,2
475,PRODCTN_FACTOR,F,6
481,PRODCTN_REF,S,6
487,MACHINE_QTY,F,6
493,STK_XAREA,F,6
499,STK_LENGTH,S,6
505,STK_ROSCH_COMPLIANT,S,1
506,STK_ARCHIVED,S,1
507,STK_BUY_GROUP,S,2
509,BUY_BTB_ONLY,S,1
510,MAKE_TO_ORDER,S,1
511,STK_GRNLAB,S,1
512,STK_ON_BOMP,S,1
0,RUNNING_NUM_FIELD,A,500 A Running numeric
number field starting from 501 and incrementing.
0,STOCK_TAKE_QTY,F,6,500 If the
starting byte position starts with zero this
means no
Data
is taken from the data file being read/linked to but
just
create the field on the database. The 5th parameter is the
default
value, so for this example a field STOCK_TAKE_QTY is
created
in the table all records will have a value of 500.
326,TECH_SPEC_EXAMLE_NUM,N,6 The field type N will convert
numeric data stored in a string
into
a numeric/float field in the database.
In version 4.2.0.72 we introduce a
basic table lookup, this allows you to add fields from an existing database
table. For example you may want to include the Customer Name field on the
Sale_History_Table but the original source data file in
In the LAY File
create a field and set the start data position as 'zero' the field will be just created in the Table
with no information/data, but then add the following new parameters in columns
5,6,7,8,9.
Column 1 - 0 (Zero, we not taking any data from the
.DAT file)
Column 2 - Field Name
Column 3 - Data Type, (S or F)
Column 4 - Field length, (6 for floating points)
Column 5 - LOOKUP (This tells DVerto we will lookup
another table)
Column 6 - Table name (An existing Table on the SQL
Server database)
Column 7 - The Tables Field name we are
retrieving/looking up.
Column 8 - The data field name in the LAY file to use
to perform the lookup.
Column 9 - The field name in the SQL Server Table to
link by.
For example a
part of your LAY file may look this:
44,HIS_Cust_Code,S,6
0,HIS_Cust_Name,S,35,LOOKUP,Customer_Table,CUS_NAME,HIS_Cust_Code,CUS_CUSTNO
This will create
a field called HIS_Cust_Name and will use the HIS_Cust_Code value to lookup the
CUS_Name via the CUS_Custno field on Customer_Table.
DVerto allows SQL execution using script files. This
can be used after you have imported the data from your server to create
different Views or Queries.
The following example INTMTH.SQL uses the Madics Sales
Intake Log Table (once it has been imported by the method above) and summarises
the Nett Intake Values by month. Your notice there is two SQL commands in the
file, upto 50 SQL commands can be place in a file, each one must start with [SQL].
[SETTINGS]
TITLE="Intake this month"
[SQL]
DROP TABLE INT_MONTH
[SQL]
SELECT DISTINCTROW Sum(Sales_Intake.INT_NETVALUE) AS
Month_NetValue,
Month([INT_DATE]) AS ThisMonth,
(Format([Int_Date],"mmmm")) AS MonthTxt
INTO Int_Month
FROM Sales_Intake
GROUP BY Month([INT_DATE]),
(Format([Int_Date],"mmmm"))
ORDER BY Month([INT_DATE]) DESC;
This HISYTD.SQL script file creates a View or Query in
the SQL Server or in MS Access.
[SETTINGS]
TITLE=2007
Sales
DROPFIRST=2007_Sales
[SQL]
CREATE
VIEW 2007_Sales AS
SELECT
* FROM Sales_History WHERE HDATE >= #01/01/2007#
Files with extension .UPL are used for uploading
tables from the local database to a remote (maybe a web) database or executing
SQL commands directly on the Remote Database.
For Example the following will delete (drop) and
upload the whole local table Madics_Stock_File upto the remote database (see
DVerto Settings) as the table name of Web_Stock_File
UPLOAD.UPL
[SETTINGS]
TITLE=Upload Stock File
SRC TABLE=Madics_Stock_File
DST TABLE=Web_Stock_File
The following will execute
an SQL command to the remote database, in this example updating the table
TBLCactuShop1Config.
COMPLETE.UPL
[SETTINGS]
TITLE=Finish Upload/Online
[SQL]
UPDATE TBLCactuShop1Config SET CFG_Value='open' WHERE
CFG_Name='shopstatus'
You will probably want to run multiple updates and a
mix of SQL scripts all at once, or at selected times. To do this automatically
is where the Schedule scripts are used. In the following INTAKE.SCH script file
we automatically update the Madics Sales Intake data from the server every hour
between
[SCHEDULE]
TITLE=Update Intake, Stats and upload.
TIME=10:00,11:00,12:00,13:00,14:00,15:00
[ACTION]
INTAKE.DVT
INTMTH.SQL
UPLOAD.UPL