def winsLoses(FirmID):
'''FIRM: Look at total wins and loses'''
query = "SELECT COUNT(*) as 'Wins' from Closed_Cases where WonID_Lawyer in (SELECT ID from Lawyers where FirmID = %s)"
param = (FirmID, )
wins = selectWrapper(query, param)
if (wins['res'] == 'failed'):
return wins
else:
n_wins = wins['arr'][0]
query = "SELECT COUNT(*) as 'Loses' from Closed_Cases where WonID_Lawyer NOT IN (SELECT ID from Lawyers where FirmID = %s) AND (Victim_LawyerID IN (SELECT ID from Lawyers where FirmID = %s) OR Accused_LawyerID IN (SELECT ID from Lawyers where FirmID = %s))"
param = (
FirmID,
FirmID,
FirmID,
)
loses = selectWrapper(query, param)
if (loses['res'] == 'failed'):
return loses
else:
n_loses = loses['arr'][0]
return {
'res':
'success',
'arr': [{
'FirmID': FirmID,
'Wins': n_wins['Wins'],
'Loses': n_loses['Loses']
}]
}
def lawyerPerformance(LawyerID):
'''FIRM: Look at lawyer's performance'''
query = "SELECT COUNT(*) as 'wins' from Closed_Cases where WonID_Lawyer = %s"
param = (LawyerID, )
wins = selectWrapper(query, param)
if (wins['res'] == 'failed'):
return wins
else:
n_wins = wins['arr'][0]
query = "SELECT COUNT(*) as 'loses' from Closed_Cases where (Accused_LawyerID = %s OR Victim_LawyerID = %s) AND NOT WonID_Lawyer = %s"
param = (
LawyerID,
LawyerID,
LawyerID,
)
loses = selectWrapper(query, param)
if (loses['res'] == 'failed'):
return loses
else:
n_loses = loses['arr'][0]
return {
'res':
'success',
'arr': [{
'LawyerID': LawyerID,
'wins': n_wins['wins'],
'loses': n_loses['loses']
}]
}
def acceptCase(FilingNo, FirstHearing, CourtNo, JudgeID):
'''JUDGE: Accept a pending case'''
query = "SELECT * from Pending_Cases where is_Verified = 1 AND FilingNo = %s"
param = (FilingNo,)
res = selectWrapper(query, param)
if(res['res'] == 'failed' or len(res['arr'])==0):
return {'res': 'failed', 'type': 'some error occured please recheck param values.'}
data = res['arr'][0]
#delete from pending cases
query = "SET FOREIGN_KEY_CHECKS = OFF"
param = tuple()
res = insertUpdateDeleteWrapper(query, param)
query = "DELETE from Pending_Cases where FilingNo=%s"
param = (FilingNo,)
result = insertUpdateDeleteWrapper(query, param)
query = "SET FOREIGN_KEY_CHECKS = ON"
param = tuple()
res = insertUpdateDeleteWrapper(query, param)
if(result['res'] == 'failed'):
return result
#add to active cases
query = "INSERT into Active_Cases(FilingNo, FilingDate, FirstHearing, NextHearing, CourtNo, JudgeID, VictimID, AccusedID) VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
param = (FilingNo, data['FilingDate'], FirstHearing, FirstHearing, CourtNo, JudgeID, data['VictimID'], data['AccusedID'])
result = insertUpdateDeleteWrapper(query, param)
if(result['res'] == 'failed'):
return result
#add to lawyer client
query = "SELECT CNRno from Active_Cases where FilingNo = %s"
param = (FilingNo,)
result = selectWrapper(query, param)
if(result['res'] == 'failed'):
return result
res = result['arr'][0]
CNR = res['CNRno']
query = "INSERT into Lawyer_Client(LawyerID, ClientID, CNRno, Side) VALUES(%s,%s,%s,0)"
param = (data['Victim_LawyerID'], data['VictimID'], CNR)
result = insertUpdateDeleteWrapper(query, param)
if(result['res'] == 'failed'):
return result
if(data['Type'] == 1):
query = "INSERT into Lawyer_Client(LawyerID, ClientID, CNRno, Side) VALUES(%s,%s,%s,1)"
param = (data['Accused_LawyerID'], data['AccusedID'], CNR)
result = insertUpdateDeleteWrapper(query, param)
return result
def getCasesDetails(CNRno):
'''STUDENT: Get case documents and fir'''
query = "SELECT * FROM Closed_Cases WHERE CNRno = %s"
param = (CNRno, )
res = selectWrapper(query, param)
if (res['res'] == 'failed' or len(res['arr']) == 0):
return res
data = res['arr'][0]
query = "SELECT * FROM Documents WHERE FilingNo = %s"
param = (data['FilingNo'], )
doc = selectWrapper(query, param)
if (doc['res'] == 'failed'):
return doc
res['doc'] = doc['arr']
if (data['AccusedID']):
query = "SELECT * FROM FIR WHERE FilingNo = %s"
param = (data['FilingNo'], )
fir = selectWrapper(query, param)
if (fir['res'] == 'failed'):
return fir
res['fir'] = fir['arr']
return res
def schedule():
'''OFFICER: Check Schedule'''
curr_start = datetime.now().strftime('%Y-%m-%d') + ' 00:00:00'
curr_end = datetime.now().strftime('%Y-%m-%d') + ' 23:59:59'
query = "SELECT * from Active_Cases WHERE NextHearing BETWEEN %s AND %s"
param = tuple([curr_start, curr_end])
return selectWrapper(query, param)
def appointLawyer(FirmID, ClientID, Status, LawyerID=""):
'''FIRM: Appoint a lawyer to a client'''
query = "UPDATE Firm_Request SET Status = %s where FirmID = %s and ClientID = %s"
param = (
Status,
FirmID,
ClientID,
)
result = insertUpdateDeleteWrapper(query, param)
if (Status == 2):
return result
else:
query = "SELECT * from Firm_Request where FirmID = %s and ClientID = %s"
param = (
FirmID,
ClientID,
)
res = selectWrapper(query, param)
if (res['res'] == 'failed'):
return res
else:
values = res['arr'][0]
query = "INSERT into Lawyer_Request(ClientID, LawyerID, FilingNo, Client_Note, Quotation, Status) VALUES(%s,%s,%s,%s,%s,0)"
param = (
ClientID,
LawyerID,
values['FilingNo'],
values['Client_Note'],
values['Quotation'],
)
return insertUpdateDeleteWrapper(query, param)
def schedule(JudgeID):
'''JUDGE: See Schedule for the day'''
curr_start = datetime.now().strftime('%Y-%m-%d') + ' 00:00:00'
curr_end = datetime.now().strftime('%Y-%m-%d') + ' 23:59:59'
query = 'SELECT * from Active_Cases WHERE NextHearing BETWEEN %s AND %s AND JudgeID = %s'
param = (curr_start, curr_end, JudgeID,)
return selectWrapper(query, param)
def earningByClients(FirmID, datePaid):
'''FIRM: Look at overall earning based on clients'''
query = "SELECT ClientID, SUM(Fee) from Lawyer_Client where datePaid>=%s and datePaid<=CURDATE() and ClientID in (SELECT ClientID from Firm_Request where FirmID=%s and Status = 1) GROUP BY ClientID ORDER BY SUM(Fee) DESC"
param = (
datePaid,
FirmID,
)
return selectWrapper(query, param)
def earningByLawyers(FirmID, datePaid):
'''FIRM: Look at overall earning based on Lawyers'''
query = "SELECT LawyerID, SUM(Fee) from Lawyer_Client where datePaid>=%s and datePaid<=CURDATE() and LawyerID in (SELECT ID from Lawyers where FirmID=%s) GROUP BY LawyerID ORDER BY SUM(Fee) DESC"
param = (
datePaid,
FirmID,
)
return selectWrapper(query, param)
def clientTrackRecord(ClientID): '''JUDGE: Track record of a Client''' query = "SELECT * from Closed_Cases where VictimID = %s OR AccusedID = %s" param = (ClientID, ClientID) res = selectWrapper(query, param) if(res['res'] == 'failed'): return res query = "SELECT * from Clients where ID = %s" param = (ClientID,) temp = selectWrapper(query, param) if(temp['res'] == 'failed'): return temp res['details'] = temp['arr'] return res
def lawyerTrackRecord(LawyerID): '''JUDGE: Track record of a Lawyer''' query = "SELECT * from Closed_Cases where Victim_LawyerID = %s OR Accused_LawyerID = %s" param = (LawyerID, LawyerID ,) res = selectWrapper(query, param) if(res['res'] == 'failed'): return res query = "SELECT * from Lawyers where ID = %s" param = (LawyerID,) temp = selectWrapper(query, param) if(temp['res'] == 'failed'): return temp res['details'] = temp['arr'] return res
def todaySchedule(LawyerID):
'''LAWYER: Get today schedule'''
curr_start = datetime.now().strftime('%Y-%m-%d') + ' 00:00:00'
curr_end = datetime.now().strftime('%Y-%m-%d') + ' 23:59:59'
query = 'SELECT * FROM Active_Cases WHERE NextHearing BETWEEN %s AND %s AND CNRno in (SELECT DISTINCT CNRno FROM Lawyer_Client WHERE LawyerID = %s)'
param = (
curr_start,
curr_end,
LawyerID,
)
return selectWrapper(query, param)
def getRelatedUser(CNRno):
'''JUDGE: Get Related Users'''
query = 'SELECT * FROM Lawyer_Client WHERE CNRno = %s'
param = (CNRno,)
res = selectWrapper(query, param)
if(res['res'] == 'failed'):
return res
data = res['arr']
result = {'res': 'success', 'Accused': [], 'Victim': [], 'Accused_Lawyer': [], 'Victim_Lawyer': []}
for enteries in data:
query = 'SELECT * FROM Clients WHERE ID = %s'
param = (enteries['ClientID'],)
client = selectWrapper(query, param)
if(client['res'] == 'failed'):
return client
query = 'SELECT * FROM Lawyers WHERE ID = %s'
param = (enteries['LawyerID'],)
lawyer = selectWrapper(query, param)
if(lawyer['res'] == 'failed'):
return lawyer
if(enteries['Side'] == 0):
result['Victim'] = client['arr']
result['Victim_Lawyer'] = lawyer['arr']
else:
result['Accused'] = client['arr']
result['Accused_Lawyer'] = lawyer['arr']
return result
def viewRelatedDocuments(FilingNo, Type):
'''OFFICER: View Documents and FIR'''
if (Type == 0):
query = "SELECT * FROM Documents WHERE FilingNo = %s"
param = (FilingNo, )
return selectWrapper(query, param)
else:
query = "SELECT * FROM Documents WHERE FilingNo = %s"
param = (FilingNo, )
docs = selectWrapper(query, param)
if (docs['res'] == 'failed'):
return docs
query = "SELECT * FROM FIR WHERE FilingNo = %s"
param = (FilingNo, )
fir = selectWrapper(query, param)
if (fir['res'] == 'failed'):
return fir
return {'res': 'success', 'doc': docs['arr'], 'fir': fir['arr']}
def getCasesByKeywords(keywords, date=""):
'''STUDENT: Search previous cases by keywords [and date]'''
if (not keywords):
return {'res': 'failed', 'type': 'empty param'}
keywords = keywords.split(",")
query = "SELECT * FROM Closed_Cases WHERE ("
param = []
if (date):
query += "Verdict_Date > %s) AND ("
param.append(date)
for words in keywords:
query += "CaseStmnt LIKE %s OR FinalVerdict LIKE %s OR "
temp = "%" + words + "%"
param.extend([temp, temp])
query = query[:-4] + ")"
param = tuple(param)
print(query, param)
return selectWrapper(query, param)
def getCasesByActs(acts, date=""):
'''STUDENT: Search previous cases by acts [and date]'''
if (not acts):
return {'res': 'failed', 'type': 'empty param'}
acts = acts.split(",")
query = "SELECT * FROM Closed_Cases WHERE ("
param = []
if (date):
query += "Verdict_Date > %s) AND ("
param.append(date)
for words in acts:
query += "Acts LIKE %s OR "
temp = "%" + words + "%"
param.append(temp)
query = query[:-4] + ")"
param = tuple(param)
return selectWrapper(query, param)
def getPrevHearings(CNRno):
'''LAWYER: Get previous hearings'''
query = 'SELECT * FROM Hearings WHERE CNRno = %s'
param = (CNRno, )
return selectWrapper(query, param)
def getAccountDetails(LawyerID):
'''LAWYER: Get Account Details'''
query = 'SELECT * FROM Lawyers WHERE ID = %s'
param = (LawyerID, )
return selectWrapper(query, param)
def prevCasesAct(Acts):
'''JUDGE: See previous judgements based on Act'''
query = "SELECT * from Closed_Cases WHERE Acts like %s"
param = ("%"+Acts+"%",)
return selectWrapper(query, param)
def getRequests(LawyerID):
'''LAWYER: Get Lawyer Requests'''
query = 'SELECT * FROM Lawyer_Request WHERE LawyerID = %s AND Status = 0'
param = (LawyerID, )
return selectWrapper(query, param)
def searchClients(FirmID):
'''FIRM: Search about its clients'''
query = "SELECT * from Clients where ID in (SELECT ClientID from Firm_Request where FirmID = %s and Status = 1)"
param = (FirmID, )
return selectWrapper(query, param)
def getAccountDetails(FirmID):
'''FIRM: Get Account Details'''
query = 'SELECT * FROM Firms WHERE ID = %s'
param = (FirmID, )
return selectWrapper(query, param)
def getLawyers(FirmID):
'''FIRM: Get lawyers under the firm'''
query = "SELECT * from Lawyers where FirmID = %s"
param = (FirmID, )
return selectWrapper(query, param)
def getPendingCases(LawyerID):
'''LAWYER: Get pending cases'''
query = 'SELECT * FROM Pending_Cases WHERE Victim_LawyerID = %s OR Accused_LawyerID = %s'
param = (LawyerID, LawyerID)
return selectWrapper(query, param)
def showLawyers(Spec_Area):
'''FIRM: search for lawyer'''
query = 'SELECT * FROM Lawyers WHERE (Spec_Area = %s OR Spec_Area IS NULL) AND FirmID IS NULL ORDER BY rating DESC, Fees_range ASC'
param = (Spec_Area, )
return selectWrapper(query, param)
def getRequests(FirmID):
'''FIRM: Get requests for the firm'''
query = "SELECT * from Firm_Request where FirmID = %s and Status = 0"
param = (FirmID, )
return selectWrapper(query, param)
def getActiveCases(LawyerID):
'''LAWYER: Get active cases'''
query = 'SELECT * FROM Active_Cases WHERE CNRno in (SELECT DISTINCT CNRno FROM Lawyer_Client WHERE LawyerID = %s)'
param = (LawyerID, )
return selectWrapper(query, param)
def getClosedCases():
'''LAWYER: Get closed cases'''
query = 'SELECT * FROM Closed_Cases'
param = tuple()
return selectWrapper(query, param)
def checkDocStatus(Type):
'''OFFICER: Check Document upload status'''
query = "SELECT * from Pending_Cases WHERE is_Verified = 0 and Type = %s"
param = (Type, )
return selectWrapper(query, param)
def getNotPaidClients(LawyerID):
'''LAWYER: Get unpaid clients'''
query = 'SELECT * FROM Lawyer_Client WHERE LawyerID = %s AND isRequested = 0'
param = (LawyerID, )
return selectWrapper(query, param)
