public static TurnOverObjectCollection DataTableToCollection(DataTable turnOvers)
{
TurnOverObjectCollection objectsTotal;
TurnOverObjectCollection objects = new TurnOverObjectCollection();
try
{
IEnumerator enumerator = null;
try
{
enumerator = turnOvers.Rows.GetEnumerator();
while (enumerator.MoveNext())
{
DataRow current = (DataRow)enumerator.Current;
objects.Add(CustomerRowToObject(current));
}
}
finally
{
if (enumerator is IDisposable)
{
(enumerator as IDisposable).Dispose();
}
}
objectsTotal = objects;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
return(objectsTotal);
}
public static TurnOverObjectCollection DataTableToCollection(DataTable turnOvers)
{
TurnOverObjectCollection objectsTotal;
TurnOverObjectCollection objects = new TurnOverObjectCollection();
try
{
IEnumerator enumerator = null;
try
{
enumerator = turnOvers.Rows.GetEnumerator();
while (enumerator.MoveNext())
{
DataRow current = (DataRow)enumerator.Current;
objects.Add(CustomerRowToObject(current));
}
}
finally
{
if (enumerator is IDisposable)
{
(enumerator as IDisposable).Dispose();
}
}
objectsTotal = objects;
}
catch (System.Exception exception1)
{
Exception innerException = exception1;
throw new Exception(MethodBase.GetCurrentMethod().Name, innerException);
}
return objectsTotal;
}
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 TurnOverObjectCollection GetByYear(int year)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
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]);
}
catch (System.Exception exception1)
{
throw new Exception(MethodBase.GetCurrentMethod().Name, exception1);
}
return turnOvers;
}
public TurnOverObjectCollection GetByVehicleGreaterThanDate(DateTime date, string vehicle)
{
TurnOverObjectCollection turnOvers = new TurnOverObjectCollection();
SqlCommand cmd = null;
try
{
cmd = new SqlCommand("TAS_TurnOver_ByVehicleGreaterThanDate");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@DATE", date));
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 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;
}
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 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 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;
}
