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