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); } }