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); }