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 + "]"; } }