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();
}
}
}
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"});
*/
}
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);
}
}
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);
}
}
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}");
}
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);
}
}
}
}
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);
}
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);
}