Python MethodUtil.getMssqlConn方法代码示例

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMssqlConn [as 别名]
def findRefSheetId(sheetid,venderid):
    try:
        conn = mtu.getMssqlConn()
        cur = conn.cursor()
        sql = "select refsheetid,refsheettype from UpdPayableTemp where sheetid='{sheetid}' and venderid='{venderid}'".format(sheetid=sheetid,venderid=venderid)
        cur.execute(sql)
        item = cur.fetchone()
    except Exception as e:
        print(e)
    return item

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMssqlConn [as 别名]
def findKxListData(venderid,pend,flag):
    conn = mtu.getMssqlConn()
    cursor = conn.cursor()
    sql = """select a.SerialID,a.shopid as inshopid,a.managedeptid,a.kno,b.kname,a.ktype,a.kmoney,a.kkflag, a.style,a.monthid,
                 a.receivabledate,a.note,a.fromshopid,b.prtflag, c.name as inshopname
                 from kxsum0 a, kxd b  , shop c where a.venderid in
                 ( select venderid from vendercard where venderid={venderid}  or mastervenderid={venderid})
                 and a.stoppay=0 and a.shopid *= c.id
                 and  a.kno = b.kno and ( (a.ktype=1 and ReceivableDate<='{pend}') or (a.ktype=0 and a.style<>0 ))
                 and a.flag=0 and kkflag={kkflag} order by a.kno
                 """.format(venderid=venderid,pend=pend,kkflag=flag)
    cursor.execute(sql)
    rlist = cursor.fetchall()
    return rlist

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMssqlConn [as 别名]
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)    #扣项金额合计
#.........这里部分代码省略.........

# 需要导入模块: from base.utils import MethodUtil [as 别名]
# 或者: from base.utils.MethodUtil import getMssqlConn [as 别名]
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))