/// <summary>
/// 礼品卡发票明细表查询
/// </summary>
public DataTable GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, out int totalCount)
{
DataTable result = null;
PagingInfoEntity pagingInfo = new PagingInfoEntity();
if (filter.PagingInfo != null)
{
pagingInfo.MaximumRows = filter.PagingInfo.PageSize;
pagingInfo.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
pagingInfo.SortField = filter.PagingInfo.SortBy;
}
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("QueryGiftInvoiceDetailReport");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "a.SOID DESC"))
{
AddParameter4GiftInvoiceDetailReportQuery(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
var suffix = GetStockSuffix(filter.StockSysNo.Value);
dataCommand.CommandText = dataCommand.CommandText
.Replace("Invoice_detail", "Invoice_detail_" + suffix)
.Replace("Invoice_Result", "Invoice_Result_" + suffix)
.Replace("Invoice_TrackingNumber", "Invoice_TrackingNumber_" + suffix) + " ORDER BY OrderID ";
result = dataCommand.ExecuteDataTable("InvoiceType", "Invoice", "InvoiceDetailReportInvoiceType");
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
public QueryResult QueryGiftInvoiceDetailReport(GiftInvoiceDetaiReportQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IInvoiceReportQueryDA> .Instance.GiftInvoiceDetailReportQuery(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
/// <summary>
/// 礼品卡发票明细表导出
/// </summary>
/// <param name="queryVM"></param>
/// <param name="columnSet"></param>
public void ExportGiftInvoiceDetailReportExcelFile(GiftInvoiceDetaiReportQueryVM queryVM, ColumnSet[] columnSet)
{
GiftInvoiceDetaiReportQueryFilter queryFilter = queryVM.ConvertVM <GiftInvoiceDetaiReportQueryVM, GiftInvoiceDetaiReportQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ECCentral.Portal.Basic.ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = ""
};
string relativeUrl = "/InvoiceService/InvoiceReport/GiftInvoiceDetailReport";
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
restClient.ExportFile(relativeUrl, queryFilter, columnSet);
}
/// <summary>
/// 礼品卡发票明细表查询
/// </summary>
/// <param name="queryVM"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="sortField"></param>
/// <param name="callback"></param>
public void QueryGiftInvoiceDetailReport(GiftInvoiceDetaiReportQueryVM queryVM, int pageSize, int pageIndex, string sortField, Action <dynamic> callback)
{
GiftInvoiceDetaiReportQueryFilter filter = queryVM.ConvertVM <GiftInvoiceDetaiReportQueryVM, GiftInvoiceDetaiReportQueryFilter>();
filter.PagingInfo = new PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InvoiceService/InvoiceReport/GiftInvoiceDetailReport";
filter.CompanyCode = CPApplication.Current.CompanyCode;
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(args.Result);
});
}
private void AddParameter4GiftInvoiceDetailReportQuery(GiftInvoiceDetaiReportQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.OutDateFrom);
if (filter.OutDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.OutDateTo.Value.AddDays(1).AddMinutes(-1));
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceCreateDate"
, DbType.DateTime
, "@InvoiceCreateTimeFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.InvoiceDateFrom);
if (filter.InvoiceDateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceCreateDate"
, DbType.DateTime
, "@InvoiceCreateTimeTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.InvoiceDateTo.Value.AddDays(1).AddMinutes(-1));
}
if (!string.IsNullOrEmpty(filter.OrderID))
{
List <string> orderIDList = new List <string>();
foreach (string item in filter.OrderID.Split('.'))
{
orderIDList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "a.SOID", DbType.String, orderIDList);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sl.InvoiceNo"
, DbType.String
, "@InvoiceNumber"
, QueryConditionOperatorType.Like
, filter.InvoiceNumber);
int intTry;
decimal decimalTry;
if (!string.IsNullOrEmpty(filter.CustomerName))
{
if (int.TryParse(filter.CustomerName, out intTry) ||
decimal.TryParse(filter.CustomerName, out decimalTry))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "ct.CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.Equal
, filter.CustomerName);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "ct.CustomerName"
, DbType.String
, "@CustomerName"
, QueryConditionOperatorType.LeftLike
, filter.CustomerName);
}
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.SOType"
, DbType.Int32
, "@SOType"
, QueryConditionOperatorType.Equal
, filter.SOType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "a.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
}
