public static List <KhieuNaiUserDTO> GetListKhieuNaiUser(GetKhieuNaiUserDTO model)
{
try
{
var conn = Config.getConnectionKhieuNai();
// Open Connection
conn.Open();
string sql = "SELECT * FROM DT_KHIEUNAI_USER WHERE IDKHIEUNAI = @_IDKHIEUNAI";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@_IDKHIEUNAI", model.IdKhieuNai);
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiUserDTO> result = new List <KhieuNaiUserDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiUserDTO>(reader);
}
conn.Close();
conn.Dispose();
return(result);
}
catch (SqlException ex)
{
string err = string.Format("[ERR_KhieuNai] loi get du lieu [DT_KHIEUNAI_USER] - IDKhieuNai={0},ex={1}", model.IdKhieuNai, ex.Message);
LogHelper.LogInfo(err, "KhieuNai");
return(new List <KhieuNaiUserDTO>());
}
}
public static List <KhieuNaiDTO> GetListKhieuNai(ThoiGianDTO model)
{
var conn = Config.getConnectionKhieuNai();
//Create Command object
//SqlCommand nonqueryCommand = conn.CreateCommand();
try
{
// Open Connection
conn.Open();
string sql = "";
if (string.IsNullOrEmpty(model.CHK))
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGUOINHAP=@_NGUOINHAP AND NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate )";
}
else
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGUOINHAP=@_NGUOINHAP AND NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate AND CHK=@_CHK)";
}
if (model.UserName == "emscntt9192")
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate AND CHK=@_CHK)";
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (model.UserName != "emscntt9192")
{
cmd.Parameters.AddWithValue("@_NGUOINHAP", model.NguoiNhap);
}
if (!string.IsNullOrEmpty(model.CHK))
{
cmd.Parameters.AddWithValue("@_CHK", model.CHK);
}
cmd.Parameters.AddWithValue("@_FromDate", model.FromDate);
cmd.Parameters.AddWithValue("@_ToDate", model.ToDate);
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiDTO> result = new List <KhieuNaiDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiDTO>(reader);
}
return(result);
}
catch (SqlException ex)
{
string err = string.Format("[ERR_KhieuNai] loi get du lieu [DT_KHIEUNAI] - NguoiNhap={0},FromDate={1},ToDate={2},ex={3}", model.NguoiNhap, model.FromDate, model.ToDate, ex.Message);
LogHelper.LogInfo(err, "KhieuNai");
return(new List <KhieuNaiDTO>());
}
finally
{
// Close Connection
conn.Close();
conn.Dispose();
}
}
public static List <KhieuNaiTienTrinhDTO> GetListKhieuNaiTienTrinh(GetKhieuNaiTienTrinhDTO model)
{
var conn = Config.getConnectionKhieuNai();
//Create Command object
//SqlCommand nonqueryCommand = conn.CreateCommand();
try
{
// Open Connection
conn.Open();
string sql = "";
if (model.IDKhieuNai != "")
{
sql = "SELECT * FROM DT_KHIEUNAI_TIENTRINH WHERE (IDKHIEUNAI=@_IDKHIEUNAI)";
}
else
{
sql = "SELECT * FROM DT_KHIEUNAI_TIENTRINH WHERE (NGAYNHAP >= @_FromDate AND NGAYNHAP <= @_ToDate AND (NGUOINHAP = @_USERNAME || NGUOIXLYTIEP = @_USERNAME))";
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (model.IDKhieuNai != "")
{
cmd.Parameters.AddWithValue("@_IDKHIEUNAI", model.IDKhieuNai);
}
else
{
cmd.Parameters.AddWithValue("@_USERNAME", model.UserName);
cmd.Parameters.AddWithValue("@_FromDate", model.FromDate);
cmd.Parameters.AddWithValue("@_ToDate", model.ToDate);
}
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiTienTrinhDTO> result = new List <KhieuNaiTienTrinhDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiTienTrinhDTO>(reader);
}
return(result);
}
catch (SqlException ex)
{
string err = string.Format("[ERR_KhieuNai] loi get du lieu [DT_KHIEUNAI_TIENTRINH] - IDKhieuNai={0},FromDate={1},ToDate={2},ex={3}", model.IDKhieuNai, model.FromDate, model.ToDate, ex.Message);
LogHelper.LogInfo(err, "KhieuNai");
return(new List <KhieuNaiTienTrinhDTO>());
}
finally
{
// Close Connection
conn.Close();
conn.Dispose();
}
}
public static List <KhieuNaiDTO> GetListKhieuNai(ThoiGianDTO model)
{
var conn = Config.getConnectionKhieuNai();
// Open Connection
conn.Open();
string sql = "";
if (string.IsNullOrEmpty(model.NguoiNhap))
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate AND CHK=@_CHK)";
}
else
{
if (string.IsNullOrEmpty(model.CHK))
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGUOINHAP=@_NGUOINHAP AND NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate)";
}
else
{
sql = "SELECT * FROM DT_KHIEUNAI WHERE (NGUOINHAP=@_NGUOINHAP AND NGAYLAP >= @_FromDate AND NGAYLAP <= @_ToDate AND CHK= @_CHK)";
}
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (!string.IsNullOrEmpty(model.NguoiNhap))
{
cmd.Parameters.AddWithValue("@_NGUOINHAP", model.NguoiNhap);
}
if (!string.IsNullOrEmpty(model.CHK))
{
cmd.Parameters.AddWithValue("@_CHK", model.CHK);
}
cmd.Parameters.AddWithValue("@_FromDate", model.FromDate);
cmd.Parameters.AddWithValue("@_ToDate", model.ToDate);
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiDTO> result = new List <KhieuNaiDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiDTO>(reader);
}
// Close Connection
conn.Close();
conn.Dispose();
return(result);
}
public static List <KhieuNaiUserDTO> GetListKhieuNaiUser(GetKhieuNaiUserDTO model)
{
var conn = Config.getConnectionKhieuNai();
// Open Connection
string sql = "";
conn.Open();
if (!string.IsNullOrEmpty(model.IdKhieuNai) && !string.IsNullOrEmpty(model.UserName))
{
sql = "SELECT * FROM DT_KHIEUNAI_USER WHERE IDKHIEUNAI = @_IDKHIEUNAI AND USERNAME = @_UserName";
}
if (string.IsNullOrEmpty(model.IdKhieuNai) && !string.IsNullOrEmpty(model.UserName))
{
sql = "SELECT * FROM DT_KHIEUNAI_USER WHERE USERNAME = @_UserName ";
}
if (!string.IsNullOrEmpty(model.IdKhieuNai) && string.IsNullOrEmpty(model.UserName))
{
sql = "SELECT * FROM DT_KHIEUNAI_USER WHERE IDKHIEUNAI = @_IDKHIEUNAI";
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (!string.IsNullOrEmpty(model.IdKhieuNai))
{
cmd.Parameters.AddWithValue("@_IDKHIEUNAI", model.IdKhieuNai);
}
if (!string.IsNullOrEmpty(model.UserName))
{
cmd.Parameters.AddWithValue("@_UserName", model.UserName);
}
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiUserDTO> result = new List <KhieuNaiUserDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiUserDTO>(reader);
}
conn.Close();
conn.Dispose();
return(result);
}
public static List <KhieuNaiTienTrinhDTO> GetListKhieuNaiTienTrinh(GetKhieuNaiTienTrinhDTO model)
{
var conn = Config.getConnectionKhieuNai();
conn.Open();
string sql = "";
if (model.IDKhieuNai != "")
{
sql = "SELECT * FROM DT_KHIEUNAI_TIENTRINH WHERE (IDKHIEUNAI=@_IDKHIEUNAI)";
}
else
{
sql = "SELECT * FROM DT_KHIEUNAI_TIENTRINH WHERE (NGAYNHAP >= @_FromDate AND NGAYNHAP <= @_ToDate AND (NGUOINHAP = @_USERNAME OR NGUOIXLYTIEP = @_USERNAME))";
}
SqlCommand cmd = new SqlCommand(sql, conn);
if (model.IDKhieuNai != "")
{
cmd.Parameters.AddWithValue("@_IDKHIEUNAI", model.IDKhieuNai);
}
else
{
cmd.Parameters.AddWithValue("@_USERNAME", model.UserName);
cmd.Parameters.AddWithValue("@_FromDate", model.FromDate);
cmd.Parameters.AddWithValue("@_ToDate", model.ToDate);
}
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <KhieuNaiTienTrinhDTO> result = new List <KhieuNaiTienTrinhDTO>();
if (reader != null)
{
result = DbExtensions.ToList <KhieuNaiTienTrinhDTO>(reader);
}
conn.Close();
conn.Dispose();
return(result);
}
public static List <NguoiThuLyTinhDTO> GetListNguoiThuLyTinh(string matinh, string idkhieunai)
{
var conn = Config.getConnectionKhieuNai();
// Open Connection
string sql = "SELECT USERNAME, TENNV FROM SYS_USER WHERE (STTTINH=" + matinh + " AND DAUMOI='1') ";
sql += "union select distinct S.USERNAME, S.TENNV from SYS_USER S with(nolock) ";
sql += " inner join DT_KHIEUNAI_USER D on ( S.USERNAME = D.USERNAME and S.STTTINH =" + matinh + ") ";
sql += " where IDKHIEUNAI = '" + idkhieunai + "'";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
IDataReader reader = (IDataReader)cmd.ExecuteReader();
List <NguoiThuLyTinhDTO> result = new List <NguoiThuLyTinhDTO>();
if (reader != null)
{
result = DbExtensions.ToList <NguoiThuLyTinhDTO>(reader);
}
conn.Close();
conn.Dispose();
return(result);
}
