public static int Add(LeaveInfo leave)
{
SqlParameter[] parameters = new SqlParameter[6];
parameters[0] = new SqlParameter("@UserID", SqlDbType.VarChar, 6);
parameters[0].Value = leave.UserID;
parameters[1] = new SqlParameter("@StartDate", SqlDbType.VarChar, 20);
parameters[1].Value = leave.StartDate;
parameters[2] = new SqlParameter("@EndDate", SqlDbType.VarChar, 20);
parameters[2].Value = leave.EndDate;
parameters[3] = new SqlParameter("@Type", SqlDbType.Int);
parameters[3].Value = leave.Type;
parameters[4] = new SqlParameter("@Flag", SqlDbType.Int);
parameters[4].Value = leave.Approved ? 1 : 0;
parameters[5] = new SqlParameter("@Days", SqlDbType.Int);
parameters[5].Value = leave.Days;
StringBuilder sql = new StringBuilder();
sql.AppendLine("DECLARE @ID int");
sql.AppendLine("IF EXISTS(SELECT * FROM LeaveInfo WHERE userId = @UserID AND startDate <= @EndDate AND endDate >= @StartDate) BEGIN");
sql.AppendLine(" SET @ID = 0");
sql.AppendLine("END");
sql.AppendLine("ELSE BEGIN");
sql.AppendLine(" INSERT INTO LeaveInfo (userId, startDate, endDate, type, flag, days)");
sql.AppendLine(" VALUES (@UserID, @StartDate, @EndDate, @Type, @Flag, @Days)");
sql.AppendLine(" SET @ID = SCOPE_IDENTITY()");
sql.AppendLine("END");
sql.AppendLine("SELECT @ID");
int newID = (int)helper.ExecuteScalar(sql.ToString(), parameters);
return newID;
}
public static int Save(LeaveInfo leave)
{
int id;
if (leave.ID <= 0) {
id = LeaveDao.Add(leave);
}
else {
id = LeaveDao.Update(leave) ? leave.ID : 0;
}
return id;
}
private LeaveInfo BuildEntity()
{
LeaveInfo leave = new LeaveInfo();
leave.ID = GetFormInteger("id");
leave.UserID = Request.Form["userId"];
leave.StartDate = Request.Form["startDate"];
leave.EndDate = Request.Form["endDate"];
leave.Type = GetFormInteger("type");
return leave;
}
public static bool Update(LeaveInfo leave)
{
SqlParameter[] parameters = new SqlParameter[5];
parameters[0] = new SqlParameter("@StartDate", SqlDbType.VarChar, 20);
parameters[0].Value = leave.StartDate;
parameters[1] = new SqlParameter("@EndDate", SqlDbType.VarChar, 20);
parameters[1].Value = leave.EndDate;
parameters[2] = new SqlParameter("@Type", SqlDbType.Int);
parameters[2].Value = leave.Type;
parameters[3] = new SqlParameter("@Days", SqlDbType.Int);
parameters[3].Value = leave.Days;
parameters[4] = new SqlParameter("@ID", SqlDbType.Int);
parameters[4].Value = leave.ID;
StringBuilder sql = new StringBuilder();
sql.AppendLine("UPDATE LeaveInfo SET startDate = @StartDate, endDate = @EndDate, type = @Type, days = @Days");
sql.AppendLine("WHERE ID = @ID");
int rowCount = helper.ExecuteNonQuery(sql.ToString(), parameters.ToArray());
return rowCount > 0;
}