EOM Refactor

Indexes we might want to add:

tbEndOfMonthTemplate.EOMTemplateName - Only 2 rows for now, not sure if\when we would add any others
tbExternalApi.Description - Only 12 rows, and again, not likely to add many more
tbEndOfMonthSection.EOMSectionKey - Only 13 rows and also not likely to add many more

All 3 of these are string columns and the tables are really small. Our queries are currently doing full table scans which is not ideal.

EndOfMonthTemplateRepository.GetEntityTemplate
Is duplicating some effort but initial attempts were unable to get anything that was any faster.

The best alternative we were able to come up with was:

DECLARE @TemplateId bigint
SELECT TOP 1 @TemplateId = template.eomtemplateid
FROM tbendofmonthtemplate template WITH(NOLOCK)
LEFT JOIN tbentityzone entityZone WITH(NOLOCK)
ON entityZone.entityid = @p0
AND entityZone.zoneid = template.zoneid
WHERE entityZone.zoneid IS NOT NULL OR template.eomtemplatename = 'Default'
ORDER BY entityZone.zoneid DESC – Be sure if there is a Zone-specific template, it is at the top

SELECT
map.eomtemplateid as EndOfMonthTemplateId,
section.EndOfMonthSectionId as Id,
section.EOMSectionKey,
section.EOMNameTranslationToken,
section.EOMNotPassedTranslationToken,
section.EOMPassedTranslationToken,
section.EOMExternalDataWebService,
section.EOMMMSPageURL,
section.EOMMobilePageLinkURL
FROM tbendofmonthtemplatesectionmap map WITH(NOLOCK)
JOIN tbendofmonthsection section WITH(NOLOCK)
ON section.endofmonthsectionid = map.eomsectionid
WHERE map.eomtemplateid = @TemplateId
ORDER BY map.EOMSectionOrder ASC

ZoneType name constants
It would be good if we set up some sort of set of constant zone type names for easy lookup in the code, that way we can just say GetByZoneType(COUNTRY_ZONETYPE) or GetByZoneType(ORDERING_ZONETYPE), not being tied directly to zone type names.

BankingCloseQueryService.GetUnFinalizedDaysByDateRange
We are making two db calls and then filtering the results. This could be simplified to just one DB call, since the only thing that is being done client side is some datetime comparisons.

*EndOfMonthStatusCheckRepository UpdateCommentsStatusCheck and
UpdateRefillsStatusCheck*
We could use a nullable boolean as a complete parameter and set the value of the complete column = ISNULL(:complete, complete), that would eliminate having two copies of the same update code for the complete/not complete logic.

IX_StockCountLocationIDItemID
If we included StockCount in IX_StockCountLocationIDItemID, we would improve the performance of qryMMSInventory_GetItemCountSummary tremendously, since half of the estimated cost of this query is in the lookup of StockCount from the tbTransactionStockCountLocationDetail table. This should also improve the query in qryMMSInventory_GetItemCountByGroup, but to a lesser extent, for the same reason.

EndOfMonthStatusCheckCommandService
There are a couple of places where we are writing to the Audit (UpdateExpense and DeleteExpense) that have the same several lines of logic, we should pull those out into a separate method for easier readability and maintainability.