Q5/ws_objects/xls_Billprint_win.pbl.src/f_xls_createtable.srf

$PBExportHeader$f_xls_createtable.srf
global type f_xls_createtable from function_object
end type

forward prototypes
global function long f_xls_createtable ()
end prototypes

global function long f_xls_createtable ();String createsql_string = ''

createsql_string = "alter table U_XLS_BillList add parentid int not null   DEFAULT (0)"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList add ROOTid int not null   DEFAULT (0)"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList add sign char(30) not null   DEFAULT ('')"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList drop CONSTRAINT PK_U_XLS_BillList"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "ALTER TABLE [dbo].[U_XLS_BillList] WITH NOCHECK ADD "
createsql_string += " 	CONSTRAINT [PK_U_XLS_BillList] PRIMARY KEY  CLUSTERED "
createsql_string += " 		( "
createsql_string += " 			[BillID] "
createsql_string += " 		)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList drop CONSTRAINT IX_U_XLS_BillList"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList drop CONSTRAINT IX_U_XLS_BillList_1"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList drop CONSTRAINT IX_U_XLS_BillList_2"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "	ALTER TABLE [dbo].[U_XLS_BillList] ADD   "
createsql_string += "	CONSTRAINT [IX_U_XLS_BillList] UNIQUE  NONCLUSTERED   "
createsql_string += " 		(  "
createsql_string += " 			[BillName],  "
createsql_string += " 			[parentid]   "
createsql_string += " 		)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "	ALTER TABLE [dbo].[U_XLS_BillList] ADD   "
createsql_string += "	CONSTRAINT [IX_U_XLS_BillList_1] UNIQUE  NONCLUSTERED   "
createsql_string += " 		(  "
createsql_string += " 			[BillType],  "
createsql_string += " 			[parentid]   "
createsql_string += " 		)  ON [PRIMARY]   "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "	ALTER TABLE [dbo].[U_XLS_BillList] ADD   "
createsql_string += "	CONSTRAINT [IX_U_XLS_BillList_2] UNIQUE  NONCLUSTERED   "
createsql_string += " 		(  "
createsql_string += " 			[sign],  "
createsql_string += " 			[parentid]   "
createsql_string += " 		)  ON [PRIMARY]   "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

//createsql_string = "alter table U_XLS_BillColumn_Def add parentid int not null   DEFAULT (0)"
//EXECUTE IMMEDIATE :createsql_string;
//COMMIT;

createsql_string = "alter table U_XLS_BillColumn_Def drop CONSTRAINT PK_U_XLS_BillColumn_Def"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;


createsql_string = "ALTER TABLE [dbo].[U_XLS_BillColumn_Def] WITH NOCHECK ADD "
createsql_string += " 	CONSTRAINT [PK_U_XLS_BillColumn_Def] PRIMARY KEY  CLUSTERED "
createsql_string += " 		( "
createsql_string += " 			[ColumnID], "
createsql_string += " 			[BillID] "
createsql_string += " 		)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillColumn_Def drop CONSTRAINT IX_U_XLS_BillColumn_Def"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;


createsql_string = "ALTER TABLE [dbo].[U_XLS_BillColumn_Def] ADD  "
createsql_string += " 		CONSTRAINT [IX_U_XLS_BillColumn_Def] UNIQUE  NONCLUSTERED  "
createsql_string += " 		( "
createsql_string += " 			[SignNAME], "
createsql_string += " 			[BillID] "
createsql_string += " 		)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillColumn_Def drop CONSTRAINT IX_U_XLS_BillColumn_Def_1"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;


createsql_string = "ALTER TABLE [dbo].[U_XLS_BillColumn_Def] ADD  "
createsql_string += " 		CONSTRAINT [IX_U_XLS_BillColumn_Def_1] UNIQUE  NONCLUSTERED  "
createsql_string += " 		( "
createsql_string += " 			[ColumnName], "
createsql_string += " 			[BillID] "
createsql_string += " 		)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;



createsql_string = "SET QUOTED_IDENTIFIER ON "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;
createsql_string = "SET ANSI_NULLS ON  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "CREATE VIEW dbo.uv_xls_billtype  "
createsql_string += " 		AS  "
createsql_string += " 		SELECT dbo.U_XLS_BillList.BillID, RTRIM(CAST(dbo.U_XLS_BillList.BillID AS char))   "
createsql_string += " 		      + '-' + RTRIM(CAST(v_columntype.ColumnType AS char)) AS ogmid,   "
createsql_string += " 		      CASE U_XLS_BillList.parentid WHEN 0 THEN '[主表]' + rtrim(U_XLS_BillList.BillName)   "
createsql_string += " 		      +'-'+ v_columntype.typename ELSE '[明细]' + rtrim(U_XLS_BillList.BillName) +'-'+ v_columntype.typename  "
createsql_string += " 		       END AS ogmname, v_columntype.ColumnType  "
createsql_string += " 		FROM dbo.U_XLS_BillList INNER JOIN  "
createsql_string += " 		          (SELECT BillID, ColumnType,   "
createsql_string += " 		               CASE columntype WHEN 0 THEN '[字段]' WHEN 1 THEN '[图片]' ELSE '[' + rtrim(cast(ColumnType  "
createsql_string += " 		                AS char)) + ']' END AS typename  "
createsql_string += " 		         FROM U_XLS_BillColumn_Def  "
createsql_string += " 		         GROUP BY BillID, ColumnType) v_columntype ON   "
createsql_string += " 		      v_columntype.BillID = dbo.U_XLS_BillList.BillID "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "SET QUOTED_IDENTIFIER OFF "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;
createsql_string = "SET ANSI_NULLS ON "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;


createsql_string = "alter table U_XLS_BillList drop CONSTRAINT IX_U_XLS_BillList_3"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

createsql_string = "alter table U_XLS_BillList drop CONSTRAINT IX_U_XLS_BillList_4"
EXECUTE IMMEDIATE :createsql_string;
COMMIT;


createsql_string = "ALTER TABLE [dbo].[U_XLS_BillList] ADD  "
createsql_string += " 			CONSTRAINT [IX_U_XLS_BillList_3] UNIQUE  NONCLUSTERED  "
createsql_string += " 			( "
createsql_string += " 				[ROOTid], "
createsql_string += " 				[BillName] "
createsql_string += " 			)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;
	
	createsql_string = "ALTER TABLE [dbo].[U_XLS_BillList] ADD  "
createsql_string += " 			CONSTRAINT [IX_U_XLS_BillList_4] UNIQUE  NONCLUSTERED  "
createsql_string += " 			( "
createsql_string += " 				[ROOTid], "
createsql_string += " 				[sign] "
createsql_string += " 			)  ON [PRIMARY]  "
EXECUTE IMMEDIATE :createsql_string;
COMMIT;

update U_XLS_BillList
set rootid = billid
where parentid = 0;
commit;


RETURN 1







end function