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