public ParkTimeseries GetTimeseries(string timeseriesID, out string ErrorMessage) { ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ParkTimeseries where TimeseriesID=@TimeseriesID and DataStatus!=@DataStatus"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("GateID", timeseriesID); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(DataReaderToModel <ParkTimeseries> .ToModel(reader)); } else { return(null); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(null); }
public EmergentOrder QueryEmergentOrder(decimal id, OrderIdType orderIdType) { string sql = "SELECT Id,[Type],Content,[Time],Account,OrderIdType FROM T_EmergentOrder WHERE Id=@Id AND OrderIdType =@OrderIdType"; EmergentOrder result = null; using (DbOperator dboperator = new DbOperator(Provider, ConnectionString)) { dboperator.AddParameter("Id", id); dboperator.AddParameter("OrderIdType", orderIdType); using (System.Data.Common.DbDataReader reader = dboperator.ExecuteReader(sql)) { if (reader.Read()) { result = new EmergentOrder(); result.Id = reader.GetDecimal(0); result.Type = (OrderStatus)reader.GetInt32(1); result.Content = reader.GetString(2); result.Time = reader.GetDateTime(3); result.Account = reader.GetString(4); result.OrderIdTypeValue = (OrderIdType)reader.GetByte(5); } } } return(result); }
public ParkCarType QueryCarTypesByCarTypeName(string parkingId, string carTypeName) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ID,CarTypeID,CarTypeName,PKID,BaseTypeID,RepeatIn,RepeatOut"); strSql.Append(",AffirmIn,AffirmOut,InBeginTime,InEdnTime,MaxUseMoney,AllowLose,LpDistinguish,InOutEditCar"); strSql.Append(",InOutTime,CarNoLike,LastUpdateTime,HaveUpdate,IsAllowOnlIne,Amount,MaxMonth,MaxValue,DataStatus"); strSql.Append(",OverdueToTemp,LotOccupy,Deposit,MonthCardExpiredEnterDay,AffirmBegin,AffirmEnd,IsNeedCapturePaper,IsNeedAuthentication,IsDispatch,OnlineUnit,IsIgnoreHZ FROM ParkCarType"); strSql.Append(" WHERE PKID =@PKID AND DataStatus!=@DataStatus and CarTypeName=@CarTypeName"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("CarTypeName", carTypeName); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); dbOperator.AddParameter("PKID", parkingId); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(DataReaderToModel <ParkCarType> .ToModel(reader)); } return(null); } } }
/// <summary> /// 获取订单 /// </summary> /// <param name="parkingid">车场编号</param> /// <param name="boxid">岗亭编号</param> /// <param name="starttime">开始时间</param> /// <param name="endtime">结束时间</param> /// <returns></returns> public List <ParkOrder> GetOrdersByGateID(string parkingid, string gateid, DateTime starttime, DateTime endtime) { List <ParkOrder> orderlist = new List <ParkOrder>(); string strSql = string.Format(@"select p.PayWay,p.Amount,p.UnPayAmount,p.PayAmount,p.DiscountAmount,p.OrderType OrderType from parkorder p left join parkiorecord i on i.recordid=p.tagid where p.status=1 and i.exitgateid=@gateid and p.pkid=@parkingid and p.DataStatus!=2 and p.ordertime>=@starttime and p.ordertime<=@endtime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("parkingid", parkingid); dbOperator.AddParameter("gateid", gateid); dbOperator.AddParameter("starttime", starttime); dbOperator.AddParameter("endtime", endtime); using (DbDataReader reader = dbOperator.ExecuteReader(strSql)) { while (reader.Read()) { orderlist.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } return(orderlist); }
/// <summary> /// 获取当班差异订单 /// </summary> /// <param name="startTime"></param> /// <param name="endtime"></param> /// <param name="userid"></param> /// <param name="ErrorMessage"></param> /// <returns></returns> public List <ParkOrder> GetDifferenceOrder(DateTime startTime, DateTime endtime, string userid, out string ErrorMessage) { List <ParkOrder> ParkOrders = new List <ParkOrder>(); ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select * from ParkOrder where UserID=@UserID and amount!=payamount and DataStatus!=@DataStatus and (OrderType=1 or OrderType=6 or OrderType=7 or OrderType=8) and OrderTime>@StartTime and OrderTime<@EndTime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("UserID", userid); dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("EndTime", endtime); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(ParkOrders); }
public List <ParkOrder> GetOrderByTimeseriesIDByStatus(string timeseriesID, int status, out string ErrorMessage) { List <ParkOrder> ParkOrders = new List <ParkOrder>(); ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select * from ParkOrder where DataStatus!=@DataStatus and Status=@Status and TagID=@TagID and (OrderType=7 or OrderType=8) "); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("TagID", timeseriesID); dbOperator.AddParameter("Status", status); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(ParkOrders); }
public List <ParkOrder> GetOrderByStatus(DateTime startTime, DateTime endtime, out string ErrorMessage) { List <ParkOrder> ParkOrders = new List <ParkOrder>(); ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select a.*,b.ReleaseType from ParkOrder a left join ParkIORecord b on a.TagID=b.RecordID where (Status=1 or Status=2) and a.DataStatus!=@DataStatus and OrderTime>@StartTime and OrderTime<@EndTime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("EndTime", endtime); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(ParkOrders); }
public List <ParkEvent> GetEventRecYC(DateTime startTime, DateTime endtime) { List <ParkEvent> ParkEvents = new List <ParkEvent>(); try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select * from ParkEvent where (EventID=13 or EventID=19) and DataStatus!=@DataStatus and RecTime>@StartTime and RecTime<@EndTime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("EndTime", endtime); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkEvents.Add(DataReaderToModel <ParkEvent> .ToModel(reader)); } } } } catch (Exception e) { } return(ParkEvents); }
public List <BasePassRemark> QueryByParkingId(string parkingId, Entities.PassRemarkType?passType) { using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ID,RecordID,PassType,Remark,PKID,LastUpdateTime,HaveUpdate,DataStatus from BasePassRemark"); strSql.Append(" where PKID=@PKID and DataStatus!=@DataStatus"); dbOperator.ClearParameters(); dbOperator.AddParameter("PKID", parkingId); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); if (passType.HasValue) { strSql.Append(" and PassType=@PassType"); dbOperator.AddParameter("PassType", (int)passType.Value); } using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { List <BasePassRemark> models = new List <BasePassRemark>(); while (reader.Read()) { models.Add(DataReaderToModel <BasePassRemark> .ToModel(reader)); } return(models); } } }
public List <ParkDerate> QueryBySellerID(string sellerId) { List <ParkDerate> models = new List <ParkDerate>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ParkDerate where SellerID= @SellerID and DataStatus!=@DataStatus"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("SellerID", sellerId); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { models.Add(DataReaderToModel <ParkDerate> .ToModel(reader)); } } } foreach (var item in models) { item.DerateIntervar = QueryParkDerateIntervar(item.DerateID); } return(models); }
public Model.RemindSetting QuerySetting(Guid user) { var carriers = new List <string>(); var statuses = new List <Model.RemindStatus>(); var sql = "SELECT Carrier,Status FROM dbo.T_OrderRemindSetting WHERE [User]=@USER"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("USER", user); using (var reader = dbOperator.ExecuteReader(sql)) { if (reader.Read()) { if (!reader.IsDBNull(0)) { var carrierArray = reader.GetString(0).Split('|'); foreach (var item in carrierArray) { carriers.Add(item); } } if (!reader.IsDBNull(1)) { var statusValues = getStatusValues(reader.GetString(1)); foreach (var item in statusValues) { statuses.Add((RemindStatus)item); } } } } } return(new RemindSetting(carriers, statuses)); }
public ParkReserveBit GetCanUseParkReserveBit(string parkID, string plateNumber, out string errorMsg) { ParkReserveBit parkReserveBit = null; errorMsg = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ParkReserveBit where PlateNumber=@PlateNumber and PKID=@PKID and DataStatus!=@DataStatus and Status=0"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("PlateNumber", plateNumber); dbOperator.AddParameter("PKID", parkID); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { parkReserveBit = DataReaderToModel <ParkReserveBit> .ToModel(reader); } } } } catch (Exception e) { errorMsg = e.Message; } return(parkReserveBit); }
public int GetAreaCarNum(string areaID, out string ErrorMessage) { ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT COUNT(*) from ParkTimeseries t LEFT JOIN parkiorecord i on t.IORecordID=i.RecordID LEFT JOIN parkgate g on t.EnterGateID = g.GateID LEFT JOIN parkbox b on b.BoxID = g.BoxID where b.AreaID =@AreaID and t.IsExit = 0 and t.DataStatus != @DataStatus "); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("AreaID", areaID); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(reader[0].ToString().ToInt()); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(0); }
public List <ParkTimeseries> GetAllExitsTimeseriesesByIORecordID(string parkid, string iorecordID, out string ErrorMessage) { List <ParkTimeseries> ParkTimeserieses = new List <ParkTimeseries>(); ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ParkTimeseries where ParkingID=@ParkingID and IORecordID=@IORecordID and IsExit=1 and DataStatus!=@DataStatus"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("ParkingID", parkid); dbOperator.AddParameter("IORecordID", iorecordID); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkTimeserieses.Add(DataReaderToModel <ParkTimeseries> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(ParkTimeserieses); }
public DataTransferObject.Organization.WithholdingView GetWithholding(WithholdingAccountType accountType, Guid company) { DataTransferObject.Organization.WithholdingView withholding = null; //[AccountNo] = @AccountNo AND string sql = "SELECT [AccountNo],[AccountType],[Time],[Status],[Amount],[Company] FROM [T_Withholding] WHERE [AccountType]=@AccountType AND [Company] = @Company "; using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { //dbOperator.AddParameter("AccountNo", withholdingView.AccountNo); dbOperator.AddParameter("AccountType", accountType); dbOperator.AddParameter("Company", company); using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader(sql)) { if (reader.Read()) { withholding = new DataTransferObject.Organization.WithholdingView(); withholding.AccountNo = reader.GetString(0); withholding.AccountType = (WithholdingAccountType)reader.GetByte(1); withholding.Time = reader.GetDateTime(2); withholding.Status = (WithholdingProtocolStatus)reader.GetByte(3); withholding.Amount = reader.GetDecimal(4); withholding.Company = reader.GetGuid(5); } } } return(withholding); }
public List <Domain.CompanyDrawdition> QueryByOwerId(Guid owerId) { string sql = "SELECT Id,OwnerId,Title,Context,Type FROM T_CompanyDrawCondition WHERE OwnerId=@OwnerId "; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("OwnerId", owerId); using (var reader = dbOperator.ExecuteReader(sql)) { List <Domain.CompanyDrawdition> list = new List <Domain.CompanyDrawdition>(); while (reader.Read()) { Domain.CompanyDrawdition dition = new Domain.CompanyDrawdition(); dition.Id = reader.GetGuid(0); dition.OwnerId = reader.GetGuid(1); dition.Title = reader.GetString(2); dition.Context = reader.GetString(3); dition.Type = reader.GetByte(4); list.Add(dition); } return(list); } } }
public ParkOrder GetCashMoneyCountByPlateNumber(string parkingID, OrderType orderType, string plateNumber, DateTime startTime, DateTime endtime, out string ErrorMessage) { ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@" SELECT * from ParkOrder where id = (SELECT max(O.id) FROM ParkOrder O left outer join ParkIOrecord I on O.TagID = I.RecordID WHERE O.status = 1 and O.CashTime >= @StartTime and O.CashTime <= @EndTime and O.PKID = @PKID and O.OrderType =@OrderType and O.DataStatus != @DataStatus and I.PlateNumber =@PlateNumber) "); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("EndTime", endtime); dbOperator.AddParameter("PKID", parkingID); dbOperator.AddParameter("OrderType", (int)orderType); dbOperator.AddParameter("PlateNumber", plateNumber); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(null); }
public IEnumerable <Data.DataMapping.Configuration> QueryConfigurations(Guid companyId) { var result = new List <Data.DataMapping.Configuration>(); string sql = @"SELECT [Id],[OfficeNumber],[Type],[Login],[Password],[Host],[Port],[SI],[PrinterSN] FROM [dbo].[T_Configuration] WHERE [Company]=@CompanyId"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("CompanyId", companyId); using (var reader = dbOperator.ExecuteReader(sql)) { while (reader.Read()) { var configuration = new Data.DataMapping.Configuration(); configuration.Company = companyId; configuration.Id = reader.GetGuid(0); configuration.OfficeNumber = reader.GetString(1); configuration.Type = reader.GetInt32(2); configuration.Login = reader.GetString(3); configuration.Password = reader.GetString(4); configuration.Host = reader.GetString(5); configuration.Port = reader.GetInt32(6); configuration.SI = reader.GetString(7); if (!reader.IsDBNull(8)) { configuration.PrinterSN = reader.GetInt32(8); } result.Add(configuration); } } } return(result); }
public ParkOrder GetTimeseriesOrderChareFeeCount(string userID, OrderType OrderType, DateTime dt, int releaseType, out string ErrorMessage) { ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT ISNULL(sum(O.Amount),0) as Amount,ISNULL(sum(O.PayAmount),0) as PayAmount ,ISNULL(sum(O.UnPayAmount),0) as UnPayAmount,ISNULL(sum(O.DiscountAmount),0) as DiscountAmount FROM ParkOrder O left outer JOIN ParkTimeseries I on O.TagID=I.TimeseriesID WHERE O.OrderTime>@OrderTime and O.UserID=@UserID and O.OrderType=@OrderType and O.DataStatus!=@DataStatus and I.ReleaseType=@ReleaseType and Status=1 "); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("UserID", userID); dbOperator.AddParameter("OrderType", (int)OrderType); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); dbOperator.AddParameter("OrderTime", dt); dbOperator.AddParameter("ReleaseType", releaseType); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(null); }
public Data.DataMapping.Configuration QueryConfiguration(Guid id) { Data.DataMapping.Configuration configuration = null; string sql = @"SELECT [Company],[OfficeNumber],[Type],[Login],[Password],[Host],[Port],[SI],[PrinterSN] FROM [dbo].[T_Configuration] WHERE [Id]=@Id"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("Id", id); using (var reader = dbOperator.ExecuteReader(sql)) { while (reader.Read()) { configuration = new Data.DataMapping.Configuration(); configuration.Id = id; configuration.Company = reader.GetGuid(0); configuration.OfficeNumber = reader.GetString(1); configuration.Type = reader.GetInt32(2); configuration.Login = reader.GetString(3); configuration.Password = reader.GetString(4); configuration.Host = reader.GetString(5); configuration.Port = reader.GetInt32(6); configuration.SI = reader.GetString(7); if (!reader.IsDBNull(8)) { configuration.PrinterSN = reader.GetInt32(8); } } } } return(configuration); }
public decimal QueryMonthExpiredNotPayAmount(DateTime start, DateTime end, string parkingId, List <string> plateNumbers) { StringBuilder strSql = new StringBuilder(); strSql.Append("select isnull(sum(o.PayAmount),0) PayAmount from ParkIORecord p inner join ParkOrder o on p.RecordID=o.TagID"); strSql.Append(" where p.IsExit=1 and p.EnterType=1 and o.Status=4"); strSql.Append(" and o.OrderTime>=@StartDate and o.OrderTime<=@EndDate and p.DataStatus!=@DataStatus and o.DataStatus!=@DataStatus"); strSql.AppendFormat(" and p.PlateNumber in('{0}') and p.ParkingID=@ParkingID", string.Join("','", plateNumbers)); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("StartDate", start.ToString("yyyy-MM-dd HH:mm:ss")); dbOperator.AddParameter("EndDate", end.ToString("yyyy-MM-dd HH:mm:ss")); dbOperator.AddParameter("ParkingID", parkingId); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { if (reader.Read()) { return(reader.GetDecimalDefaultZero(0)); } return(0); } } }
public IEnumerable <Operation> Query() { List <Operation> result = null; string sql = @"select Id, Name, ConfigurationType, RuleType, Description from Operations"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { using (var reader = dbOperator.ExecuteReader(sql)) { result = new List <Operation>(); while (reader.Read()) { int id = reader.GetInt16(0); string name = reader.GetString(1); string configurationType = reader.GetString(2); short a = reader.GetByte(3); RuleType ruleType = (RuleType)a; string description = reader.GetString(4); Operation operation = new Operation(id, name, configurationType, ruleType, description); result.Add(operation); } } } return(result); }
/// <summary> /// 通过通道获取订单 /// </summary> /// <param name="parkingid"></param> /// <param name="boxid"></param> /// <param name="starttime"></param> /// <param name="endtime"></param> /// <returns></returns> public List <ParkOrder> GetOrdersByBoxID(string parkingid, string boxid, DateTime starttime, DateTime endtime) { List <ParkOrder> orderlist = new List <ParkOrder>(); string strSql = string.Format(@"select p.PayWay,p.Amount,p.UnPayAmount,p.PayAmount,p.OrderType OrderType from parkorder p where p.status=1 and tagid in (select recordid from parkiorecord where parkingid=@parkingid and DataStatus!=2 and exitgateid in (select GateID from parkgate where IoState=2 and boxid=@boxid)) and p.DataStatus!=2 and p.ordertime>=@starttime and p.ordertime<=@endtime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("parkingid", parkingid); dbOperator.AddParameter("boxid", boxid); dbOperator.AddParameter("starttime", starttime); dbOperator.AddParameter("endtime", endtime); using (DbDataReader reader = dbOperator.ExecuteReader(strSql)) { while (reader.Read()) { orderlist.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } return(orderlist); }
IEnumerable <PNRHistory> IHistoryRepository.Query(DateTime startTime, DateTime endTime) { List <PNRHistory> pnrHistories = default(List <PNRHistory>); string sql = @"select ThreadId, GenerateTime, PNRCode, OfficeNo, Status " + @" from PNRHistories " + @" where GenerateTime between @StartTime and @EndTime"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("EndTime", endTime); using (var reader = dbOperator.ExecuteReader(sql)) { pnrHistories = new List <PNRHistory>(); while (reader.Read()) { int threadId = reader.GetInt32(0); DateTime generateTime = reader.GetDateTime(1); string pnrCode = reader.GetString(2); string officeNo = reader.GetString(3); byte status = reader.GetByte(4); PNRHistory pnrHistory = new PNRHistory(threadId, generateTime, pnrCode, officeNo, status); pnrHistories.Add(pnrHistory); } } } return(pnrHistories); }
public List <ParkOrder> GetOrderByCarDerateID(DateTime startTime, string carDerateID, out string ErrorMessage) { List <ParkOrder> ParkOrders = new List <ParkOrder>(); ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT * from ParkOrder where DataStatus != @DataStatus and CarderateID =@CarderateID and Status=1 and OrderTime>=@StartTime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("CarderateID", carDerateID); dbOperator.AddParameter("StartTime", startTime); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { while (reader.Read()) { ParkOrders.Add(DataReaderToModel <ParkOrder> .ToModel(reader)); } } } } catch (Exception e) { ErrorMessage = e.Message; } return(ParkOrders); }
public PNRHistory Query(string pnrHistoryPNRCode) { PNRHistory pnrHistory = null; // 编码有可能重复,提取时间最近的一个; string sql = @"select top 1 ThreadId, GenerateTime, PNRCode, OfficeNo, Status from PNRHistories " + @" where PNRCode = @PNRCode order by GenerateTime desc"; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("PNRCode", pnrHistoryPNRCode); using (var reader = dbOperator.ExecuteReader(sql)) { if (reader.Read()) { int threadId = reader.GetInt32(0); DateTime generateTime = reader.GetDateTime(1); string pnrCode = reader.GetString(2); string officeNo = reader.GetString(3); byte status = reader.GetByte(4); pnrHistory = new PNRHistory(threadId, generateTime, pnrCode, officeNo, status); } } } return(pnrHistory); }
public List <ParkCarType> QueryParkCarTypeByParkingIds(List <string> parkingIds) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ID,CarTypeID,CarTypeName,PKID,BaseTypeID,RepeatIn,RepeatOut"); strSql.Append(",AffirmIn,AffirmOut,InBeginTime,InEdnTime,MaxUseMoney,AllowLose,LpDistinguish,InOutEditCar"); strSql.Append(",InOutTime,CarNoLike,LastUpdateTime,HaveUpdate,IsAllowOnlIne,Amount,MaxMonth,MaxValue,DataStatus"); strSql.Append(",OverdueToTemp,LotOccupy,Deposit,MonthCardExpiredEnterDay,AffirmBegin,AffirmEnd,IsNeedCapturePaper,IsNeedAuthentication,IsDispatch,OnlineUnit,IsIgnoreHZ FROM ParkCarType"); strSql.AppendFormat(" WHERE PKID IN('{0}') AND DataStatus!=@DataStatus", string.Join("','", parkingIds)); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { List <ParkCarType> lists = new List <ParkCarType>(); while (reader.Read()) { lists.Add(DataReaderToModel <ParkCarType> .ToModel(reader)); } return(lists); } } }
public IEnumerable <PNRHistory> Query() { List <PNRHistory> pnrHistories = default(List <PNRHistory>); string sql = @"select ThreadId, GenerateTime, PNRCode, OfficeNo, Status from PNRHistories "; using (var dbOperator = new DbOperator(Provider, ConnectionString)) { using (var reader = dbOperator.ExecuteReader(sql)) { pnrHistories = new List <PNRHistory>(); while (reader.Read()) { int threadId = reader.GetInt32(0); DateTime generateTime = reader.GetDateTime(1); string pnrCode = reader.GetString(2); string officeNo = reader.GetString(3); byte status = reader.GetByte(4); PNRHistory pnrHistory = new PNRHistory(threadId, generateTime, pnrCode, officeNo, status); pnrHistories.Add(pnrHistory); } } } return(pnrHistories); }
public IEnumerable <KeyValuePair <Guid, Domain.Check_In> > Query() { string sql = "SELECT Id,AirlineName,AirlineCode,Remark,OperatingHref,Opertor,[Time] FROM T_CheckIn"; IList <KeyValuePair <Guid, Domain.Check_In> > result = new List <KeyValuePair <Guid, Domain.Check_In> >(); using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { using (DbDataReader reader = dbOperator.ExecuteReader(sql)) { while (reader.Read()) { result.Add(new KeyValuePair <Guid, Domain.Check_In>(reader.GetGuid(0), new Domain.Check_In { Id = reader.GetGuid(0), AirlineName = reader.GetString(1), AirlineCode = reader.GetString(2), Remark = reader.IsDBNull(3) ? string.Empty : reader.GetString(3), OperatingHref = reader.GetString(4), Opertor = reader.GetString(5), Time = reader.GetDateTime(6) })); } } } return(result); }
public DateTime GetLastRecordExitDate(string parkingID, string iorecordid, out string ErrorMessage) { ErrorMessage = ""; try { StringBuilder strSql = new StringBuilder(); strSql.Append(@"select top 1 ExitTime from ParkTimeseries where IORecordID=@IORecordID and ParkingID=@ParkingID and DataStatus!=@DataStatus order by ExitTime"); using (DbOperator dbOperator = ConnectionManager.CreateReadConnection()) { dbOperator.ClearParameters(); dbOperator.AddParameter("ParkingID", parkingID); dbOperator.AddParameter("IORecordID", iorecordid); dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete); using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString())) { return(reader[0].ToString().ToDateTime()); } } } catch (Exception e) { ErrorMessage = e.Message; } return(DateTime.MinValue); }