private void b_search_Click(object sender, EventArgs e)
{
ProfDataSet.PeopleDataTable dtPeople = new ProfDataSet.PeopleDataTable();
peopleTableAdapter1.Fill(dtPeople);
dgv.Rows.Clear();
if (dtPeople.Rows.Count != 0)
{
int i = 0;
foreach (DataRow pers in dtPeople.Rows)
{
if (decryptoStr(pers["famil"].ToString()).ToLower().StartsWith(tb_search.Text.Trim().ToLower()))
{
ProfDataSet.PeopleDepartmentDataTable dtDep = new ProfDataSet.PeopleDepartmentDataTable();
peopleDepartmentTableAdapter1.FillByPers(dtDep, (int)pers["id"]);
if (!dtDep.Rows[0].IsNull("idDepartment"))
{
dgv.Rows.Add();
ProfDataSet.PeopleWorkDataTable dtWork = new ProfDataSet.PeopleWorkDataTable();
peopleWorkTableAdapter1.FillByPeopleId(dtWork, (int)pers["id"]);
DataRow dr = dtWork.FirstOrDefault(p => p.isActual == "T" && p.isWorked == "T");
string workPlace = "";
string doljn = "";
if (dr != null)
{
workPlace = dr["workPlace"].ToString();
doljn = dr["doljn"].ToString();
}
dgv[0, i].Value = (int)pers["id"];
dgv[1, i].Value = decryptoStr(pers["famil"].ToString()) + " " + decryptoStr(pers["name"].ToString()) + " " + decryptoStr(pers["otch"].ToString());
dgv[2, i].Value = workPlace;
dgv[3, i].Value = doljn;
i++;
}
}
}
if (i == 0)
{
b_move.Visible = false;
MessageBox.Show("Не найден!");
}
else
{
b_move.Visible = true;
}
}
}
private void loadPeople()
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
int idD = Convert.ToInt32(tree_department.SelectedNode.Name);
SqlConnection conn = DB.GetDBConnection();
string select = " select distinct p.id, p.famil, p.name, p.otch, p.birthday, p.gender, " +
" p.type, " +
" (SELECT distinct pw.workPlace FROM prof.PeopleWork pw WHERE (pw.idPeople = p.id) AND (isWorked = 'T') AND (isActual = 'T')) as workPlace, " +
" (SELECT distinct pw.doljn FROM prof.PeopleWork pw WHERE (pw.idPeople = p.id) AND (isWorked = 'T') AND (isActual = 'T')) as doljn," +
" (select childrens from ( select distinct pc.idPeople, stuff((select ', ' + fioChildren + '( ' + CAST(YEAR(birthday) as VARCHAR) + 'г. )' from prof.PeopleChildren where idPeople = pc.idPeople order by id for XML path('')),1,1,'') childrens from prof.PeopleChildren pc) as www where www.idPeople = p.id) as child, ";
string from = " from prof.PeopleDepartment pd ";
string leftJoin = " left join prof.People p on p.id = pd.idPeople ";
string where = "";
string whereSocialStatus = "";
string whereLivingConditions = "";
FillArrayUserDeparmentsForLoadPeople(idD);
string paramArr = arrayUserDeparmentsForLoadPeople_String;
bool ms = false;
bool do35 = false;
bool pens = false;
if (rb_all_prof.Checked)
{
if (rb_male.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Муж' ";
}
else if (rb_femaly.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Жен' ";
}
else
{
where += $" pd.idDepartment in ({paramArr}) ";
}
}
else if (rb_inProf.Checked)
{
if (rb_male.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Муж' and p.isProf = 'T' ";
}
else if (rb_femaly.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Жен' and p.isProf = 'T' ";
}
else
{
where += $" pd.idDepartment in ({paramArr}) and p.isProf = 'T' ";
}
}
else if (rb_exitProf.Checked)
{
if (rb_male.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Муж' and p.isProf = 'F' ";
}
else if (rb_femaly.Checked)
{
where += $" pd.idDepartment in ({paramArr}) and p.gender = 'Жен' and p.isProf = 'F' ";
}
else
{
where += $" pd.idDepartment in ({paramArr}) and p.isProf = 'F' ";
}
}
string socIn = "";
if (clb_socialStatus.CheckedItems.Count > 0)
{
socIn += " where ";
for (int k = 0; k < clb_socialStatus.CheckedItems.Count; k++)
{
DataRowView drv = (DataRowView)clb_socialStatus.CheckedItems[k];
whereSocialStatus += $" pss.idTypeSocialStatus = {drv.Row["id"]} and ";
if (k == clb_socialStatus.CheckedItems.Count - 1)
{
socIn += $" soc.idTypeSocialStatus = {drv.Row["id"]} ";
}
else
{
socIn += $" soc.idTypeSocialStatus = {drv.Row["id"]} and ";
}
}
where += $" and (select count(id) from prof.PeopleSocialStatus pss where {whereSocialStatus} pss.idPeople = p.id ) > 0 ";
}
select += $" (select socName from ( select distinct soc.idPeople, stuff((select ', ' + ptss.name from prof.PeopleSocialStatus psoc left join prof.TypeSocialStatus ptss on ptss.id = psoc.idTypeSocialStatus where psoc.idPeople = soc.idPeople order by ptss.id for XML path('')),1,1,'') socName from prof.PeopleSocialStatus soc {socIn}) as www where www.idPeople = p.id) as socialStatus, ";
string plcIn = "";
if (clb_livingConditions.CheckedItems.Count > 0)
{
plcIn += " where ";
for (int k = 0; k < clb_livingConditions.CheckedItems.Count; k++)
{
DataRowView drv = (DataRowView)clb_livingConditions.CheckedItems[k];
whereLivingConditions += $" plc.idTypeLivingConditions = {drv.Row["id"]} and ";
if (k == clb_livingConditions.CheckedItems.Count - 1)
{
plcIn += $" plcc.idTypeLivingConditions = {drv.Row["id"]} ";
}
else
{
plcIn += $" plcc.idTypeLivingConditions = {drv.Row["id"]} and ";
}
}
where += $" and (select count(id) from prof.PeopleLivingConditions plc where {whereLivingConditions} plc.idPeople = p.id ) > 0 ";
}
select += $" (select livName from(select distinct plcc.idPeople, stuff((select ', ' + ptlc.name from prof.PeopleLivingConditions pplc left join prof.TypeLivingConditions ptlc on ptlc.id = pplc.idTypeLivingConditions where pplc.idPeople = plcc.idPeople order by ptlc.id for XML path('')),1,1,'') livName from prof.PeopleLivingConditions plcc {plcIn}) as www where www.idPeople = p.id) as livinCond ";
if (clb_other.CheckedItems.Count > 0)
{
for (int k = 0; k < clb_other.CheckedItems.Count; k++)
{
string nOther = clb_other.CheckedItems[k].ToString();
if (nOther.Contains("Молодой специалист (до 3х лет)"))
{
ms = true;
where += $" and (select staj_o.years_out from dbo.get_staj_func(p.id) staj_o ) < 3 " +
$" and (select count(pw.idPeople) from prof.PeopleWork pw where pw.idPeople = p.id) <> 0 ";
}
if (nOther.Contains("До 35 лет"))
{
do35 = true;
where += $" and dbo.fullAge(p.birthday, CURRENT_TIMESTAMP) < 35 ";
}
else if (nOther.Contains("Пенсионер"))
{
pens = true;
where += $" and p.isPensioner = 'T' ";
}
}
}
if (cb_period.Checked)
{
where += $" and ((p.dateEnter < '{ dtp_periodStart.Value.ToShortDateString() }' or p.dateEnter > '{ dtp_periodEnd.Value.ToShortDateString() }') ";
where += $" or (p.dateExit >= '{ dtp_periodStart.Value.ToShortDateString() }' and p.dateExit <= '{ dtp_periodEnd.Value.ToShortDateString() }')) ";
}
string sql = select + " " + from + " " + leftJoin + " where " + where;
SqlCommand sqlCommand = new SqlCommand(sql)
{
Connection = conn
};
conn.Open();
using (DbDataReader reader = sqlCommand.ExecuteReader())
{
if (reader.HasRows)
{
ProfDataSet.PeopleWorkDataTable dtpw = new ProfDataSet.PeopleWorkDataTable();
while (reader.Read())
{
DataRow dr = dt_persons.NewRow();
dr[0] = reader.GetInt32(0);
dr[1] = decryptoStr(reader.GetString(1)) + " " + decryptoStr(reader.GetString(2)) + " " + decryptoStr(reader.GetString(3));
dr[2] = !reader.IsDBNull(4) ? reader.GetDateTime(4) : DateTime.Now;
if (reader.GetString(5).Equals("Муж"))
{
dr[3] = "Мужской"; countMale++;
}
else
{
dr[3] = "Женский"; countFemale++;
}
dr[4] = !reader.IsDBNull(7) ? reader.GetString(7) : "";
dr[5] = !reader.IsDBNull(8) ? reader.GetString(8) : "";
dr[6] = reader.GetString(6).Equals("W") ? "Сотрудник" : "Студент";
dr[7] = !reader.IsDBNull(10) ? reader.GetString(10) : "";
dr[8] = !reader.IsDBNull(11) ? reader.GetString(11) : "";
dr[9] = !reader.IsDBNull(9) ? reader.GetString(9) : "";
string other = "";
other += pens ? "Пенсионер; " : "";
other += do35 ? "До 35 лет; " : "";
other += ms ? "Молодой специалист (до 35 лет); " : "";
dr[10] = other;
dt_persons.Rows.Add(dr);
countAll++;
}
}
}
conn.Close();
conn.Dispose();
stopWatch.Stop();
// Get the elapsed time as a TimeSpan value.
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = string.Format("{0:00}м. {1:00}с. {2:00}мс.", ts.Minutes, ts.Seconds, ts.Milliseconds / 10);
speedLoadGrid.Text = "Скорость загрузки таблицы: " + elapsedTime;
}
