public IList <RepDayOff> GetAllDayOffList()
{
var listRepDayOff = new List <RepDayOff>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id, UserId, DayOff, CreatedUserId, CreatedDate,TargetId FROM dbo.RepDayOff";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var repDayfff = new RepDayOff()
{
Id = Convert.ToInt32(reader["Id"]),
CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
DayOff = Convert.ToDateTime(reader["DayOff"]),
TargetId = Convert.ToInt32(reader["TargetId"]),
UserId = Convert.ToInt32(reader["UserId"]),
};
listRepDayOff.Add(repDayfff);
}
}
conn.Close();
return(listRepDayOff);
}
public IList <RepDayOff> GetRepDayOffByRepIdANdTarId(int repId, int tarId)
{
var listRepDayOff = new List <RepDayOff>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id, UserId, DayOff, CreatedUserId, CreatedDate,TargetId,Approved FROM dbo.RepDayOff WHERE UserId=@userId and TargetId=@tarId";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.AddWithValue("@userId", SqlDbType.Int).Value = repId;
cmd.Parameters.AddWithValue("@tarId", SqlDbType.Int).Value = tarId;
cmd.CommandText = strSqlContactStmt;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var repDayfff = new RepDayOff()
{
Id = Convert.ToInt32(reader["Id"]),
CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
DayOff = Convert.ToDateTime(reader["DayOff"]),
TargetId = Convert.ToInt32(reader["TargetId"]),
UserId = Convert.ToInt32(reader["UserId"]),
};
if (reader["Approved"] != DBNull.Value)
{
repDayfff.IsApproved = Convert.ToBoolean(reader["Approved"].ToString());
}
listRepDayOff.Add(repDayfff);
}
}
conn.Close();
return(listRepDayOff);
}
public void InsertRepDayOff(RepDayOff obj)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"INSERT INTO RepDayOff(UserId, DayOff, CreatedUserId, CreatedDate, TargetId,Approved)
VALUES(@userId, @dayOff, @createduserId,CURRENT_TIMESTAMP ,@targetId,@approved)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@targetId", SqlDbType.NVarChar).Value = obj.TargetId;
cmd.Parameters.Add("@approved", SqlDbType.Bit).Value = obj.IsApproved;
cmd.Parameters.Add("@userId", SqlDbType.NVarChar).Value = obj.UserId;
cmd.Parameters.Add("@dayOff", SqlDbType.DateTime).Value = obj.DayOff;
cmd.Parameters.Add("@createduserId", SqlDbType.NVarChar).Value = obj.CreateduserId;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}