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");
}
