FCC/Applications/SinoChem/NozzleLockConfiguration/NozzleLockAccessor.cs

using SinoChemCommonUtilities;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NozzleLockConfiguration
{
    public static class NozzleLockAccessor
    {
        static NLog.Logger logger = NLog.LogManager.LoadConfiguration("nlog.config").GetLogger("Application");

        private static SqlCommandUtility sqlCmd;

        public static void Init(string connectionString)
        {
            if (sqlCmd == null)
            {
                sqlCmd = new SqlCommandUtility(connectionString);
            }
        }

        #region sale related operations

        public static void FillinNozzles(Dictionary<int, IEnumerable<int>> fpNozzlesDict)
        {
            try
            {
                string sql;

                sql = "delete from nozzle_lock";
                sqlCmd.DeleteData(sql);

                List<int> nozzleIds = BuildNozzleListFromDict(fpNozzlesDict);
                foreach (var nId in nozzleIds)
                {
                    sql = string.Format
                        ($"insert into nozzle_lock values({nId}, {(int)NozzleLockStatus.Open}, '{DateTime.Now.ToString()}')");
                    sqlCmd.Insertdata(sql, false);
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }
        }

        public static bool IsNozzleClosedInTermsOfSale(int nozzleId)
        {
            try
            {
                NozzleLockModel model = GetNozzleLockModel(nozzleId);
                return (model != null) && (NozzleLockStatus.Clsoed == model.lock_status);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                return false;
            }
        }

        #endregion        

        #region tool related operations

        public static IEnumerable<int> RetrieveNozzleIds()
        {
            string sql = string.Format($"select * from nozzle_lock");
            List<NozzleLockModel> listOfModels = sqlCmd.ReadData<List<NozzleLockModel>>(sql, ConstructListOfNozzleLockModels);

            return listOfModels.Select(m => m.jihao);
        }

        public static void UpdateAllNozzlesLockStatus(NozzleLockStatus newStatus)
        {
            string sql = string.Format
                ($"update nozzle_lock set lock_status={(int)newStatus}, last_change_time='{DateTime.Now.ToString()}'");
            sqlCmd.UpdateData(sql);
        }

        public static void UpdateOneNozzleLockStatus(int nozzleId, NozzleLockStatus newStatus)
        {
            string sql = string.Format
                ($" update nozzle_lock set lock_status={(int)newStatus}, last_change_time='{DateTime.Now.ToString()}'" +
                 $" where jihao={nozzleId}");
            sqlCmd.UpdateData(sql);
        }

        public static NozzleLockModel GetNozzleLockModel(int nozzleId)
        {
            string sql = string.Format
                ($"select * from nozzle_lock where jihao={nozzleId}");
            NozzleLockModel model = sqlCmd.ReadData<NozzleLockModel>(sql, ConstructNozzleLockModel);
            return model;
        }

        #endregion

        #region supporting methods

        private static List<int> BuildNozzleListFromDict(Dictionary<int, IEnumerable<int>> fpNozzlesDict)
        {
            List<int> nIds = new List<int>();
            foreach (IEnumerable<int> item in fpNozzlesDict.Values)
            {
                nIds.AddRange(item);
            }
            nIds.Sort();
            return nIds;
        }

        private static NozzleLockModel ConstructNozzleLockModel(SqlDataReader reader)
        {
            if (reader.Read())
            {
                return FillinNozzleLockModel(reader);
            }
            else
            {
                return null;
            }
        }

        private static List<NozzleLockModel> ConstructListOfNozzleLockModels(SqlDataReader reader)
        {
            List<NozzleLockModel> list = new List<NozzleLockModel>();
            while (reader.Read())
            {
                NozzleLockModel model = FillinNozzleLockModel(reader);
                list.Add(model);
            }

            return list;
        }

        private static NozzleLockModel FillinNozzleLockModel(SqlDataReader reader)
        {
            return new NozzleLockModel()
            {
                jihao = DbFieldReader.SafelyReadDbField<int>(reader["jihao"]),
                lock_status = (NozzleLockStatus)DbFieldReader.SafelyReadDbField<int>(reader["lock_status"]),
                last_change_time = DbFieldReader.SafelyReadDbField<DateTime>(reader["last_change_time"]),
            };
        }

        #endregion
    }
}