public static List <Model.mdlTrackingJourney> GetTrackingCoordinate(string employeeID, string branchID, DateTime date)
{
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@DateStart", SqlDbType = SqlDbType.DateTime, Value = date
},
new SqlParameter()
{
ParameterName = "@DateFinish", SqlDbType = SqlDbType.DateTime, Value = date.AddDays(1)
},
//new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Now.Date.AddDays(1) },
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = employeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = branchID
}
};
DataTable dtDailyMsg = DataFacade.DTSQLCommand(@"SELECT TrackingDate,VehicleID,Latitude,Longitude FROM LiveTracking WHERE TrackingDate >= @DateStart AND TrackingDate < @DateFinish AND EmployeeID=@EmployeeID AND BranchID = @BranchID Order by TrackingDate", sp);
var mdlTrackingList = new List <Model.mdlTrackingJourney>();
foreach (DataRow row in dtDailyMsg.Rows)
{
var mdlTracking = new Model.mdlTrackingJourney();
mdlTracking.vehicleID = row["VehicleID"].ToString();
mdlTracking.time = Convert.ToDateTime(row["TrackingDate"]).ToString("H:mm:ss");
mdlTracking.latitude = row["Latitude"].ToString();
mdlTracking.longitude = row["Longitude"].ToString();
mdlTrackingList.Add(mdlTracking);
}
return(mdlTrackingList);
}
//Multiple Employee and to get total distance from employee's journey, above is for the single employee
public static List <Model.mdlVisitReport> GetTrackingCoordinate2(List <string> lEmployeeIDlist, string branchID, DateTime date, DateTime dateEnd)
{
string lParam = string.Empty;
foreach (var lEmployeeID in lEmployeeIDlist)
{
if (lParam == "")
{
lParam = " a.EmployeeID =" + "'" + lEmployeeID + "'";
}
else
{
lParam += " OR a.EmployeeID =" + "'" + lEmployeeID + "'";
}
}
lParam = "(" + lParam + ")";
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@DateStart", SqlDbType = SqlDbType.DateTime, Value = date
},
new SqlParameter()
{
ParameterName = "@DateFinish", SqlDbType = SqlDbType.DateTime, Value = dateEnd.AddDays(1)
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = branchID
}
};
DataTable dtVisit = DataFacade.DTSQLCommand(@"SELECT a.BranchID,a.EmployeeID,a.VehicleID,a.StartDate,a.EndDate,a.KMStart,a.KMFinish,
b.BranchName, c.EmployeeName, a.VisitDate
FROM Visit a
INNER JOIN Branch b ON b.BranchID = a.BranchID
INNER JOIN Employee c on c.EmployeeID = a.EmployeeID
WHERE (a.VisitDate BETWEEN @DateStart and @DateFinish) and (a.BranchID=@BranchID) and " + lParam + "Order By c.EmployeeName,a.StartDate", sp);
var mdlVisitList = new List <Model.mdlVisitReport>();
foreach (DataRow row in dtVisit.Rows)
{
var mdlVisit = new Model.mdlVisitReport();
mdlVisit.BranchID = row["BranchID"].ToString();
mdlVisit.BranchName = row["BranchName"].ToString();
mdlVisit.EmployeeID = row["EmployeeID"].ToString();
mdlVisit.EmployeeName = row["EmployeeName"].ToString();
mdlVisit.VehicleID = row["VehicleID"].ToString();
mdlVisit.VisitDate = Convert.ToDateTime(row["VisitDate"]).ToString("dd-MM-yyyy");
mdlVisit.StartDate = Convert.ToDateTime(row["StartDate"]).ToString("dd-MM-yyyy HH:mm:ss");
mdlVisit.EndDate = Convert.ToDateTime(row["EndDate"]).ToString("dd-MM-yyyy HH:mm:ss");
mdlVisit.KMStart = Convert.ToInt32(row["KMStart"].ToString());
mdlVisit.KMEnd = Convert.ToInt32(row["KMFinish"].ToString());
// DataTable dtJourneyDistance = DataFacade.DTSQLCommand(@"SELECT Latitude,Longitude FROM LiveTracking
// WHERE TrackingDate >= '"+Convert.ToDateTime(mdlVisit.VisitDate).ToString("yyyy-MM-dd")+"' AND TrackingDate < '"+Convert.ToDateTime(mdlVisit.EndDate).AddDays(1).ToString("yyyy-MM-dd")+"' AND BranchID = '"+mdlVisit.BranchID+"' AND EmployeeID = '"+mdlVisit.EmployeeID+"' Order by TrackingDate", sp);
DataTable dtJourneyDistance = DataFacade.DTSQLCommand(@"SELECT Latitude,Longitude FROM LiveTracking
WHERE TrackingDate >= '" + Convert.ToDateTime(row["VisitDate"]).ToString("yyyy-MM-dd") + "' AND TrackingDate < '" + Convert.ToDateTime(row["EndDate"]).AddDays(1).ToString("yyyy-MM-dd") + "' AND BranchID = '" + mdlVisit.BranchID + "' AND EmployeeID = '" + mdlVisit.EmployeeID + "' Order by TrackingDate", sp);
var mdlTracking = new Model.mdlTrackingJourney();
double Total = 0;
foreach (DataRow rowJourney in dtJourneyDistance.Rows)
{
if (mdlTracking.latitude == null || mdlTracking.longitude == null)
{
mdlTracking.latitude = rowJourney["Latitude"].ToString();
mdlTracking.longitude = rowJourney["Longitude"].ToString();
}
//if (rowJourney == dtJourneyDistance.Rows[dtJourneyDistance.Rows.Count - 1]) -->> // to get the last data in looping
else
{
mdlTracking.latitude2 = rowJourney["Latitude"].ToString();
mdlTracking.longitude2 = rowJourney["Longitude"].ToString();
var SCoor = new GeoCoordinate(double.Parse(mdlTracking.latitude, CultureInfo.InvariantCulture), double.Parse(mdlTracking.longitude, CultureInfo.InvariantCulture));
var FCoor = new GeoCoordinate(double.Parse(mdlTracking.latitude2, CultureInfo.InvariantCulture), double.Parse(mdlTracking.longitude2, CultureInfo.InvariantCulture));
double distance = RadiusFacade.getDistance(SCoor, FCoor);
Total = Total + distance;
mdlTracking.latitude = mdlTracking.latitude2;
mdlTracking.longitude = mdlTracking.longitude2;
}
}
mdlVisit.DistancebyGPS = Convert.ToString(Math.Ceiling(Total / 1000));
mdlVisitList.Add(mdlVisit);
}
return(mdlVisitList);
}
