public List <KeHoachCT> GetKeHoachCongTac(JQueryDataTableParamModel param)
{
const string storeName = "st_GetKetHoachCongTac";
string query = @"SELECT DATENAME(dw,TuNgay) as Ngay, [IdKeHoach]
,khct.TenKeHoach
,nd.HoTen
,nd.ThuTu
,khct.[UserName]
,TuNgay AS ThoiGian
,[KeHoachCongTac]
,[TuGio] +[TuNgay] AS TuNgayModel
,[DenGio]+[TuNgay] AS DenNgayModel
,[DiaDiem]
,TuGio
,DenGio
,DenNgay AS DenNgayView
,cv.VietTat
,cv.TenChucVu as ChucVu
,CASE MucDo
WHEN 1 THEN N'Rất quan trọng'
WHEN 2 THEN N'Quan trọng'
WHEN 3 THEN N'Bình thường'
END AS MucDoView
FROM [KeHoachCongTac] khct LEFT JOIN [dbo].[NguoiDung] nd
on khct.UserName=nd.UserName
LEFT JOIN ChucVu cv
on cv.IdChucVu=nd.ChucVu
" ;
@query = @query + " WHERE khct.IsDeleted=0 AND ((TuNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "') OR "
+ "(DenNgay BETWEEN '" + param.StartDate + "' AND '" + param.EndDate + "')) ";
if (!string.IsNullOrEmpty(param.CanBo))
{
@query = @query + " AND khct.UserName IN (" + param.CanBo + ")";
}
if (!string.IsNullOrEmpty(param.sSearch))
{
param.sSearch = param.sSearch.Trim();
@query = @query + " AND ( khct.TenKeHoach LIKE N'%" + param.sSearch +
"%' OR khct.KeHoachCongTac LIKE N'%" + param.sSearch +
"%' OR khct.DiaDiem LIKE N'%" + param.sSearch
+ "%' OR nd.HoTen LIKE N'%" + param.sSearch
+ "%')";
}
@query = @query + " ORDER BY nd.ThuTu ASC ";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@Query", @query),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public List <KeHoachCT> GetLichCongTac()
{
const string storeName = "st_getKeHoach";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public List <PhongBan> GetNguoiDungPhongBan()
{
const string storeName = "st_getNguoiDungPhongBan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <PhongBan>(reader));
}
}
public List <ThamDuModel> GetThamDuByIdKeHoach(int id)
{
const string storeName = "st_getThamDuByIdKeHoach";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@IdKeHoach", id),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <ThamDuModel>(reader));
}
}
public List <Phong> GetPhongByUser(string user)
{
const string storeName = "st_GetDatPhongByUser";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@User", user)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <Phong>(reader));
}
}
public List <ChucVuModel> GetChucVuTheoPhongBan(int idPhongBan)
{
const string storeName = "st_getChucVuTheoPhongBan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@IdPhongBan", idPhongBan)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <ChucVuModel>(reader));
}
}
public List <Phong> GetPhongHopTheoNgay(DateTime ngay, int id)
{
const string storeName = "st_getPhongTheoNgay";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@Ngay", ngay),
new SqlParameter("@Id", id),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <Phong>(reader));
}
}
public List <KeHoachCT> GetThamDuAndChuTri(string userName, DateTime startDate, DateTime endDate)
{
const string storeName = "st_getThamDuAndChuTri";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@UserName", userName),
new SqlParameter("@StartDate", startDate),
new SqlParameter("@EndDate", endDate),
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}
public List <User> CheckLogin(User user)
{
const string storeName = "st_CheckLogin";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@UserName", user.UserName),
new SqlParameter("@Password", user.Password)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <User>(reader));
}
}
public List <User> GetListNguoiDung(JQueryDataTableParamModel param)
{
const string query = @"st_getNguoiDung";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@DisplayLength", param.iDisplayLength),
new SqlParameter("@DisplayStart", param.iDisplayStart),
new SqlParameter("@SortCol", param.ISortCol_0),
new SqlParameter("@SortDir", param.SSortDir_0),
new SqlParameter("@Search", param.sSearch),
};
SqlDataReader reader = conn.ExecDataReaderProc(query, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <User>(reader));
}
}
public List <KeHoachCT> GetKeHoachCaNhan(JQueryDataTableParamModel param)
{
const string storeName = "st_getKeHoachCaNhan";
using (var conn = new AdoHelper())
{
SqlParameter[] objectParam = new SqlParameter[] {
new SqlParameter("@DisplayLength", param.iDisplayLength),
new SqlParameter("@DisplayStart", param.iDisplayStart),
new SqlParameter("@SortCol", param.ISortCol_0),
new SqlParameter("@SortDir", param.SSortDir_0),
new SqlParameter("@Search", param.sSearch),
new SqlParameter("@UserName", param.CanBo),
new SqlParameter("@StartDate", param.StartDate),
new SqlParameter("@EndDate", param.EndDate),
new SqlParameter("@Type", param.Type)
};
SqlDataReader reader = conn.ExecDataReaderProc(storeName, objectParam);
// convert table result to List object and return
return(DataReaderExtensions.DataReaderToObjectList <KeHoachCT>(reader));
}
}