// LedgerReportsByRentable looks for every rental agreement that overlaps [d1,d2) for the supplied unit.
// It then processes each rental agreement over the specified time range.
func LedgerReportsByRentable(xprop *XBusiness, xu *XUnit, d1, d2 *time.Time) {
rows, err := App.prepstmt.getUnitRentalAgreements.Query(xu.R.UNITID, d1, d2)
rlib.Errcheck(err)
defer rows.Close()
// billing := make([]*RentalList, 0)
var billing []*RentalList
for rows.Next() {
var ra RentalAgreement
rlib.Errcheck(rows.Scan(&ra.RAID, &ra.RATID, &ra.BID, &ra.RID, &ra.UNITID, &ra.PID, &ra.PrimaryTenant, &ra.RentalStart, &ra.RentalStop, &ra.Renewal, &ra.SpecialProvisions, &ra.LastModTime, &ra.LastModBy))
var xp XPerson
GetPayor(ra.PID, &xp.pay)
xp.psp.PRSPID = 0 // force load
xp.tnt.TID = 0 // force load
xp.trn.TCID = 0 // force load
GetXPerson(xp.pay.TCID, &xp)
var b RentalList
b.ra = &ra
b.xp = &xp
billing = append(billing, &b)
}
switch xu.R.RTID {
case RTRESIDENCE:
UnitReport(xprop, xu, &billing, d1, d2)
default:
RentableReport(xprop, xu, &billing, d1, d2)
}
rlib.Errcheck(rows.Err())
}
// GetReceiptAllocations loads all receipt allocations associated with the supplied receipt id into
// the RA array within a Receipt structure
func GetReceiptAllocations(rcptid int, r *Receipt) {
rows, err := App.prepstmt.getReceiptAllocations.Query(rcptid)
rlib.Errcheck(err)
defer rows.Close()
r.RA = make([]ReceiptAllocation, 0)
for rows.Next() {
var a ReceiptAllocation
rlib.Errcheck(rows.Scan(&a.RCPTID, &a.Amount, &a.ASMID, &a.AcctRule))
r.RA = append(r.RA, a)
}
}
// GetJournalAllocations loads all Journal allocations associated with the supplied Journal id into
// the RA array within a Journal structure
func GetJournalAllocations(jid int, j *Journal) {
rows, err := App.prepstmt.getJournalAllocations.Query(jid)
rlib.Errcheck(err)
defer rows.Close()
j.JA = make([]JournalAllocation, 0)
for rows.Next() {
var a JournalAllocation
rlib.Errcheck(rows.Scan(&a.JID, &a.Amount, &a.ASMID, &a.AcctRule))
j.JA = append(j.JA, a)
}
}
// GetAllRentableAssessments for the supplied RID and date range
func GetAllRentableAssessments(RID int, d1, d2 *time.Time) []Assessment {
rows, err := App.prepstmt.getAllRentableAssessments.Query(RID, d1, d2)
rlib.Errcheck(err)
defer rows.Close()
var t []Assessment
t = make([]Assessment, 0)
for i := 0; rows.Next(); i++ {
var a Assessment
rlib.Errcheck(rows.Scan(&a.ASMID, &a.BID, &a.RID, &a.UNITID, &a.ASMTID, &a.RAID, &a.Amount, &a.Start, &a.Stop, &a.Frequency, &a.ProrationMethod, &a.AcctRule, &a.LastModTime, &a.LastModBy))
t = append(t, a)
}
return t
}
// JournalReportText generates a textual journal report for the supplied business and time range
func JournalReportText(xprop *XBusiness, d1, d2 *time.Time) {
printJournalHeader(xprop, d1, d2)
rows, err := App.prepstmt.getAllJournalsInRange.Query(xprop.P.BID, d1, d2)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var j Journal
rlib.Errcheck(rows.Scan(&j.JID, &j.BID, &j.RAID, &j.Dt, &j.Amount, &j.Type, &j.ID))
GetJournalAllocations(j.JID, &j)
textReportJournalEntry(&j, d1, d2)
}
rlib.Errcheck(rows.Err())
}
// GetJournalMarkers loads the last n journal markers
func GetJournalMarkers(n int) []JournalMarker {
rows, err := App.prepstmt.getJournalMarkers.Query(n)
rlib.Errcheck(err)
defer rows.Close()
var t []JournalMarker
t = make([]JournalMarker, 0)
for rows.Next() {
var r JournalMarker
rlib.Errcheck(rows.Scan(&r.JMID, &r.BID, &r.State, &r.DtStart, &r.DtStop))
t = append(t, r)
}
return t
}
// GetSecurityDepositAssessments returns all the security deposit assessments for the supplied unit
func GetSecurityDepositAssessments(unitid int) []Assessment {
var m []Assessment
rows, err := App.prepstmt.getSecurityDepositAssessment.Query(unitid)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a Assessment
rlib.Errcheck(rows.Scan(&a.ASMID, &a.BID, &a.RID, &a.UNITID, &a.ASMTID, &a.RAID, &a.Amount, &a.Start, &a.Stop, &a.Frequency, &a.ProrationMethod, &a.AcctRule))
m = append(m, a)
}
rlib.Errcheck(rows.Err())
return m
}
// GetBusinessUnitTypes returns a slice of payment types indexed by the PMTID
func GetBusinessUnitTypes(bid int) map[int]UnitType {
var t map[int]UnitType
t = make(map[int]UnitType, 0)
rows, err := App.prepstmt.getAllBusinessUnitTypes.Query(bid)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a UnitType
rlib.Errcheck(rows.Scan(&a.UTID, &a.BID, &a.Style, &a.Name, &a.SqFt, &a.Frequency, &a.Proration, &a.LastModTime, &a.LastModBy))
t[a.UTID] = a
}
rlib.Errcheck(rows.Err())
return t
}
// GetUnitSpecialties returns a list of specialties associated with the supplied unit
func GetUnitSpecialties(bid, unitid int) []int {
// first, get the specialties for this unit
var m []int
rows, err := App.prepstmt.getUnitSpecialties.Query(bid, unitid)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var uspid int
rlib.Errcheck(rows.Scan(&uspid))
m = append(m, uspid)
}
rlib.Errcheck(rows.Err())
return m
}
// GetReceipts for the supplied business (bid) in date range [d1 - d2)
func GetReceipts(bid int, d1, d2 *time.Time) []Receipt {
rows, err := App.prepstmt.getReceiptsInDateRange.Query(bid, d1, d2)
rlib.Errcheck(err)
defer rows.Close()
var t []Receipt
t = make([]Receipt, 0)
for rows.Next() {
var r Receipt
rlib.Errcheck(rows.Scan(&r.RCPTID, &r.BID, &r.RAID, &r.PMTID, &r.Dt, &r.Amount, &r.AcctRule))
r.RA = make([]ReceiptAllocation, 0)
GetReceiptAllocations(r.RCPTID, &r)
t = append(t, r)
}
return t
}
// GetAssessmentTypes returns a slice of assessment types indexed by the ASMTID
func GetAssessmentTypes() map[int]AssessmentType {
var t map[int]AssessmentType
t = make(map[int]AssessmentType, 0)
rows, err := App.dbrr.Query("SELECT ASMTID,Name,Type,LastModTime,LastModBy FROM assessmenttypes")
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a AssessmentType
rlib.Errcheck(rows.Scan(&a.ASMTID, &a.Name, &a.Type, &a.LastModTime, &a.LastModBy))
t[a.ASMTID] = a
}
rlib.Errcheck(rows.Err())
return t
}
// GetPaymentTypes returns a slice of payment types indexed by the PMTID
func GetPaymentTypes() map[int]PaymentType {
var t map[int]PaymentType
t = make(map[int]PaymentType, 0)
rows, err := App.dbrr.Query("SELECT PMTID,Name,Description,LastModTime,LastModBy FROM paymenttypes")
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a PaymentType
rlib.Errcheck(rows.Scan(&a.PMTID, &a.Name, &a.Description, &a.LastModTime, &a.LastModBy))
t[a.PMTID] = a
}
rlib.Errcheck(rows.Err())
return t
}
// LedgerReportsByBusiness calculates all charges for the specified business that occur in
// the supplied start / stop time range.
func LedgerReportsByBusiness(xprop *XBusiness, d1, d2 *time.Time) {
rows, err := App.prepstmt.getAllRentablesByBusiness.Query(xprop.P.BID)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var xu XUnit
rlib.Errcheck(rows.Scan(&xu.R.RID, &xu.R.LID, &xu.R.RTID, &xu.R.BID, &xu.R.UNITID, &xu.R.Name, &xu.R.Assignment, &xu.R.Report, &xu.R.LastModTime, &xu.R.LastModBy))
if xu.R.UNITID > 0 {
GetXUnit(xu.R.RID, &xu)
LedgerReportsByRentable(xprop, &xu, d1, d2)
} else {
fmt.Printf("Rentable ID %d: name = %s, not a unit\n", xu.R.RID, xu.R.Name)
}
}
rlib.Errcheck(rows.Err())
}
// GetTenant reads a Tenant structure based on the supplied tenant id
func GetTenant(tcid int, t *Tenant) {
rlib.Errcheck(App.prepstmt.getTransactant.QueryRow(tcid).Scan(&t.TID, &t.TCID, &t.Points,
&t.CarMake, &t.CarModel, &t.CarColor, &t.CarYear, &t.LicensePlateState, &t.LicensePlateNumber,
&t.ParkingPermitNumber, &t.AccountRep, &t.DateofBirth, &t.EmergencyContactName, &t.EmergencyContactAddress,
&t.EmergencyContactTelephone, &t.EmergencyAddressEmail, &t.AlternateAddress, &t.ElibigleForFutureOccupancy,
&t.Industry, &t.Source, &t.InvoicingCustomerNumber))
}
// GetBusinessRentableTypes returns a slice of payment types indexed by the PMTID
func GetBusinessRentableTypes(bid int) map[int]RentableType {
var t map[int]RentableType
t = make(map[int]RentableType, 0)
rows, err := App.prepstmt.getAllBusinessRentableTypes.Query(bid)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a RentableType
rlib.Errcheck(rows.Scan(&a.RTID, &a.BID, &a.Name, &a.Frequency, &a.Proration, &a.LastModTime, &a.LastModBy))
a.MR = make([]RentableMarketRate, 0)
GetRentableMarketRates(&a)
t[a.RTID] = a
}
rlib.Errcheck(rows.Err())
return t
}
// GetXBusiness loads the XBusiness struct for the supplied business id.
func GetXBusiness(bid int, xprop *XBusiness) {
if xprop.P.BID == 0 && bid > 0 {
GetBusiness(bid, &xprop.P)
}
xprop.RT = GetBusinessRentableTypes(bid)
xprop.UT = GetBusinessUnitTypes(bid)
xprop.US = make(map[int]UnitSpecialtyType, 0)
rows, err := App.prepstmt.getAllBusinessSpecialtyTypes.Query(bid)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a UnitSpecialtyType
rlib.Errcheck(rows.Scan(&a.USPID, &a.BID, &a.Name, &a.Fee, &a.Description))
xprop.US[a.USPID] = a
}
rlib.Errcheck(rows.Err())
}
// GetUnitMarketRates loads all the MarketRate rent information for this unit into an array
func GetUnitMarketRates(rt *UnitType) {
// now get all the MarketRate rent info...
rows, err := App.prepstmt.getUnitMarketRates.Query(rt.UTID)
rlib.Errcheck(err)
defer rows.Close()
LatestMRDTStart := time.Date(0, 0, 0, 0, 0, 0, 0, time.UTC)
for rows.Next() {
var a UnitMarketRate
rlib.Errcheck(rows.Scan(&a.UTID, &a.MarketRate, &a.DtStart, &a.DtStop))
if a.DtStart.After(LatestMRDTStart) {
LatestMRDTStart = a.DtStart
rt.MRCurrent = a.MarketRate
}
rt.MR = append(rt.MR, a)
}
rlib.Errcheck(rows.Err())
}
func loadDefaultCashAccts() {
App.DefaultCash = make(map[int]LedgerMarker, 0)
s := "SELECT BID,Address,Address2,City,State,PostalCode,Country,Phone,Name,DefaultOccupancyType,ParkingPermitInUse,LastModTime,LastModBy from business"
rows, err := App.dbrr.Query(s)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var xprop XBusiness
rlib.Errcheck(rows.Scan(&xprop.P.BID, &xprop.P.Address, &xprop.P.Address2, &xprop.P.City, &xprop.P.State,
&xprop.P.PostalCode, &xprop.P.Country, &xprop.P.Phone, &xprop.P.Name, &xprop.P.DefaultOccupancyType,
&xprop.P.ParkingPermitInUse, &xprop.P.LastModTime, &xprop.P.LastModBy))
// All we really needed was the BID...
App.DefaultCash[xprop.P.BID] = GetDefaultCashLedgerMarker(xprop.P.BID)
if App.DefaultCash[xprop.P.BID].LMID == 0 {
fmt.Printf("No default cash account was found for business %d, %s\n", xprop.P.BID, xprop.P.Name)
}
}
}
// return a slice of assessments for the unit associated with this
// occupancy agreement.
func unitAssessments(ra *RentalAgreement, d1, d2 *time.Time) float32 {
rows, err := App.prepstmt.getUnitAssessments.Query(ra.UNITID, d1, d2)
rlib.Errcheck(err)
defer rows.Close()
var tot = float32(0.0)
var a Assessment
ap := &a
for rows.Next() {
rlib.Errcheck(rows.Scan(&a.ASMID, &a.BID, &a.RID, &a.UNITID, &a.ASMTID, &a.RAID, &a.Amount, &a.Start, &a.Stop, &a.Frequency, &a.ProrationMethod, &a.LastModTime, &a.LastModBy))
if a.Frequency >= rlib.RECURSECONDLY && a.Frequency <= rlib.RECURHOURLY && a.ASMTID != SECURITYDEPOSIT {
// TBD
fmt.Printf("Unhandled assessment recurrence type: %d\n", a.Frequency)
} else {
dl := ap.GetRecurrences(d1, d2)
for i := 0; i < len(dl); i++ {
printAssessment(dl[i], ap, REPORTJUSTIFYRIGHT)
tot += ap.Amount
}
}
}
return tot
}
// ReportAll generates the supplied report for all properties
func ReportAll(d1, d2 time.Time, report int) {
s := "SELECT BID,Address,Address2,City,State,PostalCode,Country,Phone,Name,DefaultOccupancyType,ParkingPermitInUse,LastModTime,LastModBy from business"
rows, err := App.dbrr.Query(s)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var xprop XBusiness
rlib.Errcheck(rows.Scan(&xprop.P.BID, &xprop.P.Address, &xprop.P.Address2, &xprop.P.City, &xprop.P.State,
&xprop.P.PostalCode, &xprop.P.Country, &xprop.P.Phone, &xprop.P.Name, &xprop.P.DefaultOccupancyType,
&xprop.P.ParkingPermitInUse, &xprop.P.LastModTime, &xprop.P.LastModBy))
GetXBusiness(xprop.P.BID, &xprop)
// fmt.Printf("Business: %s (%d)\n", xprop.P.Name, xprop.P.BID)
switch report {
case 1:
JournalReportText(&xprop, &d1, &d2)
case 2:
LedgerReportsByBusiness(&xprop, &d1, &d2)
default:
fmt.Printf("Generating Journal Records for %s through %s\n", d1.Format(RRDATEFMT), d2.AddDate(0, 0, -1).Format(RRDATEFMT))
GenerateJournalRecords(&xprop, &d1, &d2)
}
}
}
// RemoveJournalEntries clears out the records in the supplied range provided the range is not closed by a journalmarker
func RemoveJournalEntries(xprop *XBusiness, d1, d2 *time.Time) error {
// Remove the journal entries and the journalallocation entries
rows, err := App.prepstmt.getAllJournalsInRange.Query(xprop.P.BID, d1, d2)
if err != nil {
return err
}
defer rows.Close()
for rows.Next() {
var j Journal
rlib.Errcheck(rows.Scan(&j.JID, &j.BID, &j.RAID, &j.Dt, &j.Amount, &j.Type, &j.ID))
deleteJournalAllocations(j.JID)
deleteJournalEntry(j.JID)
}
// only delete the marker if it is in this time range and if it is not the origin marker
jm := GetLastJournalMarker()
if jm.State == MARKERSTATEOPEN {
deleteJournalMarker(jm.JMID)
}
return err
}
// GetProspect reads a Prospect structure based on the supplied transactant id
func GetProspect(prspid int, p *Prospect) {
rlib.Errcheck(App.prepstmt.getProspect.QueryRow(prspid).Scan(&p.PRSPID, &p.TCID, &p.ApplicationFee))
}
// GetBusiness loads the Business struct for the supplied business id
func GetBusiness(bid int, p *Business) {
rlib.Errcheck(App.prepstmt.getPayor.QueryRow(bid).Scan(&p.BID, &p.Address, &p.Address2, &p.City,
&p.State, &p.PostalCode, &p.Country, &p.Phone, &p.Name, &p.DefaultOccupancyType, &p.ParkingPermitInUse, &p.LastModTime, &p.LastModBy))
}
// GetPayor reads a Payor structure based on the supplied transactant id
func GetPayor(pid int, p *Payor) {
rlib.Errcheck(App.prepstmt.getPayor.QueryRow(pid).Scan(
&p.PID, &p.TCID, &p.CreditLimit, &p.EmployerName, &p.EmployerStreetAddress, &p.EmployerCity,
&p.EmployerState, &p.EmployerZipcode, &p.Occupation, &p.LastModTime, &p.LastModBy))
}
// GetTransactant reads a Transactant structure based on the supplied transactant id
func GetTransactant(tid int, t *Transactant) {
rlib.Errcheck(App.prepstmt.getTransactant.QueryRow(tid).Scan(
&t.TCID, &t.TID, &t.PID, &t.PRSPID, &t.FirstName, &t.MiddleName, &t.LastName, &t.PrimaryEmail,
&t.SecondaryEmail, &t.WorkPhone, &t.CellPhone, &t.Address, &t.Address2, &t.City, &t.State,
&t.PostalCode, &t.Country, &t.LastModTime, &t.LastModBy))
}
// GetUnit reads a Unit structure based on the supplied unit id
func GetUnit(uid int, u *Unit) {
rlib.Errcheck(App.prepstmt.getUnit.QueryRow(uid).Scan(
&u.UNITID, &u.BLDGID, &u.UTID, &u.RID, &u.AVAILID,
&u.LastModTime, &u.LastModBy))
}
// GetRentable reads and returns a Rentable structure based on the supplied rentable id
func GetRentable(rid int) Rentable {
var r Rentable
rlib.Errcheck(App.prepstmt.getRentable.QueryRow(rid).Scan(&r.RID, &r.LID, &r.RTID, &r.BID, &r.UNITID, &r.Name, &r.Assignment, &r.Report, &r.DefaultOccType, &r.OccType, &r.LastModTime, &r.LastModBy))
return r
}
// GetRentableByID reads a Rentable structure based on the supplied rentable id
func GetRentableByID(rid int, r *Rentable) {
rlib.Errcheck(App.prepstmt.getRentable.QueryRow(rid).Scan(&r.RID, &r.LID, &r.RTID, &r.BID, &r.UNITID, &r.Name, &r.Assignment, &r.Report, &r.DefaultOccType, &r.OccType, &r.LastModTime, &r.LastModBy))
}
// GenerateJournalRecords creates journal records for assessments and receipts over the supplied time range.
func GenerateJournalRecords(xprop *XBusiness, d1, d2 *time.Time) {
err := RemoveJournalEntries(xprop, d1, d2)
if err != nil {
ulog("Could not remove existin Journal Entries from %s to %s\n", d1.Format(RRDATEFMT), d2.Format(RRDATEFMT))
return
}
//===========================================================
// PROCESS ASSESSMSENTS
//===========================================================
rows, err := App.prepstmt.getAllAssessmentsByBusiness.Query(xprop.P.BID, d2, d1)
rlib.Errcheck(err)
defer rows.Close()
for rows.Next() {
var a Assessment
ap := &a
rlib.Errcheck(rows.Scan(&a.ASMID, &a.BID, &a.RID, &a.UNITID, &a.ASMTID, &a.RAID, &a.Amount, &a.Start, &a.Stop, &a.Frequency, &a.ProrationMethod, &a.AcctRule, &a.LastModTime, &a.LastModBy))
if a.Frequency >= rlib.RECURSECONDLY && a.Frequency <= rlib.RECURHOURLY {
// TBD
fmt.Printf("Unhandled assessment recurrence type: %d\n", a.Frequency)
} else {
dl := ap.GetRecurrences(d1, d2)
// fmt.Printf("type = %d, %s - %s len(dl) = %d\n", a.ASMTID, a.Start.Format(RRDATEFMT), a.Stop.Format(RRDATEFMT), len(dl))
for i := 0; i < len(dl); i++ {
journalAssessment(dl[i], &a, d1, d2)
}
}
}
rlib.Errcheck(rows.Err())
//===========================================================
// PROCESS RECEIPTS
//===========================================================
r := GetReceipts(xprop.P.BID, d1, d2)
for i := 0; i < len(r); i++ {
rntagr, _ := GetRentalAgreement(r[i].RAID)
var j Journal
j.BID = rntagr.BID
j.Amount = r[i].Amount
j.Dt = r[i].Dt
j.Type = JNLTYPERCPT
j.ID = r[i].RCPTID
j.RAID = r[i].RAID
jid, err := InsertJournalEntry(&j)
if err != nil {
ulog("Error inserting journal entry: %v\n", err)
}
if jid > 0 {
// now add the journal allocation records...
for j := 0; j < len(r[i].RA); j++ {
var ja JournalAllocation
ja.JID = jid
ja.Amount = r[i].RA[j].Amount
ja.ASMID = r[i].RA[j].ASMID
ja.AcctRule = r[i].RA[j].AcctRule
InsertJournalAllocationEntry(&ja)
}
}
}
//===========================================================
// ADD JOURNAL MARKER
//===========================================================
var jm JournalMarker
jm.BID = xprop.P.BID
jm.State = MARKERSTATEOPEN
jm.DtStart = *d1
jm.DtStop = (*d2).AddDate(0, 0, -1)
InsertJournalMarker(&jm)
}
// GetReceipt returns a receipt structure for the supplied RCPTID
func GetReceipt(rcptid int) Receipt {
var r Receipt
rlib.Errcheck(App.prepstmt.getReceipt.QueryRow(rcptid).Scan(&r.RCPTID, &r.BID, &r.RAID, &r.PMTID, &r.Dt, &r.Amount, &r.AcctRule))
GetReceiptAllocations(rcptid, &r)
return r
}