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 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 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))
