def add_hotel(self, HotelCode, RoomNo, HotelName, FolioCode, OperateTime):
try:
ResOjCheckIn = CheckIn().query2(CheckIn).filter(CheckIn.FolioCode == FolioCode). \
order_by(desc(CheckIn.CheckInID)).all()
if ResOjCheckIn:
objCheckIn = CheckIn()
resHotel = Hotel().query.filter(
Hotel.HotelCode == HotelCode).first()
if resHotel:
objCheckIn.HotelID = resHotel.HotelID
else:
print '--------------------------------------------------------------------'
print 'Error !!'
print 'Can not find this hotel info!'
errorLog().errorLogging('RecoverCheckIn',
'Can not find this hotel info!')
print '--------------------------------------------------------------------'
return FuncResult(success=False,
msg='Can not find this hotel info!')
objCheckIn.RoomNo = RoomNo
objCheckIn.RoomTypeCode = ResOjCheckIn[0].RoomTypeCode
objCheckIn.RoomTypeName = ResOjCheckIn[0].RoomTypeName
objCheckIn.LastModifyTime = datetime.datetime.now()
objCheckIn.HotelLastModifyTime = to_datetime(OperateTime)
objCheckIn.ArrivalTime = ResOjCheckIn[0].ArrivalTime
objCheckIn.DepartTime = ResOjCheckIn[0].DepartTime
objCheckIn.CheckInTime = ResOjCheckIn[0].CheckInTime
objCheckIn.CheckOutTime = ResOjCheckIn[0].CheckOutTime
objCheckIn.FolioCode = FolioCode
objCheckIn.FolioState = ResOjCheckIn[0].FolioState
objCheckIn.CheckInType = 5
objCheckIn.save(flush=True)
lsRelaCheckIn = RelationCheckIn().query.filter(
RelationCheckIn.CheckInID ==
ResOjCheckIn[0].CheckInID).all()
for objRelaCheckIn in lsRelaCheckIn:
NewObjRelaCheckIn = RelationCheckIn()
NewObjRelaCheckIn.CheckInID = objCheckIn.CheckInID
NewObjRelaCheckIn.HotelGuestID = objRelaCheckIn.HotelGuestID
NewObjRelaCheckIn.GuestDocID = objRelaCheckIn.GuestDocID
NewObjRelaCheckIn.CheckInType = 5
NewObjRelaCheckIn.CheckInTime = objRelaCheckIn.CheckInTime
NewObjRelaCheckIn.CheckOutTime = objRelaCheckIn.CheckOutTime
NewObjRelaCheckIn.GuestState = objRelaCheckIn.GuestState
NewObjRelaCheckIn.LastModifyTime = datetime.datetime.now()
NewObjRelaCheckIn.HotelLastModifyTime = to_datetime(
OperateTime)
NewObjRelaCheckIn.save()
return FuncResult(success=True, msg='Do Success!')
else:
print '---------------------------------------------------------------'
print 'Error !!'
print 'Can not find this FolioCode!'
print '---------------------------------------------------------------'
errorLog().errorLogging('RecoverCheckIn',
'Can not find this FolioCode!')
return FuncResult(success=False, msg='Have No This FolioCode!')
except Exception, ce:
return FuncResult(fail=ce)
def search_hotel(self, dictCondition, dictPageInfo):
try:
listParams = []
if 'sHotelCode' in dictCondition and dictCondition['sHotelCode'] != '':
listParams.append(Hotel.HotelCode == dictCondition['sHotelCode'])
if 'sHotelName' in dictCondition and dictCondition['sHotelName'] != '':
listParams.append(Hotel.HotelName == dictCondition['sHotelName'])
if 'sResponPerson' in dictCondition and dictCondition['sResponPerson'] != '':
listParams.append(Hotel.ResponPerson == dictCondition['sResponPerson'])
if 'nProvinceID' in dictCondition and dictCondition['nProvinceID'] != '' and dictCondition['nProvinceID'] != None:
listParams.append(Hotel.ProvinceID == dictCondition['nProvinceID'])
if 'nDistrictID' in dictCondition and dictCondition['nDistrictID'] != '' and dictCondition['nDistrictID'] != None:
listParams.append(Hotel.DistrictID == dictCondition['nDistrictID'])
if 'nCityID' in dictCondition and dictCondition['nCityID'] != '' and dictCondition['nCityID'] != None:
listParams.append(Hotel.CityID == dictCondition['nCityID'])
if 'nRoomCountMax' in dictCondition and dictCondition['nRoomCountMax'] != '':
listParams.append(Hotel.RoomCount <= dictCondition['RoomCountMax'])
if 'nRoomCountMin' in dictCondition and dictCondition['nRoomCountMin'] != '':
listParams.append(Hotel.RoomCount >= dictCondition['nRoomCountMin'])
if 'nBedCountMax' in dictCondition and dictCondition['nBedCountMax'] != '':
listParams.append(Hotel.BedCount <= dictCondition['nBedCountMax'])
if 'nBedCountMin' in dictCondition and dictCondition['nBedCountMax'] != '':
listParams.append(Hotel.BedCountMin >= dictCondition['nBedCountMin'])
if 'sRegStartDate' in dictCondition and dictCondition['sRegStartDate'] != '':
global sRegStartDate
sRegStartDate = to_datetime(dictCondition['sRegStartDate'])
listParams.append(Hotel.RegStartTime >= sRegStartDate)
if 'sRegEndDate' in dictCondition and dictCondition['sRegEndDate'] != '':
global sRegEndDate
sRegEndDate = to_datetime(dictCondition['sRegEndDate'])
listParams.append(Hotel.RegStartTime <= sRegEndDate)
if 'sCreateUserNameLike' in dictCondition and dictCondition['sCreateUserNameLike'] != '':
listParams.append(Hotel.CreateUserName.like("%" + dictCondition['sCreateUserNameLike']) + "%")
sSql = Hotel().query2(Hotel).filter(*listParams)
sSqlOrderBy = self.order_by(sSql, dictPageInfo['nSort'])
nRowCount = Hotel().query2(func.count(Hotel.HotelID).label('RowCount')).filter(*listParams).all()
dataSet = sSqlOrderBy.limit(
dictPageInfo['nPageSize']).offset((dictPageInfo['nPageNo'] - 1) * dictPageInfo['nPageSize']).all()
lsData = []
for objData in dataSet:
lsData.append(objData.copy2())
if dictCondition['sCheckInStartDate'] and dictCondition['sCheckInEndDate']:
dtDays = days_count(dictCondition['sCheckInStartDate'], dictCondition['sCheckInEndDate'])
for index in range(len(lsData)):
hotelID = lsData[index]['HotelID']
lsCheckAndGuestCount = self.search_hotel_checkin_log(hotelID, dictCondition, dictPageInfo)
nCheckInCount = lsCheckAndGuestCount[0]['nCheckInCount']
nAvgCheckInCount = nCheckInCount / float(dtDays)
lsData[index]['SumCheckInRoom'] = nCheckInCount
lsData[index]['AvgCheckInRoom'] = nAvgCheckInCount
nGuestCount = lsCheckAndGuestCount[0]['nGuestCount']
nAvgnGuestCount = nGuestCount / float(dtDays)
lsData[index]['SumCheckInGuest'] = nGuestCount
lsData[index]['AvgCheckInGuest'] = nAvgnGuestCount
Data = result_data(dictPageInfo['nPageNo'], dictPageInfo['nPageSize'], nRowCount[0][0], lsData)
except Exception, ce:
print Exception, ce
return FuncResult(success=False, msg='查询出错!!')
def addRealtionCheckIn(self, CheckInID, HotelGuestID, GuestDocID,
CheckInInfo):
objRelationCheckIn = RelationCheckIn()
objRelationCheckIn.CheckInID = CheckInID
objRelationCheckIn.HotelGuestID = HotelGuestID
objRelationCheckIn.GuestDocID = GuestDocID
objRelationCheckIn.CheckInType = 6
objRelationCheckIn.CheckInTime = to_datetime(
CheckInInfo['ArrivalTime'])
objRelationCheckIn.CheckOutTime = to_datetime(
CheckInInfo['DepartTime'])
objRelationCheckIn.LastModifyTime = datetime.datetime.now()
objRelationCheckIn.HotelLastModifyTime = to_datetime(
CheckInInfo['OperateTime'])
objRelationCheckIn.save()
pass
def data_handle(self):
objInput = web.input()
try:
obj_input = web.input()
objCondition = obj_input.get('objCondition')
objPageInfo = obj_input.get('objPageInfo')
dictCondition = json.loads(objCondition)
dictPageInfo = json.loads(objPageInfo)
if 'nUserID' in dictCondition and dictCondition['nUserID']:
nUserID = dictCondition['nUserID']
else:
nUserID = ''
if 'sWorkCode' in dictCondition and dictCondition['sWorkCode']:
sWorkCode = dictCondition['sWorkCode']
else:
sWorkCode = ''
if 'sUserCode' in dictCondition and dictCondition['sUserCode']:
sUserCode = dictCondition['sUserCode']
else:
sUserCode = ''
if 'sUserName' in dictCondition and dictCondition['sUserName']:
sUserName = dictCondition['sUserName']
else:
sUserName = ''
if 'nOperaType' in dictCondition and dictCondition['nOperaType']:
nOperaType = dictCondition['nOperaType']
else:
nOperaType = ''
if 'sPost' in dictCondition and dictCondition['sPost']:
sPost = dictCondition['sPost']
else:
sPost = ''
if 'sDateStart' in dictCondition and dictCondition[
'sDateStart'] and dictCondition['sDateStart'] != '':
dtDateStart = to_datetime(dictCondition['sDateStart'])
else:
dtDateStart = ''
if 'sDateEnd' in dictCondition and dictCondition[
'sDateEnd'] and dictCondition['sDateEnd'] != '':
dtDateEnd = to_datetime(dictCondition['sDateEnd'])
else:
dtDateEnd = ''
# objPageInfo = dictCondition['objPageInfo']
# objPageInfo = json.loads(objPageInfo)
except Exception, ce:
print Exception, ce
return FuncResult(fail='参数有误!')
def update_hotel(self, nHotelIDMust, nUpdateUserIDMust, sUpdateUserNameMust, sHotelCode,
sHotelName, sHotelAddr,sHotelTel, sLegalTel, sLegalPerson, sLegalDocNo,
str_coordinate, nProvinceID, nCityID, nDistrictID,nHotelType, sResponPersonTel,
sResponPerson, nRoomCount, nBedCount, sBusLicenseCode, sBusLicenseName,
sRemark, sRegStartTime):
# 查找酒店
obj_hotel = Hotel().query.filter(Hotel.HotelID == nHotelIDMust).first()
if not is_null(nHotelType):
obj_hotel.HotelCode = sHotelCode
if not is_null(sHotelName):
obj_hotel.HotelName = sHotelName
if not is_null(sHotelAddr):
obj_hotel.HotelAddr = sHotelAddr
if not is_null(sHotelTel):
obj_hotel.HotelTel = sHotelTel
if not is_null(sLegalTel):
obj_hotel.LegalTel = sLegalTel
if not is_null(sLegalPerson):
obj_hotel.LegalPerson = sLegalPerson
if not is_null(sLegalDocNo):
obj_hotel.LegalDocNo = sLegalDocNo
if not is_null(str_coordinate):
obj_hotel.Coordinate = str_coordinate
if not is_null(nProvinceID):
obj_hotel.ProvinceID = nProvinceID
if not is_null(nCityID):
obj_hotel.CityID = nCityID
if not is_null(nDistrictID):
obj_hotel.DistrictID = nDistrictID
if not is_null(nHotelType):
obj_hotel.HotelType = nHotelType
if not is_null(sResponPersonTel):
obj_hotel.ResponPersonTel = sResponPersonTel
if not is_null(sResponPerson):
obj_hotel.ResponPerson = sResponPerson
if not is_null(nRoomCount):
obj_hotel.RoomCount = nRoomCount
if not is_null(nBedCount):
obj_hotel.BedCount = nBedCount
if not is_null(sBusLicenseCode):
obj_hotel.BusLicenseCode = sBusLicenseCode
if not is_null(sBusLicenseName):
obj_hotel.BusLicenseName = sBusLicenseName
if not is_null(sRemark):
obj_hotel.Remark = sRemark
if not is_null(sRegStartTime):
obj_hotel.RegStartTime = to_datetime(sRegStartTime)
# 保存到数据库
obj_hotel.save()
return FuncResult(success=True, msg='酒店资料修改成功!')
def add_hotel(self, GuestCode, HotelCode, CheckOutTime, FolioCode,
OperateTime):
try:
ResOjCheckIn = CheckIn().query2(CheckIn).filter(CheckIn.FolioCode == FolioCode). \
order_by(desc(CheckIn.CheckInID)).all()
if ResOjCheckIn:
objCheckIn = CheckIn()
resHotel = Hotel().query.filter(
Hotel.HotelCode == HotelCode).first()
if resHotel:
objCheckIn.HotelID = resHotel.HotelID
else:
# return FuncResult(success=False, msg='Can not find this hotel info!')
return 2
objCheckIn.RoomNo = ResOjCheckIn[0].RoomNo
objCheckIn.RoomTypeCode = ResOjCheckIn[0].RoomTypeCode
objCheckIn.RoomTypeName = ResOjCheckIn[0].RoomTypeName
objCheckIn.LastModifyTime = datetime.datetime.now()
objCheckIn.HotelLastModifyTime = to_datetime(OperateTime)
objCheckIn.ArrivalTime = ResOjCheckIn[0].ArrivalTime
objCheckIn.DepartTime = ResOjCheckIn[0].DepartTime
objCheckIn.CheckInTime = ResOjCheckIn[0].CheckInTime
objCheckIn.CheckOutTime = to_datetime(CheckOutTime)
objCheckIn.FolioCode = FolioCode
objCheckIn.FolioState = ResOjCheckIn[0].FolioState
objCheckIn.CheckInType = 7
objCheckIn.save(flush=True)
# find which ReduceGuest
# resDocInfo = GuestDoc().query.filter(GuestDoc.DocNo == DocNo).first()
resGuestInfo = GuestCheckIn().query.filter(
GuestCheckIn.HotelDocNo == GuestCode).first()
RelaInfo = RelationCheckIn().query.filter(
RelationCheckIn.HotelGuestID ==
resGuestInfo.HotelGuestID).all()
if RelaInfo == None or resGuestInfo == None:
# return FuncResult(success=False, msg='Have No This Guest!')
return 3
# save in new relation_checkin table
lsRelaCheckIn = RelationCheckIn().query.filter(
RelationCheckIn.CheckInID ==
ResOjCheckIn[0].CheckInID).all()
if lsRelaCheckIn == None or lsRelaCheckIn == []:
return 4
for objRelaCheckIn in lsRelaCheckIn:
NewObjRelaCheckIn = RelationCheckIn()
NewObjRelaCheckIn.CheckInID = objCheckIn.CheckInID
NewObjRelaCheckIn.HotelGuestID = resGuestInfo.HotelGuestID
NewObjRelaCheckIn.GuestDocID = RelaInfo[0].GuestDocID
NewObjRelaCheckIn.CheckInType = 7
NewObjRelaCheckIn.CheckInTime = objRelaCheckIn.CheckInTime
NewObjRelaCheckIn.CheckOutTime = to_datetime(CheckOutTime)
NewObjRelaCheckIn.GuestState = objRelaCheckIn.GuestState
NewObjRelaCheckIn.LastModifyTime = datetime.datetime.now()
NewObjRelaCheckIn.HotelLastModifyTime = to_datetime(
OperateTime)
NewObjRelaCheckIn.save()
# return FuncResult(success=True, msg='Do Success!')
# return 0
else:
return 1
except Exception, ce:
return FuncResult(fail=ce)
def add_hotel(self, CheckInInfo, GuestInfoSet):
try:
# save CheckIn
objCheckIn = CheckIn()
# oldCheckIn = CheckIn().query.filter(CheckIn.FolioCode == CheckInInfo['FolioCode']).\
# order_by(desc(CheckIn.CheckInID)).all()
if isHasParams('HotelCode', CheckInInfo):
resHotel = Hotel().query.filter(
Hotel.HotelCode == CheckInInfo['HotelCode']).first()
if resHotel:
objCheckIn.HotelID = resHotel.HotelID
else:
print '--------------------------------------------------------------------'
print 'Error !!'
print 'Can not find this hotel info!'
errorLog().errorLogging('AddGuestCheckInAPI',
'Can not find this hotel info!')
print '--------------------------------------------------------------------'
return FuncResult(success=False,
msg='Can not find this hotel info!')
else:
print '--------------------------------------------------------------------'
print 'Error !!'
print 'Can not find this hotel info!'
print '--------------------------------------------------------------------'
errorLog().errorLogging('AddGuestCheckInAPI',
'Can not find this hotel info!')
return FuncResult(success=False,
msg='Can not find this hotel info!')
if isHasParams('RoomNo', CheckInInfo):
objCheckIn.RoomNo = CheckInInfo['RoomNo']
if isHasParams('RoomTypeCode', CheckInInfo):
objCheckIn.RoomTypeCode = CheckInInfo['RoomTypeCode']
if isHasParams('RoomTypeName', CheckInInfo):
objCheckIn.RoomTypeName = CheckInInfo['RoomTypeName']
if isHasParams('ArrivalTime', CheckInInfo):
objCheckIn.ArrivalTime = to_datetime(
CheckInInfo['ArrivalTime'])
objCheckIn.CheckInTime = to_datetime(
CheckInInfo['ArrivalTime'])
if isHasParams('DepartTime', CheckInInfo):
objCheckIn.DepartTime = to_datetime(CheckInInfo['DepartTime'])
objCheckIn.CheckOutTime = to_datetime(
CheckInInfo['DepartTime'])
if isHasParams('FolioCode', CheckInInfo):
objCheckIn.FolioCode = CheckInInfo['FolioCode']
objCheckIn.LastModifyTime = datetime.datetime.now()
objCheckIn.CheckInType = 6
objCheckIn.save(flush=True)
CheckInID = objCheckIn.CheckInID
# save GuesetInfo
for GuestInfo in GuestInfoSet:
objGuestInfo = GuestCheckIn()
objDocInfo = GuestDoc()
dictGuestInfo = GuestInfo['GuestInfo']
dictDocInfo = GuestInfo['DocInfo']
# have this GuesetInfo before ?
resGuestInfo = GuestCheckIn().query.filter(
GuestCheckIn.HotelDocNo ==
dictGuestInfo['HotelDocNo']).first()
if not resGuestInfo:
if isHasParams('HotelDocType', dictGuestInfo):
objGuestInfo.HotelDocType = dictGuestInfo[
'HotelDocType']
if isHasParams('HotelDocNo', dictGuestInfo):
objGuestInfo.HotelDocNo = dictGuestInfo['HotelDocNo']
if isHasParams('HotelDocName', dictGuestInfo):
objGuestInfo.HotelDocName = dictGuestInfo[
'HotelDocName']
if isHasParams('HotelBornDate', dictGuestInfo):
objGuestInfo.HotelBornDate = dictGuestInfo[
'HotelBornDate']
if isHasParams('HotelSex', dictGuestInfo):
objGuestInfo.HotelSex = dictGuestInfo['HotelSex']
if isHasParams('NationaCode', dictGuestInfo):
objGuestInfo.HotelNation = dictGuestInfo['NationaCode']
if isHasParams('HotelAddress', dictGuestInfo):
objGuestInfo.HotelAddress = dictGuestInfo[
'HotelAddress']
if isHasParams('HotelRemark', dictGuestInfo):
objGuestInfo.HotelRemark = dictGuestInfo['HotelRemark']
if isHasParams('HotelLastModifyTime', dictGuestInfo):
objGuestInfo.HotelLastModifyTime = to_datetime(
dictGuestInfo['HotelLastModifyTime'])
if isHasParams('HotelTel', dictGuestInfo):
objGuestInfo.HotelTel = dictGuestInfo['HotelTel']
if isHasParams('MatchResult', dictDocInfo):
objGuestInfo.MatchResult = dictDocInfo['MatchResult']
if isHasParams('MatchResultPercent', dictDocInfo):
objGuestInfo.MatchResultPercent = dictDocInfo[
'MatchResultPercent']
objGuestInfo.save(flush=True)
HotelGuestID = objGuestInfo.HotelGuestID
else:
HotelGuestID = resGuestInfo.HotelGuestID
# have this GuestDocID before ?
resGuestDocInfo = GuestDoc().query.filter(
GuestDoc.DocNo == dictDocInfo['DocNo']).first()
if not resGuestDocInfo:
if isHasParams('GuestType', dictDocInfo):
objDocInfo.GuestType = dictDocInfo['GuestType']
if isHasParams('GuestType', dictDocInfo):
objDocInfo.GuestType = dictDocInfo['GuestType']
if isHasParams('DocType', dictDocInfo):
objDocInfo.DocType = dictDocInfo['DocType']
if isHasParams('DocNo', dictDocInfo):
objDocInfo.DocNo = dictDocInfo['DocNo']
if isHasParams('DocName', dictDocInfo):
objDocInfo.DocName = dictDocInfo['DocName']
if isHasParams('GuestName', dictDocInfo):
objDocInfo.GuestName = dictDocInfo['GuestName']
if isHasParams('BornDate', dictDocInfo):
objDocInfo.BornDate = dictDocInfo['BornDate']
if isHasParams('Sex', dictDocInfo):
objDocInfo.Sex = dictDocInfo['Sex']
if isHasParams('NativePlace', dictDocInfo):
objDocInfo.NativePlace = dictDocInfo['NativePlace']
if isHasParams('Nation', dictDocInfo):
objDocInfo.Nation = dictDocInfo['Nation']
if isHasParams('AuthStatus', dictDocInfo):
objDocInfo.AuthStatus = dictDocInfo['AuthStatus']
if isHasParams('ResAddress', dictDocInfo):
objDocInfo.ResAddress = dictDocInfo['ResAddress']
if isHasParams('State', dictDocInfo):
objDocInfo.State = dictDocInfo['State']
if isHasParams('CertiOffice', dictDocInfo):
objDocInfo.CertiOffice = dictDocInfo['CertiOffice']
if isHasParams('CertiEffStartTime', dictDocInfo):
objDocInfo.CertiEffStartTime = to_datetime(
dictDocInfo['CertiEffStartTime'])
if isHasParams('CertiEffEndTime', dictDocInfo):
objDocInfo.CertiEffEndTime = to_datetime(
dictDocInfo['CertiEffEndTime'])
if isHasParams('DocPhotoURL', dictDocInfo):
objDocInfo.DocPhotoURL = dictDocInfo['DocPhotoURL']
if isHasParams('Remark', dictDocInfo):
objDocInfo.Remark = dictDocInfo['Remark']
objDocInfo.CreateTime = datetime.datetime.now()
objDocInfo.save(flush=True)
GuestDocID = objDocInfo.GuestDocID
else:
GuestDocID = resGuestDocInfo.GuestDocID
if isHasParams('IsForeigner', dictGuestInfo):
# if is foreigner
if dictGuestInfo['IsForeigner']:
objForeigner = Foreigner()
# have this foreigner before ?
resForeigner = Foreigner().query.filter(
Foreigner.PassNo ==
dictGuestInfo['PassNo']).first()
if not resForeigner:
if isHasParams('GuestDocID', dictGuestInfo):
objForeigner.GuestDocID = GuestDocID
if isHasParams('PassNo', dictGuestInfo):
objForeigner.PassNo = dictGuestInfo['PassNo']
if isHasParams('VisaDeadline', dictGuestInfo):
objForeigner.VisaDeadline = dictGuestInfo[
'VisaDeadline']
if isHasParams('DepartureTime', dictGuestInfo):
objForeigner.DepartureTime = dictGuestInfo[
'DepartureTime']
if isHasParams('EntryTime', dictGuestInfo):
objForeigner.EntryTime = dictGuestInfo[
'EntryTime']
if isHasParams('EntryPlace', dictGuestInfo):
objForeigner.EntryPlace = dictGuestInfo[
'EntryPlace']
if isHasParams('DeparturePlace', dictGuestInfo):
objForeigner.DeparturePlace = dictGuestInfo[
'DeparturePlace']
if isHasParams('VisaType', dictGuestInfo):
objForeigner.VisaType = dictGuestInfo[
'VisaType']
objForeigner.CreateTime = datetime.datetime.now()
objForeigner.save(flush=True)
# save in h_relation_checkin table
self.addRealtionCheckIn(CheckInID, HotelGuestID, GuestDocID,
CheckInInfo)
print 22
return FuncResult(success=True, msg='操作成功!')
except Exception, ce:
return FuncResult(fail=ce)
def search_hotel_checkin_log(self, dictCondition, dictPageInfo):
try:
objParamsValue = {}
sCheckInSql = '''
Select
h_hotel.RoomCount , h_hotel.BedCount ,Count(h_checkin.CheckInID) As CheckInCount
From
h_hotel INNER JOIN h_checkin ON h_hotel.HotelID = h_checkin.HotelID
WHERE 1=1
'''
sGuestSql = '''
Select
Count(h_relation_checkin.HotelGuestID) As GuesetCount
From
h_hotel INNER JOIN h_checkin ON h_hotel.HotelID = h_checkin.HotelID
INNER JOIN h_relation_checkin ON h_checkin.CheckInID = h_relation_checkin.CheckInID
WHERE 1=1
'''
if dictCondition.has_key('nHotelID') and dictCondition['nHotelID']:
nHotelID = dictCondition['nHotelID']
sCheckInSql = sCheckInSql + 'AND h_hotel.HotelID = :nHotelID '
sGuestSql = sGuestSql + 'AND h_hotel.HotelID = :nHotelID '
objParamsValue['nHotelID'] = nHotelID
DateSet = []
global objData
objData = {}
if dictCondition.has_key(
'sCheckInStartDate'
) and dictCondition['sCheckInStartDate'] and dictCondition.has_key(
'sCheckInEndDate') and dictCondition['sCheckInEndDate']:
k = 0
dtCheckInStartDate0 = to_datetime(
dictCondition['sCheckInStartDate'])
dtCheckOutTime0 = to_datetime(dictCondition['sCheckInEndDate'])
while dtCheckInStartDate0 <= dtCheckOutTime0:
dtCheckInStartDate = dtCheckInStartDate0 + datetime.timedelta(
hours=23) + datetime.timedelta(
minutes=59) + datetime.timedelta(seconds=59)
dtCheckOutTime = dtCheckInStartDate0
sCheckInSqlNew = sCheckInSql + 'AND h_checkin.CheckInTime <= :dtCheckInStartDate '
sGuestSqlNew = sGuestSql + 'AND h_checkin.CheckInTime <= :dtCheckInStartDate '
sCheckInSqlNew = sCheckInSqlNew + 'AND h_checkin.CheckOutTime >= :dtCheckOutTime '
sGuestSqlNew = sGuestSqlNew + 'AND h_checkin.CheckOutTime >= :dtCheckOutTime '
objParamsValue['dtCheckInStartDate'] = dtCheckInStartDate
objParamsValue['dtCheckOutTime'] = dtCheckOutTime
sCheckInSqlNew = sCheckInSqlNew + "GROUP BY h_hotel.HotelID"
sGuestSqlNew = sGuestSqlNew + "GROUP BY h_hotel.HotelID"
lsQueryData = web.ctx.cur_dbconn.execute(
text(sCheckInSqlNew), **objParamsValue).fetchall()
lsQueryData2 = web.ctx.cur_dbconn.execute(
text(sGuestSqlNew), **objParamsValue).fetchall()
if lsQueryData:
for i in range(len(lsQueryData)):
RP1 = lsQueryData[i]
objData['Date'] = dtCheckInStartDate0.strftime(
"%Y-%m-%d")
objData['RoomCount'] = RP1._row[0]
objData['BedCount'] = RP1._row[1]
if RP1._row[2]:
objData['SumCheckInRoom'] = RP1._row[2]
DateSet.append(objData)
objData = {}
else:
objData['Date'] = dtCheckInStartDate0.strftime(
"%Y-%m-%d")
objData['RoomCount'] = 0
objData['BedCount'] = 0
DateSet.append(objData)
objData = {}
if lsQueryData2:
for i in range(len(lsQueryData2)):
if lsQueryData2[i]:
RP2 = lsQueryData2[i]
DateSet[k]['SumCheckInGuest'] = RP2._row[0]
objData = {}
else:
DateSet[k]['SumCheckInGuest'] = 0
dtCheckInStartDate0 = dtCheckInStartDate0 + datetime.timedelta(
days=1)
sCheckInSqlNew = ''
sGuestSqlNew = ''
k += 1
Data = {}
Data['PangeNo'] = dictPageInfo['nPageNo']
Data['PageSize'] = dictPageInfo['nPageSize']
# Data['DataSet'] = []
if DateSet:
Data['RowCount'] = len(DateSet)
nindexStart = (Data['PangeNo'] - 1) * Data['PageSize']
nindexEnd = nindexStart + Data['PageSize']
if len(DateSet) > nindexStart:
lsDataSet = DateSet[nindexStart:nindexEnd]
Data['DataSet'] = lsDataSet
else:
Data['DataSet'] = []
else:
Data['RowCount'] = 0
Data['LastData'] = self.search_hotel_checkin_log_lasttime(
dictCondition, dictPageInfo)
except Exception, ce:
print Exception, ce
return FuncResult(success=False, msg='查询出错!!')
def search_hotel_checkin_log2(self, hotelID, dictCondition, dictPageInfo):
try:
objParamsValue = {}
sCheckInSql = '''
Select
h_hotel.RoomCount , h_hotel.BedCount ,Count(h_checkin.CheckInID) As CheckInCount
From
h_hotel INNER JOIN h_checkin ON h_hotel.HotelID = h_checkin.HotelID
WHERE 1=1
'''
sGuestSql = '''
Select
Count(h_relation_checkin.HotelGuestID) As GuesetCount
From
h_hotel INNER JOIN h_checkin ON h_hotel.HotelID = h_checkin.HotelID
INNER JOIN h_relation_checkin ON h_checkin.CheckInID = h_relation_checkin.CheckInID
WHERE 1=1
'''
if hotelID:
nHotelID = hotelID
sCheckInSql = sCheckInSql + 'AND h_hotel.HotelID = :nHotelID '
sGuestSql = sGuestSql + 'AND h_hotel.HotelID = :nHotelID '
objParamsValue['nHotelID'] = nHotelID
DateSet = []
global objData
objData = {}
if dictCondition.has_key('sCheckInStartDate') and dictCondition[
'sCheckInStartDate'] and dictCondition.has_key('sCheckInEndDate') and dictCondition['sCheckInEndDate']:
k = 0
dtCheckInStartDate0 = to_datetime(dictCondition['sCheckInStartDate'])
dtCheckOutTime0 = to_datetime(dictCondition['sCheckInEndDate'])
while dtCheckInStartDate0 <= dtCheckOutTime0:
dtCheckInStartDate = dtCheckInStartDate0 + datetime.timedelta(hours=23) + datetime.timedelta(
minutes=59) + datetime.timedelta(seconds=59)
dtCheckOutTime = dtCheckInStartDate0
sCheckInSqlNew = sCheckInSql + 'AND h_checkin.CheckInTime <= :dtCheckInStartDate '
sGuestSqlNew = sGuestSql + 'AND h_checkin.CheckInTime <= :dtCheckInStartDate '
sCheckInSqlNew = sCheckInSqlNew + 'AND h_checkin.CheckOutTime >= :dtCheckOutTime '
sGuestSqlNew = sGuestSqlNew + 'AND h_checkin.CheckOutTime >= :dtCheckOutTime '
objParamsValue['dtCheckInStartDate'] = dtCheckInStartDate
objParamsValue['dtCheckOutTime'] = dtCheckOutTime
sCheckInSqlNew = sCheckInSqlNew + "GROUP BY h_hotel.HotelID"
sGuestSqlNew = sGuestSqlNew + "GROUP BY h_hotel.HotelID"
# objParamsValue['nPageSize'] = dictPageInfo['nPageSize']
# objParamsValue['nPageNo'] = dictPageInfo['nPageNo']
# objParamsValue['nindex'] = objParamsValue['nPageSize'] * (objParamsValue['nPageNo']-1)
# print objParamsValue['nindex']
# sCheckInSqlNew = sCheckInSqlNew + " Limit :nindex, :nPageSize"
# sGuestSqlNew = sGuestSqlNew + " Limit :nindex, :nPageSize"
lsQueryData = web.ctx.cur_dbconn.execute(text(sCheckInSqlNew), **objParamsValue).fetchall()
lsQueryData2 = web.ctx.cur_dbconn.execute(text(sGuestSqlNew), **objParamsValue).fetchall()
if lsQueryData:
for index in range(len(lsQueryData)):
nCount = lsQueryData[index].CheckInCount
return nCount
else:
return 0
except Exception, ce:
print Exception, ce
return FuncResult(success=False, msg='查询出错!!')
def add_hotel(self, sHotelCode, sHotelName, sHotelAddr, sHotelTel,
sLegalTel, sLegalPerson, sLegalDocNo, sCoordinate,
nProvinceID, nCityID, nDistrictID, nHotelType, nCreatUserID,
sCreatUserCode, sResponPersonTel, sResponPerson, nRoomCount,
nBedCount, sBusLicenseCode, sBusLicenseName, sRegStartTime,
sRemark):
try:
# 实例化一个Hotel类
objHotel = Hotel()
# 赋值
if not is_null(sHotelCode):
objHotel.HotelCode = sHotelCode
if not is_null(sHotelName):
objHotel.HotelName = sHotelName
if not is_null(sHotelAddr):
objHotel.HotelAddr = sHotelAddr
if not is_null(sHotelTel):
objHotel.HotelTel = sHotelTel
if not is_null(sLegalTel):
objHotel.LegalTel = sLegalTel
if not is_null(sLegalPerson):
objHotel.LegalPerson = sLegalPerson
if not is_null(sLegalDocNo):
objHotel.LegalDocNo = sLegalDocNo
if not is_null(sCoordinate):
objHotel.Coordinate = sCoordinate
if not is_null(nProvinceID):
objHotel.ProvinceID = nProvinceID
if not is_null(nCityID):
objHotel.CityID = nCityID
if not is_null(nDistrictID):
objHotel.DistrictID = nDistrictID
if not is_null(nHotelType):
objHotel.HotelType = nHotelType
if not is_null(nCreatUserID):
objHotel.CreateUserID = nCreatUserID
if not is_null(sCreatUserCode):
objHotel.CreateUserName = sCreatUserCode
if not is_null(sResponPersonTel):
objHotel.ResponPersonTel = sResponPersonTel
if not is_null(sResponPerson):
objHotel.ResponPerson = sResponPerson
if not is_null(nRoomCount):
objHotel.RoomCount = nRoomCount
if not is_null(nBedCount):
objHotel.BedCount = nBedCount
if not is_null(sBusLicenseCode):
objHotel.BusLicenseCode = sBusLicenseCode
if not is_null(sBusLicenseName):
objHotel.BusLicenseName = sBusLicenseName
if not is_null(sRegStartTime):
objHotel.RegStartTime = to_datetime(sRegStartTime)
if not is_null(sRemark):
objHotel.Remark = sRemark
# 0：待审核，1：已审核，2：审核不通过
objHotel.AuditState = 6
objHotel.ApplyTime = datetime.datetime.now()
objHotel.CreateTime = datetime.datetime.now()
# 保存到数据库
objHotel.save()
except Exception, ce:
pass