def newInvoice(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
conn2= MethodUtil.get_MssqlConn()
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
suppList = BasSupplier.objects.filter(suppcode=suppCode).values("taxno","paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
taxno = ''
return render(request,'user_invoice_new.html',locals())
def newInvoice(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
conn2 = MethodUtil.get_MssqlConn()
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
suppList = BasSupplier.objects.filter(suppcode=suppCode).values(
"taxno", "paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
taxno = ''
return render(request, 'user_invoice_new.html', locals())
def queryBalance(request):
# suppCode = request.session.get('s_suppcode')
# payStatus = request.POST.get('payStatus','')
refSheetId = request.POST.get('refSheetId', '')
queryDict = {}
if refSheetId:
try:
conn2 = MethodUtil.get_MssqlConn()
sql0 = "select sheetid,begindate,enddate,PlanPayDate from billhead0 where sheetid='{sheetid}'".format(
sheetid=refSheetId.strip())
billhead0 = conn2.execute_row(sql0)
if billhead0:
#计划付款日期
# sql1 = "select c.TaxNo, a.PlanPayDate from billhead0 a, VenderCard b,VenderExt c where a.VenderID = b.VenderID and a.VenderID *= c.VenderID and a.SheetID = '{sheetid}'".format(sheetid=refSheetId)
# dict1 = conn2.execute_row(sql1)
# if dict1:
# queryDict['PlanPayDate']=str(dict1['PlanPayDate'])
# sql3 = "select a.jsdate,a.flag,a.fnotes,b.taxno,c.paytypeid from vendercard a,venderext b,vender c where a.venderid=b.venderid and a.venderid=c.venderid and a.venderid={venderid}".format(venderid=suppCode)
# dict3 = conn2.execute_row(sql3)
# if dict3:
# queryDict['payTypeId']=dict3['paytypeid']
sql2 = "select inshopid from billheaditem0 where sheetid ='{sheetid}'".format(
sheetid=refSheetId)
shopId = conn2.execute_row(sql2)['inshopid']
if shopId:
queryDict['shopId'] = shopId
queryDict['PlanPayDate'] = str(billhead0['PlanPayDate'])
queryDict['begindate'] = str(billhead0["begindate"])
queryDict['enddate'] = str(billhead0["enddate"])
queryDict['succ'] = True
else:
queryDict['succ'] = False
except Exception as e:
print(e)
finally:
conn2.close()
return HttpResponse(json.dumps(queryDict))
def queryBalance(request):
# suppCode = request.session.get('s_suppcode')
# payStatus = request.POST.get('payStatus','')
refSheetId = request.POST.get('refSheetId','')
queryDict={}
if refSheetId:
try:
conn2 = MethodUtil.get_MssqlConn()
sql0 = "select sheetid,begindate,enddate,PlanPayDate from billhead0 where sheetid='{sheetid}'".format(sheetid=refSheetId.strip())
billhead0 = conn2.execute_row(sql0)
if billhead0:
#计划付款日期
# sql1 = "select c.TaxNo, a.PlanPayDate from billhead0 a, VenderCard b,VenderExt c where a.VenderID = b.VenderID and a.VenderID *= c.VenderID and a.SheetID = '{sheetid}'".format(sheetid=refSheetId)
# dict1 = conn2.execute_row(sql1)
# if dict1:
# queryDict['PlanPayDate']=str(dict1['PlanPayDate'])
# sql3 = "select a.jsdate,a.flag,a.fnotes,b.taxno,c.paytypeid from vendercard a,venderext b,vender c where a.venderid=b.venderid and a.venderid=c.venderid and a.venderid={venderid}".format(venderid=suppCode)
# dict3 = conn2.execute_row(sql3)
# if dict3:
# queryDict['payTypeId']=dict3['paytypeid']
sql2 = "select inshopid from billheaditem0 where sheetid ='{sheetid}'".format(sheetid=refSheetId)
shopId = conn2.execute_row(sql2)['inshopid']
if shopId:
queryDict['shopId']=shopId
queryDict['PlanPayDate']=str(billhead0['PlanPayDate'])
queryDict['begindate'] = str(billhead0["begindate"])
queryDict['enddate'] = str(billhead0["enddate"])
queryDict['succ']=True
else:
queryDict['succ']=False
except Exception as e:
print(e)
finally:
conn2.close()
return HttpResponse(json.dumps(queryDict))
def createInvioce(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
refSheetId = MethodUtil.getReqVal(request, 'sheetid')
conn2 = MethodUtil.get_MssqlConn()
#判断发票单据是否存在
# sql = "select sheetid from CustReceive0 where venderid={venderid} and ShopID='CM01'".format(venderid=suppCode)
# sheetList = conn2.execute_row(sql)
#计划付款日期
balanceList = Billhead0.objects.filter(sheetid=refSheetId).values(
"planpaydate", "begindate", "enddate")
if balanceList:
balance = balanceList[0]
PlanPayDate = balance['planpaydate']
begindate = balance['begindate']
enddate = balance['enddate']
else:
PlanPayDate = ''
suppList = BasSupplier.objects.filter(suppcode=suppCode).values(
"taxno", "paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
itemList = Billheaditem0.objects.filter(
sheetid=refSheetId).values("inshopid").order_by("inshopid")
if itemList:
shopId = itemList[0]['inshopid']
else:
shopId = ''
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
return render(request, 'user_invoice.html', locals())
def createInvioce(request):
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
refSheetId = MethodUtil.getReqVal(request,'sheetid')
conn2= MethodUtil.get_MssqlConn()
#判断发票单据是否存在
# sql = "select sheetid from CustReceive0 where venderid={venderid} and ShopID='CM01'".format(venderid=suppCode)
# sheetList = conn2.execute_row(sql)
#计划付款日期
balanceList = Billhead0.objects.filter(sheetid=refSheetId).values("planpaydate","begindate","enddate")
if balanceList:
balance=balanceList[0]
PlanPayDate=balance['planpaydate']
begindate = balance['begindate']
enddate = balance['enddate']
else:
PlanPayDate = ''
suppList = BasSupplier.objects.filter(suppcode=suppCode).values("taxno","paytypeid")
if suppList:
paytypeid = suppList[0]["paytypeid"]
taxno = suppList[0]['taxno']
else:
paytypeid = ''
itemList = Billheaditem0.objects.filter(sheetid=refSheetId).values("inshopid").order_by("inshopid")
if itemList:
shopId = itemList[0]['inshopid']
else:
shopId = ''
timeNow = datetime.datetime.now().strftime("%Y-%m-%d")
return render(request,'user_invoice.html',locals())
def index(request):
user = request.session.get("s_user", None)
suppcode = request.session.get("s_suppcode")
s_grpcode = request.session.get("s_grpcode")
paytypeid = request.session.get("s_paytypeid")
contracttype = request.session.get("s_contracttype")
if user:
pubList = findPubInfoAllByCon(user)
else:
pubList = []
pageNum = int(request.GET.get("pageNum", 1))
page = Paginator(pubList,
__EACH_PAGE_SHOW_NUMBER,
allow_empty_first_page=True).page(pageNum)
upwd = user["password"]
pwd = mtu.md5(suppcode)
pwdInit = False
if upwd == pwd:
pwdInit = True
#查询对账日期
ritemList = ReconcilItem.objects.filter(pid=paytypeid).values("rid")
rdays = []
tdays = []
rlist = []
if ritemList:
for ritem in ritemList:
rid = ritem["rid"]
reconcil = Reconcil.objects.filter(id=rid, status=1).values(
"rname", "beginday", "endday")
if reconcil:
row = reconcil[0]
rlist.append(dict(row))
rlist = sorted(rlist, key=lambda row: row["beginday"])
rds = ""
tds = ""
if rlist:
for rw in rlist:
begin = rw["beginday"]
end = rw["endday"]
rdays.append("{begin}-{end}".format(begin=begin, end=end))
if "随时" in rw["rname"]:
tdays.append("{begin}-{end}".format(begin=begin, end=end))
else:
if begin <= 15:
tdays.append("1-{begin}".format(begin=begin - 1))
else:
tdays.append("16-{begin}".format(begin=begin - 1))
rds = ",".join(rdays)
tds = ",".join(tdays)
endDate = ""
try:
fee = BasFee.objects.get(suppcode=suppcode,
grpcode=s_grpcode,
ucode=user["ucode"])
if fee:
endDate = fee.enddate
conn = mtu.get_MssqlConn()
#供应商结算方式
pdict = findPayType(2)
if pdict and paytypeid:
payTypeName = pdict[str(int(paytypeid))]
else:
payTypeName = ""
#g-购销 l-联营 d-代销 z-租赁
pstart, pend, cstart, cend = getStartAndEndDate(
contracttype, payTypeName)
#查询单据信息（动态查询）
rdict = findBillItem(conn, suppcode, pstart, pend, cstart, cend, None,
contracttype)
if rdict and rdict["blist"]:
blist = rdict["blist"]
blen = len(blist)
request.session["s_rdict"] = blen
else:
request.session["s_rdict"] = 0
conn.close()
except Exception as e:
print(e)
return render(
request, "index.html", {
"page": page,
"pageNum": pageNum,
"pwdInit": pwdInit,
"rdays": rds,
"tdays": tds,
"endDate": endDate,
"payTypeName": payTypeName
})
def saveInvioce(request):
conn = MethodUtil.getMssqlConn()
conn2 = MethodUtil.get_MssqlConn()
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
############接收表头相关数据（CustReceive0） ############
planPayDate = request.POST.get('PlanPayDate')
timeNow = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
payDate = request.POST.get('payDate', timeNow)
shopId = request.POST.get('shopId')
refSheetId = request.POST.get('refSheetId', '')
beginDate = request.POST.get("begindate", '')
endDate = request.POST.get("enddate", '')
jsonStr = request.POST.get('jsonStr', '')
res = {}
sql = "select top 1 SheetID from CustReceive0 where VenderID='{suppCode}' and flag=0".format(
suppCode=suppCode)
row = conn2.execute_row(sql)
if row:
res['succ'] = '2'
res['sheetid'] = row["SheetID"]
else:
try:
#生成发票编号
sqlSheetId = '''declare @i int,@SheetID char(16)
exec @i=TL_GetNewSheetID 5204,@SheetID out
select @SheetID
'''
sheetId = conn2.execute_scalar(sqlSheetId)
# ############开始存储事务############
conn.autocommit(False)
cur = conn.cursor()
#保存发票主要信息
sqlCR = "insert into CustReceive0 (SheetID, BillheadSheetID, VenderID, PlanPayDate, BeginDate, EndDate,Flag, Editor, EditDate, ShopID, Operator) " \
"values('{sheetid}','{billheadsheetid}',{venderid},'{planpaydate}','{begindate}','{enddate}',{flag},'{editor}','{editdate}','{shopid}','' )" \
.format(sheetid=sheetId,billheadsheetid=refSheetId,venderid=suppCode,planpaydate=planPayDate,begindate=beginDate,enddate=endDate,
flag=0,editor=suppCode,editdate=payDate,shopid='CM01')
cur.execute(sqlCR)
#保存custitem0表数据res2[i][12]
res2 = findCustItem(conn2, refSheetId, suppCode)
for i in range(0, len(res2)):
sqlCI = "insert into custitem0 " \
"values ('{SheetID}','{PayTypeSortID}','{PayableDate}','{RefSheetID}',{RefSheetType},{ManageDeptID},'{FromShopID}','{InShopID}','{CostValue}','{CostTaxValue}','{CostTaxRate}',{AgroFlag},'{SaleValue}',{BalanceBookSerialID})" \
.format(SheetID=sheetId,PayTypeSortID=res2[i][0],PayableDate=res2[i][11],RefSheetID=res2[i][1],RefSheetType=res2[i][2],ManageDeptID=res2[i][4],FromShopID=res2[i][13],InShopID=res2[i][5],
CostValue=res2[i][6],CostTaxValue=res2[i][8],CostTaxRate=res2[i][12],AgroFlag=res2[i][10],SaleValue=res2[i][9],BalanceBookSerialID=res2[i][16])
cur.execute(sqlCI)
#保存用户录入发票详细
listData = json.loads(jsonStr)
if listData:
for data in listData:
#kmoney默认为0无需录入
sqlCRI = "insert into CustReceiveItem0 values( '" + sheetId + "','" + data[
'cno'] + "','" + suppName + "','" + data[
'cdno'] + "','" + data['cdate'] + "'," + data[
'cclass'] + ",'" + data['cgood'] + "','" + data[
'ctaxrate'] + "','" + data[
'cmoney'] + "','" + data[
'csh'] + "'," + data[
'paytype'] + ",'0.0','" + shopId + "')"
cur.execute(sqlCRI)
else:
sql3 = "select a.jsdate,a.flag,a.fnotes,b.taxno,c.paytypeid from vendercard a,venderext b,vender c where a.venderid=b.venderid and a.venderid=c.venderid and a.venderid={venderid}".format(
venderid=suppCode)
dict3 = conn2.execute_row(sql3)
taxno = dict3["taxno"]
sqlCRI = "insert into CustReceiveItem0 (sheetid,cno,cname,cdate,cclass,cgood,ctaxrate,cmoney,csh,cdno,PayType,kmoney,shopid) values( '" + sheetId + "','666666','" + suppName + "',getDate(),1,'货物',0.0,0.0,0.0,'" + taxno + "','1',0.0,'" + shopId + "')"
cur.execute(sqlCRI)
sqlFlow = "insert into sheetflow(sheetid,sheettype,flag,operflag,checker,checkno,checkdate,checkdatetime) " \
"values('{shid}',{shType},{flag},{operFlag},'{checker}',{chNo},convert(char(10),getdate(),120),getdate())"\
.format(shid=sheetId,shType=5024,flag=0,operFlag=0,checker=Constants.SCM_ACCOUNT_LOGINID,chNo=Constants.SCM_ACCOUNT_LOGINNO)
cur.execute(sqlFlow)
conn.commit()
#记录发票录入状态
try:
if refSheetId:
billhead = Billhead0.objects.values(
"sheetid", "flag", "editdate", "grpcode", "venderid",
"shopid").get(sheetid=refSheetId)
if billhead:
Billhead0Status.objects.create(
sheetid=refSheetId,
inviocestatus=1,
flag=billhead["flag"],
editdate=billhead["editdate"],
grpcode=billhead["grpcode"],
venderid=billhead["venderid"],
shopid=billhead["shopid"])
except Exception as e:
print(e)
res['succ'] = '0'
MethodUtil.insertSysLog(conn2, Constants.SCM_ACCOUNT_LOGINID,
Constants.SCM_ACCOUNT_WORKSTATIONID,
Constants.SCM_ACCOUNT_MODULEID,
Constants.SCM_ACCOUNT_EVENTID[5], "")
MethodUtil.insertSysLog(
conn2, Constants.SCM_ACCOUNT_LOGINID,
Constants.SCM_ACCOUNT_WORKSTATIONID,
Constants.SCM_ACCOUNT_MODULEID,
Constants.SCM_ACCOUNT_EVENTID[6],
"操作员:{suppCode}保存单据[{sheetId}]".format(suppCode=suppCode,
sheetId=sheetId))
except Exception as e:
print(e)
res['succ'] = '1'
conn.rollback()
finally:
cur.close()
conn.close()
conn2.close()
return HttpResponse(json.dumps(res))
def applySave(request):
"""保存结算申请单"""
paytypeid = request.session.get("s_paytypeid")
s_ucode = request.session.get("s_ucode")
venderid = request.session.get("s_suppcode")
pstart = mtu.getReqVal(request,"pstart",None)
pend = mtu.getReqVal(request,"pend",None)
cstart = mtu.getReqVal(request,"cstart",None)
cend = mtu.getReqVal(request,"cend",None)
refsheetids = mtu.getReqList(request,"refsheetid",None)
balancePlaceId = mtu.getReqVal(request,"balancePlaceId")
params = {}
result = {}
#判断是否可以提交结算单
islimit = mtu.getProperties(Constants.SCM_CONFIG_MODULE,Constants.SCM_CONFIG_BILL_ISLIMIT)
if islimit == 'True':
sequence = allowCommit(paytypeid,venderid)
else:
sequence = "0"
if sequence=="0":
planpaydate = datetime.date.today().strftime("%Y-%m-%d")
params["pstart"]=pstart
params["pend"]=pend
params["cstart"]=cstart
params["cend"]=cend
params["planpaydate"]=planpaydate
params["editor"]=s_ucode
params["editdate"]=datetime.date.today().strftime("%Y-%m-%d")
# params["sheetid"] = sheetId
params["venderid"] = venderid
try:
conn2 = mtu.get_MssqlConn()
errors = 0
try:
blist = []
for row in refsheetids:
ric = eval(row)
blist.append(ric)
payableamt = findPayableCostValue(conn2,balancePlaceId,venderid)
if not payableamt:
payableamt = decimal.Decimal(0.0)
costvalue = findCostValue(conn2,venderid)
if not costvalue:
costvalue = decimal.Decimal(0.0)
unjsvalue = unbalancedCostValue(conn2,venderid,pstart)
if not unjsvalue:
unjsvalue = decimal.Decimal(0.0)
undqvalue = undueCostValue(conn2,venderid,pend)
if not undqvalue:
undqvalue = decimal.Decimal(0.0)
advance = findAdvance(conn2,venderid)
if not advance:
advance = decimal.Decimal(0.0)
payablemoney = sum([float(row["costvalue"]) for row in blist])
if not payablemoney:
payablemoney = decimal.Decimal(0.0)
params["payablemoney"]=float(payablemoney) #应付金额
params["advance"]=float(advance) #预付款余额，预付款应扣金额(promoney)默认0 （写表billhead0）
params["costvalue"]=float(costvalue) #库存金额 （写表billhead0）
params["undqvalue"]=float(undqvalue) #未到期金额 （写表billhead0） 取不为空数据
params["payableamt"]=float(payableamt) #应付账款金额 （写表billhead0）
params["unjsvalue"]=float(unjsvalue) #应结未结金额 （写表billhead0） 取不为空数据
#新增
type=0
typeStr = "新增"
sheetId = getSheetId(conn2)
conn = mtu.getMssqlConn()
conn.autocommit(False)
klist = findKxsum(conn,venderid,pend)
kxmoney = sum([row["kmoney"] for row in klist])
if not kxmoney:
kxmoney = decimal.Decimal(0.0)
cashlist = filter(lambda row:row["kkflag"]==0,[row for row in klist])
invoicelist = filter(lambda row:row["kkflag"]==1,[row for row in klist])
kxcash = sum([row["kmoney"] for row in cashlist])
if not kxcash:
kxcash = decimal.Decimal(0.0)
kxinvoice = sum([row["kmoney"] for row in invoicelist])
if not kxinvoice:
kxinvoice = decimal.Decimal(0.0)
#应付金额=实付金额+帐扣金额
#应开票金额=实付金额
params["kxmoney"]=float(kxmoney) #扣项金额合计
params["kxcash"]=float(kxcash) #扣项交款金额
params["kxinvoice"]=float(kxinvoice) #帐扣发票金额 (帐扣金额)
params["sheetid"] = sheetId
#保存单据信息
saveBillHead0(conn,params)
#保存单据明细
saveBillHeadItem(conn,blist,sheetId)
#保存扣项明细
saveKxItem(conn,klist,sheetId)
cursor = conn.cursor()
sqlFlow = "insert into sheetflow(sheetid,sheettype,flag,operflag,checker,checkno,checkdate,checkdatetime) " \
"values('{sheetId}',{shType},{flag},{operFlag},'{checker}',{chNo},convert(char(10),getdate(),120),getdate())"\
.format(sheetId=sheetId,shType=5203,flag=0,operFlag=0,checker=Constants.SCM_ACCOUNT_LOGINID,chNo=Constants.SCM_ACCOUNT_LOGINNO)
cursor.execute(sqlFlow)
conn.commit()
cursor.close()
except Exception as e:
print(e)
errors += 1
conn.rollback()
finally:
conn.close()
if errors <= 0:
#执行保存存储过程
sql = """declare @Result int
exec @Result=st_billheadsave '{sheetId}',{type},'{cname}','A001'
select @Result""".format(sheetId=sheetId,type=type,cname = Constants.SCM_ACCOUNT_USER_NAME)
conn2.execute_scalar(sql)
#保存日志记录
note = "[SCM]操作员:[{operator}]{typeStr}单据[{sheetId}]".format(sheetId=sheetId,typeStr=typeStr,operator=s_ucode)
mtu.insertSysLog(conn2,Constants.SCM_ACCOUNT_LOGINID,Constants.SCM_ACCOUNT_WORKSTATIONID,Constants.SCM_ACCOUNT_MODULEID,Constants.SCM_ACCOUNT_EVENTID[type],note)
result["status"] = "0"
result["sheetId"] = sheetId
conn2.close()
else:
result["status"] = "1"
except Exception as e:
print(e)
else:
result["status"] = "2"
return HttpResponse(json.dumps(result))
def applyEdit(request):
venderid = request.session.get("s_suppcode")
paytypeid = request.session.get("s_paytypeid")
contracttype = request.session.get("s_contracttype")
result = {}
rdict = {}
kxinvoice = decimal.Decimal(0)
try:
conn = mtu.get_MssqlConn()
#供应商结算方式
pdict = findPayType(2)
if pdict and paytypeid:
payTypeName = pdict[str(int(paytypeid))]
else:
payTypeName = ""
#g-购销 l-联营 d-代销 z-租赁
pstart,pend,cstart,cend = getStartAndEndDate(contracttype,payTypeName)
#查询单据信息（动态查询）
rdict = findBillItem(conn,venderid,pstart,pend,cstart,cend,None,contracttype)
blist = rdict["blist"]
itemList = []
for row in blist:
item = {}
for k,v in row.items():
if isinstance(v,datetime.datetime):
v = v.strftime("%Y-%m-%d")
elif isinstance(v,decimal.Decimal):
v = str(v)
item.setdefault(k,v)
itemList.append(item)
kxinvoice = findKxInvoice(conn,venderid,pend)
conn.close()
kxlist = findKxListData(venderid,pend,0)
jxsum = decimal.Decimal(0.0)
for kx in kxlist:
kkflag = kx["kkflag"]
if kkflag == 0:
jxsum+= kx["kmoney"]
except Exception as e:
print(e)
result["paytypeid"] = paytypeid #结算方式ID
result["payTypeName"] = payTypeName #结算方式名称
result["balancePlaceName"] = Constants.SCM_BALANCE_NAME #结算地名称
result["balancePlaceId"] = Constants.SCM_BALANCE_ID #结算地ID
result["cstart"] = cstart
result["cend"] = cend
result["pstart"] = pstart
result["pend"] = pend
result["itemList"] = itemList
result["sum1"] = rdict["sum1"]
result["sum2"] = rdict["sum2"]
result["sum3"] = rdict["sum3"]
result["sum4"] = rdict["sum4"]
result["kxinvoice"] = "%0.2f" % kxinvoice
result["zkinvoice"] = "%0.2f" % kxinvoice
result["jxsum"] = "%0.2f" % jxsum
return render(request,"user_settleApply.html",result)
def applySave(request):
"""保存结算申请单"""
paytypeid = request.session.get("s_paytypeid")
s_ucode = request.session.get("s_ucode")
venderid = request.session.get("s_suppcode")
pstart = mtu.getReqVal(request,"pstart",None)
pend = mtu.getReqVal(request,"pend",None)
cstart = mtu.getReqVal(request,"cstart",None)
cend = mtu.getReqVal(request,"cend",None)
refsheetids = mtu.getReqList(request,"refsheetid",None)
balancePlaceId = mtu.getReqVal(request,"balancePlaceId")
params = {}
result = {}
#判断是否可以提交结算单
islimit = mtu.getProperties(Constants.SCM_CONFIG_MODULE,Constants.SCM_CONFIG_BILL_ISLIMIT)
if islimit == 'True':
sequence = allowCommit(paytypeid,venderid)
else:
sequence = "0"
if sequence=="0":
planpaydate = datetime.date.today().strftime("%Y-%m-%d")
params["pstart"]=pstart
params["pend"]=pend
params["cstart"]=cstart
params["cend"]=cend
params["planpaydate"]=planpaydate
params["editor"]=s_ucode
params["editdate"]=datetime.date.today().strftime("%Y-%m-%d")
# params["sheetid"] = sheetId
params["venderid"] = venderid
try:
conn2 = mtu.get_MssqlConn()
errors = 0
try:
blist = []
for row in refsheetids:
ric = eval(row)
blist.append(ric)
payableamt = findPayableCostValue(conn2,balancePlaceId,venderid)
if not payableamt:
payableamt = decimal.Decimal(0.0)
costvalue = findCostValue(conn2,venderid)
if not costvalue:
costvalue = decimal.Decimal(0.0)
unjsvalue = unbalancedCostValue(conn2,venderid,pstart)
if not unjsvalue:
unjsvalue = decimal.Decimal(0.0)
undqvalue = undueCostValue(conn2,venderid,pend)
if not undqvalue:
undqvalue = decimal.Decimal(0.0)
advance = findAdvance(conn2,venderid)
if not advance:
advance = decimal.Decimal(0.0)
payablemoney = sum([float(row["costvalue"]) for row in blist])
if not payablemoney:
payablemoney = decimal.Decimal(0.0)
params["payablemoney"]=float(payablemoney) #应付金额
params["advance"]=float(advance) #预付款余额，预付款应扣金额(promoney)默认0 （写表billhead0）
params["costvalue"]=float(costvalue) #库存金额 （写表billhead0）
params["undqvalue"]=float(undqvalue) #未到期金额 （写表billhead0） 取不为空数据
params["payableamt"]=float(payableamt) #应付账款金额 （写表billhead0）
params["unjsvalue"]=float(unjsvalue) #应结未结金额 （写表billhead0） 取不为空数据
#新增
type=0
typeStr = "新增"
sheetId = getSheetId(conn2)
conn = mtu.getMssqlConn()
conn.autocommit(False)
klist = findKxsum(conn,venderid,pend)
kxmoney = sum([row["kmoney"] for row in klist])
if not kxmoney:
kxmoney = decimal.Decimal(0.0)
cashlist = filter(lambda row:row["kkflag"]==0,[row for row in klist])
invoicelist = filter(lambda row:row["kkflag"]==1,[row for row in klist])
kxcash = sum([row["kmoney"] for row in cashlist])
if not kxcash:
kxcash = decimal.Decimal(0.0)
kxinvoice = sum([row["kmoney"] for row in invoicelist])
if not kxinvoice:
kxinvoice = decimal.Decimal(0.0)
#应付金额=实付金额+帐扣金额
#应开票金额=实付金额
params["kxmoney"]=float(kxmoney) #扣项金额合计
params["kxcash"]=float(kxcash) #扣项交款金额
params["kxinvoice"]=float(kxinvoice) #帐扣发票金额 (帐扣金额)
params["sheetid"] = sheetId
#保存单据信息
saveBillHead0(conn,params)
#保存单据明细
saveBillHeadItem(conn,blist,sheetId)
#保存扣项明细
saveKxItem(conn,klist,sheetId)
cursor = conn.cursor()
sqlFlow = "insert into sheetflow(sheetid,sheettype,flag,operflag,checker,checkno,checkdate,checkdatetime) " \
"values('{sheetId}',{shType},{flag},{operFlag},'{checker}',{chNo},convert(char(10),getdate(),120),getdate())"\
.format(sheetId=sheetId,shType=5203,flag=0,operFlag=0,checker=Constants.SCM_ACCOUNT_LOGINID,chNo=Constants.SCM_ACCOUNT_LOGINNO)
cursor.execute(sqlFlow)
conn.commit()
cursor.close()
except Exception as e:
print(e)
errors += 1
conn.rollback()
finally:
conn.close()
if errors <= 0:
#执行保存存储过程
sql = """declare @Result int
exec @Result=st_billheadsave '{sheetId}',{type},'{cname}','A001'
select @Result""".format(sheetId=sheetId,type=type,cname = Constants.SCM_ACCOUNT_USER_NAME)
conn2.execute_scalar(sql)
#保存日志记录
note = "[SCM]操作员:[{operator}]{typeStr}单据[{sheetId}]".format(sheetId=sheetId,typeStr=typeStr,operator=s_ucode)
mtu.insertSysLog(conn2,Constants.SCM_ACCOUNT_LOGINID,Constants.SCM_ACCOUNT_WORKSTATIONID,Constants.SCM_ACCOUNT_MODULEID,Constants.SCM_ACCOUNT_EVENTID[type],note)
result["status"] = "0"
result["sheetId"] = sheetId
conn2.close()
else:
result["status"] = "1"
except Exception as e:
print(e)
else:
result["status"] = "2"
return HttpResponse(json.dumps(result))
def applyEdit(request):
venderid = request.session.get("s_suppcode")
paytypeid = request.session.get("s_paytypeid")
contracttype = request.session.get("s_contracttype")
result = {}
rdict = {}
kxinvoice = decimal.Decimal(0)
try:
conn = mtu.get_MssqlConn()
#供应商结算方式
pdict = findPayType(2)
if pdict and paytypeid:
payTypeName = pdict[str(int(paytypeid))]
else:
payTypeName = ""
#g-购销 l-联营 d-代销 z-租赁
pstart,pend,cstart,cend = getStartAndEndDate(contracttype,payTypeName)
#查询单据信息（动态查询）
rdict = findBillItem(conn,venderid,pstart,pend,cstart,cend,None,contracttype)
blist = rdict["blist"]
itemList = []
for row in blist:
item = {}
for k,v in row.items():
if isinstance(v,datetime.datetime):
v = v.strftime("%Y-%m-%d")
elif isinstance(v,decimal.Decimal):
v = str(v)
item.setdefault(k,v)
itemList.append(item)
kxinvoice = findKxInvoice(conn,venderid,pend)
conn.close()
kxlist = findKxListData(venderid,pend,0)
jxsum = decimal.Decimal(0.0)
for kx in kxlist:
kkflag = kx["kkflag"]
if kkflag == 0:
jxsum+= kx["kmoney"]
except Exception as e:
print(e)
result["paytypeid"] = paytypeid #结算方式ID
result["payTypeName"] = payTypeName #结算方式名称
result["balancePlaceName"] = Constants.SCM_BALANCE_NAME #结算地名称
result["balancePlaceId"] = Constants.SCM_BALANCE_ID #结算地ID
result["cstart"] = cstart
result["cend"] = cend
result["pstart"] = pstart
result["pend"] = pend
result["itemList"] = itemList
result["sum1"] = rdict["sum1"]
result["sum2"] = rdict["sum2"]
result["sum3"] = rdict["sum3"]
result["sum4"] = rdict["sum4"]
result["kxinvoice"] = "%0.2f" % kxinvoice
result["zkinvoice"] = "%0.2f" % kxinvoice
result["jxsum"] = "%0.2f" % jxsum
return render(request,"user_settleApply.html",result)
def saveInvioce(request):
conn = MethodUtil.getMssqlConn()
conn2= MethodUtil.get_MssqlConn()
suppCode = request.session.get('s_suppcode')
suppName = request.session.get('s_suppname')
############接收表头相关数据（CustReceive0） ############
planPayDate = request.POST.get('PlanPayDate')
timeNow = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
payDate = request.POST.get('payDate',timeNow)
shopId = request.POST.get('shopId')
refSheetId = request.POST.get('refSheetId','')
beginDate = request.POST.get("begindate",'')
endDate = request.POST.get("enddate",'')
jsonStr = request.POST.get('jsonStr','')
res = {}
sql="select top 1 SheetID from CustReceive0 where VenderID='{suppCode}' and flag=0".format(suppCode=suppCode)
row = conn2.execute_row(sql)
if row:
res['succ']= '2'
res['sheetid']= row["SheetID"]
else:
try:
#生成发票编号
sqlSheetId = '''declare @i int,@SheetID char(16)
exec @i=TL_GetNewSheetID 5204,@SheetID out
select @SheetID
'''
sheetId = conn2.execute_scalar(sqlSheetId)
# ############开始存储事务############
conn.autocommit(False)
cur = conn.cursor()
#保存发票主要信息
sqlCR = "insert into CustReceive0 (SheetID, BillheadSheetID, VenderID, PlanPayDate, BeginDate, EndDate,Flag, Editor, EditDate, ShopID, Operator) " \
"values('{sheetid}','{billheadsheetid}',{venderid},'{planpaydate}','{begindate}','{enddate}',{flag},'{editor}','{editdate}','{shopid}','' )" \
.format(sheetid=sheetId,billheadsheetid=refSheetId,venderid=suppCode,planpaydate=planPayDate,begindate=beginDate,enddate=endDate,
flag=0,editor=suppCode,editdate=payDate,shopid='CM01')
cur.execute(sqlCR)
#保存custitem0表数据res2[i][12]
res2 = findCustItem(conn2,refSheetId,suppCode)
for i in range(0,len(res2)):
sqlCI = "insert into custitem0 " \
"values ('{SheetID}','{PayTypeSortID}','{PayableDate}','{RefSheetID}',{RefSheetType},{ManageDeptID},'{FromShopID}','{InShopID}','{CostValue}','{CostTaxValue}','{CostTaxRate}',{AgroFlag},'{SaleValue}',{BalanceBookSerialID})" \
.format(SheetID=sheetId,PayTypeSortID=res2[i][0],PayableDate=res2[i][11],RefSheetID=res2[i][1],RefSheetType=res2[i][2],ManageDeptID=res2[i][4],FromShopID=res2[i][13],InShopID=res2[i][5],
CostValue=res2[i][6],CostTaxValue=res2[i][8],CostTaxRate=res2[i][12],AgroFlag=res2[i][10],SaleValue=res2[i][9],BalanceBookSerialID=res2[i][16])
cur.execute(sqlCI)
#保存用户录入发票详细
listData = json.loads(jsonStr)
if listData:
for data in listData:
#kmoney默认为0无需录入
sqlCRI = "insert into CustReceiveItem0 values( '"+sheetId+"','"+data['cno']+"','"+suppName+"','"+data['cdno']+"','"+data['cdate']+"',"+data['cclass']+",'"+data['cgood']+"','"+data['ctaxrate']+"','"+data['cmoney']+"','"+data['csh']+"',"+data['paytype']+",'0.0','"+shopId+"')"
cur.execute(sqlCRI)
else:
sql3 = "select a.jsdate,a.flag,a.fnotes,b.taxno,c.paytypeid from vendercard a,venderext b,vender c where a.venderid=b.venderid and a.venderid=c.venderid and a.venderid={venderid}".format(venderid=suppCode)
dict3 = conn2.execute_row(sql3)
taxno = dict3["taxno"]
sqlCRI = "insert into CustReceiveItem0 (sheetid,cno,cname,cdate,cclass,cgood,ctaxrate,cmoney,csh,cdno,PayType,kmoney,shopid) values( '"+sheetId+"','666666','"+suppName+"',getDate(),1,'货物',0.0,0.0,0.0,'"+taxno+"','1',0.0,'"+shopId+"')"
cur.execute(sqlCRI)
sqlFlow = "insert into sheetflow(sheetid,sheettype,flag,operflag,checker,checkno,checkdate,checkdatetime) " \
"values('{shid}',{shType},{flag},{operFlag},'{checker}',{chNo},convert(char(10),getdate(),120),getdate())"\
.format(shid=sheetId,shType=5024,flag=0,operFlag=0,checker=Constants.SCM_ACCOUNT_LOGINID,chNo=Constants.SCM_ACCOUNT_LOGINNO)
cur.execute(sqlFlow)
conn.commit()
#记录发票录入状态
try:
if refSheetId:
billhead = Billhead0.objects.values("sheetid","flag","editdate","grpcode","venderid","shopid").get(sheetid=refSheetId)
if billhead:
Billhead0Status.objects.create(sheetid=refSheetId,inviocestatus=1,flag=billhead["flag"],editdate=billhead["editdate"],grpcode=billhead["grpcode"],venderid=billhead["venderid"],shopid=billhead["shopid"])
except Exception as e:
print(e)
res['succ'] = '0'
MethodUtil.insertSysLog(conn2,Constants.SCM_ACCOUNT_LOGINID,Constants.SCM_ACCOUNT_WORKSTATIONID,Constants.SCM_ACCOUNT_MODULEID,Constants.SCM_ACCOUNT_EVENTID[5],"")
MethodUtil.insertSysLog(conn2,Constants.SCM_ACCOUNT_LOGINID,Constants.SCM_ACCOUNT_WORKSTATIONID,Constants.SCM_ACCOUNT_MODULEID,Constants.SCM_ACCOUNT_EVENTID[6],"操作员:{suppCode}保存单据[{sheetId}]".format(suppCode=suppCode,sheetId=sheetId))
except Exception as e:
print(e)
res['succ'] = '1'
conn.rollback()
finally:
cur.close()
conn.close()
conn2.close()
return HttpResponse(json.dumps(res))
