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
