//данные из нашей базы
protected override void FillCard()
{
if (_Id == null)
{
return;
}
try
{
using (PriemEntities context = new PriemEntities())
{
extPersonAll person = (from pr in context.extPersonAll
where pr.Id == GuidId
select pr).FirstOrDefault();
tbNum.Text = person.PersonNum;
FillPersonData(ref person);
FillApplications();
inEnableProtocol = GetInEnableProtocol(context);
inEntryView = GetInEntryView(context);
}
}
catch (DataException de)
{
WinFormsServ.Error("Ошибка при заполнении формы (DataException)" + de.Message);
}
catch (Exception ex)
{
WinFormsServ.Error("Ошибка при заполнении формы " + ex.Message);
}
}
// проверка на уникальность абитуриента
private static bool CheckIdent(extPersonAll person)
{
using (PriemEntities context = new PriemEntities())
{
ObjectParameter boolPar = new ObjectParameter("result", typeof(bool));
context.CheckPersonIdent(person.Surname, person.Name, person.SecondName, person.BirthDate, person.PassportSeries, person.PassportNumber,
person.AttestatRegion, person.AttestatSeries, person.AttestatNum, boolPar);
return(Convert.ToBoolean(boolPar.Value));
}
}
private void btnLoad_Click(object sender, EventArgs e)
{
loadClass = new LoadFromInet();
bdcInet = loadClass.BDCInet;
int fileNum = 0;
string barcText = tbPersonNum.Text.Trim();
if (barcText == string.Empty)
{
WinFormsServ.Error("Не введен номер");
return;
}
//if (barcText.Length == 7)
//{
// if (barcText.StartsWith("2"))
// {
// WinFormsServ.Error("Выбран человек, подавший заявления в магистратуру");
// return;
// }
// barcText = barcText.Substring(1);
//}
if (!int.TryParse(barcText, out fileNum))
{
WinFormsServ.Error("Неправильно введен номер");
return;
}
if (MainClass.CheckPersonBarcode(fileNum))
{
try
{
//extPerson person = loadClass.GetPersonByBarcode(fileNum);
DataTable dtEge = new DataTable();
//if(person != null)
//{
// string queryEge = "SELECT EgeMark.Id, EgeMark.EgeExamNameId AS ExamId, EgeMark.Value, EgeCertificate.PrintNumber, EgeCertificate.Number, EgeMark.EgeCertificateId FROM EgeMark LEFT JOIN EgeCertificate ON EgeMark.EgeCertificateId = EgeCertificate.Id LEFT JOIN Person ON EgeCertificate.PersonId = Person.Id";
// DataSet dsEge = bdcInet.GetDataSet(queryEge + " WHERE Person.Barcode = " + fileNum + " ORDER BY EgeMark.EgeCertificateId ");
// dtEge = dsEge.Tables[0];
//}
CardFromInet crd = new CardFromInet(fileNum, null, true);
crd.ToUpdateList += UpdateDataGrid;
crd.Show();
}
catch (Exception exc)
{
WinFormsServ.Error(exc.Message);
tbPersonNum.Text = "";
tbPersonNum.Focus();
}
}
else
{
UpdateDataGrid();
using (PriemEntities context = new PriemEntities())
{
extPersonAll person = (from per in context.extPersonAll
where per.Barcode == fileNum
select per).FirstOrDefault();
string fio = person.FIO;
string num = person.PersonNum;
string persId = person.Id.ToString();
WinFormsServ.Search(this.dgvAbiturients, "PersonNum", num);
DialogResult dr = MessageBox.Show(string.Format("Абитуриент {0} с данным номером баркода уже импортирован в базу.\nОткрыть карточку абитуриента?", fio), "Внимание", MessageBoxButtons.YesNo);
if (dr == System.Windows.Forms.DialogResult.Yes)
{
MainClass.OpenCardPerson(persId, this, null);
}
}
}
tbPersonNum.Text = "";
tbPersonNum.Focus();
loadClass.CloseDB();
}
public extPersonAll GetPersonByBarcode(int fileNum)
{
try
{
string personQueryInet =
@"SELECT Id, Barcode, Name, SecondName, Surname, BirthDate, BirthPlace, Sex,
PassportTypeId, PassportSeries, PassportNumber, PassportAuthor, PassportDate,
PassportCode, '' AS PersonalCode, CountryId, NationalityId AS NationalityId, RegionId, Phone, Mobiles, Email,
Code, City, Street, House, Korpus, Flat,
CodeReal, CityReal, StreetReal, HouseReal, KorpusReal, FlatReal,
HostelAbit, HostelEduc, LanguageId,
SchoolCity, SchoolTypeId, SchoolName, SchoolNum, SchoolExitYear, IsExcellent,
CountryEducId, RegionEducId, AttestatRegion, AttestatSeries, AttestatNumber,
EducationDocumentSeries AS DiplomSeries, EducationDocumentNumber AS DiplomNum, AvgMark AS SchoolAVG,
(case when SchoolTypeId=1 then '' else SchoolName end) AS HighEducation, HEProfession AS HEProfession,
HEQualification AS HEQualification, DiplomaTheme AS HEWork,
EducationStart AS HEEntryYear, HEExitYear, HEStudyFormId, Parents AS PersonInfo, AddInfo AS ExtraInfo,
IsEqual, EqualDocumentNumber, HasTRKI, TRKICertificateNumber,
EnglishMark, StartEnglish
FROM extForeignPerson
WHERE 0=0";
DataSet ds = _bdcInet.GetDataSet(personQueryInet + " AND extForeignPerson.Barcode = " + fileNum);
if (ds.Tables[0].Rows.Count == 0)
{
throw new Exception("Записей не найдено");
}
DataRow row = ds.Tables[0].Rows[0];
extPersonAll pers = new extPersonAll();
pers.Id = (Guid)row["Id"];
pers.Barcode = (int?)row["Barcode"];
pers.FIO = Util.GetFIO(row["Surname"].ToString(), row["Name"].ToString(), row["SecondName"].ToString());
pers.Name = row["Name"].ToString();
pers.SecondName = row["SecondName"].ToString();
pers.Surname = row["Surname"].ToString();
pers.BirthDate = row.Field <DateTime?>("BirthDate") ?? DateTime.Now.Date;
pers.BirthPlace = row["BirthPlace"].ToString();
pers.PassportTypeId = (int)(row["PassportTypeId"]);
pers.PassportSeries = row["PassportSeries"].ToString();
pers.PassportNumber = row["PassportNumber"].ToString();
pers.PassportAuthor = row["PassportAuthor"].ToString();
pers.PassportDate = QueryServ.ToNullDateTimeValue(row["PassportDate"]);
pers.PassportCode = row["PassportCode"].ToString();
pers.PersonalCode = row["PersonalCode"].ToString();
pers.Sex = QueryServ.ToBoolValue(row["Sex"]);
pers.ForeignCountryId = (int?)(Util.ToNullObject(row["CountryId"]));
pers.ForeignNationalityId = (int)(row["NationalityId"]);
pers.RegionId = (int?)(Util.ToNullObject(row["RegionId"]));
pers.Phone = row["Phone"].ToString();
pers.Mobiles = row["Mobiles"].ToString();
pers.Email = row["Email"].ToString();
pers.Code = row["Code"].ToString();
pers.City = row["City"].ToString();
pers.Street = row["Street"].ToString();
pers.House = row["House"].ToString();
pers.Korpus = row["Korpus"].ToString();
pers.Flat = row["Flat"].ToString();
pers.CodeReal = row["CodeReal"].ToString();
pers.CityReal = row["CityReal"].ToString();
pers.StreetReal = row["StreetReal"].ToString();
pers.HouseReal = row["HouseReal"].ToString();
pers.KorpusReal = row["KorpusReal"].ToString();
pers.FlatReal = row["FlatReal"].ToString();
pers.HostelAbit = QueryServ.ToBoolValue(row["HostelAbit"]);
pers.HostelEduc = QueryServ.ToBoolValue(row["HostelEduc"]);
pers.IsEqual = QueryServ.ToBoolValue(row["IsEqual"]);
pers.HasTRKI = QueryServ.ToBoolValue(row["HasTRKI"]);
pers.EqualDocumentNumber = row["EqualDocumentNumber"].ToString();
pers.TRKICertificateNumber = row["TRKICertificateNumber"].ToString();
pers.HasAssignToHostel = false;
pers.HasExamPass = false;
pers.IsExcellent = QueryServ.ToBoolValue(row["IsExcellent"]);
pers.LanguageId = (int?)(Util.ToNullObject(row["LanguageId"]));
pers.SchoolCity = row["SchoolCity"].ToString();
pers.SchoolTypeId = (int?)(Util.ToNullObject(row["SchoolTypeId"]));
pers.SchoolName = row["SchoolName"].ToString();
pers.SchoolNum = row["SchoolNum"].ToString();
int SchoolExitYear = 0;
int.TryParse(row["SchoolExitYear"].ToString(), out SchoolExitYear);
pers.SchoolExitYear = SchoolExitYear;
pers.ForeignCountryEducId = (int?)(Util.ToNullObject(row["CountryEducId"]));
pers.RegionEducId = (int?)(Util.ToNullObject(row["RegionEducId"]));
pers.AttestatRegion = row["AttestatRegion"].ToString();
pers.AttestatSeries = row["AttestatSeries"].ToString();
pers.AttestatNum = row["AttestatNumber"].ToString();
pers.DiplomSeries = row["DiplomSeries"].ToString();
pers.DiplomNum = row["DiplomNum"].ToString();
pers.StartEnglish = QueryServ.ToBoolValue(row["StartEnglish"]);
int EnglishMark = 0;
int.TryParse(row["EnglishMark"].ToString(), out EnglishMark);
pers.EnglishMark = EnglishMark == 0 ? null : (int?)EnglishMark;
double avg;
if (!double.TryParse(row["SchoolAVG"].ToString(), out avg))
{
pers.SchoolAVG = null;
}
else
{
pers.SchoolAVG = avg;
}
pers.HighEducation = row["HighEducation"].ToString();
pers.HEProfession = row["HEProfession"].ToString();
pers.HEQualification = row["HEQualification"].ToString();
pers.HEEntryYear = (int?)(Util.ToNullObject(row["HEEntryYear"]));
pers.HEExitYear = (int?)(Util.ToNullObject(row["HEExitYear"]));
pers.HEStudyFormId = (int?)(Util.ToNullObject(row["HEStudyFormId"]));
pers.HEWork = row["HEWork"].ToString();
pers.PersonInfo = row["PersonInfo"].ToString();
pers.ExtraInfo = row["ExtraInfo"].ToString();
DataSet dsWork = _bdcInet.GetDataSet(string.Format(@"
SELECT PersonWork.WorkPlace + ', ' + PersonWork.WorkProfession + ', ' + PersonWork.WorkSpecifications + ' стаж: ' + PersonWork.Stage AS Work,
PersonWork.WorkPlace + ', ' + PersonWork.WorkProfession + ', ' + PersonWork.WorkSpecifications AS Place, PersonWork.Stage
FROM PersonWork WHERE PersonWork.PersonId = '{0}'", pers.Id));
if (dsWork.Tables[0].Rows.Count == 0)
{
pers.Stag = string.Empty;
pers.WorkPlace = string.Empty;
}
else if (dsWork.Tables[0].Rows.Count == 1)
{
pers.Stag = dsWork.Tables[0].Rows[0]["Stage"].ToString();
pers.WorkPlace = dsWork.Tables[0].Rows[0]["Place"].ToString();
}
else
{
string work = string.Empty;
foreach (DataRow dr in dsWork.Tables[0].Rows)
{
work += dr["Work"].ToString() + ";" + Environment.NewLine;
}
pers.WorkPlace = work;
}
DataSet dsScienceWork = _bdcInet.GetDataSet(string.Format(@"
SELECT ScienceWorkType.Name + ': ' + PersonScienceWork.WorkInfo AS ScienseWork
FROM PersonScienceWork LEFT JOIN ScienceWorkType ON PersonScienceWork.WorkTypeId = ScienceWorkType.Id WHERE PersonScienceWork.PersonId = '{0}'", pers.Id));
if (dsScienceWork.Tables[0].Rows.Count == 0)
{
pers.ScienceWork = string.Empty;
}
else
{
string work = string.Empty;
foreach (DataRow dr in dsScienceWork.Tables[0].Rows)
{
work += dr["ScienseWork"].ToString() + ";" + Environment.NewLine;
}
pers.ScienceWork = work;
}
return(pers);
}
catch
{
return(null);
}
}
public static void ImportMagAbits()
{
List <string> lstPersons = new List <string>();
List <string> lstAbits = new List <string>();
LoadFromInet loadClass = new LoadFromInet();
DBPriem _bdcInet = loadClass.BDCInet;
using (PriemEntities context = new PriemEntities())
{
string _sQuery = @"SELECT DISTINCT qAbiturient.Id, qAbiturient.PersonId, qAbiturient.Barcode AS AbitBarcode, extPerson.Barcode AS PersonBarcode
FROM qAbiturient INNER JOIN extPerson ON qAbiturient.PersonId = extPerson.Id WHERE StudyLevelId = 17 AND Enabled = 1 AND IsApprovedByComission = 1 AND IsImported = 0";
DataSet ds = _bdcInet.GetDataSet(_sQuery);
Guid?personId;
foreach (DataRow dr in ds.Tables[0].Rows)
{
int?abitBarcode = (int?)dr["AbitBarcode"];
if (!MainClass.CheckAbitBarcode(abitBarcode))
{
continue;
}
int?persBarcode = (int?)dr["PersonBarcode"];
if (!MainClass.CheckPersonBarcode(persBarcode))
{
personId = (from pers in context.extForeignPerson
where pers.Barcode == persBarcode
select pers.Id).FirstOrDefault();
}
else
{
extPersonAll person = loadClass.GetPersonByBarcode(persBarcode.Value);
if (!CheckIdent(person))
{
lstPersons.Add(persBarcode.ToString());
continue;
}
ObjectParameter entId = new ObjectParameter("id", typeof(Guid));
context.Person_Foreign_insert(person.Barcode, person.Name, person.SecondName, person.Surname, person.BirthDate, person.BirthPlace, person.PassportTypeId,
person.PassportSeries, person.PassportNumber, person.PassportAuthor, person.PassportDate, person.Sex, person.CountryId, person.NationalityId,
person.RegionId, person.Phone, person.Mobiles, person.Email,
person.Code, person.City, person.Street, person.House, person.Korpus, person.Flat,
person.CodeReal, person.CityReal, person.StreetReal, person.HouseReal, person.KorpusReal, person.FlatReal,
person.HostelAbit, false, null, false, null, person.IsExcellent,
person.LanguageId, person.SchoolCity, person.SchoolTypeId, person.SchoolName, person.SchoolNum, person.SchoolExitYear, person.SchoolAVG,
person.ForeignCountryEducId, person.RegionEducId, person.IsEqual, person.EqualDocumentNumber, person.HasTRKI, person.TRKICertificateNumber,
person.AttestatRegion, person.AttestatSeries, person.AttestatNum, person.DiplomSeries, person.DiplomNum,
person.HighEducation, person.HEProfession, person.HEQualification, person.HEEntryYear, person.HEExitYear, person.HEStudyFormId,
person.HEWork, person.Stag, person.WorkPlace, person.Privileges, person.PassportCode,
person.PersonalCode, person.PersonInfo, person.ExtraInfo, person.ScienceWork, person.StartEnglish, person.EnglishMark, entId);
//_bdcInet.ExecuteQuery("UPDATE Person SET IsImported = 1 WHERE Person.Barcode = " + persBarcode);
personId = (Guid)entId.Value;
}
qAbiturient abit = loadClass.GetAbitByBarcode(abitBarcode.Value);
int cnt = (from en in context.qEntry
where en.Id == abit.EntryId && !en.IsClosed
select en).Count();
if (cnt == 0)
{
lstAbits.Add(abitBarcode.ToString());
continue;
}
ObjectParameter abEntId = new ObjectParameter("id", typeof(Guid));
int competitionId;
if (abit.StudyBasisId == 1)
{
competitionId = 4;
}
else
{
competitionId = 3;
}
context.Abiturient_Insert(personId, abit.EntryId, competitionId, false, false, false, false, null, abit.DocDate, DateTime.Now,
false, false, null, null, null, null, abit.LanguageId, false,
abit.Priority, abit.Barcode, abit.CommitId, abit.CommitNumber, abit.IsGosLine, abEntId);
// _bdcInet.ExecuteQuery("UPDATE Application SET IsImported = 1 WHERE Application.Barcode = " + abitBarcode);
}
}
}
private void FillPersonData(ref extPersonAll person)
{
CardTitle = Util.GetFIO(person.Surname, person.Name, person.SecondName);
PersonName = person.Name;
SecondName = person.SecondName;
Surname = person.Surname;
BirthDate = person.BirthDate;
BirthPlace = person.BirthPlace;
PassportTypeId = person.PassportTypeId;
PassportSeries = person.PassportSeries;
PassportNumber = person.PassportNumber;
PassportAuthor = person.PassportAuthor;
PassportDate = person.PassportDate;
PassportCode = person.PassportCode;
PersonalCode = person.PersonalCode;
Sex = person.Sex;
CountryId = person.ForeignCountryId;
NationalityId = person.ForeignNationalityId;
RegionId = person.RegionId;
Phone = person.Phone;
Mobiles = person.Mobiles;
Email = person.Email;
Code = person.Code;
City = person.City;
Street = person.Street;
House = person.House;
Korpus = person.Korpus;
Flat = person.Flat;
CodeReal = person.CodeReal;
CityReal = person.CityReal;
StreetReal = person.StreetReal;
HouseReal = person.HouseReal;
KorpusReal = person.KorpusReal;
FlatReal = person.FlatReal;
HostelAbit = person.HostelAbit ?? false;
HostelEduc = person.HostelEduc ?? false;
HasAssignToHostel = person.HasAssignToHostel ?? false;
HostelFacultyId = person.HostelFacultyId;
HasExamPass = person.HasExamPass ?? false;
ExamPassFacultyId = person.ExamPassFacultyId;
IsExcellent = person.IsExcellent ?? false;
LanguageId = person.LanguageId;
SchoolCity = person.SchoolCity;
SchoolTypeId = person.SchoolTypeId;
SchoolName = person.SchoolName;
SchoolNum = person.SchoolNum;
SchoolExitYear = person.SchoolExitYear;
CountryEducId = person.ForeignCountryEducId;
IsEqual = person.IsEqual ?? false;
EqualDocumentNumber = person.EqualDocumentNumber;
HasTRKI = person.HasTRKI ?? false;
TRKISertificateNumber = person.TRKICertificateNumber;
AttestatSeries = person.AttestatSeries;
AttestatNum = person.AttestatNum;
DiplomSeries = person.DiplomSeries;
DiplomNum = person.DiplomNum;
SchoolAVG = person.SchoolAVG;
HighEducation = person.HighEducation;
HEProfession = person.HEProfession;
HEQualification = person.HEQualification;
HEEntryYear = person.HEEntryYear;
HEExitYear = person.HEExitYear;
HEWork = person.HEWork;
HEStudyFormId = person.HEStudyFormId;
Stag = person.Stag;
WorkPlace = person.WorkPlace;
Privileges = person.Privileges;
ExtraInfo = person.ExtraInfo;
PersonInfo = person.PersonInfo;
ScienceWork = person.ScienceWork;
StartEnglish = person.StartEnglish ?? false;
EnglishMark = person.EnglishMark;
personBarc = person.Barcode;
}
