public HttpResponseMessage Post([FromBody] RoutesBO objList) { DispatchAssignmentDL dl = new DispatchAssignmentDL(); var list = routes.AddRoutes(objList); return(Request.CreateResponse(HttpStatusCode.OK, list, Configuration.Formatters.JsonFormatter)); }
public IList <Guid> InsertRouteData(RoutesBO obj) { try { //routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey, //estimateddistanceinmiles, estimatedtraveltime, status, driverkey, //scheduledarrival, scheduleddeparture, odometeratsource, actualarrival, //actualdeparture, odometeratdestination var RouteDetailCollection = new List <Guid>(); string sql = "dbo.fn_insert_routes_details"; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey); cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey); cmd.Parameters.AddWithValue("_driverkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.driverkey); cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes); var reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { var RouteDetailID = Guid.Parse(reader[i].ToString()); RouteDetailCollection.Add(RouteDetailID); } } reader.Close(); } return(RouteDetailCollection); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public bool UpdateRouteDataforDispatchAssignment(RoutesBO obj) { try { string sql = "update dbo.tms_routes set drivernotes=@drivernotes, driverkey = @driverkey" + " where orderkey = @orderkey and orderdetailkey = @orderdetailkey"; //SELECT routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey, estimateddistanceinmiles, // estimatedtraveltime, status, driverkey, scheduledarrival, scheduleddeparture, odometeratsource, actualarrival, // actualdeparture, odometeratdestination, drivernotes FROM dbo.tms_routes; string query = "dbo.fn_update_route_for_dispatch_assignment"; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(query, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey); cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey); cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes); cmd.Parameters.AddWithValue("_driverkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.driverkey); var reader = cmd.ExecuteReader(); while (reader.Read()) { var result = bool.Parse(reader[0].ToString()); return(result); } reader.Close(); } return(false); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public RoutesBO getRoutesData(string orderDetailKey) { try { string sql = "dbo.fn_getRoutesData"; RoutesBO routeData = new RoutesBO(); conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; var reader = cmd.ExecuteReader(); do { while (reader.Read()) { routeData.Routekey = Utils.CustomParse <Guid>(reader["routekey"]); routeData.scheduledarrival = Utils.CustomParse <DateTime>(reader["scheduledarrival"]); routeData.scheduledarrival = Utils.CustomParse <DateTime>(reader["scheduleddeparture"]); routeData.legtype = Utils.CustomParse <int>(reader["legtype"]); routeData.legno = Utils.CustomParse <int>(reader["legno"]); routeData.drivernotes = Utils.CustomParse <string>(reader["drivernotes"]); } }while (reader.NextResult()); reader.Close(); } return(routeData); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public bool UpdatePortwaitingtime(RoutesBO route) { try { string sql = "update dbo.tms_routes set drivernotes =" + "@drivernotes where orderdetailkey = @orderdetailkey"; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, route.OrderDetailKey); cmd.Parameters.AddWithValue("@drivernotes", NpgsqlTypes.NpgsqlDbType.Smallint, route.driverkey); int returnvalue = cmd.ExecuteNonQuery(); if (returnvalue < 0) { return(false); } else { return(true); } } } catch (Exception msg) { throw msg; } finally { conn.Close(); } }
public HttpResponseMessage Post([FromBody] RoutesBO routesBO) { var routesdata = routes.UpdateRouteDataforDispatchAssignment(routesBO); return(Request.CreateResponse(HttpStatusCode.OK, routesdata, Configuration.Formatters.JsonFormatter)); }
public IList <Guid> AddRoutes(RoutesBO obj) { try { //routekey, orderdetailkey, orderkey, legno, legtype, sourceaddrkey, destinationaddrkey, //estimateddistanceinmiles, estimatedtraveltime, status, driverkey, //scheduledarrival, scheduleddeparture, odometeratsource, actualarrival, //actualdeparture, odometeratdestination var RouteDetailCollection = new List <Guid>(); string sql = "dbo.fn_insert_tms_route_details"; conn = new NpgsqlConnection(connString); conn.Open(); using (var cmd = new NpgsqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.AddWithValue("_orderkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderKey); cmd.Parameters.AddWithValue("_orderdetailkey", NpgsqlTypes.NpgsqlDbType.Uuid, obj.OrderDetailKey); cmd.Parameters.AddWithValue("_legno", NpgsqlTypes.NpgsqlDbType.Smallint, obj.legno); cmd.Parameters.AddWithValue("_legtype", NpgsqlTypes.NpgsqlDbType.Smallint, obj.legtype); if (String.IsNullOrEmpty(obj.drivernotes) || String.IsNullOrWhiteSpace(obj.drivernotes)) { cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, string.Empty); } else { cmd.Parameters.AddWithValue("_drivernotes", NpgsqlTypes.NpgsqlDbType.Varchar, obj.drivernotes); } cmd.Parameters.AddWithValue("_scheduledarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, obj.scheduledarrival); cmd.Parameters.AddWithValue("_scheduleddeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, obj.scheduleddeparture); //cmd.Parameters.AddWithValue("_scheduledarrival", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(obj.scheduleddeparture, System.Globalization.CultureInfo.InvariantCulture)); //cmd.Parameters.AddWithValue("_scheduleddeparture", NpgsqlTypes.NpgsqlDbType.Timestamp, DateTime.Parse(obj.scheduleddeparture, System.Globalization.CultureInfo.InvariantCulture)); var reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { var RouteDetailID = Guid.Parse(reader[i].ToString()); RouteDetailCollection.Add(RouteDetailID); } } reader.Close(); } return(RouteDetailCollection); } catch (Exception msg) { throw msg; } finally { conn.Close(); } }