//принимаем ссылку на форму для вытаскивания месяца и года
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);
}
