NBDXSY/LeaRun.Application/LeaRun.Application.Service/WaterWellManage/RealDataService.cs

using LeaRun.Application.Entity.WaterWellManage;
using LeaRun.Data;
using LeaRun.Util;
using LeaRun.Util.WebControl;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LeaRun.Application.Service.WaterWellManage
{
    public class RealDataService
    {
        SqlHelper sqlHelper = new SqlHelper("WaterWellDb");

        #region 查询实时数据
        /// <summary>
        /// 查询实时数据
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="keyword"></param>
        /// <param name="value"></param>
        /// <param name="isAll"></param>
        /// <returns></returns>
        public string GetRealData(Pagination pagination, string keyword, string value, bool isAll)
        {
            var watch = CommonHelper.TimerStart();
            var where = " Where 1=1 ";
            StringBuilder sb = new StringBuilder();
            if ("".Equals(value) && !isAll)
            {
                var JsonData = new
                {
                    rows = "",
                    total = pagination.total,
                    page = pagination.page,
                    records = pagination.records,
                    costtime = CommonHelper.TimerEnd(watch)
                };
                return JsonData.ToJson();
            }
            else
            {
                if (!isAll)
                {
                    string[] arr = value.Split(',');
                    string[] nodeArr = new string[arr.Length - 1];
                    for (int i = 0; i < arr.Length - 1; i++)
                    {
                        nodeArr[i] = arr[i].Replace("dev", "");
                    }
                    sb.Append("where waterWellid in (");
                    for (int i = 0; i < nodeArr.Length; i++)
                    {
                        sb.Append("'" + nodeArr[i] + "'");
                        if (i < nodeArr.Length - 1)
                        {
                            sb.Append(", ");
                        }

                    }
                    if (!"".Equals(keyword))
                    {
                        sb.Append(") and waterWellName like '%" + keyword + "%'");
                    }
                    else
                    {
                        sb.Append(")");
                    }
                    where = sb.ToString();
                }
                List<RealDataEntity> list = GetDataByTableName(pagination, keyword, where);
                var JsonData = new
                {
                    rows = list,
                    total = pagination.total,
                    page = pagination.page,
                    records = pagination.records,
                    costtime = CommonHelper.TimerEnd(watch)
                };
                return JsonData.ToJson();
            }

        }
        #endregion

        #region 查询出表中所有的数据
        /// <summary>
        /// 查询出表中所有的数据
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="nodes"></param>
        /// <param name="isAll"></param>
        /// <returns></returns>
        private List<RealDataEntity> GetDataByTableName(Pagination pagination, string keyword, string where)
        {
            string sql = " select count(1) from WaterWellBase " + where;
            pagination.records = Convert.ToInt32(sqlHelper.ExecuteScalar(sql, CommandType.Text, null));
            int start = (pagination.page - 1) * pagination.rows + 1;
            int end = pagination.page * pagination.rows;
            sql = "SELECT * FROM " +
                "(             " +
                "SELECT        " +
                "    ROW_NUMBER() OVER(ORDER BY GetDateTime) AS RowIndex," +
                "    *         " +
                "FROM          " +
                "    (select  * " +
                "    From WaterWellBase " + where + ") as mytable " +
                " )" +
                " AS PageTable " +
                " WHERE RowIndex BETWEEN " + start + " AND " + end;
            try
            {
                DataTable dt = sqlHelper.ExecuteDataTable(sql, CommandType.Text, null);
                if (dt.Rows.Count == 0)
                {
                    return null;
                }
                List<RealDataEntity> list = DataHelper.DataTableToT<RealDataEntity>(dt);
                return list;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion
    }
}