/// <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>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>
/// 以旧换新补贴款查询
/// </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);
}
}
public IEnumerable <DataStructure.Help> Search(IConnectionHandler connectionHandler, string txt)
{
var da = new HelpDA(connectionHandler);
return(da.Search(txt));
}
/// <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);
}
}
