public JsonResult Login(UserLoginForm loginForm) { var sql = "select * from user where email=@Email and pwd=@Pwd"; var mySqlConnection = DataOperate.GetCon(); var cmd = new MySqlCommand(sql, mySqlConnection); cmd.Parameters.Add(new MySqlParameter("Email", MySqlDbType.VarChar, 16)); cmd.Parameters["Email"].Value = loginForm.Email; cmd.Parameters.Add(new MySqlParameter("Pwd", MySqlDbType.VarChar, 32)); cmd.Parameters["Pwd"].Value = loginForm.Pwd; var user = DataOperate.FindOne(cmd); if (user == null) { mySqlConnection.Close(); return(Fail("用户或密码输入有误,登录失败..", 1006)); } HttpContext.Session.SetString("userEmail", loginForm.Email); HttpContext.Session.SetString("userId", user["id"].ToString()); mySqlConnection.Close(); return(Success(user)); }
public JsonResult Register(AdminRegisterForm registerForm) { string MailCode = registerForm.Code; string LicenseKey = registerForm.LicenseKey; string str = HttpContext.Session.GetInt32("code").ToString(); if (str == MailCode && LicenseKey == "fox072065yat") { string sql = "insert into admin(email,pwd) values(@Email,@Pwd)"; MySqlConnection con = DataOperate.GetCon(); MySqlCommand com = new MySqlCommand(sql, con); com = new MySqlCommand(sql, con); com.Parameters.Add(new MySqlParameter("@Email", MySqlDbType.VarChar, 16)); com.Parameters["@Email"].Value = registerForm.Email; com.Parameters.Add(new MySqlParameter("@Pwd", MySqlDbType.VarChar, 32)); com.Parameters["@Pwd"].Value = registerForm.Pwd; if (com.ExecuteNonQuery() > 0) { con.Close(); return(Success("true")); } else { con.Close(); return(Fail("注册失败,请重试..", 1004)); } } else { return(Fail("验证码输入有误,请重试..", 1005)); } }
public JsonResult CreateHouseCommission(CreateHouseCommission createHouseCommission) { string sql = "insert into house(atCity,community,address,area,floor,layout,orientation,buildAt,rentType,toilet,balcony,houseNum,roomNum,userId) " + "values(@AtCity,@Community,@Address,@Area,@Floor,@Layout,@Orientation,@BuildAt,@RentType,@Toilet,@Balcony,@HouseNum,@RoomNum,@UserId)"; MySqlConnection con = DataOperate.GetCon(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add(new MySqlParameter("@AtCity", MySqlDbType.VarChar, 10)).Value = createHouseCommission.AtCity; cmd.Parameters.Add(new MySqlParameter("@Community", MySqlDbType.VarChar, 10)).Value = createHouseCommission.Community; cmd.Parameters.Add(new MySqlParameter("@Address", MySqlDbType.VarChar, 50)).Value = createHouseCommission.Address; cmd.Parameters.Add(new MySqlParameter("@Area", MySqlDbType.Float)).Value = createHouseCommission.Area; cmd.Parameters.Add(new MySqlParameter("@Floor", MySqlDbType.VarChar, 10)).Value = createHouseCommission.Floor; cmd.Parameters.Add(new MySqlParameter("@Layout", MySqlDbType.VarChar, 10)).Value = createHouseCommission.Layout; cmd.Parameters.Add(new MySqlParameter("@Orientation", MySqlDbType.VarChar, 10)).Value = createHouseCommission.Orientation; cmd.Parameters.Add(new MySqlParameter("@BuildAt", MySqlDbType.Date)).Value = createHouseCommission.BuildAt; cmd.Parameters.Add(new MySqlParameter("@RentType", MySqlDbType.Enum)).Value = createHouseCommission.RentType; cmd.Parameters.Add(new MySqlParameter("@Toilet", MySqlDbType.Int32, 11)).Value = createHouseCommission.Toilet; cmd.Parameters.Add(new MySqlParameter("@Balcony", MySqlDbType.Int32, 11)).Value = createHouseCommission.Balcony; cmd.Parameters.Add(new MySqlParameter("@HouseNum", MySqlDbType.VarChar, 10)).Value = createHouseCommission.HouseNum; cmd.Parameters.Add(new MySqlParameter("@RoomNum", MySqlDbType.VarChar, 10)).Value = createHouseCommission.RoomNum; cmd.Parameters.Add(new MySqlParameter("@UserId", MySqlDbType.Int32, 11)).Value = GetUserId(); try { cmd.ExecuteNonQuery(); con.Close(); } catch (System.Exception e) { con.Close(); return(Success(false)); } return(Success(true)); }
public JsonResult Send(AdminSendForm sendForm) { Regex r1 = new Regex("^\\w+([-+.]\\w+)*@\\w+([-.]\\w+)*\\.\\w+([-.]\\w+)*$"); Regex r2 = new Regex("^\\w{6,18}$"); if (!r1.IsMatch(sendForm.Email)) { return(Fail("邮箱格式不正确", 1001)); } if (!r2.IsMatch(sendForm.Pwd)) { return(Fail("密码只能包含字母、数字和下划线,长度在6~18之间", 1002)); } string sql = "select count(*) from admin where email=@Email"; MySqlConnection con = DataOperate.GetCon(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add(new MySqlParameter("@Email", MySqlDbType.VarChar, 16)); cmd.Parameters["@Email"].Value = sendForm.Email; if (Convert.ToInt32(cmd.ExecuteScalar()) == 0) { String to = sendForm.Email; Random random = new Random(Guid.NewGuid().GetHashCode()); int code = random.Next(1000, 10000); string content = "您正在使用邮箱安全验证服务,您本次操作的验证码是:" + code; string strSmtpServer = SmtpSettings.Server; string strFrom = SmtpSettings.From; string strFromPass = SmtpSettings.Password; SendEmail(strSmtpServer, strFrom, strFromPass, to, "激活邮箱", content); HttpContext.Session.SetInt32("code", code); con.Close(); return(Success("true")); } return(Fail("该用户已存在,请选择新账号注册..", 1003)); }
public JsonResult Login(AdminLoginForm loginForm) { string sql = "select * from admin where email=@Email and pwd=@Pwd"; var mySqlConnection = DataOperate.GetCon(); var cmd = new MySqlCommand(sql, mySqlConnection); cmd.Parameters.Add(new MySqlParameter("Email", MySqlDbType.VarChar, 16)); cmd.Parameters["Email"].Value = loginForm.Email; cmd.Parameters.Add(new MySqlParameter("Pwd", MySqlDbType.VarChar, 32)); cmd.Parameters["Pwd"].Value = loginForm.Pwd; var admin = DataOperate.FindOne(cmd); if (admin == null) { return(Fail("用户或密码输入有误,登录失败..", 1006)); } HttpContext.Session.SetString("adminEmail", loginForm.Email); HttpContext.Session.SetString("adminId", admin["id"].ToString()); mySqlConnection.Close(); return(LoginSuccess("true")); }
public JsonResult CreateHouse(CreateHouse createHouse) { var getHouseSql = "select * from contract where id=" + createHouse.ContractId; var contract = DataOperate.FindOne(getHouseSql); if (contract == null) { return(Success(false)); } var houseId = contract["houseId"].ToString(); string sql = "update house set community=@Community, atCity=@AtCity, address=@Address, floor=@Floor, houseNum=@HouseNum, roomNum=@RoomNum, layout=@Layout, orientation=@Orientation, area=@Area, buildAt=@BuildAt, toilet=@Toilet, balcony=@Balcony, rentType=@RentType, intro=@Intro where id=@Id"; MySqlConnection con = DataOperate.GetCon(); var mySqlTransaction = con.BeginTransaction(); MySqlCommand cmd = new MySqlCommand(sql, con); cmd.Parameters.Add(new MySqlParameter("@AtCity", MySqlDbType.VarChar, 10)).Value = createHouse.AtCity; cmd.Parameters.Add(new MySqlParameter("@Community", MySqlDbType.VarChar, 20)).Value = createHouse.Community; cmd.Parameters.Add(new MySqlParameter("@Address", MySqlDbType.VarChar, 50)).Value = createHouse.Address; cmd.Parameters.Add(new MySqlParameter("@Floor", MySqlDbType.VarChar, 10)).Value = createHouse.Floor; cmd.Parameters.Add(new MySqlParameter("@HouseNum", MySqlDbType.VarChar, 10)).Value = createHouse.HouseNum; cmd.Parameters.Add(new MySqlParameter("@RoomNum", MySqlDbType.VarChar, 10)).Value = createHouse.RoomNum; cmd.Parameters.Add(new MySqlParameter("@Layout", MySqlDbType.VarChar, 10)).Value = createHouse.Layout; cmd.Parameters.Add(new MySqlParameter("@Orientation", MySqlDbType.VarChar, 10)).Value = createHouse.Orientation; cmd.Parameters.Add(new MySqlParameter("@Area", MySqlDbType.Float)).Value = createHouse.Area; cmd.Parameters.Add(new MySqlParameter("@BuildAt", MySqlDbType.Date)).Value = createHouse.BuildAt; cmd.Parameters.Add(new MySqlParameter("@Toilet", MySqlDbType.Int32, 11)).Value = createHouse.Toilet; cmd.Parameters.Add(new MySqlParameter("@Balcony", MySqlDbType.Int32, 11)).Value = createHouse.Balcony; cmd.Parameters.Add(new MySqlParameter("@RentType", MySqlDbType.Enum)).Value = createHouse.RentType; cmd.Parameters.Add(new MySqlParameter("@Intro", MySqlDbType.Text)).Value = createHouse.Intro; cmd.Parameters.Add(new MySqlParameter("@Id", MySqlDbType.Int32)).Value = houseId; cmd.Transaction = mySqlTransaction; try { cmd.ExecuteNonQuery(); var cmds = new List <MySqlCommand>(); foreach (var resImg in createHouse.ResImgs) { var resSql = "INSERT INTO resOfHouse (resType, resPath, houseId) VALUES (@ResType, @ResPath, @HouseId);"; MySqlCommand resCmd = new MySqlCommand(resSql, con); resCmd.Parameters.Add(new MySqlParameter("@ResType", MySqlDbType.Enum)).Value = "img"; resCmd.Parameters.Add(new MySqlParameter("@ResPath", MySqlDbType.VarChar, 100)).Value = resImg; resCmd.Parameters.Add(new MySqlParameter("@HouseId", MySqlDbType.Int32, 11)).Value = houseId; cmds.Add(resCmd); } var coverSql = "update house set cover='" + createHouse.ResImgs[0] + "' where id=" + houseId; MySqlCommand coverSqlCmd = new MySqlCommand(coverSql, con); cmds.Add(coverSqlCmd); if (createHouse.ResVideo != "") { var resSql = "INSERT INTO resOfHouse (resType, resPath, houseId) VALUES (@ResType, @ResPath, @HouseId);"; MySqlCommand resCmd = new MySqlCommand(resSql, con); resCmd.Parameters.Add(new MySqlParameter("@ResType", MySqlDbType.Enum)).Value = "video"; resCmd.Parameters.Add(new MySqlParameter("@ResPath", MySqlDbType.VarChar, 100)).Value = createHouse.ResVideo; resCmd.Parameters.Add(new MySqlParameter("@HouseId", MySqlDbType.Int32, 11)).Value = houseId; cmds.Add(resCmd); } if (createHouse.Res3D != "") { var resSql = "INSERT INTO resOfHouse (resType, resPath, houseId) VALUES (@ResType, @ResPath, @HouseId);"; MySqlCommand resCmd = new MySqlCommand(resSql, con); resCmd.Parameters.Add(new MySqlParameter("@ResType", MySqlDbType.Enum)).Value = "3d"; resCmd.Parameters.Add(new MySqlParameter("@ResPath", MySqlDbType.VarChar, 100)).Value = createHouse.Res3D; resCmd.Parameters.Add(new MySqlParameter("@HouseId", MySqlDbType.Int32, 11)).Value = houseId; cmds.Add(resCmd); } foreach (var mySqlCommand in cmds) { mySqlCommand.Transaction = mySqlTransaction; mySqlCommand.ExecuteNonQuery(); } var updateHouseSql = "UPDATE house SET rentStatus = 'empty'" + " WHERE id = " + houseId + " and rentStatus = 'activated'"; MySqlCommand updateHouseCmd = new MySqlCommand(updateHouseSql, con); updateHouseCmd.Transaction = mySqlTransaction; updateHouseCmd.ExecuteNonQuery(); mySqlTransaction.Commit(); con.Close(); } catch (System.Exception e) { mySqlTransaction.Rollback(); con.Close(); return(Success(false)); } return(Success(true)); }
public JsonResult GetTenantContractList() { var sql = "select * from contract where type='withTenant' and contractStatus != 'invalid'"; DataSet allContract = DataOperate.FindAll(sql); // 检查合同状态并修改 MySqlConnection con = DataOperate.GetCon(); //创建数据库连接 MySqlTransaction sTransaction = null; //创建SqlTransaction对象 sTransaction = con.BeginTransaction(); //设置开始事务 foreach (DataRow row in allContract.Tables[0].Rows) { var expired = (DateTime.Parse(row["endAt"].ToString()) < DateTime.Parse(DateTime.Now.ToString())); var contractStatus = expired ? "fallDue" : "undue"; var updateContractStatus = "update contract set contractStatus='" + contractStatus + "' where id=" + row["id"].ToString(); var contractCheckSql = "select * from contract where houseId = " + row["houseId"].ToString() + " and contractStatus = 'undue' and type='withTenant'"; var updateHouse = "update house set rentStatus='empty' where id=" + row["houseId"].ToString(); try { using (MySqlCommand com = con.CreateCommand()) { com.Transaction = sTransaction; //设置需要执行事务 com.CommandText = updateContractStatus; if (com.ExecuteNonQuery() == -1) //判断是否执行成功 { sTransaction.Rollback(); //设置事务回滚 con.Close(); return(Fail(false, 10000)); //返回布尔值False } com.CommandText = contractCheckSql; if (Convert.ToInt32(com.ExecuteScalar()) <= 0) { com.CommandText = updateHouse; if (com.ExecuteNonQuery() == -1) //判断是否执行成功 { sTransaction.Rollback(); //设置事务回滚 con.Close(); return(Fail(false, 10000)); //返回布尔值False } } } } catch (System.Exception ex) { sTransaction.Rollback(); //设置事务回滚 con.Close(); return(Fail(false, 10000)); //返回布尔值False } } sTransaction.Commit(); //提交事务 con.Close(); var sqlParent = "select * from contract where type = 'withTenant' and parentNum is null and contractStatus != 'invalid'"; var sqlChildren = "select * from contract where type = 'withTenant' and parentNum is not null and contractStatus != 'invalid'"; var parentList = new List <OwnerContract>(); DataSet parentRows = DataOperate.FindAll(sqlParent); DataSet childrenRows = DataOperate.FindAll(sqlChildren); foreach (DataRow row in parentRows.Tables[0].Rows) { var ownerContract = new OwnerContract(); foreach (var dataColumn in row.Table.Columns) { ownerContract.SetAttribute(dataColumn.ToString(), row[dataColumn.ToString()].ToString()); } ownerContract.Children = GetChildrenByParentId(childrenRows, ownerContract.Id.ToString()); parentList.Add(ownerContract); } return(Success(parentList)); }
public JsonResult CreateHouseContract(CreateHouseContract createHouseContract) { string parentNum = null; var contractOneSql = "select * from contract where type = 'withOwner' and houseId=" + createHouseContract.HouseId; var contractOne = DataOperate.FindOne(contractOneSql); if (contractOne != null) { parentNum = contractOne["id"].ToString(); } var addSql = "insert into contract(adminId,type,startAt,endAt,contractPic,houseId,userId,parentNum,rentPrice,payForm) values(@AdminId,@Type,@StartAt,@EndAt,@ContractPic,@HouseId,@UserId,@ParentNum,@RentPrice,@PayForm)"; var upSql = "update house set rentPrice='" + Convert.ToInt32(createHouseContract.RentPrice) * 1.2 + "' , payForm='" + createHouseContract.PayForm + "', rentStatus='activated' where id=" + createHouseContract.HouseId; MySqlConnection con = DataOperate.GetCon(); var ownerContractCmd = new MySqlCommand(addSql, con); ownerContractCmd.Parameters.Add(new MySqlParameter("@AdminId", MySqlDbType.Int32, 11)).Value = GetAdminId(); ownerContractCmd.Parameters.Add(new MySqlParameter("@Type", MySqlDbType.Enum)).Value = "withOwner"; ownerContractCmd.Parameters.Add(new MySqlParameter("@StartAt", MySqlDbType.Date)).Value = createHouseContract.StartAt; ownerContractCmd.Parameters.Add(new MySqlParameter("@EndAt", MySqlDbType.Date)).Value = createHouseContract.EndAt; ownerContractCmd.Parameters.Add(new MySqlParameter("@RentPrice", MySqlDbType.Decimal, 11)).Value = createHouseContract.RentPrice; ownerContractCmd.Parameters.Add(new MySqlParameter("@PayForm", MySqlDbType.Enum)).Value = createHouseContract.PayForm; ownerContractCmd.Parameters.Add(new MySqlParameter("@HouseId", MySqlDbType.Int32, 11)).Value = createHouseContract.HouseId; ownerContractCmd.Parameters.Add(new MySqlParameter("@ContractPic", MySqlDbType.VarChar, 100)).Value = createHouseContract.ContractPic; ownerContractCmd.Parameters.Add(new MySqlParameter("@UserId", MySqlDbType.Int32, 11)).Value = createHouseContract.UserId; ownerContractCmd.Parameters.Add(new MySqlParameter("@ParentNum", MySqlDbType.Int32, 11)).Value = parentNum; var sTransaction = con.BeginTransaction(); try { ownerContractCmd.Transaction = sTransaction; ownerContractCmd.ExecuteNonQuery(); var contractId = ownerContractCmd.LastInsertedId; // 生成交易账单 var transactionOwnerCmds = new List <MySqlCommand>(); DateTimeFormatInfo dtFormat = new DateTimeFormatInfo { ShortDatePattern = "yyyy-MM-dd" }; DateTime startAt = Convert.ToDateTime(createHouseContract.StartAt, dtFormat); DateTime endAt = Convert.ToDateTime(createHouseContract.EndAt, dtFormat); var first = true; while (startAt < endAt) { var transactionSql = "INSERT INTO transactions (contractId, userId, account, payForm, tranDate, startDate, endDate, tranStatus, tranType, adminId) VALUES (@ContractId, @UserId, @Account, @PayForm, @TranDate, @StartDate, @EndDate, @TranStatus, @TranType, @AdminId);"; var transactionCmd = new MySqlCommand(transactionSql, con) { Transaction = sTransaction }; transactionCmd.Parameters.Add(new MySqlParameter("@ContractId", MySqlDbType.Int32, 11)).Value = contractId; transactionCmd.Parameters.Add(new MySqlParameter("@UserId", MySqlDbType.Int32, 11)).Value = createHouseContract.UserId; transactionCmd.Parameters.Add(new MySqlParameter("@PayForm", MySqlDbType.Enum)).Value = createHouseContract.PayForm; transactionCmd.Parameters.Add(new MySqlParameter("@StartDate", MySqlDbType.Date)).Value = startAt.ToString("yyyy-MM-dd"); DateTime endDate; if (createHouseContract.PayForm == "byYear") { endDate = startAt.AddYears(1); if (endDate > endAt) { // 当年总天数 var days = (float)(endDate - startAt).Days; // ReSharper disable once PossibleLossOfFraction var realDays = (float)(endAt - startAt).Days; var rentPrice = Math.Floor(realDays / days * createHouseContract.RentPrice * 12); transactionCmd.Parameters.Add(new MySqlParameter("@Account", MySqlDbType.Decimal)).Value = rentPrice; endDate = endAt; } else { transactionCmd.Parameters.Add(new MySqlParameter("@Account", MySqlDbType.Decimal)).Value = createHouseContract.RentPrice * 12; } } else { endDate = startAt.AddMonths(1); if (endDate > endAt) { // 当月总天数 var days = (float)(endDate - startAt).Days; // ReSharper disable once PossibleLossOfFraction var realDays = (float)(endAt - startAt).Days; var rentPrice = Math.Floor(realDays / days * createHouseContract.RentPrice); transactionCmd.Parameters.Add(new MySqlParameter("@Account", MySqlDbType.Decimal)).Value = rentPrice; endDate = endAt; } else { transactionCmd.Parameters.Add(new MySqlParameter("@Account", MySqlDbType.Decimal)).Value = createHouseContract.RentPrice; } } transactionCmd.Parameters.Add(new MySqlParameter("@EndDate", MySqlDbType.Date)).Value = endDate.ToString("yyyy-MM-dd"); transactionCmd.Parameters.Add(new MySqlParameter("@TranType", MySqlDbType.Enum)).Value = "withOwner"; transactionCmd.Parameters.Add(new MySqlParameter("@AdminId", MySqlDbType.Int32, 100)).Value = GetAdminId(); if (first) { transactionCmd.Parameters.Add(new MySqlParameter("@TranDate", MySqlDbType.Date)).Value = DateTime.Now.ToString("yyyy-MM-dd"); transactionCmd.Parameters.Add(new MySqlParameter("@TranStatus", MySqlDbType.Enum)).Value = "paid"; first = false; } else { transactionCmd.Parameters.Add(new MySqlParameter("@TranDate", MySqlDbType.Date)).Value = null; transactionCmd.Parameters.Add(new MySqlParameter("@TranStatus", MySqlDbType.Enum)).Value = "unpaid"; } startAt = createHouseContract.PayForm == "byYear" ? startAt.AddYears(1) : startAt.AddMonths(1); transactionOwnerCmds.Add(transactionCmd); } foreach (var mySqlCommand in transactionOwnerCmds) { mySqlCommand.ExecuteNonQuery(); } var upApplySql = "update renewalContractApply set applyStatus = 'finished' where type='withOwner' and houseId=" + createHouseContract.HouseId + " and userId=" + createHouseContract.UserId; var upCmd1 = new MySqlCommand(upSql, con) { Transaction = sTransaction }; var upCmd2 = new MySqlCommand(upApplySql, con) { Transaction = sTransaction }; upCmd1.ExecuteNonQuery(); upCmd2.ExecuteNonQuery(); sTransaction.Commit(); } catch (System.Exception e) { sTransaction.Rollback(); return(Success(false)); } return(Success(true)); }