/// <summary> /// get total data /// </summary> /// <param name="param"></param> /// <returns></returns> private string GetCountSql(SpatialDimensionParam param) { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append($"select Count(temp.{param.DistanceAlias}) from ("); stringBuilder.Append($" {GetDataSql(param)}"); stringBuilder.Append(" )as temp"); return(stringBuilder.ToString()); }
/// <summary> /// get list /// </summary> /// <param name="param"></param> /// <typeparam name="T"></typeparam> /// <returns></returns> public List <T> GetList <T>(SpatialDimensionParam param) { if (param == null) { return(default(List <T>)); } CheckParam(param); return(_execute.SqlQuery <T>(GetDataSql(param))); }
/// <summary> /// Get sql by Latitude and longitude /// </summary> /// <param name="param"></param> /// <param name="pageIndex">page index</param> /// <param name="pageSize">page size</param> /// <returns></returns> private string GetDataSql(SpatialDimensionParam param, int pageIndex, int pageSize) { string sql = GetDataSql(param); if (pageSize != -1) { sql += $" limit {(pageIndex - 1) * pageSize},{pageSize}"; } return(sql); }
/// <summary> /// Check Param /// </summary> /// <param name="param"></param> private void CheckParam(SpatialDimensionParam param) { param.TableName.IsNullOrEmptyTip("The TableName cannot be empty"); Check.True(!(param.FileKeys == null || param.FileKeys.Count == 0), "The FileKeys cannot be empty"); Check.True(!(string.IsNullOrEmpty(param.DistanceAlias)), "The DistanceAlias cannot be empty"); Check.True(!(param.Point.Equals(default(KeyValuePair <string, string>))), "The Point cannot be empty"); Check.True(param.Distance > 0 || param.Distance == -1, "The distance has to be greater than 0 or equal -1"); Check.True(param.MinDistance >= 0, "The distance has to be greater than 0 or equals 0"); Check.True(!param.Location.Equals(default(KeyValuePair <decimal, decimal>)), "The Location Is Error"); }
/// <summary> /// Get sql by Latitude and longitude /// </summary> /// <param name="param"></param> /// <returns></returns> private string GetDataSql(SpatialDimensionParam param) { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("select "); string tableAlias = "epointTable"; param.FileKeys.ForEach(fileKey => { stringBuilder.Append($"epointTable.{fileKey.Key} as '{fileKey.Value}',"); }); stringBuilder.Append( $"((st_distance (point (epointTable.{param.Point.Key}, epointTable.{param.Point.Value}),point({param.Location.Key},{param.Location.Value}) ) / 0.0111)*1000) AS '{param.DistanceAlias}'"); stringBuilder.Append($" FROM {param.TableName} as {tableAlias}"); stringBuilder.Append($" HAVING {param.DistanceAlias}>{param.MinDistance}"); if (param.Distance != -1) { stringBuilder.Append($" And {param.DistanceAlias}<{param.Distance}"); } bool isFirst = true; param.Sorts?.ForEach(item => { if (isFirst) { stringBuilder.Append(" ORDER BY "); } stringBuilder.Append($"{item.Key}"); if (item.Value) { stringBuilder.Append(" desc,"); } else { stringBuilder.Append(" asc,"); } isFirst = false; }); string sql = stringBuilder.ToString(); if (!isFirst) { sql = sql.Substring(0, sql.Length - 1); } return(sql); }
/// <summary> /// get list /// </summary> /// <returns></returns> public IQueryable <TEntity> GetQueryable(SpatialDimensionParam param) { CheckParam(param); return(Dbcontext.Set <TEntity>().FromSql(GetDataSql(param))); }
/// <summary> /// get IQueryable /// </summary> /// <returns></returns> public IQueryable <TEntity> GetQueryable(SpatialDimensionParam param) { return(_spatialDimensionBase.GetQueryable(param)); }
/// <summary> /// get list /// </summary> /// <param name="param"></param> /// <typeparam name="T"></typeparam> /// <returns></returns> public List <TOpt> GetList <TOpt>(SpatialDimensionParam param) { return(_spatialDimensionBase.GetList <TOpt>(param)); }
/// <summary> /// get list /// </summary> /// <returns></returns> public IQueryable <TEntity> GetQueryable(SpatialDimensionParam param) { throw new NotImplementedException(); }
/// <summary> /// get list /// </summary> /// <param name="param"></param> /// <typeparam name="T"></typeparam> /// <returns></returns> public virtual List <T> GetList <T>(SpatialDimensionParam param) { throw new NotImplementedException(); }
/// <summary> /// get list /// </summary> /// <param name="param"></param> /// <typeparam name="T"></typeparam> /// <returns></returns> public List <T> GetList <T>(SpatialDimensionParam param) { throw new System.NotImplementedException(); }