public static List <DashboardChart> DashboardExpensesAccounts(int BusinessId, int Year)
{
var ExpensesAccounts = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_DashboardExpenseAccounts);
while (ireader.Read())
{
var ExpensesAccountsDet = new DashboardChart
{
SNo = ireader.GetInt32(CommonColumns.Sno),
AccountName = ireader.GetString(CommonColumns.AccountName),
Amount = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.Amount))
};
ExpensesAccounts.Add(ExpensesAccountsDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in DashboardExpensesAccounts. Exception :" + ex.Message);
}
}
return(ExpensesAccounts);
}
public static DashboardChart PurchasesByYear(int BusinessId, int Year)
{
using (var cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
var PurchasesByYear = new DashboardChart();
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchaseByYear);
while (ireader.Read())
{
var PurchasesByYearDet = new DashboardChart
{
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchase)),
NumberOfPurchase = ireader.GetInt32(CommonColumns.PurchaseCount),
AveragePurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.AveragePurchase)),
LargestPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.LargestPurchase))
};
PurchasesByYear = PurchasesByYearDet;
}
return(PurchasesByYear);
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in PurchasesByYear. Exception :" + ex.Message);
}
}
}
internal static DashboardChart AddChart(string uri)
{
return(DataContext.Do(ctx => {
var chart = new DashboardChart {
Uri = uri
};
ctx.DashboardCharts.Add(chart);
ctx.SaveChanges();
return chart;
}));
}
public static List <DashboardChart> CustomersOwing(int BusinessId, int Year)
{
var CustomersOwing = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
//cmd.AddParameters(Year, CommonConstants.Year, SqlDbType.SmallInt);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
//IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesOwingByCustomers);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesDetailsByCustomers);
while (ireader.Read())
{
var CustomersOwingDet = new DashboardChart
{
//SNo = ireader.GetInt32(CommonColumns.Sno),
UserCustomerId = ireader.GetInt32(CommonColumns.UserCustomerId),
Customer = ireader.GetString(CommonColumns.Customer),
TotalOwing = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalOwing)),
};
CustomersOwing.Add(CustomersOwingDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in CustomerOwing. Exception :" + ex.Message);
}
}
return(CustomersOwing);
}
public static List <DashboardChart> Quarters(int BusinessId, int Year, int QuarterNumber)
{
var Quarters = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
cmd.AddParameters(QuarterNumber, CommonConstants.QuarterNum, SqlDbType.SmallInt);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_SalesByMonth);
while (ireader.Read())
{
var QuartersDet = new DashboardChart
{
Quarter = ireader.GetString(CommonColumns.Quarter),
Month = ireader.GetString(CommonColumns.Month),
TotalSales = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalSales))
};
Quarters.Add(QuartersDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in Quarters. Exception :" + ex.Message);
}
}
return(Quarters);
}
public static List <DashboardChart> TopTenVendors(int BusinessId, int Year)
{
var TopTenVendors = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
cmd.AddParameters(10, CommonConstants.Top, SqlDbType.Int);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchasesDetailsByVendors);
while (ireader.Read())
{
var TopTenVendorsDet = new DashboardChart
{
UserVendorId = ireader.GetInt16(CommonColumns.UserVendorId),
Vendor = ireader.GetString(CommonColumns.Vendor),
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchases)),
TotalOwing = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalOwing)),
};
TopTenVendors.Add(TopTenVendorsDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in TopTenVendors. Exception :" + ex.Message);
}
}
return(TopTenVendors);
}
public static List <DashboardChart> QuartersPuchaseChart(int BusinessId, int Year)
{
var QuartersPuchaseChart = new List <DashboardChart>();
using (var cmd = new DBSqlCommand())
{
try
{
DateTime FromDate = new DateTime(Year, 1, 1);
DateTime ToDate = new DateTime(Year, 12, 31);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, SqlDbType.Int);
cmd.AddParameters(FromDate, CommonConstants.FromDate, SqlDbType.DateTime);
cmd.AddParameters(ToDate, CommonConstants.ToDate, SqlDbType.DateTime);
IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Rpt_PurchaseByMonth);
while (ireader.Read())
{
var QuartersPuchaseChartDet = new DashboardChart
{
Month = ireader.GetString(CommonColumns.Month),
TotalPurchase = ireader.GetDecimal(ireader.GetOrdinal(CommonColumns.TotalPurchase)),
Color = "#B0DE09"
};
QuartersPuchaseChart.Add(QuartersPuchaseChartDet);
}
}
catch (Exception ex)
{
return(null);
throw new ArgumentException("Exception in QuartersPuchaseChart. Exception :" + ex.Message);
}
}
return(QuartersPuchaseChart);
}
