public List <KetQuaHoSoModel> LayDSKetQua()
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_KET_QUA_HS_LayDSKetQua";
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <KetQuaHoSoModel> result = new List <KetQuaHoSoModel>();
foreach (DataRow item in dt.Rows)
{
KetQuaHoSoModel kq = new KetQuaHoSoModel();
kq.ID = Convert.ToInt32(item["ID"].ToString());
kq.Ten = item["Ten"].ToString();
result.Add(kq);
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public int LayMaTinh(int maHuyen)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_KHU_VUC_LayMaTinhByMaHuyen";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@MaHuyen", maHuyen));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(0);
}
List <DoiTacModel> rs = new List <DoiTacModel>();
foreach (DataRow item in dt.Rows)
{
return(Convert.ToInt32(item["ID"].ToString()));
}
return(0);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public string GetListRule(string id)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_RULE_GetWidthID";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@ID", id));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
return(item["Rule"].ToString());
}
}
}
}
return("");
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <KhuVucModel> LayDSHuyen(int maTinh)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_KHU_VUC_LayDSHuyen";
command.Parameters.Add(new SqlParameter("@MaTinh", maTinh));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <KhuVucModel> rs = new List <KhuVucModel>();
foreach (DataRow item in dt.Rows)
{
KhuVucModel kv = new KhuVucModel();
kv.ID = Convert.ToInt32(item["ID"].ToString());
kv.Ten = item["Ten"].ToString();
rs.Add(kv);
}
return(rs);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public string LayChuoiMaCha(int maNhom)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHOM_LayChuoiMaChaCuaMaNhom";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@MaNhom", maNhom));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return("");
}
return(dt.Rows[0]["ChuoiMaCha"].ToString());
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <UserPMModel> LayDSByMaQL(int maQL)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHAN_VIEN_LayDSByMaQL";
command.Parameters.Add(new SqlParameter("@MaQL", maQL));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <UserPMModel> rs = new List <UserPMModel>();
foreach (DataRow item in dt.Rows)
{
UserPMModel us = new UserPMModel();
us.IDUser = Convert.ToInt32(item["ID"].ToString());
us.Code = item["Code"].ToString();
us.FullName = item["HoTen"].ToString();
rs.Add(us);
}
return(rs);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public async Task <List <LoaiTaiLieuModel> > LayDS()
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_LOAI_TAI_LIEU_LayDS";
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <LoaiTaiLieuModel> rs = new List <LoaiTaiLieuModel>();
foreach (DataRow item in dt.Rows)
{
LoaiTaiLieuModel cm = new LoaiTaiLieuModel();
cm.ID = Convert.ToInt32(item["ID"].ToString());
cm.Ten = item["Ten"].ToString();
cm.BatBuoc = Convert.ToInt32(item["BatBuoc"].ToString());
rs.Add(cm);
}
return(rs);
}
}
catch (Exception ex)
{
throw ex;
}
}
public List <NhomDropDownModel> LayDSNhom()
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHOM_LayDSNhom";
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <NhomDropDownModel> result = new List <NhomDropDownModel>();
foreach (DataRow item in dt.Rows)
{
NhomDropDownModel nhom = new NhomDropDownModel();
nhom.ID = Convert.ToInt32(item["ID"].ToString());
nhom.Ten = item["Ten"].ToString();
result.Add(nhom);
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool Trung(string ma)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_SAN_PHAM_VAY_DemTrungMa";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@Ma", ma));
int rs = Convert.ToInt32(command.ExecuteScalar());
if (rs > 0)
{
return(true);
}
return(false);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <NhanVienInfoModel> LayDS()
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHAN_VIEN_LayDSCourierCode";
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <NhanVienInfoModel> rs = new List <NhanVienInfoModel>();
foreach (DataRow item in dt.Rows)
{
NhanVienInfoModel us = new NhanVienInfoModel();
us.ID = Convert.ToInt32(item["ID"].ToString());
us.FullText = item["FullText"].ToString();
rs.Add(us);
}
return(rs);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public UserPMModel Get(string user)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_PD_USER_GetWidthUserName";
command.Parameters.Add(new SqlParameter("@UserName", user));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
UserPMModel userModel = new UserPMModel();
userModel.IDUser = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
userModel.Email = dt.Rows[0]["Email"].ToString();
userModel.UserName = dt.Rows[0]["UserName"].ToString();
userModel.FullName = dt.Rows[0]["FullName"].ToString();
userModel.TypeUser = Convert.ToInt32(dt.Rows[0]["Type"]);
return(userModel);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <SanPhamModel> LaySanPhamByID(int id, int maHS)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_SAN_PHAM_VAY_LayDSByIDAndMaHS";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@MaDoiTac", id));
command.Parameters.Add(new SqlParameter("@MaHS", maHS));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <SanPhamModel> rs = new List <SanPhamModel>();
foreach (DataRow item in dt.Rows)
{
SanPhamModel cm = new SanPhamModel();
cm.ID = Convert.ToInt32(item["ID"].ToString());
cm.Ten = item["Ten"].ToString();
rs.Add(cm);
}
return(rs);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool Sua(NhomModel nhom, List <int> lstThanhVien)
{
using (var session = LOANSessionManager.OpenSession())
using (var transaction = session.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
IDbCommand commandNhom = new SqlCommand();
commandNhom.Connection = session.Connection;
commandNhom.CommandType = CommandType.StoredProcedure;
commandNhom.CommandText = "sp_NHOM_Sua";
session.Transaction.Enlist(commandNhom);
commandNhom.Parameters.Add(new SqlParameter("@ID", nhom.ID));
commandNhom.Parameters.Add(new SqlParameter("@MaNhomCha", nhom.MaNhomCha));
commandNhom.Parameters.Add(new SqlParameter("@MaNguoiQL", nhom.MaNguoiQL));
commandNhom.Parameters.Add(new SqlParameter("@TenVietTat", nhom.TenNgan));
commandNhom.Parameters.Add(new SqlParameter("@Ten", nhom.Ten));
commandNhom.Parameters.Add(new SqlParameter("@ChuoiMaCha", nhom.ChuoiMaCha));
commandNhom.ExecuteNonQuery();
IDbCommand commandXoaNhanVienNhom = new SqlCommand();
commandXoaNhanVienNhom.Connection = session.Connection;
commandXoaNhanVienNhom.CommandType = CommandType.StoredProcedure;
commandXoaNhanVienNhom.CommandText = "sp_NHAN_VIEN_NHOM_Xoa";
session.Transaction.Enlist(commandXoaNhanVienNhom);
commandXoaNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhom", nhom.ID));
commandXoaNhanVienNhom.ExecuteNonQuery();
IDbCommand commandNhanVienNhom = new SqlCommand();
commandNhanVienNhom.Connection = session.Connection;
commandNhanVienNhom.CommandType = CommandType.StoredProcedure;
commandNhanVienNhom.CommandText = "sp_NHAN_VIEN_NHOM_Them";
session.Transaction.Enlist(commandNhanVienNhom);
if (lstThanhVien != null)
{
for (int i = 0; i < lstThanhVien.Count; i++)
{
commandNhanVienNhom.Parameters.Clear();
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhom", nhom.ID));
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhanVien", lstThanhVien[i]));
commandNhanVienNhom.ExecuteNonQuery();
}
}
transaction.Commit();
return(true);
}
catch (BusinessException ex)
{
transaction.Rollback();
throw ex;
}
}
}
public int Them(NhomModel nhom, List <int> lstThanhVien, int createdBy)
{
using (var session = LOANSessionManager.OpenSession())
using (var transaction = session.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
IDbCommand commandNhom = new SqlCommand();
commandNhom.Connection = session.Connection;
commandNhom.CommandType = CommandType.StoredProcedure;
commandNhom.CommandText = "sp_NHOM_Them";
session.Transaction.Enlist(commandNhom);
commandNhom.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
});
commandNhom.Parameters.Add(new SqlParameter("@MaNhomCha", nhom.MaNhomCha));
commandNhom.Parameters.Add(new SqlParameter("@MaNguoiQL", nhom.MaNguoiQL));
commandNhom.Parameters.Add(new SqlParameter("@TenVietTat", nhom.TenNgan));
commandNhom.Parameters.Add(new SqlParameter("@Ten", nhom.Ten));
commandNhom.Parameters.Add(new SqlParameter("@ChuoiMaCha", nhom.ChuoiMaCha));
commandNhom.Parameters.Add(new SqlParameter("@createdBy", createdBy));
commandNhom.ExecuteNonQuery();
int maNhom = Convert.ToInt32((((SqlParameter)commandNhom.Parameters["@ID"]).Value).ToString());
IDbCommand commandNhanVienNhom = new SqlCommand();
commandNhanVienNhom.Connection = session.Connection;
commandNhanVienNhom.CommandType = CommandType.StoredProcedure;
commandNhanVienNhom.CommandText = "sp_NHAN_VIEN_NHOM_Them";
session.Transaction.Enlist(commandNhanVienNhom);
if (lstThanhVien != null)
{
for (int i = 0; i < lstThanhVien.Count; i++)
{
commandNhanVienNhom.Parameters.Clear();
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhom", maNhom));
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhanVien", lstThanhVien[i]));
commandNhanVienNhom.ExecuteNonQuery();
}
}
transaction.Commit();
return(maNhom);
}
catch (BusinessException ex)
{
transaction.Rollback();
throw ex;
}
}
}
public List <ThongTinSanPhamVayModel> LayThongTinSanPhamByID(int id, DateTime ngayTao)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_SAN_PHAM_VAY_LayDSThongTinByID";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@MaDoiTac", id));
command.Parameters.Add(new SqlParameter("@NgayTao", ngayTao));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null)
{
return(null);
}
List <ThongTinSanPhamVayModel> rs = new List <ThongTinSanPhamVayModel>();
foreach (DataRow item in dt.Rows)
{
ThongTinSanPhamVayModel sp = new ThongTinSanPhamVayModel();
sp.ID = Convert.ToInt32(item["ID"].ToString());
sp.Ten = item["Ten"].ToString();
sp.Ma = item["Ma"].ToString();
try
{
sp.NgayTao = Convert.ToDateTime(item["NgayTao"].ToString());
}
catch (Exception)
{
}
sp.NguoiTao = item["NguoiTao"].ToString();
rs.Add(sp);
}
return(rs);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool checkIsTeamLeadByUserId(int userId)
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_CheckIsTeamlead";
command.Parameters.Add(new SqlParameter("@userId", userId));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt == null || dt.Rows == null || dt.Rows.Count == 0)
{
return(false);
}
var result = Convert.ToBoolean(dt.Rows[0]["isTeamLead"]);
return(result);
//if (dt != null)
//{
// if (dt.Rows.Count > 0)
// {
// List<NhomDropDownModel> result = new List<NhomDropDownModel>();
// foreach (DataRow item in dt.Rows)
// {
// NhomDropDownModel nhom = new NhomDropDownModel();
// nhom.ID = Convert.ToInt32(item["ID"].ToString());
// nhom.Ten = item["Ten"].ToString();
// result.Add(nhom);
// }
// return result;
// }
//}
//return null;
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool Them(int maHS)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_HO_SO_DUYET_XEM_Them";
command.Parameters.Add(new SqlParameter("@ID", maHS));
command.ExecuteNonQuery();
return(true);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <UserPMModel> LayDSNhanVien(int userID, int quyen)
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_Employee_LayDSByRule";
command.Parameters.Add(new SqlParameter("@UserID", userID));
command.Parameters.Add(new SqlParameter("@Rule", quyen));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <UserPMModel> result = new List <UserPMModel>();
foreach (DataRow item in dt.Rows)
{
UserPMModel nv = new UserPMModel();
nv.IDUser = Convert.ToInt32(item["ID"].ToString());
nv.Email = item["Email"].ToString();
nv.Code = item["Code"].ToString();
nv.UserName = item["UserName"].ToString();
nv.FullName = item["FullName"].ToString();
nv.Phone = item["Phone"].ToString();
result.Add(nv);
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public int Them(SanPhamVayModel sanPham)
{
using (var session = LOANSessionManager.OpenSession())
using (var transaction = session.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
IDbCommand commandNhom = new SqlCommand();
commandNhom.Connection = session.Connection;
commandNhom.CommandType = CommandType.StoredProcedure;
commandNhom.CommandText = "sp_SAN_PHAM_VAY_Them";
session.Transaction.Enlist(commandNhom);
commandNhom.Parameters.Add(new SqlParameter("@ID", SqlDbType.BigInt)
{
Direction = ParameterDirection.Output
});
commandNhom.Parameters.Add(new SqlParameter("@MaDoiTac", sanPham.MaDoiTac));
commandNhom.Parameters.Add(new SqlParameter("@Ma", sanPham.Ma));
commandNhom.Parameters.Add(new SqlParameter("@Ten", sanPham.Ten));
if (sanPham.NgayTao == DateTime.MinValue)
{
commandNhom.Parameters.Add(new SqlParameter("@NgayTao", DBNull.Value));
}
else
{
commandNhom.Parameters.Add(new SqlParameter("@NgayTao", sanPham.NgayTao));
}
commandNhom.Parameters.Add(new SqlParameter("@MaNguoiTao", sanPham.MaNguoiTao));
commandNhom.Parameters.Add(new SqlParameter("@Loai", sanPham.Loai));
commandNhom.ExecuteNonQuery();
int maNhom = Convert.ToInt32((((SqlParameter)commandNhom.Parameters["@ID"]).Value).ToString());
transaction.Commit();
return(maNhom);
}
catch (BusinessException ex)
{
transaction.Rollback();
throw ex;
}
}
}
public bool ChangePass(string userID, string pass)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHANVIEN_ChangePass";
command.Parameters.Add(new SqlParameter("@ID", userID));
command.Parameters.Add(new SqlParameter("@Pass", pass));
command.ExecuteNonQuery();
return(true);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool CapNhat(int maNhanVien, List <int> lstIDNhom)
{
using (var session = LOANSessionManager.OpenSession())
using (var transaction = session.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
IDbCommand commandXoaNhanVienNhom = new SqlCommand();
commandXoaNhanVienNhom.Connection = session.Connection;
commandXoaNhanVienNhom.CommandType = CommandType.StoredProcedure;
commandXoaNhanVienNhom.CommandText = "sp_NHAN_VIEN_CF_Xoa";
session.Transaction.Enlist(commandXoaNhanVienNhom);
commandXoaNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhanVien", maNhanVien));
commandXoaNhanVienNhom.ExecuteNonQuery();
IDbCommand commandNhanVienNhom = new SqlCommand();
commandNhanVienNhom.Connection = session.Connection;
commandNhanVienNhom.CommandType = CommandType.StoredProcedure;
commandNhanVienNhom.CommandText = "sp_NHAN_VIEN_CF_Them";
session.Transaction.Enlist(commandNhanVienNhom);
if (lstIDNhom != null)
{
for (int i = 0; i < lstIDNhom.Count; i++)
{
commandNhanVienNhom.Parameters.Clear();
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhom", lstIDNhom[i]));
commandNhanVienNhom.Parameters.Add(new SqlParameter("@MaNhanVien", maNhanVien));
commandNhanVienNhom.ExecuteNonQuery();
}
}
transaction.Commit();
return(true);
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
}
}
public UserPMModel GetUserByID(string userID)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHAN_VIEN_GetUserByID";
command.Parameters.Add(new SqlParameter("@UserID", userID));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
UserPMModel userModel = new UserPMModel();
userModel.IDUser = Convert.ToInt32(dt.Rows[i]["ID"].ToString());
userModel.Email = dt.Rows[i]["Email"].ToString();
userModel.Code = dt.Rows[i]["Code"].ToString();
userModel.UserName = dt.Rows[i]["UserName"].ToString();
userModel.FullName = dt.Rows[i]["FullName"].ToString();
userModel.Phone = dt.Rows[i]["Phone"].ToString();
userModel.Password = dt.Rows[i]["Password"].ToString();
userModel.TypeUser = Convert.ToInt32(dt.Rows[i]["Type"]);
return(userModel);
}
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <ThongTinNhanVienModel> LayDSChiTietThanhVienNhom(int maNhom)
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_Employee_Group_GetEmployeeByGroup";
command.Parameters.Add(new SqlParameter("@groupId", maNhom));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <ThongTinNhanVienModel> result = new List <ThongTinNhanVienModel>();
foreach (DataRow item in dt.Rows)
{
ThongTinNhanVienModel nv = new ThongTinNhanVienModel();
nv.ID = Convert.ToInt32(item["ID"].ToString());
nv.HoTen = item["HoTen"].ToString();
nv.Ma = item["Ma"].ToString();
nv.Email = item["Email"].ToString();
nv.SDT = item["SDT"].ToString();
result.Add(nv);
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public List <TrangThaiHoSoModel> LayDSTrangThai(bool isTeamLead = false)
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_TRANG_THAI_HS_LayDSTrangThai";
command.Parameters.Clear();
command.Parameters.Add(new SqlParameter("@isTeamlead", isTeamLead));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <TrangThaiHoSoModel> result = new List <TrangThaiHoSoModel>();
foreach (DataRow item in dt.Rows)
{
TrangThaiHoSoModel tt = new TrangThaiHoSoModel();
tt.ID = Convert.ToInt32(item["ID"].ToString());
tt.Ten = item["Ten"].ToString();
result.Add(tt);
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public UserPMModel DangNhap(string user, string pass)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_Employee_Login";
command.Parameters.Add(new SqlParameter("@UserName", user));
command.Parameters.Add(new SqlParameter("@Password", pass));
var dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
UserPMModel userModel = new UserPMModel();
userModel.Code = dt.Rows[0]["Code"].ToString();
userModel.IDUser = Convert.ToInt32(dt.Rows[0]["ID"].ToString());
userModel.Email = dt.Rows[0]["Email"].ToString();
userModel.UserName = dt.Rows[0]["UserName"].ToString();
userModel.FullName = dt.Rows[0]["FullName"].ToString();
userModel.OrgId = Convert.ToInt32(dt.Rows[0]["OrgId"].ToString());
userModel.RoleId = string.IsNullOrWhiteSpace(dt.Rows[0]["RoleId"].ToString()) ? 0 : Convert.ToInt32(dt.Rows[0]["RoleId"].ToString());
return(userModel);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
public bool Xoa(int id)
{
using (var session = LOANSessionManager.OpenSession())
using (var transaction = session.BeginTransaction(IsolationLevel.RepeatableRead))
{
try
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_SAN_PHAM_VAY_Xoa";
session.Transaction.Enlist(command);
command.Parameters.Add(new SqlParameter("@ID", id));
command.ExecuteNonQuery();
transaction.Commit();
return(true);
}
catch (BusinessException ex)
{
transaction.Rollback();
throw ex;
}
}
}
public List <PerDiemReportInfoModel> GetPerDiemInfo(DateTime fromDate, DateTime toDate, string status)
{
try
{
using (var session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_PD_PER_DIEM_INFO_ReportPerDiemInfoByCreateDate";
command.Parameters.Add(new SqlParameter("@Status", status));
if (fromDate == DateTime.MinValue)
{
command.Parameters.Add(new SqlParameter("@FromDate", DBNull.Value));
}
else
{
command.Parameters.Add(new SqlParameter("@FromDate", fromDate));
}
if (toDate == DateTime.MinValue)
{
command.Parameters.Add(new SqlParameter("@ToDate", DBNull.Value));
}
else
{
command.Parameters.Add(new SqlParameter("@ToDate", toDate));
}
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <PerDiemReportInfoModel> rs = new List <PerDiemReportInfoModel>();
foreach (DataRow item in dt.Rows)
{
PerDiemReportInfoModel perDiem = new PerDiemReportInfoModel();
perDiem.EmployeeName = item["EmployeeName"].ToString();
perDiem.EmployeeCode = item["EmployeeCode"].ToString();
perDiem.EmployeePosition = item["EmployeePosition"].ToString();
perDiem.EmployeeDepartment = item["EmployeeDepartment"].ToString();
perDiem.CostCenter = item["CostCenter"].ToString();
perDiem.CompanyCode = item["CompanyCode"].ToString();
perDiem.TripPurpose = item["TripPurpose"].ToString();
perDiem.ChargedTo = item["ChargedTo"].ToString();
perDiem.EngagementCode = item["EngagementCode"].ToString();
perDiem.ApproverName = item["ApproverName"].ToString();
if (item["DepartureDay"].ToString() != "")
{
try
{
perDiem.DepartureDay = Convert.ToDateTime(item["DepartureDay"].ToString());
}
catch (Exception)
{
}
}
if (item["ReturnDay"].ToString() != "")
{
try
{
perDiem.ReturnDay = Convert.ToDateTime(item["ReturnDay"].ToString());
}
catch (Exception)
{
}
}
try
{
perDiem.LessCashAdvance = float.Parse(item["LessCashAdvance"].ToString());
}
catch { }
perDiem.Currency = item["Currency"].ToString();
perDiem.TotalPerDiemAllowance = float.Parse(item["TotalPerDiemAllowance"].ToString());
perDiem.Payable = perDiem.TotalPerDiemAllowance - perDiem.LessCashAdvance;
rs.Add(perDiem);
}
return(rs);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
// Danh sách nhóm duyệt(lấy từ bảng cấu hình)
public List <NhomDropDownModel> LayDSDuyetCuaNhanVien(int userId)
{
try
{
using (ISession session = LOANSessionManager.OpenSession())
{
IDbCommand command = new SqlCommand();
command.Connection = session.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "sp_NHOM_LayDSNhomDuyetChonTheoNhanVien_v2";
command.Parameters.Add(new SqlParameter("@UserID", userId));
DataTable dt = new DataTable();
dt.Load(command.ExecuteReader());
if (dt != null)
{
if (dt.Rows.Count > 0)
{
List <NhomDropDownModel> result = new List <NhomDropDownModel>();
List <NhomDropDownModel> resultTemp = new List <NhomDropDownModel>();
foreach (DataRow item in dt.Rows)
{
NhomDropDownModel nhom = new NhomDropDownModel();
nhom.ID = Convert.ToInt32(item["ID"].ToString());
nhom.Ten = item["Ten"].ToString();
nhom.ChuoiMaCha = item["ChuoiMaCha"].ToString();
nhom.TenQL = item["TenQL"].ToString();
// Nếu nhóm con
if (resultTemp.Find(x => nhom.ChuoiMaCha.Contains("." + x.ID.ToString() + ".") || nhom.ChuoiMaCha.EndsWith("." + x.ID.ToString())) != null)
{
continue;
}
else
{
// Nhóm cha
resultTemp.RemoveAll(x => x.ChuoiMaCha.Contains("." + nhom.ID.ToString() + ".") || x.ChuoiMaCha.EndsWith("." + nhom.ID.ToString()));
resultTemp.Add(nhom);
}
if (resultTemp.Count > 0)
{
IDbCommand commandLayDSCon = new SqlCommand();
commandLayDSCon.Connection = session.Connection;
commandLayDSCon.CommandType = CommandType.StoredProcedure;
commandLayDSCon.CommandText = "sp_NHOM_LayCayNhomCon_v2";
for (int i = 0; i < resultTemp.Count; i++)
{
commandLayDSCon.Parameters.Clear();
commandLayDSCon.Parameters.Add(new SqlParameter("@parentGroupId", resultTemp[i].ID));
DataTable dt2 = new DataTable();
dt2.Load(commandLayDSCon.ExecuteReader());
if (dt2 != null && dt2.Rows.Count > 0)
{
List <NhomDropDownModel> lstTemp = new List <NhomDropDownModel>();
foreach (DataRow item2 in dt2.Rows)
{
NhomDropDownModel nhom2 = new NhomDropDownModel();
nhom2.ID = Convert.ToInt32(item2["ID"].ToString());
nhom2.MaNguoiQL = Convert.ToInt32(item2["MaNguoiQL"].ToString());
nhom2.Ten = item2["Ten"].ToString();
nhom2.ChuoiMaCha = item2["ChuoiMaCha"].ToString();
nhom2.TenQL = item2["TenQL"].ToString();
lstTemp.Add(nhom2);
}
result.AddRange(TaoCayDSNhom(lstTemp, resultTemp[i].ChuoiMaCha + "." + resultTemp[i].ID.ToString()));
}
}
}
}
return(result);
}
}
return(null);
}
}
catch (BusinessException ex)
{
throw ex;
}
}
