public void ExportExcelFile(InvoiceInputQueryVM queryVM, ColumnSet[] columnSet)
{
InvoiceInputQueryFilter queryFilter = new InvoiceInputQueryFilter();
queryFilter = queryVM.ConvertVM <InvoiceInputQueryVM, InvoiceInputQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = ConstValue.MaxRowCountLimit,
PageIndex = 0,
SortBy = null
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/InvoiceService/InvoiceInput/Query";
restClient.ExportFile(relativeUrl, queryFilter, columnSet);
}
public void QueryInvoiceInput(InvoiceInputQueryVM queryVM, int PageSize, int PageIndex, string SortField, EventHandler <RestClientEventArgs <dynamic> > callback)
{
InvoiceInputQueryFilter queryFilter = new InvoiceInputQueryFilter();
queryFilter = queryVM.ConvertVM <InvoiceInputQueryVM, InvoiceInputQueryFilter>();
queryFilter.PagingInfo = new PagingInfo
{
PageSize = PageSize,
PageIndex = PageIndex,
SortBy = SortField
};
queryFilter.CompanyCode = CPApplication.Current.CompanyCode;
string relativeUrl = "/InvoiceService/InvoiceInput/Query";
restClient.QueryDynamicData(relativeUrl, queryFilter, (obj, args) =>
{
if (args.FaultsHandle())
{
return;
}
callback(obj, args);
});
}
public QueryResult QueryInvoiceInput(InvoiceInputQueryFilter request)
{
int totalCount;
DataTable dataTable = ObjectFactory <IInvoiceInputQueryDA> .Instance.QueryInvoiceInput(request, out totalCount);
dataTable.Columns.Add("POAmtSum", typeof(decimal));
dataTable.Columns.Add("EIMSAmtSum", typeof(decimal));
dataTable.Columns.Add("PO_S", typeof(string));
dataTable.Columns.Add("Invoice_S", typeof(string));
dataTable.Columns.Add("InUserAdd", typeof(string));
if (dataTable.Rows.Count > 0)
{
foreach (DataRow dr in dataTable.Rows)
{
int sysNo = (int)dr["SysNo"];
List <APInvoicePOItemInfo> POItemList = appService.GetPOItemsByDocNo(sysNo);
List <APInvoiceInvoiceItemInfo> InvoiceItemList = appService.GetInvoiceItemsByDocNo(sysNo);
dr["POAmtSum"] = POItemList.Sum(p =>
{
return(p.PoAmt ?? 0);
});
dr["EIMSAmtSum"] = POItemList.Sum(p =>
{
return(p.EIMSAmt ?? 0);
});
if (POItemList != null && POItemList.Count > 0)
{
var result = (from i in POItemList
select i.PoNo).Min();
dr["PO_S"] = string.Format("{0}({1})", result.ToString(), POItemList.Count);
}
if (InvoiceItemList != null && InvoiceItemList.Count > 0)
{
var result = (from i in InvoiceItemList
select i.InvoiceNo).Min();
dr["Invoice_S"] = string.Format("{0}({1})", result.ToString(), InvoiceItemList.Count);
}
if ((int)dr["IsVendorPortal"] == 1)
{
var splitArray = dr["InUser"].ToString().Split('/');
if (splitArray.Count() == 5)
{
dr["InUserAdd"] = string.Format("{0}-{1}", splitArray[3], splitArray[4].PadLeft(4, '0'));
}
else
{
dr["InUserAdd"] = "N/A";
}
}
else
{
dr["InUserAdd"] = dr["InUser"];
}
}
}
return(new QueryResult()
{
Data = dataTable,
TotalCount = totalCount
});
}
public DataTable QueryInvoiceInput(InvoiceInputQueryFilter query, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = query.PagingInfo.SortBy;
pagingEntity.MaximumRows = query.PagingInfo.PageSize;
pagingEntity.StartRowIndex = query.PagingInfo.PageIndex * query.PagingInfo.PageSize;
CustomDataCommand dataCommand = DataCommandManager.CreateCustomDataCommandFromConfig("GetInvoiceMasterByQuery");
using (DynamicQuerySqlBuilder sqlBuilder = new DynamicQuerySqlBuilder(
dataCommand.CommandText, dataCommand, pagingEntity, "M.DocNo DESC"))
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.CompanyCode"
, DbType.AnsiStringFixedLength
, "@CompanyCode"
, QueryConditionOperatorType.Equal
, query.CompanyCode);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.DocNo"
, DbType.Int32
, "@DocNo"
, QueryConditionOperatorType.Equal
, query.DocNo);
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorSysNo"
, DbType.Int32
, "@VendorSysNo"
, QueryConditionOperatorType.Equal
, query.VendorSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.InDate"
, DbType.DateTime
, "@CreateDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, query.CreateDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.InDate"
, DbType.DateTime
, "@CreateDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, query.CreateDateTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.ConfirmDate"
, DbType.DateTime
, "@ConfirmDateFrom"
, QueryConditionOperatorType.MoreThanOrEqual
, query.ConfirmDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.ConfirmDate"
, DbType.DateTime
, "@ConfirmDateTo"
, QueryConditionOperatorType.LessThanOrEqual
, query.ConfirmDateTo);
if (!string.IsNullOrEmpty(query.POList))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, string.Format(@"Exists(SELECT TOP 1 1
FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_PO_Item] P WITH(NOLOCK)
WHERE P.PoNo IN({0}) AND P.DocNo = M.DocNo )"
, query.POList.Replace(".", ",")));
}
if (!string.IsNullOrEmpty(query.APList))
{
sqlBuilder.ConditionConstructor.AddCustomCondition(
QueryConditionRelationType.AND
, string.Format(@"Exists(SELECT TOP 1 1
FROM [OverseaInvoiceReceiptManagement].[dbo].[APInvoice_Invo_Item] A WITH(NOLOCK)
WHERE A.InvoiceNo IN({0}) AND A.DocNo = M.DocNo)"
, query.APList.Replace(".", ",")));
}
if (query.HasDiff.HasValue)
{
if (query.HasDiff.Value)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.DiffTaxAmt"
, DbType.Decimal
, "@DiffTaxAmt"
, QueryConditionOperatorType.NotEqual
, 0m);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND
, "M.DiffTaxAmt"
, DbType.Decimal
, "@DiffTaxAmt"
, QueryConditionOperatorType.Equal
, 0m);
}
}
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.Status"
, DbType.Int32
, "@Status"
, QueryConditionOperatorType.Equal
, query.Status);
if (query.ComeFrom.HasValue)
{
if (query.ComeFrom.Value == 0)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorPortalSysNo"
, DbType.Int32
, "@VendorPortalSysNo"
, QueryConditionOperatorType.IsNull
, query.ComeFrom);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "M.VendorPortalSysNo"
, DbType.Int32
, "@VendorPortalSysNo"
, QueryConditionOperatorType.IsNotNull
, query.ComeFrom);
}
}
if (query.PaySettleCompany.HasValue)
{
sqlBuilder.ConditionConstructor.AddCondition(
QueryConditionRelationType.AND
, "V.PaySettleCompany"
, DbType.Int32
, "@PaySettleCompany"
, QueryConditionOperatorType.Equal
, query.PaySettleCompany.Value);
}
dataCommand.CommandText = sqlBuilder.BuildQuerySql();
EnumColumnList enumList = new EnumColumnList();
enumList.Add("Status", typeof(APInvoiceMasterStatus));
enumList.Add("SapImportedStatus", typeof(SapImportedStatus));
enumList.Add("DiffTaxTreatmentType", typeof(InvoiceDiffType));
DataTable dt = dataCommand.ExecuteDataTable(enumList);
totalCount = Convert.ToInt32(dataCommand.GetParameterValue("@TotalCount"));
return(dt);
}
}
