{
"workorderno": "0",
"trackingno": null,
"customerid": "none",
"serviceid": "none",
"itemkey": null,
"servicewriter": null,
"preferredtech": null,
"description": "Status is null",
"transactiontype": null,
"jobno": null,
"departmentno": null,
"entereddate": "2024-04-11 00:00:00",
"customerpo": null,
"authorizationno": null,
"priority": null,
"status": "Active",
"salescode": null,
"priceplan": null,
"taxno": null,
"taxexempt": "0",
"taxset": null,
"itemlocation": null,
"companykey": "0",
"facilitykey": "0",
"quoteprice": null,
"allowsubstitutes": "0",
"estimate": "0",
"estcompletiondate": null,
"estimatethreshold": null,
"servicecallno": null,
"scheduleddate": null,
"scheduledperiods": "0",
"worklocation": null,
"pickuproute": null,
"mapcode": null,
"startdate": null,
"deliverydate": null,
"contractno": null,
"warranty": "0",
"servicecenter": "0",
"manufacturer": "0",
"partscovered": "0",
"laborcovered": "0",
"workcomments": null,
"invoicecomments": null,
"contactdate": null,
"contactnotes": null,
"contactperson": null,
"contactphone": null,
"trips": null,
"referral": null,
"advertisement": null,
"labortime": null,
"customerrequest": null,
"datecompleted": null,
"datepaid": null,
"invoiceuponcompletion": "0",
"balance": null,
"paid": "0",
"username": null,
"enditem": null,
"enditemqty": "1",
"buildtoinventory": null,
"accessories": null,
"usertext1": null,
"usertext2": null,
"usertext3": null,
"usertext4": null,
"usertext5": null,
"usertext6": null,
"usertext7": null,
"usertext8": null,
"usernumber1": null,
"usernumber2": null,
"labor": null,
"parts": null,
"sublabor": null,
"misc": null,
"tax": null
}❌ Workorder 0 contains important data - cannot safely modify
CREATE TABLE `workorders` ( `workorderno` int(11) NOT NULL, `trackingno` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `customerid` varchar(64) COLLATE utf8_unicode_ci NOT NULL, `serviceid` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `itemkey` int(11) DEFAULT NULL, `servicewriter` int(11) DEFAULT NULL, `preferredtech` int(11) DEFAULT NULL, `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `transactiontype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `jobno` int(11) DEFAULT NULL, `departmentno` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `entereddate` datetime DEFAULT NULL, `customerpo` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `authorizationno` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `priority` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `status` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `salescode` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `priceplan` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `taxno` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `taxexempt` bit(1) NOT NULL DEFAULT b'0', `taxset` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `itemlocation` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `companykey` int(11) NOT NULL DEFAULT '0', `facilitykey` int(11) NOT NULL DEFAULT '0', `quoteprice` decimal(13,4) DEFAULT NULL, `allowsubstitutes` bit(1) NOT NULL DEFAULT b'0', `estimate` bit(1) NOT NULL DEFAULT b'0', `estcompletiondate` datetime DEFAULT NULL, `estimatethreshold` decimal(13,4) DEFAULT NULL, `servicecallno` int(11) DEFAULT NULL, `scheduleddate` datetime DEFAULT NULL, `scheduledperiods` smallint(6) NOT NULL DEFAULT '0', `worklocation` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `pickuproute` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `mapcode` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `startdate` datetime DEFAULT NULL, `deliverydate` datetime DEFAULT NULL, `contractno` int(11) DEFAULT NULL, `warranty` bit(1) NOT NULL DEFAULT b'0', `servicecenter` bit(1) NOT NULL DEFAULT b'0', `manufacturer` bit(1) NOT NULL DEFAULT b'0', `partscovered` bit(1) NOT NULL DEFAULT b'0', `laborcovered` bit(1) NOT NULL DEFAULT b'0', `workcomments` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL, `invoicecomments` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL, `contactdate` datetime DEFAULT NULL, `contactnotes` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `contactperson` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `contactphone` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `trips` smallint(6) DEFAULT NULL, `referral` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `advertisement` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `labortime` smallint(6) DEFAULT NULL, `customerrequest` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL, `datecompleted` datetime DEFAULT NULL, `datepaid` datetime DEFAULT NULL, `invoiceuponcompletion` bit(1) NOT NULL DEFAULT b'0', `balance` decimal(13,4) DEFAULT NULL, `paid` bit(1) NOT NULL DEFAULT b'0', `username` smallint(6) DEFAULT NULL, `enditem` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `enditemqty` float NOT NULL DEFAULT '0', `buildtoinventory` bit(1) DEFAULT NULL, `accessories` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext1` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext2` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext3` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext4` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext5` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext6` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext7` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usertext8` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `usernumber1` float DEFAULT NULL, `usernumber2` float DEFAULT NULL, `labor` decimal(13,4) DEFAULT NULL, `parts` decimal(13,4) DEFAULT NULL, `sublabor` decimal(13,4) DEFAULT NULL, `misc` decimal(13,4) DEFAULT NULL, `tax` decimal(13,4) DEFAULT NULL, PRIMARY KEY (`workorderno`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
{
"Table": "workorders",
"Non_unique": "0",
"Key_name": "PRIMARY",
"Seq_in_index": "1",
"Column_name": "workorderno",
"Collation": "A",
"Cardinality": "2540",
"Sub_part": null,
"Packed": null,
"Null": "",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
}Testing with workorder ID: 15000
Using test ID: 15000
✅ Stored procedure test successful!
{
"workorderno": "14936",
"id": "test@system.com",
"jobno": "0",
"projectname": "",
"equipmenttype": "",
"fueltype": "",
"drivetype": "",
"measuretype": "",
"measdistance": "",
"measangle": "",
"measx": "",
"measy": "",
"liftweight": "",
"wallheight": "",
"ledge": "",
"terweight": "",
"openingheight": "",
"openingwidth": "",
"resultvalue": "14936",
"returncode": "17",
"returnmessage": "Done"
}❌ Stored procedure error: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
❌ Direct insertion also failed: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'employeekey' in 'field list'
✅ Good news! The stored procedure is working now. Try your chatbot again.
With the enhanced fallback system, your chatbot should work regardless of the stored procedure issues.
Test input: 'I need a telehandler to lift 1600 pounds, reach 30 feet out and 25 feet high for outdoor work'
Expected: The chatbot will try the iOS workflow first, then fall back to direct database insertion if needed.