chutian_modules/parkspace_manager/db_manager/DBManager.cs

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

namespace parkspace_manager
{
    class DBManager
    {
        /// <summary>
        /// 远程数据库操作句柄
        /// </summary>
        public static DBOperation remoteDBOper;
        /// <summary>
        /// 本地数据库操作句柄
        /// </summary>
        public static DBOperation localDBOper;

        public DBManager()
        {

        }
        ~DBManager()
        {

        }
        ///// <summary>
        ///// 返回停车记录信息
        ///// </summary>
        ///// <returns></returns>
        //public static List<object[]> GetParkingRecords(string license = "", string startTime = "", string endTime = "")
        //{
        //    DateTime now = DateTime.Now;
        //    List<object[]> result = new List<object[]>();
        //    string getParkingRecordsSql = "";
        //    if (startTime == "" || endTime == "")
        //    {
        //        DateTime yesterday = DateTime.Now - (new TimeSpan(1, 0, 0, 0));
        //        DateTime twoDaysAgo = DateTime.Now - (new TimeSpan(2, 0, 0, 0));
        //        getParkingRecordsSql = "select parkingRecordsID,userID,numberPlate,parkingSpaceID,realParkTime,realGetTime,receiptNum,parkingPrice,paymentStatus " +
        //        "from parkingrecords where numberPlate " + (license == "" ? "like '%" : "= '" + license) + "' and (realParkTime like '" + now.ToString("yyyy-MM-dd") + "%' or realParkTime like '" + yesterday.ToString("yyyy-MM-dd") + "%' or realParkTime like '" + twoDaysAgo.ToString("yyyy-MM-dd") + "%');";
        //    }
        //    else
        //    {
        //        getParkingRecordsSql = "select parkingRecordsID,userID,numberPlate,parkingSpaceID,realParkTime,realGetTime,receiptNum,parkingPrice,paymentStatus " +
        //        "from parkingrecords where numberPlate " + (license == "" ? "like '%" : "= '" + license) + "' and realParkTime >= '" + startTime + "' and realParkTime <= '" + endTime + "';";
        //    }
        //    if (localDBOper != null)
        //    {
        //        lock (localDBOper)
        //        {
        //            MySqlDataReader reader = localDBOper.Query(getParkingRecordsSql);
        //            try
        //            {
        //                while (reader != null && reader.Read())
        //                {
        //                    if (reader.HasRows)
        //                    {
        //                        object[] temp = new object[reader.FieldCount];
        //                        reader.GetValues(temp);
        //                        result.Add(temp);
        //                    }
        //                }
        //            }
        //            catch (Exception e) { Console.WriteLine(e.Message); }
        //            try
        //            {
        //                if (reader != null)
        //                {
        //                    reader.Close();
        //                    reader.Dispose();
        //                }
        //            }
        //            catch (Exception e) { Console.WriteLine(e.Message); }
        //        }
        //    }
        //    result.Reverse();
        //    return result;
        //}
        ///// <summary>
        ///// 返回预约记录信息
        ///// </summary>
        ///// <returns></returns>
        //public static List<object[]> GetOrderRecords(bool localDB, string license = "", string startTime = "", string endTime = "")
        //{
        //    DateTime now = DateTime.Now;
        //    List<object[]> result = new List<object[]>();
        //    string getOrderRecordsSql = "";
        //    if (startTime == "" || endTime == "")
        //    {
        //        DateTime yesterday = DateTime.Now - (new TimeSpan(1, 0, 0, 0));
        //        DateTime twoDaysAgo = DateTime.Now - (new TimeSpan(2, 0, 0, 0));
        //        getOrderRecordsSql = "select orderRecordsID,userID,numberPlate,bookParkTime,cancelBookTime,bookFetchTime,bookHour,bookPrice,bookState " +
        //        "from orderrecords where numberPlate " + (license == "" ? "like '%" : "= '" + license) + "' and (bookParkTime like '" + now.ToString("yyyy-MM-dd") +
        //        "%' or bookParkTime like '" + yesterday.ToString("yyyy-MM-dd") + "%' or bookFetchTime like '" + now.ToString("yyyy-MM-dd") + "%' or bookFetchTime like '" + yesterday.ToString("yyyy-MM-dd") + "%');";
        //    }
        //    else
        //    {
        //        getOrderRecordsSql = "select orderRecordsID,userID,numberPlate,bookParkTime,cancelBookTime,bookFetchTime,bookHour,bookPrice,bookState " +
        //        "from orderrecords where numberPlate " + (license == "" ? "like '%" : "= '" + license) + "' and ((bookParkTime >= '" + startTime + "' and bookParkTime <= '" + endTime + "') or (bookFetchTime >= '" + startTime + "' and bookFetchTime <= '" + endTime + "'));";
        //    }
        //    if (localDBOper != null)
        //    {
        //        lock (localDBOper)
        //        {
        //            MySqlDataReader reader;
        //            if (localDB)
        //                reader = localDBOper.Query(getOrderRecordsSql);
        //            else
        //                reader = remoteDBOper.Query(getOrderRecordsSql);
        //            try
        //            {
        //                while (reader != null && reader.Read())
        //                {
        //                    if (reader.HasRows)
        //                    {
        //                        object[] temp = new object[reader.FieldCount];
        //                        reader.GetValues(temp);
        //                        result.Add(temp);
        //                    }
        //                }
        //            }
        //            catch (Exception e) { Console.WriteLine(e.Message); }
        //            try
        //            {
        //                if (reader != null)
        //                {
        //                    reader.Close();
        //                    reader.Dispose();
        //                }
        //            }
        //            catch (Exception e) { Console.WriteLine(e.Message); }
        //        }
        //    }
        //    result.Reverse();
        //    return result;
        //}
        ///// <summary>
        ///// 更新停车记录关于支付的信息
        ///// </summary>
        ///// <param name="license"></param>
        ///// <param name="receiptNum"></param>
        ///// <param name="parkingFee"></param>
        ///// <param name="paymentStatus"></param>
        ///// <returns></returns>
        //public static bool UpdateParkingRecords(string license, string receiptNum, string parkingFee, string paymentStatus, bool local)
        //{
        //    bool result = false;
        //    string updateRecordsSql = "update parkingrecords set parkingPrice = " + parkingFee + " ,paymentStatus = " + paymentStatus + " where numberPlate = '" + license + "' and receiptNum = " + receiptNum + ";";
        //    List<string> updateList = new List<string>();
        //    updateList.Add(updateRecordsSql);
        //    if (local && localDBOper != null)
        //    {
        //        lock (localDBOper)
        //        {
        //            try
        //            {
        //                result = localDBOper.UpdateTransaction(updateList);
        //            }
        //            catch { Log.WriteLog(LogType.database, "更新本地停车记录中支付信息出现异常。\n" + updateRecordsSql); return false; }
        //        }
        //    }
        //    else if (!local && remoteDBOper != null)
        //    {
        //        lock (remoteDBOper)
        //        {
        //            try
        //            {
        //                result = remoteDBOper.UpdateTransaction(updateList);
        //            }
        //            catch { Log.WriteLog(LogType.database, "更新云端记录中支付信息出现异常。\n" + updateRecordsSql); return false; }
        //        }
        //    }
        //    return result;
        //}
        ///// <summary>
        ///// 更新所有车位信息
        ///// </summary>
        //private static void UpdateAllParkingSpace(bool remote)
        //{
        //    string findParkingSpace = "select parkingSpaceID from parkingspace;";
        //    HashSet<int> recordsIDSet = new HashSet<int>();
        //    DBOperation dbHandle = null;
        //    if (!remote)
        //    {
        //        dbHandle = localDBOper;
        //    }
        //    else
        //    {
        //        dbHandle = remoteDBOper;
        //    }
        //    lock (dbHandle)
        //    {
        //        MySqlDataReader reader = dbHandle.Query(findParkingSpace);
        //        if (reader != null)
        //        {
        //            try
        //            {
        //                while (reader.Read())
        //                {
        //                    if (reader.HasRows)
        //                    {
        //                        recordsIDSet.Add(reader.GetInt32("parkingSpaceID"));
        //                    }
        //                }
        //            }
        //            catch { Log.WriteLog(LogType.process, LogFile.INFO, "数据库车位查询异常"); }
        //            try
        //            {
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            catch { }
        //        }
        //    }
        //    List<string> updateSpaceList = new List<string>();
        //    List<string> insertSpaceList = new List<string>();
        //    string updateParkingSpace = "";
        //    string insertParkingSpace = "";
        //    for (int i = 0; i < parkingSpaceInfo.Count; i++)
        //    {
        //        if (recordsIDSet.Contains(parkingSpaceInfo[i].parkingSpace))
        //        {
        //            updateParkingSpace = "update parkingspace set parkingSpaceX = " + parkingSpaceInfo[i].coordX +
        //                ",parkingSpaceY = " + parkingSpaceInfo[i].coordY + ",parkingSpaceZ = " + parkingSpaceInfo[i].floorNo + ",parkingSpaceState = " + parkingSpaceInfo[i].spaceStatus + " where (parkingSpaceID = " + parkingSpaceInfo[i].parkingSpace + ");";
        //            updateSpaceList.Add(updateParkingSpace);
        //        }
        //        else
        //        {
        //            if (!remote)
        //            {
        //                insertParkingSpace = "insert into parkingspace (parkingSpaceID,parkingSpaceX,parkingSpaceY,parkingSpaceZ,parkingSpaceState) values (" + parkingSpaceInfo[i].parkingSpace + "," + parkingSpaceInfo[i].coordX +
        //                    "," + parkingSpaceInfo[i].coordY + "," + parkingSpaceInfo[i].floorNo + "," + parkingSpaceInfo[i].spaceStatus + ");";
        //                insertSpaceList.Add(insertParkingSpace);
        //            }
        //            else
        //            {
        //                insertParkingSpace = "insert into parkingspace (parkingSpaceID,parkingSpaceX,parkingSpaceY,parkingSpaceZ,parkingSpaceState,garageID,parkingSpaceName) values (" + parkingSpaceInfo[i].parkingSpace + "," + parkingSpaceInfo[i].coordX +
        //                    "," + parkingSpaceInfo[i].coordY + "," + parkingSpaceInfo[i].floorNo + "," + parkingSpaceInfo[i].spaceStatus + "," + garageID + ",'默认');";
        //                insertSpaceList.Add(insertParkingSpace);
        //            }
        //        }
        //    }
        //    try
        //    {
        //        lock (dbHandle)
        //        {
        //            int id = 0;
        //            dbHandle.UpdateTransaction(updateSpaceList);
        //            dbHandle.Insert(insertSpaceList, out id);
        //        }
        //    }
        //    catch (Exception e) { Log.WriteLog(LogType.process, LogFile.ERROR, "更新本地所有车位异常"); Console.WriteLine("更新本地所有车位异常" + e.Message); }
        //}
        ///// <summary>
        ///// 更新车辆状态
        ///// </summary>
        ///// <param name="lic"></param>
        ///// <param name="state"></param>
        //private static void UpdateVehicle(string lic, int state, int parkingRecordsID, bool park, bool remote, int parkingSpaceID)
        //{
        //    if (lic != "")
        //    {
        //        MySqlDataReader reader;
        //        //查询车辆是否在车辆表中
        //        string checkVehicleState = "select * from vehicle where numberPlate = '" + lic + "';";
        //        if (!remote)
        //        {
        //            reader = localDBOper.Query(checkVehicleState);
        //        }
        //        else
        //        {
        //            //Log.WriteLog(LogType.database, "update, query: \n");
        //            reader = remoteDBOper.Query(checkVehicleState);
        //            //Log.WriteLog(LogType.database, "update, query: \n" + checkVehicleState);
        //        }
        //        if (reader != null)
        //        {
        //            if (reader.Read() && reader.HasRows)
        //            {
        //                //更新车辆状态
        //                string updateVehicleState = "";
        //                string updateRemoteVehicleState = "";
        //                if (park)
        //                {
        //                    if (parkingRecordsID > 0)
        //                    {
        //                        if (parkingSpaceID == 0)
        //                        {
        //                            updateVehicleState = "update vehicle set vehiclepParkState = " + state + " ,parkingRecordsID = " + parkingRecordsID + " where numberPlate = '" + lic + "';";
        //                            updateRemoteVehicleState = "update vehicle set vehiclepParkState = " + state + " ,parkingRecordsID = " + parkingRecordsID + ",garageID = " + garageID + " where numberPlate = '" + lic + "';";
        //                        }
        //                        else
        //                        {
        //                            updateVehicleState = "update vehicle set vehiclepParkState = " + state + " ,parkingRecordsID = " + parkingRecordsID + ",parkingSpaceID = " + parkingSpaceID + " where numberPlate = '" + lic + "';";
        //                            updateRemoteVehicleState = "update vehicle set vehiclepParkState = " + state + " ,parkingRecordsID = " + parkingRecordsID + ",parkingSpaceID = " + parkingSpaceID + ",garageID = " + garageID + " where numberPlate = '" + lic + "';";
        //                        }
        //                    }
        //                    else
        //                    {
        //                        updateVehicleState = "update vehicle set vehiclepParkState = " + state + " where numberPlate = '" + lic + "';";
        //                        updateRemoteVehicleState = updateVehicleState;
        //                    }
        //                }
        //                else
        //                {
        //                    updateVehicleState = "update vehicle set vehiclepParkState = " + state + " where numberPlate = '" + lic + "';";
        //                    updateRemoteVehicleState = updateVehicleState;
        //                }
        //                List<string> list = new List<string>();
        //                if (!remote)
        //                {
        //                    list.Add(updateVehicleState);
        //                    localDBOper.UpdateTransaction(list);
        //                }
        //                else
        //                {
        //                    //Log.WriteLog(LogType.database, "update, update: \n");
        //                    list.Add(updateRemoteVehicleState);
        //                    remoteDBOper.UpdateTransaction(list);
        //                    //Log.WriteLog(LogType.database, "update, update: \n" + updateRemoteVehicleState);
        //                }
        //            }
        //            else
        //            {
        //                //插入车辆
        //                string insertVehicleWithState = "";
        //                if (park)
        //                {
        //                    if (parkingRecordsID > 0)
        //                        insertVehicleWithState = "insert into vehicle (numberPlate,vehicleTypeID,vehiclepParkState,parkingRecordsID) values " +
        //                    "('" + lic + "',NULL,'" + state + "'," + parkingRecordsID + ");";
        //                    else
        //                        insertVehicleWithState = "insert into vehicle (numberPlate,vehicleTypeID,vehiclepParkState) values " +
        //                    "('" + lic + "',NULL,'" + state + "');";
        //                }
        //                else
        //                {
        //                    Log.WriteLog(LogType.process, LogFile.ERROR, "取车无法获得车辆信息");
        //                    Console.WriteLine("明显异常，取车发现无车辆");
        //                    try
        //                    {
        //                        reader.Close();
        //                        reader.Dispose();
        //                    }
        //                    catch { }
        //                    return;
        //                }

        //                List<string> list = new List<string>();
        //                list.Add(insertVehicleWithState);
        //                int id = 0;
        //                if (!remote)
        //                {
        //                    localDBOper.Insert(list, out id);
        //                }
        //                else
        //                {
        //                    remoteDBOper.Insert(list, out id);
        //                }
        //            }
        //            try
        //            {
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            catch { }
        //        }
        //    }
        //}
        ///// <summary>
        ///// 验证会员信息
        ///// </summary>
        ///// <param name="ts"></param>
        ///// <param name="userID"></param>
        ///// <param name="license"></param>
        ///// <param name="status"></param>
        //private static void ValidateUserInfo(TerminalStru ts, int userID, string license, out int status)
        //{
        //    status = 0;
        //    //与云端数据比对
        //    string checkNetSql = "select * from user where userID = 1;";
        //    MySqlDataReader readerTest = remoteDBOper.Query(checkNetSql);
        //    if (readerTest != null)
        //    {
        //        if (readerTest.Read() && readerTest.HasRows)
        //        {
        //            try
        //            {
        //                readerTest.Close();
        //                readerTest.Dispose();
        //            }
        //            catch { }
        //            string userInfoCheckSql = "select * from usercarrelation where userID = '" + userID + "' and numberPlate = '" + license + "';";
        //            MySqlDataReader reader = remoteDBOper.Query(userInfoCheckSql);
        //            if (reader != null && reader.Read() && reader.HasRows && userID != 0 && license != "")
        //            {
        //                //ts.licVerification = 1;//验证成功
        //                //PLC.WriteToPLC(ts, PLCDataType.central);
        //                status = 1;
        //                Log.WriteLog(LogType.process, LogFile.INFO, "号牌验证成功");
        //                SetNotification("注册用户，号牌验证成功", parkMonitor.model.TextColor.Info);
        //            }
        //            else
        //            {
        //                //ts.licVerification = 1;//验证失败
        //                //PLC.WriteToPLC(ts, PLCDataType.central);
        //                status = 2;
        //                Log.WriteLog(LogType.process, LogFile.WARNING, "号牌验证失败");
        //                SetNotification("注册用户，号牌验证失败", parkMonitor.model.TextColor.Error);
        //            }
        //            try
        //            {
        //                reader.Close();
        //                reader.Dispose();
        //            }
        //            catch { }
        //        }
        //        else
        //        {
        //            try
        //            {
        //                readerTest.Close();
        //                readerTest.Dispose();
        //            }
        //            catch { }
        //            //ts.licVerification = 1;//网络异常，跳过验证
        //            //PLC.WriteToPLC(ts, PLCDataType.central);
        //            status = 1;
        //            Log.WriteLog(LogType.process, LogFile.ERROR, "网络异常，无法验证号牌");
        //        }
        //    }
        //}
        ///// <summary>
        ///// 查询停车记录id
        ///// </summary>
        ///// <param name="querySql"></param>
        ///// <param name="recordsID"></param>
        ///// <param name="remote"></param>
        //private static void QueryParkingRecordsID(string querySql, out int recordsID, bool remote)
        //{
        //    recordsID = 0;
        //    MySqlDataReader reader = null;
        //    if (!remote)
        //    {
        //        reader = localDBOper.Query(querySql);
        //    }
        //    else
        //    {
        //        reader = remoteDBOper.Query(querySql);
        //    }
        //    if (reader != null)
        //    {
        //        try
        //        {
        //            while (reader.Read())
        //            {
        //                if (reader.HasRows)
        //                    recordsID = reader.GetInt32("parkingRecordsID");
        //            }
        //        }
        //        catch (Exception e) { Log.WriteLog(LogType.process, LogFile.ERROR, "查询停车记录id失败"); Console.WriteLine(e.Message); };
        //        try
        //        {
        //            reader.Close();
        //            reader.Dispose();
        //        }
        //        catch { }
        //    }
        //}

    }
}