private void btSave_Click(object sender, EventArgs e)
{
if ((tbFacultyName.Text.Length == 0) || (tbFacultyShortName.Text.Length == 0))
{
MessageBox.Show("Одно из текстовых полей пусто");
}
else if (_Updating)
{
_DB_Connection.Update(DB_Table.FACULTIES,
new Dictionary <string, object> {
{ "name", tbFacultyName.Text }, { "short_name", tbFacultyShortName.Text }
},
new Dictionary <string, object> {
{ "short_name", name }
});
_Updating = false;
ShowHideControls(false);
UpdateTable();
tbFacultyName.Clear();
tbFacultyShortName.Clear();
}
else if (_DB_Connection.Select(DB_Table.FACULTIES, new string[] { "name" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("short_name", Relation.EQUAL, tbFacultyShortName.Text)
}).Count != 0)
{
MessageBox.Show("Факультет с таким сокращением уже существует");
}
else if (_DB_Connection.Select(DB_Table.FACULTIES, new string[] { "short_name" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("name", Relation.EQUAL, tbFacultyName.Text)
}).Count != 0)
{
MessageBox.Show("Факультет с таким названием уже существует");
}
else
{
_DB_Connection.Insert(DB_Table.FACULTIES,
new Dictionary <string, object> {
{ "name", tbFacultyName.Text }, { "short_name", tbFacultyShortName.Text }
});
ShowHideControls(false);
UpdateTable();
tbFacultyName.Clear();
tbFacultyShortName.Clear();
}
}
private void dgvDirections_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
if ((dgvDirections.CurrentRow.Cells[dgvDirections_Kafedra.Index].Value == null) || (dgvDirections.CurrentRow.Cells[dgvDirections_Kafedra.Index].Value.ToString() == ""))
{
_DB_Connection.Update(DB_Table.PROFILES, new Dictionary <string, object> {
{ "name", dgvDirections.CurrentRow.Cells[dgvDirections_Name.Index].Value.ToString() }
},
new Dictionary <string, object> {
{ "faculty_short_name", dgvDirections.CurrentRow.Cells[dgvDirections_FacultyName.Index].Value.ToString() },
{ "direction_id", (uint)dgvDirections.CurrentRow.Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.CurrentRow.Cells[dgvDirections_ShortName.Index].Value.ToString() }
});
}
else
{
_DB_Connection.Update(DB_Table.PROFILES, new Dictionary <string, object> {
{ "name", dgvDirections.CurrentRow.Cells[dgvDirections_Name.Index].Value.ToString()
+ "|" + dgvDirections.CurrentRow.Cells[dgvDirections_Kafedra.Index].Value.ToString() }
},
new Dictionary <string, object> {
{ "faculty_short_name", dgvDirections.CurrentRow.Cells[dgvDirections_FacultyName.Index].Value.ToString() },
{ "direction_id", (uint)dgvDirections.CurrentRow.Cells[dgvDirections_ID.Index].Value }, { "short_name", dgvDirections.CurrentRow.Cells[dgvDirections_ShortName.Index].Value.ToString() }
});
}
}
private void dataGridView_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex != -1)
{
_DB_Connection.Update(DB_Table.ENTRANTS_EXAMINATIONS_MARKS,
new Dictionary <string, object> {
{ dataGridView_Mark.DataPropertyName, dataGridView[e.ColumnIndex, e.RowIndex].Value }
},
new Dictionary <string, object>
{
{ dataGridView_UID.DataPropertyName, dataGridView[dataGridView_UID.Index, e.RowIndex].Value },
{ "examination_id", _ExaminationID }
});
}
}
private void UpdateBasic(MySql.Data.MySqlClient.MySqlTransaction transaction)
{
_DB_Connection.Update(DB_Table.ENTRANTS, new Dictionary <string, object>
{
{ "email", mtbEMail.Text },
{ "home_phone", tbHomePhone.Text },
{ "mobile_phone", tbMobilePhone.Text }
}, new Dictionary <string, object>
{
{ "id", _EntrantID }
}, transaction);
_DB_Connection.Update(DB_Table.APPLICATIONS, new Dictionary <string, object>
{
{ "edit_time", _EditingDateTime },
{ "language", cbForeignLanguage.SelectedItem.ToString() },
{ "comment", tbReturnAdress.Text.Trim() }
}, new Dictionary <string, object>
{
{ "id", _ApplicationID }
}, transaction);
}
private void btSave_Click(object sender, EventArgs e)
{
string[] fields = { "min_math_mark", "min_russian_mark", "min_physics_mark", "min_social_mark", "min_foreign_mark" };
object[] buf = _DB_Connection.Select(DB_Table.CONSTANTS, fields)[0];
Dictionary <string, object> values = new Dictionary <string, object>(fields.Length);
Dictionary <string, object> where = new Dictionary <string, object>(fields.Length);
for (byte i = 0; i < fields.Length; ++i)
{
values.Add(fields[i], dgvConstants[dgvConstants_Value.Index, i].Value);
where.Add(fields[i], buf[i]);
}
_DB_Connection.Update(DB_Table.CONSTANTS, values, where);
Close();
}
private void btSave_Click(object sender, EventArgs e)
{
if (!_UpdatingCode.HasValue)
{
_DB_Connection.Insert(DB_Table.TARGET_ORGANIZATIONS,
new Dictionary <string, object> {
{ "name", rtbOrganizationName.Text }
});
}
else
{
_DB_Connection.Update(DB_Table.TARGET_ORGANIZATIONS,
new Dictionary <string, object> {
{ "name", rtbOrganizationName.Text }
},
new Dictionary <string, object> {
{ "id", _UpdatingCode }
});
}
DialogResult = DialogResult.OK;
}
private void bSave_Click(object sender, EventArgs e)
{
foreach (DataGridViewRow row in dataGridView.Rows)
{
_DB_Connection.Update(
DB_Table.MASTERS_EXAMS_MARKS,
new Dictionary <string, object>
{
{ "date", row.Cells[dataGridView_Date.Index].Value },
{ "mark", row.Cells[dataGridView_Mark.Index].Value },
{ "bonus", row.Cells[dataGridView_Bonus.Index].Value }
},
new Dictionary <string, object>
{
{ "campaign_id", Classes.Settings.CurrentCampaignID },
{ "entrant_id", row.Cells[dataGridView_ID.Index].Value },
{ "faculty", row.Cells[dataGridView_Faculty.Index].Value },
{ "direction_id", row.Cells[dataGridView_Direction.Index].Value },
{ "profile_short_name", row.Cells[dataGridView_Profile.Index].Value }
});
}
SharedClasses.Utility.ShowChangesSavedMessage();
}
private void bImport_Click(object sender, EventArgs e)
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string[] subjects = new string[]
{
DB_Helper.SubjectMath,
DB_Helper.SubjectRus,
DB_Helper.SubjectPhis,
DB_Helper.SubjectObsh,
DB_Helper.SubjectForen,
"Английский язык",
"Немецкий язык",
"Французский язык"
};
Cursor.Current = Cursors.WaitCursor;
List <string[]> lines = new List <string[]>();
using (System.IO.StreamReader reader = new System.IO.StreamReader(openFileDialog.FileName, System.Text.Encoding.GetEncoding(1251)))
{
while (!reader.EndOfStream)
{
lines.Add(reader.ReadLine().Split('%'));
}
}
var importedResults = lines.Where(s => subjects.Contains(s[5])).Select(s =>
{
if (s[5] != "Русский язык" && s[5].Contains(" язык"))
{
s[5] = "Иностранный язык";
}
return(new
{
LastName = s[0],
FirstName = s[1],
MiddleName = s[2],
Series = s[3],
Number = s[4],
Subject = _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, s[5]),
Value = ushort.Parse(s[6]),
Year = ushort.Parse(s[7])
});
});
uint insertCount = 0;
uint updateCount = 0;
List <string[]> notFoundList = new List <string[]>();
List <EGE_Result> savedResults = GetEgeResults().ToList();
var identities = _DB_Connection.Select(DB_Table.APPLICATIONS, "id", "entrant_id").Join(
_DB_Connection.Select(DB_Table.ENTRANTS_VIEW, "id", "series", "number"),
k1 => k1[1],
k2 => k2[0],
(s1, s2) => new
{
ApplID = (uint)s1[0],
Series = s2[1] as string,
Number = s2[2].ToString()
});
try
{
foreach (var res in importedResults)
{
//Стоит ли проверять имена вообще?
IEnumerable <uint> applIDs = savedResults.Where(s =>
s.LastName.Equals(res.LastName, StringComparison.OrdinalIgnoreCase) &&
//s.FirstName.Equals(res.FirstName, StringComparison.OrdinalIgnoreCase) &&
//s.MiddleName.Equals(res.MiddleName, StringComparison.OrdinalIgnoreCase) &&
s.Series == res.Series &&
s.Number == res.Number
).GroupBy(k => k.ApplID, (k, g) => k).Concat(identities.Where(s => s.Series == res.Series && s.Number == res.Number).Select(s => s.ApplID)).Distinct();
if (applIDs.Count() == 0)
{
notFoundList.Add(new string[] { res.LastName, res.FirstName, res.MiddleName, res.Series, res.Number });
}
foreach (uint applID in applIDs)
{
EGE_Result foundResult = savedResults.SingleOrDefault(s => s.ApplID == applID && s.SubjectID == res.Subject);
if (foundResult != null)
{
if (!foundResult.Checked || foundResult.Value < res.Value)
{
_DB_Connection.Update(
DB_Table.APPLICATION_EGE_RESULTS,
new Dictionary <string, object>
{
{ "series", res.Series },
{ "number", res.Number },
{ "year", res.Year },
{ "value", res.Value },
{ "checked", true }
},
new Dictionary <string, object>
{
{ "application_id", applID },
{ "subject_dict_id", (uint)FIS_Dictionary.SUBJECTS },
{ "subject_id", res.Subject }
});
savedResults.Remove(foundResult);
savedResults.Add(new EGE_Result(
applID,
res.Series,
res.Number,
res.LastName,
//res.FirstName,
//res.MiddleName,
res.Subject,
res.Value,
true
));
updateCount++;
}
}
else
{
_DB_Connection.Insert(
DB_Table.APPLICATION_EGE_RESULTS,
new Dictionary <string, object>
{
{ "application_id", applID },
{ "subject_dict_id", (uint)FIS_Dictionary.SUBJECTS },
{ "subject_id", res.Subject },
{ "series", res.Series },
{ "number", res.Number },
{ "year", res.Year },
{ "value", res.Value },
{ "checked", true }
});
savedResults.Add(new EGE_Result(
applID,
res.Series,
res.Number,
res.LastName,
//res.FirstName,
//res.MiddleName,
res.Subject,
res.Value,
true
));
insertCount++;
}
}
}
}
finally
{
Cursor.Current = Cursors.Default;
MessageBox.Show("Новых результатов: " + insertCount +
"\nОбновлённых результатов: " + updateCount +
(notFoundList.Count != 0 ? "\nНе найдено соответсвий для:\n" + string.Join("\n", notFoundList.Select(s => string.Join(" ", s))) : ""), "Результаты загружены", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void bSave_Click(object sender, EventArgs e)
{
if (!AssureSave())
{
return;
}
Cursor.Current = Cursors.WaitCursor;
Dictionary <string, object> data = new Dictionary <string, object>
{
{ "subject_dict_id", (uint)FIS_Dictionary.SUBJECTS },
{ "subject_id", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, cbSubject.Text) },
{ "date", dtpDate.Value },
{ "reg_start_date", dtpRegStartDate.Value },
{ "reg_end_date", dtpRegEndDate.Value }
};
using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction())
{
uint id;
if (_ID.HasValue)
{
_DB_Connection.Update(DB_Table.EXAMINATIONS, data, new Dictionary <string, object> {
{ "id", _ID }
}, transaction);
id = _ID.Value;
}
else
{
id = _DB_Connection.Insert(DB_Table.EXAMINATIONS, data, transaction);
}
string[] fields = { "examination_id", "number", "capacity", "priority" };
List <object[]> oldList = _DB_Connection.Select(
DB_Table.EXAMINATIONS_AUDIENCES,
fields,
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("examination_id", Relation.EQUAL, id)
}
);
List <object[]> newList = new List <object[]>();
foreach (DataGridViewRow row in dataGridView.Rows)
{
if (!row.IsNewRow)
{
newList.Add(new object[] { id, row.Cells[0].Value, row.Cells[1].Value, row.Index });
}
}
_DB_Helper.UpdateData(DB_Table.EXAMINATIONS_AUDIENCES, oldList, newList, fields, new string[] { "examination_id", "number" }, transaction);
transaction.Commit();
}
Cursor.Current = Cursors.Default;
SharedClasses.Utility.ShowChangesSavedMessage();
DialogResult = DialogResult.OK;
}
public void UpdateDictionaries()
{
Dictionary <uint, string> fisDictionaries = FIS_Connector.GetDictionaries(_FIS_Address, _FIS_Login, _FIS_Password);
Dictionary <uint, string> dbDictionaries = _DB_Connection.Select(DB_Table.DICTIONARIES).ToDictionary(d1 => (uint)d1[0], d2 => d2[1].ToString());
string addedReport = "Добавлены справочники:";
ushort addedCount = 0;
string deletedReport = "";
foreach (var d in fisDictionaries)
{
if (d.Key != 10 && d.Key != 19)
{
var fisDictionaryItems = FIS_Connector.GetDictionaryItems(_FIS_Address, _FIS_Login, _FIS_Password, d.Key);
if (dbDictionaries.ContainsKey(d.Key))
{
if (d.Value == dbDictionaries[d.Key])
{
UpdateDictionaryItems((FIS_Dictionary)d.Key, d.Value, fisDictionaryItems);
}
else if (SharedClasses.Utility.ShowChoiceMessageWithConfirmation(
"В ФИС изменилось наименование справочника с кодом " + d.Key +
":\nC \"" + dbDictionaries[d.Key] + "\"\nна \"" + d.Value +
"\".\n\nОбновить наименование в БД?\nВ случае отказа изменения элементов этого справочника проверятся не будут.", //TODO Нужен мягкий знак "проверятся"?
"Действие"
))
{
_DB_Connection.Update(DB_Table.DICTIONARIES,
new Dictionary <string, object> {
{ "name", d.Value }
},
new Dictionary <string, object> {
{ "id", d.Key }
}
);
UpdateDictionaryItems((FIS_Dictionary)d.Key, d.Value, fisDictionaryItems);
}
}
else
{
_DB_Connection.Insert(DB_Table.DICTIONARIES,
new Dictionary <string, object> {
{ "id", d.Key }, { "name", d.Value }
}
);
foreach (var item in fisDictionaryItems)
{
_DB_Connection.Insert(DB_Table.DICTIONARIES_ITEMS,
new Dictionary <string, object> {
{ "dictionary_id", d.Key }, { "item_id", item.Key }, { "name", item.Value }
}
);
}
addedReport += "\n" + d.Key + " \"" + d.Value + "\"";
addedCount++;
}
}
}
foreach (var d in dbDictionaries)
{
if (!fisDictionaries.ContainsKey(d.Key))
{
deletedReport += d.Key + ", ";
}
}
if (addedCount == 0)
{
addedReport = "Новых справочников нет.";
}
else
{
addedReport += "\nВсего: " + addedCount;
}
if (deletedReport != "")
{
deletedReport = "\n\nВнимание: в ФИС отсутствуют справочники с ID: " + deletedReport.Remove(deletedReport.Length - 2);
}
MessageBox.Show(addedReport + deletedReport, "Обновление завершено", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void UpdateData(DB_Table table, List <object[]> oldList, List <object[]> newList, bool autoGeneratedKey, int keysCount, string[] fieldNames)
{
using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction())
{
foreach (var oldItem in oldList)
{
if (newList.Count == 0)
{
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], oldItem[i]);
}
_DB_Connection.Delete(table, keyAndValues, transaction);
}
else
{
bool keysMatch = true;
bool valuesMatch = true;
int index = -1;
do
{
index++;
object[] newItem = newList[index];
for (int i = 0; i < fieldNames.Length; i++)
{
if (i < keysCount)
{
if ((keysMatch) && (newItem[i].ToString() != oldItem[i].ToString()))
{
keysMatch = false;
}
}
else
if ((valuesMatch) && (newItem[i].ToString() != oldItem[i].ToString()))
{
valuesMatch = false;
}
}
}while ((index < newList.Count - 1) && (!((keysMatch) && (index == keysCount - 1))));
if (keysMatch && valuesMatch)
{
newList.RemoveAt(index);
}
else if (keysMatch && !valuesMatch)
{
Dictionary <string, object> columnsAndValues = new Dictionary <string, object>();
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newList[index][i]);
}
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], newList[index][i]);
}
_DB_Connection.Update(table, columnsAndValues, keyAndValues, transaction);
newList.RemoveAt(index);
}
else
{
Dictionary <string, object> keyAndValues = new Dictionary <string, object>();
for (int i = 0; i < keysCount; i++)
{
keyAndValues.Add(fieldNames[i], oldItem[i]);
}
_DB_Connection.Delete(table, keyAndValues, transaction);
}
}
}
if (newList.Count != 0)
{
foreach (var newItem in newList)
{
Dictionary <string, object> columnsAndValues = new Dictionary <string, object>();
if (autoGeneratedKey)
{
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
}
else
{
for (int i = 0; i < keysCount; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
for (int i = keysCount; i < fieldNames.Length; i++)
{
columnsAndValues.Add(fieldNames[i], newItem[i]);
}
}
_DB_Connection.Insert(table, columnsAndValues, transaction);
}
}
transaction.Commit();
}
}
private void bOK_Click(object sender, EventArgs e)
{
if (tbNumber.Text == "")
{
MessageBox.Show("Не заполнен номер протокола.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Cursor.Current = Cursors.WaitCursor;
EducationLevel eduLevel = new DB_Helper(_DB_Connection).GetCampaignType(Classes.Settings.CurrentCampaignID) == DB_Helper.CampaignType.MASTER ? EducationLevel.MASTER : EducationLevel.BACHELOR; //TODO
object[] buf = _DB_Connection.Select(
DB_Table.ORDERS,
new string[] { "type", "edu_form_id", "edu_source_id", "faculty_short_name" },
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("number", Relation.EQUAL, _Number)
}
)[0];
string type = buf[0].ToString();
uint eduForm = (uint)buf[1];
bool paid = (uint)buf[2] == 15; //TODO
string faculty = buf[3].ToString();
var applications = _DB_Connection.Select(
DB_Table.ORDERS_HAS_APPLICATIONS,
new string[] { "applications_id" },
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("orders_number", Relation.EQUAL, _Number)
}
).Join(
_DB_Connection.Select(DB_Table.APPLICATIONS, "id", "status"),
k1 => k1[0], k2 => k2[0], (s1, s2) => new { ID = (uint)s2[0], Status = s2[1].ToString() }
);
using (MySql.Data.MySqlClient.MySqlTransaction transaction = _DB_Connection.BeginTransaction())
{
if (type == "admission")
{
foreach (var appl in applications)
{
_DB_Connection.Update(
DB_Table.ORDERS_HAS_APPLICATIONS,
new Dictionary <string, object> {
{ "record_book_number", GetFreeRecordBookNumber(eduForm, paid, eduLevel) }
},
new Dictionary <string, object>
{
{ "orders_number", _Number },
{ "applications_id", appl.ID }
},
transaction
);
_DB_Connection.Update(
DB_Table.APPLICATIONS,
new Dictionary <string, object>
{
{ "status", _Statuses.Single(s1 => s1.Item2 == (_Statuses.Single(s2 => s2.Item1 == appl.Status).Item2 | (paid?ApplicationStatus.ADM_PAID:ApplicationStatus.ADM_BUDGET))).Item1 }//TODO !!!
},
new Dictionary <string, object> {
{ "id", appl.ID }
},
transaction
);
}
}
else if (type == "exception")
{
foreach (var appl in applications)
{
_DB_Connection.Update(
DB_Table.APPLICATIONS,
new Dictionary <string, object>
{
{ "status", _Statuses.Single(s1 => s1.Item2 == (_Statuses.Single(s2 => s2.Item1 == appl.Status).Item2 & ~(paid ? ApplicationStatus.ADM_PAID : ApplicationStatus.ADM_BUDGET))).Item1 } //TODO !!!
},
new Dictionary <string, object> {
{ "id", appl.ID }
},
transaction
);
}
}
else
{
ushort places = (ushort)_DB_Connection.Select(
DB_Table.CAMPAIGNS_FACULTIES_DATA,
new string[] { "hostel_places" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID),
new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, faculty)
})[0][0];
ushort occupied = (ushort)(_DB_Connection.Select(
DB_Table.ORDERS,
new string[] { "number" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("type", Relation.EQUAL, "hostel"),
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID),
new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, faculty),
new Tuple <string, Relation, object>("protocol_number", Relation.NOT_EQUAL, null)
}).Join(
_DB_Connection.Select(DB_Table.ORDERS_HAS_APPLICATIONS),
k1 => k1[0],
k2 => k2[0],
(s1, s2) => s2[1]
).Count() + applications.Count());
if (places < occupied && MessageBox.Show(
"Число мест в общежитии, выделенных для факультета, меньше суммарного числа абитуриентов, назначенных на них по приказам. Продолжить регистрацию?",
"Внимание",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning
) == DialogResult.No)
{
return;
}
}
_DB_Connection.Update(
DB_Table.ORDERS,
new Dictionary <string, object> {
{ "protocol_number", ushort.Parse(tbNumber.Text) }, { "protocol_date", dtpDate.Value }
},
new Dictionary <string, object> {
{ "number", _Number }
},
transaction
);
transaction.Commit();
}
Cursor.Current = Cursors.Default;
DialogResult = DialogResult.OK;
}