private static List <OwnerContract> GetChildrenByParentId(DataSet childrenRows, string parentNum)
{
var childrenList = new List <OwnerContract>();
foreach (DataRow row in childrenRows.Tables[0].Rows)
{
if (row["parentNum"].ToString() == parentNum)
{
var ownerContract = new OwnerContract();
foreach (var dataColumn in row.Table.Columns)
{
ownerContract.SetAttribute(dataColumn.ToString(), row[dataColumn.ToString()].ToString());
}
childrenList.Add(ownerContract);
}
}
return(childrenList);
}
public JsonResult GetOwnerContractList()
{
var sql = "select * from contract where type='withOwner' and contractStatus != 'invalid'";
DataSet allContract = DataOperate.FindAll(sql);
// 检查合同状态并修改
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 newSql = "update contract set contractStatus='" + contractStatus + "' where id=" +
row["id"].ToString();
DataOperate.Update(newSql);
}
var sqlParent = "select * from contract where type = 'withOwner' and parentNum is null and contractStatus != 'invalid'";
var sqlChildren = "select * from contract where type = 'withOwner' 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 async Task <IActionResult> CreateBookCopy(AddBookCopyRequest request)
{
//1- Check if this book is available
//if yes: just add a new book copy
//if not: create a new book then add a new book copy in the db
var searchResult = GetByID(request.Isbn);
if (searchResult == null)
{
//book is not available : must add a book first
var book_new = new Book(request.Isbn, request.Name, request.Author, request.Genre, request.ReleaseDate, request.Publisher);
_context.Books.Add(book_new);
_context.SaveChanges();
}
//state to be changed
var av = "New";
var email = HttpContext.Items["Email"] as string;
//2-search for users that have this email in the db
var user = await _context.Users.FirstAsync(x => x.Email == email);
//3- get the user id
var userId = user.UserId;
var ownerContract = new OwnerContract(userId ?? default(int), request.branchID, request.EndDate);
_context.OwnerContracts.Add(ownerContract);
_context.SaveChanges();
int id = ownerContract.ContractID ?? default(int);
var bookCopyNew = new BookCopy(currentState: av, isAvailable: true, ISBN: request.Isbn, shelfID: 0, OWNERcontractId: id);
_context.BookCopies.Add(bookCopyNew);
_context.SaveChanges();
return(Ok());
}
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));
}
