protected virtual IEnumerable customers()
{
BillingFilter filter = Filter.Current;
if (filter == null)
{
yield break;
}
bool found = false;
foreach (CustomersList item in Customers.Cache.Inserted)
{
found = true;
yield return(item);
}
if (found)
{
yield break;
}
PXSelectBase <EPExpenseClaimDetails> sel = new PXSelectJoinGroupBy <EPExpenseClaimDetails, InnerJoin <Customer, On <EPExpenseClaimDetails.customerID, Equal <Customer.bAccountID> >,
LeftJoin <Contract, On <EPExpenseClaimDetails.contractID, Equal <Contract.contractID> > > >,
Where <EPExpenseClaimDetails.released, Equal <boolTrue>,
And <EPExpenseClaimDetails.billable, Equal <boolTrue>,
And <EPExpenseClaimDetails.billed, Equal <boolFalse>,
And <EPExpenseClaimDetails.expenseDate, LessEqual <Current <BillingFilter.endDate> > > > > >,
Aggregate <GroupBy <EPExpenseClaimDetails.customerID,
GroupBy <EPExpenseClaimDetails.customerLocationID> > > >(this);
if (filter.CustomerClassID != null)
{
sel.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
}
if (filter.CustomerID != null)
{
sel.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
}
foreach (PXResult <EPExpenseClaimDetails, Customer, Contract> res in sel.Select())
{
CustomersList retitem = new CustomersList();
Customer customer = res;
EPExpenseClaimDetails claimdetaisl = res;
Contract contract = (Contract)res;
if (contract.BaseType == PMProject.ProjectBaseType.Project)
{
continue;
}
retitem.CustomerID = customer.BAccountID;
retitem.LocationID = claimdetaisl.CustomerLocationID;
retitem.CustomerClassID = customer.CustomerClassID;
retitem.Selected = false;
yield return(Customers.Insert(retitem));
}
}
public virtual IEnumerable transactions()
{
PXSelectBase <PMTran> select = new PXSelectJoinGroupBy <PMTran,
LeftJoin <Account, On <Account.accountID, Equal <PMTran.offsetAccountID> >,
LeftJoin <PMRegister, On <PMTran.tranType, Equal <PMRegister.module>, And <PMTran.refNbr, Equal <PMRegister.refNbr> > >,
LeftJoin <ARTran, On <ARTran.pMTranID, Equal <PMTran.tranID> >,
LeftJoin <PMAllocationAuditTran, On <PMAllocationAuditTran.sourceTranID, Equal <PMTran.tranID> >,
LeftJoin <PMTranEx, On <PMTranEx.tranID, Equal <PMAllocationAuditTran.tranID> > > > > > >,
Where <PMTran.projectID, Equal <Current <TranFilter.projectID> > >,
Aggregate <GroupBy <PMTran.tranID, GroupBy <PMTran.billable, GroupBy <PMTran.useBillableQty, GroupBy <PMTran.allocated,
GroupBy <PMTran.released, GroupBy <PMTran.billed, GroupBy <PMTran.reversed, GroupBy <PMTran.isNonGL, GroupBy <PMTran.isQtyOnly> > > > > > > > > > >(this);
TranFilter filter = this.Filter.Current;
if (filter != null)
{
if (filter.AccountGroupID != null)
{
select.WhereAnd <Where <PMTran.accountGroupID, Equal <Current <TranFilter.accountGroupID> >, Or <Account.accountGroupID, Equal <Current <TranFilter.accountGroupID> > > > >();
}
if (filter.ProjectTaskID != null)
{
select.WhereAnd <Where <PMTran.taskID, Equal <Current <TranFilter.projectTaskID> > > >();
}
if (filter.InventoryID != null)
{
select.WhereAnd <Where <PMTran.inventoryID, Equal <Current <TranFilter.inventoryID> > > >();
}
if (filter.ResourceID != null)
{
select.WhereAnd <Where <PMTran.resourceID, Equal <Current <TranFilter.resourceID> > > >();
}
if (filter.OnlyAllocation == true)
{
select.WhereAnd <Where <PMRegister.isAllocation, Equal <True> > >();
}
if (filter.DateFrom != null && filter.DateTo != null)
{
if (filter.DateFrom == filter.DateTo)
{
select.WhereAnd <Where <PMTran.date, Equal <Current <TranFilter.dateFrom> > > >();
}
else
{
select.WhereAnd <Where <PMTran.date, Between <Current <TranFilter.dateFrom>, Current <TranFilter.dateTo> > > >();
}
}
else if (filter.DateFrom != null)
{
select.WhereAnd <Where <PMTran.date, GreaterEqual <Current <TranFilter.dateFrom> > > >();
}
else if (filter.DateTo != null)
{
select.WhereAnd <Where <PMTran.date, LessEqual <Current <TranFilter.dateTo> > > >();
}
}
return(select.Select());
}
public virtual void PMEnsureInitialized()
{
if (_historyLoaded == null)
{
_reportPeriods = new RMReportPeriods <PMHistory>(this.Base);
var accountGroupSelect = new PXSelect <PMAccountGroup>(this.Base);
accountGroupSelect.View.Clear();
accountGroupSelect.Cache.Clear();
var projectSelect = new PXSelect <PMProject, Where <PMProject.isTemplate, Equal <False>, And <PMProject.nonProject, Equal <False>, And <PMProject.baseType, Equal <PMProject.ProjectBaseType> > > > >(this.Base);
projectSelect.View.Clear();
projectSelect.Cache.Clear();
var taskSelect = new PXSelectJoin <PMTask, InnerJoin <PMProject, On <PMTask.projectID, Equal <PMProject.contractID> > >, Where <PMProject.isTemplate, Equal <False>, And <PMProject.nonProject, Equal <False> > > >(this.Base);
taskSelect.View.Clear();
taskSelect.Cache.Clear();
var itemSelect = new PXSelectJoinGroupBy <InventoryItem, InnerJoin <PMHistory, On <InventoryItem.inventoryID, Equal <PMHistory.inventoryID> > >, Aggregate <GroupBy <InventoryItem.inventoryID> > >(this.Base);
itemSelect.View.Clear();
itemSelect.Cache.Clear();
if (Base.Report.Current.ApplyRestrictionGroups == true)
{
projectSelect.WhereAnd <Where <Match <Current <AccessInfo.userName> > > >();
taskSelect.WhereAnd <Where <Match <Current <AccessInfo.userName> > > >();
itemSelect.WhereAnd <Where <Match <Current <AccessInfo.userName> > > >();
}
accountGroupSelect.Select();
projectSelect.Select();
taskSelect.Select();
foreach (InventoryItem item in itemSelect.Select())
{
//The PXSelectJoinGroupBy is read-only, and Inventory items won't be added to the cache. Add them manually.
itemSelect.Cache.SetStatus(item, PXEntryStatus.Notchanged);
}
_historySegments = new HashSet <PMHistoryKeyTuple>();
_pmhistoryPeriodsByKey = new Dictionary <PMHistoryKeyTuple, Dictionary <string, PMHistory> >();
_historyLoaded = new HashSet <int>();
_accountGroupsRangeCache = new RMReportRange <PMAccountGroup>(Base, PM.AccountGroupAttribute.DimensionName, RMReportConstants.WildcardMode.Fixed, RMReportConstants.BetweenMode.Fixed);
_projectsRangeCache = new RMReportRange <PMProject>(Base, PM.ProjectAttribute.DimensionName, RMReportConstants.WildcardMode.Fixed, RMReportConstants.BetweenMode.Fixed);
_tasksRangeCache = new RMReportRange <PMTask>(Base, PM.ProjectTaskAttribute.DimensionName, RMReportConstants.WildcardMode.Normal, RMReportConstants.BetweenMode.Fixed);
_itemRangeCache = new RMReportRange <InventoryItem>(Base, IN.InventoryAttribute.DimensionName, RMReportConstants.WildcardMode.Fixed, RMReportConstants.BetweenMode.Fixed);
//Add Inventory <OTHER> with InventoryID=0
InventoryItem other = new InventoryItem
{
InventoryCD = RMReportWildcard.EnsureWildcardForFixed(Messages.OtherItem, _itemRangeCache.Wildcard),
InventoryID = PMInventorySelectorAttribute.EmptyInventoryID,
Descr = Messages.OtherItemDescription
};
itemSelect.Cache.SetStatus(other, PXEntryStatus.Notchanged);
}
}
protected virtual PXSelectBase <SOOrder> BuildCommandCreateShipment(SOOrderFilter filter)
{
PXSelectBase <SOOrder> cmd = new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType> >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
InnerJoin <SOShipmentPlan,
On <SOOrder.orderType, Equal <SOShipmentPlan.orderType>,
And <SOOrder.orderNbr, Equal <SOShipmentPlan.orderNbr> > >,
InnerJoin <INSite, On <INSite.siteID, Equal <SOShipmentPlan.siteID> >,
LeftJoin <SOOrderShipment,
On <SOOrderShipment.orderType, Equal <SOShipmentPlan.orderType>,
And <SOOrderShipment.orderNbr, Equal <SOShipmentPlan.orderNbr>,
And <SOOrderShipment.siteID, Equal <SOShipmentPlan.siteID>,
And <SOOrderShipment.confirmed, Equal <boolFalse> > > > >,
LeftJoinSingleTable <Customer, On <SOOrder.customerID, Equal <Customer.bAccountID> > > > > > > >,
Where <SOShipmentPlan.inclQtySOBackOrdered, Equal <short0>, And <SOOrderShipment.shipmentNbr, IsNull,
And2 <Where <Customer.bAccountID, IsNull, Or <Match <Customer, Current <AccessInfo.userName> > > >,
And <Match <INSite, Current <AccessInfo.userName> > > > > >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr,
GroupBy <SOOrder.approved> > > > >(this);
if (filter.SiteID != null)
{
cmd.WhereAnd <Where <SOShipmentPlan.siteID, Equal <Current <SOOrderFilter.siteID> > > >();
}
if (filter.DateSel == "S")
{
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <SOShipmentPlan.planDate, LessEqual <Current <SOOrderFilter.endDate> > > >();
}
if (filter.StartDate != null)
{
cmd.WhereAnd <Where <SOShipmentPlan.planDate, GreaterEqual <Current <SOOrderFilter.startDate> > > >();
}
filter.DateSel = string.Empty;
}
return(cmd);
}
public static IEnumerable DriverRecordsDelegate(PXGraph graph, SharedClasses.RouteSelected_view routeSelected, PXFilter <DriverSelectionFilter> filter)
{
if (routeSelected.Current == null)
{
yield break;
}
List <object> args = new List <object>();
PXSelectBase <EPEmployee> commandFilter = new PXSelectJoinGroupBy <EPEmployee,
InnerJoin <FSRouteEmployee,
On <
FSRouteEmployee.employeeID, Equal <EPEmployee.bAccountID> >,
LeftJoin <FSRouteDocument,
On <
FSRouteDocument.driverID, Equal <FSRouteEmployee.employeeID>,
And <
FSRouteDocument.date, Equal <Required <FSRouteDocument.date> > > > > >,
Where <
FSRouteEmployee.routeID, Equal <Required <FSRouteEmployee.routeID> >,
And <
FSxEPEmployee.sDEnabled, Equal <True>,
And <
FSxEPEmployee.isDriver, Equal <True> > > >,
Aggregate <
GroupBy <
EPEmployee.bAccountID> >,
OrderBy <
Asc <FSRouteEmployee.priorityPreference> > >(graph);
args.Add(routeSelected.Current.Date);
args.Add(routeSelected.Current.RouteID);
if (filter.Current.ShowUnassignedDrivers == true)
{
commandFilter.WhereAnd <Where <FSRouteDocument.routeID, IsNull> >();
}
var bqlResultSet = commandFilter.Select(args.ToArray());
foreach (PXResult <EPEmployee, FSRouteEmployee, FSRouteDocument> bqlResult in bqlResultSet)
{
EPEmployee epEmployeeRow = (EPEmployee)bqlResult;
FSRouteEmployee fsRouteEmployeeRow = (FSRouteEmployee)bqlResult;
FSRouteDocument fsRouteDocumentRow = (FSRouteDocument)bqlResult;
FSxEPEmployee fsxEPEmployeeRow = PXCache <EPEmployee> .GetExtension <FSxEPEmployee>(epEmployeeRow);
if (fsRouteDocumentRow != null && fsRouteDocumentRow.RouteID != null)
{
fsxEPEmployeeRow.Mem_UnassignedDriver = true;
}
yield return(bqlResult);
}
}
protected virtual PXSelectBase <SOOrder> BuildCommandPrepareInvoice()
{
var cmd =
new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType>, And <SOOrderType.aRDocType, NotEqual <ARDocType.noUpdate> > >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
LeftJoin <SOOrderShipment, On <SOOrderShipment.orderType, Equal <SOOrder.orderType>, And <SOOrderShipment.orderNbr, Equal <SOOrder.orderNbr> > >,
LeftJoinSingleTable <ARInvoice, On <ARInvoice.docType, Equal <SOOrderShipment.invoiceType>, And <ARInvoice.refNbr, Equal <SOOrderShipment.invoiceNbr> > >,
LeftJoinSingleTable <Customer, On <SOOrder.customerID, Equal <Customer.bAccountID> > > > > > >,
Where <SOOrder.hold, Equal <boolFalse>, And <SOOrder.cancelled, Equal <boolFalse>,
And <Where <Customer.bAccountID, IsNull, Or <Match <Customer, Current <AccessInfo.userName> > > > > > >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr,
GroupBy <SOOrder.approved> > > > >(this);
if (PXAccess.FeatureInstalled <FeaturesSet.inventory>())
{
cmd.WhereAnd <
Where <Sub <Sub <Sub <SOOrder.shipmentCntr,
SOOrder.openShipmentCntr>,
SOOrder.billedCntr>,
SOOrder.releasedCntr>, Greater <short0>,
Or2 <Where <SOOrder.orderQty, Equal <decimal0>,
And <SOOrder.curyUnbilledMiscTot, Greater <decimal0> > >,
Or <Where <SOOrderType.requireShipping, Equal <boolFalse>, And <ARInvoice.refNbr, IsNull> > > > > >();
}
else
{
cmd.WhereAnd <
Where <SOOrder.curyUnbilledMiscTot, Greater <decimal0>, And <SOOrderShipment.shipmentNbr, IsNull,
Or <Where <SOOrderType.requireShipping, Equal <boolFalse>, And <ARInvoice.refNbr, IsNull> > > > > >();
}
return(cmd);
}
public static IEnumerable VehicleRecordsDelegate(PXGraph graph, SharedClasses.RouteSelected_view routeSelected, PXFilter <VehicleSelectionFilter> filter)
{
if (routeSelected.Current == null)
{
yield break;
}
List <object> args = new List <object>();
PXSelectBase <FSVehicle> commandFilter = new PXSelectJoinGroupBy <FSVehicle,
LeftJoin <FSRouteDocument,
On <
FSRouteDocument.vehicleID, Equal <FSVehicle.SMequipmentID>,
And <FSRouteDocument.date, Equal <Required <FSRouteDocument.date> > > > >,
Where <
FSVehicle.isVehicle, Equal <True> >,
Aggregate <
GroupBy <
FSVehicle.SMequipmentID> >,
OrderBy <
Asc <FSServiceVehicleType.priorityPreference> > >(graph);
args.Add(routeSelected.Current.Date);
if (filter.Current.ShowUnassignedVehicles == true)
{
commandFilter.WhereAnd <Where <FSRouteDocument.routeID, IsNull> >();
}
var list = commandFilter.Select(args.ToArray());
foreach (PXResult <FSVehicle, FSRouteDocument> bqlResult in list)
{
FSVehicle fsEquipmentRow = (FSVehicle)bqlResult;
FSRouteDocument fsRouteDocumentRow = (FSRouteDocument)bqlResult;
if (fsRouteDocumentRow != null && fsRouteDocumentRow.RouteID != null)
{
fsEquipmentRow.Mem_UnassignedVehicle = true;
}
yield return(bqlResult);
}
}
public virtual IEnumerable envelopes()
{
PXSelectBase <ESignEnvelopeInfo> query = new PXSelectJoinGroupBy <ESignEnvelopeInfo,
InnerJoin <UploadFileRevision,
On <UploadFileRevision.fileID, Equal <ESignEnvelopeInfo.fileID>,
And <UploadFileRevision.fileRevisionID, Equal <ESignEnvelopeInfo.fileRevisionID> > >,
InnerJoin <ESignAccount, On <ESignEnvelopeInfo.eSignAccountID, Equal <ESignAccount.accountID> >,
InnerJoin <UploadFileWithIDSelector, On <UploadFileWithIDSelector.fileID, Equal <UploadFileRevision.fileID> >,
InnerJoin <SiteMap, On <SiteMap.screenID, Equal <UploadFileWithIDSelector.primaryScreenID> > > > > >,
Where <ESignEnvelopeInfo.lastStatus, NotEqual <Empty>,
And <ESignEnvelopeInfo.isFinalVersion, NotEqual <True> > >,
Aggregate <GroupBy <ESignEnvelopeInfo.envelopeInfoID> > >(this);
EnvelopeFilter filter = Filter.Current;
if (filter.OwnerID != null)
{
query.WhereAnd <Where <ESignEnvelopeInfo.createdByID, Equal <Current <EnvelopeFilter.ownerID> > > >();
}
return(query.Select());
}
protected virtual IEnumerable history()
{
APHistoryFilter header = Filter.Current;
if (header == null)
{
yield break;
}
if (header.Period == null)
{
PXSelectBase <Vendor> sel = new PXSelectJoinGroupBy <Vendor,
LeftJoin <APHistory,
On <APHistory.vendorID, Equal <Vendor.bAccountID> > >,
Aggregate <
Sum <APHistory.finPtdCrAdjustments,
Sum <APHistory.finPtdDiscTaken,
Sum <APHistory.finPtdDrAdjustments,
Sum <APHistory.finPtdPayments,
Sum <APHistory.finPtdPurchases,
Sum <APHistory.finPtdRGOL,
Sum <APHistory.tranPtdCrAdjustments,
Sum <APHistory.tranPtdDiscTaken,
Sum <APHistory.tranPtdDrAdjustments,
Sum <APHistory.tranPtdPayments,
Sum <APHistory.tranPtdPurchases,
Sum <APHistory.tranPtdRGOL,
GroupBy <Vendor.bAccountID> > > > > > > > > > > > > > >(this);
if (header.APAcctID != null)
{
sel.WhereAnd <Where <Vendor.aPAcctID, Equal <Current <APHistoryFilter.aPAcctID> > > >();
}
if (header.APSubID != null)
{
sel.WhereAnd <Where <Vendor.aPSubID, Equal <Current <APHistoryFilter.aPSubID> > > >();
}
bool foreign = false;
if (header.CuryID != null)
{
sel.WhereAnd <Where <Vendor.curyID, Equal <Current <APHistoryFilter.curyID> > > >();
GL.Company company = PXSelect <GL.Company> .Select(this);
foreign = (company == null || company.BaseCuryID != header.CuryID);
if (foreign)
{
PXUIFieldAttribute.SetVisible <APHistoryResult.rGOL>(History.Cache, null, false);
}
}
if (header.CashAcctID != null)
{
sel.WhereAnd <Where <Vendor.cashAcctID, Equal <Current <APHistoryFilter.cashAcctID> > > >();
}
if (header.PaymentTypeID != null)
{
sel.WhereAnd <Where <Vendor.paymentTypeID, Equal <Current <APHistoryFilter.paymentTypeID> > > >();
}
if (header.VendorClassID != null)
{
sel.WhereAnd <Where <Vendor.vendorClassID, Equal <Current <APHistoryFilter.vendorClassID> > > >();
}
if (header.Status != null)
{
sel.WhereAnd <Where <Vendor.status, Equal <Current <APHistoryFilter.status> > > >();
}
foreach (PXResult <Vendor, APHistory> record in sel.Select())
{
Vendor vendor = record;
APHistory history = record;
APHistoryResult res = new APHistoryResult();
res.AcctCD = vendor.AcctCD;
res.AcctName = vendor.AcctName;
if (header.ByFinancialPeriod != true)
{
if (!foreign)
{
res.Purchases = history.TranPtdPurchases ?? 0m;
res.Payments = history.TranPtdPayments ?? 0m;
res.Discount = history.TranPtdDiscTaken ?? 0m;
res.RGOL = history.TranPtdRGOL ?? 0m;
res.CrAdjustments = history.TranPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.TranPtdDrAdjustments ?? 0m;
res.Balance = res.Purchases
- res.Payments
- res.Discount
+ res.RGOL
- res.DrAdjustments
+ res.CrAdjustments;
}
else
{
res.Purchases = history.TranPtdPurchases ?? 0m;
res.Payments = history.TranPtdPayments ?? 0m;
res.Discount = history.TranPtdDiscTaken ?? 0m;
res.RGOL = 0m;
res.CrAdjustments = history.TranPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.TranPtdDrAdjustments ?? 0m;
res.Balance = res.Purchases
- res.Payments
- res.Discount
- res.DrAdjustments
+ res.CrAdjustments;
}
}
else
{
if (!foreign)
{
res.Purchases = history.FinPtdPurchases ?? 0m;
res.Payments = history.FinPtdPayments ?? 0m;
res.Discount = history.FinPtdDiscTaken ?? 0m;
res.RGOL = history.FinPtdRGOL ?? 0m;
res.CrAdjustments = history.FinPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.FinPtdDrAdjustments ?? 0m;
res.Balance = res.Purchases
- res.Payments
- res.Discount
+ res.RGOL
- res.DrAdjustments
+ res.CrAdjustments;
}
else
{
res.Purchases = history.FinPtdPurchases ?? 0m;
res.Payments = history.FinPtdPayments ?? 0m;
res.Discount = history.FinPtdDiscTaken ?? 0m;
res.RGOL = 0m;
res.CrAdjustments = history.FinPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.FinPtdDrAdjustments ?? 0m;
res.Balance = res.Purchases
- res.Payments
- res.Discount
- res.DrAdjustments
+ res.CrAdjustments;
}
}
if (res.Balance != 0m || header.ShowWithBalanceOnly != true)
{
yield return(res);
}
}
}
else
{
PXSelectBase <Vendor> sel = new PXSelectJoinGroupBy <Vendor,
LeftJoin <APHistory,
On <APHistory.vendorID, Equal <Vendor.bAccountID>,
And <APHistory.finPeriodID, Equal <Current <APHistoryFilter.period> > > > >,
Aggregate <
Sum <APHistory.finBegBalance,
Sum <APHistory.finYtdBalance,
Sum <APHistory.finPtdCrAdjustments,
Sum <APHistory.finPtdDiscTaken,
Sum <APHistory.finPtdDrAdjustments,
Sum <APHistory.finPtdPayments,
Sum <APHistory.finPtdPurchases,
Sum <APHistory.finPtdRGOL,
Sum <APHistory.tranBegBalance,
Sum <APHistory.tranYtdBalance,
Sum <APHistory.tranPtdCrAdjustments,
Sum <APHistory.tranPtdDiscTaken,
Sum <APHistory.tranPtdDrAdjustments,
Sum <APHistory.tranPtdPayments,
Sum <APHistory.tranPtdPurchases,
Sum <APHistory.tranPtdRGOL,
GroupBy <Vendor.bAccountID> > > > > > > > > > > > > > > > > > >(this);
PXUIFieldAttribute.SetVisible <APHistoryResult.balance>(History.Cache, null, false);
PXUIFieldAttribute.SetVisible <APHistoryResult.begBalance>(History.Cache, null);
PXUIFieldAttribute.SetVisible <APHistoryResult.endBalance>(History.Cache, null);
if (header.APAcctID != null)
{
sel.WhereAnd <Where <Vendor.aPAcctID, Equal <Current <APHistoryFilter.aPAcctID> > > >();
}
if (header.APSubID != null)
{
sel.WhereAnd <Where <Vendor.aPSubID, Equal <Current <APHistoryFilter.aPSubID> > > >();
}
bool foreign = false;
if (header.CuryID != null)
{
sel.WhereAnd <Where <Vendor.curyID, Equal <Current <APHistoryFilter.curyID> > > >();
GL.Company company = PXSelect <GL.Company> .Select(this);
foreign = (company == null || company.BaseCuryID != header.CuryID);
if (foreign)
{
PXUIFieldAttribute.SetVisible <APHistoryResult.rGOL>(History.Cache, null, false);
}
}
if (header.CashAcctID != null)
{
sel.WhereAnd <Where <Vendor.cashAcctID, Equal <Current <APHistoryFilter.cashAcctID> > > >();
}
if (header.PaymentTypeID != null)
{
sel.WhereAnd <Where <Vendor.paymentTypeID, Equal <Current <APHistoryFilter.paymentTypeID> > > >();
}
if (header.VendorClassID != null)
{
sel.WhereAnd <Where <Vendor.vendorClassID, Equal <Current <APHistoryFilter.vendorClassID> > > >();
}
if (header.Status != null)
{
sel.WhereAnd <Where <Vendor.status, Equal <Current <APHistoryFilter.status> > > >();
}
foreach (PXResult <Vendor, APHistory> record in sel.Select())
{
Vendor vendor = record;
APHistory history = record;
APHistoryResult res = new APHistoryResult();
res.AcctCD = vendor.AcctCD;
res.AcctName = vendor.AcctName;
if (header.ByFinancialPeriod != true)
{
if (!foreign)
{
res.Purchases = history.TranPtdPurchases ?? 0m;
res.Payments = history.TranPtdPayments ?? 0m;
res.Discount = history.TranPtdDiscTaken ?? 0m;
res.RGOL = history.TranPtdRGOL ?? 0m;
res.CrAdjustments = history.TranPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.TranPtdDrAdjustments ?? 0m;
res.BegBalance = history.TranBegBalance ?? 0m;
res.EndBalance = res.BegBalance
+ res.Purchases
- res.Payments
- res.Discount
+ res.RGOL
- res.DrAdjustments
+ res.CrAdjustments;
}
else
{
res.Purchases = history.TranPtdPurchases ?? 0m;
res.Payments = history.TranPtdPayments ?? 0m;
res.Discount = history.TranPtdDiscTaken ?? 0m;
res.RGOL = 0m;
res.CrAdjustments = history.TranPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.TranPtdDrAdjustments ?? 0m;
res.BegBalance = history.TranBegBalance ?? 0m;
res.EndBalance = res.BegBalance
+ res.Purchases
- res.Payments
- res.Discount
- res.DrAdjustments
+ res.CrAdjustments;
}
}
else
{
if (!foreign)
{
res.Purchases = history.FinPtdPurchases ?? 0m;
res.Payments = history.FinPtdPayments ?? 0m;
res.Discount = history.FinPtdDiscTaken ?? 0m;
res.RGOL = history.FinPtdRGOL ?? 0m;
res.CrAdjustments = history.FinPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.FinPtdDrAdjustments ?? 0m;
res.BegBalance = history.FinBegBalance ?? 0m;
res.EndBalance = res.BegBalance
+ res.Purchases
- res.Payments
- res.Discount
+ res.RGOL
- res.DrAdjustments
+ res.CrAdjustments;
}
else
{
res.Purchases = history.FinPtdPurchases ?? 0m;
res.Payments = history.FinPtdPayments ?? 0m;
res.Discount = history.FinPtdDiscTaken ?? 0m;
res.RGOL = 0m;
res.CrAdjustments = history.FinPtdCrAdjustments ?? 0m;
res.DrAdjustments = history.FinPtdDrAdjustments ?? 0m;
res.BegBalance = history.FinBegBalance ?? 0m;
res.EndBalance = res.BegBalance
+ res.Purchases
- res.Payments
- res.Discount
- res.DrAdjustments
+ res.CrAdjustments;
}
}
if (res.Balance != 0m || header.ShowWithBalanceOnly != true)
{
yield return(res);
}
}
}
}
public virtual IEnumerable pOreceiptslist()
{
APInvoice doc = Base.Document.Current;
if (doc?.VendorID == null ||
doc.VendorLocationID == null ||
doc.DocType != APDocType.Invoice &&
doc.DocType != APDocType.DebitAdj)
{
yield break;
}
string poReceiptType = doc.DocType == APDocType.Invoice
? POReceiptType.POReceipt
: POReceiptType.POReturn;
Dictionary <APTran, int> usedReceipt = new Dictionary <APTran, int>(new POReceiptComparer());
int count;
foreach (APTran aPTran in Base.Transactions.Cache.Inserted)
{
if (aPTran.ReceiptNbr != null)
{
usedReceipt.TryGetValue(aPTran, out count);
usedReceipt[aPTran] = count + 1;
}
}
foreach (APTran aPTran in Base.Transactions.Cache.Deleted)
{
if (aPTran.ReceiptNbr != null && Base.Transactions.Cache.GetStatus(aPTran) != PXEntryStatus.InsertedDeleted)
{
usedReceipt.TryGetValue(aPTran, out count);
usedReceipt[aPTran] = count - 1;
}
}
foreach (APTran aPTran in Base.Transactions.Cache.Updated)
{
string originalValue = (string)Base.Transactions.Cache.GetValueOriginal <APTran.receiptNbr>(aPTran);
if (aPTran.ReceiptNbr != originalValue)
{
if (originalValue != null)
{
APTran originTran = new APTran {
ReceiptNbr = originalValue
};
usedReceipt.TryGetValue(originTran, out count);
usedReceipt[originTran] = count - 1;
}
if (aPTran.ReceiptNbr != null)
{
usedReceipt.TryGetValue(aPTran, out count);
usedReceipt[aPTran] = count + 1;
}
}
}
PXSelectBase <POReceipt> cmd = new PXSelectJoinGroupBy <
POReceipt,
InnerJoin <POReceiptLineS, On <POReceiptLineS.receiptNbr, Equal <POReceipt.receiptNbr> >,
LeftJoin <APTran, On <APTran.released, Equal <False>,
And <Where <POReceiptLineS.pOAccrualType, Equal <POAccrualType.receipt>,
And <APTran.receiptNbr, Equal <POReceiptLineS.receiptNbr>,
And <APTran.receiptType, Equal <POReceiptLineS.receiptType>,
And <APTran.receiptLineNbr, Equal <POReceiptLineS.lineNbr>,
Or <POReceiptLineS.pOAccrualType, Equal <POAccrualType.order>,
And <APTran.pOOrderType, Equal <POReceiptLineS.pOType>,
And <APTran.pONbr, Equal <POReceiptLineS.pONbr>,
And <APTran.pOLineNbr, Equal <POReceiptLineS.pOLineNbr> > > > > > > > > > > > >,
Where <POReceipt.hold, Equal <False>,
And <POReceipt.released, Equal <True>,
And <POReceipt.receiptType, Equal <Required <POReceipt.receiptType> >,
And <APTran.refNbr, IsNull,
And <POReceiptLineS.unbilledQty, Greater <decimal0>,
And <Where <POReceiptLineS.pONbr, Equal <Current <POReceiptFilter.orderNbr> >,
Or <Current <POReceiptFilter.orderNbr>, IsNull> > > > > > > >,
Aggregate <
GroupBy <POReceipt.receiptType,
GroupBy <POReceipt.receiptNbr,
Sum <POReceiptLineS.receiptQty,
Sum <POReceiptLineS.unbilledQty,
Count <POReceiptLineS.lineNbr> > > > > > >(Base);
if (Base.APSetup.Current.RequireSingleProjectPerDocument == true)
{
cmd.WhereAnd <Where <POReceipt.projectID, Equal <Current <APInvoice.projectID> > > >();
}
if (PXAccess.FeatureInstalled <FeaturesSet.vendorRelations>())
{
cmd.Join <LeftJoin <POOrder, On <POOrder.orderType, Equal <POReceiptLineS.pOType>, And <POOrder.orderNbr, Equal <POReceiptLineS.pONbr> > > > >();
cmd.WhereAnd <Where <POReceipt.vendorID, Equal <Current <APInvoice.suppliedByVendorID> >,
And <POReceipt.vendorLocationID, Equal <Current <APInvoice.suppliedByVendorLocationID> >,
And <Where <POOrder.payToVendorID, IsNull, Or <POOrder.payToVendorID, Equal <Current <APInvoice.vendorID> > > > > > > >();
}
else
{
cmd.WhereAnd <Where <POReceipt.vendorID, Equal <Current <APInvoice.vendorID> >,
And <POReceipt.vendorLocationID, Equal <Current <APInvoice.vendorLocationID> > > > >();
}
foreach (PXResult <POReceipt, POReceiptLineS, APTran> result in cmd.View.SelectMultiBound(new object[] { doc }, poReceiptType))
{
POReceipt receipt = result;
APTran aPTran = new APTran {
ReceiptNbr = receipt.ReceiptNbr
};
if (usedReceipt.TryGetValue(aPTran, out count))
{
usedReceipt.Remove(aPTran);
if (count < result.RowCount)
{
yield return(receipt);
}
}
else
{
yield return(receipt);
}
}
foreach (APTran deletedTran in usedReceipt.Where(_ => _.Value < 0).Select(_ => _.Key))
{
yield return(PXSelect <POReceipt, Where <POReceipt.receiptNbr, Equal <Required <APTran.receiptNbr> > > >
.SelectSingleBound(Base, new object[] { }, deletedTran.ReceiptNbr)
.RowCast <POReceipt>()
.First());
}
}
public virtual IEnumerable orders()
{
PXUIFieldAttribute.SetDisplayName <SOOrder.customerID>(Caches[typeof(SOOrder)], Messages.CustomerID);
List <SOOrder> ret = new List <SOOrder>();
SOOrderFilter filter = PXCache <SOOrderFilter> .CreateCopy(Filter.Current);
if (filter.Action == "<SELECT>")
{
return(ret);
}
string actionID = (string)Orders.GetTargetFill(null, null, null, filter.Action, "@actionID");
if (_ActionChanged)
{
Orders.Cache.Clear();
}
foreach (SOOrder order in Orders.Cache.Updated)
{
ret.Add(order);
}
PXSelectBase <SOOrder> cmd;
switch (actionID)
{
case "1":
cmd = new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType> >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
InnerJoin <SOShipmentPlan, On <SOOrder.orderType, Equal <SOShipmentPlan.orderType>, And <SOOrder.orderNbr, Equal <SOShipmentPlan.orderNbr> > >,
LeftJoin <SOOrderShipment, On <SOOrderShipment.orderType, Equal <SOShipmentPlan.orderType>, And <SOOrderShipment.orderNbr, Equal <SOShipmentPlan.orderNbr>, And <SOOrderShipment.siteID, Equal <SOShipmentPlan.siteID>, And <SOOrderShipment.confirmed, Equal <boolFalse> > > > > > > > >,
Where <SOShipmentPlan.inclQtySOBackOrdered, Equal <short0>, And <SOOrderShipment.shipmentNbr, IsNull> >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr> > > >(this);
if (filter.DateSel == "S")
{
cmd.WhereAnd <Where <SOShipmentPlan.planDate, LessEqual <Current <SOOrderFilter.endDate> > > >();
if (filter.StartDate != null)
{
cmd.WhereAnd <Where <SOShipmentPlan.planDate, GreaterEqual <Current <SOOrderFilter.startDate> > > >();
}
filter.DateSel = string.Empty;
}
break;
case "3":
cmd = new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType> >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
LeftJoin <SOOrderShipment, On <SOOrderShipment.orderType, Equal <SOOrder.orderType>, And <SOOrderShipment.orderNbr, Equal <SOOrder.orderNbr> > >,
LeftJoin <ARInvoice, On <ARInvoice.docType, Equal <SOOrderShipment.invoiceType>, And <ARInvoice.refNbr, Equal <SOOrderShipment.invoiceNbr> > > > > > >,
Where <SOOrderType.aRDocType, NotEqual <ARDocType.noUpdate>,
And2 <Where <SOOrderType.requireShipping, Equal <boolFalse>, Or <SOOrder.shipmentCntr, Equal <short0>, And <SOOrder.openLineCntr, Equal <short0> > > >,
And <ARInvoice.refNbr, IsNull> > >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr> > > >(this);
break;
case "3.5":
cmd = new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType> >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
InnerJoin <SOOrderShipment, On <SOOrderShipment.orderType, Equal <SOOrder.orderType>, And <SOOrderShipment.orderNbr, Equal <SOOrder.orderNbr> > >,
LeftJoin <ARInvoice, On <ARInvoice.docType, Equal <SOOrderShipment.invoiceType>, And <ARInvoice.refNbr, Equal <SOOrderShipment.invoiceNbr> > > > > > >,
Where <SOOrderShipment.confirmed, Equal <boolTrue>,
And <SOOrderType.aRDocType, NotEqual <ARDocType.noUpdate>,
And <ARInvoice.refNbr, IsNull> > >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr> > > >(this);
break;
default:
if (!string.IsNullOrEmpty(filter.Action) && filter.Action.StartsWith("PrepareInvoice", StringComparison.OrdinalIgnoreCase))
{
cmd = new PXSelectJoinGroupBy <SOOrder,
InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType>, And <SOOrderType.aRDocType, NotEqual <ARDocType.noUpdate> > >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> >,
LeftJoin <SOOrderShipment, On <SOOrderShipment.orderType, Equal <SOOrder.orderType>, And <SOOrderShipment.orderNbr, Equal <SOOrder.orderNbr> > >,
LeftJoin <ARInvoice, On <ARInvoice.docType, Equal <SOOrderShipment.invoiceType>, And <ARInvoice.refNbr, Equal <SOOrderShipment.invoiceNbr> > > > > > >,
Where <SOOrder.hold, Equal <boolFalse>, And <SOOrder.cancelled, Equal <boolFalse>,
And <Where <Sub <Sub <Sub <SOOrder.shipmentCntr,
SOOrder.openShipmentCntr>,
SOOrder.billedCntr>,
SOOrder.releasedCntr>, Greater <short0>,
Or2 <Where <SOOrder.orderQty, Equal <decimal0>,
And <SOOrder.curyUnbilledMiscTot, Greater <decimal0> > >,
Or <Where <SOOrderType.requireShipping, Equal <boolFalse>, And <ARInvoice.refNbr, IsNull> > > > > > > >,
Aggregate <
GroupBy <SOOrder.orderType,
GroupBy <SOOrder.orderNbr> > > >(this);
}
else
{
cmd = new PXSelectJoin <SOOrder, InnerJoin <SOOrderType, On <SOOrderType.orderType, Equal <SOOrder.orderType> >,
LeftJoin <Carrier, On <SOOrder.shipVia, Equal <Carrier.carrierID> > > > >(this);
}
break;
}
switch (filter.DateSel)
{
case "S":
cmd.WhereAnd <Where <SOOrder.shipDate, LessEqual <Current <SOOrderFilter.endDate> > > >();
break;
case "C":
cmd.WhereAnd <Where <SOOrder.cancelDate, LessEqual <Current <SOOrderFilter.endDate> > > >();
break;
case "O":
cmd.WhereAnd <Where <SOOrder.orderDate, LessEqual <Current <SOOrderFilter.endDate> > > >();
break;
}
if (filter.StartDate != null)
{
switch (filter.DateSel)
{
case "S":
cmd.WhereAnd <Where <SOOrder.shipDate, GreaterEqual <Current <SOOrderFilter.startDate> > > >();
break;
case "C":
cmd.WhereAnd <Where <SOOrder.cancelDate, GreaterEqual <Current <SOOrderFilter.startDate> > > >();
break;
case "O":
cmd.WhereAnd <Where <SOOrder.orderDate, GreaterEqual <Current <SOOrderFilter.startDate> > > >();
break;
}
}
if (filter.SiteID != null)
{
switch (actionID)
{
case "1":
cmd.WhereAnd <Where <SOShipmentPlan.siteID, Equal <Current <SOOrderFilter.siteID> > > >();
break;
}
}
if (!string.IsNullOrEmpty(filter.CarrierPluginID))
{
cmd.WhereAnd <Where <Carrier.carrierPluginID, Equal <Current <SOOrderFilter.carrierPluginID> > > >();
}
if (!string.IsNullOrEmpty(filter.ShipVia))
{
cmd.WhereAnd <Where <SOOrder.shipVia, Equal <Current <SOOrderFilter.shipVia> > > >();
}
if (filter.CustomerID != null)
{
cmd.WhereAnd <Where <SOOrder.customerID, Equal <Current <SOOrderFilter.customerID> > > >();
}
int startRow = PXView.StartRow;
int totalRows = 0;
List <PXFilterRow> newFilters = new List <PXFilterRow>();
foreach (PXFilterRow f in PXView.Filters)
{
if (f.DataField.ToLower() == "behavior")
{
f.DataField = "SOOrderType__Behavior";
}
newFilters.Add(f);
}
foreach (PXResult <SOOrder, SOOrderType> res in cmd.View.Select(null, null, PXView.Searches, PXView.SortColumns, PXView.Descendings, newFilters.ToArray(), ref startRow, PXView.MaximumRows, ref totalRows))
{
SOOrder order = res;
SOOrder cached;
order.Behavior = ((SOOrderType)res).Behavior;
order.ARDocType = ((SOOrderType)res).ARDocType;
order.DefaultOperation = ((SOOrderType)res).DefaultOperation;
if ((cached = (SOOrder)Orders.Cache.Locate(order)) == null || Orders.Cache.GetStatus(cached) == PXEntryStatus.Notchanged)
{
ret.Add(order);
}
}
PXView.StartRow = 0;
Orders.Cache.IsDirty = false;
return(ret);
}
protected virtual IEnumerable enqResult()
{
GLHistoryEnqFilter filter = (GLHistoryEnqFilter)this.Filter.Current;
bool showCurrency = filter.ShowCuryDetail.HasValue && filter.ShowCuryDetail.Value;
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyID>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdCreditTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdDebitTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyEndBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryEndBalance>(EnqResult.Cache, null, showCurrency);
if (filter.LedgerID == null || filter.FinPeriodID == null)
{
yield break; //Prevent code from accessing database;
}
#region cmd
PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod,
InnerJoin <Account,
On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >,
InnerJoin <Sub,
On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >,
LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>,
And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>,
And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>,
And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>,
And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >,
LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>,
And <GLHistoryByPeriod.branchID, Equal <AH.branchID>,
And <GLHistoryByPeriod.accountID, Equal <AH.accountID>,
And <GLHistoryByPeriod.subID, Equal <AH.subID>,
And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > > >,
Where <GLHistoryByPeriod.ledgerID, Equal <Current <GLHistoryEnqFilter.ledgerID> >,
And <GLHistoryByPeriod.finPeriodID, Equal <Current <GLHistoryEnqFilter.finPeriodID> >,
And <
Where2 <
Where <Account.accountID, NotEqual <Current <GLSetup.ytdNetIncAccountID> >, And <Where <Account.type, Equal <AccountType.asset>,
Or <Account.type, Equal <AccountType.liability> > > > >,
Or <Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >,
And <Where <Account.type, Equal <AccountType.expense>,
Or <Account.type, Equal <AccountType.income>,
Or <Account.accountID, Equal <Current <GLSetup.ytdNetIncAccountID> > > > > > > > > > > >,
Aggregate <
Sum <AH.finYtdBalance,
Sum <AH.tranYtdBalance,
Sum <AH.curyFinYtdBalance,
Sum <AH.curyTranYtdBalance,
Sum <GLHistory.finPtdDebit,
Sum <GLHistory.tranPtdDebit,
Sum <GLHistory.finPtdCredit,
Sum <GLHistory.tranPtdCredit,
Sum <GLHistory.finBegBalance,
Sum <GLHistory.tranBegBalance,
Sum <GLHistory.finYtdBalance,
Sum <GLHistory.tranYtdBalance,
Sum <GLHistory.curyFinBegBalance,
Sum <GLHistory.curyTranBegBalance,
Sum <GLHistory.curyFinYtdBalance,
Sum <GLHistory.curyTranYtdBalance,
Sum <GLHistory.curyFinPtdCredit,
Sum <GLHistory.curyTranPtdCredit,
Sum <GLHistory.curyFinPtdDebit,
Sum <GLHistory.curyTranPtdDebit,
GroupBy <GLHistoryByPeriod.branchID,
GroupBy <GLHistoryByPeriod.ledgerID,
GroupBy <GLHistoryByPeriod.accountID,
GroupBy <GLHistoryByPeriod.finPeriodID
> > > > > > > > > > > > > > > > > > > > > > > > > >(this);
if (filter.LedgerID != null)
{
Ledger ledger = (Ledger)PXSelectorAttribute.Select <GLHistoryEnqFilter.ledgerID>(Filter.Cache, filter);
if (ledger?.BalanceType == LedgerBalanceType.Budget)
{
// we shouldn't select history from the previous years for the budget ledgers
cmd.WhereAnd <Where <Substring <GLHistoryByPeriod.finPeriodID, int1, int4>, Equal <Substring <GLHistoryByPeriod.lastActivityPeriod, int1, int4> > > >();
}
}
if (filter.AccountID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.accountID, Equal <Current <GLHistoryEnqFilter.accountID> > > >();
}
if (filter.AccountClassID != null)
{
cmd.WhereAnd <Where <Account.accountClassID, Equal <Current <GLHistoryEnqFilter.accountClassID> > > >();
}
if (filter.SubID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.subID, Equal <Current <GLHistoryEnqFilter.subID> > > >();
}
int[] branchIDs = null;
if (filter.BranchID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <GLHistoryEnqFilter.branchID> > > >();
}
else if (filter.OrganizationID != null)
{
branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID, false);
cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, In <Required <GLHistoryEnqFilter.branchID> >,
And <MatchWithBranch <GLHistoryByPeriod.branchID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd.WhereAnd <Where <Sub.subCD, Like <Current <GLHistoryEnqFilter.subCDWildcard> > > >();
}
//cmd.WhereAnd<Where<Match<Current<AccessInfo.userName>>>>();
#endregion
string yearBegPeriod = filter.BegFinPeriod;
GLSetup glSetup = glsetup.Current;
bool reverseSign = (glSetup != null) && (glSetup.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed);
foreach (PXResult <GLHistoryByPeriod, Account, Sub, GLHistory, AH> it in cmd.Select(yearBegPeriod, branchIDs))
{
GLHistoryByPeriod baseview = (GLHistoryByPeriod)it;
Account acct = (Account)it;
GLHistory ah = (GLHistory)it;
AH ah1 = (AH)it;
ah.FinFlag = filter.UseMasterCalendar != true;
ah1.FinFlag = filter.UseMasterCalendar != true;
if (reverseSign && acct.AccountID == glSetup.YtdNetIncAccountID)
{
continue;
}
GLHistoryEnquiryResult item = new GLHistoryEnquiryResult();
item.BranchID = baseview.BranchID;
item.LedgerID = baseview.LedgerID;
item.AccountID = baseview.AccountID;
item.AccountCD = acct.AccountCD;
item.Type = acct.Type;
item.Description = acct.Description;
item.LastActivityPeriod = baseview.LastActivityPeriod;
item.PtdCreditTotal = ah.PtdCredit ?? 0m;
item.PtdDebitTotal = ah.PtdDebit ?? 0m;
item.EndBalance = ah1.YtdBalance ?? 0m;
item.ConsolAccountCD = acct.GLConsolAccountCD;
item.AccountClassID = acct.AccountClassID;
if (!(string.IsNullOrEmpty(ah.CuryID) && string.IsNullOrEmpty(ah1.CuryID)))
{
item.CuryEndBalance = ah1.CuryYtdBalance ?? 0m;
item.CuryPtdCreditTotal = ah.CuryPtdCredit ?? 0m;
item.CuryPtdDebitTotal = ah.CuryPtdDebit ?? 0m;
item.CuryID = string.IsNullOrEmpty(ah.CuryID) ? ah1.CuryID : ah.CuryID;
}
else
{
item.CuryEndBalance = null;
item.CuryPtdCreditTotal = null;
item.CuryPtdDebitTotal = null;
item.CuryID = null;
}
item.recalculate(true); // End balance is considered as correct digit - so we need to calculate begBalance base on ending one
item.recalculateSignAmount(reverseSign);
yield return(item);
}
}
public virtual IEnumerable pOOrderLinesList()
{
APInvoice doc = Base.Document.Current;
var filter = orderfilter.Current;
if (doc?.VendorID == null ||
doc.VendorLocationID == null ||
doc.DocType != APDocType.Invoice &&
doc.DocType != APDocType.DebitAdj)
{
return(Enumerable.Empty <POLineRS>());
}
PXSelectBase <POLineRS> cmd = new PXSelectJoinGroupBy <
POLineRS,
LeftJoin <APTran,
On <APTran.pOOrderType, Equal <POLineRS.orderType>,
And <APTran.pONbr, Equal <POLineRS.orderNbr>,
And <APTran.pOLineNbr, Equal <POLineRS.lineNbr>,
And <APTran.receiptNbr, IsNull,
And <APTran.receiptLineNbr, IsNull,
And <APTran.released, Equal <False> > > > > > > >,
Where <POLineRS.orderType, NotEqual <POOrderType.blanket>,
And <POLineRS.pOAccrualType, Equal <POAccrualType.order>,
And <POLineRS.cancelled, NotEqual <True>,
And <POLineRS.closed, NotEqual <True>,
And <POLineRS.orderType, NotEqual <POOrderType.standardBlanket>,
And <POLineRS.curyID, Equal <Current <APInvoice.curyID> >,
And <POLineRS.status, Equal <POOrderStatus.open>,
And2 <Where <APTran.refNbr, IsNull, Or <APTran.refNbr, Equal <Current <APInvoice.refNbr> > > >,
And <Where <Current <POOrderFilter.orderNbr>, IsNull, Or <POLineRS.orderNbr, Equal <Current <POOrderFilter.orderNbr> > > > > > > > > > > > >,
Aggregate
<GroupBy <POLineRS.orderType,
GroupBy <POLineRS.orderNbr,
GroupBy <POLineRS.lineNbr> > > > >(Base);
if (Base.APSetup.Current.RequireSingleProjectPerDocument == true)
{
cmd.WhereAnd <Where <POOrder.projectID, Equal <Current <APInvoice.projectID> > > >();
}
if (PXAccess.FeatureInstalled <FeaturesSet.vendorRelations>())
{
cmd.WhereAnd <Where <POLineRS.vendorID, Equal <Current <APInvoice.suppliedByVendorID> >,
And <POLineRS.vendorLocationID, Equal <Current <APInvoice.suppliedByVendorLocationID> >,
And <POLineRS.payToVendorID, Equal <Current <APInvoice.vendorID> > > > > >();
}
else
{
cmd.WhereAnd <Where <POLineRS.vendorID, Equal <Current <APInvoice.vendorID> >,
And <POLineRS.vendorLocationID, Equal <Current <APInvoice.vendorLocationID> > > > >();
}
if (filter.ShowBilledLines != true)
{
cmd.WhereAnd <Where <POLineRS.unbilledQty, Greater <decimal0>,
Or <POLineRS.curyUnbilledAmt, Greater <decimal0> > > >();
}
var currentOrders = Base.Transactions.Select().RowCast <APTran>().Select(t => new { t.POOrderType, POOrderNbr = t.PONbr, t.POLineNbr }).ToList();
var orderLinesResult = cmd.View.SelectMultiBound(new object[] { doc, filter });
var orderLines = orderLinesResult.RowCast <POLineRS>()
.Where(t => !currentOrders.Contains(new
{
POOrderType = t.OrderType,
POOrderNbr = t.OrderNbr,
POLineNbr = t.LineNbr
}));
return(orderLines.ToList());
}
protected virtual IEnumerable enqResult()
{
AccountByYearFilter filter = this.CurrentFilter;
bool showCurrency = filter.ShowCuryDetail.HasValue && filter.ShowCuryDetail.Value;
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyID>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdCreditTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdDebitTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyEndBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryEndBalance>(EnqResult.Cache, null, showCurrency);
if (filter.AccountID == null || filter.LedgerID == null || filter.FinYear == null)
{
yield break; //Prevent code from accessing database;
}
PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod,
InnerJoin <Account,
On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >,
InnerJoin <FinPeriod,
On <GLHistoryByPeriod.finPeriodID, Equal <FinPeriod.finPeriodID> >,
InnerJoin <Sub,
On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >,
LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>,
And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>,
And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>,
And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>,
And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >,
LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>,
And <GLHistoryByPeriod.branchID, Equal <AH.branchID>,
And <GLHistoryByPeriod.accountID, Equal <AH.accountID>,
And <GLHistoryByPeriod.subID, Equal <AH.subID>,
And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > > > >,
Where <GLHistoryByPeriod.ledgerID, Equal <Current <AccountByYearFilter.ledgerID> >,
And <FinPeriod.finYear, Equal <Current <AccountByYearFilter.finYear> >,
And <GLHistoryByPeriod.accountID, Equal <Current <AccountByYearFilter.accountID> >,
And <
Where2 <
Where <Account.accountID, NotEqual <Current <GLSetup.ytdNetIncAccountID> >, And <Where <Account.type, Equal <AccountType.asset>,
Or <Account.type, Equal <AccountType.liability> > > > >,
Or <
Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >,
And <Where <Account.type, Equal <AccountType.expense>,
Or <Account.type, Equal <AccountType.income>,
Or <Account.accountID, Equal <Current <GLSetup.ytdNetIncAccountID> > > > > > > > > > > > >,
Aggregate <
Sum <AH.finYtdBalance,
Sum <AH.curyFinYtdBalance,
Sum <GLHistory.finPtdDebit,
Sum <GLHistory.finPtdCredit,
Sum <GLHistory.finBegBalance,
Sum <GLHistory.finYtdBalance,
Sum <GLHistory.curyFinBegBalance,
Sum <GLHistory.curyFinYtdBalance,
Sum <GLHistory.curyFinPtdCredit,
Sum <GLHistory.curyFinPtdDebit,
GroupBy <GLHistoryByPeriod.ledgerID,
GroupBy <GLHistoryByPeriod.accountID,
GroupBy <GLHistoryByPeriod.finPeriodID
> > > > > > > > > > > > > > >(this);
if (filter.SubID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.subID, Equal <Current <AccountByYearFilter.subID> > > >();
}
if (filter.BranchID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <AccountByYearFilter.branchID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd.WhereAnd <Where <Sub.subCD, Like <Current <AccountByYearFilter.subCDWildcard> > > >();
}
string yearBegPeriod = filter.BegFinPeriod;
GLSetup glSetup = glsetup.Current;
bool reverseSign = (glSetup != null) && (glSetup.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed);
foreach (PXResult <GLHistoryByPeriod, Account, FinPeriod, Sub, GLHistory, AH> it in cmd.Select(yearBegPeriod))
{
GLHistoryByPeriod baseview = (GLHistoryByPeriod)it;
Account acct = (Account)it;
GLHistory ah = (GLHistory)it;
AH ah1 = (AH)it;
if (reverseSign && acct.AccountID == glSetup.YtdNetIncAccountID)
{
continue;
}
GLHistoryEnquiryResult item = new GLHistoryEnquiryResult();
item.AccountID = baseview.AccountID;
item.LedgerID = baseview.LedgerID;
item.LastActivityPeriod = baseview.FinPeriodID;
item.PtdCreditTotal = ah.FinPtdCredit;
item.PtdDebitTotal = ah.FinPtdDebit;
item.CuryID = ah1.CuryID;
item.Type = acct.Type;
item.EndBalance = ah1.FinYtdBalance;
if (!string.IsNullOrEmpty(ah1.CuryID))
{
item.CuryEndBalance = ah1.CuryFinYtdBalance; //
item.CuryPtdCreditTotal = ah.CuryFinPtdCredit;
item.CuryPtdDebitTotal = ah.CuryFinPtdDebit;
}
else
{
item.CuryEndBalance = null;
item.CuryPtdCreditTotal = null;
item.CuryPtdDebitTotal = null;
}
item.recalculate(true); // End balance is considered as correct digit - so we need to calculate begBalance base on ending one
item.recalculateSignAmount(reverseSign);
yield return(item);
}
}
protected virtual IEnumerable productRecords()
{
ProductFilter filter = Filter.Current as ProductFilter;
switch (filter.Aggregated)
{
case 1: // Sum Ext Price
// Aggregation specified in BQL
PXSelectBase <Account> query =
new PXSelectJoinGroupBy <Account,
LeftJoin <SalesOrder, On <SalesOrder.customerAccountID, Equal <Account.accountID> >,
LeftJoin <OrderDetail, On <SalesOrder.orderNbr, Equal <OrderDetail.orderNbr> >,
LeftJoin <Product, On <Product.productID, Equal <OrderDetail.productID> > > > >,
Where <Account.companyType, Equal <CompanyTypes.customer> >,
Aggregate <GroupBy <Account.accountID,
GroupBy <Product.categoryCD,
Sum <OrderDetail.extPrice> > > > >(this);
// Filtering parameters added dynamically
if (filter.CustomerID != null)
{
query.WhereAnd <Where <SalesOrder.customerAccountID, Equal <Current <ProductFilter.customerID> > > >();
}
if (filter.CategoryCD != null)
{
query.WhereAnd <Where <Product.categoryCD, Equal <Current <ProductFilter.categoryCD> > > >();
}
// Constructing each PXResult<> from the retrieved aggregated data
PXResultset <Account, OrderDetail, Product> res = new PXResultset <Account, OrderDetail, Product>();
// We need Product, so we have to list all classes that go before Account in PXSelect
// Including SalesOrder, even if we don't use it within foreach
foreach (PXResult <Account, SalesOrder, OrderDetail, Product> p in query.Select())
{
Product p1 = (Product)p;
OrderDetail d1 = (OrderDetail)p;
Account a1 = (Account)p;
// Create new objects to have empty values in fields that aren't aggregated
Product resultProd = new Product();
OrderDetail resultDetail = new OrderDetail();
resultProd.CategoryCD = p1.CategoryCD;
resultDetail.ExtPrice = d1.ExtPrice;
// a1 passed directly since we need three fields of Account (key fields)
// and we don't need empty values in Account
res.Add(new PXResult <Account, OrderDetail, Product>(a1, resultDetail, resultProd));
}
return(res);
case 2: // Calculate and sum up amounts of order details
query =
new PXSelectJoin <Account,
LeftJoin <SalesOrder, On <SalesOrder.customerAccountID, Equal <Account.accountID> >,
LeftJoin <OrderDetail, On <SalesOrder.orderNbr, Equal <OrderDetail.orderNbr> >,
LeftJoin <Product, On <Product.productID, Equal <OrderDetail.productID> > > > >,
Where <Account.companyType, Equal <CompanyTypes.customer> >,
// Sorting is applied to retrieve records here;
// in the UI the records are sorted as specified in the data view
OrderBy <Asc <SalesOrder.customerAccountID, Asc <Product.categoryCD> > > >(this);
if (filter.CustomerID != null)
{
query.WhereAnd <Where <SalesOrder.customerAccountID, Equal <Current <ProductFilter.customerID> > > >();
}
if (filter.CategoryCD != null)
{
query.WhereAnd <Where <Product.categoryCD, Equal <Current <ProductFilter.categoryCD> > > >();
}
// Constructing a new PXResultset with calculated aggregates
res = new PXResultset <Account, OrderDetail, Product>();
Product pendingProd = null;
Account pendingAccount = null;
decimal?amtSum = 0;
foreach (PXResult <Account, SalesOrder, OrderDetail, Product> p in query.Select())
{
Product p1 = (Product)p;
OrderDetail od1 = (OrderDetail)p;
SalesOrder o1 = (SalesOrder)p;
Account a1 = (Account)p;
if (pendingProd != null && pendingAccount != null && (p1.CategoryCD != pendingProd.CategoryCD ||
o1.CustomerAccountID != pendingAccount.AccountID))
{
// Create new objects to have empty values in fields that aren't aggregated
Product resultProd = new Product();
resultProd.CategoryCD = pendingProd.CategoryCD;
OrderDetail resultDetail = new OrderDetail();
resultDetail.ExtPrice = amtSum;
res.Add(new PXResult <Account, OrderDetail, Product>(a1, resultDetail, resultProd));
amtSum = 0;
}
pendingProd = p1;
pendingAccount = a1;
amtSum += od1.UnitPrice * od1.OrderDetailQty;
}
if (pendingProd != null && pendingAccount != null)
{
Product resultProd = new Product();
resultProd.CategoryCD = pendingProd.CategoryCD;
OrderDetail resultDetail = new OrderDetail();
resultDetail.ExtPrice = amtSum;
res.Add(new PXResult <Account, OrderDetail, Product>(pendingAccount, resultDetail, resultProd));
}
return(res);
default: // 0
return(null);
}
}
protected virtual IEnumerable items()
{
BillingFilter filter = Filter.Current;
if (filter == null)
{
yield break;
}
bool found = false;
foreach (ProjectsList item in Items.Cache.Inserted)
{
found = true;
yield return(item);
}
if (found)
{
yield break;
}
PXSelectBase <PMProject> selectUnbilled = new PXSelectJoinGroupBy <PMProject,
InnerJoin <PMUnbilledDailySummary, On <PMUnbilledDailySummary.projectID, Equal <PMProject.contractID> >,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMUnbilledDailySummary.projectID>,
And <PMTask.isActive, Equal <True>,
And <PMTask.taskID, Equal <PMUnbilledDailySummary.taskID>,
And <Where <PMTask.billingOption, Equal <PMBillingOption.onBilling>,
Or2 <Where <PMTask.billingOption, Equal <PMBillingOption.onTaskCompletion>, And <PMTask.isCompleted, Equal <True> > >,
Or <Where <PMTask.billingOption, Equal <PMBillingOption.onProjectCompetion>, And <PMProject.isCompleted, Equal <True> > > > > > > > > >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID>,
And <PMBillingRule.accountGroupID, Equal <PMUnbilledDailySummary.accountGroupID> > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And2 <Where <PMBillingRule.includeNonBillable, Equal <False>, And <PMUnbilledDailySummary.billable, Greater <int0>,
Or <Where <PMBillingRule.includeNonBillable, Equal <True>, And <Where <PMUnbilledDailySummary.nonBillable, Greater <int0>, Or <PMUnbilledDailySummary.billable, Greater <int0> > > > > > > >,
And2 <Where <PMUnbilledDailySummary.date, LessEqual <Current <BillingFilter.invoiceDate> > >, And <Match <Current <AccessInfo.userName> > > > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
if (Setup.Current.CutoffDate == PMCutOffDate.Excluded)
{
selectUnbilled = new PXSelectJoinGroupBy <PMProject,
InnerJoin <PMUnbilledDailySummary, On <PMUnbilledDailySummary.projectID, Equal <PMProject.contractID> >,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMUnbilledDailySummary.projectID>,
And <PMTask.isActive, Equal <True>,
And <PMTask.taskID, Equal <PMUnbilledDailySummary.taskID>,
And <Where <PMTask.billingOption, Equal <PMBillingOption.onBilling>,
Or2 <Where <PMTask.billingOption, Equal <PMBillingOption.onTaskCompletion>, And <PMTask.isCompleted, Equal <True> > >,
Or <Where <PMTask.billingOption, Equal <PMBillingOption.onProjectCompetion>, And <PMProject.isCompleted, Equal <True> > > > > > > > > >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID>,
And <PMBillingRule.accountGroupID, Equal <PMUnbilledDailySummary.accountGroupID> > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And2 <Where <PMBillingRule.includeNonBillable, Equal <False>, And <PMUnbilledDailySummary.billable, Greater <int0>,
Or <Where <PMBillingRule.includeNonBillable, Equal <True>, And <Where <PMUnbilledDailySummary.nonBillable, Greater <int0>, Or <PMUnbilledDailySummary.billable, Greater <int0> > > > > > > >,
And2 <Where <PMUnbilledDailySummary.date, Less <Current <BillingFilter.invoiceDate> > >, And <Match <Current <AccessInfo.userName> > > > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
}
PXSelectBase <PMProject> selectRecurring = new PXSelectJoinGroupBy <PMProject,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMProject.contractID> >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID> >,
InnerJoin <PMRecurringItem, On <PMTask.projectID, Equal <PMRecurringItem.projectID>,
And <PMTask.taskID, Equal <PMRecurringItem.taskID>,
And <PMTask.isCompleted, Equal <False> > > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And <Match <Current <AccessInfo.userName> > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
PXSelectBase <PMProject> selectProgressive = new PXSelectJoinGroupBy <PMProject,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMProject.contractID> >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID> >,
InnerJoin <PMBudget, On <PMTask.projectID, Equal <PMBudget.projectID>,
And <PMTask.taskID, Equal <PMBudget.projectTaskID>,
And <PMBudget.type, Equal <GL.AccountType.income>,
And <PMBudget.curyAmountToInvoice, NotEqual <decimal0> > > > > > > > > >,
Where <Match <Current <AccessInfo.userName> > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
if (filter.StatementCycleId != null)
{
selectUnbilled.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
selectRecurring.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
selectProgressive.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
}
if (filter.CustomerClassID != null)
{
selectUnbilled.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
selectRecurring.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
selectProgressive.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
}
if (filter.CustomerID != null)
{
selectUnbilled.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
selectRecurring.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
selectProgressive.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
}
if (filter.TemplateID != null)
{
selectUnbilled.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
selectRecurring.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
selectProgressive.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
}
using (new PXFieldScope(selectUnbilled.View,
typeof(PMProject.contractID),
typeof(PMProject.contractCD),
typeof(PMProject.description),
typeof(PMProject.customerID),
typeof(ContractBillingSchedule.contractID),
typeof(ContractBillingSchedule.lastDate),
typeof(ContractBillingSchedule.nextDate)))
{
foreach (PXResult item in selectUnbilled.Select())
{
var result = CreateListItem(item);
if (Items.Locate(result) == null)
{
yield return(Items.Insert(result));
}
}
}
using (new PXFieldScope(selectRecurring.View,
typeof(PMProject.contractID),
typeof(PMProject.contractCD),
typeof(PMProject.description),
typeof(PMProject.customerID),
typeof(ContractBillingSchedule.contractID),
typeof(ContractBillingSchedule.lastDate),
typeof(ContractBillingSchedule.nextDate)))
{
foreach (PXResult item in selectRecurring.Select())
{
var result = CreateListItem(item);
if (Items.Locate(result) == null)
{
yield return(Items.Insert(result));
}
}
}
using (new PXFieldScope(selectProgressive.View,
typeof(PMProject.contractID),
typeof(PMProject.contractCD),
typeof(PMProject.description),
typeof(PMProject.customerID),
typeof(ContractBillingSchedule.contractID),
typeof(ContractBillingSchedule.lastDate),
typeof(ContractBillingSchedule.nextDate)
))
{
foreach (PXResult item in selectProgressive.Select())
{
var result = CreateListItem(item);
if (Items.Locate(result) == null)
{
yield return(Items.Insert(result));
}
}
}
Items.Cache.IsDirty = false;
}
protected virtual IEnumerable internalResultRecords()
{
const bool useItemCostHistoryToOptimizeBalanceCalculation = true; // false to debug and ensure that INItemCostHistory corresponds to INCostTran
InventoryTranByAcctEnqFilter filter = Filter.Current;
bool summaryByDay = filter.SummaryByDay ?? false;
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.tranType>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.docRefNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.subItemCD>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.siteID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.locationID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.accountID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.subID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.inventoryID>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.costAdj>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.finPerNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.tranPerNbr>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.qty>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible <InventoryTranByAcctEnqResult.unitCost>(ResultRecords.Cache, null, !summaryByDay);
PXUIFieldAttribute.SetVisible(Tran.Cache, null, !summaryByDay);
var resultList = new List <PXResult <InventoryTranByAcctEnqResult, INTran> >();
decimal cumulativeBalance = 0m;
if (filter.AccountID == null)
{
return(resultList); //empty
}
if (filter.FinPeriodID == null)
{
return(resultList); //empty
}
if (useItemCostHistoryToOptimizeBalanceCalculation)
{
PXSelectBase <INItemCostHist> cmd_CostHist = new PXSelectJoinGroupBy <INItemCostHist,
InnerJoin <Sub,
On <Sub.subID, Equal <INItemCostHist.subID> > >,
Where <INItemCostHist.finPeriodID, Less <Current <InventoryTranByAcctEnqFilter.finPeriodID> > >,
Aggregate <
Sum <INItemCostHist.tranYtdCost,
Sum <INItemCostHist.tranBegCost,
Sum <INItemCostHist.finYtdCost,
Sum <INItemCostHist.finBegCost> > > > > >(this);
//if (filter.AccountID != null) // checked above
{
cmd_CostHist.WhereAnd <Where <INItemCostHist.accountID, Equal <Current <InventoryTranByAcctEnqFilter.accountID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd_CostHist.WhereAnd <Where <Sub.subCD, Like <Current <InventoryTranByAcctEnqFilter.subCDWildcard> > > >();
}
PXResultset <INItemCostHist> costHistResult = (PXResultset <INItemCostHist>)cmd_CostHist.Select();
if (costHistResult.Count == 1) // 0 is possible too
{
if (filter.ByFinancialPeriod == true)
{
cumulativeBalance += (((INItemCostHist)costHistResult[0]).FinYtdCost ?? 0m) - (((INItemCostHist)costHistResult[0]).FinBegCost ?? 0m);
}
else
{
cumulativeBalance += (((INItemCostHist)costHistResult[0]).TranYtdCost ?? 0m) - (((INItemCostHist)costHistResult[0]).TranBegCost ?? 0m);
}
}
}
PXSelectBase <INTranCost> cmd = new PXSelectReadonly2 <INTranCost,
InnerJoin <INTran,
On <INTran.tranType, Equal <INTranCost.tranType>,
And <INTran.refNbr, Equal <INTranCost.refNbr>,
And <INTran.lineNbr, Equal <INTranCost.lineNbr> > > >,
InnerJoin <InventoryItem, On <InventoryItem.inventoryID, Equal <INTranCost.inventoryID>,
And <Match <InventoryItem, Current <AccessInfo.userName> > > >,
InnerJoin <Sub, On <Sub.subID, Equal <INTranCost.invtSubID> >,
InnerJoin <INSubItem, On <INSubItem.subItemID, Equal <INTranCost.costSubItemID> >,
LeftJoin <INSite, On <INSite.siteID, Equal <INTranCost.costSiteID> >,
LeftJoin <INLocation, On <INLocation.locationID, Equal <INTranCost.costSiteID> >,
LeftJoin <INCostStatus, On <INTranCost.costID, Equal <INCostStatus.costID> > > > > > > > >,
Where <INSite.siteID, IsNull,
Or <Match <INSite, Current <AccessInfo.userName> > > >,
OrderBy <Asc <INTranCost.tranDate,
Asc <INTranCost.createdDateTime> > > >(this);
//if (filter.FinPeriodID != null) // checked above
if (filter.ByFinancialPeriod == true)
{
cmd.WhereAnd <Where <INTranCost.finPeriodID, Equal <Current <InventoryTranByAcctEnqFilter.finPeriodID> > > >();
}
else
{
cmd.WhereAnd <Where <INTranCost.tranPeriodID, Equal <Current <InventoryTranByAcctEnqFilter.finPeriodID> > > >();
}
//if (filter.AccountID != null) // checked above
{
cmd.WhereAnd <Where <INTranCost.invtAcctID, Equal <Current <InventoryTranByAcctEnqFilter.accountID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd.WhereAnd <Where <Sub.subCD, Like <Current <InventoryTranByAcctEnqFilter.subCDWildcard> > > >();
}
if (filter.EndDate != null)
{
cmd.WhereAnd <Where <INTranCost.tranDate, LessEqual <Current <InventoryTranByAcctEnqFilter.endDate> > > >();
}
if (filter.InventoryID != null)
{
cmd.WhereAnd <Where <INTranCost.inventoryID, Equal <Current <InventoryTranByAcctEnqFilter.inventoryID> > > >();
}
if (filter.SiteID != null)
{
cmd.WhereAnd <Where <INTranCost.costSiteID, Equal <Current <InventoryTranByAcctEnqFilter.siteID> > > >();
}
int gridLineNbr = 0;
foreach (PXResult <INTranCost, INTran, InventoryItem, Sub, INSubItem, INSite, INLocation, INCostStatus> it in cmd.Select())
{
INTranCost tc_rec = (INTranCost)it;
INTran t_rec = (INTran)it;
INSite s_rec = (INSite)it;
INLocation l_rec = (INLocation)it;
INSubItem si_rec = (INSubItem)it;
INCostStatus cs_rec = (INCostStatus)it;
decimal rowCost = (tc_rec.InvtMult * tc_rec.TranCost) ?? 0m;
if (tc_rec.TranDate < filter.StartDate)
{
cumulativeBalance += rowCost;
}
else
{
if (summaryByDay)
{
if ((resultList.Count > 0) && (((InventoryTranByAcctEnqResult)resultList[resultList.Count - 1]).TranDate == tc_rec.TranDate))
{
InventoryTranByAcctEnqResult lastItem = resultList[resultList.Count - 1];
if (rowCost >= 0)
{
lastItem.Debit += rowCost;
}
else
{
lastItem.Credit -= rowCost;
}
lastItem.EndBalance += rowCost;
resultList[resultList.Count - 1] = new PXResult <InventoryTranByAcctEnqResult, INTran>(lastItem, null);
}
else
{
InventoryTranByAcctEnqResult item = new InventoryTranByAcctEnqResult();
item.BegBalance = cumulativeBalance;
item.TranDate = tc_rec.TranDate;
if (rowCost >= 0)
{
item.Debit = rowCost;
item.Credit = 0m;
}
else
{
item.Debit = 0m;
item.Credit = -rowCost;
}
item.EndBalance = item.BegBalance + rowCost;
item.GridLineNbr = ++gridLineNbr;
item.CreatedDateTime = tc_rec.CreatedDateTime;
resultList.Add(new PXResult <InventoryTranByAcctEnqResult, INTran>(item, null));
}
cumulativeBalance += rowCost;
}
else
{
InventoryTranByAcctEnqResult item = new InventoryTranByAcctEnqResult();
item.BegBalance = cumulativeBalance;
item.TranDate = tc_rec.TranDate;
if (rowCost >= 0)
{
item.Debit = rowCost;
item.Credit = 0m;
}
else
{
item.Debit = 0m;
item.Credit = -rowCost;
}
item.EndBalance = item.BegBalance + rowCost;
item.AccountID = tc_rec.InvtAcctID;
item.SubID = tc_rec.InvtSubID;
item.TranType = tc_rec.TranType;
item.DocType = t_rec.DocType;
item.DocRefNbr = tc_rec.RefNbr;
item.ReceiptNbr = cs_rec.ReceiptNbr;
item.InventoryID = tc_rec.InventoryID;
item.SubItemCD = si_rec.SubItemCD;
if (s_rec.SiteID != null)
{
item.SiteID = s_rec.SiteID;
item.LocationID = null;
}
else
if (l_rec.LocationID != null) //though it's more or less guaranteed
{
item.SiteID = l_rec.SiteID;
item.LocationID = l_rec.LocationID;
}
item.TranDate = tc_rec.TranDate;
item.FinPerNbr = tc_rec.FinPeriodID;
item.TranPerNbr = tc_rec.TranPeriodID;
item.Qty = tc_rec.Qty * tc_rec.InvtMult;
item.UnitCost = (tc_rec.Qty ?? 0m) == 0m ? null : ((tc_rec.TranCost ?? 0m) + (tc_rec.VarCost ?? 0m)) / tc_rec.Qty;
item.CostAdj = tc_rec.CostRefNbr != tc_rec.RefNbr;
item.GridLineNbr = ++gridLineNbr;
item.CreatedDateTime = tc_rec.CreatedDateTime;
resultList.Add(new PXResult <InventoryTranByAcctEnqResult, INTran>(item, t_rec));
cumulativeBalance += rowCost;
}
}
}
return(resultList);
}
public virtual IEnumerable pOOrderslist()
{
APInvoice doc = Base.Document.Current;
bool isInvoice = (doc.DocType == APDocType.Invoice),
isPrepayment = (doc.DocType == APDocType.Prepayment);
if (doc?.VendorID == null ||
doc.VendorLocationID == null ||
!isInvoice && !isPrepayment)
{
yield break;
}
var usedOrderLines = new Dictionary <APTran, int>(new POOrderComparer());
foreach (APTran aPTran in Base.Transactions.Select().RowCast <APTran>().AsEnumerable()
.Where(t => !string.IsNullOrEmpty(t.PONbr) && (isPrepayment || t.POAccrualType == POAccrualType.Order)))
{
usedOrderLines.TryGetValue(aPTran, out int count);
usedOrderLines[aPTran] = count + 1;
}
PXSelectBase <POOrderRS> cmd = new PXSelectJoinGroupBy <
POOrderRS,
InnerJoin <POLine, On <POLine.orderType, Equal <POOrderRS.orderType>,
And <POLine.orderNbr, Equal <POOrderRS.orderNbr> > > >,
Where <POOrderRS.orderType, NotIn3 <POOrderType.blanket, POOrderType.standardBlanket>,
And <POOrderRS.curyID, Equal <Current <APInvoice.curyID> >,
And <POLine.cancelled, NotEqual <True>,
And <POLine.closed, NotEqual <True>,
And <POOrderRS.status, In3 <POOrderStatus.open, POOrderStatus.completed> > > > > >,
Aggregate
<GroupBy <POOrderRS.orderType,
GroupBy <POOrderRS.orderNbr,
GroupBy <POOrderRS.orderDate,
GroupBy <POOrderRS.curyID,
GroupBy <POOrderRS.curyOrderTotal,
GroupBy <POOrderRS.hold,
GroupBy <POOrderRS.cancelled,
Sum <POLine.orderQty,
Sum <POLine.curyExtCost,
Sum <POLine.extCost,
Count <POLine.lineNbr> > > > > > > > > > > > >(Base);
if (isInvoice)
{
cmd.WhereAnd <Where <POLine.pOAccrualType, Equal <POAccrualType.order> > >();
}
else if (isPrepayment)
{
cmd.WhereAnd <Where <POOrderRS.taxZoneID, Equal <Current <APInvoice.taxZoneID> >, Or <POOrderRS.taxZoneID, IsNull, And <Current <APInvoice.taxZoneID>, IsNull> > > >();
}
if (Base.APSetup.Current.RequireSingleProjectPerDocument == true)
{
cmd.WhereAnd <Where <POOrderRS.projectID, Equal <Current <APInvoice.projectID> > > >();
}
if (PXAccess.FeatureInstalled <FeaturesSet.vendorRelations>())
{
cmd.WhereAnd <Where <POOrderRS.vendorID, Equal <Current <APInvoice.suppliedByVendorID> >,
And <POOrderRS.vendorLocationID, Equal <Current <APInvoice.suppliedByVendorLocationID> >,
And <POOrderRS.payToVendorID, Equal <Current <APInvoice.vendorID> > > > > >();
}
else
{
cmd.WhereAnd <Where <POOrderRS.vendorID, Equal <Current <APInvoice.vendorID> >,
And <POOrderRS.vendorLocationID, Equal <Current <APInvoice.vendorLocationID> > > > >();
}
foreach (PXResult <POOrderRS, POLine> result in cmd.View.SelectMultiBound(new object[] { doc }))
{
POOrderRS order = result;
APTran aPTran = new APTran
{
PONbr = order.OrderNbr,
POOrderType = order.OrderType
};
usedOrderLines.TryGetValue(aPTran, out int count);
if (count < result.RowCount)
{
yield return(order);
}
}
}
protected virtual IEnumerable enqResult()
{
GLHistoryEnqFilter filter = CurrentFilter;
bool showCurrency = filter.ShowCuryDetail == true;
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyID>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdCreditTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyPtdDebitTotal>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.curyEndBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryBegBalance>(EnqResult.Cache, null, showCurrency);
PXUIFieldAttribute.SetVisible <GLHistoryEnquiryResult.signCuryEndBalance>(EnqResult.Cache, null, showCurrency);
if (filter.AccountID == null || filter.LedgerID == null || filter.FinPeriodID == null)
{
yield break; //Prevent code from accessing database;
}
PXSelectBase <GLHistoryByPeriod> cmd = new PXSelectJoinGroupBy <GLHistoryByPeriod,
InnerJoin <Account,
On <GLHistoryByPeriod.accountID, Equal <Account.accountID>, And <Match <Account, Current <AccessInfo.userName> > > >,
InnerJoin <Sub,
On <GLHistoryByPeriod.subID, Equal <Sub.subID>, And <Match <Sub, Current <AccessInfo.userName> > > >,
LeftJoin <GLHistory, On <GLHistoryByPeriod.accountID, Equal <GLHistory.accountID>,
And <GLHistoryByPeriod.ledgerID, Equal <GLHistory.ledgerID>,
And <GLHistoryByPeriod.branchID, Equal <GLHistory.branchID>,
And <GLHistoryByPeriod.subID, Equal <GLHistory.subID>,
And <GLHistoryByPeriod.finPeriodID, Equal <GLHistory.finPeriodID> > > > > >,
LeftJoin <AH, On <GLHistoryByPeriod.ledgerID, Equal <AH.ledgerID>,
And <GLHistoryByPeriod.branchID, Equal <AH.branchID>,
And <GLHistoryByPeriod.accountID, Equal <AH.accountID>,
And <GLHistoryByPeriod.subID, Equal <AH.subID>,
And <GLHistoryByPeriod.lastActivityPeriod, Equal <AH.finPeriodID> > > > > > > > > >,
Where <GLHistoryByPeriod.ledgerID, Equal <Current <GLHistoryEnqFilter.ledgerID> >,
And <GLHistoryByPeriod.accountID, Equal <Current <GLHistoryEnqFilter.accountID> >,
And <GLHistoryByPeriod.finPeriodID, Equal <Current <GLHistoryEnqFilter.finPeriodID> >,
And <
Where2 <
Where <Account.accountID, NotEqual <Current <GLSetup.ytdNetIncAccountID> >, And <Where <Account.type, Equal <AccountType.asset>,
Or <Account.type, Equal <AccountType.liability> > > > >,
Or <Where <GLHistoryByPeriod.lastActivityPeriod, GreaterEqual <Required <GLHistoryByPeriod.lastActivityPeriod> >,
And <Where <Account.type, Equal <AccountType.expense>,
Or <Account.type, Equal <AccountType.income>,
Or <Account.accountID, Equal <Current <GLSetup.ytdNetIncAccountID> > > > > > > > > > > > >,
Aggregate <
Sum <AH.finYtdBalance,
Sum <AH.curyFinYtdBalance,
Sum <GLHistory.finPtdDebit,
Sum <GLHistory.finPtdCredit,
Sum <GLHistory.finBegBalance,
Sum <GLHistory.finYtdBalance,
Sum <GLHistory.curyFinBegBalance,
Sum <GLHistory.curyFinYtdBalance,
Sum <GLHistory.curyFinPtdCredit,
Sum <GLHistory.curyFinPtdDebit,
GroupBy <GLHistoryByPeriod.ledgerID,
GroupBy <GLHistoryByPeriod.accountID,
GroupBy <GLHistoryByPeriod.subID> > > > > > > > > > > > > > >(this);
int[] branchIDs = null;
if (filter.BranchID != null)
{
cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, Equal <Current <GLHistoryEnqFilter.branchID> > > >();
}
else if (filter.OrganizationID != null)
{
branchIDs = PXAccess.GetChildBranchIDs(filter.OrganizationID);
cmd.WhereAnd <Where <GLHistoryByPeriod.branchID, In <Required <GLHistoryEnqFilter.branchID> > > >();
}
if (!SubCDUtils.IsSubCDEmpty(filter.SubCD))
{
cmd.WhereAnd <Where <Sub.subCD, Like <Current <GLHistoryEnqFilter.subCDWildcard> > > >();
}
foreach (PXResult <GLHistoryByPeriod, Account, Sub, GLHistory, AH> it in cmd.Select(filter.BegFinPeriod, branchIDs))
{
GLHistoryByPeriod baseview = it;
Account acct = it;
GLHistory ah = it;
AH ah1 = it;
GLHistoryEnquiryResult item = new GLHistoryEnquiryResult
{
AccountID = baseview.AccountID,
AccountCD = acct.AccountCD,
LedgerID = baseview.LedgerID,
Type = acct.Type,
Description = acct.Description,
LastActivityPeriod = baseview.LastActivityPeriod,
PtdCreditTotal = ah.FinPtdCredit,
PtdDebitTotal = ah.FinPtdDebit,
CuryID = ah1.CuryID,
SubCD = ((Sub)it).SubCD,
EndBalance = ah1.FinYtdBalance
};
if (!string.IsNullOrEmpty(ah1.CuryID))
{
item.CuryEndBalance = ah1.CuryFinYtdBalance;
item.CuryPtdCreditTotal = ah.CuryFinPtdCredit;
item.CuryPtdDebitTotal = ah.CuryFinPtdDebit;
}
else
{
item.CuryEndBalance = null;
item.CuryPtdCreditTotal = null;
item.CuryPtdDebitTotal = null;
}
item.recalculate(true); // End balance is considered as correct digit - so we need to calculate begBalance base on ending one
item.recalculateSignAmount(glsetup.Current?.TrialBalanceSign == GLSetup.trialBalanceSign.Reversed);
yield return(item);
}
}
protected virtual IEnumerable items()
{
BillingFilter filter = Filter.Current;
if (filter == null)
{
yield break;
}
bool found = false;
foreach (ProjectsList item in Items.Cache.Inserted)
{
found = true;
yield return(item);
}
if (found)
{
yield break;
}
PXSelectBase <PMProject> selectUnbilled = new PXSelectJoinGroupBy <PMProject,
InnerJoin <PMUnbilledDailySummary, On <PMUnbilledDailySummary.projectID, Equal <PMProject.contractID> >,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMUnbilledDailySummary.projectID>,
And <PMTask.isActive, Equal <True>,
And <PMTask.taskID, Equal <PMUnbilledDailySummary.taskID>,
And <Where <PMTask.billingOption, Equal <PMBillingOption.onBilling>,
Or2 <Where <PMTask.billingOption, Equal <PMBillingOption.onTaskCompletion>, And <PMTask.isCompleted, Equal <True> > >,
Or <Where <PMTask.billingOption, Equal <PMBillingOption.onProjectCompetion>, And <PMProject.isCompleted, Equal <True> > > > > > > > > >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID>,
And <PMBillingRule.accountGroupID, Equal <PMUnbilledDailySummary.accountGroupID> > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And2 <Where <PMBillingRule.includeNonBillable, Equal <False>, And <PMUnbilledDailySummary.billable, Greater <int0>,
Or <Where <PMBillingRule.includeNonBillable, Equal <True>, And <Where <PMUnbilledDailySummary.nonBillable, Greater <int0>, Or <PMUnbilledDailySummary.billable, Greater <int0> > > > > > > >,
And2 <Where <PMUnbilledDailySummary.date, LessEqual <Current <BillingFilter.invoiceDate> > >, And <Match <Current <AccessInfo.userName> > > > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
if (Setup.Current.CutoffDate == PMCutOffDate.Excluded)
{
selectUnbilled = new PXSelectJoinGroupBy <PMProject,
InnerJoin <PMUnbilledDailySummary, On <PMUnbilledDailySummary.projectID, Equal <PMProject.contractID> >,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMUnbilledDailySummary.projectID>,
And <PMTask.isActive, Equal <True>,
And <PMTask.taskID, Equal <PMUnbilledDailySummary.taskID>,
And <Where <PMTask.billingOption, Equal <PMBillingOption.onBilling>,
Or2 <Where <PMTask.billingOption, Equal <PMBillingOption.onTaskCompletion>, And <PMTask.isCompleted, Equal <True> > >,
Or <Where <PMTask.billingOption, Equal <PMBillingOption.onProjectCompetion>, And <PMProject.isCompleted, Equal <True> > > > > > > > > >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID>,
And <PMBillingRule.accountGroupID, Equal <PMUnbilledDailySummary.accountGroupID> > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And2 <Where <PMBillingRule.includeNonBillable, Equal <False>, And <PMUnbilledDailySummary.billable, Greater <int0>,
Or <Where <PMBillingRule.includeNonBillable, Equal <True>, And <Where <PMUnbilledDailySummary.nonBillable, Greater <int0>, Or <PMUnbilledDailySummary.billable, Greater <int0> > > > > > > >,
And2 <Where <PMUnbilledDailySummary.date, Less <Current <BillingFilter.invoiceDate> > >, And <Match <Current <AccessInfo.userName> > > > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
}
PXSelectBase <PMProject> selectRecurring = new PXSelectJoinGroupBy <PMProject,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMProject.contractID> >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID> >,
InnerJoin <PMRecurringItem, On <PMTask.projectID, Equal <PMRecurringItem.projectID>,
And <PMTask.taskID, Equal <PMRecurringItem.taskID>,
And <PMTask.isCompleted, Equal <False> > > > > > > > >,
Where2 <Where <ContractBillingSchedule.nextDate, LessEqual <Current <BillingFilter.invoiceDate> >,
Or <ContractBillingSchedule.type, Equal <BillingType.BillingOnDemand> > >,
And <Match <Current <AccessInfo.userName> > > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
PXSelectBase <PMProject> selectProgressive = new PXSelectJoinGroupBy <PMProject,
InnerJoin <ContractBillingSchedule, On <PMProject.contractID, Equal <ContractBillingSchedule.contractID> >,
InnerJoin <Customer, On <PMProject.customerID, Equal <Customer.bAccountID> >,
InnerJoin <PMTask, On <PMTask.projectID, Equal <PMProject.contractID> >,
InnerJoin <PMBillingRule, On <PMBillingRule.billingID, Equal <PMTask.billingID> >,
InnerJoin <PMBudget, On <PMTask.projectID, Equal <PMBudget.projectID>,
And <PMTask.taskID, Equal <PMBudget.projectTaskID>,
And <PMBudget.type, Equal <GL.AccountType.income>,
And <PMBudget.amountToInvoice, NotEqual <decimal0> > > > > > > > > >,
Where <Match <Current <AccessInfo.userName> > >,
Aggregate <GroupBy <PMProject.contractID> > >(this);
if (filter.StatementCycleId != null)
{
selectUnbilled.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
selectRecurring.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
selectProgressive.WhereAnd <Where <Customer.statementCycleId, Equal <Current <BillingFilter.statementCycleId> > > >();
}
if (filter.CustomerClassID != null)
{
selectUnbilled.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
selectRecurring.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
selectProgressive.WhereAnd <Where <Customer.customerClassID, Equal <Current <BillingFilter.customerClassID> > > >();
}
if (filter.CustomerID != null)
{
selectUnbilled.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
selectRecurring.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
selectProgressive.WhereAnd <Where <Customer.bAccountID, Equal <Current <BillingFilter.customerID> > > >();
}
if (filter.TemplateID != null)
{
selectUnbilled.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
selectRecurring.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
selectProgressive.WhereAnd <Where <PMProject.templateID, Equal <Current <BillingFilter.templateID> > > >();
}
foreach (PXResult item in selectUnbilled.Select().Union(selectRecurring.Select()).Union(selectProgressive.Select()))
{
PMProject project = PXResult.Unwrap <PMProject>(item);
ContractBillingSchedule schedule = PXResult.Unwrap <ContractBillingSchedule>(item);
Customer customer = PXResult.Unwrap <Customer>(item);
ProjectsList result = new ProjectsList();
result.ProjectID = project.ContractID;
result.ProjectCD = project.ContractCD;
result.Description = project.Description;
result.CustomerID = project.CustomerID;
result.CustomerName = customer.AcctName;
result.LastDate = schedule.LastDate;
DateTime?fromDate = null;
if (schedule.NextDate != null)
{
switch (schedule.Type)
{
case BillingType.Annual:
fromDate = schedule.NextDate.Value.AddYears(-1);
break;
case BillingType.Monthly:
fromDate = schedule.NextDate.Value.AddMonths(-1);
break;
case BillingType.Weekly:
fromDate = schedule.NextDate.Value.AddDays(-7);
break;
case BillingType.Quarterly:
fromDate = schedule.NextDate.Value.AddMonths(-3);
break;
}
}
result.FromDate = fromDate;
result.NextDate = schedule.NextDate;
if (Items.Locate(result) == null)
{
yield return(Items.Insert(result));
}
}
Items.Cache.IsDirty = false;
}
/// <summary>
/// Generates a list of documents that meet the filter criteria.
/// This list is used for display in the processing screen
/// </summary>
/// <returns>List of Dunning Letters</returns>
protected virtual IEnumerable details()
{
Details.Cache.Clear();
PrintParameters header = Filter.Current;
if (header == null)
{
yield break;
}
List <DetailsResult> results = new List <DetailsResult>();
PXSelectBase <ARDunningLetter> cmd = new PXSelectJoinGroupBy <ARDunningLetter,
InnerJoin <ARDunningLetterDetail,
On <ARDunningLetterDetail.dunningLetterID, Equal <ARDunningLetter.dunningLetterID> > >,
Where <ARDunningLetter.dunningLetterDate,
Between <Required <ARDunningLetter.dunningLetterDate>, Required <ARDunningLetter.dunningLetterDate> >,
And <ARDunningLetter.consolidated, Equal <Required <ARDunningLetter.consolidated> > > >,
Aggregate <GroupBy <ARDunningLetter.dunningLetterID, Sum <ARDunningLetterDetail.overdueBal> > >,
OrderBy <Asc <ARDunningLetter.bAccountID> > >(this);
if (Filter.Current.Action == ActionTypes.Release)
{
cmd.WhereAnd <Where <ARDunningLetter.released, Equal <False>, And <ARDunningLetter.voided, Equal <False> > > >();
}
else
{
cmd.WhereAnd <Where <ARDunningLetter.released, Equal <True>, And <ARDunningLetter.voided, Equal <False> > > >();
}
foreach (PXResult <ARDunningLetter, ARDunningLetterDetail> res in cmd.Select(header.BeginDate, header.EndDate, this.Consolidated))
{
ARDunningLetter dunningLetter = res;
ARDunningLetterDetail detailSum = res;
if (Filter.Current.Action == ActionTypes.Print &&
header.ShowAll == false &&
(dunningLetter.DontPrint == true || dunningLetter.Printed == true))
{
continue;
}
if (Filter.Current.Action == ActionTypes.Email &&
header.ShowAll == false &&
(dunningLetter.DontEmail == true || dunningLetter.Emailed == true))
{
continue;
}
if (Filter.Current.Action == ActionTypes.MarkDontEmail &&
header.ShowAll == false &&
(dunningLetter.DontEmail == true || dunningLetter.Emailed == true))
{
continue;
}
DetailsResult row = new DetailsResult();
row.BranchID = dunningLetter.BranchID;
row.CustomerId = dunningLetter.BAccountID;
row.DunningLetterID = dunningLetter.DunningLetterID;
row.DunningLetterDate = dunningLetter.DunningLetterDate;
row.DunningLetterLevel = dunningLetter.DunningLetterLevel;
row.LastLevel = dunningLetter.LastLevel;
row.DontEmail = dunningLetter.DontEmail;
row.DontPrint = dunningLetter.DontPrint;
row.Emailed = dunningLetter.Emailed;
row.Printed = dunningLetter.Printed;
row.DocBal = detailSum.OverdueBal;
results.Add(row);
}
foreach (DetailsResult item in results)
{
Details.Cache.SetStatus(item, PXEntryStatus.Held);
yield return(item);
}
Details.Cache.IsDirty = false;
}
public virtual IEnumerable pOOrderslist()
{
APInvoice doc = Base.Document.Current;
if (doc?.VendorID == null ||
doc.VendorLocationID == null ||
doc.DocType != APDocType.Invoice &&
doc.DocType != APDocType.DebitAdj)
{
yield break;
}
Dictionary <APTran, int> usedOrder = new Dictionary <APTran, int>(new POOrderComparer());
int count;
foreach (APTran aPTran in Base.Transactions.Cache.Inserted)
{
if (aPTran.PONbr != null && aPTran.POOrderType != null && string.IsNullOrEmpty(aPTran.ReceiptNbr))
{
usedOrder.TryGetValue(aPTran, out count);
usedOrder[aPTran] = count + 1;
}
}
foreach (APTran aPTran in Base.Transactions.Cache.Deleted)
{
if (aPTran.PONbr != null && aPTran.POOrderType != null && string.IsNullOrEmpty(aPTran.ReceiptNbr) && Base.Transactions.Cache.GetStatus(aPTran) != PXEntryStatus.InsertedDeleted)
{
usedOrder.TryGetValue(aPTran, out count);
usedOrder[aPTran] = count - 1;
}
}
foreach (APTran aPTran in Base.Transactions.Cache.Updated)
{
string originalNbr = (string)Base.Transactions.Cache.GetValueOriginal <APTran.pONbr>(aPTran);
string originalType = (string)Base.Transactions.Cache.GetValueOriginal <APTran.pOOrderType>(aPTran);
if (aPTran.PONbr != originalNbr || aPTran.POOrderType != originalType)
{
if (originalNbr != null && originalType != null)
{
APTran originTran = new APTran
{
PONbr = originalNbr,
POOrderType = originalType
};
usedOrder.TryGetValue(originTran, out count);
usedOrder[originTran] = count - 1;
}
if (aPTran.PONbr != null)
{
usedOrder.TryGetValue(aPTran, out count);
usedOrder[aPTran] = count + 1;
}
}
}
PXSelectBase <POOrder> cmd = new PXSelectJoinGroupBy <
POOrder,
InnerJoin <POLine, On <POLine.orderType, Equal <POOrder.orderType>,
And <POLine.orderNbr, Equal <POOrder.orderNbr>,
And <POLine.pOAccrualType, Equal <POAccrualType.order>,
And <POLine.cancelled, NotEqual <True>,
And <POLine.closed, NotEqual <True> > > > > >,
LeftJoin <APTran,
On <APTran.pOOrderType, Equal <POLine.orderType>,
And <APTran.pONbr, Equal <POLine.orderNbr>,
And <APTran.pOLineNbr, Equal <POLine.lineNbr>,
And <APTran.receiptNbr, IsNull,
And <APTran.receiptLineNbr, IsNull,
And <APTran.released, Equal <False> > > > > > > > >,
Where <APTran.refNbr, IsNull,
And <POOrder.orderType, NotEqual <POOrderType.blanket>,
And <POOrder.orderType, NotEqual <POOrderType.standardBlanket>,
And <POOrder.curyID, Equal <Current <APInvoice.curyID> >,
And <POOrder.status, Equal <POOrderStatus.open> > > > > >,
Aggregate
<GroupBy <POOrder.orderType,
GroupBy <POOrder.orderNbr,
GroupBy <POOrder.orderDate,
GroupBy <POOrder.curyID,
GroupBy <POOrder.curyOrderTotal,
GroupBy <POOrder.hold,
GroupBy <POOrder.receipt,
GroupBy <POOrder.cancelled,
Sum <POLine.orderQty,
Sum <POLine.curyExtCost,
Sum <POLine.extCost,
Count <POLine.lineNbr> > > > > > > > > > > > > >(Base);
if (Base.APSetup.Current.RequireSingleProjectPerDocument == true)
{
cmd.WhereAnd <Where <POOrder.projectID, Equal <Current <APInvoice.projectID> > > >();
}
if (PXAccess.FeatureInstalled <FeaturesSet.vendorRelations>())
{
cmd.WhereAnd <Where <POOrder.vendorID, Equal <Current <APInvoice.suppliedByVendorID> >,
And <POOrder.vendorLocationID, Equal <Current <APInvoice.suppliedByVendorLocationID> >,
And <POOrder.payToVendorID, Equal <Current <APInvoice.vendorID> > > > > >();
}
else
{
cmd.WhereAnd <Where <POOrder.vendorID, Equal <Current <APInvoice.vendorID> >,
And <POOrder.vendorLocationID, Equal <Current <APInvoice.vendorLocationID> > > > >();
}
foreach (PXResult <POOrder, POLine> result in cmd.View.SelectMultiBound(new object[] { doc }))
{
POOrder order = result;
APTran aPTran = new APTran
{
PONbr = order.OrderNbr,
POOrderType = order.OrderType
};
if (usedOrder.TryGetValue(aPTran, out count))
{
usedOrder.Remove(aPTran);
if (count < result.RowCount)
{
yield return(order);
}
}
else
{
yield return(order);
}
}
foreach (APTran deletedTran in usedOrder.Where(_ => _.Value < 0).Select(_ => _.Key))
{
yield return(PXSelect <
POOrder,
Where <POOrder.orderNbr, Equal <Required <APTran.pONbr> >,
And <POOrder.orderType, Equal <Required <APTran.pOOrderType> > > > >
.SelectSingleBound(Base, new object[] { }, deletedTran.PONbr, deletedTran.POOrderType)
.RowCast <POOrder>()
.First());
}
}