wnserver/obj/utils/ClssSql.py

class ClassSqls:
    # 共用的
    # 存储过程
    ccc = """alter FUNCTION [dbo].[f_splitSTR](
        @s   varchar(8000),   --待分拆的字符串
        @split varchar(10)     --数据分隔符
        )RETURNS @re TABLE(col varchar(100))
        AS
        BEGIN
         DECLARE @splitlen int
         SET @splitlen = LEN(@split + 'a') - 2
         WHILE CHARINDEX(@split,@s) > 0
         BEGIN
          INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s) - 1))
          SET @s = STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
         END
         INSERT @re VALUES(@s)
         UPDATE A SET a.col=b.NAME FROM @re A,SALM B WITH (NOLOCK) WHERE A.COL=B.SAL_NO 
        RETURN
        END"""
    aaaa="""select left(Convert(varchar(100), no_dd, 23), 11) as no_dd,create_time,update_time,bg_no,bg_id,zc_no,(
        select
        (
        stuff(
        (select ',' + col from f_splitSTR(rtrim((replace(replace(replace(replace(sal_no,'[',''),']',''),'''',''),' ',''))),',') for xml path('')),1,1,''))
        
        ) as sal_no,create_user,update_user,mo_no from django_mf_bg"""
    # 1、这里是插入通知单自定义表的单号
    CommonSql_z = """INSERT INTO MF_TZ_Z(TZ_NO) SELECT A.TZ_NO FROM MF_TZ A WITH (NOLOCK) LEFT JOIN MF_TZ_Z B WITH(NOLOCK) ON A.TZ_NO=B.TZ_NO WHERE ISNULL(B.TZ_NO,'')='' """
    # 2、生产单号sql
    CommonSqlNo = """select max(right(bg_no,11)) as bg_no from django_mf_bg where left(Convert(varchar(100), no_dd, 23), 11) = CONVERT(varchar(10),GETDATE(),120) and bg_id='{0}'"""
    # 3、查询（Django_tf_bg）表身需要删除的单据
    CommonTfSql = """select a.zt_no,a.bg_id,a.qty,a.zl,b.qty_pg_lx,b.qty_sh_lx,b.spc_no,a.mo_no,a.zd_zc from Django_tf_bg a,(select a.tz_no,isnull(b.spc_no,0) as spc_no,c.qty_pg_lx,c.qty_sh_lx from 
                mf_tz a,dept b,mf_tz_z c where a.tz_no=c.tz_no and a.dep=b.dep)b where a.zt_no=b.tz_no and a.bg_no_id='{0}'"""
    # 4、单据查询
    #   查询表头
    CommonMf_sql = """select left(Convert(varchar(100), no_dd, 23), 11) as no_dd,create_time,update_time,bg_no,bg_id,zc_no,sal_no,create_user,update_user,mo_no,cast(isbad as int) as isbad,isnull(zd_zc,'') as zd_zc from django_mf_bg where bg_no='{0}'"""
    CommonTf_sql = """select left(Convert(varchar(100), a.no_dd, 23), 11) as no_dd,a.itm,a.bg_id,a.mo_no,a.zt_no,a.prd_no,a.prd_name,a.cc,a.zy,a.qty,a.zl,a.ms,a.rem,a.dd,a.prd_rem,a.bg_no_id,
                    a.sal_no,a.zc_no_up,a.zc_no_end as zc_no_dn,b.spc_no,isnull(a.zd_zc,'') as zd_zc from django_tf_bg a,(select a.tz_no,isnull(b.spc_no,0) as spc_no from mf_tz a,dept b 
                    where a.dep=b.dep)b where a.zt_no=b.tz_no and a.bg_no_id='{0}' order by a.itm asc"""
    # 5、查看用户信息
    CommonUsers = """select a.username as value,cast(a.username as varchar(20))+'--'+b.name as label from users a left join SunSystem..PSWD b on a.username=b.usr and b.compno='01'"""
    # 5、弹窗条件查询的sql
    # 查询通知单
    CommonFilter = """select left(Convert(varchar(100), no_dd, 23), 11) as no_dd,create_time,update_time,bg_no,bg_id,zc_no,(
        select
        (
        stuff(
        (select ',' + col from f_splitSTR(rtrim((replace(replace(replace(replace(sal_no,'[',''),']',''),'''',''),' ',''))),',') for xml path('')),1,1,''))
        
        ) as sal_no,create_user,update_user,mo_no from django_mf_bg {0}"""
    # 各类单据过滤查询
    # TzInfoJs = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,isnull(a.Qty,0)-isnull(e.qty_js_lx,0)as qty,
    # isnull(a.Qty,0)-isnull(e.qty_js_lx,0) as yscl,b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,isnull(a.qty,0) as totalqty,
    # zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
    # zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn),
    # isnull(f.spc_no,0) as spc_no
    # from MF_TZ a
    # left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from
    # tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
    # left join prdt c on a.mrp_no=c.prd_no
    # left join prdt_z d on c.prd_no=d.prd_no
    # left join mF_TZ_z e on a.tz_no=e.tz_no
    # left join dept f on f.dep = a.dep
    # where isnull(a.close_id,'')<>'T' and isnull(a.Qty,0)>isnull(e.qty_js_lx,0) and a.zc_no='{0}' and a.mo_no in('MO06230001','MO06220004')"""
    TzInfoJs = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,case when isnull(a.zc_no_up,'')<>'' then isnull(e.qty_djs_lx ,0) else isnull(a.qty,0) end-isnull(e.qty_js_lx,0)as qty,
case when isnull(a.zc_no_up,'')<>'' then isnull(e.qty_djs_lx ,0) else isnull(a.qty,0) end-isnull(e.qty_js_lx,0) as yscl,b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,
case when isnull(a.zc_no_up,'')<>'' then isnull(e.qty_djs_lx ,0) else isnull(a.qty,0) end as totalqty,
zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn),
isnull(f.spc_no,0) as spc_no
from MF_TZ a
left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from 
tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
left join prdt c on a.mrp_no=c.prd_no
left join prdt_z d on c.prd_no=d.prd_no
left join mF_TZ_z e on a.tz_no=e.tz_no
left join dept f on f.dep = a.dep
where isnull(a.close_id,'')<>'T' and case when isnull(a.zc_no_up,'')<>'' then isnull(e.qty_djs_lx ,0) else isnull(a.Qty,0) end > isnull(e.qty_js_lx,0) and a.zc_no='{0}'"""
    TzInfoPg = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,isnull(e.qty_js_lx,0)-isnull(e.qty_pg_lx,0) 
        as qty,isnull(e.qty_js_lx,0)-isnull(e.qty_pg_lx,0) as yscl,b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,isnull(e.qty_js_lx,0)as totalqty,
        zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
        zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn) 
        from MF_TZ a
        left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from 
        tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID  and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
        left join prdt c on a.mrp_no=c.prd_no
        left join prdt_z d on c.prd_no=d.prd_no
        left join mF_TZ_z e on a.tz_no=e.tz_no
        where isnull(a.close_id,'')<>'T' and isnull(e.qty_js_lx,0)>isnull(e.qty_pg_lx,0) and a.zc_no='{0}' and a.mo_no='{1}'"""
    TzInfoSh = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,isnull(e.qty_pg_lx,0)-isnull(e.qty_sh_lx,0) 
        as qty,isnull(e.qty_pg_lx,0)-isnull(e.qty_sh_lx,0) as yscl,b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,isnull(e.qty_pg_lx,0) as totalqty,
        zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
        zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn) ,
        sal_no=(select top 1 c1.sal_no+'-'+c1.name as sal_no from django_mf_bg a1,django_tf_bg b1,salm c1 where 
		REPLACE(REPLACE(a1.sal_no,''']',''),'[''','')=c1.sal_no and a1.bg_no=b1.bg_no_id and a1.bg_id='PG' and b1.zt_no=a.tz_no)
        from MF_TZ a
        left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from 
        tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID  and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
        left join prdt c on a.mrp_no=c.prd_no
        left join prdt_z d on c.prd_no=d.prd_no
        left join mF_TZ_z e on a.tz_no=e.tz_no
        where isnull(a.close_id,'')<>'T' and isnull(e.qty_pg_lx,0)>isnull(e.qty_sh_lx,0)  and a.zc_no='{0}' and a.mo_no='{1}'"""
    # 按单号过滤
    TzInfoZy = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,isnull(e.qty_sh_lx,0)-isnull(e.qty_zy_lx,0)-isnull(QTY_YCGD_LX,0) 
        as qty,isnull(e.qty_sh_lx,0)-isnull(e.qty_zy_lx,0)-isnull(QTY_YCGD_LX,0) as yscl, b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,isnull(e.qty_sh_lx,0) as totalqty,
        zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
        zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn)
        from MF_TZ a
        left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from 
        tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID  and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
        left join prdt c on a.mrp_no=c.prd_no
        left join prdt_z d on c.prd_no=d.prd_no
        left join mF_TZ_z e on a.tz_no=e.tz_no
        where isnull(a.close_id,'')<>'T' and isnull(e.qty_sh_lx,0)>isnull(e.qty_zy_lx,0)+isnull(QTY_YCGD_LX,0) and a.zc_no='{0}' and a.mo_no='{1}' and a.zc_no_dn='{2}'"""
    # 过滤全部
    TzInfoZy1 = """select 0 as zl, a.zc_no,a.mo_no,a.tz_no as zt_no,a.mrp_no as prd_no,c.name as prd_name,isnull(e.qty_sh_lx,0)-isnull(e.qty_zy_lx,0)-isnull(QTY_YCGD_LX,0) 
            as qty,isnull(e.qty_sh_lx,0)-isnull(e.qty_zy_lx,0)-isnull(QTY_YCGD_LX,0) as yscl, b.sc_lx as cc,b.zy_lx as zy,a.rem as ms,a.rrem as rem,d.dd,c.rem as prd_rem,isnull(e.qty_sh_lx,0) as totalqty,
            zc_no_up = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_up),
            zc_no_dn = (select isnull(zc_no+'-'+name,'') from zc_no where zc_no=a.zc_no_dn)
            from MF_TZ a
            left join (select aa.sc_lx,aa.zy_lx,aa.os_no,bb.mo_no from 
            tf_pos_z aa,mf_mo bb,tf_pos cc where cc.os_id='SO' AND AA.OS_ID=CC.OS_ID  and aa.itm=cc.itm and aa.os_no=cc.os_no and cc.est_itm=bb.est_itm and aa.os_no=bb.so_no)b on a.mo_no=b.mo_no
            left join prdt c on a.mrp_no=c.prd_no
            left join prdt_z d on c.prd_no=d.prd_no
            left join mF_TZ_z e on a.tz_no=e.tz_no
            where isnull(a.close_id,'')<>'T' and isnull(e.qty_sh_lx,0)>isnull(e.qty_zy_lx,0)+isnull(QTY_YCGD_LX,0) and a.zc_no='{0}' and a.zc_no_dn='{1}'"""
    # 批量导入用户
    InsetUser = """insert into users(last_login,is_superuser,username,first_name,last_name,email,is_staff,is_active,date_joined,password)
            select '' as last_login,0 as is_superuser, usr as username,'' as first_name,'' as last_name,'123456@qq.com' as email,
            1 as is_staff,1 as is_active,'' as date_joined,'pbkdf2_sha256$150000$cr8SzfavxM7f$2hVNA/mT/iVW4aC/ibGUjjMPcoqGl0JdCJnYqoqmt68=' as password
             from SunSystem..PSWD where usr not in(select username from users)and compno='01'"""
    # 接收单
    #     更新通知单栏位，如果spc_no==0时
    BgUpSql = """update a set a.qty_js_lx=b.qty,a.qty1_js_lx=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl, 
            zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    #     更新通知单栏位，如果spc_no==1时
    BgUpSql1 = """update a set a.qty_js_lx=b.qty,a.qty1_js_lx=b.zl,a.qty_pg_lx=b.qty,a.qty_sh_lx=b.qty from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl, 
                zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    # 删除的时候更新，当spc_no=0时
    BgUpdel = """update MF_TZ_Z set qty_js_lx=isnull(qty_js_lx,0)-{0},qty1_js_lx=isnull(qty1_js_lx,0)-{1} where tz_no='{2}'"""
    # 删除的时候更新，当spc_no=1时
    BgUpdel1 = """update MF_TZ_Z set qty_js_lx=isnull(qty_js_lx,0)-{0},qty1_js_lx=isnull(qty1_js_lx,0)-{1},qty_pg_lx=isnull(qty_pg_lx,0)-{3},qty_sh_lx=isnull(qty_sh_lx,0)-{4} where tz_no='{2}'"""
    #      查询单据的时候计算应生产量
    BgYscl = """SELECT ISNULL(A.QTY,0)-ISNULL(B.QTY_JS_LX,0) AS qty,a.tz_no,a.QTY as totalqty,cast(ISNULL(B.QTY_PG_LX,0) as float) as pgqty FROM MF_TZ A,MF_TZ_Z B WHERE A.TZ_NO=B.TZ_NO AND A.TZ_NO='{0}'"""
    # 派工单
    # 删除的时候更新
    PgUpdel = """update MF_TZ_Z set qty_pg_lx=isnull(qty_pg_lx,0)-{0},qty1_pg_lx=isnull(qty1_pg_lx,0)-{1} where tz_no='{2}'"""
    PgUpSql = """update a set a.qty_pg_lx=b.qty,a.qty1_pg_lx=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl, 
            zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    PgYscl = """select isnull(qty_js_lx,0)-isnull(qty_pg_lx,0) as qty,tz_no,qty_js_lx as totalqty from MF_TZ_Z where tz_no='{0}'"""
    # 收货单
    # 删除的时候更新
    ShUpdel = """update MF_TZ_Z set qty_sh_lx=isnull(qty_sh_lx,0)-{0},qty1_sh_lx=isnull(qty1_sh_lx,0)-{1} where tz_no='{2}'"""
    ShUpSql =  """update a set a.qty_sh_lx=b.qty,a.qty1_sh_lx=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl,
            zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    ShYscl = """select isnull(qty_pg_lx,0)-isnull(qty_sh_lx,0) as qty,tz_no,qty_pg_lx as totalqty from MF_TZ_Z where tz_no='{0}'"""
    # 转移单
    # 删除的时候更新
    #     正常单据
    ZyUpdel = """update MF_TZ_Z set qty_zy_lx=isnull(qty_zy_lx,0)-{0},qty1_zy_lx=isnull(qty1_zy_lx,0)-{1} where tz_no='{2}'"""
        # 异常单据
    ZyUpdel1 = """update MF_TZ_Z set QTY_YCGD_LX=isnull(QTY_YCGD_LX,0)-{0},QTY1_YCGD_LX=isnull(QTY1_YCGD_LX,0)-{1} where tz_no='{2}'"""
    # 正常单据更新对应的数量
    ZyUpSql =  """update a set a.qty_zy_lx=b.qty,a.qty1_zy_lx=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl,
          zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    # 更新下制程的数量栏位
    ZyUpSql_dai = """update a set a.QTY_DJS_LX=b.qty,a.QTY1_DJS_LX=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl,
          MO_NO from Django_tf_bg where mo_no='{0}' and bg_id='{1}' group by MO_NO)b,MF_TZ C WHERE A.TZ_NO=C.TZ_NO AND C.MO_NO=B.MO_NO and C.ZC_NO='{2}'"""
    # 异常单据更新到异常栏位上
    ZyUpSql1 = """update a set a.QTY_YCGD_LX=b.qty,a.QTY1_YCGD_LX=b.zl from MF_TZ_Z a,(select sum(isnull(qty,0)) as qty,sum(isnull(cast(zl as float),0)) as zl,
              zt_no from Django_tf_bg where zt_no='{0}' and bg_id='{1}' group by zt_no)b WHERE a.tz_no=b.zt_no"""
    ZyYscl = """select isnull(qty_sh_lx,0)-isnull(qty_zy_lx,0)-isnull(qty_ycgd_lx,0) as qty,tz_no,qty_sh_lx as totalqty from MF_TZ_Z where tz_no='{0}'"""
    # 通知单以生产量、自定义栏位
    ZySelfz = """select isnull(a.qty_fin,0) as qty_fin,isnull(a.qty,0) as qty from MF_TZ a,MF_TZ_z b where a.tz_no=b.tz_no and a.tz_no='{0}'"""
    ZyUpFin = """update a set a.QTY_FIN=isnull(b.qty_zy_lx,0)+isnull(b.QTY_YCGD_LX,0) from MF_TZ a,MF_TZ_z b where a.tz_no=b.tz_no and a.tz_no='{0}'"""
    ZyUpClose = """update mf_tz set close_id='T' where tz_no='{0}'"""
    ZyUpNoClose = """update mf_tz set close_id='F' where tz_no='{0}'"""
    # 更新掉旧的带接收数量
    update_old_dai_sql = """update a set a.QTY_DJS_LX=a.QTY_DJS_LX-{0},a.QTY1_DJS_LX=a.QTY1_DJS_LX-{1}  from mf_tz_z A,mf_tz b where a.tz_no=b.tz_no and b.mo_no='{2}' and b.zc_no='{3}' """