Beispiel #1
0
    public void GetChannelDataNet(string siteId, string siteAliasName, string loc, string loggerId, DateTime start, DateTime end, ref List <MMVModel> data)
    {
        double?max = null;
        double?min = null;
        double?avg = null;

        string frwdId = null;
        string rvrsId = null;


        string cnnStr = ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;

        using (SqlConnection cnn = new SqlConnection(cnnStr))
        {
            string     cmdText = "select [channelId] from t_channel_configurations where [forwardFlow]=1 and [loggerId]='" + loggerId + "'";
            SqlCommand cmd     = new SqlCommand(cmdText, cnn);

            cnn.Open();

            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                if (rd[0] != DBNull.Value)
                {
                    frwdId = (string)rd[0];
                }
            }

            cnn.Close();
        }

        using (SqlConnection cnn = new SqlConnection(cnnStr))
        {
            string     cmdText = "select [channelId] from t_channel_configurations where [reverseFlow]=1 and [loggerId]='" + loggerId + "'";
            SqlCommand cmd     = new SqlCommand(cmdText, cnn);

            cnn.Open();

            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                if (rd[0] != DBNull.Value)
                {
                    rvrsId = (string)rd[0];
                }
            }

            cnn.Close();
        }

        if (string.IsNullOrEmpty(frwdId) && string.IsNullOrEmpty(rvrsId))
        {
            return;
        }

        if (!string.IsNullOrEmpty(frwdId) && string.IsNullOrEmpty(rvrsId))
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                string cmdText = "select max([value]) as mx,min([value]) as mn,avg([value]) as vg from t_data_logger_" + frwdId + " where [timestamp] between '"
                                 + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                SqlCommand cmd = new SqlCommand(cmdText, cnn);

                cnn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    if (rd["mx"] != DBNull.Value)
                    {
                        max = (double)rd["mx"];
                    }

                    if (rd["mn"] != DBNull.Value)
                    {
                        min = (double)rd["mn"];
                    }

                    if (rd["vg"] != DBNull.Value)
                    {
                        avg = (double)rd["vg"];
                    }
                }

                cnn.Close();
            }

            if (max != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[value] from t_data_logger_" + frwdId + " where [value]=" + max + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Max";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (min != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[value] from t_data_logger_" + frwdId + " where [value]=" + min + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Min";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (avg != null)
            {
                MMVModel d = new MMVModel();

                d.Loc           = loc;
                d.LoggerId      = loggerId;
                d.SiteAliasName = siteAliasName;
                d.SiteId        = siteId;
                d.Unit          = "m3/h";
                d.Type          = "net";
                d.TypeVal       = "Avg";
                d.Val           = avg;
                data.Add(d);
            }
        }

        if (string.IsNullOrEmpty(frwdId) && !string.IsNullOrEmpty(rvrsId))
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                string cmdText = "select max(-[value]) as mx,min(-[value]) as mn,avg(-[value]) as vg from t_data_logger_" + rvrsId + " where [timestamp] between '"
                                 + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                SqlCommand cmd = new SqlCommand(cmdText, cnn);

                cnn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    if (rd["mx"] != DBNull.Value)
                    {
                        max = (double)rd["mx"];
                    }

                    if (rd["mn"] != DBNull.Value)
                    {
                        min = (double)rd["mn"];
                    }

                    if (rd["vg"] != DBNull.Value)
                    {
                        avg = (double)rd["vg"];
                    }
                }

                cnn.Close();
            }

            if (max != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],(-[value]) as [value] from t_data_logger_" + rvrsId + " where (-[value])=" + max + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Max";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (min != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],(-[value]) as [value] from t_data_logger_" + frwdId + " where (-[value])=" + min + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Min";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (avg != null)
            {
                MMVModel d = new MMVModel();
                d.Loc           = loc;
                d.LoggerId      = loggerId;
                d.SiteAliasName = siteAliasName;
                d.SiteId        = siteId;
                d.Unit          = "m3/h";
                d.Type          = "net";
                d.TypeVal       = "Avg";
                d.Val           = avg;
                data.Add(d);
            }
        }

        if (!string.IsNullOrEmpty(frwdId) && !string.IsNullOrEmpty(rvrsId))
        {
            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                string cmdText = "select max(net) as mx,min(net) as mn,avg(net) as vg from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
                                 + @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                SqlCommand cmd = new SqlCommand(cmdText, cnn);

                cnn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    if (rd["mx"] != DBNull.Value)
                    {
                        max = (double)rd["mx"];
                    }

                    if (rd["mn"] != DBNull.Value)
                    {
                        min = (double)rd["mn"];
                    }

                    if (rd["vg"] != DBNull.Value)
                    {
                        avg = (double)rd["vg"];
                    }
                }

                cnn.Close();
            }

            if (max != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[net] as [value] from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
                                     + @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [net]=" + max + " and [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Max";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (min != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[net] as [value] from (select t1.[timestamp], t1.[value] as frwd, t2.[value] as rvrs, isnull(t1.[value],0)-isnull(t2.[value],0) as net from t_data_logger_"
                                     + @frwdId + " t1 left join t_data_logger_" + @rvrsId + " t2 on t1.[timestamp]=t2.[timestamp]) t where [net]=" + min + " and [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Unit          = "m3/h";
                        d.Type          = "net";
                        d.TypeVal       = "Min";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (avg != null)
            {
                MMVModel d = new MMVModel();

                d.Loc           = loc;
                d.LoggerId      = loggerId;
                d.SiteAliasName = siteAliasName;
                d.SiteId        = siteId;
                d.Unit          = "m3/h";
                d.Type          = "net";
                d.TypeVal       = "Avg";
                d.Val           = avg;
                data.Add(d);
            }
        }
    }
Beispiel #2
0
    public void GetChannelData(string siteId, string siteAliasName, string loc, string loggerId, string typeStr, string type, DateTime start, DateTime end, ref List <MMVModel> data)
    {
        string chnId   = "";
        string chnName = "";
        string unit    = "";

        string cnnStr = ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString;

        using (SqlConnection cnn = new SqlConnection(cnnStr))
        {
            string     cmdText = "select [channelId],[channelName],[unit] from t_channel_configurations where [loggerId]='" + loggerId + "' and " + typeStr;
            SqlCommand cmd     = new SqlCommand(cmdText, cnn);

            cnn.Open();

            SqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                if (rd["channelId"] != DBNull.Value)
                {
                    chnId = (string)rd["channelId"];
                }

                if (rd["channelName"] != DBNull.Value)
                {
                    chnName = (string)rd["channelName"];
                }

                if (rd["unit"] != DBNull.Value)
                {
                    unit = (string)rd["unit"];
                }
            }

            cnn.Close();
        }

        if (!string.IsNullOrEmpty(chnId))
        {
            double?max = null;
            double?min = null;
            double?avg = null;

            using (SqlConnection cnn = new SqlConnection(cnnStr))
            {
                string cmdText = "select max([value]) as mx,min([value]) as mn,avg([value]) as vg from t_Data_Logger_" + chnId
                                 + " where [timestamp] between '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm") + "'";

                SqlCommand cmd = new SqlCommand(cmdText, cnn);

                cnn.Open();

                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    if (rd["mx"] != DBNull.Value)
                    {
                        max = (double)rd["mx"];
                    }

                    if (rd["mn"] != DBNull.Value)
                    {
                        min = (double)rd["mn"];
                    }

                    if (rd["vg"] != DBNull.Value)
                    {
                        avg = (double)rd["vg"];
                    }
                }

                cnn.Close();
            }

            if (max != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[value] from t_Data_Logger_" + chnId + " where [value]=" + max + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.ChnId         = chnId;
                        d.ChnName       = chnName;
                        d.Unit          = unit;
                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Type          = type;
                        d.TypeVal       = "Max";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (min != null)
            {
                using (SqlConnection cnn = new SqlConnection(cnnStr))
                {
                    string cmdText = "select [timestamp],[value] from t_Data_Logger_" + chnId + " where [value]=" + min + " and [timestamp] between '"
                                     + start.ToString("yyyy-MM-dd HH:mm:ss") + "' and '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "'";

                    SqlCommand cmd = new SqlCommand(cmdText, cnn);

                    cnn.Open();

                    SqlDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        MMVModel d = new MMVModel();

                        if (rd["timestamp"] != DBNull.Value)
                        {
                            d.Tmstmp = (DateTime)rd["timestamp"];
                        }

                        if (rd["value"] != DBNull.Value)
                        {
                            d.Val = (double)rd["value"];
                        }

                        d.ChnId         = chnId;
                        d.ChnName       = chnName;
                        d.Unit          = unit;
                        d.Loc           = loc;
                        d.LoggerId      = loggerId;
                        d.SiteAliasName = siteAliasName;
                        d.SiteId        = siteId;
                        d.Type          = type;
                        d.TypeVal       = "Min";

                        data.Add(d);
                    }

                    cnn.Close();
                }
            }

            if (avg != null)
            {
                MMVModel d = new MMVModel();

                d.Loc           = loc;
                d.LoggerId      = loggerId;
                d.SiteAliasName = siteAliasName;
                d.SiteId        = siteId;
                d.Type          = type;
                d.TypeVal       = "Avg";
                d.Unit          = unit;
                d.Val           = avg;
                data.Add(d);
            }
        }
    }