Ссылка на описание тестового задания

Проект сделан на Delphi Berlin+MySQL;

Реализация на VCL Forms Application

База данных

Для автоматического проставления даты в момент добавления записей использованы триггеры, например

CREATE DEFINER = CURRENT_USER TRIGGER `flat_db`.`counterData_BEFORE_INSERT` BEFORE INSERT ON `counterData` FOR EACH ROW
BEGIN
SET NEW.MeasureDateTime = Now();
END

CREATE DEFINER = CURRENT_USER TRIGGER `flat_db`.`counterData_BEFORE_INSERT` BEFORE INSERT ON `counterData` FOR EACH ROW

BEGIN

SET NEW.MeasureDateTime = Now();

END

Полный код SQL для создания базы данных

CREATE DATABASE  IF NOT EXISTS `flat_db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `flat_db`;
-- MySQL dump 10.13  Distrib 5.7.9, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: flat_db
-- ------------------------------------------------------
-- Server version	5.5.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `counterdata`
--

DROP TABLE IF EXISTS `counterdata`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `counterdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `counters_id` int(11) NOT NULL,
  `value` decimal(10,2) DEFAULT NULL,
  `measureDateTime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_counterData_counters1_idx` (`counters_id`),
  CONSTRAINT `fk_counterData_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `counterdata`
--

LOCK TABLES `counterdata` WRITE;
/*!40000 ALTER TABLE `counterdata` DISABLE KEYS */;
INSERT INTO `counterdata` VALUES (1,1,1.00,'2018-10-14 09:04:40'),(2,1,2.00,'2018-10-14 09:04:50'),(3,1,3.00,'2018-10-14 09:04:56'),(4,2,1.00,'2018-10-14 09:08:37'),(5,5,1.01,'2018-10-14 14:06:21'),(6,5,1.02,'2018-10-14 14:10:25'),(7,5,1.03,'2018-10-14 14:16:32');
/*!40000 ALTER TABLE `counterdata` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`counterData_BEFORE_INSERT` BEFORE INSERT ON `counterData` FOR EACH ROW
BEGIN
SET NEW.MeasureDateTime = Now();
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `counters`
--

DROP TABLE IF EXISTS `counters`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `counters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creationDateTime` datetime DEFAULT NULL,
  `serialNumber` varchar(45) DEFAULT NULL,
  `lastCheckDate` datetime DEFAULT NULL,
  `nextCheckDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `counters`
--

LOCK TABLES `counters` WRITE;
/*!40000 ALTER TABLE `counters` DISABLE KEYS */;
INSERT INTO `counters` VALUES (1,NULL,'1',NULL,NULL),(2,NULL,'2',NULL,NULL),(3,NULL,'3',NULL,NULL),(4,'2018-10-14 11:36:25','eSerialNumber1231','2018-10-14 11:10:42','2018-10-14 11:10:42'),(5,'2018-10-14 13:11:12','eSerialNumber1','2018-10-14 11:10:42','2018-10-14 11:10:42'),(6,'2018-10-14 15:56:46','eSerialNumber6','2018-10-14 11:10:42','2018-10-14 11:10:42'),(7,'2018-10-14 15:58:33','eSerialNumber7','2018-10-14 11:10:42','2018-10-14 11:10:42'),(8,'2018-10-14 19:20:20','eSerialNumber6','2018-10-14 11:10:42','2018-10-14 11:10:42'),(9,'2018-10-14 19:21:56','eSerialNumber7','2018-10-14 11:10:42','2018-10-14 11:10:42'),(10,'2018-10-14 19:24:19','eSerialNumber8','2018-10-14 11:10:42','2018-10-14 11:10:42'),(11,'2018-10-14 19:25:56','eSerialNumber9','2018-10-14 11:10:42','2018-10-14 11:10:42'),(12,'2018-10-14 19:27:53','eSerialNumber10','2018-10-14 11:10:42','2018-10-14 11:10:42'),(13,'2018-10-14 20:49:25','eSerialNumber11','2018-10-14 11:10:42','2018-10-14 11:10:42'),(14,'2018-10-14 22:40:17','eSerialNumber12','2018-10-14 11:10:42','2018-10-14 11:10:42'),(15,'2018-10-15 09:52:24','eSerialNumber13','2018-10-14 11:10:42','2018-10-14 11:10:42'),(16,'2018-10-15 10:04:03','eSerialNumber','2018-10-14 11:10:42','2018-10-14 11:10:42'),(17,'2018-10-15 11:30:24','eSerialNumber14','2018-10-14 11:10:42','2018-10-14 11:10:42'),(18,'2018-10-15 11:40:00','eSerialNumber15','2018-10-14 11:10:42','2018-10-14 11:10:42'),(19,'2018-10-15 12:04:48','eSerialNumber16','2018-10-14 11:10:42','2018-10-14 11:10:42'),(20,'2018-10-15 12:28:33','eSerialNumber17','2018-10-15 12:28:24','2018-10-15 12:28:24'),(21,'2018-10-15 12:28:44','eSerialNumber18','2018-10-15 12:28:35','2018-10-15 12:28:35'),(22,'2018-10-15 12:31:31','eSerialNumber19','2018-10-15 12:31:19','2018-10-15 12:31:19'),(23,'2018-10-15 12:33:14','eSerialNumber20','2018-10-15 12:33:09','2018-10-15 12:33:09'),(24,'2018-10-15 12:34:40','eSerialNumber21','2018-10-15 12:34:33','2018-10-15 12:34:33'),(25,'2018-10-15 12:37:27','eSerialNumber22','2018-10-15 12:37:22','2018-10-15 12:37:22');
/*!40000 ALTER TABLE `counters` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`counters_BEFORE_INSERT` BEFORE INSERT ON `counters` FOR EACH ROW
BEGIN
SET NEW.creationDateTime=Now();
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `countersupdatehistory`
--

DROP TABLE IF EXISTS `countersupdatehistory`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `countersupdatehistory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `counterOldValue` decimal(10,2) DEFAULT NULL,
  `flats_id` int(11) NOT NULL,
  `counters_id` int(11) NOT NULL,
  `countersOld_id` int(11) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_countersUpdateHistory_flats1_idx` (`flats_id`),
  KEY `fk_countersUpdateHistory_counters1_idx` (`counters_id`),
  KEY `fk_countersUpdateHistory_counters2_idx` (`countersOld_id`),
  CONSTRAINT `fk_countersUpdateHistory_counters2` FOREIGN KEY (`countersOld_id`) REFERENCES `counters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_countersUpdateHistory_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_countersUpdateHistory_flats1` FOREIGN KEY (`flats_id`) REFERENCES `flats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `countersupdatehistory`
--

LOCK TABLES `countersupdatehistory` WRITE;
/*!40000 ALTER TABLE `countersupdatehistory` DISABLE KEYS */;
INSERT INTO `countersupdatehistory` VALUES (1,1.00,1,1,1,'2018-10-14 17:12:37'),(2,3.00,2,7,2,'2018-10-14 19:01:18'),(3,NULL,10,12,NULL,'2018-10-14 19:27:53'),(4,3.00,9,12,11,'2018-10-14 19:28:25'),(5,3.00,9,12,11,'2018-10-14 19:30:54'),(6,3.00,9,12,11,'2018-10-14 19:36:07'),(7,3.00,9,12,11,'2018-10-14 22:25:03'),(8,3.00,10,11,12,'2018-10-14 22:25:03'),(12,3.00,10,13,11,'2018-10-14 22:38:17'),(13,3.00,9,13,12,'2018-10-14 22:39:55'),(14,3.00,10,12,13,'2018-10-14 22:39:55'),(18,NULL,11,14,NULL,'2018-10-14 22:51:36'),(21,3.00,18,18,18,'2018-10-15 12:03:50'),(22,0.00,18,18,18,'2018-10-15 12:03:50'),(23,NULL,19,19,NULL,'2018-10-15 12:05:56'),(24,3.00,19,18,19,'2018-10-15 12:08:26'),(25,0.00,18,19,18,'2018-10-15 12:08:26'),(26,3.00,18,18,19,'2018-10-15 12:10:36'),(27,0.00,19,19,18,'2018-10-15 12:10:36'),(28,3.00,19,18,19,'2018-10-15 12:15:25'),(29,0.00,18,19,18,'2018-10-15 12:15:25'),(31,0.00,1,24,7,'2018-10-15 12:34:40'),(32,0.00,1,25,24,'2018-10-15 12:37:27'),(33,3.00,1,25,25,'2018-10-15 12:47:44'),(34,0.00,1,25,25,'2018-10-15 12:47:44');
/*!40000 ALTER TABLE `countersupdatehistory` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`countersUpdateHistory_BEFORE_INSERT` BEFORE INSERT ON `countersUpdateHistory` FOR EACH ROW
BEGIN
SET NEW.datetime=Now();
END */;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

--
-- Table structure for table `flats`
--

DROP TABLE IF EXISTS `flats`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `flats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `flatNumber` varchar(45) DEFAULT NULL,
  `houseNumbers_id` int(11) NOT NULL,
  `counters_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_flats_houseNumbers_idx` (`houseNumbers_id`),
  KEY `fk_flats_counters1_idx` (`counters_id`),
  CONSTRAINT `fk_flats_houseNumbers` FOREIGN KEY (`houseNumbers_id`) REFERENCES `housenumbers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_flats_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `flats`
--

LOCK TABLES `flats` WRITE;
/*!40000 ALTER TABLE `flats` DISABLE KEYS */;
INSERT INTO `flats` VALUES (1,'1',1,NULL),(2,'2',1,2),(3,'3',1,3),(4,'4',1,4),(5,'5',1,5),(6,'6',1,8),(7,'7',1,9),(8,'8',1,NULL),(9,'9',1,12),(10,'10',1,17),(11,'11',1,14),(12,'1',2,NULL),(13,'2',2,NULL),(14,'3',2,NULL),(15,'1',8,15),(16,'2',8,16),(17,'3',8,NULL),(18,'12',1,19),(19,'13',1,18);
/*!40000 ALTER TABLE `flats` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `housenumbers`
--

DROP TABLE IF EXISTS `housenumbers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `housenumbers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `streets_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_houseNumbers_streets1_idx` (`streets_id`),
  CONSTRAINT `fk_houseNumbers_streets1` FOREIGN KEY (`streets_id`) REFERENCES `streets` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `housenumbers`
--

LOCK TABLES `housenumbers` WRITE;
/*!40000 ALTER TABLE `housenumbers` DISABLE KEYS */;
INSERT INTO `housenumbers` VALUES (1,'1A',1),(2,'1B',1),(3,'1F',2),(4,'2N',2),(8,'1A',3),(9,'2F',3),(10,'3B',3);
/*!40000 ALTER TABLE `housenumbers` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `streets`
--

DROP TABLE IF EXISTS `streets`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `streets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `streets`
--

LOCK TABLES `streets` WRITE;
/*!40000 ALTER TABLE `streets` DISABLE KEYS */;
INSERT INTO `streets` VALUES (1,'Первая улица'),(2,'Вторая улица'),(3,'Третья улица');
/*!40000 ALTER TABLE `streets` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'flat_db'
--

--
-- Dumping routines for database 'flat_db'
--
/*!50003 DROP FUNCTION IF EXISTS `getLastCounterDataRecord` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `getLastCounterDataRecord`(aCounterId integer) RETURNS int(11)
BEGIN

RETURN (SELECT max(id) FROM flat_db.counterdata where counters_id=aCounterId);
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-10-15 13:06:39

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

CREATE DATABASE IF NOT EXISTS `flat_db` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `flat_db`;

-- MySQL dump 10.13 Distrib 5.7.9, for Win64 (x86_64)

-- Host: 127.0.0.1 Database: flat_db

-- ------------------------------------------------------

-- Server version 5.5.23

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `counterdata`

DROP TABLE IF EXISTS `counterdata`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `counterdata` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`counters_id` int(11) NOT NULL,

`value` decimal(10,2) DEFAULT NULL,

`measureDateTime` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_counterData_counters1_idx` (`counters_id`),

CONSTRAINT `fk_counterData_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `counterdata`

LOCK TABLES `counterdata` WRITE;

/*!40000 ALTER TABLE `counterdata` DISABLE KEYS */;

INSERT INTO `counterdata` VALUES (1,1,1.00,'2018-10-14 09:04:40'),(2,1,2.00,'2018-10-14 09:04:50'),(3,1,3.00,'2018-10-14 09:04:56'),(4,2,1.00,'2018-10-14 09:08:37'),(5,5,1.01,'2018-10-14 14:06:21'),(6,5,1.02,'2018-10-14 14:10:25'),(7,5,1.03,'2018-10-14 14:16:32');

/*!40000 ALTER TABLE `counterdata` ENABLE KEYS */;

UNLOCK TABLES;

/*!50003 SET @saved_cs_client = @@character_set_client */ ;

/*!50003 SET @saved_cs_results = @@character_set_results */ ;

/*!50003 SET @saved_col_connection = @@collation_connection */ ;

/*!50003 SET character_set_client = utf8 */ ;

/*!50003 SET character_set_results = utf8 */ ;

/*!50003 SET collation_connection = utf8_general_ci */ ;

/*!50003 SET @saved_sql_mode = @@sql_mode */ ;

/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;

DELIMITER ;;

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`counterData_BEFORE_INSERT` BEFORE INSERT ON `counterData` FOR EACH ROW

BEGIN

SET NEW.MeasureDateTime = Now();

END */;;

DELIMITER ;

/*!50003 SET sql_mode = @saved_sql_mode */ ;

/*!50003 SET character_set_client = @saved_cs_client */ ;

/*!50003 SET character_set_results = @saved_cs_results */ ;

/*!50003 SET collation_connection = @saved_col_connection */ ;

-- Table structure for table `counters`

DROP TABLE IF EXISTS `counters`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `counters` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`creationDateTime` datetime DEFAULT NULL,

`serialNumber` varchar(45) DEFAULT NULL,

`lastCheckDate` datetime DEFAULT NULL,

`nextCheckDate` datetime DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `counters`

LOCK TABLES `counters` WRITE;

/*!40000 ALTER TABLE `counters` DISABLE KEYS */;

INSERT INTO `counters` VALUES (1,NULL,'1',NULL,NULL),(2,NULL,'2',NULL,NULL),(3,NULL,'3',NULL,NULL),(4,'2018-10-14 11:36:25','eSerialNumber1231','2018-10-14 11:10:42','2018-10-14 11:10:42'),(5,'2018-10-14 13:11:12','eSerialNumber1','2018-10-14 11:10:42','2018-10-14 11:10:42'),(6,'2018-10-14 15:56:46','eSerialNumber6','2018-10-14 11:10:42','2018-10-14 11:10:42'),(7,'2018-10-14 15:58:33','eSerialNumber7','2018-10-14 11:10:42','2018-10-14 11:10:42'),(8,'2018-10-14 19:20:20','eSerialNumber6','2018-10-14 11:10:42','2018-10-14 11:10:42'),(9,'2018-10-14 19:21:56','eSerialNumber7','2018-10-14 11:10:42','2018-10-14 11:10:42'),(10,'2018-10-14 19:24:19','eSerialNumber8','2018-10-14 11:10:42','2018-10-14 11:10:42'),(11,'2018-10-14 19:25:56','eSerialNumber9','2018-10-14 11:10:42','2018-10-14 11:10:42'),(12,'2018-10-14 19:27:53','eSerialNumber10','2018-10-14 11:10:42','2018-10-14 11:10:42'),(13,'2018-10-14 20:49:25','eSerialNumber11','2018-10-14 11:10:42','2018-10-14 11:10:42'),(14,'2018-10-14 22:40:17','eSerialNumber12','2018-10-14 11:10:42','2018-10-14 11:10:42'),(15,'2018-10-15 09:52:24','eSerialNumber13','2018-10-14 11:10:42','2018-10-14 11:10:42'),(16,'2018-10-15 10:04:03','eSerialNumber','2018-10-14 11:10:42','2018-10-14 11:10:42'),(17,'2018-10-15 11:30:24','eSerialNumber14','2018-10-14 11:10:42','2018-10-14 11:10:42'),(18,'2018-10-15 11:40:00','eSerialNumber15','2018-10-14 11:10:42','2018-10-14 11:10:42'),(19,'2018-10-15 12:04:48','eSerialNumber16','2018-10-14 11:10:42','2018-10-14 11:10:42'),(20,'2018-10-15 12:28:33','eSerialNumber17','2018-10-15 12:28:24','2018-10-15 12:28:24'),(21,'2018-10-15 12:28:44','eSerialNumber18','2018-10-15 12:28:35','2018-10-15 12:28:35'),(22,'2018-10-15 12:31:31','eSerialNumber19','2018-10-15 12:31:19','2018-10-15 12:31:19'),(23,'2018-10-15 12:33:14','eSerialNumber20','2018-10-15 12:33:09','2018-10-15 12:33:09'),(24,'2018-10-15 12:34:40','eSerialNumber21','2018-10-15 12:34:33','2018-10-15 12:34:33'),(25,'2018-10-15 12:37:27','eSerialNumber22','2018-10-15 12:37:22','2018-10-15 12:37:22');

/*!40000 ALTER TABLE `counters` ENABLE KEYS */;

UNLOCK TABLES;

/*!50003 SET @saved_cs_client = @@character_set_client */ ;

/*!50003 SET @saved_cs_results = @@character_set_results */ ;

/*!50003 SET @saved_col_connection = @@collation_connection */ ;

/*!50003 SET character_set_client = utf8 */ ;

/*!50003 SET character_set_results = utf8 */ ;

/*!50003 SET collation_connection = utf8_general_ci */ ;

/*!50003 SET @saved_sql_mode = @@sql_mode */ ;

/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;

DELIMITER ;;

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`counters_BEFORE_INSERT` BEFORE INSERT ON `counters` FOR EACH ROW

BEGIN

SET NEW.creationDateTime=Now();

END */;;

DELIMITER ;

/*!50003 SET sql_mode = @saved_sql_mode */ ;

/*!50003 SET character_set_client = @saved_cs_client */ ;

/*!50003 SET character_set_results = @saved_cs_results */ ;

/*!50003 SET collation_connection = @saved_col_connection */ ;

-- Table structure for table `countersupdatehistory`

DROP TABLE IF EXISTS `countersupdatehistory`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `countersupdatehistory` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`counterOldValue` decimal(10,2) DEFAULT NULL,

`flats_id` int(11) NOT NULL,

`counters_id` int(11) NOT NULL,

`countersOld_id` int(11) DEFAULT NULL,

`datetime` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_countersUpdateHistory_flats1_idx` (`flats_id`),

KEY `fk_countersUpdateHistory_counters1_idx` (`counters_id`),

KEY `fk_countersUpdateHistory_counters2_idx` (`countersOld_id`),

CONSTRAINT `fk_countersUpdateHistory_counters2` FOREIGN KEY (`countersOld_id`) REFERENCES `counters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_countersUpdateHistory_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_countersUpdateHistory_flats1` FOREIGN KEY (`flats_id`) REFERENCES `flats` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `countersupdatehistory`

LOCK TABLES `countersupdatehistory` WRITE;

/*!40000 ALTER TABLE `countersupdatehistory` DISABLE KEYS */;

INSERT INTO `countersupdatehistory` VALUES (1,1.00,1,1,1,'2018-10-14 17:12:37'),(2,3.00,2,7,2,'2018-10-14 19:01:18'),(3,NULL,10,12,NULL,'2018-10-14 19:27:53'),(4,3.00,9,12,11,'2018-10-14 19:28:25'),(5,3.00,9,12,11,'2018-10-14 19:30:54'),(6,3.00,9,12,11,'2018-10-14 19:36:07'),(7,3.00,9,12,11,'2018-10-14 22:25:03'),(8,3.00,10,11,12,'2018-10-14 22:25:03'),(12,3.00,10,13,11,'2018-10-14 22:38:17'),(13,3.00,9,13,12,'2018-10-14 22:39:55'),(14,3.00,10,12,13,'2018-10-14 22:39:55'),(18,NULL,11,14,NULL,'2018-10-14 22:51:36'),(21,3.00,18,18,18,'2018-10-15 12:03:50'),(22,0.00,18,18,18,'2018-10-15 12:03:50'),(23,NULL,19,19,NULL,'2018-10-15 12:05:56'),(24,3.00,19,18,19,'2018-10-15 12:08:26'),(25,0.00,18,19,18,'2018-10-15 12:08:26'),(26,3.00,18,18,19,'2018-10-15 12:10:36'),(27,0.00,19,19,18,'2018-10-15 12:10:36'),(28,3.00,19,18,19,'2018-10-15 12:15:25'),(29,0.00,18,19,18,'2018-10-15 12:15:25'),(31,0.00,1,24,7,'2018-10-15 12:34:40'),(32,0.00,1,25,24,'2018-10-15 12:37:27'),(33,3.00,1,25,25,'2018-10-15 12:47:44'),(34,0.00,1,25,25,'2018-10-15 12:47:44');

/*!40000 ALTER TABLE `countersupdatehistory` ENABLE KEYS */;

UNLOCK TABLES;

/*!50003 SET @saved_cs_client = @@character_set_client */ ;

/*!50003 SET @saved_cs_results = @@character_set_results */ ;

/*!50003 SET @saved_col_connection = @@collation_connection */ ;

/*!50003 SET character_set_client = utf8 */ ;

/*!50003 SET character_set_results = utf8 */ ;

/*!50003 SET collation_connection = utf8_general_ci */ ;

/*!50003 SET @saved_sql_mode = @@sql_mode */ ;

/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;

DELIMITER ;;

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `flat_db`.`countersUpdateHistory_BEFORE_INSERT` BEFORE INSERT ON `countersUpdateHistory` FOR EACH ROW

BEGIN

SET NEW.datetime=Now();

END */;;

DELIMITER ;

/*!50003 SET sql_mode = @saved_sql_mode */ ;

/*!50003 SET character_set_client = @saved_cs_client */ ;

/*!50003 SET character_set_results = @saved_cs_results */ ;

/*!50003 SET collation_connection = @saved_col_connection */ ;

-- Table structure for table `flats`

DROP TABLE IF EXISTS `flats`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `flats` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`flatNumber` varchar(45) DEFAULT NULL,

`houseNumbers_id` int(11) NOT NULL,

`counters_id` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_flats_houseNumbers_idx` (`houseNumbers_id`),

KEY `fk_flats_counters1_idx` (`counters_id`),

CONSTRAINT `fk_flats_houseNumbers` FOREIGN KEY (`houseNumbers_id`) REFERENCES `housenumbers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,

CONSTRAINT `fk_flats_counters1` FOREIGN KEY (`counters_id`) REFERENCES `counters` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `flats`

LOCK TABLES `flats` WRITE;

/*!40000 ALTER TABLE `flats` DISABLE KEYS */;

INSERT INTO `flats` VALUES (1,'1',1,NULL),(2,'2',1,2),(3,'3',1,3),(4,'4',1,4),(5,'5',1,5),(6,'6',1,8),(7,'7',1,9),(8,'8',1,NULL),(9,'9',1,12),(10,'10',1,17),(11,'11',1,14),(12,'1',2,NULL),(13,'2',2,NULL),(14,'3',2,NULL),(15,'1',8,15),(16,'2',8,16),(17,'3',8,NULL),(18,'12',1,19),(19,'13',1,18);

/*!40000 ALTER TABLE `flats` ENABLE KEYS */;

UNLOCK TABLES;

-- Table structure for table `housenumbers`

DROP TABLE IF EXISTS `housenumbers`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `housenumbers` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(45) DEFAULT NULL,

`streets_id` int(11) NOT NULL,

PRIMARY KEY (`id`),

KEY `fk_houseNumbers_streets1_idx` (`streets_id`),

CONSTRAINT `fk_houseNumbers_streets1` FOREIGN KEY (`streets_id`) REFERENCES `streets` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `housenumbers`

LOCK TABLES `housenumbers` WRITE;

/*!40000 ALTER TABLE `housenumbers` DISABLE KEYS */;

INSERT INTO `housenumbers` VALUES (1,'1A',1),(2,'1B',1),(3,'1F',2),(4,'2N',2),(8,'1A',3),(9,'2F',3),(10,'3B',3);

/*!40000 ALTER TABLE `housenumbers` ENABLE KEYS */;

UNLOCK TABLES;

-- Table structure for table `streets`

DROP TABLE IF EXISTS `streets`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `streets` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(256) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `streets`

LOCK TABLES `streets` WRITE;

/*!40000 ALTER TABLE `streets` DISABLE KEYS */;

INSERT INTO `streets` VALUES (1,'Первая улица'),(2,'Вторая улица'),(3,'Третья улица');

/*!40000 ALTER TABLE `streets` ENABLE KEYS */;

UNLOCK TABLES;

-- Dumping events for database 'flat_db'

-- Dumping routines for database 'flat_db'

/*!50003 DROP FUNCTION IF EXISTS `getLastCounterDataRecord` */;

/*!50003 SET @saved_cs_client = @@character_set_client */ ;

/*!50003 SET @saved_cs_results = @@character_set_results */ ;

/*!50003 SET @saved_col_connection = @@collation_connection */ ;

/*!50003 SET character_set_client = utf8 */ ;

/*!50003 SET character_set_results = utf8 */ ;

/*!50003 SET collation_connection = utf8_general_ci */ ;

/*!50003 SET @saved_sql_mode = @@sql_mode */ ;

/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` FUNCTION `getLastCounterDataRecord`(aCounterId integer) RETURNS int(11)

BEGIN

RETURN (SELECT max(id) FROM flat_db.counterdata where counters_id=aCounterId);

END ;;

DELIMITER ;

/*!50003 SET sql_mode = @saved_sql_mode */ ;

/*!50003 SET character_set_client = @saved_cs_client */ ;

/*!50003 SET character_set_results = @saved_cs_results */ ;

/*!50003 SET collation_connection = @saved_col_connection */ ;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-10-15 13:06:39

Приложение

Сделано под влиянием концепции MVC – используются модели, представления, контроллеры, и также смешанные модели, так называемые ViewModel.

Логгирование ошибок проводится при помощи многопоточного TLDSLogger.

Для уменьшения количества кода, уменьшения ошибок, в контроллере используются классовые методы.

Подключение к базе данных

При помощи библиотеки FireDAC, с обработкой ошибок, согласно документации.

Драйвер libmysql.dll лежит в c:\windows\SysWow64\

Модуль подключения выглядит следующим образом.

unit uDb;

interface

uses
  System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait,
  FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL, Data.DB, FireDAC.Comp.Client;

type
  TDb = class(TDataModule)
    FDConnection: TFDConnection;
    FDPhysMySQLDriverLink: TFDPhysMySQLDriverLink;
    procedure DataModuleCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    function Connect: boolean;
  end;

var
  DB: TDB;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

function TDB.Connect: boolean;
var
  oParams: TStrings;
  ErrorInfo: string;
begin
//Драйвер << Можно отдельно прописать, если он не в папке с EXE
//  FDPhysMySQLDriverLink1.VendorLib:='C:\MySQLDriver\libmysql.dll';
// либо положить его в c:\windows\SysWow64\
  oParams := TStringList.Create;
  try
    oParams.Add('DataBase=flat_db');
    oParams.Add('Password=masterkey');
    oParams.Add('User_Name=root');
    oParams.Add('Port=3306');
    oParams.Add('Server=localhost');
    oParams.Add('CharacterSet=utf8');
//    oParams.Add('Pooled=true');
    //
    FDConnection.Params.Assign(oParams);
    FDConnection.DriverName := 'MySQL';
   //Пробуем подключиться
    try
      FDConnection.Connected := true;
      if FDConnection.Connected then
      begin
        Result := true;
//     showmessage('Connected');
      end
      else
        Result := false;
    except
      on E: EFDDBEngineException do
        case E.Kind of
          ekUserPwdInvalid:
       // user name or password are incorrect
            raise Exception.Create('DBConnection Error. User name or password are incorrect' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);
          ekUserPwdExpired:
            raise Exception.Create('DBConnection Error. User password is expired' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);
          ekServerGone:
            raise Exception.Create('DBConnection Error. DBMS is not accessible due to some reason' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);
        else                // other issues
          raise Exception.Create('DBConnection Error. UnknownMistake' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);
        end;
      on E: Exception do
        raise Exception.Create(E.ClassName + ' поднята ошибка, с сообщением : ' + #13#10 + #13#10 + E.Message);
    end;
  finally
    FreeAndNil(oParams);
  end;
end;

procedure TDb.DataModuleCreate(Sender: TObject);
begin
  Connect();
end;

end.

unit uDb;

interface

uses

System.SysUtils, System.Classes, FireDAC.Stan.Intf, FireDAC.Stan.Option,

FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,

FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait,

FireDAC.Phys.MySQLDef, FireDAC.Phys.MySQL, Data.DB, FireDAC.Comp.Client;

type

TDb = class(TDataModule)

FDConnection: TFDConnection;

FDPhysMySQLDriverLink: TFDPhysMySQLDriverLink;

procedure DataModuleCreate(Sender: TObject);

private

{ Private declarations }

public

{ Public declarations }

function Connect: boolean;

end;

var

DB: TDB;

implementation

{%CLASSGROUP 'Vcl.Controls.TControl'}

{$R *.dfm}

function TDB.Connect: boolean;

var

oParams: TStrings;

ErrorInfo: string;

begin

//Драйвер << Можно отдельно прописать, если он не в папке с EXE

// FDPhysMySQLDriverLink1.VendorLib:='C:\MySQLDriver\libmysql.dll';

// либо положить его в c:\windows\SysWow64\

oParams := TStringList.Create;

try

oParams.Add('DataBase=flat_db');

oParams.Add('Password=masterkey');

oParams.Add('User_Name=root');

oParams.Add('Port=3306');

oParams.Add('Server=localhost');

oParams.Add('CharacterSet=utf8');

// oParams.Add('Pooled=true');

FDConnection.Params.Assign(oParams);

FDConnection.DriverName := 'MySQL';

//Пробуем подключиться

try

FDConnection.Connected := true;

if FDConnection.Connected then

begin

Result := true;

// showmessage('Connected');

end

else

Result := false;

except

on E: EFDDBEngineException do

case E.Kind of

ekUserPwdInvalid:

// user name or password are incorrect

raise Exception.Create('DBConnection Error. User name or password are incorrect' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);

ekUserPwdExpired:

raise Exception.Create('DBConnection Error. User password is expired' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);

ekServerGone:

raise Exception.Create('DBConnection Error. DBMS is not accessible due to some reason' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);

else // other issues

raise Exception.Create('DBConnection Error. UnknownMistake' + #13#10 + #13#10 + E.ClassName + ' поднята ошибка, с сообщением : ' + E.Message);

end;

on E: Exception do

raise Exception.Create(E.ClassName + ' поднята ошибка, с сообщением : ' + #13#10 + #13#10 + E.Message);

end;

finally

FreeAndNil(oParams);

end;

procedure TDb.DataModuleCreate(Sender: TObject);

begin

Connect();

end;

end.

Экземпляр TDb объявлен как глобальный. В случае работы с потоками, лучше создавать экземпляр TDb для каждого потока и уничтожать внутри потока, когда он уже не нужен (из опыта).

Просмотр списка всех квартир с показаниями, актуальными на сегодня. Будет плюсом применение фильтров (по дому/по улице).

В основе данной таблицы лежит следующий запрос

select
s.name as streetName,
hn.name as houseNumberName,
f.id as flatId,
f.flatNumber,
(select value from counterdata 
where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as lastCounterDataValue,
(select measureDateTime from counterdata 
where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as measureDateTime,
(select serialNumber from counters where id=f.counters_id) as serialNumber
from flats f,housenumbers hn, streets s
where 
hn.streets_id=s.id
and f.houseNumbers_id=hn.id

select

s.name as streetName,

hn.name as houseNumberName,

f.id as flatId,

f.flatNumber,

(select value from counterdata

where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as lastCounterDataValue,

(select measureDateTime from counterdata

where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as measureDateTime,

(select serialNumber from counters where id=f.counters_id) as serialNumber

from flats f,housenumbers hn, streets s

where

hn.streets_id=s.id

and f.houseNumbers_id=hn.id

Сам DBGrid находится на фрэйме

Фрэйм подключается к соответствующей вкладке TPageControl на главной форме

procedure TMainForm.FormCreate(Sender: TObject);
var
  db: TDb;
begin
  //flatsFrame
  db := Tdb.Create(Self);
  FlatsFrame := TFlatsFrame.Create(Self);
  FlatsFrame.Parent := tsFlats;
  FlatsFrame.Align := alClient;
  FlatsFrame.Init;
  FlatsFrame.Show();
  //countersFrame
  CountersFrame := TCountersFrame.Create(Self);
  CountersFrame.Parent := tsCounters;
  CountersFrame.Align := alClient;
  CountersFrame.Init;
  CountersFrame.Show();
  //
  FLDSLogger := TLDSLogger.Create(logFileName);
end;

procedure TMainForm.FormCreate(Sender: TObject);

var

db: TDb;

begin

//flatsFrame

db := Tdb.Create(Self);

FlatsFrame := TFlatsFrame.Create(Self);

FlatsFrame.Parent := tsFlats;

FlatsFrame.Align := alClient;

FlatsFrame.Init;

FlatsFrame.Show();

//countersFrame

CountersFrame := TCountersFrame.Create(Self);

CountersFrame.Parent := tsCounters;

CountersFrame.Align := alClient;

CountersFrame.Init;

CountersFrame.Show();

FLDSLogger := TLDSLogger.Create(logFileName);

end;

После создания экземпляра TFlatsFrame происходит заполнение данными следующим образом

procedure TFlatsFrame.Init;
begin
  FDConnectionTemp.Connected := false;
  with qFlats do
  begin
    Connection := Db.FDConnection;
    Disconnect();
    Open();
  end;
  with qStreets do
  begin
    Connection := Db.FDConnection;
    CachedUpdates := true;
    Disconnect();
    Open();
    cbStreets.KeyValue := qStreets.FieldByName('id').AsInteger;
  end;
  with qHouseNumbers do
  begin
    Connection := Db.FDConnection;
    CachedUpdates := true;
    params.ParamValues['streets_id'] := qStreets.FieldByName('id').AsInteger;
    Disconnect();
    Open();
    cbHouseNumbers.KeyValue := qHouseNumbers.FieldByName('id').AsInteger;
  end;
end;

procedure TFlatsFrame.Init;

begin

FDConnectionTemp.Connected := false;

with qFlats do

begin

Connection := Db.FDConnection;

Disconnect();

Open();

end;

with qStreets do

begin

Connection := Db.FDConnection;

CachedUpdates := true;

Disconnect();

Open();

cbStreets.KeyValue := qStreets.FieldByName('id').AsInteger;

end;

with qHouseNumbers do

begin

Connection := Db.FDConnection;

CachedUpdates := true;

params.ParamValues['streets_id'] := qStreets.FieldByName('id').AsInteger;

Disconnect();

Open();

cbHouseNumbers.KeyValue := qHouseNumbers.FieldByName('id').AsInteger;

end;

Поскольку списки улиц и номеров домов у нас взаимосвязаны, то в момент обновления списка улиц, нам нужно, чтобы обновлялись дома, сделаем это так

procedure TFlatsFrame.cbStreetsCloseUp(Sender: TObject);
begin
  with qHouseNumbers do
  begin
    Connection := Db.FDConnection;
    CachedUpdates := true;
    params.ParamValues['streets_id'] := qStreets.FieldByName('id').AsInteger;
    Disconnect();
    Open();
    cbHouseNumbers.KeyValue := qHouseNumbers.FieldByName('id').AsInteger;
  end;
end;

procedure TFlatsFrame.cbStreetsCloseUp(Sender: TObject);

begin

with qHouseNumbers do

begin

Connection := Db.FDConnection;

CachedUpdates := true;

params.ParamValues['streets_id'] := qStreets.FieldByName('id').AsInteger;

Disconnect();

Open();

cbHouseNumbers.KeyValue := qHouseNumbers.FieldByName('id').AsInteger;

end;

Фильтр оформим в виде такого же запроса, с добавлением фильтрующих параметров, а именно:

select
s.name as streetName,
hn.name as houseNumberName,
f.id as flatId,
f.flatNumber,
(select value from counterdata 
where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as lastCounterDataValue,
(select measureDateTime from counterdata 
where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as measureDateTime,
(select serialNumber from counters where id=f.counters_id) as serialNumber
from flats f,housenumbers hn, streets s
where 
hn.streets_id=s.id
and f.houseNumbers_id=hn.id
and s.id=:street_id
and hn.id=:houseNumber_id

select

s.name as streetName,

hn.name as houseNumberName,

f.id as flatId,

f.flatNumber,

(select value from counterdata

where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as lastCounterDataValue,

(select measureDateTime from counterdata

where id = (SELECT id FROM flat_db.counterdata where counters_id=f.counters_id order by id desc limit 1)) as measureDateTime,

(select serialNumber from counters where id=f.counters_id) as serialNumber

from flats f,housenumbers hn, streets s

where

hn.streets_id=s.id

and f.houseNumbers_id=hn.id

and s.id=:street_id

and hn.id=:houseNumber_id

Соответственно фильтрацию на фрэйме проведем вот так

procedure TFlatsFrame.bFilterClick(Sender: TObject);
begin
  with qFlatsFilter do
  begin
    Connection := DB.FDConnection;
    CachedUpdates := true;
    params.ParamValues['street_id'] := qStreets.FieldByName('id').AsInteger;
    params.ParamValues['houseNumber_id'] := qHouseNumbers.FieldByName('id').AsInteger;
    Disconnect();
    Open();
  end;
  DSFlats.DataSet := qFlatsFilter;
end;

procedure TFlatsFrame.bFilterClick(Sender: TObject);

begin

with qFlatsFilter do

begin

Connection := DB.FDConnection;

CachedUpdates := true;

params.ParamValues['street_id'] := qStreets.FieldByName('id').AsInteger;

params.ParamValues['houseNumber_id'] := qHouseNumbers.FieldByName('id').AsInteger;

Disconnect();

Open();

end;

DSFlats.DataSet := qFlatsFilter;

end;

Ключевой момент в том, здесь, что мы просто подменяем датасеты.

Сбросим фильтр аналогично

procedure TFlatsFrame.bFlushFilterClick(Sender: TObject);
begin
  qFlats.Disconnect();
  qFlats.Open();
  DSFlats.DataSet := qFlats;
end;

procedure TFlatsFrame.bFlushFilterClick(Sender: TObject);

begin

qFlats.Disconnect();

qFlats.Open();

DSFlats.DataSet := qFlats;

end;

Вывод списка счетчиков для выбранного дома, для которых требуется поверка.

Для того, чтобы выводить счетчики на поверку, нам нужно сначала просто их вывести целиком, сделаем это так…

В основе будет лежать следующий запрос

SELECT
c.id,
c.serialNumber,
c.creationDateTime,
c.lastCheckDate,
c.nextCheckDate,
#street
(select name from streets where id=(
select streets_id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id))) as streetName,
#houseNumber
(select name from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)) as houseNumberName,
#flatNumber
(select flatNumber from flats where counters_id=c.id) as flatNumber,
# value
(SELECT value FROM flat_db.counterdata where counters_id=c.id order by id desc limit 1) as value,
/*
(SELECT value FROM flat_db.counterdata where counters_id=c.id
and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats 
where id=(select flatNumber from flats where counters_id=c.id)

))) as value,
*/
# measureDateTime
(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id order by id desc limit 1) as measureDateTime
/*
(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id
and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats 
where id=(select flatNumber from flats where counters_id=c.id)
))) as measureDateTime
*/
FROM 
counters c
order by id desc

SELECT

c.id,

c.serialNumber,

c.creationDateTime,

c.lastCheckDate,

c.nextCheckDate,

#street

(select name from streets where id=(

select streets_id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id))) as streetName,

#houseNumber

(select name from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)) as houseNumberName,

#flatNumber

(select flatNumber from flats where counters_id=c.id) as flatNumber,

# value

(SELECT value FROM flat_db.counterdata where counters_id=c.id order by id desc limit 1) as value,

(SELECT value FROM flat_db.counterdata where counters_id=c.id

and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats

where id=(select flatNumber from flats where counters_id=c.id)

))) as value,

# measureDateTime

(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id order by id desc limit 1) as measureDateTime

(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id

and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats

where id=(select flatNumber from flats where counters_id=c.id)

))) as measureDateTime

FROM

counters c

order by id desc

Соответственно, фильтрующий запрос будет аналогичным, но с дополнительными фильтрующими параметрами

SELECT
c.id, 
c.serialNumber,
c.creationDateTime,
c.lastCheckDate,
c.nextCheckDate,
#street
(select name from streets where id=(
select streets_id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id))) as streetName,
#houseNumber
(select name from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)) as houseNumberName,
#flatNumber
(select flatNumber from flats where counters_id=c.id) as flatNumber,
# value
(SELECT value FROM flat_db.counterdata where counters_id=c.id
and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats 
where id=(select flatNumber from flats where counters_id=c.id)
))) as value,
# measureDateTime
(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id
and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats 
where id=(select flatNumber from flats where counters_id=c.id)
))) as measureDateTime
FROM 
counters c
where 
date(c.NextCheckDate)<=date(Now())
and
(select id from streets where id=(
select streets_id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)))=:streetId
and
(select id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)) =:houseNumberId
order by id desc

SELECT

c.id,

c.serialNumber,

c.creationDateTime,

c.lastCheckDate,

c.nextCheckDate,

#street

(select name from streets where id=(

select streets_id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id))) as streetName,

#houseNumber

(select name from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)) as houseNumberName,

#flatNumber

(select flatNumber from flats where counters_id=c.id) as flatNumber,

# value

(SELECT value FROM flat_db.counterdata where counters_id=c.id

and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats

where id=(select flatNumber from flats where counters_id=c.id)

))) as value,

# measureDateTime

(SELECT measureDateTime FROM flat_db.counterdata where counters_id=c.id

and id=( select Max(Id) from counterdata where counters_id=(Select counters_id from flats

where id=(select flatNumber from flats where counters_id=c.id)

))) as measureDateTime

FROM

counters c

where

date(c.NextCheckDate)<=date(Now())

and

(select id from streets where id=(

select streets_id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)))=:streetId

and

(select id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)) =:houseNumberId

order by id desc

Ключевой здесь является секция where посмотрим на нее более внимательно

where 
date(c.NextCheckDate)<=date(Now())
and
(select id from streets where id=(
select streets_id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)))=:streetId
and
(select id from housenumbers where 
id=(select houseNumbers_id from flats where counters_id=c.id)) =:houseNumberId
order by id desc

where

date(c.NextCheckDate)<=date(Now())

and

(select id from streets where id=(

select streets_id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)))=:streetId

and

(select id from housenumbers where

id=(select houseNumbers_id from flats where counters_id=c.id)) =:houseNumberId

order by id desc

Следующая дата поверки должна быть меньше либо равна текущему времени, а также соответствующие улица и номер дома.

Ввод показаний для счетчика в квартире (показания не должны убывать).

Показания должны не убывать.

Для этого создадим новую форму

Реализуем механизм добавления новых показаний следующим образом

procedure TCountersFrame.bAddNewDataClick(Sender: TObject);
var
  f: TAddNewCounterData;
  newValue: double;
begin
  f := TAddNewCounterData.Create(Self);
  try
    f.CounterID := qCounters.FieldByName('id').AsInteger;
    f.Init();
    if f.ShowModal = mrOk then
    begin
      newValue := TCommon.GetDoubleFromIntAndFrac(f.seInt.Value, f.seFrac.Value);
       // insert new record
      if (not TCountersController.IsNewValueOk(f.CounterID, newValue)) then
      begin
        Application.MessageBox('Новое значение должно быть больше предыдущего', //
          'Сообщение системы', MB_OK + MB_ICONSTOP);
        exit;
      end;
      TCountersController.InsertCounterData(f.CounterID, newValue);
      qCounters.Disconnect();
      qCounters.Open();
    end;
  finally
    f.Free();
  end;
end;

procedure TCountersFrame.bAddNewDataClick(Sender: TObject);

var

f: TAddNewCounterData;

newValue: double;

begin

f := TAddNewCounterData.Create(Self);

try

f.CounterID := qCounters.FieldByName('id').AsInteger;

f.Init();

if f.ShowModal = mrOk then

begin

newValue := TCommon.GetDoubleFromIntAndFrac(f.seInt.Value, f.seFrac.Value);

// insert new record

if (not TCountersController.IsNewValueOk(f.CounterID, newValue)) then

begin

Application.MessageBox('Новое значение должно быть больше предыдущего', //

'Сообщение системы', MB_OK + MB_ICONSTOP);

exit;

end;

TCountersController.InsertCounterData(f.CounterID, newValue);

qCounters.Disconnect();

qCounters.Open();

end;

finally

f.Free();

end;

Как видно, здесь используется классовый метод контроллера, посмотрим на него

class function TCountersController.IsNewValueOk(aCounterId: integer; aNewValue: double): boolean;

  function getLastValue(): double;
  var
    q: TFDQuery;
    l: TLDSLogger;
  begin
    q := TFDQuery.Create(nil);
    l := TLDSLogger.Create(logFileName);
    try
      try
        with q do
        begin
          Connection := Db.FDConnection;
          sql.Text := 'SELECT * FROM flat_db.counterdata where counters_id=:counters_id order by id desc limit 1;';
          params.ParamValues['counters_id'] := aCounterId;
          Disconnect();
          Open();
          if (IsEmpty) or (FieldByName('value').IsNull) then
            result := -1
          else
            result := FieldByName('value').AsFloat;
          Close();
        end;
      except
        on E: Exception do
          l.LogStr('error=' + E.Message, tlpError);
      end;
    finally
      q.Free();
      l.Free();
    end;
  end;

var
  lastValue: Double;
begin
// get last value
  lastValue := getLastValue();
  if lastValue = -1 then
    result := true; // means no values in db
  result := (aNewValue > lastValue);
end;

class function TCountersController.IsNewValueOk(aCounterId: integer; aNewValue: double): boolean;

function getLastValue(): double;

var

q: TFDQuery;

l: TLDSLogger;

begin

q := TFDQuery.Create(nil);

l := TLDSLogger.Create(logFileName);

try

with q do

begin

Connection := Db.FDConnection;

sql.Text := 'SELECT * FROM flat_db.counterdata where counters_id=:counters_id order by id desc limit 1;';

params.ParamValues['counters_id'] := aCounterId;

Disconnect();

Open();

if (IsEmpty) or (FieldByName('value').IsNull) then

result := -1

else

result := FieldByName('value').AsFloat;

Close();

end;

except

on E: Exception do

l.LogStr('error=' + E.Message, tlpError);

end;

finally

q.Free();

l.Free();

end;

var

lastValue: Double;

begin

// get last value

lastValue := getLastValue();

if lastValue = -1 then

result := true; // means no values in db

result := (aNewValue > lastValue);

end;

Также используется методы из модуля uCommon, этот модуль создан для методов, которые используются больше, чем в одном модуле и являются общими.

Добавление нового счетчика и ассоциация с квартирой

procedure TCountersFrame.bAddClick(Sender: TObject);
var
  c: TCounter;
  f: TAddEditCounterForm;
  fc: TFLatCounter;
  fcChosen: TFLatCounter;
  counterId: Integer;
  flatChosen: TFlat;
begin
  flatChosen := TFlat.Create(Self);
  f := TAddEditCounterForm.Create(Self);
  c := TCounter.Create(Self);
  try
    f.Init();
    with f do
    begin
      // update view
     // eSerialNumber.Text := fc.Counter.SerialNumber;
      dtpLast.DateTime := Now(); // fc.Counter.LastCheckDate;
      dtpNext.DateTime := Now(); //fc.Counter.NextCheckDate;
      if f.ShowModal = mrOk then
      begin
      // updating model
        c.SerialNumber := eSerialNumber.Text;
        c.LastCheckDate := dtpLast.DateTime;
        c.NextCheckDate := dtpNext.DateTime;
        counterId := TCountersController.InsertCounter(c);
        c.Id := counterId;
        //
        if f.cbIsAssociateToFlat.Checked then
        begin
          fc := TCountersController.GetAssociationToFlat(counterId);
          flatChosen.Id := f.qFlatNumbers.FieldByName('id').AsInteger;
          fcChosen := TCountersController.GetAssociationToCounter(flatChosen);
          TCountersController.AssociateFlatToCounter(fc, fcChosen);
        // associating flat and counter
        end;
      end;
      qCounters.Disconnect();
      qCounters.Open();
      DSCounters.DataSet := qCounters;
    end;
  finally
    c.Free();
    flatChosen.Free();
    fc.Free();
    fcChosen.Free();
    f.Free();  
  end;
end;

procedure TCountersFrame.bAddClick(Sender: TObject);

var

c: TCounter;

f: TAddEditCounterForm;

fc: TFLatCounter;

fcChosen: TFLatCounter;

counterId: Integer;

flatChosen: TFlat;

begin

flatChosen := TFlat.Create(Self);

f := TAddEditCounterForm.Create(Self);

c := TCounter.Create(Self);

try

f.Init();

with f do

begin

// update view

// eSerialNumber.Text := fc.Counter.SerialNumber;

dtpLast.DateTime := Now(); // fc.Counter.LastCheckDate;

dtpNext.DateTime := Now(); //fc.Counter.NextCheckDate;

if f.ShowModal = mrOk then

begin

// updating model

c.SerialNumber := eSerialNumber.Text;

c.LastCheckDate := dtpLast.DateTime;

c.NextCheckDate := dtpNext.DateTime;

counterId := TCountersController.InsertCounter(c);

c.Id := counterId;

if f.cbIsAssociateToFlat.Checked then

begin

fc := TCountersController.GetAssociationToFlat(counterId);

flatChosen.Id := f.qFlatNumbers.FieldByName('id').AsInteger;

fcChosen := TCountersController.GetAssociationToCounter(flatChosen);

TCountersController.AssociateFlatToCounter(fc, fcChosen);

// associating flat and counter

end;

qCounters.Disconnect();

qCounters.Open();

DSCounters.DataSet := qCounters;

end;

finally

c.Free();

flatChosen.Free();

fc.Free();

fcChosen.Free();

f.Free();

end;

Самым интересным здесь является метод AssociateFlatToCounter из контроллера, посмотрим на него более внимательно. Здесь у нас возможны 3 ситуации

// 1) В выбранной квартире есть свой ассоциированный счетчик,
// Текущий счетчик ассоциирован с квартирой
// поменять их местами

// 2) Текущий счетчик не ассоциирован с квартирой,
// В выбранной квартире уже есть счетчик, тогда предложить
// перезаписать

//3) Текущий счетчик не ассоциирован с квартирой,
//В выбранной квартире нет счетчика, тогда просто записываем

class procedure TCountersController.AssociateFlatToCounter(var aCounter: TFLatCounter; var aChosenFlat: TFLatCounter);
var
  q: TFDQuery;
  l: TLDSLogger;
  h: TCountersUpdateHistory;
  h2: TCountersUpdateHistory;
begin
  h := TCountersUpdateHistory.Create(nil);
  h2 := TCountersUpdateHistory.Create(nil);
  try
  // 1) В выбранной квартире есть свой ассоциированный счетчик,
  //  Текущий счетчик ассоциирован с квартирой
  // поменять их местами
    if (aChosenFlat.IsAssociated) and (aCounter.IsAssociated) then
    begin
    // ask in dialog
      ExchangeCountersInFlats(aCounter.Counter.Id, aChosenFlat.Counter.Id);
    // write history

      h.CounterOld := aCounter.Counter;
      h.CounterNew := aChosenFlat.Counter;
      h.Flat.Id := aCounter.Flat.Id;
      TCountersController.LogAssociatonFlatToCounter(h);
    //
      h2.CounterOld := aChosenFlat.Counter;
      h2.Flat.Id := aChosenFlat.Flat.Id;
      h2.CounterNew := aCounter.Counter;
      TCountersController.LogAssociatonFlatToCounter(h2);

    end
  // 2) Текущий счетчик не ассоциирован с квартирой,
  // В выбранной квартире уже есть счетчик, тогда предложить
  // перезаписать
    else if (aChosenFlat.IsAssociated) and (not aCounter.IsAssociated) then
    begin
    // ask in dialog
      UpdateCounterInFlat(aCounter.Counter.Id, aChosenFlat.Flat.Id);
  // write history
      h.CounterOld := aChosenFlat.Counter;
      h.Flat.Id := aChosenFlat.Flat.Id;
      h.CounterNew := aCounter.Counter;
      TCountersController.LogAssociatonFlatToCounter(h);
    end
   //3) Текущий счетчик не ассоциирован с квартирой,
   //В выбранной квартире нет счетчика, тогда просто записываем
    else if (not aChosenFlat.IsAssociated) and (not aCounter.IsAssociated) then
    begin
      UpdateCounterInFlat(aCounter.Counter.Id, aChosenFlat.Flat.Id);
      h.CounterOld.Free();
      h.CounterOld := nil;
      h.Flat.Id := aChosenFlat.Flat.Id;
      h.CounterNew := aCounter.Counter;
      TCountersController.LogAssociatonFlatToCounter(h);
    end;
  finally
    h.Free();
    h2.Free();
  end;
end;

class procedure TCountersController.AssociateFlatToCounter(var aCounter: TFLatCounter; var aChosenFlat: TFLatCounter);

var

q: TFDQuery;

l: TLDSLogger;

h: TCountersUpdateHistory;

h2: TCountersUpdateHistory;

begin

h := TCountersUpdateHistory.Create(nil);

h2 := TCountersUpdateHistory.Create(nil);

try

// 1) В выбранной квартире есть свой ассоциированный счетчик,

// Текущий счетчик ассоциирован с квартирой

// поменять их местами

if (aChosenFlat.IsAssociated) and (aCounter.IsAssociated) then

begin

// ask in dialog

ExchangeCountersInFlats(aCounter.Counter.Id, aChosenFlat.Counter.Id);

// write history

h.CounterOld := aCounter.Counter;

h.CounterNew := aChosenFlat.Counter;

h.Flat.Id := aCounter.Flat.Id;

TCountersController.LogAssociatonFlatToCounter(h);

h2.CounterOld := aChosenFlat.Counter;

h2.Flat.Id := aChosenFlat.Flat.Id;

h2.CounterNew := aCounter.Counter;

TCountersController.LogAssociatonFlatToCounter(h2);

end

// 2) Текущий счетчик не ассоциирован с квартирой,

// В выбранной квартире уже есть счетчик, тогда предложить

// перезаписать

else if (aChosenFlat.IsAssociated) and (not aCounter.IsAssociated) then

begin

// ask in dialog

UpdateCounterInFlat(aCounter.Counter.Id, aChosenFlat.Flat.Id);

// write history

h.CounterOld := aChosenFlat.Counter;

h.Flat.Id := aChosenFlat.Flat.Id;

h.CounterNew := aCounter.Counter;

TCountersController.LogAssociatonFlatToCounter(h);

end

//3) Текущий счетчик не ассоциирован с квартирой,

//В выбранной квартире нет счетчика, тогда просто записываем

else if (not aChosenFlat.IsAssociated) and (not aCounter.IsAssociated) then

begin

UpdateCounterInFlat(aCounter.Counter.Id, aChosenFlat.Flat.Id);

h.CounterOld.Free();

h.CounterOld := nil;

h.Flat.Id := aChosenFlat.Flat.Id;

h.CounterNew := aCounter.Counter;

TCountersController.LogAssociatonFlatToCounter(h);

end;

finally

h.Free();

h2.Free();

end;

При каждой ассоциации, у нас пишется история замены счетчиков для квартиры, в которой происходит эта замена при помощи метода LogAssociatonFlatToCounter

class procedure TCountersController.LogAssociatonFlatToCounter(var aModel: TCountersUpdateHistory);
var
  q: TFDQuery;
  l: TLDSLogger;
begin
  q := TFDQuery.Create(nil);
  l := TLDSLogger.Create(logFileName);
  try
    try
      with q do
      begin
        Connection := Db.FDConnection;
        if (aModel.CounterOld = nil) then // 1st add of counter to flat
        begin
          sql.Text := 'INSERT INTO `flat_db`.`countersupdatehistory` ' + //
            '(`flats_id`, `counters_id`) ' + //
            'VALUES (:flats_id, :counters_id);'; //
          params.ParamValues['flats_id'] := aModel.Flat.Id;
          params.ParamValues['counters_id'] := aModel.CounterNew.Id;
        end
        else
        begin
          sql.Text := 'INSERT INTO `flat_db`.`countersupdatehistory` ' + //
            '(`counterOldValue`, `flats_id`, `counters_id`, `countersOld_id`) ' + //
            'VALUES (:counterOldValue, :flats_id, :counters_id, :countersOld_id);'; //
          params.ParamValues['countersOld_id'] := aModel.CounterOld.Id;
          params.ParamValues['counterOldValue'] := aModel.CounterOld.CounterDataLastRecord.Value;
          params.ParamValues['flats_id'] := aModel.Flat.Id;
          params.ParamValues['counters_id'] := aModel.CounterNew.Id;
        end;
        ExecSQL();
      end;
    except
      on E: Exception do
        l.LogStr('error=' + E.Message, tlpError);
    end;
  finally
    q.Free();
    l.Free();
  end;
end;

class procedure TCountersController.LogAssociatonFlatToCounter(var aModel: TCountersUpdateHistory);

var

q: TFDQuery;

l: TLDSLogger;

begin

q := TFDQuery.Create(nil);

l := TLDSLogger.Create(logFileName);

try

with q do

begin

Connection := Db.FDConnection;

if (aModel.CounterOld = nil) then // 1st add of counter to flat

begin

sql.Text := 'INSERT INTO `flat_db`.`countersupdatehistory` ' + //

'(`flats_id`, `counters_id`) ' + //

'VALUES (:flats_id, :counters_id);'; //

params.ParamValues['flats_id'] := aModel.Flat.Id;

params.ParamValues['counters_id'] := aModel.CounterNew.Id;

end

else

begin

sql.Text := 'INSERT INTO `flat_db`.`countersupdatehistory` ' + //

'(`counterOldValue`, `flats_id`, `counters_id`, `countersOld_id`) ' + //

'VALUES (:counterOldValue, :flats_id, :counters_id, :countersOld_id);'; //

params.ParamValues['countersOld_id'] := aModel.CounterOld.Id;

params.ParamValues['counterOldValue'] := aModel.CounterOld.CounterDataLastRecord.Value;

params.ParamValues['flats_id'] := aModel.Flat.Id;

params.ParamValues['counters_id'] := aModel.CounterNew.Id;

end;

ExecSQL();

end;

except

on E: Exception do

l.LogStr('error=' + E.Message, tlpError);

end;

finally

q.Free();

l.Free();

end;

Посмотреть историю замен счетчиков для заданной квартиры.

История выглядит следующим образом

SQL код просмотра истории замен счетчиков для квартиры

SELECT 
h.datetime,
c.serialNumber,
(select serialNumber from counters where id=h.countersOld_id) as oldSerialNumber,
(select counterOldValue from counters where id=h.countersOld_id) as oldCounterValue
FROM flat_db.countersupdatehistory h,counters c
where flats_id=:flats_id
and h.counters_id=c.id

SELECT

h.datetime,

c.serialNumber,

(select serialNumber from counters where id=h.countersOld_id) as oldSerialNumber,

(select counterOldValue from counters where id=h.countersOld_id) as oldCounterValue

FROM flat_db.countersupdatehistory h,counters c

where flats_id=:flats_id

and h.counters_id=c.id

Редактирование счетчика проводится аналогично

Код редактирования счетчика и его ассоциации с квартирой

procedure TCountersFrame.bEditClick(Sender: TObject);
var
  c: TCounter;
  f: TAddEditCounterForm;
  fc: TFLatCounter;
  fcChosen: TFLatCounter;
  anotherCounterId: integer;
  counterId: Integer;
  flatChosen: TFlat;
begin
  flatChosen := TFlat.Create(Self);
  f := TAddEditCounterForm.Create(Self);
  try
    f.Init();
    // проверяем ассоциацию счетчика с квартирой
    counterId := qCounters.FieldByName('id').AsInteger;
    fc := TCountersController.GetAssociationToFlat(counterId);
    if (not fc.IsAssociated) then
      fc.Counter := TCountersController.GetCounter(counterId); // if no association
    if fc.IsAssociated then
    begin
      // Заполняем списки
      f.cbIsAssociateToFlat.Checked := true;
      f.cbStreets.KeyValue := fc.Street.Id;
      // дома
      f.LoadHouseNumbersInList(fc.Street.Id);
      f.cbHouseNumbers.KeyValue := fc.HouseNumber.Id;
      // квартиры
      f.LoadFlatsInList(fc.HouseNumber.Id);
      f.cbFlatNumbers.KeyValue := fc.Flat.Id;
    end;
    with f do
    begin
      // update view
      eSerialNumber.Text := fc.Counter.SerialNumber;
      dtpLast.DateTime := fc.Counter.LastCheckDate;
      dtpNext.DateTime := fc.Counter.NextCheckDate;
      if f.ShowModal = mrOk then
      begin
      // updating model from view
        fc.Counter.SerialNumber := eSerialNumber.Text;
        fc.Counter.LastCheckDate := dtpLast.DateTime;
        fc.Counter.NextCheckDate := dtpNext.DateTime;
        TCountersController.Update(fc.Counter, qCounters.FieldByName('id').AsInteger);
        // associating flat and counter
        if f.cbIsAssociateToFlat.Checked then
        begin
          flatChosen.Id := f.qFlatNumbers.FieldByName('id').AsInteger;
          fcChosen := TCountersController.GetAssociationToCounter(flatChosen);
          TCountersController.AssociateFlatToCounter(fc, fcChosen);
        end
        else if (not f.cbIsAssociateToFlat.Checked) and (fc.IsAssociated) then
        begin
          // brake association
          TCountersController.BrakeAssociation(fc);
        end;
      end;
      qCounters.Disconnect();
      qCounters.Open();
      DSCounters.DataSet := qCounters;
    end;
  finally
    flatChosen.Free();
    f.Free();
    fc.Free();
    fcChosen.Free();
  end;
end;

procedure TCountersFrame.bEditClick(Sender: TObject);

var

c: TCounter;

f: TAddEditCounterForm;

fc: TFLatCounter;

fcChosen: TFLatCounter;

anotherCounterId: integer;

counterId: Integer;

flatChosen: TFlat;

begin

flatChosen := TFlat.Create(Self);

f := TAddEditCounterForm.Create(Self);

try

f.Init();

// проверяем ассоциацию счетчика с квартирой

counterId := qCounters.FieldByName('id').AsInteger;

fc := TCountersController.GetAssociationToFlat(counterId);

if (not fc.IsAssociated) then

fc.Counter := TCountersController.GetCounter(counterId); // if no association

if fc.IsAssociated then

begin

// Заполняем списки

f.cbIsAssociateToFlat.Checked := true;

f.cbStreets.KeyValue := fc.Street.Id;

// дома

f.LoadHouseNumbersInList(fc.Street.Id);

f.cbHouseNumbers.KeyValue := fc.HouseNumber.Id;

// квартиры

f.LoadFlatsInList(fc.HouseNumber.Id);

f.cbFlatNumbers.KeyValue := fc.Flat.Id;

end;

with f do

begin

// update view

eSerialNumber.Text := fc.Counter.SerialNumber;

dtpLast.DateTime := fc.Counter.LastCheckDate;

dtpNext.DateTime := fc.Counter.NextCheckDate;

if f.ShowModal = mrOk then

begin

// updating model from view

fc.Counter.SerialNumber := eSerialNumber.Text;

fc.Counter.LastCheckDate := dtpLast.DateTime;

fc.Counter.NextCheckDate := dtpNext.DateTime;

TCountersController.Update(fc.Counter, qCounters.FieldByName('id').AsInteger);

// associating flat and counter

if f.cbIsAssociateToFlat.Checked then

begin

flatChosen.Id := f.qFlatNumbers.FieldByName('id').AsInteger;

fcChosen := TCountersController.GetAssociationToCounter(flatChosen);

TCountersController.AssociateFlatToCounter(fc, fcChosen);

end

else if (not f.cbIsAssociateToFlat.Checked) and (fc.IsAssociated) then

begin

// brake association

TCountersController.BrakeAssociation(fc);

end;

qCounters.Disconnect();

qCounters.Open();

DSCounters.DataSet := qCounters;

end;

finally

flatChosen.Free();

f.Free();

fc.Free();

fcChosen.Free();

end;

Проект сделал примерно за 5 часов. С уважением, Пантелеев Станислав.

Delphi. Тестовое задание. Проект квартира

База данных

Приложение

Подключение к базе данных

Просмотр списка всех квартир с показаниями, актуальными на сегодня. Будет плюсом применение фильтров (по дому/по улице).

Вывод списка счетчиков для выбранного дома, для которых требуется поверка.

Ввод показаний для счетчика в квартире (показания не должны убывать).

Добавление нового счетчика и ассоциация с квартирой

Посмотреть историю замен счетчиков для заданной квартиры.

Редактирование счетчика проводится аналогично

MY PROJECTS

Recent Posts

Categories