/// <summary>
/// 根据SysNo查询Memo
/// </summary>
/// <param name="sysNo"></param>
/// <returns></returns>
public DataTable GetMemoBySysNo(int?sysNo)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("Invoice_QueryBySysNo");
cmd.AddInputParameter("@SysNo", DbType.Int32, sysNo);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
enumColList.Add("AuditStatus", typeof(PayableAuditStatus));
cmd.ConvertColumn(dt, enumColList, null);
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);
}
}
public DataTable AccruedByRule(AccruedQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_AccruedByRule");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "accrued.AccruePeriod ASC,accrued.AssignedCode ASC"))
{
AddAccruedByRuleParameters(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");
cmd.ConvertColumn(dt, enumColList, codeNameColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
public DataTable ReceiveByVendorQuery(ReceivedReportQueryFilter filter, out int totalCount)
{
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig("ExternalSYS_Query_ReceiveByVendor");
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), "vendor.SysNo ASC"))
{
AddReceiveByVendorParameters(filter, cmd, sb);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
codeNameColList.Add("EIMSType", "ExternalSYS", "EIMSType");
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 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);
}
}
public DataTable FinanceQuery(FinanceQueryFilter filter, out int totalCount, out double totalPayAmt)
{
string sqlName = string.Empty;
string orderStr = "SysNo DESC";
if (filter.IsGroupByVendor == true)
{
sqlName = "Invoice_Query_FinanceGroupByVendor";
}
else
{
sqlName = "Invoice_Query_Finance";
if (!string.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
orderStr = filter.PagingInfo.SortBy;
}
}
CustomDataCommand cmd = DataCommandManager.CreateCustomDataCommandFromConfig(sqlName);
using (DynamicQuerySqlBuilder sb = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, HelpDA.ToPagingInfo(filter.PagingInfo), orderStr))
{
cmd.CommandText = sb.BuildQuerySql();
if (filter.IsGroupByVendor == true)
{
AddFinanceParametersGroupByVendor(cmd, filter);
}
else
{
AddFinanceParameters(filter, cmd);
}
//合计已到应付总额
cmd.AddOutParameter("@TotalPayableAmt", DbType.Double, 12);
DataTable dt = cmd.ExecuteDataTable();
EnumColumnList enumColList = new EnumColumnList();
CodeNamePairColumnList codeNameColList = new CodeNamePairColumnList();
if (filter.IsGroupByVendor == false ||
filter.IsGroupByVendor == null)
{
enumColList.Add("AuditStatus", typeof(PayableAuditStatus));
//codeNameColList.Add("OrderType", "Invoice", "OrderType");
enumColList.Add("OrderType", typeof(PayableOrderType));
//codeNameColList.Add("OrderStatus", "Invoice", "OrderStatus");
codeNameColList.Add("InvoiceStatus", "Invoice", "InvoiceStatus");
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
else
{
codeNameColList.Add("IsConsign", "Invoice", "VendorType");
}
cmd.ConvertColumn(dt, enumColList, codeNameColList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
var totalPayableAmtParam = cmd.GetParameterValue("@TotalPayableAmt");
if (totalPayableAmtParam != DBNull.Value)
{
totalPayAmt = Convert.ToDouble(totalPayableAmtParam);
}
else
{
totalPayAmt = 0.0;
}
return(dt);
}
}