public List <AdvanceParking> QueryPage(string companyId, string plateNo, DateTime?start, DateTime?end, int pageIndex, int pageSize, out int recordTotalCount)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT ID,OrderId,PlateNo,StartTime,EndTime,Amount,WxOpenId,CreateTime,OrderState,PayTime,PrepayId,SerialNumber,CompanyID FROM AdvanceParking WHERE OrderState=1");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
if (!string.IsNullOrEmpty(plateNo))
{
sql.Append(" AND PlateNo like @PlateNo");
dbOperator.AddParameter("PlateNo", "%" + plateNo + "%");
}
if (start.HasValue)
{
sql.AppendFormat(" AND CreateTime >= '{0}'", start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (end.HasValue)
{
sql.AppendFormat(" AND CreateTime <= '{0}'", end.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(companyId))
{
sql.Append(" AND CompanyID =@CompanyID");
dbOperator.AddParameter("CompanyID", companyId);
}
List <AdvanceParking> models = new List <AdvanceParking>();
using (DbDataReader reader = dbOperator.Paging(sql.ToString(), " ID DESC", pageIndex, pageSize, out recordTotalCount))
{
while (reader.Read())
{
models.Add(DataReaderToModel <AdvanceParking> .ToModel(reader));
}
}
return(models);
}
}
public EmployeePlate Query(string plateId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * from EmployeePlate where PlateID= @PlateID and DataStatus!=@DataStatus");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PlateID", plateId);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <EmployeePlate> .ToModel(reader));
}
else
{
return(null);
}
}
}
}
public ParkMonthlyCarApply QueryByRecordID(string recordId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select *,p.PKName,c.CarTypeName,m.CarModelName from ParkMonthlyCarApply a ");
strSql.Append(" inner join BaseParkinfo p on a.PKID=p.PKID");
strSql.Append(" inner join ParkCarType c on c.CarTypeID=a.CarTypeID");
strSql.Append(" inner join ParkCarModel m on m.CarModelID=a.CarModelID");
strSql.Append(" where a.RecordID=@RecordID");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", recordId);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkMonthlyCarApply> .ToModel(reader));
}
return(null);
}
}
}
/// <summary>
/// 获取所有线上支付的金额
/// </summary>
/// <param name="PKID">车场编号</param>
/// <param name="StartTime">开始时间</param>
/// <param name="EndTime">结束时间</param>
/// <returns></returns>
//public List<ParkOrder> GetSettlementPayAmount(string PKID, DateTime StartTime, DateTime EndTime)
public List <Statistics_Gather> GetSettlementPayAmount(string PKID, DateTime StartTime, DateTime EndTime)
{
//List<ParkOrder> orderlist = new List<ParkOrder>();
//string strSql = "select RecordID,OrderNo,PayAmount from parkorder where pkid=@PKID and ordertime>=@StartTime and ordertime<=@EndTime and status=1 and DataStatus!=2 and payway!=1";
List <Statistics_Gather> orderlist = new List <Statistics_Gather>();
string strSql = "select OnLine_Amount from Statistics_Gather where parkingid=@PKID and gathertime>=@StartTime and gathertime<=@EndTime";
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PKID", PKID);
dbOperator.AddParameter("StartTime", StartTime);
dbOperator.AddParameter("EndTime", EndTime);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
//orderlist.Add(DataReaderToModel<ParkOrder>.ToModel(reader));
orderlist.Add(DataReaderToModel <Statistics_Gather> .ToModel(reader));
}
}
}
return(orderlist);
}
public List <ParkGate> QueryByParkingId(string parkingId)
{
List <ParkGate> gatelist = new List <ParkGate>();
StringBuilder strSql = new StringBuilder();
strSql.Append("select g.* from ParkGate g");
strSql.Append(" left join ParkBox b on g.BoxID=b.BoxID");
strSql.Append(" left join ParkArea a on a.AreaID=b.AreaID");
strSql.Append(" where a.PKID=@ParkingID and g.DataStatus!=@DataStatus and a.DataStatus!=@DataStatus and b.DataStatus!=@DataStatus");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("ParkingID", parkingId);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
gatelist.Add(DataReaderToModel <ParkGate> .ToModel(reader));
}
}
}
return(gatelist);
}
public List <BaseCard> QueryBaseCardByEmployeeMobile(string villageId, string mobile)
{
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
StringBuilder strSql = new StringBuilder();
strSql.Append("select c.*,e.EmployeeName,e.MobilePhone from BaseCard c inner join BaseEmployee e on c.EmployeeID= e.EmployeeID ");
strSql.Append(" where c.VID=@VID and e.MobilePhone=@MobilePhone and c.DataStatus!=@DataStatus and e.DataStatus!=@DataStatus");
dbOperator.AddParameter("VID", villageId);
if (!string.IsNullOrWhiteSpace(mobile))
{
strSql.Append(" and MobilePhone=@MobilePhone");
dbOperator.AddParameter("MobilePhone", mobile);
}
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
return(DataReaderToModel <List <BaseCard> > .ToModel(reader));
}
}
}
public List <ParkSettlementApprovalFlowModel> GetSettlementApprovalFlows(IList <string> villIDs)
{
//return null;
List <ParkSettlementApprovalFlowModel> flows = new List <ParkSettlementApprovalFlowModel>();
//string strSql = "select p.*,u.username FlowUserName from ParkSettlementApprovalFlow p left join sysuser u on u.recordid=p.FlowUser where p.pkid=@PKID order by p.flowid asc";
string strSql = "select p.*,u.username FlowUserName from ParkSettlementApprovalFlow p left join sysuser u on u.recordid=p.FlowUser where 1=1 ";
strSql += " and p.pkid in (select PKID from BaseParkInfo where VID in('" + string.Join("','", villIDs) + "'))";
strSql += "order by p.flowid asc";
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
//dbOperator.AddParameter("PKID", PKID);
//dbOperator.AddParameter("PKID", villIDs);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
flows.Add(DataReaderToModel <ParkSettlementApprovalFlowModel> .ToModel(reader));
}
}
}
return(flows);
}
/// <summary>
/// 上传车场系统数据
/// </summary>
public static void StartClearClientData()
{
while (IsStartClear)
{
if (DateTime.Now.Hour == 3 || !_IsDS)
{
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
string strsql = "select * from BaseParkinfo a left join BaseVillage b on a.VID=b.VID ";
strsql += "where a.DataStatus<2 and b.DataStatus<2";
if (_IsDS)
{
strsql += "and b.ProxyNo='" + SystemInfo.ProxyNo + "'";
}
dbOperator.ClearParameters();
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
while (reader.Read())
{
BaseParkinfo model = DataReaderToModel <BaseParkinfo> .ToModel(reader);
listparkinfo.Add(model);
}
}
}
ClearPic();
ClearParkEvent();
ClearParkIORecord();
ClearParkOrder();
ClearParkTimeseries();
}
Thread.Sleep(1000 * 60 * 30);
}
}
/// <summary>
/// 获得所有结算单
/// </summary>
/// <param name="PKID">车场编号</param>
/// <param name="SettleStatus">结算单状态</param>
/// <param name="Priod">帐期</param>
/// <returns></returns>
public List <ParkSettlementModel> GetSettlements(string PKID, int SettleStatus, string Priod, string UserID)
{
List <ParkSettlementModel> settlements = new List <ParkSettlementModel>();
StringBuilder strSql = new StringBuilder();
// strSql.Append(@"select s.ID,s.RecordID,s.PKID,s.Priod,s.SettleStatus,s.TotalAmount,s.HandlingFeeAmount,s.ReceivableAmount,s.StartTime,s.EndTime,s.PayTime,s.CreateTime,s.Receipt,s.CreateUser,p.pkname ParkName,u.UserName,s.Remark from parksettlement s left join baseparkinfo p on s.pkid=p.pkid left join sysuser u on u.RecordID=s.CreateUser where s.pkid=@PKID and settlestatus in (
//select flowid from ParkSettlementApprovalFlow where flowuser=@userid
// and pkid=@PKID)");
// if (SettleStatus != -1)
// {
// strSql.Append(" and settlestatus=@settlestatus");
// }
// if (Priod != "-1")
// {
// strSql.Append(" and Priod=@Priod");
// }
// StringBuilder strSql1 = new StringBuilder();
// strSql1.Append(@"select s.ID,s.RecordID,s.PKID,s.Priod,s.SettleStatus,s.TotalAmount,s.HandlingFeeAmount,s.ReceivableAmount,s.StartTime,s.EndTime,s.PayTime,s.CreateTime,s.Receipt,s.CreateUser,p.pkname ParkName,u.UserName,s.Remark from parksettlement s left join baseparkinfo p on s.pkid=p.pkid left join sysuser u on u.RecordID=s.CreateUser where s.pkid=@PKID and createuser=@UserID");
// if (SettleStatus != -1)
// {
// strSql1.Append(" and settlestatus=@settlestatus");
// }
// if (Priod != "-1")
// {
// strSql1.Append(" and Priod=@Priod");
// }
// string s = strSql.ToString() + " union " + strSql1.ToString();
strSql.Append(@"select s.ID,s.RecordID,s.PKID,s.Priod,s.SettleStatus,s.TotalAmount,s.HandlingFeeAmount,s.ReceivableAmount,s.StartTime,s.EndTime,s.PayTime,s.CreateTime,s.Receipt,s.CreateUser,p.pkname ParkName,u.UserName,s.Remark from parksettlement s left join baseparkinfo p on s.pkid=p.pkid left join sysuser u on u.RecordID=s.CreateUser where s.pkid=@PKID");
if (SettleStatus != -1)
{
strSql.Append(" and settlestatus=@settlestatus");
}
if (Priod != "-1")
{
strSql.Append(" and Priod=@Priod");
}
List <ParkSettlementApprovalFlowModel> approvals = new ParkSettlementApprovalFlowDAL().GetSettlementApprovalFlows(PKID);
//判断当前帐户是不是项目帐户
var companys = new CompanyDAL().QueryTopCompany();
List <Entities.SysUser> users = new List <Entities.SysUser> ();
if (companys != null)
{
users = new SysUserDAL().QuerySysUserByCompanys(companys.Select(u => u.CPID).ToList());
}
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PKID", PKID);
dbOperator.AddParameter("SettleStatus", SettleStatus);
dbOperator.AddParameter("Priod", Priod);
dbOperator.AddParameter("userid", UserID);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
while (reader.Read())
{
ParkSettlementModel sm = DataReaderToModel <ParkSettlementModel> .ToModel(reader);
if (sm.CreateTime > DateTime.MinValue)
{
sm.CreateTimeName = sm.CreateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.EndTime > DateTime.MinValue)
{
sm.EndTimeName = sm.EndTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.PayTime > DateTime.MinValue)
{
sm.PayTimeName = sm.PayTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.StartTime > DateTime.MinValue)
{
sm.StartTimeName = sm.StartTime.ToString("yyyy-MM-dd HH:mm:ss");
}
//申请人为当前人
//if (UserID == sm.CreateUser)
//{
// if (sm.SettleStatus != 0)
// {
// if (!IsApprovalFlow(PKID, UserID, sm.SettleStatus))
// {
// sm.IsHide = true;
// }
// }
//}
if (sm.SettleStatus == 0)
{
var appro = approvals.Find(u => u.FlowID == 0);
if (appro != null)
{
if (appro.FlowUser == UserID)
{
sm.IsHide = false;
}
else
{
sm.IsHide = true;
}
}
else
{
sm.IsHide = true;
}
}
else if (sm.SettleStatus == 2 || sm.SettleStatus == -1)
{
sm.IsHide = true;
}
else
{
//如果是平台用户,则不可见
if (users.Find(u => u.RecordID == UserID) != null)
{
sm.IsHide = true;
}
else
{
sm.IsHide = false;
}
}
settlements.Add(sm);
}
}
}
return(settlements);
}
public ParkEvent AddEventRec(ParkEvent model, out string ErrorMessage)
{
ErrorMessage = "";
try
{
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
model.DataStatus = (int)DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
model.RecordID = GuidGenerator.GetGuidString();
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into ParkEvent(RecordID,CardNo,CardNum,CarModelID,CarTypeID,DataStatus,EmployeeName,EventID,GateID,HaveUpdate,IORecordID,IOState,LastUpdateTime,OperatorID,ParkingID,PictureName,PlateColor,PlateNumber,RecTime,Remark,IsScanCode,IsOffline,CertNo,CertName,CertificateImage,Address,BirthDate,Nation,Sex)");
strSql.Append(" values(@RecordID,@CardNo,@CardNum,@CarModelID,@CarTypeID,@DataStatus,@EmployeeName,@EventID,@GateID,@HaveUpdate,@IORecordID,@IOState,@LastUpdateTime,@OperatorID,@ParkingID,@PictureName,@PlateColor,@PlateNumber,@RecTime,@Remark,@IsScanCode,@IsOffline,@CertNo,@CertName,@CertificateImage,@Address,@BirthDate,@Nation,@Sex);");
strSql.Append(" select * from ParkEvent where RecordID=@RecordID ");
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", model.RecordID);
dbOperator.AddParameter("CardNo", model.CardNo);
dbOperator.AddParameter("CardNum", model.CardNum);
dbOperator.AddParameter("CarModelID", model.CarModelID);
dbOperator.AddParameter("CarTypeID", model.CarTypeID);
dbOperator.AddParameter("DataStatus", model.DataStatus);
dbOperator.AddParameter("EmployeeName", model.EmployeeName);
dbOperator.AddParameter("EventID", model.EventID);
dbOperator.AddParameter("GateID", model.GateID);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("IORecordID", model.IORecordID);
dbOperator.AddParameter("IOState", model.IOState);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("OperatorID", model.OperatorID);
dbOperator.AddParameter("ParkingID", model.ParkingID);
dbOperator.AddParameter("PictureName", model.PictureName);
dbOperator.AddParameter("PlateColor", model.PlateColor);
dbOperator.AddParameter("PlateNumber", model.PlateNumber);
dbOperator.AddParameter("RecTime", model.RecTime);
dbOperator.AddParameter("Remark", model.Remark);
dbOperator.AddParameter("IsScanCode", model.IsScanCode);
dbOperator.AddParameter("IsOffline", model.IsOffline);
dbOperator.AddParameter("CertNo", model.CertNo);
dbOperator.AddParameter("CertName", model.CertName);
dbOperator.AddParameter("CertificateImage", model.CertificateImage);
dbOperator.AddParameter("Address", model.Address);
dbOperator.AddParameter("BirthDate", model.BirthDate);
dbOperator.AddParameter("Nation", model.Nation);
dbOperator.AddParameter("Sex", model.Sex);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkEvent> .ToModel(reader));
}
}
}
}
catch (Exception e)
{
ErrorMessage = e.Message;
}
return(null);
}
public List <ParkTimeseries> GetTimeseriesIORecordByLikeStrWhitPageTab(string parkingID, int pageSize, int pageIndex, List <string> cardTypeIDs, string likeStr, out int pageCount, out string ErrorMessage, int isExit = 0)
{
string strWhere = string.Format(" t.ParkingID='{0}' and t.DataStatus!=2 ", parkingID);
if (isExit == 0 || isExit == 1)
{
strWhere = strWhere + " and t.Isexit=" + isExit;
}
if (!likeStr.IsEmpty())
{
strWhere = string.Format("{0} and {1}", strWhere, likeStr);
}
if (cardTypeIDs != null && cardTypeIDs.Count > 0)
{
string cardids = "";
foreach (var item in cardTypeIDs)
{
if (item.IsEmpty())
{
continue;
}
cardids += "'" + item + "',";
}
if (!cardids.IsEmpty())
{
cardids = cardids.Substring(0, cardids.Length - 1);
strWhere = strWhere + " and i.CarTypeID in(" + cardids + ")";
}
}
ErrorMessage = "";
if (pageIndex < 1)
{
ErrorMessage = "pageIndex 从1开始";
}
pageCount = 0;
List <ParkTimeseries> modes = new List <ParkTimeseries>();
try
{
string tWhere = " 1=1 ";
if (strWhere.IsEmpty())
{
strWhere = tWhere;
}
string countSql = string.Format(@"Select count(t.ID) Count From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID WHERE {0} ", strWhere);
string sql = string.Format(@"Select top {1} * From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID Where t.ID>=(
Select Max(ID) From (Select top {0} t.ID From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID WHERE {2} Order By t.ID) As tmp ) and {2}; ", (pageIndex - 1) * pageSize + 1, pageSize, strWhere);
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
using (DbDataReader reader = dbOperator.ExecuteReader(countSql.ToString()))
{
if (reader.Read())
{
pageCount = reader[0].ToInt();
}
else
{
return(null);
}
}
}
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
using (DbDataReader reader = dbOperator.ExecuteReader(sql.ToString()))
{
ParkTimeseries timeseries = null;
ParkIORecord iorecord = null;
while (reader.Read())
{
timeseries = new ParkTimeseries();
iorecord = new ParkIORecord();
timeseries = DataReaderToModel <ParkTimeseries> .ToModel(reader);
iorecord = DataReaderToModel <ParkIORecord> .ToModel(reader);
timeseries.IORecord = iorecord;
modes.Add(timeseries);
}
}
}
}
catch (Exception e)
{
ErrorMessage = "服务异常!" + e.Message;
}
return(modes);
}
public List <ParkTimeseries> GetTimeseriesIORecordWhitPageTab(string parkingID, int pageSize, int pageIndex, List <string> carTypeIDs, string carModelID, string likePlateNumber, string ingateid, string outgateid, DateTime startTime, DateTime endTime, out int pageCount, out string ErrorMessage, int isExit = 0, int stayDay = -1)
{
string strWhere = string.Format(" t.ParkingID='{0}' and t.DataStatus!=2 ", parkingID);
if (isExit == 0 || isExit == 1)
{
strWhere = strWhere + " and t.Isexit=" + isExit;
}
if (!likePlateNumber.IsEmpty())
{
strWhere = string.Format("{0} and i.PlateNumber like '%{1}%'", strWhere, likePlateNumber);
}
if (!carModelID.IsEmpty())
{
strWhere = strWhere + " and i.CarModelID='" + carModelID + "'";
}
if (carTypeIDs != null && carTypeIDs.Count > 0)
{
string cardids = "";
foreach (var item in carTypeIDs)
{
if (item.IsEmpty())
{
continue;
}
cardids += item + ",";
}
if (!cardids.IsEmpty())
{
cardids = cardids.Substring(0, cardids.Length - 1);
strWhere = strWhere + " and i.CarTypeID in(" + cardids + ")";
}
}
if (!ingateid.IsEmpty())
{
strWhere = strWhere + " and t.EnterGateID=" + ingateid;
}
if (!outgateid.IsEmpty())
{
strWhere = strWhere + " and t.ExitGateID='" + outgateid + "'";
}
if (stayDay >= 0)
{
strWhere = string.Format("{0} and DateDiff(D,t.Entertime,'{1}')={2}", strWhere, DateTime.Now, stayDay);
}
if (isExit == 0)
{
strWhere = strWhere + " and t.Entertime>='" + startTime + "'";
strWhere = strWhere + " and t.Entertime<='" + endTime + "'";
}
else if (isExit == 1)
{
strWhere = strWhere + " and t.ExitTime>='" + startTime + "'";
strWhere = strWhere + " and t.ExitTime<='" + endTime + "'";
}
else
{
strWhere = strWhere + " and t.Entertime>='" + startTime + "'";
strWhere = strWhere + " and t.Entertime<='" + endTime + "'";
}
ErrorMessage = "";
if (pageIndex < 1)
{
ErrorMessage = "pageIndex 从1开始";
}
pageCount = 0;
List <ParkTimeseries> modes = new List <ParkTimeseries>();
try
{
string countSql = "";
string sql = "";
if (strWhere.IsEmpty())
{
strWhere = " 1=1 ";
}
countSql = string.Format(@"Select count(t.ID) Count From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID WHERE {0} ", strWhere);
sql = string.Format(@"Select top {1} * From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID Where t.ID>=(
Select Max(ID) From (Select top {0} t.ID From ParkTimeseries t left join ParkIORecord i on t.IORecordID=i.RecordID WHERE {2} Order By t.ID ) As tmp ) AND {2} Order by t.Entertime desc; ", (pageIndex - 1) * pageSize + 1, pageSize, strWhere);
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
using (DbDataReader reader = dbOperator.ExecuteReader(countSql))
{
if (reader.Read())
{
pageCount = reader.GetInt32(0);
}
}
}
using (DbOperator dbOperator = ConnectionManager.CreateConnection())
{
using (DbDataReader reader = dbOperator.ExecuteReader(sql))
{
while (reader.Read())
{
var timeseries = DataReaderToModel <ParkTimeseries> .ToModel(reader);
var iorecord = DataReaderToModel <ParkIORecord> .ToModel(reader);
timeseries.IORecord = iorecord;
modes.Add(timeseries);
}
}
}
}
catch (Exception e)
{
ErrorMessage = "服务异常!" + e.Message;
}
return(modes);
}
/// <summary>
/// 更具条件获取进出记录
/// </summary>
/// <param name="condition"></param>
/// <param name="pagesize"></param>
/// <param name="pageindex"></param>
/// <param name="total"></param>
/// <returns></returns>
public List <VisitorInfo> QueryPageInOut(Entities.Condition.VisitorInfoCondition condition, int pagesize, int pageindex, out int total)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select a.*,c.UserName,b.EntranceTime,d.GateName,e.GateName as ExitGateName,b.ExitTime, ");
strSql.Append(" b.EntranceImage,b.EntranceCertificateImage,b.ExitImage,b.ExitcertificateImage,f.Amount,f.PayAmount,f.UnPayAmount ");
strSql.Append(" from VisitorInfo a left join ParkIORecord b on a.RecordID=b.VisitorID ");
strSql.Append(" left join SysUser c on b.EntranceOperatorID=c.RecordID ");
strSql.Append(" left join ParkGate d on b.EntranceGateID=d.GateID ");
strSql.Append(" left join ParkGate e on b.ExitGateID=e.GateID ");
strSql.Append(" left join ParkOrder f on b.RecordID=f.TagID ");
strSql.Append(" where a.DataStatus!=2 and b.EntranceTime>=@StartTime and b.EntranceTime<=@EndTime and b.IsExit=1 and f.Status=1 ");
if (!string.IsNullOrWhiteSpace(condition.PlateNumber))
{
strSql.Append(" and PlateNumber like @PlateNumber ");
}
if (!string.IsNullOrWhiteSpace(condition.VisitorName))
{
strSql.Append(" and VisitorName like @VisitorName ");
}
if (condition.VisitorState != 0)
{
strSql.Append(" and VisitorState = @VisitorState ");
}
if (!string.IsNullOrWhiteSpace(condition.VisitorMobilePhone))
{
strSql.Append(" and VisitorMobilePhone like @VisitorMobilePhone ");
}
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("StartTime", condition.StartTime);
dbOperator.AddParameter("EndTime", condition.EndTime);
if (!string.IsNullOrWhiteSpace(condition.PlateNumber))
{
dbOperator.AddParameter("PlateNumber", "%" + condition.PlateNumber + "%");
}
if (!string.IsNullOrWhiteSpace(condition.VisitorName))
{
dbOperator.AddParameter("VisitorName", "%" + condition.VisitorName + "%");
}
if (!string.IsNullOrWhiteSpace(condition.VisitorMobilePhone))
{
dbOperator.AddParameter("VisitorMobilePhone", "%" + condition.VisitorMobilePhone + "%");
}
if (condition.VisitorState != -1)
{
dbOperator.AddParameter("VisitorState", condition.VisitorState);
}
using (DbDataReader reader = dbOperator.Paging(strSql.ToString(), "CreateTime DESC", pageindex, pagesize, out total))
{
List <VisitorInfo> models = new List <VisitorInfo>();
while (reader.Read())
{
models.Add(DataReaderToModel <VisitorInfo> .ToModel(reader));
}
return(models);
}
}
}
/// <summary>
/// 获取车牌进场信息
/// </summary>
/// <param name="PlateNumber"></param>
/// <param name="VID"></param>
/// <param name="SellerID"></param>
/// <returns></returns>
public static string GetPKIORecordByPlateNumber(string PlateNumber, string VID, string SellerID)
{
string striorecord = "";
List <ParkIORecord> list = new List <ParkIORecord>();
try
{
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
string strsql = string.Format(@"select a.*,b.PKName from ParkIORecord a left join BaseParkinfo b on a.parkingid=b.PKID
left join ParkCarType d on a.CarTypeID=d.CarTypeID where IsExit=0 and b.VID=@VID and a.DataStatus<2 and b.DataStatus<2
and a.PlateNumber like '%" + PlateNumber + "%' and (d.BaseTypeID=1 or d.BaseTypeID=3) ");
dbOperator.ClearParameters();
dbOperator.AddParameter("VID", VID);
//dbOperator.AddParameter("PlateNumber", "'%"+PlateNumber+"%'");
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
while (reader.Read())
{
ParkIORecord model = DataReaderToModel <ParkIORecord> .ToModel(reader);
using (DbOperator dbOperator2 = ConnectionManager.CreateReadConnection())
{
strsql = "select a.DerateID,a.CreateTime as DiscountTime from ParkCarDerate a left join ParkDerate b on a.DerateID=b.DerateID where a.IORecordID=@IORecordID and b.SellerID=@SellerID";
dbOperator2.ClearParameters();
dbOperator2.AddParameter("IORecordID", model.RecordID);
dbOperator2.AddParameter("SellerID", SellerID);
using (DbDataReader reader2 = dbOperator2.ExecuteReader(strsql.ToString()))
{
if (reader2.Read())
{
model.IsDiscount = true;
model.DiscountTime = reader2["DiscountTime"].ToDateTime();
model.DerateID = reader2["DerateID"].ToString();
}
else
{
model.IsDiscount = false;
}
}
}
if (Directory.Exists(SystemInfo.ImgPath))
{
if (File.Exists(SystemInfo.ImgPath + "\\" + model.EntranceImage))
{
try
{
MemoryStream mstream = new MemoryStream(File.ReadAllBytes(SystemInfo.ImgPath + "\\" + model.EntranceImage));
model.InimgData = System.Convert.ToBase64String(mstream.ToArray());
}
catch
{
}
}
}
list.Add(model);
}
striorecord = JsonHelper.GetJsonString(list);
}
}
}
catch (Exception ex)
{
logger.Fatal("获取车牌进场信息回调异常！GetPKIORecordByPlateNumberCallback()" + ex.Message + "\r\n");
}
return(striorecord);
}
/// <summary>
/// 消费打折
/// </summary>
/// <param name="IORecordID"></param>
/// <param name="DerateID"></param>
/// <param name="VID"></param>
/// <param name="SellerID"></param>
/// <param name="DerateMoney"></param>
/// <returns></returns>
public static string DiscountPlateNumber(string IORecordID, string DerateID, string VID, string SellerID, decimal DerateMoney)
{
ConsumerDiscountResult result = new ConsumerDiscountResult();
try
{
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
ParkIORecord IOmodel = null;
string strsql = "select * from ParkIORecord where RecordID=@RecordID and DataStatus<2";
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", IORecordID);
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
if (reader.Read())
{
IOmodel = DataReaderToModel <ParkIORecord> .ToModel(reader);
}
}
if (IOmodel != null)
{
if (IOmodel.IsExit)
{
result.Result = 3;
return(JsonHelper.GetJsonString(result));//车辆已出场
}
strsql = "select a.*,b.SellerID from ParkCarDerate a left join ParkDerate b on a.DerateID=b.DerateID where IORecordID=@IORecordID and b.SellerID=@SellerID and a.DataStatus<2 and Status!=3";
dbOperator.ClearParameters();
dbOperator.AddParameter("IORecordID", IORecordID);
dbOperator.AddParameter("SellerID", SellerID);
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
if (reader.Read())
{
result.Result = 4;
return(JsonHelper.GetJsonString(result));//已打折
}
}
ParkDerate derate = null;
strsql = "select * from ParkDerate where DataStatus<2 and DerateID=@DerateID";
dbOperator.ClearParameters();
dbOperator.AddParameter("DerateID", DerateID);
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
if (reader.Read())
{
derate = DataReaderToModel <ParkDerate> .ToModel(reader);
}
else
{
result.Result = 5;
return(JsonHelper.GetJsonString(result));//找不到优免规则
}
}
ParkSeller seller = null;
strsql = "select * from ParkSeller where DataStatus<2 and SellerID=@SellerID";
dbOperator.ClearParameters();
dbOperator.AddParameter("SellerID", SellerID);
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
if (reader.Read())
{
seller = DataReaderToModel <ParkSeller> .ToModel(reader);
}
else
{
result.Result = 6;
return(JsonHelper.GetJsonString(result));//找不到商家信息
}
}
decimal SumFreeMoney = 0;
strsql = "select SUM(FreeMoney) as SumFreeMoney from ParkCarDerate a left join ParkDerate b on a.DerateID=b.DerateID where a.DataStatus<2 and a.Status=1 and b.SellerID=@SellerID ";
dbOperator.ClearParameters();
dbOperator.AddParameter("SellerID", SellerID);
using (DbDataReader reader = dbOperator.ExecuteReader(strsql.ToString()))
{
if (reader.Read())
{
SumFreeMoney = reader["SumFreeMoney"].ToDecimal();
}
}
if (derate.DerateType == DerateType.TimesPayment || derate.DerateType == DerateType.TimePeriodAndTimesPayment)
{
DerateMoney = (decimal)derate.DerateMoney;
}
if (derate.DerateType != DerateType.ReliefTime && derate.DerateType != DerateType.VotePayment && derate.DerateType != DerateType.DayFree)
{
if (seller.Balance + seller.Creditline < DerateMoney + SumFreeMoney)
{
result.Result = 7;
return(JsonHelper.GetJsonString(result));//商家余额不足
}
}
else if (derate.DerateType == DerateType.DayFree)
{
if (seller.Balance + seller.Creditline < (DerateMoney * derate.DerateMoney) + SumFreeMoney)
{
result.Result = 7;
return(JsonHelper.GetJsonString(result));//商家余额不足
}
}
ParkCarDerate Carderate = new ParkCarDerate();
Carderate.AreaID = IOmodel.AreaID;
Carderate.CarDerateID = System.Guid.NewGuid().ToString();
Carderate.CarDerateNo = IdGenerator.Instance.GetId().ToString();
Carderate.CardNo = IOmodel.CardNo;
Carderate.CreateTime = DateTime.Now;
Carderate.DerateID = DerateID;
Carderate.ExpiryTime = DateTime.Now.AddDays(7);
Carderate.Status = CarDerateStatus.Used;
if (derate.DerateType != DerateType.ReliefTime && derate.DerateType != DerateType.VotePayment && derate.DerateType != DerateType.DayFree)
{
Carderate.FreeMoney = DerateMoney;
}
else if (derate.DerateType == DerateType.DayFree)
{
Carderate.FreeMoney = DerateMoney * derate.DerateMoney;
Carderate.ExpiryTime = IOmodel.EntranceTime.AddDays((int)DerateMoney);
}
else
{
Carderate.FreeTime = (int)DerateMoney;
}
Carderate.IORecordID = IOmodel.RecordID;
Carderate.HaveUpdate = 1;
Carderate.PKID = IOmodel.ParkingID;
Carderate.PlateNumber = IOmodel.PlateNumber;
Carderate.DataStatus = 0;
Carderate.LastUpdateTime = DateTime.Now;
dbOperator.BeginTransaction();
strsql = "insert into ParkCarDerate (CarDerateID,CarDerateNo,DerateID,FreeTime,PlateNumber,IORecordID,CardNo,ExpiryTime,CreateTime,Status,FreeMoney,AreaID,PKID,LastUpdateTime,HaveUpdate,DataStatus)";
strsql += "values(@CarDerateID,@CarDerateNo,@DerateID,@FreeTime,@PlateNumber,@IORecordID,@CardNo,@ExpiryTime,@CreateTime,@Status,@FreeMoney,@AreaID,@PKID,@LastUpdateTime,@HaveUpdate,@DataStatus)";
dbOperator.ClearParameters();
dbOperator.AddParameter("CarDerateID", Carderate.CarDerateID);
dbOperator.AddParameter("CarDerateNo", Carderate.CarDerateNo);
dbOperator.AddParameter("DerateID", Carderate.DerateID);
dbOperator.AddParameter("FreeTime", Carderate.FreeTime);
dbOperator.AddParameter("PlateNumber", Carderate.PlateNumber);
dbOperator.AddParameter("IORecordID", Carderate.IORecordID);
dbOperator.AddParameter("CardNo", Carderate.CardNo);
dbOperator.AddParameter("ExpiryTime", Carderate.ExpiryTime);
dbOperator.AddParameter("CreateTime", Carderate.CreateTime);
dbOperator.AddParameter("Status", Carderate.Status);
dbOperator.AddParameter("FreeMoney", Carderate.FreeMoney);
dbOperator.AddParameter("AreaID", Carderate.AreaID);
dbOperator.AddParameter("PKID", Carderate.PKID);
dbOperator.AddParameter("LastUpdateTime", Carderate.LastUpdateTime);
dbOperator.AddParameter("HaveUpdate", Carderate.HaveUpdate);
dbOperator.AddParameter("DataStatus", Carderate.DataStatus);
if (dbOperator.ExecuteNonQuery(strsql) <= 0)
{
dbOperator.RollbackTransaction();
result.Result = 8;
return(JsonHelper.GetJsonString(result));//打折失败
}
if (derate.DerateType == DerateType.DayFree)
{
strsql = "update ParkSeller set Balance=Balance-" + Carderate.FreeMoney + "where SellerID=@SellerID";
dbOperator.ClearParameters();
dbOperator.AddParameter("SellerID", seller.SellerID);
if (dbOperator.ExecuteNonQuery(strsql) <= 0)
{
dbOperator.RollbackTransaction();
result.Result = 8;
return(JsonHelper.GetJsonString(result));//打折失败
}
}
dbOperator.CommitTransaction();
result.Result = 1;
result.ResModel = Carderate;
return(JsonHelper.GetJsonString(result));
}
else
{
result.Result = 2;
return(JsonHelper.GetJsonString(result));//找不到进出记录信息
}
}
}
catch (Exception ex)
{
logger.Fatal("消费打折回调异常！DiscountPlateNumberCallback()" + ex.Message + "\r\n");
result.Result = -1;
return(JsonHelper.GetJsonString(result));//异常
}
}
public ParkOrder Add(ParkOrder model, DbOperator dbOperator)
{
model.DataStatus = DataStatus.Normal;
model.LastUpdateTime = DateTime.Now;
model.HaveUpdate = SystemDefaultConfig.DataUpdateFlag;
model.RecordID = GuidGenerator.GetGuidString();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"insert into ParkOrder(RecordID,Amount,CarderateID,CashMoney,CashTime,DataStatus,DiscountAmount,HaveUpdate,LastUpdateTime,NewMoney,NewUsefulDate,
NewUserBegin,OldMoney,OldUserBegin,OldUserulDate,OnlineOrderNo,OnlineUserID,OrderNo,OrderSource,OrderTime,OrderType,PayAmount,
PayTime,PayWay,PKID,Remark,Status,TagID,UnPayAmount,UserID,FeeRuleID) ");
strSql.Append(@" values(@RecordID,@Amount,@CarderateID,@CashMoney,@CashTime,@DataStatus,@DiscountAmount,@HaveUpdate,@LastUpdateTime,@NewMoney,@NewUsefulDate,
@NewUserBegin,@OldMoney,@OldUserBegin,@OldUserulDate,@OnlineOrderNo,@OnlineUserID,@OrderNo,@OrderSource,@OrderTime,@OrderType,@PayAmount,
@PayTime,@PayWay,@PKID,@Remark,@Status,@TagID,@UnPayAmount,@UserID,@FeeRuleID);");
strSql.Append(" select * from ParkOrder where RecordID=@RecordID ");
dbOperator.ClearParameters();
dbOperator.AddParameter("RecordID", model.RecordID);
dbOperator.AddParameter("Amount", model.Amount);
dbOperator.AddParameter("CarderateID", model.CarderateID);
dbOperator.AddParameter("CashMoney", model.CashMoney);
dbOperator.AddParameter("DataStatus", (int)model.DataStatus);
dbOperator.AddParameter("DiscountAmount", model.DiscountAmount);
dbOperator.AddParameter("HaveUpdate", model.HaveUpdate);
dbOperator.AddParameter("LastUpdateTime", model.LastUpdateTime);
dbOperator.AddParameter("NewMoney", model.NewMoney);
if (model.CashTime == null)
{
dbOperator.AddParameter("CashTime", DBNull.Value);
}
else
{
dbOperator.AddParameter("CashTime", model.CashTime);
}
if (model.NewUsefulDate == null)
{
dbOperator.AddParameter("NewUsefulDate", DBNull.Value);
}
else
{
dbOperator.AddParameter("NewUsefulDate", model.NewUsefulDate);
}
if (model.OldUserulDate == null)
{
dbOperator.AddParameter("OldUserulDate", DBNull.Value);
}
else
{
dbOperator.AddParameter("OldUserulDate", model.OldUserulDate);
}
if (model.OldUserBegin == null)
{
dbOperator.AddParameter("OldUserBegin", DBNull.Value);
}
else
{
dbOperator.AddParameter("OldUserBegin", model.OldUserBegin);
}
if (model.NewUserBegin == null)
{
dbOperator.AddParameter("NewUserBegin", DBNull.Value);
}
else
{
dbOperator.AddParameter("NewUserBegin", model.NewUserBegin);
}
dbOperator.AddParameter("OldMoney", model.OldMoney);
dbOperator.AddParameter("OnlineOrderNo", model.OnlineOrderNo);
dbOperator.AddParameter("OnlineUserID", model.OnlineUserID);
dbOperator.AddParameter("OrderNo", model.OrderNo);
dbOperator.AddParameter("OrderSource", model.OrderSource);
dbOperator.AddParameter("OrderTime", model.OrderTime);
dbOperator.AddParameter("OrderType", model.OrderType);
dbOperator.AddParameter("PayAmount", model.PayAmount);
dbOperator.AddParameter("PayTime", model.PayTime);
dbOperator.AddParameter("PayWay", model.PayWay);
dbOperator.AddParameter("PKID", model.PKID);
dbOperator.AddParameter("FeeRuleID", model.FeeRuleID);
dbOperator.AddParameter("Remark", model.Remark);
dbOperator.AddParameter("Status", model.Status);
dbOperator.AddParameter("TagID", model.TagID);
dbOperator.AddParameter("UnPayAmount", model.UnPayAmount);
dbOperator.AddParameter("UserID", model.UserID);
using (DbDataReader reader = dbOperator.ExecuteReader(strSql.ToString()))
{
if (reader.Read())
{
return(DataReaderToModel <ParkOrder> .ToModel(reader));
}
return(null);
}
}
/// <summary>
/// 获得所有结算单
/// </summary>
/// <param name="PKID">车场编号</param>
/// <param name="SettleStatus">结算单状态</param>
/// <param name="Priod">帐期</param>
/// <returns></returns>
public List <ParkSettlementModel> GetSettlements(string PKID, int SettleStatus, string Priod, string UserID)
{
List <ParkSettlementModel> settlements = new List <ParkSettlementModel>();
StringBuilder strSql = new StringBuilder();
strSql.Append(@"select s.ID,s.RecordID,s.PKID,s.Priod,s.SettleStatus,s.TotalAmount,s.HandlingFeeAmount,s.ReceivableAmount,s.StartTime,s.EndTime,s.PayTime,s.CreateTime,s.Receipt,s.CreateUser,p.pkname ParkName,u.UserName,s.Remark from parksettlement s left join baseparkinfo p on s.pkid=p.pkid left join sysuser u on u.RecordID=s.CreateUser where s.pkid=@PKID and settlestatus in (
select flowid from ParkSettlementApprovalFlow where flowuser=@userid
and pkid=@PKID)");
if (SettleStatus != -1)
{
strSql.Append(" and settlestatus=@settlestatus");
}
if (Priod != "-1")
{
strSql.Append(" and Priod=@Priod");
}
StringBuilder strSql1 = new StringBuilder();
strSql1.Append(@"select s.ID,s.RecordID,s.PKID,s.Priod,s.SettleStatus,s.TotalAmount,s.HandlingFeeAmount,s.ReceivableAmount,s.StartTime,s.EndTime,s.PayTime,s.CreateTime,s.Receipt,s.CreateUser,p.pkname ParkName,u.UserName,s.Remark from parksettlement s left join baseparkinfo p on s.pkid=p.pkid left join sysuser u on u.RecordID=s.CreateUser where s.pkid=@PKID and createuser=@UserID");
if (SettleStatus != -1)
{
strSql1.Append(" and settlestatus=@settlestatus");
}
if (Priod != "-1")
{
strSql1.Append(" and Priod=@Priod");
}
string s = strSql.ToString() + " union " + strSql1.ToString();
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PKID", PKID);
dbOperator.AddParameter("SettleStatus", SettleStatus);
dbOperator.AddParameter("Priod", Priod);
dbOperator.AddParameter("userid", UserID);
using (DbDataReader reader = dbOperator.ExecuteReader(s))
{
while (reader.Read())
{
ParkSettlementModel sm = DataReaderToModel <ParkSettlementModel> .ToModel(reader);
if (sm.CreateTime > DateTime.MinValue)
{
sm.CreateTimeName = sm.CreateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.EndTime > DateTime.MinValue)
{
sm.EndTimeName = sm.EndTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.PayTime > DateTime.MinValue)
{
sm.PayTimeName = sm.PayTime.ToString("yyyy-MM-dd HH:mm:ss");
}
if (sm.StartTime > DateTime.MinValue)
{
sm.StartTimeName = sm.StartTime.ToString("yyyy-MM-dd HH:mm:ss");
}
//申请人为当前人
if (UserID == sm.CreateUser)
{
if (sm.SettleStatus != 0)
{
if (!IsApprovalFlow(PKID, UserID, sm.SettleStatus))
{
sm.IsHide = true;
}
}
}
settlements.Add(sm);
}
}
}
return(settlements);
}
public List <ParkGrantView> QueryPage(Entities.Condition.ParkGrantCondition condition, int pagesize, int pageindex, out int total)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select p.*,o.EmployeeID,o.EmployeeName,o.CertifNo,o.MobilePhone,o.HomePhone,u.CardNo,");
strSql.Append(" u.CardNumb,u.Balance,op.PlateNo,op.Color,c.OverdueToTemp,");
strSql.Append(" c.MonthCardExpiredEnterDay,o.FamilyAddr,o.Remark from ParkGrant p");
strSql.Append(" inner join BaseCard u on p.CardID=u.CardID");
strSql.Append(" inner join ParkCarType c on c.CarTypeID=p.CarTypeID");
strSql.Append(" left join BaseEmployee o on o.EmployeeID =u.EmployeeID");
strSql.Append(" left join EmployeePlate op on p.PlateID=op.PlateID");
strSql.Append(" where p.DataStatus!=@DataStatus and u.DataStatus!=@DataStatus and (o.DataStatus!=@DataStatus or o.DataStatus is null)");
strSql.Append(" and (op.DataStatus!=@DataStatus or op.DataStatus is null) and p.PKID=@PKID");
using (DbOperator dbOperator = ConnectionManager.CreateReadConnection())
{
dbOperator.ClearParameters();
dbOperator.AddParameter("PKID", condition.ParkingId);
dbOperator.AddParameter("DataStatus", (int)DataStatus.Delete);
if (!string.IsNullOrWhiteSpace(condition.EmployeeNameOrMoblie))
{
strSql.Append(" and (o.EmployeeName like @EmployeeName or o.MobilePhone like @EmployeeName or o.HomePhone like @EmployeeName)");
dbOperator.AddParameter("EmployeeName", condition.EmployeeNameOrMoblie);
}
if (!string.IsNullOrWhiteSpace(condition.CarTypeId))
{
strSql.Append(" and p.CarTypeID=@CarTypeID");
dbOperator.AddParameter("CarTypeID", condition.CarTypeId);
}
if (!string.IsNullOrWhiteSpace(condition.CarModelId))
{
strSql.Append(" and p.CarModelId=@CarModelId");
dbOperator.AddParameter("CarModelId", condition.CarModelId);
}
if (!string.IsNullOrWhiteSpace(condition.PlateNumber))
{
strSql.Append(" and op.PlateNo like @PlateNo");
dbOperator.AddParameter("PlateNo", "%" + condition.PlateNumber + "%");
}
if (!string.IsNullOrWhiteSpace(condition.CardNo))
{
strSql.Append(" and (u.CardNo like @CardNo or u.CardNum like @CardNo)");
dbOperator.AddParameter("CardNo", condition.CardNo);
}
if (!string.IsNullOrWhiteSpace(condition.HomeAddress))
{
strSql.Append(" and o.FamilyAddr like @FamilyAddr");
dbOperator.AddParameter("FamilyAddr", "%" + condition.HomeAddress + "%");
}
if (!string.IsNullOrWhiteSpace(condition.ParkingLot))
{
strSql.Append(" and p.PKLot like @PKLot");
dbOperator.AddParameter("PKLot", "%" + condition.ParkingLot + "%");
}
if (condition.State.HasValue && condition.State.Value >= 0)
{
strSql.Append(" and p.State=@State");
dbOperator.AddParameter("State", condition.State.Value);
}
if (condition.State.HasValue && condition.State.Value < 0)
{
strSql.Append(" and (p.EndDate<@EndDate or p.EndDate is null)");
dbOperator.AddParameter("EndDate", DateTime.Now.Date.ToString("yyyy-MM-dd"));
}
if (condition.QueryHasParkingLot)
{
strSql.Append(" and p.PKLot !='' and p.PKLot is not null ");
}
using (DbDataReader reader = dbOperator.Paging(strSql.ToString(), "ID DESC", pageindex, pagesize, out total))
{
List <ParkGrantView> models = new List <ParkGrantView>();
while (reader.Read())
{
models.Add(DataReaderToModel <ParkGrantView> .ToModel(reader));
}
return(models);
}
}
}