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