Parameters for loading data to SWOT using the SWOTService

Root Folder > Connections
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 :
  1. 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;
  2. SQL (Server)
    1. Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;
  3. 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()) 
when @Option between 10 and 50 then  Dateadd(wk,-Floor(@Option/10),getdate()) 
when  @Option =99 then Dateadd(d,-1,datefromparts(year(getdate()),month(getdate()),1)) 
when  @Option =100 then Dateadd(d,-1,datefromparts(year(dateadd(m,-1,getdate())),month(dateadd(m,-1,getdate())),1)) 
when  @Option =200 then Dateadd(d,-1,datefromparts(year(dateadd(m,-2,getdate())),month(dateadd(m,-2,getdate())),1)) 
when  @Option =300 then Dateadd(d,-1,datefromparts(year(dateadd(m,-3,getdate())),month(dateadd(m,-3,getdate())),1)) else getdate() end
       
 
 
 
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, , Modified: February 14 at 12:52 PM

Add Feedback

Was this article helpful?

Share this article

FacebookGoogle+TwitterOther Social NetworksPrint