Group_01/D7DJyueya
5
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
D7DJyueya/打卷检验管理/doc/sql.txt
DESKTOP-E401PHE\Administrator adb2c5b80a ~
2025-07-18 10:28:43 +08:00

3703 lines
154 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

/****** 对象: Table [dbo].[CP_Type] 脚本日期: 05/06/2014 11:58:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CP_Type](
[CPID] [char](20) NOT NULL,
[CPNo] [char](20) NOT NULL,
[CPName] [varchar](40) NOT NULL,
[CPParent] [char](20) NOT NULL,
[CPLevel] [int] NULL,
[Valid] [char](1) NOT NULL DEFAULT ('Y'),
[CPOrder] [int] NOT NULL DEFAULT ((99)),
[DefStr1] [varchar](20) NULL,
[DefStr2] [varchar](20) NULL,
[DefStr3] [varchar](20) NULL,
CONSTRAINT [PK_CP_Type] PRIMARY KEY CLUSTERED
(
[CPID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_Order] 脚本日期: 05/06/2014 11:53:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_View_Order '2012-01-01','2014-01-01',''
CREATE Procedure [dbo].[P_View_Order]
@Begdate varchar(20),
@enddate varchar(20),
@WSql varchar(2000)
AS
DECLARE @MSql varchar(8000)
Set @MSql=
'select AA.*,GYYQ=GM+SM+SLD+SXLD+SL+ND+PH+KQQ+DSGY+DPL+ZRX+SLYD
from
(select
OrderQty=(select Sum(PRTOrderQty) from JYOrder_Sub B where B.MainId=A.MainId group by B.MainId),
ZQty=(select Sum(PRTOrderQty) from JYOrder_Sub B where B.MainId=A.MainId group by B.MainId),
OrderUnit=(select Top 1 OrderUnit from JYOrder_Sub B where B.MainId=A.MainId),
PriceUnit=(select Top 1 PriceUnit from JYOrder_Sub B where B.MainId=A.MainId),
PRTPrice=(select Top 1 PRTPrice from JYOrder_Sub B where B.MainId=A.MainId),
SLBName=(select Top 1 SLBName from JYOrder_Sub B where B.MainId=A.MainId),
Case when isnull(MPRTColorLaoDu1,'''')='''' then '''' else ''干磨:''+RTrim(MPRTColorLaoDu1)+'' '' end as GM,
Case when isnull(MPRTColorLaoDu2,'''')='''' then '''' else ''湿磨:''+RTrim(MPRTColorLaoDu2)+'' '' end as SM,
Case when isnull(MPRTColorLaoDu3,'''')='''' then '''' else ''沾色牢度:''+RTrim(MPRTColorLaoDu3)+'' '' end as SLD,
Case when isnull(MPRTColorLaoDu4,'''')='''' then '''' else ''水洗牢度:''+RTrim(MPRTColorLaoDu4)+'' '' end as SXLD,
Case when isnull(MPRTSL,'''')='''' then '''' else ''缩率:''+RTrim(MPRTSL)+'' '' end as SL,
Case when isnull(MPRTNiuDu,'''')='''' then '''' else ''扭度:''+RTrim(MPRTNiuDu)+'' '' end as ND,
Case when isnull(MPRTPH,'''')='''' then '''' else ''PH:''+RTrim(MPRTPH)+'' '' end as PH,
Case when isnull(MPRTKQiQiu,'''')='''' then '''' else ''抗起球:''+RTrim(MPRTKQiQiu)+'' '' end as KQQ,
Case when isnull(MPRTDuiSeGY,'''')='''' then '''' else ''对色光源:''+RTrim(MPRTDuiSeGY)+'' '' end as DSGY,
Case when isnull(MPRTDingPoLv,'''')='''' then '''' else ''顶破率:''+RTrim(MPRTDingPoLv)+'' '' end as DPL,
Case when isnull(MPRTZuRanXing,'''')='''' then '''' else ''阻燃性:''+RTrim(MPRTZuRanXing)+'' '' end as ZRX,
Case when isnull(MPRTQtyNote,'''')='''' then '''' else ''数量溢短:''+RTrim(MPRTQtyNote)+'' '' end as SLYD,
A.*,A.OrderNo OrderNoM
from JYOrder_Main A
where A.OrdDate>='''+@Begdate+''' and A.OrdDate<'''+@enddate+''''
Set @MSql=@MSql+@WSql+')AA'
--Print(@MSql)
exec(@MSql)
GO
/****** 对象: Table [dbo].[HB] 脚本日期: 05/06/2014 11:58:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HB](
[序号] [float] NULL,
[产品编码] [nvarchar](255) NULL,
[中文名称] [nvarchar](255) NULL,
[英文名称] [nvarchar](255) NULL,
[成份] [nvarchar](255) NULL,
[英文成份] [nvarchar](255) NULL,
[规格] [nvarchar](255) NULL,
[门幅] [nvarchar](255) NULL,
[克重] [nvarchar](255) NULL,
[价格] [float] NULL,
[类别] [nvarchar](255) NULL,
[面料品种] [nvarchar](255) NULL,
[订单号] [nvarchar](255) NULL,
[工厂号] [nvarchar](255) NULL,
[供应商] [nvarchar](255) NULL,
[颜色] [nvarchar](255) NULL,
[采样人] [nvarchar](255) NULL,
[备注] [nvarchar](255) NULL,
[来样日期] [datetime] NULL,
[CYID] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Contract_Sub] 脚本日期: 05/06/2014 11:55:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contract_Sub](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[ConNo] [char](20) NULL,
[XHNo] [char](20) NULL,
[C_Code] [char](20) NULL,
[C_CodeName] [varchar](50) NULL,
[C_Spec] [varchar](40) NULL,
[C_Color] [varchar](20) NULL,
[MF] [varchar](20) NULL,
[KZ] [varchar](20) NULL,
[C_Qty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[C_Unit] [varchar](20) NULL,
[Price] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[PriceUnit] [varchar](20) NULL,
[MFUnit] [varchar](20) NULL,
[KZUnit] [varchar](20) NULL,
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MainUnit] [varchar](20) NULL,
[C_Status] [varchar](20) NULL,
[C_Valid] [char](1) NOT NULL DEFAULT ('Y'),
[C_Note] [varchar](200) NULL,
[MFQty] [decimal](18, 2) NULL,
[KZQty] [decimal](18, 2) NULL,
[MQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_Contract_Sub] PRIMARY KEY CLUSTERED
(
[MainId] ASC,
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[WFB_MJJY_CD] 脚本日期: 05/06/2014 12:04:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WFB_MJJY_CD](
[MJID] [char](20) NOT NULL,
[MCID] [char](20) NOT NULL,
[CDbeg] [varchar](20) NULL,
[CDend] [varchar](20) NULL,
[CDQty] [decimal](18, 2) NULL CONSTRAINT [DF__WFB_MJJY___CDQty__025D5595] DEFAULT ((0)),
[CDName] [varchar](20) NULL,
[CDCode] [varchar](20) NULL,
[CDReason] [varchar](50) NULL,
[KouFenType] [varchar](40) NULL,
[KouFenQty] [decimal](18, 1) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_WFB_MJJY_CD] PRIMARY KEY CLUSTERED
(
[MCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[YF_Money_CRID] 脚本日期: 05/06/2014 12:04:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[YF_Money_CRID](
[CRID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 对象: Table [dbo].[Contract_Sub_Mx] 脚本日期: 05/06/2014 11:56:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contract_Sub_Mx](
[SubId] [char](20) NOT NULL,
[MxId] [char](20) NOT NULL,
[ComeDate] [datetime] NULL,
[BatchNo] [char](20) NULL,
[MxQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MxUnit] [varchar](20) NULL,
[MxNote] [varchar](200) NULL,
[MxValid] [char](1) NOT NULL DEFAULT ('Y'),
[RKPlace] [varchar](40) NULL,
[FZUnit] [varchar](20) NULL,
[KCID] [int] NOT NULL DEFAULT ((0)),
[RKPLCode] [varchar](40) NULL,
[MXMQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_Contract_Sub_Mx] PRIMARY KEY CLUSTERED
(
[SubId] ASC,
[MxId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[CP_YDang] 脚本日期: 05/06/2014 11:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CP_YDang](
[CYID] [char](20) NOT NULL,
[CYNo] [varchar](40) NULL,
[CYName] [varchar](100) NULL,
[CYEName] [varchar](100) NULL,
[CYSpec] [varchar](200) NULL,
[CYCF] [varchar](100) NULL,
[CYECF] [varchar](100) NULL,
[CYType] [varchar](50) NULL,
[CYColor] [varchar](20) NULL,
[CYMF] [varchar](100) NULL,
[CYKZ] [varchar](100) NULL,
[CYPrice] [decimal](18, 2) NOT NULL CONSTRAINT [DF__CP_YDang__CYPric__7E6CC920] DEFAULT ((0)),
[CYPUnit] [varchar](20) NULL,
[CYPBZ] [varchar](20) NULL,
[CYValid] [char](1) NOT NULL CONSTRAINT [DF__CP_YDang__CYVali__7F60ED59] DEFAULT ('Y'),
[CYNote] [varchar](200) NULL,
[DefStr1] [varchar](20) NULL,
[DefStr2] [varchar](20) NULL,
[DefStr3] [varchar](20) NULL,
[DefFlt1] [decimal](18, 2) NOT NULL CONSTRAINT [DF__CP_YDang__DefFlt__00551192] DEFAULT ((0)),
[DefFlt2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__CP_YDang__DefFlt__014935CB] DEFAULT ((0)),
[DefFlt3] [decimal](18, 2) NOT NULL CONSTRAINT [DF__CP_YDang__DefFlt__023D5A04] DEFAULT ((0)),
[DefFlt4] [int] NOT NULL CONSTRAINT [DF__CP_YDang__DefFlt__03317E3D] DEFAULT ((0)),
[TPFlag] [bit] NOT NULL CONSTRAINT [DF__CP_YDang__TPFlag__0425A276] DEFAULT ((0)),
[DefStr4] [varchar](200) NULL,
[DefStr5] [varchar](200) NULL,
[DefStr6] [varchar](50) NULL,
[DefStr7] [varchar](50) NULL,
[DefStr8] [varchar](50) NULL,
[DefStr9] [varchar](50) NULL,
[DefStr10] [varchar](50) NULL,
[CYHX] [varchar](20) NULL,
[LYDate] [datetime] NULL,
[DefFlt5] [int] NULL,
[CYPrice1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[CYPrice2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ReadOnly] [bit] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_CP_YDang] PRIMARY KEY CLUSTERED
(
[CYID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ContractHZ_Main] 脚本日期: 05/06/2014 11:56:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractHZ_Main](
[MainId] [char](20) NOT NULL,
[ConNo] [char](20) NOT NULL,
[ClothCode] [char](20) NULL,
[ClothCodeName] [char](20) NULL,
[QDTime] [datetime] NULL,
[QDPalce] [varchar](200) NULL,
[ConType] [varchar](20) NULL,
[DeliveryDate] [datetime] NULL,
[FactoryNo] [char](60) NULL,
[FactoryNoName] [char](60) NULL,
[CompanyName] [char](60) NULL,
[ConTK1] [varchar](200) NULL,
[ConTK2] [varchar](200) NULL,
[ConTK3] [varchar](200) NULL,
[ConTK4] [varchar](200) NULL,
[ConTK5] [varchar](200) NULL,
[ConTK6] [varchar](200) NULL,
[ConTK7] [varchar](200) NULL,
[ConTK8] [varchar](200) NULL,
[ConTK9] [varchar](200) NULL,
[ConTK10] [varchar](200) NULL,
[Note] [varchar](250) NULL,
[Chker] [varchar](20) NULL,
[ChkStatus] [varchar](20) NULL,
[ChkNote] [varchar](200) NULL,
[Chktime] [datetime] NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Status] [varchar](20) NULL,
[Valid] [char](1) NOT NULL DEFAULT ('Y'),
[LXPerson] [varchar](20) NULL,
[LXTel] [varchar](20) NULL,
[JHPlace] [varchar](40) NULL,
CONSTRAINT [PK_ContractHZ_Main] PRIMARY KEY CLUSTERED
(
[MainId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ContractHZ_Sub] 脚本日期: 05/06/2014 11:56:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractHZ_Sub](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[XHNo] [char](20) NULL,
[C_Code] [char](20) NULL,
[C_CodeName] [varchar](50) NULL,
[C_Spec] [varchar](40) NULL,
[C_Color] [varchar](20) NULL,
[MF] [varchar](20) NULL,
[KZ] [varchar](20) NULL,
[C_Qty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[C_Unit] [varchar](20) NULL,
[Price] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[PriceUnit] [varchar](20) NULL,
[MFUnit] [varchar](20) NULL,
[KZUnit] [varchar](20) NULL,
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MainUnit] [varchar](20) NULL,
[C_Status] [varchar](20) NULL,
[C_Valid] [char](1) NOT NULL DEFAULT ('Y'),
[C_Note] [varchar](200) NULL,
[MFQty] [decimal](18, 2) NULL,
[KZQty] [decimal](18, 2) NULL,
[MQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_ContractHZ_Sub] PRIMARY KEY CLUSTERED
(
[MainId] ASC,
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ContractSX_Cloth_LL] 脚本日期: 05/06/2014 11:57:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[ContractSX_Cloth_LL](
[DHId] [char](20) NULL,
[OrdSubId] [char](20) NULL,
[LLId] [char](20) NOT NULL,
[TPPS] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[BCPQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPUnit] [varchar](20) NULL,
[HCQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[HCPS] [decimal](18, 0) NOT NULL DEFAULT ((0))
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [FirstNo] [varchar](40) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [FirstName] [varchar](40) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [LLIdx] [decimal](18, 0) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [TPMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [BCPMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [HCMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [HXPS] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [HXQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [HXMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [HXUnit] [varchar](20) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [ToNo] [varchar](40) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [ToName] [varchar](40) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [JXJGFlag] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD [DHIDHelp] [varchar](20) NULL
ALTER TABLE [dbo].[ContractSX_Cloth_LL] ADD CONSTRAINT [PK_ContractSX_Cloth_LL] PRIMARY KEY CLUSTERED
(
[LLId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Contract_Sub_Mxto] 脚本日期: 05/06/2014 11:56:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Contract_Sub_Mxto](
[MxId] [char](20) NOT NULL,
[ToId] [char](20) NOT NULL,
[OrdSubId] [char](20) NULL,
[TPQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__Contract___TPQty__6383C8BA] DEFAULT ((0)),
[TPDate] [datetime] NULL,
[TPNote] [varchar](200) NULL,
[TPPerson] [varchar](20) NULL,
[ToValid] [char](1) NOT NULL CONSTRAINT [DF__Contract___ToVal__4F7CD00D] DEFAULT ('Y'),
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_Contract_Sub_Mxto] PRIMARY KEY CLUSTERED
(
[MxId] ASC,
[ToId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ContractSX_Main] 脚本日期: 05/06/2014 11:57:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractSX_Main](
[MainId] [char](20) NOT NULL,
[ConNo] [char](20) NOT NULL,
[ClothCode] [char](20) NULL,
[ClothCodeName] [char](20) NULL,
[QDTime] [datetime] NULL,
[QDPalce] [varchar](200) NULL,
[DeliveryDate] [datetime] NULL,
[FactoryNo] [char](60) NULL,
[FactoryNoName] [char](60) NULL,
[CompanyName] [char](60) NULL,
[ConTK1] [varchar](200) NULL,
[ConTK2] [varchar](200) NULL,
[ConTK3] [varchar](200) NULL,
[ConTK4] [varchar](200) NULL,
[ConTK5] [varchar](200) NULL,
[ConTK6] [varchar](200) NULL,
[ConTK7] [varchar](200) NULL,
[ConTK8] [varchar](200) NULL,
[ConTK9] [varchar](200) NULL,
[ConTK10] [varchar](200) NULL,
[Note] [varchar](250) NULL,
[Chker] [varchar](20) NULL,
[ChkStatus] [varchar](20) NULL,
[ChkNote] [varchar](200) NULL,
[Chktime] [datetime] NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Status] [varchar](20) NULL,
[Valid] [char](1) NOT NULL DEFAULT ('Y'),
[LXPerson] [varchar](20) NULL,
[LXTel] [varchar](20) NULL,
[JHPlace] [varchar](40) NULL,
CONSTRAINT [PK_ContractSX_Main] PRIMARY KEY CLUSTERED
(
[MainId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_JYOrder_HZFX] 脚本日期: 05/06/2014 11:52:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from WFB_MJJY
--exec P_JYOrder_HZFX '2012-01-01','2012-12-30',3
--1 按订单，2 按生产单, 3 按卷,4 按订单个数,5按生产单个数,6单个按订单查询
CREATE Procedure [dbo].[P_JYOrder_HZFX]
@begdate varchar(20),
@enddate varchar(20),
@PState int,
@FFWSql varchar(200)
As
Create Table #Temp1(ORDID varchar(20),CDQty decimal(18,2) not null default(0))
Create Table #Temp2(AutoId int identity(1,1) not null,CDName varchar(20))
insert into #Temp2(CDName)
select distinct(CDName) from WFB_MJJY_CD
DECLARE @Min int
DECLARE @Max int
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
--------------------------------------------------------------------------更新表结构
DECLARE @Msql varchar(5000)
DECLARE @FileldName varchar(20)
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Alter table #Temp1 Add '+@FileldName+' decimal(18,2) not null default(0) '
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
--------------------------------------------------------------------------更新表结构
--------------------------------------------------------------------------更新疵点数量
IF @PState=1
BEGIN
insert into #Temp1(ORDID)
select distinct(SubId) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.SubId=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.SubId=#Temp1.ORDID)
END ELSE
IF @PState=2
BEGIN
insert into #Temp1(ORDID)
select distinct(APID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.APID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.APId=#Temp1.ORDID)
END
ELSE
IF @PState=3
BEGIN
insert into #Temp1(ORDID)
select distinct(MJID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
where A.MJID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
where A.MJId=#Temp1.ORDID)
END
Else
IF @PState=4
BEGIN
insert into #Temp1(ORDID)
select distinct(SubId) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.SubId=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.SubId=#Temp1.ORDID)
END ELSE
IF @PState=5
BEGIN
insert into #Temp1(ORDID)
select distinct(APID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.APID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.APId=#Temp1.ORDID)
END
--------------------------------------------------------------------------更新疵点数量
--------------------------------------------------------------------------显示数据
DECLARE @ViewSql varchar(500)
Set @ViewSql=''
select @Min=Min(AutoId),@Max=Max(AutoId) from #Temp2
While @Min<=@Max
Begin
select @FileldName='A.'+RTrim(CDName) from #Temp2 where AutoId=@Min
Set @ViewSql=@ViewSql+','+@FileldName
set @Min=@Min+1
End
DECLARE @MainSql varchar(8000)
IF @PState=1 or @PState=4
BEGIN
Set @MainSql='
select C.OrderNo,gangno='''',RTrim(PRTColor) PRTColor,RTrim(MPRTMF) MPRTMF,RTrim(MPRTKZ) MPRTKZ,
Rtrim(MPRTCodeName) MPRTCodeName,A.CDQty,
gangQty=(select Count(*) from JYOrder_Sub_AnPai AA where AA.SubId=B.SubId),
MJMaoZ=(select Cast(sum(MJMaoZ) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID),
MJLen=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID),
ZPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''正品''),
CPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''次品''),
LYQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''留样''),
JQty=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID group by WM.SubId),
ZPPS=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''正品'' group by WM.SubID ),
CPPS=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''次品'' group by WM.SubID ),
LYPS=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''留样'' group by WM.SubID ),
CDUnit=(select Top 1 MJTypeOther from WFB_MJJY WM where WM.SubID=A.ORDID and isnull(WM.MJTypeOther,'''')<>'''')'+@ViewSql+'
from #Temp1 A
inner join JYOrder_Sub B on A.ORDID=B.SubId
inner join JYOrder_Main C on B.MainId=C.MainId'
exec(@MainSql+@FFWSql)
END ELSE
IF @PState=2 or @PState=5
BEGIN
Set @MainSql='
select C.OrderNo,RTrim(B.AOrdDefStr1) gangno,RTrim(PRTColor) PRTColor,RTrim(MPRTMF) MPRTMF,RTrim(MPRTKZ) MPRTKZ,
Rtrim(MPRTCodeName) MPRTCodeName,A.CDQty,gangQty=1,
MJMaoZ=(select Cast(sum(MJMaoZ) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID),
MJLen=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID),
ZPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''正品''),
CPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''次品''),
LYQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''留样''),
JQty=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID group by WM.APID),
ZPPS=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''正品'' group by WM.APID ),
CPPS=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''次品'' group by WM.APID ),
LYPS=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''留样'' group by WM.APID ),
CDUnit=(select Top 1 MJTypeOther from WFB_MJJY WM where WM.APID=A.ORDID and isnull(WM.MJTypeOther,'''')<>'''')'+@ViewSql+'
from #Temp1 A
inner join JYOrder_Sub_AnPai B on A.ORDID=B.APID
inner join JYOrder_Sub BB on B.SubId=BB.SubId
inner join JYOrder_Main C on B.MainId=C.MainId '
exec(@MainSql+@FFWSql)
END ELSE
IF @PState=3
BEGIN
Set @MainSql='
select CC.OrderNo,B.GangNo,B.AOrdDefStr1,CC.MPRTCodeName,CC.MPRTMF,CC.MPRTKZ,B.AOrddefstr6,B.AOrddefstr4,
C.*,BB.PRTColor,A.CDQty,CDQK=dbo.F_Get_Order_SubStr(A.ORDID,''MJCDHZSL''),CC.CustomerNoName,C.PanDing,
KouFenQty=(select Sum(KouFenQty) from WFB_MJJY_CD CD where CD.MJID=C.MJID),C.MainId,C.SubId '+@ViewSql+'
from #Temp1 A
inner join WFB_MJJY C on A.ORDID=C.MJID
inner join JYOrder_Sub_AnPai B on C.APID=B.APID
inner join JYOrder_Sub BB on C.SubId=BB.SubId
inner join JYOrder_Main CC on C.MainId=CC.MainId'
exec(@MainSql+@FFWSql)
END
--------------------------------------------------------------------------显示数据
Drop table #Temp1
Drop table #Temp2
--select Top 100 * from JYOrder_Main
/****** 对象: UserDefinedFunction [dbo].[F_Get_Order_SubStr] 脚本日期: 12/24/2012 14:36:05 ******/
SET ANSI_NULLS ON
GO
/****** 对象: Table [dbo].[ContractSX_Cloth_DH] 脚本日期: 05/06/2014 11:57:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractSX_Cloth_DH](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[DHId] [char](20) NOT NULL,
[FirstNo] [varchar](40) NULL,
[FirstName] [varchar](40) NULL,
[BatchNo] [varchar](20) NULL,
[DHQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[DHUnit] [varchar](20) NULL,
[TPQtyHZ] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[DHStatus] [varchar](20) NULL,
[DHValid] [char](1) NOT NULL DEFAULT ('Y'),
[DHNote] [varchar](200) NULL,
[DHPS] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[TPPSHZ] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[DHMQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPMQtyHZ] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[DHType] [varchar](20) NULL,
[QJGDHID] [varchar](20) NULL,
CONSTRAINT [PK_ContractSX_Cloth_DH] PRIMARY KEY CLUSTERED
(
[DHId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_MaxBH] 脚本日期: 05/06/2014 12:03:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SY_MaxBH](
[BHStr] [char](2) NOT NULL,
[BHType] [varchar](20) NOT NULL,
[BHDate] [char](6) NULL,
[BHNo] [int] NULL DEFAULT ((0)),
[Type] [int] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[BHStr] ASC,
[BHType] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[XD_File] 脚本日期: 05/06/2014 12:04:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[XD_File](
[XFID] [char](20) NOT NULL,
[CYID] [varchar](20) NULL,
[CYNo] [char](20) NOT NULL,
[FileName] [varchar](50) NOT NULL,
[FileDate] [datetime] NOT NULL CONSTRAINT [DF__XD_File__FileDat__1C873BEC] DEFAULT (getdate()),
[FileType] [varchar](50) NOT NULL,
CONSTRAINT [PK_XD_File] PRIMARY KEY CLUSTERED
(
[XFID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_SET] 脚本日期: 05/06/2014 12:03:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SY_SET](
[Id] [int] NOT NULL,
[IsYGCodezdsc] [bit] NOT NULL DEFAULT ((1)),
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 对象: Table [dbo].[Contract_Cloth_DH] 脚本日期: 05/06/2014 11:54:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contract_Cloth_DH](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[DHId] [char](20) NOT NULL,
[FirstNo] [varchar](40) NULL,
[FirstName] [varchar](40) NULL,
[BatchNo] [varchar](20) NULL,
[DHQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[DHUnit] [varchar](20) NULL,
[TPQtyHZ] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[DHStatus] [varchar](20) NULL,
[DHValid] [char](1) NOT NULL DEFAULT ('Y'),
[DHNote] [varchar](200) NULL,
[DHPS] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[TPPSHZ] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[DHMQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPMQtyHZ] [decimal](18, 2) NOT NULL DEFAULT ((0))
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [DHTYpe] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [SXDHID] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [SXTPQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [PBName] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [PBSpec] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [PBMF] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [PBKZ] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD [SXMainId] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_DH] ADD CONSTRAINT [PK_Contract_Cloth_DH] PRIMARY KEY CLUSTERED
(
[DHId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Contract_Cloth_BefChkHX] 脚本日期: 05/06/2014 11:54:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contract_Cloth_BefChkHX](
[HXId] [char](20) NOT NULL,
[LLId] [char](20) NOT NULL,
[HXDate] [datetime] NULL,
[HXPS] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[HXQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[HXMQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[HXUnit] [varchar](20) NULL,
[ZSXS] [decimal](18, 2) NOT NULL DEFAULT ((1)),
[HXFactory] [varchar](40) NULL,
[HXNote] [varchar](40) NULL,
[HXType] [varchar](40) NULL,
[HXStatus] [varchar](40) NULL,
[GangNo] [varchar](20) NULL,
CONSTRAINT [PK_Contract_Cloth_BefChkHX] PRIMARY KEY CLUSTERED
(
[HXId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Contract_Cloth_LLMx] 脚本日期: 05/06/2014 11:55:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Contract_Cloth_LLMx](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[DHId] [char](20) NOT NULL,
[MXId] [char](20) NOT NULL,
[OrdSubId] [char](20) NULL,
[TPQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPUnit] [varchar](20) NULL,
[TPDate] [datetime] NULL,
[TPNote] [varchar](200) NULL,
[TPPerson] [varchar](20) NULL,
[ToValid] [char](1) NOT NULL DEFAULT ('Y'),
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPPS] [decimal](18, 0) NOT NULL DEFAULT ((0))
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [HCID] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [HCQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [HCPS] [decimal](18, 0) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [TPMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [MQty2] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [TPYZQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [YZQty2] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [ToNo] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [ToName] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [JGPrice] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [GangFee] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [HCFlag] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [SXMainId] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [SXDHID] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD [HJGType] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LLMx] ADD CONSTRAINT [PK_Contract_Cloth_LLMx] PRIMARY KEY CLUSTERED
(
[MXId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[CK_BanCP_KC] 脚本日期: 05/06/2014 11:54:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CK_BanCP_KC](
[CRID] [int] NOT NULL,
[BCID] [char](20) NOT NULL,
[KCKGQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCQtyUnit] [varchar](20) NULL,
[ZDYStr1] [varchar](20) NULL,
[ZDYStr2] [varchar](20) NULL,
[ZDYStr3] [varchar](20) NULL,
[ZDYStr4] [varchar](20) NULL,
[ZDYStr5] [varchar](20) NULL,
[ZDYFlote1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote4] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote5] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCNote] [varchar](250) NULL,
[KCStatus] [char](2) NULL,
[KCValid] [char](1) NOT NULL DEFAULT ('Y'),
[MJID] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[CRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_ClothHZ] 脚本日期: 05/06/2014 11:53:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_View_ClothHZ '2012-01-01','2012-12-31',' and ConNo like ''%1233%'' '
CREATE Procedure [dbo].[P_View_ClothHZ]
@begdate varchar(20),
@enddate varchar(20),
@WSQL varchar(100)
AS
DECLARE @MSQL varchar(7000)
Set @MSQL='SELECT * FROM(
Select ConNo,C_CodeName,C_Spec,MFQty,KZQty,PBC,FirstNo,FirstName,
DHPS,DHMQty,TPPS,TPMQty,BCPPS,BCPMQty,
BPPS=DHPS-TPPS,BPQty=DHMQty-TPMQty,
HCPS=HCPS-HXPS,HCMQty=HCMQty-HXMQty,
JGZPS=TPPS-(HCPS-HXPS),JGZMQty=BCPMQty-(HCMQty-HXMQty),
KCPS=DHPS-(HCPS-HXPS),KCQty=DHMQty-TPMQty+BCPMQty-(HCMQty-HXMQty),
case when TPMQty<>0 then (TPMQty-BCPMQty)*1.00/TPMQty*100 else null end as BCPSH
,case when BCPMQty<>0 and HCMQty>0 then (BCPMQty-HCMQty+HXMQty)*1.00/BCPMQty*100 else null end as CPSH
from(
select FirstNo,FirstName,DHPS,DHMQty,
TPPS=(select isnull(Sum(TPPS),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
TPMQty=(select isnull(Sum(TPMQty),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
BCPPS=(select isnull(Sum(TPPS),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
BCPMQty=(select isnull(Sum(BCPMQty),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
HCPS=(select isnull(Sum(HCPS),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
HCMQty=(select isnull(Sum(HCMQty),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
HXPS=(select isnull(Sum(HXPS),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
HXMQty=(select isnull(Sum(HXMQty),0) from Contract_Cloth_LL LL where LL.DHID=DH.DHID and isnull(DHIDHelp,'''')=''''),
CM.ConNo,PBC=CM.FactoryNoName,CS.C_CodeName,CS.C_Spec,CS.MFQty,CS.KZQty
from Contract_Cloth_DH DH
inner join Contract_Main CM on DH.MainId=CM.MainId
inner join Contract_Sub CS on DH.SubId=CS.SubId
where CM.QDTime>='''+@begdate+''' and CM.QDTime<'''+@enddate+'''
) AA
union All
Select ConNo,C_CodeName,C_Spec,MFQty,KZQty,PBC,FirstNo,FirstName,
DHPS,DHMQty,TPPS,TPMQty,BCPPS,BCPMQty,
BPPS=0,BPQty=0,
HCPS=HCPS-HXPS,HCMQty=HCMQty-HXMQty,
JGZPS=TPPS-(HCPS-HXPS),JGZMQty=BCPMQty-(HCMQty-HXMQty),
KCPS=DHPS-(HCPS-HXPS),KCQty=DHMQty-TPMQty+BCPMQty-(HCMQty-HXMQty)
,case when TPMQty<>0 then (TPMQty-BCPMQty)*1.00/TPMQty*100 else null end as BCPSH
,case when BCPMQty<>0 and HCMQty>0 then (BCPMQty-HCMQty+HXMQty)*1.00/BCPMQty*100 else null end as CPSH
from(
select DH.FirstNo,DH.FirstName,
DHPS=(select isnull(Sum(LL.HCPS),0) from Contract_Cloth_LL LL where LL.LLID=DH.DHIDHelp),
DHMQty=(select isnull(Sum(LL.HCMQty),0) from Contract_Cloth_LL LL where LL.LLID=DH.DHIDHelp),
DH.TPPS,
DH.TPMQty,
BCPPS=DH.TPPS,
DH.BCPMQty,
DH.HCPS,
DH.HCMQty,
DH.HXPS,
DH.HXMQty,
CM.ConNo,PBC=CM.FactoryNoName,CS.C_CodeName,CS.C_Spec,CS.MFQty,CS.KZQty
from Contract_Cloth_LL DH
inner join Contract_Cloth_DH DHB on DH.DHID=DHB.DHID
inner join Contract_Main CM on DHB.MainId=CM.MainId
inner join Contract_Sub CS on DHB.SubId=CS.SubId
where CM.QDTime>='''+@begdate+''' and CM.QDTime<'''+@enddate+''' and isnull(DH.DHIDHelp,'''')<>''''
) AA )AAA where 1=1 '
Set @MSql=@MSQL+@WSQL
--Print(@MSql)
Exec(@MSql)
GO
/****** 对象: Table [dbo].[Contract_Main] 脚本日期: 05/06/2014 11:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contract_Main](
[MainId] [char](20) NOT NULL,
[ConNo] [char](20) NOT NULL,
[ClothCode] [char](20) NULL,
[ClothCodeName] [char](20) NULL,
[QDTime] [datetime] NULL,
[QDPalce] [varchar](200) NULL,
[DeliveryDate] [datetime] NULL,
[FactoryNo] [char](60) NULL,
[FactoryNoName] [char](60) NULL,
[CompanyName] [char](60) NULL,
[ConTK1] [varchar](200) NULL,
[ConTK2] [varchar](200) NULL,
[ConTK3] [varchar](200) NULL,
[ConTK4] [varchar](200) NULL,
[ConTK5] [varchar](200) NULL,
[Note] [varchar](250) NULL,
[Chker] [varchar](20) NULL,
[ChkStatus] [varchar](20) NULL,
[ChkNote] [varchar](200) NULL,
[Chktime] [datetime] NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL CONSTRAINT [DF__Contract___Fillt__078C1F06] DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Status] [varchar](20) NULL,
[Valid] [char](1) NOT NULL CONSTRAINT [DF__Contract___Valid__0880433F] DEFAULT ('Y'),
[LXPerson] [varchar](20) NULL,
[LXTel] [varchar](20) NULL,
[JHPlace] [varchar](40) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[Contract_Main] ADD [ConTK6] [varchar](100) NULL
ALTER TABLE [dbo].[Contract_Main] ADD [ConTK7] [varchar](100) NULL
ALTER TABLE [dbo].[Contract_Main] ADD [ConTK8] [varchar](100) NULL
ALTER TABLE [dbo].[Contract_Main] ADD [ConTK9] [varchar](100) NULL
ALTER TABLE [dbo].[Contract_Main] ADD [ConTK10] [varchar](100) NULL
ALTER TABLE [dbo].[Contract_Main] ADD CONSTRAINT [PK_Contract_Main] PRIMARY KEY CLUSTERED
(
[MainId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[ClothContract_QryList] 脚本日期: 05/06/2014 11:52:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from JYOrder_Main
CREATE Procedure [dbo].[ClothContract_QryList]
@MainId varchar(20),
@WSql varchar(200)
AS
DECLARE @MSql varchar(1000)
Set @MSql='select OM.*,OS.*,
OM.ConNo ConNoM,OS.C_CodeName C_CodeNameM,
Money=Isnull(OS.C_Qty,0)*isnull(OS.Price,0),
CPQty=(select sum(PRTOrderQty) from JYOrder_Sub A inner join JYOrder_Main B on
A.MainId=B.MainId where B.OrderNo=OM.ConNo),
CPUnit=(select Top 1 OrderUnit from JYOrder_Sub A inner join JYOrder_Main B on
A.MainId=B.MainId where B.OrderNo=OM.ConNo),
CPDate=(select Top 1 DlyDate from JYOrder_Main B where B.OrderNo=OM.ConNo),
YWY=(select Top 1 Filler from JYOrder_Main B where B.OrderNo=OM.ConNo)
from Contract_Main OM
Left join Contract_Sub OS on OM.MainId=OS.MainId
where 1=1 '
IF RTRim(@MainId)<>''
Begin
Set @MSql=@MSql+' and OM.MainId='''+RTRIM(@MainId)+''''
End
Set @MSql=@MSql+@WSql
Exec(@MSql)
GO
/****** 对象: Table [dbo].[JYOrderCon_Main] 脚本日期: 05/06/2014 12:02:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JYOrderCon_Main](
[MainId] [char](20) NOT NULL,
[ConNo] [char](20) NULL,
[CustomerNo] [char](20) NULL,
[CustomerNoName] [varchar](80) NULL,
[FromPlace] [varchar](80) NULL,
[ToPlace] [varchar](80) NULL,
[PayMent] [varchar](200) NULL,
[ShippMent] [varchar](20) NULL,
[OrdDate] [datetime] NULL,
[DlyDate] [datetime] NULL,
[DlyPlace] [varchar](100) NULL,
[SYRName] [varchar](100) NULL,
[SYRAddress] [varchar](100) NULL,
[SYRTel] [varchar](20) NULL,
[SYRFax] [varchar](20) NULL,
[BankNo] [varchar](20) NULL,
[BankName] [varchar](100) NULL,
[BankAddress] [varchar](100) NULL,
[BankFastNo] [varchar](20) NULL,
[PostNo] [varchar](20) NULL,
[MPRTCode] [varchar](40) NULL,
[MPRTCodeName] [varchar](50) NULL,
[MPRTSpec] [varchar](40) NULL,
[MPRTCF] [varchar](40) NULL,
[MPRTMF] [varchar](40) NULL,
[MPRTKZ] [varchar](40) NULL,
[MPRTSL] [varchar](40) NULL,
[ConDefDate1] [datetime] NULL,
[ConDefDate2] [datetime] NULL,
[ConDefDate3] [datetime] NULL,
[ConDefDate4] [datetime] NULL,
[ConDefDate5] [datetime] NULL,
[Note] [varchar](250) NULL,
[ConDefStr1] [varchar](40) NULL,
[ConDefStr2] [varchar](40) NULL,
[ConDefStr3] [varchar](40) NULL,
[ConDefStr4] [varchar](40) NULL,
[ConDefStr5] [varchar](40) NULL,
[ConDefNote1] [varchar](40) NULL,
[ConDefNote2] [varchar](40) NULL,
[ConDefNote3] [varchar](40) NULL,
[ConDefNote4] [varchar](40) NULL,
[ConDefNote5] [varchar](40) NULL,
[ConPerson1] [varchar](20) NULL,
[ConPerson2] [varchar](20) NULL,
[ConPerson3] [varchar](20) NULL,
[ConPerson4] [varchar](20) NULL,
[ConPerson5] [varchar](20) NULL,
[ConFlag1] [bit] NOT NULL CONSTRAINT [DF__JYOrderCo__ConFl__5C6CB6D7] DEFAULT ((0)),
[ConFlag2] [bit] NOT NULL CONSTRAINT [DF__JYOrderCo__ConFl__5D60DB10] DEFAULT ((0)),
[ConFlag3] [bit] NOT NULL CONSTRAINT [DF__JYOrderCo__ConFl__5E54FF49] DEFAULT ((0)),
[ConFlag4] [bit] NOT NULL CONSTRAINT [DF__JYOrderCo__ConFl__5F492382] DEFAULT ((0)),
[ConFlag5] [bit] NOT NULL CONSTRAINT [DF__JYOrderCo__ConFl__603D47BB] DEFAULT ((0)),
[Chker] [varchar](20) NULL,
[ChkStatus] [varchar](20) NULL,
[ChkNote] [varchar](200) NULL,
[Chktime] [datetime] NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL CONSTRAINT [DF__JYOrderCo__Fillt__61316BF4] DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Status] [varchar](20) NULL,
[Valid] [char](1) NULL CONSTRAINT [DF__JYOrderCo__Valid__6225902D] DEFAULT ('Y'),
[DlyNote] [varchar](100) NULL,
[QtyNote] [varchar](100) NULL,
[PriceNote] [varchar](100) NULL,
[TelNo] [varchar](100) NULL,
[FaxNo] [varchar](100) NULL,
[MidBanck] [varchar](100) NULL,
[MidBank] [varchar](100) NULL,
[MPRTTYpe] [varchar](20) NULL,
[CPCFNo] [varchar](20) NULL,
[CPTanLi] [varchar](20) NULL,
[CPRanYin] [varchar](20) NULL,
[CPRanHouGY] [varchar](20) NULL,
[CPType] [varchar](20) NULL,
[CPZHName] [varchar](80) NULL,
[ZhiLiangNote] [varchar](100) NULL,
[ChuanYangNote] [varchar](100) NULL,
[YSBiaoZhunNote] [varchar](100) NULL,
[OtherNote] [varchar](100) NULL,
[CTMAddress] [varchar](100) NULL,
[CTMTelNo] [varchar](100) NULL,
[CTMFaxNo] [varchar](100) NULL,
[QianDPlace] [varchar](20) NULL,
[SelfBankNo] [varchar](40) NULL,
[BankSelfFastNo] [varchar](40) NULL,
[KHConNo] [varchar](40) NULL,
[CPCFBi] [varchar](20) NULL,
CONSTRAINT [PK_JJYOrderCon_Main] PRIMARY KEY CLUSTERED
(
[MainId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_ModuleMain] 脚本日期: 05/06/2014 12:03:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SY_ModuleMain](
[ModuleID] [char](2) NOT NULL,
[ModuleName] [varchar](60) NOT NULL,
[Idx] [int] NOT NULL CONSTRAINT [DF_SY_ModuleMain_Idx__0A3F39D6] DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[ModuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_ModuleSub] 脚本日期: 05/06/2014 12:03:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SY_ModuleSub](
[ModuleID] [char](2) NOT NULL,
[ModuleSubID] [char](2) NOT NULL,
[FormName] [char](30) NOT NULL,
[FormFile] [char](30) NOT NULL,
[FormID] [int] NOT NULL CONSTRAINT [PK__SY_ModuleSub__FormI__6DB809C1] DEFAULT ((0)),
[FormType] [int] NOT NULL CONSTRAINT [DF_XS_Modules_FormType_sdswe] DEFAULT ((0)),
[Idx] [int] NOT NULL CONSTRAINT [PK__SY_ModuleSub__Idx__0B335E0F] DEFAULT ((0)),
[FormPara] [varchar](100) NULL,
[FormPara1] [varchar](100) NULL,
[FormPara2] [varchar](100) NULL,
[FormPara3] [varchar](100) NULL,
[FormPara4] [varchar](100) NULL,
[FormPara5] [varchar](100) NULL,
[FormPara6] [varchar](100) NULL,
[FormPara7] [varchar](100) NULL,
[FormPara8] [varchar](100) NULL,
[FormPara9] [varchar](100) NULL,
[FormPara10] [varchar](100) NULL,
[FormPara11] [varchar](500) NULL,
[Note] [varchar](1000) NULL,
CONSTRAINT [PK__SY_ModuleSub] PRIMARY KEY CLUSTERED
(
[ModuleID] ASC,
[ModuleSubID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_Purview] 脚本日期: 05/06/2014 12:03:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SY_Purview](
[UserID] [char](20) NOT NULL,
[ModuleID] [char](2) NOT NULL,
[ModuleSubID] [char](2) NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserID] ASC,
[ModuleID] ASC,
[ModuleSubID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_OrderSub] 脚本日期: 05/06/2014 11:53:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from JYOrder_Main
--exec P_View_OrderSub '2012-01-01','2014-01-01',' and A.MainId=''JM130703001 '' '
CREATE Procedure [dbo].[P_View_OrderSub]
@Begdate varchar(20),
@enddate varchar(20),
@WSql varchar(2000)
AS
DECLARE @MSql varchar(8000)
Set @MSql=
'select AA.*,GYYQ=GM+SM+SLD+SXLD+SL+ND+PH+KQQ+DSGY+DPL+ZRX+SLYD
from
(select
OrderQty=(select Sum(PRTOrderQty) from JYOrder_Sub B where B.MainId=A.MainId group by B.MainId),
ZQty=(select Sum(PRTOrderQty) from JYOrder_Sub B where B.MainId=A.MainId group by B.MainId),
OrderUnit=(select Top 1 OrderUnit from JYOrder_Sub B where B.MainId=A.MainId),
PriceUnit=(select Top 1 PriceUnit from JYOrder_Sub B where B.MainId=A.MainId),
PRTPrice=(select Top 1 PRTPrice from JYOrder_Sub B where B.MainId=A.MainId),
SLBName=(select Top 1 SLBName from JYOrder_Sub B where B.MainId=A.MainId),
Case when isnull(MPRTColorLaoDu1,'''')='''' then '''' else ''干磨:''+RTrim(MPRTColorLaoDu1)+'' '' end as GM,
Case when isnull(MPRTColorLaoDu2,'''')='''' then '''' else ''湿磨:''+RTrim(MPRTColorLaoDu2)+'' '' end as SM,
Case when isnull(MPRTColorLaoDu3,'''')='''' then '''' else ''沾色牢度:''+RTrim(MPRTColorLaoDu3)+'' '' end as SLD,
Case when isnull(MPRTColorLaoDu4,'''')='''' then '''' else ''水洗牢度:''+RTrim(MPRTColorLaoDu4)+'' '' end as SXLD,
Case when isnull(MPRTSL,'''')='''' then '''' else ''缩率:''+RTrim(MPRTSL)+'' '' end as SL,
Case when isnull(MPRTNiuDu,'''')='''' then '''' else ''扭度:''+RTrim(MPRTNiuDu)+'' '' end as ND,
Case when isnull(MPRTPH,'''')='''' then '''' else ''PH:''+RTrim(MPRTPH)+'' '' end as PH,
Case when isnull(MPRTKQiQiu,'''')='''' then '''' else ''抗起球:''+RTrim(MPRTKQiQiu)+'' '' end as KQQ,
Case when isnull(MPRTDuiSeGY,'''')='''' then '''' else ''对色光源:''+RTrim(MPRTDuiSeGY)+'' '' end as DSGY,
Case when isnull(MPRTDingPoLv,'''')='''' then '''' else ''顶破率:''+RTrim(MPRTDingPoLv)+'' '' end as DPL,
Case when isnull(MPRTZuRanXing,'''')='''' then '''' else ''阻燃性:''+RTrim(MPRTZuRanXing)+'' '' end as ZRX,
Case when isnull(MPRTQtyNote,'''')='''' then '''' else ''数量溢短:''+RTrim(MPRTQtyNote)+'' '' end as SLYD,
B.XHNO,B.PRTColor,B.PRTOrderQty,B.PRTHX,B.SOrddefstr1,B.SOrddefstr4,B.SOrddefstr2,
Case when B.OrderUnit=''M'' then ''(''+Cast (Cast(dbo.F_Get_Order_MFKZ(A.MainId,''MF'')
*1.00/100*B.PRTOrderQty*dbo.F_Get_Order_MFKZ(A.MainId,''KZ'')
/1000 as decimal(18,0) ) as varchar(20))+''Kg)''
when B.OrderUnit=''Y'' then ''(''+Cast ( Cast(dbo.F_Get_Order_MFKZ(A.MainId,''MF'')
*1.00/100*B.PRTOrderQty*0.9144*dbo.F_Get_Order_MFKZ(A.MainId,''KZ'')
/1000 as decimal(18,0) )as varchar(20))+''Kg)''
else '''' end as PRTOrderKgQtyStr,
Case when B.OrderUnit=''M'' then Cast(dbo.F_Get_Order_MFKZ(A.MainId,''MF'')
*1.00/100*B.PRTOrderQty*dbo.F_Get_Order_MFKZ(A.MainId,''KZ'')
/1000 as decimal(18,0) )
when B.OrderUnit=''Y'' then Cast(dbo.F_Get_Order_MFKZ(A.MainId,''MF'')
*1.00/100*B.PRTOrderQty*0.9144*dbo.F_Get_Order_MFKZ(A.MainId,''KZ'')
/1000 as decimal(18,0) )
when B.OrderUnit=''Kg'' then B.PRTOrderQty
else null end as PRTOrderKgQty,
A.*,A.OrderNo OrderNoM
from JYOrder_Main A
inner join JYOrder_Sub B on A.MainId=B.MainId
where A.OrdDate>='''+@Begdate+''' and A.OrdDate<'''+@enddate+''''
Set @MSql=@MSql+@WSql+')AA'
--Print(@MSql)
exec(@MSql)
--select * from JYOrder_Main
--alter table JYOrder_Main Add PiZhong decimal(18,2) not null default(0)
GO
/****** 对象: Table [dbo].[JYOrder_Sub] 脚本日期: 05/06/2014 12:00:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JYOrder_Sub](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[XHNo] [char](20) NULL,
[OrderNo] [char](20) NULL,
[PRTCode] [varchar](40) NULL,
[PRTCodeName] [varchar](50) NULL,
[PRTSpec] [varchar](40) NULL,
[PRTType] [varchar](40) NULL,
[PRTColor] [varchar](20) NULL,
[PRTMF] [varchar](20) NULL,
[PRTKZ] [varchar](20) NULL,
[PRTOrderQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__PRTOr__0C85DE4D] DEFAULT ((0)),
[PRTClothQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__PRTCl__0D7A0286] DEFAULT ((0)),
[OrderUnit] [varchar](20) NULL,
[PRTPrice] [decimal](10, 4) NOT NULL CONSTRAINT [DF__JYOrder_S__PRTPr__0E6E26BF] DEFAULT ((0)),
[PriceUnit] [varchar](20) NULL,
[SOrdQty1] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__0F624AF8] DEFAULT ((0)),
[SOrdQty2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__10566F31] DEFAULT ((0)),
[SOrdQty3] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__114A936A] DEFAULT ((0)),
[SOrdQty4] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__123EB7A3] DEFAULT ((0)),
[SOrdQty5] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__1332DBDC] DEFAULT ((0)),
[SOrdQty6] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__14270015] DEFAULT ((0)),
[SOrdQty7] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__151B244E] DEFAULT ((0)),
[SOrdQty8] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__160F4887] DEFAULT ((0)),
[SOrdQty9] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__17036CC0] DEFAULT ((0)),
[SOrdQty10] [decimal](18, 2) NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdQ__17F790F9] DEFAULT ((0)),
[SOrddefstr1] [varchar](50) NULL,
[SOrddefstr2] [varchar](50) NULL,
[SOrddefstr3] [varchar](50) NULL,
[SOrddefstr4] [varchar](100) NULL,
[SOrddefstr5] [varchar](50) NULL,
[SOrddefstr6] [varchar](50) NULL,
[SOrddefstr7] [varchar](20) NULL,
[SOrddefstr8] [varchar](20) NULL,
[SOrddefstr9] [varchar](20) NULL,
[SOrddefstr10] [varchar](20) NULL,
[SOrdDefNote1] [varchar](40) NULL,
[SOrdDefNote2] [varchar](40) NULL,
[SOrdDefNote3] [varchar](40) NULL,
[SOrdDefNote4] [varchar](40) NULL,
[SOrdDefNote5] [varchar](40) NULL,
[SOrdDefNote6] [varchar](40) NULL,
[SOrdDefNote7] [varchar](40) NULL,
[SOrdDefNote8] [varchar](40) NULL,
[SOrdDefNote9] [varchar](40) NULL,
[SOrdDefNote10] [varchar](40) NULL,
[SOrdDefNote11] [varchar](40) NULL,
[SOrdDefNote12] [varchar](40) NULL,
[SOrdDefNote13] [varchar](40) NULL,
[SOrdDefNote14] [varchar](40) NULL,
[SOrdDefNote15] [varchar](40) NULL,
[SOrdDefNote16] [varchar](40) NULL,
[SOrdDefNote17] [varchar](40) NULL,
[SOrdDefNote18] [varchar](40) NULL,
[SOrdDefNote19] [varchar](40) NULL,
[SOrdDefNote20] [varchar](40) NULL,
[SOrdFlag1] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__18EBB532] DEFAULT ((0)),
[SOrdFlag2] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__19DFD96B] DEFAULT ((0)),
[SOrdFlag3] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1AD3FDA4] DEFAULT ((0)),
[SOrdFlag4] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1BC821DD] DEFAULT ((0)),
[SOrdFlag5] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1CBC4616] DEFAULT ((0)),
[SOrdFlag6] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1DB06A4F] DEFAULT ((0)),
[SOrdFlag7] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1EA48E88] DEFAULT ((0)),
[SOrdFlag8] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__1F98B2C1] DEFAULT ((0)),
[SOrdFlag9] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__208CD6FA] DEFAULT ((0)),
[SOrdFlag10] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__2180FB33] DEFAULT ((0)),
[SOrdFlag11] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__22751F6C] DEFAULT ((0)),
[SOrdFlag12] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__236943A5] DEFAULT ((0)),
[SOrdFlag13] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__245D67DE] DEFAULT ((0)),
[SOrdFlag14] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__25518C17] DEFAULT ((0)),
[SOrdFlag15] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__2645B050] DEFAULT ((0)),
[SOrdFlag16] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__2739D489] DEFAULT ((0)),
[SOrdFlag17] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__282DF8C2] DEFAULT ((0)),
[SOrdFlag18] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__29221CFB] DEFAULT ((0)),
[SOrdFlag19] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__2A164134] DEFAULT ((0)),
[SOrdFlag20] [bit] NOT NULL CONSTRAINT [DF__JYOrder_S__SOrdF__2B0A656D] DEFAULT ((0)),
[SubStatus] [varchar](20) NULL,
[SubValid] [char](1) NOT NULL CONSTRAINT [DF__JYOrder_S__SubVa__2BFE89A6] DEFAULT ('Y'),
[SLbName] [varchar](20) NULL,
[SLbInt] [int] NULL,
[PRTHX] [varchar](20) NULL,
[KgQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KgPrice] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_JYOrder_Sub] PRIMARY KEY CLUSTERED
(
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[SY_User] 脚本日期: 05/06/2014 12:04:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SY_User](
[UserID] [char](20) NOT NULL,
[UserName] [char](20) NULL,
[PassWord] [varchar](1000) NULL,
[IdNo] [char](18) NULL,
[Address] [varchar](100) NULL,
[Phone] [char](40) NULL,
[IsYwy] [bit] NOT NULL DEFAULT ((1)),
[EMail] [char](50) NULL,
[note] [nvarchar](250) NULL,
[Valid] [char](2) NOT NULL,
[Udept] [varchar](30) NULL,
[UType] [varchar](30) NULL,
[BanZu] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[P_Label] 脚本日期: 05/06/2014 12:03:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[P_Label](
[LabelID] [int] IDENTITY(1,1) NOT NULL,
[CustomerNO] [char](20) NULL,
[CustomerNOName] [char](20) NULL,
[OrderId] [int] NULL,
[LabelCaption] [varchar](60) NULL,
[labelClass] [varchar](20) NULL,
[LabelType] [varchar](20) NULL,
[LabelFile] [image] NULL,
[LabelFileName] [varchar](60) NULL,
[Filler] [varchar](20) NULL,
[FillTime] [datetime] NULL,
[beizhu] [varchar](200) NULL,
[Editer] [varchar](20) NULL,
[EditTime] [datetime] NULL,
[Valid] [char](1) NOT NULL CONSTRAINT [DF_P_Label_Valid] DEFAULT ('Y'),
CONSTRAINT [PK_P_Label] PRIMARY KEY CLUSTERED
(
[LabelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[KH_ZDY] 脚本日期: 05/06/2014 12:03:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KH_ZDY](
[ZDYNo] [char](20) NOT NULL,
[ZDYName] [varchar](100) NOT NULL,
[Type] [char](20) NOT NULL,
[MainType] [char](20) NULL,
[HelpType] [char](20) NULL,
[Note] [varchar](100) NULL,
[OrderNo] [int] NOT NULL DEFAULT ((0)),
[Valid] [char](1) NOT NULL DEFAULT ('Y'),
[ZdyFlag] [int] NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_KH_ZDY] PRIMARY KEY CLUSTERED
(
[ZDYNo] ASC,
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[YF_Money_KC] 脚本日期: 05/06/2014 12:05:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YF_Money_KC](
[CRID] [int] NOT NULL,
[KCQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCMoney] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCBBMoney] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[BZType] [varchar](20) NULL,
[FactoryNo] [varchar](20) NULL,
[FactoryName] [varchar](80) NULL,
[ZDYStr1] [varchar](20) NULL,
[ZDYStr2] [varchar](20) NULL,
[ZDYStr3] [varchar](20) NULL,
[ZDYStr4] [varchar](20) NULL,
[ZDYStr5] [varchar](20) NULL,
[ZDYFlote1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote4] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[ZDYFlote5] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KCNote] [varchar](250) NULL,
[KCStatus] [char](2) NULL,
[KCValid] [char](1) NOT NULL DEFAULT ('Y'),
[KCKPMoney] [decimal](18, 2) NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[CRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[Contract_Cloth_LL] 脚本日期: 05/06/2014 11:55:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[Contract_Cloth_LL](
[DHId] [char](20) NULL,
[OrdSubId] [char](20) NULL,
[LLId] [char](20) NOT NULL,
[TPPS] [decimal](18, 0) NOT NULL DEFAULT ((0)),
[BCPQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[TPUnit] [varchar](20) NULL,
[HCQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[HCPS] [decimal](18, 0) NOT NULL DEFAULT ((0))
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [FirstNo] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [FirstName] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [LLIdx] [decimal](18, 0) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [TPMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [BCPMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HCMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HXPS] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HXQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HXMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HXUnit] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [ToNo] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [ToName] [varchar](40) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [JXJGFlag] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [DHIDHelp] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [SXMainId] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [SXDHID] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD [HJGType] [varchar](20) NULL
ALTER TABLE [dbo].[Contract_Cloth_LL] ADD CONSTRAINT [PK_Contract_Cloth_LL] PRIMARY KEY CLUSTERED
(
[LLId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: UserDefinedFunction [dbo].[getPinYin] 脚本日期: 05/06/2014 12:05:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create FUNCTION [dbo].[getPinYin](@HZ NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @word NCHAR(1)
DECLARE @PY NVARCHAR(4000)
SET @PY = ''
WHILE LEN(@HZ) > 0
BEGIN
SET @word = LEFT(@HZ,1)
SET @PY = @PY + (CASE WHEN UNICODE(@word) BETWEEN 19968 AND 19968+20901
THEN (SELECT TOP 1 PY
FROM ( select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
WHERE word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC)
ELSE @word
END
)
SET @HZ = RIGHT(@HZ,LEN(@HZ)-1)
END
RETURN @PY
END
GO
/****** 对象: Table [dbo].[RT_FileUpdate] 脚本日期: 05/06/2014 12:03:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RT_FileUpdate](
[FileName] [varchar](50) NOT NULL,
[FileEditDate] [datetime] NOT NULL,
[FileSize] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Files] [image] NULL,
[Filler] [char](10) NULL,
[FillTime] [datetime] NOT NULL DEFAULT (getdate()),
[LastEditer] [char](10) NULL,
[LastEditTime] [datetime] NULL,
[FileCreateDate] [datetime] NULL,
[FilePath] [varchar](50) NULL DEFAULT (''),
[FileType] [varchar](20) NULL,
CONSTRAINT [PK_RT_FileUpdate] PRIMARY KEY CLUSTERED
(
[FileName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[CK_BanCP_CRID] 脚本日期: 05/06/2014 11:54:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CK_BanCP_CRID](
[CRID] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[CRID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** 对象: Table [dbo].[CK_BanCP_CR] 脚本日期: 05/06/2014 11:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CK_BanCP_CR](
[MainId] [char](20) NULL,
[SubId] [char](20) NULL,
[MJID] [char](20) NOT NULL,
[APID] [char](20) NOT NULL,
[BCID] [char](20) NOT NULL,
[CRTime] [datetime] NULL,
[CRFlag] [varchar](20) NULL,
[CRType] [varchar](20) NULL,
[CRID] [int] NULL,
[KGQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[QtyUnit] [varchar](20) NULL,
[CPType] [varchar](20) NULL,
[BCCode] [varchar](20) NULL,
[BCName] [varchar](20) NULL,
[BCFK] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[BCKZ] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[BCColor] [varchar](20) NULL,
[BCHW] [varchar](20) NULL,
[BCWKMS] [varchar](20) NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Valid] [varchar](5) NULL DEFAULT ('Y'),
[JTType] [varchar](20) NULL,
[BaoId] [varchar](20) NULL,
[DBKCType] [varchar](20) NULL,
[NowOutFlag] [bit] NOT NULL DEFAULT ((0)),
[CKOrdNo] [varchar](20) NULL,
[RKOrdID] [varchar](20) NULL,
[BaoNo] [varchar](20) NULL,
[RKPlace] [varchar](20) NULL,
[ZDPerson] [varchar](20) NULL,
[ZDTime] [datetime] NULL,
[CRNote] [varchar](200) NULL,
CONSTRAINT [PK_CK_BanCP_CR] PRIMARY KEY CLUSTERED
(
[BCID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_OrderJD] 脚本日期: 05/06/2014 11:53:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from Contract_Cloth_LLMX
--select * from CK_BanCp_CR
--exec P_View_OrderJD '','','',''
CREATE Procedure [dbo].[P_View_OrderJD]
@begdate varchar(20),
@enddate varchar(20),
@Filler varchar(20),
@WSql varchar(1000)
AS
DECLARE @MSQL varchar(8000)
Set @MSQL='
select A.MainId,A.OrderNo,A.CustomerNoName,A.DlyDate,A.OrdDate,A.MPRTCodeName,A.MPRTSpec,
A.MPRTCF,A.MPRTMF,A.MPRTKZ,A.MPRTSL,A.Note,TaiTou=RTrim(A.OrdDefStr2),A.Filler
,OrdColor=dbo.F_Get_Order_SubStr(A.MainId,''OrdColor'')
,OrdQty=(select sum(PRTOrderQty)
from JYOrder_Sub JS
where JS.MainId=A.MainId )
,OrdUnit=(select Top 1 JS.OrderUnit
from JYOrder_Sub JS
where JS.MainId=A.MainId )
,ClothHTFlag=(select case when count(*)>0 then 1 else 0 end As ClothHTFlag
from Contract_Main CM where CM.ConNo=A.OrderNo group by CM.ConNo)
, ClothHTQty=(select sum(CS.C_Qty)
from Contract_Main CM inner join Contract_Sub CS on CM.Mainid=CS.Mainid
where CM.ConNo=A.OrderNo )
, ClothHTUnit=(select Top 1 CS.C_Unit
from Contract_Main CM inner join Contract_Sub CS on CM.Mainid=CS.Mainid
where CM.ConNo=A.OrderNo )
,ClothDHFlag=(select case when count(*)>0 then 1 else 0 end As ClothDHFlag
from Contract_Main CM inner join Contract_Sub CS on CM.Mainid=CS.Mainid
inner join Contract_Sub_MX CSM on CS.Subid=CSM.Subid
where CM.ConNo=A.OrderNo group by CM.ConNo)
,ClothDHQty=(select sum(CSM.MXQty)
from Contract_Main CM inner join Contract_Sub CS on CM.Mainid=CS.Mainid
inner join Contract_Sub_MX CSM on CS.Subid=CSM.Subid
where CM.ConNo=A.OrderNo )
,ClothTPFlag=(select case when count(*)>0 then 1 else 0 end As ClothTPFlag
from Contract_Cloth_LL CL inner join JYOrder_Sub JS on CL.OrdSubid=JS.Subid
where JS.Mainid=A.MainId group by JS.Mainid)
,ClothTPQty=(select sum(CM.TPQty)
from Contract_Cloth_LLMX CM inner join JYOrder_Sub JS on CM.OrdSubid=JS.Subid
where JS.MainId=A.MainId )
,ClothTPUnit=(select Top 1 CM.TPUnit
from Contract_Cloth_LLMX CM inner join JYOrder_Sub JS on CM.OrdSubid=JS.Subid
where JS.MainId=A.MainId )
,ClothTPColor=dbo.F_Get_Order_SubStr(A.MainId,''TPColor'')
,ClothHCFlag=(select case when count(*)>0 then 1 else 0 end As ClothHCFlag
from JYOrder_Sub_AnPai JAP
where JAP.Mainid=A.MainId group by JAP.Mainid)
,ClothHCQty=(select sum(AOrdQty1)
from JYOrder_Sub_AnPai JAP
where JAP.Mainid=A.MainId group by JAP.Mainid)
,ClothHCUnit=(select Top 1 JAP.AOrddefstr2
from JYOrder_Sub_AnPai JAP
where JAP.Mainid=A.MainId )
,HCColor=dbo.F_Get_Order_SubStr(A.MainId,''HCColor'')
,ClothRKFlag=(select case when count(*)>0 then 1 else 0 end As ClothRKFlag
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId group by CBR.Mainid)
,ClothRKQty=(select sum(Qty)
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''检验入库'' group by CBR.Mainid)
,ClothRKUnit=(select Top 1 CBR.QtyUnit
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''检验入库'' )
,RKColor=dbo.F_Get_Order_SubStr(A.MainId,''RKColor'')
,ClothCKFlag=(select case when count(*)>0 then 1 else 0 end As ClothCKFlag
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''正常出库'' group by CBR.Mainid)
,ClothCKQty=(select sum(Qty)
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''正常出库'' group by CBR.Mainid)
,CKColor=dbo.F_Get_Order_SubStr(A.MainId,''CKColor'')
,KCZPQty=(select sum(KCQty) from CK_BanCP_CR CR inner join CK_BanCP_KC KC on CR.CRID=KC.CRID
where CR.MainId=A.MainId and KC.KCQty>0 and CRType=''检验入库'' and CPType=''正品''
)
,KCCPQty=(select sum(KCQty) from CK_BanCP_CR CR inner join CK_BanCP_KC KC on CR.CRID=KC.CRID
where CR.MainId=A.MainId and KC.KCQty>0 and CRType=''检验入库'' and CPType=''次品''
)
,KCLYQty=(select sum(KCQty) from CK_BanCP_CR CR inner join CK_BanCP_KC KC on CR.CRID=KC.CRID
where CR.MainId=A.MainId and KC.KCQty>0 and CRType=''检验入库'' and CPType=''留样''
)
from JYOrder_Main A where 1=1'
if RTrim(@begdate)<>''
begin
Set @MSQL=@MSQL+' and A.OrdDate>='''+RTrim(@begdate)+''' and A.OrdDate<'''+RTrim(@enddate)+''''
end
if RTrim(@Filler)<>''
begin
Set @MSQL=@MSQL+' and A.Filler='''+RTrim(@Filler)+''''
end
if RTrim(@WSql)<>''
begin
Set @MSQL=@MSQL+@WSql
end
exec(@MSQL)
GO
/****** 对象: StoredProcedure [dbo].[P_View_Cost] 脚本日期: 05/06/2014 11:53:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from YF_Money_CR
--exec P_View_Cost '2013-01-01','2013-10-10',''
CREATE Procedure [dbo].[P_View_Cost]
@Begdate varchar(20),
@Enddate varchar(20),
@WSql varchar(200)
AS
DECLARE @MSql varchar(8000)
Set @MSql='select A.*,A.OrderNo OrderNoM
,OrderQty=(select isnull(Sum(PRTOrderQty),0) from JYOrder_Sub B where B.MainId=A.MainId)
,DPQty=(select isnull(Sum(Qty),0) from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''应付款登记''
and YFType=''自动生成'' and YFName=''坯布费用'' )
,DPUnit=(select Top 1 QtyUnit from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''应付款登记''
and YFType=''自动生成'' and YFName=''坯布费用'' )
,PriceUnit=(select Top 1 PriceUnit from JYOrder_Sub B where B.MainId=A.MainId)
,OrderUnit=(select Top 1 OrderUnit from JYOrder_Sub B where B.MainId=A.MainId)
,PRTPrice=dbo.F_Get_Order_SubStr(A.MainId,''OrdPrice'')
,FHQty=(select isnull(sum(Qty),0) from CK_BanCp_CR B
where B.MainId=A.MainId and B.CRType=''正常出库'' and CPType=''正品'' )
,YSK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''应收款登记'')
,YSKYB=(select isnull(sum(Money),0) from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''应收款登记'')
,SK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''收款登记'' )
,SKYB=(select isnull(sum(Money),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''收款登记'' )
,FK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''付款登记'' )
,YFK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''应付款登记'' )
from JYOrder_Main A where 1=1'
if RTrim(@Begdate)<>''
begin
Set @Msql=@Msql+' and OrdDate>='''+@Begdate+''' and OrdDate<'''+@Enddate+''''
end
if RTrim(@Wsql)<>''
begin
Set @Msql=@Msql+@Wsql
end
Set @MSql='select YSK=isnull(YSK,0),FHQty=isnull(FHQty,0),
AA.* ,MLR=SK-YFK,YMoneyYB=YSKYB-SKYB,YMoneyBB=YSK-SK
from( '+@MSql+')AA'
--Print(@MSql)
exec(@MSql)
GO
/****** 对象: Table [dbo].[TBSubID] 脚本日期: 05/06/2014 12:04:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBSubID](
[SubId] [char](20) NULL,
[DName] [varchar](40) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_HC] 脚本日期: 05/06/2014 11:53:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[P_View_HC]
@Begdate varchar(20),
@Enddate varchar(20),
@WSql varchar(100)
AS
DECLARE @MSQL varchar(2000)
Set @MSQL='select B.OrderNo OrderNoM,C.PRTColor,A.*
from JYOrder_Sub_AnPai A
inner join JYOrder_Main B on A.MainId=B.MainId
inner join JYOrder_Sub C on A.SubId=C.SubId
where 1=1 '
IF RTRIM(@Begdate)<>''
BEGIN
Set @MSQL=@MSQL+' and A.ADefDate1>='''+@Begdate+'''
and A.ADefDate1<'''+@Enddate+''''
END
SET @MSQL=@MSQL+@WSql
EXEC(@MSQL)
GO
/****** 对象: Table [dbo].[ContractSX_Sub_Mx] 脚本日期: 05/06/2014 11:58:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractSX_Sub_Mx](
[SubId] [char](20) NOT NULL,
[MxId] [char](20) NOT NULL,
[ComeDate] [datetime] NULL,
[BatchNo] [char](20) NULL,
[MxQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__MxQty__119F9925] DEFAULT ((0)),
[Qty1] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty1__1293BD5E] DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty2__1387E197] DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty3__147C05D0] DEFAULT ((0)),
[MxUnit] [varchar](20) NULL,
[MxNote] [varchar](200) NULL,
[MxValid] [char](1) NOT NULL CONSTRAINT [DF__ContractS__MxVal__15702A09] DEFAULT ('Y'),
[RKPlace] [varchar](40) NULL,
[FZUnit] [varchar](20) NULL,
[KCID] [int] NOT NULL CONSTRAINT [DF__ContractSX__KCID__16644E42] DEFAULT ((0)),
[RKPLCode] [varchar](40) NULL,
[MXMQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__MXMQt__1758727B] DEFAULT ((0)),
[MXPrice] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__MXPri__5614BF03] DEFAULT ((0)),
[DHType] [varchar](20) NULL,
[QJGDHID] [varchar](20) NULL,
CONSTRAINT [PK_ContractSX_Sub_Mx] PRIMARY KEY CLUSTERED
(
[SubId] ASC,
[MxId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_JYOrder_HZFX10] 脚本日期: 05/06/2014 11:52:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from WFB_MJJY
--exec P_JYOrder_HZFX '2012-01-01','2013-12-30',3
CREATE Procedure [dbo].[P_JYOrder_HZFX10]
@begdate varchar(20),
@enddate varchar(20),
@PState int --1 按订单，2 按生产单, 3 按卷,4 按订单个数,5按生产单个数
As
Create Table #Temp1(ORDID varchar(20),CDQty decimal(18,2) not null default(0))
Create Table #Temp2(AutoId int identity(1,1) not null,CDName varchar(20))
insert into #Temp2(CDName)
select distinct(CDName) from WFB_MJJY_CD
DECLARE @Min int
DECLARE @Max int
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
--------------------------------------------------------------------------更新表结构
DECLARE @Msql varchar(500)
DECLARE @FileldName varchar(20)
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Alter table #Temp1 Add '+@FileldName+' decimal(18,2) not null default(0) '
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
--------------------------------------------------------------------------更新表结构
--------------------------------------------------------------------------更新疵点数量
IF @PState=1
BEGIN
insert into #Temp1(ORDID)
select distinct(SubId) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.SubId=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.SubId=#Temp1.ORDID)
END ELSE
IF @PState=2
BEGIN
insert into #Temp1(ORDID)
select distinct(APID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.APID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.APId=#Temp1.ORDID)
END
ELSE
IF @PState=3
BEGIN
insert into #Temp1(ORDID)
select distinct(MJID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
where A.MJID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(Sum(isnull(CDQty,0)),0) from WFB_MJJY_CD A
where A.MJId=#Temp1.ORDID)
END
Else
IF @PState=4
BEGIN
insert into #Temp1(ORDID)
select distinct(SubId) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.SubId=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
--print(@Msql)
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.SubId=#Temp1.ORDID)
END ELSE
IF @PState=5
BEGIN
insert into #Temp1(ORDID)
select distinct(APID) from WFB_MJJY where FillTime>=@begdate and FillTime<@enddate
select @Min=isnull(Min(AutoId),0),@Max=isnull(Max(AutoId),0) from #Temp2
While @Min<=@Max
Begin
select @FileldName=RTrim(CDName) from #Temp2 where AutoId=@Min
Set @Msql='Update #Temp1 Set '+@FileldName+'='
+'(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID
where B.APID=#Temp1.ORDID and A.CDName='''+@FileldName+''')'
exec(@Msql)
set @Min=@Min+1
End
UPdate #Temp1 Set CDQty=(select isnull(count(*),0) from WFB_MJJY_CD A
inner join WFB_MJJY B on A.MJID=B.MJID where B.APId=#Temp1.ORDID)
END
--------------------------------------------------------------------------更新疵点数量
--------------------------------------------------------------------------显示数据
DECLARE @ViewSql varchar(500)
Set @ViewSql=''
select @Min=Min(AutoId),@Max=Max(AutoId) from #Temp2
While @Min<=@Max
Begin
select @FileldName='A.'+RTrim(CDName) from #Temp2 where AutoId=@Min
Set @ViewSql=@ViewSql+','+@FileldName
set @Min=@Min+1
End
DECLARE @MainSql varchar(8000)
IF @PState=1 or @PState=4
BEGIN
Set @MainSql='
select C.OrderNo,gangno='''',RTrim(PRTColor) PRTColor,RTrim(MPRTMF) MPRTMF,RTrim(MPRTKZ) MPRTKZ,
Rtrim(MPRTCodeName) MPRTCodeName,A.CDQty,
MJMaoZ=(select Cast(sum(MJMaoZ) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID),
MJLen=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID),
JQty=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID group by WM.SubId),
CDUnit=(select Top 1 MJTypeOther from WFB_MJJY WM where WM.SubID=A.ORDID and isnull(WM.MJTypeOther,'''')<>'''')'+@ViewSql+'
from #Temp1 A
inner join JYOrder_Sub B on A.ORDID=B.SubId
inner join JYOrder_Main C on B.MainId=C.MainId'
exec(@MainSql)
END ELSE
IF @PState=2 or @PState=5
BEGIN
Set @MainSql='
select CC.OrderNo,RTrim(B.gangno) gangno,RTrim(PRTColor) PRTColor,RTrim(MPRTMF) MPRTMF,RTrim(MPRTKZ) MPRTKZ,
Rtrim(MPRTCodeName) MPRTCodeName,A.CDQty,
MJMaoZ=(select Cast(sum(MJMaoZ) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID),
MJLen=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID),
JQty=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID group by WM.APID),
CDUnit=(select Top 1 MJTypeOther from WFB_MJJY WM where WM.APID=A.ORDID and isnull(WM.MJTypeOther,'''')<>'''')'+@ViewSql+'
from #Temp1 A
inner join JYOrder_Sub_AnPai B on A.ORDID=B.APID
inner join JYOrder_Sub BB on B.SubId=BB.SubId
inner join JYOrder_Main CC on B.MainId=CC.MainId'
exec(@MainSql)
END ELSE
IF @PState=3
BEGIN
Set @MainSql='
select CC.OrderNo,B.GangNo,CC.MPRTCodeName,CC.MPRTMF,CC.MPRTKZ,
C.*,BB.PRTColor,A.CDQty,CDQK=dbo.F_Get_Order_SubStr(A.ORDID,''MJCDHZSL'') '+@ViewSql+'
from #Temp1 A
inner join WFB_MJJY C on A.ORDID=C.MJID
inner join JYOrder_Sub_AnPai B on C.APID=B.APID
inner join JYOrder_Sub BB on C.SubId=BB.SubId
inner join JYOrder_Main CC on C.MainId=CC.MainId'
exec(@MainSql)
END
--------------------------------------------------------------------------显示数据
Drop table #Temp1
Drop table #Temp2
/****** 对象: UserDefinedFunction [dbo].[F_Get_Order_SubStr] 脚本日期: 12/24/2012 14:36:05 ******/
SET ANSI_NULLS ON
GO
/****** 对象: Table [dbo].[JYOrder_Sub_AnPai] 脚本日期: 05/06/2014 12:01:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[JYOrder_Sub_AnPai](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[APID] [char](20) NOT NULL,
[GangNo] [varchar](20) NULL,
[XHNo] [char](20) NULL,
[OrderNo] [char](20) NULL,
[AOrdQty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty4] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty5] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty6] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty7] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty8] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty9] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrdQty10] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[AOrddefstr1] [varchar](20) NULL,
[AOrddefstr2] [varchar](20) NULL,
[AOrddefstr3] [varchar](20) NULL,
[AOrddefstr4] [varchar](20) NULL,
[AOrddefstr5] [varchar](20) NULL,
[AOrddefstr6] [varchar](20) NULL,
[AOrddefstr7] [varchar](20) NULL,
[AOrddefstr8] [varchar](20) NULL,
[AOrddefstr9] [varchar](20) NULL,
[AOrddefstr10] [varchar](20) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote1] [varchar](200) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote2] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote3] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote4] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote5] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote6] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote7] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote8] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote9] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote10] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote11] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote12] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote13] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote14] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote15] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote16] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote17] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote18] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote19] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote20] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote21] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote22] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote23] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote24] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote25] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote26] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote27] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote28] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote29] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote30] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote31] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote32] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote33] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote34] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote35] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote36] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote37] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote38] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote39] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdDefNote40] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag1] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag2] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag3] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag4] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag5] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag6] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag7] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag8] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag9] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AOrdFlag10] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate1] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate2] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate3] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate4] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate5] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate6] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate7] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate8] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate9] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ADefDate10] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Chker] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ChkStatus] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ChkNote] [varchar](200) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Chktime] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Filler] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Filltime] [datetime] NOT NULL DEFAULT (getdate())
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Editer] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [Edittime] [datetime] NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AStatus] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [AValid] [char](1) NOT NULL DEFAULT ('Y')
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [ZSXS] [decimal](18, 2) NOT NULL DEFAULT ((1))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [HCYZQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [HCMQty] [decimal](18, 2) NOT NULL DEFAULT ((0))
SET ANSI_PADDING ON
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [LLID] [varchar](20) NULL
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [LBName] [varchar](40) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [JYMF] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [JYKZ] [decimal](18, 2) NOT NULL DEFAULT ((0))
SET ANSI_PADDING ON
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [APType] [varchar](20) NULL
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD [JGPrice] [decimal](18, 2) NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[JYOrder_Sub_AnPai] ADD CONSTRAINT [PK_JYOrder_Sub_AnPai] PRIMARY KEY CLUSTERED
(
[APID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_View_CPKCMX] 脚本日期: 05/06/2014 11:53:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [P_View_CPKCMX] ''
CREATE Procedure [dbo].[P_View_CPKCMX]
@WSQl varchar(200)
AS
DECLARE @MSQL varchar(6000)
Set @MSQL='
Select AAA.*,Case when CDFlag>0 then dbo.F_Get_Order_SubStr(AAA.MJID,''MJCDHZSL'') else '''' end as CDQK
from (
select AA.KCQty,AA.KCKgQty,B.OrderNo,B.MPRTCodeName,C.PRTColor,B.MPRTMF,B.MPRTKZ,D.MJXH,C.SOrddefstr1,C.PRTHX
,AOrdDefstr1=(select AOrdDefstr1 from JYOrder_Sub_AnPai where ApId=A.ApId),B.Filler,B.OrdPerson2,B.MainId,C.SubId
,A.CPType,AA.MJID,CDFlag=(select Count(*) from WFB_MJJY_CD CD where MJID=D.MJID),A.QtyUnit,AA.CRID,A.CRTime
from CK_BanCP_KC AA
inner join CK_BanCP_CR A on AA.CRID=A.CRID and A.CRType=''检验入库''
inner join JYOrder_Main B on A.MainId=B.MainId
inner join JYOrder_Sub C on A.SubId=C.SubId
inner join WFB_MJJY D on AA.MJId=D.MJId
where (AA.KCQty>0 or AA.KCKGQty>0) '+@WSQl
Set @MSQL=@MSQL+')AAA order by OrderNo,PRTColor,MJID'
exec(@MSQL)
GO
/****** 对象: Table [dbo].[YF_Money_CR] 脚本日期: 05/06/2014 12:04:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YF_Money_CR](
[MainId] [char](20) NULL,
[SubId] [char](20) NULL,
[YFID] [char](20) NOT NULL,
[CRTime] [datetime] NULL,
[QtyFlag] [int] NOT NULL CONSTRAINT [DF__YF_Money___QtyFl__24E777C3] DEFAULT ((1)),
[CRFlag] [varchar](20) NULL,
[CRType] [varchar](20) NULL,
[CRID] [int] NULL,
[Qty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__YF_Money_CR__Qty__25DB9BFC] DEFAULT ((0)),
[Price] [decimal](18, 4) NULL CONSTRAINT [DF__YF_Money___Price__26CFC035] DEFAULT ((0)),
[HuiLv] [decimal](18, 4) NOT NULL CONSTRAINT [DF__YF_Money___HuiLv__27C3E46E] DEFAULT ((0)),
[BZType] [varchar](20) NULL,
[Money] [decimal](18, 2) NOT NULL CONSTRAINT [DF__YF_Money___Money__28B808A7] DEFAULT ((0)),
[BBMoney] [decimal](18, 2) NOT NULL CONSTRAINT [DF__YF_Money___BBMon__29AC2CE0] DEFAULT ((0)),
[QtyUnit] [varchar](20) NULL,
[FactoryNo] [varchar](20) NULL,
[FactoryName] [varchar](80) NULL,
[YFType] [varchar](20) NULL,
[YFCode] [varchar](20) NULL,
[YFName] [varchar](20) NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL CONSTRAINT [DF__YF_Money___Fillt__2AA05119] DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Valid] [varchar](5) NULL CONSTRAINT [DF__YF_Money___Valid__2B947552] DEFAULT ('Y'),
[NowOutFlag] [bit] NOT NULL CONSTRAINT [DF__YF_Money___NowOu__2C88998B] DEFAULT ((0)),
[OrdMainId] [varchar](20) NULL,
[OrdSubId] [varchar](20) NULL,
[KPMoney] [decimal](18, 2) NOT NULL CONSTRAINT [DF__YF_Money___KPMon__2D7CBDC4] DEFAULT ((0)),
[KCKPMoney] [decimal](18, 2) NOT NULL CONSTRAINT [DF__YF_Money___KCKPM__2E70E1FD] DEFAULT ((0)),
[PS] [decimal](18, 0) NOT NULL CONSTRAINT [DF__YF_Money_CR__PS__2F650636] DEFAULT ((0)),
[BatchNo] [varchar](40) NULL,
[Note] [varchar](200) NULL,
[PayMent] [varchar](40) NULL,
[YFTypeID] [varchar](20) NULL,
[YFDefFlag1] [bit] NOT NULL CONSTRAINT [DF__YF_Money___YFDef__5772F790] DEFAULT ((0)),
[YFDefFlag2] [bit] NOT NULL CONSTRAINT [DF__YF_Money___YFDef__58671BC9] DEFAULT ((0)),
[YFDefFlag3] [bit] NOT NULL CONSTRAINT [DF__YF_Money___YFDef__595B4002] DEFAULT ((0)),
[ComTaiTou] [varchar](50) NULL,
[CPName] [varchar](20) NULL,
[CPSpec] [varchar](20) NULL,
[KPNO] [varchar](40) NULL,
CONSTRAINT [PK_YF_Money_CR] PRIMARY KEY CLUSTERED
(
[YFID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_Label_PrintSet] 脚本日期: 05/06/2014 11:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--功能：获取样品打印标签用
--著者：李
--时间：2009-07-23
CREATE PROCEDURE [dbo].[P_Label_PrintSet]
@splcode varchar(30)=''
AS
BEGIN
SELECT A.TmId as BarCode,
A.JH as 卷号,
A.OrderNo as 订单号,
A.CPCode as 品名编号,
A.CPName as 品名,
A.Color as 颜色,
A.GangNo as 缸号,
A.HX as 花型,
A.MF1 as 门幅上限,
A.MF2 as 门幅下限,
A.MFunit as 门幅单位,
A.KZ1 as 克重上限,
A.KZ2 as 克重下限,
A.KZUnit as 克重单位,
A.QtyMi as 米数,
A.QtyMa as 码数,
A.QtyJZ as 公斤,
A.QtyMZ as 毛重,
A.QtyPZ as 皮重,
CASE
WHEN (A.MF1 > 0 AND A.MF2 > 0) THEN CAST(CAST(A.MF1 AS FLOAT) AS VARCHAR) + '-' + CAST(CAST(A.MF1 AS FLOAT) AS VARCHAR) + A.MFunit
WHEN A.MF1 > 0 THEN CAST(CAST(A.MF1 AS FLOAT) AS VARCHAR) + A.MFunit
WHEN A.MF2 > 0 THEN CAST(CAST(A.MF2 AS FLOAT) AS VARCHAR)+ A.MFunit
ELSE ''
END AS 门幅,
CASE
WHEN (A.KZ1 > 0 AND A.KZ2 > 0) THEN CAST(CAST(A.KZ1 AS FLOAT) AS VARCHAR) + '-' +CAST(CAST(A.KZ2 AS FLOAT) AS VARCHAR) + A.KZUnit
WHEN A.KZ1 > 0 THEN CAST(CAST(A.KZ1 AS FLOAT) AS VARCHAR) + A.KZUnit
WHEN A.KZ2 > 0 THEN CAST(CAST(A.KZ2 AS FLOAT) AS VARCHAR)+ A.KZUnit
ELSE ''
END AS 克重
from CP_JY A
WHERE rtrim(A.TmId)=@splcode
END
GO
/****** 对象: Table [dbo].[JYOrderCon_Sub] 脚本日期: 05/06/2014 12:03:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JYOrderCon_Sub](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[XHNo] [char](20) NULL,
[ConNo] [char](20) NULL,
[PRTCode] [varchar](40) NULL,
[PRTCodeName] [varchar](50) NULL,
[PRTSpec] [varchar](40) NULL,
[PRTType] [varchar](40) NULL,
[PRTColor] [varchar](20) NULL,
[PRTMF] [varchar](20) NULL,
[PRTKZ] [varchar](20) NULL,
[PRTOrderQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[PRTClothQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[OrderUnit] [varchar](20) NULL,
[PRTPrice] [decimal](10, 4) NOT NULL DEFAULT ((0)),
[PriceUnit] [varchar](20) NULL,
[SOrdQty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty4] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty5] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty6] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty7] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty8] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty9] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrdQty10] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SOrddefstr1] [varchar](20) NULL,
[SOrddefstr2] [varchar](20) NULL,
[SOrddefstr3] [varchar](20) NULL,
[SOrddefstr4] [varchar](20) NULL,
[SOrddefstr5] [varchar](20) NULL,
[SOrddefstr6] [varchar](20) NULL,
[SOrddefstr7] [varchar](20) NULL,
[SOrddefstr8] [varchar](20) NULL,
[SOrddefstr9] [varchar](20) NULL,
[SOrddefstr10] [varchar](20) NULL,
[SOrdDefNote1] [varchar](40) NULL,
[SOrdDefNote2] [varchar](40) NULL,
[SOrdDefNote3] [varchar](40) NULL,
[SOrdDefNote4] [varchar](40) NULL,
[SOrdDefNote5] [varchar](40) NULL,
[SOrdDefNote6] [varchar](40) NULL,
[SOrdDefNote7] [varchar](40) NULL,
[SOrdDefNote8] [varchar](40) NULL,
[SOrdDefNote9] [varchar](40) NULL,
[SOrdDefNote10] [varchar](40) NULL,
[SOrdDefNote11] [varchar](40) NULL,
[SOrdDefNote12] [varchar](40) NULL,
[SOrdDefNote13] [varchar](40) NULL,
[SOrdDefNote14] [varchar](40) NULL,
[SOrdDefNote15] [varchar](40) NULL,
[SOrdDefNote16] [varchar](40) NULL,
[SOrdDefNote17] [varchar](40) NULL,
[SOrdDefNote18] [varchar](40) NULL,
[SOrdDefNote19] [varchar](40) NULL,
[SOrdDefNote20] [varchar](40) NULL,
[SOrdFlag1] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag2] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag3] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag4] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag5] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag6] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag7] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag8] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag9] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag10] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag11] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag12] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag13] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag14] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag15] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag16] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag17] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag18] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag19] [bit] NOT NULL DEFAULT ((0)),
[SOrdFlag20] [bit] NOT NULL DEFAULT ((0)),
[SubStatus] [varchar](20) NULL,
[SubValid] [char](1) NOT NULL DEFAULT ('Y'),
[SLbName] [varchar](20) NULL,
[SLbInt] [int] NULL,
[PRTHX] [varchar](20) NULL,
[KGQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[KGPrice] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_JYOrderCon_Sub] PRIMARY KEY CLUSTERED
(
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_OrderNo_YSK_View] 脚本日期: 05/06/2014 11:52:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from YF_Money_CR where CRType='应收款登记'
CREATE Procedure [dbo].[P_OrderNo_YSK_View]
@Begdate varchar(20),
@Enddate varchar(20),
@WSql varchar(200)
AS
DECLARE @MSql varchar(6000)
Set @MSql='
select A.MainId,A.OrderNo,A.MPRTCodeName,
CRTime=(select Top 1 CRTime from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'') ,
--HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRFlag=''应收收'') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记''),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'' and isnull(YC.BZType,'''')=''USD'') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款发票登记'') ,
YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'' and isnull(YC.BZType,'''')<>''USD'') ,
SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记''
and isnull(YFName,'''')<>''退税'' and isnull(YC.BZType,'''')=''USD'') ,
HuiLv=(select sum(Money*HuiLv)*1.00/sum(Money) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记''
and isnull(YFName,'''')<>''退税'' and isnull(YC.BZType,'''')=''USD'') ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记''
and isnull(YFName,'''')<>''退税'' and isnull(YC.BZType,'''')<>''USD'') ,
YSYBYMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'' and isnull(YC.BZType,'''')=''USD'')
-(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记''
and isnull(YFName,'''')<>''退税'' and isnull(YC.BZType,'''')=''USD''),
YSBBYMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''应收款登记'' and isnull(YC.BZType,'''')<>''USD'')
-(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记''
and isnull(YFName,'''')<>''退税'' and isnull(YC.BZType,'''')<>''USD''),
SKTSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''收款登记'' and isnull(YFName,'''')=''退税'') ,
FKFS=(select Top 1 PayMent from JYOrderCon_Main C where C.ConNo=A.OrderNo) ,
A.Filler ,
A.OrdDefNote3,
A.OrdDefNote4
from JYOrder_Main A
where exists(select * from YF_Money_CR B where B.MainId=A.MainId and B.CRFlag=''应收收''
and B.CRTime>='''+@begdate+''' and B.CRTime<'''+@enddate+''')
'
Set @MSql=@MSql+@WSql
exec(@MSql)
--select * from YF_Money_CR where CRFlag='应收收'
GO
/****** 对象: Table [dbo].[WFB_MJJY] 脚本日期: 05/06/2014 12:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WFB_MJJY](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[MJID] [char](20) NOT NULL,
[MJMaoZ] [decimal](18, 2) NOT NULL CONSTRAINT [DF__WFB_MJJY__MJMaoZ__321755AF] DEFAULT ((0)),
[MJLen] [decimal](18, 2) NOT NULL CONSTRAINT [DF__WFB_MJJY__MJLen__330B79E8] DEFAULT ((0)),
[MJFK] [decimal](18, 2) NOT NULL CONSTRAINT [DF__WFB_MJJY__MJFK__33FF9E21] DEFAULT ((0)),
[MJSJKZ] [decimal](18, 2) NOT NULL CONSTRAINT [DF__WFB_MJJY__MJSJKZ__34F3C25A] DEFAULT ((0)),
[MJBanZu] [varchar](20) NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL CONSTRAINT [DF__WFB_MJJY__Fillti__35E7E693] DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Valie] [varchar](5) NOT NULL CONSTRAINT [DF__WFB_MJJY__Valie__36DC0ACC] DEFAULT ('Y'),
[PrtAgnFlag] [bit] NOT NULL CONSTRAINT [DF__WFB_MJJY__PrtAgn__6D381B7D] DEFAULT ((0)),
[PrtAgnDate] [datetime] NULL,
[JTType] [varchar](20) NULL,
[PrtAgnPerson] [varchar](20) NULL,
[MJType] [varchar](20) NULL,
[ZDMJID] [varchar](20) NULL,
[HJMJID] [varchar](20) NULL,
[MJTypeOther] [varchar](20) NULL,
[APID] [varchar](20) NULL,
[MJQty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MJQty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MJQty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MJQty4] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MJQty5] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MJXH] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[WFB_MJJY] ADD [MJStr1] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [MJStr2] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [MJStr3] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [MJStr4] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [MJStr5] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [LYFlag] [bit] NOT NULL DEFAULT ((0))
ALTER TABLE [dbo].[WFB_MJJY] ADD [HXFlag] [bit] NOT NULL DEFAULT ((0))
SET ANSI_PADDING ON
ALTER TABLE [dbo].[WFB_MJJY] ADD [HXID] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD [PanDing] [varchar](20) NULL
ALTER TABLE [dbo].[WFB_MJJY] ADD CONSTRAINT [PK_WFB_MJJY] PRIMARY KEY CLUSTERED
(
[MJID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[JYOrder_Main] 脚本日期: 05/06/2014 12:00:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[JYOrder_Main](
[MainId] [char](20) NOT NULL,
[OrderNo] [char](20) NOT NULL,
[JGFactory] [varchar](20) NULL,
[JGFactoryName] [varchar](40) NULL,
[ConNo] [char](20) NULL,
[CustomerNo] [char](20) NULL,
[CustomerNoName] [varchar](40) NULL,
[DlyDate] [datetime] NULL,
[DlyPlace] [varchar](100) NULL,
[OrdDate] [datetime] NULL,
[OrdUpDate] [datetime] NULL,
[MPRTCode] [varchar](40) NULL,
[MPRTCodeName] [varchar](50) NULL,
[MPRTSpec] [varchar](40) NULL,
[MPRTCF] [varchar](40) NULL,
[MPRTMF] [varchar](40) NULL,
[MPRTKZ] [varchar](40) NULL,
[MPRTSL] [varchar](40) NULL,
[OrdDefDate1] [datetime] NULL,
[OrdDefDate2] [datetime] NULL,
[OrdDefDate3] [datetime] NULL,
[OrdDefDate4] [datetime] NULL,
[OrdDefDate5] [datetime] NULL,
[Note] [varchar](500) NULL,
[OrdDefStr1] [varchar](40) NULL,
[OrdDefStr2] [varchar](40) NULL,
[OrdDefStr3] [varchar](40) NULL,
[OrdDefStr4] [varchar](40) NULL,
[OrdDefStr5] [varchar](40) NULL,
[OrdDefStr6] [varchar](40) NULL,
[OrdDefStr7] [varchar](40) NULL,
[OrdDefStr8] [varchar](40) NULL,
[OrdDefStr9] [varchar](40) NULL,
[OrdDefStr10] [varchar](40) NULL,
[OrdDefStr11] [varchar](40) NULL,
[OrdDefStr12] [varchar](40) NULL,
[OrdDefStr13] [varchar](40) NULL,
[OrdDefStr14] [varchar](40) NULL,
[OrdDefStr15] [varchar](40) NULL,
[OrdDefNote1] [varchar](40) NULL,
[OrdDefNote2] [varchar](40) NULL,
[OrdDefNote3] [varchar](40) NULL,
[OrdDefNote4] [varchar](40) NULL,
[OrdDefNote5] [varchar](40) NULL,
[OrdDefNote6] [varchar](40) NULL,
[OrdDefNote7] [varchar](40) NULL,
[OrdDefNote8] [varchar](40) NULL,
[OrdDefNote9] [varchar](40) NULL,
[OrdDefNote10] [varchar](40) NULL,
[OrdDefNote11] [varchar](40) NULL,
[OrdDefNote12] [varchar](40) NULL,
[OrdDefNote13] [varchar](40) NULL,
[OrdDefNote14] [varchar](40) NULL,
[OrdDefNote15] [varchar](40) NULL,
[OrdPerson1] [varchar](20) NULL,
[OrdPerson2] [varchar](20) NULL,
[OrdPerson3] [varchar](20) NULL,
[OrdPerson4] [varchar](20) NULL,
[OrdPerson5] [varchar](20) NULL,
[OrdPerson6] [varchar](20) NULL,
[OrdPerson7] [varchar](20) NULL,
[OrdPerson8] [varchar](20) NULL,
[OrdPerson9] [varchar](20) NULL,
[OrdPerson10] [varchar](20) NULL,
[OrdFlag1] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag2] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag3] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag4] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag5] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag6] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag7] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag8] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag9] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag10] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag11] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag12] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag13] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag14] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag15] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag16] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag17] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag18] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag19] [bit] NOT NULL DEFAULT ((0)),
[OrdFlag20] [bit] NOT NULL DEFAULT ((0)),
[Chker] [varchar](20) NULL,
[ChkStatus] [varchar](20) NULL,
[ChkNote] [varchar](200) NULL,
[Chktime] [datetime] NULL,
[Filler] [varchar](20) NULL,
[Filltime] [datetime] NOT NULL DEFAULT (getdate()),
[Editer] [varchar](20) NULL,
[Edittime] [datetime] NULL,
[Status] [varchar](20) NULL,
[Valid] [char](1) NULL DEFAULT ('Y'),
[LbName] [varchar](20) NULL,
[LbInt] [int] NULL,
[YSYBMoneyFZ] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[YSYBFlag] [bit] NOT NULL DEFAULT ((0)),
[YCLFactory] [varchar](50) NULL,
[PBFactory] [varchar](50) NULL,
[RanFactory] [varchar](50) NULL,
[HZLFactory] [varchar](50) NULL,
[MPRTColorLaoDu] [varchar](50) NULL,
[MPRTNiuDu] [varchar](50) NULL,
[MPRTPH] [varchar](50) NULL,
[MPRTZHName] [varchar](50) NULL,
[MPRTBZNote] [varchar](100) NULL,
[MPRTKaiJian] [varchar](50) NULL,
[MPRTChuanY] [varchar](50) NULL,
[MPRTYaoFeng] [varchar](50) NULL,
[MPRTJiBenNote] [varchar](100) NULL,
[MPRTTeBieNote] [varchar](100) NULL,
[MPRTQtyNote] [varchar](50) NULL,
[MPRTColorLaoDu1] [varchar](40) NULL,
[MPRTColorLaoDu2] [varchar](40) NULL,
[MPRTColorLaoDu3] [varchar](40) NULL,
[MPRTColorLaoDu4] [varchar](40) NULL,
[MPRTDuiSeGY] [varchar](40) NULL,
[MPRTKQiQiu] [varchar](40) NULL,
[MPRTDingPoLv] [varchar](40) NULL,
[MPRTZuRanXing] [varchar](40) NULL,
[MPRTKuanNO] [varchar](40) NULL,
[LBNameNote] [varchar](200) NULL,
[MaiTouNote] [varchar](200) NULL,
[MPRTTangJin] [varchar](40) NULL,
[MPRTSCTeBieNote] [varchar](100) NULL,
[PiZhong] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[SYRName] [varchar](60) NULL,
CONSTRAINT [PK_JYOrder_Main] PRIMARY KEY CLUSTERED
(
[MainId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[ContractSX_Sub] 脚本日期: 05/06/2014 11:58:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractSX_Sub](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[ConNo] [char](20) NULL,
[XHNo] [char](20) NULL,
[C_Code] [char](20) NULL,
[C_CodeName] [varchar](50) NULL,
[C_Spec] [varchar](40) NULL,
[C_Color] [varchar](20) NULL,
[MF] [varchar](20) NULL,
[KZ] [varchar](20) NULL,
[C_Qty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[C_Unit] [varchar](20) NULL,
[Price] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[PriceUnit] [varchar](20) NULL,
[MFUnit] [varchar](20) NULL,
[KZUnit] [varchar](20) NULL,
[Qty1] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL DEFAULT ((0)),
[MainUnit] [varchar](20) NULL,
[C_Status] [varchar](20) NULL,
[C_Valid] [char](1) NOT NULL DEFAULT ('Y'),
[C_Note] [varchar](200) NULL,
[MFQty] [decimal](18, 2) NULL,
[KZQty] [decimal](18, 2) NULL,
[MQty] [decimal](18, 2) NOT NULL DEFAULT ((0)),
CONSTRAINT [PK_ContractSX_Sub] PRIMARY KEY CLUSTERED
(
[MainId] ASC,
[SubId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[P_HJG_View] 脚本日期: 05/06/2014 11:52:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_HJG_View '2012-10-01','2014-10-1',1,'001'
CREATE Procedure [dbo].[P_HJG_View]
@begdate varchar(20),
@enddate varchar(20),
@Pstate int,
@ConNo varchar(20)
AS
DECLARE @MSQL Varchar(8000)
Set @MSQL='
select isnull(FactoryNoName20,FactoryNoName10) FactoryNoName,
isnull(ConNO20,ConNO10) ConNO,
AA.* from(
select A.*,isnull(C.C_CodeName,D.PBName) C_CodeName,C.MainId,D.DHUnit,C.SubId,ConNo20=B.ConNo
,isnull(C.C_Spec,D.PBSpec) C_Spec,isnull(C.MFQty,D.PBMF) MFQty,isnull(C.KZQty,D.PBKZ) KZQty
,ConNO10=(select ConNo from ContractSX_Main SM where SM.MainId=D.SXMainId )
,FactoryNoName10=(select FirstName from ContractSX_Cloth_DH SD where SD.DHID=D.SXDHID)
,JM.OrderNo,JS.PRTColor,FactoryNoName20=B.FactoryNoName
,Price=(select Top 1 JGPrice from Contract_Cloth_LLMX AAA where AAA.DHID=A.DHID )
,KCPS=TPPS-HCPS,KCQty=BCPQty-HCQty
,APFlag=(select Count(*) from JYOrder_Sub_AnPai JAP where JAP.LLID=A.LLID)
from Contract_Cloth_LL A
inner join Contract_Cloth_DH D on A.DHID=D.DHID
left join Contract_Main B on D.Mainid=B.Mainid
left join Contract_Sub C on D.Subid=C.Subid
inner join JYOrder_Sub JS on A.OrdSubId=JS.SubId
inner join JYOrder_Main JM on JS.Mainid=JM.MainId
where exists( select * from Contract_Cloth_LLMX LLM where LLM.DHID=isnull(A.DHIdHelp,A.DHID))) AA'
if @Pstate=1
begin
Set @MSQL=@MSQL+' where exists(select * from Contract_Cloth_LLMX CLL where CLL.DHID=AA.DHID
and CLL.TPDate>='''+@begdate+''' and CLL.TPDate<'''+@enddate+''')'
end
if @Pstate=2
begin
Set @MSQL=@MSQL+' where isnull(ConNO20,ConNO10) like '''+'%'+@ConNo+'%'+''''
end
exec(@MSQL)
GO
/****** 对象: Table [dbo].[ContractSX_Cloth_LLMx] 脚本日期: 05/06/2014 11:57:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ContractSX_Cloth_LLMx](
[MainId] [char](20) NOT NULL,
[SubId] [char](20) NOT NULL,
[DHId] [char](20) NOT NULL,
[MXId] [char](20) NOT NULL,
[OrdSubId] [char](20) NULL,
[TPQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__TPQty__60C757A0] DEFAULT ((0)),
[SXQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__SXQty__61BB7BD9] DEFAULT ((0)),
[SXMQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__SXMQt__62AFA012] DEFAULT ((0)),
[SXPS] [decimal](18, 0) NOT NULL CONSTRAINT [DF__ContractSX__SXPS__63A3C44B] DEFAULT ((0)),
[ZSXS] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__ZSXS__6497E884] DEFAULT ((0)),
[SXUnit] [varchar](20) NULL,
[PBName] [varchar](40) NULL,
[PBSpec] [varchar](40) NULL,
[PBMF] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__PBMF__658C0CBD] DEFAULT ((0)),
[PBKZ] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__PBKZ__668030F6] DEFAULT ((0)),
[TPUnit] [varchar](20) NULL,
[TPDate] [datetime] NULL,
[TPNote] [varchar](200) NULL,
[TPPerson] [varchar](20) NULL,
[ToValid] [char](1) NOT NULL CONSTRAINT [DF__ContractS__ToVal__6774552F] DEFAULT ('Y'),
[Qty1] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty1__68687968] DEFAULT ((0)),
[Qty2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty2__695C9DA1] DEFAULT ((0)),
[Qty3] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractSX__Qty3__6A50C1DA] DEFAULT ((0)),
[TPPS] [decimal](18, 0) NOT NULL CONSTRAINT [DF__ContractSX__TPPS__6B44E613] DEFAULT ((0)),
[HCID] [varchar](20) NULL,
[HCQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__HCQty__6C390A4C] DEFAULT ((0)),
[HCPS] [decimal](18, 0) NOT NULL CONSTRAINT [DF__ContractSX__HCPS__6D2D2E85] DEFAULT ((0)),
[TPMQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__TPMQt__6E2152BE] DEFAULT ((0)),
[MQty2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__MQty2__6F1576F7] DEFAULT ((0)),
[TPYZQty] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__TPYZQ__70099B30] DEFAULT ((0)),
[YZQty2] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__YZQty__70FDBF69] DEFAULT ((0)),
[ToNo] [varchar](40) NULL,
[ToName] [varchar](40) NULL,
[JGPrice] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__JGPri__71F1E3A2] DEFAULT ((0)),
[GangFee] [decimal](18, 2) NOT NULL CONSTRAINT [DF__ContractS__GangF__72E607DB] DEFAULT ((0)),
[HCFlag] [bit] NOT NULL CONSTRAINT [DF__ContractS__HCFla__73DA2C14] DEFAULT ((0)),
CONSTRAINT [PK_ContractSX_Cloth_LLMx] PRIMARY KEY CLUSTERED
(
[MXId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: StoredProcedure [dbo].[ClothContractSX_QryList] 脚本日期: 05/06/2014 11:52:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from JYOrder_Main
Create Procedure [dbo].[ClothContractSX_QryList]
@MainId varchar(20),
@WSql varchar(200)
AS
DECLARE @MSql varchar(1000)
Set @MSql='select OM.*,OS.*,
OM.ConNo ConNoM,OS.C_CodeName C_CodeNameM,
Money=Isnull(OS.C_Qty,0)*isnull(OS.Price,0),
CPQty=(select sum(PRTOrderQty) from JYOrder_Sub A inner join JYOrder_Main B on
A.MainId=B.MainId where B.OrderNo=OM.ConNo),
CPUnit=(select Top 1 OrderUnit from JYOrder_Sub A inner join JYOrder_Main B on
A.MainId=B.MainId where B.OrderNo=OM.ConNo),
CPDate=(select Top 1 DlyDate from JYOrder_Main B where B.OrderNo=OM.ConNo),
YWY=(select Top 1 Filler from JYOrder_Main B where B.OrderNo=OM.ConNo)
from ContractSX_Main OM
Left join ContractSX_Sub OS on OM.MainId=OS.MainId
where 1=1 '
IF RTRim(@MainId)<>''
Begin
Set @MSql=@MSql+' and OM.MainId='''+RTRIM(@MainId)+''''
End
Set @MSql=@MSql+@WSql
Exec(@MSql)
GO
/****** 对象: StoredProcedure [dbo].[P_Select_CP_YDang] 脚本日期: 05/06/2014 11:52:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Exec P_Select_CP_YDang '',0,''
CREATE procedure [dbo].[P_Select_CP_YDang]
@Code char(20), -----编号
@PState int, ----0：新增，1：修改
@CYType varchar(40)
AS
DECLARE @Tmsql VARCHAR(8000)
DECLARE @fsj VARCHAR(200)
DECLARE @CtrlStr VARCHAR(200)
DECLARE @Level int
DECLARE @FCode char(20)
create table #Tmp1(code char(20))
create Table #Tmp2(code char(20))
create Table #Tmp3(code char(20))
select @Level=CPLevel from CP_Type where CPID=@CYType
Set @FCode=@CYType
insert into #Tmp1 values(@FCode)
insert into #Tmp2 values(@FCode)
while @Level<=(select Max(CPLevel) from CP_Type)
begin
insert into #Tmp1(code)
select CPID from CP_Type
where CPLevel=(@Level+1) and exists (select code from #Tmp2 where code=CP_Type.CPParent )
insert into #Tmp3(code)
select CPID from CP_Type
where CPLevel=@Level+1 and exists(select code from #Tmp2 where code=CP_Type.CPParent )
delete #Tmp2
insert into #Tmp2 select * from #Tmp3
Delete #Tmp3
Set @Level=@Level+1
end
Set @Tmsql= 'select A.*,B.CPName from CP_YDang A inner join CP_Type B On A.CYType=B.CPID
where Exists(select code from #Tmp1 where code=A.CYType)'
if @PState=1 set @fsj=' and A.CYID='''+rtrim(@Code)+''''
exec(@Tmsql+@fsj+@CtrlStr+'order by A.CYName ')
Drop table #Tmp1
Drop table #Tmp2
Drop table #Tmp3
GO
/****** 对象: StoredProcedure [dbo].[P_Select_CP_YDang_MaxNo] 脚本日期: 05/06/2014 11:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Exec P_Select_CP_YDang '',0,''
CREATE procedure [dbo].[P_Select_CP_YDang_MaxNo]
@CYType varchar(40)
AS
DECLARE @Tmsql VARCHAR(8000)
DECLARE @fsj VARCHAR(200)
DECLARE @CtrlStr VARCHAR(200)
DECLARE @Level int
DECLARE @FCode char(20)
create table #Tmp1(code char(20))
create Table #Tmp2(code char(20))
create Table #Tmp3(code char(20))
select @Level=CPLevel from CP_Type where CPID=@CYType
Set @FCode=@CYType
insert into #Tmp1 values(@FCode)
insert into #Tmp2 values(@FCode)
while @Level<=(select Max(CPLevel) from CP_Type)
begin
insert into #Tmp1(code)
select CPID from CP_Type
where CPLevel=(@Level+1) and exists (select code from #Tmp2 where code=CP_Type.CPParent )
insert into #Tmp3(code)
select CPID from CP_Type
where CPLevel=@Level+1 and exists(select code from #Tmp2 where code=CP_Type.CPParent )
delete #Tmp2
insert into #Tmp2 select * from #Tmp3
Delete #Tmp3
Set @Level=@Level+1
end
Set @Tmsql= 'select CYNO AA from CP_YDang A
where Cast(CYID as int)=
(select isnull(Max(Cast(CYID as int)),0) from CP_YDang AA where exists (select code from #Tmp1 where code=AA.CYType))
'
exec(@Tmsql+@fsj+@CtrlStr)
Drop table #Tmp1
Drop table #Tmp2
Drop table #Tmp3
GO
/****** 对象: StoredProcedure [dbo].[P_View_YFK] 脚本日期: 05/06/2014 11:53:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[P_View_YFK]
@begdate varchar(20),
@enddate varchar(20),
@PState int,
@OrderNo varchar(20)
AS
if @PState=1
begin
select C.OrderNo
,PBName=(select Top 1 C_CodeName from Contract_Sub_MX CSM
inner join Contract_Sub CS on CSM.SubId=CS.SubId
where CSM.MXId=A.YFTypeID and A.YFName='坯布费用' )
,A.*
,PRTColor=(select JS.PRTColor from JYOrder_Sub_AnPai JSA
inner join JYOrder_Sub JS on JSA.SubId=JS.SubId where JSA.APID=A.YFTypeID )
,CPZHName=(select Top 1 CPZHName from JYOrderCon_Main JM where JM.ConNo=C.ConNo)
,CPCFBi=(select Top 1 CPCFBi from JYOrderCon_Main JM where JM.ConNo=C.ConNo)
,TJName=(select Top 1 Note from KH_Zdy KZ
where KZ.ZdyName=A.FactoryName and KZ.Type='Factory' and isnull(KZ.Note,'')<>'')
from YF_Money_CR A
inner join YF_Money_KC B on A.CRID=B.CRID
left join JYOrder_Main C on A.MainId=C.MainId
where A.CRTime>=@begdate and A.CRTime<@enddate and A.CRType='应付款登记'
end
if @PState=2
begin
select C.OrderNo
,PBName=(select Top 1 C_CodeName from Contract_Sub_MX CSM
inner join Contract_Sub CS on CSM.SubId=CS.SubId
where CSM.MXId=A.YFTypeID and A.YFName='坯布费用' )
,A.*
,PRTColor=(select JS.PRTColor from JYOrder_Sub_AnPai JSA
inner join JYOrder_Sub JS on JSA.SubId=JS.SubId where JSA.APID=A.YFTypeID )
,CPZHName=(select Top 1 CPZHName from JYOrderCon_Main JM where JM.ConNo=C.ConNo)
,CPCFBi=(select Top 1 CPCFBi from JYOrderCon_Main JM where JM.ConNo=C.ConNo)
,TJName=(select Top 1 Note from KH_Zdy KZ
where KZ.ZdyName=A.FactoryName and KZ.Type='Factory' and isnull(KZ.Note,'')<>'')
from YF_Money_CR A
inner join YF_Money_KC B on A.CRID=B.CRID
left join JYOrder_Main C on A.MainId=C.MainId
where C.OrderNo like @OrderNo and A.CRType='应付款登记'
end
GO
/****** 对象: StoredProcedure [dbo].[Get_SY_MaxBH] 脚本日期: 05/06/2014 11:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--功能：获得最大编号(无日期)
--著者：李
--exec Get_SY_MaxBH 'LJ','s1',8,'1'
create PROCEDURE [dbo].[Get_SY_MaxBH]
@BHstr varchar(2),
@BHType varchar(20),
@Len int =3,
@status varchar(10)='0'
AS
begin
if @status='0'
begin
IF EXISTS (SELECT * FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='0')
BEGIN
UPDATE SY_MaxBH SET BHNo=BHNo+1 WHERE BHstr=@BHstr AND BHType=@BHType And Type='0'
SELECT MaxBH = rtrim(BHstr) + RIGHT(cast('1'+replicate('0',@Len) as int) + BHNo, @Len) FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='0'
END
ELSE
BEGIN
INSERT SY_MaxBH (BHstr, BHType, BHNo,Type) VALUES (@BHstr, @BHType,1,0)
SELECT MaxBH = rtrim(BHstr) + RIGHT(cast('1'+replicate('0',@Len) as int) + BHNo, @Len) FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='0'
END
end
else
begin
DECLARE @Date DATETIME
DECLARE @Datestr CHAR(6)
SET @Date = GETDATE()
SET @Datestr = RIGHT(YEAR(@Date) * 10000 + MONTH(@Date) * 100 + DAY(@Date), 6)
IF EXISTS (SELECT * FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType AND BHDate=@Datestr And Type='1')
BEGIN
UPDATE SY_MaxBH SET BHNo=BHNo+1 WHERE BHstr=@BHstr AND BHType=@BHType AND BHDate=@Datestr And Type='1'
SELECT MaxBH = rtrim(BHstr) + BHDate + RIGHT(cast('1'+replicate('0',@Len) as int) + BHNo, @Len) FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='1'
END
ELSE IF EXISTS (SELECT * FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='1')
BEGIN
UPDATE SY_MaxBH SET BHDate=@Datestr, BHNo=1 WHERE BHstr=@BHstr AND BHType=@BHType And Type='1'
SELECT MaxBH = rtrim(BHstr) + BHDate + RIGHT(cast('1'+replicate('0',@Len) as int) + BHNo, @Len) FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='1'
END
ELSE
BEGIN
INSERT SY_MaxBH (BHstr, BHType,BHDate, BHNo,type) VALUES (@BHstr, @BHType,@Datestr, 1,1)
SELECT MaxBH = rtrim(BHstr) + BHDate + RIGHT(cast('1'+replicate('0',@Len) as int) + BHNo, @Len) FROM SY_MaxBH WHERE BHstr=@BHstr AND BHType=@BHType And Type='1'
END
end
end
GO
/****** 对象: StoredProcedure [dbo].[P_View_DJClothKC] 脚本日期: 05/06/2014 11:53:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_View_DJClothKC
CREATE Procedure [dbo].[P_View_DJClothKC]
AS
select A.MainId,A.SubId,A.APID,A.GangNo,A.AOrddefstr1,A.AOrdQty1,A.AOrdQty2,A.AOrddefstr2,A.ADefDate1,
B.OrderNo,B.CustomerNoName,B.DlyDate,B.OrdDate,B.MPRTCode,B.MPRTCodeName,B.MPRTSpec,
B.MPRTMF,B.MPRTKZ,B.OrdPerson1,B.OrdPerson2,
C.PRTColor,C.PRTHX
from JYOrder_Sub_AnPai A
inner join JYOrder_Main B on A.MainId=B.MainId
inner join JYOrder_Sub C on A.SubId=C.SubId
where not exists(select * from WFB_MJJY D where D.APID=A.APID)
GO
/****** 对象: StoredProcedure [dbo].[P_YFFK_List] 脚本日期: 05/06/2014 11:53:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[P_YFFK_List]
@begdate varchar(20),
@enddate varchar(20)
AS
SELECT AA.*,WKP=YFKMoneyHZ-KPHZ ,BQMoney=isnull(YFMoney,0)-isnull(FKMoney,0),
BQWKP=isnull(YFMoney,0)-isnull(BQKP,0)-isnull(FKMoneyXJ,0)
FROM(
SELECT TJName,KCBBMoney=isnull(sum(KCBBMoney),0),KCKPMoney=isnull(sum(KCKPMoney),0),
SQMoney=isnull(sum(SQMoney),0),YFMoney=isnull(sum(YFMoney),0),
BQKP=isnull(sum(BQKP),0),KPHZ=isnull(sum(KPHZ),0),
FKMoney=isnull(sum(FKMoney),0),FKMoneyHZ=isnull(sum(FKMoneyHZ),0),
FKMoneyXJ=isnull(sum(FKMoneyXJ),0),YFKMoneyHZ=isnull(sum(YFKMoneyHZ),0),
KHZJM=dbo.getPinYin(TJName)
FROM(
select A.KCBBMoney,A.CRID,A.FactoryName,A.KCKPMoney,
SQMoney=(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID
and B.CRTime<@begdate),
YFMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
BQKP=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRFlag='应付付'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
KPHZ=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRFlag='应付付'),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
FKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记'
and isnull(B.PayMent,'')<>'现金'),
FKMoneyXJ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记' and B.PayMent='现金'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
YFKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应付款登记'
)
,TJName=(select Top 1 Note from KH_Zdy KZ where KZ.ZdyName=A.FactoryName and KZ.Type='Factory' and isnull(KZ.Note,'')<>'')
from YF_Money_KC A
where A.ZdyStr1='应付付')BB
Group by TJName
)AA
GO
/****** 对象: StoredProcedure [dbo].[P_YSSK_List] 脚本日期: 05/06/2014 11:53:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[P_YSSK_List]
@begdate varchar(20),
@enddate varchar(20)
AS
SELECT AA.*,SQMoney=BQMoney-YFMoney+FKMoney,SQMoneyYB=BQMoneyYB-YFMoneyYB+FKMoneyYB,WKP=FKMoneyHZ-KCKPMoney from(
select A.KCBBMoney,A.CRID,A.FactoryName,A.KCKPMoney,A.KCMoney,
BQMoney=isnull(A.KCBBMoney,0)-(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID
and B.CRTime>=@enddate and B.CRTime<getdate()+1),
BQMoneyYB=isnull(A.KCMoney,0)-(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID
and B.CRTime>=@enddate and B.CRTime<getdate()+1),
YFMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
YFMoneyYB=(select isnull(Sum(Money),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
BQKP=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRFlag='应收收'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
FKMoneyYB=(select isnull(Sum(Money),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
FKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应收款登记'
)
,KHZJM=dbo.getPinYin(A.FactoryName)
from YF_Money_KC A
where A.ZdyStr1='应收收')AA
--select * from YF_Money_CR
GO
/****** 对象: StoredProcedure [dbo].[P_YFFK_ListBAK] 脚本日期: 05/06/2014 11:53:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[P_YFFK_ListBAK]
@begdate varchar(20),
@enddate varchar(20)
AS
SELECT AA.*,WKP=YFKMoneyHZ-KPHZ ,BQMoney=isnull(YFMoney,0)-isnull(FKMoney,0),
BQWKP=isnull(YFMoney,0)-isnull(BQKP,0)-isnull(FKMoneyXJ,0)
FROM(
select A.KCBBMoney,A.CRID,A.FactoryName,A.KCKPMoney,
SQMoney=(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID
and B.CRTime<@begdate),
YFMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
BQKP=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRFlag='应付付'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
KPHZ=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRFlag='应付付'),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
FKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记'
and isnull(B.PayMent,'')<>'现金'),
FKMoneyXJ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='付款登记' and B.PayMent='现金'
and B.CRTime>=@Begdate and B.CRTime<@Enddate),
YFKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='应付款登记'
)
,KHZJM=dbo.getPinYin(A.FactoryName)
from YF_Money_KC A
where A.ZdyStr1='应付付')AA
GO
/****** 对象: UserDefinedFunction [dbo].[F_Get_Order_SubStr] 脚本日期: 05/06/2014 12:05:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[F_Get_Order_SubStr](@IdValue AS varchar(100),@FieldName varchar(20))
returns varchar(1000) as
begin
declare @a as varchar(1000)
set @a=''
declare @b as int
if RTRIM(@FieldName)='MJCDHZ'
Begin
select @a=@a+','+'<'+RTrim(CDName)+' '+Case when CDEnd>0 then '从'+RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'到'+RTRIM(Cast(CDEnd as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'长度:'+RTRIM(Cast(CDQty as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))+'>'
else RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))+'处>' end
from WFB_MJJY_CD A inner join WFB_MJJY B on A.MJID=B.MJID where A.MJID=@IdValue
select @b=0
End Else
if RTRIM(@FieldName)='MJCDHZSL'
Begin
select @a=@a+','+''+RTrim(CDName)+' '+Case when isnull(Cast(CDEnd as decimal(18,2)),0)>0 and isnull(Cast(CDBeg as decimal(18,2)),0)>0 and CDQty>0
then '从'+RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'到'+RTRIM(Cast(CDEnd as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'长度:'+RTRIM(Cast(CDQty as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
when (isnull(Cast(CDEnd as decimal(18,2)),0)=0 or isnull(Cast(CDBeg as decimal(18,2)),0)=0) and CDQty>0
then '长度:'+RTRIM(Cast(CDQty as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
when isnull(Cast(CDEnd as decimal(18,2)),0)=0 and isnull(Cast(CDBeg as decimal(18,2)),0)>0 then
RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))+'处'
else ' ' end
from WFB_MJJY_CD A inner join WFB_MJJY B on A.MJID=B.MJID where A.MJID=@IdValue
select @b=Count(*)
from WFB_MJJY_CD where MJID=@IdValue
End Else
if RTRIM(@FieldName)='TPColor'
Begin
select @a=@a+','+''+RTrim(PRTColor)
from Contract_Cloth_LLMX A inner join JYOrder_Sub B on A.OrdSubID=B.SubID where B.MainID=@IdValue group by A.OrdSubID,B.PRTColor
select @b=Count(*) from
(select OrdSubID
from Contract_Cloth_LLMX A inner join JYOrder_Sub B on A.OrdSubID=B.SubID where B.MainID=@IdValue group by A.OrdSubID,B.PRTColor) AA
End Else
if RTRIM(@FieldName)='OrdColor'
Begin
select @a=@a+','+''+RTrim(PRTColor)
from JYOrder_Sub B where B.MainID=@IdValue group by B.SubID,B.PRTColor
select @b=Count(*) from
(select SubID
from JYOrder_Sub B where B.MainID=@IdValue group by B.SubID,B.PRTColor) AA
End Else
if RTRIM(@FieldName)='HCColor'
Begin
select @a=@a+','+''+RTrim(PRTColor)
from JYOrder_Sub_AnPai B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue group by B.SubID,C.PRTColor
select @b=Count(*) from
(select B.SubID
from JYOrder_Sub_AnPai B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue group by B.SubID,C.PRTColor) AA
End Else
if RTRIM(@FieldName)='RKColor'
Begin
select @a=@a+','+''+RTrim(PRTColor)
from CK_BanCp_CR B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue and B.CRType='检验入库' group by B.SubID,C.PRTColor
select @b=Count(*) from
(select B.SubID
from CK_BanCp_CR B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue and B.CRType='检验入库' group by B.SubID,C.PRTColor) AA
End Else
if RTRIM(@FieldName)='CKColor'
Begin
select @a=@a+','+''+RTrim(PRTColor)
from CK_BanCp_CR B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue and B.CRType='正常出库' group by B.SubID,C.PRTColor
select @b=Count(*) from
(select B.SubID
from CK_BanCp_CR B inner join JYOrder_Sub C on B.SubId=C.SubId where C.MainID=@IdValue and B.CRType='正常出库' group by B.SubID,C.PRTColor) AA
End
Else
if RTRIM(@FieldName)='ORdPrice'
Begin
select @a=@a+','+''+RTrim(Cast(PRTPrice as varchar(20)))
from JYOrder_Sub B where B.MainID=@IdValue group by B.PRTPrice
select @b=Count(*) from
(select PRTPrice from JYOrder_Sub B where B.MainID=@IdValue group by B.PRTPrice) AA
End
if @b>1
begin
Set @a=Cast(@b as varchar(20))+'个<'+substring(@a,2,len(@a))+'>'
end else
begin
set @a=substring(@a,2,len(@a))
end
return @a
end
GO
/****** 对象: StoredProcedure [dbo].[P_YFFK_ListTT] 脚本日期: 05/06/2014 11:53:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [P_YFFK_ListTT] '2012-01-01','2013-10-10'
CREATE Procedure [dbo].[P_YFFK_ListTT]
@begdate varchar(20),
@enddate varchar(20)
AS
SELECT KCBBMoney=BQMoney,AA.* ,SQMoney=isnull(BQMoney,0)-isnull(YFMoney,0)+isnull(FKMoney,0),WKP=isnull(YFKMoneyHZ,0)-isnull(KPMoney,0)
from
(
select A.*,
BQMoney=isnull(A.KCBBMoney,0)-(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou
and B.CRTime>=@Enddate --and B.CRTime<getdate()+1
),
YFMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='应付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
BQKP=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRFlag='应付付'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='付款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
YFKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='应付款登记'
),
KHZJM=dbo.getPinYin(A.FactoryName)
from
(select CRID,FactoryName,ComTaiTou=isnull(ComTaiTou,''),KCBBMoney=isnull(sum(BBMoney*QtyFlag),0),KPMoney=isnull(sum(KPMoney),0)
from YF_Money_CR
where CRFlag='应付付'
Group by CRID,FactoryName,ComTaiTou
) A
)AA
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/****** 对象: StoredProcedure [dbo].[P_YSSK_ListTT] 脚本日期: 05/06/2014 11:53:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [P_YSSK_ListTT] '2012-01-01','2013-10-10'
CREATE Procedure [dbo].[P_YSSK_ListTT]
@begdate varchar(20),
@enddate varchar(20)
AS
SELECT KCBBMoney=BQMoney,KCMoney=BQMoneyYB,AA.*,SQMoney=isnull(BQMoney,0)-isnull(YFMoney,0)+isnull(FKMoney,0),
SQMoneyYB=isnull(BQMoneyYB,0)-isnull(YFMoneyYB,0)+isnull(FKMoneyYB,0),
WKP=isnull(FKMoneyHZ,0)-isnull(KCKPMoney,0) from(
select A.KCBBMoney,A.CRID,A.FactoryName,A.ComTaiTou,A.KCKPMoney,A.KCMoney,
BQMoney=isnull(A.KCBBMoney,0)-(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou
and B.CRTime>=@enddate --and B.CRTime<getdate()+1
),
BQMoneyYB=isnull(A.KCMoney,0)-(select isnull(Sum(BBMoney*QtyFlag),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou
and B.CRTime>=@enddate --and B.CRTime<getdate()+1
),
YFMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='应收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
YFMoneyYB=(select isnull(Sum(Money),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='应收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
BQKP=(select isnull(Sum(KPMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRFlag='应收收'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
FKMoneyYB=(select isnull(Sum(Money),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='收款登记'
and B.CRTime>=@Begdate and B.CRTime<@Enddate
),
FKMoneyHZ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and isnull(B.ComTaiTou,'')=A.ComTaiTou and B.CRType='应收款登记'
)
,KHZJM=dbo.getPinYin(A.FactoryName)
from (select CRID,FactoryName,ComTaiTou=isnull(ComTaiTou,''),KCBBMoney=isnull(sum(BBMoney*QtyFlag),0),KCMoney=isnull(sum(Money*QtyFlag),0),
KCKPMoney=isnull(sum(KPMoney),0)
from YF_Money_CR
where CRFlag='应收收'
Group by CRID,FactoryName,ComTaiTou
) A
)AA
--select * from YF_Money_CR
GO
/****** 对象: StoredProcedure [dbo].[P_View_DJBKC] 脚本日期: 05/06/2014 11:53:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_View_DJBKC
CREATE Procedure [dbo].[P_View_DJBKC]
AS
select A.MainId,A.SubId,isnull(sum(A.AOrdQty1),0) HCQty,isnull(Sum(A.AOrdQty2),0) HCPS
into #Temp1
from JYOrder_Sub_AnPai A
where not exists(select * from WFB_MJJY D where D.APID=A.APID)
group by MainId,SubId
select C.MainId,C.SubId,isnull(sum(A.HXQty),0) HXQty,isnull(Sum(A.HXPS),0) HXPS
into #Temp2
from Contract_Cloth_BefChkHX A
inner join Contract_Cloth_LL B on A.LLID=B.LLID
inner join JYOrder_Sub C on B.OrdSubId=C.SubId
where exists(select * from #Temp1 D where D.SubId=C.SubId )
group by C.MainId,C.SubId
select AA.*,BB.HXPS,BB.HXQty,KCQty=isnull(HCQty,0)-isnull(HXQty,0),KCPS=isnull(HCPS,0)-isnull(HXPS,0),
B.OrderNo,B.CustomerNoName,B.DlyDate,B.OrdDate,B.MPRTCode,B.MPRTCodeName,B.MPRTSpec,
B.MPRTMF,B.MPRTKZ,B.OrdPerson1,B.OrdPerson2,
C.PRTColor,C.PRTHX
from #Temp1 AA
left join #Temp2 BB on AA.Mainid=BB.MainId and AA.SubId=BB.SubId
inner join JYOrder_Main B on AA.MainId=B.MainId
inner join JYOrder_Sub C on AA.SubId=C.SubId
GO
/****** 对象: StoredProcedure [dbo].[P_View_DJBKCBak] 脚本日期: 05/06/2014 11:53:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec P_View_DJBKC
Create Procedure [dbo].[P_View_DJBKCBak]
AS
select A.MainId,A.SubId,A.GangNo,isnull(sum(A.AOrdQty1),0) HCQty,isnull(Sum(A.AOrdQty2),0) HCPS,A.AOrddefstr2
into #Temp1
from JYOrder_Sub_AnPai A
where not exists(select * from WFB_MJJY D where D.APID=A.APID)
group by MainId,SubId,GangNo,AOrddefstr2
select C.MainId,C.SubId,A.GangNo,isnull(sum(A.HXQty),0) HXQty,isnull(Sum(A.HXPS),0) HXPS
into #Temp2
from Contract_Cloth_BefChkHX A
inner join Contract_Cloth_LL B on A.LLID=B.LLID
inner join JYOrder_Sub C on B.OrdSubId=C.SubId
where exists(select * from #Temp1 D where D.SubId=C.SubId and D.GangNo=A.GangNo)
group by C.MainId,C.SubId,A.GangNo
select AA.*,BB.HXPS,BB.HXQty,KCQty=isnull(HCQty,0)-isnull(HXQty,0),KCPS=isnull(HCPS,0)-isnull(HXPS,0),
B.OrderNo,B.CustomerNoName,B.DlyDate,B.OrdDate,B.MPRTCode,B.MPRTCodeName,B.MPRTSpec,
B.MPRTMF,B.MPRTKZ,B.OrdPerson1,B.OrdPerson2,
C.PRTColor,C.PRTHX
from #Temp1 AA
left join #Temp2 BB on AA.Mainid=BB.MainId and AA.SubId=BB.SubId and AA.GangNo=BB.GangNo
inner join JYOrder_Main B on AA.MainId=B.MainId
inner join JYOrder_Sub C on AA.SubId=C.SubId
GO
/****** 对象: StoredProcedure [dbo].[P_OrderNo_YSK] 脚本日期: 05/06/2014 11:52:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[P_OrderNo_YSK]
@Begdate varchar(20),
@Enddate varchar(20)
AS
select A.MainId,A.OrderNo,A.MPRTCodeName,
CRTime=(select Top 1 CRTime from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
--HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
HuiLv=(select sum(Money*HuiLv)*1.00/sum(Money) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记'
and isnull(YFName,'')<>'退税' and isnull(YC.BZType,'')='USD') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRFlag='应收收') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记'),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记'
and isnull(BZType,'')='USD') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款发票登记') ,
YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记'
and isnull(YFName,'')<>'退税' and isnull(BZType,'')='USD') ,
PJMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记'
and isnull(YFName,'')<>'退税' and isnull(BZType,'')='USD') ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记') ,
YSYBYMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记'
and isnull(BZType,'')='USD')
-(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记'
and isnull(YFName,'')<>'退税' and isnull(BZType,'')='USD'),
SKTSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记' and isnull(YFName,'')='退税') ,
FKFS=(select Top 1 PayMent from JYOrderCon_Main C where C.ConNo=A.OrderNo) ,
A.Filler ,
A.OrdDefNote3,
A.OrdDefNote4
from JYOrder_Main A
where exists(select * from YF_Money_CR B where B.MainId=A.MainId and B.CRFlag='应收收'
and B.CRTime>=@begdate and B.CRTime<@enddate)
and not exists(select * from YF_Money_CR B where B.Mainid=A.Mainid and B.CRType='收款登记' and B.YFDefFlag1=1)
GO
/****** 对象: StoredProcedure [dbo].[P_OrderNo_YSKDL] 脚本日期: 05/06/2014 11:52:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [P_OrderNo_YSKDL] '2012-10-01','2013-10-10'
CREATE Procedure [dbo].[P_OrderNo_YSKDL]
@Begdate varchar(20),
@Enddate varchar(20)
AS
Select AAA.*,Case when YSYBFlag=0 then YSYBMoney else YSYBMoneyFZ end as YSYBMoneyY,
YSKPMoneyY=KPMoney-SKBBMoney
from (
select A.MainId,A.OrderNo,A.MPRTCodeName,
CRTime=(select Top 1 CRTime from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记'),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款发票登记') ,
--YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='应收款登记') ,
--SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记' ) ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='收款登记') ,
FKFS=(select Top 1 PayMent from JYOrderCon_Main C where C.ConNo=A.OrderNo) ,
A.Filler ,
A.OrdDefNote3,
A.OrdDefNote4,
A.YSYBFlag,
A.YSYBMoneyFZ
from JYOrder_Main A
where exists(select * from YF_Money_CR B where B.MainId=A.MainId and B.CRType='应收款登记'
and B.CRTime>=@begdate and B.CRTime<@enddate)
and exists (select MainId from YF_Money_CR B where B.Mainid=A.Mainid and B.CRType='收款登记' and B.YFDefFlag1=1)
)AAA
--select * from JYOrder_Main
--alter table JYOrder_Main Add YSYBFlag bit not null default(0)
GO
/****** 对象: UserDefinedFunction [dbo].[F_Get_Order_MFKZ] 脚本日期: 05/06/2014 12:05:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[F_Get_Order_MFKZ](@IdValue AS varchar(100),@MFKZName varchar(20))
returns decimal(18,2) as
begin
declare @a as decimal(18,2)
declare @b as Varchar(20)
if RTrim(@MFKZName)='MF'
begin
select @b=RTrim(MPRTMF) from JYOrder_Main where MainId=@IdValue
Set @b=Substring(@b,charindex('/',@b)+1,2)
Set @a=Cast(@b as decimal(18,2))*2.54
end
if RTrim(@MFKZName)='KZ'
begin
select @b=RTrim(MPRTKZ) from JYOrder_Main where MainId=@IdValue
Set @b=Substring(@b,1,charindex('g',@b)-1)
Set @a=Cast(@b as decimal(18,2))
end
return @a
end
GO
Reference in New Issue View Git Blame Copy Permalink