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;
}
}
public RepDIVRForm(DataRow selRow)
{
this.selRow = selRow;
idSpec = (int)selRow["id"];
InitializeComponent();
dbConSQL = new SQLDBConnectLite();
try
{
rvDIVR.LocalReport.ReportPath = @"ReportIVR.rdlc";
if (selRow["foto"] != null)
{
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableLogo", GetTableLogo(selRow["foto"].ToString())));
}
else
{
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableLogo", GetTableLogo("")));
}
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableParty", dbConSQL.GetDataTable("party", "SELECT p.arr_date AS arr_date, p.ord AS ord, p.reason AS reason, spr_party_number.name AS party_number FROM party AS p LEFT JOIN spr_party_number ON spr_party_number.id=p.party_number_id WHERE (p.id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableRelations", dbConSQL.GetDataTable("relations", "SELECT r.last_name AS last_name, r.first_name AS first_name, r.patronymic AS patronymic, r.address AS address, r.birthdate AS birthdate, spr_degree.name AS degree FROM relations AS r LEFT JOIN spr_degree ON spr_degree.id=r.degree_id WHERE (r.id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTablePsycho_char", dbConSQL.GetDataTable("psycho_char", "SELECT date_meet, orientation, psycho_char, behavior FROM psycho_char WHERE (id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTablePrev_conv", dbConSQL.GetDataTable("prev_conv", "SELECT start_date, period, text_prev, article, release_date FROM prev_conv WHERE (id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableIvr1", dbConSQL.GetDataTable("ivr1", "SELECT data_ivr, content, description FROM ivr WHERE ((id_spec=" + idSpec + ") AND (id_type_ivr = 1))")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableIvr2", dbConSQL.GetDataTable("ivr2", "SELECT ivr.data_ivr AS data_ivr, ivr.content AS content, ivr.description AS description, employee.last_name + ' ' + employee.first_name + ' ' + employee.patronymic + ', ' + employee.rank + ', ' + employee.post as employee FROM ivr LEFT JOIN employee ON (ivr.employee_id = employee.id) WHERE ((id_spec=" + idSpec + ") AND (id_type_ivr = 2))")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableIvr3", dbConSQL.GetDataTable("ivr3", "SELECT ivr.data_ivr AS data_ivr, ivr.content AS content, ivr.description AS description, employee.last_name + ' ' + employee.first_name + ' ' + employee.patronymic + ', ' + employee.rank + ', ' + employee.post as employee FROM ivr LEFT JOIN employee ON (ivr.employee_id = employee.id) WHERE ((id_spec=" + idSpec + ") AND (id_type_ivr = 3))")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableResolution", dbConSQL.GetDataTable("resolution", "SELECT date_resolution, resolution, description FROM resolution WHERE (id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTableBonus", dbConSQL.GetDataTable("bonus", "SELECT bonus.date_bonus AS date_bonus, bonus.bonus_reason AS bonus_reason, spr_bonus_type.name AS bonus_type, spr_performers.name AS performers, bonus.order_date AS order_date, bonus.order_number AS order_number FROM spr_performers RIGHT JOIN (spr_bonus_type RIGHT JOIN bonus ON spr_bonus_type.id=bonus.bonus_type_id) ON spr_performers.id=bonus.performer_id WHERE (bonus.id_spec=" + idSpec + ")")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTablePenalty1", dbConSQL.GetDataTable("penalty1", "SELECT penalty.date_penalty AS date_penalty, penalty.reason AS reason, spr_penalty_type.name AS penalty_type, penalty.order_date AS order_date, penalty.order_number AS order_number, penalty.removal AS removal, spr_performers.name AS performers FROM spr_performers RIGHT JOIN (spr_penalty_type RIGHT JOIN penalty ON spr_penalty_type.id=penalty.penalty_type_id) ON spr_performers.id=penalty.performer_id WHERE ((penalty.id_spec=" + idSpec + ") AND (oral=true))")));
rvDIVR.LocalReport.DataSources.Add(new ReportDataSource("DataSetDIVR_DataTablePenalty2", dbConSQL.GetDataTable("penalty2", "SELECT penalty.date_penalty AS date_penalty, penalty.reason AS reason, spr_penalty_type.name AS penalty_type, penalty.order_date AS order_date, penalty.order_number AS order_number, penalty.removal AS removal, spr_performers.name AS performers FROM spr_performers RIGHT JOIN (spr_penalty_type RIGHT JOIN penalty ON spr_penalty_type.id=penalty.penalty_type_id) ON spr_performers.id=penalty.performer_id WHERE ((penalty.id_spec=" + idSpec + ") AND (oral=false))")));
DateTimeFormatInfo fmt = (new CultureInfo("ru-RU")).DateTimeFormat;
ReportParameter p1 = new ReportParameter("last_name", selRow["last_name"].ToString());
ReportParameter p2 = new ReportParameter("first_name", selRow["first_name"].ToString());
ReportParameter p3 = new ReportParameter("patronymic", selRow["patronymic"].ToString());
ReportParameter p4 = new ReportParameter("birthdate", getValidDate(selRow["birthdate"]));
ReportParameter p5 = new ReportParameter("court", selRow["court"].ToString() + " " + getValidDate(selRow["crime_date"]));
ReportParameter p6 = new ReportParameter("article", selRow["article"].ToString());
ReportParameter p7 = new ReportParameter("period", selRow["period"].ToString());
ReportParameter p8 = new ReportParameter("period_start", getValidDate(selRow["period_start"]));
ReportParameter p9 = new ReportParameter("period_end", getValidDate(selRow["period_end"]));
ReportParameter p10 = new ReportParameter("period_light", getValidDate(selRow["period_light"]));
ReportParameter p11 = new ReportParameter("period_normal", getValidDate(selRow["period_normal"]));
ReportParameter p12 = new ReportParameter("period_kp", getValidDate(selRow["period_kp"]));
ReportParameter p13 = new ReportParameter("period_udo", getValidDate(selRow["period_udo"]));
SQLDBConnect sqlCon = new SQLDBConnect();
String nameName = sqlCon.GetValue("name", "SELECT * FROM spr_nation WHERE id = " + selRow["nation_id"].ToString());;
ReportParameter p14 = new ReportParameter("nation", nameName);
nameName = sqlCon.GetValue("name", "SELECT * FROM spr_mstatus WHERE id = " + selRow["mstatus_id"].ToString());
ReportParameter p15 = new ReportParameter("mstatus", nameName);
nameName = sqlCon.GetValue("name", "SELECT * FROM spr_edu WHERE id = " + selRow["edu_id"].ToString());
ReportParameter p16 = new ReportParameter("edu", nameName);
nameName = sqlCon.GetValue("name", "SELECT * FROM spr_profession WHERE id = " + selRow["profession_id"].ToString());
ReportParameter p17 = new ReportParameter("profession", nameName);
ReportParameter p18 = new ReportParameter("crime_description", (string)selRow["crime_description"].ToString());
ReportParameter p19 = new ReportParameter("med_description", (string)selRow["med_description"].ToString());
ReportParameter p20 = new ReportParameter("other", (string)selRow["other"].ToString());
ReportParameter p21 = new ReportParameter("result", (string)selRow["result"].ToString());
this.rvDIVR.LocalReport.SetParameters(new ReportParameter[] { p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16, p17, p18, p19, p20, p21 });
this.rvDIVR.ProcessingMode = ProcessingMode.Local;
this.rvDIVR.RefreshReport();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "ќшибка при построении отчета");
Close();
}
}