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)); }