public string GetJSONInfo(string sql)
{
var reader = MySQLHelper.ExecuteReader(SqlConnString, MySQLHelper.CreateCommand(sql));
return(JSONHelper.ToJson(JSONHelper.DbReaderToHash(reader)));
}
public string GetJSONInfo(SqlDataReader reader)
{
return(JSONHelper.ToJson(JSONHelper.DbReaderToHash(reader)));
}
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));
}