//只有订单在预约和已经使用状态才能下发
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>());
}