public EmployeeRecord GetRecordByID(SqlConnection connection, int id)
{
EmployeeRecord record = new EmployeeRecord();
SqlCommand sqlSelect = BuildSelectByIdQuery(connection, id);
bool connectionCloseOnExit = false;
if (connection.State != System.Data.ConnectionState.Open)
{
connectionCloseOnExit = true;
connection.Open();
}
SqlDataReader sqlReader = sqlSelect.ExecuteReader();
if (sqlReader.Read())
{
try { record.ID = Convert.ToInt32(sqlReader["id"]); }
catch { }
if (sqlReader["last_name"] != null && sqlReader["last_name"] != DBNull.Value)
record.LastName = sqlReader["last_name"].ToString().Trim();
if (sqlReader["first_name"] != null && sqlReader["first_name"] != DBNull.Value)
record.FirstName = sqlReader["first_name"].ToString().Trim();
if (sqlReader["middle_name"] != null && sqlReader["middle_name"] != DBNull.Value)
record.MiddleName = sqlReader["middle_name"].ToString().Trim();
if (sqlReader["dob"] != null && sqlReader["dob"] != DBNull.Value)
{
try { record.dtDOB = Convert.ToDateTime(sqlReader["dob"]); }
catch { record.DOB = null; }
}
if (sqlReader["employed"] != null && sqlReader["employed"] != DBNull.Value)
{
try { record.dtEmployed = Convert.ToDateTime(sqlReader["employed"]); }
catch { record.Employed = null; }
}
if (sqlReader["phone_work"] != null && sqlReader["phone_work"] != DBNull.Value)
record.PhoneWork = sqlReader["phone_work"].ToString().Trim();
if (sqlReader["phone_mobile"] != null && sqlReader["phone_mobile"] != DBNull.Value)
record.PhoneMobile = sqlReader["phone_mobile"].ToString().Trim();
if (sqlReader["email"] != null && sqlReader["email"] != DBNull.Value)
record.Email = sqlReader["email"].ToString().Trim();
if (sqlReader["department"] != null && sqlReader["department"] != DBNull.Value)
record.Department = sqlReader["department"].ToString().Trim();
if (sqlReader["job_title"] != null && sqlReader["job_title"] != DBNull.Value)
record.JobTitle = sqlReader["job_title"].ToString().Trim();
if (sqlReader["city"] != null && sqlReader["city"] != DBNull.Value)
record.City = sqlReader["city"].ToString().Trim();
if (sqlReader["login_name"] != null && sqlReader["login_name"] != DBNull.Value)
record.Login = sqlReader["login_name"].ToString().Trim();
if (sqlReader["org_city"] != null && sqlReader["org_city"] != DBNull.Value)
record.OrgCity = sqlReader["org_city"].ToString().Trim();
if (sqlReader["org_street"] != null && sqlReader["org_street"] != DBNull.Value)
record.OrgStreet = sqlReader["org_street"].ToString().Trim();
if (sqlReader["org_house_no"] != null && sqlReader["org_house_no"] != DBNull.Value)
record.OrgHouseNo = sqlReader["org_house_no"].ToString().Trim();
if (sqlReader["post_box"] != null && sqlReader["post_box"] != DBNull.Value)
record.OrgPostBox = sqlReader["post_box"].ToString().Trim();
_Records.Add(record);
}
sqlReader.Close();
if (connectionCloseOnExit == true)
connection.Close();
return record;
}
public void ReadFromDB(SqlConnection connection)
{
SqlCommand sqlSelect = BuildSelectQuery(connection, false);
SqlCommand sqlSelectCnt = BuildSelectQuery(connection, true);
bool connectionCloseOnExit = false;
_Records.Clear();
_bEmpty = true;
_nCountTotal = -1;
if (connection.State != System.Data.ConnectionState.Open)
{
connectionCloseOnExit = true;
connection.Open();
}
SqlDataReader sqlReaderCnt = sqlSelectCnt.ExecuteReader();
if (sqlReaderCnt.Read())
{
try { _nCountTotal = Convert.ToInt32(sqlReaderCnt["cnt"]); }
catch { _nCountTotal = -1; }
}
sqlReaderCnt.Close();
SqlDataReader sqlReader = sqlSelect.ExecuteReader();
_bEmpty = false;
while (sqlReader.Read())
{
if (sqlReader["id"] == null || sqlReader["id"] == DBNull.Value)
continue;
EmployeeRecord record = new EmployeeRecord();
try { record.ID = Convert.ToInt32(sqlReader["id"]); }
catch { continue; }
if (sqlReader["last_name"] != null && sqlReader["last_name"] != DBNull.Value)
record.LastName = sqlReader["last_name"].ToString().Trim();
if (sqlReader["first_name"] != null && sqlReader["first_name"] != DBNull.Value)
record.FirstName = sqlReader["first_name"].ToString().Trim();
if (sqlReader["middle_name"] != null && sqlReader["middle_name"] != DBNull.Value)
record.MiddleName = sqlReader["middle_name"].ToString().Trim();
if (sqlReader["dob"] != null && sqlReader["dob"] != DBNull.Value)
{
try { record.dtDOB = Convert.ToDateTime(sqlReader["dob"]); }
catch { record.DOB = null; }
}
if (sqlReader["employed"] != null && sqlReader["employed"] != DBNull.Value)
{
try { record.dtEmployed = Convert.ToDateTime(sqlReader["employed"]); }
catch { record.Employed = null; }
}
if (sqlReader["phone_work"] != null && sqlReader["phone_work"] != DBNull.Value)
record.PhoneWork = sqlReader["phone_work"].ToString().Trim();
if (sqlReader["phone_mobile"] != null && sqlReader["phone_mobile"] != DBNull.Value)
record.PhoneMobile = sqlReader["phone_mobile"].ToString().Trim();
if (sqlReader["email"] != null && sqlReader["email"] != DBNull.Value)
record.Email = sqlReader["email"].ToString().Trim();
if (sqlReader["department"] != null && sqlReader["department"] != DBNull.Value)
record.Department = sqlReader["department"].ToString().Trim();
if (sqlReader["job_title"] != null && sqlReader["job_title"] != DBNull.Value)
record.JobTitle = sqlReader["job_title"].ToString().Trim();
if (sqlReader["city"] != null && sqlReader["city"] != DBNull.Value)
record.City = sqlReader["city"].ToString().Trim();
_Records.Add(record);
}
sqlReader.Close();
if (CountTotal < 0) _nCountTotal = _Records.Count;
if (connectionCloseOnExit == true)
connection.Close();
}
string ShowRecord(EmployeeRecord record)
{
StringBuilder sbRecord = new StringBuilder();
string sEmail = "";
bool bTodayIsBirthday = false;
bool bEOLNeeded = false;
string showDetailsCmd = (_bEnableDetails) ?
(String.Format("javascript:__doPostBack('ShowRecordDetails', '{0}');", record.ID)) :
("");
if (record.DOB != null && record.dtDOB.Day == DateTime.Today.Day &&
record.dtDOB.Month == DateTime.Today.Month)
{
bTodayIsBirthday = true;
}
if (record.Email.Trim().Length > 0)
sEmail = string.Format("<a href=\"mailto:{0}\">{0}</a>", record.Email);
sbRecord.AppendLine("<div class=\"styleMrEListCard\">");
sbRecord.AppendLine("<div class=\"styleMrEListCardShadowRght\">");
sbRecord.AppendLine("<div class=\"styleMrEListCardBody\">");
sbRecord.AppendLine("<div class=\"styleMrEListCardPAdd\">");
if (_dataListRow[(int)dataListRowNames.rowPhoto].Visible)
{
string encodedDefPhotoUrl = Server.UrlEncode(_siteUrl + _sNoProfileImageFile);
string encodedConnectionString = Server.UrlEncode(EncryptString(_sConnectionStringPhoto, _sEncodeParams));
string sPictureHandler = String.Format("{0}/_layouts/MriyaStaffWebparts/ShowPhoto.ashx?id={1}&npi={2}&cs={3}",
_siteUrl, record.ID, encodedDefPhotoUrl, encodedConnectionString);
sbRecord.AppendLine("<div class=\"styleMrEListCardPhotoImg\">");
sbRecord.AppendLine(string.Format("<a href=\"{0}\"><img src=\"{1}\"></a>",
showDetailsCmd, sPictureHandler));
sbRecord.AppendLine("</div>");
}
sbRecord.AppendLine("<div class=\"styleMrEListCardText\">");
if (_dataListRow[(int)dataListRowNames.rowName].Visible)
{
if (bEOLNeeded)
sbRecord.AppendLine("<br/>");
string sEName = record.LastName;
if (sEName.Length > 0)
sEName += "<br/>";
if (record.FirstName.Length > 0)
{
if (sEName.Length > 0)
sEName += " ";
sEName += record.FirstName;
}
if (record.MiddleName.Length > 0)
{
if (sEName.Length > 0)
sEName += " ";
sEName += record.MiddleName;
}
sbRecord.AppendFormat("<span class=\"styleMrEListCardName{0}\"><a href=\"{1}\">{2}</a></span>\n",
((bTodayIsBirthday) ? (" styleMrEListCardTodayIsBirthday") : ("")),
showDetailsCmd, sEName);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowJobTitle].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardJobTitle\">{1}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
record.JobTitle);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowDepartment].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardDepartment\">{1}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
record.Department);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowBirthday].Visible)
{
string sBirthday = "";
if (record.DOB != null)
sBirthday = record.dtDOB.ToString("dd MMMM");
sbRecord.AppendFormat("{0}<span class=\"{1}\"><span class=\"styleMrEListCardItemCaption\">Дата народження:</span> {2}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
((bTodayIsBirthday == true) ? ("styleMrEListCardBirthday styleMrEListCardTodayIsBirthday") : ("styleMrEListCardBirthday")),
sBirthday);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowPhoneWork].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardPhoneWork\"><span class=\"styleMrEListCardItemCaption\">{1}:</span> {2}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
Properties.Resources.textHeaderPhoneWork1,
record.PhoneWork);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowPhoneMobile].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardPhoneMobile\"><span class=\"styleMrEListCardItemCaption\">{1}:</span> {2}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
Properties.Resources.textHeaderPhoneMobile,
record.PhoneMobile);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowEmail].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardEmail\"><span class=\"styleMrEListCardItemCaption\">E-mail:</span> {1}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
sEmail);
bEOLNeeded = true;
}
if (_dataListRow[(int)dataListRowNames.rowCity].Visible)
{
sbRecord.AppendFormat("{0}<span class=\"styleMrEListCardCity\">{1}</span>\n",
(bEOLNeeded) ? ("<br/>") : (""),
record.City);
bEOLNeeded = true;
}
sbRecord.AppendLine("</div>"); // styleMrEListCardText
sbRecord.AppendLine("<div style=\"clear:both\"></div>");
sbRecord.AppendLine("</div>"); // styleMrEListCardPAdd
sbRecord.AppendLine("</div>"); // styleMrEListCardBody
sbRecord.AppendLine("</div>"); // styleMrEListCardShadowRght
sbRecord.AppendLine("<div class=\"styleMrEListCardShadBttmLft\"></div>");
sbRecord.AppendLine("<div class=\"styleMrEListCardShadBttmRgt\"></div>");
sbRecord.AppendLine("</div>"); //styleMrEListCard
return sbRecord.ToString();
}