public Decimal GetAverageTurnOverMonthByYear(int year)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
Decimal turnOver = new Decimal();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByYear");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@YEAR", year));
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
for (int i = 0; i < turnOvers.Count; i++)
{
turnOver += turnOvers[i].TurnOver;
}
int months = 1;
if (year != System.DateTime.Now.Year)
{
months = 12;
}
else
{
months = System.DateTime.Now.Month;
}
turnOver = turnOver / months;
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOver);
}
public Decimal GetAverageTurnOverMonthByYearByVehicle(int year, string vehicle)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
Decimal turnOver = new Decimal();
try
{
turnOvers = GetByVehicleByYear(year, vehicle);
for (int i = 0; i < turnOvers.Count; i++)
{
turnOver += turnOvers[i].TurnOver;
}
int months = 1;
if (year != System.DateTime.Now.Year)
{
months = 12;
}
else
{
months = System.DateTime.Now.Month;
}
turnOver = turnOver / months;
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOver);
}
public TurnOverObjectCollection GetAll()
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver");
cmd.CommandType = CommandType.StoredProcedure;
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOvers);
}
public TurnOverObjectCollection GetByCustomer(CustomerObject customer)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByCustomer");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TNR_ID", customer.TasID));
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOvers);
}
public TurnOverObjectCollection GetByGreaterThanDate(DateTime date)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByGreaterThanDate");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@DATE", date));
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(AddZeroTurnOvers(turnOvers));
}
public TurnOverObjectCollection GetByMonth(int year, int month)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByMonth");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@YEAR", year));
cmd.Parameters.Add(new SqlParameter("@MONTH", month));
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOvers);
}
public TurnOverObjectCollection GetByVehicleByYear(int year, string vehicle)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByVehicleByYear");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@YEAR", year));
cmd.Parameters.Add(new SqlParameter("@VEHICLE", vehicle));
turnOvers = TurnOverConvertor.DataTableToCollection(this.ExecuteNonQuery(cmd, false).Tables[0]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(AddZeroTurnOvers(turnOvers));
}
public Decimal GetTurnOverByYearByCustomer(CustomerObject customer, int year)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
Decimal turnOver = new Decimal();
SqlCommand cmd = null;
DataTable dt;
try
{
string query = "SELECT SUM(Turnover) "
+ " FROM [TIS].[dbo].[TurnOverCustomer]"
+ " WHERE tas_TNR_ID = " + customer.TasID + " AND Date >= '" + year + "0101' AND Date <= '" + year + "1231'";
cmd = new SqlCommand(query);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new SqlParameter("@TNR_ID", customer.TasID));
cmd.Parameters.Add(new SqlParameter("@YEAR", year));
dt = this.ExecuteNonQuery(cmd, false).Tables[0];
if (dt.Rows[0][0].ToString() == "")
{
turnOver = 0;
}
else
{
turnOver = Convert.ToDecimal(dt.Rows[0][0].ToString());
}
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOver);
}
public Decimal GetAverageTurnOverMonthByYearByCustomer(CustomerObject customer, int year)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
Decimal turnOver = new Decimal();
try
{
if (customer == null) //Average All
{
return(GetAverageTurnOverMonthByYear(year));
}
else // Average Customer
{
turnOvers = GetByCustomerByYear(customer, year);
for (int i = 0; i < turnOvers.Count; i++)
{
turnOver += turnOvers[i].TurnOver;
}
int months = 1;
if (year != System.DateTime.Now.Year)
{
months = 12;
}
else
{
months = System.DateTime.Now.Month;
}
turnOver = turnOver / months;
}
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOver);
}
public TurnOverObjectCollection AddZeroTurnOvers(TurnOverObjectCollection turnOvers)
{
try
{
TurnOverObjectCollection newTurnOvers = new TurnOverObjectCollection();
TurnOverObject newTurnOver;
Boolean firstRow = true;
int previousYear = 0;
int previousMonth = 0;
int month = 0;
int year = 0;
foreach (TurnOverObject turnOver in turnOvers)
{
if (firstRow == false)
{
//Check Add zero TurnOver row
if (turnOver.Month != previousMonth + 1)
{
if (previousMonth != 12)
{
month = previousMonth + 1;
year = previousYear;
}
else
{
if (turnOver.Month != 1)
{
month = 1;
year = previousYear + 1;
}
else
{
month = 0;
year = 0;
}
}
}
else
{
month = 0;
year = 0;
}
//Add Zero TurnOver row
if (year != 0 & month != 0)
{
int i = 0;
do
{
newTurnOver = new TurnOverObject();
newTurnOver.Month = month + i;
newTurnOver.Year = year;
newTurnOver.TnrID = turnOver.TnrID;
newTurnOver.Vehicle = turnOver.Vehicle;
newTurnOver.TurnOver = 0;
newTurnOvers.Add(newTurnOver);
i += 1;
if (month + i == 13)
{
month = 1;
year = year + 1;
i = 0;
}
}while (turnOver.Month != month + i);
}
}
//Add Normal Turnover
newTurnOver = new TurnOverObject();
newTurnOver.Month = turnOver.Month;
newTurnOver.Year = turnOver.Year;
newTurnOver.TnrID = turnOver.TnrID;
newTurnOver.Vehicle = turnOver.Vehicle;
newTurnOver.TurnOver = turnOver.TurnOver;
newTurnOver.Date = turnOver.Date;
newTurnOvers.Add(newTurnOver);
firstRow = false;
previousMonth = newTurnOver.Month;
previousYear = newTurnOver.Year;
}
return(newTurnOvers);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return(turnOvers);
}