//public bool DeleteCategory(int catID)
//{
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID", catID)
// };
// return SqlDBHelper.ExecuteNonQuery("sp_CategoryDelete", CommandType.StoredProcedure, parameters);
//}
public Category GetCategoryDetails(int catID)
{
Category category = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ID", catID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_CategorySelect]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
category = new Category();
category.ID = Convert.ToInt32(row["ID"]);
category.Name = row["name"] as string;
category.Description = row["description"] as string;
}
}
return(category);
}
public List <Category> GetCategoryListByLocId(int locID)
{
List <Category> categorylist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@location", locID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_CategorySelectBylocation]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
categorylist = new List <Category>();
foreach (DataRow row in table.Rows)
{
Category category = new Category();
category.ID = Convert.ToInt32(row["CategoryID"]);
category.Name = row["CategoryName"] as string;
category.Description = row["description"] as string;
categorylist.Add(category);
}
}
}
return(categorylist);
}
//public bool DeleteResource(int resID)
//{
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID", resID)
// };
// return SqlDBHelper.ExecuteNonQuery("sp_ResourceDelete", CommandType.StoredProcedure, parameters);
//}
public Resource GetResourceDetails(int resID)
{
Resource resource = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ID", resID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_ResourceSelect]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
resource = new Resource();
resource.ID = Convert.ToInt32(row["ID"]);
resource.Name = row["name"] as string;
resource.Location = row["location_name"] as string;
resource.Description = row["description"] as string;
resource.CategoryID = Convert.ToInt32(row["categoryID"]);
resource.Capacity = Convert.ToInt32(row["capacity"]);
}
}
return(resource);
}
//***************
public List <Option> GetOptionListByReqIDID(int reqID)
{
List <Option> optionlist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@reqID", reqID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_OptionsSelectByReqID]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
optionlist = new List <Option>();
foreach (DataRow row in table.Rows)
{
Option option = new Option();
option.ID = Convert.ToInt32(row["ID"]);
option.Name = row["name"] as string;
optionlist.Add(option);
}
}
}
return(optionlist);
}
public bool IsGreaterThan30Days_LastTime_Sms_Sent(string mobile, bool IsShounatSms = false, string loggerPath = null)
{
bool res = false;
string note = "";
if (!string.IsNullOrEmpty(mobile))
{
SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Mobile", mobile), new SqlParameter("@IsShounatSms", IsShounatSms) };
var dt = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_Select_From_tbl_StudentDefence_Log]", CommandType.StoredProcedure, parameters);
if (dt != null && dt.Rows.Count > 0)
{
note += $"CountDt : {dt.Rows.Count}\t\tLastDateSentSms : { dt.Rows[0].Field<string>("LastDateSentSms")}\t\t";
var LastDateSentSms = dt.Rows[0].Field <string>("LastDateSentSms");
if (!string.IsNullOrEmpty(LastDateSentSms))
{
var diffDate = (DateTime.Now.Date - LastDateSentSms.ToGregorian().Date).TotalDays;
note += $"DiffDate : {diffDate}\t\t";
//اگراخرین دفاع مربوط به بیش از 30 روز پیش بود
if (diffDate >= 30)
{
res = true;
}
}
}
else //هنوز رکوردی در جدول وجود نداشت
{
res = true;
note += "CountDt : Dt has no rows\t\t";
}
}
note += $"IsGreaterThan : {res}";
ServerLogger(mobile, note);
return(res);
}
//public bool AddNewRC_User(RC_User user)
//{
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@name", user.Name),
// new SqlParameter("@RoleID", user.RoleID),
// new SqlParameter("@DaneshID",user.DaneshID)
// };
// return SqlDBHelper.ExecuteNonQuery("sp_RC_UsersInsert", CommandType.StoredProcedure, parameters); ;
//}
//public bool UpdateRC_User(RC_User user)
//{
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID",user.ID),
// new SqlParameter("@name", user.Name),
// new SqlParameter("@RoleID", user.RoleID),
// new SqlParameter("@DaneshID",user.DaneshID)
// };
// return SqlDBHelper.ExecuteNonQuery("sp_RC_UsersUpdate", CommandType.StoredProcedure, parameters);
//}
public List <RC_User> GetOstadListByDaneshID(int daneshID)
{
List <RC_User> userlist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@daneshID", daneshID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_GetOstadListByDaneshID]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
userlist = new List <RC_User>();
foreach (DataRow row in table.Rows)
{
RC_User user = new RC_User();
user.ID = Convert.ToInt32(row["code_ostad"]);
user.Name = (row["name"] as string) + " " + (row["family"] as string);
//user.RoleID = Convert.ToInt32(row["RoleID"]);
//user.DaneshID = Convert.ToInt32(row["DaneshID"]);
userlist.Add(user);
}
}
}
return(userlist);
}
public List <Resource> GetResourceListByLocationIdandCatId(int locId, int catId)
{
List <Resource> resourcelist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@locId", locId),
new SqlParameter("catId", catId)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_GetResourceListByLocationIdandCatId]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
resourcelist = new List <Resource>();
foreach (DataRow row in table.Rows)
{
Resource resource = new Resource();
resource.ID = Convert.ToInt32(row["ID"]);
resource.Name = row["name"] as string;
resource.Location = row["location_name"] as string;
resource.Description = row["description"] as string;
resource.CategoryID = Convert.ToInt32(row["categoryID"]);
resource.Capacity = Convert.ToInt32(row["capacity"]);
resourcelist.Add(resource);
}
}
}
return(resourcelist);
}
public List <Res_Opt_Junc> GetRes_Opt_JuncListByResID(int resID)
{
List <Res_Opt_Junc> res_opt_junclist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ResID", resID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_res_opt_juncSelectByResID]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
res_opt_junclist = new List <Res_Opt_Junc>();
foreach (DataRow row in table.Rows)
{
Res_Opt_Junc res_opt_junc = new Res_Opt_Junc();
res_opt_junc.ID = Convert.ToInt32(row["ID"]);
res_opt_junc.Res_id = Convert.ToInt32(row["Res_id"]);
res_opt_junc.Opt_id = Convert.ToInt32(row["Opt_id"]);
res_opt_junc.IsActive = Convert.ToBoolean(row["IsActive"]);
res_opt_junclist.Add(res_opt_junc);
}
}
}
return(res_opt_junclist);
}
public List <Location> GetLocationByUserRoleId(int roleId)
{
List <Location> loclist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@roleId", roleId)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_LocationsSelectByRoleId]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
loclist = new List <Location>();
foreach (DataRow row in table.Rows)
{
Location loc = new Location();
loc.Name = row["location"] as string;
//loc.Address = row["address"] as string;
loclist.Add(loc);
}
}
}
return(loclist);
}
public List <RequestDateTime> GetDateTimeListByRequestIdForStudent(int requestId)
{
List <RequestDateTime> dateTimelist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@requestId", requestId),
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_GetDateTimeListByRequestIdForStudent]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
dateTimelist = new List <RequestDateTime>();
foreach (DataRow row in table.Rows)
{
RequestDateTime dateTime = new RequestDateTime();
dateTime.DateTimeId = Convert.ToInt32(row["DateTimeId"]);
dateTime.Date = row["Date"].ToString();
dateTime.StartTime = Convert.ToInt64(row["StartTime"]);
dateTime.EndTime = Convert.ToInt64(row["EndTime"]);
dateTime.RequestId = Convert.ToInt32(row["RequestId"]);
dateTime.ResourceId = Convert.ToInt32(row["ResourceId"] ?? 0);
dateTime.MayConflict = Convert.ToBoolean(row["MayConflict"]);
dateTime.ClassName = row["ClassName"].ToString();
dateTimelist.Add(dateTime);
}
}
}
return(dateTimelist);
}
public List <Location> GetLocationListByCatID(int catID)
{
List <Location> loclist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@catID", catID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_LocationsSelectByCatID]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
loclist = new List <Location>();
foreach (DataRow row in table.Rows)
{
Location loc = new Location();
loc.Id = Convert.ToInt32(row["location"]);
loc.Name = row["location_name"] as string;
//loc.Address = row["address"] as string;
loclist.Add(loc);
}
}
}
return(loclist);
}
//public bool DeleteRC_User(int catID)
//{
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID", catID)
// };
// return SqlDBHelper.ExecuteNonQuery("sp_RC_UsersDelete", CommandType.StoredProcedure, parameters);
//}
public RC_User GetUserDetails(int usrID)
{
RC_User user = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@userID", usrID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("spgetuserdetail", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
user = new RC_User();
user.ID = Convert.ToInt32(row["code_ostad"]);
user.Name = row["name"] as string + " " + row["family"] as string;
//user.RoleID =Convert.ToInt32(row["RoleID"]);
//user.DaneshID = Convert.ToInt32(row["DaneshID"]);
}
}
return(user);
}
public DataTable CheckOneDateTimeWithResourceIdPlusForStudent(int dateTimeId, int resourceId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@dateTimeId", dateTimeId),
new SqlParameter("@ResourceId", resourceId),
};
return(SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[CheckOneDateTimeWithResourceIdPlusForStudent]", CommandType.StoredProcedure, parameters));
}
public List <Course> GetCourseListByUserID(int userID)
{
List <Course> courselist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ostadID", userID)
};
//need sp to fetch course list by teacher id
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_GetListOfThisTermClassOfOstad]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
courselist = new List <Course>();
foreach (DataRow row in table.Rows)
{
Course course = new Course();
course.DID = Convert.ToInt32(row["did"]);
string kstime = "";
if (row["saatklass"] != System.DBNull.Value)
{
kstime = (row["saatklass"] as string).Replace("شماره کلاس کلاس آنلاينعادي", "");
kstime = kstime.Replace("شماره کلاسکلاس آنلاينعادي", "");
}
course.Name = row["namedars"] as string + " - " + kstime;
course.Capacity = Convert.ToInt32(row["zarfporm"].ToString());
course.saatklass = row["saatklass"] as string;
course.DaneshID = Convert.ToInt32(row["iddanesh"].ToString());
var catId = row["catID"].ToString();
if (!(string.IsNullOrEmpty(catId) || string.IsNullOrWhiteSpace(catId)))
{
course.catID = Convert.ToInt32(row["catID"].ToString());
}
var status = row["status"].ToString();
if (!(string.IsNullOrEmpty(status) || string.IsNullOrWhiteSpace(status)))
{
if (row["status"] != null)
{
course.status = Convert.ToInt32(row["status"].ToString());
}
}
courselist.Add(course);
}
}
}
return(courselist);
}
//public Resource GetResourcelink(int resID)
//{
// Resource resource = null;
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID", resID)
// };
// using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_GetResourceLinkByResID]", CommandType.StoredProcedure, parameters))
// return resource;
//}
public string GetResourcelink(int resourceId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@resID", resourceId)
};
var result = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[sp_GetResourceLinkByResID]", CommandType.StoredProcedure, parameters);
var link = string.Empty;
if (result != null && result.Rows.Count > 0)
{
link = result.Rows[0][0] as string;
}
return(link);
}
public bool HasSentSmsTodayForShounat(string mobile, bool IsShounatSms = false)
{
bool sentSms = false;
if (!string.IsNullOrEmpty(mobile) && IsShounatSms)
{
SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Mobile", mobile), new SqlParameter("@IsShounatSms", IsShounatSms) };
var dt = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_Select_From_tbl_StudentDefence_Log]", CommandType.StoredProcedure, parameters);
//اگر رکوردی به تاریخ امروز وجو داشت یعنی اس ام اس ارسال شده
if (dt != null && dt.Rows.Count > 0)
{
sentSms = true;
}
}
return(sentSms);
}
public DataTable GetShortTermProfByDepId(int depId)
{
DataTable userlist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@DEPARTMENT_CODE", depId)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[dbo].[SP_Get_AllProfessor_By_DepartmentCode]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
userlist = table.Copy();
}
}
return(userlist);
}
public DataTable GetShortCourseList()
{
DataTable courselist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@depcode", 0)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[dbo].[SP_Get_AllProfessor]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count > 0)
{
courselist = table.Copy();
}
}
return(courselist);
}
public Course GetCourseDetails2(int classID)
{
Course course = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@classID", classID)
};
//need an stored procedure to get course data
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
course = new Course();
course.Name = row["CourseName"] as string;
course.Capacity = Convert.ToInt32(row["FILL_CAPACITY"]);
course.DaneshID = Convert.ToInt32(row["DepCode"]);
}
}
return(course);
}
public RC_User Get_Ostad_Details(int ostadID)
{
RC_User user = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ostadID", ostadID)
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_GetOstadName]", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
user = new RC_User();
user.ID = Convert.ToInt32(row["code_ostad"]);
user.Name = (row["name"] as string) + " " + (row["family"] as string);
//user.DaneshID = Convert.ToInt32(row["DaneshID"]);
}
}
return(user);
}
public Course GetCourseDetails(int corID)
{
Course course = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@courseID", corID)
};
//need an stored procedure to get course data , no sp exist yet !!!!!!!!
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("SP_GetCourseZarfDanesh", CommandType.StoredProcedure, parameters))
{
if (table.Rows.Count == 1)
{
DataRow row = table.Rows[0];
course = new Course();
course.DID = Convert.ToInt32(row["dcode"]);
course.Name = row["namedars"] as string;
course.Capacity = Convert.ToInt32(row["zarfporm"]);
course.DaneshID = Convert.ToInt32(row["daneshid"]);
}
}
return(course);
}
