private void UpdateTable()
{
dataGridView.Rows.Clear();
Dictionary <uint, string> subjects = _DB_Helper.GetDictionaryItems(FIS_Dictionary.SUBJECTS);
foreach (DB_Queries.Exam exam in DB_Queries.GetCampaignExams(_DB_Connection, Classes.Settings.CurrentCampaignID))
{
dataGridView.Rows.Add(
exam.ID,
subjects[exam.SubjID],
exam.Date,
exam.RegStartDate,
exam.RegEndDate
);
}
}
private void dataGridView_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
{
if (DB_Queries.ExaminationHasMarks(_DB_Connection, (uint)e.Row.Cells[dataGridView_ID.Index].Value))
{
MessageBox.Show("Невозможно удалить экзамен с распределёнными абитуриентами. Сначала очистите список оценок.", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Warning);
e.Cancel = true;
}
else if (SharedClasses.Utility.ShowUnrevertableActionMessageBox())
{
_DB_Connection.Delete(DB_Table.EXAMINATIONS, new Dictionary <string, object> {
{ "id", e.Row.Cells[dataGridView_ID.Index].Value }
});
}
else
{
e.Cancel = true;
}
}
private void ToggleButtons()
{
if (dataGridView.SelectedRows.Count == 0)
{
toolStrip_Edit.Enabled = false;
toolStrip_Delete.Enabled = false;
toolStrip_Distribute.Enabled = false;
toolStrip_Marks.Enabled = false;
toolStrip_Print.Enabled = false;
}
else
{
bool hasMarks = DB_Queries.ExaminationHasMarks(_DB_Connection, SelectedExamID);
toolStrip_Edit.Enabled = true;
toolStrip_Delete.Enabled = !hasMarks;
toolStrip_Distribute.Enabled = true;
toolStrip_Marks.Enabled = hasMarks;
toolStrip_Print.Enabled = hasMarks;
}
}
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;
}
}
private void UpdateTable()
{
DataGridViewColumn sortedColumn = dataGridView.SortedColumn;
System.ComponentModel.ListSortDirection sortMethod = System.ComponentModel.ListSortDirection.Ascending;
if (dataGridView.SortOrder == SortOrder.Ascending)
{
sortMethod = System.ComponentModel.ListSortDirection.Ascending;
}
else if (dataGridView.SortOrder == SortOrder.Descending)
{
sortMethod = System.ComponentModel.ListSortDirection.Descending;
}
int firstDisplayedRow = dataGridView.FirstDisplayedScrollingRowIndex;
dataGridView.Rows.Clear();
DB_Helper dbHelper = new DB_Helper(_DB_Connection);
List <Tuple <string, Relation, object> > filter = new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, Classes.Settings.CurrentCampaignID)
};
if (toolStrip_RegFilter.SelectedItem.ToString() == "Зарегистрированные")
{
filter.Add(new Tuple <string, Relation, object>("protocol_number", Relation.NOT_EQUAL, null));
}
else if (toolStrip_RegFilter.SelectedItem.ToString() == "Незарегистрированные")
{
filter.Add(new Tuple <string, Relation, object>("protocol_number", Relation.EQUAL, null));
}
foreach (var row in _DB_Connection.Select(
DB_Table.ORDERS,
new string[] { "number", "type", "date", "protocol_number", "edu_source_id", "edu_form_id", "faculty_short_name", "direction_id", "profile_short_name" },
filter
).GroupJoin(
_DB_Connection.Select(DB_Table.ORDERS_HAS_APPLICATIONS, "orders_number"),
k1 => k1[0],
k2 => k2[0],
(e, g) => new { e, Count = g.Count() }
))
{
dataGridView.Rows.Add(
row.e[0],
_OrderTypes[row.e[1].ToString()],
(DateTime)row.e[2],
row.e[3] as ushort?,
dbHelper.GetDictionaryItemName(FIS_Dictionary.EDU_SOURCE, (uint)row.e[4]),
dbHelper.GetDictionaryItemName(FIS_Dictionary.EDU_FORM, (uint)row.e[5]),
row.e[6].ToString() + (row.e[7] is uint? " " + dbHelper.GetDirectionNameAndCode((uint)row.e[7]).Item1: null),
row.e[8] is string?DB_Queries.GetProfileName(_DB_Connection, row.e[6].ToString(), (uint)row.e[7], row.e[8].ToString()) : null,
row.Count
);
}
if (sortedColumn != null)
{
dataGridView.Sort(sortedColumn, sortMethod);
}
if (firstDisplayedRow != -1 && dataGridView.Rows.Count > firstDisplayedRow)
{
dataGridView.FirstDisplayedScrollingRowIndex = firstDisplayedRow;
}
}
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;
}
public ExaminationEdit(DB_Connector connection, uint?id)
{
_DB_Connection = connection;
_ID = id;
#region Components
InitializeComponent();
dataGridView_Capacity.ValueType = typeof(ushort);
Tuple <uint, uint> curCampStartEnd = DB_Queries.GetCampaignStartEnd(_DB_Connection, Classes.Settings.CurrentCampaignID);
dtpDate.MinDate = new DateTime((int)curCampStartEnd.Item1, 1, 1);
dtpDate.MaxDate = new DateTime((int)curCampStartEnd.Item2, 12, 31);
dtpRegStartDate.MinDate = dtpDate.MinDate;
dtpRegStartDate.MaxDate = dtpDate.MaxDate;
dtpRegEndDate.MinDate = dtpDate.MinDate;
dtpRegEndDate.MaxDate = dtpDate.MaxDate;
foreach (DateTimePicker dtp in Controls.OfType <DateTimePicker>())
{
dtp.Tag = _ID.HasValue;
}
if (_ID.HasValue)
{
dtpRegStartDate.Enabled = !DB_Queries.ExaminationHasMarks(_DB_Connection, _ID.Value);
dtpRegEndDate.Enabled = !DB_Queries.ExaminationHasMarks(_DB_Connection, _ID.Value);
}
#endregion
_DB_Helper = new DB_Helper(_DB_Connection);
Dictionary <uint, string> subjects = _DB_Helper.GetDictionaryItems(FIS_Dictionary.SUBJECTS);
cbSubject.Items.AddRange(subjects.Values.ToArray());
if (_ID.HasValue)
{
object[] exam = _DB_Connection.Select(DB_Table.EXAMINATIONS,
new string[] { "subject_id", "date", "reg_start_date", "reg_end_date" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("id", Relation.EQUAL, _ID)
})[0];
cbSubject.SelectedItem = subjects[(uint)exam[0]];
dtpDate.Value = (DateTime)exam[1];
dtpRegStartDate.Value = (DateTime)exam[2];
dtpRegEndDate.Value = (DateTime)exam[3];
foreach (object[] row in _DB_Connection.Select(
DB_Table.EXAMINATIONS_AUDIENCES,
new string[] { "number", "capacity", "priority" },
new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("examination_id", Relation.EQUAL, _ID)
}).OrderBy(s => s[2]))
{
dataGridView.Rows.Add(row[0], row[1]);
}
}
}
private void PostApplications()
{
try
{
List <object[]> appsData = _DB_Connection.Select(DB_Table.APPLICATIONS, new string[] { "id", "mcado", "chernobyl", "needs_hostel", "passing_examinations",
"priority_right", "entrant_id", "status" }, new List <Tuple <string, Relation, object> >
{
new Tuple <string, Relation, object>("campaign_id", Relation.EQUAL, _CampaignID),
new Tuple <string, Relation, object>("master_appl", Relation.EQUAL, false),
new Tuple <string, Relation, object>("status", Relation.NOT_EQUAL, "withdrawn")
});
string connectionStr = "server=" + server + ";port=3306;database=" + dbname + ";user="******";password="******";";
MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(connectionStr);
if (appsData.Count > 0)
{
if ((cbPost.Checked) && (rbDirectToDB.Checked))
{
connection.Open();
string Query = "SET foreign_key_checks = 0;" +
"LOCK TABLES `" + dbname + "`.`abitur_tmptable` WRITE, " +
"`" + dbname + "`.`application_tmptable` WRITE, " +
"`" + dbname + "`.`profile_table` READ, " +
"`" + dbname + "`.`direction_table` AS dt READ, " +
"`" + dbname + "`.`faculty_table` AS ft READ;" +
"TRUNCATE TABLE `" + dbname + "`.`abitur_tmptable`;" +
"TRUNCATE TABLE `" + dbname + "`.`application_tmptable`;";
new MySql.Data.MySqlClient.MySqlCommand(Query, connection).ExecuteNonQuery();
}
XDocument PackageData = new XDocument(new XElement("Root"));
PackageData.Root.Add(new XElement("AuthData", _AuthData));
PackageData.Root.Add(new XElement("PackageData"));
IEnumerable <DB_Queries.Mark> marks = DB_Queries.GetMarks(_DB_Connection, appsData.Select(s => (uint)s[0]), _CampaignID);
IEnumerable <DB_Queries.Document> documents = DB_Queries.GetDocuments(_DB_Connection, appsData.Select(s => (uint)s[0]));
var passwords = _DB_Connection.Select(DB_Table.ENTRANTS, "id", "personal_password").Select(s => new { EntrID = (uint)s[0], Password = s[1].ToString() });
var names = _DB_Connection.Select(DB_Table.ENTRANTS_VIEW, "id", "last_name", "first_name", "middle_name").Select(s => new
{
EntrID = (uint)s[0],
LastName = s[1].ToString(),
FirstName = s[2].ToString(),
MiddleName = s[3] as string
});
count = 0;
foreach (object[] application in appsData)
{
if ((threadPost.ThreadState == ThreadState.Aborted) || (threadPost.ThreadState == ThreadState.Stopped))
{
break;
}
XElement abitur = new XElement("Abitur",
new XElement("Uin", application[0]),
new XElement("EntrantUin", application[6]),
new XElement("Surname", names.Single(s => s.EntrID == (uint)application[6]).LastName),
new XElement("Name", names.Single(s => s.EntrID == (uint)application[6]).FirstName),
new XElement("Name2", names.Single(s => s.EntrID == (uint)application[6]).MiddleName),
new XElement("Password", passwords.Single(s => s.EntrID == (uint)application[6]).Password),
new XElement("Status", application[7]),
new XElement("MathBall", 0),
new XElement("CheckedByFISMath", 0),
new XElement("PhisBall", 0),
new XElement("CheckedByFISPhis", 0),
new XElement("RusBall", 0),
new XElement("CheckedByFISRus", 0),
new XElement("ObshBall", 0),
new XElement("CheckedByFISObsh", 0),
new XElement("ForenBall", 0),
new XElement("CheckedByFISForen", 0),
new XElement("IABall", 0),
new XElement("ODO", 0),
new XElement("Olymp", 0),
new XElement("Exam", 0),
new XElement("Hostel", 0),
new XElement("PP", 0),
new XElement("MCADO", 0),
new XElement("Chern", 0),
new XElement("Documents",
new XElement("ApplicationOfAdmission", 1),
new XElement("PassportCopy", 0),
new XElement("CertificateDiplomCopy", 0),
new XElement("HRRefCopy", 0),
new XElement("ReferralPK", 0),
new XElement("MedRef", 0),
new XElement("Photo", 0),
new XElement("OrphanDocument", 0),
new XElement("InvalidDocument", 0),
new XElement("AbsenceOfContraindicationsForTraining", 0)),
new XElement("Applications"));
if ((bool)application[4])
{
abitur.SetElementValue("Exam", 1);
}
if ((bool)application[3])
{
abitur.SetElementValue("Hostel", 1);
}
if ((bool)application[5])
{
abitur.SetElementValue("PP", 1);
abitur.Element("Documents").SetElementValue("ReferralPK", 1);
}
if ((bool)application[1])
{
abitur.SetElementValue("MCADO", 1);
}
if ((bool)application[2])
{
abitur.SetElementValue("Chern", 1);
abitur.Element("Documents").SetElementValue("ReferralPK", 1);
}
SetMarks((uint)application[0], abitur, marks);
SetDocuments((uint)application[0], abitur, documents);
SetIA((uint)application[0], abitur);
SetEntrances((uint)application[0], abitur);
if ((cbPost.Checked) && (rbDirectToDB.Checked))
{
string Query = "INSERT INTO `" + dbname + "`.`abitur_tmptable` " +
"(`abitur_id`, `uin`, `entrant_uin`, `surname`, `_name`, `name2`, `status`, " +
"`math_ball`, `checked_by_FIS_math`, `phis_ball`, `checked_by_FIS_phis`, `rus_ball`, `checked_by_FIS_rus`, " +
"`obsh_ball`, `checked_by_FIS_obsh`, `foren_ball`, `checked_by_FIS_foren`, `IA_ball`, " +
"`ODO`, `Olymp`, `Exam`, `hostel`, `PP`, `MCADO`, `Chern`, `appl_of_admission`, " +
"`passport_copy`, `a_d_copy`, `hr_ref_copy`, `referral_pk`, `med_ref`, `photo`, `password`, " +
"`orphan_document`, `invalid_document`, `pmp_document`, `absence_of_contraindications`) VALUES " +
"(0, " + abitur.Element("Uin").Value.ToString() + ", " + abitur.Element("EntrantUin").Value.ToString() + ", '" + abitur.Element("Surname").Value.ToString() + "', '" +
abitur.Element("Name").Value.ToString() + "' ,'" + abitur.Element("Name2").Value.ToString() + "' ,'" + abitur.Element("Status").Value.ToString() + "' ," +
abitur.Element("MathBall").Value.ToString() + " ," + abitur.Element("CheckedByFISMath").Value.ToString() + " ," +
abitur.Element("PhisBall").Value.ToString() + " ," + abitur.Element("CheckedByFISPhis").Value.ToString() + " ," +
abitur.Element("RusBall").Value.ToString() + " ," + abitur.Element("CheckedByFISRus").Value.ToString() + " ," +
abitur.Element("ObshBall").Value.ToString() + " ," + abitur.Element("CheckedByFISObsh").Value.ToString() + " ," +
abitur.Element("ForenBall").Value.ToString() + " ," + abitur.Element("CheckedByFISForen").Value.ToString() + " ," +
abitur.Element("IABall").Value.ToString() + " ," + abitur.Element("ODO").Value.ToString() + ", " +
abitur.Element("Olymp").Value.ToString() + " ," + abitur.Element("Exam").Value.ToString() + ", " +
abitur.Element("Hostel").Value.ToString() + " ," + abitur.Element("PP").Value.ToString() + ", " +
abitur.Element("MCADO").Value.ToString() + ", " + abitur.Element("Chern").Value.ToString() + " ," +
abitur.Element("Documents").Element("ApplicationOfAdmission").Value.ToString() + " ," +
abitur.Element("Documents").Element("PassportCopy").Value.ToString() + " ," +
abitur.Element("Documents").Element("CertificateDiplomCopy").Value.ToString() + " ," +
abitur.Element("Documents").Element("HRRefCopy").Value.ToString() + " ," +
abitur.Element("Documents").Element("ReferralPK").Value.ToString() + " ," +
abitur.Element("Documents").Element("MedRef").Value.ToString() + " ," +
abitur.Element("Documents").Element("Photo").Value.ToString() + ", '" + abitur.Element("Password").Value.ToString() + "', " +
abitur.Element("Documents").Element("OrphanDocument").Value.ToString() + ", " +
abitur.Element("Documents").Element("InvalidDocument").Value.ToString() + ", 0, " +
abitur.Element("Documents").Element("AbsenceOfContraindicationsForTraining").Value.ToString() + ");";
string direction, profile;
int direction_id = 1000, profile_id = 1000;
string query_tmp;
foreach (XElement appl in abitur.Element("Applications").Elements())
{
direction = appl.Element("Direction").Value;
profile = appl.Element("Profile").Value;
if (direction != "0")
{
query_tmp = "SELECT dt.direction_id FROM `" + dbname + "`.`direction_table` AS dt, " +
"`" + dbname + "`.`faculty_table` AS ft WHERE (dt.id_by_FIS=" + direction + ") AND " +
"(ft.short_caption='" + appl.Element("Faculty").Value + "') AND " +
"(dt.faculty_id=ft.faculty_id)";
direction_id = Convert.ToInt16(new MySql.Data.MySqlClient.MySqlCommand(query_tmp, connection).ExecuteScalar().ToString());
}
if (profile != "")
{
query_tmp = "SELECT profile_id FROM `" + dbname + "`.`profile_table` WHERE short_caption='" + profile + "'";
profile_id = Convert.ToInt16(new MySql.Data.MySqlClient.MySqlCommand(query_tmp, connection).ExecuteScalar().ToString());
}
Query = Query + "INSERT INTO `" + dbname + "`.`application_tmptable` " +
"(`application_id`, `uin`, `direction_id`, `profile_id`, `_condition`, `appl_of_consent`, `form_of_education`) VALUES " +
"(0, " + abitur.Element("Uin").Value + ", " + direction_id.ToString() + ", " + profile_id.ToString() + " ," +
appl.Element("Condition").Value + " ," + appl.Element("ApplicationOfConsent").Value + " ," + appl.Element("FormOfEducation").Value + ");";
}
new MySql.Data.MySqlClient.MySqlCommand(Query, connection).ExecuteNonQuery();
}
PackageData.Root.Element("PackageData").Add(abitur);
count++;
lCount.BeginInvoke(new InvokeDelegate(() => { lCount.Text = count.ToString() + " абитуриентов"; }));
}
if ((cbPost.Checked) && (rbDirectToDB.Checked))
{
new MySql.Data.MySqlClient.MySqlCommand("UNLOCK TABLES;", connection).ExecuteNonQuery();
}
timerSum.Stop();
//timerSum.Enabled = false;
_SchemaSet.Add(null, SchemaPath);
PackageData.Validate(_SchemaSet, (send, ee) => { throw ee.Exception; });
if (cbSave.Checked)
{
PackageData.Save("doc.xml");
}
if ((cbPost.Checked) && (rbDirectToDB.Checked))
{
connection.Close();
HttpWebRequest HttpRequest = (HttpWebRequest)WebRequest.Create(script);
HttpWebResponse HttpResponse;
HttpResponse = (HttpWebResponse)HttpRequest.GetResponse();
if (HttpResponse.StatusCode == HttpStatusCode.OK)
{
System.IO.Stream responseStream = HttpResponse.GetResponseStream();
string responseStr = new System.IO.StreamReader(responseStream).ReadToEnd();
tbResponse.BeginInvoke(new InvokeDelegate(() =>
{
tbResponse.Text = tbResponse.Text + responseStr + "\r\n" +
"Отправлено: " + count.ToString() + " абитуриента за " + (sumTime / 60).ToString() + " мин. " + (sumTime % 60).ToString() + " сек.; " +
"Среднее время на одного абитуриента: " + System.Math.Round((float)sumTime / count, 2) + " сек.\r\n\r\n";
}));
HttpResponse.Close();
}
}
if ((cbPost.Checked) && (rbPostMethod.Checked))
{
byte[] bytesData = System.Text.Encoding.UTF8.GetBytes("XMLData=" + PackageData.ToString());
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(cbAdress.Text);
request.ContentType = "application/x-www-form-urlencoded";
request.Accept = "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8";
request.UserAgent = "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36";
request.ContentLength = bytesData.Length;
request.Method = "POST";
System.IO.Stream requestStream = request.GetRequestStream();
requestStream.Write(bytesData, 0, bytesData.Length);
requestStream.Close();
HttpWebResponse response;
response = (HttpWebResponse)request.GetResponse();
if (response.StatusCode == HttpStatusCode.OK)
{
System.IO.Stream responseStream = response.GetResponseStream();
string responseStr = new System.IO.StreamReader(responseStream).ReadToEnd();
tbResponse.Text = responseStr;
}
}
if ((cbPost.Checked) && (rbFTPMethod.Checked))
{
// Get the object used to communicate with the server.
FtpWebRequest FtpRequest = (FtpWebRequest)WebRequest.Create(tbServer.Text + "/doc.xml");
FtpRequest.Method = WebRequestMethods.Ftp.UploadFile;
// This example assumes the FTP site uses anonymous logon.
FtpRequest.Credentials = new NetworkCredential(tbUser.Text, tbPassword.Text);
// Copy the contents of the file to the request stream.
System.IO.StreamReader sourceStream = new System.IO.StreamReader("doc.xml");
byte[] fileContents = System.Text.Encoding.UTF8.GetBytes(sourceStream.ReadToEnd());
sourceStream.Close();
FtpRequest.ContentLength = fileContents.Length;
System.IO.Stream requestStream = FtpRequest.GetRequestStream();
requestStream.Write(fileContents, 0, fileContents.Length);
requestStream.Close();
FtpWebResponse FtpResponse = (FtpWebResponse)FtpRequest.GetResponse();
tbResponse.Text = tbResponse.Text + DateTime.Now.ToString() + ": " + FtpResponse.StatusDescription + "\n";
FtpResponse.Close();
//FtpResponse.StatusCode=FtpStatusCode.ClosingData
if (FtpResponse.StatusCode == FtpStatusCode.ClosingData)
{
HttpWebRequest HttpRequest = (HttpWebRequest)WebRequest.Create(tbAdress.Text);
HttpWebResponse HttpResponse;
HttpResponse = (HttpWebResponse)HttpRequest.GetResponse();
if (HttpResponse.StatusCode == HttpStatusCode.OK)
{
System.IO.Stream responseStream = HttpResponse.GetResponseStream();
string responseStr = new System.IO.StreamReader(responseStream).ReadToEnd();
tbResponse.Text = tbResponse.Text + responseStr + "\r\n";
HttpResponse.Close();
}
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
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;
}
}
