/// <summary>
/// 检查报告是否全部生成
/// </summary>
/// <param name="tigerPath"></param>
/// <param name="formId"></param>
/// <returns></returns>
public Result CheckReportIsAll(string tigerPath, long formId)
{
return(RunFun(logPath =>
{
tigerPath = ToolFile.GetAbsolutelyPath(tigerPath);
List <FileInfo> list = new DirectoryInfo(tigerPath).GetFiles().ToList();
DataRow dr = GetFormInfo(formId);
DataTable dt = DbContent.GetTable(string.Format(GetObservedList(), dr["CompanyID"] + "", dr["FormApplicationID"] + ""));
WriteLog(logPath, "共" + dt.Rows.Count + "人");
foreach (DataRow item in dt.Rows)
{
string name = item["FullName"] + "_" + item["EntryID"];
if (!list.Exists(c => c.Name.Contains(name)))
{
WriteLog(logPath, name);
}
}
return Res;
}));
}
/// <summary>
/// 创建报告
/// </summary>
/// <param name="tigerPath">目标路径</param>
/// <returns></returns>
public Result CreatReport(string tigerPath)
{
return(RunFun(logpath =>
{
tigerPath = ToolFile.GetAbsolutelyPath(tigerPath);
string modthPath = ToolFile.GetUpIndex(base.ModelPath, 1) + "Custom\\宝龙-满意度版块PDF的副本.rdl";
string sqlStr = @"
Select distinct r.FormReportItemID,c.Text
From AskForm_FormReportItemParam r
INNER JOIN AskForm_Choice c ON c.ChoiceID = r.[Value]
Where
r.CompanyID=@CompanyID And r.FormApplicationID =@FormApplicationID
And r.IsDeleted=0";
DataTable dt = DbContent.GetTable(GetSqlParam() + sqlStr);
foreach (DataRow dr in dt.Rows)
{
ReportParameterCollection reportParameters = new ReportParameterCollection
{
new ReportParameter("CompanyID", @CompanyID),
new ReportParameter("FormApplicationID", FormApplicationID),
new ReportParameter("FormID", FormID),
new ReportParameter("MinValue", "0"),
new ReportParameter("ReportTitle", "板块报告"),
new ReportParameter("FormReportItemID", dr["FormReportItemID"] + "")
};
ToolReport.GenerateLocalReport(modthPath, tigerPath, dr["Text"] + ".pdf", reportParameters, true);
}
return Res;
}));
}
/// <summary>
/// 返回信息
/// </summary>
/// <param name="entryId"></param>
/// <returns></returns>
public DataRow GetEntryInfo(string entryId)
{
return(DbContent.GetTable(string.Format(@"
SELECT ct.*
,et.CreatedDate
INTO #dt
FROM AskForm_EntryContent ct
INNER JOIN AskForm_Entry et ON et.EntryID = ct.EntryID AND et.IsDeleted = 0
WHERE ct.EntryID = {0}
SELECT distinct FieldID ,FieldTitle ,FieldName ,Position
INTO #Fie
FROM #dt
ORDER BY Position
DECLARE @sql nvarchar(2000) = ' SELECT t.EntryID,Min(t.CreatedDate) AS ''CreatedDate'' '
DECLARE @FieldID nvarchar(200)
DECLARE @Name nvarchar(200)
WHILE EXISTS(SELECT *
FROM #Fie)
BEGIN
SELECT @FieldID= FieldID ,@Name= FieldName
FROM #Fie;
SET @sql +=',(SELECT [Value] FROM #dt WHERE EntryID = t.EntryID AND FieldID = '+@FieldID+' ) AS '''+@Name+''' '
DELETE FROM #Fie WHERE FieldID=@FieldID AND FieldName = @Name;
END
SET @sql +='FROM #dt t GROUP BY t.EntryID,t.CreatedDate ORDER BY t.CreatedDate'
exec sp_executesql @sql
DROP TABLE #dt,#Fie", entryId)).Rows[0]);
}
/// <summary>
/// 生成报告
/// </summary>
/// <param name="modelPath">报告模板</param>
/// <param name="tigerPath">生成地址</param>
/// <param name="formId">问卷编号</param>
/// <param name="entryId">被评估人编号</param>
/// <param name="suffix">报告格式</param>
/// <param name="maxLength">最多生成报告数，0为不限制</param>
/// <returns></returns>
public Result CreateReport(string modelPath, string tigerPath, long formId, long entryId, string suffix, int maxLength)
{
return(RunFun(logPath =>
{
tigerPath = ToolFile.GetAbsolutelyPath(tigerPath);
modelPath = base.ModelPath + modelPath;
DataRow dr = GetFormInfo(formId);
if (entryId > 0)
{
ReportParameterCollection col = new ReportParameterCollection
{
new ReportParameter("CompanyID", dr["CompanyID"] + ""),
new ReportParameter("FormApplicationID", dr["FormApplicationID"] + ""),
new ReportParameter("FormID", dr["FormID"] + ""),
new ReportParameter("ObservedID", entryId + ""),
new ReportParameter("EntryID", entryId + ""),
new ReportParameter("MinValue", "1")
};
col = ToolReport.BindPara(modelPath, col);
ToolReport.GenerateLocalReport(modelPath, tigerPath, entryId + "." + suffix, col, false);
}
else
{
DataTable dt = DbContent.GetTable(string.Format(GetObservedList(), dr["CompanyID"] + "", dr["FormApplicationID"] + ""));
int con = 0;
foreach (DataRow item in dt.Rows)
{
if (maxLength > 0 && con >= maxLength)
{
break;
}
ReportParameterCollection col = new ReportParameterCollection
{
new ReportParameter("CompanyID", dr["CompanyID"] + ""),
new ReportParameter("FormApplicationID", dr["FormApplicationID"] + ""),
new ReportParameter("FormID", dr["FormID"] + ""),
new ReportParameter("ObservedID", item["EntryID"] + ""),
new ReportParameter("EntryID", item["EntryID"] + ""),
new ReportParameter("MinValue", "1")
};
col = ToolReport.BindPara(modelPath, col);
con++;
WriteLog(logPath, dt.Rows.IndexOf(item) + "\t" + item["FullName"] + "_" + item["EntryID"]);
ToolReport.GenerateLocalReport(modelPath, tigerPath, item["FullName"] + "_" + item["EntryID"] + "." + suffix, col, false);
}
}
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;
}));
}
/// <summary>
/// 生成部门报告
/// </summary>
/// <param name="dsfd">dzhg</param>
/// <returns></returns>
public Result GeneratingReports()
{
//获取报告地址
string modelFileName = "满意度-药明.rdl";
string modelPath = Path.Combine(ModelPath, modelFileName);
//开始生成报告
return(RunFun((logPath) =>
{
WriteLog(logPath, "开始生成报告");
//绑定 CompanyID，FormApplicationID，FormID
ReportParameterCollection col = ToolReport.BindPara(modelPath, Convert.ToInt32(FormID), false);
//获取BUList
string prefix = GetSqlParam();
DataTable buList = DbContent.GetTable(prefix + BUListSQL);
//BU循环
foreach (DataRow buRow in buList.Rows)
{
string bu = buRow.ItemArray[0].ToString();
WriteLog(logPath, $"当前BU：{bu}");
long time = Watch(() =>
{
string departmentListSQL = DepartmentListSQL.Replace("{BU}", $"'{bu}'");
DataTable departmentList = DbContent.GetTable(prefix + departmentListSQL);
//Department循环
foreach (DataRow departmentRow in departmentList.Rows)
{
string department = departmentRow.ItemArray[0].ToString();
WriteLog(logPath, $"当前Department：{department}");
//绑定 BU,Department参数
col.Add(new ReportParameter("BU", bu));
col.Add(new ReportParameter("Department", department));
// 绑定默认参数
col = ToolReport.BindPara(modelPath, col);
//生成部门报告
ToolReport.GenerateLocalReport(modelPath, Path.Combine(DataStarPath, bu) + "\\", $"Biologics-满意度-{department}.pdf", col, true);
WriteLog(logPath, $"Biologics-满意度-{department}.pdf 报告生成完毕");
}
});
}
WriteLog(logPath, "报告生成完毕");
return Res;
}));
}
/// <summary>
/// 根据FormID，返回参数
/// </summary>
/// <param name="formID"></param>
/// <returns></returns>
protected BaseFun GetParam(long formID)
{
string sqlStr = string.Format("SELECT CompanyID,FormApplicationID,FormID FROM AskForm_Form WHERE FormID = {0} AND IsDeleted = 0", formID);
DataTable dt = DbContent.GetTable(sqlStr);
if (dt.Rows.Count >= 1)
{
BaseFun bf = new BaseFun(null);
bf.CompanyID = dt.Rows[0]["CompanyID"] + "";
bf.FormApplicationID = dt.Rows[0]["FormApplicationID"] + "";
bf.FormID = dt.Rows[0]["FormID"] + "";
return(bf);
}
return(null);
}
/// <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="formId">表单ID</param>
/// <returns></returns>
public DataRow GetFormInfo(long formId)
{
return(DbContent.GetTable(string.Format("SELECT * FROM AskForm_Form WHERE FormID = '{0}'; ", formId)).Rows[0]);
}