public ResponseData GetData(FindTranDataModel req, HeaderRep headerRep, bool is_test = false) { string user_id = CurrentUserId; if (!is_test && user_id == null) { ErrorString = "Не был определен пользователь для входа в систему."; return(data_empty); } try { // Установка всех фильтров var filterAddInfRep = new HandleFilterParams <AddInfRep>(); var filterBasicRep = new HandleFilterParams <BasicRep>(); var filterClientRep = new HandleFilterParams <ClientRep>(); var filterIssuerRep = new HandleFilterParams <IssuerRep>(); var filterNoticeRep = new HandleFilterParams <NoticeRep>(); var filterAddRep = new HandleFilterParams <AddRep>(); var filterRep406 = new HandleFilterParams <Rep406>(); var filterSupdocRep = new HandleFilterParams <SupdocRep>(); bool is_rep_406 = false; bool is_cr = false; bool is_ir = false; bool is_nr = false; bool is_add_rep = false; bool is_sr = false; // Условие фильтра дубликатов и статуса NEW // if (!req.onlyDuple) // filterAddInfRep.AddField(CreateParam("AddInfRep_Status", "string", "not_equal", NEW_STR)); filterAddInfRep.AddField(CreateParam("AddInfRep_Status", "string", req.onlyDuple ? "equal" : "not_equal", DUPLICATE_STR)); // Добавить правила if (req.Rules != null) { foreach (QueryBuildRule qbr in req.Rules) { string table_name = GetClassName(qbr.id); if (table_name == "AddInfRep") { filterAddInfRep.AddField(qbr); } else if (table_name == "BasicRep") { filterBasicRep.AddField(qbr); } else if (table_name == "ClientRep") { filterClientRep.AddField(qbr); is_cr = true; } else if (table_name == "IssuerRep") { filterIssuerRep.AddField(qbr); is_ir = true; } else if (table_name == "NoticeRep") { filterNoticeRep.AddField(qbr); is_nr = true; } else if (table_name == "AddRep") { filterAddRep.AddField(qbr); is_add_rep = true; } else if (table_name == "Rep406") { filterRep406.AddField(qbr); is_rep_406 = true; } else if (table_name == "SupdocRep") { filterSupdocRep.AddField(qbr); is_sr = true; } else { throw new ApplicationException("Несуществующее имя таблицы: " + table_name); } } } // Получить все выражения bool all = false; var check_filials = filterBasicRep.GetExpression(GetFilterFilials(user_id, out all), "Filial", all); var check_clients = filterClientRep.GetExpression(GetFilterClients(user_id, out all), "TypeRez", all); var check_supdocs = filterAddInfRep.GetExpression(GetFilterSupdocs(user_id, out all), "SupdocFl", all); var check_addinf_rep = filterAddInfRep.GetExpression(); var check_basic_rep = filterBasicRep.GetExpression(); var check_client_rep = filterClientRep.GetExpression(); var check_issuer_rep = filterIssuerRep.GetExpression(); var check_notice_rep = filterNoticeRep.GetExpression(); var check_add_rep = filterAddRep.GetExpression(); var check_rep_406 = filterRep406.GetExpression(); var check_supdoc_rep = filterSupdocRep.GetExpression(); int total_records = db.AddInfReps.Where(x => x.IdRep == headerRep.Id).Select(x => x.IdOper).Count(); // Запрос операций, удовлетворяющих всем условиям всех фильтров. var Operations = from ar in db.AddInfReps.AsExpandable() join br in db.BasicReps.AsExpandable() on ar.IdOper equals br.IdOper join cr_left in db.ClientReps.AsExpandable() on ar.IdOper equals cr_left.IdOper into cr_1 join ir_left in db.IssuerReps.AsExpandable() on ar.IdOper equals ir_left.IdOper into ir_1 join nr_left in db.NoticeReps.AsExpandable() on ar.IdOper equals nr_left.IdOper into nr_1 join r406_left in db.Rep406s.AsExpandable() on ar.IdOper equals r406_left.IdOper into r406_1 join sr_left in db.SupdocReps.AsExpandable() on ar.IdOper equals sr_left.IdOper into sr_1 join add_rep_left in db.AddReps.AsExpandable() on ar.IdOper equals add_rep_left.IdOper into add_rep_1 from cr in cr_1.DefaultIfEmpty() from nr in nr_1.DefaultIfEmpty() from ir in ir_1.DefaultIfEmpty() from r406 in r406_1.DefaultIfEmpty() from sr in sr_1.DefaultIfEmpty() from add_rep in add_rep_1.DefaultIfEmpty() where headerRep.Id == ar.IdRep && (!is_rep_406 || (is_rep_406 && ar.Form == "406")) && (!is_cr || (is_cr && cr != null)) && (!is_ir || (is_ir && ir != null)) && (!is_nr || (is_nr && nr != null)) && (!is_add_rep || (is_add_rep && add_rep != null)) && (!is_sr || (is_sr && sr != null)) && check_filials.Invoke(br) && (cr == null || check_clients.Invoke(cr)) && check_supdocs.Invoke(ar) && check_addinf_rep.Invoke(ar) && check_basic_rep.Invoke(br) && (cr == null || check_client_rep.Invoke(cr)) && (ir == null || check_issuer_rep.Invoke(ir)) && (nr == null || check_notice_rep.Invoke(nr)) && (add_rep == null || check_add_rep.Invoke(add_rep)) && (r406 == null || check_rep_406.Invoke(r406)) && (sr == null || check_supdoc_rep.Invoke(sr)) orderby br.Filial, br.PostDate select new GridData { // AddInfRep Form = ar.Form, Section = ar.Section, Status = ar.Status, SupdocFl = ar.SupdocFl, DateLock = ar.DateLock, DateRemove = ar.DateRemove, NumJD = ar.NumJD, NumDVK = ar.NumDVK, DateRequest = ar.DateRequest, DateGetDoc = ar.DateGetDoc, CNum = ar.CNum, CBAccount = ar.CBAccount, // BasicRep Filial = br.Filial, PostDate = br.PostDate, TypeTooling = br.TypeTooling, OperType = br.OperType, DirectionPay = br.DirectionPay, Count = br.Count, Share = br.Share, Ccy = br.Ccy, AmountAll = br.AmountAll, AmntIncome = br.AmntIncome, RefNum = br.RefNum, CodeVO = br.CodeVO, // (cr.TypeRez == PERSON ? CODE_VO_PERSON : br.CodeVO), ValueDate = br.ValueDate, Swift = br.Swift, SwiftFil = br.SwiftFil, // ClientRep NameRez = (cr == null ? string.Empty : cr.NameRez), TypeRez = (cr == null ? string.Empty : cr.TypeRez), BicPartner = (cr == null ? string.Empty : cr.BicPartner), CountryRez = (cr == null ? string.Empty : cr.CountryRez), INN = (cr == null ? string.Empty : cr.INN), NameNerez = (cr == null ? string.Empty : cr.NameNerez), TypeNerez = (cr == null ? string.Empty : cr.TypeNerez), CountryNerez = (cr == null ? string.Empty : cr.CountryNerez), BankName = (cr == null ? string.Empty : cr.BankName), CountryBank = (cr == null ? string.Empty : cr.CountryBank), PartnerName = (cr == null ? string.Empty : cr.PartnerName), CountryPartner = (cr == null ? string.Empty : cr.CountryPartner), CustNo = (cr == null ? string.Empty : cr.CustNo), // IssuerRep IssuerName = (ir == null ? string.Empty : ir.IssuerName), SecurityCode = (ir == null ? string.Empty : ir.SecurityCode), RegNumIssuer = (ir == null ? string.Empty : ir.RegNumIssuer), DateRegIssuer = (ir == null ? null : ir.DateRegIssuer), RepayDate = (ir == null ? null : ir.RepayDate), CcyIssuer = (ir == null ? string.Empty : ir.CcyIssuer), IssuerCode = (ir == null ? null : ir.IssuerCode), IssuerReestr = (ir == null ? string.Empty : ir.IssuerReestr), // VK_406_REP OperKind = (r406 == null ? null : r406.OperKind), Notice40 = (r406 == null ? string.Empty : r406.Notice40), AmountTo = (r406 == null ? null : r406.AmountTo), AmountFrom = (r406 == null ? null : r406.AmountFrom), BicBank = (r406 == null ? string.Empty : r406.BicBank), CountryBank406 = (r406 == null ? string.Empty : r406.CountryBank406), AddCode10 = (r406 == null ? string.Empty : r406.AddCode10), KindRez = (r406 == null ? string.Empty : r406.KindRez), KindNerez = (r406 == null ? string.Empty : r406.KindNerez), KindContract = (r406 == null ? string.Empty : r406.KindContract), TypeContract = (r406 == null ? string.Empty : r406.TypeContract), Notice15 = (r406 == null ? string.Empty : r406.Notice15), Notice16 = (r406 == null ? string.Empty : r406.Notice16), NoticeOther = (r406 == null ? string.Empty : r406.NoticeOther), // VK_SUPDOC_REP CodeSupdoc = (sr == null ? string.Empty : sr.CodeSupdoc), SupdocName = (sr == null ? string.Empty : sr.SupdocName), Prinvest = (sr == null ? null : sr.Prinvest), EntryDate = (sr == null ? null : sr.EntryDate), SupdocType = (sr == null ? string.Empty : sr.SupdocType), RegNumBranch = (sr == null ? string.Empty : sr.RegNumBranch), ClientName = (sr == null ? string.Empty : sr.ClientName), SourceSupdoc = (sr == null ? string.Empty : sr.SourceSupdoc), PassportNum = (sr == null ? string.Empty : sr.PassportNum), DocDate = (sr == null ? null : sr.DocDate), UpdateSdDate = (sr == null ? null : sr.UpdateSdDate), OperationId = ar.IdOper }; int count_rows = Operations.Count(); GridData[] arr_operations = new GridData[] { }; if (count_rows != 0) { CorrectProperties.Correction <GridData>(Operations); // Добавить правила сортировки полей StringBuilder sb = new StringBuilder(); if (req.OrderRules != null) { foreach (FindTranOrder fto in req.OrderRules) { sb.AppendFormat("{0} {1},", GridData.GetFieldName(fto.column), fto.dir); } } string order_by = sb.ToString().TrimEnd(','); if (req.StartRowNo >= count_rows) { ErrorString = "Некорректный номер строки для выбора из списка операций."; return(data_empty); } int page_length = ((req.StartRowNo + req.PageLength) <= count_rows) ? req.PageLength : (count_rows - req.StartRowNo); // Получение сортированного списка if (!String.IsNullOrEmpty(order_by)) { Operations = Operations.OrderBy(order_by); } arr_operations = Operations.Skip(req.StartRowNo).Take(page_length).ToArray(); } return(new ResponseData { recordsTotal = total_records, // количество всех записей отчета recordsFiltered = count_rows, // количество отфильтрованных записей data = GetDataArray(arr_operations) // массив со значениями строк }); } catch (Exception ex) { ErrorString = "Некорректное условие в фильтре: " + ex.Message; return(data_empty); } }
public string CreateKlikoFiles(HeaderRep headerRep, string export_path, string filials, string type_clients) { try { export_path = export_path.TrimEnd(new char[] { '\\', '/' }) + @"\"; string filterFilial = GetFilialFilter(filials); Expression <Func <BasicRep, bool> > CheckFilials = System.Linq.Dynamic.DynamicExpression.ParseLambda <BasicRep, bool>(filterFilial); string filterTypeClients = GetTypeClientFilter(type_clients); Expression <Func <ClientRep, bool> > CheckTypeClients = System.Linq.Dynamic.DynamicExpression.ParseLambda <ClientRep, bool>(filterTypeClients); var Operations = (from ar in db.AddInfReps.AsExpandable() join br in db.BasicReps.AsExpandable() on ar.IdOper equals br.IdOper join cr in db.ClientReps.AsExpandable() on ar.IdOper equals cr.IdOper join ir_left in db.IssuerReps.AsExpandable() on ar.IdOper equals ir_left.IdOper into ir_1 join nr_left in db.NoticeReps.AsExpandable() on ar.IdOper equals nr_left.IdOper into nr_1 join r406_left in db.Rep406s.AsExpandable() on ar.IdOper equals r406_left.IdOper into r406_1 from nr in nr_1.DefaultIfEmpty() from ir in ir_1.DefaultIfEmpty() from r406 in r406_1.DefaultIfEmpty() where headerRep.Id == ar.IdRep && ar.Status == INCLUDE && CheckFilials.Invoke(br) && (cr == null || CheckTypeClients.Invoke(cr)) orderby br.Filial, br.PostDate, br.CodeVO select new KlikoInfo { Form = ar.Form, Section = ar.Section, // BasicRep Filial = br.Filial, PostDate = br.PostDate, CodeVO = (cr.TypeRez == PERSON ? CODE_VO_PERSON : br.CodeVO), TypeTooling = br.TypeTooling, OperType = br.OperType, DirectionPay = br.DirectionPay, Count = br.Count, Share = br.Share, Ccy = br.Ccy, AmountAll = br.AmountAll, AmntIncome = br.AmntIncome, ValueDate = br.ValueDate, Swift = br.Swift, SwiftFil = br.SwiftFil, // ClientRep BicPartner = cr.BicPartner, CountryRez = cr.CountryRez, NameRez = cr.NameRez, TypeRez = cr.TypeRez, INN = cr.INN, NameNerez = cr.NameNerez, TypeNerez = cr.TypeNerez, CountryNerez = cr.CountryNerez, BankName = cr.BankName, CountryBank = cr.CountryBank, PartnerName = cr.PartnerName, CountryPartner = cr.CountryPartner, // IssuerRep IssuerName = (ir == null ? string.Empty : ir.IssuerName), SecurityCode = (ir == null ? string.Empty : ir.SecurityCode), RegNumIssuer = (ir == null ? string.Empty : ir.RegNumIssuer), DateRegIssuer = (ir == null ? null : ir.DateRegIssuer), RepayDate = (ir == null ? null : ir.RepayDate), CcyIssuer = (ir == null ? null : ir.CcyIssuer), IssuerCode = (ir == null ? null : ir.IssuerCode), IssuerReestr = (ir == null ? null : ir.IssuerReestr), // NoticeRep Notice = (nr == null ? string.Empty : nr.Notice), NoticeIssuer = (nr == null ? string.Empty : nr.NoticeIssuer), NoticeExchange = (nr == null ? string.Empty : nr.NoticeExchange), NoticeInst = (nr == null ? string.Empty : nr.NoticeInst), NoticeProperty = (nr == null ? string.Empty : nr.NoticeProperty), NoticeBank = (nr == null ? string.Empty : nr.NoticeBank), // VK_406_REP OperKind = (r406 == null ? null : r406.OperKind), AmountTo = (r406 == null ? null : r406.AmountTo), AmountFrom = (r406 == null ? null : r406.AmountFrom), BicBank = (r406 == null ? null : r406.BicBank), CountryBank406 = (r406 == null ? null : r406.CountryBank406), AddCode10 = (r406 == null ? null : r406.AddCode10), KindRez = (r406 == null ? null : r406.KindRez), KindNerez = (r406 == null ? null : r406.KindNerez), KindContract = (r406 == null ? null : r406.KindContract), TypeContract = (r406 == null ? null : r406.TypeContract), Notice15 = (r406 == null ? null : r406.Notice15), Notice16 = (r406 == null ? null : r406.Notice16), Notice40 = (r406 == null ? null : r406.Notice40), NoticeOther = (r406 == null ? null : r406.NoticeOther) }).ToList(); // Коррекция свойств CorrectProperties.Correction <KlikoInfo>(Operations); // Создание файлов List <string> sections = GetSections(); string[] sections_for_names = sections.ConvertAll <string>(x => x = x.Replace(".", "")).ToArray(); string[] file_names = CreateFileNames(type_clients, headerRep, sections_for_names); bool[] first_tags = Enumerable.Repeat(true, file_names.Length).ToArray(); List <KlikoInfo> Operations406 = Operations.FindAll(x => x.Form == FORM_406); if (Operations406.Count > 0) { string file406 = export_path + file_names[0]; using (StreamWriter file = new StreamWriter(file406, false, Encoding.GetEncoding(866))) { file.WriteLine(FIRST_LINE_FORM406); foreach (KlikoInfo info in Operations406) { AddOperationToFile(file, GetAttributesForm406Array(info)); } } } for (int i = 0; i < sections.Count; i++) { List <KlikoInfo> Operations405 = Operations.FindAll(x => x.Form == FORM_405 && x.Section == sections[i]); if (Operations405.Count > 0) { string file405 = export_path + file_names[i + 1]; using (StreamWriter file = new StreamWriter(file405, true, Encoding.GetEncoding(866))) { file.WriteLine(String.Format(FIRST_LINE_FORM405, sections[i][0])); foreach (KlikoInfo info in Operations405) { AddOperationToFile(file, GetAttributesForm405Array(info)); } } } } } catch (Exception ex) { return("Ошибка при экспорте в КЛИКО: " + ex.Message); } return("true"); }