//Группировка для XML
public override List <RecordXML> GetXMLRecords(List <Record> records)
{
string dir = System.AppDomain.CurrentDomain.BaseDirectory;
if (File.Exists(dir + "\\test.txt"))
{
File.Delete(dir + "\\test.txt");
}
Dictionary <string, string> ulusDict = GetUlusDict();
List <RecordXML> list = new List <RecordXML>();
using (NpgsqlConnection conn = new NpgsqlConnection("Server=10.50.0.82;Port=5432;Database=SubsUOW;User Id=postgres;Password=3I5BEvynPQ;"))
{
using (NpgsqlCommand cmd = new NpgsqlCommand())
{
cmd.Connection = conn;
conn.Open();
//делаем запросы
foreach (Record rec in records)
{
//сперва выясняем проживающий одинокий или нет
//находим год
int year = rec.DateFinish.Value.Year;
int cnt = 0;
cmd.CommandText = "select count(*) as cnt from rodstvennik" + year + " where id_zayavka=" + rec.IdZayavka;
//если нет родственников, то проезжаем
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
cnt = Int32.Parse(reader["cnt"].ToString());
}
}
//одинокий
if (cnt == 0)
{
//не делаем запрос - тупо перезапишем
RecordXML odinokiy = new RecordXML();
odinokiy.Oso = rec.Oso;
odinokiy.NumberZ = rec.NumberZ;
odinokiy.IdZayavka = rec.IdZayavka;
odinokiy.RecType = rec.RecType;
odinokiy.LmszId = rec.LmszId;
odinokiy.CategoryId = rec.CategoryId;
odinokiy.OnmszCode = rec.OnmszCode;
odinokiy.SnilsRecip = rec.SnilsRecip;
odinokiy.FamilyNameRecip = rec.FamilyNameRecip;
odinokiy.NameRecip = rec.NameRecip;
odinokiy.PatronymicRecip = rec.PatronymicRecip;
odinokiy.GenderRecip = rec.GenderRecip;
odinokiy.BirthDateRecip = rec.BirthDateRecip;
odinokiy.DoctypeRecip = rec.DoctypeRecip;
if (!CheckDocumentOwner(rec, ulusDict, "01"))
{
continue;
}
odinokiy.DocSeriesRecip = rec.DocSeriesRecip;
odinokiy.DocNumberRecip = rec.DocNumberRecip;
odinokiy.DocIssueDateRecip = rec.DocIssueDateRecip;
odinokiy.DocIssuerRecip = rec.DocIssuerRecip;
odinokiy.prozhiv = null;
odinokiy.DecisionDate = rec.DecisionDate;
odinokiy.DateStart = rec.DateStart;
odinokiy.DateFinish = rec.DateFinish;
odinokiy.UsingSign = rec.UsingSign;
odinokiy.Criteria = rec.Criteria;
odinokiy.FormCode = rec.FormCode;
odinokiy.Amount = rec.Amount;
odinokiy.MeasuryCode = rec.MeasuryCode;
odinokiy.Monetization = rec.Monetization;
odinokiy.Content = rec.Content;
odinokiy.Comment = rec.Content;
list.Add(odinokiy);
}
else
{
RecordXML neOdinokiy = new RecordXML();
neOdinokiy.Oso = rec.Oso;
neOdinokiy.NumberZ = rec.NumberZ;
neOdinokiy.IdZayavka = rec.IdZayavka;
neOdinokiy.RecType = rec.RecType;
neOdinokiy.LmszId = rec.LmszId;
neOdinokiy.CategoryId = rec.CategoryId;
neOdinokiy.OnmszCode = rec.OnmszCode;
neOdinokiy.SnilsRecip = rec.SnilsRecip;
neOdinokiy.FamilyNameRecip = rec.FamilyNameRecip;
neOdinokiy.NameRecip = rec.NameRecip;
neOdinokiy.PatronymicRecip = rec.PatronymicRecip;
neOdinokiy.GenderRecip = rec.GenderRecip;
neOdinokiy.BirthDateRecip = rec.BirthDateRecip;
neOdinokiy.DoctypeRecip = rec.DoctypeRecip;
//выкидываем в лог
if (!CheckDocumentOwner(rec, ulusDict, "01"))
{
continue;
}
neOdinokiy.DocSeriesRecip = rec.DocSeriesRecip;
neOdinokiy.DocNumberRecip = rec.DocNumberRecip;
neOdinokiy.DocIssueDateRecip = rec.DocIssueDateRecip;
neOdinokiy.DocIssuerRecip = rec.DocIssuerRecip;
neOdinokiy.DecisionDate = rec.DecisionDate;
neOdinokiy.DateStart = rec.DateStart;
neOdinokiy.DateFinish = rec.DateFinish;
neOdinokiy.UsingSign = rec.UsingSign;
neOdinokiy.Criteria = rec.Criteria;
neOdinokiy.FormCode = rec.FormCode;
neOdinokiy.Amount = rec.Amount;
neOdinokiy.MeasuryCode = rec.MeasuryCode;
neOdinokiy.Monetization = rec.Monetization;
neOdinokiy.Content = rec.Content;
neOdinokiy.Comment = rec.Content;
neOdinokiy.prozhiv = new List <Prozhiv>();
//тут делаем запрос к родственникам
cmd.CommandText = "select * from rodstvennik" + year + " where id_zayavka=" + rec.IdZayavka;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//проживающий может быть как с паспортом, так и с свидетельством
int idDoc = Int32.Parse(reader["id_doc"].ToString());
Prozhiv prozhiv = new Prozhiv();
//если тип документа паспорт 1 если пусто (будет 0), то тогда на ошибку
prozhiv.BirthdateReason = DateTime.Parse(reader["date_rozhd"].ToString());
if (idDoc == 1)
{
prozhiv.DocIssueDateReason = DateTime.Parse(reader["data_vidachi_passport"].ToString());
prozhiv.DocIssuerReason = reader["kem_vidan_passport"].ToString();
prozhiv.DocNumberReason = reader["passport_number"].ToString();
prozhiv.DoctypeReason = "01";
prozhiv.DocSeriesReason = reader["passport_seria"].ToString();
}
//свидетельство о рождении
else if (idDoc == 2)
{
prozhiv.DocIssueDateReason = DateTime.Parse(reader["data_vidachi_svid"].ToString());
prozhiv.DocIssuerReason = null;
prozhiv.DocNumberReason = reader["svid_number"].ToString();
prozhiv.DoctypeReason = "05";
prozhiv.DocSeriesReason = reader["svid_ser"].ToString();
}
prozhiv.FamilyNameReason = reader["Familia"].ToString();
prozhiv.GenderReason = reader["pol"].ToString();
prozhiv.NameReason = reader["imya"].ToString();
prozhiv.PatronymicReason = reader["otchestvo"].ToString();
prozhiv.SnilsReason = reader["snils"].ToString();
//выкидываем (не добавляем вообще заявителя)
if (idDoc == 0)
{
if (!CheckDocumentProzhiv(rec, prozhiv, ulusDict, "00"))
{
continue;
}
}
if (idDoc == 1)
{
if (!CheckDocumentProzhiv(rec, prozhiv, ulusDict, "01"))
{
continue;
}
}
if (idDoc == 2)
{
if (!CheckDocumentProzhiv(rec, prozhiv, ulusDict, "05"))
{
continue;
}
}
neOdinokiy.prozhiv.Add(prozhiv);
}
}
list.Add(neOdinokiy);
}
}
conn.Close();
}
}
using (TextWriter writer = new StreamWriter("kaprem_XML.csv", false, Encoding.GetEncoding(1251)))
{
List <ChelovekCsv> listcsv = new List <ChelovekCsv>();
List <RecordXML> list2csv = new List <RecordXML>();
//int id = 1;
foreach (var rec in list)
{
var item = new RecordXML();
item.IdZayavka = rec.IdZayavka;
item.Year = rec.Year;
item.RecType = rec.RecType;
switch (rec.Oso)
{
case "oso_abi@ol_sz2":
item.Oso = "Абыйский";
break;
case "oso_ala@ol_sz2":
item.Oso = "Аллаиховский";
break;
case "oso_ana@ol_sz2":
item.Oso = "Анабарский";
break;
case "oso_bul@ol_sz2":
item.Oso = "Булунский";
break;
case "oso_chu@ol_sz2":
item.Oso = "Чурапчинский";
break;
case "oso_evb@ol_sz2":
item.Oso = "Эвено-Бытантайский";
break;
case "oso_gor@ol_sz2":
item.Oso = "Горный";
break;
case "oso_len@ol_sz2":
item.Oso = "Ленский";
break;
case "oso_mom@ol_sz2":
item.Oso = "Момский";
break;
case "oso_oln@ol_sz2":
item.Oso = "Оленёкский";
break;
case "oso_vkl@ol_sz2":
item.Oso = "Верхнеколымский";
break;
case "oso_vvl@ol_sz2":
item.Oso = "Верхневилюйский";
break;
case "oso_amg@ol_sz3":
item.Oso = "Амгинский";
break;
case "oso_gat@ol_sz3":
item.Oso = "Жатайский";
break;
case "oso_gig@ol_sz3":
item.Oso = "Жиганский";
break;
case "oso_mir@ol_sz3":
item.Oso = "Мирнинский";
break;
case "oso_nam@ol_sz3":
item.Oso = "Намский";
break;
case "oso_niu@ol_sz3":
item.Oso = "Нюрбинский";
break;
case "oso_oym@ol_sz3":
item.Oso = "Оймяконский";
break;
case "oso_skl@ol_sz3":
item.Oso = "Среднеколымский";
break;
case "oso_sun@ol_sz3":
item.Oso = "Сунтарский";
break;
case "oso_usy@ol_sz3":
item.Oso = "Усть-Янский";
break;
case "oso_vil@ol_sz3":
item.Oso = "Вилюйский";
break;
case "oso_ykt@ol_sz3":
item.Oso = "Якутск";
break;
case "oso_ald@ol_sz4":
item.Oso = "Алданский";
break;
case "oso_han@ol_sz4":
item.Oso = "Хангаласский";
break;
case "oso_kob@ol_sz4":
item.Oso = "Кобяйский";
break;
case "oso_mgk@ol_sz4":
item.Oso = "Мегино-Кангаласский";
break;
case "oso_ner@ol_sz4":
item.Oso = "Нерюнгринский";
break;
case "oso_nko@ol_sz4":
item.Oso = "Нижнеколымский";
break;
case "oso_olk@ol_sz4":
item.Oso = "Олёкминский";
break;
case "oso_tat@ol_sz4":
item.Oso = "Таттинский";
break;
case "oso_tom@ol_sz4":
item.Oso = "Томпонский";
break;
case "oso_ual@ol_sz4":
item.Oso = "Усть-Алданский";
break;
case "oso_uma@ol_sz4":
item.Oso = "Усть-Майский";
break;
case "oso_vya@ol_sz4":
item.Oso = "Верхоянский";
break;
}
item.NumberZ = rec.NumberZ;
item.LmszId = rec.LmszId;
item.CategoryId = rec.CategoryId;
item.OnmszCode = rec.OnmszCode;
item.SnilsRecip = rec.SnilsRecip;
item.FamilyNameRecip = rec.FamilyNameRecip;
item.NameRecip = rec.NameRecip;
item.PatronymicRecip = rec.PatronymicRecip;
item.GenderRecip = rec.GenderRecip;
item.BirthDateRecip = rec.BirthDateRecip;
item.DoctypeRecip = rec.DoctypeRecip;
item.DocSeriesRecip = rec.DocSeriesRecip;
item.DocNumberRecip = rec.DocNumberRecip;
item.DocIssueDateRecip = rec.DocIssueDateRecip;
item.DocIssuerRecip = rec.DocIssuerRecip;
item.SnilsReason = rec.SnilsReason;
item.FamilyNameReason = rec.FamilyNameReason;
item.NameReason = rec.NameReason;
item.PatronymicReason = rec.PatronymicReason;
item.GenderReason = rec.GenderReason;
item.BirthdateReason = rec.BirthdateReason;
item.DoctypeReason = rec.DocIssuerReason;
item.DocSeriesReason = rec.DocSeriesReason;
item.DocNumberReason = rec.DocNumberReason;
item.DocIssueDateReason = rec.DocIssueDateReason;
item.DocIssuerReason = rec.DocIssuerReason;
item.DecisionDate = rec.DecisionDate;
item.DateStart = rec.DateStart;
item.DateFinish = rec.DateFinish;
item.UsingSign = rec.UsingSign;
item.Criteria = rec.Criteria;
item.FormCode = rec.FormCode;
item.Amount = rec.Amount;
item.MeasuryCode = rec.MeasuryCode;
item.Monetization = rec.Monetization;
item.Content = rec.Content;
item.Comment = rec.Comment;
item.IdRayon = rec.IdRayon;
item.prozhiv = rec.prozhiv;
list2csv.Add(item);
}
var sortedUsers = from u in list2csv
orderby u.Oso, u.FamilyNameRecip, u.NameRecip, u.PatronymicRecip
select u;
var csv = new CsvWriter(writer);
csv.Configuration.Delimiter = ";";
foreach (var rec in sortedUsers)
{
var item = new ChelovekCsv();
item.IdZayavka = rec.IdZayavka;
item.Rayon = rec.Oso;
item.FamilyNameRecip = rec.FamilyNameRecip;
item.NameRecip = rec.NameRecip;
item.PatronymicRecip = rec.PatronymicRecip;
item.BirthDateRecip = rec.BirthDateRecip;
item.DocSeriesRecip = rec.DocSeriesRecip;
item.DocNumberRecip = rec.DocNumberRecip;
item.DocIssueDateRecip = rec.DocIssueDateRecip;
item.DocIssuerRecip = rec.DocIssuerRecip;
item.DecisionDate = rec.DecisionDate;
item.DateStart = rec.DateStart;
item.DateFinish = rec.DateFinish;
item.Amount = rec.Amount;
listcsv.Add(item);
if (rec.prozhiv != null)
{
foreach (Prozhiv prozhiv in rec.prozhiv.OrderBy(f => f.FamilyNameReason).ThenBy(n => n.NameReason).ThenBy(o => o.PatronymicReason))
{
var item2 = new ChelovekCsv();
item2.IdZayavka = rec.IdZayavka;
item2.Rayon = item.Rayon;
item2.FamilyNameReason = prozhiv.FamilyNameReason;
item2.NameReason = prozhiv.NameReason;
item2.PatronymicReason = prozhiv.PatronymicReason;
item2.GenderReason = prozhiv.GenderReason;
item2.BirthdateReason = prozhiv.BirthdateReason;
item2.DocNumberReason = prozhiv.DocNumberReason;
item2.DocSeriesReason = prozhiv.DocSeriesReason;
item2.DocIssuerReason = prozhiv.DocIssuerReason;
item2.DocIssueDateReason = prozhiv.DocIssueDateReason;
listcsv.Add(item2);
}
}
//id++;
}
csv.WriteRecords(listcsv); // where values implements IEnumerable
}
return(list);
}
public bool CheckDocumentProzhiv(Record rec, Prozhiv prozhiv, Dictionary <string, string> ulusDict, string docType)
{
Regex regexSerPasp = new Regex(@"^[0-9]{4}$");
Regex regexNumberPasp = new Regex(@"^[0-9]{6}$");
//{1,3} -число повторов от 1 до 3
// Regex regexSerSvid = new Regex(@"[IVXLCDM]{1,3}[\-][А-Я]{2}");
//иначе если это паспорт проживающего
if (docType == "01")
{
//проверяем серию паспорта
Match match = regexSerPasp.Match(prozhiv.DocSeriesReason);
if (match.Success == false)
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА сер пасп прожив: " +
prozhiv.DocSeriesReason + ";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА сер пасп прожив: " + prozhiv.DocSeriesReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
if (rec.DocSeriesReason == "1111")
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА сер пасп прожив: " +
prozhiv.DocSeriesReason + ";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА сер пасп прожив: " + prozhiv.DocSeriesReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
if (rec.DocNumberReason == "111111")
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason +
";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
//проверяем номер паспорта
match = regexNumberPasp.Match(prozhiv.DocNumberReason);
if (match.Success == false)
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason +
";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
}
//просто свидетельство проверим
else if (docType == "05")
{
//проверяем номер свидетельства
Match match = regexNumberPasp.Match(prozhiv.DocNumberReason);
if (match.Success == false)
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА номер свид о рожд прожив: " + prozhiv.DocNumberReason +
";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА номер свид о рожд прожив: " + prozhiv.DocNumberReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
}
else if (docType == "00")
{
Logger.WriteToLog("rayon: " + ulusDict[rec.Oso] + ";" + " № заявки: " +
rec.NumberZ.Trim() + ";ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason +
";" + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason + ";");
ErrorItem errorItem = new ErrorItem();
errorItem.Ulus = ulusDict[rec.Oso];
errorItem.NumberZ = "№ заявки: " + rec.NumberZ.Trim();
errorItem.ErrorText = "ОШИБКА номер пасп прожив: " + prozhiv.DocNumberReason;
errorItem.FIO = "прожив: " + prozhiv.FamilyNameReason + " " +
prozhiv.NameReason + " " + prozhiv.PatronymicReason;
errorList.Add(errorItem);
return(false);
}
return(true);
}
