public DataTable MonthlyTrendReport(string month, string year)
{
DataTable data = new DataTable();
//Checks that Data exist for present month or not?
string presentMMYYYY = arch.DecodeMonthYear(month, year);
bool dataExistForPresntMonth = commonReportArch.DataExistForMonth(month, year);
//Checks that Data exist for prev month or not?
string previousMonth = arch.GetPreviousMonth(month);
string previousMonthYear = arch.GetPrevMonthsYear(month, year);
string prevMMYYYY = arch.DecodeMonthYear(previousMonth, previousMonthYear);
bool dataExistForPrevMonth = commonReportArch.DataExistForMonth(previousMonth, previousMonthYear);
//Checks that Data exist for next month or not?
string nextMonth = arch.GetNextMonth(month);
string nextMonthYear = arch.GetNextMonthsYear(month, year);
string nextMMYYYY = arch.DecodeMonthYear(nextMonth, nextMonthYear);
bool dataExistForNextMonth = commonReportArch.DataExistForMonth(nextMonth, nextMonthYear);
string[,] reportData = null;
string[] columnName = null ;
if (dataExistForPresntMonth && dataExistForPrevMonth && dataExistForNextMonth)
{
columnName = new string[] { "Expensed By", previousMonth + " (Prev.)", "Trend1", month + " (Pres.)", "Trend2", nextMonth + " (Nxt.)" };
reportData = new string[reportArch.GetAllUsers().Length, 6];
reportData = GetReportData1(prevMMYYYY, presentMMYYYY, nextMMYYYY);
data = arch.GetDataTableFrom2DArray(columnName, reportData);
}
else if (dataExistForPresntMonth && dataExistForPrevMonth && !dataExistForNextMonth)
{
columnName = new string[] { "Expensed By", previousMonth + " (Prev.)", "Trend", month + " (Pres.)" };
reportData = new string[reportArch.GetAllUsers().Length, 4];
reportData = GetReportData2(prevMMYYYY, presentMMYYYY);
data = arch.GetDataTableFrom2DArray(columnName, reportData);
}
else if (dataExistForPresntMonth && !dataExistForPrevMonth && !dataExistForNextMonth)
{
columnName = new string[] { "Expensed By", month + " (Pres.)" };
data = reportArch.MonthlyReportData(month, year);
}
else if (dataExistForPresntMonth && !dataExistForPrevMonth && dataExistForNextMonth)
{
columnName = new string[] { "Expensed By", month + " (Pres.)", "Trend", nextMonth + " (Nxt.)" };
reportData = new string[reportArch.GetAllUsers().Length, 4];
reportData = GetReportData2(presentMMYYYY, nextMMYYYY);
data = arch.GetDataTableFrom2DArray(columnName, reportData);
}
return data;
}
public DataSet MonthlyReportData(string month, string Year)
{
DataSet dsReportData = new DataSet();
string monthYear = arch.DecodeMonthYear(month, Year);
string Query = "SELECT Distinct(Exp_By) as ExpBy,User_Info.First_Name + ' ' +User_Info.Last_Name as ExpenseBy, Sum(Exp_Amount) as TotalExpense from " +
"Expense_Details,User_Info Where " +
"Expense_Details.Exp_By=User_Info.User_Id AND " +
"Expense_Details.MonthYear='" + monthYear + "' AND Expense_Details.IsDeleted=0" +
" Group by Exp_By, User_Info.First_Name + ' ' +User_Info.Last_Name ";
dsReportData = _dbHelper.ExecuteDataSet(Query);
return(dsReportData);
}
public bool DataExistForMonth(string month, string year)
{
string monthYear = arch.DecodeMonthYear(month, year);
string Query = "SELECT COUNT(*) FROM Expense_Details WHERE " +
"MonthYear='" + monthYear + "' AND IsDeleted=0";
if (Convert.ToInt16(_dbHelper.ExecuteScalar(Query).ToString()) > 0)
{
return(true);
}
else
{
return(false);
}
}
public DataTable MonthlyReportData(string month, string Year)
{
DataTable dtReportData = new DataTable();
string monthYear = arch.DecodeMonthYear(month, Year);
string Query = "SELECT Distinct(Expense_Details.Item_Id), Item_Details.Item_Name as Item, Sum(Exp_Amount) as TotalExpense from " +
"Expense_Details,Item_Details Where " +
"Expense_Details.Item_Id=Item_Details.Item_Id AND " +
"Expense_Details.MonthYear='" + monthYear + "' AND Expense_Details.IsDeleted=0 " +
"Group by Expense_Details.Item_Id, Item_Details.Item_Name";
dtReportData = _dbHelper.ExecuteDataTable(Query);
return(dtReportData);
}