The fundamental principle behind the upload utility is to load data using REST (Http 80) commands up to the allocated SWOT server. The data is protected by means of a token that connects the data to a specific customer dataset. The token is set at installation of the service and users do not have access to the token that controls the data.

The utility is designed to be a series of steps

Here are the main command TYPEs:

REST

Summary Sets up the current comnnection string to the data that is to be retrieved in susequent CUST, PROD, TRAN CONT and PALT records

Command Unused
Parameter1 Used only in the case of multiple datsets. If the REST call is used a second instance, the New dataset instance Token is entered into Parameter 1. Added 24/Feb/24.
Parameter2 The type of .Net connection command to use to access the local transaction data. Options are:
- ODBC
  - Type: ODBC
  - Parameter 3 ODBC Connection string
  - Parameter 4
- ADO
  - Type: OLE/DB connection
  - Parameter 3 OLE/DB connection string
  - Parameter 4
- SQL
  - Type: SQL Server connection
  - Parameter 3 SQL Server connection string
  - Parameter 4
- CSV
  - Type:CSV text data file. CharacterSet=65001
  - Parameter 3 The full path including filename of the file(s) to be uploaded. NOTE: This command does not upload the file
  - Parameter 4 YES or NO. Whether the first record in the CSV file contains the Column Headers

Parameter 3 in perhaps the most important – the connection string to the local data. The connection string method offers a wide range of possibilities.

More information about the scope and syntax of connection strings can be found online e.g. https://www.connectionstrings.com/net-framework-data-provider-for-odbc/

Here are some examples of connection strings :

ODBC:
1. Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=\\svrb01\ sales_data\;Exclusive=No;Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;
2. Driver={Microsoft Access Driver (*.mdb)};DBQ=yourdatabasename.mdb;
SQL (Server)
1. Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;
ADO
1. Provider=DB2OLEDB;Network Transport Library=TCPIP; Network Address=192.168.0.12;Initial Catalog=DbAdventures;Package Collection=SamplePackage;Default Schema=SampleSchema;
2. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=yourdatabasename.mdb;

CUST

Summary Details for loading Customer data. The customer class is defines as follows:

[cid] [nvarchar](36) NOT NULL,
[cname] [nvarchar](60) NOT NULL,
[ccat1] [nvarchar](22) NULL,
[ccat2] [nvarchar](22) NULL,
[ccat3] [nvarchar](22) NULL,
[ccat4] [nvarchar](22) NULL,
[ccat5] [nvarchar](22) NULL,
[nvalue1] [numeric](12, 3) NULL,
[nvalue2] [numeric](12, 3) NULL,
[nvalue3] [numeric](12, 3) NULL,
[nvalue4] [numeric](12, 3) NULL,
[nvalue5] [numeric](12, 3) NULL,
[ccontact] [nvarchar](100) NULL,
[cemail] [nvarchar](100) NULL,
[cphone1] [nvarchar](20) NULL,
[cphone2] [nvarchar](20) NULL,
[ExternalRef1] [nvarchar](36) NULL,
[ExternalRef2] [nvarchar](36) NULL

Command The SQL Command
Parameter1 Unused
Parameter2

PROD

Summary Details for loading Product data. The product class is defined as follows:

[cid] [nvarchar](36) NOT NULL,
[cname] [char](60) NOT NULL,
[pcat1] [char](22) NULL,
[pcat2] [char](22) NULL,
[pcat3] [char](22) NULL,
[pcat4] [char](22) NULL,
[pcat5] [char](22) NULL,
[nunitprice] [numeric](12, 3) NULL,
[nunitcost] [numeric](12, 3) NULL,
[nvalue1] [numeric](12, 3) NULL,
[nvalue2] [numeric](12, 3) NULL,
[nvalue3] [numeric](12, 3) NULL,
[nvalue4] [numeric](12, 3) NULL,
[nvalue5] [numeric](12, 3) NULL,
[cmanager] [char](22) NULL,
[ExternalRef1] [nvarchar](36) NULL,
[ExternalRef2] [nvarchar](36) NULL

Command The SQL Command
Parameter1 Unused
Parameter2

TRAN

Summary Details for loading Transaction data. The Transaction class is defined as follows:

[cprodid] [nvarchar](36) NOT NULL,
[ccustid] [nvarchar](36) NOT NULL,
[cperiodid] [char](4) NULL,
[csmanid] [nvarchar](36) NULL,
[cperiod2id] [char](4) NULL,
[nunits] [decimal](18, 3) NOT NULL,
[nvalue] [decimal](18, 3) NOT NULL,
[ncost] [decimal](18, 3) NOT NULL,
[dtrandate] [date] NOT NULL,
[dtrantime] [datetime] NULL,
[ltag] [int] NULL,
[ExternalRef1] [nvarchar](36) NULL,
[ExternalRef2] [nvarchar](36) NULL

Command The SQL Command
Parameter 1 Unused
Parameter 2 The Date field from the Transaction SQL

Parameter 3 The type of date

                    case "NODATE":
                        ConvertDate = "";
                        break;
                    case "ACCPAC":
                        ConvertDate = GDMaxDate.ToString("yyyyMMdd");
                        break;
                    case "DATETIME":
                        ConvertDate = " Convert(Date,\'" + GDMaxDate.ToString("yyyy-MM-dd") + "\')";
                        break;
                    case "SQLDATE":
                        ConvertDate = GDMaxDate.ToString("yyyy-MM-dd");
                        break;
                    case "TIMESTAMP":
                        ConvertDate = "{ts \'" + GDMaxDate.ToString("yyyy-MM-dd HH:mm:ss") + "\'}";
                        break;

                    default:
                        ConvertDate = "{d \'" + GDMaxDate.ToString("yyyy-MM-dd") + "\'}";
                        break;

Parameter 4 Delete Dates option.

when @Option between 1 and 7 then  Dateadd(d,-@Option,getdate()) 
     {delete @Option number of days previous}
when @Option between 10 and 50 then  Dateadd(wk,-Floor(@Option/10),getdate()) 
     {Delete @Option number of weeks previous}

when @Option = 97 then from 1-15th of Month delete back to 1st, then after the 15th of the month delete 7 days

when @Option = 98 then from 1-15th of Month delete back to 1st, then after the 15th of the month delete 14 days

when  @Option =99 then Dateadd(d,-1,datefromparts(year(getdate()),month(getdate()),1))  
     {Delete from the first day on the month}
when  @Option =100 then Dateadd(d,-1,datefromparts(year(dateadd(m,-1,getdate())),month(dateadd(m,-1,getdate())),1))  
     {Delete 1 month}
when  @Option =200 then Dateadd(d,-1,datefromparts(year(dateadd(m,-2,getdate())),month(dateadd(m,-2,getdate())),1))  
     {Delete 2 months}
when  @Option =300 then Dateadd(d,-1,datefromparts(year(dateadd(m,-3,getdate())),month(dateadd(m,-3,getdate())),1)) else getdate() 
     {Delete 3 months}

CONT

Summary Details for loading Contact data. The Contact class is defined as follows:

[ContactID] [nvarchar](36) NULL,
[ContactGUID] [nvarchar](36) NULL,
[CustomerID] [nvarchar](36) NOT NULL,
[ContactType] [nvarchar](30) NULL,
[FirstName] [nvarchar](30) NULL,
[MiddleNames] [nvarchar](30) NULL,
[Surname] [nvarchar](30) NULL,
[FullName] [nvarchar](96) NULL,
[JobTitle] [nvarchar](36) NULL,
[Email1] [nvarchar](64) NULL,
[Email2] [nvarchar](64) NULL,
[Phone1] [nvarchar](24) NULL,
[Phone2] [nvarchar](24) NULL,
[Address1] [nvarchar](128) NULL,
[Address2] [nvarchar](128) NULL,
[Suburb] [nvarchar](64) NULL,
[City] [nvarchar](64) NULL,
[Postcode] [nvarchar](32) NULL,
[Country] [nvarchar](64) NULL

Command The SQL Command
Parameter1 Unused
Parameter2

SMAN

Summary Details for loading Sales Rep data. The Sales Rep class is defined as follows:

    [cid]          NVARCHAR (36) NOT NULL,
    [cname]        CHAR (60)     NOT NULL,
    [scat1]        CHAR (22)     NULL,
    [scat2]        CHAR (22)     NULL,
    [scat3]        CHAR (22)     NULL,
    [scat4]        CHAR (22)     NULL,
    [scat5]        CHAR (22)     NULL,
    [cmanager]     CHAR (50)     NULL,
    [irank]        INT           NULL,
    [ExternalRef1] NVARCHAR (36) NULL,
    [ExternalRef2] NVARCHAR (36) NULL,
    [lnc]          INT           NULL

Command The SQL Command
Parameter1 Unused
Parameter2

PALT

Summary Details for loading Alternate Product data. The Alternate product class is defined as follows:

  [Company][nvarchar] (50) NULL,
  [CProdid][nvarchar] (36) NULL,
  [Description][nvarchar] (100) NULL,
  [Lot_No][nvarchar] (50) NULL,
  [Location][nvarchar] (50) NULL,
  [Cost][decimal](18, 10) NULL,
  [Purchase_Date][datetime2] (7) NULL,
  [Expiration_Date][datetime2] (7) NULL,
  [Special_Date][datetime2] (7) NULL,
  [Product_Manager][nvarchar] (50) NULL,
  [PCat1][nvarchar] (50) NULL,
  [PCat2][nvarchar] (50) NULL,
  [PCat3][nvarchar] (50) NULL,
  [PCat4][nvarchar] (50) NULL,
  [PCat5][nvarchar] (50) NULL,
  [Total_Status][nvarchar] (50) NULL,
  [Total_Units][decimal](18, 10) NULL,
  [Total_Value][decimal](18, 10) NULL,
  [Special_Status][nvarchar] (50) NULL,
  [Special_Status_Units][decimal](18, 10) NULL,
  [Special_Status_Value][decimal](18, 10) NULL,
  [CSmanID][nvarchar] (36) NULL

Command The SQL Command
Parameter1 Unused
Parameter2

FTPP

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

FTPG

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

ZIPD

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

ZIPF

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

ZIPX

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

CMD

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

LDIR

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

LSDIR

Summary

Command Unused
Parameter1 Unused
Parameter2 Unused
Parameter3 Unused
Parameter4 Unused

Article ID: 43, Created: October 25, 2023 at 8:23 AM, Modified: September 2 at 12:10 AM

Parameters for loading data to SWOT using the SWOTService

Add Feedback

Was this article helpful?

Share this article