private void FixDB_1_2() { try { DataTable spec_psychoTable = conSql.GetDataTable("spec_psycho", "SELECT spec_psycho.id AS spec_psycho_id FROM spec_psycho LEFT JOIN spec ON spec.id=spec_psycho.id_spec WHERE (isnull(spec.last_name) And isnull(spec.first_name))"); foreach (DataRow row in spec_psychoTable.Rows) { conSql.DoQuery("DELETE * FROM spec_psycho WHERE (id=" + row["spec_psycho_id"].ToString() + ")"); } string min_id_party = conSql.GetValue("min_id", "select min(id) as min_id from spr_party_number"); conSql.DoQuery("UPDATE spec SET party_id = " + min_id_party + " WHERE (isnull(party_id) OR (party_id=0))"); DataTable partyTable = conSql.GetDataTable("party", "SELECT spec.party_id AS party_id FROM spec LEFT JOIN spr_party_number ON spr_party_number.id=spec.party_id WHERE (isnull(spr_party_number.name))"); foreach (DataRow row in partyTable.Rows) { conSql.DoQuery("UPDATE spec SET party_id = " + min_id_party + " WHERE (party_id=" + row["party_id"].ToString() + ")"); } DataTable prev_convTable = conSql.GetDataTable("prev_conv", "SELECT * FROM prev_conv"); foreach (DataRow row in prev_convTable.Rows) { conSql.DoQuery("UPDATE prev_conv SET period = '" + row["period_years"].ToString() + " лет " + row["period_months"].ToString() + " мес " + row["period_days"].ToString() + " дней " + "' WHERE (id=" + row["id"].ToString() + ")"); } //”дал¤ем лишние записи в таблице spec_psycho // conSql.DoQuery("DELETE * FROM spec_psycho WHERE (id=6 OR id=7 OR id=8 OR id=9)"); } catch (Exception e) { MessageBox.Show(e.Message, "ќшибка при изменении структуры базы данных, обновление 1.2"); return; } }
private void bnLoad_Click(object sender, EventArgs e) { DataTable dt_buf = sqlCon.GetDataTable("spec"); String str = null; foreach (DataRow row in dt_buf.Rows) { if (Convert.IsDBNull(row["selected"])) { continue; } if (Convert.ToBoolean(row["selected"]) == true) { str = (str == null) ? str + Convert.ToInt32(row["id"]) : str + ", " + Convert.ToInt32(row["id"]); } } if (str == null) { MessageBox.Show("Выберите осужденных, которых вы хотите выгрузить"); return; } //MessageBox.Show(str); IVRTransfer ivr = new IVRTransfer(); ivr.UploadData(str, cbMoveToArh.Checked); }
public FormControlManager() { sqlCon = new SQLDBConnect(); DataTable dt = sqlCon.GetDataTable("spr_user_controls", "SELECT * FROM spr_user_controls"); dt = sqlCon.GetDataTable("user_rol_access", "SELECT * FROM user_rol_access"); }
// internal protected ReportViewer reportviewer; public RepBonusAndPenaltyForm(DataRow selRow) { this.selRow = selRow; idSpec = (Int32)selRow["id"]; InitializeComponent(); dbCon = new SQLDBConnect(); try { DateTimeFormatInfo fmt = (new CultureInfo("ru-RU")).DateTimeFormat; rvBandP.LocalReport.ReportPath = @"ReportBandP.rdlc"; DataTable dtBonus = dbCon.GetDataTable("bonus", "SELECT COUNT(*) AS numberpp, b1.date_bonus AS date_bonus, b1.bonus_reason AS bonus_reason, spr_bonus_type.name AS bonus_type FROM (bonus AS b1 LEFT JOIN bonus AS b2 ON b2.id<=b1.id) LEFT JOIN spr_bonus_type ON spr_bonus_type.id=b1.bonus_type_id WHERE (b1.id_spec=" + idSpec + ") AND (b2.id_spec=" + idSpec + ") GROUP BY b1.id, b1.date_bonus, b1.bonus_reason, spr_bonus_type.name"); rvBandP.LocalReport.DataSources.Add(new ReportDataSource("DataSetBandP_DataTableBonus", dtBonus)); DataTable dtPenalty = dbCon.GetDataTable("penalty", "SELECT COUNT(*) AS numberpp, p1.date_penalty AS date_penalty, p1.reason AS reason, p1.removal AS removal, spr_penalty_type.name AS penalty_type FROM (penalty AS p1 LEFT JOIN penalty AS p2 ON p2.id<=p1.id) LEFT JOIN spr_penalty_type ON spr_penalty_type.id=p1.penalty_type_id WHERE (p1.id_spec=" + idSpec + ") AND (p2.id_spec=" + idSpec + ") GROUP BY p1.id, p1.date_penalty, p1.reason, p1.removal, spr_penalty_type.name"); rvBandP.LocalReport.DataSources.Add(new ReportDataSource("DataSetBandP_DataTablePenalty", dtPenalty)); ReportParameter p1 = new ReportParameter("name", selRow["last_name"].ToString() + " " + selRow["first_name"].ToString() + " " + selRow["patronymic"].ToString()); rvBandP.LocalReport.SetParameters(new ReportParameter[] { p1 }); rvBandP.ProcessingMode = ProcessingMode.Local; rvBandP.RefreshReport(); } catch (Exception e) { MessageBox.Show(e.Message, "ќшибка при построении отчета"); Close(); } }
public EditUserRolForm() { InitializeComponent(); notSaved = false; sqlCon = new SQLDBConnect(); newDBcon = new SprDbConnect("spr_user_rol"); dgUserRol.DataSource = newDBcon.GetDataTable("spr_user_rol"); dgUserRol.Columns[0].HeaderText = "Код"; dgUserRol.Columns[0].Visible = false; dgUserRol.Columns[0].DataPropertyName = "id"; dgUserRol.Columns[1].HeaderText = "Наименование"; dgControls.AutoGenerateColumns = false; DataGridViewTextBoxColumn column = new DataGridViewTextBoxColumn(); /* * column.HeaderText = "Код"; * column.Visible = false; * column.DataPropertyName = "id"; * column.Name = "id"; * dgControls.Columns.Add(column); */ column = new DataGridViewTextBoxColumn(); column.HeaderText = "Наименование"; column.Visible = true; column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; column.DataPropertyName = "opisanie"; dgControls.Columns.Add(column); DataGridViewCheckBoxColumn columnCheck = new DataGridViewCheckBoxColumn(); { columnCheck.Name = "enabled"; columnCheck.DataPropertyName = "enabled"; columnCheck.HeaderText = "Разрешено"; columnCheck.Width = 80; columnCheck.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; } dgControls.Columns.Add(columnCheck); //Заполняем таблицу user_rol_access на случай появления новых контролов или групп пользователей sqlCon.DoQuery(@"INSERT INTO user_rol_access SELECT spr_user_controls.id AS id_user_controls, spr_user_rol.id AS id_user_rol FROM spr_user_controls, spr_user_rol WHERE spr_user_controls.id NOT IN (SELECT DISTINCT id_user_controls FROM user_rol_access) OR spr_user_rol.id NOT IN (SELECT DISTINCT id_user_rol FROM user_rol_access)"); DataTable dt = sqlCon.GetDataTable("user_access", @"SELECT user_rol_access.id, user_rol_access.id_user_rol, user_rol_access.enabled, spr_user_controls.opisanie FROM user_rol_access LEFT JOIN spr_user_controls ON (user_rol_access.id_user_controls = spr_user_controls.id)"); DataView dv = new DataView(dt); dgControls.DataSource = dv; }
private void InsertControlsBySingle(String formName) { ArrayList ctlList = GetAllControls(formName); //DataTable dt = sqlCon.GetDataTable("spr_user_controls", "SELECT * FROM spr_user_controls"); DataTable dt = sqlCon.GetDataTable("spr_user_controls"); if (dt == null) { return; } foreach (Object obj in ctlList) { String name = null; String name_rus = null; String opisanie = null; if (obj is Button) { name = ((Button)obj).Name; name_rus = ((Button)obj).Text; opisanie = GetParentChain((Button)obj) + " (кнопка)"; } else if (obj is ToolStripItem) { name = ((ToolStripItem)obj).Name; name_rus = ((ToolStripItem)obj).Text; opisanie = GetParentChain((ToolStripItem)obj) + " (пункт меню)"; } if (String.IsNullOrEmpty(name) | String.IsNullOrEmpty(formName) | IsControlExist(formName, name)) { continue; } DataRow row = dt.NewRow(); row["name"] = name; row["name_rus"] = name_rus; row["name_form"] = formName; row["opisanie"] = opisanie; dt.Rows.Add(row); //MessageBox.Show(dt.Rows.Count.ToString()); } }
private void tabControl1_Selected(object sender, TabControlEventArgs e) { switch (e.TabPageIndex) { case 0: bnBack.Enabled = false; bnNext.Text = "Далее >>"; break; case 1: bnBack.Enabled = true; bnNext.Text = "Готово"; dgDepartment.DataSource = sqlCon.GetDataTable("department", "SELECT id, name FROM department WHERE (higher='" + region + "')"); dgDepartment.Rows[departmentIndex].Selected = true; break; } }
private void EditFind(string strSQL) { DataView IsPresentView = new DataView(newDBcon.GetDataTable("spec")); if (strSQL != "") { SQLDBConnect sqlCon = new SQLDBConnect(); DataTable dt = sqlCon.GetDataTable("dt", strSQL); if (dt == null) { return; } String ids = null; foreach (DataRow row in dt.Rows) { ids = (ids == null) ? row["id"].ToString() : ids + ", " + row["id"].ToString(); } //String strID = (ids == null) ? "-1" : ids; //String strFilter = String.IsNullOrEmpty(strID) ? "(is_present = true)" : "(is_present = true) AND (id NOT IN (" + strID + "))"; String strFilter = String.IsNullOrEmpty(ids) ? "(is_present = true)" : "(is_present = true) AND (id NOT IN (" + ids + "))"; IsPresentView.RowFilter = strFilter; } else { if (cbParty.SelectedIndex > -1) { if (tbLastName.Text != String.Empty) { IsPresentView.RowFilter = "(is_present = true) AND (last_name LIKE '%" + tbLastName.Text + "%') AND (party_id = " + cbParty.SelectedValue + ")"; } else { IsPresentView.RowFilter = "(is_present = true) AND (party_id = " + cbParty.SelectedValue + ")"; } } else { if (tbLastName.Text != String.Empty) { IsPresentView.RowFilter = "(is_present = true) AND (last_name LIKE '%" + tbLastName.Text + "%')"; } else { IsPresentView.RowFilter = "is_present = true"; } } } dgListPerson.AutoGenerateColumns = false; dgListPerson.DataSource = IsPresentView; labelHelp.Text = "Загружено записей: " + dgListPerson.RowCount; }
public ChooseDepartmentForm() { InitializeComponent(); sqlCon = new SQLDBConnect(); bnBack.Enabled = false; dgRegion.Columns.Add(new DataGridViewTextBoxColumn()); dgRegion.Columns[0].DataPropertyName = "id"; dgRegion.Columns[0].Name = "id"; dgRegion.Columns[0].HeaderText = "Код ОКПО"; dgRegion.Columns[0].Width = 100; dgRegion.Columns.Add(new DataGridViewTextBoxColumn()); dgRegion.Columns[1].DataPropertyName = "name"; dgRegion.Columns[1].HeaderText = "Наименование региона"; dgRegion.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgRegion.AutoGenerateColumns = false; dgDepartment.Columns.Add(new DataGridViewTextBoxColumn()); dgDepartment.Columns[0].DataPropertyName = "id"; dgDepartment.Columns[0].Name = "id"; dgDepartment.Columns[0].HeaderText = "Код ОКПО"; dgDepartment.Columns[0].Width = 100; dgDepartment.Columns.Add(new DataGridViewTextBoxColumn()); dgDepartment.Columns[1].DataPropertyName = "name"; dgDepartment.Columns[1].HeaderText = "Наименование подразделения"; dgDepartment.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dgDepartment.AutoGenerateColumns = false; /* * RegistryKey regKey = Registry.CurrentUser; * regKey = regKey.OpenSubKey("Software\\UFSIN\\ivrJournal"); * String dbPath = regKey.GetValue("dbPath", @"c:\Program files\ufsin_rk\Дневник ИВР\divr.mdb").ToString(); */ departmentID = sqlCon.GetSystemValue("Department"); dgRegion.DataSource = sqlCon.GetDataTable("region", "SELECT id, name FROM department WHERE higher='0'"); this.tabControl1.Selected += new TabControlEventHandler(tabControl1_Selected); }
private void SaveUserRol() { DataTable dt2update = sqlCon.GetDataTable("user_rol_access", "SELECT * FROM user_rol_access"); DataTable dt = ((DataView)dgControls.DataSource).Table; foreach (DataRow rowSrc in dt.Rows) { foreach (DataRow rowDst in dt2update.Rows) { if (rowSrc["id"].ToString() == rowDst["id"].ToString()) { rowDst["enabled"] = rowSrc["enabled"]; } } } sqlCon.UpdateDataTable("user_rol_access"); }
private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { toolStripProgressBar.Enabled = false; dgListPerson.DataSource = IsPresentView; SetRights(); SQLDBConnect sqlCon = new SQLDBConnect(); String dep = sqlCon.GetSystemValue("Department"); DataTable dt = sqlCon.GetDataTable("spr_party_number", @"SELECT * FROM spr_party_number WHERE department_id='" + dep + @"'"); cbParty.DataSource = dt; cbParty.SelectedIndex = -1; labelHelp.Text = "Загружено записей: " + dgListPerson.RowCount; ((MainForm)this.MdiParent).statusStrip.Items.Clear(); }
private void bnShow_Click(object sender, EventArgs e) { String qstrSel = null; String qstrWhere = null; ArrayList fieldList = GetFieldList(); ArrayList condList = GetConditionList(); if (fieldList.Count == 0) { return; } foreach (Object obj in fieldList) { String field = obj.ToString(); qstrSel = (qstrSel == null) ? field : qstrSel + ", " + field; } foreach (Object obj in condList) { String field = obj.ToString(); qstrWhere = (qstrWhere == null) ? " WHERE (" + field + ")" : qstrWhere + " AND " + "(" + field + ")"; } qstrWhere = (qstrWhere == null) ? " WHERE (spec.is_present = true) " : qstrWhere + " AND (spec.is_present = true)"; String qstrJoin = @"((((spec LEFT JOIN spr_nation ON (spec.nation_id = spr_nation.id) ) LEFT JOIN spr_edu ON (spec.edu_id = spr_edu.id) ) LEFT JOIN spr_profession ON (spec.profession_id = spr_profession.id) ) LEFT JOIN spr_mstatus ON (spec.mstatus_id = spr_mstatus.id) ) LEFT JOIN spr_party_number ON (spec.party_id = spr_party_number.id)"; String qstr = "SELECT " + qstrSel + " FROM " + qstrJoin + qstrWhere; //MessageBox.Show(qstr); DataTable dtResult = eurDBcon.GetDataTable("result", qstr); dgExport.DataSource = dtResult; lbRecords.Text = "" + dgExport.RowCount.ToString(); }
public RepListForm() { InitializeComponent(); eurDBcon = new SQLDBConnect(); dtListField = eurDBcon.GetDataTable("list_field", "SELECT * FROM stuctur_base"); DataGridViewComboBoxColumn column = new DataGridViewComboBoxColumn(); { column.HeaderText = "Поля базы"; column.DropDownWidth = 250; column.Width = 90; column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; column.MaxDropDownItems = 10; column.FlatStyle = FlatStyle.Flat; column.DataSource = dtListField; column.ValueMember = "id"; //column.ValueMember = "spr"; column.DisplayMember = "field_name_rus"; column.Name = "fields"; } dgListField.Columns.Add(column); /* * DataGridViewCheckBoxColumn columnCheck = new DataGridViewCheckBoxColumn(); * { * columnCheck.Name = "sort"; * // columnCheck.DataPropertyName = "enabled"; * columnCheck.HeaderText = "Сортировать"; * columnCheck.Width = 100; * columnCheck.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; * } * dgListField.Columns.Add(columnCheck); */ column = new DataGridViewComboBoxColumn(); { column.Name = "field_name_rus"; column.HeaderText = "Поля базы"; column.DropDownWidth = 250; column.Width = 90; column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; column.MaxDropDownItems = 10; column.FlatStyle = FlatStyle.Flat; column.DataSource = dtListField; //column.ValueMember = "spr"; column.ValueMember = "id"; column.DisplayMember = "field_name_rus"; column.Name = "fields"; } dgWhere.Columns.Add(column); dtSprCondition = eurDBcon.GetDataTable("enum_condition", "SELECT * FROM enum_condition"); column = new DataGridViewComboBoxColumn(); { column.HeaderText = "Условие"; column.DropDownWidth = 150; column.Width = 90; column.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; column.MaxDropDownItems = 10; column.FlatStyle = FlatStyle.Flat; column.DataSource = dtSprCondition; column.ValueMember = "id"; column.DisplayMember = "name"; column.Name = "conditions"; } dgWhere.Columns.Add(column); DataGridViewTextBoxColumn columnText = new DataGridViewTextBoxColumn(); { columnText.Name = "Value"; columnText.HeaderText = "Значение"; columnText.Width = 150; columnText.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; columnText.Name = "value"; } dgWhere.Columns.Add(columnText); }
// DataTable dt_buf; public IVRTransferForm() { InitializeComponent(); DataGridViewTextBoxColumn textColumn = new DataGridViewTextBoxColumn(); { textColumn.DataPropertyName = "id"; textColumn.HeaderText = "код"; textColumn.Width = 30; textColumn.MinimumWidth = 50; textColumn.Visible = false; textColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; } dgIVRList.Columns.Add(textColumn); textColumn = new DataGridViewTextBoxColumn(); { textColumn.DataPropertyName = "last_name"; textColumn.HeaderText = "Фамилия"; textColumn.Width = 100; textColumn.MinimumWidth = 90; textColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; } dgIVRList.Columns.Add(textColumn); textColumn = new DataGridViewTextBoxColumn(); { textColumn.DataPropertyName = "first_name"; textColumn.HeaderText = "Имя"; textColumn.Width = 100; textColumn.MinimumWidth = 90; textColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; } dgIVRList.Columns.Add(textColumn); textColumn = new DataGridViewTextBoxColumn(); { textColumn.DataPropertyName = "patronymic"; textColumn.HeaderText = "Отчество"; textColumn.Width = 100; textColumn.MinimumWidth = 90; textColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; } dgIVRList.Columns.Add(textColumn); textColumn = new DataGridViewTextBoxColumn(); { textColumn.DataPropertyName = "birthdate"; textColumn.HeaderText = "Дата рождения"; textColumn.Width = 80; textColumn.MinimumWidth = 80; textColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; } dgIVRList.Columns.Add(textColumn); DataGridViewCheckBoxColumn columnCheck = new DataGridViewCheckBoxColumn(); { columnCheck.Name = "selected"; columnCheck.DataPropertyName = "selected"; columnCheck.HeaderText = "Метка"; columnCheck.Width = 50; columnCheck.AutoSizeMode = DataGridViewAutoSizeColumnMode.None; } dgIVRList.Columns.Add(columnCheck); sqlCon = new SQLDBConnect(); DataTable dt_buf = sqlCon.GetDataTable("spec", "SELECT * FROM spec WHERE is_present = true"); if (dt_buf == null) { return; } DataColumn newColumn5 = new DataColumn(); newColumn5.ColumnName = "selected"; newColumn5.DataType = System.Type.GetType("System.Boolean"); dt_buf.Columns.Add(newColumn5); foreach (DataRow row in dt_buf.Rows) { row["selected"] = false; } dgIVRList.AutoGenerateColumns = false; dgIVRList.DataSource = dt_buf; String dep = sqlCon.GetSystemValue("Department"); DataTable dt_party = sqlCon.GetDataTable("spr_party_number", @"SELECT * FROM spr_party_number WHERE department_id='" + dep + @"'"); cbParty.DataSource = dt_party; cbParty.ValueMember = "id"; cbParty.DisplayMember = "name"; cbParty.SelectedIndex = -1; }
//Процедура выгрузки public void UploadData(String strSQL, Boolean moveToArh) { String stringSQL = null; String stringSQL1 = null; String stringSQL2 = null; //spec stringSQL = "SELECT * FROM spec WHERE id IN (" + strSQL + ")"; DataTable dt = sqlConSrc.GetDataTable("spec", stringSQL); stringSQL1 = "SELECT DISTINCT edu_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_edu WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_edu", stringSQL2); stringSQL1 = "SELECT DISTINCT nation_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_nation WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_nation", stringSQL2); stringSQL1 = "SELECT DISTINCT mstatus_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_mstatus WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_mstatus", stringSQL2); stringSQL1 = "SELECT DISTINCT mstatus_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_mstatus WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_mstatus", stringSQL2); stringSQL1 = "SELECT DISTINCT profession_id FROM spec WHERE id IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_profession WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_profession", stringSQL2); //party_number //stringSQL1 = "SELECT DISTINCT party_id FROM spec WHERE id IN (" + strSQL + ")"; //stringSQL2 = "SELECT * FROM spr_party_number WHERE id IN (" + stringSQL1 + ")"; stringSQL2 = "SELECT * FROM spr_party_number"; sqlConSrc.GetDataTable("spr_party_number", stringSQL2); //relations stringSQL = "SELECT * FROM relations WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("relations", stringSQL); stringSQL1 = "SELECT DISTINCT degree_id FROM relations WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_degree WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_degree", stringSQL2); //bonus stringSQL = "SELECT * FROM bonus WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("bonus", stringSQL); stringSQL1 = "SELECT DISTINCT bonus_type_id FROM bonus WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_bonus_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_bonus_type", stringSQL2); stringSQL1 = "SELECT DISTINCT performer_id FROM bonus WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_performers WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_performers", stringSQL2); //penalty stringSQL = "SELECT * FROM penalty WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("penalty", stringSQL); stringSQL1 = "SELECT DISTINCT penalty_type_id FROM penalty WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_penalty_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_penalty_type", stringSQL2); //party stringSQL = "SELECT * FROM party WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("party", stringSQL); /* * stringSQL1 = "SELECT DISTINCT party_number_id FROM party WHERE id IN (" + strSQL + ")"; * stringSQL2 = "SELECT * FROM spr_party_number WHERE id IN (" + stringSQL1 + ")"; * sqlConSrc.GetDataTable("spr_party_number2", stringSQL2); */ //spec_psycho stringSQL = "SELECT * FROM spec_psycho WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("spec_psycho", stringSQL); //psycho_char stringSQL = "SELECT * FROM psycho_char WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("psycho_char", stringSQL); //prev_conv stringSQL = "SELECT * FROM prev_conv WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("prev_conv", stringSQL); stringSQL1 = "SELECT DISTINCT release_reason_id FROM prev_conv WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_release_reason WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_release_reason", stringSQL2); //profilact_ychet stringSQL = "SELECT * FROM profilact_ychet WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("profilact_ychet", stringSQL); stringSQL1 = "SELECT DISTINCT id_profilact_ychet FROM profilact_ychet WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_profilact_ychet WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_profilact_ychet", stringSQL2); //ivr stringSQL = "SELECT * FROM ivr WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("ivr", stringSQL); stringSQL1 = "SELECT DISTINCT employee_id FROM ivr WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM employee WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("employee", stringSQL2); stringSQL1 = "SELECT DISTINCT work_type FROM ivr WHERE id_spec IN (" + strSQL + ")"; stringSQL2 = "SELECT * FROM spr_work_type WHERE id IN (" + stringSQL1 + ")"; sqlConSrc.GetDataTable("spr_work_type", stringSQL2); //resolution stringSQL = "SELECT * FROM resolution WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("resolution", stringSQL); //system stringSQL = "SELECT * FROM system"; sqlConSrc.GetDataTable("system", stringSQL); //results stringSQL = "SELECT * FROM results WHERE id_spec IN (" + strSQL + ")"; sqlConSrc.GetDataTable("results", stringSQL); DirectoryInfo dir = new DirectoryInfo(tempDir); dir.Create(); CopyFoto(GetDBDirPath(), tempDir, dt); DataSet ds = sqlConSrc.GetDataSet(); ds.WriteXml(tempDir + @"\" + dataFile, XmlWriteMode.WriteSchema); Boolean svResult = SaveFile(tempDir); if (!svResult) { return; } if (moveToArh) { sqlConSrc.DoQuery("UPDATE spec SET is_present = false WHERE id IN (" + strSQL + ")"); } }
//Процедура загрузки public Boolean DownloadData(DataTable dt_buf) { DataTable dtSrc = sqlConSrc.GetDataTable("spec"); DataTable dtDst = sqlConDst.GetDataTable("spec", "SELECT * FROM spec"); Dictionary <int, int> dictSprNat; Dictionary <int, int> dictSprProf; Dictionary <int, int> dictSprEdu; Dictionary <int, int> dictSprMS; Dictionary <int, int> dictSprParty; Dictionary <int, int> dictSprDegree; Dictionary <int, int> dictSprBT; Dictionary <int, int> dictSprPerf; Dictionary <int, int> dictSprPT; Dictionary <int, int> dictSprRelR; Dictionary <int, int> dictSprProfY; Dictionary <int, int> dictSprEmpl; Dictionary <int, int> dictSprWT; Dictionary <int, int> dictSpec; Boolean alignStatus = AlignSpr("spr_nation", out dictSprNat, AlignMode.dialog) & AlignSpr("spr_profession", out dictSprProf, AlignMode.dialog) & AlignSpr("spr_edu", out dictSprEdu, AlignMode.dialog) & AlignSpr("spr_mstatus", out dictSprMS, AlignMode.dialog) & AlignSpr("spr_party_number", out dictSprParty, AlignMode.add) & AlignSpr("spr_degree", out dictSprDegree, AlignMode.dialog) & AlignSpr("spr_bonus_type", out dictSprBT, AlignMode.dialog) & AlignSpr("spr_performers", out dictSprPerf, AlignMode.dialog) & AlignSpr("spr_penalty_type", out dictSprPT, AlignMode.dialog) & AlignSpr("spr_release_reason", out dictSprRelR, AlignMode.dialog) & AlignSpr("spr_profilact_ychet", out dictSprProfY, AlignMode.dialog) & AlignSpr("employee", out dictSprEmpl, AlignMode.add) & AlignSpr("spr_work_type", out dictSprWT, AlignMode.dialog); if (!alignStatus) { return(false); } ReplaceSprID(dtSrc, "nation_id", dictSprNat); ReplaceSprID(dtSrc, "profession_id", dictSprProf); ReplaceSprID(dtSrc, "edu_id", dictSprEdu); ReplaceSprID(dtSrc, "mstatus_id", dictSprMS); ReplaceSprID(dtSrc, "party_id", dictSprParty); ArrayList idList = new ArrayList(); GetNotSelected(dtSrc, dt_buf, idList); CompareTables(dtSrc, dtDst, idList, "first_name", "last_name", "patronymic", "birthdate"); int count = RemoveByFilter(dtSrc, "id", idList); //MessageBox.Show("Сообщение", "Информация о " +count.ToString()+ " абонентах не была загружена " ); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("spec"); CopyFoto(tempDir, GetDBDirPath(), dtSrc); GetDict(dtSrc, dtDst, out dictSpec); //relations dtSrc = sqlConSrc.GetDataTable("relations"); dtDst = sqlConDst.GetDataTable("relations", "SELECT * FROM relations"); count = RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "degree_id", dictSprDegree); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("relations"); //bonus dtSrc = sqlConSrc.GetDataTable("bonus"); dtDst = sqlConDst.GetDataTable("bonus", "SELECT * FROM bonus"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "bonus_type_id", dictSprBT); ReplaceSprID(dtSrc, "performer_id", dictSprPerf); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("bonus"); //penalty dtSrc = sqlConSrc.GetDataTable("penalty"); dtDst = sqlConDst.GetDataTable("penalty", "SELECT * FROM penalty"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "penalty_type_id", dictSprPT); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("penalty"); //party dtSrc = sqlConSrc.GetDataTable("party"); dtDst = sqlConDst.GetDataTable("party", "SELECT * FROM party"); RemoveByFilter(dtSrc, "id_spec", idList); //AlignSpr("spr_party_number", out dictSpr, AlignMode.add); ReplaceSprID(dtSrc, "party_number_id", dictSprParty); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("party"); //spec_psycho dtSrc = sqlConSrc.GetDataTable("spec_psycho"); dtDst = sqlConDst.GetDataTable("spec_psycho", "SELECT * FROM spec_psycho"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("spec_psycho"); //psycho_char dtSrc = sqlConSrc.GetDataTable("psycho_char"); dtDst = sqlConDst.GetDataTable("psycho_char", "SELECT * FROM psycho_char"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("psycho_char"); //prev_conv dtSrc = sqlConSrc.GetDataTable("prev_conv"); dtDst = sqlConDst.GetDataTable("prev_conv", "SELECT * FROM prev_conv"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "release_reason_id", dictSprRelR); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("prev_conv"); //profilact_ychet dtSrc = sqlConSrc.GetDataTable("profilact_ychet"); dtDst = sqlConDst.GetDataTable("profilact_ychet", "SELECT * FROM profilact_ychet"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "id_profilact_ychet", dictSprProfY); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("profilact_ychet"); //ivr dtSrc = sqlConSrc.GetDataTable("ivr"); dtDst = sqlConDst.GetDataTable("ivr", "SELECT * FROM ivr"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "employee_id", dictSprEmpl); ReplaceSprID(dtSrc, "work_type", dictSprWT); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("ivr"); //resolution dtSrc = sqlConSrc.GetDataTable("resolution"); dtDst = sqlConDst.GetDataTable("resolution", "SELECT * FROM resolution"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("resolution"); //results dtSrc = sqlConSrc.GetDataTable("results"); dtDst = sqlConDst.GetDataTable("results", "SELECT * FROM results"); RemoveByFilter(dtSrc, "id_spec", idList); ReplaceSprID(dtSrc, "id_spec", dictSpec); FixRowState(dtSrc); MergeTables(dtSrc, dtDst); sqlConDst.UpdateDataTable("results"); if (idList.Count > 0) { MessageBox.Show("Информация о " + idList.Count.ToString() + " абонентах не была загружена ", "Сообщение"); } return(true); }