Use FinalTenMan;

/* new tenants for December */
INSERT INTO Tenant
VALUES ('NV4U', 'Nomaic Vibes Record Studio', '27 Bronzewing Ln', 'Raleigh', 'NC', 
'28786', '828-454-0773', 'Count Cliver', '2004-12-01', '2031-11-31', 650000, 0.02);

/* rent costs recalculated for December */
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('NV4U','R', 23200,'2005-12-01', '2005-11-30');
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('NV4U','T', 4000, '2005-12-01', '2005-11-30');
INSERT INTO RentCosts (TenantNum, Type, Amount, StartDate, EndDate)
VALUES ('NV4U','I', 2000, '2005-12-01', '2005-11-30');

/* 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 DP4U Dec 2004 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'DP4U', '2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'DP4U', '2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'DP4U', '2004-12-01');

/* AR Invoices NV4U Dec 2004 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'NV4U','2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'NV4U','2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'NV4U','2004-12-01');

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

/* AR Invoices TG4U Dec 2004 */
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('R', 'TG4U','2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('T', 'TG4U','2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('I', 'TG4U','2004-12-01');
INSERT INTO AccountsReceivable (Type, TenantNum, ARDate)
VALUES  ('P', 'TG4U','2004-12-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 Nov 2004 */
INSERT INTO ARHistory
 SELECT *  FROM AccountsReceivable 
 WHERE YEAR(ARDate) = 2004 
 AND MONTH(ARDate) = 11;