public bool UpdateLocation(Location loc, string oldname)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@oldname", oldname),
new SqlParameter("@name", loc.Name),
new SqlParameter("@address", loc.Address)
};
return(SqlDBHelper.ExecuteNonQuery("[Resource_Control].[sp_LocationUpdate]", CommandType.StoredProcedure, parameters));
}
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 int CheckOneDateTimeWithResourceIdPlus(int dateTimeId, int resourceId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@dateTimeId", dateTimeId),
new SqlParameter("@ResourceId", resourceId),
};
return(SqlDBHelper.ExecuteScalar("[Resource_Control].[SP_CheckOneDateTimeWithResourceIdPlus]", CommandType.StoredProcedure, parameters));
}
public bool UpdateCategory(Category category)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ID", category.ID),
new SqlParameter("@name", category.Name),
new SqlParameter("@description", category.Description)
};
return(SqlDBHelper.ExecuteNonQuery("[Resource_Control].[sp_CategoryUpdate]", CommandType.StoredProcedure, parameters));
}
public bool UpdateRes_Opt_Junc(Res_Opt_Junc res_opt_junc)
{
SqlParameter[] parameters = new SqlParameter[]
{
// new SqlParameter("@ID",res_opt_junc.ID),
new SqlParameter("@Res_Id", res_opt_junc.Res_id),
new SqlParameter("@Opt_Id", res_opt_junc.Opt_id),
new SqlParameter("@IsActive", res_opt_junc.IsActive)
};
return(SqlDBHelper.ExecuteNonQuery("[Resource_Control].[sp_res_opt_juncUpdate]", CommandType.StoredProcedure, parameters));
}
public int AddNewResource(Resource resource)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@name", resource.Name),
new SqlParameter("@location", resource.Location),
new SqlParameter("@description", resource.Description),
new SqlParameter("@categoryID", resource.CategoryID),
new SqlParameter("@capacity", resource.Capacity)
};
return(SqlDBHelper.ExecuteScalar("[Resource_Control].[sp_ResourceInsert]", CommandType.StoredProcedure, parameters));
}
public int InsertNewDateTimeForDefenceV2(RequestDateTime dateTime, int collegeId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Date", dateTime.Date),
new SqlParameter("@StartTime", dateTime.StartTime),
new SqlParameter("@EndTime", dateTime.EndTime),
new SqlParameter("@RequestId", dateTime.RequestId),
new SqlParameter("@collegeId", collegeId),
};
return(SqlDBHelper.ExecuteScalar("[Resource_Control].[sp_InsertRequestDateTimeForDefenceAddRequest]", CommandType.StoredProcedure, parameters));;
}
public int UpdateOneDateTimeRequest(RequestDateTime reqDateTime)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@dateTimeId", reqDateTime.DateTimeId),
new SqlParameter("@Date", reqDateTime.Date),
new SqlParameter("@StartTime", reqDateTime.StartTime),
new SqlParameter("@EndTime", reqDateTime.EndTime),
new SqlParameter("@ResourceId", reqDateTime.ResourceId)
};
return(SqlDBHelper.ExecuteScalar("[Resource_Control].[SP_UpdateOneDateTimeRequest]", CommandType.StoredProcedure, parameters));
}
//[Resource_Control].[SP_Log_Defence_LastDate_Sent_Sms]
public bool AddOrUpdate_tbl_StudentDefence_Log(string mobile, bool IsShounatSms = false)
{
bool res = false;
if (!string.IsNullOrEmpty(mobile))
{
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@Mobile", mobile)
, new SqlParameter("@IsShounatSms", IsShounatSms)
};
res = SqlDBHelper.ExecuteNonQuery("[Resource_Control].[SP_AddOrUpdate_tbl_StudentDefence_Log]", CommandType.StoredProcedure, parameters);
}
return(res);
}
public void InsertListOfDateTime(List <RequestDateTime> dateTimeList)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Date", SqlDbType.NVarChar, 10, "Date"),
new SqlParameter("@StartTime", SqlDbType.BigInt, 1, "StartTime"),
new SqlParameter("@EndTime", SqlDbType.BigInt, 1, "EndTime"),
new SqlParameter("@RequestId", SqlDbType.Int, 1, "RequestId"),
new SqlParameter("@ResourceId", SqlDbType.Int, 1, "ResourceId"),
new SqlParameter("@MayConflict", SqlDbType.Bit, 1, "MayConflict"),
};
SqlDBHelper.ExecuteNonQueryList("[Resource_Control].[sp_InsertRequestDateTime]", CommandType.StoredProcedure, parameters, dateTimeList);
}
public bool UpdateResource(Resource resource)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@ID", resource.ID),
new SqlParameter("@name", resource.Name),
new SqlParameter("@location", resource.Location),
new SqlParameter("@description", resource.Description),
new SqlParameter("@categoryID", resource.CategoryID),
new SqlParameter("@IsDeleted", resource.IsDeleted),
new SqlParameter("@capacity", resource.Capacity)
};
return(SqlDBHelper.ExecuteNonQuery("[Resource_Control].[sp_ResourceUpdate]", 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 int UpdateListOfDateTimeForDefence(List <RequestDateTime> dateTimeList)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@DateTimeId", SqlDbType.NVarChar, 10, "DateTimeId"),
new SqlParameter("@Date", SqlDbType.NVarChar, 10, "Date"),
new SqlParameter("@StartTime", SqlDbType.BigInt, 1, "StartTime"),
new SqlParameter("@EndTime", SqlDbType.BigInt, 1, "EndTime"),
new SqlParameter("@RequestId", SqlDbType.Int, 1, "RequestId"),
new SqlParameter("@ResourceId", SqlDbType.Int, 1, "ResourceId"),
new SqlParameter("@MayConflict", SqlDbType.Bit, 1, "MayConflict"),
};
return(SqlDBHelper.ExecuteNonQueryList("[Resource_Control].[SP_UpdateRequestDateTimeForDefence]", CommandType.StoredProcedure, parameters, dateTimeList));
}
//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 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 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 void InsertIntoDefenceSmsLog(string fullName, string userID, int requestId, string mobile, bool?smsType, string smsResult, DateTime dateSendSms)
{
try
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@FullName", fullName),
new SqlParameter("@StCode_OstCode", userID),
new SqlParameter("@RequestId", requestId),
new SqlParameter("@Mobile", mobile),
new SqlParameter("@SmsType", smsType),
new SqlParameter("@SmsResult", smsResult),
new SqlParameter("@DateSendSms", dateSendSms)
};
SqlDBHelper.ExecuteNonQuery("[Resource_Control].[SP_InsertIntoDefenceSmsLog]", CommandType.StoredProcedure, parameters);
}
catch (Exception x)
{
throw x;
}
}
//public Option GetOptionDetails(int optID)
//{
// Option option = null;
// SqlParameter[] parameters = new SqlParameter[]
// {
// new SqlParameter("@ID", optID)
// };
// using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("sp_OptionSelect", CommandType.StoredProcedure, parameters))
// {
// if (table.Rows.Count == 1)
// {
// DataRow row = table.Rows[0];
// option = new Option();
// option.ID = Convert.ToInt32(row["ID"]);
// option.Name = row["name"] as string;
// }
// }
// return option;
//}
public List <Option> GetOptionList()
{
List <Option> optionlist = null;
using (DataTable table = SqlDBHelper.ExecuteSelectCommand("[Resource_Control].[sp_OptionsSelectAll]", CommandType.StoredProcedure))
{
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 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 List <Location> GetAllLocation()
{
List <Location> loclist = null;
using (DataTable table = SqlDBHelper.ExecuteSelectCommand("[Resource_Control].[sp_LocationsSelectAll]", CommandType.StoredProcedure))
{
if (table.Rows.Count > 0)
{
loclist = new List <Location>();
foreach (DataRow row in table.Rows)
{
Location loc = new Location();
loc.Id = Convert.ToInt32(row["Id"]);
loc.Name = row["name"] as string;
loc.Address = row["address"] as string;
loclist.Add(loc);
}
}
}
return(loclist);
}
public List <RequestDateTime> GetDateTimeListByRequestId(int requestId)
{
List <RequestDateTime> dateTimelist = null;
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@requestId", requestId),
};
using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("[Resource_Control].[SP_GetDateTimeListByRequestId]", 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"]);
if (row["ResourceId"] != DBNull.Value)
{
dateTime.ResourceId = Convert.ToInt32(row["ResourceId"] ?? 0);
}
dateTime.MayConflict = Convert.ToBoolean(row["MayConflict"]);
dateTime.ClassName = row["ClassName"].ToString();
dateTimelist.Add(dateTime);
}
}
}
return(dateTimelist);
}
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);
}
public List <Category> GetCategoryList()
{
List <Category> categorylist = null;
using (DataTable table = SqlDBHelper.ExecuteSelectCommand("[Resource_Control].[sp_CategorySelectAll]", CommandType.StoredProcedure))
{
if (table.Rows.Count > 0)
{
categorylist = new List <Category>();
foreach (DataRow row in table.Rows)
{
Category category = new Category();
category.ID = Convert.ToInt32(row["ID"]);
category.Name = row["name"] as string;
category.Description = row["description"] as string;
categorylist.Add(category);
}
}
}
return(categorylist);
}
