public void CreateToDo(int ids, string title, string desc, int assign, string stat, DateTime dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_ToDo(ProjectID,MemberID,ToDoTitle,ToDoDescription,AssignedTo,Deadline,Status,CreatedAt,UpdatedAt,CompletedBy) values(@Projid,@memid,@title,@descrip,@assignto,@deadline,@status,@createdat,@updatedat,@comby)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("projid", ids);
sqlConn.Command.Parameters.AddWithValue("@memid", 2);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@descrip", desc);
sqlConn.Command.Parameters.AddWithValue("@assignto", assign);
sqlConn.Command.Parameters.AddWithValue("@status", stat);
sqlConn.Command.Parameters.AddWithValue("@deadline", dt);
sqlConn.Command.Parameters.AddWithValue("@createdat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@updatedat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@comby", DateTime.UtcNow);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public bool CheckRole(int memid)
{
bool userExit = false;
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
int roleID = 0;
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select m.MemberID,m.RoleID,r.RoleID,r.Name from tbl_memberinfo as m inner join tbl_role as r on m.RoleID=r.RoleID where m.MemberID=@memberid";
sqlConn.Query = "select MemberID,RoleID from tbl_member where MemberID=@memid ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@memid", memid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
// userExit = true;
//ProjectLab.Areas.Admin.Models.member.Member mem = new ProjectLab.Areas.Admin.Models.member.Member();
roleID = Int32.Parse(sqlConn.Reader["RoleID"].ToString());
}
}
if (roleID == 1) //Admin
{
return(true);
}
else if (roleID == 2) //Project Manager
{
return(true);
}
else if (roleID == 3) //Member
{
return(true);
}
else if (roleID == 4) //EndUser
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(userExit);
}
public void Edit(int tid, string title, string Description, int assign, string status, DateTime dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_todo set ToDoTitle=@title,ToDoDescription=@Desc,AssignedTo=@assign,Deadline=@dead,Status=@status where ToDoID=@todoid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@todoid", tid);
sqlConn.Command.Parameters.AddWithValue("@dead", dt);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@Desc", Description);
sqlConn.Command.Parameters.AddWithValue("@assign", assign);
sqlConn.Command.Parameters.AddWithValue("@status", status);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Edit(int id, string title, string description, string evdte)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_Calender_Event set Title =@title, Description=@descrip, EventDate=@evedte where Calender_Event_ID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@evedte", evdte);
sqlConn.Command.Parameters.AddWithValue("@descrip", description);
sqlConn.Command.Parameters.AddWithValue("@id", id);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Createmilestones(int ids, string title, string desc, string status, DateTime dte)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_milestones(ProjectID,CreatedBy,ReviewedBy,Status,Deadline,MilestonesTitle,MilestonesDescription,UpdatedAt,CreatedAt) values(@Projid,@createdby,@reviewby,@status,@deadline,@title,@descrip,@updatedat,@createdat)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("projid", ids);
sqlConn.Command.Parameters.AddWithValue("@createdby", 2);
sqlConn.Command.Parameters.AddWithValue("@reviewby", 2);
sqlConn.Command.Parameters.AddWithValue("@status", status);
sqlConn.Command.Parameters.AddWithValue("@deadline", dte);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@descrip", desc);
sqlConn.Command.Parameters.AddWithValue("@updatedat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@createdat", DateTime.Now);
// sqlConn.Command.Parameters.AddWithValue("@memss",mem);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Edit(int mileid, string title, string Description, string status, DateTime dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_milestones set MilestonesTitle=@title,MilestonesDescription=@Desc, Deadline=@datetime, Status=@status where MilestonesID=@mileid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@mileid", mileid);
sqlConn.Command.Parameters.AddWithValue("@updatedat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@Desc", Description);
sqlConn.Command.Parameters.AddWithValue("@datetime", dt);
sqlConn.Command.Parameters.AddWithValue("@status", status);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void CreateToDo(int ids, string title, string desc, int assign,string stat,DateTime dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_ToDo(ProjectID,MemberID,ToDoTitle,ToDoDescription,AssignedTo,Deadline,Status,CreatedAt,UpdatedAt,CompletedBy) values(@Projid,@memid,@title,@descrip,@assignto,@deadline,@status,@createdat,@updatedat,@comby)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("projid", ids);
sqlConn.Command.Parameters.AddWithValue("@memid", 2);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@descrip", desc);
sqlConn.Command.Parameters.AddWithValue("@assignto", assign);
sqlConn.Command.Parameters.AddWithValue("@status", stat);
sqlConn.Command.Parameters.AddWithValue("@deadline", dt);
sqlConn.Command.Parameters.AddWithValue("@createdat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@updatedat", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@comby", DateTime.UtcNow);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
///////////////////////////////////////////////////////////////
public void createimage(string ti)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_memberImage(Title) output Inserted.ImageID values(@Title)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Title", ti);
sqlConn.Connection.Open();
var id = sqlConn.Command.ExecuteScalar();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_memberinfo(ImageID) values (@id)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Edit(int memID, string fname, string lname, string Email, string Username, string password)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_member set Email=@Email,Username=@Username where MemberID=@MemberID; update tbl_memberinfo set FirstName=@FirstName,LastName=@LastName where MemberID=@MemberID";
// sqlConn.Query = " update tbl_member as m join tbl_memberinfo as mi on set m.Email=@email,m.UserName=@username,mi.FirstName=@Fnam,mi.LastName=@lname from tbl_member as m.MemberID = mi.MemberID ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Username", Username);
sqlConn.Command.Parameters.AddWithValue("@Password", password);
sqlConn.Command.Parameters.AddWithValue("@Email", Email);
sqlConn.Command.Parameters.AddWithValue("@FirstName", fname);
sqlConn.Command.Parameters.AddWithValue("@LastName", lname);
sqlConn.Command.Parameters.AddWithValue("@MemberID", memID);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public DataSet GetEmployeeDetails()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
DataSet ds = null;
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select Email,Username from tbl_member";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
SqlDataAdapter adapter;
ds = new DataSet();
adapter = new SqlDataAdapter(sqlConn.Command);
///////////////////////////
adapter.Fill(ds);
// ds.Tables[0].Merge(dt);
/////////////////////////
// adapter.Fill(ds, "Users");
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
sqlConn.Connection.Dispose();
if (sqlConn.Connection.State != ConnectionState.Closed)
sqlConn.Connection.Close();
}
return ds;
}
public void CreateEvent(string title, string descript, string dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_Calender_Event(ProjectID,CompanyID,Title,Description,EventDate,CreatedAt,Status,Members) values(@pid,@cid,@title,@desc,@date,@At,@status,@mem)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@pid", 4);
sqlConn.Command.Parameters.AddWithValue("@cid", 2);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@desc", descript);
sqlConn.Command.Parameters.AddWithValue("@date", dt);
sqlConn.Command.Parameters.AddWithValue("@At", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@Status", "Active");
sqlConn.Command.Parameters.AddWithValue("@mem", "me");
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void CreateEvent(string title, string descript, string dt)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_Calender_Event(ProjectID,CompanyID,Title,Description,EventDate,CreatedAt,Status,Members) values(@pid,@cid,@title,@desc,@date,@At,@status,@mem)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@pid", 4);
sqlConn.Command.Parameters.AddWithValue("@cid", 2);
sqlConn.Command.Parameters.AddWithValue("@title", title);
sqlConn.Command.Parameters.AddWithValue("@desc", descript);
sqlConn.Command.Parameters.AddWithValue("@date", dt);
sqlConn.Command.Parameters.AddWithValue("@At", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@Status", "Active");
sqlConn.Command.Parameters.AddWithValue("@mem", "me");
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw (ex);
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
/// <summary>
/// //////////////////////creating account for the member ///////////////////////
public void Createaccount(string fname, string lname, string Email, string username, string password, int roleid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_member(Email,UserName,Password,RoleID) output Inserted.MemberID values(@Email,@Username,@Password)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Email", Email);
sqlConn.Command.Parameters.AddWithValue("@Username", username);
sqlConn.Command.Parameters.AddWithValue("@Password", password);
sqlConn.Command.Parameters.AddWithValue("@RoleId", roleid);
sqlConn.Connection.Open();
var id = sqlConn.Command.ExecuteScalar();
sqlConn.Query = "insert into tbl_memberinfo (MemberID,FirstName,LastName) values (@id,@fname,@lname)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Command.Parameters.AddWithValue("@fname", fname);
sqlConn.Command.Parameters.AddWithValue("@lname", lname);
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public List <ToDo> GetOnlyToDo(int tid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
ToDo tdo = new ToDo();
var doList = new List <ToDo>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_ToDo where ToDoID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", tid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
doList.Add(new Admin.Models.ToDo.ToDo
{
ToDoID = Int32.Parse(sqlConn.Reader["ToDoID"].ToString()),
ToDoTitle = sqlConn.Reader["ToDoTitle"].ToString(),
ToDoDescription = sqlConn.Reader["ToDoDescription"].ToString(),
AssignedTo = Int32.Parse(sqlConn.Reader["AssignedTo"].ToString()),
CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
Status = sqlConn.Reader["Status"].ToString(),
Deadline = DateTime.Parse(sqlConn.Reader["Deadline"].ToString()),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(doList);
}
public List <Milestones> GetOnlyMilestones(int pid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Milestones mile = new Milestones();
var mileList = new List <Milestones>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_milestones where MilestonesID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", pid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
mileList.Add(new Admin.Models.Milestones.Milestones
{
MilestonesID = Int32.Parse(sqlConn.Reader["MilestonesID"].ToString()),
MilestonesTitle = sqlConn.Reader["MilestonesTitle"].ToString(),
MilestonesDescription = sqlConn.Reader["MilestonesDescription"].ToString(),
Status = sqlConn.Reader["Status"].ToString(),
Deadline = DateTime.Parse(sqlConn.Reader["Deadline"].ToString()),
CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
UpdatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(mileList);
}
/// /////////////////////////////////////////////////////////////////
/// </summary>
///
///
// public class ImageResult : FileStreamResult
// {
// public ImageResult(Image input):this(input,input.Width,input.Height) { }
// public ImageResult(Image input, int width,int height) :
// base(GetMemoryStream(input,width,height,ImageFormat.Png), "image/png")
//{ }
// static MemoryStream GetMemoryStream(Image input,int width,
// int height,ImageFormat fmt)
// {
// // maintain aspect ratio
// if (input.Width > input.Height)
// height = input.Height * width / input.Width;
// else
// width = input.Width * height / input.Height;
// var bmp = new Bitmap(input, width, height);
// var ms = new MemoryStream();
// bmp.Save(ms, ImageFormat.Png);
// ms.Position = 0;
// return ms;
// }
//}
// ///
// ///
///
///
/// /////////////////
/// <returns></returns>
public List <Member> GetAllmembers()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var memberList = new List <Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select m.MemberID,m.Email,m.UserName,mi.FirstName,mi.LastName from tbl_member as m join tbl_memberinfo as mi on m.MemberID = mi.MemberID ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
//var id = Convert.ToInt32(sqlConn.Command.ExecuteScalar());
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
memberList.Add(new Admin.Models.member.Member
{
MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString()),
Email = sqlConn.Reader["Email"].ToString(),
Username = sqlConn.Reader["Username"].ToString(),
// Password=sqlConn.Reader["Password"].ToString(),
// string Password = GenerateRandomString(Password).ToString();
FirstName = sqlConn.Reader["FirstName"].ToString(),
LastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(memberList);
}
public List <Milestones> GetStatusName()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Milestones mile = new Milestones();
var mileList = new List <Milestones>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select StatusID,Name from tbl_status";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
// sqlConn.Command.Parameters.AddWithValue("@id", sid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
mileList.Add(new Admin.Models.Milestones.Milestones
{
StatusID = Int32.Parse(sqlConn.Reader["StatusID"].ToString()),
Name = sqlConn.Reader["Name"].ToString(),
// MilestonesDescription = sqlConn.Reader["MilestonesDescription"].ToString(),
// CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(mileList);
}
public Member Details(int ID)
{
Member proj = new Member();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select Email,UserName,Password,FirstName,LastName from tbl_member,tbl_memberinfo where tbl_member.MemberID=tbl_memberinfo.MemberID";
sqlConn.Query = "select m.MemberID,m.Email,m.UserName,m.Password,mi.FirstName,mi.LastName from tbl_member as m join tbl_memberinfo as mi on m.MemberID = mi.MemberID where m.MemberID=@memid and mi.MemberID=@memid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@memid", ID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
proj.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
proj.Email = sqlConn.Reader["Email"].ToString();
proj.Username = sqlConn.Reader["UserName"].ToString();
proj.Password = sqlConn.Reader["Password"].ToString();
proj.FirstName = sqlConn.Reader["FirstName"].ToString();
proj.LastName = sqlConn.Reader["LastName"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(proj);
}
public ToDo GetData(int tID)
{
ToDo to = new ToDo();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_todo where ToDoID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", tID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
to.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
to.ToDoID = Int32.Parse(sqlConn.Reader["ToDoID"].ToString());
to.ToDoTitle = sqlConn.Reader["ToDoTitle"].ToString();
to.ToDoDescription = sqlConn.Reader["ToDoDescription"].ToString();
to.AssignedTo = Int32.Parse(sqlConn.Reader["AssignedTo"].ToString());
to.Status = sqlConn.Reader["Status"].ToString();
to.CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString());
to.Deadline = DateTime.Parse(sqlConn.Reader["Deadline"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(to);
}
public List <Events> GetAllEvents()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Events eve = new Events();
var eventList = new List <Events>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select Calender_Event_ID,Title,Description,CreatedAt,EventDate from tbl_Calender_Event ORDER BY CASE WHEN ISDATE (EventDate)=1 THEN CONVERT(Datetime,EventDate,101) ELSE null END ASC";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
eventList.Add(new Admin.Models.Events.Events
{
Calender_Event_ID = Int32.Parse(sqlConn.Reader["Calender_Event_ID"].ToString()),
EventTitle = sqlConn.Reader["Title"].ToString(),
EventDescription = sqlConn.Reader["Description"].ToString(),
CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
EventDate = sqlConn.Reader["EventDate"].ToString(),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(eventList);
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public List <Member> getNameByroleid()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var onlyRoleName = new List <Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select RoleID,Name from tbl_role";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
onlyRoleName.Add(new Admin.Models.member.Member
{
RoleID = Int32.Parse(sqlConn.Reader["RoleID"].ToString()),
Name = sqlConn.Reader["Name"].ToString(),
//.RoleILastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(onlyRoleName);
}
public List <ToDo> GetStatusName()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
ToDo mile = new ToDo();
var toList = new List <ToDo>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select StatusID,Name from tbl_status";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
// sqlConn.Command.Parameters.AddWithValue("@id", sid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
toList.Add(new Admin.Models.ToDo.ToDo
{
StatusID = Int32.Parse(sqlConn.Reader["StatusID"].ToString()),
Name = sqlConn.Reader["Name"].ToString(),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(toList);
}
public Milestones GetData(int mID)
{
Milestones mile = new Milestones();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_milestones where MilestonesID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", mID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
mile.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
mile.MilestonesID = Int32.Parse(sqlConn.Reader["MilestonesID"].ToString());
mile.MilestonesTitle = sqlConn.Reader["MilestonesTitle"].ToString();
mile.MilestonesDescription = sqlConn.Reader["MilestonesDescription"].ToString();
mile.Deadline = DateTime.Parse(sqlConn.Reader["Deadline"].ToString());
mile.Status = sqlConn.Reader["Status"].ToString();
mile.UpdatedAt = DateTime.Parse(sqlConn.Reader["UpdatedAt"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(mile);
}
/// <summary>
/// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///
public List <Member> getonlymembers()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var onlymemberlist = new List <Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select MemberID,FirstName,LastName from tbl_memberinfo";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
onlymemberlist.Add(new Admin.Models.member.Member
{
MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString()),
FirstName = sqlConn.Reader["FirstName"].ToString(),
LastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(onlymemberlist);
}
public Events GetDetails(int id)
{
Events eve = new Events();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select Calender_Event_ID,Title,Description,CreatedAt,EventDate from tbl_Calender_Event where Calender_Event_ID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
eve.Calender_Event_ID = Int32.Parse(sqlConn.Reader["Calender_Event_ID"].ToString());
eve.EventTitle = sqlConn.Reader["Title"].ToString();
eve.EventDescription = sqlConn.Reader["Description"].ToString();
eve.CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString());
eve.EventDate = sqlConn.Reader["EventDate"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(eve);
}
public Milestones Datamilestones(int pid)
{
Milestones data = new Milestones();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select ProjectID, MilestonesID,MilestonesTitle,MilestonesDescription from tbl_milestones where ProjectID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", pid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
data.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
data.MilestonesID = Int32.Parse(sqlConn.Reader["MilestonesID"].ToString());
data.MilestonesTitle = sqlConn.Reader["MilestonesTitle"].ToString();
data.MilestonesDescription = sqlConn.Reader["MilestonesDescription"].ToString();
data.Deadline = DateTime.Parse(sqlConn.Reader["Deadline"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(data);
}
public Member GetMemberNameFromMemberId(int mid)
{
Member mem = new Member();
// string encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID, FirstName,Lastname from tbl_memberinfo where MemberID=@mid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@mid", mid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
mem.FirstName = sqlConn.Reader["FirstName"].ToString();
mem.LastName = sqlConn.Reader["LastName"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(mem);
}
public Milestones StatusNameByStatusId(int sid)
{
Milestones data = new Milestones();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select StatusID,Name from tbl_status where StatusID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", sid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
data.StatusID = Int32.Parse(sqlConn.Reader["StatusID"].ToString());
data.Name = sqlConn.Reader["Name"].ToString();
// data.MilestonesTitle = sqlConn.Reader["MilestonesTitle"].ToString();
// data.MilestonesDescription = sqlConn.Reader["MilestonesDescription"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(data);
}
//public bool ValidateUser(string username, string password)
// {
// bool userExit = false;
// SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
// try
// {
// sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select 1 from tbl_member as tbm where tbm.username=@username and tbm.password=@password";
// sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
// sqlConn.Command.Parameters.AddWithValue("@username", username);
// sqlConn.Command.Parameters.AddWithValue("@password", password);
// sqlConn.Connection.Open();
// using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
// {
// while (sqlConn.Reader.Read())
// {
// userExit = true;
// }
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (sqlConn.Reader != null)
// sqlConn.Reader.Close();
// sqlConn.Connection.Close();
// sqlConn.Connection.Dispose();
// }
// return userExit;
// }
public string GetEncryptedPassword(string username)
{
ProjectLab.Areas.Admin.Models.member.Member mem = new ProjectLab.Areas.Admin.Models.member.Member();
string encPassword = "";
// var encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID,Password from tbl_member as tbm where tbm.username=@username";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@username", username);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
encPassword = sqlConn.Reader["Password"].ToString();
// mem.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(encPassword);
}
//public bool ValidateUser(string username, string password)
// {
// bool userExit = false;
// SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
// try
// {
// sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select 1 from tbl_member as tbm where tbm.username=@username and tbm.password=@password";
// sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
// sqlConn.Command.Parameters.AddWithValue("@username", username);
// sqlConn.Command.Parameters.AddWithValue("@password", password);
// sqlConn.Connection.Open();
// using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
// {
// while (sqlConn.Reader.Read())
// {
// userExit = true;
// }
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// if (sqlConn.Reader != null)
// sqlConn.Reader.Close();
// sqlConn.Connection.Close();
// sqlConn.Connection.Dispose();
// }
// return userExit;
// }
public string GetEncryptedPassword(string username)
{
ProjectLab.Areas.Admin.Models.member.Member mem = new ProjectLab.Areas.Admin.Models.member.Member();
string encPassword = "";
// var encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID,Password from tbl_member as tbm where tbm.username=@username";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@username", username);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
encPassword = sqlConn.Reader["Password"].ToString();
// mem.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return encPassword;
}
public SocketServer(int numConnections, int receiveBufferSize)
{
myMessageHandler = new MessageHandler(connectionString);
m_numConnections = numConnections;
m_receiveBufferSize = receiveBufferSize;
// allocate buffers such that the maximum number of sockets can have one outstanding read and
//write posted to the socket simultaneously
// m_bufferManager = new BufferManager(receiveBufferSize * numConnections * opsToPreAlloc,
// receiveBufferSize);
m_readWritePool = new SocketAsyncEventArgsPool(numConnections);
// m_maxNumberAcceptedClients = new Semaphore(numConnections, numConnections);
SqlDataReader dr = null;
try
{
SQLManager.SQLManager mySql = new SQLManager.SQLManager(connectionString);
dr = mySql.getDataReader("EXEC getGroups");
while (dr.Read())
{
System.Collections.ArrayList userList = new System.Collections.ArrayList();
string users = (string)dr["Users"];
string[] userArr = users.Split((",".ToCharArray()));
for (int i = 0; i<userArr.Length-1; i++)
{
userList.Add(long.Parse(userArr[i]));
}
groupLookup.Add((long)dr["GPID"], userList);
}
}
catch
{
}
finally
{
if (dr != null)
dr.Close();
}
}
public void DeleteSelectedMilestones(int mid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_milestones where MilestonesID=@mileid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@mileid", mid);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Delete(int ID)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_member where MemberID=@MemberID; delete tbl_memberinfo where MemberID=@MemberID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@MemberID", ID);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Delete(int ID)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_Calender_Event where Calender_Event_ID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", ID);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public Member GetMemberIDFromUsername(string username)
{
Member mem = new Member();
// string encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID from tbl_member as tbm where tbm.username=@username";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@username", username);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
mem.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
{
sqlConn.Reader.Close();
}
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return(mem);
}
public void Createmember(string fname, string lname, string Email, string username, string password,int roleid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_member(Email,UserName,Password,RoleID) output Inserted.MemberID values(@Email,@Username,@Password,@rid)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Email", Email);
sqlConn.Command.Parameters.AddWithValue("@Username", username);
sqlConn.Command.Parameters.AddWithValue("@Password", password);
sqlConn.Command.Parameters.AddWithValue("@rid", roleid);
sqlConn.Connection.Open();
var id = sqlConn.Command.ExecuteScalar();
sqlConn.Query = "insert into tbl_memberinfo (MemberID,FirstName,LastName) values (@id,@fn,@ln)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Command.Parameters.AddWithValue("@fn", fname);
sqlConn.Command.Parameters.AddWithValue("@ln", lname);
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
///////////////////////////////////////////////////////////////
public void createimage(string ti)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_memberImage(Title) output Inserted.ImageID values(@Title)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Title", ti);
sqlConn.Connection.Open();
var id = sqlConn.Command.ExecuteScalar();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_memberinfo(ImageID) values (@id)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public List<Project> GetAllProjects()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Project proj = new Project();
var projectList = new List<Project>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select p.ProjectID,p.ProjectTitle,p.ProjectDescription,p.Members from tbl_Projects as p";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
projectList.Add(new Admin.Models.Project.Project
{
ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString()),
ProjectTitle = sqlConn.Reader["ProjectTitle"].ToString(),
ProjectDescription = sqlConn.Reader["ProjectDescription"].ToString(),
ProjectMembers = sqlConn.Reader["Members"].ToString(),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return projectList;
}
public Member GetRoleIDByMemberID(int memid)
{
Member mem = new Member();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select RoleID,MemberID from tbl_member where MemberID=@MemberID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@MemberID", memid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
mem.RoleID = Int32.Parse(sqlConn.Reader["RoleID"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return mem;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public List<Member> getNameByroleid()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var onlyRoleName = new List<Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select RoleID,Name from tbl_role";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
onlyRoleName.Add(new Admin.Models.member.Member
{
RoleID = Int32.Parse(sqlConn.Reader["RoleID"].ToString()),
Name = sqlConn.Reader["Name"].ToString(),
//.RoleILastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return onlyRoleName;
}
public Member GetMemberIDFromUsername(string username)
{
Member mem = new Member();
// string encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID from tbl_member as tbm where tbm.username=@username";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@username", username);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
mem.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return mem;
}
/// /////////////////////////////////////////////////////////////////
/// </summary>
///
///
// public class ImageResult : FileStreamResult
// {
// public ImageResult(Image input):this(input,input.Width,input.Height) { }
// public ImageResult(Image input, int width,int height) :
// base(GetMemoryStream(input,width,height,ImageFormat.Png), "image/png")
//{ }
// static MemoryStream GetMemoryStream(Image input,int width,
// int height,ImageFormat fmt)
// {
// // maintain aspect ratio
// if (input.Width > input.Height)
// height = input.Height * width / input.Width;
// else
// width = input.Width * height / input.Height;
// var bmp = new Bitmap(input, width, height);
// var ms = new MemoryStream();
// bmp.Save(ms, ImageFormat.Png);
// ms.Position = 0;
// return ms;
// }
//}
// ///
// ///
///
///
/// /////////////////
/// <returns></returns>
public List<Member> GetAllmembers()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var memberList = new List<Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select m.MemberID,m.Email,m.UserName,mi.FirstName,mi.LastName from tbl_member as m join tbl_memberinfo as mi on m.MemberID = mi.MemberID ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
//var id = Convert.ToInt32(sqlConn.Command.ExecuteScalar());
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
memberList.Add(new Admin.Models.member.Member
{
MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString()),
Email = sqlConn.Reader["Email"].ToString(),
Username = sqlConn.Reader["Username"].ToString(),
// Password=sqlConn.Reader["Password"].ToString(),
// string Password = GenerateRandomString(Password).ToString();
FirstName = sqlConn.Reader["FirstName"].ToString(),
LastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return memberList;
}
/// <summary>
/// //////////////////////////////get only comments start here.....
/// </summary>
/// <param name="did"></param>
/// <returns></returns>
public List<Project> GetOnlyComments(int did)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Project comm = new Project();
var commlist = new List<Project>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select DiscussionID,Comment from tbl_Comment where DiscussionID=@id";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", did);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
commlist.Add(new Admin.Models.Project.Project
{
ProjectID = Int32.Parse(sqlConn.Reader["DiscussionID"].ToString()),
ProjectTitle = sqlConn.Reader["Comment"].ToString(),
CreatedAt=DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return commlist;
}
public Project GetMembersByProjectID(int pid)
{
Project data = new Project();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select Members from tbl_Projects where ProjectID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", pid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
// data.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
data.ProjectMembers = sqlConn.Reader["Members"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return data;
}
public Project Comments(int did)
{
Project data = new Project();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select DiscussionID,CommentedBy,Comment from tbl_Comment where DiscussionID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", did);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
data.DiscussionID = Int32.Parse(sqlConn.Reader["DiscussionID"].ToString());
data.Comments = sqlConn.Reader["Comment"].ToString();
data.CommentedBy = sqlConn.Reader["CommentedBy"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return data;
}
public Project GetDiscussionTitleByDiscussionID(int ID)
{
{
Project proj = new Project();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select DiscussionTitle from tbl_discussion where DiscussionID=@disID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@disID", ID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
// proj.DiscussionID = Int32.Parse(sqlConn.Reader["DiscussionID"].ToString());
proj.DiscussionTitle = sqlConn.Reader["DiscussionTitle"].ToString();
// proj.CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString());
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return proj;
}
}
public Project GetData(int projectID)
{
Project proj = new Project();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_Projects where ProjectID=@projectID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@projectID", projectID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
proj.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
proj.ProjectTitle = sqlConn.Reader["ProjectTitle"].ToString();
proj.ProjectDescription = sqlConn.Reader["ProjectDescription"].ToString();
proj.ProjectMembers = sqlConn.Reader["Members"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return proj;
}
public void Delete(int ID)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_member where MemberID=@MemberID; delete tbl_memberinfo where MemberID=@MemberID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@MemberID", ID);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Edit(int memID, string fname, string lname, string Email, string Username, string password)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_member set Email=@Email,Username=@Username where MemberID=@MemberID; update tbl_memberinfo set FirstName=@FirstName,LastName=@LastName where MemberID=@MemberID";
// sqlConn.Query = " update tbl_member as m join tbl_memberinfo as mi on set m.Email=@email,m.UserName=@username,mi.FirstName=@Fnam,mi.LastName=@lname from tbl_member as m.MemberID = mi.MemberID ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@Username", Username);
sqlConn.Command.Parameters.AddWithValue("@Password", password);
sqlConn.Command.Parameters.AddWithValue("@Email", Email);
sqlConn.Command.Parameters.AddWithValue("@FirstName", fname);
sqlConn.Command.Parameters.AddWithValue("@LastName", lname);
sqlConn.Command.Parameters.AddWithValue("@MemberID", memID);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public void Delete(int ID)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_Projects where ProjectID=@ProjectID;delete tbl_Discussion where ProjectID=@ProjectID;delete tbl_milestones where ProjectID=@ProjectID ;delete tbl_todo where ProjectID=@ProjectID ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ProjectID", ID);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public Member GetMemberByMemberID(int memID)
{
Member mem = new Member();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select Email,UserName,Password,FirstName,LastName from tbl_member,tbl_memberinfo where tbl_member.MemberID=tbl_memberinfo.MemberID";
sqlConn.Query = "select m.MemberID,m.Email,m.UserName,m.Password,mi.FirstName,mi.LastName from tbl_member as m join tbl_memberinfo as mi on m.MemberID = mi.MemberID where m.MemberID=@MemberID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@MemberID", memID);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
mem.MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString());
mem.Email = sqlConn.Reader["Email"].ToString();
mem.Username = sqlConn.Reader["UserName"].ToString();
mem.Password = sqlConn.Reader["Password"].ToString();
mem.FirstName = sqlConn.Reader["FirstName"].ToString();
mem.LastName = sqlConn.Reader["LastName"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return mem;
}
public void DelDiscussion(int DID)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "delete tbl_discussion where DiscussionID=@ID;delete tbl_Comment where DiscussionID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", DID);
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public Member GetMemberNameFromMemberId(int mid)
{
Member mem = new Member();
// string encPassword = "";
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select MemberID, FirstName,Lastname from tbl_memberinfo where MemberID=@mid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@mid", mid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
if (sqlConn.Reader.Read())
{
mem.FirstName = sqlConn.Reader["FirstName"].ToString();
mem.LastName = sqlConn.Reader["LastName"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return mem;
}
public void CreateProject(string projecTitle, string projecDescription, string fn, string ln, string members)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "insert into tbl_Projects(CompanyID,ProjectTitle,CreatedBy,CreatedAt,UpdatedAt,Status,ProjectDescription,Members) output Inserted.ProjectID values(@companyID,@ProjectTitle,@CreatedBy,@CreatedAt,@updatedAt,@Status,@ProjectDescription,@ids)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@companyID", 2);
sqlConn.Command.Parameters.AddWithValue("@ProjectTitle", projecTitle);
sqlConn.Command.Parameters.AddWithValue("@CreatedBy", 2);
sqlConn.Command.Parameters.AddWithValue("@CreatedAt", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@updatedAt", DateTime.Now);
sqlConn.Command.Parameters.AddWithValue("@Status", "Active");
sqlConn.Command.Parameters.AddWithValue("@ProjectDescription", projecDescription);
sqlConn.Command.Parameters.AddWithValue("@ids", members);
// sqlConn.Command.Parameters.AddWithValue("@ids", files);
sqlConn.Connection.Open();
var id = sqlConn.Command.ExecuteScalar();
sqlConn.Query = "insert into tbl_Project_File(ProjectID) values(@id)";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@id", id);
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
/// <summary>
/// /////////////////////////////////////////////////////////////////////////////////////////////////////////////////
///
public List<Member> getonlymembers()
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Member mem = new Member();
var onlymemberlist = new List<Member>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = " select MemberID,FirstName,LastName from tbl_memberinfo";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
onlymemberlist.Add(new Admin.Models.member.Member
{
MemberID = Int32.Parse(sqlConn.Reader["MemberID"].ToString()),
FirstName = sqlConn.Reader["FirstName"].ToString(),
LastName = sqlConn.Reader["LastName"].ToString()
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return onlymemberlist;
}
/// <summary>
/// ///////////////////////////////////////////////////////////////
/// </summary>
/// <param name="projectID"></param>
/// <param name="projecTitle"></param>
/// <param name="projectDescription"></param>
public void Edit(int projectID, string projecTitle, string projectDescription,string mem)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "update tbl_Projects set ProjectTitle=@projTitle,ProjectDescription=@projDesc,Members=@member where ProjectID=@projectID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
//cmd.Parameters.AddWithValue("@CMSID", empid);
sqlConn.Command.Parameters.AddWithValue("@projTitle", projecTitle);
sqlConn.Command.Parameters.AddWithValue("@projDesc", projectDescription);
sqlConn.Command.Parameters.AddWithValue("@projectID", projectID);
sqlConn.Command.Parameters.AddWithValue("@member", mem);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
//////////////////////////////////////////for discussion ////////////////////////////
public Project Discussion(int pid)
{
Project data = new Project();
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select DiscussionID,DiscussionTitle,DiscussionDescription from tbl_discussion where ProjectID=@ID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@ID", pid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
data.DiscussionID = Int32.Parse(sqlConn.Reader["DiscussionID"].ToString());
data.ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString());
data.DiscussionTitle = sqlConn.Reader["DiscussionTitle"].ToString();
data.DiscussionDescription = sqlConn.Reader["DiscussionDescription"].ToString();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return data;
}
/////////////////////////////////start for the discussion list////////////////
public List<Project> GetAllDiscussion(int pid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
Project Discuss = new Project();
var discussList = new List<Project>();
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select * from tbl_discussion where ProjectID=@pid";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@pid", pid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
discussList.Add(new Admin.Models.Project.Project
{
ProjectID = Int32.Parse(sqlConn.Reader["ProjectID"].ToString()),
// DiscussionID = Int32.Parse(sqlConn.Reader["DiscussionID"].ToString()),
DiscussionTitle = sqlConn.Reader["DiscussionTitle"].ToString(),
DiscussionDescription = sqlConn.Reader["DiscussionDescription"].ToString(),
CreatedAt = DateTime.Parse(sqlConn.Reader["CreatedAt"].ToString()),
CreatedBy=sqlConn.Reader["CreatedBy"].ToString(),
});
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return discussList;
}
public void Details(int projectid)
{
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
sqlConn.Query = "select ProjectID from tbl_Projects where ProjectID=@projectID";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
// sqlConn.Command.Parameters.AddWithValue("@projTitle", projecTitle);
// sqlConn.Command.Parameters.AddWithValue("@projDesc", projectDescription);
sqlConn.Command.Parameters.AddWithValue("@projectID", projectid);
try
{
sqlConn.Connection.Open();
sqlConn.Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
}
public bool CheckRole(int memid)
{
bool userExit = false;
SQLManager.SQLManager sqlConn = new SQLManager.SQLManager();
int roleID = 0;
try
{
sqlConn.Connection = new SqlConnection(sqlConn.ConnectionString);
// sqlConn.Query = "select m.MemberID,m.RoleID,r.RoleID,r.Name from tbl_memberinfo as m inner join tbl_role as r on m.RoleID=r.RoleID where m.MemberID=@memberid";
sqlConn.Query = "select MemberID,RoleID from tbl_member where MemberID=@memid ";
sqlConn.Command = new SqlCommand(sqlConn.Query, sqlConn.Connection);
sqlConn.Command.Parameters.AddWithValue("@memid", memid);
sqlConn.Connection.Open();
using (sqlConn.Reader = sqlConn.Command.ExecuteReader())
{
while (sqlConn.Reader.Read())
{
// userExit = true;
//ProjectLab.Areas.Admin.Models.member.Member mem = new ProjectLab.Areas.Admin.Models.member.Member();
roleID = Int32.Parse(sqlConn.Reader["RoleID"].ToString());
}
}
if (roleID == 1) //Admin
{
return true;
}
else if (roleID == 2) //Project Manager
{
return true;
}
else if (roleID == 3) //Member
{
return true;
}
else if (roleID == 4) //EndUser
{
return false;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlConn.Reader != null)
sqlConn.Reader.Close();
sqlConn.Connection.Close();
sqlConn.Connection.Dispose();
}
return userExit;
}