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