📄 create.sql
字号:
/*This file contains scripts for database objects to accompany the book
ASP .NET Professional Projects
By Hersh Bhasin
To install
~~~~~~~~~~
Step1 : Create a MS SQL Server database with the name ASPNET
Step2 : Execute this file in the SQL Server Query Analyzer
*/
/****************************************************************
Create Database
*****************************************************************/
USE master
GO
IF DB_ID('ASPNET') IS NOT NULL BEGIN
DROP DATABASE ASPNET
END
GO
CREATE DATABASE ASPNET
GO
USE ASPNET
GO
/*********************************************************
TABLE CREATION
**********************************************************/
CREATE TABLE groups
(id char(3) NULL,
code_display char(30) NOT NULL,
code_value integer NOT NULL,
code_category integer NULL ,
type char(1) NOT NULL,
constraint groups_pk primary key(code_value) )
CREATE TABLE masters
(
code_value integer identity NOT NULL,
code_display char(30) NOT NULL,
code_category integer NOT NULL,
type char(1) NOT NULL,
closing money default 0 NULL,
opening money default 0 NULL,
constraint masters_pk primary key(code_value) )
CREATE TABLE TblSelection
(Selection varchar(50) NOT NULL ,
CONSTRAINT p
PRIMARY KEY NONCLUSTERED
(Selection))
CREATE TABLE tr_header
(id char(3) NOT NULL,
date datetime NOT NULL,
doc_no int not null,
narr varchar(150) NULL,
ref varchar(15) NULL,
constraint trhdr_pk primary key(doc_no))
CREATE TABLE transactions
(doc_no int NOT NULL,
sr_no money NOT NULL,
code_value int NOT NULL,
dr_amount money default 0 NOT NULL,
cr_amount money default 0 NOT NULL,
posted_to int null ,
constraint trans_pk primary key(doc_no,sr_no),
constraint masters_fk foreign key(code_value)
references masters(code_value),
constraint hdr_fk foreign key(doc_no)
references tr_header(doc_no) )
/************************************************
Insert Data
*************************************************/
insert into groups
( code_value,
code_display,
code_category,
id, type )
values ( 1,
'Capital account',
null,
'R','L' )
insert into groups
( code_category,
code_value,
code_display,
id, type )
values ( null,
2,
'Long term loans',
'R', 'L' )
insert into groups
( code_category,
code_value,
code_display,
id , type)
values ( 3,
303,
'Bank od & limits',
'R','L' )
insert into groups
( code_category,
code_value,
code_display,
id, type )
values ( 2,
201,
'Secured loans',
'R','L' )
insert into groups
( code_category,
code_value,
code_display,
id, type )
values ( 2,
202,
'Unsecured loans',
'R' ,'L')
insert into groups
( code_category,
code_value,
code_display,
id , type)
values ( null,
3,
'Current liabilities',
'R' ,'L')
insert into groups
( code_category,
code_value,
code_display,
id , type)
values ( 3,
300,
'Duties taxes payable (bs)',
'R','L' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 3,
301,
'Provisions',
'R' ,'L')
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 3,
302,
'Sundry creditors',
'R' ,'L')
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
4,
'Fixed assets',
'R' ,'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
5,
'Investments',
'R' ,'A')
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
6,
'Current assets',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 6,
601,
'Deposits (assets)',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 6,
602,
'Advances (assets)',
'R' , 'A' )
insert into groups
( id , type,
code_display,
code_value,
code_category )
values ( 'R', 'A' ,
'Advances - excise a/cs',
60200,
602 )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 6,
603,
'Sundry debtors',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 6,
604,
'Cash in hand',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 6,
605,
'Bank accounts',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
7,
'Revenue accounts',
'R', 'M' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 7,
700,
'Sales account',
'R' ,'I' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 7,
701,
'Purchase account',
'R' ,'E')
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 7,
702,
'Income account',
'R', 'I' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 7,
703,
'Duties and taxes paid (p/l)',
'R', 'E' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 7,
704,
'Expenditure account',
'R' , 'E' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 704,
70400,
'Expenses (direct)',
'R' , 'E' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( 704,
70401,
'Expenses (indirect)',
'R' ,'E')
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
8,
'Branch/divisions',
'R', 'A' )
insert into groups
( code_category,
code_value,
code_display,
id , type )
values ( null,
9,
'Reserves and surplus',
'R' ,'L' )
insert into masters ( code_display, code_category,type)
Values ( 'Cash in Hand',604,'A')
insert into masters ( code_display, code_category,type)
Values ( 'Wells Fargo Checking',605,'A')
insert into masters ( code_display, code_category,type)
Values ( 'Visa Card',605,'A')
insert into masters ( code_display, code_category,type)
Values ( 'Salary',70400,'E')
insert into masters ( code_display, code_category,type)
Values ( 'Interest Income',702,'I')
insert into masters ( code_display, code_category,type)
Values ( 'Rent',70401,'E')
insert into masters ( code_display, code_category,type)
Values ( 'Utilities',70400,'E')
insert into masters ( code_display, code_category,type)
Values ( 'Medical Expenses',70401,'E')
go
/***************************************************
Stored Procedures
***************************************************/
if exists (select * from sysobjects where id = object_id('p_masters') and sysstat & 0xf = 4)
drop procedure p_masters
go
create procedure p_masters
@code_value integer = null,
@code_display varchar(30),
@code_category integer = NULL ,
@type char(1)= NULL,
@opening money = 0 ,
@closing money =0,
@group_name varchar(30) = NULL
as
/*********************************************************************************
This procedures creates or updates a new master record. If a null
code_value is passed, a record is inserted else the record is updated. You can
pass either the code of the group or the descriptive name of the group.
Example passing the code of the group (604):
execute p_masters 1,' Petty Cash a/c' ,604, 'A',0,0 ,NULL
Example passing the name of the group(Cash a/c):
p_masters 1,'Petty Cash a/c' ,604, 'A',0,0,'Cash a/c'
***********************************************************************************/
DECLARE @flag integer
DECLARE @oldType as char(1)
DECLARE @grCode_value integer
DECLARE @grType as char(1)
IF isnull(@code_value,0) = 0
-----------If code value = 0 then INSERT a new record ------------------
BEGIN
IF Datalength(@group_name) > 1
Begin
---- If the group_name is provided look up group
------details using the descriptive name
SELECT @grtype = type ,
@grCode_value = code_value
from Groups
WHERE code_display = rtrim(@group_name)
End
Else
---If a numeric code_value of the group is provided,
---look up the group details using it
Begin
SELECT @grtype = type ,
@grCode_value = code_value
from Groups
WHERE code_value = @code_category
End
Insert into masters(code_category,code_display,type,opening,closing)
Values(@grCode_value ,@code_display,@grtype,isnull(@opening,0),isnull(@closing,0))
IF @@ERROR != 0
begin
GOTO doerror
END
END
ELSE
----------UPDATE a record if a code_value is passed ---------
BEGIN
Update masters
Set code_category = @code_category,
code_display = @code_display,
--type = @type, don't allow update of type
opening =@opening,
closing =@closing
Where code_value =@code_value
IF @@ERROR != 0
begin
GOTO doerror
END
END
SELECT 0
GOTO doreturn
doerror:
Return - 100
doreturn:
RETURN 0
-----------------------------------------
go
if exists (select * from sysobjects where id = object_id('p_trans') and sysstat & 0xf = 4)
drop procedure p_trans
go
create procedure p_trans
@date datetime ,
@ref varchar(30) = NULL,
@dr_amount money = 0,
@cr_amount money =0,
@posted_to integer,
@id char(3),
@doc_no integer = NULL,
@narr varchar(150) = NULL
as
/************************************************************
Author: Hersh Bhasin
This procedure creates or modifies a transaction record.
Each transaction record will have a entry in tr_header and
two records (a debit and a credit record) in the tranasaction table.
Usage:
To Insert a record:
call with a null doc_no to insert
example : exec p_trans @date="01/01/2001", @ref="test",
@code_value = 1, @dr_amount = 10, @cr_amount=0,
@posted_to = "Sales a/c" ,@id="RPT",@doc_no=Null
To modify a record:
call with an existing doc_no:
example : exec p_trans @date="01/01/2001", @ref="test",
@code_value = 1, @dr_amount = 10, @cr_amount=0,
@posted_to = "Sales a/c" ,@id="RPT",@doc_no=50
******************************************************************/
DECLARE @ll_doc integer
DECLARE @ret integer
DECLARE @code_value integer
/*
Get the selected cash/bank account:
The user makes a selection from selection.aspx
and tblselection is updated with the code_value
*/
Select @code_value = selection from tblSelection
BEGIN TRANSACTION
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -