//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)); }