FCC/Applications/SinoChem/SinochemInternetPlusApp/PumpStatusToSinoChemPosDatabaseCommunicator/SiteConfigUtility.cs

using SinochemInternetPlusApp;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SinoChemFC2PosProxy
{
    internal class SiteConfigUtility
    {
        private static SiteConfigUtility instance = new SiteConfigUtility();

        private object syncObject = new object();
        private IEnumerable<DataRow> latestConfig = null;

        private SiteConfigUtility()
        {
        }

        public static SiteConfigUtility Default
        {
            get { return instance; }
        }

        /// <summary>
        /// format:
        /// oneRow["jiHao"]  oneRow["sideId"]  oneRow["HoseLogicalId"]  oneRow["siteLevelNozzleId"] oneRow["pumpid"]
        /// </summary>
        /// <param name="latestConfig"></param>
        public void UpdateLatestSiteConfig(IEnumerable<DataRow> config)
        {
            lock (this.syncObject)
            {
                this.latestConfig = config;
            }
        }

        public IEnumerable<int> GetAllSiteLevelNozzleIds()
        {
            lock (this.syncObject)
            {
                return this.latestConfig.Select(r => int.Parse(r["siteLevelNozzleId"].ToString()));
            }
        }

        /// <summary>
        /// query from latest site configuration to get the site level nozzle id by pump and nozzle logical id.
        /// </summary>
        /// <param name="pumpId">each side of a physical pump contains 2 logical pump which have 2 pump ids.</param>
        /// <param name="logicalNozzleId">start from 1, typically a pump have 4 nozzles</param>
        /// <returns>-1 indicate query failed.</returns>
        public int GetSiteLevelNozzleIdByLogicalNozzleId(int pumpId, int logicalNozzleId)
        {
            lock (this.syncObject)
            {
                var targetRow =
                    latestConfig.FirstOrDefault(
                        r => r["pumpid"].ToString() == pumpId.ToString() &&
                             r["HoseLogicalId"].ToString() == logicalNozzleId.ToString());
                if (targetRow != null)
                {
                    return int.Parse(targetRow["siteLevelNozzleId"].ToString());
                }

                return -1;
            }
        }

        /// <summary>
        /// all site level nozzle ids for a pump, typically a pump have 4 nozzles.
        /// </summary>
        /// <param name="pumpId"></param>
        /// <returns></returns>
        public IEnumerable<int> GetSiteLevelNozzleIdsByPumpId(int pumpId)
        {
            lock (this.syncObject)
            {
                var targetRows = latestConfig.Where(r => r["pumpid"].ToString() == pumpId.ToString());
                if (targetRows.Any())
                {
                    return targetRows.Select(r => int.Parse(r["siteLevelNozzleId"].ToString()));
                }

                return null;
            }
        }

        /// <summary>
        /// Get the pump totalizer for specific nozzle.
        /// The reading is from FC database.
        /// </summary>
        /// <param name="pumpId">from 1, site unique</param>
        /// <param name="logicalNozzleId">nozzle id in a pump, start from 1, typically a pump have 4 nozzles.</param>
        /// <returns>first item is VolumeTotalizer, second item is MoneyTotalizer></returns>
        public Tuple<float, float> GetTotalizer(int pumpId, int logicalNozzleId)
        {
            using (
                var fcOdbcConnection =
                    new SqlConnection(App.AppSettings["FCDatabaseConnStr"]))
            {
                using (var oda = new SqlDataAdapter(
                    string.Format("select attr_name,attr_value from " +
                                  "ssf_device_data where attr_name in ('VolumeTotalizer', 'MoneyTotalizer') " +
                                  "and device_id='{0}' and subdevice_id='{1}'", pumpId, logicalNozzleId),
                    fcOdbcConnection))
                {
                    var totalizersInFcDb = new DataTable();
                    oda.Fill(totalizersInFcDb);
                    var totalizersDataRowsInFcDb = totalizersInFcDb.Rows.Cast<DataRow>();
                    if (!totalizersDataRowsInFcDb.Any())
                        return new Tuple<float, float>(0, 0);
                    var VolumeTotalizerRow =
                        totalizersDataRowsInFcDb.FirstOrDefault(r => r["attr_name"].ToString() == "VolumeTotalizer");
                    var MoneyTotalizerRow =
                        totalizersDataRowsInFcDb.FirstOrDefault(r => r["attr_name"].ToString() == "MoneyTotalizer");
                    var result =
                        new Tuple<float, float>(
                            VolumeTotalizerRow == null ? 0 : float.Parse(VolumeTotalizerRow["attr_value"].ToString()),
                            MoneyTotalizerRow == null ? 0 : float.Parse(MoneyTotalizerRow["attr_value"].ToString()));
                    return result;
                }
            }
        }

        /// <summary>
        /// Get the pump nozzle totalizer which saved in POS database(table: jy_info, column fzqty and fzamount) for specific nozzle.
        /// The reading is from POS database.
        /// </summary>
        /// <param name="pumpId">from 1, site unique</param>
        /// <param name="logicalNozzleId">nozzle id in a pump, start from 1, typically a pump have 4 nozzles.</param>
        /// <returns>first item is fzqty(VolumeTotalizer), second item is fzamount(MoneyTotalizer)</returns>
        public Tuple<string, float> GetTotalizerFromPos(int pumpId, int logicalNozzleId)
        {
            int jihao = GetSiteLevelNozzleIdByLogicalNozzleId(pumpId, logicalNozzleId);
            using (
                var posSqlConnection =
                    new SqlConnection(App.AppSettings["PosDatabaseConnStr"]))
            {
                using (var sda = new SqlDataAdapter(
                    string.Format("select fzqty,fzamount from jy_info where jihao={0}", jihao),
                    posSqlConnection))
                {
                    var totalizersInPosDb = new DataTable();
                    sda.Fill(totalizersInPosDb);
                    if (totalizersInPosDb.Rows.Count == 0)
                        return new Tuple<string, float>("0", 0);
                    var result =
                        new Tuple<string, float>((string)(totalizersInPosDb.Rows[0]["fzqty"]),
                            float.Parse(totalizersInPosDb.Rows[0]["fzamount"].ToString()));
                    return result;
                }
            }
        }

        /// <summary>
        /// Get the pump totalizer for specific nozzle.
        /// The reading is from FC database.
        /// </summary>
        /// <param name="siteLevelNozzleId">site level nozzle id, start from 1, site unique</param>
        /// <returns>first item is VolumeTotalizer, second item is MoneyTotalizer></returns>
        public Tuple<float, float> GetTotalizer(int siteLevelNozzleId)
        {
            int pumpId = -1;
            int hoseLogicalId = -1;
            lock (this.syncObject)
            {
                var targetRow =
                    this.latestConfig.First(r => r["siteLevelNozzleId"].ToString() == siteLevelNozzleId.ToString());
                pumpId = int.Parse(targetRow["pumpid"].ToString());
                hoseLogicalId = int.Parse(targetRow["HoseLogicalId"].ToString());
            }

            return this.GetTotalizer(pumpId, hoseLogicalId);
        }

        /// <summary>
        /// Get the grade name which configurated on a nozzle.
        /// </summary>
        /// <param name="pumpId">from 1, site unique</param>
        /// <param name="logicalNozzleId">nozzle id in a pump, start from 1, typically a pump have 4 nozzles.</param>
        /// <returns>SYN 2000, DIESEL and etc.</returns>
        public string GetGradeName(int pumpId, int logicalNozzleId)
        {
            using (
                var fcOdbcConnection =
                    new SqlConnection(App.AppSettings["FCDatabaseConnStr"]))
            {
                var selectCmd = new SqlCommand(
                    string.Format("select param_value from " +
                                  "config_values where library='pump' and groupname='subinfo' and id='{0}' and device_id = 'hose{1}' " +
                                  "and parameter='grade' "
                        , pumpId, logicalNozzleId),
                    fcOdbcConnection);
                fcOdbcConnection.Open();
                var gradeName = selectCmd.ExecuteScalar().ToString();
                return gradeName;
            }
        }

        public string GetGradeName(int siteLevelNozzleId)
        {
            int pumpId = -1;
            int hoseLogicalId = -1;
            lock (this.syncObject)
            {
                var targetRow =
                    this.latestConfig.First(r => r["siteLevelNozzleId"].ToString() == siteLevelNozzleId.ToString());
                pumpId = int.Parse(targetRow["pumpid"].ToString());
                hoseLogicalId = int.Parse(targetRow["HoseLogicalId"].ToString());
            }

            return this.GetGradeName(pumpId, hoseLogicalId);
        }

        /// <summary>
        /// Get the grade name by its grade id, grade id is like: 2,3,4,7
        /// </summary>
        /// <param name="gradeId">like: 2,3,4,7</param>
        /// <returns>SYN 2000, DIESEL and etc.</returns>
        public string GetGradeNameByGradeId(int gradeId)
        {
            using (
                var fcOdbcConnection =
                    new SqlConnection(App.AppSettings["FCDatabaseConnStr"]))
            {
                var selectCmd = new SqlCommand(
                    string.Format("select id from " +
                                  "config_values where library='grade' and groupname='general' and parameter='number' and param_value='{0}' "
                        , gradeId),
                    fcOdbcConnection);
                fcOdbcConnection.Open();
                var gradeName = selectCmd.ExecuteScalar().ToString();
                return gradeName;
            }
        }
    }
}