public List<tbl_email_awaiting> GetEmailsByStatus(int status, DateTime filterDate)
{
using (SqlConnection DBCon = new SqlConnection(connectionString))
{
List<tbl_email_awaiting> email_awaitingList = new List<tbl_email_awaiting>();
SqlCommand CmdSelect = new SqlCommand("get_emails_by_status", DBCon);
CmdSelect.CommandType = CommandType.StoredProcedure;
CmdSelect.Connection = DBCon;
CmdSelect.Parameters.AddWithValue("@filterDate", SqlDbType.DateTime).Value = filterDate;
CmdSelect.Parameters.AddWithValue("@status", SqlDbType.Int).Value = status;
DBCon.Open();
SqlDataReader rd = CmdSelect.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
tbl_email_awaiting email_awaiting = new tbl_email_awaiting();
email_awaiting.guid = (!DBNull.Value.Equals(rd["guid"])) ? rd["guid"].ToString() : string.Empty;
email_awaiting.int_status = Convert.ToInt32((!DBNull.Value.Equals(rd["int_status"])) ? rd["int_status"].ToString() : "0");
email_awaiting.str_error = ((!DBNull.Value.Equals(rd["str_error"])) ? rd["str_error"] : string.Empty).ToString();
email_awaiting.dt_due_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_due_date"])) ? rd["dt_due_date"] : DateTime.Now);
email_awaiting.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.MinValue);
email_awaiting.dt_send_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_send_date"])) ? rd["dt_send_date"] : DateTime.MinValue);
email_awaiting.dt_expire_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_expire_date"])) ? rd["dt_expire_date"] : DateTime.MinValue);
email_awaiting.int_failed_count = Convert.ToInt32((!DBNull.Value.Equals(rd["int_failed_count"])) ? rd["int_failed_count"].ToString() : "0");
email_awaiting.email_detail_guid = (!DBNull.Value.Equals(rd["email_detail_guid"])) ? rd["email_detail_guid"].ToString() : string.Empty;
email_awaitingList.Add(email_awaiting);
}
rd.NextResult();
while (rd.Read())
{
tbl_email_awaiting_details tbl_email_awaiting_detail = new tbl_email_awaiting_details();
tbl_email_awaiting_detail.guid = (!DBNull.Value.Equals(rd["guid"])) ? rd["guid"].ToString() : string.Empty;
tbl_email_awaiting_detail.str_subject = (!DBNull.Value.Equals(rd["str_subject"])) ? rd["str_subject"].ToString() : "";
tbl_email_awaiting_detail.str_body = ((!DBNull.Value.Equals(rd["str_body"])) ? rd["str_body"] : string.Empty).ToString();
tbl_email_awaiting_detail.str_from_address = ((!DBNull.Value.Equals(rd["str_from_address"])) ? rd["str_from_address"] : string.Empty).ToString();
tbl_email_awaiting_detail.str_from_name = ((!DBNull.Value.Equals(rd["str_from_name"])) ? rd["str_from_address"] : string.Empty).ToString();
tbl_email_awaiting_detail.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.Now);
tbl_email_awaiting_detail.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.MinValue);
var email_awaitings = email_awaitingList.Where(a => a.email_detail_guid == tbl_email_awaiting_detail.guid);
foreach (var email_awaiting in email_awaitings)
{
email_awaiting.tbl_email_awaiting_details = tbl_email_awaiting_detail;
}
}
rd.NextResult();
while (rd.Read())
{
tbl_email_to_address tbl_email_to_addr = new tbl_email_to_address();
tbl_email_to_addr.guid = (!DBNull.Value.Equals(rd["guid"]) ? rd["guid"].ToString() : string.Empty);
tbl_email_to_addr.str_email_address = (!DBNull.Value.Equals(rd["str_email_address"])) ? rd["str_email_address"].ToString() : "";
tbl_email_to_addr.str_email_name = ((!DBNull.Value.Equals(rd["str_email_name"])) ? rd["str_email_name"] : string.Empty).ToString();
tbl_email_to_addr.int_type = Convert.ToInt32((!DBNull.Value.Equals(rd["int_type"])) ? rd["int_type"] : "0");
tbl_email_to_addr.email_guid = ((!DBNull.Value.Equals(rd["email_guid"])) ? rd["email_guid"] : string.Empty).ToString();
var email_awaiting = email_awaitingList.SingleOrDefault(a => a.guid == tbl_email_to_addr.email_guid);
if (email_awaiting.tbl_email_to_address == null)
email_awaiting.tbl_email_to_address = new List<tbl_email_to_address>();
email_awaiting.tbl_email_to_address.Add(tbl_email_to_addr);
}
rd.NextResult();
while (rd.Read())
{
tbl_email_attachment tbl_email_attach = new tbl_email_attachment();
tbl_email_attach.guid = (!DBNull.Value.Equals(rd["guid"]) ? rd["guid"].ToString() : string.Empty);
tbl_email_attach.str_file_path = (!DBNull.Value.Equals(rd["str_file_path"])) ? rd["str_file_path"].ToString() : "";
tbl_email_attach.bit_attached = Convert.ToBoolean((!DBNull.Value.Equals(rd["bit_attached"])) ? rd["bit_attached"] : "false");
tbl_email_attach.str_file_path = (!DBNull.Value.Equals(rd["str_file_path"])) ? rd["str_file_path"].ToString() : "";
tbl_email_attach.str_error = ((!DBNull.Value.Equals(rd["str_error"])) ? rd["str_error"] : "").ToString();
tbl_email_attach.email_detail_guid = ((!DBNull.Value.Equals(rd["email_detail_guid"])) ? rd["email_detail_guid"] : "").ToString();
var email_awaiting = email_awaitingList.SingleOrDefault(p => p.tbl_email_awaiting_details.guid == tbl_email_attach.email_detail_guid);
if (email_awaiting.tbl_email_awaiting_details.tbl_email_attachment == null)
email_awaiting.tbl_email_awaiting_details.tbl_email_attachment = new List<tbl_email_attachment>();
email_awaiting.tbl_email_awaiting_details.tbl_email_attachment.Add(tbl_email_attach);
}
}
return email_awaitingList;
}
}
public List <tbl_email_awaiting> GetFailedBulk(int reTryTimes)
{
using (SqlConnection DBCon = new SqlConnection(connectionString))
{
List <tbl_email_awaiting> email_awaitingList = new List <tbl_email_awaiting>();
SqlCommand CmdSelect = new SqlCommand("get_faild_email_bulk", DBCon);
CmdSelect.CommandType = CommandType.StoredProcedure;
CmdSelect.Connection = DBCon;
CmdSelect.Parameters.AddWithValue("@re_try_times", SqlDbType.Int).Value = reTryTimes;
DBCon.Open();
SqlDataReader rd = CmdSelect.ExecuteReader();
if (rd.HasRows)
{
while (rd.Read())
{
tbl_email_awaiting email_awaiting = new tbl_email_awaiting();
email_awaiting.guid = (!DBNull.Value.Equals(rd["guid"])) ? rd["guid"].ToString() : string.Empty;
email_awaiting.int_status = Convert.ToInt32((!DBNull.Value.Equals(rd["int_status"])) ? rd["int_status"].ToString() : "0");
email_awaiting.str_error = ((!DBNull.Value.Equals(rd["str_error"])) ? rd["str_error"] : string.Empty).ToString();
email_awaiting.dt_due_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_due_date"])) ? rd["dt_due_date"] : DateTime.Now);
email_awaiting.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.MinValue);
email_awaiting.dt_send_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_send_date"])) ? rd["dt_send_date"] : DateTime.MinValue);
email_awaiting.dt_expire_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_expire_date"])) ? rd["dt_expire_date"] : DateTime.MinValue);
email_awaiting.int_failed_count = Convert.ToInt32((!DBNull.Value.Equals(rd["int_failed_count"])) ? rd["int_failed_count"].ToString() : "0");
email_awaiting.email_detail_guid = (!DBNull.Value.Equals(rd["email_detail_guid"])) ? rd["email_detail_guid"].ToString() : string.Empty;
email_awaitingList.Add(email_awaiting);
}
rd.NextResult();
while (rd.Read())
{
tbl_email_awaiting_details tbl_email_awaiting_detail = new tbl_email_awaiting_details();
tbl_email_awaiting_detail.guid = (!DBNull.Value.Equals(rd["guid"])) ? rd["guid"].ToString() : string.Empty;
tbl_email_awaiting_detail.str_subject = (!DBNull.Value.Equals(rd["str_subject"])) ? rd["str_subject"].ToString() : "";
tbl_email_awaiting_detail.str_body = ((!DBNull.Value.Equals(rd["str_body"])) ? rd["str_body"] : string.Empty).ToString();
tbl_email_awaiting_detail.str_from_address = ((!DBNull.Value.Equals(rd["str_from_address"])) ? rd["str_from_address"] : string.Empty).ToString();
tbl_email_awaiting_detail.str_from_name = ((!DBNull.Value.Equals(rd["str_from_name"])) ? rd["str_from_address"] : string.Empty).ToString();
tbl_email_awaiting_detail.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.Now);
tbl_email_awaiting_detail.dt_inserted_date = Convert.ToDateTime((!DBNull.Value.Equals(rd["dt_inserted_date"])) ? rd["dt_inserted_date"] : DateTime.MinValue);
var email_awaitings = email_awaitingList.Where(a => a.email_detail_guid == tbl_email_awaiting_detail.guid);
foreach (var email_awaiting in email_awaitings)
{
email_awaiting.tbl_email_awaiting_details = tbl_email_awaiting_detail;
}
}
rd.NextResult();
while (rd.Read())
{
tbl_email_to_address tbl_email_to_addr = new tbl_email_to_address();
tbl_email_to_addr.guid = (!DBNull.Value.Equals(rd["guid"]) ? rd["guid"].ToString() : string.Empty);
tbl_email_to_addr.str_email_address = (!DBNull.Value.Equals(rd["str_email_address"])) ? rd["str_email_address"].ToString() : "";
tbl_email_to_addr.str_email_name = ((!DBNull.Value.Equals(rd["str_email_name"])) ? rd["str_email_name"] : string.Empty).ToString();
tbl_email_to_addr.int_type = Convert.ToInt32((!DBNull.Value.Equals(rd["int_type"])) ? rd["int_type"] : "0");
tbl_email_to_addr.email_guid = ((!DBNull.Value.Equals(rd["email_guid"])) ? rd["email_guid"] : string.Empty).ToString();
var email_awaiting = email_awaitingList.SingleOrDefault(a => a.guid == tbl_email_to_addr.email_guid);
if (email_awaiting.tbl_email_to_address == null)
{
email_awaiting.tbl_email_to_address = new List <tbl_email_to_address>();
}
email_awaiting.tbl_email_to_address.Add(tbl_email_to_addr);
}
rd.NextResult();
while (rd.Read())
{
tbl_email_attachment tbl_email_attach = new tbl_email_attachment();
tbl_email_attach.guid = (!DBNull.Value.Equals(rd["guid"]) ? rd["guid"].ToString() : string.Empty);
tbl_email_attach.str_file_path = (!DBNull.Value.Equals(rd["str_file_path"])) ? rd["str_file_path"].ToString() : "";
tbl_email_attach.bit_attached = Convert.ToBoolean((!DBNull.Value.Equals(rd["bit_attached"])) ? rd["bit_attached"] : "false");
tbl_email_attach.str_file_path = (!DBNull.Value.Equals(rd["str_file_path"])) ? rd["str_file_path"].ToString() : "";
tbl_email_attach.str_error = ((!DBNull.Value.Equals(rd["str_error"])) ? rd["str_error"] : "").ToString();
tbl_email_attach.email_detail_guid = ((!DBNull.Value.Equals(rd["email_detail_guid"])) ? rd["email_detail_guid"] : "").ToString();
var email_awaiting = email_awaitingList.SingleOrDefault(p => p.tbl_email_awaiting_details.guid == tbl_email_attach.email_detail_guid);
if (email_awaiting.tbl_email_awaiting_details.tbl_email_attachment == null)
{
email_awaiting.tbl_email_awaiting_details.tbl_email_attachment = new List <tbl_email_attachment>();
}
email_awaiting.tbl_email_awaiting_details.tbl_email_attachment.Add(tbl_email_attach);
}
}
return(email_awaitingList);
}
}
