private void bExport_Click(object sender, EventArgs e)
{
if (saveFileDialog.ShowDialog() == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
var identities = DB_Queries.GetDocuments(
_DB_Connection,
_DB_Connection.Select(
DB_Table.APPLICATIONS,
new string[] { "id" },
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID)
}
).Select(s => (uint)s[0])).Where(s => s.Type == "identity").Join(
_DB_Connection.Select(
DB_Table.IDENTITY_DOCS_ADDITIONAL_DATA,
new string[] { "document_id", "last_name" /*, "first_name", "middle_name"*/ },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("type_id", Relation.EQUAL, _DB_Helper.GetDictionaryItemID(FIS_Dictionary.IDENTITY_DOC_TYPE, DB_Helper.PassportName))
}),
k1 => k1.ID,
k2 => k2[0],
(s1, s2) => Tuple.Create(s2[1].ToString(), /* s2[2].ToString(), s2[3] as string,*/ s1.Series, s1.Number));
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(saveFileDialog.FileName, false, System.Text.Encoding.GetEncoding(1251)))
{
foreach (var appl in GetEgeResults().Select(s =>
Tuple.Create(s.LastName /*, s.FirstName, s.MiddleName*/, s.Series, s.Number)
).Concat(identities).Distinct())
{
//writer.WriteLine(appl.Item1 + "%" + appl.Item2 + "%" + appl.Item3 + "%" + appl.Item4 + "%" + appl.Item5);
writer.WriteLine(appl.Item1 + "%%%" + appl.Item2 + "%" + appl.Item3);
}
}
Cursor.Current = Cursors.Default;
}
}
public MainForm()
{
InitializeComponent();
//_DB_Connection = new DB_Connector("server = localhost; port = 3306; database = pk_db;"/*Properties.Settings.Default.pk_db_CS*/, "administrator", "adm1234");
_DB_Connection = new DB_Connector("server = serv-priem; port = 3306; database = pk_db;" /*Properties.Settings.Default.pk_db_CS*/, "administrator", "adm1234");
_DB_Helper = new DB_Helper(_DB_Connection);
Dictionary <uint, string> campaigns = new Dictionary <uint, string>();
foreach (object[] campaign in _DB_Connection.Select(DB_Table.CAMPAIGNS, new string[] { "id", "name" }))
{
if (_DB_Helper.GetCampaignType((uint)campaign[0]) == DB_Helper.CampaignType.BACHELOR_SPECIALIST)
{
campaigns.Add((uint)campaign[0], campaign[1].ToString());
}
}
cbCampaigns.DataSource = campaigns.ToList();
cbCampaigns.ValueMember = "Key";
cbCampaigns.DisplayMember = "Value";
cbAdress.SelectedIndex = 0;
cbAdress.Enabled = cbPost.Checked;
cbUnits.SelectedIndex = 1;
for (int i = 1; i <= 60; i++)
{
cbInterval.Items.Add(i);
}
cbInterval.SelectedItem = 15;
_SubjectsCodes = new Dictionary <uint, string>
{
{ _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectMath), "Math" },
{ _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectPhis), "Phis" },
{ _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectRus), "Rus" },
{ _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectObsh), "Obsh" },
{ _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectForen), "Foren" }
};
}
private void toolStrip_Distribute_Click(object sender, EventArgs e)
{
if (DB_Queries.ExaminationHasMarks(_DB_Connection, SelectedExamID))
{
MessageBox.Show("В экзамен уже включены абитуриенты. При повторном распределении они не будут удалены.");
}
Cursor.Current = Cursors.WaitCursor;
var applications = _DB_Connection.Select(
DB_Table.APPLICATIONS,
new string[] { "id", "registration_time", "entrant_id" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("passing_examinations", Relation.EQUAL, true),
new Tuple <string, Relation, object>("status", Relation.EQUAL, "new")
}).Where(a => (DateTime)a[1] >= (DateTime)dataGridView.SelectedRows[0].Cells[dataGridView_RegStartDate.Index].Value &&
(DateTime)a[1] < (DateTime)dataGridView.SelectedRows[0].Cells[dataGridView_RegEndDate.Index].Value + new TimeSpan(1, 0, 0, 0)
).Select(s => new { ApplID = (uint)s[0], EntrID = (uint)s[2] });
uint subjectID = _DB_Helper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, dataGridView.SelectedRows[0].Cells[dataGridView_Subject.Index].Value.ToString());
IEnumerable <uint> excludedAppls = DB_Queries.GetMarks(
_DB_Connection,
applications.Select(s => s.ApplID),
Classes.Settings.CurrentCampaignID
).Where(s =>
s.FromExamDate.HasValue &&
(s.SubjID == subjectID ||
s.Value < _DB_Helper.GetMinMark(s.SubjID)
)).Select(s => s.ApplID);
applications = applications.Where(s => !excludedAppls.Contains(s.ApplID));
var applsDirections = _DB_Connection.Select(
DB_Table.APPLICATIONS_ENTRANCES,
"application_id", "faculty_short_name", "direction_id"
);
var subjectDirections = _DB_Connection.Select(
DB_Table.ENTRANCE_TESTS,
new string[] { "direction_faculty", "direction_id" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID),
new Tuple <string, Relation, object>("subject_id", Relation.EQUAL, subjectID)
});
var applsSubjects = applsDirections.Join(
subjectDirections,
k1 => Tuple.Create(k1[1], k1[2]),
k2 => Tuple.Create(k2[0], k2[1]),
(s1, s2) => (uint)s1[0]
).Distinct();
applications = applications.Join(
applsSubjects,
k1 => k1.ApplID,
k2 => k2,
(s1, s2) => s1
);
if (applications.Count() == 0)
{
MessageBox.Show("Ни один абитуриент не попал в экзамен по условиям фильтрации.", "Предупреждение", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
var entrantsIDs = _DB_Connection.Select(DB_Table.ENTRANTS, "id").Join(
applications,
en => en[0],
a => a.EntrID,
(s1, s2) => s2.EntrID
).Distinct();//TODO Нужно?
foreach (object entrID in entrantsIDs)
{
_DB_Connection.InsertOnDuplicateUpdate(
DB_Table.ENTRANTS_EXAMINATIONS_MARKS,
new Dictionary <string, object> {
{ "entrant_id", entrID }, { "examination_id", SelectedExamID }
}
);
}
ToggleButtons();
Cursor.Current = Cursors.Default;
}
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 OrderEdit(DB_Connector connection, string number)
{
_DB_Connection = connection;
_DB_Helper = new DB_Helper(_DB_Connection);
_EditNumber = number;
#region Components
InitializeComponent();
cbType.DisplayMember = "Item2";
cbType.ValueMember = "Item1";
cbType.DataSource = new List <Tuple <string, string> >
{
Tuple.Create("admission", "Зачисление"),
Tuple.Create("exception", "Отчисление"),
Tuple.Create("hostel", "Выделение мест в общежитии")
};
rbBudget.Tag = new RB_Tag("budget", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceB));
rbPaid.Tag = new RB_Tag(null, _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceP));
rbTarget.Tag = new RB_Tag(null, _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceT));
rbQuota.Tag = new RB_Tag("quota", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceQ));
rbO.Tag = new RB_Tag("o", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormO));
rbOZ.Tag = new RB_Tag("oz", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormOZ));
rbZ.Tag = new RB_Tag("z", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormZ));
if (_EditNumber != null)
{
dtpDate.Tag = true;
}
else
{
dtpDate.Tag = false;
}
#endregion
cbFDP.ValueMember = "Value";
_CampaignType = _DB_Helper.GetCampaignType(Classes.Settings.CurrentCampaignID);
if (_CampaignType != DB_Helper.CampaignType.BACHELOR_SPECIALIST)
{
dataGridView_Status.Visible = false;
dataGridView_MFR.Visible = false;
dataGridView_MOR.Visible = false;
dataGridView_ROI.Visible = false;
dataGridView_Math.Visible = false;
dataGridView_Physics.Visible = false;
dataGridView_Russian.Visible = false;
dataGridView_Social.Visible = false;
dataGridView_Foreign.Visible = false;
if (_CampaignType == DB_Helper.CampaignType.MASTER)
{
dataGridView_Sum.Visible = true;
dataGridView_Exam.Visible = true;
dataGridView_IndAch.Visible = true;
//dataGridView_Honors.Visible = true;
lFDP.Text = "Программа:";
}
}
if (_EditNumber != null)
{
object[] order = _DB_Connection.Select(
DB_Table.ORDERS,
new string[] { "type", "date", "edu_form_id", "edu_source_id", "faculty_short_name", /**/ "direction_id" /**/, "profile_short_name", "protocol_number" },
private uint SaveBasic(MySql.Data.MySqlClient.MySqlTransaction transaction)
{
List <object[]> passportFound = _DB_Connection.Select(DB_Table.DOCUMENTS, new string[] { "id" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("type", Relation.EQUAL, "identity"),
new Tuple <string, Relation, object>("series", Relation.EQUAL, tbIDDocSeries.Text),
new Tuple <string, Relation, object>("number", Relation.EQUAL, tbIDDocNumber.Text)
});
if (passportFound.Count > 0)
{
_EntrantID = (uint)_DB_Connection.Select(DB_Table.APPLICATIONS, new string[] { "entrant_id" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("id", Relation.EQUAL, (uint)_DB_Connection.Select(DB_Table._APPLICATIONS_HAS_DOCUMENTS,
new string[] { "applications_id" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("documents_id", Relation.EQUAL, (uint)passportFound[0][0])
})[0][0])
})[0][0];
}
else
{
char[] passwordChars = { 'a', 'b', 'c', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
int passwordLength = 6;
string password = "";
Random rand = new Random();
for (int i = 0; i < passwordLength; i++)
{
password += passwordChars[rand.Next(passwordChars.Length)];
}
_EntrantID = _DB_Connection.Insert(DB_Table.ENTRANTS, new Dictionary <string, object>
{
{ "email", mtbEMail.Text },
{ "home_phone", tbHomePhone.Text },
{ "mobile_phone", tbMobilePhone.Text },
{ "personal_password", password }
}, transaction);
}
uint applID = _DB_Connection.Insert(DB_Table.APPLICATIONS, new Dictionary <string, object>
{
{ "entrant_id", _EntrantID.Value },
{ "registration_time", DateTime.Now },
{ "needs_hostel", false },
{ "registrator_login", _RegistratorLogin },
{ "campaign_id", _CurrCampainID },
{ "language", cbForeignLanguage.SelectedItem.ToString() },
{ "priority_right", false },
{ "special_conditions", false },
{ "master_appl", false },
{ "first_high_edu", true },
{ "comment", tbReturnAdress.Text.Trim() }
}, transaction);
uint idDocUid = _DB_Connection.Insert(DB_Table.DOCUMENTS, new Dictionary <string, object>
{
{ "type", "identity" },
{ "series", tbIDDocSeries.Text.Trim() },
{ "number", tbIDDocNumber.Text.Trim() },
{ "date", dtpIDDocDate.Value },
{ "organization", tbIssuedBy.Text.Trim() }
}, transaction);
_DB_Connection.Insert(DB_Table._APPLICATIONS_HAS_DOCUMENTS, new Dictionary <string, object>
{
{ "applications_id", applID },
{ "documents_id", idDocUid }
}, transaction);
_DB_Connection.Insert(DB_Table.IDENTITY_DOCS_ADDITIONAL_DATA, new Dictionary <string, object>
{
{ "document_id", idDocUid },
{ "last_name", tbLastName.Text.Trim() },
{ "first_name", tbFirstName.Text.Trim() },
{ "middle_name", tbMiddleName.Text.Trim() },
{ "gender_dict_id", (uint)FIS_Dictionary.GENDER },
{ "gender_id", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.GENDER, cbSex.SelectedItem.ToString()) },
{ "subdivision_code", mtbSubdivisionCode.MaskFull? mtbSubdivisionCode.Text: null },
{ "type_dict_id", (uint)FIS_Dictionary.IDENTITY_DOC_TYPE },
{ "type_id", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.IDENTITY_DOC_TYPE, cbIDDocType.SelectedItem.ToString()) },
{ "nationality_dict_id", (uint)FIS_Dictionary.COUNTRY },
{ "nationality_id", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.COUNTRY, cbNationality.SelectedItem.ToString()) },
{ "birth_date", dtpDateOfBirth.Value },
{ "birth_place", tbPlaceOfBirth.Text.Trim() },
{ "reg_region", cbRegion.Text },
{ "reg_district", cbDistrict.Text },
{ "reg_town", cbTown.Text },
{ "reg_street", cbStreet.Text },
{ "reg_house", cbHouse.Text },
{ "reg_index", tbPostcode.Text },
{ "reg_flat", tbAppartment.Text }
}, transaction);
if (cbPhotos.Checked)
{
uint otherDocID = _DB_Connection.Insert(DB_Table.DOCUMENTS, new Dictionary <string, object>
{
{ "type", "photos" }
}, transaction);
_DB_Connection.Insert(DB_Table._APPLICATIONS_HAS_DOCUMENTS, new Dictionary <string, object>
{
{ "applications_id", applID },
{ "documents_id", otherDocID }
}, transaction);
}
return(applID);
}
private void SetEntrances(uint appID, XElement abitur)
{
List <object[]> entrances = _DB_Connection.Select(DB_Table.APPLICATIONS_ENTRANCES, new string[] { "edu_form_id", "direction_id", "profile_short_name",
"is_agreed_date", "edu_source_id", "faculty_short_name" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("application_id", Relation.EQUAL, appID)
});
int agreedCount = 0;
uint lastAgreedDir = 0;
DateTime lastAgreedDate = DateTime.MinValue;
foreach (object[] entrance in entrances)
{
DateTime?date = entrance[3] as DateTime?;
if (date != null)
{
agreedCount++;
if ((DateTime)entrance[3] > lastAgreedDate)
{
lastAgreedDate = (DateTime)entrance[3];
lastAgreedDir = (uint)entrance[1];
}
}
}
List <XElement> appls = new List <XElement>();
foreach (object[] entrance in entrances)
{
XElement appl = new XElement("Application");
if ((uint)entrance[0] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormO))
{
appl.Add(new XElement("FormOfEducation", 1));
}
else if ((uint)entrance[0] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormOZ))
{
appl.Add(new XElement("FormOfEducation", 2));
}
else if ((uint)entrance[0] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_FORM, DB_Helper.EduFormZ))
{
appl.Add(new XElement("FormOfEducation", 3));
}
if ((uint)entrance[4] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceB))
{
appl.Add(new XElement("Faculty", entrance[5].ToString()));
appl.Add(new XElement("Direction", (uint)entrance[1]));
appl.Add(new XElement("Profile", ""));
appl.Add(new XElement("Condition", 1));
}
else if ((uint)entrance[4] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceT))
{
appl.Add(new XElement("Faculty", entrance[5].ToString()));
appl.Add(new XElement("Direction", (uint)entrance[1]));
appl.Add(new XElement("Profile", ""));
appl.Add(new XElement("Condition", 2));
abitur.Element("Documents").SetElementValue("ReferralPK", 1);
}
else if ((uint)entrance[4] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceQ))
{
appl.Add(new XElement("Faculty", entrance[5].ToString()));
appl.Add(new XElement("Direction", (uint)entrance[1]));
appl.Add(new XElement("Profile", ""));
appl.Add(new XElement("Condition", 3));
}
else if ((uint)entrance[4] == _DB_Helper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceP))
{
appl.Add(new XElement("Faculty", entrance[5].ToString()));
appl.Add(new XElement("Direction", 0));
appl.Add(new XElement("Profile", entrance[2].ToString()));
appl.Add(new XElement("Condition", 4));
}
appl.Add(new XElement("ApplicationOfConsent", 0));
DateTime?date = entrance[3] as DateTime?;
if (date != null)
{
if (agreedCount == 1)
{
appl.SetElementValue("ApplicationOfConsent", 1);
}
else if (lastAgreedDir == (uint)entrance[1])
{
appl.SetElementValue("ApplicationOfConsent", 2);
}
else
{
appl.SetElementValue("ApplicationOfConsent", 1);
}
}
appls.Add(appl);
}
foreach (XElement appl in appls)
{
abitur.Element("Applications").Add(appl);
}
}
public void UpdateOlympicsDictionary()
{
Dictionary <uint, FIS_Olympic_TEMP> fisDictionaryItems = FIS_Connector.GetOlympicsDictionaryItems(
_FIS_Address,
_FIS_Login,
_FIS_Password,
_DB_Helper.GetDictionaryItemID(FIS_Dictionary.OLYMPICS_PROFILES, "физика"),
_DB_Helper.GetDictionaryItemID(FIS_Dictionary.OLYMPICS_PROFILES, "математика")
);
Dictionary <uint, FIS_Olympic_TEMP> dbDictionaryItems = _DB_Helper.GetOlympicsDictionaryItems();
string addedReport = "В справочник №" + 19 + " \"" + "Олимпиады" + "\" добавлены элементы:";
ushort addedCount = 0;
string deletedReport = "";
foreach (var olymp in fisDictionaryItems)
{
if (dbDictionaryItems.ContainsKey(olymp.Key))
{
if (olymp.Value.Year != dbDictionaryItems[olymp.Key].Year && SharedClasses.Utility.ShowChoiceMessageWithConfirmation(
"В ФИС изменился год олимпиады " + olymp.Key + " \"" + dbDictionaryItems[olymp.Key].Name + "\""
+ ":\nC \"" + dbDictionaryItems[olymp.Key].Year + "\"\nна \"" + olymp.Value.Year +
"\".\n\nОбновить значение в БД?",
"Действие"
))
{
_DB_Connection.Update(DB_Table.DICTIONARY_19_ITEMS,
new Dictionary <string, object> {
{ "year", olymp.Value.Year }
},
new Dictionary <string, object> {
{ "olympic_id", olymp.Key }
}
);
}
if (olymp.Value.Number != dbDictionaryItems[olymp.Key].Number && SharedClasses.Utility.ShowChoiceMessageWithConfirmation(
"В ФИС изменился номер олимпиады " + olymp.Key + " \"" + dbDictionaryItems[olymp.Key].Name + "\""
+ ":\nC \"" + dbDictionaryItems[olymp.Key].Number + "\"\nна \"" + olymp.Value.Number +
"\".\n\nОбновить значение в БД?",
"Действие"
))
{
_DB_Connection.Update(DB_Table.DICTIONARY_19_ITEMS,
new Dictionary <string, object> {
{ "olympic_number", olymp.Value.Number }
},
new Dictionary <string, object> {
{ "olympic_id", olymp.Key }
}
);
}
if (olymp.Value.Name != dbDictionaryItems[olymp.Key].Name && SharedClasses.Utility.ShowChoiceMessageWithConfirmation(
"В ФИС изменилось имя олимпиады с кодом "
+ olymp.Key + ":\nC \"" + dbDictionaryItems[olymp.Key].Name + "\"\nна \"" + olymp.Value.Name +
"\".\n\nОбновить значение в БД?",
"Действие"
))
{
_DB_Connection.Update(DB_Table.DICTIONARY_19_ITEMS,
new Dictionary <string, object> {
{ "olympic_name", olymp.Value.Name }
},
new Dictionary <string, object> {
{ "olympic_id", olymp.Key }
}
);
}
foreach (var prof in olymp.Value.Profiles)
{
if (dbDictionaryItems[olymp.Key].Profiles.ContainsKey(prof.Key))
{
if (prof.Value.LevelID != dbDictionaryItems[olymp.Key].Profiles[prof.Key].LevelID && SharedClasses.Utility.ShowChoiceMessageWithConfirmation(
"В ФИС изменился код уровня для профиля с кодом " + prof.Key.Item2 + " олимпиады " + olymp.Key
+ " \"" + dbDictionaryItems[olymp.Key].Name + "\"" + ":\nC \"" + dbDictionaryItems[olymp.Key].Profiles[prof.Key].LevelID + "\"\nна \"" + prof.Value.LevelID +
"\".\n\nОбновить значение в БД?",
"Действие"
))
{
_DB_Connection.Update(DB_Table.DICTIONARY_OLYMPIC_PROFILES,
new Dictionary <string, object> {
{ "level_id", prof.Value.LevelID }
},
new Dictionary <string, object>
{
{ "olympic_id", olymp.Key },
{ "profile_dict_id", prof.Key.Item1 },
{ "profile_id", prof.Key.Item2 }
}
);
}
foreach (System.Tuple <uint, uint> subj in prof.Value.Subjects)
{
if (!dbDictionaryItems[olymp.Key].Profiles[prof.Key].Subjects.Contains(subj))
{
_DB_Connection.Insert(DB_Table._DICTIONARY_OLYMPIC_PROFILES_HAS_DICTIONARIES_ITEMS,
new Dictionary <string, object>
{
{ "dictionary_olympic_profiles_olympic_id", olymp.Key },
{ "dictionary_olympic_profiles_profile_dict_id", prof.Key.Item1 },
{ "dictionary_olympic_profiles_profile_id", prof.Key.Item2 },
{ "dictionaries_items_dictionary_id", subj.Item1 },
{ "dictionaries_items_item_id", subj.Item2 }
}
);
}
}
}
else
{
InsertProfileWithSubjects(olymp, prof);
}
}
}
else
{
_DB_Connection.Insert(DB_Table.DICTIONARY_19_ITEMS,
new Dictionary <string, object>
{
{ "olympic_id", olymp.Key },
{ "year", olymp.Value.Year },
{ "olympic_number", olymp.Value.Number },
{ "olympic_name", olymp.Value.Name }
}
);
foreach (var prof in olymp.Value.Profiles)
{
InsertProfileWithSubjects(olymp, prof);
}
addedReport += "\n" + olymp.Key;
addedCount++;
}
}
foreach (var olymp in dbDictionaryItems)
{
if (!fisDictionaryItems.ContainsKey(olymp.Key))
{
deletedReport += "олимпиада - " + olymp.Key + ", ";
}
else
{
foreach (var prof in olymp.Value.Profiles)
{
if (!fisDictionaryItems[olymp.Key].Profiles.ContainsKey(prof.Key))
{
deletedReport += "олимпиада - " + olymp.Key + " -> профиль - " + prof.Key + ", ";
}
else
{
foreach (System.Tuple <uint, uint> subj in prof.Value.Subjects)
{
if (!fisDictionaryItems[olymp.Key].Profiles[prof.Key].Subjects.Contains(subj))
{
deletedReport += "олимпиада - " + olymp.Key + " -> профиль - " + prof.Key + " -> дисциплина - " + subj.Item2 + ", ";
}
}
}
}
}
}
if (addedCount == 0)
{
addedReport = "Новых олимпиад нет.";
}
else
{
addedReport += "\nВсего: " + addedCount;
}
if (deletedReport != "")
{
deletedReport = "\n\nВнимание: в ФИС отсутствуют объекты с ID: " + deletedReport.Remove(deletedReport.Length - 2);
}
ShowUpdateMessage(addedReport);
}
private void btAdd_Click(object sender, EventArgs e)
{
if ((cbAchievementType.SelectedIndex == -1) || (tbAchievementName.Text == "") || (tbMaxValue.Text == ""))
{
MessageBox.Show("Все поля должны быть заполнены");
}
else
{
bool found = false;
foreach (var record in _DB_Connection.Select(DB_Table.INSTITUTION_ACHIEVEMENTS, new string[] { "name" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("name", Relation.EQUAL, tbAchievementName.Text)
}))
{
MessageBox.Show("Достижение с таким наименованием уже существует");
found = true;
}
if (!found)
{
_DB_Connection.Insert(DB_Table.INSTITUTION_ACHIEVEMENTS, new Dictionary <string, object> {
{ "name", tbAchievementName.Text },
{ "category_dict_id", (uint)FIS_Dictionary.IND_ACH_CATEGORIES }, { "category_id", _DB_Helper.GetDictionaryItemID(FIS_Dictionary.IND_ACH_CATEGORIES, cbAchievementType.SelectedValue.ToString()) },
{ "value", tbMaxValue.Text }, { "campaign_id", _LoadedCampaign }
});
}
}
cbAchievementType.Enabled = false;
tbAchievementName.Enabled = false;
tbMaxValue.Enabled = false;
btAdd.Enabled = false;
label2.Enabled = false;
label4.Enabled = false;
label5.Enabled = false;
}
private void bPrint_Click(object sender, EventArgs e)
{
if (!cbActs.Checked && !cbReceipts.Checked && !cbExamSheets.Checked)
{
MessageBox.Show("Не отмечена информация к печати.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Cursor.Current = Cursors.WaitCursor;
IEnumerable <string> ordersNumbers;
if (rbDate.Checked)
{
ordersNumbers = _DB_Connection.Select(
DB_Table.ORDERS,
new string[] { "number" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("date", Relation.GREATER_EQUAL, dtpStart.Value.Date),
new Tuple <string, Relation, object>("date", Relation.LESS, dtpEnd.Value.Date.AddDays(1))
}).Select(s => s[0].ToString());
}
else
{
ordersNumbers = new string[] { tbNumber.Text }
};
DB_Helper dbHelper = new DB_Helper(_DB_Connection);
var orders = ordersNumbers.Join(
_DB_Connection.Select(
DB_Table.ORDERS,//TODO убрать лишние поля
new string[] { "number", "type", "date", "protocol_number", "protocol_date", "edu_form_id", "edu_source_id", "faculty_short_name", "direction_id", "profile_short_name", "campaign_id" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID),
new Tuple <string, Relation, object>("type", Relation.EQUAL, "admission"),
new Tuple <string, Relation, object>("protocol_number", Relation.NOT_EQUAL, null)
}),
k1 => k1,
k2 => k2[0],
(s1, s2) => new
{
Number = s1,
Date = (DateTime)s2[2],
ProtocolNumber = (ushort)s2[3],
ProtocolDate = (DateTime)s2[4],
EduForm = (uint)s2[5],
EduSource = (uint)s2[6],
Faculty = s2[7].ToString(),
Direction = (uint)s2[8],
Profile = s2[9] as string,
Master = dbHelper.GetCampaignType((uint)s2[10]) == DB_Helper.CampaignType.MASTER
});
if (orders.Count() == 0)
{
MessageBox.Show("Не найдено соответствующих приказов.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
Dictionary <uint, string> forms = new Dictionary <uint, string>
{
{ 11, "очной формы" },
{ 12, "очно-заочной (вечерней) формы" },
{ 10, "заочной формы" }
};
uint mathID = dbHelper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectMath);
uint physID = dbHelper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectPhis);
uint rusID = dbHelper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectRus);
uint socID = dbHelper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectObsh);
uint forID = dbHelper.GetDictionaryItemID(FIS_Dictionary.SUBJECTS, DB_Helper.SubjectForen);
List <Classes.DocumentCreator.DocumentParameters> actsDocs = new List <Classes.DocumentCreator.DocumentParameters>();
List <Classes.DocumentCreator.DocumentParameters> receiptsDocs = new List <Classes.DocumentCreator.DocumentParameters>();
List <Classes.DocumentCreator.DocumentParameters> sheetsDocs = new List <Classes.DocumentCreator.DocumentParameters>();
foreach (var order in orders)
{
var applications = _DB_Connection.Select(
DB_Table.ORDERS_HAS_APPLICATIONS,
new string[] { "applications_id", "record_book_number" },
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("orders_number", Relation.EQUAL, order.Number)
}
).Join(
_DB_Connection.Select(DB_Table.APPLICATIONS, "id", "entrant_id", "status"),
k1 => k1[0],
k2 => k2[0],
(s1, s2) => new { ApplID = (uint)s2[0], EntrID = (uint)s2[1], RecordBook = s1[1].ToString() }
).Join(
_DB_Connection.Select(DB_Table.ENTRANTS_VIEW, "id", "last_name", "first_name", "middle_name"),
k1 => k1.EntrID,
k2 => k2[0],
(s1, s2) => new { s1.ApplID, s1.EntrID, s1.RecordBook, LastName = s2[1].ToString(), FirstName = s2[2].ToString(), MiddleName = s2[3] as string }
);
List <Tuple <string, Relation, object> > whereExpr = new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("type", Relation.EQUAL, "exception"),
new Tuple <string, Relation, object>("protocol_number", Relation.NOT_EQUAL, null),
new Tuple <string, Relation, object>("edu_form_id", Relation.EQUAL, order.EduForm),
new Tuple <string, Relation, object>("edu_source_id", Relation.EQUAL, order.EduSource),
new Tuple <string, Relation, object>("faculty_short_name", Relation.EQUAL, order.Faculty),
new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, order.Direction)
};
if (order.Profile != null)
{
whereExpr.Add(new Tuple <string, Relation, object>("profile_short_name", Relation.EQUAL, order.Profile));
}
applications = applications.Where(s =>
{
IEnumerable <DateTime> admDates = _DB_Connection.Select(
DB_Table.ORDERS_HAS_APPLICATIONS,
new string[] { "orders_number" },
new List <Tuple <string, Relation, object> > {
new Tuple <string, Relation, object>("applications_id", Relation.EQUAL, s.ApplID)
}
).Join(
_DB_Connection.Select(DB_Table.ORDERS, new string[] { "number", "date" },
whereExpr
),
k1 => k1[0],
k2 => k2[0],
(s1, s2) => (DateTime)s2[1]
);
return(!admDates.Any(d => d > order.Date));
});
if (applications.Count() == 0)
{
continue;
}
if (dbHelper.GetCampaignType(Classes.Settings.CurrentCampaignID) == DB_Helper.CampaignType.MASTER)
{
var table = applications.Join(
_DB_Connection.Select(
DB_Table.MASTERS_EXAMS_MARKS,
new string[] { "entrant_id", "mark", "bonus" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID),
new Tuple <string, Relation, object>("faculty", Relation.EQUAL, order.Faculty),
new Tuple <string, Relation, object>("direction_id", Relation.EQUAL, order.Direction),
new Tuple <string, Relation, object>("profile_short_name", Relation.EQUAL, order.Profile),
}),
k1 => k1.EntrID,
k2 => k2[0],
(s1, s2) => new { s1.ApplID, s1.LastName, s1.FirstName, s1.MiddleName, s1.RecordBook, Mark = (short)s2[1] + (ushort)s2[2] }
);
//Tuple<string, string> dirNameCode = dbHelper.GetDirectionNameAndCode(order.Direction);
//string dirSocr = dbHelper.GetDirectionShortName(order.Faculty, order.Direction);
//if (cbActs.Checked)
// actsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
// Classes.Settings.DocumentsTemplatesPath + "Act.xml",
// null,
// null,
// new string[]
// {
// DateTime.Now.ToShortDateString(),
// order.Date.Year.ToString(),
// forms[order.EduForm] + " обучения" +
// (order.EduSource == dbHelper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceP) ? " по договорам с оплатой стоимости обучения" : ""),
// order.Faculty,
// dirNameCode.Item2,
// dirNameCode.Item1,
// order.Master?"Магистерская программа: " :(order.Profile != null ?(dirNameCode.Item2.Split('.')[1]=="05"?"Специализация": "Профиль")+": " : ""),
// order.Profile != null ? order.Profile + " - " + DB_Queries.GetProfileName(_DB_Connection,order.Faculty,order.Direction,order.Profile).Split('|')[0] : ""
// },
// new IEnumerable<string[]>[] { table.Select(s=>new
// {
// Name = s.LastName + " " + s.FirstName + " " + s.MiddleName,
// s.RecordBook
// }).OrderBy(s => s.Name).Select(s => new string[] { s.Name, s.RecordBook }) }));
foreach (var appl in table)
{
//if (cbReceipts.Checked)
// receiptsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
// Classes.Settings.DocumentsTemplatesPath + "AdmReceipt.xml",
// null,
// null,
// new string[]
// {
// order.Number,
// order.Date.ToShortDateString(),
// (appl.LastName+" "+appl.FirstName[0]+"."+(appl.MiddleName.Length!=0?appl.MiddleName[0].ToString()+".":"")).ToUpper(),
// order.Faculty,
// order.Profile != null ?order.Profile:dirSocr
// },
// null
// ));
if (cbExamSheets.Checked)
{
sheetsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
Classes.Settings.DocumentsTemplatesPath + "ExamSheetM.xml",
null,
null,
new string[]
{
appl.Mark.ToString(),
dbHelper.GetDictionaryItemName(FIS_Dictionary.EDU_FORM, order.EduForm),
appl.ApplID.ToString(),
appl.LastName.ToUpper(),
appl.FirstName.ToUpper(),
appl.MiddleName.ToUpper(),
order.Profile
},
null
));
}
}
}
else
{
IEnumerable <DB_Queries.Mark> marks = DB_Queries.GetMarks(_DB_Connection, applications.Select(s => s.ApplID), Classes.Settings.CurrentCampaignID);
var table = applications.Join(
marks.GroupBy(
k => Tuple.Create(k.ApplID, k.SubjID),
(k, g) => new { g.First().ApplID, Mark = g.First(s => s.Value == g.Max(m => m.Value)) }
).GroupBy(
k => k.ApplID,
(k, g) => new { g.First().ApplID, Subjects = g.Select(s => s.Mark) }
),
k1 => k1.ApplID,
k2 => k2.ApplID,
(s1, s2) => new { s1.ApplID, s1.LastName, s1.FirstName, s1.MiddleName, s1.RecordBook, s2.Subjects }
);
Tuple <string, string> dirNameCode = dbHelper.GetDirectionNameAndCode(order.Direction);
string dirSocr = dbHelper.GetDirectionShortName(order.Faculty, order.Direction);
if (cbActs.Checked)
{
actsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
Classes.Settings.DocumentsTemplatesPath + "Act.xml",
null,
null,
new string[]
{
DateTime.Now.ToShortDateString(),
order.Date.Year.ToString(),
forms[order.EduForm] + " обучения" +
(order.EduSource == dbHelper.GetDictionaryItemID(FIS_Dictionary.EDU_SOURCE, DB_Helper.EduSourceP) ? " по договорам с оплатой стоимости обучения" : ""),
order.Faculty,
dirNameCode.Item2,
dirNameCode.Item1,
order.Master?"Магистерская программа: " :(order.Profile != null ?(dirNameCode.Item2.Split('.')[1] == "05"?"Специализация": "Профиль") + ": " : ""),
order.Profile != null ? order.Profile + " - " + DB_Queries.GetProfileName(_DB_Connection, order.Faculty, order.Direction, order.Profile).Split('|')[0] : ""
},
new IEnumerable <string[]>[] { table.Select(s => new
{
Name = s.LastName + " " + s.FirstName + " " + s.MiddleName,
s.RecordBook
}).OrderBy(s => s.Name).Select(s => new string[] { s.Name, s.RecordBook }) }));
}
foreach (var appl in table)
{
if (cbReceipts.Checked)
{
receiptsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
Classes.Settings.DocumentsTemplatesPath + "AdmReceipt.xml",
null,
null,
new string[]
{
order.Number,
order.Date.ToShortDateString(),
(appl.LastName + " " + appl.FirstName[0] + "." + (appl.MiddleName.Length != 0?appl.MiddleName[0].ToString() + ".":"")).ToUpper(),
order.Faculty,
order.Profile != null ?order.Profile:dirSocr
},
null
));
}
if (cbExamSheets.Checked)
{
sheetsDocs.Add(new Classes.DocumentCreator.DocumentParameters(
Classes.Settings.DocumentsTemplatesPath + "ExamSheet.xml",
null,
null,
new string[]
{
appl.Subjects.SingleOrDefault(s => s.SubjID == mathID)?.Value.ToString(),
dbHelper.GetDictionaryItemName(FIS_Dictionary.EDU_FORM, order.EduForm),
appl.Subjects.SingleOrDefault(s => s.SubjID == physID)?.Value.ToString(),
appl.ApplID.ToString(),
appl.Subjects.SingleOrDefault(s => s.SubjID == rusID)?.Value.ToString(),
appl.LastName.ToUpper(),
appl.Subjects.SingleOrDefault(s => s.SubjID == socID)?.Value.ToString(),
appl.FirstName.ToUpper(),
appl.Subjects.SingleOrDefault(s => s.SubjID == forID)?.Value.ToString(),
appl.MiddleName.ToUpper(),
order.Profile != null ?order.Profile:dirSocr
},
null
));
}
}
}
}
if (cbActs.Checked)
{
string doc = Classes.Settings.TempPath + "acts" + new Random().Next();
Classes.DocumentCreator.Create(doc, actsDocs, false);
System.Diagnostics.Process.Start(doc + ".docx");
}
if (cbReceipts.Checked)
{
string doc = Classes.Settings.TempPath + "receipts" + new Random().Next();
Classes.DocumentCreator.Create(doc, receiptsDocs, false);
System.Diagnostics.Process.Start(doc + ".docx");
}
if (cbExamSheets.Checked)
{
string doc = Classes.Settings.TempPath + "examSheets" + new Random().Next();
Classes.DocumentCreator.Create(doc, sheetsDocs, false);
System.Diagnostics.Process.Start(doc + ".docx");
}
Cursor.Current = Cursors.Default;
}
}
