def rewrite_verify(request): #审核处理重写
#os.system("cmd /c D:/django/mysite/mana1/writemc.py")
myjson = simplejson.loads(request.POST["myjson"])
rs1 = trim_csv(myjson["table"], 7)
s = "" #存放门店名单
for rs in rs1:
if rs[0] == "1": #勾选的门店
s += "'" + rs[1] + "',"
s = s[0:-1]
#os.system('cmd.exe /c c:/ZSW/memcached/memcached.exe -m 256 -p 11211') #开启内存服务
html = ""
if s <> "":
writemc.Writemc(sqlstr=s)
result = confsql.runquery(u"select * from brainfo where 门店代码 in(" + s +
")") #获取门店综合信息 供门店审核是否需要重写
html = "<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html += "<tr><td><input type='checkbox'></td><td>" + str(
rs[0]) + "</td><td>" + str(
rs[1].encode("utf8")) + "</td><td>" + str(
rs[2]) + "</td><td>" + str(rs[3]) + "</td><td>" + str(
rs[4]) + "</td><td>" + str(rs[5]) + "</td></tr>"
return HttpResponse(html)
else:
writemc.Writemc() #全部重写
result = confsql.runquery(
"select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html = "<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html += "<tr><td><input type='checkbox'></td><td>" + str(
rs[0]) + "</td><td>" + str(
rs[1].encode("utf8")) + "</td><td>" + str(
rs[2]) + "</td><td>" + str(rs[3]) + "</td><td>" + str(
rs[4]) + "</td><td>" + str(rs[5]) + "</td></tr>"
return HttpResponse(html)
def rewrite_verify(request): #审核处理重写
#os.system("cmd /c D:/django/mysite/mana1/writemc.py")
myjson = simplejson.loads(request.POST["myjson"])
rs1=trim_csv(myjson["table"],7)
s="" #存放门店名单
for rs in rs1:
if rs[0]=="1": #勾选的门店
s+="'"+rs[1]+"',"
s=s[0:-1]
#os.system('cmd.exe /c c:/ZSW/memcached/memcached.exe -m 256 -p 11211') #开启内存服务
html=""
if s<>"":
writemc.Writemc(sqlstr=s)
result=confsql.runquery(u"select * from brainfo where 门店代码 in("+s+")") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
else:
writemc.Writemc() #全部重写
result=confsql.runquery("select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
def insult_maxminCuxiaori(request):
'查询每月促销日小下限翻倍'
t=get_template('mana1/insult_maxminCuxiaori.html')
sqlstr=""" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='sp') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join product_all t2
on t1.xcode=t2.proid
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.braxl as xname,'品牌小类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='braxl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select braxl_id, braxl from product_all group by braxl_id, braxl) t2
on t1.xcode=t2.braxl_id and t1.excode='braxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='proxl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select proxl_id, proxl from product_all group by proxl_id, proxl) t2
on t1.xcode=t2.proxl_id and t1.excode='proxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='prozl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select prozl_id, prozl from product_all group by prozl_id, prozl) t2
on t1.xcode=t2.prozl_id and t1.excode='prozl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='prodl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select prodl_id, prodl from product_all group by prodl_id, prodl) t2
on t1.xcode=t2.prodl_id and t1.excode='prodl'
"""
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_tianshu(request):
'查询拳头商品'
t=get_template('mana1/insult_tianshu.html')
sqlstr='select t1.braid,t2.braname,t1.anquankucun,t1.peisongzhouqi,t1.adddate from tianshu t1 left join branch t2 on t1.braid=t2.braid'
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_quantou(request):
'查询拳头商品'
t=get_template('mana1/insult_quantou.html')
sqlstr='select t1.proid,t2.proname,t1.adddate from quantou t1 left join product_all t2 on t1.proid=t2.proid'
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_tianshu(request):
'查询拳头商品'
t = get_template('mana1/insult_tianshu.html')
sqlstr = 'select t1.braid,t2.braname,t1.anquankucun,t1.peisongzhouqi,t1.adddate from tianshu t1 left join branch t2 on t1.braid=t2.braid'
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_shangxiaxian(request):
'程序计算上下限结果查询'
if request.method=='POST':
result=[]
mendian=request.POST['mendian']
res=confsql.runquery(sqlstr=" select t1.braid,t1.mdname,t1.proid,t1.spname,t1.prodl_id||'_'||t1.prodl,t1.prozl_id||'_'||t1.prozl,t1.proxl_id||'_'||t1.proxl,week1_qty,week2_qty,week3_qty,week4_qty,t1.maxval,t1.minval,t1.oldmaxval,t1.oldminval from xiaoshou28_maxmin t1 where braid='"+mendian+"' limit 20000")
for rs in res:
items={}
items['braid']=rs[0]
items['braname']=rs[1]
items['proid']=rs[2]
items['proname']=rs[3]
items['prodl']=rs[4]
items['prozl']=rs[5]
items['proxl']=rs[6]
items['week1_qty']=int(rs[7] or 0)
items['week2_qty']=int(rs[8] or 0)
items['week3_qty']=int(rs[9] or 0)
items['week4_qty']=int(rs[10] or 0)
items['maxval']=int(rs[11] or 0)
items['minval']=int(rs[12] or 0)
items['oldmaxval']=int(rs[13] or 0)
items['oldminval']=int(rs[14] or 0)
result.append(items)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t=get_template('mana1/insult_shangxiaxian.html')
html=t.render(Context())
return HttpResponse(html)
def insult_maxminCuxiaori(request):
'查询每月促销日小下限翻倍'
t = get_template('mana1/insult_maxminCuxiaori.html')
sqlstr = """ select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='sp') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join product_all t2
on t1.xcode=t2.proid
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.braxl as xname,'品牌小类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='braxl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select braxl_id, braxl from product_all group by braxl_id, braxl) t2
on t1.xcode=t2.braxl_id and t1.excode='braxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='proxl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select proxl_id, proxl from product_all group by proxl_id, proxl) t2
on t1.xcode=t2.proxl_id and t1.excode='proxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='prozl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select prozl_id, prozl from product_all group by prozl_id, prozl) t2
on t1.xcode=t2.prozl_id and t1.excode='prozl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode,max_multiple,min_multiple,t1.startdate, t1.enddate,remark
from (select * from maxminCuxiaori where excode='prodl') as t1 left outer join branch as t3
on (t1.mdcode=t3.braid) left outer join (select prodl_id, prodl from product_all group by prodl_id, prodl) t2
on t1.xcode=t2.prodl_id and t1.excode='prodl'
"""
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_quehuo(request):
""" 缺货查询 """
t=get_template('mana1/insult_quehuo.html')
sqlstr="select mdcode,mdname,barcode,spcode,spname,prodl_id||'_'||prodl as prodl,prozl_id||'_'||prozl as prozl,proxl_id||'_'||proxl as proxl,braxl_id||'_'||braxl as braxl,curqty from dhalldata where assortment='youtu' and curqty<0"
result=confsql.runquery(sqlstr)
log(result)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_quehuo(request):
""" 缺货查询 """
t = get_template('mana1/insult_quehuo.html')
sqlstr = "select mdcode,mdname,barcode,spcode,spname,prodl_id||'_'||prodl as prodl,prozl_id||'_'||prozl as prozl,proxl_id||'_'||proxl as proxl,braxl_id||'_'||braxl as braxl,curqty from dhalldata where assortment='youtu' and curqty<0"
result = confsql.runquery(sqlstr)
log(result)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_delivery(request):
''' 配送规则查询界面 '''
result = [] #存放初始表头
if request.method == 'POST':
jlist = simplejson.loads(request.POST["jsonlist"])
if jlist['braid'] <> '' or jlist['weekdelivery'] <> '' or jlist[
'braname'] <> '': #不全为空
sqlstr = 'select * from (select t1.braid,t2.braname,t1.weekdelivery,t1.adddate from delivery t1,branch t2 where t1.braid=t2.braid) t1 where '
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1:
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
else:
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip() + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4] #去除最后多余的and
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['braid'] = rs[0]
res['braname'] = rs[1]
res['weekdelivery'] = rs[2]
res['adddate'] = rs[3]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr = 'select * from delivery'
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['braid'] = rs[0]
res['braname'] = confsql.getbranamefrommc(rs[0])
res['weekdelivery'] = rs[1]
res['adddate'] = rs[2]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t = get_template('mana1/insult_delivery.html')
html = t.render(Context({'result': result}))
return HttpResponse(html)
def rewrite(request):
if request.method == 'POST':
#检查格式是否正确，并返回检查结果
rs1 = trim_csv(request.POST['value'], itemlenth=7)
rs2 = [] #存放最终结果
rs_temp = [] #存放临时数据
#不能有空
for rs in rs1:
if rs[0] == "" or rs[1] == "" or rs[2] == "" or rs[3] == "" or rs[
4] == "" or rs[5] == "" or rs[6] == "":
rs_temp.append(rs)
rs.append("不能有为空的项")
rs2.append(rs)
if len(rs_temp) > 0:
for rs in rs_temp:
rs1.remove(rs)
rs_temp = [] #存放临时数据
#门店代码为五位码
for rs in rs1:
if len(rs[1]) <> 5:
rs_temp.append(rs)
rs.append("门店代码应为五位码")
rs2.append(rs)
if len(rs_temp) > 0:
for rs in rs_temp:
rs1.remove(rs)
#没有问题的数据
for rs in rs1:
rs.append("")
rs2.append(rs)
s = "<table width='800'><tr><th>审核</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th><th>说明</th></tr>"
for rs in rs2:
if rs[0] == '0':
s += "<tr><td><input type='checkbox'></td>"
else:
s += "<tr><td><input type='checkbox' checked='checked'></td>"
s += "<td>" + rs[1] + "</td><td>" + rs[2] + "</td><td>" + rs[
3] + "</td><td>" + rs[4] + "</td><td>" + rs[
5] + "</td><td>" + rs[6] + "</td>"
if rs[7] == "":
s += "<td>" + rs[7] + "</td></tr>"
else:
s += "<td style='background-color:yellow'>" + rs[
7] + "</td></tr>"
s += "</table>"
return HttpResponse(s)
else:
t = get_template("mana1/rewrite.html")
#result=[{"门店代码":"00001","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434},{"门店代码":"00002","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434},{"门店代码":"00003","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434}]
result = confsql.runquery(
"select 门店代码, 门店名称, 品项数, 库存数量, 建议订货总量, 建议订货总额 from brainfo"
) #获取门店综合信息 供门店审核是否需要重写
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_delivery(request):
''' 配送规则查询界面 '''
result=[] #存放初始表头
if request.method=='POST':
jlist=simplejson.loads(request.POST["jsonlist"])
if jlist['braid']<>'' or jlist['weekdelivery']<>'' or jlist['braname']<>'': #不全为空
sqlstr='select * from (select t1.braid,t2.braname,t1.weekdelivery,t1.adddate from delivery t1,branch t2 where t1.braid=t2.braid) t1 where '
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1:
sqlstr+=j+" like '%%"+jlist[j].strip()+"%%' and "
else:
sqlstr+="("
for l in li:
sqlstr+=j+" like '%%"+l.strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4] #去除最后多余的and
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['braid']=rs[0]
res['braname']=rs[1]
res['weekdelivery']=rs[2]
res['adddate']=rs[3]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr='select * from delivery'
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['braid']=rs[0]
res['braname']=confsql.getbranamefrommc(rs[0])
res['weekdelivery']=rs[1]
res['adddate']=rs[2]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t=get_template('mana1/insult_delivery.html')
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_product_gl_packetqty_rules(request):
'查询商品配送单位规则'
t=get_template('mana1/insult_product_gl_packetqty_rules.html')
sqlstr="select t1.xcode,t2.proname,'商品代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.proid and excode='sp' group by t1.xcode,t2.proname,t1.packetqty1 union all "
sqlstr+="select t1.xcode,t2.proxl,'小类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.proxl_id and excode='xl' group by t1.xcode,t2.proxl,t1.packetqty1 union all "
sqlstr+="select t1.xcode,t2.prozl,'中类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.prozl_id and excode='zl' group by t1.xcode,t2.prozl,t1.packetqty1 union all "
sqlstr+="select t1.xcode,t2.prodl,'大类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.prodl_id and excode='dl' group by t1.xcode,t2.prodl,t1.packetqty1 "
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_product_gl_packetqty_rules(request):
'查询商品配送单位规则'
t = get_template('mana1/insult_product_gl_packetqty_rules.html')
sqlstr = "select t1.xcode,t2.proname,'商品代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.proid and excode='sp' group by t1.xcode,t2.proname,t1.packetqty1 union all "
sqlstr += "select t1.xcode,t2.proxl,'小类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.proxl_id and excode='xl' group by t1.xcode,t2.proxl,t1.packetqty1 union all "
sqlstr += "select t1.xcode,t2.prozl,'中类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.prozl_id and excode='zl' group by t1.xcode,t2.prozl,t1.packetqty1 union all "
sqlstr += "select t1.xcode,t2.prodl,'大类代码',round(t1.packetqty1) from product_gl_packetqty_rules t1,product_all t2 where t1.xcode=t2.prodl_id and excode='dl' group by t1.xcode,t2.prodl,t1.packetqty1 "
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_dhrules(request):
'查询订货量修改规则'
t=get_template('mana1/insult_dhrules.html')
sqlstr=" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid), product_all t2 where t1.xcode=t2.proid and t1.excode='sp' union all "
sqlstr+=" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select proxl_id, proxl from product_all group by proxl_id, proxl) t2 where t1.xcode=t2.proxl_id and t1.excode='xl' union all "
sqlstr+=" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prozl_id, prozl from product_all group by prozl_id, prozl) t2 where t1.xcode=t2.prozl_id and t1.excode='zl' union all "
sqlstr+=" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prodl_id, prodl from product_all group by prodl_id, prodl) t2 where t1.xcode=t2.prodl_id and t1.excode='dl' union all"
sqlstr+=" select t1.mdcode, case when t3.braname is null then '' else t3.braname end, t1.xcode, '' as xname, '' as excode , case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end , t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) where t1.xcode =''"
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_dhrules(request):
'查询订货量修改规则'
t = get_template('mana1/insult_dhrules.html')
sqlstr = " select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid), product_all t2 where t1.xcode=t2.proid and t1.excode='sp' union all "
sqlstr += " select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select proxl_id, proxl from product_all group by proxl_id, proxl) t2 where t1.xcode=t2.proxl_id and t1.excode='xl' union all "
sqlstr += " select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prozl_id, prozl from product_all group by prozl_id, prozl) t2 where t1.xcode=t2.prozl_id and t1.excode='zl' union all "
sqlstr += " select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码',case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end ,t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prodl_id, prodl from product_all group by prodl_id, prodl) t2 where t1.xcode=t2.prodl_id and t1.excode='dl' union all"
sqlstr += " select t1.mdcode, case when t3.braname is null then '' else t3.braname end, t1.xcode, '' as xname, '' as excode , case when t1.yqkey = 'maxlimit' then '上阈值' when t1.yqkey = 'minlimit' then '下阈值' end , case when t1.yqrule='jd' then '绝对值' when t1.yqrule='xd' then '相对值' end , t1.yqvalue from dhrules as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) where t1.xcode =''"
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_basedisplay(request):
'查询订货量修改规则'
t=get_template('mana1/insult_basedisplay.html')
sqlstr="""
select t1.braid,t3.braname,t1.proid,t2.proname,t1.basedisplay
from basedisplay t1 left join product_all t2
on t1.proid=t2.proid left join branch t3 on t1.braid=t3.braid
group by t1.braid,t3.braname,t1.proid,t2.proname,t1.basedisplay
"""
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_basedisplay(request):
'查询订货量修改规则'
t = get_template('mana1/insult_basedisplay.html')
sqlstr = """
select t1.braid,t3.braname,t1.proid,t2.proname,t1.basedisplay
from basedisplay t1 left join product_all t2
on t1.proid=t2.proid left join branch t3 on t1.braid=t3.braid
group by t1.braid,t3.braname,t1.proid,t2.proname,t1.basedisplay
"""
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def rewrite(request):
if request.method=='POST':
#检查格式是否正确，并返回检查结果
rs1=trim_csv(request.POST['value'],itemlenth=7)
rs2=[] #存放最终结果
rs_temp=[] #存放临时数据
#不能有空
for rs in rs1:
if rs[0]=="" or rs[1]=="" or rs[2]=="" or rs[3]=="" or rs[4]=="" or rs[5]=="" or rs[6]=="":
rs_temp.append(rs)
rs.append("不能有为空的项")
rs2.append(rs)
if len(rs_temp)>0:
for rs in rs_temp:
rs1.remove(rs)
rs_temp=[] #存放临时数据
#门店代码为五位码
for rs in rs1:
if len(rs[1])<>5:
rs_temp.append(rs)
rs.append("门店代码应为五位码")
rs2.append(rs)
if len(rs_temp)>0:
for rs in rs_temp:
rs1.remove(rs)
#没有问题的数据
for rs in rs1:
rs.append("")
rs2.append(rs)
s="<table width='800'><tr><th>审核</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th><th>说明</th></tr>"
for rs in rs2:
if rs[0]=='0':
s+="<tr><td><input type='checkbox'></td>"
else:
s+="<tr><td><input type='checkbox' checked='checked'></td>"
s+="<td>"+rs[1]+"</td><td>"+rs[2]+"</td><td>"+rs[3]+"</td><td>"+rs[4]+"</td><td>"+rs[5]+"</td><td>"+rs[6]+"</td>"
if rs[7]=="":
s+="<td>"+rs[7]+"</td></tr>"
else:
s+="<td style='background-color:yellow'>"+rs[7]+"</td></tr>"
s+="</table>"
return HttpResponse(s)
else:
t=get_template("mana1/rewrite.html")
#result=[{"门店代码":"00001","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434},{"门店代码":"00002","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434},{"门店代码":"00003","品项数":3500,"库存数量":23340,"库存金额":4337979.32,"建议订货总量":2344,"建议订货总额":34345434}]
result=confsql.runquery("select 门店代码, 门店名称, 品项数, 库存数量, 建议订货总量, 建议订货总额 from brainfo") #获取门店综合信息 供门店审核是否需要重写
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_dhrulesYuzhi(request):
'查询暂停补货范围规则'
t = get_template('mana1/insult_dhrulesYuzhi.html')
sqlstr = u""" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid), product_all t2 where t1.xcode=t2.proid and t1.excode='sp'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.braxl as xname,'品牌小类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select braxl_id, braxl from product_all group by braxl_id, braxl) t2 where t1.xcode=t2.braxl_id and t1.excode='braxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select proxl_id, proxl from product_all group by proxl_id, proxl) t2 where t1.xcode=t2.proxl_id and t1.excode='proxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prozl_id, prozl from product_all group by prozl_id, prozl) t2 where t1.xcode=t2.prozl_id and t1.excode='prozl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prodl_id, prodl from product_all group by prodl_id, prodl) t2 where t1.xcode=t2.prodl_id and t1.excode='prodl'
"""
result = confsql.runquery(sqlstr)
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_dhrulesYuzhi(request):
'查询暂停补货范围规则'
t=get_template('mana1/insult_dhrulesYuzhi.html')
sqlstr=u""" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid), product_all t2 where t1.xcode=t2.proid and t1.excode='sp'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.braxl as xname,'品牌小类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select braxl_id, braxl from product_all group by braxl_id, braxl) t2 where t1.xcode=t2.braxl_id and t1.excode='braxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select proxl_id, proxl from product_all group by proxl_id, proxl) t2 where t1.xcode=t2.proxl_id and t1.excode='proxl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prozl_id, prozl from product_all group by prozl_id, prozl) t2 where t1.xcode=t2.prozl_id and t1.excode='prozl'
union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode,t1.yqkey,t1.yqrule,t1.yqvalue,t1.startdate, t1.enddate,t1.remark,t1.adddate from dhrulesYuzhi as t1 left outer join branch as t3 on (t1.mdcode=t3.braid) ,(select prodl_id, prodl from product_all group by prodl_id, prodl) t2 where t1.xcode=t2.prodl_id and t1.excode='prodl'
"""
result=confsql.runquery(sqlstr)
html=t.render(Context({'result':result}))
return HttpResponse(html)
def insult_dhPauserules(request):
'查询暂停补货范围规则'
result=[]
sqlstr=u""" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='sp') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join product_all t2 on t1.xcode=t2.proid union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode, t1.startdate, t1.enddate from( select * from dhpauserules where excode='xl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select proxl_id, proxl from product_all group by proxl_id, proxl) t2 on t1.xcode=t2.proxl_id union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='zl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select prozl_id, prozl from product_all group by prozl_id, prozl) t2 on t1.xcode=t2.prozl_id union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='dl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select prodl_id, prodl from product_all group by prodl_id, prodl) t2 on t1.xcode=t2.prodl_id union all
select t1.mdcode, case when t3.braname is null then '' else t3.braname end, t1.xcode, '' as xname, '' as excode , t1.startdate, t1.enddate from (select * from dhpauserules where xcode ='') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) """
log(sqlstr)
result=confsql.runquery(sqlstr)
rec=[]
for rs in result:
res={}
res['braid']=rs[0]
res['braname']=rs[1]
res['xcode']=rs[2]
res['xname']=rs[3]
res['excode']=rs[4]
res['startdate']=rs[5]
res['enddate']=rs[6]
rec.append(res)
t=get_template('mana1/insult_dhPauserules.html')
html=t.render(Context({"rec":rec}))
return HttpResponse(html)
def insult_dhPauserules(request):
'查询暂停补货范围规则'
result = []
sqlstr = u""" select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proname as xname,'商品代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='sp') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join product_all t2 on t1.xcode=t2.proid union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.proxl as xname,'小类代码' as excode, t1.startdate, t1.enddate from( select * from dhpauserules where excode='xl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select proxl_id, proxl from product_all group by proxl_id, proxl) t2 on t1.xcode=t2.proxl_id union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prozl as xname,'中类代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='zl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select prozl_id, prozl from product_all group by prozl_id, prozl) t2 on t1.xcode=t2.prozl_id union all
select t1.mdcode,case when t3.braname is null then '' else t3.braname end,t1.xcode,t2.prodl as xname,'大类代码' as excode, t1.startdate, t1.enddate from (select * from dhpauserules where excode='dl') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) left outer join (select prodl_id, prodl from product_all group by prodl_id, prodl) t2 on t1.xcode=t2.prodl_id union all
select t1.mdcode, case when t3.braname is null then '' else t3.braname end, t1.xcode, '' as xname, '' as excode , t1.startdate, t1.enddate from (select * from dhpauserules where xcode ='') t1 left outer join branch as t3 on (t1.mdcode=t3.braid) """
log(sqlstr)
result = confsql.runquery(sqlstr)
rec = []
for rs in result:
res = {}
res['braid'] = rs[0]
res['braname'] = rs[1]
res['xcode'] = rs[2]
res['xname'] = rs[3]
res['excode'] = rs[4]
res['startdate'] = rs[5]
res['enddate'] = rs[6]
rec.append(res)
t = get_template('mana1/insult_dhPauserules.html')
html = t.render(Context({"rec": rec}))
return HttpResponse(html)
def insult_checkAdvMaxMin(request):
if request.method=='POST':
result=[] #存放初始表头
jlist=simplejson.loads(request.POST["jsonlist"])
if jlist['spcode']<>'' or jlist['proname']<>'' or jlist['mdcode']<>'' or jlist['braname']<>'' or jlist['applyfordate']<>'' or jlist['verifydate']<>'': #不全为空
sqlstr="select t1.* from (select t1.mdcode,t2.braname,t1.spcode,t3.proname,t3.prodl_id||'_'||t3.prodl,t3.prozl_id||'_'||t3.prozl,t3.proxl_id||'_'||t3.proxl,t1.oldmaxval,t1.oldminval,t1.newmaxval,t1.newminval,t1.applyfordate,t1.verifydate, case when t1.remarks = 'save' then '同意' when t1.remarks = 'del' then '不同意' else '未审核' end as remarks from advmaxmin t1,branch t2,product_all t3 where t1.mdcode=t2.braid and t1.spcode=t3.proid) as t1 where "
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1: #一个条件
sqlstr+= "t1." + j.encode('utf-8') + " like '%%"+jlist[j].encode('utf-8').strip()+"%%' and "
else: #逗号分隔的多条件
sqlstr+="("
for l in li:
sqlstr+= "t1."+j.encode('utf-8')+" like '%%"+l.encode('utf-8').strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4]
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['mdcode']=rs[0]
res['braname']=rs[1]
res['spcode']=rs[2]
res['proname']=rs[3]
res['prodl']=rs[4]
res['prozl']=rs[5]
res['proxl']=rs[6]
res['oldmaxval']=rs[7]
res['oldminval']=rs[8]
res['newmaxval']=rs[9]
res['newminval']=rs[10]
res['applyfordate']=rs[11]
res['verifyDate']=rs[12]
res['remarks'] = rs[13]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr="select t1.mdcode,t2.braname,t1.spcode,t3.proname,t3.prodl_id||'_'||t3.prodl,t3.prozl_id||'_'||t3.prozl,t3.proxl_id||'_'||t3.proxl,t1.oldmaxval,t1.oldminval,t1.newmaxval,t1.newminval,t1.applyfordate,t1.verifyDate, case when t1.remarks = 'save' then '同意' when t1.remarks = 'del' then '不同意' else '未审核' end as remarks from advmaxmin t1,branch t2,product_all t3 where t1.mdcode=t2.braid and t1.spcode=t3.proid limit 10000"
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['mdcode']=rs[0]
res['braname']=rs[1]
res['spcode']=rs[2]
res['proname']=rs[3]
res['prodl']=rs[4]
res['prozl']=rs[5]
res['proxl']=rs[6]
res['oldmaxval']=rs[7]
res['oldminval']=rs[8]
res['newmaxval']=rs[9]
res['newminval']=rs[10]
res['applyfordate']=rs[11]
res['verifyDate']=rs[12]
res['remarks'] = rs[13]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t=get_template('mana1/insult_checkadvmaxmin.html')
html=t.render(Context())
return HttpResponse(html)
def backup_maxmin(request):
'备份上下限'
if request.method=='POST':
try:
jlist=simplejson.loads(request.POST["jsonlist"])
if jlist['proid']<>'' or jlist['proname']<>'' or jlist['braid']<>'' or jlist['braname']<>'' or jlist['prodl']<>'' or jlist['prozl']<>'' or jlist['proxl']<>'': #不全为空
sqlstr="""
insert into maxmin_bak
select braid,proid,maxval,minval,banben,startdate,enddate,adddate,'""" + datetime.date.today().strftime('%Y-%m-%d') + """' from(
select * from (
select t1.braid,t2.braname,t1.proid,t3.proname,t3.prodl_id||'_'||t3.prodl as prodl,t3.prozl_id||'_'||t3.prozl as prozl,t3.proxl_id||'_'||t3.proxl as proxl,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate
from maxmin t1,branch t2,product_all t3
where t1.braid=t2.braid and t1.proid=t3.proid
) t1 where
"""
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1: #一个条件
sqlstr+=j+" like '%%"+jlist[j].strip()+"%%' and "
else: #逗号分隔的多条件
sqlstr+="("
for l in li:
sqlstr+=j+" like '%%"+l.strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4]
sqlstr+=") tt1"
confsql.runSql(sqlstr)
sqlstr="""
select count(*) from (
select t1.braid,t2.braname,t1.proid,t3.proname,t3.prodl_id||'_'||t3.prodl as prodl,t3.prozl_id||'_'||t3.prozl as prozl,t3.proxl_id||'_'||t3.proxl as proxl,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate
from maxmin t1,branch t2,product_all t3
where t1.braid=t2.braid and t1.proid=t3.proid
) t1 where
"""
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1: #一个条件
sqlstr+=j+" like '%%"+jlist[j].strip()+"%%' and "
else: #逗号分隔的多条件
sqlstr+="("
for l in li:
sqlstr+=j+" like '%%"+l.strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4]
result=confsql.runquery(sqlstr)
for rs in result:
num=rs[0]
return HttpResponse(num)
else: #全为空，查询所有
sqlstr="""
insert into maxmin_bak
select braid,proid,maxval,minval,banben,startdate,enddate,adddate,'""" + datetime.date.today().strftime('%Y-%m-%d') + """'
from maxmin
"""
confsql.runSql(sqlstr)
sqlstr="select count(*) from maxmin"
result=confsql.runquery(sqlstr)
for rs in result:
num=rs[0]
return HttpResponse(num)
except:
return HttpResponse(0)
else:
t=get_template('mana1/backup_maxmin.html')
html=t.render(Context())
return HttpResponse(html)
def cleanmc_verify(request): #审核处理清空内存建议值
#os.system("cmd /c D:/django/mysite/mana1/writemc.py")
myjson = simplejson.loads(request.POST["myjson"])
rs1=trim_csv(myjson["table"],7)
s=[] #存放门店名单
for rs in rs1:
if rs[0]=="1": #勾选的门店
s.append(rs[1])
html=""
if len(s)>0:
for rs in s:
#清空数据库指定建议值及内存指定建议值
result=confsql.runquery("select * from sugvalue where braid='"+str(rs)+"'")
for rs in result:
mc.delete(str(rs['braid'])+"__"+str(rs['proid'])+"__sugvalue") #先根据数据库清内存
confsql.runquery("delete from sugvalue where braid='"+str(rs['braid'])+"'") #后清数据库
confsql.runquery("delete from brainfo where 门店代码='"+str(rs['braid'])+"'")
result=confsql.runquery("select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
else:
#全部重写
result=confsql.runquery("select * from sugvalue")
for rs in result:
mc.delete(str(rs['braid'])+"__"+str(rs['proid'])+"__sugvalue")
confsql.runquery("delete from sugvalue")
confsql.runquery("delete from brainfo")
result=confsql.runquery("select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
def cleanmc_verify(request): #审核处理清空内存建议值
myjson = simplejson.loads(request.POST["myjson"])
rs1=functions.trim_csv(myjson["table"],7)
s=[] #存放门店名单
for rs in rs1:
if rs[0]=="1": #勾选的门店
s.append(rs[1])
html=""
if len(s)>0:
for rs in s:
#清空数据库指定建议值及内存指定建议值
result=confsql.runquery("select * from sugvalue where braid='"+str(rs)+"'")
for rs in result:
mc.delete(str(rs['braid'])+"__"+str(rs['proid'])+"__sugvalue") #先根据数据库清内存
confsql.runquery("delete from sugvalue where braid='"+str(rs['braid'])+"'") #后清数据库
confsql.runquery("delete from brainfo where 门店代码='"+str(rs['braid'])+"'")
result=confsql.runquery("select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
else:
#全部重写
result=confsql.runquery("select * from sugvalue")
for rs in result:
mc.delete(str(rs['braid'])+"__"+str(rs['proid'])+"__sugvalue")
confsql.runquery("delete from sugvalue")
confsql.runquery("delete from brainfo")
result=confsql.runquery("select * from brainfo") #获取门店综合信息 供门店审核是否需要重写
html="<tr><th>重算</th><th>门店代码</th><th>门店名称</th><th>品项数</th><th>库存数量</th><th>建议订货总量</th><th>建议订货总额</th></tr>"
for rs in result:
html+="<tr><td><input type='checkbox'></td><td>"+str(rs[0])+"</td><td>"+str(rs[1].encode("utf8"))+"</td><td>"+str(rs[2])+"</td><td>"+str(rs[3])+"</td><td>"+str(rs[4])+"</td><td>"+str(rs[5])+"</td></tr>"
return HttpResponse(html)
def insult_sugvalue(request):
""" 建议值查询界面 """
result = [] # 存放初始表头
if request.method == "POST":
jlist = simplejson.loads(request.POST["jsonlist"])
ty = request.POST["ty"]
if ty == "sub": # 非聚集
if jlist["spcode"] <> "" or jlist["spname"] <> "" or jlist["mdcode"] <> "" or jlist["mdname"] <> "": # 不全为空
sqlstr = "select mdcode, mdname, spcode, spname, suggest, suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and delivery = 'T' and "
for j in jlist:
if jlist[j] <> "":
li = jlist[j].split(",")
if len(li) == 1:
sqlstr += j + " like '%%" + jlist[j].strip() + "%%' and "
else:
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip() + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4] # 去除最后多余的and
# sqlstr += " suggest>0 "
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res["门店代码"] = rs[0]
res["门店名称"] = rs[1]
res["商品代码"] = rs[2]
res["商品名称"] = rs[3]
res["建议配货量"] = rs[4]
res["配货总金额"] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
# sqlstr='select * from sugvalue'
sqlstr = "select mdcode, mdname, spcode, spname, suggest, suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and delivery = 'T'"
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res["门店代码"] = rs[0]
res["门店名称"] = rs[1]
res["商品代码"] = rs[2]
res["商品名称"] = rs[3]
res["建议配货量"] = rs[4]
res["配货总金额"] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else: # 聚集结果
if jlist["spcode"] <> "" or jlist["spname"] <> "" or jlist["mdcode"] <> "" or jlist["mdname"] <> "": # 不全为空
items = ""
dic = {"spcode": "商品代码", "spname": "商品名称", "mdcode": "门店代码", "mdname": "门店名称"}
for j in jlist:
if jlist[j] <> "":
items += j + ","
items = items[0:-1]
sqlstr = (
"select "
+ items
+ ", sum(suggest) as suggest,sum(suggestcost) as suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and "
)
for j in jlist:
if jlist[j] <> "":
sqlstr += j + " like '%%" + jlist[j].strip() + "%%' and "
sqlstr = sqlstr[0:-4] # 去除最后多余的and
# sqlstr += " suggest>0 "
sqlstr += "group by " + items
lines = confsql.runquery(sqlstr)
for rs in lines:
i = 0
res = {}
for j in jlist:
if jlist[j] <> "":
res = {}
res[dic[j]] = rs[i]
i += 1
res["建议配货量"] = rs[i]
res["配货总金额"] = rs[i + 1]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
# sqlstr='select * from sugvalue'
sqlstr = "select * from sugvalue where suggest>0 "
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res["门店代码"] = rs[0]
res["门店名称"] = rs[1]
res["商品代码"] = rs[2]
res["商品名称"] = rs[3]
res["建议配货量"] = rs[4]
res["配货总金额"] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t = get_template("mana1/insult_sugvalue.html")
html = t.render(Context({"result": result}))
return HttpResponse(html)
def insult_sugvalue(request):
''' 建议值查询界面 '''
result=[] #存放初始表头
if request.method=='POST':
jlist=simplejson.loads(request.POST["jsonlist"])
ty=request.POST["ty"]
if ty=="sub": #非聚集
if jlist['proid']<>'' or jlist['proname']<>'' or jlist['braid']<>'' or jlist['braname']<>'': #不全为空
sqlstr='select * from sugvalue where suggest>0 and '
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1:
sqlstr+=j+" like '%%"+jlist[j].strip()+"%%' and "
else:
sqlstr+="("
for l in li:
sqlstr+=j+" like '%%"+l.strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4] #去除最后多余的and
#sqlstr += " suggest>0 "
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['门店代码']=rs[0]
res['门店名称']=rs[1]
res['商品代码']=rs[2]
res['商品名称']=rs[3]
res['建议配货量']=rs[4]
res['配货总金额']=rs[5]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
#sqlstr='select * from sugvalue'
sqlstr='select * from sugvalue where suggest>0 '
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['门店代码']=rs[0]
res['门店名称']=rs[1]
res['商品代码']=rs[2]
res['商品名称']=rs[3]
res['建议配货量']=rs[4]
res['配货总金额']=rs[5]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else: #聚集结果
if jlist['proid']<>'' or jlist['proname']<>'' or jlist['braid']<>'' or jlist['braname']<>'': #不全为空
items=""
dic={'proid':'商品代码','proname':'商品名称','braid':'门店代码','braname':'门店名称'}
for j in jlist:
if jlist[j]<>'':
items+=j+","
items=items[0:-1]
sqlstr='select '+items+',sum(suggest) as suggest,sum(suggestcost) as suggestcost from sugvalue where suggest>0 and '
for j in jlist:
if jlist[j]<>'':
sqlstr+=j+" like '%%"+jlist[j].strip() +"%%' and "
sqlstr=sqlstr[0:-4] #去除最后多余的and
#sqlstr += " suggest>0 "
sqlstr+="group by "+items
lines=confsql.runquery(sqlstr)
for rs in lines:
i=0
res={}
for j in jlist:
if jlist[j]<>'':
res={}
res[dic[j]]=rs[i]
i+=1
res['建议配货量']=rs[i]
res['配货总金额']=rs[i+1]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
#sqlstr='select * from sugvalue'
sqlstr='select * from sugvalue where suggest>0 '
lines=confsql.runquery(sqlstr)
for rs in lines:
res={}
res['门店代码']=rs[0]
res['门店名称']=rs[1]
res['商品代码']=rs[2]
res['商品名称']=rs[3]
res['建议配货量']=rs[4]
res['配货总金额']=rs[5]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t=get_template('mana1/insult_sugvalue.html')
html=t.render(Context({'result':result}))
return HttpResponse(html)
def backup_maxmin(request):
'备份上下限'
if request.method == 'POST':
try:
jlist = simplejson.loads(request.POST["jsonlist"])
if jlist['proid'] <> '' or jlist['proname'] <> '' or jlist[
'braid'] <> '' or jlist['braname'] <> '' or jlist[
'prodl'] <> '' or jlist['prozl'] <> '' or jlist[
'proxl'] <> '': #不全为空
sqlstr = """
insert into maxmin_bak
select braid,proid,maxval,minval,banben,startdate,enddate,adddate,'""" + datetime.date.today(
).strftime('%Y-%m-%d') + """' from(
select * from (
select t1.braid,t2.braname,t1.proid,t3.proname,t3.prodl_id||'_'||t3.prodl as prodl,t3.prozl_id||'_'||t3.prozl as prozl,t3.proxl_id||'_'||t3.proxl as proxl,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate
from maxmin t1,branch t2,product_all t3
where t1.braid=t2.braid and t1.proid=t3.proid
) t1 where
"""
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1: #一个条件
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
else: #逗号分隔的多条件
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip(
) + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4]
sqlstr += ") tt1"
confsql.runSql(sqlstr)
sqlstr = """
select count(*) from (
select t1.braid,t2.braname,t1.proid,t3.proname,t3.prodl_id||'_'||t3.prodl as prodl,t3.prozl_id||'_'||t3.prozl as prozl,t3.proxl_id||'_'||t3.proxl as proxl,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate
from maxmin t1,branch t2,product_all t3
where t1.braid=t2.braid and t1.proid=t3.proid
) t1 where
"""
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1: #一个条件
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
else: #逗号分隔的多条件
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip(
) + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4]
result = confsql.runquery(sqlstr)
for rs in result:
num = rs[0]
return HttpResponse(num)
else: #全为空，查询所有
sqlstr = """
insert into maxmin_bak
select braid,proid,maxval,minval,banben,startdate,enddate,adddate,'""" + datetime.date.today(
).strftime('%Y-%m-%d') + """'
from maxmin
"""
confsql.runSql(sqlstr)
sqlstr = "select count(*) from maxmin"
result = confsql.runquery(sqlstr)
for rs in result:
num = rs[0]
return HttpResponse(num)
except:
return HttpResponse(0)
else:
t = get_template('mana1/backup_maxmin.html')
html = t.render(Context())
return HttpResponse(html)
def insult_sugvalue(request):
''' 建议值查询界面 '''
result = [] #存放初始表头
if request.method == 'POST':
jlist = simplejson.loads(request.POST["jsonlist"])
ty = request.POST["ty"]
if ty == "sub": #非聚集
if jlist['spcode'] <> '' or jlist['spname'] <> '' or jlist[
'mdcode'] <> '' or jlist['mdname'] <> '': #不全为空
sqlstr = "select mdcode, mdname, spcode, spname, suggest, suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and delivery = 'T' and "
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1:
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
else:
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip(
) + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4] #去除最后多余的and
#sqlstr += " suggest>0 "
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['门店代码'] = rs[0]
res['门店名称'] = rs[1]
res['商品代码'] = rs[2]
res['商品名称'] = rs[3]
res['建议配货量'] = rs[4]
res['配货总金额'] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
#sqlstr='select * from sugvalue'
sqlstr = "select mdcode, mdname, spcode, spname, suggest, suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and delivery = 'T'"
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['门店代码'] = rs[0]
res['门店名称'] = rs[1]
res['商品代码'] = rs[2]
res['商品名称'] = rs[3]
res['建议配货量'] = rs[4]
res['配货总金额'] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else: #聚集结果
if jlist['spcode'] <> '' or jlist['spname'] <> '' or jlist[
'mdcode'] <> '' or jlist['mdname'] <> '': #不全为空
items = ""
dic = {
'spcode': '商品代码',
'spname': '商品名称',
'mdcode': '门店代码',
'mdname': '门店名称'
}
for j in jlist:
if jlist[j] <> '':
items += j + ","
items = items[0:-1]
sqlstr = "select " + items + ", sum(suggest) as suggest,sum(suggestcost) as suggestcost from dhalldata where assortment is not null and status < '5' and suggest > 0 and shelf > '-1' and dhtag='T' and "
for j in jlist:
if jlist[j] <> '':
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
sqlstr = sqlstr[0:-4] #去除最后多余的and
#sqlstr += " suggest>0 "
sqlstr += "group by " + items
lines = confsql.runquery(sqlstr)
for rs in lines:
i = 0
res = {}
for j in jlist:
if jlist[j] <> '':
res = {}
res[dic[j]] = rs[i]
i += 1
res['建议配货量'] = rs[i]
res['配货总金额'] = rs[i + 1]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
#sqlstr='select * from sugvalue'
sqlstr = 'select * from sugvalue where suggest>0 '
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['门店代码'] = rs[0]
res['门店名称'] = rs[1]
res['商品代码'] = rs[2]
res['商品名称'] = rs[3]
res['建议配货量'] = rs[4]
res['配货总金额'] = rs[5]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t = get_template('mana1/insult_sugvalue.html')
html = t.render(Context({'result': result}))
return HttpResponse(html)
def insult_maxmin(request):
''' 上下限查询界面 '''
if request.method == 'POST':
try:
jlist = simplejson.loads(request.POST["jsonlist"])
if jlist['proid'] <> '' or jlist['proname'] <> '' or jlist[
'braid'] <> '' or jlist['braname'] <> '' or jlist[
'banben'] <> '' or jlist['prodl'] <> '' or jlist[
'prozl'] <> '' or jlist['proxl'] <> '' or jlist[
'barcode'] <> '': #不全为空,即按单个或多个条件查询
sqlstr = "select * from (select t1.braid,t2.braname,t3.barcode,t1.proid,t3.proname,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate ,t3.prodl_id||'_'||t3.prodl as prodl, t3.prozl_id||'_'||t3.prozl as prozl, t3.proxl_id||'_'||t3.proxl as proxl from maxmin t1 left outer join branch t2 on t1.braid=t2.braid left outer join product_all t3 on t1.proid=t3.proid) t1 where "
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1: #一个条件
sqlstr += j + " like '%%" + jlist[j].strip(
) + "%%' and "
else: #逗号分隔的多条件
sqlstr += "("
for l in li:
sqlstr += j + " like '%%" + l.strip(
) + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4]
sqlstr += " limit 20000"
lines = confsql.runquery(sqlstr)
result = [] #存放初始表头
for rs in lines:
res = {}
res['braid'] = rs[0]
res['braname'] = rs[1]
res['barcode'] = rs[2]
res['proid'] = rs[3]
res['proname'] = rs[4]
res['maxval'] = rs[5]
res['minval'] = rs[6]
res['banben'] = rs[7]
res['startdate'] = rs[8]
res['enddate'] = rs[9]
res['adddate'] = rs[10]
res['prodl'] = rs[11]
res['prozl'] = rs[12]
res['proxl'] = rs[13]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr = "select t1.braid,t2.braname,t3.barcode,t1.proid,t3.proname,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate,t3.prodl_id||'_'||t3.prodl as prodl, t3.prozl_id||'_'||t3.prozl as prozl, t3.proxl_id||'_'||t3.proxl as proxl from maxmin t1 left outer join branch t2 on t1.braid=t2.braid left outer join product_all t3 on t1.proid=t3.proid limit 20000"
lines = confsql.runquery(sqlstr)
result = [] #存放初始表头
for rs in lines:
res = {}
res['braid'] = rs[0]
res['braname'] = rs[1]
res['barcode'] = rs[2]
res['proid'] = rs[3]
res['proname'] = rs[4]
res['maxval'] = rs[5]
res['minval'] = rs[6]
res['banben'] = rs[7]
res['startdate'] = rs[8]
res['enddate'] = rs[9]
res['adddate'] = rs[10]
res['prodl'] = rs[11]
res['prozl'] = rs[12]
res['proxl'] = rs[13]
result.append(res)
jsonres = simplejson.dumps(result)
log(jsonres)
return HttpResponse(jsonres)
except:
return HttpResponse(0)
else:
t = get_template('mana1/insult_maxmin.html')
html = t.render(Context())
return HttpResponse(html)
def insult_checkAdvMaxMin(request):
if request.method == 'POST':
result = [] #存放初始表头
jlist = simplejson.loads(request.POST["jsonlist"])
if jlist['spcode'] <> '' or jlist['proname'] <> '' or jlist[
'mdcode'] <> '' or jlist['braname'] <> '' or jlist[
'applyfordate'] <> '' or jlist['verifydate'] <> '': #不全为空
sqlstr = "select t1.* from (select t1.mdcode,t2.braname,t1.spcode,t3.proname,t3.prodl_id||'_'||t3.prodl,t3.prozl_id||'_'||t3.prozl,t3.proxl_id||'_'||t3.proxl,t1.oldmaxval,t1.oldminval,t1.newmaxval,t1.newminval,t1.applyfordate,t1.verifydate, case when t1.remarks = 'save' then '同意' when t1.remarks = 'del' then '不同意' else '未审核' end as remarks from advmaxmin t1,branch t2,product_all t3 where t1.mdcode=t2.braid and t1.spcode=t3.proid) as t1 where "
for j in jlist:
if jlist[j] <> '':
li = jlist[j].split(",")
if len(li) == 1: #一个条件
sqlstr += "t1." + j.encode(
'utf-8') + " like '%%" + jlist[j].encode(
'utf-8').strip() + "%%' and "
else: #逗号分隔的多条件
sqlstr += "("
for l in li:
sqlstr += "t1." + j.encode(
'utf-8') + " like '%%" + l.encode(
'utf-8').strip() + "%%' or "
sqlstr = sqlstr[0:-4] + ") and "
sqlstr = sqlstr[0:-4]
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['mdcode'] = rs[0]
res['braname'] = rs[1]
res['spcode'] = rs[2]
res['proname'] = rs[3]
res['prodl'] = rs[4]
res['prozl'] = rs[5]
res['proxl'] = rs[6]
res['oldmaxval'] = rs[7]
res['oldminval'] = rs[8]
res['newmaxval'] = rs[9]
res['newminval'] = rs[10]
res['applyfordate'] = rs[11]
res['verifyDate'] = rs[12]
res['remarks'] = rs[13]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr = "select t1.mdcode,t2.braname,t1.spcode,t3.proname,t3.prodl_id||'_'||t3.prodl,t3.prozl_id||'_'||t3.prozl,t3.proxl_id||'_'||t3.proxl,t1.oldmaxval,t1.oldminval,t1.newmaxval,t1.newminval,t1.applyfordate,t1.verifyDate, case when t1.remarks = 'save' then '同意' when t1.remarks = 'del' then '不同意' else '未审核' end as remarks from advmaxmin t1,branch t2,product_all t3 where t1.mdcode=t2.braid and t1.spcode=t3.proid limit 10000"
lines = confsql.runquery(sqlstr)
for rs in lines:
res = {}
res['mdcode'] = rs[0]
res['braname'] = rs[1]
res['spcode'] = rs[2]
res['proname'] = rs[3]
res['prodl'] = rs[4]
res['prozl'] = rs[5]
res['proxl'] = rs[6]
res['oldmaxval'] = rs[7]
res['oldminval'] = rs[8]
res['newmaxval'] = rs[9]
res['newminval'] = rs[10]
res['applyfordate'] = rs[11]
res['verifyDate'] = rs[12]
res['remarks'] = rs[13]
result.append(res)
jsonres = simplejson.dumps(result)
return HttpResponse(jsonres)
else:
t = get_template('mana1/insult_checkadvmaxmin.html')
html = t.render(Context())
return HttpResponse(html)
def getspaceinfo(xcode, excode):
"根据各类代码,取得对应的货架信息"
if excode == 'sp':
sqlstr="select spaceid,spacename,assortment from dhalldata where spcode='"+xcode+"' and spaceid is not null group by spaceid,spacename,assortment"
result=confsql.runquery(sqlstr)
res=[]
for rs in result:
items={}
items['spaceid']=rs[0]
items['spacename']=rs[1]
items['assortment']=rs[2]
res.append(items)
if len(res) > 0:
return res
else:
sqlstr = "select braxl_id from product_all where proid='"+xcode+"'"
result=confsql.runquery(sqlstr)
if len(result) == 0:
return []
else:
xcode = result[0][0]
excode = 'pbxl'
return getspaceinfo(xcode, excode)
if excode == 'pbxl':
sqlstr="select spaceid,spacename,assortment from dhalldata where braxl_id='"+xcode+"' and spaceid is not null group by spaceid,spacename,assortment"
result=confsql.runquery(sqlstr)
res=[]
for rs in result:
items={}
items['spaceid']=rs[0]
items['spacename']=rs[1]
items['assortment']=rs[2]
res.append(items)
if len(res) > 0:
return res
else:
sqlstr = "select proxl_id from product_all where braxl_id='"+xcode+"'"
result=confsql.runquery(sqlstr)
if len(result) == 0:
return []
else:
xcode = result[0][0]
excode = 'xl'
return getspaceinfo(xcode, excode)
if excode == 'xl':
sqlstr="select spaceid,spacename,assortment from dhalldata where proxl_id='"+xcode+"' and spaceid is not null group by spaceid,spacename,assortment"
result=confsql.runquery(sqlstr)
res=[]
for rs in result:
items={}
items['spaceid']=rs[0]
items['spacename']=rs[1]
items['assortment']=rs[2]
res.append(items)
if len(res) > 0:
return res
else:
sqlstr = "select prozl_id from product_all where proxl_id='"+xcode+"'"
result=confsql.runquery(sqlstr)
if len(result) == 0:
return []
else:
xcode = result[0][0]
excode = 'zl'
return getspaceinfo(xcode, excode)
if excode == 'zl':
sqlstr="select spaceid,spacename,assortment from dhalldata where prozl_id='"+xcode+"' and spaceid is not null group by spaceid,spacename,assortment"
result=confsql.runquery(sqlstr)
res=[]
for rs in result:
items={}
items['spaceid']=rs[0]
items['spacename']=rs[1]
items['assortment']=rs[2]
res.append(items)
if len(res) > 0:
return res
else:
sqlstr = "select prodl_id from product_all where prozl_id='"+xcode+"'"
result=confsql.runquery(sqlstr)
if len(result) == 0:
return []
else:
xcode = result[0][0]
excode = 'dl'
return getspaceinfo(xcode, excode)
if excode == 'dl':
sqlstr="select spaceid,spacename,assortment from dhalldata where prodl_id='"+xcode+"' and spaceid is not null group by spaceid,spacename,assortment"
result=confsql.runquery(sqlstr)
res=[]
for rs in result:
items={}
items['spaceid']=rs[0]
items['spacename']=rs[1]
items['assortment']=rs[2]
res.append(items)
if len(res) > 0:
return res
else:
return []
def insult_maxmin(request):
''' 上下限查询界面 '''
if request.method=='POST':
try:
jlist=simplejson.loads(request.POST["jsonlist"])
if jlist['proid']<>'' or jlist['proname']<>'' or jlist['braid']<>'' or jlist['braname']<>'' or jlist['banben']<>'' or jlist['prodl']<>'' or jlist['prozl']<>'' or jlist['proxl']<>'' or jlist['barcode']<>'': #不全为空,即按单个或多个条件查询
sqlstr="select * from (select t1.braid,t2.braname,t3.barcode,t1.proid,t3.proname,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate ,t3.prodl_id||'_'||t3.prodl as prodl, t3.prozl_id||'_'||t3.prozl as prozl, t3.proxl_id||'_'||t3.proxl as proxl from maxmin t1 left outer join branch t2 on t1.braid=t2.braid left outer join product_all t3 on t1.proid=t3.proid) t1 where "
for j in jlist:
if jlist[j]<>'':
li=jlist[j].split(",")
if len(li)==1: #一个条件
sqlstr+=j+" like '%%"+jlist[j].strip()+"%%' and "
else: #逗号分隔的多条件
sqlstr+="("
for l in li:
sqlstr+=j+" like '%%"+l.strip() +"%%' or "
sqlstr=sqlstr[0:-4]+") and "
sqlstr=sqlstr[0:-4]
sqlstr+=" limit 20000"
lines=confsql.runquery(sqlstr)
result=[] #存放初始表头
for rs in lines:
res={}
res['braid']=rs[0]
res['braname']=rs[1]
res['barcode']=rs[2]
res['proid']=rs[3]
res['proname']=rs[4]
res['maxval']=rs[5]
res['minval']=rs[6]
res['banben']=rs[7]
res['startdate']=rs[8]
res['enddate']=rs[9]
res['adddate']=rs[10]
res['prodl'] = rs[11]
res['prozl'] = rs[12]
res['proxl'] = rs[13]
result.append(res)
jsonres=simplejson.dumps(result)
return HttpResponse(jsonres)
else: #全为空，查询所有
sqlstr="select t1.braid,t2.braname,t3.barcode,t1.proid,t3.proname,t1.maxval,t1.minval,t1.banben,t1.startdate,t1.enddate,t1.adddate,t3.prodl_id||'_'||t3.prodl as prodl, t3.prozl_id||'_'||t3.prozl as prozl, t3.proxl_id||'_'||t3.proxl as proxl from maxmin t1 left outer join branch t2 on t1.braid=t2.braid left outer join product_all t3 on t1.proid=t3.proid limit 20000"
lines=confsql.runquery(sqlstr)
result=[] #存放初始表头
for rs in lines:
res={}
res['braid']=rs[0]
res['braname']=rs[1]
res['barcode']=rs[2]
res['proid']=rs[3]
res['proname']=rs[4]
res['maxval']=rs[5]
res['minval']=rs[6]
res['banben']=rs[7]
res['startdate']=rs[8]
res['enddate']=rs[9]
res['adddate']=rs[10]
res['prodl'] = rs[11]
res['prozl'] = rs[12]
res['proxl'] = rs[13]
result.append(res)
jsonres=simplejson.dumps(result)
log(jsonres)
return HttpResponse(jsonres)
except:
return HttpResponse(0)
else:
t=get_template('mana1/insult_maxmin.html')
html=t.render(Context())
return HttpResponse(html)