parkMonitor/parkMonitor/DataBase/DBOperation.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace parkMonitor.DataBase
{
    public class DBOperation
    {
        //查询车库表获得剩余车位数
        public int getGarageFreeSpace(string connectionStr, int garageID)
        {
            MySqlDataReader reader = null;
            int garageFreeSpace = 0;
            string sql = "select * from garage where garageID = '" + garageID + "'";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                garageFreeSpace = reader.GetInt32("garageFreeSpace");
                return garageFreeSpace;
            }
            else
            {
                Console.WriteLine("车位剩余数查无结果");
            }
            oper.DBClose();
            return 0;
        }

        //查询空闲车位位置
        public Dictionary<int, Parking_Space> GetParkingSpace(string connectionStr, int garageID)
        {
            Dictionary<int, Parking_Space> lps = new Dictionary<int, Parking_Space>();
            MySqlDataReader reader = null;
            string sql = "select * from parkingspace where parkingSpaceState = 0 and garageID = '" + garageID + "' ";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                for (int i = 1; i <= count; i++)
                {
                    Parking_Space ps = new Parking_Space();
                    ps.parkingSpaceID = reader.GetInt32("parkingSpaceID");
                    ps.parkingSpaceX = reader.GetInt32("parkingSpaceX");
                    ps.parkingSpaceY = reader.GetInt32("parkingSpaceY");
                    ps.parkingSpaceZ = reader.GetInt32("parkingSpaceZ");
                    ps.garageID = garageID;
                    lps.Add(i, ps);
                }
                return lps;
            }
            else
            {
                Console.WriteLine("空闲车位查无结果"); 
            }
            oper.DBClose();
            return null;
        }

        //查询所有车位位置及状态
        public Dictionary<int, Parking_Space> GetAllParkingSpace(string connectionStr, int garageID)
        {
            Dictionary<int, Parking_Space> lps = new Dictionary<int, Parking_Space>();
            MySqlDataReader reader = null;
            string sql = "select * from parkingspace where garageID = '" + garageID + "' ";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                for (int i = 1; i <= count; i++)
                {
                    Parking_Space ps = new Parking_Space();
                    ps.parkingSpaceID = reader.GetInt32("parkingSpaceID");
                    ps.parkingSpaceX = reader.GetInt32("parkingSpaceX");
                    ps.parkingSpaceY = reader.GetInt32("parkingSpaceY");
                    ps.parkingSpaceZ = reader.GetInt32("parkingSpaceZ");
                    ps.parkingSpaceState = reader.GetInt32("parkingSpaceState");
                    ps.garageID = garageID;
                    lps.Add(i, ps);
                }
                return lps;
            }
            else
            {
                Console.WriteLine("所有车位查无结果");
            }
            oper.DBClose();
            return null;
        }

        //数据插入云记录表,并返回停车记录id
        public int InsertToParkingRecords(string connectionStr, int userID, string numberPlate, int parkingSpaceID, int garageID, int parkingRecordsState, string realParkTime)
        {
            string sql = "insert into parkingrecords(userID,numberPlate,parkingSpaceID,garageID,parkingRecordsState,realParkTime) values('" + userID + "','" + numberPlate + "','" + parkingSpaceID + "','" + garageID + "','" + parkingRecordsState + "','" + realParkTime + "')";
            int parkingRecordsID = 0;
            Operation oper = new Operation(connectionStr, sql);
            oper.getInsert();
            parkingRecordsID = oper.getInsertId();
            oper.DBClose();
            return parkingRecordsID;
        }

        //根据车牌号更新车辆表
        public void UpdateVehicle(string connectionStr, string numberPlate, int vehiclepParkState, string scanEntryTime, int parkingRecordsID, int parkingSpaceID, int vehicleTypeConfirm, int frontWheelbase, int rearWheelbase)
        {
            string sql = "update vehicle set vehiclepParkState = '" + vehiclepParkState + "',scanEntryTime = '" + scanEntryTime + "',parkingRecordsID = '" + parkingRecordsID + "',parkingSpaceID = '" + parkingSpaceID + "',vehicleTypeConfirm = '" + vehicleTypeConfirm + "',frontwheelbase = '" + frontWheelbase + "',rearwheelbase = '" + rearWheelbase + "' where numberPlate = '" + numberPlate + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //更新车库表剩余车位数
        public void UpdateGarageFreeSpace(string connectionStr, int garageFreeSpace, int garageID)
        {
            string sql = "update garage set garageFreeSpace = '" + garageFreeSpace + "' where garageID = '" + garageID + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //根据车牌号更新车辆状态
        public void UpdateVehicleParkState(string connectionStr, string numberPlate, int vehiclepParkState)
        {
            string sql = "update vehicle set vehiclepParkState = '" + vehiclepParkState + "'where numberPlate = '" + numberPlate + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //更新车位状态
        public void UpdateParkingSpaceState(string connectionStr, int parkingSpaceID, int parkingSpaceState)
        {
            string sql = "update parkingspace set parkingSpaceState = '" + parkingSpaceState + "'where parkingSpaceID = '" + parkingSpaceID + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //更新停车记录表
        public void UpdateParkingRecords(string connectionStr, int parkingRecordsState, string realGetTime, int parkingRecordsID)
        {
            string sql = "update parkingrecords set parkingRecordsState = '" + parkingRecordsState + "',realGetTime = '" + realGetTime + "'where parkingRecordsID = '" + parkingRecordsID + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //插入消息推送表
        public void InsertToMessageQueue(string connectionStr, int userID, string context, int messageType)
        {
            string sql = "insert into messagequeue(userID,context,messageType) values('" + userID + "','" + context + "','" + messageType + "')";
            Operation oper = new Operation(connectionStr, sql);
            oper.getInsert();
            oper.DBClose();
        }

        //根据车牌查询得到车库id和车位id以及轮距
        public Vehicle GetVehicle(string connectionStr, string numberPlate)
        {
            Vehicle v = new Vehicle();
            MySqlDataReader reader = null;
            string sql = "select * from vehicle where numberPlate = '" + numberPlate + "'";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                int parkingSpaceID = reader.GetInt32("parkingSpaceID");
                int garageID = reader.GetInt32("garageID");
                int frontwheelbase = reader.GetInt32("frontwheelbase");
                int rearwheelbase = reader.GetInt32("rearwheelbase");
                v.parkingSpaceID = parkingSpaceID;
                v.garageID = garageID;
                v.frontwheelbase = frontwheelbase;
                v.rearwheelbase = rearwheelbase;
                return v;
            }
            else
            {
                Console.WriteLine("云端轮距查无结果");
            }
            oper.DBClose();
            return null;
        }

        //根据车位id获得x,y,z
        public Parking_Space GetFetchingSpace(string connectionStr, int parkingSpaceID)
        {
            Parking_Space ps = new Parking_Space();
            MySqlDataReader reader = null;
            string sql = "select * from parkingspace where parkingSpaceID = '" + parkingSpaceID + " '";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                ps.parkingSpaceID = parkingSpaceID;
                ps.parkingSpaceX = reader.GetInt32("parkingSpaceX");
                ps.parkingSpaceY = reader.GetInt32("parkingSpaceY");
                ps.parkingSpaceZ = reader.GetInt32("parkingSpaceZ");
                return ps;
            }
            else
            {
                Console.WriteLine("车位xyz查无结果");
            }
            oper.DBClose();
            return null;
        }

        //查询手机号是否被注册
        public bool IsTelRegister(string connectionStr, string tel)
        {
            bool isTelRegister = false;
            MySqlDataReader reader = null;
            string sql = "select * from user where userTelephone = '" + tel + "'";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0)
            {
                isTelRegister = true;
            }
            else
            {
                isTelRegister = false;
            }
            oper.DBClose();
            return isTelRegister;
        }

        //注册信息写入数据库,返回注册成功信息
        public int InsertUser(string connectionStr, string tel, string password)
        {
            string sql = "insert into user(userTelephone,userPassword,userLevel) values('" + tel + "','" + password + "',1)";
            int userID = 0;
            Operation oper = new Operation(connectionStr, sql);
            oper.getInsert();
            userID = oper.getInsertId();
            oper.DBClose();
            return userID;
        }

        //根据电话号码查询userID
        public int GetUserID(string connectionStr, string tel)
        {
            int userID = 0;
            MySqlDataReader reader = null;
            string sql = "select userID from user where userTelephone = '" + tel + "'";
            int count = 0;
            Operation oper = new Operation(connectionStr, sql);
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                userID = reader.GetInt32("userID");
                return userID;
            }
            else
            {
                Console.WriteLine("userID查无结果");
            }
            oper.DBClose();
            return 0;
        }

        //查询停车记录id
        public int GetParkingRecordsID(string connectionStr, string numberPlate)
        {
            int parkingRecordsID = 0;
            MySqlDataReader reader = null;
            string sql = "select parkingRecordsID from parkingrecords where numberPlate = '" + numberPlate + "' and parkingRecordsState = 3";
            int count = 0;
            Operation oper = new Operation(connectionStr, sql);
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                parkingRecordsID = reader.GetInt32("parkingRecordsID");
                return parkingRecordsID;
            }
            else
            {
                Console.WriteLine("停车记录id查无结果");
            }
            oper.DBClose();
            return 0;
        }

        //车库有无此车
        public bool IsNumberPlate(string connectionStr, string numberPlate, int garageID)
        {
            bool isNumberPlate = true;
            MySqlDataReader reader = null;
            string sql = "select * from parkingrecords where numberPlate = '" + numberPlate + "' and parkingRecordsState = 3 and garageID = '" + garageID + "'";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                isNumberPlate = true;
            }
            else
            {
                isNumberPlate = false;
            }
            oper.DBClose();
            return isNumberPlate;
        }

        //数据插入本地记录表,并返回停车记录id
        public int InsertToLocalParkingRecords(string connectionStr, int parkingStatus, int userID, string numberPlate, int parkingSpaceID, int garageID, int parkingRecordsState, string realParkTime, int frontWheelbase, int rearWheelbase)
        {
            string sql = "insert into parkingrecords(parkingStatus,userID,numberPlate,parkingSpaceID,garageID,parkingRecordsState,realParkTime,frontWheelbase,rearWheelbase) values('" + parkingStatus + "','" + userID + "','" + numberPlate + "','" + parkingSpaceID + "','" + garageID + "','" + parkingRecordsState + "','" + realParkTime + "','" + frontWheelbase + "','" + rearWheelbase + "')";
            int parkingRecordsID = 0;
            Operation oper = new Operation(connectionStr, sql);
            oper.getInsert();
            parkingRecordsID = oper.getInsertId();
            oper.DBClose();
            return parkingRecordsID;
        }

        //更新本地停车记录表
        public void UpdateParkingRecords(string connectionStr, int parkingStatus, int parkingRecordsState, string realGetTime, int parkingRecordsID)
        {
            string sql = "update parkingrecords set parkingStatus = '" + parkingStatus + "', parkingRecordsState = '" + parkingRecordsState + "',realGetTime = '" + realGetTime + "'where parkingRecordsID = '" + parkingRecordsID + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //插入车辆表
        public void InsertVehicle(string connectionStr, string numberPlate, int vehiclepParkState, int parkingRecordsID, int parkingSpaceID, int vehicleTypeConfirm)
        {
            string sql = "insert into vehicle(numberPlate,vehiclepParkState,parkingRecordsID,parkingSpaceID,vehicleTypeConfirm) values('" + numberPlate + "','" + vehiclepParkState + "','" + parkingRecordsID + "','" + parkingSpaceID + "','" + vehicleTypeConfirm + "')";
            Operation oper = new Operation(connectionStr, sql);
            oper.getInsert();
            oper.DBClose();
        }

        //判断车辆表中是否存在该车辆号牌
        public bool IsNumberPlateFromVehicle(string connectionStr, string numberPlate)
        {
            bool isNumberPlateFromVehicle = true;
            string sql = "select * from vehicle where numberPlate = '" + numberPlate + "' and vehiclepParkState = 1";
            MySqlDataReader reader = null;
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                isNumberPlateFromVehicle = true;
            }
            else
            {
                isNumberPlateFromVehicle = false;
            }
            oper.DBClose();
            return isNumberPlateFromVehicle;
        }

        //根据车牌号更新车辆表
        public void UpdateVehicle(string connectionStr, string numberPlate, int vehiclepParkState, int parkingRecordsID, int parkingSpaceID, int vehicleTypeConfirm)
        {
            string sql = "update vehicle set vehiclepParkState = '" + vehiclepParkState + "',parkingRecordsID = '" + parkingRecordsID + "',parkingSpaceID = '" + parkingSpaceID + "',vehicleTypeConfirm = '" + vehicleTypeConfirm + "' where numberPlate = '" + numberPlate + "'";
            Operation oper = new Operation(connectionStr, sql);
            oper.getUpdate();
            oper.DBClose();
        }

        //根据车牌查询得到车库id和车位id以及轮距
        public Vehicle GetLocalVehicle(string connectionStr, string numberPlate, int garageID)
        {
            Vehicle v = new Vehicle();
            MySqlDataReader reader = null;
            string sql = "select * from parkingrecords where numberPlate = '" + numberPlate + "' and parkingRecordsState = 3 and garageID = '" + garageID + "'";
            Operation oper = new Operation(connectionStr, sql);
            int count = 0;
            reader = oper.getResultSet(ref count);
            if (count > 0 && reader.Read())
            {
                int parkingSpaceID = reader.GetInt32("parkingSpaceID");
                int frontwheelbase = reader.GetInt32("frontwheelbase");
                int rearwheelbase = reader.GetInt32("rearwheelbase");
                v.parkingSpaceID = parkingSpaceID;
                v.garageID = garageID;
                v.frontwheelbase = frontwheelbase;
                v.rearwheelbase = rearwheelbase;
                return v;
            }
            else
            {
                Console.WriteLine("本地轮距查无结果");
            }
            oper.DBClose();
            return null;
        }

    }
}