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 GetHouseByContractId(int contractId) { var sql = "select * from contract where id=" + contractId; var contract = DataOperate.FindOne(sql); var houseId = contract["houseId"].ToString(); var houseSql = "select * from house where id=" + houseId; return(Success(DataOperate.FindAll(houseSql))); }
public JsonResult PassTobeOwnerApply(PassTobeOwnerApply passTobeOwnerApply) { string sql = "select * from applyTobeowner where id = " + passTobeOwnerApply.Id + ""; var record = DataOperate.FindOne(sql); string updateSql = "UPDATE applyTobeowner SET applyStatus = 'pass', adminId = " + GetAdminId() + " WHERE id = " + passTobeOwnerApply.Id + " and applyStatus = 'todo'"; string updateUserSql = "UPDATE user SET type = 'owner' WHERE id = " + record["userId"].ToString() + ";"; return(Success(DataOperate.ExecTransaction(new[] { updateSql, updateUserSql }))); }
public JsonResult UpdatePwd(UpdatePwd updatePwd) { string sql1 = "select * from admin where id=" + GetAdminId(); var r = DataOperate.FindOne(sql1); if (r == null) { return(Fail(false, 2001)); } else { if (r["pwd"].ToString() == updatePwd.Pwd) { string sql2 = "update admin set pwd='" + updatePwd.NewPwd + "' where id=" + GetAdminId(); return(Success(DataOperate.Update(sql2))); } else { return(Fail(false, 2002)); } } }
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 GetUserInfo() { string sql = "select * from user where id = " + GetUserId(); return(Success(DataOperate.FindOne(sql))); }
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)); }