价格查询

select * from dbo.ModelPriceList --物料价格 成本价

select * from dbo.AgencyModelPriceList --代理价格

角色表格

Roles

user IsSytem=1是 管理员

IsLock=1 不允许登录系统

select * from Users where ValidDateEnd<GETDATE() and IsLock=0

update Users set IsLock=1 where ValidDateEnd<GETDATE() and IsLock=0

select * from Users where ValidDateEnd>GETDATE() and IsLock=0

审批流程表 （配置）

ApproveOrder

审批流程查询

select a.AutoID,a.Kind,f.ItemName,a.DutyID,b.DutyName,a.OrderCode,a.IsPermit,a.DeptCode,c.DeptName,a.ProjectStyleCode,a.ExtendKind,a.ApproveExplain,a.SysDateTime from dbo.ApproveOrder a left join DutyInfo b on a.DutyID=b.DutyCode left join FixItem f on a.Kind=f.ItemCode left join deptinfo c on c.deptcode=a.deptcode --where a.Kind='YLCS_5' and a.DeptCode is null order by ItemName,Kind,DeptCode,ExtendKind,OrderCode

流程插入语句

INSERT INTO [EduPlatform_QHGC].[dbo].[ApproveOrder20210413] ([Kind] ,[DutyID] ,[OrderCode] ,[IsPermit] ,[DeptCode] ,[ProjectStyleCode],[ExtendKind],[SysDateTime]) VALUES('YLCS_6','QHYG0A020','1','0',NULL,NULL,NULL,GETDATE())

--岗位查询

select a.DutyName ,a.DutyCode,b.UserName, b.ValidDateEnd , case when b.ValidDateEnd <= '2021-4-12' then '否' else '是' end from DutyInfo a left join Users b on a.DutyCode= b.DutyID or right(b.DutyID,9)=a.DutyCode or left (b.DutyID,9)=a.DutyCode where b.UserName<>'' --and b.UserName='孙萍' order by DutyName,ValidDateEnd

负责人修改

update a set a.XMJLHRCODE='00000180',XMJLNAME='曹宇辰',Memo=isnull(a.Memo,'')+'原负责人'+XMJLHRCODE+'离职变更' from PRJ_NormalInfo a inner join ProjectEX b on a.ProjectCode=b.ProjectCode and a.CurrState=b.CurrState where XMJLHRCODE in ( select Code from Users where UserName in('李亚楠','王英杰','何前玉','于鹏','刘立新')) and a.IsValid='1'

项目信息

导出项目信息

select a.ProjectCode,a.ProjectName,a.DeptCode,b.DeptName,a.OrgID,c.OrgName,a.Region,d.RegionName,c.FatherID,e.RegionName, case when b.ACTIVE_FLAG=1 then '有效' else '无效' end as deptState,a.InputDate from Project a inner join DeptInfo b on a.DeptCode=b.DeptCode left join OrgInfo c on c.OrgID=a.OrgID left join Region d on d.RegionCode=a.Region left join Region e on e.RegionCode=c.FatherID where a.IsValid='1'

导出部门信息

select a.DeptCode,a.DeptName,case when a.ACTIVE_FLAG=1 then '有效' else '无效' end deptstate,a.FatherDeptCode,b.DeptName from DeptInfo a left join DeptInfo b on a.FatherDeptCode=b.DeptCode order by a.DeptGrade

导出地区信息

select a.FatherID,b.RegionName,a.OrgID,a.OrgName , case when a.IsAvailable='1' then '有效' else '无效' end orgstate from OrgInfo a inner join Region b on a.FatherID=b.RegionCode order by a.FatherID,OrgID

根据部门添加流程

流程信息

模板标准语句

update PRJ_EarnEstimate set ApproveState=1 , ApproveDetail='客户备注手动修改通过',ApproveDate=GETDATE() where ProjectCode='PLC200001' and CurrState=6

update ProjectEX set ApproveState_YLCS=1 where ProjectCode='PLC200001'

--审批状态和表

--CASE WHEN d.ApproveState=1 THEN ''审批通过''

​ WHEN d.ApproveState=2 THEN ''审批拒绝''

​ WHEN d.ApproveState=3 THEN ''审批中''

​ WHEN d.ApproveState=4 THEN ''待审批

​ '' ELSE ''待提交'' END ApproveStateName

--PRJ_EarnEstimate 盈利测算表

-- ProjectEX 项目表 ApproveState_YLCS 这个是预算的状态 这个状态不改，审批漏斗审不了

---State=1 审批通过

update dbo.PRJ_ApproveInfo set State=1 where ProjectCode='PBJ200091' and AutoID=21480

--流程状态

--CASE WHEN d.ApproveState=1 THEN ''审批通过'' WHEN d.ApproveState=2 THEN ''审批拒绝'' WHEN d.ApproveState=3 THEN ''审批中'' WHEN d.ApproveState=4 THEN ''待审批'' ELSE ''待提交'' END ApproveStateName

update ProjectEX set ApproveState=1 where ProjectCode='PBJ200091'

--更新审批意见

update PRJ_ApproveInfo set Explain='代签项目，毛利率3.78%' where AutoID=23341

2022年4月28日执行

--PBJ200093\PBJ200091\PBJ200074\PBJ200073\PBJ200072 declare @p varchar(100) set @p='PBJ200072' select * from dbo.PRJ_ApproveInfo where ProjectCode=@p and State=3

update dbo.PRJ_ApproveInfo set State=1 where ProjectCode=@p and State=3 update ProjectEX set ApproveState=1 where ProjectCode=@p

2022年10月25日执行 结束审批流程

update PRJ_EarnEstimate set ApproveState=1 , ApproveDetail='客户备注手动修改通过',ApproveDate=GETDATE() where ProjectCode='PTJ200045' and CurrState=6

update ProjectEX set ApproveState_YLCS=1 where ProjectCode='PTJ200045'

2022年10月31日执行 更新预算审批状态

--update PRJ_EarnEstimate set ApproveState=1 where ProjectCode='PLC200001' and Flag='01' and CurrState=6

分包价格不更新

采购价格更新-保存主合同报价-试算分包。

添加新员工，需要分配区域和部门

添加员工审批 看不见是因为你没有给员工分区域和部门的数据权限