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