public QueryResult QueryAllInvoice(InvoicePrintAllQueryFilter request)
{
int totalCount;
var dataTable = ObjectFactory <IInvoiceReportQueryDA> .Instance.InvoicePrintAllQuery(request, out totalCount);
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
private void MapSortField4InvoicePrintAllQuery(InvoicePrintAllQueryFilter 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 "SOSysNo":
filter.PagingInfo.SortBy = sortField.Replace("SOSysNo", "so.SysNo");
break;
case "WareHouseNumber":
filter.PagingInfo.SortBy = sortField.Replace("WareHouseNumber", "sb.WareHouseNumber");
break;
case "IsVAT":
filter.PagingInfo.SortBy = sortField.Replace("IsVAT", "so.IsVAT");
break;
case "SOStatus":
filter.PagingInfo.SortBy = sortField.Replace("SOStatus", "so.Status");
break;
case "SOTotalAmt":
filter.PagingInfo.SortBy = sortField.Replace("SOTotalAmt", "sb.SOTotalAmt");
break;
case "SOAmt":
filter.PagingInfo.SortBy = sortField.Replace("SOAmt", "sb.SOAmt");
break;
case "OtherAmt":
filter.PagingInfo.SortBy = sortField.Replace("OtherAmt", "sb.ShippingCharge + sb.PayPrice + sb.PremiumAmt");
break;
case "OrderDate":
filter.PagingInfo.SortBy = sortField.Replace("OrderDate", "so.OrderDate");
break;
case "OutTime":
filter.PagingInfo.SortBy = sortField.Replace("OutTime", "so.OutTime");
break;
case "AuditTime":
filter.PagingInfo.SortBy = sortField.Replace("AuditTime", "so.AuditTime");
break;
}
}
}
/// <summary>
/// 发票打印导出
/// </summary>
/// <param name="queryVM"></param>
/// <param name="columnSet"></param>
public void ExportAllInvoiceExcelFile(InvoicePrintAllQueryVM queryVM, ColumnSet[] columnSet)
{
InvoicePrintAllQueryFilter queryFilter = queryVM.ConvertVM <InvoicePrintAllQueryVM, InvoicePrintAllQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ECCentral.Portal.Basic.ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = ""
};
string relativeUrl = "/InvoiceService/InvoiceReport/AllInvoice";
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 QueryAllInvoice(InvoicePrintAllQueryVM queryVM, int pageSize, int pageIndex, string sortField, Action <dynamic> callback)
{
InvoicePrintAllQueryFilter filter = queryVM.ConvertVM <InvoicePrintAllQueryVM, InvoicePrintAllQueryFilter>();
filter.PagingInfo = new PagingInfo()
{
PageIndex = pageIndex,
PageSize = pageSize,
SortBy = sortField
};
string relativeUrl = "/InvoiceService/InvoiceReport/AllInvoice";
filter.CompanyCode = CPApplication.Current.CompanyCode;
restClient.QueryDynamicData(relativeUrl, filter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(args.Result);
});
}
/// <summary>
/// 发票打印查询
/// </summary>
/// <param name="filter"></param>
/// <param name="totalCount"></param>
/// <returns></returns>
public DataTable InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, out int totalCount)
{
DataTable result = null;
MapSortField4InvoicePrintAllQuery(filter);
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoicePrintAll");
dataCommand.AddInputParameter("@NEG", DbType.AnsiStringFixedLength, InvoiceType.SELF);
dataCommand.AddInputParameter("@MET", DbType.AnsiStringFixedLength, InvoiceType.MET);
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, "so.SysNo DESC"))
{
AddParameter4InvoicePrintAllQuery(filter, sqlBuilder);
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumColumns = new EnumColumnList();
enumColumns.Add("SOStatus", typeof(ECCentral.BizEntity.SO.SOStatus));
CodeNamePairColumnList codeNamePairColunms = new CodeNamePairColumnList();
codeNamePairColunms.Add("InvoiceType", "Invoice", "SOIsVAT");
result = dataCommand.ExecuteDataTable(enumColumns, codeNamePairColunms);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
}
return(result);
}
private void AddParameter4InvoicePrintAllQuery(InvoicePrintAllQueryFilter filter, DynamicQuerySqlBuilder sqlBuilder)
{
//SOSysNo
var sosysNoString = filter.SOSysNo;
if (!string.IsNullOrEmpty(sosysNoString))
{
List <int> sosysNOList = new List <int>();
foreach (string item in sosysNoString.Split('.'))
{
sosysNOList.Add(int.Parse(item.Trim()));
}
sqlBuilder.ConditionConstructor.AddInCondition(QueryConditionRelationType.AND, "so.SysNo", DbType.Int32, sosysNOList);
}
//SOStatusCode(已出库)
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.Status"
, DbType.Int32
, "@SOStatusCode"
, QueryConditionOperatorType.Equal
, ECCentral.BizEntity.SO.SOStatus.OutStock);
//IsVAT是否增票
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.IsVAT"
, DbType.Int32
, "@IsVAT"
, QueryConditionOperatorType.Equal
, filter.IsVAT);
//CreateDateFrom
var createdateFrom = filter.CreateDateFrom;
if (createdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OrderDate"
, DbType.DateTime
, "@CreateDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, createdateFrom.Value);
}
//CreateDateTo
var createdateTo = filter.CreateDateTo;
if (createdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OrderDate"
, DbType.DateTime
, "@CreateDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, createdateTo.Value.AddDays(1).AddSeconds(-1));
}
//OutDateFrom
var outdateFrom = filter.OutDateFrom;
if (outdateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OutTime"
, DbType.DateTime
, "@OutDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, outdateFrom.Value);
}
//OutDateTo
var outdateTo = filter.OutDateTo;
if (outdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.OutTime"
, DbType.DateTime
, "@OutDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, outdateTo.Value.AddDays(1).AddSeconds(-1));
}
//AuditDateFrom
var auditDateFrom = filter.AuditDateFrom;
if (auditDateFrom.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.AuditTime"
, DbType.DateTime
, "@AuditDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, auditDateFrom.Value);
}
//AuditDateTo
var auditdateTo = filter.AuditDateTo;
if (auditdateTo.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "so.AuditTime"
, DbType.DateTime
, "@AuditDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, auditdateTo.Value.AddDays(1).AddSeconds(-1));
}
//StockSysNo
var stockSysNOString = filter.StockSysNo;
if (string.IsNullOrEmpty(stockSysNOString))
{
//1:全部查询:只查询“发票打印”支持的分仓,从配置文件中取值，即StockSysNO="51,59,90,99"；
string invoicePrintstocks = this.GetInvoicePrintStocks(filter.CompanyCode);
List <string> stockNOList = new List <string>();
foreach (string item in invoicePrintstocks.Split(','))
{
stockNOList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, stockNOList);
}
else
{
//非全部查询
if (stockSysNOString.IndexOf(',') > 0)
{
//2:上海仓:实际查询为“嘉定仓”+“宝山仓”,即StockSysNO="51,59"
List <string> stockNOList = new List <string>();
foreach (string item in stockSysNOString.Split(','))
{
stockNOList.Add(item.Trim());
}
sqlBuilder.ConditionConstructor.AddInCondition <string>(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, stockNOList);
}
else
{
//3: 其他仓:即StockSysNO="90" 或者 StockSysNo="99"
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sb.WareHouseNumber"
, DbType.AnsiStringFixedLength
, "@StockSysNo"
, QueryConditionOperatorType.Equal
, stockSysNOString);
}
}
//MerchantSysNo
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.MerchantSysNo"
, DbType.Int32
, "@MerchantSysNo"
, QueryConditionOperatorType.Equal
, filter.VendorSysNo);
//InvoiceType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.InvoiceType"
, DbType.AnsiStringFixedLength
, "@InvoiceType"
, QueryConditionOperatorType.Equal
, filter.InvoiceType);
//StockType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.StockType"
, DbType.AnsiStringFixedLength
, "@StockType"
, QueryConditionOperatorType.Equal
, filter.StockType);
//ShippingType
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "scs.ShippingType"
, DbType.AnsiStringFixedLength
, "@ShippingType"
, QueryConditionOperatorType.Equal
, filter.ShippingType);
//CompanyCode
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "sb.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, filter.CompanyCode);
}
