【共享充电宝】express获取首页门店逻辑

写作背景，在发现大部分的时间都去哪了。为什么，整天做重复的工作。而不是有效的。问题出现在哪呢。

mysql.js

const mysql = require('mysql')

const conn = mysql.createConnection({
    host: '127.0.0.1',
    user: 'mini',
    password: '15aZw9fNKjMz9jSKrXN6',
    database: 'battery',
    port: 3306
})

try {
    conn.connect(function(err){
        if(err) {console.log("连接失败！")};
        console.log("连接成功,当前连接线程ID："+conn.threadId);
    })
} catch(e) {}

module.exports = conn

var express = require('express');
var router = express.Router();
const conn = require('../config/mysql');

router.get('/detail', async (req, res, next) => {
  try {
    const query = req.query

    let where = `store.store_id=${query.id}`

    const sql = `select * from store INNER JOIN device ON device.device_id = store.device_id 
    WHERE ${where} LIMIT 0, 1`

    conn.query(sql, function (err, rows, fields) {
      if (err)
        res.send({ "result": "fail", "error_info": err })
      else
        res.send({ "result": "ok", "data": rows[0] })
    })
  } catch (err) {
    console.log(err)
    next(err)
  }
})

router.get('/list', async (req, res, next) => {
  try {
    const query = req.query

    let where = "1 = 1"
    if (1 == query.type) {
      where += " AND device.use_count > 0"
    }

    if (2 == query.type) {
      where += " AND device.return_count > 0"
    }

    if (query.province_code) {
      where += ` AND province_code='${query.province_code}'`
    }

    if (query.city_code) {
      where += ` AND city_code='${query.city_code}'`
    }

    if (query.district_code) {
      where += ` AND district_code='${query.district_code}'`
    }

    const offset = query.offset || 0
    const limit = query.limit || 1000

    const sql = `select *, 
  (
      6371 * acos(
          cos( radians(${query.latitude}) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(${query.longitude}) ) + sin( radians(${query.latitude}) ) * sin( radians( latitude ) )
      )
  ) AS distance
  from store INNER JOIN device ON device.device_id = store.device_id 
  WHERE ${where} 
  ORDER BY DISTANCE ASC LIMIT ${offset}, ${limit}`
    conn.query(sql, function (err, rows, fields) {
      if (err)
        res.send({ "result": "fail", "error_info": err })
      else
        res.send({ "result": "ok", "data": rows })
    })
  } catch (err) {
    console.log(err)
    next(err)
  }
})

router.post('/add', async (req, res, next) => {
  try {
    const query = req.body

    if (!query.full_address) {
      res.send({ "result": "fail", "error_info": "请选择地址" })
    }

    if (!query.store_name) {
      res.send({ "result": "fail", "error_info": "店铺名称不能为空" })
    }

    conn.query(`INSERT INTO store (store_name, store_image, full_address, latitude, longitude, province_code, city_code, district_code) 
    VALUES ('${query.store_name}', '${query.store_image}', '${query.full_address}', '${query.latitude}', '${query.longitude}'
    , '${query.province_code}', '${query.city_code}', '${query.district_code}')`, function (err, rows, fields) {
      res.send({ "result": "ok" })
    })
  } catch (err) {
    console.log(err)
    next(err)
  }
})



module.exports = router;

数据库

-- --------------------------------------------------------
-- 主机:                           127.0.0.1
-- 服务器版本:                        5.7.26 - MySQL Community Server (GPL)
-- 服务器操作系统:                      Win32
-- HeidiSQL 版本:                  11.0.0.5919
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!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' */;



-- 导出  表 mini.device 结构
CREATE TABLE IF NOT EXISTS `device` (
  `device_id` int(11) NOT NULL AUTO_INCREMENT,
  `use_count` int(11) DEFAULT NULL,
  `return_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`device_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='设备';

-- 正在导出表  mini.device 的数据：1 rows
DELETE FROM `device`;
/*!40000 ALTER TABLE `device` DISABLE KEYS */;
INSERT INTO `device` (`device_id`, `use_count`, `return_count`) VALUES
    (1, 8, 8);
/*!40000 ALTER TABLE `device` ENABLE KEYS */;

-- 导出  表 mini.order 结构
CREATE TABLE IF NOT EXISTS `order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `start_time` int(11) NOT NULL,
  `end_time` int(11) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `order_amount` decimal(10,2) NOT NULL,
  `pay_amount` decimal(10,2) NOT NULL,
  `order_time` int(11) NOT NULL,
  `pay_time` int(11) NOT NULL,
  `order_sn` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 正在导出表  mini.order 的数据：0 rows
DELETE FROM `order`;
/*!40000 ALTER TABLE `order` DISABLE KEYS */;
/*!40000 ALTER TABLE `order` ENABLE KEYS */;

-- 导出  表 mini.store 结构
CREATE TABLE IF NOT EXISTS `store` (
  `store_id` int(11) NOT NULL AUTO_INCREMENT,
  `store_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `store_image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL,
  `latitude` varchar(90) COLLATE utf8_unicode_ci NOT NULL,
  `longitude` varchar(180) COLLATE utf8_unicode_ci NOT NULL,
  `shop_id` int(11) DEFAULT NULL,
  `device_id` int(11) DEFAULT '1',
  `full_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `province_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `city_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  `district_code` char(2) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`store_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 正在导出表  mini.store 的数据：0 rows
DELETE FROM `store`;
/*!40000 ALTER TABLE `store` DISABLE KEYS */;
INSERT INTO `store` (`store_id`, `store_name`, `store_image`, `create_time`, `update_time`, `latitude`, `longitude`, `shop_id`, `device_id`, `full_address`, `province_code`, `city_code`, `district_code`) VALUES
    (2, '小拼充电宝', '/static/image/home/logo.png', NULL, NULL, '28.00708', '120.63768', NULL, 1, '浙江省温州市瓯海区兴海路86号', '33', '03', '04'),
    (4, '小拼充电宝', '/static/image/home/logo.png', NULL, NULL, '28.00708', '120.63768', NULL, 1, '浙江省温州市瓯海区兴海路86号', '33', '03', '04'),
    (5, '小拼充电宝', '/static/image/home/logo.png', NULL, NULL, '28.00708', '120.63768', NULL, 1, '浙江省温州市瓯海区兴海路86号', '33', '03', '04'),
    (6, '小拼充电宝', '/static/image/home/logo.png', NULL, NULL, '28.00671', '120.638196', NULL, 1, '浙江省温州市瓯海区兴海路29号', '33', '03', '04'),
    (7, '小拼充电宝', '/static/image/home/logo.png', NULL, NULL, '28.007113', '120.638279', NULL, 1, '浙江省温州市瓯海区兴海路将军华府62号', '33', '03', '04');
/*!40000 ALTER TABLE `store` ENABLE KEYS */;

-- 导出  表 mini.user 结构
CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` tinyint(4) NOT NULL,
  `open_id` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `image_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `create_time` int(11) unsigned NOT NULL,
  `update_time` int(11) unsigned NOT NULL,
  `mobile` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- 正在导出表  mini.user 的数据：0 rows
DELETE FROM `user`;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
/*!40000 ALTER TABLE `user` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;