Exemplo n.º 1
0
        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);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
Exemplo n.º 2
0
        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);
                                }
                            }
                        }
                    }
                }
            }
        }
Exemplo n.º 3
0
        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);
                                }
                            }
                        }
                    }
                }
            }
        }