SolverTech.Tasha.Plugins.IO.Database.config

Plugin that provides data extraction from the database.

Example of the default file:

<?xml version="1.0" encoding="utf-8"?> <DatabaseIOSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <ConnectionString></ConnectionString> 
 <CommandText>SELECT * FROM Table WHERE datum = 'XXXdateXXX'</CommandText>  
 <DbProvider>oracle mssql dbf odbc mysql</DbProvider>  

</DatabaseIOSettings>

Example file for single-track import from IS Pohoda:

<?xml version="1.0" encoding="utf-8"?> <DatabaseIOSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

 <ConnectionString>Server=192.168.10.101;Database=StwPh_29454174_2019;User Id=tasha;Password=TasHa2019</ConnectionString>
 <CommandText><![CDATA[

SELECT objednavky.RefADdod AS id ,objednavky.Cislo AS CisloObjednavky ,objednavky.firma AS firma ,objednavky.Firma2 AS name ,objednavky.Ulice2 AS ulice ,objednavky.PSC2 AS psc ,objednavky.Obec2 AS mesto ,0 AS x ,0 AS y ,10 AS duration ,600 AS start1 ,1600 AS end1 ,2 AS precedence ,objednavky.Cislo AS CisloObjednavky ,objednavky.Datum AS DatumDodani ,KcCelkem ,CASE WHEN objednavky.Email IS NULL THEN objednavky.Email2 ELSE objednavky.Email END AS email ,objednavky.Utvar2 AS utvar ,objednavky.Tel AS tel ,objednavky.tel2 AS tel2 ,objednavky.Pozn AS pozn ,objednavky.Pozn2 AS pozn2 ,[StwPh_62742710_2019].[dbo].OBJpol.Mnozstvi AS demand1 ,[StwPh_62742710_2019].[dbo].OBJpol.MJ AS jednotka ,[StwPh_62742710_2019].[dbo].OBJpol.MJKoef AS koeficient ,CONCAT([StwPh_62742710_2019].[dbo].OBJpol.SText,' - ',[StwPh_62742710_2019].[dbo].OBJpol.Pozn) AS info1 FROM [StwPh_62742710_2019].[dbo].[OBJ] AS objednavky LEFT JOIN [StwPh_62742710_2019].[dbo].OBJpol ON objednavky.ID=[StwPh_62742710_2019].[dbo].OBJpol.RefAg WHERE objednavky.Vyrizeno=0 AND objednavky.Datum=>'XXXdateXXX' ]]>

 </CommandText>
 <DbProvider>mssql</DbProvider>

</DatabaseIOSettings>

Structure of IS Pohoda:

The structure of the Pohoda IS is somewhat variable (depending on both the Pohoda settings and the specific users), but some items are fixed. For each client, the database used varies, but typically the database has a name like "StwPh_29454174_2019".

Important tables and fields in tables (beware, there are users who enter a phone number in the "Company" field):

• dbo.OBJ - table with orders
  • dbo.OBJ.ID - order identifier, used to connect to dbo.OBJPol
  • dbo.OBJ.Cislo - Order number
  • dbo.OBJ.RefAD (sometimes dbo.OBJ.RefADdod) - subscriber number - generally ideal id for Tasha, can also be used to connect to dbo.AD
  • dbo.OBJ.Firma - name of the customer, generally the ideal name for Tasha (can also be Firma2)
  • dbo.OBJ.Ulice - street and customer's descriptive number (can also be Street2)
  • dbo.OBJ.Obec - city of the customer
  • dbo.OBJ.PSC - customer's postcode
  • dbo.OBJ.KcCelkem - Total price of the order
• dbo.OBJpol - table with order items
  • dbo.OBJpol.RefAg - field with order number, used to connect to dbo.OBJ
  • dbo.OBJpol.Mnozstvi - Quantity of goods, ideal as demand1
  • dbo.OBJpol.MJ - unit of goods, useful in conversions e.g. pcs per kg.
  • dbo.OBJpol.MJKoef - Coefficient for quantity. It is used to calculate the real quantity of goods
  • dbo.OBJpol.SText - name of goods
  • dbo.OBJpol.Kc - price for ordered quantity without VAT
  • dbo.OBJpol.KcDPH - price for ordered goods with VAT
• dbo.AD - folder table
  • dbo.AD.ID - person identifier, used to connect to dbo.OBJ