Esempio n. 1
0
        public static void ImportFpRawData(IEnumerable <FpRawData> records)
        {
            var index = 1;

            using var context = new FpDbContext();
            foreach (var record in records)
            {
                WriteLine($"{index++} {record.Idcard} {record.Name.FillRight(6)} {record.Type}");
                if (!string.IsNullOrEmpty(record.Idcard))
                {
                    var fpData = from e in context.FpRawData
                                 where e.Idcard == record.Idcard &&
                                 e.Type == record.Type &&
                                 e.Date == record.Date
                                 select e;
                    if (fpData.Any())
                    {
                        foreach (var data in fpData)
                        {
                            record.NO = data.NO;
                            context.Entry(data)
                            .CurrentValues.SetValues(record);
                        }
                    }
                    else
                    {
                        context.Add(record);
                    }
                    context.SaveChanges();
                }
            }
        }
Esempio n. 2
0
    public static void TestDatabaseEx()
    {
        using var db = new FpDbContext();
        Console.WriteLine(db.GetTableName <FpHistoryData>());
        Console.WriteLine(db.GetTableName <FpMonthData>());
        Console.WriteLine(db.GetTableName <Jbrymx>());

        db.DeleteAll <Jbrymx>();

        /*
         * db.LoadExcel<Jbrymx>(
         *  @"D:\精准扶贫\2019\参保人员明细表\居保参保人员明细表20191203A.xlsx",
         *  2, 101,
         *  new List<string> {"D", "A", "B", "C", "E", "F", "H", "J", "K", "N"});
         */
    }
Esempio n. 3
0
        FetchFpRawDataGroup(string month, int skip, bool onlyPkry = false)
        {
            using var db = new FpDbContext();
            IEnumerable <string> types = pkry;

            if (!onlyPkry)
            {
                types = types.Concat(cjry);
            }
            foreach (var d in db.FpRawData.Where(
                         data => data.Date == month && types.Contains(data.Type))
                     .AsEnumerable()
                     .GroupBy(data => data.Idcard).Skip(skip))
            {
                yield return(d);
            }
        }
Esempio n. 4
0
        FetchFpRawData(string month, int skip, bool onlyPkry = false)
        {
            using var db = new FpDbContext();
            IEnumerable <string> types = pkry;

            if (!onlyPkry)
            {
                types = types.Concat(cjry);
            }
            var fpRawData = from data in db.FpRawData
                            where data.Date == month &&
                            types.Contains(data.Type)
                            select data;

            foreach (var d in fpRawData.Skip(skip))
            {
                yield return(d);
            }
        }
Esempio n. 5
0
        public static void ExportFpData(
            string monthOrAll, string tmplXlsx, string saveXlsx)
        {
            using var db = new FpDbContext();

            WriteLine($"开始导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}");

            var workbook = ExcelExtension.LoadExcel(tmplXlsx);
            var sheet = workbook.GetSheetAt(0);
            int startRow = 2, currentRow = 2;

            IQueryable <Database.Jzfp2021.FpData> data = null;

            if (monthOrAll.ToUpper() == "ALL")
            {
                data = db.FpHistoryData.FromSqlRaw(
                    "SELECT * FROM fphistorydata ORDER BY CONVERT( xzj USING gbk ), " +
                    "CONVERT( csq USING gbk ), CONVERT( name USING gbk )");
            }
            else
            {
                data = db.FpMonthData.FromSqlRaw(
                    "SELECT * FROM fpmonthdata WHERE month={0} ORDER BY CONVERT( xzj USING gbk ), " +
                    "CONVERT( csq USING gbk ), CONVERT( name USING gbk )", monthOrAll);
            }

            foreach (var d in data)
            {
                var index = currentRow - startRow + 1;

                WriteLine($"{index} {d.Idcard} {d.Name}");

                var row = sheet.GetOrCopyRow(currentRow++, startRow);

                row.Cell("A").SetValue(index);
                row.Cell("B").SetValue(d.NO);
                row.Cell("C").SetValue(d.Xzj);
                row.Cell("D").SetValue(d.Csq);
                row.Cell("E").SetValue(d.Address);
                row.Cell("F").SetValue(d.Name);
                row.Cell("G").SetValue(d.Idcard);
                row.Cell("H").SetValue(d.BirthDay);
                row.Cell("I").SetValue(d.Pkrk);
                row.Cell("J").SetValue(d.PkrkDate);
                row.Cell("K").SetValue(d.Tkry);
                row.Cell("L").SetValue(d.TkryDate);
                row.Cell("M").SetValue(d.Qedb);
                row.Cell("N").SetValue(d.QedbDate);
                row.Cell("O").SetValue(d.Cedb);
                row.Cell("P").SetValue(d.CedbDate);
                row.Cell("Q").SetValue(d.Yejc);
                row.Cell("R").SetValue(d.YejcDate);
                row.Cell("S").SetValue(d.Ssjc);
                row.Cell("T").SetValue(d.SsjcDate);
                row.Cell("U").SetValue(d.Sypkry);
                row.Cell("V").SetValue(d.Jbrdsf);
                row.Cell("W").SetValue(d.JbrdsfFirstDate);
                row.Cell("X").SetValue(d.JbrdsfLastDate);
                row.Cell("Y").SetValue(d.Jbcbqk);
                row.Cell("Z").SetValue(d.JbcbqkDate);
            }

            workbook.Save(saveXlsx);

            WriteLine($"结束导出扶贫底册: {monthOrAll}扶贫数据=>{saveXlsx}");
        }
Esempio n. 6
0
        public void Execute()
        {
            var startDate = StartDate != null?ConvertToDashedDate(StartDate) : "";

            var endDate = EndDate != null?ConvertToDashedDate(EndDate) : "";

            var timeSpan = "";

            if (startDate != "")
            {
                timeSpan += startDate;
                if (endDate != "")
                {
                    timeSpan += "_" + endDate;
                }
            }
            WriteLine(timeSpan);

            var dir  = @"D:\精准扶贫\";
            var xlsx = "批量信息变更模板.xls";

            Result <Cbsh> result = null;

            Session.Use(session =>
            {
                session.SendService(new CbshQuery(startDate, endDate));
                result = session.GetResult <Cbsh>();
            });

            if (result != null)
            {
                WriteLine($"共计 {result.Count} 条");
                if (result.Count > 0)
                {
                    var workbook = ExcelExtension.LoadExcel(Path.Join(dir, xlsx));
                    var sheet = workbook.GetSheetAt(0);
                    int index = 1, copyIndex = 1;
                    var export = false;
                    using var context = new FpDbContext();
                    foreach (var cbsh in result.Data)
                    {
                        var data = from fpData in context.FpHistoryData
                                   where fpData.Idcard == cbsh.idcard
                                   select fpData;
                        if (data.Any())
                        {
                            var info = data.First();
                            WriteLine(
                                $"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay} {info.Jbrdsf} " +
                                $"{(info.Name != cbsh.name ? info.Name : "")}");
                            var row = sheet.GetOrCopyRow(index++, copyIndex, false);
                            row.Cell("B").SetValue(cbsh.idcard);
                            row.Cell("E").SetValue(cbsh.name);
                            row.Cell("J").SetValue(_jbClassMap[info.Jbrdsf]);
                            export = true;
                        }
                        else
                        {
                            WriteLine($"{cbsh.idcard} {cbsh.name.FillRight(6)} {cbsh.birthDay}");
                        }
                    }
                    if (Export && export)
                    {
                        WriteLine($"导出 批量信息变更{timeSpan}.xls");
                        workbook.Save(
                            Path.Join(dir, $"批量信息变更{timeSpan}.xls"), true);
                    }
                }
            }
        }
Esempio n. 7
0
        public void Execute()
        {
            var dlny     = Util.DateTime.ConvertToDashedDate(Date);
            var saveXlsx = $@"{Program.rootDir}\到龄贫困人员待遇核定情况表(截至{Date}).xlsx";

            var workbook = ExcelExtension.LoadExcel(Program.fphdXlsx);
            var sheet = workbook.GetSheetAt(0);
            int startRow = 3, currentRow = 3;

            Result <Dyry> result = null;

            Session.Use(session =>
            {
                session.SendService(new DyryQuery(dlny));
                result = session.GetResult <Dyry>();
            });

            if (result != null && result.Data.Count > 0)
            {
                using var context = new FpDbContext();
                foreach (var data in result.Data)
                {
                    var idcard = data.idcard;
                    var fpData = from e in context.FpRawData
                                 where e.Idcard == idcard &&
                                 (e.Type == "贫困人口" ||
                                  e.Type == "特困人员" ||
                                  e.Type == "全额低保人员" ||
                                  e.Type == "差额低保人员")
                                 select e;
                    if (fpData.Any())
                    {
                        string type = "", name = "";
                        foreach (var record in fpData)
                        {
                            if (record.Type == "贫困人口")
                            {
                                name = record.Name;
                                type = record.Type;
                                break;
                            }
                            else if (record.Type == "特困人员" &&
                                     type != "贫困人口")
                            {
                                name = record.Name;
                                type = record.Type;
                            }
                            else if (record.Type == "全额低保人员" &&
                                     type != "贫困人口" &&
                                     type != "特困人员")
                            {
                                name = record.Name;
                                type = record.Type;
                            }
                            else if (record.Type == "差额低保人员" &&
                                     string.IsNullOrEmpty(type))
                            {
                                name = record.Name;
                                type = record.Type;
                            }
                        }
                        WriteLine(
                            $"{currentRow - startRow + 1} {data.idcard} {data.name} {type}");

                        var qjns = data.Yjnx - data.Sjnx;
                        if (qjns < 0)
                        {
                            qjns = 0;
                        }

                        var row = sheet.GetOrCopyRow(currentRow++, startRow);
                        row.Cell("A").SetValue(currentRow - startRow);
                        row.Cell("B").SetValue(data.xzqh);
                        row.Cell("C").SetValue(data.name);
                        row.Cell("D").SetValue(data.idcard);
                        row.Cell("E").SetValue(data.birthDay);
                        row.Cell("F").SetValue(data.sex.ToString());
                        row.Cell("G").SetValue(data.hjClass.ToString());
                        row.Cell("H").SetValue(name);
                        row.Cell("I").SetValue(type);
                        row.Cell("J").SetValue(data.JBState);
                        row.Cell("K").SetValue(data.lqny);
                        row.Cell("L").SetValue(data.Yjnx);
                        row.Cell("M").SetValue(data.Sjnx);
                        row.Cell("N").SetValue(qjns);
                        row.Cell("O").SetValue(data.qbzt);
                        row.Cell("P").SetValue(data.bz);
                    }
                }
            }
            workbook.Save(saveXlsx);
        }
Esempio n. 8
0
        public void Execute()
        {
            var saveXlsx = $@"{Program.rootDir}\信息核对报告表{Date}.xlsx";

            Result <Dyfh> result = null;

            Session.Use(session =>
            {
                session.SendService(new DyfhQuery());
                result = session.GetResult <Dyfh>();
            });

            if (!result.IsEmpty)
            {
                using var context = new FpDbContext();
                foreach (var data in result.Data)
                {
                    var idcard = data.idcard;
                    var fpData = from e in context.FpRawData
                                 where e.Idcard == idcard &&
                                 (e.Type == "贫困人口" ||
                                  e.Type == "特困人员" ||
                                  e.Type == "全额低保人员" ||
                                  e.Type == "差额低保人员")
                                 select e;
                    if (fpData.Any())
                    {
                        data.bz = "按人社厅发〔2018〕111号文办理";
                        foreach (var record in fpData)
                        {
                            if (record.Type == "贫困人口")
                            {
                                data.fpName = record.Name;
                                data.fpType = record.Type;
                                data.fpDate = record.Date;
                                break;
                            }
                            else if (record.Type == "特困人员" &&
                                     data.fpType != "贫困人口")
                            {
                                data.fpName = record.Name;
                                data.fpType = record.Type;
                                data.fpDate = record.Date;
                            }
                            else if (record.Type == "全额低保人员" &&
                                     data.fpType != "贫困人口" &&
                                     data.fpType != "特困人员")
                            {
                                data.fpName = record.Name;
                                data.fpType = record.Type;
                                data.fpDate = record.Date;
                            }
                            else if (record.Type == "差额低保人员" &&
                                     string.IsNullOrEmpty(data.fpType))
                            {
                                data.fpName = record.Name;
                                data.fpType = record.Type;
                                data.fpDate = record.Date;
                            }
                        }
                    }
                }
            }

            var workbook = ExcelExtension.LoadExcel(Program.infoXlsx);
            var sheet = workbook.GetSheetAt(0);
            int startRow = 3, currentRow = 3;

            foreach (var data in result.Data)
            {
                var index = currentRow - startRow + 1;

                WriteLine($"{index} {data.idcard} {data.name} {data.bz} {data.fpType}");

                var row = sheet.GetOrCopyRow(currentRow++, startRow);
                row.Cell("A").SetValue(index);
                row.Cell("B").SetValue(data.name);
                row.Cell("C").SetValue(data.idcard);
                row.Cell("D").SetValue(data.xzqh);
                row.Cell("E").SetValue(data.payAmount);
                row.Cell("F").SetValue(data.payMonth);
                row.Cell("G").SetValue("是 [ ]");
                row.Cell("H").SetValue("否 [ ]");
                row.Cell("I").SetValue("是 [ ]");
                row.Cell("J").SetValue("否 [ ]");
                row.Cell("L").SetValue(data.bz ?? "");
                row.Cell("M").SetValue(data.fpType ?? "");
                row.Cell("N").SetValue(data.fpDate ?? "");
                row.Cell("O").SetValue(data.fpName ?? "");
            }
            workbook.Save(saveXlsx);
        }