Python MethodUtil.get_MssqlConn方法代码示例

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

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

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

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

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