/// <summary>
/// 报告数据导入数据库
/// </summary>
/// <param name="dirPath"></param>
/// <param name="formId"></param>
/// <param name="reportId"></param>
/// <returns></returns>
public Result AddReportItem(string dirPath = "D:\\Shared\\弘阳报告", long formId = 8374570001, long reportId = 15581150001)
{
return(RunFun(logpath =>
{
string path = "\\Survey\\ExportReports\\";// 根目录
// 获取表单GUID并拼接路径
string sqlStr = "SELECT * FROM AskForm_Form WHERE FormID = " + formId;
DataTable dt = DbContent.GetTable(sqlStr);
string companyId = "";
string appId = "";
if (dt.HasItems(c => c.Columns.Contains("FormGuid")))
{
path += dt.Rows[0]["FormGuid"] + "\\" + reportId + "\\";
companyId = dt.Rows[0]["CompanyID"] + "";
appId = dt.Rows[0]["FormApplicationID"] + "";
}
else
{
return Res;
}
// 获取文件夹下所有文件
if (Directory.Exists(dirPath))
{
string[] pdfArr = Directory.GetFiles(dirPath);
foreach (var pdf in pdfArr)
{
string fileName = ToolFile.GetFileName(pdf, false);
string name = ToolFile.GetFileName(pdf);
sqlStr = string.Format(@"INSERT AskForm_FormReportItem SELECT {0},{1},'{2}','','{3}',0,{4},{5},{6},0,GETDATE(),GETDATE()"
, reportId, ToolString.GetRandomStr(12, 6), name, path + fileName, companyId, appId, formId);
DbContent.ExecuteNonQuery(sqlStr);
WriteLog(logpath, sqlStr);
}
}
return Res;
}));
}
/// <summary>
/// 生成团队报告
/// </summary>
/// <param name="modelPath">报告模板</param>
/// <param name="tigerPath">生成地址</param>
/// <param name="formId">问卷编号</param>
/// <param name="suffix">报告格式</param>
/// <returns></returns>
public Result CreateTeamReport(string modelPath, string tigerPath, long formId, string suffix)
{
return(RunFun(logPath =>
{
tigerPath = ToolFile.GetAbsolutelyPath(tigerPath);
modelPath = base.ModelPath + modelPath;
string sqlFilter = $"SELECT * FROM AskForm_FormFilter ff WHERE ff.FormID = {formId} AND ff.Name LIKE '%FieldFilter%' AND ff.IsDeleted = 0";
DataTable dtFilter = DbContent.GetTable(sqlFilter);
// 循环过滤条件
foreach (DataRow filter in dtFilter.Rows)
{
ReportParameterCollection col = new ReportParameterCollection
{
new ReportParameter("CompanyID", filter["CompanyID"] + ""),
new ReportParameter("FormApplicationID", filter["FormApplicationID"] + ""),
new ReportParameter("FormID", filter["FormID"] + ""),
new ReportParameter("FormFilterID", filter["FormFilterID"] + ""),
new ReportParameter("MinValue", "1")
};
col = ToolReport.BindPara(modelPath, col);
// 获取过滤字段
string sqlField = $"SELECT f.FieldID,f.Title,fff.Content FROM AskForm_FormFilterField fff INNER JOIN AskForm_Field f ON f.FieldID = fff.FieldID WHERE FormFilterID = {filter["FormFilterID"]} AND fff.IsDeleted = 0";
DataTable dtField = DbContent.GetTable(sqlField);
// 循环字段
foreach (DataRow field in dtField.Rows)
{
string sqlContent = $"SELECT distinct [Value] FROM AskForm_EntryText et WHERE et.FieldID = {field["FieldID"]} AND et.IsDeleted = 0";
DataTable dtContent = DbContent.GetTable(sqlContent);
// 循环内容
foreach (DataRow content in dtContent.Rows)
{
// 修改内容
string sqlUpFilterField = $"UPDATE AskForm_FormFilterField SET Content='{content["Value"]}' WHERE FormFilterID = {filter["FormFilterID"]} AND FieldID ={field["FieldID"]} ";
WriteLog(logPath, "修改【" + field["Title"] + "】为【" + content["Value"] + "】，" + DbContent.ExecuteNonQuery(sqlUpFilterField));
ToolReport.GenerateLocalReport(modelPath, tigerPath, field["Title"] + "_" + content["Value"] + "." + suffix, col, false);
}
}
}
return Res;
}));
}