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

Edit box office form

fdm.txt · Last modified: 2012/01/25 13:24 by yetidi
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki