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));
}
