public XlsGridReportDefBuilder(IDataContext dataContext, BizForm form, SqlQueryReader reader, Guid userId)
{
DataContext = dataContext;
Form = form;
UserId = userId;
SqlDataSet = new SqlQueryDataSet(reader, userId);
}
public XlsReportDefBuilder(IAppServiceProvider provider, ReportDef report, SqlQueryReader reader)
{
Report = report;
SqlDataSet = new SqlQueryDataSet(provider, reader);
Headers.Add(new XlsGridReportSectionText(Report.Caption));
}
public XlsGridDefBuilder(IAppServiceProvider provider, BizForm form, SqlQueryReader reader)
{
Provider = provider;
Form = form;
UserId = provider.GetCurrentUserId();
SqlDataSet = new SqlQueryDataSet(Provider, reader);
}
public void XlsGridReportDefBuilderTest()
{
using (var connection = new SqlConnection(CissaConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = AppProvider.CreateProvider(dataContext))
{
var formRepo = provider.Get <IFormRepository>(); //new FormRepository(dataContext);
var form = formRepo.GetTableForm(Guid.Parse("{B46A77AB-3F36-42CD-998A-018BE911AD16}"));
var defRepo = provider.Get <IDocDefRepository>(); //new DocDefRepository(dataContext);
var docDef = defRepo.DocDefById(form.DocumentDefId ?? Guid.Empty);
var qb = new QueryBuilder(docDef.Id);
qb.Where("PostCode").Eq("10");
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
//var query = new DocQuery(qb.Def, dataContext);
var query = sqlQueryBuilder.Build(qb.Def, form, null /*, Guid.Empty, dataContext*/);
query.TopNo = 100;
using (var reader = new SqlQueryReader(dataContext, query))
{
var defBuilder = new XlsGridReportDefBuilder(dataContext, form, reader /*.All()*/,
Guid.Empty);
defBuilder.AddHeaderText("Тестовый отчет").Bold().Center().FontDSize(4);
defBuilder.AddHeaderText("за 04.2015").Bold().Center();
defBuilder.AddFooterText("Подпись: ").Right().Margins(10, 10);
var table = defBuilder.AddFooterTable();
table.Bold().Center().Margins(30, 30).Style.Borders = TableCellBorder.All;
table.Add(0, 0, "Руководитель");
table.Add(1, 0, "Финансовый менеджер");
table.Add(2, 0, "Начальник отдела АСП");
table.Add(0, 1, "Подпись");
table.Add(1, 1, "Подпись");
table.Add(2, 1, "Подпись");
table.Add(0, 2, "Дата");
table.Add(1, 2, "Дата");
table.Add(2, 2, "Дата").Italic().Right();
var def = defBuilder.BuildFromBizForm();
def.Style.FontName = "Arial Narrow";
def.Style.VAlign = VAlignment.Middle;
var builder = new XlsBuilder(def);
var workbook = builder.Build();
using (
var stream = new FileStream(@"c:\distr\cissa\testXlsGridReportDefBuilder.xls",
FileMode.Create))
{
workbook.Write(stream);
}
}
}
}
}
}
public void Fill(SqlQueryReader reader)
{
if (!reader.Active)
{
reader.Open();
}
Fill(reader.Reader);
}
private void PrepareFields(SqlQueryReader reader)
{
if (_fields.Count == 0)
{
foreach (var readerField in reader.Fields)
{
var field = new QueryReaderField(readerField.AttributeName)
{
AttributeId = readerField.AttributeId,
DataType = readerField.DataType,
FieldIndex = readerField.Index,
AttrDef = readerField.AttrDef
};
_fields.Add(field);
PrepareField(field);
}
}
else
{
foreach (var field in _fields)
{
if (field.AttributeId != Guid.Empty)
{
var readerField = reader.FindField(field.AttributeId);
if (readerField != null)
{
field.DataType = readerField.DataType;
field.AttributeName = readerField.AttributeName;
field.FieldIndex = readerField.Index;
field.AttrDef = readerField.AttrDef;
}
else
{
field.DataType = BaseDataType.Unknown;
}
}
else
{
var readerField = reader.FindField(field.AttributeName);
if (readerField != null)
{
field.DataType = readerField.DataType;
field.AttributeId = readerField.AttributeId;
field.FieldIndex = readerField.Index;
field.AttrDef = readerField.AttrDef;
}
else
{
field.DataType = BaseDataType.Unknown;
}
}
PrepareField(field);
}
}
}
public SqlQueriesController(SqlQueryReader sqlQueryReader,
SqlRunner runner,
ResultRepository resultRepository,
SessionRepository sessionRepository,
Tabularizer tabularizer)
{
_sqlQueryReader = sqlQueryReader;
_runner = runner;
_resultRepository = resultRepository;
_sessionRepository = sessionRepository;
_tabularizer = tabularizer;
}
public XElement GetValueElement(SqlQueryReader reader, SqlQueryField field)
{
var name = GetFieldElementName(field);
if (reader.IsDbNull(field.Index))
{
return(new XElement(name, ""));
}
if (field.AttrDef != null)
{
switch (field.AttrDef.Type.Id)
{
case (short)CissaDataType.Enum:
if (field.AttrDef.EnumDefType == null)
{
break;
}
var enumDef = _enumRepo.Get(field.AttrDef.EnumDefType.Id);
var enumItem = enumDef.EnumItems.FirstOrDefault(i => i.Id == reader.GetGuid(field.Index));
if (enumItem != null)
{
return(new XElement(name, new XElement("Id", enumItem.Id),
new XElement("Value", enumItem.Value)));
}
break;
case (short)CissaDataType.Doc:
if (PublicDocAttribute(field.AttrDef))
{
var doc = _docRepo.LoadById(reader.GetGuid(field.Index));
return(new XElement(name, GetDocElement(doc)));
}
break;
/*
* case (short) CissaDataType.DocList:
* if (PublicDocAttribute(field.AttrDef))
* {
* DocRepo.Ge
* var docList = new DocList(DataContext);
* return new XElement(name, GetDocListElement(doc));
* }
* break;
*/
}
}
return(new XElement(name, reader.GetValue(field.Index)));
}
private void AddColumn(CrossDataTable table, ReportDef report, SqlQueryReader reader, ReportAttributeColumnDef column, Dictionary <Guid, SqlQuerySource> sourceMap,
SqlQueryDataSet dataSet, Dictionary <CrossDataColumn, SqlQueryDataSetField> columnMaps)
{
SqlQuerySource querySource;
var reportSource = report.GetSourceDef(column.Attribute.SourceId);
var reportSourceAttr = reportSource.Attributes != null
? reportSource.Attributes.FirstOrDefault(a => a.Id == column.Attribute.AttributeId)
: null;
var hasQuerySource = sourceMap.TryGetValue(column.Attribute.SourceId, out querySource);
var attr = reportSourceAttr == null
? !hasQuerySource
? reader.Query.FindAttribute(column.Attribute.AttributeId)
: reader.Query.FindAttribute(querySource, column.Attribute.AttributeId)
: !hasQuerySource
? reader.Query.FindAttribute(reportSourceAttr.Ident)
: reader.Query.FindAttribute(querySource, reportSourceAttr.Ident);
if (attr != null)
{
var i = reader.Query.Attributes.IndexOf(attr);
if (column.Grouping == ReportColumnGroupingType.None ||
column.Grouping == ReportColumnGroupingType.Group)
{
var crossColumn = new CrossDataKeyColumn(i, column.Caption);
table.AddColumn(crossColumn);
columnMaps.Add(crossColumn,
new SqlQueryDataSetField(dataSet, attr,
column.Grouping == ReportColumnGroupingType.None
? SqlQuerySummaryFunction.None
: SqlQuerySummaryFunction.Group));
}
else if (column.Grouping == ReportColumnGroupingType.CrossGroup)
{
var crossColumn = new CrossDataGroupColumn(i, column.Caption);
table.AddColumn(crossColumn);
columnMaps.Add(crossColumn, new SqlQueryDataSetField(dataSet, attr));
}
else
{
var crossColumn = new CrossDataFuncColumn(i, column.Caption, column.ToSqlGrouping());
table.AddColumn(crossColumn);
columnMaps.Add(crossColumn, new SqlQueryDataSetField(dataSet, attr, column.ToSqlGrouping()));
}
}
}
protected override void Prepare()
{
if (Reader != null)
{
Reader.Dispose();
Reader = null;
}
if (Query == null && QueryDef != null)
{
Query = _sqlQueryBuilder.Build(QueryDef); // SqlQueryBuilder.Build(DataContext, QueryDef, (Guid) UserId);
PrepareQueryAttributes(Query);
}
Reader = new SqlQueryReader(DataContext, Query);
Reader.Open();
PrepareFields(Reader);
}
private CrossDataTable CreateCrossDataTable(ReportDef report, SqlQueryReader reader)
{
var table = new CrossDataTable();
var sourceMap = MapSources(report, reader);
var columnMaps = new Dictionary <CrossDataColumn, SqlQueryDataSetField>();
var dataSet = new SqlQueryDataSet(Provider, reader);
if (report.Columns != null)
{
foreach (var column in report.Columns.OfType <ReportAttributeColumnDef>())
{
AddColumn(table, report, reader, column, sourceMap, dataSet, columnMaps);
}
}
table.Fill(dataSet, columnMaps);
return(table);
}
public void TestMethod3()
{
using (var connection = new SqlConnection(CissaConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = AppProvider.CreateProvider(dataContext))
{
var formRepo = provider.Get <IFormRepository>(); //new FormRepository(dataContext);
var form = formRepo.GetTableForm(Guid.Parse("{B46A77AB-3F36-42CD-998A-018BE911AD16}"));
var defRepo = provider.Get <IDocDefRepository>(); // new DocDefRepository(dataContext);
var docDef = defRepo.DocDefById(form.DocumentDefId ?? Guid.Empty);
var qb = new QueryBuilder(docDef.Id);
qb.Where("PostCode").Eq("10");
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
//var query = new DocQuery(qb.Def, dataContext);
using (var query = sqlQueryBuilder.Build(qb.Def, form, null))
{
query.TopNo = 100;
using (var reader = new SqlQueryReader(dataContext, query))
{
var defBuilder = new XlsGridDefBuilder(provider, form, reader /*.All()*/);
var def = defBuilder.BuildFromBizForm();
def.Style.FontName = "Arial Narrow";
def.Style.VAlign = VAlignment.Middle;
var builder = new XlsBuilder(def);
var workbook = builder.Build();
using (
var stream = new FileStream(@"c:\distr\cissa\testXlsDefApps.xls", FileMode.Create))
{
workbook.Write(stream);
}
}
}
}
}
}
}
private Dictionary <Guid, SqlQuerySource> MapSources(ReportDef report, SqlQueryReader reader)
{
var result = new Dictionary <Guid, SqlQuerySource>();
foreach (var sourceDef in report.Sources.Where(s => s.DocDef != null))
{
if (reader.Query.Source.IsDocDef(sourceDef.Id) && !result.ContainsValue(reader.Query.Source))
{
result.Add(sourceDef.Id, reader.Query.Source);
}
else
{
var querySource =
reader.Query.Sources.FirstOrDefault(
s => s.IsDocDef(sourceDef.Id) && !result.ContainsValue(s));
if (querySource != null)
{
result.Add(sourceDef.Id, querySource);
}
}
}
return(result);
}
public SqlQueryDataSet(IAppServiceProvider provider, SqlQueryReader reader)
{
Provider = provider;
Reader = reader;
}
public SqlQueryDataSet(SqlQueryReader reader, Guid userId)
{
Reader = reader;
}
public void NrszReport3Test()
{
var docId = new Guid("ab5e0193-97ff-4644-82cd-bb9ba8236dee");
using (var connection = new SqlConnection(NrszConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = GetProvider(dataContext))
{
var docRepo = provider.Get <IDocRepository>();
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
var letter = docRepo.LoadById(docId);
//Формирование запроса*
var qb = new QueryBuilder(AssignedServiceDefId);
qb.Where("&Id").Eq(letter.Id);
using (var query = sqlQueryBuilder.Build(qb.Def))
{
var personSrc = query.JoinSource(query.Source, PersonDefId, SqlSourceJoinType.Inner, "Person");
query.AddAttribute(personSrc, "Last_Name");
query.AddAttribute(personSrc, "First_Name");
query.AddAttribute(personSrc, "Middle_Name");
query.AddAttribute(personSrc, "IIN");
query.AddAttribute(personSrc, "Date_of_Birth");
query.AddAttribute(personSrc, "PassportSeries");
query.AddAttribute(personSrc, "PassportNo");
query.AddAttribute(query.Source, "ServiceType");
query.AddAttribute(query.Source, "DateFrom");
query.AddAttribute(query.Source, "DateTo");
query.AddAttribute(query.Source, "Amount");
/*query.AddAttribute(appStateSrc, "Street");
* query.AddAttribute(appStateSrc, "House");
* query.AddAttribute(appStateSrc, "Flat");*/
//query.AddAttribute(query.Source, "&Id");
using (var reader = new SqlQueryReader(dataContext, query))
{
//Создание потока из XML файла из которого формируется Word документ
using (var xmlStream = new FileStream(@"c:\cissaFiles\xml\BuildBenefitPersonalInformation.xml", FileMode.Open))
{
//Создатель образа Word документа из XML
var xmlBuilder = new XmlWordDocDefBuilder(xmlStream);
//Подключение запроса
xmlBuilder.AddDataSet("query", new SqlQueryDataSet(provider, reader));
//Формирование образа Word документа
var wordDef = xmlBuilder.Build();
//Создатель Word документа
var wordBuilder = new WordDocBuilder(wordDef);
//Создание потока куда будет записан Word документ
using (var wordDocStream = new FileStream(@"c:\cissaFiles\asistPrintFiles\BenefitPersonalInformation.doc", FileMode.Create))
//создание документа и сохранение в файл
wordBuilder.Build(wordDocStream);
}
}
}
}
}
}
}
public IEnumerable <XElement> BuildCurrent(SqlQueryReader reader)
{
return(reader.Fields.Select(fld => GetValueElement(reader, fld)));
}
public void XlsTestMethod()
{
using (var connection = new SqlConnection(AsistConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = GetProvider(dataContext))
{
using (var def = new XlsDef())
{
var qb = new QueryBuilder(PaymentDefId);
//qb.Where("Registry").Eq(sheet.Id);
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
var query = sqlQueryBuilder.Build(qb.Def);
var bankAccountSrc = query.JoinSource(query.Source, BankAccountDefId,
SqlSourceJoinType.Inner,
"BankAccount");
var assignSrc = query.JoinSource(query.Source, AssignmentDefId, SqlSourceJoinType.Inner,
"Assignment");
var appSrc = query.JoinSource(assignSrc, AppDefId, SqlSourceJoinType.Inner, "Application");
var personSrc = query.JoinSource(appSrc, PersonDefId, SqlSourceJoinType.Inner, "Person");
var appStateSrc = query.JoinSource(appSrc, AppStateDefId, SqlSourceJoinType.Inner,
"Application_State");
var districtSrc = query.JoinSource(appStateSrc, DistrictDefId, SqlSourceJoinType.LeftOuter,
"DistrictId");
query.AddAttribute(personSrc, "IIN");
query.AddAttribute(bankAccountSrc, "Account_No");
query.AddAttribute(personSrc, "Last_Name");
query.AddAttribute(personSrc, "First_Name");
query.AddAttribute(personSrc, "Middle_Name");
query.AddAttribute(personSrc, "PassportSeries");
query.AddAttribute(personSrc, "PassportNo");
query.AddAttribute("&Id");
query.AddAttribute(assignSrc, "Amount");
var h = def.AddArea().AddRow();
h.AddNode("Id");
h.AddNode("ПИН");
h.AddNode("Номер банковского счета");
h.AddNode("Фамилия, Имя, Отчество");
h.AddNode("Номер паспорта");
h.AddNode("Сумма пособия");
using (var reader = new SqlQueryReader(dataContext, query))
{
while (reader.Read())
{
var inn = !reader.IsDbNull(0) ? reader.GetString(0) : "";
var accountNo = !reader.IsDbNull(1) ? reader.GetString(1) : "";
var ln = !reader.IsDbNull(2) ? reader.GetString(2) : "";
var fn = !reader.IsDbNull(3) ? reader.GetString(3) : "";
var mn = !reader.IsDbNull(4) ? reader.GetString(4) : "";
var ps = !reader.IsDbNull(5) ? reader.GetString(5) : "";
var pNo = !reader.IsDbNull(6) ? reader.GetString(6) : "";
var id = !reader.IsDbNull(7) ? reader.GetGuid(7) : Guid.Empty;
decimal amount = !reader.IsDbNull(8) ? reader.GetDecimal(8) : 0;
var r = def.AddArea().AddRow();
r.AddColumn().AddText(id.ToString());
r.AddColumn().AddText(inn);
r.AddColumn().AddText(accountNo);
r.AddColumn().AddText(string.Format("{0} {1} {2}", ln, fn, mn));
r.AddColumn().AddText(string.Format("{0} {1}", ps, pNo));
r.AddColumn().AddFloat((double)amount);
}
}
var builder = new XlsBuilder(def);
var workbook = builder.Build();
using (
var stream = new FileStream(
"c:\\distr\\cissa\\UnloadingBankPaymentRegistry.xls", FileMode.Create))
{
workbook.Write(stream);
}
}
}
}
}
}
public void PrintAsistAssignNotifications()
{
var fd = new DateTime(2015, 1, 1); //DateTime);period["DateFrom"];
var ld = new DateTime(2015, 12, 31); //DateTime);period["DateTo"];
var districtId = new Guid("592d7513-edb6-4f7d-8f05-d71fdb16c041");
using (var connection = new SqlConnection(AsistConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = GetProvider(dataContext))
{
// Формирование запроса
var qb = new QueryBuilder(AssignmentNoteDefId);
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
using (var query = sqlQueryBuilder.Build(qb.Def))
{
var bankAccountSrc = query.JoinSource(query.Source, BankAccountDefId,
SqlSourceJoinType.Inner, "Bank_Account");
var appSrc = query.JoinSource(bankAccountSrc, AppDefId, SqlSourceJoinType.Inner,
"Application");
var personSrc = query.JoinSource(appSrc, PersonDefId, SqlSourceJoinType.Inner, "Person");
var appStateSrc = query.JoinSource(appSrc, AppStateDefId, SqlSourceJoinType.Inner,
"Application_State");
var districtSrc = query.JoinSource(appStateSrc, DistrictDefId, SqlSourceJoinType.LeftOuter,
"DistrictId");
var djamoatSrc = query.JoinSource(appStateSrc, DjamoatDefId, SqlSourceJoinType.LeftOuter,
"DjamoatId");
var villageSrc = query.JoinSource(appStateSrc, VillageDefId, SqlSourceJoinType.LeftOuter,
"VillageId");
var bankInfoSrc = query.JoinSource(districtSrc, BankFilialDefId, SqlSourceJoinType.LeftOuter,
"District");
var bankNameSrc = query.JoinSource(bankInfoSrc, BankDefId, SqlSourceJoinType.LeftOuter,
"Bank");
//query.AndCondition(query.Source, "&State", ConditionOperation.Equal, newStateId);
query.AndCondition(appSrc, "ApplicationDate", ConditionOperation.GreatEqual, fd);
query.AndCondition(appSrc, "ApplicationDate", ConditionOperation.LessEqual, ld);
/*if (districtId != Guid.Empty)
* query.AndCondition(appStateSrc, "DistrictId", ConditionOperation.Equal, districtId);*/
query.AddAttribute(appSrc, "ApplicationDate");
query.AddAttribute(appSrc, "No");
query.AddAttribute(personSrc, "IIN");
query.AddAttribute(bankAccountSrc, "Account_No");
query.AddAttribute(bankAccountSrc, "Application");
query.AddAttribute(personSrc, "Last_Name");
query.AddAttribute(personSrc, "First_Name");
query.AddAttribute(personSrc, "Middle_Name");
query.AddAttribute(personSrc, "PassportSeries");
query.AddAttribute(personSrc, "PassportNo");
query.AddAttribute(appStateSrc, "Street");
query.AddAttribute(appStateSrc, "House");
query.AddAttribute(appStateSrc, "Flat");
query.AddAttribute(query.Source, "&Id");
query.AddAttribute(bankNameSrc, "Name");
query.AddAttribute(bankInfoSrc, "No.Branch");
query.AddAttribute(bankInfoSrc, "Address");
query.AddAttribute(villageSrc, "Name").Alias = "VillageName";
query.AddAttribute(districtSrc, "Name").Alias = "DistrictName";
query.AddAttribute(djamoatSrc, "Name").Alias = "DjamoatName";
var assignqb = new QueryBuilder(AssignmentDefId);
assignqb.Where("Application").Eq(Guid.Empty, "appId");
var assignQuery = sqlQueryBuilder.Build(assignqb.Def);
assignQuery.AddAttribute("Month");
assignQuery.AddAttribute("Year");
using (var notifications = new SqlQueryReader(provider, query))
{
using (var assignments = new SqlQueryReader(provider, assignQuery))
{
assignments.AddMasterLink(notifications, "Application", "appId");
// Создание потока из XML файла из которого формируется Word документ
using (
var xmlStream =
new FileStream(@"c:\cissaFiles\xml\BuildBenefitAssignNotifications.xml",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
//@"c:\distr\cissa\BuildWordDocReportTest.xml"
// Создатель образа Word документа из XML
var xmlBuilder = new XmlWordDocDefBuilder(xmlStream);
// Подключение запроса
xmlBuilder.AddDataSet("notifications", new SqlQueryDataSet(provider, notifications));
xmlBuilder.AddDataSet("assignments", new SqlQueryDataSet(provider, assignments));
xmlBuilder.AddFunction("MMMM", GetTjMonthNames);
// Формирование образа Word документа
var wordDef = xmlBuilder.Build();
// Создатель Word документа
var wordBuilder = new WordDocBuilder(wordDef);
// Создание потока куда будет записан Word документ
using (
var wordDocStream =
new FileStream(
@"c:\cissaFiles\asistPrintFiles\BenefitAssignNotification.doc",
FileMode.Create))
// создание документа и сохранение в файл
wordBuilder.Build(wordDocStream);
}
}
}
}
}
}
}
}
public void TestMethod2()
{
using (var connection = new SqlConnection(ChatkalConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var mdc = new MultiDataContext(new[] { dataContext }))
{
var factory = AppServiceProviderFactoryProvider.GetFactory();
using (var provider = factory.Create())
{
var defRepo = provider.Get <IDocDefRepository>(); // new DocDefRepository(dataContext);
var docDef = defRepo.DocDefByName("Person-Sheet");
using (var def = new XlsDef())
{
// Header
def.Style.FontName = "Arial Narrow";
def.AddArea().AddRow().AddEmptyCell();
def.AddArea().AddRow().AddText("Список граждан");
def.AddArea().AddRow().AddEmptyCell();
// Grid Header
var h1 = def.AddArea().AddRow();
foreach (var attr in docDef.Attributes)
{
h1.AddNode(attr.Name).ShowAllBorders(true);
}
var qb = new QueryBuilder(docDef.Id);
qb.Where("LastName").Contains("ИВАН");
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
var query = sqlQueryBuilder.Build(qb.Def);
query.AddAttribute("&Id");
var list = new List <Guid>();
using (var r = new SqlQueryReader(dataContext, query))
while (r.Read())
{
list.Add(r.GetGuid(0));
}
using (var docDataSet = new DocDataSet(provider, dataContext, list, Guid.Empty))
{
var gridRow = def.AddGrid(docDataSet).AddRow();
foreach (var attr in docDef.Attributes)
{
gridRow.ShowAllBorders(true);
gridRow.Style.AutoWidth = true;
gridRow.Style.AutoHeight = true;
gridRow.AddDataField(new DocDataSetField(docDataSet, attr));
}
var builder = new XlsBuilder(def);
var workbook = builder.Build();
using (
var stream = new FileStream(@"c:\distr\cissa\testXlsDefPersons.xls", FileMode.Create))
{
workbook.Write(stream);
}
}
}
}
}
}
}
}
public void TestXmlWordDocDefBuilder()
{
using (var connection = new SqlConnection(AsistConnectionString))
{
using (var dataContext = new DataContext(connection))
{
using (var provider = GetProvider(dataContext))
{
var qb = new QueryBuilder(AssignmentNoteDefId);
//qb.Where("Registry").Eq(sheet.Id);
var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
var query = sqlQueryBuilder.Build(qb.Def);
var bankAccountSrc = query.JoinSource(query.Source, BankAccountDefId, SqlSourceJoinType.Inner,
"Bank_Account");
var appSrc = query.JoinSource(bankAccountSrc, AppDefId, SqlSourceJoinType.Inner, "Application");
var personSrc = query.JoinSource(appSrc, PersonDefId, SqlSourceJoinType.Inner, "Person");
var appStateSrc = query.JoinSource(appSrc, AppStateDefId, SqlSourceJoinType.Inner,
"Application_State");
var districtSrc = query.JoinSource(appStateSrc, DistrictDefId, SqlSourceJoinType.LeftOuter,
"DistrictId");
var villageSrc = query.JoinSource(appStateSrc, VillageDefId, SqlSourceJoinType.LeftOuter,
"VillageId");
query.AddAttribute(personSrc, "IIN");
query.AddAttribute(bankAccountSrc, "Account_No");
query.AddAttribute(bankAccountSrc, "Application");
query.AddAttribute(personSrc, "Last_Name");
query.AddAttribute(personSrc, "First_Name");
query.AddAttribute(personSrc, "Middle_Name");
query.AddAttribute(personSrc, "PassportSeries");
query.AddAttribute(personSrc, "PassportNo");
query.AddAttribute("&Id");
query.AddAttribute(appSrc, "ApplicationDate");
query.AddAttribute(personSrc, "PassportSeries");
query.AddAttribute(personSrc, "PassportNo");
query.AddAttribute(appStateSrc, "Street");
query.AddAttribute(appStateSrc, "House");
query.AddAttribute(appStateSrc, "Flat");
query.AddAttribute(query.Source, "&Id");
query.AddAttribute(villageSrc, "Name").Alias = "VillageName";
query.AddAttribute(districtSrc, "Name").Alias = "DistrictName";
qb = new QueryBuilder(AssignmentDefId);
qb.Where("Application").Eq(Guid.Empty, "appId");
var assignQuery = sqlQueryBuilder.Build(qb.Def);
assignQuery.AddAttribute("Month");
assignQuery.AddAttribute("Year");
using (var r = new SqlQueryReader(dataContext, query))
{
using (var assignments = new SqlQueryReader(dataContext, assignQuery))
{
assignments.AddMasterLink(r, "Application", "appId");
using (var stream =
new FileStream(@"C:\CissaFiles\xml\BuildBenefitFailureNotification.xml" /*"c:\distr\cissa\BuildWordDocReportTest2.xml"*/, FileMode.Open))
{
var builder = new XmlWordDocDefBuilder(stream);
builder.AddDataSet("notifications", new SqlQueryDataSet(provider, r));
builder.AddDataSet("assignments", new SqlQueryDataSet(provider, assignments));
var wdDef = builder.Build();
var b = new WordDocBuilder(wdDef);
using (
var wordDocStream = new FileStream(@"c:\distr\cissa\wordDocFromXmlTest2.doc",
FileMode.Create))
b.Build(wordDocStream);
}
}
}
}
}
}
}