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