parkMonitor/parkMonitor/DB/DBOperation.cs

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

namespace parkMonitor.DB
{
    public class DBOperation
    {
        private static MySqlConnection con;
        private static MySqlCommand cmd;
        IDBOperator oper = new Operator();
        //遍历用户号牌,查询号牌是否为该用户
        public bool IsUserNumberPlate(int userID, string numberPlate)
        {
            bool isUserNumberPlate = false;
            List<string> list = new List<string>();
            MySqlDataReader reader = null;
            string sql = "select numberPlate from usercarrelation where userID = '" + userID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);

                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        list.Add(reader.GetString("numberPlate"));
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            if (list.Contains(numberPlate))
            {
                isUserNumberPlate = true;
            }
            else
            {
                isUserNumberPlate = false;
            }
            return isUserNumberPlate;
        }

        //根据号牌查询车型
        public int getVehicleTypeID(string numberPlate)
        {
            int vehicleTypeID = 0;
            MySqlDataReader reader = null;
            string sql = "select vehicleTypeID from vehicle where numberPlate = '" + numberPlate + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        vehicleTypeID = reader.GetInt32("vehicleTypeID");
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return vehicleTypeID;
        }

        //判断测量数据是否正确
        public bool IsDataRight(int vehicleTypeLength, int vehicleTypeWidth, int vehicleTypeHeight, int vehicleTypeWheelbase, int vehicleTypeID)
        {
            bool isDataRight = true;
            MySqlDataReader reader = null;
            string sql = "select vehicleTypeLength,vehicleTypeWidth,vehicleTypeHeight,vehicleTypeWheelbase from vehicletype where vehicleTypeID = '" + vehicleTypeID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        int length = reader.GetInt32("vehicleTypeLength");
                        int width = reader.GetInt32("vehicleTypeWidth");
                        int height = reader.GetInt32("vehicleTypeHeight");
                        int wheelbase = reader.GetInt32("vehicleTypeWheelbase");
                        int lengthError = Convert.ToInt32(ConfigurationManager.AppSettings["length"]);
                        int widthError = Convert.ToInt32(ConfigurationManager.AppSettings["width"]);
                        int heightError = Convert.ToInt32(ConfigurationManager.AppSettings["height"]);
                        int wheelbaseError = Convert.ToInt32(ConfigurationManager.AppSettings["wheelbase"]);
                        if (System.Math.Abs(length - vehicleTypeLength) < lengthError && System.Math.Abs(width - vehicleTypeWidth) < widthError && System.Math.Abs(height - vehicleTypeHeight) < heightError && System.Math.Abs(wheelbase - vehicleTypeWheelbase) < wheelbaseError)
                        {
                            isDataRight = true;
                        }
                        else
                        {
                            isDataRight = false;
                        }
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return isDataRight;
        }
        //查询车位位置及状态，返回list
        public List<Parking_Space> GetParkingSpace(int garageID)
        {
            List<Parking_Space> lps = new List<Parking_Space>();
            MySqlDataReader reader = null;
            string sql = "select * from parkingspace where parkingSpaceState = 0 and garageID = '"+garageID+"' ";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                while (reader == null)
                {
                    reader = oper.getResultSet(cmd);
                }
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        int parkingSpaceID = reader.GetInt32("parkingSpaceID");
                        //float parkingSpaceWeight = reader.GetFloat("parkingSpaceWeight");
                        //int parkingSpaceStatement = reader.GetInt32("parkingSpaceState");
                        //int garageID = reader.GetInt32("garageID");
                        int parkingSpaceX = reader.GetInt32("parkingSpaceX");
                        int parkingSpaceY = reader.GetInt32("parkingSpaceY");
                        int parkingSpaceZ = reader.GetInt32("parkingSpaceZ");
                        Parking_Space ps = new Parking_Space();
                        ps.parkingSpaceID = parkingSpaceID;
                        ps.garageID = garageID;
                        //ps.parkingSpaceWeight = parkingSpaceWeight;
                        //ps.parkingSpaceStatement = parkingSpaceStatement;
                        ps.parkingSpaceX = parkingSpaceX;
                        ps.parkingSpaceY = parkingSpaceY;
                        ps.parkingSpaceZ = parkingSpaceZ;

                        lps.Add(ps);
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return lps;
        }

        //根据车库ID查询权重
        public List<Garage> GetGarageWeight(int garageID)
        {
            List<Garage> lgWeight = new List<Garage>();
            MySqlDataReader reader = null;
            string sql = "select * from garage where garageID = '" + garageID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);

                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        int xweight = reader.GetInt32("xweight");
                        int yweight = reader.GetInt32("yweight");
                        int zweight = reader.GetInt32("zweight");
                        Garage gWeight = new Garage();
                        gWeight.xweight = xweight;
                        gWeight.yweight = yweight;
                        gWeight.zweight = zweight;
                        lgWeight.Add(gWeight);
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return lgWeight;
        }

        //数据插入记录表,并返回停车记录id
        public int InsertToParkingRecords(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 + "')";
            con = oper.getConn();
            int parkingRecordsID = 0;
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getInsert(cmd);
                parkingRecordsID = Convert.ToInt32(cmd.LastInsertedId);
                if (cmd != null)
                {                   
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return parkingRecordsID;
        }

        //根据车牌号更新车辆表
        public void UpdateVehicle(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 + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getUpdate(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //查询车库表获得剩余车位数
        public int getGarageFreeSpace(int garageID)
        {
            int garageFreeSpace = 0;
            MySqlDataReader reader = null;
            string sql = "select * from garage where garageID = '" + garageID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        garageFreeSpace = reader.GetInt32("garageFreeSpace");
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return garageFreeSpace;
        }

        //更新车库表剩余车位数
        public void UpdateGarageFreeSpace(int garageFreeSpace, int garageID)
        {
            string sql = "update garage set garageFreeSpace = '" + garageFreeSpace + "' where garageID = '" + garageID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getUpdate(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //根据车牌号更新车辆状态
        public void UpdateVehicleParkState(string numberPlate, int vehiclepParkState)
        {
            string sql = "update vehicle set vehiclepParkState = '" + vehiclepParkState + "'where numberPlate = '" + numberPlate + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getUpdate(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //更新车位状态
        public void UpdateParkingSpaceState(int parkingSpaceID, int parkingSpaceState)
        {
            string sql = "update parkingspace set parkingSpaceState = '" + parkingSpaceState + "'where parkingSpaceID = '" + parkingSpaceID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getUpdate(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //更新停车记录表
        public void UpdateParkingRecords(int parkingRecordsState, string realGetTime, int parkingRecordsID)
        {
            string sql = "update parkingrecords set parkingRecordsState = '" + parkingRecordsState + "',realGetTime = '" + realGetTime + "'where parkingRecordsID = '" + parkingRecordsID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getUpdate(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //插入消息推送表
        public void InsertToMessageQueue(int userID, string context, int messageType)
        {
            string sql = "insert into messagequeue(userID,context,messageType) values('" + userID + "','" + context + "','" + messageType + "')";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                oper.getInsert(cmd);
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        //查询车库表获得车库名称
        public string getGarageName(int garageID)
        {
            string garageName = null;
            MySqlDataReader reader = null;
            string sql = "select * from garage where garageID = '" + garageID + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        garageName = reader.GetString("garageName");
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return garageName;
        }

        //根据车牌查询得到车库id和车位id以及轮距
        public Vehicle GetVehicle(string numberPlate)
        {
            // List<Vehicle> lVehicle = new List<Vehicle>();
            Vehicle v = new Vehicle();
            MySqlDataReader reader = null;
            string sql = "select * from vehicle where numberPlate = '" + numberPlate + "'";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                reader = oper.getResultSet(cmd);
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        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;
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            return v;
        }

        //根据车位id获得x,y,z
        public Fetching_Space GetFetchingSpace(int parkingSpaceID)
        {
            Fetching_Space fs = new Fetching_Space();
            MySqlDataReader reader = null;
            string sql = "select * from parkingspace where parkingSpaceID = '" + parkingSpaceID + " '";
            con = oper.getConn();
            try
            {
                con.Open();
                cmd = oper.getComm(sql, con);
                while (reader == null)
                {
                    reader = oper.getResultSet(cmd);
                }
                while (reader.Read())
                {
                    if (reader.HasRows)
                    {
                        int parkingSpaceX = reader.GetInt32("parkingSpaceX");
                        int parkingSpaceY = reader.GetInt32("parkingSpaceY");
                        int parkingSpaceZ = reader.GetInt32("parkingSpaceZ");
                        fs.parkingSpaceX = parkingSpaceX;
                        fs.parkingSpaceY = parkingSpaceY;
                        fs.parkingSpaceZ = parkingSpaceZ;
                    }
                }
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }            
            return fs;
        }

    }
}