//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);
}
public static List <Model.mdlVisitJourney> GetVisitCoordinate(string employeeID, string branchID, DateTime date)
{
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@Date", SqlDbType = SqlDbType.DateTime, Value = date
},
//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 dtVisit = DataFacade.DTSQLCommand(@"select a.VisitID,b.CustomerID,c.CustomerName,b.StartDate,b.EndDate,b.Latitude,b.Longitude,b.isInRange,b.ReasonID,d.Value from Visit a
inner join VisitDetail b ON a.VisitID = b.VisitID
inner join Customer c ON b.CustomerID = c.CustomerID
left join Reason d on b.ReasonID = d.ReasonID
WHERE a.VisitDate >= @Date AND a.VisitDate < DATEADD(dd,1,@Date) AND a.EmployeeID = @EmployeeID AND a.BranchID = @BranchID ORDER BY StartDate", sp);
var mdlVisitList = new List <Model.mdlVisitJourney>();
foreach (DataRow row in dtVisit.Rows)
{
var mdlVisit = new Model.mdlVisitJourney();
mdlVisit.visitID = row["VisitID"].ToString();
mdlVisit.customerID = row["CustomerID"].ToString();
mdlVisit.customerName = row["CustomerName"].ToString();
mdlVisit.startTime = Convert.ToDateTime(row["StartDate"]).ToString("H:mm:ss");
mdlVisit.finishTime = Convert.ToDateTime(row["EndDate"]).ToString("H:mm:ss");
mdlVisit.latitude = row["Latitude"].ToString();
mdlVisit.longitude = row["Longitude"].ToString();
if (Convert.ToInt16(row["isInRange"]) == 1)
{
mdlVisit.isInRange = "YES";
}
else
{
mdlVisit.isInRange = "NO";
}
mdlVisit.reasonID = row["ReasonID"].ToString();
if (mdlVisit.reasonID != "")
{
mdlVisit.reasonName = row["Value"].ToString();
}
else
{
mdlVisit.reasonName = "";
}
mdlVisitList.Add(mdlVisit);
}
return(mdlVisitList);
}
//001
//public static List<Model.DeliveryOrder> GetDOByCustomerID(string customerID,string warehouseID)
//{
// var DO = DataContext.DeliveryOrders.Where(fld => fld.CustomerID.Equals(customerID) && fld.WarehouseID.Equals(warehouseID)).OrderByDescending(fld => fld.DODate).ToList();
// return DO;
//}
public static List <Model.mdlDeliveryOrder> LoadDeliveryOrder(Model.mdlParam json)
{
var mdlDeliveryOrderList = new List <Model.mdlDeliveryOrder>();
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@StartDate", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date
},
new SqlParameter()
{
ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value = Convert.ToDateTime(json.Date).Date.AddDays(1)
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID
}
};
DataTable dtDeliveryOrder = DataFacade.DTSQLCommand(@"SELECT a.DONumber
,a.CallPlanID
,a.DODate
,a.DOStatus
,a.Description
,a.CustomerID
,a.EmployeeID
,a.VehicleID
,a.BranchID
,a.Signature
,a.IsPrint
,a.Remark
,a.WarehouseID
FROM DeliveryOrder a
INNER JOIN (
SELECT CallPlanID FROM CallPlan
WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND IsFinish = 0 AND Date >= @StartDate AND Date < @FinishDate
) b ON b.CallPlanID = a.CallPlanID
WHERE DOStatus = 'Shipper'", sp);
//INNER JOIN (select * from CallPlanDetail where CallPlanID='C1000001') b ON b.CustomerID = a.CustomerID
foreach (DataRow row in dtDeliveryOrder.Rows)
{
var mdlDeliveryOrder = new Model.mdlDeliveryOrder();
mdlDeliveryOrder.DONumber = row["DONumber"].ToString();
mdlDeliveryOrder.CallPlanID = row["CallPlanID"].ToString();
mdlDeliveryOrder.CustomerID = row["CustomerID"].ToString();
mdlDeliveryOrder.WarehouseID = row["WarehouseID"].ToString();
mdlDeliveryOrder.EmployeeID = row["EmployeeID"].ToString();
mdlDeliveryOrder.VehicleID = row["VehicleID"].ToString(); //006--
mdlDeliveryOrder.DODate = Convert.ToDateTime(row["DODate"]).ToString("yyyy-MM-dd hh:mm:ss");
mdlDeliveryOrder.DOStatus = row["DOStatus"].ToString();
mdlDeliveryOrder.Description = row["Description"].ToString();
mdlDeliveryOrder.Signature = row["Signature"].ToString();
mdlDeliveryOrder.IsPrint = row["IsPrint"].ToString();
mdlDeliveryOrder.Remark = row["Remark"].ToString();
mdlDeliveryOrder.BranchID = row["BranchID"].ToString();
mdlDeliveryOrder.VisitID = "";
mdlDeliveryOrderList.Add(mdlDeliveryOrder);
}
return(mdlDeliveryOrderList);
}
public static List <Model.mdlDeliveryOrderDetail> LoadDeliveryOrderDetail(Model.mdlParam json, List <Model.mdlDeliveryOrder> listDO)
{
List <SqlParameter> sp = new List <SqlParameter>();
StringBuilder sb = new StringBuilder();
int count = 1;
foreach (var DO in listDO)
{
var sqlParameter = new SqlParameter();
sqlParameter.ParameterName = "@DoNumbers" + count.ToString();
if (DO == listDO.Last())
{
sb.Append("@DoNumbers" + count.ToString());
}
else
{
sb.Append("@DoNumbers" + count.ToString() + ",");
}
sqlParameter.SqlDbType = SqlDbType.NVarChar;
sqlParameter.Value = DO.DONumber;
sp.Add(sqlParameter);
count++;
}
var mdlDeliveryOrderDetailList = new List <Model.mdlDeliveryOrderDetail>();
var deliveryOrder = LoadDeliveryOrder(json);
//List<SqlParameter> sp = new List<SqlParameter>()
//{
// new SqlParameter() {ParameterName = "@StartDate", SqlDbType = SqlDbType.DateTime, Value= DateTime.Now.Date },
// new SqlParameter() {ParameterName = "@FinishDate", SqlDbType = SqlDbType.DateTime, Value= DateTime.Now.Date.AddDays(1) },
// new SqlParameter() {ParameterName = "@EmployeeID", SqlDbType = SqlDbType.NVarChar, Value = json.EmployeeID },
// new SqlParameter() {ParameterName = "@DoNumbers", SqlDbType = SqlDbType.NVarChar, Value = DONumbers},
// new SqlParameter() {ParameterName = "@BranchID", SqlDbType = SqlDbType.NVarChar, Value = json.BranchID }
//};
// DataTable dtDeliveryOrderDetailList = DataFacade.DTSQLCommand(@"SELECT DONumber,
// ProductID,
// UOM,
// Quantity,
// QuantityReal,
// ProductGroup,
// LotNumber,
// BoxID
// FROM DeliveryOrderDetail
// WHERE DONumber IN (
// SELECT a.DONumber
// FROM DeliveryOrder a
// INNER JOIN (
// SELECT * FROM CallPlanDetail
// WHERE CallPlanID IN (
// SELECT CallPlanID FROM CallPlan
// WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND IsFinish = 0 AND Date >= @StartDate
// )
// ) b ON b.CustomerID = a.CustomerID
// WHERE EmployeeID = @EmployeeID AND BranchID = @BranchID AND DODate >= @StartDate AND DOStatus = 'Shipper'
// )", sp);
DataTable dtDeliveryOrderDetailList = DataFacade.DTSQLCommand(@"SELECT DONumber,
ProductID,
UOM,
Quantity,
QuantityReal,
ProductGroup,
LotNumber,
BoxID,
Line
FROM DeliveryOrderDetail WHERE DONumber IN (" + sb.ToString() + ") AND Quantity != 0", sp);
foreach (DataRow row in dtDeliveryOrderDetailList.Rows)
{
var mdlDeliveryOrderDetail = new Model.mdlDeliveryOrderDetail();
mdlDeliveryOrderDetail.DONumber = row["DONumber"].ToString();
mdlDeliveryOrderDetail.ProductID = row["ProductID"].ToString();
mdlDeliveryOrderDetail.UOM = row["UOM"].ToString();
mdlDeliveryOrderDetail.Quantity = row["Quantity"].ToString();
mdlDeliveryOrderDetail.QuantityReal = row["QuantityReal"].ToString();
mdlDeliveryOrderDetail.ProductGroup = row["ProductGroup"].ToString();
mdlDeliveryOrderDetail.LotNumber = row["LotNumber"].ToString();
mdlDeliveryOrderDetail.BoxID = row["BoxID"].ToString();
mdlDeliveryOrderDetail.Line = row["Line"].ToString();
mdlDeliveryOrderDetailList.Add(mdlDeliveryOrderDetail);
}
return(mdlDeliveryOrderDetailList);
}
public static string AddNewCustomer(string customerName, string address, string customerType, string city, string countryReg, string account, string distributor, string employeeID)
{
List <SqlParameter> sp = new List <SqlParameter>()
{
new SqlParameter()
{
ParameterName = "@CustomerID", SqlDbType = SqlDbType.VarChar, Value = CreateNewCustomerID()
},
new SqlParameter()
{
ParameterName = "@CustomerName", SqlDbType = SqlDbType.VarChar, Value = customerName
},
new SqlParameter()
{
ParameterName = "@CustomerAddress", SqlDbType = SqlDbType.VarChar, Value = address
},
new SqlParameter()
{
ParameterName = "@Phone", SqlDbType = SqlDbType.VarChar, Value = ""
},
new SqlParameter()
{
ParameterName = "@Email", SqlDbType = SqlDbType.VarChar, Value = ""
},
new SqlParameter()
{
ParameterName = "@PIC", SqlDbType = SqlDbType.VarChar, Value = ""
},
new SqlParameter()
{
ParameterName = "@CustomerTypeID", SqlDbType = SqlDbType.VarChar, Value = customerType
},
new SqlParameter()
{
ParameterName = "@Latitude", SqlDbType = SqlDbType.VarChar, Value = ""
},
new SqlParameter()
{
ParameterName = "@Longitude", SqlDbType = SqlDbType.VarChar, Value = ""
},
new SqlParameter()
{
ParameterName = "@BranchID", SqlDbType = SqlDbType.VarChar, Value = city
},
new SqlParameter()
{
ParameterName = "@Radius", SqlDbType = SqlDbType.Decimal, Value = 0
},
new SqlParameter()
{
ParameterName = "@City", SqlDbType = SqlDbType.VarChar, Value = city
},
new SqlParameter()
{
ParameterName = "@CountryRegionCode", SqlDbType = SqlDbType.VarChar, Value = countryReg
},
new SqlParameter()
{
ParameterName = "@Blocked", SqlDbType = SqlDbType.Bit, Value = false
},
new SqlParameter()
{
ParameterName = "@Account", SqlDbType = SqlDbType.VarChar, Value = account
},
new SqlParameter()
{
ParameterName = "@Distributor", SqlDbType = SqlDbType.VarChar, Value = distributor
},
new SqlParameter()
{
ParameterName = "@EmployeeID", SqlDbType = SqlDbType.VarChar, Value = employeeID
}
};
string query = @"INSERT INTO [dbo].[Customer]
([CustomerID]
,[CustomerName]
,[CustomerAddress]
,[Phone]
,[Email]
,[PIC]
,[CustomerTypeID]
,[Latitude]
,[Longitude]
,[BranchID]
,[Radius]
,[City]
,[CountryRegionCode]
,[Blocked]
,[Account]
,[Distributor]
,[EmployeeID])
VALUES
(@CustomerID,@CustomerName,@CustomerAddress,@Phone,@Email,@PIC,@CustomerTypeID,@Latitude,@Longitude,@BranchID,@Radius,@City,@CountryRegionCode,@Blocked,@Account,@Distributor,@EmployeeID)";
return(DataFacade.DTSQLVoidCommand(query, sp));
}
