<repo_name>lyy1/PerfectServer<file_sep>/Sources/DB.swift
//
//  DB.swift
//  PerfectTemplate
//
//  Created by lyy on 2017/6/5.
//
//

import Foundation
import PerfectHTTP
import PerfectLib
import MySQL

let Host = "127.0.0.1"
let User = "root"
let Password = ""
enum DB: String {
    case bill = "Bill"
    case checkIn = "CheckIn"
    case courseManager = "CourseManager"
    case sysManage = "SysManage"
}
enum tablesofSysManage: String {
    case bed = "Bed"
    case check = "Checking"
    case medicine = "Medicine"
    case nursing = "Nursing"
    case user = "User"
}
enum tableofBill: String {
    case checkBill = "CheckBill"
    case medicineBill = "MedicineBill"
    case nursingBill = "NursingBill"
}

enum tableofCheckIn: String {
    case patient = "Patient"
}

enum tableofCourseManage: String {
    case advice = "AdviceofDoc"
    case medicalRecord = "Medicalrecord"
    case treatment = "Treatment"
}

class DataBaseManager {
    var mysql: MySQL
    init() {
        mysql = MySQL.init()
        guard mysql.setOption(MySQLOpt.MYSQL_SET_CHARSET_NAME, "utf8") else {
            print("not set")
            return
        }
        guard connectedDataBase() else {
            return
        }
    }
    
    private func connectedDataBase() -> Bool {
        let connected = mysql.connect(host: Host, user: User, password: <PASSWORD>)
        guard connected else {
            print("MySQL连接失败" + mysql.errorMessage())
            return false
        }
        print("MySQL连接成功")
        return true
    }
    
    func mysqlStatement(sql: String) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let successQuery = mysql.query(statement: sql)
        
        guard successQuery else {
            let msg = "SQL失败：\(sql)"
            print(msg)
            return (false, nil, msg)
        }
        return (true, mysql.storeResults(), nil)
    }
    
    func selectAllDataBaseSQL(db: DB, tableName: String) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let connected = mysql.selectDatabase(named: db.rawValue)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        let SQL = "SELECT * FROM \(tableName)"
        return mysqlStatement(sql: SQL)
    }
    
    func selectLatestDataBaseSQL(db: DB, tableName: String, qty: Int) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?)
    {
        let connected = mysql.selectDatabase(named: db.rawValue)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        
        let SQL = "SELECT * FROM \(tableName) ORDER BY Date DESC LIMIT \(qty)"
        return mysqlStatement(sql: SQL)
    }
    
    func selectDataBaseSQLWhere(db: DB, tableName: String, keyValue: String) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let connected = mysql.selectDatabase(named: db.rawValue)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        let SQL = "SELECT * FROM \(tableName) WHERE \(keyValue)"
        return mysqlStatement(sql: SQL)
    }
    
    func Login(userID: String, pwd: String) -> String{
        let result = selectDataBaseSQLWhere(db:DB.sysManage, tableName: tablesofSysManage.user.rawValue, keyValue:"UserID ='\(userID)'")
        guard result.success else {
            print("查询数据失败")
            return ""
        }
        var userInfo = [String: String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            guard row[6] == pwd else {
                return
            }
            userInfo["Name"] = row[1]
            userInfo["Sex"] = row[2]
            userInfo["Age"] = row[3]
            userInfo["Tel"] = row[4]
            userInfo["SectionName"] = row[5]
            userInfo["RightInfo"] = row[7]
        })
    
        let data : NSData! = try? JSONSerialization.data(withJSONObject: userInfo, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlGetUserDataResult() -> String {
        let result = selectAllDataBaseSQL(db:DB.sysManage, tableName: tablesofSysManage.user.rawValue)
        var resultArray = [Dictionary<String, String>]()
        var dic = [String:String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            dic["UserID"] = row[0]
            dic["Name"] = row[1]
            dic["Sex"] = row[2]
            dic["Age"] = row[3]
            dic["Tel"] = row[4]
            dic["SectionName"] = row[5]
            dic["RightInfo"] = row[7]
            resultArray.append(dic)
        })
        
        let data : NSData! = try? JSONSerialization.data(withJSONObject: resultArray, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlGetBedDataResult() -> String {
        let result = selectAllDataBaseSQL(db:DB.sysManage, tableName: tablesofSysManage.bed.rawValue)
        var resultArray = [Dictionary<String, String>]()
        var dic = [String:String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            dic["BedNum"] = row[0]
            dic["SectionName"] = row[1]
            dic["RoomNum"] = row[2]
            dic["RoomSize"] = row[3]
            dic["BedPrice"] = row[4]
            dic["BedState"] = row[5]
            resultArray.append(dic)
        })
        
        let data : NSData! = try? JSONSerialization.data(withJSONObject: resultArray, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlGetMedicineDataResult() -> String {
        let result = selectAllDataBaseSQL(db:DB.sysManage, tableName: tablesofSysManage.medicine.rawValue)
        var resultArray = [Dictionary<String, String>]()
        var dic = [String:String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            dic["MedNum"] = row[0]
            dic["MedName"] = row[1]
            dic["MedUse"] = row[2]
            dic["MedPrice"] = row[3]
            resultArray.append(dic)
        })
        
        let data : NSData! = try? JSONSerialization.data(withJSONObject: resultArray, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlGetCheckDataResult() -> String {
        let result = selectAllDataBaseSQL(db:DB.sysManage, tableName: tablesofSysManage.check.rawValue)
        var resultArray = [Dictionary<String, String>]()
        var dic = [String:String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            dic["CheckNum"] = row[0]
            dic["CheckName"] = row[1]
            dic["CheckPrice"] = row[2]
            resultArray.append(dic)
        })
        
        let data : NSData! = try? JSONSerialization.data(withJSONObject: resultArray, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlGetNursingDataResult() -> String {
        let result = selectAllDataBaseSQL(db:DB.sysManage, tableName: tablesofSysManage.nursing.rawValue)
        var resultArray = [Dictionary<String, String>]()
        var dic = [String:String]()
        result.mysqlResult?.forEachRow(callback: { (row) in
            dic["NursingNum"] = row[0]
            dic["NursingName"] = row[1]
            dic["NursingPrice"] = row[2]
            resultArray.append(dic)
        })
        
        let data : NSData! = try? JSONSerialization.data(withJSONObject: resultArray, options: []) as NSData!
        let JSONString = NSString(data: data as Data, encoding: String.Encoding.utf8.rawValue)
        return JSONString! as String
    }
    
    func mysqlAddSysManageData(db: String, table: String, dic: [String: String]) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let connected = mysql.selectDatabase(named: db)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        var value = ""
        var key = ""
        for item in dic {
            if value != "" {
                value += ","
            }
            if item.value == "" {
                value += "NULL"
            } else {
                value += "\'\(item.value)\'"
            }
            if key == "" {
                key += "\(item.key)"
            } else {
                key += ",\(item.key)"
            }
        }
        let SQL = "INSERT INTO \(table) (\(key)) VALUES (\(value));"
        print(SQL)
        return mysqlStatement(sql: SQL)
    }
    
    func mysqlDelete(db: String, table: String, Sname: String, num: String) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let connected = mysql.selectDatabase(named: db)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        
        let SQL = "DELETE FROM \(table) WHERE \(Sname)=\'\(num)\'"
        return mysqlStatement(sql: SQL)
    }
    
    func mysqlUpdate(db: String, table: String, dic: [String: String], Sname: String, num: String) -> (success: Bool, mysqlResult: MySQL.Results?, errorMsg: String?) {
        let connected = mysql.selectDatabase(named: db)
        guard connected else {
            let msg = "连接此数据库失败"
            print(msg)
            return (false, nil, msg)
        }
        
        var updateStr = ""
        for item in dic {
            if updateStr == "" {
                updateStr += "\(item.key)=\'\(item.value)\'"
            } else {
                updateStr += ",\(item.key)=\'\(item.value)\'"
            }
        }
        let SQL = "UPDATE \(table) SET \(updateStr) WHERE \(Sname)=\'\(num)\'"
        return mysqlStatement(sql: SQL)
    }
    
    func mysqlGetBillData() -> [String: Any]
    {
        var results = [String: [[String:String]]]()
        
        var CheckBillArray = [[String:String]]()
        let billsofCheck = selectLatestDataBaseSQL(db: DB.bill, tableName: tableofBill.checkBill.rawValue, qty: 3)
        billsofCheck.mysqlResult?.forEachRow(callback: { (row) in
            var dic = [String:String]()
            dic["Num"] = row[0]
            dic["PatientNum"] = row[1]
            dic["NurseNum"] = row[2]
            dic["CheckNum"] = row[3]
            dic["Date"] = row[4]
            dic["State"] = row[5]
            dic["Amount"] = row[6]
            dic["TotalPrice"] = row[7]
            CheckBillArray.append(dic)
        })
        results["CheckBill"] = CheckBillArray
        
        var NursingBillArray = [[String:String]]()
        let billsofNurse = selectLatestDataBaseSQL(db: DB.bill, tableName: tableofBill.nursingBill.rawValue, qty: 3)
        billsofNurse.mysqlResult?.forEachRow(callback: { (row) in
            var dic = [String:String]()
            dic["Num"] = row[0]
            dic["PatientNum"] = row[1]
            dic["NurseNum"] = row[2]
            dic["CheckNum"] = row[3]
            dic["Date"] = row[4]
            dic["State"] = row[5]
            dic["Amount"] = row[6]
            dic["TotalPrice"] = row[7]
            NursingBillArray.append(dic)
        })
        results["NursingBill"] = CheckBillArray
        
        var MedBillArray = [[String:String]]()
        let billsofMed = selectLatestDataBaseSQL(db: DB.bill, tableName: tableofBill.medicineBill.rawValue, qty: 3)
        billsofMed.mysqlResult?.forEachRow(callback: { (row) in
            var dic = [String:String]()
            dic["Num"] = row[0]
            dic["PatientNum"] = row[1]
            dic["NurseNum"] = row[2]
            dic["CheckNum"] = row[3]
            dic["Date"] = row[4]
            dic["State"] = row[5]
            dic["Amount"] = row[6]
            dic["TotalPrice"] = row[7]
            dic["Num1"] = row[8]
            dic["Num2"] = row[9]
            MedBillArray.append(dic)
        })
        results["MedBill"] = MedBillArray
        let encoded = results
        return encoded
    }
}
<file_sep>/Sources/main.swift
import PerfectLib
import PerfectHTTP
import PerfectHTTPServer

let port1 = 8080

var server = HTTPServer()
server.serverName = "maispangle"
server.serverPort = UInt16(port1)


enum apiaaa: String {
    case getUserData = "getAllUserData"
    case get
}
var routes = Routes()
routes.add(method: .get, uri: "/api/getInfo/{api}", handler: {
    request, response in
    response.status = HTTPResponseStatus.accepted
    switch request.urlVariables["api"]! {
    case "getAllUserDataResult":
        response.setBody(string: DataBaseManager().mysqlGetUserDataResult())
    case "getBedDataResult":
        response.setBody(string: DataBaseManager().mysqlGetBedDataResult())
    case "getCheckDataResult":
        response.setBody(string: DataBaseManager().mysqlGetCheckDataResult())
    case "getNursingDataResult":
        response.setBody(string: DataBaseManager().mysqlGetNursingDataResult())
    case "getMedicineDataResult":
        response.setBody(string: DataBaseManager().mysqlGetMedicineDataResult())
    case "getBillsDataResult":
        //        response.setBody(string: DataBaseManager().mysqlGetBillData())
        do {
            try response.setBody(json: DataBaseManager().mysqlGetBillData())
        } catch {
            fatalError("\(error)")
        }
    default: response.status = .notFound
    }
    response.completed()
})

routes.add(method: .get, uri: "/api/login", handler: {
    request, response in
    response.status = .accepted
    var username: String = ""
    var pwd: String = ""
    for (a, b) in request.queryParams {
        if a == "ID" {
            username = b
        }
        if a == "pwd" {
            pwd = b
        }
    }
    if username == "" || pwd == "" {
        response.status = .notFound
    }
    let result = DataBaseManager().Login(userID: username, pwd: pwd)
    response.setBody(string: result)
    response.completed()
})

routes.add(method: .post, uri: "/api/Insert", handler: {
    request, response in
    response.status = .accepted
    var table: String
    var db: String
    let t = request.postParams
    if request.queryParams[0].0 == "db" &&  request.queryParams[1].0 == "table" {
        db = request.queryParams[0].1
        table = request.queryParams[1].1
        var dic = [String: String]()
        for (key, val) in t {
            dic[key] = val
        }
        
        if DataBaseManager().mysqlAddSysManageData(db: db, table: table, dic: dic).success {
            response.setBody(string: "true")
        } else {
            response.setBody(string: "fail")
        }
    } else {
        response.setBody(string: "fail")
    }
    response.completed()
})

routes.add(method: .get, uri: "/api/Delete", handler: {
    request, response in
    response.status = .accepted
    var table: String
    var db: String
    var num: String
    var name: String
    if request.queryParams[0].0 == "db" &&  request.queryParams[1].0 == "table" && request.queryParams[2].0 == "Num" && request.queryParams[3].0 == "name" {
        db = request.queryParams[0].1
        table = request.queryParams[1].1
        num = request.queryParams[2].1
        name = request.queryParams[3].1
        
        if DataBaseManager().mysqlDelete(db: db, table: table, Sname: name, num: num).success {
            response.setBody(string: "true")
        } else {
            response.setBody(string: "fail")
        }
    } else {
        response.setBody(string: "fail")
    }
    response.completed()
})

routes.add(method: .post, uri: "/api/Edit", handler: {
    request, response in
    response.status = .accepted
    var table: String
    var db: String
    let t = request.postParams
    var num: String
    var name: String
    if request.queryParams[0].0 == "db" &&  request.queryParams[1].0 == "table" && request.queryParams[2].0 == "Num" && request.queryParams[3].0 == "name" {
        db = request.queryParams[0].1
        table = request.queryParams[1].1
        num = request.queryParams[2].1
        name = request.queryParams[3].1
        
        var dic = [String: String]()
        for (key, val) in t {
            dic[key] = val
        }
        
        if DataBaseManager().mysqlUpdate(db: db, table: table, dic: dic, Sname: name, num: num).success {
            response.setBody(string: "true")
        } else {
            response.setBody(string: "fail")
        }
    } else {
        response.setBody(string: "fail")
    }
    response.completed()
})

server.addRoutes(routes)

//MARK: filter
struct Filter404: HTTPResponseFilter {
    func filterBody(response: HTTPResponse, callback: (HTTPResponseFilterResult) -> ()) {
        callback(.continue)
    }
    
    func filterHeaders(response: HTTPResponse, callback: (HTTPResponseFilterResult) -> ()) {
        if case .notFound = response.status {
            response.setBody(string: "404 not found")
            response.setHeader(.contentLength, value: "\(response.bodyBytes.count)")
            callback(.done)
        } else {
            callback(.continue)
        }
    }
}
server.setResponseFilters([(Filter404(), .high)])

do {
    // Launch the servers based on the configuration data.
    try server.start()
} catch {
    fatalError("\(error)") // fatal error launching one of the servers
}
