Group_01/D7DJyueya
5
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
D7DJyueya/打卷检验管理/doc/sql.txt

3703 lines
154 KiB
Plaintext
Raw Permalink Normal View History

~
2025-07-18 10:28:43 +08:00
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[CP_Type] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_Order] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 ''<27><>ĥ:''+RTrim(MPRTColorLaoDu1)+'' '' end as GM,
Case when isnull(MPRTColorLaoDu2,'''')='''' then '''' else ''ʪĥ:''+RTrim(MPRTColorLaoDu2)+'' '' end as SM,
Case when isnull(MPRTColorLaoDu3,'''')='''' then '''' else ''մɫ<D5B4>ζ<EFBFBD>:''+RTrim(MPRTColorLaoDu3)+'' '' end as SLD,
Case when isnull(MPRTColorLaoDu4,'''')='''' then '''' else ''ˮϴ<CBAE>ζ<EFBFBD>:''+RTrim(MPRTColorLaoDu4)+'' '' end as SXLD,
Case when isnull(MPRTSL,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD>:''+RTrim(MPRTSL)+'' '' end as SL,
Case when isnull(MPRTNiuDu,'''')='''' then '''' else ''Ť<><C5A4>:''+RTrim(MPRTNiuDu)+'' '' end as ND,
Case when isnull(MPRTPH,'''')='''' then '''' else ''PH:''+RTrim(MPRTPH)+'' '' end as PH,
Case when isnull(MPRTKQiQiu,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+RTrim(MPRTKQiQiu)+'' '' end as KQQ,
Case when isnull(MPRTDuiSeGY,'''')='''' then '''' else ''<27><>ɫ<EFBFBD><C9AB>Դ:''+RTrim(MPRTDuiSeGY)+'' '' end as DSGY,
Case when isnull(MPRTDingPoLv,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+RTrim(MPRTDingPoLv)+'' '' end as DPL,
Case when isnull(MPRTZuRanXing,'''')='''' then '''' else ''<27><>ȼ<EFBFBD><C8BC>:''+RTrim(MPRTZuRanXing)+'' '' end as ZRX,
Case when isnull(MPRTQtyNote,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[HB] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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](
[<5B><><EFBFBD><EFBFBD>] [float] NULL,
[<5B><>Ʒ<EFBFBD><C6B7><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[Ӣ<><D3A2><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B>ɷ<EFBFBD>] [nvarchar](255) NULL,
[Ӣ<>ijɷ<C4B3>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B>ŷ<EFBFBD>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B>۸<EFBFBD>] [float] NULL,
[<5B><><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD>Ʒ<EFBFBD><C6B7>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B><>Ӧ<EFBFBD><D3A6>] [nvarchar](255) NULL,
[<5B><>ɫ] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [nvarchar](255) NULL,
[<5B><>ע] [nvarchar](255) NULL,
[<5B><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>] [datetime] NULL,
[CYID] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Sub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[WFB_MJJY_CD] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[YF_Money_CRID] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Sub_Mx] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[CP_YDang] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractHZ_Main] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractHZ_Sub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Cloth_LL] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Sub_Mxto] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Main] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_JYOrder_HZFX] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>2 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>, 3 <20><><EFBFBD><EFBFBD>,4 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,5<><35><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,6<><36><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>ѯ
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
--------------------------------------------------------------------------<2D><><EFBFBD>±<EFBFBD><C2B1>ṹ
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
--------------------------------------------------------------------------<2D><><EFBFBD>±<EFBFBD><C2B1>ṹ
--------------------------------------------------------------------------<2D><><EFBFBD>´õ<C2B4><C3B5><EFBFBD><EFBFBD><EFBFBD>
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
--------------------------------------------------------------------------<2D><><EFBFBD>´õ<C2B4><C3B5><EFBFBD><EFBFBD><EFBFBD>
--------------------------------------------------------------------------<2D><>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD>
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=''<27><>Ʒ''),
CPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''<27><>Ʒ''),
LYQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''<27><><EFBFBD><EFBFBD>''),
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=''<27><>Ʒ'' group by WM.SubID ),
CPPS=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''<27><>Ʒ'' group by WM.SubID ),
LYPS=(select count(*) from WFB_MJJY WM where WM.SubID=A.ORDID and WM.MJType=''<27><><EFBFBD><EFBFBD>'' 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=''<27><>Ʒ''),
CPQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''<27><>Ʒ''),
LYQty=(select Cast(sum(MJLen) as decimal(18,2)) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''<27><><EFBFBD><EFBFBD>''),
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=''<27><>Ʒ'' group by WM.APID ),
CPPS=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''<27><>Ʒ'' group by WM.APID ),
LYPS=(select count(*) from WFB_MJJY WM where WM.APID=A.ORDID and WM.MJType=''<27><><EFBFBD><EFBFBD>'' 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
--------------------------------------------------------------------------<2D><>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD>
Drop table #Temp1
Drop table #Temp2
--select Top 100 * from JYOrder_Main
/****** <20><><EFBFBD><EFBFBD>: UserDefinedFunction [dbo].[F_Get_Order_SubStr] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 12/24/2012 14:36:05 ******/
SET ANSI_NULLS ON
GO
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Cloth_DH] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_MaxBH] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[XD_File] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_SET] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Cloth_DH] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Cloth_BefChkHX] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Cloth_LLMx] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[CK_BanCP_KC] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_ClothHZ] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Main] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[ClothContract_QryList] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[JYOrderCon_Main] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_ModuleMain] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_ModuleSub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_Purview] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_OrderSub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 ''<27><>ĥ:''+RTrim(MPRTColorLaoDu1)+'' '' end as GM,
Case when isnull(MPRTColorLaoDu2,'''')='''' then '''' else ''ʪĥ:''+RTrim(MPRTColorLaoDu2)+'' '' end as SM,
Case when isnull(MPRTColorLaoDu3,'''')='''' then '''' else ''մɫ<D5B4>ζ<EFBFBD>:''+RTrim(MPRTColorLaoDu3)+'' '' end as SLD,
Case when isnull(MPRTColorLaoDu4,'''')='''' then '''' else ''ˮϴ<CBAE>ζ<EFBFBD>:''+RTrim(MPRTColorLaoDu4)+'' '' end as SXLD,
Case when isnull(MPRTSL,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD>:''+RTrim(MPRTSL)+'' '' end as SL,
Case when isnull(MPRTNiuDu,'''')='''' then '''' else ''Ť<><C5A4>:''+RTrim(MPRTNiuDu)+'' '' end as ND,
Case when isnull(MPRTPH,'''')='''' then '''' else ''PH:''+RTrim(MPRTPH)+'' '' end as PH,
Case when isnull(MPRTKQiQiu,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+RTrim(MPRTKQiQiu)+'' '' end as KQQ,
Case when isnull(MPRTDuiSeGY,'''')='''' then '''' else ''<27><>ɫ<EFBFBD><C9AB>Դ:''+RTrim(MPRTDuiSeGY)+'' '' end as DSGY,
Case when isnull(MPRTDingPoLv,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+RTrim(MPRTDingPoLv)+'' '' end as DPL,
Case when isnull(MPRTZuRanXing,'''')='''' then '''' else ''<27><>ȼ<EFBFBD><C8BC>:''+RTrim(MPRTZuRanXing)+'' '' end as ZRX,
Case when isnull(MPRTQtyNote,'''')='''' then '''' else ''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>:''+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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[JYOrder_Sub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[SY_User] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[P_Label] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[KH_ZDY] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[YF_Money_KC] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[Contract_Cloth_LL] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: UserDefinedFunction [dbo].[getPinYin] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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'<27><>' as word
union all select 'B',N'<27><>'
union all select 'C',N'<27>e'
union all select 'D',N'<27>z'
union all select 'E',N'<27><>'
union all select 'F',N'<27>v'
union all select 'G',N'<27>B'
union all select 'H',N'<27><>'
union all select 'J',N'<27>h'
union all select 'K',N'<27>i'
union all select 'L',N'<27>w'
union all select 'M',N'<27><>'
union all select 'N',N'<27><>'
union all select 'O',N'<27>a'
union all select 'P',N'<27><>'
union all select 'Q',N'<27><>'
union all select 'R',N'<27>U'
union all select 'S',N'<27>R'
union all select 'T',N'<27>X'
union all select 'W',N'<27>F'
union all select 'X',N'<27>R'
union all select 'Y',N'<27><>'
union all select 'Z',N'<27><>'
) 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[RT_FileUpdate] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[CK_BanCP_CRID] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[CK_BanCP_CR] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_OrderJD] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' group by CBR.Mainid)
,ClothRKUnit=(select Top 1 CBR.QtyUnit
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' )
,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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' group by CBR.Mainid)
,ClothCKQty=(select sum(Qty)
from CK_BanCp_CR CBR
where CBR.Mainid=A.MainId and CRType=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' 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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><>Ʒ''
)
,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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><>Ʒ''
)
,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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><><EFBFBD><EFBFBD>''
)
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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_Cost] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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=''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>''
and YFType=''<27>Զ<EFBFBD><D4B6><EFBFBD><EFBFBD><EFBFBD>'' and YFName=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' )
,DPUnit=(select Top 1 QtyUnit from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>''
and YFType=''<27>Զ<EFBFBD><D4B6><EFBFBD><EFBFBD><EFBFBD>'' and YFName=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' )
,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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>'' and CPType=''<27><>Ʒ'' )
,YSK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')
,YSKYB=(select isnull(sum(Money),0) from YF_Money_CR B where B.MainId=A.MainId and B.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'')
,SK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' )
,SKYB=(select isnull(sum(Money),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' )
,FK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'' )
,YFK=(select isnull(sum(BBMoney),0) from YF_Money_CR B where B.MainId=A.MainId and CRType=''Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'' )
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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[TBSubID] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_HC] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Sub_Mx] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_JYOrder_HZFX10] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>2 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>, 3 <20><><EFBFBD><EFBFBD>,4 <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,5<><35><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>
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
--------------------------------------------------------------------------<2D><><EFBFBD>±<EFBFBD><C2B1>ṹ
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
--------------------------------------------------------------------------<2D><><EFBFBD>±<EFBFBD><C2B1>ṹ
--------------------------------------------------------------------------<2D><><EFBFBD>´õ<C2B4><C3B5><EFBFBD><EFBFBD><EFBFBD>
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
--------------------------------------------------------------------------<2D><><EFBFBD>´õ<C2B4><C3B5><EFBFBD><EFBFBD><EFBFBD>
--------------------------------------------------------------------------<2D><>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD>
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
--------------------------------------------------------------------------<2D><>ʾ<EFBFBD><CABE><EFBFBD><EFBFBD>
Drop table #Temp1
Drop table #Temp2
/****** <20><><EFBFBD><EFBFBD>: UserDefinedFunction [dbo].[F_Get_Order_SubStr] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 12/24/2012 14:36:05 ******/
SET ANSI_NULLS ON
GO
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[JYOrder_Sub_AnPai] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_CPKCMX] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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=''<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>''
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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[YF_Money_CR] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_Label_PrintSet] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 05/06/2014 11:52:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--<2D><><EFBFBD>ܣ<EFBFBD><DCA3><EFBFBD>ȡ<EFBFBD><C8A1>Ʒ<EFBFBD><C6B7>ӡ<EFBFBD><D3A1>ǩ<EFBFBD><C7A9>
--<2D><><EFBFBD>ߣ<EFBFBD><DFA3><EFBFBD>
--ʱ<>䣺2009-07-23
CREATE PROCEDURE [dbo].[P_Label_PrintSet]
@splcode varchar(30)=''
AS
BEGIN
SELECT A.TmId as BarCode,
A.JH as <20><><EFBFBD><EFBFBD>,
A.OrderNo as <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,
A.CPCode as Ʒ<><C6B7><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,
A.CPName as Ʒ<><C6B7>,
A.Color as <20><>ɫ,
A.GangNo as <20>׺<EFBFBD>,
A.HX as <20><><EFBFBD><EFBFBD>,
A.MF1 as <20>ŷ<EFBFBD><C5B7><EFBFBD><EFBFBD><EFBFBD>,
A.MF2 as <20>ŷ<EFBFBD><C5B7><EFBFBD><EFBFBD><EFBFBD>,
A.MFunit as <20>ŷ<EFBFBD><C5B7><EFBFBD>λ,
A.KZ1 as <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,
A.KZ2 as <20><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>,
A.KZUnit as <20><><EFBFBD>ص<EFBFBD>λ,
A.QtyMi as <20><><EFBFBD><EFBFBD>,
A.QtyMa as <20><><EFBFBD><EFBFBD>,
A.QtyJZ as <20><><EFBFBD><EFBFBD>,
A.QtyMZ as ë<><C3AB>,
A.QtyPZ as Ƥ<><C6A4>,
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 <20>ŷ<EFBFBD>,
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 <20><><EFBFBD><EFBFBD>
from CP_JY A
WHERE rtrim(A.TmId)=@splcode
END
GO
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[JYOrderCon_Sub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_OrderNo_YSK_View] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 05/06/2014 11:52:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from YF_Money_CR where CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'') ,
--HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRFlag=''Ӧ<><D3A6><EFBFBD><EFBFBD>'') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(YC.BZType,'''')=''USD'') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տƱ<EEB7A2>Ǽ<EFBFBD>'') ,
YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(YC.BZType,'''')<>''USD'') ,
SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''
and isnull(YFName,'''')<>''<27><>˰'' 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=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''
and isnull(YFName,'''')<>''<27><>˰'' and isnull(YC.BZType,'''')=''USD'') ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''
and isnull(YFName,'''')<>''<27><>˰'' and isnull(YC.BZType,'''')<>''USD'') ,
YSYBYMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(YC.BZType,'''')=''USD'')
-(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''
and isnull(YFName,'''')<>''<27><>˰'' and isnull(YC.BZType,'''')=''USD''),
YSBBYMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(YC.BZType,'''')<>''USD'')
-(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>''
and isnull(YFName,'''')<>''<27><>˰'' and isnull(YC.BZType,'''')<>''USD''),
SKTSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType=''<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'' and isnull(YFName,'''')=''<27><>˰'') ,
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=''Ӧ<><D3A6><EFBFBD><EFBFBD>''
and B.CRTime>='''+@begdate+''' and B.CRTime<'''+@enddate+''')
'
Set @MSql=@MSql+@WSql
exec(@MSql)
--select * from YF_Money_CR where CRFlag='Ӧ<><D3A6><EFBFBD><EFBFBD>'
GO
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[WFB_MJJY] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[JYOrder_Main] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Sub] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_HJG_View] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: Table [dbo].[ContractSX_Cloth_LLMx] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[ClothContractSX_QryList] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_Select_CP_YDang] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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), -----<2D><><EFBFBD><EFBFBD>
@PState int, ----0<><30><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>1<EFBFBD><31><EFBFBD>޸<EFBFBD>
@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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_Select_CP_YDang_MaxNo] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_YFK] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' )
,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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' )
,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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
end
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[Get_SY_MaxBH] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 05/06/2014 11:52:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--<2D><><EFBFBD>ܣ<EFBFBD><DCA3><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>(<28><><EFBFBD><EFBFBD><EFBFBD><EFBFBD>)
--<2D><><EFBFBD>ߣ<EFBFBD><DFA3><EFBFBD>
--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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_DJClothKC] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_YFFK_List] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
and isnull(B.PayMent,'')<>'<27>ֽ<EFBFBD>'),
FKMoneyXJ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>' and B.PayMent='<27>ֽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
)
,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='Ӧ<><D3A6><EFBFBD><EFBFBD>')BB
Group by TJName
)AA
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_YSSK_List] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
)
,KHZJM=dbo.getPinYin(A.FactoryName)
from YF_Money_KC A
where A.ZdyStr1='Ӧ<><D3A6><EFBFBD><EFBFBD>')AA
--select * from YF_Money_CR
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_YFFK_ListBAK] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'),
FKMoney=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
and isnull(B.PayMent,'')<>'<27>ֽ<EFBFBD>'),
FKMoneyXJ=(select isnull(Sum(BBMoney),0)
from YF_Money_CR B
where B.CRID=A.CRID and B.CRType='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>' and B.PayMent='<27>ֽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
)
,KHZJM=dbo.getPinYin(A.FactoryName)
from YF_Money_KC A
where A.ZdyStr1='Ӧ<><D3A6><EFBFBD><EFBFBD>')AA
GO
/****** <20><><EFBFBD><EFBFBD>: UserDefinedFunction [dbo].[F_Get_Order_SubStr] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 '<27><>'+RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'<27><>'+RTRIM(Cast(CDEnd as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'<27><><EFBFBD><EFBFBD>:'+RTRIM(Cast(CDQty as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))+'>'
else RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))+'<27><>>' 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 '<27><>'+RTRIM(Cast(CDBeg as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'<27><>'+RTRIM(Cast(CDEnd as varchar(20)))+RTRIM(isnull(B.MJTypeOther,''))
+'<27><><EFBFBD><EFBFBD>:'+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 '<27><><EFBFBD><EFBFBD>:'+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,''))+'<27><>'
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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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='<27><><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>' 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))+'<27><><'+substring(@a,2,len(@a))+'>'
end else
begin
set @a=substring(@a,2,len(@a))
end
return @a
end
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_YFFK_ListTT] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='<27><><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD><EFBFBD>Ǽ<EFBFBD>'
),
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
Group by CRID,FactoryName,ComTaiTou
) A
)AA
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_YSSK_ListTT] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
)
,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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
Group by CRID,FactoryName,ComTaiTou
) A
)AA
--select * from YF_Money_CR
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_DJBKC] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_View_DJBKCBak] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_OrderNo_YSK] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
--HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
HuiLv=(select sum(Money*HuiLv)*1.00/sum(Money) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(YFName,'')<>'<27><>˰' and isnull(YC.BZType,'')='USD') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRFlag='Ӧ<><D3A6><EFBFBD><EFBFBD>') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(BZType,'')='USD') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տƱ<EEB7A2>Ǽ<EFBFBD>') ,
YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(YFName,'')<>'<27><>˰' and isnull(BZType,'')='USD') ,
PJMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(YFName,'')<>'<27><>˰' and isnull(BZType,'')='USD') ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
YSYBYMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(BZType,'')='USD')
-(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
and isnull(YFName,'')<>'<27><>˰' and isnull(BZType,'')='USD'),
SKTSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' and isnull(YFName,'')='<27><>˰') ,
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='Ӧ<><D3A6><EFBFBD><EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' and B.YFDefFlag1=1)
GO
/****** <20><><EFBFBD><EFBFBD>: StoredProcedure [dbo].[P_OrderNo_YSKDL] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' ),
Qty=(select isnull(sum(Qty),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
Price=(select Top 1 Price from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
HuiLv=(select Top 1 HuiLv from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
BZType=(select Top 1 BZType from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
QtyUnit=(select Top 1 QtyUnit from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'),
YSYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
KPMoney=(select isnull(sum(KPMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տƱ<EEB7A2>Ǽ<EFBFBD>') ,
--YSBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
--SKYBMoney=(select isnull(sum(Money),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' ) ,
SKBBMoney=(select isnull(sum(BBMoney),0) from YF_Money_CR YC where YC.MainId=A.MainId and YC.CRType='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>') ,
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='Ӧ<>տ<EFBFBD><D5BF>Ǽ<EFBFBD>'
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='<27>տ<EFBFBD><D5BF>Ǽ<EFBFBD>' and B.YFDefFlag1=1)
)AAA
--select * from JYOrder_Main
--alter table JYOrder_Main Add YSYBFlag bit not null default(0)
GO
/****** <20><><EFBFBD><EFBFBD>: UserDefinedFunction [dbo].[F_Get_Order_MFKZ] <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 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 Copy Permalink