//принимаем ссылку на форму для вытаскивания месяца и года public override List <Record> Load(LiquidTechnologies.Test.SampleApp form) { errorList = new List <ErrorItem>(); string dir = System.AppDomain.CurrentDomain.BaseDirectory; if (File.Exists(dir + "\\Log.txt")) { File.Delete(dir + "\\Log.txt"); } CultureInfo culture = new CultureInfo("ru-RU"); culture.NumberFormat.NumberDecimalSeparator = "."; culture.NumberFormat.CurrencyDecimalSeparator = "."; culture.DateTimeFormat.ShortDatePattern = "dd.MM.yyyy"; Thread.CurrentThread.CurrentUICulture = culture; Thread.CurrentThread.CurrentCulture = culture; List <Record> records = new List <Record>(); DateTime date = DateTime.ParseExact(form.dateTimeGetter.Text, "MMMM yyyy", CultureInfo.CurrentCulture); //год для поиска в таблицах по годам int year = date.Year; //по какой год будем искать - зависит от года int yearEnd = year - 3; //год для выплаты int yearPayment = date.Year; string yearBase = year.ToString().Substring(year.ToString().Length - 2); int month = date.Month; int dateStart = 1; int dateEnd = 0; //находим последний день месяца if (month < 12) { dateEnd = new DateTime(date.Year, month + 1, 1).AddDays(-1).Day; } else { dateEnd = 31; } string monthVal = ""; if (month < 10) { monthVal = "0" + month; } //сперва находим - есть ли авансовый год using (NpgsqlConnection conn = new NpgsqlConnection("Server=10.50.0.82;Port=5432;Database=SubsUOW;User Id=postgres;Password=3I5BEvynPQ;")) { NpgsqlCommand cmd = new NpgsqlCommand("select get_max_year() as max_year", conn); conn.Open(); using (NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { int maxYear = 0; maxYear = Int32.Parse(reader["max_year"].ToString()); //увеличиваем на год если есть авансовый год if (maxYear != year) { year++; } } } } Dictionary <string, string> reverseUluses = GetReverseUlus(); for (int i = year; i > yearEnd; i--) { string commandText = "select DISTINCT 'Fact' as RecType, " + "'0688203c-0fe1-49bc-88d6-40f6fee800b6' as LMSZID," + "'59c8cf04-a207-4704-b40f-f0843b230b11' as categoryID, " + "'0336.000001' as ONMSZCode, " + "zayavitel.snils as SNILS_recip," + "zayavitel.familia as FamilyName_recip, " + "zayavitel.imya as Name_recip," + "zayavitel.otchestvo as Patronomic_recip, " + "(CASE WHEN zayavitel.pol = 'М' THEN 'М' ELSE 'Ж' END) as Gender_recip," + "to_char(zayavitel.data_rozhd,'dd.mm.yyyy') as BirthDate_recip," + "'01' as doctype_recip," + "zayavitel.passport_seria as doc_Series_recip," + "zayavitel.passport_number as doc_Number_recip, " + "to_char(zayavitel.data_vidachi_passport, 'dd.mm.yyyy') as doc_IssueDate_recip," + "zayavitel.kem_vidan_passport as doc_Issuer_recip," + "zayavitel.snils as SNILS_reason," + "zayavitel.familia as FamilyName_reason," + "zayavitel.imya as Name_reason," + "zayavitel.otchestvo as Patronomic_reason, " + "(CASE WHEN zayavitel.pol = 'М' THEN 'М' ELSE 'Ж' END) as Gender_reason," + "to_char(zayavitel.data_rozhd,'dd.mm.yyyy') as BirthDate_reason, " + "'01' as doctype_reason," + "zayavitel.passport_seria as doc_Series_reason," + "zayavitel.passport_number as doc_Number_reason, " + "to_char(zayavitel.data_vidachi_passport, 'dd.mm.yyyy') as doc_IssueDate_reason, " + "zayavitel.kem_vidan_passport as doc_Issuer_reason," + "to_char(zayav.data_reshenia, 'dd.mm.yyyy') as decision_date, " + "to_char(zayav.komp_s, 'dd.mm.yyyy') as dateStart, " + "to_char(zayav.komp_po, 'dd.mm.yyyy') as dateFinish," + "'Нет' as UsingSign, " + "'' as Criteria," + "'01' as FormCode," + "payment.clear_payment as amount," + "'01' as measuryCode," + "'Нет' as monetization," + "'' as content," + "'' as comment," + "payment.id_z as id_z, payment.id_rayon as id_rayon, " + //айдишка и год заявления нужна для последующего выбора снилсов для проживающих "zayav.id as id_zayav," + i + " as year, " + "zayav.number_zayavka as number_z, " + "rayon.rayon as rayon " + "from " + " payment" + yearPayment + " payment " + " join reestr on payment.id_reestr= reestr.id " + " join zayavlenie" + i + " zayav on zayav.id_z= payment.id_z " + " join zayavitel on payment.id_z= zayavitel.id " + " join rayon on rayon.id=payment.id_rayon " + " where reestr.month_year= date'" + yearPayment + "-" + month + "-01' and zayav.restore= false and zayav.proveden= true " + " and zayav.data_dok>='" + yearPayment + "-" + month + "-01' and zayav.data_dok<='" + yearPayment + "-" + month + "-" + dateEnd + "' order by rayon,FamilyName_recip,Name_recip,Patronomic_recip"; using (NpgsqlConnection conn = new NpgsqlConnection("Server=10.50.0.82;Port=5432;Database=SubsUOW;User Id=postgres;Password=3I5BEvynPQ;")) { NpgsqlCommand cmd = new NpgsqlCommand(commandText, conn); conn.Open(); using (NpgsqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Record record = new Record(); try { record.IdZayavka = reader["id_zayav"].ToString(); } catch (Exception ex) { //id-шек - только перерасчет //вообще пропускаем - не разобраться за какой месяц дали, по какому заявлению и т.д continue; } record.Year = reader["year"].ToString(); record.RecType = reader["RecType"].ToString().Trim(); record.LmszId = reader["LMSZID"].ToString().Trim(); record.CategoryId = reader["categoryID"].ToString().Trim(); record.OnmszCode = reader["ONMSZCode"].ToString().Trim(); record.SnilsRecip = reader["SNILS_recip"].ToString().Trim(); record.FamilyNameRecip = reader["FamilyName_recip"].ToString().Trim(); record.NameRecip = reader["Name_recip"].ToString().Trim(); record.NumberZ = reader["number_z"].ToString(); record.Oso = reverseUluses[reader["rayon"].ToString()]; try { record.PatronymicRecip = reader["Patronomic_recip"].ToString().Trim(); } catch (Exception) { record.PatronymicRecip = "-"; } record.GenderRecip = reader["Gender_recip"].ToString().Trim(); record.BirthDateRecip = (reader["BirthDate_recip"].ToString() == "") ? (DateTime?)null : DateTime.ParseExact(reader["BirthDate_recip"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DoctypeRecip = reader["doctype_recip"].ToString().Trim(); record.DocSeriesRecip = reader["doc_Series_recip"].ToString().Replace(" ", "").Trim(); record.DocNumberRecip = reader["doc_Number_recip"].ToString().Trim(); record.DocIssueDateRecip = (reader["doc_IssueDate_recip"].ToString() == "") ? (DateTime?)null : DateTime.ParseExact(reader["doc_IssueDate_recip"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DocIssuerRecip = reader["doc_Issuer_recip"].ToString().Trim(); record.SnilsReason = reader["SNILS_reason"].ToString().Trim(); record.FamilyNameReason = reader["FamilyName_reason"].ToString().Trim(); record.NameReason = reader["Name_reason"].ToString().Trim(); try { record.PatronymicReason = reader["Patronomic_reason"].ToString().Trim(); } catch { record.PatronymicReason = "-"; } record.GenderReason = reader["Gender_reason"].ToString().Trim(); record.BirthdateReason = DateTime.ParseExact(reader["BirthDate_reason"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DoctypeReason = reader["doctype_reason"].ToString(); record.DocSeriesReason = reader["doc_Series_reason"].ToString().Replace(" ", "").Trim(); record.DocNumberReason = reader["doc_Number_reason"].ToString().Trim(); record.DocIssueDateReason = DateTime.ParseExact(reader["doc_IssueDate_reason"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DocIssuerReason = reader["doc_Issuer_reason"].ToString().Trim(); record.DecisionDate = (reader["decision_date"].ToString() == "") ? (DateTime?)null : DateTime.ParseExact(reader["decision_date"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DateStart = (reader["dateStart"].ToString() == "") ? (DateTime?)null : DateTime.ParseExact(reader["dateStart"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.DateFinish = (reader["dateFinish"].ToString() == "") ? (DateTime?)null : DateTime.ParseExact(reader["dateFinish"].ToString(), "dd.MM.yyyy", CultureInfo.CurrentCulture); record.UsingSign = reader["usingSign"].ToString().Trim(); record.Criteria = reader["criteria"].ToString().Trim(); record.FormCode = reader["FormCode"].ToString().Trim(); if (reader["amount"].ToString() == "") { continue; } else if (decimal.Parse(reader["amount"].ToString()) == 0) { continue; } record.Amount = (reader["amount"].ToString() == "") ? (Decimal?)null : Decimal.Parse(reader["amount"].ToString().Replace(',', '.')); record.MeasuryCode = reader["measuryCode"].ToString(); record.Monetization = reader["monetization"].ToString(); record.Comment = reader["comment"].ToString(); record.IdRayon = Int32.Parse(reader["id_rayon"].ToString()); //если снилс не проходит, то пропускаем if (record.RecType == "Fact") { if (!CheckSnilsKapRem(record, GetUlus(), false)) { continue; } } else { if (!CheckSnilsKapRem(record, GetUlus(), true)) { continue; } } records.Add(record); } } } } Dictionary <int, string> monthes = Monthes(); using (TextWriter writer = new StreamWriter("капрем_" + monthes[month] + "_" + yearPayment + ".csv", false, Encoding.GetEncoding(1251))) { var csv = new CsvWriter(writer); csv.Configuration.Delimiter = ";"; csv.WriteRecords(records); // where values implements IEnumerable } return(records); }
public virtual List <Record> Load(LiquidTechnologies.Test.SampleApp form) { return(null); }