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));
}
