/// <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); } }