public static List<GIAY_TO> getGiayTo(List<NGUOI_LX> listData)
{
List<GIAY_TO> rtn = new List<GIAY_TO>();
if (listData == null || listData.Count <= 0)
{
return rtn;
}
//Tao SQL
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM [dbo].[NguoiLXHS_GiayTo] WHERE MADK IN (");
int i = 1;
foreach (NGUOI_LX user in listData)
{
// IN clause
sb.Append("@MADK" + i.ToString() + ",");
i++;
}
sb.Length--;
sb.Append(")");
//set SqlCommand
SqlConnection connection = Ultils.GetDBConnection();
SqlCommand command = new SqlCommand(sb.ToString(), connection);
//Set param
i = 1;
foreach (NGUOI_LX user in listData)
{
// parameter
command.Parameters.AddWithValue("@MADK" + i.ToString(), user.MADK);
i++;
}
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
GIAY_TO obj = null;
while (reader.Read())
{
obj = new GIAY_TO();
if (!reader.IsDBNull(reader.GetOrdinal("MaDK"))) { obj.MADK = reader.GetString(reader.GetOrdinal("MaDK")); } else { obj.MADK = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("MaGT"))) { obj.MAGT = reader.GetInt32(reader.GetOrdinal("MaGT")).ToString(); } else { obj.MAGT = null; }
if (!reader.IsDBNull(reader.GetOrdinal("SoHoSo"))) { obj.SOHOSO = reader.GetString(reader.GetOrdinal("SoHoSo")); } else { obj.SOHOSO = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("TenGT"))) { obj.TEN_NLX = reader.GetString(reader.GetOrdinal("TenGT")); } else { obj.TEN_NLX = ""; }
if (!reader.IsDBNull(reader.GetOrdinal("TrangThai"))) { obj.TRANGTHAI = reader.GetBoolean(reader.GetOrdinal("TrangThai")).ToString(); } else { obj.TRANGTHAI = null; }
rtn.Add(obj);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
return rtn;
}
public static List <NGUOI_LX> getData(DateTime start, DateTime end, String trangthai, string cmnd, String lstMaDv)
{
List <NGUOI_LX> rtn = new List <NGUOI_LX>();
string queryString = "";
if (trangthai != null && trangthai.Trim().Length > 0)
{
queryString = "SELECT n.* FROM [dbo].[NguoiLX] n LEFT JOIN [dbo].[NguoiLX_HoSo] h ON n.MaDK = h.MaDK WHERE 1 = 1 ";
}
else
{
queryString = "SELECT n.* FROM [dbo].[NguoiLX] n WHERE 1 = 1 ";
}
if (start != null)
{
queryString += " AND n.NgayTao>=@StartDate ";
queryString += " AND n.NgayTao>=@StartDate ";
}
if (end != null)
{
queryString += " AND n.NgayTao<=@EndDate ";
}
if (trangthai != null && trangthai.Trim().Length > 0)
{
queryString += " AND h.TT_XULY=@status ";
}
if (cmnd != null && cmnd.Trim().Length > 0)
{
queryString += " AND n.SoCMT like @cmnd ";
}
if (lstMaDv != null && lstMaDv.Trim().Length > 0)
{
queryString += " AND n.DonViNhanHSo IN (" + lstMaDv + ") ";
}
SqlConnection connection = Ultils.GetDBConnection();
SqlCommand command = new SqlCommand(queryString, connection);
if (start != null)
{
command.Parameters.AddWithValue("@StartDate", start);
}
if (end != null)
{
end = end.AddDays(1);
command.Parameters.AddWithValue("@EndDate", end);
}
if (trangthai != null && trangthai.Trim().Length > 0)
{
command.Parameters.AddWithValue("@status", trangthai);
}
if (cmnd != null && cmnd.Trim().Length > 0)
{
command.Parameters.AddWithValue("@cmnd", "%" + cmnd.Trim() + "%");
}
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
NGUOI_LX obj = null;
int i = 1;
while (reader.Read())
{
obj = new NGUOI_LX();
obj.STT = i++;
if (!reader.IsDBNull(reader.GetOrdinal("MaDK")))
{
obj.MADK = reader.GetString(reader.GetOrdinal("MaDK"));
}
else
{
obj.MADK = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("DonViNhanHSo")))
{
obj.DV_NHAN_HS = reader.GetString(reader.GetOrdinal("DonViNhanHSo"));
}
else
{
obj.DV_NHAN_HS = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("HoDemNLX")))
{
obj.HO_DEM_NLX = reader.GetString(reader.GetOrdinal("HoDemNLX"));
}
else
{
obj.HO_DEM_NLX = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("TenNLX")))
{
obj.TEN_NLX = reader.GetString(reader.GetOrdinal("TenNLX"));
}
else
{
obj.TEN_NLX = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("HoVaTen")))
{
obj.HO_VA_TEN = reader.GetString(reader.GetOrdinal("HoVaTen"));
}
else
{
obj.HO_VA_TEN = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("MaQuocTich")))
{
obj.MA_QUOC_TICH = reader.GetString(reader.GetOrdinal("MaQuocTich"));
}
else
{
obj.MA_QUOC_TICH = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NgaySinh")))
{
obj.NGAY_SINH = DateTime.ParseExact(reader.GetString(reader.GetOrdinal("NgaySinh")), "yyyyMMdd", CultureInfo.InvariantCulture).ToString(Ultils.FORMAT_DATE);
}
else
{
obj.NGAY_SINH = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiTT")))
{
obj.NOI_TT = reader.GetString(reader.GetOrdinal("NoiTT"));
}
else
{
obj.NOI_TT = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiTT_MaDVHC")))
{
obj.NOI_TT_MA_DVHC = reader.GetString(reader.GetOrdinal("NoiTT_MaDVHC"));
}
else
{
obj.NOI_TT_MA_DVHC = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiTT_MaDVQL")))
{
obj.NOI_TT_MA_DVQL = reader.GetString(reader.GetOrdinal("NoiTT_MaDVQL"));
}
else
{
obj.NOI_TT_MA_DVQL = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiCT")))
{
obj.NOI_CT = reader.GetString(reader.GetOrdinal("NoiCT"));
}
else
{
obj.NOI_CT = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiCT_MaDVHC")))
{
obj.NOI_CT_MA_DVHC = reader.GetString(reader.GetOrdinal("NoiCT_MaDVHC"));
}
else
{
obj.NOI_CT_MA_DVHC = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiCT_MaDVQL")))
{
obj.NOI_CT_MA_DVQL = reader.GetString(reader.GetOrdinal("NoiCT_MaDVQL"));
}
else
{
obj.NOI_CT_MA_DVQL = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("SoCMT")))
{
obj.SO_CMT = reader.GetString(reader.GetOrdinal("SoCMT"));
}
else
{
obj.SO_CMT = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NgayCapCMT")))
{
obj.NGAY_CAP_CMT = reader.GetDateTime(reader.GetOrdinal("NgayCapCMT")).ToString(Ultils.FORMAT_DATETIME);
}
else
{
obj.NGAY_CAP_CMT = null;
}
if (!reader.IsDBNull(reader.GetOrdinal("NoiCapCMT")))
{
obj.NOI_CAP_CMT = reader.GetString(reader.GetOrdinal("NoiCapCMT"));
}
else
{
obj.NOI_CAP_CMT = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("GhiChu")))
{
obj.GHI_CHU = reader.GetString(reader.GetOrdinal("GhiChu"));
}
else
{
obj.GHI_CHU = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("TrangThai")))
{
obj.TRANG_THAI = Convert.ToInt32(reader.GetBoolean(reader.GetOrdinal("TrangThai")));
}
else
{
obj.TRANG_THAI = null;
}
if (!reader.IsDBNull(reader.GetOrdinal("NguoiTao")))
{
obj.NGUOI_TAO = reader.GetString(reader.GetOrdinal("NguoiTao"));
}
else
{
obj.NGUOI_TAO = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NguoiSua")))
{
obj.NGUOI_SUA = reader.GetString(reader.GetOrdinal("NguoiSua"));
}
else
{
obj.NGUOI_SUA = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("NgayTao")))
{
obj.NGAY_TAO = reader.GetDateTime(reader.GetOrdinal("NgayTao")).ToString(Ultils.FORMAT_DATETIME);
}
else
{
obj.NGAY_TAO = null;
}
if (!reader.IsDBNull(reader.GetOrdinal("NgaySua")))
{
obj.NGAY_SUA = reader.GetDateTime(reader.GetOrdinal("NgaySua")).ToString(Ultils.FORMAT_DATETIME);
}
else
{
obj.NGAY_SUA = null;
}
if (!reader.IsDBNull(reader.GetOrdinal("GioiTinh")))
{
obj.GIOI_TINH = reader.GetString(reader.GetOrdinal("GioiTinh"));
}
else
{
obj.GIOI_TINH = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("HoVaTenIn")))
{
obj.HO_VA_TEN_IN = reader.GetString(reader.GetOrdinal("HoVaTenIn"));
}
else
{
obj.HO_VA_TEN_IN = "";
}
if (!reader.IsDBNull(reader.GetOrdinal("SO_CMND_CU")))
{
obj.SO_CMND_CU = reader.GetString(reader.GetOrdinal("SO_CMND_CU"));
}
else
{
obj.SO_CMND_CU = "";
}
rtn.Add(obj);
}
}
finally
{
// Always call Close when done reading.
reader.Close();
}
Console.WriteLine("getData: " + rtn.Count);
return(rtn);
}
public static int insertData(GIAY_TO obj)
{
int rtn = 0;
SqlConnection connection = Ultils.GetDBConnection();
try
{
string queryString = "INSERT INTO [dbo].[NguoiLXHS_GiayTo] (MaDK, MaGT, SoHoSo, TenGT, TrangThai) " +
"VALUES (@MADK, @MAGT, @SOHOSO, @TEN_NLX, @TRANGTHAI)";
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@MaDK", obj.MADK);
if (obj.MAGT != null)
{
command.Parameters.AddWithValue("@MaGT", Int32.Parse(obj.MAGT));
}
else
{
command.Parameters.AddWithValue("@MaGT", DBNull.Value);
}
if (obj.SOHOSO == null || obj.SOHOSO.Length <= 0)
{
command.Parameters.AddWithValue("@SoHoSo", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@SoHoSo", obj.SOHOSO);
}
if (obj.TEN_NLX == null || obj.TEN_NLX.Length <= 0)
{
command.Parameters.AddWithValue("@TEN_NLX", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@TEN_NLX", obj.TEN_NLX);
}
if (obj.TRANGTHAI == null)
{
command.Parameters.AddWithValue("@TrangThai", DBNull.Value);
}
else
{
command.Parameters.AddWithValue("@TrangThai", obj.TRANGTHAI);
}
rtn = command.ExecuteNonQuery();
}
catch (Exception ex)
{
rtn = -1;
throw ex;
}
finally
{
connection.Close();
}
return rtn;
}