销售计划单开发过程笔记

棉球小哥

这是数据库查询取值，效果是取出物料ID和销售员ID都和填的单子相同的价税合计列

SELECT TARRE.FALLAMOUNTFOR FROM t_AR_receivableEntry as TARRE
inner join t_AR_receivable as TARR on TARRE.FID = TARR.FID
WHERE FMATERIALID = 132335 and TARR.FSALEERID = 110744 and FDOCUMENTSTATUS = 'C' ORDER BY FENTRYID DESC

棉球小哥

下一步：用python把它们都加起来，算出总值后填进去

棉球小哥

import clr
clr.AddReference('System.Data')
clr.AddReference('Kingdee.BOS')
clr.AddReference('Kingdee.BOS.Core')
clr.AddReference('Kingdee.BOS.App')
from Kingdee.BOS.App.Data import *

def OnInitialize(e):
    XSYID = this.View.Model.GetValue("F_XJKM_Entity")
    

    # 使用参数化查询
    orgSQL = """/*dialect*/SELECT TARRE.FALLAMOUNTFOR FROM t_AR_receivableEntry as TARRE
             INNER JOIN t_AR_receivable as TARR ON TARRE.FID = TARR.FID
             WHERE TARR.FSALEERID = """+ str(XSYID) +""" AND FDOCUMENTSTATUS = 'C'
             ORDER BY FENTRYID DESC"""
    

    try:
        # 执行查询SQL语句
        JSKC=DBUtils.ExecuteScalar(this.Context,orgSQL,None);
        

        # 检查结果是否非空
        if JSKC:
        	this.View.ShowMessage("成功.")
        else:
            # 处理结果为空的情况
            this.View.ShowMessage("No data found.")
    except Exception as ex:
        # 处理异常情况
        this.View.ShowMessage("An error occurred ")
这个try很好用 留存一下

棉球小哥

成功取出并输出

import clr
clr.AddReference('System.Data')
clr.AddReference('Kingdee.BOS')
clr.AddReference('Kingdee.BOS.Core')
clr.AddReference('Kingdee.BOS.App')
from Kingdee.BOS.App.Data import *

def OnInitialize(e):
    XSYID = this.View.Model.GetValue("F_XJKM_Base")
    # this.View.ShowMessage(str(XSYID))
    # 使用参数化查询
    orgSQL = """/*dialect*/SELECT TARRE.FALLAMOUNTFOR FROM t_AR_receivableEntry as TARRE
             INNER JOIN t_AR_receivable as TARR ON TARRE.FID = TARR.FID
             WHERE TARR.FSALEERID = """+ str(110744) +""" AND FDOCUMENTSTATUS = 'C'
             ORDER BY FENTRYID DESC"""
    JSKC=DBUtils.ExecuteDataSet(this.Context,orgSQL);
    DATA1 = JSKC.Tables[0];
        # 检查结果是否非空
    if JSKC:
    		for dr in DATA1.Rows:
        		this.View.ShowMessage("成功." + str(dr["FALLAMOUNTFOR"]));
    else:
            # 处理结果为空的情况
            this.View.ShowMessage("No data found.")

棉球小哥

最终版本：
(但如果给一个人设置了两个目标，会出现完成率全部为最底部目标的计算值)
解决办法：在开单时设置销售员目标唯一性

import clr
clr.AddReference('System.Data')
clr.AddReference('Kingdee.BOS')
clr.AddReference('Kingdee.BOS.Core')
clr.AddReference('Kingdee.BOS.App')
from Kingdee.BOS.App.Data import *

def OnInitialize(e):
    # this.View.ShowMessage(str(XSYID))

    #销售员ID
    shitSQL = """/*dialect*/SELECT F_XJKM_XSY,F_XJKM_INTEGER from XJKM_t_Cust_Entry100001"""
    SHIT = DBUtils.ExecuteDynamicObject(this.Context, shitSQL)
    for SHITNUM in SHIT:
        FARTSQL = """/*dialect*/SELECT TARRE.FALLAMOUNTFOR FROM t_AR_receivableEntry as TARRE
             INNER JOIN t_AR_receivable as TARR ON TARRE.FID = TARR.FID
             WHERE TARR.FSALEERID = '{0}' AND FDOCUMENTSTATUS = 'C'
             ORDER BY FENTRYID DESC""".format(str(SHITNUM["F_XJKM_XSY"]))
        FART = DBUtils.ExecuteDynamicObject(this.Context, FARTSQL)
        FART_FLOAT = 0.0
        ASS_STR = ""
        for FARTNUM in FART:
            FART_FLOAT += float(FARTNUM["FALLAMOUNTFOR"])
        ASS = (FART_FLOAT / float(SHITNUM["F_XJKM_INTEGER"])) * 100
        ASS_STR = "{:.2f}%".format(ASS)
        PEESQL = """/*dialect*/UPDATE t 
                             SET F_XJKM_INTEGER1 = '{0}',F_XJKM_TEXT = '{1}'
                             FROM XJKM_t_Cust_Entry100001 t
							 WHERE F_XJKM_XSY = '{2}'""".format(int(FART_FLOAT),str(ASS_STR),str(SHITNUM["F_XJKM_XSY"]))
        FINAL_EXECUTE=DBUtils.Execute(this.Context,PEESQL);
        this.View.UpdateView("F_XJKM_Integer1")
    this.View.UpdateView("F_XJKM_Text")