示例#1
0
        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);
                            }
                        }
                    }
                }
            }
        }
示例#2
0
        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);
                                }
                            }
                        }
                    }
                }
            }
        }
示例#3
0
        public void CommonFinancingData()
        {
            using (var connection = new SqlConnection(AsistConnectionString))
            {
                using (var dataContext = new DataContext(connection))
                {
                    using (var provider = AppProvider.CreateProvider(dataContext))
                    {
                        double precent = 0;
                        var    items   = new List <SItem>();

                        var qb = new QueryBuilder(paymentDefId);
                        var sqlQueryBuilder = provider.Get <ISqlQueryBuilder>();
                        using (var query = sqlQueryBuilder.Build(qb.Def))
                        {
                            var districtRegSrc = query.JoinSource(query.Source, DistrictBankPaymentDefId,
                                                                  SqlSourceJoinType.Inner, "Registry");
                            var registrySrc = query.JoinSource(districtRegSrc, RegistryDefId, SqlSourceJoinType.Inner,
                                                               "BankPaymentRegistry");
                            var bankSrc        = query.JoinSource(registrySrc, BankDefId, SqlSourceJoinType.Inner, "Bank");
                            var bankAccountSrc = query.JoinSource(query.Source, BankAccountDefId,
                                                                  SqlSourceJoinType.Inner, "BankAccount");
                            var appSource = query.JoinSource(bankAccountSrc, AppDefId, SqlSourceJoinType.Inner,
                                                             "Application");
                            var appStateSource = query.JoinSource(appSource, AppStateDefId, SqlSourceJoinType.Inner,
                                                                  "Application_State");
                            var assignMentSrc = query.JoinSource(query.Source, AssignmentDefId, SqlSourceJoinType.Inner,
                                                                 "Assignment");

                            query.AndCondition(registrySrc, "Year", ConditionOperation.Equal, 2015);
                            query.AndCondition(registrySrc, "Month", ConditionOperation.Equal, 10);
                            // query.AndCondition(registrySrc, "Date", ConditionOperation.GreatEqual, fd);
                            // query.AndCondition(registrySrc, "Date", ConditionOperation.LessEqual, ld);
                            query.AndCondition(registrySrc, "&State", ConditionOperation.Equal, RegistryStateId);

                            query.AddAttribute(bankSrc, "PercentServices");
                            query.AddAttribute(appStateSource, "DistrictId");
                            query.AddAttribute(assignMentSrc, "No");
                            query.AddAttribute(assignMentSrc, "Month");
                            query.AddAttribute(assignMentSrc, "Amount");
                            query.AddAttribute(query.Source, "&Id", "count({0})");

                            query.AddGroupAttribute(bankSrc, "PercentServices");
                            query.AddGroupAttribute(appStateSource, "DistrictId");
                            query.AddGroupAttribute(assignMentSrc, "No");
                            query.AddGroupAttribute(assignMentSrc, "Month");
                            query.AddGroupAttribute(assignMentSrc, "Amount");

                            query.AddOrderAttribute(bankSrc, "PercentServices");
                            query.AddOrderAttribute(appStateSource, "DistrictId");
                            query.AddOrderAttribute(assignMentSrc, "No");
                            query.AddOrderAttribute(assignMentSrc, "Month");
                            query.AddOrderAttribute(assignMentSrc, "Amount");

                            var factory = provider.Get <ISqlQueryReaderFactory>();
                            using (var reader = factory.Create(query))
                            {
                                reader.Open();
                                Console.WriteLine(reader.Query.BuildSql());
                                while (reader.Read())
                                {
                                    precent = reader.GetDouble(0);
                                    var districtId  = reader.IsDbNull(1) ? Guid.Empty : reader.GetGuid(1);
                                    var no          = reader.IsDbNull(2) ? 0 : reader.GetInt32(2);
                                    var assignMonth = reader.GetInt32(3);
                                    var amount      = reader.GetDecimal(4);
                                    var count       = reader.GetInt32(5);
                                    items.Add(new SItem
                                    {
                                        DistrictId = districtId,
                                        No         = no,
                                        Month      = assignMonth,
                                        Amount     = amount,
                                        Precent    = precent,
                                        Count      = count
                                    });
                                }
                            }
                        }
                        decimal percentOfService = Convert.ToDecimal(precent);
                        foreach (var dId in items.GroupBy(i => i.DistrictId).Select(i => i.Key))
                        {
                            var     districtNumber = 0;
                            var     districtAmount = 0m;
                            decimal percentAmount  = 0m;
                            var     totalAmount    = 0m;
                            for (var q = 1; q < 5; q++)
                            {
                                var quarterSum = items.Where(i => i.DistrictId == dId && i.No == q).Sum(i => i.Count);
                                Console.WriteLine(@"Number_{0} = {1}", q.ToString(), quarterSum);
                                Console.WriteLine(@"Amount_{0} = {1}", q.ToString(), quarterSum * 100);
                                districtNumber += quarterSum;
                                districtAmount += quarterSum;
                            }
                            Console.WriteLine(@"Number_All = {0}", districtNumber);
                            Console.WriteLine(@"Amount_All = {0}", districtAmount * 100);
                            Console.WriteLine(@"Percent_Services = {0}", districtAmount * 100 * percentOfService / 100);
                            Console.WriteLine(@"Total_Amount = {0}",
                                              districtAmount * 100 + districtAmount * 100 * percentOfService / 100);
                        }
                    }
                }
            }
        }
示例#4
0
        public void BuildNewPaymentScheduleTest()
        {
            using (var connection = new SqlConnection(AsistConnectionString))
            {
                using (var dataContext = new DataContext(connection))
                {
                    using (var provider = AppProvider.CreateProvider(dataContext))
                    {
                        var items = new List <SItem2>();

                        var qb = new QueryBuilder(AssignmentDefId);
                        //qb.Where("Year").Eq(year);

                        var sqlQueryBuilder       = provider.Get <ISqlQueryBuilder>();
                        var sqlQueryReaderFactory = provider.Get <ISqlQueryReaderFactory>();

                        using (var sub = sqlQueryBuilder.Build(qb.Def))
                        {
                            sub.AddAttribute(sub.Source, "Application");
                            //query.AddAttribute(appSource, "Date", "year({0})");
                            //query.AddAttribute(appSource, "Date", "month({0})");
                            sub.AddAttribute(new[]
                            {
                                new SqlQuerySourceAttributeRef(sub.Source, "Year"),
                                new SqlQuerySourceAttributeRef(sub.Source, "Month")
                            }, "MIN(cast({0} as Int) * 16 + cast({1} as Int))").Alias = "FirstPeriod";
                            //sub.AddAttribute(sub.Source, "Month");
                            //sub.AddAttribute(sub.Source, "No");

                            sub.AddGroupAttribute(sub.Source, "Application");

                            /*sub.AddGroupAttribute(new[]
                             *  {
                             *      new SqlQuerySourceAttributeRef(sub.Source, "Year"),
                             *      new SqlQuerySourceAttributeRef(sub.Source, "Month")
                             *  }, "MIN(cast({0} as Int) * 16 + cast({1} as Int))");
                             * sub.AddGroupAttribute(sub.Source, "Month");
                             * sub.AddGroupAttribute(sub.Source, "No");*/

                            Console.WriteLine(sub.BuildSql());

                            using (var query = sqlQueryBuilder.Build(qb.Def))
                            {
                                var appSource = query.JoinSource(query.Source, AppDefId, SqlSourceJoinType.Inner,
                                                                 "Application");
                                var appStateSource =
                                    query.JoinSource(appSource, AppStateDefId, SqlSourceJoinType.Inner,
                                                     "Application_State");
                                var firstAssn = query.JoinSource(appSource, sub, SqlSourceJoinType.Inner, "Application");

                                query.AddAttribute(appStateSource, "DistrictId");
                                query.AddAttribute(firstAssn, "FirstPeriod").Alias = "Period";
                                //query.AddAttribute(appSource, "Date", "year({0})");
                                //query.AddAttribute(appSource, "Date", "month({0})");
                                //query.AddAttribute(query.Source, "Year");
                                query.AddAttribute(query.Source, "Month");
                                query.AddAttribute(query.Source, "No");
                                query.AddAttribute(query.Source, "&Id", "count({0})");
                                query.AddAttribute(query.Source, "Amount", "sum({0})");

                                //query.AndCondition(appSource, "&State", ConditionOperation.Equal, OnPaymentStateId);
                                query.AndCondition(query.Source, "Year", ConditionOperation.Equal, 2016);

                                query.AddGroupAttribute(appStateSource, "DistrictId");
                                query.AddGroupAttribute(firstAssn, "FirstPeriod");
                                //query.AddGroupAttribute(appSource, "Date", "year({0})");
                                //query.AddGroupAttribute(appSource, "Date", "month({0})");
                                // query.AddGroupAttribute(query.Source, "Year");
                                query.AddGroupAttribute(query.Source, "Month");
                                query.AddGroupAttribute(query.Source, "No");

                                query.AddOrderAttribute(appStateSource, "DistrictId");
                                query.AddOrderAttribute(firstAssn, "FirstPeriod");
                                //query.AddOrderAttribute(appSource, "Date", "year({0})");
                                //query.AddOrderAttribute(appSource, "Date", "month({0})");
                                //query.AddOrderAttribute(query.Source, "Year");
                                query.AddOrderAttribute(query.Source, "Month");
                                query.AddOrderAttribute(query.Source, "No");

                                Console.WriteLine(@"===================================================");
                                Console.WriteLine(query.BuildSql());
                                using (var reader = sqlQueryReaderFactory.Create(query))
                                {
                                    while (reader.Read())
                                    {
                                        var districtId = reader.IsDbNull(0) ? Guid.Empty : reader.GetGuid(0);
                                        //var appYear = reader.GetInt32(1);
                                        //var appMonth = reader.GetInt32(2);
                                        var aYear  = reader.GetInt32(1);
                                        var month  = reader.GetInt32(2);
                                        var no     = reader.IsDbNull(3) ? 0 : reader.GetInt32(3);
                                        var count  = reader.GetInt32(4);
                                        var amount = reader.IsDbNull(5) ? 0 : reader.GetDecimal(5);

                                        items.Add(new SItem2
                                        {
                                            DistrictId = districtId,
                                            AppYear    = aYear,
                                            /*AppMonth = appMonth,*/
                                            Month = month,
                                            No    = no,
                                            Count = count
                                        });
                                        Console.WriteLine(
                                            @"District: {0}; AppYear: {1}-{2}; Month: {3}; No: {4}; Count: {5}; Amount: {6}", districtId,
                                            aYear / 16, aYear % 16,
                                            month,
                                            no,
                                            count, amount);
                                    }
                                }
                            }
                            var decisions = new List <SItem2>();
                            qb = new QueryBuilder(TerminationDefId); // Decision Of Termination
                            qb.Where("&State").Eq(CompletedDecisionStateId);
                            using (var query = sqlQueryBuilder.Build(qb.Def))
                            {
                                var appSource = query.JoinSource(query.Source, AppDefId, SqlSourceJoinType.Inner,
                                                                 "Application");
                                var appStateSource =
                                    query.JoinSource(appSource, AppStateDefId, SqlSourceJoinType.Inner,
                                                     "Application_State");
                                sub.AddGroupAttribute("Amount");
                                sub.AddAttribute(sub.Source, "Year", "MAX({0})").Alias = "Max_Year";
                                var firstAssn = query.JoinSource(appSource, sub, SqlSourceJoinType.Inner, "Application");

                                query.AddAttribute(appStateSource, "DistrictId");
                                query.AddAttribute(firstAssn, "FirstPeriod").Alias = "Period";
                                query.AddAttribute(query.Source, "Date", "month({0})");
                                query.AddAttribute(query.Source, "Date", "year({0})");
                                query.AddAttribute(firstAssn, "Amount", "sum({0})").Alias = "Amount";
                                query.AddAttribute(query.Source, "&Id", "count({0})");
                                query.AddAttribute(firstAssn, "Max_Year").Alias = "Max_Year";

                                //query.AndCondition(query.Source, "Date", ConditionOperation.GreatEqual, new DateTime(2016, 1, 1).AddMonths(-9));
                                //query.AndCondition(query.Source, "Date", ConditionOperation.LessThen, new DateTime(2016, 1, 1).AddMonths(12));
                                query.AndCondition(firstAssn, "Max_Year", ConditionOperation.Equal, 2016);

                                query.AddGroupAttribute(appStateSource, "DistrictId");
                                query.AddGroupAttribute(firstAssn, "FirstPeriod");
                                query.AddGroupAttribute(firstAssn, "Max_Year");
                                query.AddGroupAttribute(query.Source, "Date", "month({0})");
                                query.AddGroupAttribute(query.Source, "Date", "year({0})");
                                // query.AddGroupAttribute(firstAssn, "Amount");

                                query.AddOrderAttribute(appStateSource, "DistrictId");
                                query.AddOrderAttribute(firstAssn, "FirstPeriod");
                                query.AddOrderAttribute(firstAssn, "Max_Year");
                                query.AddOrderAttribute(query.Source, "Date", "month({0})");
                                query.AddOrderAttribute(query.Source, "Date", "year({0})");

                                Console.WriteLine(@"Decision of Terminations ****************************");
                                Console.WriteLine(query.BuildSql());
                                using (var reader = sqlQueryReaderFactory.Create(query))
                                {
                                    while (reader.Read())
                                    {
                                        var districtId = reader.IsDbNull(0) ? Guid.Empty : reader.GetGuid(0);
                                        var period     = reader.GetInt32(1);
                                        var month      = reader.IsDbNull(2) ? 0 : reader.GetInt32(2);
                                        var year       = reader.IsDbNull(3) ? 0 : reader.GetInt32(3);
                                        var amount     = reader.IsDbNull(4) ? 0 : reader.GetDecimal(4);
                                        var count      = reader.GetInt32(5);
                                        var maxYear    = reader.GetInt32(6);

                                        if (districtId == Guid.Empty)
                                        {
                                            continue;
                                        }
                                        decisions.Add(new SItem2
                                        {
                                            DistrictId = districtId,
                                            AppYear    = period,
                                            Month      = month,
                                            //                            No = no,
                                            Count = count
                                        });
                                        Console.WriteLine(
                                            @"District: {0}; AppYear: {1}-{2}; Year: {3}; Month: {4}; Count: {5}; Amount: {6}; Max-Year: {7}", districtId,
                                            period / 16, period % 16,
                                            year, month,
                                            count, amount, maxYear);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }