/// <summary> /// 综合报表查询 /// </summary> /// <param name="filter">查询条件结合</param> /// <param name="totalCount">总记录数</param> /// <returns>DataTable数据集合</returns> public DataTable ComprehensiveQuery(EIMSComprehensiveQueryFilter filter, out int totalCount) { CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_Comprehensive"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "ruleRebate.EndDate ASC")) { AddComprehensive(filter, cmd, sb); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("EIMSType", "ExternalSYS", "EIMSType"); codeNameColList.Add("RuleStatus", "ExternalSYS", "RuleStatus"); codeNameColList.Add("InvoiceStatus", "ExternalSYS", "InvoiceStatus"); codeNameColList.Add("RebateBaseType", "ExternalSYS", "RebateBaseType"); cmd.ConvertColumn(dt, enumColList, codeNameColList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询EIMS单据 /// </summary> /// <param name="filter">查询条件集合</param> /// <param name="totalCount"></param> /// <returns></returns> public DataTable EIMSInvoiceQuery(EIMSInvoiceQueryFilter filter, out int totalCount) { CustomDataCommand cmd; if (filter.IsC1Summary == null || filter.IsC1Summary == false) { cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoice"); } else { cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_EIMSInvoiceBYC1Name"); } using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "vendor.SysNo DESC")) { AddEIMSInvoiceParameters(filter, sb, cmd); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("EIMSType", "ExternalSYS", "EIMSType"); codeNameColList.Add("ReceiveType", "ExternalSYS", "ReceivedType"); codeNameColList.Add("Status", "ExternalSYS", "InvoiceStatus"); cmd.ConvertColumn(dt, enumColList, codeNameColList); totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 以旧换新补贴款查询 /// </summary> /// <param name="filter">查询条件集合</param> /// <param name="totalCount">返回总记录数</param> /// <returns></returns> public DataTable OldChangeNewQuery(OldChangeNewQueryFilter filter, out int totalCount) { MapSortField(filter); CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_Query_OldChangeNew"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "result.SysNo desc")) { AddOldChangeNewParameters(filter, cmd, sb); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); enumColList.Add("Status", typeof(OldChangeNewStatus)); cmd.ConvertEnumColumn(dt, enumColList); dt.Columns.Add("StatusCode", typeof(string)); foreach (DataRow row in dt.Rows) { row["StatusCode"] = row["Status"].ToString(); } #region 添加计算列 dt.Columns.Add("TotalRebate", typeof(decimal)); dt.Columns.Add("TotalReviseRebate", typeof(decimal)); dt.Columns.Add("TotalPassReviseRebate", typeof(decimal)); dt.Columns.Add("TotalReturnRebate", typeof(decimal)); dt.Columns.Add("TotalReturnReviseRebate", typeof(decimal)); if (dt != null && dt.Rows.Count > 0) { decimal TotalRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalRebate")); decimal TotalReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalReviseRebate")); decimal TotalPassReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalPassReviseRebate") == DBNull.Value ? 0m : cmd.GetParameterValue("TotalPassReviseRebate")); decimal TotalReturnReviseRebate = Convert.ToDecimal(cmd.GetParameterValue("TotalReturnReviseRebate") == DBNull.Value ? 0m : cmd.GetParameterValue("TotalReturnReviseRebate")); dt.Rows[0]["TotalRebate"] = TotalRebate; dt.Rows[0]["TotalReviseRebate"] = TotalReviseRebate; dt.Rows[0]["TotalPassReviseRebate"] = TotalPassReviseRebate; dt.Rows[0]["TotalReturnReviseRebate"] = TotalReturnReviseRebate; } #endregion totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询合同与对应单据 /// </summary> /// <param name="filter">查询条件集合</param> /// <param name="totalCount">总记录数</param> /// <returns>DataTable数据集合</returns> public DataTable UnbilledRuleListQuery(UnbilledRuleListQueryFilter filter, out int totalCount) { CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_UnbilledRuleList"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "vendor.SysNo DESC")) { AddUnbilledRuleList(filter, sb, cmd); DataTable dt = cmd.ExecuteDataTable(); EnumColumnList enumColList = new EnumColumnList(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("SettleType", "ExternalSYS", "SettleType"); codeNameColList.Add("BillingCycle", "ExternalSYS", "BillingCycle"); codeNameColList.Add("SettleWeeklyDay", "ExternalSYS", "DayOfWeek"); cmd.ConvertColumn(dt, enumColList, codeNameColList); //添加 合同结算周期列 dt.Columns.Add("RuleBalanceCycle", typeof(string)); foreach (DataRow row in dt.Rows) { if (row["SettleType"].ToString().Equals("手动")) { row["RuleBalanceCycle"] = row["BillingCycle"].ToString(); } else if (row["SettleType"].ToString().Equals("自动")) { switch (row["BillingCycle"].ToString()) { case "每周": row["RuleBalanceCycle"] = row["SettleType"].ToString() + "," + row["BillingCycle"].ToString() + row["SettleWeeklyDay"].ToString(); break; case "每月": row["RuleBalanceCycle"] = row["SettleType"].ToString() + "," + row["BillingCycle"].ToString() + row["SettleMonthlyDay"].ToString() + "日"; break; default: row["RuleBalanceCycle"] = row["SettleType"].ToString() + "," + row["BillingCycle"].ToString(); break; } } } totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount")); return(dt); } }
/// <summary> /// 查询上传SAP数据 /// </summary> /// <param name="filter"></param> /// <param name="TotalCount"></param> /// <returns></returns> public DataTable QuerySAPDOCHeader(HeaderDataQueryFilter filter, out int TotalCount) { CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetHeaderData"); using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "[InDate] DESC")) { AddSAPDOCHeaderParameters(filter, cmd, sb); DataTable dt = cmd.ExecuteDataTable(); CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList(); codeNameColList.Add("DOC_TYPE", "Invoice", "DocumentType"); cmd.ConvertCodeNamePairColumn(dt, codeNameColList); TotalCount = Convert.ToInt32(cmd.GetParameterValue("TotalCount")); return(dt); } }