Legacy SQL to REST API Endpoint Mapping¶

This document provides a complete mapping of legacy SQL statements from sql_statements.sql to their equivalent REST API endpoints.

Base URL: http://localhost:5000/api/v1

Table of Contents¶

System Configuration
Statistics - Messages
Statistics - Measurements
Statistics - Analytics
Products Management
Product Parameters - TH (Tray Handling)
Product Parameters - SC (Steep Conveyor)
Product Parameters - EGP (Gripper)
Product Parameters - RFL (Refill Logic)
Product Parameters - XGX (Vision)
Product Parameters - IV3 (Vision)
Product Parameters - Kann (Stepper Motors)
Product Parameters - HP (Vibration Hopper)
Product Parameters - ES (Essert Shaker)
Product Parameters - CMMT (Servo Motors)
Product Parameters - CL (Circulation Logic)
Product Parameters - Unspecified
Order/Serial Management
Robot Positions

System Configuration¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
1	`SELECT * FROM db_Essert.tbl_ARWID`	`/api/v1/system/arwid`	GET	Get ARWID system configuration

Statistics - Messages¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
3	`DELETE FROM db_Statistics.tbl_CurrentMessages`	`/api/v1/statistics/messages/current`	DELETE	Clear all current messages
5	`INSERT INTO db_Statistics.tbl_MessageArchive (...)`	`/api/v1/statistics/messages/archive`	POST	Archive a message with timestamp and rising edge
7	`INSERT INTO db_Statistics.tbl_CurrentMessages (...)`	`/api/v1/statistics/messages/current`	POST	Add current message
9	`DELETE FROM db_Statistics.tbl_CurrentMessages WHERE MessageID = ?`	`/api/v1/statistics/messages/current/{messageId}`	DELETE	Remove specific current message

Example Request (Add Current Message):

POST /api/v1/statistics/messages/current
{
  "messageId": 123,
  "timestamp": "2024-01-15T10:30:00Z"
}

Example Request (Archive Message):

POST /api/v1/statistics/messages/archive
{
  "messageId": 123,
  "timestamp": "2024-01-15T10:30:00Z",
  "risingEdge": true
}

Statistics - Measurements¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
11	`INSERT INTO db_Statistics.tbl_Valve (...)`	`/api/v1/statistics/measurements/valve`	POST	Record valve measurement
13	`INSERT INTO db_statistics.tbl_egp (...)`	`/api/v1/statistics/measurements/egp`	POST	Record EGP gripper measurement
15	`INSERT INTO db_Statistics.tbl_kann (...)`	`/api/v1/statistics/measurements/kann`	POST	Record Kann stepper motor measurement
21-23	`INSERT INTO db_Statistics.tbl_ListSerialnumbers (...) ON DUPLICATE KEY UPDATE`	`/api/v1/orderserial/serial-numbers`	POST	Create or update serial number
22-23	`INSERT INTO db_Statistics.tbl_ListOrdernumbers (...) ON DUPLICATE KEY UPDATE`	`/api/v1/orderserial/order-numbers`	POST	Create or update order number
27	`INSERT INTO db_Statistics.tbl_Length (...)`	`/api/v1/statistics/measurements/length`	POST	Record length measurement

Example Request (Valve Measurement):

POST /api/v1/statistics/measurements/valve
{
  "mapping": 1,
  "versionUid": 100,
  "orderNumber": "ORD-001",
  "serialNumber": "SN-001",
  "duration": 125.5,
  "isOk": true,
  "direction": "forward",
  "timestamp": "2024-01-15T10:30:00Z"
}

Example Request (Length Measurement):

POST /api/v1/statistics/measurements/length
{
  "processId": 10,
  "versionUid": 100,
  "orderNumber": "ORD-001",
  "serialNumber": "SN-001",
  "datasetUid": 5,
  "value": 12.345,
  "timestamp": "2024-01-15T10:30:00Z"
}

Statistics - Analytics¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
29-34	`SELECT COUNT(UID) ... FROM tbl_CycleTime WHERE OrderUID = ? AND ProcessID = ?`	`/api/v1/statistics/analytics/process/{orderUid}/{processId}`	GET	Get process statistics by order and process
36-41	`SELECT COUNT(UID) ... WHERE VersionUID = ? AND ProcessID = ? AND TIMESTAMP BETWEEN ? AND ?`	`/api/v1/statistics/analytics/process/version/{versionUid}/{processId}?startDate={date}&endDate={date}`	GET	Get process statistics by version and date range
43-47	`SELECT CycleTime FROM tbl_CycleTime ... ORDER BY UID DESC LIMIT 10`	`/api/v1/statistics/analytics/cycle-times/recent/{versionUid}/{processId}?count=10`	GET	Get recent cycle times
49-61	`SELECT COALESCE(MAX(T.CycleTime), ...) AS Maximum, ... AS Average`	`/api/v1/statistics/analytics/cycle-times/statistics/{versionUid}/{processId}`	GET	Get cycle time statistics (max and average)

Example Response (Process Statistics):

GET /api/v1/statistics/analytics/process/123/10
{
  "okCount": 150,
  "nokCount": 5
}

Example Response (Cycle Time Statistics):

GET /api/v1/statistics/analytics/cycle-times/statistics/100/10
{
  "maximum": 155.75,
  "average": 142.33
}

Products Management¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
63-72	`SELECT ... FROM tbl_Versions, tbl_ProductNames WHERE ... LIKE ? UNION ...`	`/api/v1/products/search?searchTerm={term}`	GET	Search products and versions
103-106	`SELECT V., PN. FROM tbl_Versions AS V, tbl_ProductNames AS PN WHERE V.UID = ?`	`/api/v1/products/{uid}`	GET	Get product by UID
108-114	`INSERT INTO tbl_ProductNames (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/products`	POST	Create new product
116-122	`INSERT INTO tbl_Versions (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/products/{uid}/versions`	POST	Create new version for product
82-86	`UPDATE tbl_Versions SET VersionName = ?, Timestamp = ?, Creator = ? WHERE UID = ?`	`/api/v1/products/{productUid}/versions/{versionUid}`	PUT	Update version information
88-91	`UPDATE tbl_Productnames SET ProductName = ?, Timestamp = ? WHERE UID = ?`	`/api/v1/products/{uid}`	PUT	Update product name
124-135	`UPDATE tbl_ProductNames, tbl_Versions SET ... WHERE tbl_Versions.UID = ?`	`/api/v1/products/{uid}`	PUT	Update product with all details
94-101	`UPDATE tbl_Versions SET CRC = (SELECT CRC32(...)) WHERE UID = ?`	Automatic	-	CRC calculated automatically by API
74-80	`INSERT INTO DB_Changelogs.tbl_PP_Versions (...) SELECT ... FROM tbl_Versions WHERE UID = ?`	Automatic	-	Changelog tracking handled automatically
145-155	`INSERT INTO tbl_Versions ... FROM (SELECT ProductNamesUID FROM ...)`	`/api/v1/products/with-parameters`	POST	Create product with initial parameters
161-164	`SELECT * FROM tbl_Versions WHERE ProductNamesUID = ... AND FactorySetup = 1`	`/api/v1/products/{uid}/versions?factorySetup=true`	GET	Get factory setup versions
170-178	`UPDATE tbl_Versions SET FactorySetup = 0, Timestamp = ? WHERE UID = ?`	`/api/v1/products/{productUid}/versions/{versionUid}/factory-setup`	PATCH	Set factory setup flag
180-197	`UPDATE tbl_Versions SET VersionName = ?, Favorite = ?, ... WHERE UID = ?`	`/api/v1/products/{productUid}/versions/{versionUid}`	PUT	Update complete version information
213-218	`SELECT COUNT(ProductNamesUID) ... WHERE ProductNamesUID = (...)`	Internal	-	Version count validation (internal)
220-221	`DELETE FROM tbl_Versions WHERE UID = ?`	`/api/v1/products/{productUid}/versions/{versionUid}`	DELETE	Delete single version
223-233	`DELETE FROM tbl_Versions WHERE tbl_Versions.UID IN (...); DELETE FROM tbl_Productnames WHERE UID = ?`	`/api/v1/products/{uid}`	DELETE	Delete product with all versions
235-237	`SELECT COUNT(UID) AS Count FROM tbl_Versions WHERE VersionName = ? AND ProductNamesUID = ?`	Internal	-	Duplicate version name check (internal)
239-241	`SELECT COUNT(UID) AS Count FROM tbl_ProductNames WHERE ProductName = ?`	Internal	-	Duplicate product name check (internal)

Example Request (Create Product):

POST /api/v1/products
{
  "productName": "Test Product",
  "displayName": "Test Display Name",
  "articleNumber": "ART-001",
  "timestamp": "2024-01-15T10:30:00Z",
  "picture": "base64encodedimage..."
}

Example Request (Create Version):

POST /api/v1/products/123/versions
{
  "versionName": "v1.0",
  "favorite": false,
  "released": true,
  "factorySetup": false,
  "timestamp": "2024-01-15T10:30:00Z",
  "creator": "John Doe",
  "comment1": "Initial version",
  "comment2": "",
  "comment3": "",
  "comment4": "",
  "comment5": ""
}

Example Request (Search Products):

GET /api/v1/products/search?searchTerm=Test

Product Parameters - TH (Tray Handling)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
243-246	`SELECT T1.* FROM tbl_TH_Shape AS T1, tbl_TH_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T2.Mapping = ?`	`/api/v1/parameters/th/setup/{versionUid}/{mapping}`	GET	Get TH shape setup
248-251	`SELECT T1.* FROM tbl_TH_Process AS T1, tbl_TH_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T2.Mapping = ?`	`/api/v1/parameters/th/setup/{versionUid}/{mapping}`	GET	Get TH process setup
253-255	`SELECT * FROM tbl_TH_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/th/mapping/{versionUid}/{mapping}`	GET	Get TH mapping
257-260	`SELECT T1.* FROM tbl_TH_PositionData AS T1, tbl_TH_Mapping_PositionData AS T2, tbl_TH_Mapping AS T3 WHERE ...`	`/api/v1/parameters/th/position-data/{mappingUid}`	GET	Get TH position data
262-265	`SELECT T1.* FROM tbl_TH_ShiftData AS T1, tbl_TH_Mapping_ShiftData AS T2, tbl_TH_Mapping AS T3 WHERE ...`	`/api/v1/parameters/th/shift-data/{mappingUid}`	GET	Get TH shift data
269-275	`INSERT INTO tbl_TH_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/th/mapping`	POST	Create TH mapping
277-283	`INSERT INTO tbl_TH_Mapping_PositionData (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/th/position-mapping`	POST	Create TH position mapping
291-296	`INSERT INTO tbl_TH_PositionData (MappingUID, Position, Locked, Rx, Ry, Rz, Timestamp, Creator) VALUES ...`	`/api/v1/parameters/th/position-data`	POST	Create TH position data
298-304	`INSERT INTO tbl_TH_Mapping_ShiftData (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/th/shift-mapping`	POST	Create TH shift mapping
310-315	`INSERT INTO tbl_TH_ShiftData (MappingUID, Distance, CRNumber, Timestamp, Creator) VALUES ...`	`/api/v1/parameters/th/shift-data`	POST	Create TH shift data
322-329	`UPDATE tbl_TH_Mapping SET ShapeUID = ?, ProcessUID = ?, Mapping = ?, Timestamp = ? WHERE ...`	`/api/v1/parameters/th/mapping/{uid}`	PUT	Update TH mapping
365-372	`UPDATE tbl_TH_PositionData SET Locked = ?, Rx = ?, Ry = ?, Rz = ?, Timestamp = ?, Creator = ? WHERE ...`	`/api/v1/parameters/th/position-data/{uid}`	PUT	Update TH position data
406-413	`UPDATE tbl_TH_ShiftData SET Distance = ?, Timestamp = ?, Creator = ? WHERE MappingUID = ? AND CRNumber = ?`	`/api/v1/parameters/th/shift-data/{uid}`	PUT	Update TH shift data
432-451	`DELETE FROM tbl_TH_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_TH_PositionData WHERE ...; DELETE FROM tbl_TH_Mapping_PositionData WHERE ...; DELETE FROM tbl_TH_ShiftData WHERE ...; DELETE FROM tbl_TH_Mapping_ShiftData WHERE ...`	`/api/v1/parameters/th/mappings/version/{versionUid}`	DELETE	Delete all TH mappings for version

Example Request (Create TH Mapping):

POST /api/v1/parameters/th/mapping
{
  "versionUid": 100,
  "mapping": "1",
  "setupUid": 50,
  "shapeUid": 10
}

Example Request (Create TH Position Data):

POST /api/v1/parameters/th/position-data
{
  "mappingUid": 200,
  "position": 5,
  "locked": false,
  "rx": 10.5,
  "ry": 20.3,
  "rz": 30.1,
  "timestamp": "2024-01-15T10:30:00Z",
  "creator": "John Doe"
}

Product Parameters - SC (Steep Conveyor)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
453-454	`SELECT T1.* FROM tbl_SC_Setup AS T1, tbl_SC_Mapping AS T2 WHERE T1.UID = T2.SetupUID AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/sc/setup/{versionUid}/{mapping}`	GET	Get SC setup
456	`SELECT * FROM tbl_SC_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/sc/mapping/{versionUid}/{mapping}`	GET	Get SC mapping
460-467	`INSERT INTO tbl_SC_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/sc/mapping`	POST	Create SC mapping
474-480	`UPDATE tbl_SC_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/sc/mapping/{uid}`	PUT	Update SC mapping
482-486	`DELETE FROM tbl_SC_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_SC_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/sc/mappings/version/{versionUid}`	DELETE	Delete SC mappings for version or product

Example Request (Create SC Mapping):

POST /api/v1/parameters/sc/mapping
{
  "versionUid": 100,
  "mapping": "1",
  "setupUid": 50,
  "timestamp": "2024-01-15T10:30:00Z"
}

Product Parameters - EGP (Gripper)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
488-492	`SELECT T1.* FROM tbl_EGP_Setup AS T1, tbl_EGP_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/egp/setup/{versionUid}/{mapping}`	GET	Get EGP gripper setup
494-497	`SELECT * FROM tbl_EGP_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/egp/mapping/{versionUid}/{mapping}`	GET	Get EGP mapping
502-515	`INSERT INTO tbl_EGP_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/egp/mapping`	POST	Create EGP mapping
523-536	`UPDATE tbl_EGP_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/egp/mapping/{uid}`	PUT	Update EGP mapping
538-550	`DELETE FROM tbl_EGP_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_EGP_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/egp/mappings/version/{versionUid}`	DELETE	Delete EGP mappings

Example Request (Create EGP Mapping):

POST /api/v1/parameters/egp/mapping
{
  "versionUid": 100,
  "mapping": "1",
  "setupUid": 50,
  "timestamp": "2024-01-15T10:30:00Z"
}

Product Parameters - RFL (Refill Logic)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
552-556	`SELECT T1.* FROM tbl_RFL_Setup AS T1, tbl_RFL_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/rfl/setup/{versionUid}/{mapping}`	GET	Get RFL setup
558-561	`SELECT * FROM tbl_RFL_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/rfl/mapping/{versionUid}/{mapping}`	GET	Get RFL mapping
566-579	`INSERT INTO tbl_RFL_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/rfl/mapping`	POST	Create RFL mapping
587-600	`UPDATE tbl_RFL_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/rfl/mapping/{uid}`	PUT	Update RFL mapping
602-614	`DELETE FROM tbl_RFL_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_RFL_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/rfl/mappings/version/{versionUid}`	DELETE	Delete RFL mappings

Product Parameters - XGX (Vision)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
616-620	`SELECT T1.* FROM tbl_XGX_Setup AS T1, tbl_XGX_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/xgx/setup/{versionUid}/{mapping}`	GET	Get XGX vision setup
622-625	`SELECT * FROM tbl_XGX_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/xgx/mapping/{versionUid}/{mapping}`	GET	Get XGX mapping
630-643	`INSERT INTO tbl_XGX_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/xgx/mapping`	POST	Create XGX mapping
651-664	`UPDATE tbl_XGX_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/xgx/mapping/{uid}`	PUT	Update XGX mapping
666-678	`DELETE FROM tbl_XGX_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_XGX_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/xgx/mappings/version/{versionUid}`	DELETE	Delete XGX mappings

Product Parameters - IV3 (Vision)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
680-684	`SELECT T1.* FROM tbl_IV3_Setup AS T1, tbl_IV3_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/iv3/setup/{versionUid}/{mapping}`	GET	Get IV3 vision setup
686-689	`SELECT * FROM tbl_IV3_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/iv3/mapping/{versionUid}/{mapping}`	GET	Get IV3 mapping
694-707	`INSERT INTO tbl_IV3_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/iv3/mapping`	POST	Create IV3 mapping
715-728	`UPDATE tbl_IV3_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/iv3/mapping/{uid}`	PUT	Update IV3 mapping
730-742	`DELETE FROM tbl_IV3_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_IV3_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/iv3/mappings/version/{versionUid}`	DELETE	Delete IV3 mappings

Product Parameters - Kann (Stepper Motors)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
744-748	`SELECT MV.* FROM tbl_Kann_Mapping AS MP, tbl_Kann_Movements AS MV WHERE ... AND MP.VersionsUID = ? AND MP.Mapping = ?`	`/api/v1/parameters/kann/movements/{versionUid}/{mapping}`	GET	Get Kann movements
750-754	`SELECT S.* FROM tbl_Kann_Setup AS S, tbl_Kann_Mapping AS MP WHERE ... AND MP.VersionsUID = ? AND MP.Mapping = ?`	`/api/v1/parameters/kann/setup/{versionUid}/{mapping}`	GET	Get Kann setup
756-759	`SELECT * FROM tbl_Kann_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/kann/mapping/{versionUid}/{mapping}`	GET	Get Kann mapping
764-777	`INSERT INTO tbl_Kann_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/kann/mapping`	POST	Create Kann mapping
785-798	`UPDATE tbl_Kann_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/kann/mapping/{uid}`	PUT	Update Kann mapping
800-812	`DELETE FROM tbl_Kann_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_Kann_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/kann/mappings/version/{versionUid}`	DELETE	Delete Kann mappings

Product Parameters - HP (Vibration Hopper)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
814-818	`SELECT T1.* FROM tbl_HP_Setup AS T1, tbl_HP_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/hp/setup/{versionUid}/{mapping}`	GET	Get HP vibration hopper setup
820-823	`SELECT * FROM tbl_HP_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/hp/mapping/{versionUid}/{mapping}`	GET	Get HP mapping
828-841	`INSERT INTO tbl_HP_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/hp/mapping`	POST	Create HP mapping
849-862	`UPDATE tbl_HP_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/hp/mapping/{uid}`	PUT	Update HP mapping
864-876	`DELETE FROM tbl_HP_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_HP_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/hp/mappings/version/{versionUid}`	DELETE	Delete HP mappings

Product Parameters - ES (Essert Shaker)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
878-883	`SELECT * FROM tbl_ES_Clips WHERE UID = 1; INSERT INTO tbl_ES_Clips VALUES (1, ...)`	`/api/v1/parameters/es/clips/{uid}`	GET	Get ES clips
885-925	`SELECT CL.*, Seq.Clip_1_Duration, Seq.Clip_1_Delay FROM tbl_ES_Clips AS CL, tbl_ES_Sequences AS Seq, tbl_ES_Mapping AS MP WHERE ... [repeated for Clip_2 through Clip_6]`	`/api/v1/parameters/es/clips/{versionUid}/{mapping}`	GET	Get ES clips for sequence
927-931	`SELECT T1.* FROM tbl_ES_Sequences AS T1, tbl_ES_Mapping AS T2 WHERE ... AND T2.VersionsUID = ? AND T1.Mapping = ?`	`/api/v1/parameters/es/sequence/{versionUid}/{mapping}`	GET	Get ES sequence
933-936	`SELECT * FROM tbl_ES_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/es/mapping/{versionUid}/{mapping}`	GET	Get ES mapping
941-954	`INSERT INTO tbl_ES_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/es/mapping`	POST	Create ES mapping
962-975	`UPDATE tbl_ES_Mapping SET SequencesUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/es/mapping/{uid}`	PUT	Update ES mapping
977-989	`DELETE FROM tbl_ES_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_ES_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/es/mappings/version/{versionUid}`	DELETE	Delete ES mappings

Product Parameters - CMMT (Servo Motors)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
992-996	`SELECT MV.* FROM tbl_CMMT_Mapping AS MP, tbl_CMMT_Movements AS MV WHERE ... AND MP.VersionsUID = ? AND MP.Mapping = ?`	`/api/v1/parameters/cmmt/movements/{versionUid}/{mapping}`	GET	Get CMMT servo movements
999-1003	`SELECT S.* FROM tbl_CMMT_Setup AS S, tbl_CMMT_Mapping AS MP WHERE ... AND MP.VersionsUID = ? AND MP.Mapping = ?`	`/api/v1/parameters/cmmt/setup/{versionUid}/{mapping}`	GET	Get CMMT setup
1006-1009	`SELECT * FROM tbl_CMMT_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/cmmt/mapping/{versionUid}/{mapping}`	GET	Get CMMT mapping
1016-1030	`INSERT INTO tbl_CMMT_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/cmmt/mapping`	POST	Create CMMT mapping
1040-1054	`UPDATE tbl_CMMT_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/cmmt/mapping/{uid}`	PUT	Update CMMT mapping
1057-1070	`DELETE FROM tbl_CMMT_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_CMMT_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/cmmt/mappings/version/{versionUid}`	DELETE	Delete CMMT mappings

Product Parameters - CL (Circulation Logic)¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
1073-1077	`SELECT S.* FROM tbl_CL_Setup AS S, tbl_CL_Mapping AS M WHERE ... AND M.VersionsUID = ? AND S.Mapping = ?`	`/api/v1/parameters/cl/setup/{versionUid}/{mapping}`	GET	Get CL circulation logic setup
1080-1083	`SELECT * FROM tbl_CL_Mapping WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/cl/mapping/{versionUid}/{mapping}`	GET	Get CL mapping
1090-1104	`INSERT INTO tbl_CL_Mapping (...) VALUES (...); UPDATE ... SET CRC = ...`	`/api/v1/parameters/cl/mapping`	POST	Create CL mapping
1114-1128	`UPDATE tbl_CL_Mapping SET SetupUID = ?, Mapping = ?, Timestamp = ? WHERE VersionsUID = ? AND Mapping = ?`	`/api/v1/parameters/cl/mapping/{uid}`	PUT	Update CL mapping
1131-1144	`DELETE FROM tbl_CL_Mapping WHERE VersionsUID = ?; DELETE FROM tbl_CL_Mapping WHERE VersionsUID IN (...)`	`/api/v1/parameters/cl/mappings/version/{versionUid}`	DELETE	Delete CL mappings

Product Parameters - Unspecified¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
1152-1157	`SELECT VersionsUID, <Param_Columns>, Timestamp, CRC FROM tbl_Unspecified WHERE VersionsUID = ?`	`/api/v1/parameters/unspecified/{versionUid}`	GET	Get unspecified parameters for version
1163-1165	`INSERT INTO tbl_Unspecified (VersionsUID, <Param_Columns>, Timestamp, CRC) VALUES (?, ...)`	`/api/v1/parameters/unspecified`	POST	Create unspecified parameters
1175-1178	`UPDATE tbl_Unspecified SET <Param_1> = ?, <Param_2> = ?, ... Timestamp = ?, CRC = ? WHERE VersionsUID = ?`	`/api/v1/parameters/unspecified/{versionUid}`	PUT	Update unspecified parameters
1181-1194	`DELETE FROM tbl_Unspecified WHERE VersionsUID = ?; DELETE FROM tbl_Unspecified WHERE VersionsUID IN (...)`	`/api/v1/parameters/unspecified/version/{versionUid}`	DELETE	Delete unspecified parameters

Note: Unspecified parameters are dynamically defined per project. The actual parameter columns are determined at runtime.

Example Request (Create Unspecified Parameters):

POST /api/v1/parameters/unspecified
{
  "versionUid": 100,
  "parameters": {
    "parameter_1": "value1",
    "parameter_2": 42,
    "parameter_3": true
  },
  "timestamp": "2024-01-15T10:30:00Z"
}

Order/Serial Management¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
1147-1149	`SELECT * FROM tbl_listordernumbers WHERE UID > 0 ORDER BY Ordernumber ASC`	`/api/v1/orderserial/order-numbers`	GET	Get all order numbers
21-23	`INSERT INTO db_Statistics.tbl_ListSerialnumbers (...) ON DUPLICATE KEY UPDATE UID = UID`	`/api/v1/orderserial/serial-numbers`	POST	Create or get serial number
22-23	`INSERT INTO db_Statistics.tbl_ListOrdernumbers (...) ON DUPLICATE KEY UPDATE UID = UID`	`/api/v1/orderserial/order-numbers`	POST	Create or get order number

Example Request (Get All Order Numbers):

GET /api/v1/orderserial/order-numbers

Example Response:

[
  {
    "uid": 1,
    "orderNumber": "ORD-001",
    "timestamp": "2024-01-15T10:30:00Z"
  },
  {
    "uid": 2,
    "orderNumber": "ORD-002",
    "timestamp": "2024-01-15T11:00:00Z"
  }
]

Robot Positions¶

SQL Line	SQL Statement	API Endpoint	HTTP Method	Description
17	`CALL DB_Robots.RobotMasterTeach(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`	`/api/v1/robots/master-teach`	POST	Execute robot master teach procedure
19	`SELECT CAST(T1.CoordinateX + T2.MasterOffsetX AS FLOAT) AS X, ... FROM DB_Robots.tbl_MasterData AS T1, DB_Robots.tbl_PositionData AS T2 WHERE ...`	`/api/v1/robots/position/{positionNumber}?versionUid={uid}&type={type}&mapping={mapping}`	GET	Get robot position with master offsets

Example Request (Get Robot Position):

GET /api/v1/robots/position/5?versionUid=100&type=1&mapping=1

Example Response:

{
  "x": 100.5,
  "y": 200.3,
  "z": 150.7,
  "rx": 10.0,
  "ry": 20.0,
  "rz": 30.0,
  "turnId": 1,
  "flip": false,
  "elbowUp": true,
  "elbowFront": false,
  "uFrame": 1,
  "tFrame": 1,
  "toolOffsetX": 5.0,
  "toolOffsetY": 5.0,
  "toolOffsetZ": 5.0,
  "toolOffsetRx": 0.0,
  "toolOffsetRy": 0.0,
  "toolOffsetRz": 0.0
}

Summary Statistics¶

Category	SQL Lines	Number of Endpoints
System Configuration	1	1
Statistics - Messages	3-9	4
Statistics - Measurements	11-27	6
Statistics - Analytics	29-61	4
Products Management	63-241	12
TH Parameters	243-451	15
SC Parameters	453-486	5
EGP Parameters	488-550	5
RFL Parameters	552-614	5
XGX Parameters	616-678	5
IV3 Parameters	680-742	5
Kann Parameters	744-812	5
HP Parameters	814-876	5
ES Parameters	878-989	6
CMMT Parameters	991-1070	5
CL Parameters	1072-1144	5
Unspecified Parameters	1151-1194	4
Order/Serial Management	1146-1149	3
Robot Positions	17-19	2
Total	1194 lines	~102 endpoints

Key Benefits of REST API over Direct SQL¶

Security: No direct database access, SQL injection protection
Validation: Input validation and business rule enforcement
Automatic CRC Calculation: No need to manually calculate CRC values
Automatic Changelog: Changes automatically logged to changelog tables
Error Handling: Consistent error responses with proper HTTP status codes
Documentation: Self-documenting with OpenAPI/Swagger
Versioning: API versioning support (v1, v2, etc.)
Authentication/Authorization: Can add JWT, OAuth, etc.
Transaction Management: Automatic transaction handling for complex operations
Business Logic Encapsulation: Complex operations abstracted behind simple endpoints

Migration Notes¶

Automatic Features¶

The REST API automatically handles these operations that were manual in SQL: - CRC Calculation: All CRC values calculated automatically using CRC32 - Changelog Tracking: Previous values automatically archived to changelog tables - UID Generation: AUTO_INCREMENT UIDs generated by database - Timestamp Management: Current timestamps set automatically where appropriate - Order/Serial Number Lookups: ON DUPLICATE KEY UPDATE pattern handled internally

Breaking Changes¶

Stored Procedures: SQL stored procedure RobotMasterTeach is now a REST endpoint
Batch Inserts: Multi-row inserts should be done via multiple API calls or batch endpoint
Complex Queries: Some complex SELECT queries with multiple JOINs are simplified into single endpoint calls
Direct Table Access: No direct access to table structures; must use DTOs

Performance Considerations¶

Network Overhead: REST API adds network latency compared to direct SQL
Batch Operations: For bulk operations, consider using batch endpoints
Caching: API implements caching for frequently accessed data
Connection Pooling: API uses connection pooling for optimal database performance

Additional Resources¶

API Documentation: http://localhost:5000/swagger
API Health Check: GET http://localhost:5000/health
Architecture Documentation: See CLAUDE.md for hexagonal architecture details
Test Examples: See Essert.MF.API.Rest.Tests for integration test examples

Document Version: 1.0 Last Updated: 2025-01-13 Generated from: sql_statements.sql (1194 lines)