private static void InitExcelHandlers()
{
// 指定的多个带有controller的assembly的名称
string handlerAssemblies = AppSettingConfig.GetSetting("ExcelHandlerAssemblies", ""); //"Wanda.HR.KPI.Web.Common";
_excelHandlers = new Dictionary <string, ExcelCategoryHandler>();
string[] assemblyArray = handlerAssemblies.Split(",;".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
List <Type> handlerTypes = new List <Type>();
foreach (var assembly in assemblyArray)
{
var types = (from t in Assembly.Load(assembly).GetTypes()
where IsSubClassOf(t, typeof(ExcelCategoryHandler))
select t).ToList();
handlerTypes.AddRange(types);
}
foreach (Type type in handlerTypes)
{
string typeName = type.Name.ToLower();
// todo alias
ExcelCategoryHandler handler = (ExcelCategoryHandler)Activator.CreateInstance(type);
_excelHandlers.Add(typeName, handler);
}
}
public static void ResetAppTime(DateTime newDate)
{
if (AppSettingConfig.GetSetting("UseMockDate", "false").ToLower() == "true")
{
DateTime newAppTime = newDate.Add(DateTime.Now.Subtract(DateTime.Now.Date));
TimeSpan offset = newAppTime.Subtract(DateTime.Now);
HttpContext.Current.Session[TimeSessionKey] = offset;
}
}
/// <summary>
/// 从配置文件中读取要排除的流程
/// </summary>
/// <returns></returns>
private List <string> GetExcludeProcessCode()
{
string PAISONG_PROCESS_CODES = AppSettingConfig.GetSetting("PaiSongProcess", "");
if (string.IsNullOrEmpty(PAISONG_PROCESS_CODES))
{
return(null);
}
return(PAISONG_PROCESS_CODES.Split(",;".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList());
}
/// <summary>
/// 获取上月的未完成公司
/// </summary>
/// <param name="Expression">未完成公式</param>
/// <param name="TargetIDs">未完成指标ID组合</param>
/// <returns></returns>
private int GetLastMonthCompanyCount(string Expression, List <Guid> TargetIDs, List <MonthlyReportDetail> _LastMissTargetList)
{
List <Guid> Companys = new List <Guid>();
//计算上月的
foreach (MonthlyReportDetail d in _LastMissTargetList)
{
if (TargetIDs.Contains(d.TargetID)) //上月指标包含当前指标
{
Hashtable bizContext = BizContextEngine.BizContextService.GetBizContext(_LastMissTargetList.FindAll(R => R.CompanyID == d.CompanyID), "A_MonthlyReportDetail");
ExpressionParser _parser = new ExpressionParser(bizContext);
//解析这个公式
//其中任何一个指标数据满足Counter的条件，此公司的数据都应该归类于该Counter
if (_parser.CacluateCondition(Expression))
{
if (!Companys.Contains(d.CompanyID))
{
Companys.Add(d.CompanyID);
}
}
}
}
//---Update 2015-5-13 这里只有单个指标不用理会公司（商管，物管)
string MissTargetSystemName = AppSettingConfig.GetSetting("MissTargetSystemName", "");
if (MissTargetSystemName.Contains(_System.SystemName) == false)
{
if (!IsSingleTarget)
{
//将补回的数据从总的数据中移除掉
MissTargetTextList.RemoveAll(Re => Companys.Exists(R => R == Re.CompanyID));
}
}
//--Update 2015-5-13 End
return(Companys.Count);
}
/// <summary>
/// 当月未完成数据导入（直管模版的）
/// </summary>
/// <param name="filePathName"></param>
/// <param name="error"></param>
/// <param name="rpt"></param>
/// <param name="MonthRptID"></param>
/// <returns></returns>
private List <MonthlyReportDetail> ReadCurrentDirectlyMonthlyReportDetailExcel(string filePathName, StringBuilder error, ReportInstance rpt, Guid MonthRptID)
{
List <MonthlyReportDetail> list = new List <MonthlyReportDetail>();
ExcelEngine excel = new ExcelEngine();
Workbook book = new Workbook(filePathName);
Worksheet sheet = book.Worksheets[0];
if (excel.GetStringCustomProperty(sheet, "SheetName") != "MissTargetRptExcel")
{
error.Append("请上传正确的报表文件");
return(null);
}
int count = book.Worksheets.Count;// sheet个数
if (count == 0)
{
error.Append("模板错误");
return(null);
}
WorksheetCollection sheets = book.Worksheets;
//当次上报的 所有未完成公司的指标
List <MonthlyReportDetail> missTargetList = rpt.ReportDetails;
//循环Sheet
for (int i = 0; i < count; i++)
{
//当次上报的 所有未完成公司的指标
int startRow = 4;
int endRow = 0;
int startColumn = 1;
int endColumn = 15;
Cells cells = sheets[i].Cells;
endRow = cells.MaxDataRow;
endColumn = cells.MaxDataColumn;
//读取Excel中的数据
DataTable dt = cells.ExportDataTableAsString(startRow, startColumn, endRow - startRow + 1, endColumn);
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
DateTime PromissDate = DateTime.Now;
MonthlyReportDetail data = missTargetList.Where(M => M.TargetName == dr[2].ToString()).OrderBy(p => p.CreateTime).FirstOrDefault();
if (data == null)
{
error.Append("请重新下载Excel后，填写上报");
return(null);
}
if (data.TargetID.ToString().ToUpper() == AppSettingConfig.GetSetting("MonthSGRent", "").ToUpper() && data.NDifference < 0 && data.IsMissTarget == false)
{
data.CurrentMIssTargetReason = dr[11].ToString(); //原因
data.CurrentMIssTargetDescription = dr[12].ToString(); //采取措施
}
else //这有当月未完成，累计完成的情况，才会填写原因以及措施
{
data.CurrentMIssTargetReason = dr[11].ToString(); //原因
data.CurrentMIssTargetDescription = dr[12].ToString(); //采取措施
}
list.Add(data);
}
}
}
return(list);
}
/// <summary>
/// 当月未完成数据导入
/// </summary>
/// <param name="filePathName"></param>
/// <param name="error"></param>
/// <param name="rpt"></param>
/// <param name="MonthRptID"></param>
/// <returns></returns>
private List <MonthlyReportDetail> ReadCurrentMonthlyReportDetailExcel(string filePathName, StringBuilder error, ReportInstance rpt, Guid MonthRptID)
{
List <MonthlyReportDetail> list = new List <MonthlyReportDetail>();
ExcelEngine excel = new ExcelEngine();
Workbook book = new Workbook(filePathName);
Worksheet sheet = book.Worksheets[0];
if (excel.GetStringCustomProperty(sheet, "SheetName") != "MissTargetRptExcel")
{
error.Append("请上传正确的报表文件");
return(null);
}
int count = book.Worksheets.Count;// sheet个数
if (count == 0)
{
error.Append("模板错误");
return(null);
}
WorksheetCollection sheets = book.Worksheets;
//当次上报的 所有未完成公司的指标
List <MonthlyReportDetail> missTargetList = rpt.ReportDetails;
//循环Sheet
for (int i = 0; i < count; i++)
{
//当次上报的 所有未完成公司的指标
int startRow = 4;
int endRow = 0;
int startColumn = 1;
int endColumn = 15;
Cells cells = sheets[i].Cells;
endRow = cells.MaxDataRow;
endColumn = cells.MaxDataColumn;
DataTable dt = new DataTable();
if (endRow >= startRow) //这里判断Excel里最大行数必须大于开始读取的行数
{
//读取Excel中的数据
dt = cells.ExportDataTableAsString(startRow, startColumn, endRow - startRow + 1, endColumn);
}
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
DateTime PromissDate = DateTime.Now;
//Excel中公司的名称和指标名称==来确定给Model
if (dr[1].ToString() != "未完成合计") // 这里为了排除商管多加出来的合计项
{
MonthlyReportDetail data = missTargetList.Where(M => M.CompanyName == dr[1].ToString() && M.TargetName == dr[2].ToString()).FirstOrDefault();
if (data == null)
{
continue; //如果是不包含指标跳过
}
if (data.TargetID.ToString().ToUpper() == AppSettingConfig.GetSetting("MonthSGRent", "").ToUpper() && data.NDifference < 0 && data.IsMissTarget == false)
{
data.CurrentMIssTargetReason = dr[11].ToString() + "\n"; //原因
data.CurrentMIssTargetDescription = dr[12].ToString(); //采取措施
}
else
{
data.CurrentMIssTargetReason = dr[11].ToString() + "\n"; //原因
data.CurrentMIssTargetDescription = dr[12].ToString(); //采取措施
}
list.Add(data);
}
}
}
}
return(list);
}
public void Execute(IJobExecutionContext context)
{
Common.ScheduleService.Log.Instance.Info("Service GenerateQuerryJsonData 开始 execute");
C_ReportTime RptTime = C_ReportTimeOperator.Instance.GetReportTime();
try
{
bool JsonDataSwitch = false;
bool.TryParse(AppSettingConfig.GetSetting("JsonDataSwitch", ""), out JsonDataSwitch);
List <B_MonthlyReport> MList = new List <B_MonthlyReport>();
//开关：true=按照当前上报月 ，查询， 关闭：false=查询所有的数据执行
if (JsonDataSwitch)
{
MList = B_MonthlyreportOperator.Instance.GetMonthlyReportByApproveList(RptTime.ReportTime.Value.Year, RptTime.ReportTime.Value.Month);
}
else
{
MList = B_MonthlyreportOperator.Instance.GetMonthlyreportList().ToList();
}
//获取，需要执行的JSon数据 && p.ID == Guid.Parse("114B9692-BC9E-47F0-A6EB-C14450E25C4E")
var GList = MList.Where(p => string.IsNullOrEmpty(p.DataOptimizationJson)).ToList();
if (GList != null && GList.Count > 0)
{
GList.ForEach(G =>
{
try
{
// 基础信息， 月度报告汇总表
ReportInstance rpt = new ReportInstance(G.ID, true, "");
List <DictionaryVmodel> listSRDS = ReportInstanceSummaryEngine.ReportInstanceSummaryService.GetSummaryRptDataSource(rpt, false);
StringBuilder SumStr = new StringBuilder();
SumStr.Append(JsonHelper.Serialize(listSRDS)); // 追加所有的出来的数据
string strProp = string.Empty;
//判断下，直管公司里是没有属性的
if (rpt._System.Category != 4)
{
//公司的属性List
var VpropList = (List <VCompanyProperty>)listSRDS[4].ObjValue;
VpropList.ForEach(vp =>
{
strProp = strProp + vp.ColumnName + ":";
if (vp.listCP != null && vp.listCP.Count > 0)
{
vp.listCP.ForEach(v =>
{
strProp = strProp + v.ItemCompanyPropertyValue + ",";
});
}
strProp = strProp + ";";
});
}
//月度明细数据
ReportInstance rpt_Detail = new ReportInstance(G.ID, true, "");
List <DictionaryVmodel> DetailData = ReportInstanceDetailEngine.ReportInstanceDetailService.GetDetailRptDataSource(rpt_Detail, strProp, "Detail", false);
StringBuilder DetailStr = new StringBuilder();
DetailStr.Append(JsonHelper.Serialize(DetailData)); // 追加所有的出来的数据
//累计未完成
ReportInstance rpt_MIss = new ReportInstance(G.ID, true, "");
List <DictionaryVmodel> MIssData = ReportInstanceMissTargetEngine.ReportInstanceMissTargetService.GetMissTargetRptDataSource(rpt_MIss);
StringBuilder MIssStr = new StringBuilder();
MIssStr.Append(JsonHelper.Serialize(MIssData)); // 追加所有的出来的数据
//当月未完成
ReportInstance rpt_CurrenMIss = new ReportInstance(G.ID, true, "");
List <DictionaryVmodel> CurrentMIssData = ReportInstanceCurrentMissTargetEngine.ReportInstanceMissTargetService.GetCurrentMissTargetRptDataSource(rpt_CurrenMIss);
StringBuilder CurrentMIssStr = new StringBuilder();
CurrentMIssStr.Append(JsonHelper.Serialize(CurrentMIssData)); // 追加所有的出来的数据
//补回情况
C_System sys;
if (rpt.ReportDetails != null && rpt.ReportDetails.Count() > 0)
{
sys = StaticResource.Instance[rpt._System.ID, rpt.ReportDetails[0].CreateTime];
}
else
{
sys = StaticResource.Instance[rpt._System.ID, DateTime.Now];
}
ReportInstance rpt_Return = new ReportInstance(G.ID, true, "");
List <DictionaryVmodel> ReturnData = ReportInstanceReturnEngine.ReportInstanceReturnService.GetReturnRptDataSource(rpt_Return, sys); //rpt.GetReturnRptDataSource();
StringBuilder ReturnStr = new StringBuilder();
ReturnStr.Append(JsonHelper.Serialize(ReturnData)); // 追加所有的出来的数据
// 序列化的Json数据保存
B_MonthlyReportJsonData JsonData = new B_MonthlyReportJsonData();
try
{// 获取Json数据
JsonData = B_MonthlyReportJsonDataOperator.Instance.GetMonthlyReportJsonData(G.ID);
}
catch (Exception)
{
JsonData = null;
}
if (JsonData != null)
{
JsonData.ModifierName = "SysService";
JsonData.ModifyTime = DateTime.Now;
JsonData.QuerrySumJsonData = SumStr.ToString();
JsonData.QuerryDetaileJsonData = DetailStr.ToString();
JsonData.QuerryMissJsonData = MIssStr.ToString();
JsonData.QuerryReturnJsonData = ReturnStr.ToString();
JsonData.QuerryCurrentMissJsonData = CurrentMIssStr.ToString();
B_MonthlyReportJsonDataOperator.Instance.UpdateMonthlyReportJsonData(JsonData);
G.DataOptimizationJson = "100";
B_MonthlyreportOperator.Instance.UpdateMonthlyreport(G);
}
else
{
JsonData = new B_MonthlyReportJsonData();
JsonData.ID = G.ID;
JsonData.FinMonth = rpt.FinMonth;
JsonData.FinYear = rpt.FinYear;
JsonData.PlanType = "M";
JsonData.SystemID = rpt._SystemID;
JsonData.ModifierName = "SysService";
JsonData.ModifyTime = DateTime.Now;
JsonData.QuerrySumJsonData = SumStr.ToString();
JsonData.QuerryDetaileJsonData = DetailStr.ToString();
JsonData.QuerryMissJsonData = MIssStr.ToString();
JsonData.QuerryReturnJsonData = ReturnStr.ToString();
JsonData.QuerryCurrentMissJsonData = CurrentMIssStr.ToString();
B_MonthlyReportJsonDataOperator.Instance.AddMonthlyReportJsonData(JsonData);
G.DataOptimizationJson = "100";
B_MonthlyreportOperator.Instance.UpdateMonthlyreport(G);
}
}
catch (Exception ep)
{
G.DataOptimizationJson = "-100";
B_MonthlyreportOperator.Instance.UpdateMonthlyreport(G);
Common.ScheduleService.Log.Instance.Error("Service GenerateQueryAttachments 错误：" + ep.ToString());
}
});
}
}
catch (Exception ex)
{
Common.ScheduleService.Log.Instance.Error("Service GenerateQueryAttachments 错误：" + ex.ToString());
}
Common.ScheduleService.Log.Instance.Info("Service GenerateQuerryJsonData 结束 execute");
}
/// <summary>
/// 添加年累计
/// </summary>
/// <param name="approval"></param>
/// <returns></returns>
protected Groups InitYearRpt(B_BusinessBase approval)
{
var VModel = JsonConvert.DeserializeObject <List <DictionaryVmodel> >(approval.FormData);
List <MonthReportSummaryViewModel> lstResult = JsonConvert.DeserializeObject <List <MonthReportSummaryViewModel> >(VModel[1].ObjValue.ToString());
Groups groupYearResult = new Groups()
{
type = ConstantWS.flowData_group_haveHeader,
subTitle = "月度经营报告（累计）",
titles = new List <Titles>()
{
new Titles()
{
content = "项目"
}
, new Titles()
{
content = "累计计划<br>（万元）"
}
, new Titles()
{
content = "累计实际<br>（万元）"
}
, new Titles()
{
content = "完成率"
}
}
};
List <Rows> ltYearResultRows = new List <Rows>();
Rows rsYearResult;
foreach (var model in lstResult)
{
rsYearResult = new Rows();
rsYearResult.cells = new List <Cells>();
if (model.TargetID.ToString().ToUpper() == AppSettingConfig.GetSetting("MonthSGRent", "").ToUpper())
{
//这里 对商管的租金收缴率，做下特殊处理
rsYearResult.cells.Add(new Cells()
{
content = model.TargetName, td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = model.NAccumulativePlanStr, td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = model.NAccumulativeActualStr, td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = model.NAccumulativeActualRate, td_style = "text-align:center"
});
}
else
{
rsYearResult.cells.Add(new Cells()
{
content = model.TargetName, td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = String.Format("{0:N0}", model.NAccumulativePlanAmmount), td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = String.Format("{0:N0}", model.NAccumulativeActualAmmount), td_style = "text-align:center"
});
rsYearResult.cells.Add(new Cells()
{
content = model.NAccumulativeActualRate, td_style = "text-align:center"
});
}
ltYearResultRows.Add(rsYearResult);
}
groupYearResult.rows = ltYearResultRows;
return(groupYearResult);
}
/// <summary>
/// 写Cookie
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
public static void SetCookieValue(string name, string value, int days)
{
string domain = AppSettingConfig.GetSetting("Domain", "vendor.wanda.cn");
SetCookieValue(name, domain, value, days);
}