/// <summary>
/// 自印刷发票系统查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable InvoiceSelfPrintQuery(InvoiceSelfPrintQueryFilter filter, out int totalCount)
{
DataTable result = null;
MapSelfPrintSortFileds(filter);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintInvoiceList");
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;
}
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingInfo, "sm.SysNo DESC"))
{
AddSelfPrintParameter(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
result = dataCommand.ExecuteDataTable();
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
public QueryResult QueryInvoiceSelf(InvoiceSelfPrintQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IInvoiceReportQueryDA> .Instance.InvoiceSelfPrintQuery(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
private static void MapSelfPrintSortFileds(InvoiceSelfPrintQueryFilter filter)
{
if (filter.PagingInfo != null && !String.IsNullOrEmpty(filter.PagingInfo.SortBy))
{
var index = 0;
index = filter.PagingInfo.SortBy.Contains("asc") ? 4 : 5;
var sort = filter.PagingInfo.SortBy.Substring(0, filter.PagingInfo.SortBy.Length - index);
var sortField = filter.PagingInfo.SortBy;
switch (sort)
{
case "OrderID":
filter.PagingInfo.SortBy = sortField.Replace("OrderID", "sm.SysNo");
break;
case "OutTime":
filter.PagingInfo.SortBy = sortField.Replace("OutTime", "sm.OutTime");
break;
case "InvoiceNumber":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceNumber", "im.InvoiceNumber");
break;
case "InvoiceAmount":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceAmount", "im.Amount");
break;
case "InvoiceType":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceType", "im.InvoiceType");
break;
case "InvoiceCode":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceCode", "im.InvoiceCode");
break;
case "InvoiceDate":
filter.PagingInfo.SortBy = sortField.Replace("InvoiceDate", "im.PrintDate");
break;
case "IsNegativeInvoice":
filter.PagingInfo.SortBy = sortField.Replace("IsNegativeInvoice", "im.IsNegativeInvoice");
break;
case "IsRePrint":
filter.PagingInfo.SortBy = sortField.Replace("IsRePrint", "im.IsRePrint");
break;
default: break;
}
}
}
/// <summary>
/// 自印发票导出
/// </summary>
/// <param name="queryVM"></param>
/// <param name="columnSet"></param>
public void ExportInvoiceSelfExcelFile(InvoiceSelfPrintQueryVM queryVM, ColumnSet[] columnSet)
{
InvoiceSelfPrintQueryFilter queryFilter = queryVM.ConvertVM <InvoiceSelfPrintQueryVM, InvoiceSelfPrintQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ECCentral.Portal.Basic.ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = ""
};
string relativeUrl = "/InvoiceService/InvoiceReport/InvoiceSelfQuery";
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 QueryInvoiceSelf(InvoiceSelfPrintQueryVM queryVM, int pageSize, int pageIndex, string sortField, Action <dynamic> callback)
{
InvoiceSelfPrintQueryFilter filter = queryVM.ConvertVM <InvoiceSelfPrintQueryVM, InvoiceSelfPrintQueryFilter>();
filter.PagingInfo = new PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InvoiceService/InvoiceReport/InvoiceSelfQuery";
filter.CompanyCode = CPApplication.Current.CompanyCode;
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(args.Result);
});
}
private void AddSelfPrintParameter(InvoiceSelfPrintQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.Status"
, DbType.String
, "@Status"
, QueryConditionOperatorType.Equal
, "4");
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.IsVAT"
, DbType.Int32
, "@IsVAT"
, QueryConditionOperatorType.Equal
, 0);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.CompanyCode"
, DbType.String
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
var warehouseNumber = filter.StockSysNo;
if (warehouseNumber.HasValue & filter.StockSysNo != 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "subso.WareHouseNumber"
, DbType.String
, "@WarehouseNumber"
, QueryConditionOperatorType.Equal
, filter.StockSysNo);
}
else
{
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("InvoiceReport.GetSelfPrintStocks");
string stocks = dataCommand.ExecuteScalar().ToString();
if (!String.IsNullOrEmpty(stocks))
{
List <string> stockSysNo = new List <string>();
foreach (string item in stocks.Split(','))
{
stockSysNo.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "subso.WareHouseNumber", DbType.String, stockSysNo);
}
}
var outdateFrom = filter.OutDateFrom;
if (outdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.OutDateFrom);
}
var outdateTo = filter.OutDateTo;
if (outdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sm.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, filter.OutDateTo);
}
var invoicedateFrom = filter.InvoiceDateFrom;
if (invoicedateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.PrintDate"
, DbType.DateTime
, "@InvoiceDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, filter.InvoiceDateFrom);
}
var invoicedateTo = filter.InvoiceDateTo;
if (invoicedateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.PrintDate"
, DbType.DateTime
, "@InvoiceDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, invoicedateTo.Value.AddDays(1).AddMinutes(-1));
}
var orderIDString = filter.OrderID;
if (!string.IsNullOrEmpty(orderIDString))
{
List <string> orderIDList = new List <string>();
foreach (string item in orderIDString.Split('.'))
{
orderIDList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND, "sm.SysNo", DbType.String, orderIDList);
}
var invoiceNumber = filter.InvoiceNumber;
if (!string.IsNullOrEmpty(invoiceNumber))
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "im.InvoiceNumber"
, DbType.String
, "@InvoiceNumber"
, QueryConditionOperatorType.Like
, filter.InvoiceNumber);
}
}
