public string GetRentsByCoordinatePopular(string lat, string lon, string radios) { var list = new List <Hashtable>(); string sql = "select rid,rentno,ROwner,ROwnerTel,RIDCard,RAddress,RDName,RRName,RRentTypeDesc,RLocationDescription,RRentType,Latitude,Longitude,RentNumber as Status,rFloor,rtotalfloor,rrentarea,rroomtypedesc, rdirectiondesc, RPropertyDesc from v_RentDetail_view where IsObsoleted = 0 and rid in(380,451,419,417,375,452,379,363,418,365,377,366) order by RCreatedDate desc"; DataSet ds = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sql)); foreach (DataRow row in ds.Tables[0].Rows) { if (row["Latitude"] != null && row["Longitude"] != null) { double la, lo; if (double.TryParse(row["Latitude"].ToString(), out la) && double.TryParse(row["Longitude"].ToString(), out lo)) { DataTable dt = ds.Tables[0].Clone(); double distance = GetDistance(double.Parse(lat), double.Parse(lon), double.Parse(row["Latitude"].ToString()), double.Parse(row["Longitude"].ToString())); if (distance <= double.Parse(radios)) { //retRows.Add(row); var item = new Hashtable(); for (var i = 0; i < dt.Columns.Count; i++) { var name = dt.Columns[i].ColumnName; var value = row[i].ToString(); item[name] = value; } if (IsExistsRedPackage(row["rentno"].ToString(), null)) { item["IsRedPackage"] = "1"; } else { item["IsRedPackage"] = "0"; } list.Add(item); if (list.Count == 10) { break; } } } else { var item = new Hashtable(); DataTable dt = ds.Tables[0].Clone(); for (var i = 0; i < dt.Columns.Count; i++) { var name = dt.Columns[i].ColumnName; var value = row[i].ToString(); item[name] = value; } list.Add(item); if (list.Count == 10) { break; } } } else { var item = new Hashtable(); DataTable dt = ds.Tables[0].Clone(); for (var i = 0; i < dt.Columns.Count; i++) { var name = dt.Columns[i].ColumnName; var value = row[i].ToString(); item[name] = value; } list.Add(item); if (list.Count == 10) { break; } } } ////查询符合地区的所有数据 //string sqlArray = string.Empty; //for (int j = 0; j < list.Count; j++) //{ // if (j == (list.Count - 1)) // { // sqlArray += "'" + list[j]["rentno"].ToString() + "'"; // } // else // { // sqlArray += "'" + list[j]["rentno"].ToString() + "',"; // } //} //var arrayKayObject = new Hashtable(); //配凑Key=>value //if (!string.IsNullOrEmpty(sqlArray)) //{ // //根据地区的房屋id获取该地区评分高的数据 // string sqlEvaluation = "SELECT top 10 RentNO, sum(count)/count(*) as count FROM (SELECT RentNO, (EvaluateItem0+EvaluateItem1+EvaluateItem2)/3 as count FROM v_Rent_Evaluation_view WHERE EvaluateType = 1 and RentNO in(" + sqlArray + ")) as a GROUP BY a.RentNO ORDER BY count DESC"; // DataSet dataEvaluation = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sqlEvaluation)); // foreach (DataRow row in dataEvaluation.Tables[0].Rows) // { // var name = row["RentNO"].ToString(); // var value = row["count"].ToString(); // arrayKayObject[name] = value; // } //} ////筛选需要的数据 //var jsonList = new List<Hashtable>(); //for (int k = 0; k < list.Count; k++) //{ // string rentNo = list[k]["rentno"].ToString(); // //筛选key // if (arrayKayObject.Contains(rentNo)) // { // RentImageHelper helper = new RentImageHelper(); // string count = string.Empty; // List<Hashtable> images = helper.GetRentImages(rentNo, out count); // var item1 = new Hashtable(); // for (int q = 0; q < images.Count; q++) // { // list[k].Add("Image" + q, images[q]["ImagePath"].ToString()); //添加图片 // } // list[k].Add("Evaluate", arrayKayObject[rentNo]); //添加评分 // jsonList.Add(list[k]); // } //} for (int j = 0; j < list.Count; j++) { string rentNo = list[j]["rentno"].ToString(); RentImageHelper helper = new RentImageHelper(); string count = string.Empty; List <Hashtable> images = helper.GetRentImages(rentNo, out count); var item1 = new Hashtable(); for (int k = 0; k < images.Count; k++) { list[j].Add("Image" + k, images[k]["ImagePath"].ToString()); } //string sqlEvaluation = "select * from v_Rent_Evaluation_view where RentNO = '" + rentNo + "'"; //DataSet dataEvaluation = MySQLHelper.ExecuteDataset(SqlConnString, MySQLHelper.CreateCommand(sqlEvaluation)); //Double numAll = 0; //Int32 numberPeople = 0; //foreach (DataRow row in dataEvaluation.Tables[0].Rows) //{ // Double EvaluateItem0 = double.Parse(row["EvaluateItem0"].ToString()); // Double EvaluateItem1 = double.Parse(row["EvaluateItem1"].ToString()); // Double EvaluateItem2 = double.Parse(row["EvaluateItem2"].ToString()); // Double num = (EvaluateItem0 + EvaluateItem1 + EvaluateItem2) / 3; // numAll += num; // numberPeople++; //} //Double average = double.IsNaN(numAll / numberPeople) ? 0 : (numAll / numberPeople); //list[j].Add("Evaluate", average); int[] strArr1 = { 0, 1, 2 }; bool exists1 = ((IList)strArr1).Contains(j); int[] strArr2 = { 3, 4, 5, 6 }; bool exists2 = ((IList)strArr2).Contains(j); int[] strArr3 = { 7, 8, 9 }; bool exists3 = ((IList)strArr3).Contains(j); if (exists1) { list[j].Add("Evaluate", 5.0); } else if (exists2) { list[j].Add("Evaluate", 4.9); } else { list[j].Add("Evaluate", 4.8); } } return(JSONHelper.ToJson(list)); }
public string GetHousesByCondition(int pageSize, int pageIndex, string housetype, string rentType, string address, string isAvalible, string userID, string startdate, string endate) { string where = "1=1 and isObsoleted=0 "; if (!string.IsNullOrEmpty(housetype)) { where += " and RRoomType='" + housetype + "'"; } if (!string.IsNullOrEmpty(rentType)) { where += " and RRentType='" + rentType + "'"; } if (!string.IsNullOrEmpty(address)) { where += " and RAddress like '%" + address + "%'"; } string dateSql = "select distinct rentno from Rent_RentAttribute where (((rrastartdate BETWEEN '" + startdate + "' and '" + endate + "') or (rraenddate BETWEEN '" + startdate + "' and '" + endate + "')) or ((rrastartdate <'" + startdate + "' and rraenddate>'" + endate + "'))) and rrastatus in ('0','1','2','6')"; DataTable dateDt = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(dateSql)).Tables[0]; dateSql = string.Empty; foreach (DataRow row in dateDt.Rows) { dateSql += "'" + row["rentno"].ToString() + "',"; } dateSql = " and rentno not in (" + dateSql + "'')"; RentInfoHelper helper = new RentInfoHelper(); if (!userID.Equals("1")) { string tempStr = string.Empty; DataTable dt = helper.GetDataTable("select * from dbo.Rent_user_dept_relationship where t_wu_user_id = (select userid from cf_user where loginname = '" + userID + "')"); foreach (DataRow row in dt.Rows) { tempStr += row["t_ad_reg_dept_id"].ToString() + ","; } if (!string.IsNullOrEmpty(tempStr)) { tempStr = tempStr.Substring(0, tempStr.Length - 1); where += " and Region in (" + tempStr + ")"; } } where += dateSql; System.Data.SqlClient.SqlDataReader r = helper.GetList(pageSize, pageIndex, "v_RentDetail_view", "RID", where, "*", "RCreatedDate", true); //return helper.GetJSONInfo(r); var list = new List <Hashtable>(); list = JSONHelper.DbReaderToHash(r); //查询所有数据 string sqlArray = string.Empty; for (int j = 0; j < list.Count; j++) { if (j == (list.Count - 1)) { sqlArray += "'" + list[j]["RentNO"].ToString() + "'"; } else { sqlArray += "'" + list[j]["RentNO"].ToString() + "',"; } } var arrayKayObject = new Hashtable(); //配凑Key=>value if (!string.IsNullOrEmpty(sqlArray)) { //根据地区的房屋id获取该地区评分的数据 string sqlEvaluation = "SELECT RentNO, sum(count)/count(*) as count FROM (SELECT RentNO, (EvaluateItem0+EvaluateItem1+EvaluateItem2)/3 as count FROM v_Rent_Evaluation_view WHERE EvaluateType = 1 and RentNO in(" + sqlArray + ")) as a GROUP BY a.RentNO ORDER BY count DESC"; DataSet dataEvaluation = MySQLHelper.ExecuteDataset(MySQLHelper.SqlConnString, MySQLHelper.CreateCommand(sqlEvaluation)); foreach (DataRow row in dataEvaluation.Tables[0].Rows) { var name = row["RentNO"].ToString(); var value = row["count"].ToString(); arrayKayObject[name] = value; } } for (int k = 0; k < list.Count; k++) { string rentNo = list[k]["RentNO"].ToString(); RentImageHelper helperImage = new RentImageHelper(); string count = string.Empty; List <Hashtable> images = helperImage.GetRentImages(rentNo, out count); var item1 = new Hashtable(); for (int q = 0; q < images.Count; q++) { list[k].Add("Image" + q, images[q]["ImagePath"].ToString()); //添加图片 } //筛选key if (arrayKayObject.Contains(rentNo)) { list[k].Add("Evaluate", arrayKayObject[rentNo]); //添加评分 } } return(JSONHelper.ToJson(list)); }