public void Query(PostIncomeQueryVM query, int pageSize, int pageIndex, string sortField, Action <PostIncomeQueryResultVM> callback)
{
PostIncomeQueryFilter queryFilter = query.ConvertVM <PostIncomeQueryVM, PostIncomeQueryFilter>();
queryFilter.PagingInfo = new PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/InvoiceService/PostIncome/Query";
restClient.QueryDynamicData(relativeUrl, queryFilter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
var result = new PostIncomeQueryResultVM();
result.ResultList = DynamicConverter <PostIncomeVM> .ConvertToVMList(args.Result.Rows);
result.TotalCount = args.Result.TotalCount;
callback(result);
});
}
public void ExportExcelFile(PostIncomeQueryVM queryVM, ColumnSet[] columnSet)
{
PostIncomeQueryFilter queryFilter = queryVM.ConvertVM <PostIncomeQueryVM, PostIncomeQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ECCentral.Portal.Basic.ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = "a.SysNo desc"
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/InvoiceService/PostIncome/Export";
restClient.ExportFile(relativeUrl, queryFilter, columnSet);
}
public virtual QueryResult QueryPostIncome(PostIncomeQueryFilter request)
{
int totalCount = 0;
var dataTable = ObjectFactory <IPostIncomeQueryDA> .Instance.Query(request, out totalCount);
foreach (DataRow row in dataTable.Rows.AsParallel())
{
if (!row.IsNull("CreateDate"))
{
row["CreateDate"] = Convert.ToDateTime(row["CreateDate"]).ToShortDateString();
//((DateTime)row["CreateDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("ModifyDate"))
{
row["ModifyDate"] = Convert.ToDateTime(row["ModifyDate"]).ToShortDateString();
//((DateTime)row["ModifyDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("IncomeDate"))
{
row["IncomeDate"] = Convert.ToDateTime(row["IncomeDate"]).ToShortDateString();
//((DateTime)row["IncomeDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("ConfirmDate"))
{
if (row["ConfirmStatus"].ToString() == "1")
{
row["ConfirmDate"] = Convert.ToDateTime(row["ConfirmDate"]).ToShortDateString();
//((DateTime)row["ConfirmDate"]).ToString().Substring(0, 10);
}
else
{
row["ConfirmDate"] = DBNull.Value;
}
}
}
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public virtual QueryResult ExportPostIncome(PostIncomeQueryFilter request)
{
int totalCount = 0;
var dataTable = ObjectFactory <IPostIncomeQueryDA> .Instance.Query(request, out totalCount);
dataTable.Columns.Add("HandleStatusDesc");
foreach (DataRow row in dataTable.Rows.AsParallel())
{
if (!row.IsNull("IncomeAmt"))
{
row["IncomeAmt"] = ((decimal)row["IncomeAmt"]).ToString(InvoiceConst.StringFormat.DecimalFormat);
}
if (!row.IsNull("OrderTime"))
{
row["OrderTime"] = ((DateTime)row["OrderTime"]).ToString(InvoiceConst.StringFormat.LongDateFormat);
}
if (!row.IsNull("OutTime"))
{
row["OutTime"] = ((DateTime)row["OutTime"]).ToString(InvoiceConst.StringFormat.LongDateFormat);
}
if (!row.IsNull("CreateDate"))
{
row["CreateDate"] = ((DateTime)row["CreateDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("ModifyDate"))
{
row["ModifyDate"] = ((DateTime)row["ModifyDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("IncomeDate"))
{
row["IncomeDate"] = ((DateTime)row["IncomeDate"]).ToString().Substring(0, 10);
}
if (!row.IsNull("ConfirmDate"))
{
if (row["ConfirmStatus"].ToString() == "1")
{
row["ConfirmDate"] = ((DateTime)row["ConfirmDate"]).ToString().Substring(0, 10);
}
else
{
row["ConfirmDate"] = DBNull.Value;
}
}
if (!row.IsNull("ConfirmedSOSysNoList"))
{
row["ConfirmedSOSysNoList"] = row["ConfirmedSOSysNoList"].ToString().TrimEnd('.');
}
if (row["HandleStatus"].ToString() == "1")
{
row["HandleStatusDesc"] = PostIncomeHandleStatus.Handled.ToDisplayText();
}
else
{
if (row["ConfirmStatus"].ToString() == "1")
{
row["HandleStatusDesc"] = PostIncomeHandleStatusUI.UnHandled.ToDisplayText();
}
else
{
row["HandleStatusDesc"] = PostIncomeHandleStatusUI.UnConfirmed.ToDisplayText();
}
}
}
return(new QueryResult()
{
Data = dataTable
,
TotalCount = totalCount
});
}
public DataTable Query(PostIncomeQueryFilter query, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (query.PagingInfo != null)
{
MapSortField(query.PagingInfo);
pagingInfo.MaximumRows = query.PagingInfo.PageSize;
pagingInfo.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
pagingInfo.SortField = query.PagingInfo.SortBy;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryPostIncomeList");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "a.SysNo desc"))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.SOSysNo",
DbType.Int32, "@SOSysNo", QueryConditionOperatorType.Equal, query.SOSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeAmt",
DbType.Decimal, "@IncomeAmt", QueryConditionOperatorType.Equal, query.IncomeAmt);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
DbType.Date, "@CreateDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.CreateDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CreateDate",
DbType.Date, "@CreateDateTo", QueryConditionOperatorType.LessThan, query.CreateDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
DbType.Date, "@IncomeDateFrom", QueryConditionOperatorType.MoreThanOrEqual, query.IncomeDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeDate",
DbType.Date, "@IncomeDateTo", QueryConditionOperatorType.LessThan, query.IncomeDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayBank",
DbType.String, "@PayBank", QueryConditionOperatorType.Like, query.PayBank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.IncomeBank",
DbType.String, "@IncomeBank", QueryConditionOperatorType.Like, query.IncomeBank);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "b.DisplayName",
DbType.String, "@CreateUser", QueryConditionOperatorType.Like, query.CreateUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "e.DisplayName",
DbType.String, "@AuditUser", QueryConditionOperatorType.Like, query.AuditUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.PayUser",
DbType.String, "@PayUser", QueryConditionOperatorType.Like, query.PayUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.CompanyCode",
DbType.AnsiStringFixedLength, "@CompanyCode", QueryConditionOperatorType.Equal, query.CompanyCode);
if (!string.IsNullOrEmpty(query.ConfirmedSOSysNoList))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, "a.SysNo",
QueryConditionOperatorType.In, string.Format(@"SELECT a.PostIncomeSysNo FROM
[OverseaInvoiceReceiptManagement].[dbo].[PostIncomeConfirm] a WITH(NOLOCK)
WHERE a.ConfirmedSoSysNo IN ({0}) AND a.Status <> 'C'" , query.ConfirmedSOSysNoList.Replace('.', ',')));
}
if (query.HandleStatus != null)
{
switch (query.HandleStatus.Value)
{
case PostIncomeHandleStatusUI.UnConfirmed:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.NotEqual, PostIncomeStatus.Confirmed);
break;
case PostIncomeHandleStatusUI.UnHandled:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.WaitingHandle);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.ConfirmStatus",
DbType.Int32, "@ConfirmStatus", QueryConditionOperatorType.Equal, PostIncomeStatus.Confirmed);
break;
case PostIncomeHandleStatusUI.Handled:
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "a.HandleStatus",
DbType.Int32, "@HandleStatus", QueryConditionOperatorType.Equal, PostIncomeHandleStatus.Handled);
break;
default:
break;
}
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("HandleStatus", typeof(PostIncomeHandleStatus));
enumColumns.Add("ConfirmStatus", typeof(PostIncomeStatus));
result = dataCommand.ExecuteDataTable(enumColumns);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
