public ActionResult Index(int period1 = 1, int period2 = 12)
{
DataSet invoiceTable = new DataSet();
DataSet customerTable = new DataSet();
DataSet FinancialTable = new DataSet();
DataSet companyTable = new DataSet();
FinancialModel FinancialDashboardModel = new FinancialModel();
string connectionString = FirstREST.SqlConnection.GetConnectionString();
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select * From dbo.Company", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(companyTable, "company");
SaftFileDateModel temp = new SaftFileDateModel();
temp.startDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("StartDate");
temp.endDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("EndDate");
FinancialDashboardModel.SaftInfo = temp;
}
}
}
string monthQuery = "";
if (period1 == period2)
{
monthQuery = " Month=" + period1;
}
else if (period1 < period2)
{
monthQuery = " (Month >=" + period1 + "AND Month <=" + period2 + ")";
}
else if (period1 > period2 && period2 < FinancialDashboardModel.SaftInfo.startDate.Month)
{
monthQuery = " (Month >=" + period1 + "AND Month <=12) OR (Month >=1 AND Month <=" + period2 + ")";
}
else
{
monthQuery = " Month LIKE '%%'";
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select Year, Month, AccountID, accountDescription, Amount, IsCredit From dbo.MonthlyAccountSums INNER JOIN dbo.Account on MonthlyAccountSums.AccountID=Account.id WHERE " + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "Account");
foreach (DataRow row in invoiceTable.Tables["Account"].Rows)
{
AccountModel tempAccount = new AccountModel();
tempAccount.accountId = row.Field <Int64>("AccountId");
tempAccount.accountDescription = row.Field <string>("accountDescription");
tempAccount.amount = row.Field <Int64>("Amount");
FinancialDashboardModel.CompanyAccounts.Add(tempAccount);
}
}
}
using (SqlCommand command = new SqlCommand("Select * from dbo.Journal", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(customerTable, "Journal");
foreach (DataRow row in customerTable.Tables["Journal"].Rows)
{
JournalModel tempJournal = new JournalModel();
tempJournal.journalID = row.Field <string>("JournalID");
tempJournal.journalDescription = row.Field <string>("Description");
tempJournal.journalTotalCredit = row.Field <double>("TotalCredit");
tempJournal.journalTotalDebit = row.Field <double>("TotalDebit");
FinancialDashboardModel.CompanyJournals.Add(tempJournal);
}
}
}
ProfitsAndLossesModel tempModel = new ProfitsAndLossesModel();
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '71%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "sales");
tempModel.sales = 0;
foreach (DataRow row in FinancialTable.Tables["sales"].Rows)
{
tempModel.sales += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '72%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "services");
tempModel.services = 0;
foreach (DataRow row in FinancialTable.Tables["services"].Rows)
{
tempModel.services += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '61%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "salesExpenses");
tempModel.salesExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["salesExpenses"].Rows)
{
tempModel.salesExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '62%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "administrativeExpenses");
tempModel.administrativeExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["administrativeExpenses"].Rows)
{
tempModel.administrativeExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '63%' OR AccountId LIKE '64%' OR AccountId LIKE '65%' OR AccountId LIKE '66%' OR AccountId LIKE '67%' or AccountId LIKE '68%' or AccountId LIKE '69%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "otherExpenses");
tempModel.otherExpenses = 0;
foreach (DataRow row in FinancialTable.Tables["otherExpenses"].Rows)
{
tempModel.otherExpenses += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '73%' OR AccountId LIKE '74%' OR AccountId LIKE '75%' OR AccountId LIKE '76%' OR AccountId LIKE '77%' or AccountId LIKE '78%' or AccountId LIKE '79%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "otherIncomes");
tempModel.otherIncomes = 0;
foreach (DataRow row in FinancialTable.Tables["otherIncomes"].Rows)
{
tempModel.otherIncomes += row.Field <Int64>("Amount");
}
}
}
tempModel.totalSalesAndRevenue = tempModel.sales + tempModel.services + tempModel.otherIncomes;
tempModel.totalOperatingCosts = tempModel.salesExpenses + tempModel.administrativeExpenses + tempModel.otherExpenses;
tempModel.netIncome = tempModel.totalSalesAndRevenue - tempModel.totalOperatingCosts;
FinancialDashboardModel.ProfitsAndLosses.Add(tempModel);
Int64 revenue = 0;
Int64 operatingExpensesWithoutAmortizations = 0;
Int64 amortizationAndDepreciation = 0;
Int64 currentLiabilities = 0;
Int64 inventories = 0;
Int64 accountsReceivable = 0;
Int64 liquidAssets = 0;
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '22%' OR AccountId LIKE '23%' OR AccountId LIKE '24%' OR AccountId LIKE '25%' OR AccountId LIKE '26%' OR AccountId LIKE '27%' OR AccountId LIKE '28%' OR AccountId LIKE '29%' OR AccountId LIKE '6%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "currentLiabilities");
foreach (DataRow row in FinancialTable.Tables["currentLiabilities"].Rows)
{
currentLiabilities += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '3%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "inventories");
foreach (DataRow row in FinancialTable.Tables["inventories"].Rows)
{
inventories += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '21%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "accountsReceivable");
foreach (DataRow row in FinancialTable.Tables["accountsReceivable"].Rows)
{
accountsReceivable += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '1%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "liquidAssets");
foreach (DataRow row in FinancialTable.Tables["liquidAssets"].Rows)
{
liquidAssets += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '7%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "revenue");
foreach (DataRow row in FinancialTable.Tables["revenue"].Rows)
{
revenue += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '61%' OR AccountId LIKE '62%' OR AccountId LIKE '63%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "operatingExpensesWithoutAmortizations");
foreach (DataRow row in FinancialTable.Tables["operatingExpensesWithoutAmortizations"].Rows)
{
operatingExpensesWithoutAmortizations += row.Field <Int64>("Amount");
}
}
}
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.MonthlyAccountSums WHERE AccountId LIKE '64%' OR AccountId LIKE '65%' OR AccountId LIKE '66%' OR AccountId LIKE '67%' AND" + monthQuery, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(FinancialTable, "amortizationAndDepreciation");
foreach (DataRow row in FinancialTable.Tables["amortizationAndDepreciation"].Rows)
{
amortizationAndDepreciation += row.Field <Int64>("Amount");
}
}
}
FinancialDashboardModel.CompanyIndicators.EBIT = revenue - operatingExpensesWithoutAmortizations + amortizationAndDepreciation;
FinancialDashboardModel.CompanyIndicators.EBITDA = revenue - operatingExpensesWithoutAmortizations;
FinancialDashboardModel.CompanyIndicators.quickRatio = (liquidAssets + accountsReceivable + inventories + revenue) / (accountsReceivable);
FinancialDashboardModel.CompanyIndicators.currentRatio = (liquidAssets + accountsReceivable + revenue) / (accountsReceivable);
}
return(View(FinancialDashboardModel));
}
// GET: /sales/period1/period2
public ActionResult Index(int period1 = 1, int period2 = 12)
{
DataSet invoiceTable = new DataSet();
DataSet companyTable = new DataSet();
DataSet customerTable = new DataSet();
SalesModel SalesDashboardModel = new SalesModel();
string connectionString = FirstREST.SqlConnection.GetConnectionString();
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
var query = " ";
if (period1 < period2)
{
query = "Select * From dbo.Invoice where period >= " + period1 + "and period <=" + period2;
}
else if (period1 == period2)
{
query = "Select * From dbo.Invoice where period = " + period1;
}
else
{
query = "Select * From dbo.Invoice where period <= 12 and period >=" + period1 + "or period >= 1 and period <=" + period2;
}
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "Invoices");
foreach (DataRow row in invoiceTable.Tables["Invoices"].Rows)
{
InvoiceModel temp_invoice = new InvoiceModel();
temp_invoice.invoiceNo = row.Field <string>("invoiceNo");
temp_invoice.invoiceStatus = row.Field <string>("invoiceStatus");
temp_invoice.period = row.Field <int>("period");
temp_invoice.invoiceDate = row.Field <DateTime>("invoiceDate");
temp_invoice.invoiceType = row.Field <string>("invoiceType");
temp_invoice.customerID = row.Field <string>("customerID");
temp_invoice.grossTotal = row.Field <double>("grossTotal");
temp_invoice.netTotal = row.Field <double>("netTotal");
temp_invoice.taxTotal = row.Field <double>("taxTotal");
SalesDashboardModel.CompanyInvoices.Add(temp_invoice);
}
}
}
using (SqlCommand command = new SqlCommand("Select * from dbo.Customer", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(customerTable, "Customers");
foreach (DataRow row in customerTable.Tables["Customers"].Rows)
{
CustomerModel tempCostumer = new CustomerModel();
tempCostumer.customerAccountId = row.Field <string>("AccountID");
tempCostumer.customerAddr = row.Field <string>("Country");
tempCostumer.customerId = row.Field <string>("CustomerID");
tempCostumer.customerName = row.Field <string>("CustomerName");
tempCostumer.customerTaxId = row.Field <string>("CustomerTaxID");
SalesDashboardModel.CompanyCustomers.Add(tempCostumer);
}
}
}
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select * From dbo.Sales", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "sales");
SalesInfoModel temp = new SalesInfoModel();
temp.totalInvoiceCredit = invoiceTable.Tables["sales"].Rows[0].Field <double>("InvoicesTotalCredit");
temp.totalInvoiceDebit = invoiceTable.Tables["sales"].Rows[0].Field <double>("InvoicesTotalDebit");
SalesDashboardModel.SalesInfo = temp;
}
}
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select AVG(GrossTotal) as average From dbo.Invoice", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "Average");
SalesDashboardModel.averageTransactionPrice = invoiceTable.Tables["Average"].Rows[0].Field <double>("average");
}
}
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select SUM(taxTotal) as sum From dbo.Invoice", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(invoiceTable, "Sum");
SalesDashboardModel.sumTotalTaxes = invoiceTable.Tables["Sum"].Rows[0].Field <double>("sum");
}
}
}
using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
using (SqlCommand command = new SqlCommand("Select * From dbo.Company", connection))
{
using (SqlDataAdapter adapter = new SqlDataAdapter(command))
{
adapter.Fill(companyTable, "company");
SaftFileDateModel temp = new SaftFileDateModel();
temp.startDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("StartDate");
temp.endDate = companyTable.Tables["company"].Rows[0].Field <DateTime>("EndDate");
SalesDashboardModel.SaftInfo = temp;
}
}
}
return(View(SalesDashboardModel));
}
