public virtual QueryResult QueryOutBoundNotReturn(OutBoundNotReturnQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IReportQueryDA> .Instance.QueryOutBoundNotReturn(request, out totalCount);
dataTable.Columns.Add("SendEmailCount", typeof(int));
dataTable.Columns.Add("IsContact", typeof(Boolean));
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
dr["SendEmailCount"] = dr["IsSendMail"] == DBNull.Value ? 0 : dr["IsSendMail"];
DateTime now = DateTime.Now;
VendorStatus Vendor_Status;
if (!Enum.TryParse(dr["Vendor_Status"].ToString(), out Vendor_Status))
{
//转换失败赋个条件判断不需要的值...
Vendor_Status = VendorStatus.UnAvailable;
}
DateTime ValidDate, ExpiredDate;
ValidDate = dr["ValidDate"] == DBNull.Value ? now.AddDays(10) : Convert.ToDateTime(dr["ValidDate"]);
ExpiredDate = dr["ExpiredDate"] == DBNull.Value ? now.AddDays(-10) : Convert.ToDateTime(dr["ExpiredDate"]);
decimal?TotalPOMoney, ContractAmt;
TotalPOMoney = dr["TotalPOMoney"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["TotalPOMoney"]);
ContractAmt = dr["ContractAmt"] == DBNull.Value ? 0 : Convert.ToDecimal(dr["ContractAmt"]);
bool IsContact = false;
if (Vendor_Status == VendorStatus.Available)
{
if (now >= ValidDate && now <= ExpiredDate)
{
IsContact = true;
}
else if (TotalPOMoney > ContractAmt)
{
IsContact = true;
}
}
dr["IsContact"] = IsContact;
}
}
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public void ExportOutBoundExcelFile(OutBoundNotReturnQueryVM queryVM, ColumnSet[] columns)
{
OutBoundNotReturnQueryFilter queryFilter = new OutBoundNotReturnQueryFilter();
queryFilter = queryVM.ConvertVM <OutBoundNotReturnQueryVM, OutBoundNotReturnQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = string.Empty
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/RMAService/Report/OutBoundNotReturn/Query";
restClient.ExportFile(relativeUrl, queryFilter, columns);
}
public void QueryOutBoundNotReturn(OutBoundNotReturnQueryVM queryVM, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
OutBoundNotReturnQueryFilter queryFilter = new OutBoundNotReturnQueryFilter();
queryFilter = queryVM.ConvertVM <OutBoundNotReturnQueryVM, OutBoundNotReturnQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = PageSize,
PageIndex = PageIndex,
SortBy = SortField
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/RMAService/Report/OutBoundNotReturn/Query";
restClient.QueryDynamicData(relativeUrl, queryFilter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(obj, args);
});
}
public virtual DataTable QueryOutBoundNotReturn(OutBoundNotReturnQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("QueryNotReturnPerformance");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, " ob.SysNo DESC "))
{
#region conditions
if (filter.C3SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"product.Category3SysNo",
DbType.Int32,
"@C3SysNo",
QueryConditionOperatorType.Equal,
filter.C3SysNo.Value
);
}
else if (filter.C2SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category.Category2Sysno",
DbType.Int32,
"@C2SysNo",
QueryConditionOperatorType.Equal,
filter.C2SysNo.Value
);
}
else if (filter.C1SysNo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"category.Category1Sysno",
DbType.Int32,
"@C1SysNo",
QueryConditionOperatorType.Equal,
filter.C1SysNo.Value
);
}
if (filter.HasResponse.HasValue)
{
sqlBuilder.ConditionConstructor.AddNullCheckCondition(
QueryConditionRelationType.AND,
"register.ResponseDesc",
filter.HasResponse.Value
? QueryConditionOperatorType.IsNotNull : QueryConditionOperatorType.IsNull
);
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ob.OutTime",
DbType.DateTime,
"@OutTimeFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.OutTimeFrom
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ob.OutTime",
DbType.DateTime,
"@OutTimeTo",
QueryConditionOperatorType.LessThan,
filter.OutTimeTo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"product.PMUserSysNo",
DbType.Int32,
"@PMUserSysNo",
QueryConditionOperatorType.Equal,
filter.PMUserSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"product.SysNo",
DbType.Int32,
"@ProductSysNo",
QueryConditionOperatorType.Equal,
filter.ProductSysNo
);
if (filter.SendDays.HasValue)
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND, "DATEADD(DAY, @SendDays, ob.OutTime) < GETDATE()"
);
cmd.AddInputParameter("@SendDays", DbType.Int32, filter.SendDays.Value);
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"so.SysNo",
DbType.Int32,
"@SOSysNo",
QueryConditionOperatorType.Equal,
filter.SOSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"ob.VendorSysNo",
DbType.Int32,
"@VendorSysNo",
QueryConditionOperatorType.Equal,
filter.VendorSysNo
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RefundStatus",
DbType.Int32,
"@RefundStatus",
QueryConditionOperatorType.Equal,
filter.RefundStatus
);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.RevertStatus",
DbType.Int32,
"@RevertStatus",
QueryConditionOperatorType.Equal,
filter.RevertStatus
);
// only query Handling register
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.Status",
DbType.Int32,
"@RegisterStatus",
QueryConditionOperatorType.Equal,
1
);
// only query SendAlready outbund
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND,
"register.OutBoundStatus",
DbType.Int32,
"@OutboundStatus",
QueryConditionOperatorType.Equal,
1
);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "register.CompanyCode",
DbType.String, "@CompanyCode", QueryConditionOperatorType.Equal, filter.CompanyCode);
#endregion
cmd.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("RefundStatus", typeof(RMARefundStatus));
enumList.Add("RevertStatus", typeof(RMARevertStatus));
enumList.Add("Vendor_Status", typeof(VendorStatus));
CodeNamePairColumnList codeList = new CodeNamePairColumnList();
codeList.Add("PayPeriodType", "RMA", "VendorPayPeriodType");
DataTable dt = cmd.ExecuteDataTable(enumList, codeList);
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(dt);
}
}
