Use FinalTenMan;

/* new tenants for January */
INSERT INTO Tenant
VALUES ('BK4U', 'Burger King', '236 Russ Ave', 'Waynesville', 
'NC', '28786', '828-555-1234', 'Bob Barker', '2005-01-01', 
'2020-01-01', 2500000, 0.04);

/* rent costs recalculation for January */
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('BK4U', 'R', 24000, '2005-01-01', '2005-12-31');
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('BK4U', 'T', 2200, '2005-01-01', '2005-12-31');
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('BK4U', 'I', 1500, '2005-01-01', '2005-12-31');

/* create temp table to calculate percentage rent */
CREATE TEMPORARY TABLE tempRC1
SELECT YEAR(SalesDate) AS SalesYear, TenantNum, SUM(Amount) AS TotalSales
FROM TenantSales
GROUP BY YEAR(SalesDate), TenantNum;

CREATE TEMPORARY TABLE tempRC2
SELECT tempRC1.TenantNum, SalesYear, 
((tempRC1.TotalSales - Tenant.BaseSales) * PercentRate) AS PRent
FROM Tenant, tempRC1
WHERE  Tenant.TenantNum = tempRC1.TenantNum
AND tempRC1.TotalSales - Tenant.BaseSales > 0;

UPDATE RentCosts, tempRC2
SET Amount = tempRC2.PRent
WHERE RentCosts.TenantNum = tempRC2.TenantNum
AND RentCosts.Type = 'P' 
AND Year(StartDate) = SalesYear;

UPDATE RentCosts
SET Amount = 0
WHERE Amount IS NULL;

/* AR Invoices for BK4U for Jan 2005 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'BK4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'BK4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'BK4U','2005-01-01');

/* AR Invoices for NV4U for Jan 2005 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'NV4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'NV4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'NV4U','2005-01-01');

/* AR Invoices DP4U Jan 2005 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'DP4U', '2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'DP4U', '2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'DP4U', '2005-01-01');

/* AR Invoices AE4U Jan 2005 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'AE4U', '2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'AE4U', '2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'AE4U', '2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('P', 'AE4U', '2005-01-01');

/* AR Invoices TG4U Jan 2005 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'TG4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'TG4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'TG4U','2005-01-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('P', 'TG4U','2005-01-01');

/* create a temp table to calculate amounts and then 
 * put in AccountsReceivable table
 */
CREATE TEMPORARY TABLE tempAR2
SELECT TenantNum, Type, EndDate, (RentCosts.Amount / 12) AS MonthAmount 
FROM  RentCosts;
UPDATE AccountsReceivable, tempAR2
SET AccountsReceivable.Amount = tempAR2.MonthAmount
WHERE AccountsReceivable.tenantNum = tempAR2.tenantNum 
AND AccountsReceivable.Type = tempAR2.Type 
AND tempAR2.EndDate >= AccountsReceivable.ARDate
AND AccountsReceivable.Amount IS NULL;

UPDATE AccountsReceivable
SET Amount = 0
WHERE Amount IS NULL;

/* populate invoiceNum */
Update AccountsReceivable
SET InvoiceNum = CONCAT(Type, YEAR(ARDate), '/', MONTH(ARDate));

/* ARHistory Dec 2004 */
INSERT INTO ARHistory
 SELECT *  FROM AccountsReceivable 
 WHERE YEAR(ARDate) = 2004 
 AND MONTH(ARDate) = 12;