public static AjaxResult SetHouseStatus()
{
RentInfoHelper helper = new RentInfoHelper();
string sql = "select * from v_RentHistory_view where RRAIsActive = 0 and RRAEndDate<'" + DateTime.Now.ToString("yyyy-MM-dd") + "' and isavailable='已租'";
DataTable dt = helper.GetDataTable(sql);
foreach (DataRow row in dt.Rows)
{
sql = "update rent_rent set isavailable=0 where rentno='" + row["RentNO"].ToString() + "'";
helper.ExcuteSql(sql);
sql = "Update rent_rentAttribute set RRAIsActive=1 where RRAID=" + row["RRAID"].ToString();
helper.ExcuteSql(sql);
}
return(AjaxResult.Success());
}
public static AjaxResult UpdateUserAreaRef(string User, string DataJSON)
{
var data = new JavaScriptSerializer().Deserialize <List <Dictionary <string, object> > >(DataJSON);
UserInfoHelper helper = new UserInfoHelper();
DeleteUserAreaRef(User);
foreach (var item in data)
{
RentInfoHelper rHelper = new RentInfoHelper();
DataTable dt = rHelper.GetDataTable("select * from Rent_PoliceStation where parentId=" + item["PSID"].ToString());
if (dt.Rows.Count <= 0)
{
string Region = item["PSID"].ToString();
helper.InsertRelationShip(User, Region);
}
}
return(AjaxResult.Success());
}
protected void InitialControls()
{
RentAttributeHelper rentAttributeHelper = new RentAttributeHelper();
DataBindProvince();
DataBindNationName();
if (EditType == "D")
{
RentAttribute rentAttribute = new RentAttribute(int.Parse(RentNo));
lblRentNo.Text = rentAttribute.RentNo;
RentInfo rentInfo = new RentInfo(rentAttribute.RentNo);
txtContactName.Text = rentAttribute.RRAContactName;
txtIDCard.Text = rentAttribute.RRAIDCard;
txtContactTel.Text = rentAttribute.RRAContactTel;
txtRentPrice.Text = rentAttribute.RRentPrice.ToString();
ddlContactProvince.SelectedIndex = ddlContactProvince.Items.IndexOf(ddlContactProvince.Items.FindByText(rentAttribute.RRAContactProvince));
ddlNationName.SelectedIndex = ddlNationName.Items.IndexOf(ddlNationName.Items.FindByText(rentAttribute.RRANationName));
txtStartDate.Text = rentAttribute.RRAStartDate.ToString("yyyy-MM-dd");
txtEndDate.Text = rentAttribute.RRAEndDate.ToString("yyyy-MM-dd");
txtDescription.Text = rentAttribute.RRADescription;
lblCreatedBy.Text = rentAttribute.RRACreatedBy;
RentInfoHelper helper = new RentInfoHelper();
DataTable dtUser = helper.GetDataTable("select * from cf_user where loginName='" + lblCreatedBy.Text + "'");
foreach (DataRow row in dtUser.Rows)
{
lblCreatedByName.Text = row["RealName"].ToString();
}
lblCreatedDate.Text = rentAttribute.RRACreatedDate.ToString("yyyy-MM-dd hh:mm:ss");
btnStart.Visible = false;
btnEnd.Visible = false;
btnSave.Visible = false;
}
else
{
//获取租房者信息
RentAttribute rentAttribute = new RentAttribute(int.Parse(RentNo));
lblRentNo.Text = rentAttribute.RentNo;
RentInfo rentInfo = new RentInfo(rentAttribute.RentNo);
txtContactName.Text = rentAttribute.RRAContactName; //承租人姓名
txtIDCard.Text = rentAttribute.RRAIDCard; //身份证号ID
txtContactTel.Text = rentAttribute.RRAContactTel; //承租人联系电话
txtRentPrice.Text = rentAttribute.RRentPrice.ToString(); //租赁价格
ddlContactProvince.SelectedIndex = ddlContactProvince.Items.IndexOf(ddlContactProvince.Items.FindByText(rentAttribute.RRAContactProvince)); //获取所属省份
ddlNationName.SelectedIndex = ddlNationName.Items.IndexOf(ddlNationName.Items.FindByText(rentAttribute.RRANationName)); //获取所属国籍
txtStartDate.Text = rentAttribute.RRAStartDate.ToString("yyyy-MM-dd"); //租赁起始时间
txtEndDate.Text = rentAttribute.RRAEndDate.ToString("yyyy-MM-dd"); //租赁结束时间
txtDescription.Text = rentAttribute.RRADescription; //备注
lblCreatedBy.Text = rentAttribute.RRACreatedBy;
RentInfoHelper helper = new RentInfoHelper();
DataTable dtUser = helper.GetDataTable("select * from cf_user where loginName='" + lblCreatedBy.Text + "'");
foreach (DataRow row in dtUser.Rows)
{
lblCreatedByName.Text = row["RealName"].ToString(); //创建人
}
lblCreatedDate.Text = rentAttribute.RRACreatedDate.ToString("yyyy-MM-dd hh:mm:ss"); //创建时间
if (EditType == "A")
{
lblRentStatus.Text = "此房源已出租";
btnSave.Visible = false;
}
}
}
protected void InitialControls()
{
DataBindDistrict();
DataBindPoliceStationParent();
DataBindRoomType();
if (string.IsNullOrEmpty(RentNo) && EditType == "E")
{
lblCreatedBy.Text = SysContext.CurrentUserName;
lblCreatedByName.Text = SysContext.CurrentRealName;
lblCreatedDate.Text = System.DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss");
CFUserInfo info = new CFUserInfo(SysContext.CurrentUserName);
txtOwner.Text = info.RealName;
txtIDCard.Text = info.IDCard;
txtOwnerTel.Text = info.Phone;
}
else
{
ddlDistrict.Enabled = false;
RentInfo rentInfo = new RentInfo(RentNo);
ViewState["RentNo"] = RentNo;
lblRentNo.Text = RentNo;
ddlDistrict.SelectedIndex = ddlDistrict.Items.IndexOf(ddlDistrict.Items.FindByValue(rentInfo.RDName));
DataBindStreet();
ddlStreet.SelectedIndex = ddlStreet.Items.IndexOf(ddlStreet.Items.FindByValue(rentInfo.RSName));
DataBindRoad(string.Empty);
ddlRoad.SelectedIndex = ddlRoad.Items.IndexOf(ddlRoad.Items.FindByValue(rentInfo.RRName));
ddlPoliceStationParent.SelectedIndex = ddlPoliceStationParent.Items.IndexOf(ddlPoliceStationParent.Items.FindByValue(rentInfo.RPSParentName));
DataBindPoliceStation();
if (rentInfo.RPSID != null)
{
ddlPoliceStation.SelectedIndex = ddlPoliceStation.Items.IndexOf(ddlPoliceStation.Items.FindByValue(rentInfo.RPSID.ToString()));
}
else
{
ddlPoliceStation.SelectedIndex = -1;
}
ddlRoomType.SelectedIndex = ddlRoomType.Items.IndexOf(ddlRoomType.Items.FindByValue(rentInfo.RRoomType));
ddlDirection.SelectedIndex = ddlDirection.Items.IndexOf(ddlDirection.Items.FindByValue(rentInfo.RDirection));
ddlBuildingType.SelectedIndex = ddlBuildingType.Items.IndexOf(ddlBuildingType.Items.FindByValue(rentInfo.RBuildingType));
ddlStructure.SelectedIndex = ddlStructure.Items.IndexOf(ddlStructure.Items.FindByValue(rentInfo.RStructure));
ddlProperty.SelectedIndex = ddlProperty.Items.IndexOf(ddlProperty.Items.FindByValue(rentInfo.RProperty));
ddlRentType.SelectedIndex = ddlRentType.Items.IndexOf(ddlRentType.Items.FindByValue(rentInfo.RentType));
ddlOwnType.SelectedIndex = ddlOwnType.Items.IndexOf(ddlOwnType.Items.FindByValue(rentInfo.OwnType));
txtAddress.Text = rentInfo.RAddress;
txtDoor.Text = rentInfo.RDoor;
if (rentInfo.RTotalDoor == 0)
{
txtTotalDoor.Text = string.Empty;
}
else
{
txtTotalDoor.Text = rentInfo.RTotalDoor.ToString();
}
txtFloor.Text = rentInfo.RFloor.ToString();
if (rentInfo.RTotalFloor == 0)
{
txtTotalFloor.Text = string.Empty;
}
else
{
txtTotalFloor.Text = rentInfo.RTotalFloor.ToString();
}
txtHouseAge.Text = rentInfo.RHouseAge.ToString();
txtBuildRentArea.Text = rentInfo.RRentArea.ToString();
txtOwner.Text = rentInfo.ROwner;
txtIDCard.Text = rentInfo.RIDCard;
txtOwnerTel.Text = rentInfo.ROwnerTel;
txtFee.Text = rentInfo.RLocationDescription;
lblCreatedBy.Text = rentInfo.RCreatedBy;
RentInfoHelper helper = new RentInfoHelper();
DataTable dtUser = helper.GetDataTable("select * from cf_user where loginName='" + lblCreatedBy.Text + "'");
foreach (DataRow row in dtUser.Rows)
{
lblCreatedByName.Text = row["RealName"].ToString();
}
lblCreatedDate.Text = rentInfo.RCreatedDate.ToString("yyyy-MM-dd hh:mm:ss");
txtLangitude.Text = rentInfo.Longitude;
txtLatitude.Text = rentInfo.Latitude;
if (EditType == "E")
{
btnSave.Visible = true;
}
else
{
btnSave.Visible = false;
}
}
}
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));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string psName = Request["psname"];
startDate = Request["sDate"];
endDate = Request["eDate"];
statisticType = Request["category"];
RentInfoHelper helper = new RentInfoHelper();
chartData = "[";
switch (statisticType)
{
case "AllPoliceStation":
case "AllPoliceHouse":
if (policeStationName != "全部")
{
policeStationName = psName;
}
mtitle = "<b>房源全量统计</b>";
stitle = "所属警局：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
yTitle = "入住数量";
serieTitle = "入住量";
DataTable dt = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName, LigerRM.Common.SysContext.CurrentUserID.ToString());
decimal totalCount = 0;
foreach (DataRow row in dt.Rows)
{
totalCount += decimal.Parse(row["RentRecordCount"].ToString());
}
Random ran = new Random(1);
int rowIndex = ran.Next(0, dt.Rows.Count - 1);
int index = 0;
foreach (DataRow row in dt.Rows)
{
//{
// name: 'Chrome',
// y: 12.8,
// sliced: true,
// selected: true
//},
if (rowIndex == index)
{
chartData += "{name:'" + row["PSName"].ToString() + "',y:" + ((decimal.Parse(row["RentRecordCount"].ToString()) / totalCount) * 100).ToString("f2") + ",sliced: true,selected: true},";
}
else
{
chartData += "['" + row["PSName"].ToString() + "'," + ((decimal.Parse(row["RentRecordCount"].ToString()) / totalCount) * 100).ToString("f2") + "],";
}
index++;
}
break;
case "AllDistrict":
if (policeStationName != "全部")
{
DataTable dt1 = helper.GetDataTable("select * from Rent_PoliceStation where PSName='" + psName + "'");
if (dt1.Rows.Count > 0)
{
policeStationName = dt1.Rows[0]["co_corp_name"].ToString();
}
}
mtitle = "<b>所属区域全量统计</b>";
stitle = "所属区域：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
yTitle = "入住数量";
serieTitle = "入住量";
DataTable dt2 = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName, LigerRM.Common.SysContext.CurrentUserID.ToString());
foreach (DataRow row in dt2.Rows)
{
chartData += "['" + row["rd_reg_dept_name"].ToString() + "'," + row["PackageCount"].ToString() + "],";
}
break;
//case "AllPoliceHouse":
// if (policeStationName != "全部")
// {
// policeStationName = psName;
// }
// mtitle = "<b>所属警局全量统计</b>";
// stitle = "所属警局：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
// yTitle = "入住数量";
// serieTitle = "入住量";
// DataTable dt3 = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName);
// foreach (DataRow row in dt3.Rows)
// {
// chartData += "['" + row["PSName"].ToString() + "'," + row["RentRecordCount"].ToString() + "],";
// }
// break;
}
if (!string.IsNullOrEmpty(chartData))
{
chartData = chartData.Substring(0, chartData.Length - 1);
}
chartData = chartData + "]";
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string psName = Request["psname"];
startDate = Request["sDate"];
endDate = Request["eDate"];
statisticType = Request["category"];
RentInfoHelper helper = new RentInfoHelper();
chartData = "[";
switch (statisticType)
{
case "AllPoliceStation":
if (policeStationName != "全部")
{
policeStationName = psName;
}
mtitle = "<b>房源全量统计</b>";
stitle = "所属警局：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
yTitle = "入住数量";
serieTitle = "入住量";
DataTable dt = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName, LigerRM.Common.SysContext.CurrentUserID.ToString());
foreach (DataRow row in dt.Rows)
{
chartData += "['" + row["PSName"].ToString() + "'," + row["RentRecordCount"].ToString() + "],";
}
break;
case "AllDistrict":
if (policeStationName != "全部")
{
DataTable dt1 = helper.GetDataTable("select * from Rent_PoliceStation where PSName='" + psName + "'");
if (dt1.Rows.Count > 0)
{
policeStationName = dt1.Rows[0]["co_corp_name"].ToString();
}
}
mtitle = "<b>所属区域全量统计</b>";
stitle = "所属区域：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
yTitle = "入住数量";
serieTitle = "入住量";
DataTable dt2 = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName, LigerRM.Common.SysContext.CurrentUserID.ToString());
foreach (DataRow row in dt2.Rows)
{
chartData += "['" + row["rd_reg_dept_name"].ToString() + "'," + row["PackageCount"].ToString() + "],";
}
break;
case "AllPoliceHouse":
if (policeStationName != "全部")
{
policeStationName = psName;
}
mtitle = "<b>所属警局全量统计</b>";
stitle = "所属警局：" + policeStationName + " <br />统计日期：" + startDate + " 至 " + endDate;
yTitle = "入住数量";
serieTitle = "入住量";
DataTable dt3 = helper.GetStatisticData(statisticType, startDate, endDate, psName == "全部" ? "" : psName, LigerRM.Common.SysContext.CurrentUserID.ToString());
foreach (DataRow row in dt3.Rows)
{
chartData += "['" + row["PSName"].ToString() + "'," + row["RentRecordCount"].ToString() + "],";
}
break;
}
if (!string.IsNullOrEmpty(chartData))
{
chartData = chartData.Substring(0, chartData.Length - 1);
}
chartData = chartData + "]";
}
}
