--
-- Host: localhost    Database: PortALogic5
-- ------------------------------------------------------


--
-- Table structure for table `AccessCode`
--

CREATE TABLE `AccessCode` (
  `AccessCodeID` int NOT NULL,
  `fkCustomer` int NOT NULL,
  `AccessType` tinyint NOT NULL,
  `AccessNumber` varchar(100) NOT NULL,
  `Codes` varchar(100) NOT NULL,
  `ActiveInd` tinyint NOT NULL,
  `PIN` int NOT NULL,
  `LastUsed` datetime DEFAULT NULL,
  PRIMARY KEY (`AccessCodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `AccessCode`
--

INSERT INTO `AccessCode` VALUES (3,4,0,'2','2',1,2010,'2024-10-03 10:32:30'),(5,6,0,'3','3',1,4030,'2024-10-04 08:57:47'),(6,7,0,'4','4',1,4201,NULL),(7,1,0,'999999','999999',1,3006,'2024-10-04 14:40:23'),(8,8,0,'5','5',1,4197,NULL);

--
-- Table structure for table `AlarmHistory`
--

CREATE TABLE `AlarmHistory` (
  `pkAlarmHistory` int unsigned NOT NULL AUTO_INCREMENT,
  `CreateDate` datetime NOT NULL,
  `Level` int NOT NULL,
  `Description` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Solution` varchar(1000) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `incloud` int NOT NULL,
  UNIQUE KEY `pkAlarmHistory` (`pkAlarmHistory`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `AlarmHistory`
--

INSERT INTO `AlarmHistory` VALUES (2,'2024-09-26 11:29:34',2,'ePort error','Check log files for details.',1),(3,'2024-09-26 11:29:45',3,'Recovered from an ePort error','',1),(4,'2024-10-04 14:40:06',2,'ePort error','Check log files for details.',1),(5,'2024-10-04 14:40:16',3,'Recovered from an ePort error','',1);

--
-- Table structure for table `BlackList`
--

CREATE TABLE `BlackList` (
  `pkBlackList` int unsigned NOT NULL AUTO_INCREMENT,
  `CreateDate` datetime NOT NULL,
  `AccessCodeID` int NOT NULL,
  UNIQUE KEY `pkBlackList` (`pkBlackList`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `BlackList`
--


--
-- Table structure for table `CardNumbers`
--

CREATE TABLE `CardNumbers` (
  `TicketNumber` int NOT NULL,
  `passcode` int NOT NULL,
  `cardtype` int NOT NULL,
  `InitialValue` decimal(12,4) NOT NULL,
  `CreateDate` datetime NOT NULL,
  `incloud` tinyint DEFAULT NULL,
  PRIMARY KEY (`TicketNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `CardNumbers`
--


--
-- Table structure for table `Customer`
--

CREATE TABLE `Customer` (
  `pkCustomer` int unsigned NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Zip` varchar(50) NOT NULL,
  `PhoneNumber` varchar(30) NOT NULL,
  `PIN` int DEFAULT NULL,
  `FacilityCode` bigint NOT NULL,
  `userdef1` varchar(100) DEFAULT NULL,
  `userdef2` varchar(100) DEFAULT NULL,
  `DeleteInd` tinyint DEFAULT NULL,
  `DiscountRate` decimal(8,7) DEFAULT NULL,
  PRIMARY KEY (`pkCustomer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Customer`
--

INSERT INTO `Customer` VALUES (1,'Credit Card','','','','','',0,0,'','',0,0.0000000),(2,'Caldwell','','','','','',2998,0,'1234','',0,0.0000000),(3,'Bob Smith','','','','','',4493,0,'','',1,0.0000000),(4,'Public Works','','','','','',6235,0,'','',0,0.0000000),(5,'testing ','','','','','',5570,0,'779900701','',1,0.0000000),(6,'CALDWELL AG RESOURCES','304 W AVENUE G ','CALDWELL ','KS ','67022','6208452622',6003,0,'','',0,0.0000000),(7,'KUEHNY FARMS ','101039 LOGAN RD ','MEDFORD ','OK','73759','5808491112',4269,0,'','',0,0.0000000),(8,'Jimmy Brown','1541 S RYAN ROAD','CALDWELL','KS','67022','620-845-2268',6190,0,'','',0,0.0000000);

--
-- Table structure for table `Customer_ProductType`
--

CREATE TABLE `Customer_ProductType` (
  `Customer_ProductTypeID` int unsigned NOT NULL,
  `fkCustomer` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `DeleteInd` tinyint NOT NULL,
  PRIMARY KEY (`Customer_ProductTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Customer_ProductType`
--


--
-- Table structure for table `HouseAccessCode`
--

CREATE TABLE `HouseAccessCode` (
  `HouseAccessCodeID` int unsigned NOT NULL AUTO_INCREMENT,
  `TicketNumber` int NOT NULL,
  `AccessNumber` varchar(100) NOT NULL,
  `ActiveInd` tinyint NOT NULL,
  `PIN` int NOT NULL,
  PRIMARY KEY (`HouseAccessCodeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `HouseAccessCode`
--


--
-- Table structure for table `LocalPLC`
--

CREATE TABLE `LocalPLC` (
  `Version` varchar(50) NOT NULL,
  `ReaderIPAddress` varchar(255) NOT NULL,
  `PortNumber` int DEFAULT NULL,
  `Name` varchar(50) NOT NULL,
  `StationID` int DEFAULT NULL,
  `MaxPickups` int NOT NULL,
  `StationOpenClosed` int NOT NULL,
  `CloudMySQLIP` varchar(1000) NOT NULL,
  `DefaultState` varchar(100) NOT NULL,
  `BillType` int NOT NULL,
  `Cloudtype` tinyint NOT NULL,
  `StationType` tinyint NOT NULL,
  `DefaultProductType` int NOT NULL,
  `CloudSchema` varchar(255) NOT NULL,
  `ControllerIpAddress` varchar(255) NOT NULL,
  `InUse` tinyint NOT NULL,
  `KinveyEnvironment` tinyint NOT NULL,
  `TelerikCloudJobNumber` varchar(255) NOT NULL,
  `PumpNumber` int NOT NULL,
  `StationFault` int NOT NULL,
  `SamplerSample` int NOT NULL,
  `MaxFillTime` int NOT NULL,
  `TruckNumber` int NOT NULL,
  `RequestedVolume` int NOT NULL,
  `PortALogicFault` int NOT NULL,
  `CloudUserName` varchar(255) NOT NULL,
  `CloudPassword` varchar(255) NOT NULL,
  `BalanceThreshold` int NOT NULL,
  `HeartBeat` tinyint NOT NULL,
  `FVolumeLimitEnabled` tinyint NOT NULL,
  `FVolumeLimitQuantity` decimal(12,2) NOT NULL,
  `FVolumeLimitPeriod` tinyint NOT NULL,
  `FCustomerVolumeLimitPeriod` tinyint NOT NULL,
  `FCustomerVolumeLimitEnabled` tinyint NOT NULL,
  `FCustomerVolumeLimitQuantity` decimal(12,2) NOT NULL,
  `VolumeLimitEnabled` tinyint NOT NULL,
  `VolumeLimitQuantity` decimal(12,2) NOT NULL,
  `VolumeLimitPeriod` tinyint NOT NULL,
  `CustomerVolumeLimitPeriod` tinyint NOT NULL,
  `CustomerVolumeLimitEnabled` tinyint NOT NULL,
  `CustomerVolumeLimitQuantity` decimal(12,2) NOT NULL,
  `ePortTelemeter` varchar(255) NOT NULL,
  PRIMARY KEY (`Version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `LocalPLC`
--

INSERT INTO `LocalPLC` VALUES ('1.1.3','10.0.0.10',0,'',1,10,0,'10.2.0.1:XXXX','IL',0,-1,2,1,'blank','10.0.0.3',0,1,'blank',0,0,0,10,0,0,0,'','',0,0,0,0.00,0,0,0,0.00,0,1.00,1,1,0,1.00,'CHANGEME');

--
-- Table structure for table `LookUpInt`
--

CREATE TABLE `LookUpInt` (
  `ID` int NOT NULL,
  `Val` int NOT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `LookUpInt`
--

INSERT INTO `LookUpInt` VALUES (1,1000,'siteID'),(2,1,'unit'),(3,10,'screenInactivityLogOffTime'),(4,0,'initialSetup'),(5,57234,'adminCode'),(6,255,'displayBrightness'),(7,128,'displayContrast'),(8,0,'restrictedSubType'),(9,10,'maxLoginAttempts'),(10,0,'BlockAccountLogin'),(11,2275,'clearSamplerBottlesCode');

--
-- Table structure for table `Manifest`
--

CREATE TABLE `Manifest` (
  `pkManifest` int unsigned NOT NULL AUTO_INCREMENT,
  `ManifestUniqueID` varchar(255) DEFAULT NULL,
  `TelerikID` varchar(255) DEFAULT NULL,
  `StationID` int NOT NULL,
  `TruckNumber` int NOT NULL,
  `fkOrders` int NOT NULL,
  `OrderDate` datetime DEFAULT NULL,
  `CreateDate` datetime NOT NULL,
  `TotalVolume` decimal(12,4) NOT NULL,
  `ManifestNumber` varchar(255) NOT NULL,
  `Processed` tinyint NOT NULL,
  `incloud` tinyint NOT NULL,
  `UtcOrderDate` datetime DEFAULT NULL,
  `UtcCreateDate` datetime NOT NULL,
  `ProductTypeID` int DEFAULT NULL,
  `CostPerUnit` decimal(12,4) DEFAULT NULL,
  PRIMARY KEY (`pkManifest`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Manifest`
--


--
-- Table structure for table `Orders`
--

CREATE TABLE `Orders` (
  `pkOrders` int unsigned NOT NULL AUTO_INCREMENT,
  `StationID` int NOT NULL,
  `TicketNumber` int NOT NULL,
  `OrderDate` datetime NOT NULL,
  `TruckNumber` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `fkAlarm` int NOT NULL,
  `ReceiptNumber` int NOT NULL,
  `VolumeRequested` decimal(12,4) NOT NULL,
  `Volume` decimal(12,4) NOT NULL,
  `CostPerUnit` decimal(12,4) NOT NULL,
  `Amount` decimal(12,4) NOT NULL,
  `Sampled` tinyint NOT NULL,
  `pH` decimal(8,2) NOT NULL,
  `ElapsedTime` int NOT NULL,
  `Spare1` decimal(12,2) NOT NULL,
  `Spare2` decimal(12,2) NOT NULL,
  `Spare3` decimal(12,2) NOT NULL,
  `Spare4` decimal(12,2) NOT NULL,
  `Spare5` decimal(12,2) NOT NULL,
  `Spare6` decimal(12,2) NOT NULL,
  `Spare7` decimal(12,2) NOT NULL,
  `Spare8` decimal(12,2) NOT NULL,
  `Spare9` decimal(12,2) NOT NULL,
  `Spare10` decimal(12,2) NOT NULL,
  `WasteSources` varchar(255) DEFAULT NULL,
  `initials` varchar(50) DEFAULT NULL,
  `ManifestNumber` varchar(255) DEFAULT NULL,
  `incloud` tinyint NOT NULL,
  `UtcDate` datetime DEFAULT NULL,
  `Spare11` varchar(255) DEFAULT NULL,
  `Spare12` varchar(255) DEFAULT NULL,
  `Spare13` varchar(255) DEFAULT NULL,
  `Spare14` varchar(255) DEFAULT NULL,
  `Spare15` varchar(255) DEFAULT NULL,
  `Spare16` varchar(255) DEFAULT NULL,
  `Spare17` varchar(255) DEFAULT NULL,
  `Spare18` varchar(255) DEFAULT NULL,
  `Spare19` varchar(255) DEFAULT NULL,
  `Spare20` varchar(255) DEFAULT NULL,
  `Spare21` decimal(12,4) NOT NULL,
  `Spare22` decimal(12,4) NOT NULL,
  `Spare23` decimal(12,4) NOT NULL,
  `Spare24` decimal(12,4) NOT NULL,
  `Spare25` decimal(12,4) NOT NULL,
  `Spare26` decimal(12,4) NOT NULL,
  `Spare27` decimal(12,4) NOT NULL,
  `Spare28` decimal(12,4) NOT NULL,
  `Spare29` decimal(12,4) NOT NULL,
  `Spare30` decimal(12,4) NOT NULL,
  `FillPointNum` tinyint NOT NULL,
  PRIMARY KEY (`pkOrders`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Orders`
--

INSERT INTO `Orders` VALUES (1,1,526926,'2024-07-25 14:48:45',0,1,0,1,10.0000,10.0000,6.7500,0.0675,0,0.00,0,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,3.00,'','','',1,'2024-07-25 19:48:45',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(2,1,331158,'2024-08-19 08:57:33',0,1,0,2,4000.0000,4000.0000,6.7500,27.0000,0,0.00,20,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-08-19 13:57:33',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(3,1,331158,'2024-08-28 16:15:01',0,1,0,3,2000.0000,2000.0000,6.7500,13.5000,0,0.00,10,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-08-28 21:15:01',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(4,1,331158,'2024-08-28 16:23:52',0,1,0,4,1300.0000,1092.0000,6.7500,7.3710,0,0.00,5,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-08-28 21:23:52',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(5,1,331158,'2024-08-29 12:56:30',0,1,0,5,4000.0000,3591.0000,6.7500,24.2393,0,0.00,18,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-08-29 17:56:30',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(6,1,331158,'2024-09-30 09:22:06',0,1,0,6,2800.0000,2800.0000,6.5000,18.2000,0,0.00,14,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-09-30 14:22:06',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(7,1,331158,'2024-10-01 17:58:11',0,1,0,7,4000.0000,2815.0000,6.5000,18.2975,0,0.00,14,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-10-01 22:58:11',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(8,1,526926,'2024-10-01 19:01:49',0,1,0,8,0.0000,2451.0000,6.5000,15.9315,0,0.00,12,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,3.00,'','','',1,'2024-10-02 00:01:49',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(9,1,331158,'2024-10-02 15:57:03',0,1,0,9,2200.0000,2200.0000,6.5000,14.3000,0,0.00,11,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-10-02 20:57:03',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1),(10,1,331158,'2024-10-04 09:15:44',0,1,0,10,5400.0000,3284.0000,6.5000,21.3460,0,0.00,16,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,5.00,'','','',1,'2024-10-04 14:15:44',NULL,'','','',NULL,NULL,NULL,NULL,NULL,NULL,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000,1);

--
-- Table structure for table `Parameters`
--

CREATE TABLE `Parameters` (
  `ID` int NOT NULL,
  `Val` decimal(12,4) NOT NULL,
  `Name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Unit` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Parameters`
--

INSERT INTO `Parameters` VALUES (0,0.0000,'','bit'),(1,0.0000,'','bit'),(2,0.0000,'','bit'),(3,0.0000,'','bit'),(4,0.0000,'','bit'),(5,0.0000,'','bit'),(6,0.0000,'','bit'),(7,0.0000,'','bit'),(8,0.0000,'','bit'),(9,0.0000,'','bit'),(10,0.0000,'','bit'),(11,0.0000,'','bit'),(12,0.0000,'','bit'),(13,0.0000,'','bit'),(14,0.0000,'','bit'),(15,0.0000,'','bit'),(16,0.0000,'','bit'),(17,0.0000,'','bit'),(18,0.0000,'','bit'),(19,0.0000,'','bit'),(20,0.0000,'','bit'),(21,0.0000,'','bit'),(22,0.0000,'','bit'),(23,0.0000,'','bit'),(24,0.0000,'','bit'),(25,0.0000,'','bit'),(26,0.0000,'','bit'),(27,0.0000,'','bit'),(28,0.0000,'','bit'),(29,0.0000,'','bit'),(30,0.0000,'','bit'),(31,0.0000,'','bit'),(32,0.0000,'','bit'),(33,0.0000,'','bit'),(34,0.0000,'','bit'),(35,0.0000,'','bit'),(36,0.0000,'','bit'),(37,0.0000,'','bit'),(38,0.0000,'','bit'),(39,0.0000,'','bit'),(40,0.0000,'',''),(41,0.0000,'',''),(42,0.0000,'',''),(43,0.0000,'',''),(44,0.0000,'',''),(45,0.0000,'',''),(46,0.0000,'',''),(47,0.0000,'',''),(48,0.0000,'',''),(49,0.0000,'',''),(50,0.0000,'',''),(51,0.0000,'',''),(52,0.0000,'',''),(53,0.0000,'',''),(54,0.0000,'',''),(55,0.0000,'',''),(56,0.0000,'',''),(57,0.0000,'',''),(58,0.0000,'',''),(59,0.0000,'',''),(60,0.0000,'',''),(61,0.0000,'',''),(62,0.0000,'',''),(63,0.0000,'',''),(64,0.0000,'',''),(65,0.0000,'',''),(66,0.0000,'',''),(67,0.0000,'',''),(68,0.0000,'',''),(69,0.0000,'',''),(70,0.0000,'',''),(71,0.0000,'',''),(72,0.0000,'',''),(73,0.0000,'',''),(74,0.0000,'',''),(75,0.0000,'',''),(76,0.0000,'',''),(77,0.0000,'',''),(78,0.0000,'',''),(79,0.0000,'',''),(80,0.0000,'',''),(81,0.0000,'',''),(82,0.0000,'',''),(83,0.0000,'',''),(84,0.0000,'',''),(85,0.0000,'',''),(86,0.0000,'',''),(87,0.0000,'',''),(88,0.0000,'',''),(89,0.0000,'',''),(90,0.0000,'',''),(91,0.0000,'',''),(92,0.0000,'',''),(93,0.0000,'',''),(94,0.0000,'',''),(95,0.0000,'',''),(96,0.0000,'',''),(97,0.0000,'',''),(98,0.0000,'',''),(99,0.0000,'',''),(100,1.0000,'Volume Pulse','N/A'),(101,1.0000,'Enable Drain Valve','bit'),(102,1.0000,'Enable No Flow Logoff','bit'),(103,20.0000,'No Flow Logoff Time Limit','min'),(104,10.0000,'Drain Valve Open Delay','sec'),(105,0.0000,'Drain Valve Open Duration (PLC)','sec'),(106,0.0000,'Minimum Analog Volume','N/A'),(107,1000.0000,'Maximum Analog Volume','N/A'),(108,2.0000,'Flow Meter Type (1: Pulse, 2: Analog)','N/A'),(109,60.0000,'Transaction Time Limit','min'),(110,0.0000,'Minimum pH Analog','N/A'),(111,14.0000,'Maximum pH Analog','N/A'),(112,2.0000,'Minimum Allowed pH Analog','N/A'),(113,11.0000,'Maximum Allowed pH Analog','N/A'),(114,0.0000,'pH Fault Delay','sec'),(115,0.0000,'Equipment Fail to Run Fault Delay','sec'),(116,0.0000,'Equipment Off Delay','sec'),(117,0.0000,'Vehicle Not Detected Logoff Delay','sec'),(118,0.0000,'Minimum Conductivity Analog Input','N/A'),(119,100000.0000,'Maximum Conductivity Analog Input','N/A'),(120,1000.0000,'Maximum Allowed Conductivity Analog','N/A'),(121,0.0000,'Conductivity Fault Delay','sec'),(122,0.0000,'High Level Fault Delay','sec'),(123,0.0000,'Close Water Valve Early Time','sec'),(124,5.0000,'Inspection Period','sec'),(125,0.0000,'',''),(126,0.0000,'',''),(127,0.0000,'Flush Run Time','sec'),(128,0.0000,'',''),(129,0.0000,'Valve Fail to Open','sec'),(130,0.0000,'Valve Fail to Close','sec'),(131,0.0000,'',''),(132,0.0000,'',''),(133,0.0000,'',''),(134,0.0000,'',''),(135,0.0000,'',''),(136,0.0000,'',''),(137,0.0000,'',''),(138,0.0000,'',''),(139,0.0000,'',''),(140,0.0000,'',''),(141,0.0000,'',''),(142,0.0000,'',''),(143,0.0000,'',''),(144,0.0000,'',''),(145,0.0000,'',''),(146,0.0000,'',''),(147,0.0000,'',''),(148,0.0000,'',''),(149,0.0000,'',''),(150,0.0000,'',''),(151,0.0000,'',''),(152,0.0000,'',''),(153,0.0000,'',''),(154,0.0000,'',''),(155,0.0000,'',''),(156,0.0000,'',''),(157,0.0000,'',''),(158,0.0000,'',''),(159,0.0000,'',''),(160,0.0000,'',''),(161,0.0000,'',''),(162,0.0000,'',''),(163,0.0000,'',''),(164,0.0000,'',''),(165,0.0000,'',''),(166,0.0000,'',''),(167,0.0000,'',''),(168,0.0000,'',''),(169,0.0000,'',''),(170,0.0000,'',''),(171,0.0000,'',''),(172,0.0000,'',''),(173,0.0000,'',''),(174,0.0000,'',''),(175,0.0000,'',''),(176,0.0000,'',''),(177,0.0000,'',''),(178,0.0000,'',''),(179,0.0000,'',''),(180,0.0000,'',''),(181,0.0000,'',''),(182,0.0000,'',''),(183,0.0000,'',''),(184,0.0000,'',''),(185,0.0000,'',''),(186,0.0000,'',''),(187,0.0000,'',''),(188,0.0000,'',''),(189,0.0000,'',''),(190,0.0000,'',''),(191,0.0000,'',''),(192,0.0000,'',''),(193,0.0000,'',''),(194,0.0000,'',''),(195,0.0000,'',''),(196,0.0000,'',''),(197,0.0000,'',''),(198,0.0000,'',''),(199,0.0000,'','');

--
-- Table structure for table `Payments`
--

CREATE TABLE `Payments` (
  `pkPayments` int unsigned NOT NULL AUTO_INCREMENT,
  `StationID` smallint NOT NULL,
  `TicketNumber` int NOT NULL,
  `eportnumber` int NOT NULL,
  `PaymentDate` datetime NOT NULL,
  `EffectiveDate` datetime NOT NULL,
  `Amount` decimal(12,4) NOT NULL,
  `Volume` decimal(12,4) NOT NULL,
  `incloud` tinyint DEFAULT NULL,
  `cc_last_4` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`pkPayments`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Payments`
--

INSERT INTO `Payments` VALUES (1,0,331158,0,'2024-08-30 09:08:00','2024-08-30 09:08:00',81.2500,0.0000,1,NULL),(2,0,331158,0,'2024-10-03 09:10:00','2024-10-03 09:10:00',101.3700,0.0000,1,NULL);

--
-- Table structure for table `Pickup`
--

CREATE TABLE `Pickup` (
  `pkPickup` int unsigned NOT NULL AUTO_INCREMENT,
  `StationID` int NOT NULL,
  `TruckNumber` int NOT NULL,
  `fkOrders` int NOT NULL,
  `OrderDate` datetime DEFAULT NULL,
  `PickupDate` datetime NOT NULL,
  `Volume` decimal(12,4) NOT NULL,
  `initials` varchar(50) NOT NULL,
  `ManifestNumber` varchar(255) NOT NULL,
  `ManifestUniqueID` varchar(255) DEFAULT NULL,
  `ManifestTelerikID` varchar(255) DEFAULT NULL,
  `wgnumber` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(100) DEFAULT NULL,
  `State` varchar(100) DEFAULT NULL,
  `Zip` varchar(100) DEFAULT NULL,
  `Processed` tinyint NOT NULL,
  `incloud` tinyint NOT NULL,
  `UtcOrderDate` datetime DEFAULT NULL,
  `UtcPickupDate` datetime NOT NULL,
  `WasteGeneratorName` varchar(100) DEFAULT NULL,
  `VolumeRatio` decimal(8,7) DEFAULT NULL,
  PRIMARY KEY (`pkPickup`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Pickup`
--


--
-- Table structure for table `PrintLines`
--

CREATE TABLE `PrintLines` (
  `ID` int NOT NULL,
  `Val` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `PrintLines`
--

INSERT INTO `PrintLines` VALUES (1,'Water Station Receipt'),(2,'Contact Information:'),(3,'Your Site WTP'),(4,'(987) 654-3210'),(5,'Customer Service');

--
-- Table structure for table `ProductType`
--

CREATE TABLE `ProductType` (
  `ProductTypeID` int NOT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `CostPerUnit` decimal(12,4) NOT NULL,
  `WType` int NOT NULL,
  `SubType` int NOT NULL,
  `BillPer1000` bit(1) NOT NULL,
  `BillPer100` bit(1) NOT NULL,
  `DeleteInd` tinyint NOT NULL,
  PRIMARY KEY (`ProductTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ProductType`
--

INSERT INTO `ProductType` VALUES (0,'Mixed',0.0000,0,0,_binary '\0',_binary '\0',1),(1,'Water',6.5000,1,3,_binary '\0',_binary '\0',0),(2,'Credit Card Water',6.5000,1,3,_binary '\0',_binary '\0',0);

--
-- Table structure for table `ReceiptNumber`
--

CREATE TABLE `ReceiptNumber` (
  `Number` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`Number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ReceiptNumber`
--

INSERT INTO `ReceiptNumber` VALUES (10);

--
-- Table structure for table `ScaleDumpRecords`
--

CREATE TABLE `ScaleDumpRecords` (
  `pkScaleDumpRecords` int unsigned NOT NULL AUTO_INCREMENT,
  `CreateDate` datetime NOT NULL,
  `TruckNumber` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `fkAlarm` int NOT NULL,
  `Volume` decimal(12,4) NOT NULL,
  `ElapsedTime` int NOT NULL,
  `Spare9` int NOT NULL,
  `Closed` bit(1) NOT NULL,
  UNIQUE KEY `pkScaleDumpRecords` (`pkScaleDumpRecords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ScaleDumpRecords`
--


--
-- Table structure for table `ScaleOpenRecords`
--

CREATE TABLE `ScaleOpenRecords` (
  `pkScaleOpenRecords` int unsigned NOT NULL AUTO_INCREMENT,
  `CreateDate` datetime NOT NULL,
  `fkScaleRecords` int NOT NULL,
  `AccessCodeID` int NOT NULL,
  UNIQUE KEY `pkScaleOpenRecords` (`pkScaleOpenRecords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ScaleOpenRecords`
--


--
-- Table structure for table `ScaleRecords`
--

CREATE TABLE `ScaleRecords` (
  `pkScaleRecords` int unsigned NOT NULL AUTO_INCREMENT,
  `CreateDate` datetime NOT NULL,
  `AccessCodeID` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `Weight` int NOT NULL,
  `Closed` bit(1) NOT NULL,
  `incloud` bit(1) NOT NULL,
  UNIQUE KEY `pkScaleRecords` (`pkScaleRecords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `ScaleRecords`
--


--
-- Table structure for table `StationConfig`
--

CREATE TABLE `StationConfig` (
  `ID` int NOT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Val` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `StationConfig`
--

INSERT INTO `StationConfig` VALUES (1,'fiveInchScreenUsed',1),(2,'tenInchScreenUsed',0),(3,'trucksUsed',1),(4,'designerTesting',0),(5,'allowedProductTypesUsed',0),(6,'restrictedSubTypeUsed',0),(7,'printerUsed',1),(8,'autoPrintReceipt',0),(9,'fillPointsUsed',0),(10,'floLogicUsed',0),(11,'rfidCardsUsed',0),(12,'showSpanishTextUsed',0),(13,'keypadUsed',1),(20,'autoFlowStart',1),(21,'flowRateUsed',0),(22,'flowPHused',0),(23,'flowConductivityUsed',0),(24,'flowMeterUsed',1),(25,'autoFlowEnd',0),(30,'pickupAddressUsed',0),(31,'pickupCityUsed',0),(32,'pickupVolumeUsed',0),(40,'homeScreenStartUsed',1),(41,'homeScreenProxCardUsed',0),(42,'enterPINscreenUsed',1),(43,'selectProductTypeScreenUsed',1),(44,'enterManifestScreenUsed',0),(45,'enterSpare9ScreenUsed',0),(46,'enterPickupScreenUsed',0),(47,'confirmScreenUsed',0),(48,'enterVolumeScreenUsed',0),(49,'selectLanguageScreenUsed',0),(50,'enterSpare12ScreenUsed',0),(51,'enterSpare13ScreenUsed',0),(52,'enterSpare14ScreenUsed',0),(53,'confirmDispensingScreenUsed',0),(54,'selectSingleWasteGenScreenUsed',0),(55,'confirmSingleWasteGenScreenUsed',0),(56,'enterWgPickupScreenUsed',0),(57,'wgPickupVolumeUsed',0),(58,'allowedCustomersWgUsed',0),(59,'homeScreenStartPrintUsed',0),(60,'enterSpare1ScreenUsed',0),(61,'enterSpare2ScreenUsed',0),(62,'enterSpare3ScreenUsed',0),(63,'enterSpare4ScreenUsed',0),(64,'enterSpare5ScreenUsed',0),(65,'tankFlushScreenUsed',0),(66,'checkBlackListUsed',0);

--
-- Table structure for table `StationFaultLookup`
--

CREATE TABLE `StationFaultLookup` (
  `ID` int NOT NULL,
  `FaultDesc` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `FaultDescAbbrev` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `FaultSolution` varchar(256) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `StationFaultLookup`
--

INSERT INTO `StationFaultLookup` VALUES (0,'','',''),(1,'PLC Communication Lost','PLC Comm Lost','Check PLC status and ethernet connection in station.'),(2,'Station Permissive Lost','Permissive Lost','Check Main Control Panel status.'),(3,'Power Loss / E-Stop','Power Loss / E-Stop','Check station control power and/or Emergency Stop push buttons.'),(4,'No Flow Logoff','No Flow Logoff','Ensure driver stops transaction before leaving the station.'),(5,'Valve Fault','Valve Fault','Check valve condition and piping.'),(6,'Currently in Bypass Mode','Bypass Mode','Not an error.'),(7,'High Level Alarm','High Level','Lower level of tank.'),(8,'Transaction Timeout','Transaction Timeout','Ensure driver dumps material faster next time or increase the timeout in Stations.'),(9,'Low pH Alarm','Low pH','Check driver waste source.'),(10,'High pH Alarm','High pH','Check driver waste source.'),(11,'Inactivity Logoff','Inactivity Logoff','User was automatically logged off after a period of inactivity.'),(12,'Equipment Fail to Run','Equip Fail to Run','Check downstream equipment status.'),(13,'Vehicle Left during Transaction','Vehicle Left','Check if truck left or the vehicle detection sensor status.'),(14,'Industrial PC Communication Failure','Station Comm Failure','Check Station alarms for the cause of the problem.'),(15,'High Conductivity','High Conductivity','Check driver waste source.'),(16,'Drum Fault','Drum Fault','Motor overloaded.  Inspect equipment.'),(17,'Auger Fault','Auger Fault','Motor overloaded.  Inspect equipment.'),(18,'Equipment Not in Auto','Equip Not in Auto','Ensure that the selector switches on the main control panel are in AUTO.'),(19,'Press Fault','Press Fault','Check main control panel alarm history for exact cause.'),(20,'Screen Fault','Screen Fault','Check main control panel alarm history for exact cause.'),(21,'Booster Pump Fault','Booster Pump Fault','Motor overloaded.  Inspect equipment.'),(22,'Sampler Full','Sampler Full','Remove samples, enter administrator code at station, and reset samplers.'),(23,'Unexpected Flow','Unexpected Flow','Flow was detected while not during a transaction.  Check for leaks/unauthorized use.'),(24,'Low Conductivity','Low Conductivity','Check driver waste source.'),(25,'Low Acoustic','Low Acoustic','Check driver waste source.'),(26,'Septage High Level','Septage High Lv','Lower level of tank.'),(27,'Leachate High Level','Leachate High Lv','Lower level of tank.'),(28,'HSOW High Level','HSOW High Lv','Lower level of tank.'),(29,'Grinder Fault','Grinder Fault','Motor overloaded.  Inspect equipment.'),(30,'Flow Meter Fault','Flow Meter Fault','Inspect flow meter.'),(31,'System not ready','System not ready',''),(32,'Station 1 in use.  Please wait.','',''),(33,'Station 2 in use.  Please wait.','',''),(34,'Station 3 in use.  Please wait.','',''),(35,'System Cleanup in progress.','',''),(36,'High Acoustic','High Acoustic','Check driver waste source.'),(37,'Excessive Fill Volume','Excess Fill Volume','Investigate truck driver haul.'),(38,'Grit Collector Fault','Grit Col Fault','Motor overloaded.  Inspect equipment.'),(39,'Grit Discharge Fault','Grit Dis Fault','Motor overloaded.  Inspect equipment.'),(40,'Conveyor Fault','Conveyor Fault','Motor overloaded.  Inspect equipment.'),(41,'Grease Skimmer Fault','Grease Skim Fault','Motor overloaded.  Inspect equipment.'),(42,'Grease Screw Fault','Grease Screw Fault','Motor overloaded.  Inspect equipment.'),(43,'Grease Pump Fault','Grease Pump Fault','Motor overloaded.  Inspect equipment.'),(44,'Tank Screw Fault','Tank Screw Fault','Motor overloaded.  Inspect equipment.'),(45,'pH Reader Fault','pH Reader Fault','Inspect equipment.'),(46,'','',''),(47,'','',''),(48,'','',''),(49,'','',''),(50,'','',''),(51,'MySQL local database error','MYSQL DB ERROR','Check log files for details.'),(52,'','','Check log files for details.'),(53,'Modbus PLC error','MODBUS PLC ERROR','Check log files for details.'),(54,'Allen-Bradley PLC error','AB PLC ERROR','Check log files for details.'),(55,'ePort error','EPORT ERROR','Check log files for details.'),(56,'Matrix display error','OIU ERROR','Check log files for details.'),(57,'Sampler error','SAMPLER ERROR','Check log files for details.'),(58,'','','Check log files for details.'),(59,'Webrelay error','WEBRELAY ERROR','Check log files for details.'),(60,'','',''),(61,'Keypad error','KEYPAD ERROR','Check log files for details.'),(62,'Proximity card reader error','CARD READER ERROR','Check log files for details.'),(63,'Ethernet card reader error','CARD READER ERROR','Check log files for details.'),(64,'Restclient error','RESTCLIENT ERROR','Check log files for details.'),(65,'Websocket error','WEBSOCKET ERROR','Check log files for details.'),(66,'Barcode reader error','BARCODE READER ERROR','Check log files for details.'),(67,'UPS AC Power Lost','UPS AC POWER LOST','Restore AC power to panel.'),(68,'','',''),(69,'','',''),(70,'','','');

--
-- Table structure for table `StationSchedule`
--

CREATE TABLE `StationSchedule` (
  `daynumber` int NOT NULL,
  `StationStart` char(5) NOT NULL,
  `StationEnd` char(5) NOT NULL,
  `OpenInd` tinyint NOT NULL,
  `AlldayInd` tinyint NOT NULL,
  PRIMARY KEY (`daynumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `StationSchedule`
--

INSERT INTO `StationSchedule` VALUES (0,'00:00','23:30',1,1),(1,'00:00','23:30',1,1),(2,'00:00','23:30',1,1),(3,'00:00','23:30',1,1),(4,'00:00','23:30',1,1),(5,'00:00','23:30',1,1),(6,'00:00','23:30',1,1);

--
-- Table structure for table `StationWiFi`
--

CREATE TABLE `StationWiFi` (
  `ID` int NOT NULL,
  `Description` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `Val` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `StationWiFi`
--

INSERT INTO `StationWiFi` VALUES (1,'enable_wifi','0'),(2,'network_name','WaterStation_WiFi_1'),(3,'network_password','waterstation'),(4,'enable_bridge','1'),(5,'network_ap_gateway','192.168.12.1');

--
-- Table structure for table `Task`
--

CREATE TABLE `Task` (
  `pkTask` int NOT NULL,
  `Val` int DEFAULT NULL,
  PRIMARY KEY (`pkTask`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Task`
--

INSERT INTO `Task` VALUES (1,0);

--
-- Table structure for table `Ticket`
--

CREATE TABLE `Ticket` (
  `TicketNumber` int NOT NULL,
  `passcode` int NOT NULL,
  `StationID` smallint NOT NULL,
  `cardtype` int NOT NULL,
  `ActivateDate` datetime NOT NULL,
  `fkCustomer` int NOT NULL,
  `ProductTypeID` int NOT NULL,
  `DebitAccountInd` tinyint NOT NULL,
  `BalanceDeactivateInd` tinyint NOT NULL,
  `CapacityBalance` decimal(12,4) NOT NULL,
  `activeInd` tinyint NOT NULL,
  `LowBalance` tinyint NOT NULL,
  `incloud` tinyint NOT NULL,
  PRIMARY KEY (`TicketNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Ticket`
--

INSERT INTO `Ticket` VALUES (269969,1119,1,1,'2024-10-03 05:22:26',8,1,0,0,0.0000,1,0,1),(276989,1118,1,1,'2024-07-26 10:13:49',7,1,0,0,0.0000,1,0,1),(331158,1116,1,1,'2024-07-25 09:55:20',6,1,0,0,0.0000,1,0,1),(526926,1017,1,1,'2024-07-24 08:25:12',4,1,0,0,0.0000,1,0,1),(632685,1016,1,1,'2024-07-22 04:39:56',3,1,0,0,0.0000,0,0,1),(732864,1012,1,1,'2024-07-22 04:34:14',2,1,0,0,0.0000,1,0,1),(779034,1018,1,1,'2024-07-25 04:09:28',5,1,0,0,0.0000,0,0,1),(999999,1111,1,2,'2024-07-19 06:08:45',1,2,0,0,0.0000,1,0,1);

--
-- Table structure for table `TicketRates`
--

CREATE TABLE `TicketRates` (
  `ID` int NOT NULL,
  `Val` decimal(12,4) NOT NULL,
  `Rate` decimal(12,4) NOT NULL DEFAULT '0.0000',
  `incloud` tinyint DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `TicketRates`
--

INSERT INTO `TicketRates` VALUES (1,0.0000,0.0500,NULL),(2,0.0000,0.0500,NULL),(3,0.0000,0.0500,NULL),(4,0.0000,0.0500,NULL),(5,0.0000,0.0500,NULL),(6,0.0000,0.0500,NULL),(7,0.0000,0.0500,NULL),(8,0.0000,0.0500,NULL),(9,0.0000,0.0500,NULL),(10,0.0000,0.0500,NULL);

--
-- Table structure for table `Truck`
--

CREATE TABLE `Truck` (
  `pkTruck` int NOT NULL,
  `fkCustomer` int NOT NULL,
  `TruckNumber` int NOT NULL,
  `PIN` int NOT NULL,
  `Capacity` int NOT NULL,
  `ActiveInd` tinyint NOT NULL,
  `SampleNextLoad` tinyint NOT NULL,
  `DefaultProductType` int NOT NULL,
  `BillByCapacityInd` tinyint NOT NULL,
  `TareWeight` int NOT NULL,
  `AccessCodeID` int NOT NULL,
  `DeleteInd` tinyint NOT NULL,
  `Spare1` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`pkTruck`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `Truck`
--


--
-- Table structure for table `WasteGenerator`
--

CREATE TABLE `WasteGenerator` (
  `wgnumber` int unsigned NOT NULL,
  `wgname` varchar(255) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `City` varchar(255) NOT NULL,
  `State` varchar(50) NOT NULL,
  `Zip` varchar(50) NOT NULL,
  `PhoneNumber` varchar(30) NOT NULL,
  `ActiveInd` tinyint NOT NULL,
  `SourceType` int NOT NULL,
  `SubType` int NOT NULL,
  `ProductType` int NOT NULL,
  `userdef1` varchar(50) DEFAULT NULL,
  `userdef2` varchar(50) DEFAULT NULL,
  `Notes` varchar(4000) DEFAULT NULL,
  `DeleteInd` tinyint NOT NULL,
  `incloud` tinyint NOT NULL,
  PRIMARY KEY (`wgnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `WasteGenerator`
--


--
-- Table structure for table `WasteGenerator_Customer`
--

CREATE TABLE `WasteGenerator_Customer` (
  `WasteGenerator_CustomerID` int unsigned NOT NULL,
  `wgnumber` int NOT NULL,
  `fkCustomer` int NOT NULL,
  `DeleteInd` tinyint NOT NULL,
  PRIMARY KEY (`WasteGenerator_CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `WasteGenerator_Customer`
--


--
-- Dumping routines for database 'PortALogic5'
--
/*!50003 DROP FUNCTION IF EXISTS `FIRST_DAY_OF_WEEK` */;
DELIMITER ;;
CREATE DEFINER=`port1234`@`%` FUNCTION `FIRST_DAY_OF_WEEK`(day DATE) RETURNS date
    DETERMINISTIC
BEGIN
  RETURN SUBDATE(day, WEEKDAY(day));
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GET_FACILITY_BALANCE` */;
DELIMITER ;;
CREATE DEFINER=`port1234`@`%` FUNCTION `GET_FACILITY_BALANCE`() RETURNS decimal(12,2)
    DETERMINISTIC
BEGIN
	declare mVolumeLimitEnabled tinyint;
	declare mVolumeLimitPeriod tinyint;
	

	declare mStartDate Date;
	declare mVolumeLimitQuantity decimal(12,2);
	declare mVolumeLimitBalance decimal(12, 2);
	
	select FVolumeLimitEnabled from LocalPLC into mVolumeLimitEnabled;
	select FVolumeLimitPeriod from LocalPLC into mVolumeLimitPeriod;
	select FVolumeLimitQuantity from LocalPLC into mVolumeLimitQuantity;
	
	if (mVolumeLimitPeriod <= 1) then
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-%d') as DATE) into mStartDate;
	end if;
	
	if (mVolumeLimitPeriod = 2) then 
		select DATE_ADD(FIRST_DAY_OF_WEEK(NOW()), INTERVAL -1 DAY) INTO mStartDate;
	end if;
	
	if(mVolumeLimitPeriod = 3) then 
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE) into mStartDate;
	end if;
	
	
	if (mVolumeLimitEnabled = 1) then 
		select mVolumeLimitQuantity - (select case when sum(Volume) is null then 0 else sum(Volume) end from Orders where OrderDate >= mStartDate) into mVolumeLimitBalance;
	else
		select 0 into mVolumeLimitBalance;
	end if;
	
  RETURN mVolumeLimitBalance;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GET_FACILITY_CUST_BALANCE` */;
DELIMITER ;;
CREATE DEFINER=`port1234`@`%` FUNCTION `GET_FACILITY_CUST_BALANCE`(1_AccessCode varchar(100)) RETURNS decimal(12,2)
    DETERMINISTIC
BEGIN
	declare mCount int;
	declare mVolumeLimitEnabled tinyint;
	declare mVolumeLimitPeriod tinyint;
	
	declare mCustomerVolumeLimitEnabled tinyint;
	declare mCustomerVolumeLimitQuantity decimal(12,2);
	declare mCustomerVolumeLimitBalance decimal(12, 2);

	declare mStartDate Date;
	
	
	select FCustomerVolumeLimitPeriod from LocalPLC into mVolumeLimitPeriod;
	select FCustomerVolumeLimitEnabled from LocalPLC into mCustomerVolumeLimitEnabled;
	select FCustomerVolumeLimitQuantity from LocalPLC into mCustomerVolumeLimitQuantity;

	if (mVolumeLimitPeriod <= 1) then
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-%d') as DATE) into mStartDate;
	end if;
	
	if (mVolumeLimitPeriod = 2) then 
		select DATE_ADD(FIRST_DAY_OF_WEEK(NOW()), INTERVAL -1 DAY) INTO mStartDate;
	end if;
	
	if(mVolumeLimitPeriod = 3) then 
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE) into mStartDate;
	end if;
	
	
	if (mCustomerVolumeLimitEnabled = 1) then
		select count(*) from AccessCode where Codes = 1_AccessCode into mCount;
		if (mCount > 0) then
			select mCustomerVolumeLimitQuantity - (select case when sum(a.Volume) is null then 0 else sum(a.Volume) end from Orders a inner join Ticket b on a.TicketNumber = b.TicketNumber 
			inner join AccessCode c on c.fkCustomer = b.fkCustomer where c.Codes = 1_AccessCode and OrderDate >= mStartDate ) into mCustomerVolumeLimitBalance;
		else
			select mCustomerVolumeLimitQuantity - (select case when sum(a.Volume) is null then 0 else sum(a.Volume) end 
			from Orders a inner join HouseAccessCode b on a.TicketNumber = b.TicketNumber 
				where b.AccessNumber = 1_AccessCode and OrderDate >= mStartDate) into mCustomerVolumeLimitBalance;
		end if;
	else
		select 0 into mCustomerVolumeLimitBalance;
	end if;
	
  RETURN mCustomerVolumeLimitBalance;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GET_STATION_BALANCE` */;
DELIMITER ;;
CREATE DEFINER=`port1234`@`%` FUNCTION `GET_STATION_BALANCE`() RETURNS decimal(12,2)
    DETERMINISTIC
BEGIN
	declare mVolumeLimitEnabled tinyint;
	declare mVolumeLimitPeriod tinyint;
	

	declare mStartDate Date;
	declare mVolumeLimitQuantity decimal(12,2);
	declare mVolumeLimitBalance decimal(12, 2);
	
	select VolumeLimitEnabled from LocalPLC into mVolumeLimitEnabled;
	select VolumeLimitPeriod from LocalPLC into mVolumeLimitPeriod;
	select VolumeLimitQuantity from LocalPLC into mVolumeLimitQuantity;
	
	if (mVolumeLimitPeriod <= 1) then
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-%d') as DATE) into mStartDate;
	end if;
	
	if (mVolumeLimitPeriod = 2) then 
		select DATE_ADD(FIRST_DAY_OF_WEEK(NOW()), INTERVAL -1 DAY) INTO mStartDate;
	end if;
	
	if(mVolumeLimitPeriod = 3) then 
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE) into mStartDate;
	end if;
	
	
	if (mVolumeLimitEnabled = 1) then 
		select mVolumeLimitQuantity - (select case when sum(Volume) is null then 0 else sum(Volume) end from Orders where OrderDate >= mStartDate and StationID = (select StationID from LocalPLC)) into mVolumeLimitBalance;
	else
		select 0 into mVolumeLimitBalance;
	end if;
	
  RETURN mVolumeLimitBalance;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `GET_STATION_CUST_BALANCE` */;
DELIMITER ;;
CREATE DEFINER=`port1234`@`%` FUNCTION `GET_STATION_CUST_BALANCE`(1_AccessCode varchar(100)) RETURNS decimal(12,2)
    DETERMINISTIC
BEGIN
	declare mCount int;
	declare mVolumeLimitEnabled tinyint;
	declare mVolumeLimitPeriod tinyint;
	
	declare mCustomerVolumeLimitEnabled tinyint;
	declare mCustomerVolumeLimitQuantity decimal(12,2);
	declare mCustomerVolumeLimitBalance decimal(12, 2);

	declare mStartDate Date;
	
	
	select CustomerVolumeLimitPeriod from LocalPLC into mVolumeLimitPeriod;
	select CustomerVolumeLimitEnabled from LocalPLC into mCustomerVolumeLimitEnabled;
	select CustomerVolumeLimitQuantity from LocalPLC into mCustomerVolumeLimitQuantity;

	if (mVolumeLimitPeriod <= 1) then
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-%d') as DATE) into mStartDate;
	end if;
	
	if (mVolumeLimitPeriod = 2) then 
		select DATE_ADD(FIRST_DAY_OF_WEEK(NOW()), INTERVAL -1 DAY) INTO mStartDate;
	end if;
	
	if(mVolumeLimitPeriod = 3) then 
		select CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE) into mStartDate;
	end if;
	
	
	if (mCustomerVolumeLimitEnabled = 1) then
		select count(*) from AccessCode where Codes = 1_AccessCode into mCount;
		if (mCount > 0) then
			select mCustomerVolumeLimitQuantity - (select case when sum(a.Volume) is null then 0 else sum(a.Volume) end from Orders a inner join Ticket b on a.TicketNumber = b.TicketNumber 
			inner join AccessCode c on c.fkCustomer = b.fkCustomer where c.Codes = 1_AccessCode and OrderDate >= mStartDate and a.StationID = (select StationID from LocalPLC)) into mCustomerVolumeLimitBalance;
		else
			select mCustomerVolumeLimitQuantity - (select case when sum(a.Volume) is null then 0 else sum(a.Volume) end 
			from Orders a inner join HouseAccessCode b on a.TicketNumber = b.TicketNumber 
				where b.AccessNumber = 1_AccessCode and OrderDate >= mStartDate and a.StationID = (select StationID from LocalPLC)) into mCustomerVolumeLimitBalance;
		end if;
	else
		select 0 into mCustomerVolumeLimitBalance;
	end if;
	
  RETURN mCustomerVolumeLimitBalance;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CustomGetPrintOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CustomGetPrintOrder`(1_TruckNumber int)
BEGIN
        declare mLine1 varchar(50);
        declare mLine2 varchar(50);
        declare mLine3 varchar(50);
        declare mLine4 varchar(50);
        declare mLine5 varchar(50);
        declare mLine6 varchar(50);
        
        declare mReceiptL varchar(50);
            declare mReceiptV varchar(50);
        declare mStationV varchar(50);
        declare mHauler varchar(50);
        declare mVolumeEntered varchar(50);
        declare mVolume varchar(50);
        declare OrderID varchar(20);
        declare mManifestNumber varchar(50);
        declare mNumPickups varchar(50);
        declare mManifestVolume varchar(50);
        declare mSpare1 varchar(50);
        declare mSpare2 varchar(50);
        declare mSpare3 varchar(50);
        declare mTicketNumber int;
        declare mCustomerID int;
        declare mElapsedTime int;
        declare muserdef1 varchar(50);
            
        declare mSpare4 varchar(50);
        declare mSpare6 varchar(50);

        declare mAmount decimal(12,2);
        declare mpH decimal(12,2);
        
        declare munit varchar(50);
        declare munit_vol varchar(50);
        declare mProductTypeID varchar(50);

        declare mAlarmID int(10);
        declare mAlarmDesc varchar(50);

        declare ticket1 varchar(50);
        declare ticket2 varchar(50);
        declare ticket3 varchar(50);
        declare ticket4 varchar(50);
        declare ticket5 varchar(50);
        declare ticket6 varchar(50);
        declare ticket7 varchar(50);
        declare ticket8 varchar(50);
        declare ticket9 varchar(50);
        declare ticket10 varchar(50);
        DECLARE countTickets int;
        

        select '' into ticket1;
        select '' into ticket2;
        select '' into ticket3;
        select '' into ticket4;
        select '' into ticket5;
        select '' into ticket6;
        select '' into ticket7;
        select '' into ticket8;
        select '' into ticket9;
        select '' into ticket10;

        
        
        
        select Val from PrintLines where ID = 1 into mLine1;
        select Val from PrintLines where ID = 14 into mLine2;
        select Val from PrintLines where ID = 15 into mLine3;
        select Val from PrintLines where ID = 16 into mLine4;
        select Val from PrintLines where ID = 17 into mLine5;
        
        
        select CONVERT(ReceiptNumber, CHAR(7)) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mReceiptV;
        select DATE_FORMAT(OrderDate, '%m/%d/%Y %H:%i') from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mLine6;
        select initials from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mHauler;
        select round(VolumeRequested,0) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mVolumeEntered;
        select round(Volume, 0) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mVolume;
        select ProductTypeID from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mProductTypeID;
        select ElapsedTime from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mElapsedTime;
        select Spare1 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mSpare1;
        select Spare2 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mSpare2;
        select Amount from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mSpare3;  
        select Spare4 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mSpare4;
        select Spare6 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mSpare6;
        select ROUND(Amount, 2) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mAmount;
        select pH from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mpH;
        select pkOrders from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into OrderID;
        select count(*) from Pickup where fkOrders = OrderID into mNumPickups;
        select TicketNumber from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mTicketNumber;
        
        select fkAlarm from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by OrderDate desc limit 1 into mAlarmID;   
        select FaultDescAbbrev from StationFaultLookup where ID = mAlarmID into mAlarmDesc;
        
        
        select ManifestNumber from Manifest where fkOrders = OrderID into mManifestNumber;
        select round(TotalVolume) from Manifest where fkOrders = OrderID into mManifestVolume;
        
        
        select count(*) from Pickup where fkOrders = OrderID into countTickets;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 into ticket1;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 1 into ticket2;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 2 into ticket3;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 3 into ticket4;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 4 into ticket5;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 5 into ticket6;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 6 into ticket7;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 7 into ticket8;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 8 into ticket9;
        select ManifestNumber from Pickup where fkOrders = OrderID LIMIT 1 OFFSET 9 into ticket10;

        
        select StationID from LocalPLC limit 1 into mStationV;
        select case when Val = 1 then 'GALLONS' when Val = 2 then 'LITERS' when Val =  3 then 'BARRELS' when Val = 4 then 'IGALLONS' when Val = 5 then 'CUBIC METER' end from LookUpInt where ID = 2 into munit;
        select case when Val = 1 then 'Volume (GAL)' when Val = 2 then 'Volume (L)' when Val =  3 then 'Volume (BBL)' when Val = 4 then 'Volume (IGAL)' when Val = 5 then 'Volume (M^3)' end from LookUpInt where ID = 2 into munit_vol;
        select fkCustomer from Ticket where TicketNumber = mTicketNumber into mCustomerID;
        select case when userdef1 is null then '' else userdef1 end from Customer where pkCustomer = mCustomerID into muserdef1;
        
        
        select mLine1 as col1, '' as col2 
        union select 'Station No.' as col1, mStationV as col2 
        union select 'Receipt No.' as col1, mReceiptV as col2 
        union select 'Truck No.' as col1, 1_TruckNumber as col2 
        union select 'Dispensed' as col1, mVolume as col2
        union select 'Units' as col1, munit as col2 
        union select 'Vol Balance' as col1, '' as col2 
        union select 'Product Type' as col1, mProductTypeID as col2 
        union select 'pH' as col1, mpH as col2 
        union select 'Elapsed Time' as col1, mElapsedTime as col2 
        union select 'Alarm' as col1, mAlarmDesc as col2
        union select '1' as col1, ticket1 as col2
        union select '2' as col1, ticket2 as col2
        union select '3' as col1, ticket3 as col2
        union select '4' as col1, ticket4 as col2
        union select '5' as col1, ticket5 as col2
        union select '6' as col1, ticket6 as col2
        union select '7' as col1, ticket7 as col2
        union select '8' as col1, ticket8 as col2
        union select '9' as col1, ticket9 as col2
        union select '10' as col1, ticket10 as col2
        union select mLine2 as col1, '' as col2 
        union select mLine3 as col1, '' as col2 
        union select mLine4 as col1, '' as col2 
        union select mLine5 as col1, '' as col2  
        
        
        	
        
        
        
        ;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetAccountNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetAccountNumber`(1_ProxCardNumber varchar(100))
BEGIN
    declare mTicketNumber int;
    declare mPIN int;
    declare mActiveCode int;
    declare mActtiveAccount int;
    declare mCapacityBalance int;
    declare mCustomerID int;
    declare mCount int;
    declare mDebitAccountInd int;
    declare mBalanceDeactivateInd int;
    declare mProductTypeID int;
    select count(*) from AccessCode where Codes = 1_ProxCardNumber into mCount;
    if (mCount > 0) then
        
        select PIN from AccessCode where Codes = 1_ProxCardNumber into mPIN;
        select fkCustomer from AccessCode where Codes = 1_ProxCardNumber into mCustomerID;
        select ActiveInd from AccessCode where Codes = 1_ProxCardNumber into mActiveCode;
        select TicketNumber from Ticket where fkCustomer = mCustomerID into mTicketNumber;
        select ActiveInd from Ticket where fkCustomer = mCustomerID into mActtiveAccount;
        select CapacityBalance from Ticket where fkCustomer = mCustomerID into mCapacityBalance;
        select DebitAccountInd from Ticket where fkCustomer = mCustomerID into mDebitAccountInd;
        select BalanceDeactivateInd from Ticket where fkCustomer = mCustomerID into mBalanceDeactivateInd;
        select ProductTypeID from Ticket where fkcustomer = mCustomerID into mProductTypeID;
        
        if (mActtiveAccount = 1) then
            select mTicketNumber as col1, 
            case when mDebitAccountInd = 1 and mBalanceDeactivateInd = 1 then 3 else 1 end as col2,
            0 as col3, 0 as col4, 0 as col5, 0 as col6, mPIN as col7, mProductTypeID as col8, mActiveCode as col9;
        else 
            select mTicketNumber as col1, 2 col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9;
        end if;
    else
        select 0 as col1, 0 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9;	    
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetBalanceAccessCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetBalanceAccessCode`(1_AccessCode varchar(100))
BEGIN
	declare mCount int;
	declare mCountActive int;
	declare mAccessActive tinyint;
	declare mPay decimal(65,4);
	declare mO  decimal(65,4);
	declare mDebit tinyint;
	declare mAutodeativate tinyint;
	declare mCapBalance int;
	declare mtype int;
	declare mTicketNumber int;
	declare mPIN int;
	declare mCapacity int;
    declare mCapacityBalance int;
	declare mTruckNumber int;
    declare mTruckTareWeight int;
	declare mProductTypeID int;
	declare mAccessCodeID int; 
	declare mSamplerSample int;
	declare mSampleNextLoad int; 
	declare mTruckActive tinyint;
	declare mVolumeLimitEnabled tinyint;
	declare mFVolumeLimitEnabled tinyint;
	declare mCustomerVolumeLimitEnabled tinyint;
	declare mFCustomerVolumeLimitEnabled tinyint;
	declare mVolumeLimitBalance decimal(12, 2);
	declare mCustomerVolumeLimitBalance decimal(12, 2);
	

	select AccessCodeID from AccessCode where Codes = 1_AccessCode limit 1 into mAccessCodeID;
    select VolumeLimitEnabled from LocalPLC into mVolumeLimitEnabled;
	select FVolumeLimitEnabled from LocalPLC into mFVolumeLimitEnabled;
	
	
	
	if(mVolumeLimitEnabled = 1 or mFVolumeLimitEnabled = 1) then 
		if (mFVolumeLimitEnabled = 1) then
			select GET_FACILITY_BALANCE() INTO mVolumeLimitBalance;
		else 
			select GET_STATION_BALANCE() INTO mVolumeLimitBalance;
		end if;
	else
		select 0 into mVolumeLimitBalance;
	end if;
	
	
	select CustomerVolumeLimitEnabled from LocalPLC into mCustomerVolumeLimitEnabled;
	select FCustomerVolumeLimitEnabled from LocalPLC into mFCustomerVolumeLimitEnabled;
	if(mCustomerVolumeLimitEnabled = 1 or mFCustomerVolumeLimitEnabled = 1) then
		if(mFCustomerVolumeLimitEnabled = 1) then
			select GET_FACILITY_CUST_BALANCE(1_AccessCode) INTO mCustomerVolumeLimitBalance;
		else
			select GET_STATION_CUST_BALANCE(1_AccessCode) INTO mCustomerVolumeLimitBalance;
		end if;
	else 
		select 0 into mCustomerVolumeLimitBalance;
	end if;
	
	select count(*) from AccessCode where Codes = 1_AccessCode into mCount;
	select TicketNumber from AccessCode a inner join Ticket b on a.fkCustomer = b.fkCustomer where Codes = 1_AccessCode into mTicketNumber;
	select PIN from AccessCode a inner join Ticket b on a.fkCustomer = b.fkCustomer where Codes = 1_AccessCode into mPIN;
	select case when DebitAccountInd is null then 0 else DebitAccountInd end from Ticket where TicketNumber = mTicketNumber into mDebit;
	select case when CapacityBalance is null then 0 else CapacityBalance end from Ticket where TicketNumber = mTicketNumber into mCapBalance;
	select cardtype from Ticket where TicketNumber = mTicketNumber into mtype;
	select case when BalanceDeactivateInd is null then 0 else BalanceDeactivateInd end from Ticket where TicketNumber = mTicketNumber into mAutodeativate;
	select ProductTypeID from Ticket where TicketNumber = mTicketNumber into mProductTypeID;
	select count(*) from Ticket where activeInd = 1 and TicketNumber = mTicketNumber into mCountActive;
	select ActiveInd from AccessCode where Codes = 1_AccessCode into mAccessActive;
	select case when sum(Volume) is null then 0 else sum(Volume) end from Payments where TicketNumber = mTicketNumber into mPay;
	select case when sum(Volume) is null then 0 else sum(Volume) end from Orders where TicketNumber = mTicketNumber into mO;
	
    if(mCount > 0) then
        update AccessCode set LastUsed = Now() where Codes = 1_AccessCode;
        if ((select count(*) from Truck a inner join AccessCode b on a.AccessCodeID = b.AccessCodeID where b.Codes = 1_AccessCode) = 1) then
            select Capacity from Truck a inner join AccessCode b on a.AccessCodeID = b.AccessCodeID where b.Codes = 1_AccessCode into mCapacity;
            select TruckNumber from Truck a inner join AccessCode b on a.AccessCodeID = b.AccessCodeID where b.Codes = 1_AccessCode into mTruckNumber;
            select TareWeight from Truck a inner join AccessCode b on a.AccessCodeID = b.AccessCodeID where b.Codes = 1_AccessCode into mTruckTareWeight;
            select CapacityBalance from Ticket where TicketNumber = mTicketNumber into mCapacityBalance;
			select ActiveInd from Truck where TruckNumber = mTruckNumber into mTruckActive;
			select SampleNextLoad from Truck where TruckNumber = mTruckNumber and DeleteInd = 0 into mSampleNextLoad;
			if (mSampleNextLoad > 0) then
				select SamplerSample from LocalPLC into mSamplerSample;
			else 
				select 0 into mSamplerSample;
			end if;
        else
            select 0 into mCapacity;
            select 0 into mCapacityBalance;
            select 0 into mTruckNumber;
			select 0 into mSampleNextLoad;
			select 0 into mSamplerSample;
			select 0 into mTruckActive;
			select 0 into mTruckTareWeight;
        end if;

        
        if (mAccessActive > 0) then
            select case when mCountActive = 1 then 
            case when (mDebit = 1 and mAutodeativate = 1) then 3 else 1 end else 2 end as cardcount, 
            mtype as cardtype, case when (mDebit = 1 and mAutodeativate = 1) then mCapBalance else  mPay - mO end as Balance, mTicketNumber as TicketNumber,
            mPIN as PIN, mCapacity as col5, mCapacityBalance as col6, mTruckNumber as col7, mProductTypeID as col8, mAccessCodeID as col9, mSamplerSample as col10, mTruckActive as col11,
            case when mFVolumeLimitEnabled then mFVolumeLimitEnabled else mVolumeLimitEnabled end as col12, mVolumeLimitBalance as col13,
            case when mFCustomerVolumeLimitEnabled = 1 then mFCustomerVolumeLimitEnabled else mCustomerVolumeLimitEnabled end as col14, mCustomerVolumeLimitBalance as col15, mTruckTareWeight as col16;  
        else 
            select 4 as cardcount, mtype as cardtype, case when (mDebit = 1 and mAutodeativate = 1) then mCapBalance else  mPay - mO end as Balance, mTicketNumber as TicketNumber, 
            mPIN as PIN, mCapacity as col5, mCapacityBalance as col6, mTruckNumber as col7, mProductTypeID as col8, mAccessCodeID as col9, mSamplerSample as col10, mTruckActive as col11,
            case when mFVolumeLimitEnabled then mFVolumeLimitEnabled else mVolumeLimitEnabled end as col12, mVolumeLimitBalance as col13,
            case when mFCustomerVolumeLimitEnabled = 1 then mFCustomerVolumeLimitEnabled else mCustomerVolumeLimitEnabled end as col14, mCustomerVolumeLimitBalance as col15, mTruckTareWeight as col16;
        end if;
    else 
        select count(*) from HouseAccessCode where AccessNumber = 1_AccessCode into mCount;
        if (mCount > 0) then
            select TicketNumber from HouseAccessCode where AccessNumber = 1_AccessCode into mTicketNumber;
            select PIN from HouseAccessCode where AccessNumber = 1_AccessCode into mPIN;
            
            select 1 as cardcount, 1 as cardtype, 0 as Balance, mTicketNumber as TicketNumber, mPIN as PIN, 0 as col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11,
			case when mFVolumeLimitEnabled = 1 then mFVolumeLimitEnabled else mVolumeLimitEnabled end as col12, mVolumeLimitBalance as col13,
			case when mFCustomerVolumeLimitEnabled = 1 then mFCustomerVolumeLimitEnabled else mCustomerVolumeLimitEnabled end as col14, mCustomerVolumeLimitBalance as col15, mTruckTareWeight as col16;
        else 
            select 0 as cardcount, 0 as cardtype, 0 as Balance, 0 as TicketNumber, 0 as PIN, 0 as col5, 0 as col6, 0 as col7, 0 as col8, 0 as col9, 0 as col10, 0 as col11, 0 as col12, 0 as col13, 0 as col14, 0 as col15, 0 as col16;
        end if;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetPrintOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetPrintOrder`(1_TruckNumber int)
BEGIN
        declare mLine1 varchar(50);
        declare mLine2 varchar(50);
        declare mLine3 varchar(50);
        declare mLine4 varchar(50);
        declare mLine5 varchar(50);
        
        declare mReceiptV varchar(50);
        declare mStationV varchar(50);
        declare mHauler varchar(50);
        declare mVolumeEntered varchar(50);
        declare mVolume varchar(50);
        declare OrderID varchar(20);
        declare mOrderManifestNumber varchar(50);
        declare mManifestNumber varchar(50);
        declare mNumPickups varchar(50);
        declare mManifestVolume varchar(50);
        declare mSpare1 varchar(50);
        declare mSpare2 varchar(50);
        declare mSpare3 varchar(50);
        declare mSpare9 int;
        declare mTicketNumber int;
        declare mCustomerID int;
        declare mElapsedTime int;
        declare muserdef1 varchar(50);
            
        declare mSpare4 varchar(50);
        declare mSpare6 varchar(50);

        declare mAmount decimal(12,2);
        declare mpH decimal(12,2);
        
        declare munit varchar(50);
        declare munit_vol varchar(50);
        declare mProductTypeID varchar(50);
        declare mDescription varchar(100);
        declare mSpare9Descr varchar(50);

        declare mAlarmID int(10);
        declare mAlarmDesc varchar(50);

        
        
        
        select Val from PrintLines where ID = 1 into mLine1;
        select Val from PrintLines where ID = 2 into mLine2;
        select Val from PrintLines where ID = 3 into mLine3;
        select Val from PrintLines where ID = 4 into mLine4;
        select Val from PrintLines where ID = 5 into mLine5;
        
        
        select CONVERT(ReceiptNumber, CHAR(7)) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mReceiptV;
        select initials from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mHauler;
        select round(VolumeRequested,0) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mVolumeEntered;
        select round(Volume, 0) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mVolume;        
        select ElapsedTime from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mElapsedTime;
        select ManifestNumber from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mOrderManifestNumber;
        select Spare1 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare1;
        select Spare2 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare2;
        select Amount from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare3;  
        select Spare4 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare4;
        select Spare6 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare6;
        select Spare9 from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mSpare9;
        select ROUND(Amount, 2) from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mAmount;
        select pH from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mpH;
        select pkOrders from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into OrderID;
        select count(*) from Pickup where fkOrders = OrderID into mNumPickups;
        select TicketNumber from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mTicketNumber;

		
        select ProductTypeID from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mProductTypeID;
        select Description from ProductType where ProductTypeID = mProductTypeID into mDescription;
		IF (mSpare9 = 1) THEN
			select 'Single' into mSpare9Descr;
		ELSE
			select 'Mixed' into mSpare9Descr;
		END IF;

        select fkAlarm from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) order by pkOrders desc limit 1 into mAlarmID;   
        select FaultDescAbbrev from StationFaultLookup where ID = mAlarmID limit 1 into mAlarmDesc;
        
        
        select ManifestNumber from Manifest where fkOrders = OrderID limit 1 into mManifestNumber;
        select round(TotalVolume) from Manifest where fkOrders = OrderID limit 1 into mManifestVolume;
        
        
        select StationID from LocalPLC limit 1 into mStationV;
        select case when Val = 1 then 'GALLONS' when Val = 2 then 'LITERS' when Val =  3 then 'BARRELS' when Val = 4 then 'IGALLONS' when Val = 5 then 'CUBIC METER' end from LookUpInt where ID = 2 limit 1 into munit;
        select case when Val = 1 then 'Volume (GAL)' when Val = 2 then 'Volume (L)' when Val =  3 then 'Volume (BBL)' when Val = 4 then 'Volume (IGAL)' when Val = 5 then 'Volume (M^3)' end from LookUpInt where ID = 2 limit 1 into munit_vol;
        select fkCustomer from Ticket where TicketNumber = mTicketNumber limit 1 into mCustomerID;
        select case when userdef1 is null then '' else userdef1 end from Customer where pkCustomer = mCustomerID limit 1 into muserdef1;
        
        
        select mLine1 as col1, '' as col2 
        union select 'Station No.' as col1, mStationV as col2 
        union select 'Receipt No.' as col1, mReceiptV as col2 
        union select 'Truck No.' as col1, 1_TruckNumber as col2 
        union select 'Volume Entered' as col1, mVolumeEntered as col2 
        union select 'Dispensed' as col1, mVolume as col2
        union select 'Units' as col1, munit as col2 
        union select 'Volume Balance' as col1, '' as col2 
        union select 'Product Type' as col1, mDescription as col2 
        union select 'Manifest' as col1, mOrderManifestNumber as col2 
        union select 'Product Type' as col1, mDescription as col2 
        union select 'pH' as col1, mpH as col2 
        union select 'uS/cm' as col1, mSpare1 as col2
        union select 'Load Type' as col1, mSpare9Descr as col2
        union select 'Elapsed Time' as col1, mElapsedTime as col2 
        union select 'Alarm' as col1, mAlarmDesc as col2
        union select mLine2 as col1, '' as col2 
        union select mLine3 as col1, '' as col2 
        union select mLine4 as col1, '' as col2 
        union select mLine5 as col1, '' as col2  
        
			
        
        
       
        ;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `GetTruckNumber` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTruckNumber`(1_ProxCardNumber varchar(100))
BEGIN
    declare mTruckNumber int;
    declare mPIN int;
    declare mAccessCodeID int; 
    declare mCount int;
    declare mCountT int;
    declare mAccessActive tinyint;
    select count(*) from AccessCode where Codes = 1_ProxCardNumber into mCount;
    if (mCount > 0) then
        select AccessCodeID from AccessCode where Codes = 1_ProxCardNumber  into mAccessCodeID;
        select ActiveInd from AccessCode where Codes = 1_ProxCardNumber into mAccessActive;
        select TruckNumber from Truck where AccessCodeID = mAccessCodeID and DeleteInd = 0 into mTruckNumber;
        select count(*) from Truck where TruckNumber = mTruckNumber and DeleteInd = 0 into mCountT;
        if (mCountT > 0) then
            
            select PIN from AccessCode where Codes = 1_ProxCardNumber into mPIN;
            if (mAccessActive = 0) then
                select mTruckNumber as col1, 2 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8;
            else
                call ValidateTruck(mTruckNumber, mPIN);
            end if;
            
        else
            select 0 as col1, 0 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8;
        end if;
    else
        select 0 as col1, 0 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8;	    
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertAlarmHistory` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertAlarmHistory`(
    1_Level int, 
    2_Description varchar(1000), 
    3_Solution varchar(1000)
)
BEGIN
    
    declare mcount int;
    select count(*) from AlarmHistory where CreateDate >= DATE(Now()) and Level = 1_Level and Description = 2_Description into mcount;
    delete from AlarmHistory where CreateDate < DATE_SUB(NOW(), INTERVAL 30 DAY);
    
    
    if ((mcount = 0) || (1_Level = 4)) then
        insert into AlarmHistory(CreateDate, `Level`, `Description`, Solution, incloud) values(Now(), 1_Level, 2_Description, 3_Solution, 0);
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertOrUpdateProduct` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertOrUpdateProduct`(
    1_ProductTypeID int,
    2_Description varchar(255),
    3_CostPerUnit decimal(65,4),
    4_DeleteInd tinyint,
    5_WType int,
    6_SubType int,
    7_BillPer1000 bit,
    8_BillPer100 bit
)
BEGIN
    DECLARE mCount INT;
    SELECT case when count(*) is null then 0 else count(*) end from ProductType where ProductTypeID = 1_ProductTypeID into mCount;

    
    if(mCount = 0) then
        insert into ProductType(
            ProductTypeID,
            Description,
            CostPerUnit,
            DeleteInd,
	        WType,
	        SubType,
            BillPer1000,
            BillPer100
        )
        values(
            1_ProductTypeID,
            2_Description,
            3_CostPerUnit,
            4_DeleteInd,
	        5_WType,
	        6_SubType,
	        7_BillPer1000,
	        8_BillPer100
        );
    end if;

    
    if(mCount > 0) then
        update ProductType set Description = 2_Description, 
            CostPerUnit = 3_CostPerUnit, 
            DeleteInd = 4_DeleteInd, 
            WType = 5_WType, 
            SubType = 6_SubType, 
            BillPer1000 = 7_BillPer1000,
            BillPer100 = 8_BillPer100
        where ProductTypeID = 1_ProductTypeID; 
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertPayment` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertPayment`(
    1_TicketNumber int,
    2_eportnumber int,
    3_Amount decimal(65,4),
    4_cc_last_4 varchar(10)
)
BEGIN
    declare mStationID int;
    declare mRate decimal(65,4);

    select StationID from LocalPLC limit 1 into mStationID;
    select Rate from TicketRates a inner join Ticket b on a.ID = b.cardtype and b.TicketNumber = 1_TicketNumber into mRate;
    
    insert into Payments(StationID,TicketNumber,eportnumber,PaymentDate,EffectiveDate,Amount,Volume,incloud, cc_last_4)
    values(mStationID,1_TicketNumber,2_eportnumber,Now(),Now(),3_Amount,3_Amount/mRate,0, 4_cc_last_4);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertWasteWithManifest` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertWasteWithManifest`(
        1_TruckNumber int,
        3_Volume decimal(65,4),
        4_fkAlarm int,
        5_ElapsedTime int,
        6_Spare9 decimal(12,2),
        7_pH decimal(8, 2),
        8_ProductTypeID int 
    )
BEGIN
        DECLARE mTicketNumber INT;
        DECLARE mOrderDate datetime;
        DECLARE mUtcDate datetime;
        DECLARE mReceiptNumber INT;
        DECLARE mRate decimal(65,4);
        DECLARE mRateNoDiscount decimal(65,4);
        DECLARE 2_VolumeRequested decimal(65,4);
        DECLARE mwastesources varchar(8000);
        DECLARE minitials varchar(10);
        DECLARE mmanifest varchar(255);
        DECLARE mProductTypeID int;
        DECLARE mTodayVolume decimal(65,4);
		DECLARE mAmount decimal(12,4);
        DECLARE mCapacity int;
        DECLARE mByCapacityInd tinyint;
        DECLARE mDisCount float;
        DECLARE mBillPer1000 bit;
        DECLARE mBillPer100 bit;
		DECLARE mTruckCapacity int;	

        
        SELECT BillByCapacityInd from Truck where TruckNumber = 1_TruckNumber into mByCapacityInd;
        SELECT b.TicketNumber from Truck a inner join Ticket b on a.fkCustomer = b.fkCustomer where a.TruckNumber  = 1_TruckNumber and b.activeInd = 1 and a.DeleteInd = 0 order by b.ActivateDate asc limit 1 into mTicketNumber;

        
        SELECT TotalVolume from Manifest where Processed = 0 and TruckNumber = 1_TruckNumber limit 1 into 2_VolumeRequested;
        SELECT ManifestNumber from Manifest where Processed = 0 and TruckNumber = 1_TruckNumber limit 1 into mmanifest;
        select ProductTypeID from Manifest where TruckNumber = 1_TruckNumber and Processed = 0 order by CreateDate desc limit 1 into mProductTypeID;
        select CostPerUnit from Manifest where TruckNumber = 1_TruckNumber and Processed = 0 order by CreateDate desc limit 1 into mRateNoDiscount;

        
        select case when DiscountRate is null then 0.0 else DiscountRate end from Ticket a inner join Customer b on a.fkCustomer = b.pkCustomer where a.TicketNumber = mTicketNumber into mDisCount;
        select mRateNoDiscount - (case when mDisCount is null then 0 else mDisCount end * mRateNoDiscount) into mRate;

        
        SELECT Now() into mOrderDate;
        SELECT UTC_TIMESTAMP() into mUtcDate;
        SELECT '1' into mwastesources;
        SELECT '' into minitials;
        SELECT BillPer1000 from ProductType where ProductTypeID = 9_ProductType limit 1 into mBillPer1000;
        SELECT BillPer100 from ProductType where ProductTypeID = 9_ProductType limit 1 into mBillPer100;
        SELECT case when SUM(Volume) is null then 0 else SUM(Volume) end from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) and OrderDate >= DATE(Now()) and StationID = (select StationID from LocalPLC) into mTodayVolume;
        SELECT Number from ReceiptNumber limit 1 into mReceiptNumber;

        
        if (mBillPer1000 > 0) then
            
            if (mByCapacityInd = 1) then
                select mCapacity into mTruckCapacity;
                select (ceil(mCapacity / 1000)) * mRate into mAmount;
            else
                select (ceil(3_Volume / 1000)) * mRate into mAmount;
            end if;
        elseif (mBillPer100 > 0) then
            
            if (mByCapacityInd = 1) then
                select mCapacity into mTruckCapacity;
                select ((ceil(mCapacity / 100)) / 10) * mRate into mAmount;
            else
                select (ceil(3_Volume / 100) / 10) * mRate into mAmount;
            end if;
        else
            
            if (mByCapacityInd = 1) then
                select (mRate * mCapacity) / 1000 into mAmount;
                select mCapacity into mTruckCapacity;
            else
                select (mRate * 3_Volume) / 1000 into mAmount;
            end if;
        end if;

        
        if(mTicketNumber > 0) then
            insert into Orders
            (
                StationID,
                TicketNumber,
                OrderDate,
                UtcDate,
                TruckNumber,
                ProductTypeID,
                ReceiptNumber,
                VolumeRequested,
                Volume,
                Amount,
                CostPerUnit,
                incloud,fkAlarm,Sampled,pH,ElapsedTime,
                Spare1,Spare2,Spare3,Spare4,Spare5,Spare6,Spare7,Spare8,Spare9,Spare10,
                WasteSources,initials,ManifestNumber,
                Spare21,Spare22,Spare23,Spare24,Spare25,Spare26,Spare27,Spare28,Spare29,Spare30,FillPointNum
            )
            values
            (
                (select StationID from LocalPLC),
                mTicketNumber,
                mOrderDate,
                mUtcDate,
                1_TruckNumber,
                8_ProductTypeID,
                mReceiptNumber + 1,
                2_VolumeRequested,
                3_Volume,
                mAmount,
                mRate,
                0,4_fkAlarm,0,7_pH,5_ElapsedTime,
                0,0,0,0,0,mTodayVolume + Volume,0,0,6_Spare9,0,
                mwastesources,minitials,mmanifest,
                case when mDisCount is null then 0 else mDisCount end,0,0,0,0,0,0,0,0,0,0
            );

            
            UPDATE ReceiptNumber set Number = Number + 1;
        end if;

        
        update Pickup set fkOrders = @@identity, OrderDate = mOrderDate, UtcOrderDate = mUtcDate where TruckNumber = 1_TruckNumber and Processed = 0;
        update Pickup set Processed = 1 where TruckNumber = 1_TruckNumber and Processed = 0;
        
        
        update Manifest set fkOrders = @@identity, OrderDate = mOrderDate where TruckNumber = 1_TruckNumber and Processed = 0;
        update Manifest set Processed = 1 where TruckNumber = 1_TruckNumber and Processed = 0;

        Update Manifest set Processed = 1 where Processed = 0 and TruckNumber = 1_TruckNumber;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertWasteWithTruck` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertWasteWithTruck`(
        1_TruckNumber int,
        2_VolumeRequested decimal(65,4),
        3_Volume decimal(65,4),
        4_Sampled tinyint,
        5_pH decimal(8,2),
        6_spare1 decimal(12,2),
        7_spare2 decimal(12,2),
        8_spare3 decimal(12,2),
        9_ProductType decimal(12,2),
        10_spare4 decimal(12,2),
        11_fkAlarm int,
        12_ElapsedTime decimal(12,2),
        13_Spare9 decimal(12,2),
		14_ManifestNumber varchar(255),
        15_spare5 decimal(12,2)
    )
BEGIN
        DECLARE mTicketNumber INT;
        DECLARE mReceiptNumber INT;
        DECLARE mRate decimal(65,4);
        DECLARE mRateNoDiscount decimal(65,4);
		DECLARE mAmount decimal(12,4);
        DECLARE mCapacity int;
        DECLARE mByCapacityInd tinyint;
        DECLARE mDisCount float;
        DECLARE mAccessCodeID int;
		DECLARE mTruckCapacity int;	
        DECLARE mBillPer1000 bit;
        DECLARE mBillPer100 bit;

        
        SELECT b.TicketNumber from Truck a inner join Ticket b on a.fkCustomer = b.fkCustomer where a.TruckNumber  = 1_TruckNumber and b.activeInd = 1 and a.DeleteInd = 0 order by b.ActivateDate asc limit 1 into mTicketNumber;
        select AccessCodeID from Truck where TruckNumber = 1_TruckNumber and DeleteInd = 0 into mAccessCodeID;
        SELECT BillByCapacityInd from Truck where TruckNumber = 1_TruckNumber into mByCapacityInd;
        select Capacity from Truck where TruckNumber = 1_TruckNumber into mCapacity;
        select 0 into mTruckCapacity;

        
        select case when DiscountRate is null then 0.0 else DiscountRate end from Ticket a inner join Customer b on a.fkCustomer = b.pkCustomer where a.TicketNumber = mTicketNumber into mDisCount;
        SELECT CostPerUnit from ProductType where ProductTypeID = 9_ProductType limit 1 into mRateNoDiscount;
        select mRateNoDiscount - (case when mDisCount is null then 0 else mDisCount end * mRateNoDiscount) into mRate;

        
        SELECT BillPer1000 from ProductType where ProductTypeID = 9_ProductType limit 1 into mBillPer1000;
        SELECT BillPer100 from ProductType where ProductTypeID = 9_ProductType limit 1 into mBillPer100;

        SELECT Number from ReceiptNumber limit 1 into mReceiptNumber;

        
        if (mBillPer1000 > 0) then
            
            if (mByCapacityInd = 1) then
                select mCapacity into mTruckCapacity;
                select (ceil(mCapacity / 1000)) * mRate into mAmount;
            else
                select (ceil(3_Volume / 1000)) * mRate into mAmount;
            end if;
        elseif (mBillPer100 > 0) then
            
            if (mByCapacityInd = 1) then
                select mCapacity into mTruckCapacity;
                select ((ceil(mCapacity / 100)) / 10) * mRate into mAmount;
            else
                select (ceil(3_Volume / 100) / 10) * mRate into mAmount;
            end if;
        else
            
            if (mByCapacityInd = 1) then
                select (mRate * mCapacity) / 1000 into mAmount;
                select mCapacity into mTruckCapacity;
            else
                select (mRate * 3_Volume) / 1000 into mAmount;
            end if;
        end if;

        if(mTicketNumber > 0) then
            insert into Orders
            (
                StationID,
                TicketNumber,
                OrderDate,
                UtcDate,
                TruckNumber,
                ProductTypeID,
                ReceiptNumber,
                VolumeRequested,
                Volume,
                Amount,
                CostPerUnit, incloud,fkAlarm,Sampled,pH,ElapsedTime,Spare1,Spare2,Spare3,Spare4,Spare5,Spare6,Spare7,Spare8,Spare9,Spare10,WasteSources,initials,ManifestNumber,
                Spare21,Spare22,Spare23,Spare24,Spare25,Spare26,Spare27,Spare28,Spare29,Spare30,FillPointNum
            )
            values
            (
                (select StationID from LocalPLC),
                mTicketNumber,
                Now(),
                UTC_TIMESTAMP(),
                1_TruckNumber,
                9_ProductType,
                mReceiptNumber + 1,
                mTruckCapacity,
                3_Volume,
                mAmount,
                mRate,
                0,11_fkAlarm,4_Sampled,5_pH,12_ElapsedTime,6_spare1,7_spare2,8_spare3,10_spare4,15_spare5,
                0,
                0,
                0,
                13_Spare9,
                mAccessCodeID,
                '','',14_ManifestNumber,
                case when mDisCount is null then 0 else mDisCount end,0,0,0,0,0,0,0,0,0,0
            );

            
            UPDATE ReceiptNumber set Number = Number + 1;
        end if;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `InsertWaterWithAccessCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertWaterWithAccessCode`(
        1_Codes varchar(100),
        2_VolumeRequested decimal(65,4),
        3_Volume decimal(65,4),
        4_fkAlarm int,
        5_spare5 decimal(12,2),
        6_ProductType int,
        7_ElapsedTime decimal(12,2),
        8_CashPayment bit,
        9_CashBalance decimal(12,2),
   		10_Spare12 varchar(255),
   		11_Spare13 varchar(255),
   		12_Spare14 varchar(255),
        13_FillPointNum int
 )
BEGIN
        DECLARE mStationID INT;
        DECLARE mTicketNumber INT;
        DECLARE mReceiptNumber INT;
        DECLARE mRate decimal(65,4);
        DECLARE mRateNoDiscount decimal(65,4);
		DECLARE mAmount decimal(12,4);
        DECLARE mCapacity int;
        DECLARE mBillPer1000 bit;
        DECLARE mBillPer100 bit;
        DECLARE mDisCount float;
        DECLARE mAccessCodeID int;
        DECLARE mTruckNumber int;
        DECLARE mCount int;

        
        select StationID from LocalPLC into mStationID;
        SELECT Number from ReceiptNumber limit 1 into mReceiptNumber;

		if (1_Codes = '999999') then
			select 999999 into mAccessCodeID;
			select 999999 into mTicketNumber;
		else 
			select AccessCodeID from AccessCode where Codes = 1_Codes and ActiveInd = 1 into mAccessCodeID;
			SELECT b.TicketNumber from AccessCode a inner join Ticket b on a.fkCustomer = b.fkCustomer where a.Codes  = 1_Codes and b.ActiveInd = 1 into mTicketNumber;
		end if;
		
        
        SELECT CostPerUnit from ProductType where ProductTypeID = 6_ProductType limit 1 into mRateNoDiscount;
        SELECT BillPer1000 from ProductType where ProductTypeID = 6_ProductType limit 1 into mBillPer1000;
        SELECT BillPer100 from ProductType where ProductTypeID = 6_ProductType limit 1 into mBillPer100;

        select case when DiscountRate is null then 0.0 else DiscountRate end from Ticket a inner join Customer b on a.fkCustomer = b.pkCustomer where a.TicketNumber = mTicketNumber into mDisCount;
        select mRateNoDiscount - (case when mDisCount is null then 0 else mDisCount end * mRateNoDiscount) into mRate;

        
        if (8_CashPayment) THEN
            select 9_CashBalance into mAmount;
            select 1 into 5_spare5;
        elseif (1_Codes = '999999') then
            
            select 2_VolumeRequested into 3_Volume;

            
            if (mBillPer1000 > 0) then
                select (ceil(3_Volume / 1000)) * mRate into mAmount;
            elseif (mBillPer100 > 0) then
                select ((ceil(3_Volume / 100)) / 10) * mRate into mAmount;
            else
                select (mRate * 3_Volume) / 1000 into mAmount;
            end if;        
        else
            
            if (mBillPer1000 > 0) then
                select (ceil(3_Volume / 1000)) * mRate into mAmount;
            elseif (mBillPer100 > 0) then
                select ((ceil(3_Volume / 100)) / 10) * mRate into mAmount;
            else
                select (mRate * 3_Volume) / 1000 into mAmount;
            end if;
        end if;

        
        select count(*) from Truck where DeleteInd = 0 and AccessCodeID = mAccessCodeID into mCount;
        if (mCount) then
            select TruckNumber from Truck where AccessCodeID = mAccessCodeID into mTruckNumber;
        else
            select 0 into mTruckNumber;
        end if;

        if(mTicketNumber > 0) then
            insert into Orders(
                StationID,
                TicketNumber,
                OrderDate,
                UtcDate,
                TruckNumber,
                ProductTypeID,
                ReceiptNumber,
                VolumeRequested,
                Volume,
                Amount,
                CostPerUnit,
                incloud,fkAlarm,Sampled,pH,ElapsedTime,Spare1,Spare2,Spare3,Spare4,
                Spare5,Spare6,Spare7,Spare8,Spare9,Spare10,
                WasteSources,initials,ManifestNumber,
                Spare12,Spare13,Spare14,
                Spare21,Spare22,Spare23,Spare24,Spare25,Spare26,Spare27,Spare28,Spare29,Spare30,
                FillPointNum
            )
            values
            (
                mStationID,
                mTicketNumber,
                Now(),
                UTC_TIMESTAMP(),
                mTruckNumber,
                6_ProductType,
                mReceiptNumber + 1,
                2_VolumeRequested,
                3_Volume,
                mAmount,
                mRate,
                0,4_fkAlarm,0,0,7_ElapsedTime,0,0,0,0,
                5_spare5,0,0,0,0,mAccessCodeID,
                '','','',
                10_Spare12,11_Spare13,12_Spare14,
                case when mDisCount is null then 0 else mDisCount end,0,0,0,0,0,0,0,0,0,
                13_FillPointNum
            );

            
            UPDATE ReceiptNumber set Number = Number + 1;
        end if;
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spiPayments` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spiPayments`(
    1_StationID int,
    2_TicketNumber int,
    3_eportnumber int,
    4_PaymentDate datetime,
    5_EffectiveDate datetime,
    6_Amount decimal(65,4),
    7_Volume decimal(65,4),
    8_incloud tinyint
)
BEGIN
    DECLARE mCount INT;
    SELECT case when count(*) is null then 0 else count(*) end from Payments where StationID = 1_StationID and PaymentDate = 4_PaymentDate and TicketNumber = 2_TicketNumber and eportnumber = 3_eportnumber into mCount;
    if(mCount = 0) then
        
        insert into Payments(
        StationID,
        TicketNumber,
        eportnumber,
        PaymentDate,
        EffectiveDate,
        Amount,
        Volume,
        incloud)
        values(
        1_StationID,
        2_TicketNumber,
        3_eportnumber,
        4_PaymentDate,
        5_EffectiveDate,
        6_Amount,
        7_Volume,
        8_incloud);
    end if;
    if(mCount > 0) then
        update Payments set Amount = 6_Amount where TicketNumber = 2_TicketNumber and PaymentDate = 4_PaymentDate;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spiScaleDumpRecords` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spiScaleDumpRecords`(
    1_TruckNumber int,
    2_ProductTypeID int,
    3_fkAlarm int,
    4_Volume decimal(65,4),
    5_ElapsedTime int,
    6_spare_9 int
)
BEGIN       
    
    delete from ScaleDumpRecords where CreateDate < DATE_SUB(NOW(), INTERVAL 30 DAY) and Closed = 1;
    
    
    INSERT INTO ScaleDumpRecords (`CreateDate`, `TruckNumber`, `ProductTypeID`, `fkAlarm`, `Volume`, `ElapsedTime`, `Spare9`, `Closed`)
    VALUES(Now(), 1_TruckNumber, 2_ProductTypeID, 3_fkAlarm, 4_Volume, 5_ElapsedTime, 6_spare_9, 0);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spiScaleOpenRecords` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spiScaleOpenRecords`(
    1_fkScaleRecords int,
    2_AccessCodeID int
)
BEGIN
    
    delete from ScaleOpenRecords where CreateDate < DATE_SUB(NOW(), INTERVAL 1 DAY);

    
    INSERT INTO ScaleOpenRecords (`CreateDate`, `fkScaleRecords`, `AccessCodeID`)
    VALUES(Now(), 1_fkScaleRecords, 2_AccessCodeID);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spiScaleRecords` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spiScaleRecords`(
    1_AccessCodeID int,
    2_ProductTypeID int,
    3_Weight int
)
BEGIN       
    
    delete from ScaleRecords where CreateDate < DATE_SUB(NOW(), INTERVAL 30 DAY) and incloud = 1;
    
    
    INSERT INTO ScaleRecords (`CreateDate`, `AccessCodeID`, `ProductTypeID`, `Weight`, `Closed`, `incloud`)

    VALUES(Now(), 1_AccessCodeID, 2_ProductTypeID, 3_Weight, 0, 0);
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `spsPrintOrder` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `spsPrintOrder`(
        1_AccessCode varchar(50),
        2_FillPointNum int
    )
BEGIN
        declare mLine1 varchar(50);
        declare mLine2 varchar(50);
        declare mLine3 varchar(50);
        declare mLine4 varchar(50);
        declare mLine5 varchar(50);
        
        declare mReceiptV varchar(50);
        
        declare mVolumeEntered varchar(50);
        declare mVolume varchar(50);
        declare mTransaction varchar(50);
        declare mNewBalance varchar(50);
        declare mEportTelemeter varchar(50);
            
        declare munit varchar(50);
        declare munit_vol varchar(50);
        declare mProductTypeID varchar(50);
        declare mDescription varchar(100);

        declare mAlarmID int(10);
        declare mAlarmDesc varchar(50);

		declare mAccessID int;
		declare mAccessNumber varchar(50);
		declare mTicketNumber int;
        declare mStationID int;
		
        
        
        
        select Val from PrintLines where ID = 1 into mLine1;
        select Val from PrintLines where ID = 2 into mLine2;
        select Val from PrintLines where ID = 3 into mLine3;
        select Val from PrintLines where ID = 4 into mLine4;
        select Val from PrintLines where ID = 5 into mLine5;
        
        
        select StationID from LocalPLC into mStationID;
        select ePortTelemeter from LocalPLC limit 1 into mEportTelemeter;

        
		if (1_AccessCode = '999999') then
			select 999999 into mTicketNumber;
		else
			select TicketNumber from AccessCode a inner join Ticket b on a.fkCustomer = b.fkCustomer where Codes = 1_AccessCode limit 1 into mTicketNumber;
		end if;
		
		if (1_AccessCode = '999999') then
			select 999999 into mAccessNumber;
		else 
			select FLOOR(Spare10) from Orders where TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mAccessID; 
			select AccessNumber from AccessCode where AccessCodeID =  mAccessID limit 1 into mAccessNumber;
		end if;
		
        select ROUND(VolumeRequested) from Orders WHERE TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mVolumeEntered;
        select ROUND(Volume) from Orders WHERE TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mVolume;
        select CONVERT(Amount, DECIMAL(12,2)) from Orders WHERE TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mTransaction;
        select ProductTypeID from Orders where TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mProductTypeID;
        select Description from ProductType where ProductTypeID = mProductTypeID into mDescription;

        
        select fkAlarm from Orders where TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mAlarmID;
        select FaultDescAbbrev from StationFaultLookup where ID = mAlarmID into mAlarmDesc;
        
        
        select ReceiptNumber from Orders WHERE TicketNumber = mTicketNumber AND StationID = mStationID AND FillPointNum = 2_FillPointNum ORDER BY pkOrders desc limit 1 into mReceiptV;
        select case when Val = 1 then 'GALLONS' when Val = 2 then 'LITERS' when Val =  3 then 'BARRELS' when Val = 4 then 'IGALLONS' when Val = 5 then 'CUBIC METER' end from LookUpInt where ID = 2 into munit;
        select case when Val = 1 then 'Volume (GAL)' when Val = 2 then 'Volume (L)' when Val =  3 then 'Volume (BBL)' when Val = 4 then 'Volume (IGAL)' when Val = 5 then 'Volume (M^3)' end from LookUpInt where ID = 2 into munit_vol;
        
        
        select mLine1 as col1, '' as col2 
        union select 'Station No.' as col1, mStationID as col2
        union select 'Receipt No.' as col1, mReceiptV as col2 
        union select 'Volume Entered' as col1, mVolumeEntered as col2
        union select 'Dispensed' as col1, mVolume as col2
        union select 'Units' as col1, munit as col2
        union select 'Volume Balance' as col1, '' as col2
        union select 'Product Type' as col1, mDescription as col2 
        
        
        union select 'Trans No.' as col1, '' as col2
        union select 'Serial No.' as col1, mEportTelemeter as col2
        union select 'Access No.' as col1, mAccessNumber as col2
        union select 'Credit Card' as col1, '' as col2
        union select 'Orig. Balance' as col1, '' as col2
        union select 'Amount Charged' as col1, '' as col2
        union select 'New Balance' as col1, '' as col2
        union select 'Alarm' as col1, mAlarmDesc as col2

        
        union select mLine2 as col1, '' as col2 
        union select mLine3 as col1, '' as col2 
        union select mLine4 as col1, '' as col2 
        union select mLine5 as col1, '' as col2;
        
    END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertAccessCode` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertAccessCode`(
    1_AccessCodeID int,
    2_fkCustomer int,
    3_AccessType tinyint,
    4_AccessNumber varchar(100),
    5_Codes varchar(100),
    6_ActiveInd tinyint,
    7_PIN int
)
BEGIN
    declare mcount int;
    select count(*) from AccessCode where AccessCodeID = 1_AccessCodeID into mcount;
    if (mcount = 0) then
        insert into AccessCode(AccessCodeID,fkCustomer,AccessType,AccessNumber,Codes,ActiveInd,PIN) values(1_AccessCodeID, 2_fkCustomer, 3_AccessType, 4_AccessNumber, 5_Codes, 6_ActiveInd, 7_PIN);
    else
        update AccessCode set AccessType = 3_AccessType, fkCustomer = 2_fkCustomer, AccessNumber = 4_AccessNumber, Codes = 5_Codes, ActiveInd = 6_ActiveInd, PIN = 7_PIN where AccessCodeID = 1_AccessCodeID;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertCustomer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertCustomer`(
    1_pkCustomer int,
    2_Name varchar(255),
    3_Address varchar(255),
    4_City varchar(255),
    5_State varchar(255),
    6_Zip varchar(50),
    7_Phone varchar(50),
    8_PIN int,
    9_DeleteInd tinyint,
    10_FacilityCode bigint,
    11_userdef1 varchar(100),
    12_userdef2 varchar(100)
)
BEGIN
    declare mcount int;
    select count(*) from Customer where pkCustomer = 1_pkCustomer into mcount;
    if (mcount = 0) then
        insert into Customer(pkCustomer,Name,Address,City,`State`,Zip,PhoneNumber,PIN,FacilityCode,userdef1,userdef2,DeleteInd,DiscountRate)
		values(1_pkCustomer, 2_Name, 3_Address, 4_City, 5_State, 6_Zip, 7_Phone,8_PIN, 10_FacilityCode,11_userdef1,12_userdef2, 9_DeleteInd,0);
    else
        update Customer set Name = 2_Name, Address = 3_Address, City = 4_City, State = 5_State, Zip = 6_Zip, PhoneNumber = 7_Phone, PIN = 8_PIN, DeleteInd = 9_DeleteInd ,userdef1 = 11_userdef1,userdef2 = 12_userdef2  where pkCustomer = 1_pkCustomer;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertCustomer_ProductType` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertCustomer_ProductType`(
    1_Customer_ProductTypeID int,
    2_fkCustomer int,
    3_ProductTypeID int,
    4_DeleteInd tinyint
)
BEGIN
    declare mcount int;
    select count(*) from Customer_ProductType where Customer_ProductTypeID = 1_Customer_ProductTypeID into mcount;
    if (mcount = 0) then
        insert into Customer_ProductType values(1_Customer_ProductTypeID, 2_fkCustomer, 3_ProductTypeID, 4_DeleteInd);
    else
        update Customer_ProductType set DeleteInd = 4_DeleteInd where Customer_ProductTypeID = 1_Customer_ProductTypeID;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertTicket` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertTicket`(
    1_TicketNumber int,
    2_passcode int,
    3_StationID int,
    4_cardtype int,
    5_ActivateDate DateTime,
    6_fkCustomer int,
    7_ProductTypeID int,
    8_activeInd tinyint,
    9_CapacityBalance decimal(65,4),
    10_DebitAccountInd tinyint,
    11_BalanceDeactivateInd tinyint
)
BEGIN
    DECLARE mCount INT;
    SELECT case when count(*) is null then 0 else count(*) end from Ticket where TicketNumber = 1_TicketNumber into mCount;
    if(mCount = 0) then
        
        insert into Ticket(
        TicketNumber,
        passcode,
        StationID,
        cardtype,
        ActivateDate,
        fkCustomer,
        ProductTypeID,
        activeInd,
        CapacityBalance,
        DebitAccountInd,
        BalanceDeactivateInd,
        LowBalance,
        incloud)
        values(
        1_TicketNumber,
        2_passcode,
        3_StationID,
        4_cardtype,
        5_ActivateDate,
        6_fkCustomer,
        7_ProductTypeID,
        8_activeInd,
        9_CapacityBalance,
        10_DebitAccountInd,
        11_BalanceDeactivateInd,
        0,
        1);
    end if;

    if(mCount > 0) then 
        update Ticket set ProductTypeID = 7_ProductTypeID, activeInd = 8_activeInd, CapacityBalance = 9_CapacityBalance, DebitAccountInd = 10_DebitAccountInd, BalanceDeactivateInd = 11_BalanceDeactivateInd, fkCustomer = 6_fkCustomer where TicketNumber = 1_TicketNumber;

    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertTruck` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertTruck`(
    1_pkTruck int,
    2_fkCustomer int,
    3_TruckNumber int,
    4_PIN int,
    5_Capacity int,
    6_ActiveInd tinyint,
    7_DeleteInd tinyint,
    9_DefaultProductType int,
    10_BillByCapacityInd tinyint,
    11_TareWeight int,
    12_AccessCodeID int,
    13_Spare1 varchar(255)
)
BEGIN
    DECLARE mCount INT;
    SELECT case when count(*) is null then 0 else count(*) end from Truck where pkTruck = 1_pkTruck into mCount;
    if(mCount = 0) then
        
        insert into Truck(
        pkTruck,
        fkCustomer,
        TruckNumber,
        PIN,
        Capacity,
        ActiveInd,
        DeleteInd, 
        SampleNextLoad, 
        DefaultProductType,
        BillByCapacityInd,
        TareWeight,
        AccessCodeID,
        Spare1)
        values(
        1_pkTruck,
        2_fkCustomer,
        3_TruckNumber,
        4_PIN,
        5_Capacity,
        6_ActiveInd,
        7_DeleteInd,
        0,
        9_DefaultProductType,
        10_BillByCapacityInd,
        11_TareWeight,
        12_AccessCodeID,
        13_Spare1
        );
    end if;

    if(7_DeleteInd = 1) then
        delete from Truck where pkTruck = 1_pkTruck;
    end if;

    if(mCount > 0) then 
        update Truck set PIN = 4_PIN, Capacity = 5_Capacity, ActiveInd = 6_ActiveInd, fkCustomer = 2_fkCustomer, TruckNumber = 3_TruckNumber, DeleteInd = 7_DeleteInd, DefaultProductType = 9_DefaultProductType, BillByCapacityInd = 10_BillByCapacityInd, TareWeight = 11_TareWeight, AccessCodeID = 12_AccessCodeID, Spare1 = 13_Spare1 where pkTruck = 1_pkTruck;

    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertWasteGenerator` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertWasteGenerator`(
    1_wgnumber int,
    2_wgname varchar(255),
    3_Address varchar(255),
    4_City varchar(255),
    5_State varchar(50),
    6_Zip varchar(50),
    7_PhoneNumber varchar(30),
    8_ActiveInd tinyint,
    9_SourceType int,
    10_SubType int,
    11_ProductType int,
    12_userdef1 varchar(50),
    13_userdef2 varchar(50),
    14_Notes varchar(4000),
    15_DeleteInd tinyint 
)
BEGIN
    DECLARE mCount INT;
    SELECT case when count(*) is null then 0 else count(*) end from WasteGenerator where wgnumber = 1_wgnumber into mCount;
    if(mCount = 0) then
    insert into WasteGenerator(
        wgnumber,
        wgname,
        Address,
        City,
        State,
        Zip,
        PhoneNumber,
        ActiveInd,
        SourceType,
        SubType,
        ProductType,
        userdef1,
        userdef2,
        Notes,
        DeleteInd,
        incloud
        )
        values(
        1_wgnumber,
        2_wgname,
        3_Address,
        4_City,
        5_State,
        6_Zip,
        7_PhoneNumber,
        8_ActiveInd,
        9_SourceType,
        10_SubType,
        11_ProductType,
        12_userdef1,
        13_userdef2,
        14_Notes,
        15_DeleteInd,
        1
        );

    end if;


    if(mCount > 0) then
        update WasteGenerator set wgname = 2_wgname, `Address` = 3_Address, City = 4_City, State = 5_State, Zip = 6_Zip, PhoneNumber = 7_PhoneNumber,
        ActiveInd = 8_ActiveInd, SourceType = 9_SourceType, SubType = 10_SubType, ProductType = 11_ProductType, userdef1 = 12_userdef1, userdef2 = 13_userdef2, Notes = 14_Notes,
        DeleteInd = 15_DeleteInd
        where wgnumber = 1_wgnumber;

    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateOrInsertWasteGenerator_Customer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateOrInsertWasteGenerator_Customer`(
    1_WasteGenerator_CustomerID int,
    2_wgnumber int,
    3_fkCustomer int,
    4_DeleteInd tinyint
)
BEGIN
    declare mcount int;
    select count(*) from WasteGenerator_Customer where WasteGenerator_CustomerID = 1_WasteGenerator_CustomerID into mcount;
    if (mcount = 0) then
        insert into WasteGenerator_Customer values(1_WasteGenerator_CustomerID, 2_wgnumber, 3_fkCustomer, 4_DeleteInd);
    else
        update WasteGenerator_Customer set DeleteInd = 4_DeleteInd where WasteGenerator_CustomerID = 1_WasteGenerator_CustomerID;
    end if;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `UpdateVolume` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateVolume`(1_Volume int)
BEGIN
    UPDATE LocalPLC set PlcVolume = 1_Volume;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ValidateTruck` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ValidateTruck`(1_TruckNumber int, 2_PIN int)
BEGIN 
    declare mcol1 int;
    declare mcol3 int;
    declare mcol4 int;
    declare mcount int;
    declare mTruckActive int;
    declare mTodayVolume decimal(65,4);
    declare mTicketNumber int;
    declare mAccountActive int;
    declare mBalanceDeactivateInd tinyint;
    declare mdebit int;
    declare mSamplerSample int;
    declare mSampleNextLoad int; 
    declare mDefaultWasteType int;
    SELECT count(*) from Truck a inner join AccessCode b on a.AccessCodeID = b.AccessCodeID where TruckNumber = 1_TruckNumber and b.PIN = 2_PIN into mcount;
    SELECT case when SUM(Volume) is null then 0 else SUM(Volume) end from Orders where TruckNumber = 1_TruckNumber and StationID = (select StationID from LocalPLC) and OrderDate >= DATE(Now()) into mTodayVolume;
    select DefaultProductType from Truck where TruckNumber = 1_TruckNumber and DeleteInd = 0 limit 1 into mDefaultWasteType;


    if(mcount = 0) then
    select 1_TruckNumber as col1, 0 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8;
    else
    select TicketNumber from Ticket a inner join Truck b on a.fkCustomer = b.fkCustomer 
        and b.TruckNumber = 1_TruckNumber and b.DeleteInd = 0 order by a.ActivateDate asc limit 1 into mTicketNumber;
    select activeInd from Ticket where TicketNumber = mTicketNumber limit 1 into mAccountActive;
    select ActiveInd from Truck where TruckNumber = 1_TruckNumber and DeleteInd = 0 limit 1 into mTruckActive;
    select SamplerSample from LocalPLC into mSamplerSample;
    select Capacity from Truck where TruckNumber = 1_TruckNumber and DeleteInd = 0 limit 1 into mcol4;
    select case when DebitAccountInd is null then 0 else DebitAccountInd end from Ticket a inner join Truck b on 
        a.fkCustomer = b.fkCustomer and TruckNumber = 1_TruckNumber and b.DeleteInd = 0 into mDebit;
    select case when BalanceDeactivateInd is null then 0 else BalanceDeactivateInd end 
        from Ticket where TicketNumber = mTicketNumber limit 1 into  mBalanceDeactivateInd;
    select CapacityBalance from Ticket where TicketNumber = mTicketNumber limit 1 into mcol3;
    select SampleNextLoad from Truck where TruckNumber = 1_TruckNumber and DeleteInd = 0 into mSampleNextLoad;
        if(mAccountActive = 0 or mTruckActive = 0) then
            select 1_TruckNumber as col1, 2 as col2, 0 as col3, 0 as col4, 0 as col5, 0 as col6, 0 as col7, 0 as col8;
        end if;

        if(mAccountActive = 1 and mTruckActive = 1 and mDebit = 0) then
            select 1_TruckNumber as col1, 1 as col2, 0 as col3, mcol4 as col4, mSampleNextLoad as col5, 
            case when mSampleNextLoad = 0 then 0 else mSamplerSample end as col6, 2_PIN as col7, mDefaultWasteType as col8;
        end if;

        if(mAccountActive = 1 and mTruckActive = 1 and mDebit = 1 and mBalanceDeactivateInd = 1) then
            select 1_TruckNumber as col1, 3 as col2, mcol3 as col3, mcol4 as col4, mSampleNextLoad as col5, 
            case when mSampleNextLoad = 0 then 0 else mSamplerSample end as col6, 2_PIN as col7, mDefaultWasteType as col8;
        end if;
        if(mAccountActive = 1 and mTruckActive = 1 and mDebit = 1 and mBalanceDeactivateInd = 0) then
            select 1_TruckNumber as col1, 1 as col2, mcol3 as col3, mcol4 as col4, mSampleNextLoad as col5, 
            case when mSampleNextLoad = 0 then 0 else mSamplerSample end as col6, 2_PIN as col7, mDefaultWasteType as col8;
        end if;
    end if;
END ;;
DELIMITER ;


