Film distribution manager (2008-2012)
- Reports
- Spain invoice for Ecuador

Film distribution manager (2008-2012)

Note: This article is about Desktop FDM. But in 2009 we decided to move to FDM online. The FDM online is our current growing project. And it's in use everyday from 3 countries.

This is a big project made in MS C# .NET 2008 (Silverlight, mySQL). The work with it is continued.

There are a set of non-modal forms with the simple interface.

The functions of the program:

The database interface to insert and edit of data in tables
The reports are exported into MS Excel
The invoices could be printed, e-mailed or saved into PDF also with using of MS Excel (the program use MS Excel via OLE-interface).

I continued this work started by another programmer. And here I described all new forms/reports I made.

Reports

There are 4 reports: cost report, inventory report, input tax report, and output tax report.

Cost report

Input data

Name	Description
Film	The film, if not specified – it fetch by all films
Supplier	same
Category	The category from the table “Cost_category”
Month	The month
Year	The year

Output data

It fetch all records from the cost table into the MS Excel spreadsheet.

Column	Description
TITLE	The title of the film, fetched from the table “Films”
CATEGORY	The category from the table “Cost_category”
SUPPLIER	The name of supplier from the table “Supplier”
COST NUM	Studio name + cost_id. The studio is based on film_id. For example, 'BVI0032'.
INV. DATE	The date of invoice from table “cost”
INV. NUM.	The name of invoice from table “cost”
Xrate B$D	The exchange rate for country “Barbados”, fetched from the table “exchange_rates” on today. It's empty if B$D is empty.
Xrate TTD	The exchange rate for country “Trinidad and Tobago”, fetched from the table “exchange_rates” on today. It's empty if TDD is empty.
B$D	The amount in BB if the currency_id was BB, otherwise – empty
TTD	The amount in TT if the currency_id was TT, otherwise – empty
US$	The amount in USD if the currency_id was USD, otherwise – empty
COST US$	The calculated amount in USD. It's calculated from the amount using its xrate
TAX	The TAX in USD, calculated from the tax. Empty if tax = 0
FM	The month
FY	The year

Inventory report

Input data

Name	Description
Studio	The studio from the table “Studios”
Film	The film
Year	The year

Output data

It fetch all records from the print_location (based on the “inventory” table) into the MS Excel spreadsheet.

Column	Description
Print #	The “print_diez” of the copy
Origin	The country where this film from, fetched from “Inventory” table for the print (film_id + print_diez)
Title	The title of the film, fetched from the table “Films”
AWB	The AWB, fetched from “Inventory” table for the print (film_id + print_diez)
Ser #	The serial number, fetched from “Inventory” table for the print (film_id + print_diez)
CodeName	The code_name, fetched from “Inventory” table for the print (film_id + print_diez)
Ship_date	The invoicedate from “Print_location” table
Location	The name of “country_id” from “Print_location” table
Status	The status of print from “Print_location” table
Week	The week from “Print_location” table
Invoice #	The number of invoice from table “Print_location” if it was filled, otherwise ”!”

In fact this report is generated from two tables: “Print_location” and “Inventory”.
Each row = one ore more rows from “Inventory” based on input data, and all missed fields are filled from the table “Print_location”. If the table “Print_location” does not contain some data – the cells will be empty, you can see it on the picture, the row [“Bolt”, “C”] have not the appropriate records in the table “Print_location”, so the columns “Ship date” and “Location” are empty.

So here you can see how these tables work together. Each print copy should be entered into the inventory table. And later it should be entered how each print copy was traveled between cinemas in the table “Print_location”. If the user forgot to fill all locations – in the Inventory report there will be one row about the print copy.

Input Tax report

Currently we have 2 tax reports: Input Tax Report and Output Tax Report. Probably its quantity will be increased later. So we use the universal tax report form where use could select the report type.

Input Tax Report

Input data

Name	Description
Country	The country. When user selects which country, then all costs or revenues from that country will appear on tax report. User should select the country. Otherwise Barbados will be used.
Studio1	The studio from the table “Studios”
Studio2	The studio from the table “Studios”
Month1	The month from
Month2	The month to
Year	The year

It could work from one or two or (all) studios. Monthes from Month1 to Month2, or only Month1 if Month2 is empty, or any month if both Month1 and Month2 are empty. The same for Year.

So when all params are empty – it'll generate the full report about everything.

Output data

Now it fetch all records from the “cost” table into the MS Excel spreadsheet.

Column	Description
Cost#	The number of Cost. I don't sure where I should take it in future. But I can use autocount.
Supplier	The supplier of the ? (I don't know)
Category	The category of the tax?
Cost LC	Ok. The Cost in the local currency
TAX	Ok, the Tax in the local currency

Output Tax Report

Output Tax Report fetch data from the “Box Offices”, “Invoice”, and “Estimates” tables.

Input data

Name	Description
Country	The country. When user selects which country, then all costs or revenues from that country will appear on tax report.
Month1	The month from
Month2	The month to
Year	The year

It could work with monthes from Month1 to Month2, or only Month1 if Month2 is empty, or any month if both Month1 and Month2 are empty. The same for Year.

So when all params are empty – it'll generate the full report about everything.

Output data

Now it fetch all records from the “box_offices” table into the MS Excel spreadsheet.
Some fields are fetched also from the tables “Estimates” and “Invoices”. See column descriptions.

Column	Description
Invoice#	The number of the Invoice. It's fetched from the “Invoices” table.
Cinema	The cinema
Film	The title of the film. Fetched from the “films” table by its “film_id”.
Royalti	The value of “current_rental” from the table “Estimates” based on the “film_id” for this “Invoice#”.
TAX	The “Roaylti” * 0.15 (a formula is used)

Spain invoice for Ecuador

Prerequestes

Another form of the invoice is used for Ecuador. This form is differ. Also it include tax and royalti value for every date.

Changes in the database

For first view I think it's not needed to add region field into the country table because it could make the program too complex.

I think we should add into country table the next values:

Country table (example):

Country	Currency ID
Ecuador	USD
Ecuador (Region 1)	USD
Ecuador (Region 2)	USD
Ecuador (Region 3)	USD
Ecuador (Region 4)	USD
Ecuador (Region 5)	USD

Tax rate table (example):

Country	Tax rate
Ecuador	0.15
Ecuador (Region 1)	0.15
Ecuador (Region 2)	0.9
Ecuador (Region 3)	0.14
Ecuador (Region 4)	0.12
Ecuador (Region 5)	0.14

These data will be used in the invoice form. What country I should use here – billing company, or cinema, or supplier. I take tax rate from country_id.

Online FDM application

I continue this work. I changed the desktop application into online ASP.Net website. That's a very good progress for this work I think so. Continue read about online FDM application here