def getContractDetail(request, ldict):
ls_sql = "select id,bill_no,vslvoy,cargo_name,origin_place,client_id,cargo_piece,cargo_weight," \
"cargo_volume,booking_date,in_port_date,return_cntr_date,custom_id,ship_corp_id,port_id," \
"yard_id,finish_flag,finish_time,remark,contract_no,dispatch_place,custom_title1," \
"custom_title2,landtrans_id,check_yard_id,unbox_yard_id,credit_id,cargo_type,cntr_freedays," \
"pre_inport_date from contract"
#ldict = json.loads(request.POST['jpargs'])
lrtn = rawsql2json(*rawsql4request(ls_sql, ldict))
if lrtn["total"] > 0 :
pass
else:
return HttpResponse(json.dumps(lrtn,ensure_ascii = False))
# lrtn 包含提单号、用于统计后续箱量。所有的提单号，用于统计所有箱量。
ls_sqlsum = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in ( %s )
and A.cntr_type = B.id
group by b.cntr_type
'''
ls_sql4action = ''' select action_name from contract_action as A, c_contract_action as B
where contract_id = %s and A.action_id = B.id order by B.sortno desc limit 1;
'''
list_contrId = []
for i in lrtn["rows"]:
#get all the cntr in the bill
l_sumCntr = rawSql2JsonDict(ls_sqlsum % str(i["id"]) )
list_contrId.append(str(i["id"]))
if len(l_sumCntr) > 0:
ls = ";".join([x["showsum"] for x in l_sumCntr])
i.update({ "cntr_sum": ls })
ls2 = ";".join([x["checksum"] for x in l_sumCntr])
i.update({ "check_num": ls2 })
else:
i.update({ "cntr_sum": "None" })
i.update({ "check_num": "None" })
# get the last cntr action , as to say, have the biggest sortno of the c_cntr_action
l_lastAction = rawSql2JsonDict(ls_sql4action % str(i["id"]) )
if len(l_lastAction) > 0:
ls = ";".join([x["action_name"] for x in l_lastAction])
i.update({ "current_action": ls })
else:
i.update({ "current_action": "None" })
# get all the cntr for all the sum bill .
ldict_sum = rawSql2JsonDict(ls_sqlsum % ( ",".join(list_contrId) ) )
ls_sumCheck = "None"
if len(ldict_sum) > 0 :
ls_sumCheck = ";".join([x["checksum"] for x in ldict_sum])
ls_sumall = ";".join([x["showsum"] for x in ldict_sum])
lrtn.update( { "footer" : [{"cntr_sum":ls_sumall , "bill_no": "合计", "check_num": ls_sumCheck } ] } )
else:
lrtn.update( { "footer" : [{"cntr_sum":"None" , "bill_no": "合计", "check_num": "None" } ] } )
# get all the cntr for check from the contract.
return HttpResponse(json.dumps(lrtn, ensure_ascii = False))
''' test:
def getBussSumary(request, aDict):
ls_time1 = str(aDict["ex_parm"]["begindate"])
ls_time2 = str(aDict["ex_parm"]["enddate"])
ls_clientid = str(aDict["ex_parm"]["client_id"]).strip(" ")
ls_sqlclient = ""
if len(ls_clientid) > 0:
ls_sqlclient = " client_id = %s and " % ls_clientid
ls_sql = "select client_id, cargo_type, cargo_name, origin_place, sum(cargo_volume)" \
" from contract where " + ls_sqlclient + \
" finish_time between '%s' and '%s' group by client_id, cargo_type, cargo_name, origin_place" % ( ls_time1, ls_time2)
lrtn = rawsql2json(*rawsql4request(ls_sql, aDict))
if lrtn["total"] > 0 :
pass
else:
lrtn = { "msg":"查询成功", "stateCod": "1", "error": [""],"rows":[],"footer":[] }
return HttpResponse(json.dumps(lrtn,ensure_ascii = False))
ls_sqlsum = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in (select id from contract where COALESCE(client_id,0) = %s and COALESCE(cargo_type,0) = %s and COALESCE(cargo_name,0) = %s and COALESCE(origin_place,0) = %s)
and A.cntr_type = B.id
group by b.cntr_type
'''
for i in lrtn["rows"]:
#get all the cntr in the bill
ls_cargoType = "0"
ls_cargoName = "0"
ls_originPlace = "0"
if len(str(i["cargo_type"])) > 0:
ls_cargoType = str(i["cargo_type"])
if len(str(i["cargo_name"])) > 0:
ls_cargoName = str(i["cargo_name"])
if len(str(i["origin_place"])) > 0:
ls_originPlace = str(i["origin_place"])
#l_sumCntr = rawSql2JsonDict(ls_sqlsum % (str(i["client_id"]),str(i["cargo_type"]),str(i["cargo_name"]),str(i["origin_place"])) )
l_sumCntr = rawSql2JsonDict(ls_sqlsum % (str(i["client_id"]),ls_cargoType,ls_cargoName,ls_originPlace ))
if len(l_sumCntr) > 0:
ls = ";".join([x["showsum"] for x in l_sumCntr])
ls2 = ";".join([x["checksum"] for x in l_sumCntr])
i.update({ "cntr_num": ls , "check_num": ls2 })
else:
i.update({ "cntr_num": "None", "check_num": "None" })
# get all the cntr for all the sum bill .
ls_sumcntr4footer = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in (select id from contract where %s finish_time between '%s' and '%s')
and A.cntr_type = B.id group by b.cntr_type ''' % ( ls_sqlclient, ls_time1, ls_time2)
ldict_sum = rawSql2JsonDict(ls_sumcntr4footer )
ls_sumCheck = "None"
if len(ldict_sum) > 0 :
ls_sumCheck = ";".join([x["checksum"] for x in ldict_sum])
ls_sumall = ";".join([x["showsum"] for x in ldict_sum])
lrtn.update( { "footer" : [{"cntr_num":ls_sumall , "client_id": "合计", "check_num": ls_sumCheck } ] } )
else:
lrtn.update( { "footer" : [{"cntr_num":"None" , "client_id": "合计", "check_num": "None" } ] } )
# get all the cntr for check from the contract.
return HttpResponse(json.dumps(lrtn, ensure_ascii = False))
def auditDeleteQuery(request, ldict):
l_rtn = {}
l_clientid = str(ldict['ex_parm']['client_id'])
l_feetyp = str(ldict['ex_parm']['fee_typ'])
ls_sql = "select ex_over, audit_tim from act_fee where client_id=%s and fee_typ='%s' and audit_id=true order by id desc limit 1 " % (
l_clientid, l_feetyp)
l_actRecord = cursorSelect(ls_sql)
if len(l_actRecord) > 0:
pass
else:
l_rtn.update({
"msg": "查询成功，但没有符合条件记录",
"error": '',
"stateCod": 1,
"result": {
"act": [],
"pre": []
}
})
return l_rtn
ls_auditTim = l_actRecord[0][1]
ls_exOver = l_actRecord[0][0]
ls_sqlpre1 = "select id,contract_id,fee_typ,fee_cod,client_id,amount," \
"fee_tim,lock_flag,fee_financial_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim " \
"from pre_fee where audit_id = true " \
"and audit_tim = '%s' and ex_over='%s' " % (ls_auditTim, ls_exOver)
ls_sqlpre2 = "select id,contract_id,fee_typ,fee_cod,client_id,(0-amount) amount," \
"fee_tim,lock_flag,fee_financial_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim " \
"from pre_fee where audit_id = false and rec_tim = '%s' and ex_from='%s' " % (ls_auditTim, ls_exOver)
ls_sqlact1 = "select id,client_id,fee_typ,amount,invoice_no,check_no,pay_type," \
"fee_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim,accept_no " \
"from act_fee where audit_id = true " \
"and audit_tim = '%s' and ex_over='%s' " % (ls_auditTim, ls_exOver)
ls_sqlact2 = "select id,client_id,fee_typ,(0-amount) amount,invoice_no,check_no,pay_type," \
"fee_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim,accept_no " \
"from act_fee where audit_id = false and rec_tim = '%s' and ex_from='%s' " % (ls_auditTim, ls_exOver)
try:
list_pre = rawSql2JsonDict(ls_sqlpre1)
list_pre.extend(rawSql2JsonDict(ls_sqlpre2))
list_act = rawSql2JsonDict(ls_sqlact1)
list_act.extend(rawSql2JsonDict(ls_sqlact2))
l_result = {"act": list_act, "pre": list_pre}
l_rtn.update({
"msg": "查询成功",
"error": [],
"stateCod": 1,
"result": l_result
})
except Exception as e:
l_rtn.update({
"msg": "查询失败",
"error": list((str(e.args), )),
"stateCod": -5
})
return l_rtn
def initProtElemContent(request, adict):
''' # 协议要素内容初始化
import App.ajaxRespFee
import zdCommon.dbhelp
aaa = { "func": '协议要素内容初始化',
"reqtype": 'update',
"ex_parm": { "id": '1' } }
App.ajaxRespFee.initProtElemContent(aaa, aaa)
'''
l_rtn = {"msg": "成功", "stateCod": "001", "error": [], "rows": [] }
l_recnam = request.session['userid']
ls_eleId = str(adict["ex_parm"]["id"])
ls_sqlinit = "select init_data_sql from p_fee_ele where id = %s"
lds_rtn = cursorSelect(ls_sqlinit, [ls_eleId])
try:
if lds_rtn:
ls_sub = str(lds_rtn[0][0]).replace('"',"'")
ls_sqlIns = ''' insert into p_fee_ele_lov(ele_id, lov_cod, lov_name, rec_nam, rec_tim)
select '%s' as ele_id , lov_cod, lov_name, %s, now() from
( %s ) tt2
where lov_cod not in (select lov_cod from p_fee_ele_lov where ele_id = '%s')
''' % (ls_eleId, l_recnam , ls_sub, ls_eleId)
cursorExec(ls_sqlIns)
ls_sqlrtn = "select id,ele_id,lov_cod,lov_name,remark from p_fee_ele_lov where ele_id = %s "
l_result = rawSql2JsonDict(ls_sqlrtn, [ls_eleId])
l_rtn.update( {"msg": "操作成功", "error":[], "stateCod" : 202, "rows": l_result } )
except Exception as e:
l_rtn.update( {"msg": "操作失败", "error": list( (str(e.args),) ) , "stateCod" : -1 } )
return l_rtn
def clientFeeDetailReport(request, adict):
''' 客户费用明细报表 ex_parm:{ client_id:'', //客户id fee_typ:'', //费用类型 begin_tim:'', //开始时间 end_tim:'' //截止时间 }'''
l_rtn = { }
ls_clientId = str(adict['ex_parm']['client_id']).strip()
ls_feeType = str(adict['ex_parm']['fee_typ']).strip()
ls_client_q = ("p.client_id = %s and " % ls_clientId) if len(ls_clientId) > 0 else ""
ls_feeType_q = ("p.fee_typ = '%s' and " % ls_feeType) if len(ls_feeType) > 0 else ""
ls_sql = '''
select c.bill_no,sum(case p.fee_cod when 1 then amount else 0 end) baogan,sum(case p.fee_cod when 2 then amount else 0 end) chaoqi,
sum(case p.fee_cod when 3 then amount else 0 end) duicun,sum(case p.fee_cod when 4 then amount else 0 end) banyi,
sum(case p.fee_cod when 5 then amount else 0 end) yanhuo,sum(case p.fee_cod when 6 then amount else 0 end) xunzheng,
sum(case p.fee_cod when 7 then amount else 0 end) changdi,sum(case p.fee_cod when 8 then amount else 0 end) tuoche,
sum(case p.fee_cod when 11 then amount else 0 end) zhibao,
sum(case p.fee_cod in(1,2,3,4,5,6,7,8,11) when true then 0 else amount end) qita
from pre_fee as p,contract as c
where %s %s and p.ex_feeid = 'O'
and (p.fee_financial_tim between '%s' and '%s')
and p.contract_id = c.id
group by c.bill_no
'''
try:
list_rtn = rawSql2JsonDict(ls_sql % ( ls_client_q, ls_feeType_q, str(adict['ex_parm']['begin_tim'] ), str(adict['ex_parm']['end_tim'])) )
l_rtn.update( {"msg": "查询成功", "error":[], "stateCod" : 1, "rows": list_rtn } )
except Exception as e:
l_rtn.update( {"msg": "查询失败", "error": list( (str(e.args),) ) , "stateCod" : -1 } )
return l_rtn
def auditSumQuery(request, ldict):
l_rtn = { }
ls_clientid = str(ldict['ex_parm']['client_id'])
ls_feetyp = str(ldict['ex_parm']['fee_typ'])
ls_begin = str(ldict['ex_parm']['begin_audit_tim'])
ls_end = str(ldict['ex_parm']['end_audit_tim'])
# 处理参数
ls_clientsql = " client_id > 0 " if len(ls_clientid) < 1 or int(ls_clientid) < 1 else (" client_id = " + ls_clientid)
ls_feesql = " " if len(ls_feetyp) < 1 else (" and fee_typ = '%s' " % ls_feetyp)
ls_timesql = ""
if len(ls_begin) > 0 : ls_timesql += " and audit_tim > '%s' " % ls_begin
if len(ls_end) > 0 : ls_timesql += " and audit_tim < '%s' " % ls_end
# 生成sql语句
ls_sql1 = '''select s.client_id, s.fee_typ, s.ex_over,s.audit_tim,s.amount - sum(COALESCE(a.amount,0)) amount
from act_fee a right join
( select client_id, fee_typ, ex_over,audit_tim,sum(amount) amount from act_fee
'''
ls_sql2 = " where audit_id = true and " + ls_clientsql + ls_feesql + ls_timesql
ls_sql3 = ''' group by client_id, fee_typ, ex_over,audit_tim) s
on a.ex_from = s.ex_over
group by s.client_id, s.fee_typ, s.ex_over,s.audit_tim,s.amount
'''
# 执行并返回。
try:
l_result = rawSql2JsonDict(ls_sql1 + ls_sql2 + ls_sql3)
l_rtn.update( {"msg": "查询成功", "error":[], "stateCod" : 1, "rows": l_result } )
except Exception as e:
l_rtn.update( {"msg": "查询失败", "error": list( (str(e.args),) ) , "stateCod" : -1 } )
return l_rtn
def auditDetailQuery(request, ldict):
l_rtn = {}
ls_exOver = str(ldict['ex_parm']['ex_over'])
if len(ls_exOver) < 1:
l_rtn.update({
"msg": "查询失败",
"error": list(("缺少核销参数", )),
"stateCod": -1
})
return l_rtn
try:
list_pre = rawSql2JsonDict(
"select bill_no,fee_typ,fee_cod,amount from pre_fee,contract "
"where contract.id = pre_fee.contract_id"
" and ex_over = '%s'" % ls_exOver)
list_pre.extend(
rawSql2JsonDict(
"select bill_no,fee_typ,fee_cod,(0-amount) amount from pre_fee,contract "
" where contract.id = pre_fee.contract_id"
" and ex_from = '%s'" % ls_exOver))
list_act = rawSql2JsonDict(
"select client_id,fee_typ,amount,pay_type,invoice_no,check_no from act_fee where ex_over = '%s'"
% ls_exOver)
list_act.extend(
rawSql2JsonDict(
"select client_id,fee_typ,(0-amount) amount,pay_type,invoice_no,check_no from act_fee where ex_from = '%s'"
% ls_exOver))
l_result = {"act": list_act, "pre": list_pre}
l_rtn.update({
"msg": "查询成功",
"error": [],
"stateCod": 1,
"result": l_result
})
except Exception as e:
l_rtn.update({
"msg": "查询失败",
"error": list((str(e.args), )),
"stateCod": -1
})
return l_rtn
def queryRptFee(request, adict):
'''
1.where p.client_id = %s and p.fee_typ = '%s'
如果参数client_id为空 则p.client_id = %s 条件忽略
如果参数fee_typ为空 则为p.fee_typ = '%s' 条件忽略
2. p.fee_typ 是应收的 金额取正值， 应付的 金额取负值
'''
l_rtn = {"msg": "成功", "stateCod": "001", "error": [], "rows": [] }
ls_clientId = str(adict["ex_parm"]["client_id"])
ls_feeType = str(adict["ex_parm"]["fee_typ"])
ls_beginTim = str(adict["ex_parm"]["begin_tim"])
ls_endTim = str(adict["ex_parm"]["end_tim"])
ls_rptid = str(adict["ex_parm"]["rpt"])
ls_sqlFee = '''select c_rpt_fee.fee_id,c_rpt_fee.fee_typ, c_fee.fee_name from c_rpt_fee,c_fee
where c_rpt_fee.rpt_id = %s and c_rpt_fee.item_id in
(select id from c_rpt_item where rpt_id = %s )
and c_rpt_fee.fee_id = c_fee.id;''' % (ls_rptid, ls_rptid)
try:
l_fee = cursorSelect(ls_sqlFee)
l_cacheFeeCod = []
l_cacheFeeSql = []
for i_fee in l_fee:
l_cacheFeeSql.append( (" sum(case (p.fee_cod = %s and p.fee_typ = '%s')" + ' when true then amount else 0 end) "%s" ') % (str(i_fee[0]), str(i_fee[1]), str(i_fee[0]) ) )
l_cacheFeeCod.append(str(i_fee[0]))
if len(l_cacheFeeCod) > 0:
ls_client_q = ("p.client_id = %s and" % ls_clientId) if len(ls_clientId.strip()) > 0 else ""
ls_feeType_q = ("p.fee_typ = '%s' and" % ls_feeType) if len(ls_feeType.strip()) > 0 else ""
ls_sqlAll = ''' select c.bill_no,%s,
sum(case fee_typ when 'I' then amount else 0 end) zongji_in,
sum(case fee_typ when 'O' then amount else 0 end) zongji_out,
sum(case fee_typ when 'I' then amount else 0-amount end) zongji_gain
from pre_fee as p,contract as c
where %s %s p.ex_feeid = 'O'
and (c.finish_time between '%s' and '%s')
and p.contract_id = c.id
group by c.bill_no
''' % ( ",".join(l_cacheFeeSql) , ls_client_q, ls_feeType_q, ls_beginTim, ls_endTim )
# % ( ",".join(l_cacheFeeSql) , ",".join(l_cacheFeeCod), ls_client_q, ls_feeType_q, ls_beginTim, ls_endTim )
l_result = rawSql2JsonDict(ls_sqlAll)
l_zongji_in = sum([float(i["zongji_in"]) for i in l_result])
l_zongji_out = sum([float(i["zongji_out"]) for i in l_result])
l_zongji_gain = sum([float(i["zongji_gain"]) for i in l_result])
l_rtn.update( {"msg": "查询成功", "error":[], "stateCod" : 1, "rows": l_result,
"footer":[{"bill_no":"合计：", "zongji_in": l_zongji_in,"zongji_out": l_zongji_out,"zongji_gain": l_zongji_gain }]
} )
else:
l_rtn.update( {"msg": "没定义查询数据列。", "error": [] , "stateCod" : 0 } )
except Exception as e:
l_rtn.update( {"msg": "查询失败", "error": list( (str(e.args),) ) , "stateCod" : -1 } )
return l_rtn
def auditDetailQuery(request,ldict):
l_rtn = {}
ls_exOver = str(ldict['ex_parm']['ex_over'])
if len(ls_exOver) < 1:
l_rtn.update( {"msg": "查询失败", "error": list( ("缺少核销参数",) ) , "stateCod" : -1 } )
return l_rtn
try:
list_pre = rawSql2JsonDict("select bill_no,fee_typ,fee_cod,amount from pre_fee,contract "
"where contract.id = pre_fee.contract_id"
" and ex_over = '%s'" % ls_exOver)
list_pre.extend(rawSql2JsonDict("select bill_no,fee_typ,fee_cod,(0-amount) amount from pre_fee,contract "
" where contract.id = pre_fee.contract_id"
" and ex_from = '%s'" % ls_exOver))
list_act = rawSql2JsonDict("select client_id,fee_typ,amount,pay_type,invoice_no,check_no from act_fee where ex_over = '%s'" % ls_exOver)
list_act.extend(rawSql2JsonDict("select client_id,fee_typ,(0-amount) amount,pay_type,invoice_no,check_no from act_fee where ex_from = '%s'" % ls_exOver))
l_result = { "act":list_act, "pre":list_pre }
l_rtn.update( {"msg": "查询成功", "error":[], "stateCod" : 1, "result": l_result } )
except Exception as e:
l_rtn.update( {"msg": "查询失败", "error": list( (str(e.args),) ) , "stateCod" : -1 } )
return l_rtn
def auditDeleteQuery(request, ldict):
l_rtn = {}
l_clientid = str(ldict['ex_parm']['client_id'])
l_feetyp = str(ldict['ex_parm']['fee_typ'])
ls_sql = "select ex_over, audit_tim from act_fee where client_id=%s and fee_typ='%s' and audit_id=true order by id desc limit 1 " % (l_clientid, l_feetyp)
l_actRecord = cursorSelect(ls_sql)
if len(l_actRecord) > 0 :
pass
else:
l_rtn.update( {"msg": "查询成功，但没有符合条件记录", "error": '',"stateCod":1,"result":{"act":[], "pre":[]} } )
return l_rtn
ls_auditTim = l_actRecord[0][1]
ls_exOver = l_actRecord[0][0]
ls_sqlpre1 = "select id,contract_id,fee_typ,fee_cod,client_id,amount," \
"fee_tim,lock_flag,fee_financial_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim " \
"from pre_fee where audit_id = true " \
"and audit_tim = '%s' and ex_over='%s' " % (ls_auditTim, ls_exOver)
ls_sqlpre2 = "select id,contract_id,fee_typ,fee_cod,client_id,(0-amount) amount," \
"fee_tim,lock_flag,fee_financial_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim " \
"from pre_fee where audit_id = false and rec_tim = '%s' and ex_from='%s' " % (ls_auditTim, ls_exOver)
ls_sqlact1 = "select id,client_id,fee_typ,amount,invoice_no,check_no,pay_type," \
"fee_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim,accept_no " \
"from act_fee where audit_id = true " \
"and audit_tim = '%s' and ex_over='%s' " % (ls_auditTim, ls_exOver)
ls_sqlact2 = "select id,client_id,fee_typ,(0-amount) amount,invoice_no,check_no,pay_type," \
"fee_tim,remark,ex_from,ex_over,ex_feeid,audit_id,audit_tim,accept_no " \
"from act_fee where audit_id = false and rec_tim = '%s' and ex_from='%s' " % (ls_auditTim, ls_exOver)
try:
list_pre = rawSql2JsonDict(ls_sqlpre1)
list_pre.extend(rawSql2JsonDict(ls_sqlpre2))
list_act = rawSql2JsonDict(ls_sqlact1)
list_act.extend(rawSql2JsonDict(ls_sqlact2))
l_result = { "act":list_act, "pre":list_pre }
l_rtn.update( {"msg": "查询成功", "error":[], "stateCod" : 1, "result": l_result } )
except Exception as e:
l_rtn.update( {"msg": "查询失败", "error": list( (str(e.args),) ) , "stateCod" : -5 } )
return l_rtn
def clientFeeDetailReport(request, adict):
''' 客户费用明细报表 ex_parm:{ client_id:'', //客户id fee_typ:'', //费用类型 begin_tim:'', //开始时间 end_tim:'' //截止时间 }'''
l_rtn = {}
ls_clientId = str(adict['ex_parm']['client_id']).strip()
ls_feeType = str(adict['ex_parm']['fee_typ']).strip()
ls_client_q = ("p.client_id = %s and " %
ls_clientId) if len(ls_clientId) > 0 else ""
ls_feeType_q = ("p.fee_typ = '%s' and " %
ls_feeType) if len(ls_feeType) > 0 else ""
ls_sql = '''
select c.bill_no,sum(case p.fee_cod when 1 then amount else 0 end) baogan,sum(case p.fee_cod when 2 then amount else 0 end) chaoqi,
sum(case p.fee_cod when 3 then amount else 0 end) duicun,sum(case p.fee_cod when 4 then amount else 0 end) banyi,
sum(case p.fee_cod when 5 then amount else 0 end) yanhuo,sum(case p.fee_cod when 6 then amount else 0 end) xunzheng,
sum(case p.fee_cod when 7 then amount else 0 end) changdi,sum(case p.fee_cod when 8 then amount else 0 end) tuoche,
sum(case p.fee_cod when 11 then amount else 0 end) zhibao,
sum(case p.fee_cod in(1,2,3,4,5,6,7,8,11) when true then 0 else amount end) qita
from pre_fee as p,contract as c
where %s %s and p.ex_feeid = 'O'
and (p.fee_financial_tim between '%s' and '%s')
and p.contract_id = c.id
group by c.bill_no
'''
try:
list_rtn = rawSql2JsonDict(
ls_sql %
(ls_client_q, ls_feeType_q, str(adict['ex_parm']['begin_tim']),
str(adict['ex_parm']['end_tim'])))
l_rtn.update({
"msg": "查询成功",
"error": [],
"stateCod": 1,
"rows": list_rtn
})
except Exception as e:
l_rtn.update({
"msg": "查询失败",
"error": list((str(e.args), )),
"stateCod": -1
})
return l_rtn
def auditSumQuery(request, ldict):
l_rtn = {}
ls_clientid = str(ldict['ex_parm']['client_id'])
ls_feetyp = str(ldict['ex_parm']['fee_typ'])
ls_begin = str(ldict['ex_parm']['begin_audit_tim'])
ls_end = str(ldict['ex_parm']['end_audit_tim'])
# 处理参数
ls_clientsql = " client_id > 0 " if len(ls_clientid) < 1 or int(
ls_clientid) < 1 else (" client_id = " + ls_clientid)
ls_feesql = " " if len(ls_feetyp) < 1 else (" and fee_typ = '%s' " %
ls_feetyp)
ls_timesql = ""
if len(ls_begin) > 0: ls_timesql += " and audit_tim > '%s' " % ls_begin
if len(ls_end) > 0: ls_timesql += " and audit_tim < '%s' " % ls_end
# 生成sql语句
ls_sql1 = '''select s.client_id, s.fee_typ, s.ex_over,s.audit_tim,s.amount - sum(COALESCE(a.amount,0)) amount
from act_fee a right join
( select client_id, fee_typ, ex_over,audit_tim,sum(amount) amount from act_fee
'''
ls_sql2 = " where audit_id = true and " + ls_clientsql + ls_feesql + ls_timesql
ls_sql3 = ''' group by client_id, fee_typ, ex_over,audit_tim) s
on a.ex_from = s.ex_over
group by s.client_id, s.fee_typ, s.ex_over,s.audit_tim,s.amount
'''
# 执行并返回。
try:
l_result = rawSql2JsonDict(ls_sql1 + ls_sql2 + ls_sql3)
l_rtn.update({
"msg": "查询成功",
"error": [],
"stateCod": 1,
"rows": l_result
})
except Exception as e:
l_rtn.update({
"msg": "查询失败",
"error": list((str(e.args), )),
"stateCod": -1
})
return l_rtn
def initProtElemContent(request, adict):
''' # 协议要素内容初始化
import yardApp.ajaxRespFee
import zdCommon.dbhelp
aaa = { "func": '协议要素内容初始化',
"reqtype": 'update',
"ex_parm": { "id": '1' } }
yardApp.ajaxRespFee.initProtElemContent(aaa, aaa)
'''
l_rtn = {"msg": "成功", "stateCod": "001", "error": [], "rows": []}
l_recnam = request.session['userid']
ls_eleId = str(adict["ex_parm"]["id"])
ls_sqlinit = "select init_data_sql from p_fee_ele where id = %s"
lds_rtn = cursorSelect(ls_sqlinit, [ls_eleId])
try:
if lds_rtn:
ls_sub = str(lds_rtn[0][0]).replace('"', "'")
ls_sqlIns = ''' insert into p_fee_ele_lov(ele_id, lov_cod, lov_name, rec_nam, rec_tim)
select '%s' as ele_id , lov_cod, lov_name, %s, now() from
( %s ) tt2
where lov_cod not in (select lov_cod from p_fee_ele_lov where ele_id = '%s')
''' % (ls_eleId, l_recnam, ls_sub, ls_eleId)
cursorExec(ls_sqlIns)
ls_sqlrtn = "select id,ele_id,lov_cod,lov_name,remark from p_fee_ele_lov where ele_id = %s "
l_result = rawSql2JsonDict(ls_sqlrtn, [ls_eleId])
l_rtn.update({
"msg": "操作成功",
"error": [],
"stateCod": 202,
"rows": l_result
})
except Exception as e:
l_rtn.update({
"msg": "操作失败",
"error": list((str(e.args), )),
"stateCod": -1
})
return l_rtn
def getBussSumary(request, aDict):
ls_time1 = str(aDict["ex_parm"]["begindate"])
ls_time2 = str(aDict["ex_parm"]["enddate"])
ls_clientid = str(aDict["ex_parm"]["client_id"]).strip(" ")
ls_sqlclient = ""
if len(ls_clientid) > 0:
ls_sqlclient = " client_id = %s and " % ls_clientid
ls_sql = "select client_id, cargo_type, cargo_name, origin_place, sum(cargo_volume)" \
" from contract where " + ls_sqlclient + \
" finish_time between '%s' and '%s' group by client_id, cargo_type, cargo_name, origin_place" % ( ls_time1, ls_time2)
lrtn = rawsql2json(*rawsql4request(ls_sql, aDict))
if lrtn["total"] > 0:
pass
else:
lrtn = {
"msg": "查询成功",
"stateCod": "1",
"error": [""],
"rows": [],
"footer": []
}
return HttpResponse(json.dumps(lrtn, ensure_ascii=False))
ls_sqlsum = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in (select id from contract where COALESCE(client_id,0) = %s and COALESCE(cargo_type,0) = %s and COALESCE(cargo_name,0) = %s and COALESCE(origin_place,0) = %s)
and A.cntr_type = B.id
group by b.cntr_type
'''
for i in lrtn["rows"]:
#get all the cntr in the bill
ls_cargoType = "0"
ls_cargoName = "0"
ls_originPlace = "0"
if len(str(i["cargo_type"])) > 0:
ls_cargoType = str(i["cargo_type"])
if len(str(i["cargo_name"])) > 0:
ls_cargoName = str(i["cargo_name"])
if len(str(i["origin_place"])) > 0:
ls_originPlace = str(i["origin_place"])
#l_sumCntr = rawSql2JsonDict(ls_sqlsum % (str(i["client_id"]),str(i["cargo_type"]),str(i["cargo_name"]),str(i["origin_place"])) )
l_sumCntr = rawSql2JsonDict(
ls_sqlsum %
(str(i["client_id"]), ls_cargoType, ls_cargoName, ls_originPlace))
if len(l_sumCntr) > 0:
ls = ";".join([x["showsum"] for x in l_sumCntr])
ls2 = ";".join([x["checksum"] for x in l_sumCntr])
i.update({"cntr_num": ls, "check_num": ls2})
else:
i.update({"cntr_num": "None", "check_num": "None"})
# get all the cntr for all the sum bill .
ls_sumcntr4footer = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in (select id from contract where %s finish_time between '%s' and '%s')
and A.cntr_type = B.id group by b.cntr_type ''' % (
ls_sqlclient, ls_time1, ls_time2)
ldict_sum = rawSql2JsonDict(ls_sumcntr4footer)
ls_sumCheck = "None"
if len(ldict_sum) > 0:
ls_sumCheck = ";".join([x["checksum"] for x in ldict_sum])
ls_sumall = ";".join([x["showsum"] for x in ldict_sum])
lrtn.update({
"footer": [{
"cntr_num": ls_sumall,
"client_id": "合计",
"check_num": ls_sumCheck
}]
})
else:
lrtn.update({
"footer": [{
"cntr_num": "None",
"client_id": "合计",
"check_num": "None"
}]
})
# get all the cntr for check from the contract.
return HttpResponse(json.dumps(lrtn, ensure_ascii=False))
def getContractDetail(request, ldict):
ls_sql = "select id,bill_no,vslvoy,cargo_name,origin_place,client_id,cargo_piece,cargo_weight," \
"cargo_volume,booking_date,in_port_date,return_cntr_date,custom_id,ship_corp_id,port_id," \
"yard_id,finish_flag,finish_time,remark,contract_no,dispatch_place,custom_title1," \
"custom_title2,landtrans_id,check_yard_id,unbox_yard_id,credit_id,cargo_type,cntr_freedays," \
"pre_inport_date from contract"
#ldict = json.loads(request.POST['jpargs'])
lrtn = rawsql2json(*rawsql4request(ls_sql, ldict))
if lrtn["total"] > 0:
pass
else:
return HttpResponse(json.dumps(lrtn, ensure_ascii=False))
# lrtn 包含提单号、用于统计后续箱量。所有的提单号，用于统计所有箱量。
ls_sqlsum = ''' select b.cntr_type || ' X ' || sum(cntr_num) as showsum, b.cntr_type || ' X ' || sum(check_num) as checksum from contract_cntr as A , c_cntr_type as B
where A.contract_id in ( %s )
and A.cntr_type = B.id
group by b.cntr_type
'''
ls_sql4action = ''' select action_name from contract_action as A, c_contract_action as B
where contract_id = %s and A.action_id = B.id order by B.sortno desc limit 1;
'''
list_contrId = []
for i in lrtn["rows"]:
#get all the cntr in the bill
l_sumCntr = rawSql2JsonDict(ls_sqlsum % str(i["id"]))
list_contrId.append(str(i["id"]))
if len(l_sumCntr) > 0:
ls = ";".join([x["showsum"] for x in l_sumCntr])
i.update({"cntr_sum": ls})
ls2 = ";".join([x["checksum"] for x in l_sumCntr])
i.update({"check_num": ls2})
else:
i.update({"cntr_sum": "None"})
i.update({"check_num": "None"})
# get the last cntr action , as to say, have the biggest sortno of the c_cntr_action
l_lastAction = rawSql2JsonDict(ls_sql4action % str(i["id"]))
if len(l_lastAction) > 0:
ls = ";".join([x["action_name"] for x in l_lastAction])
i.update({"current_action": ls})
else:
i.update({"current_action": "None"})
# get all the cntr for all the sum bill .
ldict_sum = rawSql2JsonDict(ls_sqlsum % (",".join(list_contrId)))
ls_sumCheck = "None"
if len(ldict_sum) > 0:
ls_sumCheck = ";".join([x["checksum"] for x in ldict_sum])
ls_sumall = ";".join([x["showsum"] for x in ldict_sum])
lrtn.update({
"footer": [{
"cntr_sum": ls_sumall,
"bill_no": "合计",
"check_num": ls_sumCheck
}]
})
else:
lrtn.update({
"footer": [{
"cntr_sum": "None",
"bill_no": "合计",
"check_num": "None"
}]
})
# get all the cntr for check from the contract.
return HttpResponse(json.dumps(lrtn, ensure_ascii=False))
''' test:
def queryRptFee(request, adict):
'''
1.where p.client_id = %s and p.fee_typ = '%s'
如果参数client_id为空 则p.client_id = %s 条件忽略
如果参数fee_typ为空 则为p.fee_typ = '%s' 条件忽略
2. p.fee_typ 是应收的 金额取正值， 应付的 金额取负值
'''
l_rtn = {"msg": "成功", "stateCod": "001", "error": [], "rows": []}
ls_clientId = str(adict["ex_parm"]["client_id"])
ls_feeType = str(adict["ex_parm"]["fee_typ"])
ls_beginTim = str(adict["ex_parm"]["begin_tim"])
ls_endTim = str(adict["ex_parm"]["end_tim"])
ls_rptid = str(adict["ex_parm"]["rpt"])
ls_sqlFee = '''select c_rpt_fee.fee_id,c_rpt_fee.fee_typ, c_fee.fee_name from c_rpt_fee,c_fee
where c_rpt_fee.rpt_id = %s and c_rpt_fee.item_id in
(select id from c_rpt_item where rpt_id = %s )
and c_rpt_fee.fee_id = c_fee.id;''' % (ls_rptid,
ls_rptid)
try:
l_fee = cursorSelect(ls_sqlFee)
l_cacheFeeCod = []
l_cacheFeeSql = []
for i_fee in l_fee:
l_cacheFeeSql.append(
(" sum(case (p.fee_cod = %s and p.fee_typ = '%s')" +
' when true then amount else 0 end) "%s" ') %
(str(i_fee[0]), str(i_fee[1]), str(i_fee[0])))
l_cacheFeeCod.append(str(i_fee[0]))
if len(l_cacheFeeCod) > 0:
ls_client_q = ("p.client_id = %s and" %
ls_clientId) if len(ls_clientId.strip()) > 0 else ""
ls_feeType_q = ("p.fee_typ = '%s' and" %
ls_feeType) if len(ls_feeType.strip()) > 0 else ""
ls_sqlAll = ''' select c.bill_no,%s,
sum(case fee_typ when 'I' then amount else 0 end) zongji_in,
sum(case fee_typ when 'O' then amount else 0 end) zongji_out,
sum(case fee_typ when 'I' then amount else 0-amount end) zongji_gain
from pre_fee as p,contract as c
where %s %s p.ex_feeid = 'O'
and (c.finish_time between '%s' and '%s')
and p.contract_id = c.id
group by c.bill_no
''' % (",".join(l_cacheFeeSql), ls_client_q, ls_feeType_q,
ls_beginTim, ls_endTim)
# % ( ",".join(l_cacheFeeSql) , ",".join(l_cacheFeeCod), ls_client_q, ls_feeType_q, ls_beginTim, ls_endTim )
l_result = rawSql2JsonDict(ls_sqlAll)
l_zongji_in = sum([float(i["zongji_in"]) for i in l_result])
l_zongji_out = sum([float(i["zongji_out"]) for i in l_result])
l_zongji_gain = sum([float(i["zongji_gain"]) for i in l_result])
l_rtn.update({
"msg":
"查询成功",
"error": [],
"stateCod":
1,
"rows":
l_result,
"footer": [{
"bill_no": "合计：",
"zongji_in": l_zongji_in,
"zongji_out": l_zongji_out,
"zongji_gain": l_zongji_gain
}]
})
else:
l_rtn.update({"msg": "没定义查询数据列。", "error": [], "stateCod": 0})
except Exception as e:
l_rtn.update({
"msg": "查询失败",
"error": list((str(e.args), )),
"stateCod": -1
})
return l_rtn