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 zt_no,bg_id,qty,zl from Django_tf_bg where 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 from django_mf_bg where bg_no='{0}'"""
    CommonTf_sql = """select left(Convert(varchar(100), no_dd, 23), 11) as no_dd,itm,bg_id,mo_no,zt_no,prd_no,prd_name,cc,zy,qty,zl,ms,rem,dd,prd_rem,bg_no_id,sal_no,zc_no_up,zc_no_end as zc_no_dn
                    from django_tf_bg where bg_no_id='{0}' order by 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)
    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(a.Qty,0)>isnull(e.qty_js_lx,0) and a.zc_no='B02' and a.mo_no in('MO06180004','MO06220004')"""
    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) 
        as qty,isnull(e.qty_sh_lx,0)-isnull(e.qty_zy_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) and a.zc_no='{0}' and a.mo_no='{1}' and a.ZC_NO_DN='{2}'"""
    # 批量导入用户
    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'"""
    # 接收单
    #     更新通知单栏位
    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"""
    # 删除的时候更新
    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}'"""
    #      查询单据的时候计算应生产量
    BgYscl = """SELECT ISNULL(A.QTY,0)-ISNULL(B.QTY_JS_LX,0) AS qty,a.tz_no,a.QTY as totalqty 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}'"""
    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"""
    ZyYscl = """select isnull(qty_sh_lx,0)-isnull(qty_zy_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=b.qty_zy_lx 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}'"""