//只有订单在预约和已经使用状态才能下发 public string[] queryTelByTermiId(string termiId) { OrderEx order = null; if (termiId != null) { using (DataContext db = new SqlserverContext()) { Table <Car> T_car = db.GetTable <Car>(); Table <Order> T_order = db.GetTable <Order>(); Table <CarStat> T_Stat = db.GetTable <CarStat>(); Car car = T_car.FirstOrDefault <Car>(o => o.TermID.Equals(termiId)); if (car != null) { order = (from m in T_order join n in T_Stat on m.CarID equals n.CarID where m.CarID == car.id && (n.stat == (int)Commons.CatStatus.book || n.stat == (int)Commons.CatStatus.run) && (m.State == (int)Commons.Type.BOOK || m.State == (int)Commons.Type.USING) orderby n.CreateTime descending select new OrderEx { CarID = m.CarID, ID = m.ID, State = m.State, UserID = m.UserID, }).FirstOrDefault(); if (order != null) { return(new string[] { order.UserID, order.CarID }); } } } } return(null); }
public IList <Station> queryIdleStation() { IList <Station> stations = new List <Station>(); //查询有空闲车的租赁点 using (DataContext db = new SqlserverContext()) { Table <Station> T_Station = db.GetTable <Station>(); Table <Car> T_Car = db.GetTable <Car>(); Table <Piles> T_Piles = db.GetTable <Piles>(); Table <CarStat> T_CarStat = db.GetTable <CarStat>(); IList <Station> allStation = T_Station.ToList <Station>(); if (allStation != null && allStation.Count() > 0) { foreach (var i in allStation) { IList <Piles> allPiles = T_Piles.Where <Piles>(o => o.StationID == i.id).ToList <Piles>(); if (allPiles != null && allPiles.Count() > 0) { foreach (var m in allPiles) { CarStat stat = T_CarStat.Where <CarStat>(o => o.PilesID == m.id).OrderByDescending(x => x.CreateTime).First(); if (stat != null && stat.stat == (int)Commons.CatStatus.idle) { stations.Add(i); break; } } } } } } return(stations); }
public Car findCarByCarId(string id) { using (DataContext db = new SqlserverContext()) { Table <Car> T_Car = db.GetTable <Car>(); Table <RunRealTime> runRealTimeTable = db.GetTable <RunRealTime>(); Table <Order> orderTable = db.GetTable <Order>(); Car car = T_Car.FirstOrDefault <Car>(o => o.id.Equals(id)); if (car != null) { RunRealTime runRealTime = (from m in runRealTimeTable where m.TerminalId == car.TermID orderby m.sampleTime descending select m).FirstOrDefault(); //if (runRealTime != null) //{ //} car.miles = (Convert.ToInt32(car.CarCharge) * 10).ToString(); Order order = (from m in orderTable where m.CarID == car.id orderby m.CreateTime descending select m).FirstOrDefault(); if (order != null) { car.lastUsedTime = order.CreateTime; } Order uorder = (from m in orderTable where m.CarID == car.id orderby m.CreateTime select m).FirstOrDefault(); if (uorder != null) { car.UpdateTime = order.CreateTime; } } return(car); } }
//订单撤销 //return true:成功 false:失败 public bool cancelOrder(string orderid) { if (!String.IsNullOrWhiteSpace(orderid)) { using (DataContext db = new SqlserverContext()) { Table <Order> T_order = db.GetTable <Order>(); Table <OrderLog> T_orderLog = db.GetTable <OrderLog>(); Table <CarStat> carStatTable = db.GetTable <CarStat>(); IEnumerable <OrderLog> orders = T_orderLog.Where <OrderLog>(o => o.OrderID.Equals(orderid)); IList <UserAuthorization> list = null; Table <UserAuthorization> AuthTable = db.GetTable <UserAuthorization>(); if (orders != null && orders.Count() > 0) { return(false); } //如果当前时间可以取消,修改该订单状态 Order order = T_order.FirstOrDefault <Order>(x => x.ID.Equals(orderid)); // DateTime t1 = DateTime.ParseExact(order.Time,"yyyy-MM-dd HH:mm:ss",System.Globalization.CultureInfo.CurrentCulture); if (order != null && order.State == (int)Commons.Type.BOOK) {//在订单状态为预约状态,车状态为预定状态,可以取消订单 CarStat carStat = (from m in carStatTable where m.CarID == order.CarID orderby m.CreateTime descending select m).FirstOrDefault(); if (carStat.stat == (int)Commons.CatStatus.book) { order.State = (int)Commons.Type.CANCEL; CarStat stat = new CarStat(); stat.id = Guid.NewGuid().ToString().Replace("-", ""); stat.stat = (int)Commons.CatStatus.idle; stat.UserID = order.UserID; stat.CarID = order.CarID; stat.PilesID = carStat.PilesID; stat.CreateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); carStatTable.InsertOnSubmit(stat); //收回授权状态 list = (from c in AuthTable where c.OrderId.Equals(orderid) && c.Status == (int)Commons.Authorized.AUTHORIZE select c).ToList(); foreach (UserAuthorization item in list) { item.Status = (int)Commons.Authorized.UNAUTHORIZE; } db.SubmitChanges(); return(true); } } } } return(false); }
public User CreateOrUpdate(User info) { //DbTransaction tran = null; //DbConnection conn = null; //内存中不包含该用户,保存用户信息 if (info != null) { using (DataContext db = new SqlserverContext()) { Table <User> T_USER = db.GetTable <User>(); User u = T_USER.FirstOrDefault <User>(o => o.UserId.Equals(info.UserId)); if (u != null) { u.CardID = info.CardID; u.Name = info.Name; u.UserDriverLicense = info.UserDriverLicense; u.UserStatus = ((int)(Commons.Authentication.Verified)).ToString(); db.SubmitChanges(); return(u); } else { T_USER.InsertOnSubmit(info); db.SubmitChanges(); return(info); } } } return(null); }
public Order updateStatus(Order order) { if (order != null) { using (DataContext db = new SqlserverContext()) { Table <Order> T_Order = db.GetTable <Order>(); //Order orders = (from c in T_Order where c.UserID == userId && c.ID == orderId select c) as Order; Order orders = T_Order.FirstOrDefault <Order>(x => x.ID.Equals(order.ID)); LogerHelper.debug(orders.UserID); if (orders != null) { Order order1 = T_Order.FirstOrDefault <Order>(x => x.ID.Equals(order.ID)); order1.State = (int)Commons.Type.USING; db.SubmitChanges(); LogerHelper.debug("还车修改订单状态成功!" + orders.State); if (orders != null) { return(orders); } } else { LogerHelper.debug("还车修改订单状态失败!"); } } } else { LogerHelper.debug("输入order为空"); } return(null); }
//***********************用户注册DB操作BEGIN********************************* public UserLogin Create(UserGroup Base, UserLogin login, User info, UserRegister register) { //DbTransaction tran = null; //DbConnection conn = null; //内存中不包含该用户,保存用户信息 if (Base != null && info != null && login != null && register != null) { using (DataContext db = new SqlserverContext()) { Table <UserGroup> T_UserGroup = db.GetTable <UserGroup>(); Table <UserLogin> T_UserLogin = db.GetTable <UserLogin>(); Table <User> T_User = db.GetTable <User>(); Table <UserRegister> T_UserRegister = db.GetTable <UserRegister>(); //conn = db.Connection; //if (conn.State == ConnectionState.Closed) //{ // conn.Open(); // tran = conn.BeginTransaction(); //} T_UserGroup.InsertOnSubmit(Base); T_UserLogin.InsertOnSubmit(login); T_User.InsertOnSubmit(info); T_UserRegister.InsertOnSubmit(register); //tran.Commit(); db.SubmitChanges(); LogerHelper.debug("REGESTER SUCCESS"); //conn.Close(); return(login); //注册账号已经存在 } } // ctx.Connection.Close(); //数据保存出错 //HashOperator.getInstance().Set<UserGroup>("InsertUserBaseError", Base.ID, Base); //HashOperator.getInstance().Set<User>("InsertUserInfoError", info.UserGroupID, info); //保存失败,继续存储,保证数据库与内存数据实现同步 //Thread oThread = new Thread(delegate() //{ // List<UserGroup> _userbase = HashOperator.getInstance().GetAll<UserGroup>("InsertUserBaseError"); // List<User> _userinfo = HashOperator.getInstance().GetAll<User>("InsertUserInfoError"); // SaveOrUpdateError.insertUser(_userbase, _userinfo); //}); //oThread.Name = "INSERT_User"; //oThread.Start(); //存储日志 return(null); }
public User getUserByCardID(string CardId) { using (DataContext ctx = new SqlserverContext()) { Table <User> wcUser = ctx.GetTable <User>(); return(wcUser.SingleOrDefault <User>(o => o.CardID.Equals(CardId))); } }
public User getUserByPhoneNumber(string phoneNumber) { using (DataContext ctx = new SqlserverContext()) { Table <User> wcUser = ctx.GetTable <User>(); return(wcUser.SingleOrDefault <User>(o => o.PhoneNumber.Equals(phoneNumber))); } }
//根据登录名查询userid public string getUserId(UserLogin login) { using (DataContext db = new SqlserverContext()) { Table <UserLogin> T_UserLogin = db.GetTable <UserLogin>(); return(T_UserLogin.FirstOrDefault <UserLogin>(x => x.LoginID.Equals(login.LoginID)).UserId); } }
public Station findStaByPileId(string pile) { using (DataContext db = new SqlserverContext()) { Table <Piles> T_PILE = db.GetTable <Piles>(); Table <Station> T_STATION = db.GetTable <Station>(); Piles piles = T_PILE.FirstOrDefault <Piles>(o => o.id == Convert.ToInt32(pile)); if (piles != null) { Station st = T_STATION.FirstOrDefault <Station>(x => x.id == piles.StationID); if (st != null) { return(st); } } } return(null); }
public User selectOpenId(string id) { if (!String.IsNullOrWhiteSpace(id)) { using (DataContext ctx = new SqlserverContext()) { Table <UserLogin> T_wcUser = ctx.GetTable <UserLogin>(); Table <User> T_User = ctx.GetTable <User>(); UserLogin user = T_wcUser.FirstOrDefault <UserLogin>(o => o.LoginID.Equals(id)); if (user != null && !String.IsNullOrWhiteSpace(user.UserId)) { User use = T_User.FirstOrDefault <User>(y => y.UserId.Equals(user.UserId)) as User; if (use != null) { return(use); } } } } return(null); }
/* * SELECT b.* ,a.stationID * FROM dbo.T_Piles as a, * dbo.T_Car as b, * (select * from * (select *,ROW_NUMBER() over (partition by carid order by createtime desc) as new_index from dbo.T_CarStat) a where a.new_index=1) as c * where c.stat=1 * and c.pilesid<>0 * and c.carid =b.id * and a.id = c.pilesid */ //根据租赁点查询车 //分三步: //1.根据租赁点id查询充电桩。2.根据充电桩查车辆状态最新一条和该充电桩有关的数据 /*public IList<Car> findCar(int id) * { * //DbConnection conn = null; * IList<Car> cars = new List<Car>(); * try * {//内存中不包含该用户,保存用户信息 * if (id.ToString() != null) * { * using (DataContext db = new SqlserverContext()) * { * Table<Car> T_Car = db.GetTable<Car>(); * Table<Piles> T_Piles = db.GetTable<Piles>(); * Table<CarStat> T_CarStat = db.GetTable<CarStat>(); * Table<RunRealTime> T_RunRealTime = db.GetTable<RunRealTime>(); * IEnumerable<Piles> piles = T_Piles.Where(o => id == o.StationID); * foreach (var p in piles) * { * IEnumerable<CarStat> t = T_CarStat.Where<CarStat>(m => (p.id == m.PilesID)); * if (t.Count() > 0) * { * CarStat stats = t.OrderByDescending(x => x.CreateTime).FirstOrDefault<CarStat>(); * if (stats != null && stats.stat == (int)Commons.CatStatus.idle) * { * * Car car = T_Car.FirstOrDefault<Car>(n => n.id.Equals(stats.CarID)); * //返回车辆电量 起用时间 上次使用时间 可用公里数 * //查询该车最新时间使用记录 * if (car != null) * { * LogerHelper.debug("idle 车"); * IEnumerable<CarStat> cs = T_CarStat.Where(x => x.CarID.Equals(car.id)); * if (cs.Count() > 0) * { * CarStat st = cs.OrderBy(y => y.CreateTime).FirstOrDefault<CarStat>(); * //上次使用时间 * car.lastUsedTime = st.CreateTime; * //查询车电量 * //IList<RunRealTime> Lists=null; * //Lists=(from c in T_RunRealTime where c.TerminalId == car.TermID select c).ToList() as IList<RunRealTime>; * IEnumerable<RunRealTime> runs = T_RunRealTime.Where<RunRealTime>(o => o.TerminalId.Equals(car.TermID)); * if (runs.Count() > 0) * { * RunRealTime run = runs.OrderBy(z => z.sampleTime).FirstOrDefault<RunRealTime>(); * car.CarCharge = run.batteryInfo.ToString(); * //查询该车公里数 * car.miles = ((int.Parse(car.CarCharge)) * 10).ToString(); * cars.Add(car); * } * } * * * } * } * } * else { * //return (from m in T_Car select m).ToList(); * } * } * if (cars.Count() > 0) * { * // conn.Close(); * return cars; * } * return null; * } * } * } * catch (Exception ex) * { * LogerHelper.DefaultInfo(ex.Source + ":" + ex.ToString()); * } * finally * { * * * * } * return null; * }*/ //获取地区列表 public IList <District> findDistrict(string Name) { //DbConnection conn = null; IList <District> Districts = new List <District>(); //内存中不包含该用户,保存用户信息 if (!String.IsNullOrWhiteSpace(Name)) { using (DataContext db = new SqlserverContext()) { Table <City> T_City = db.GetTable <City>(); Table <District> T_District = db.GetTable <District>(); //根据城市查询cityid City city = T_City.First <City>(o => o.CityName.Equals(Name)); if (city != null) { Districts = T_District.Where <District>(o => o.CityID == city.CityID).ToList <District>(); } } } return(Districts); }
/** * param: userReg * return: true--用户存在 * false--用户不存在 */ public bool veriryUserExistOrNot(string CardId, string tel) { using (DataContext ctx = new SqlserverContext()) { Table <User> wcUser = ctx.GetTable <User>(); User user = wcUser.FirstOrDefault <User>(o => o.CardID.Equals(CardId) || o.PhoneNumber.Equals(tel)); if (user != null) { return(true); } } return(false); }
//查询用户是否已经注册 //false 没有注册 true 已经注册 public bool verifyUser(UserReg login) { using (DataContext db = new SqlserverContext()) { Table <UserLogin> T_UserLogin = db.GetTable <UserLogin>(); //db账号不存在 if (T_UserLogin.FirstOrDefault(o => login.LoginID.Equals(o.LoginID) && login.LoginPwd.Equals(o.LoginPwd)) == null) { return(false); } } return(true); }
public User findLoginUser(UserLogin LoginUser) { using (DataContext db = new SqlserverContext()) { Table <User> T_UserLogin = db.GetTable <User>(); User users = T_UserLogin.FirstOrDefault <User>(x => x.PhoneNumber.Equals(LoginUser.LoginID) && x.Password.Equals(LoginUser.LoginPwd)); if (users != null) { return(users); } } return(null); }
public User findUserId(string id) { using (DataContext db = new SqlserverContext()) { Table <User> T_UserLogin = db.GetTable <User>(); User users = T_UserLogin.FirstOrDefault <User>(x => x.UserId.Equals(id)); if (users != null) { return(users); } } return(null); }
public int SelectNum(string userid) { if (!String.IsNullOrWhiteSpace(userid)) { using (DataContext db = new SqlserverContext()) { Table <Order> T_order = db.GetTable <Order>(); int num = T_order.Where(o => o.UserID.Equals(userid)).ToList().Count(); return(num); } } return(0); }
public IList <Car> findCar(int id) { using (DataContext db = new SqlserverContext()) { Table <ValidCar> carTable = db.GetTable <ValidCar>(); Table <RunRealTime> runRealTimeTable = db.GetTable <RunRealTime>(); Table <Order> orderTable = db.GetTable <Order>(); List <ValidCar> validCarList = (from m in carTable where m.StationID == id select m).ToList(); List <Car> ret = new List <Car>(); foreach (ValidCar car in validCarList) { Car catItem = new Car() { CarCharge = car.CarCharge, CarFac = car.CarFac, CarNo = car.CarNo, CarStartDate = car.CarStartDate, CarType = car.CarType, CarVolume = car.CarVolume, CreateTime = car.CreateTime, id = car.id, TermID = car.TermID }; RunRealTime runRealTime = (from m in runRealTimeTable where m.TerminalId == car.TermID orderby m.sampleTime descending select m).FirstOrDefault(); if (runRealTime != null) { catItem.miles = (Convert.ToInt32(catItem.CarCharge) * 10).ToString(); } Order order = (from m in orderTable where m.CarID == car.id orderby m.CreateTime descending select m).FirstOrDefault(); if (order != null) { catItem.lastUsedTime = order.CreateTime; } Order uorder = (from m in orderTable where m.CarID == car.id orderby m.CreateTime select m).FirstOrDefault(); if (uorder != null) { catItem.UpdateTime = uorder.CreateTime; } ret.Add(catItem); } return(ret); } }
public OrderLog Addlog(OrderLog log) { if (log != null) { using (DataContext db = new SqlserverContext()) { Table <OrderLog> T_order = db.GetTable <OrderLog>(); T_order.InsertOnSubmit(log); db.SubmitChanges(); return(log); } } return(null); }
public CarStat findCarStat(string userId, string carId) { using (DataContext db = new SqlserverContext()) { Table <CarStat> T_CarStat = db.GetTable <CarStat>(); CarStat carstat = T_CarStat.FirstOrDefault <CarStat>(o => o.UserID.Equals(userId) && o.CarID.Equals(carId) && o.stat == (int)Commons.CatStatus.idle); if (carstat != null) { return(carstat); } } return(null); }
public Cup AddCup(Cup charge) { if (charge != null) { using (DataContext db = new SqlserverContext()) { Table <Cup> T_order = db.GetTable <Cup>(); T_order.InsertOnSubmit(charge); db.SubmitChanges(); return(charge); } } return(null); }
public Com.ChinaPalmPay.Platform.RentCar.Model.Alipay Addalipay(Com.ChinaPalmPay.Platform.RentCar.Model.Alipay ali) { if (ali != null) { using (DataContext db = new SqlserverContext()) { Table <Com.ChinaPalmPay.Platform.RentCar.Model.Alipay> T_order = db.GetTable <Com.ChinaPalmPay.Platform.RentCar.Model.Alipay>(); T_order.InsertOnSubmit(ali); db.SubmitChanges(); return(ali); } } return(null); }
public Messages AddMsg(Messages msg) { if (msg != null) { using (DataContext db = new SqlserverContext()) { Table <Messages> T_Msg = db.GetTable <Messages>(); T_Msg.InsertOnSubmit(msg); db.SubmitChanges(); return(msg); } } return(null); }
public IList <Messages> queryMsg(string id) { IList <Messages> msgs = new List <Messages>(); if (!String.IsNullOrWhiteSpace(id)) { using (DataContext ctx = new SqlserverContext()) { Table <Messages> MSG = ctx.GetTable <Messages>(); msgs = (from c in MSG where c.userid == id || c.type == (((int)(Commons.MessagesType.sysType)).ToString()) select c).ToList(); } } return(msgs); }
public Recharge Addpay(Recharge charge) { if (charge != null) { using (DataContext db = new SqlserverContext()) { Table <Recharge> T_order = db.GetTable <Recharge>(); T_order.InsertOnSubmit(charge); db.SubmitChanges(); return(charge); } } return(null); }
public UserAuthorization Add(UserAuthorization auth) { if (auth != null) { using (DataContext db = new SqlserverContext()) { Table <UserAuthorization> AuthTable = db.GetTable <UserAuthorization>(); AuthTable.InsertOnSubmit(auth); db.SubmitChanges(); return(auth); } } return(null); }
public bool updateToOpenDoor(string userId) {//根据UserId修改Order状态为开门 using (DataContext db = new SqlserverContext()) { Table <Order> orderTable = db.GetTable <Order>(); Table <UserAuthorization> AuthTable = db.GetTable <UserAuthorization>(); UserAuthorization auth = AuthTable.Where(o => o.UserId.Equals(userId)).OrderByDescending(p => p.Date).FirstOrDefault(); //查询该订单 if (auth != null) { if (auth.Status == (int)Commons.Authorized.BASED || auth.Status == (int)Commons.Authorized.AUTHORIZE) { Order order = orderTable.Where(x => x.ID.Equals(auth.OrderId)).FirstOrDefault(); if (order != null) { order.State = (int)Commons.Type.OPENDOOR; db.SubmitChanges(); return(true); } } } } return(false); }
//根据用户id查询被授权用户当前是否存在有效订单 //true: 存在 false:不存在 public bool queryValidOrderById(string userId) { if (!String.IsNullOrWhiteSpace(userId)) { IList <Order> ords = null; using (DataContext db = new SqlserverContext()) { Table <Order> OrderTable = db.GetTable <Order>(); Table <UserAuthorization> UserAuthTable = db.GetTable <UserAuthorization>(); int cnt = (from n in UserAuthTable join m in OrderTable on n.OrderId equals m.ID where userId == n.UserId && (n.Status == (int)Commons.Authorized.BASED || n.Status == (int)Commons.Authorized.AUTHORIZE) && (m.State == (int)Commons.Type.BOOK || m.State == (int)Commons.Type.OPENDOOR) orderby n.Date descending select m.ID).Count(); if (cnt > 0) { return(true); } } } return(false); }
public IList <Recharge> queryRecharge(string userId, string orderId) { IList <Recharge> list = null; if (!String.IsNullOrWhiteSpace(userId) && !String.IsNullOrWhiteSpace(orderId)) { using (DataContext db = new SqlserverContext()) { Table <Recharge> T_REC = db.GetTable <Recharge>(); list = (from c in T_REC where c.UserID.Equals(userId) && c.OrderID.Equals(orderId) && c.Type == (int)Commons.PayType.CONSUME select c).ToList() as IList <Recharge>; return(list); } } return(new List <Recharge>()); }