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