ali_delphi
یک شنبه 27 آذر 1384, 17:23 عصر
سلام
من می خواستم یک نرم افزار بانک بنویسم البته آزمایشی و برای خودم که به مشکل برخوردم. من وقتی که کاربر جدیدی ثبت نام می کرد کد او را بر می گرداند ولی اگر چند نفر همزمان این کار را می کردند کد ها را یکسان می داد آخه من در کد های اس کیو الم ؛ اصلا تمام کد رو می ذارم اینجا. اشکالاتش رو بگیرید و اگر تونستید سوالم رو براش راهی بیابید چه در Delphi و چه در SQL. :گریه: :گریه: :متعجب: :ناراحت:
use master
create database TBank
on
primary (
Name = 'TBank',
FileName = 'E:\T Projects\Database\MSSQL\TBank.mdf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
),
(
Name = 'TBankSecondary',
FileName = 'E:\T Projects\Database\MSSQL\TBankSecondary.ndf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
)
log on
(
Name = 'TBankLog',
FileName = 'E:\T Projects\Database\MSSQL\TBank.ldf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
)
use TBank
/* Create All Tables and Procedures that Refer to Registrations of Customers! */
Create Table UsualCustomers
(
CreditNo Int identity(1000,1),
FName nChar(30) not null,
LName nChar(30) not null,
UName nChar(20) Primary key,
Pass nChar(20) not null,
Email nChar(50) not null,
Region Int not null,
Country Int not null,
City Int not null,
Address nText not null,
POBox nChar(15) not null,
RegDate as GetDate()
)
/* This Procedure will Add a New Customer that is a Person; Just a Person */
Go
create procedure AddUsualCust
@UName nChar(20),
@Pass nChar(20),
@FName nChar(30),
@LName nChar(30),
@Email nChar(50),
@Region nChar(30),
@Country nChar(50),
@City nChar(50),
@Address nText,
@POBox nChar(15)
As
if not (exists (select [name] from master..syslogins where [name] = @UName))
begin
exec sp_addlogin @UName, @Pass, 'TBank'
if Exists (select * from master..sysLogins where [name] = @UName)
begin
exec sp_grantdbaccess @UName,@UName
if Exists (select * from TBank..sysLogins where [name] = @UName)
begin
declare @Count int
declare @Cit int
declare @Reg int
set @Count = (select Code from Country where Country = @Country)
set @Cit = (select Code from City where City = @City)
set @Reg = (select Code from Region where Region = @Region)
insert into UsualCustomers (UName,Pass,FName,LName,EMail,Region,Country,City, Address,POBox) values (@UName,@Pass,@FName,@LName,@EMail,@Reg,@Count,@Ci t,@Address,@POBox)
if exists (select * from UsualCustomers where UName = @UName)
begin
Print 'You have Been Registered Successfully. Your Information for your Log in will Sent to you by E-Mail or Go to Customer Service to Give you a REPORT.'
select * from UsualCustomers where (UName = @UName) and (FName = @FName) and (LName = @LName)
return 0 /* It means that you Registered Completed Successfully. */
end
end
else
begin
print 'Something had happen in Service. Please Contact to System Administrator to Help you.'
return 4 /* It means that Some Problem is with Garnting Database to Specific User! */
end
end
end
else
begin
print 'The User Name that you Choose, Already Exists. Please Choose another one.'
return 5 /* 5 means that UName is Available. Please choose another */
end
Go
/* Create a Table to Store Company`s Credits */
Create Table CompanyCustomers
(
Credit int identity(1000,1),
UName nChar(20) Primary Key,
Pass nchar(20) not null,
Company nChar(50) not null,
Address nText not null,
Logo Image not null,
EMail nChar(40) not null,
Region int not null,
Country int not null,
City int not null
)
/* A Short Summary of All Company Table that I Design.
In Some Companies there are a hundred of Persons who are Operators and Employees, Some one
must pay their salary and Even mail theme Many important Notes about their Economic Methods that
they want. But many of General or better to say Personal Users never want these Services and these
Services need Memory to Save and It is very Important for us. But if Some one wants these services,
He or She must Pay for Each MB of Memory that He or She use; Except Saye Mehre T Communication
Group Employees!
*/
Go
create procedure AddCompanyCust
@UName nChar(20),
@Pass nChar(20),
@Company nChar(50),
@Address nText,
@Logo Image,
@EMail nChar(40),
@Country nChar(50),
@City nChar(50),
@Region nChar(30)
As
If not (exists (select * from Master..sysLogins where [name] = @UName))
begin
exec sp_addlogin @UName,@Pass,'TBank'
if exists (select * from Master..syslogins where [name] = @UName)
begin
exec sp_grantdbaccess @UName,@UName
if exists (select * from TBank..syslogins where [name] = @UName)
begin
declare @Count int
declare @Cit int
declare @Reg int
set @Count = (select Code from Country where Country = @Country)
set @Cit = (select Code from City where City = @City)
set @Reg = (select Code from Region where Region = @Region)
insert into CompanyCustomers (UName,Pass,Company,Address,Logo,EMail,Region,Coun try,City) values (@UName,@Pass,@Company,@Address,@Logo,@EMail,@Reg, @Count,@Cit)
if exists (select * from TBank..CompanyCustomers where UName = @UName)
begin
print 'You have Registered Successfully!'
return 0 /* You are Registered */
end
else
begin
print 'Something is going bad. Contact Administrator or try again.'
exec sp_revokelogin @UName
exec sp_droplogin @UName
return 4 /* Some Problem is available */
end
end
else
begin
print 'Something is going wrong! Contact System Administrator or Try again!'
exec sp_droplogin @UName
return 3 /* You can not register in Database */
end
end
else
begin
print 'Something is going wrong! Contact System Administrator or Try again!'
return 2 /* You can not register in Server */
end
end
else
begin
print 'User Name that you Specify is available. Please Choose another One'
return 5 /* It means that UName is Available now, Choose another */
end
Go
/* This Table Contains all Employees who must Receive their Salaries from Companies */
Create Table CompanyEmployee
(
ComCredit int not null,
EmpCredit int not null,
SendDate datetime not null,
Salary money not null
)
/* This Procedure Add Employee Names in Company Table */
Go
Create Procedure AddEmployee
@ComCredit int,
@Company nChar(50),
@EmpCredit int,
@EmpFName nChar(30),
@EmpLName nChar(30),
@SendDate datetime,
@Salary money,
@UName nChar(20),
@Pass nChar(20)
As
if (exists (select * from UsualCustomers where CreditNo = @EmpCredit)) and (exists (select * from CompanyCustomers where Credit = @ComCredit))
begin
declare @Comp nChar(50)
declare @EmpF nChar(30)
declare @EmpL nChar(30)
declare @UNam nChar(20)
declare @Pa nChar(20)
set @Comp = (select Company from CompanyCustomers where Credit = @ComCredit)
set @EmpF = (select FName from UsualCustomers where CreditNo = @EmpCredit)
set @EmpL = (select LName from UsualCustomers where CreditNo = @EmpCredit)
set @UNam = (select UName from CompanyCustomers where Credit = @ComCredit)
set @Pa = (select Pass from CompanyCustomers where Credit = @ComCredit)
if (@Comp = (select Company from CompanyCustomers where Company = @Company)) and (@EmpF = (select FName from UsualCustomers where FName = @EmpFName)) and (@EmpL = (select LName from UsualCustomers where LName = @EmpLName)) and (@UNam = (select UName from CompanyCustomers where UName = @UName)) and (@Pa = (select Pass from CompanyCustomers where Pass = @Pass))
begin
insert into CompanyEmployee (ComCredit,EmpCredit,SendDate,Salary) values (@ComCredit,@EmpCredit,@SendDate,@Salary)
if exists (select * from CompanyEmployee where (ComCredit = @ComCredit) and (EmpCredit = @EmpCredit))
begin
print 'You have Registered Successfully!'
return 0 /* Registration have done */
end
else
begin
print 'Some thing goes wrong. Contact System Administrator or try again.'
end
end
else
begin
print 'Some Information are Incorrect. Please Correct theme and try again. PASSWORD AND USER-NAME ARE CASE SENSITIVE'
return 4 /* means Input Information are Wrong */
end
end
else
begin
print 'Company Is not valid or Customer!'
return 5 /* means Company or Employee are Unavailable */
end
Go
create Table Governments
(
Credit int identity(10000000,1),
Region int not null,
Country int not null,
UName nChar(50) not null,
Pass nChar(50) not null,
OptCode nChar(10) not null,
GCode nChar(10) not null,
RegDate as getdate(),
Flag image not null
)
/* End of Creation of All Tables and Procedures that Refer to Registrations of Customers! */
/* Creating All Tables and Procedures on Checks and Economic Reports */
/* Create A Table for Checks */
create table SChecks
(
Number int identity(10000,1),
FCredit int not null,
TCredit int not null,
Amount money not null,
SDate as getdate(),
ADate datetime not null
)
go
create procedure AddCheck
@FCredit int,
@TCredit int,
@Amount money,
@ADate datetime,
@FFName nchar(30),
@FLName nchar(30),
@TFName nchar(30),
@TLName nchar(30),
@UName nchar(20),
@Pass nchar(20)
as
if (exists (select * from UsualCustomers where (UName = @UName) and (Pass = @Pass) and (CreditNo = @FCredit) and (FName = @FFName) and (LName = @FLName))) and (exists (select * from UsualCustomers where (FName = @TFName) and (LName = @TLName) and (CreditNo = @TCredit) and (FName = @TFName) and (LName = @TLName)))
begin
insert into SChecks (FCredit,TCredit,Amount,ADate) values (@FCredit,@TCredit,@Amount,@ADate)
/* I must ask somebody */
end
else
begin
print 'Your Information are Incorrect. Please Correct theme and try again.'
return 5 /* It means as print as up */
end
go
/* This table store Passed Checks */
create Table PChecks
(
Number int not null,
PDate as Getdate()
)
/* This Table Store UNPASSED CHECKS */
create Table EChecks
(
Number int not null,
Passed int not null /* 0: Not Passed yet ; 1: Passed and Move to Passed Checks */
)
/*
EChecks and PChecks will Filled by TBank Main Server Software. This Software only Run on Server
and do all Of Passing Checks, Connections, Error Handling, E-Mails, Economic Mails and etc.
*/
/* Here we have a Sample of Economic Report */
/* create procedure */
/* End of Creating All Tables and Procedures on Checks and Economic Reports */
/* Creating All Tables and Procedure on Supported Regions */
Create Table Region
(
Region nchar(30) not null,
Code int identity(1,1)
)
/* Now Insert Regions of All over The World */
Go
Insert into Region (Region) values ('Asia Except Middle East')
Insert into Region (Region) values ('Middle East')
Insert into Region (Region) values ('Europ')
Insert into Region (Region) values ('Australia and Newsland')
Insert into Region (Region) values ('North America')
Insert into Region (Region) values ('South America')
Insert into Region (Region) values ('Africa')
Go
Create Table Country
(
RegCode int not null,
Country nchar(50) not null,
Code int identity(1,1)
)
/* Now Insert Some Default Countries */
Go
Insert into Country (RegCode,Country) values (2,'Iran')
Insert into Country (RegCode,Country) values (4,'Australia')
Insert into Country (RegCode,Country) values (5,'USA')
Insert into Country (RegCode,Country) values (3,'England')
Insert into Country (RegCode,Country) values (5,'Canada')
Insert into Country (RegCode,Country) values (1,'Afghanistan')
Go
Create Table City
(
CountCode int not null,
City nchar(50) not null,
Code int identity(1,1)
)
/* Now Insert Some Default Cities */
Go
Insert into City (CountCode,City) values (1,'Kerman')
Insert into City (CountCode,City) values (1,'Tehran')
Insert into City (CountCode,City) values (6,'Kabol')
Insert into City (CountCode,City) values (3,'Washington D.C.')
Insert into City (CountCode,City) values (3,'New York')
Insert into City (CountCode,City) values (4,'London')
Insert into City (CountCode,City) values (5,'Torento')
Insert into City (CountCode,City) values (1,'Isfahan')
Go
/* End of Creation of All Tables and Procedure on Supported Regions */ :گیج:
من می خواستم یک نرم افزار بانک بنویسم البته آزمایشی و برای خودم که به مشکل برخوردم. من وقتی که کاربر جدیدی ثبت نام می کرد کد او را بر می گرداند ولی اگر چند نفر همزمان این کار را می کردند کد ها را یکسان می داد آخه من در کد های اس کیو الم ؛ اصلا تمام کد رو می ذارم اینجا. اشکالاتش رو بگیرید و اگر تونستید سوالم رو براش راهی بیابید چه در Delphi و چه در SQL. :گریه: :گریه: :متعجب: :ناراحت:
use master
create database TBank
on
primary (
Name = 'TBank',
FileName = 'E:\T Projects\Database\MSSQL\TBank.mdf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
),
(
Name = 'TBankSecondary',
FileName = 'E:\T Projects\Database\MSSQL\TBankSecondary.ndf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
)
log on
(
Name = 'TBankLog',
FileName = 'E:\T Projects\Database\MSSQL\TBank.ldf',
Size = 5MB,
MaxSize = 25MB,
FileGrowth = 5MB
)
use TBank
/* Create All Tables and Procedures that Refer to Registrations of Customers! */
Create Table UsualCustomers
(
CreditNo Int identity(1000,1),
FName nChar(30) not null,
LName nChar(30) not null,
UName nChar(20) Primary key,
Pass nChar(20) not null,
Email nChar(50) not null,
Region Int not null,
Country Int not null,
City Int not null,
Address nText not null,
POBox nChar(15) not null,
RegDate as GetDate()
)
/* This Procedure will Add a New Customer that is a Person; Just a Person */
Go
create procedure AddUsualCust
@UName nChar(20),
@Pass nChar(20),
@FName nChar(30),
@LName nChar(30),
@Email nChar(50),
@Region nChar(30),
@Country nChar(50),
@City nChar(50),
@Address nText,
@POBox nChar(15)
As
if not (exists (select [name] from master..syslogins where [name] = @UName))
begin
exec sp_addlogin @UName, @Pass, 'TBank'
if Exists (select * from master..sysLogins where [name] = @UName)
begin
exec sp_grantdbaccess @UName,@UName
if Exists (select * from TBank..sysLogins where [name] = @UName)
begin
declare @Count int
declare @Cit int
declare @Reg int
set @Count = (select Code from Country where Country = @Country)
set @Cit = (select Code from City where City = @City)
set @Reg = (select Code from Region where Region = @Region)
insert into UsualCustomers (UName,Pass,FName,LName,EMail,Region,Country,City, Address,POBox) values (@UName,@Pass,@FName,@LName,@EMail,@Reg,@Count,@Ci t,@Address,@POBox)
if exists (select * from UsualCustomers where UName = @UName)
begin
Print 'You have Been Registered Successfully. Your Information for your Log in will Sent to you by E-Mail or Go to Customer Service to Give you a REPORT.'
select * from UsualCustomers where (UName = @UName) and (FName = @FName) and (LName = @LName)
return 0 /* It means that you Registered Completed Successfully. */
end
end
else
begin
print 'Something had happen in Service. Please Contact to System Administrator to Help you.'
return 4 /* It means that Some Problem is with Garnting Database to Specific User! */
end
end
end
else
begin
print 'The User Name that you Choose, Already Exists. Please Choose another one.'
return 5 /* 5 means that UName is Available. Please choose another */
end
Go
/* Create a Table to Store Company`s Credits */
Create Table CompanyCustomers
(
Credit int identity(1000,1),
UName nChar(20) Primary Key,
Pass nchar(20) not null,
Company nChar(50) not null,
Address nText not null,
Logo Image not null,
EMail nChar(40) not null,
Region int not null,
Country int not null,
City int not null
)
/* A Short Summary of All Company Table that I Design.
In Some Companies there are a hundred of Persons who are Operators and Employees, Some one
must pay their salary and Even mail theme Many important Notes about their Economic Methods that
they want. But many of General or better to say Personal Users never want these Services and these
Services need Memory to Save and It is very Important for us. But if Some one wants these services,
He or She must Pay for Each MB of Memory that He or She use; Except Saye Mehre T Communication
Group Employees!
*/
Go
create procedure AddCompanyCust
@UName nChar(20),
@Pass nChar(20),
@Company nChar(50),
@Address nText,
@Logo Image,
@EMail nChar(40),
@Country nChar(50),
@City nChar(50),
@Region nChar(30)
As
If not (exists (select * from Master..sysLogins where [name] = @UName))
begin
exec sp_addlogin @UName,@Pass,'TBank'
if exists (select * from Master..syslogins where [name] = @UName)
begin
exec sp_grantdbaccess @UName,@UName
if exists (select * from TBank..syslogins where [name] = @UName)
begin
declare @Count int
declare @Cit int
declare @Reg int
set @Count = (select Code from Country where Country = @Country)
set @Cit = (select Code from City where City = @City)
set @Reg = (select Code from Region where Region = @Region)
insert into CompanyCustomers (UName,Pass,Company,Address,Logo,EMail,Region,Coun try,City) values (@UName,@Pass,@Company,@Address,@Logo,@EMail,@Reg, @Count,@Cit)
if exists (select * from TBank..CompanyCustomers where UName = @UName)
begin
print 'You have Registered Successfully!'
return 0 /* You are Registered */
end
else
begin
print 'Something is going bad. Contact Administrator or try again.'
exec sp_revokelogin @UName
exec sp_droplogin @UName
return 4 /* Some Problem is available */
end
end
else
begin
print 'Something is going wrong! Contact System Administrator or Try again!'
exec sp_droplogin @UName
return 3 /* You can not register in Database */
end
end
else
begin
print 'Something is going wrong! Contact System Administrator or Try again!'
return 2 /* You can not register in Server */
end
end
else
begin
print 'User Name that you Specify is available. Please Choose another One'
return 5 /* It means that UName is Available now, Choose another */
end
Go
/* This Table Contains all Employees who must Receive their Salaries from Companies */
Create Table CompanyEmployee
(
ComCredit int not null,
EmpCredit int not null,
SendDate datetime not null,
Salary money not null
)
/* This Procedure Add Employee Names in Company Table */
Go
Create Procedure AddEmployee
@ComCredit int,
@Company nChar(50),
@EmpCredit int,
@EmpFName nChar(30),
@EmpLName nChar(30),
@SendDate datetime,
@Salary money,
@UName nChar(20),
@Pass nChar(20)
As
if (exists (select * from UsualCustomers where CreditNo = @EmpCredit)) and (exists (select * from CompanyCustomers where Credit = @ComCredit))
begin
declare @Comp nChar(50)
declare @EmpF nChar(30)
declare @EmpL nChar(30)
declare @UNam nChar(20)
declare @Pa nChar(20)
set @Comp = (select Company from CompanyCustomers where Credit = @ComCredit)
set @EmpF = (select FName from UsualCustomers where CreditNo = @EmpCredit)
set @EmpL = (select LName from UsualCustomers where CreditNo = @EmpCredit)
set @UNam = (select UName from CompanyCustomers where Credit = @ComCredit)
set @Pa = (select Pass from CompanyCustomers where Credit = @ComCredit)
if (@Comp = (select Company from CompanyCustomers where Company = @Company)) and (@EmpF = (select FName from UsualCustomers where FName = @EmpFName)) and (@EmpL = (select LName from UsualCustomers where LName = @EmpLName)) and (@UNam = (select UName from CompanyCustomers where UName = @UName)) and (@Pa = (select Pass from CompanyCustomers where Pass = @Pass))
begin
insert into CompanyEmployee (ComCredit,EmpCredit,SendDate,Salary) values (@ComCredit,@EmpCredit,@SendDate,@Salary)
if exists (select * from CompanyEmployee where (ComCredit = @ComCredit) and (EmpCredit = @EmpCredit))
begin
print 'You have Registered Successfully!'
return 0 /* Registration have done */
end
else
begin
print 'Some thing goes wrong. Contact System Administrator or try again.'
end
end
else
begin
print 'Some Information are Incorrect. Please Correct theme and try again. PASSWORD AND USER-NAME ARE CASE SENSITIVE'
return 4 /* means Input Information are Wrong */
end
end
else
begin
print 'Company Is not valid or Customer!'
return 5 /* means Company or Employee are Unavailable */
end
Go
create Table Governments
(
Credit int identity(10000000,1),
Region int not null,
Country int not null,
UName nChar(50) not null,
Pass nChar(50) not null,
OptCode nChar(10) not null,
GCode nChar(10) not null,
RegDate as getdate(),
Flag image not null
)
/* End of Creation of All Tables and Procedures that Refer to Registrations of Customers! */
/* Creating All Tables and Procedures on Checks and Economic Reports */
/* Create A Table for Checks */
create table SChecks
(
Number int identity(10000,1),
FCredit int not null,
TCredit int not null,
Amount money not null,
SDate as getdate(),
ADate datetime not null
)
go
create procedure AddCheck
@FCredit int,
@TCredit int,
@Amount money,
@ADate datetime,
@FFName nchar(30),
@FLName nchar(30),
@TFName nchar(30),
@TLName nchar(30),
@UName nchar(20),
@Pass nchar(20)
as
if (exists (select * from UsualCustomers where (UName = @UName) and (Pass = @Pass) and (CreditNo = @FCredit) and (FName = @FFName) and (LName = @FLName))) and (exists (select * from UsualCustomers where (FName = @TFName) and (LName = @TLName) and (CreditNo = @TCredit) and (FName = @TFName) and (LName = @TLName)))
begin
insert into SChecks (FCredit,TCredit,Amount,ADate) values (@FCredit,@TCredit,@Amount,@ADate)
/* I must ask somebody */
end
else
begin
print 'Your Information are Incorrect. Please Correct theme and try again.'
return 5 /* It means as print as up */
end
go
/* This table store Passed Checks */
create Table PChecks
(
Number int not null,
PDate as Getdate()
)
/* This Table Store UNPASSED CHECKS */
create Table EChecks
(
Number int not null,
Passed int not null /* 0: Not Passed yet ; 1: Passed and Move to Passed Checks */
)
/*
EChecks and PChecks will Filled by TBank Main Server Software. This Software only Run on Server
and do all Of Passing Checks, Connections, Error Handling, E-Mails, Economic Mails and etc.
*/
/* Here we have a Sample of Economic Report */
/* create procedure */
/* End of Creating All Tables and Procedures on Checks and Economic Reports */
/* Creating All Tables and Procedure on Supported Regions */
Create Table Region
(
Region nchar(30) not null,
Code int identity(1,1)
)
/* Now Insert Regions of All over The World */
Go
Insert into Region (Region) values ('Asia Except Middle East')
Insert into Region (Region) values ('Middle East')
Insert into Region (Region) values ('Europ')
Insert into Region (Region) values ('Australia and Newsland')
Insert into Region (Region) values ('North America')
Insert into Region (Region) values ('South America')
Insert into Region (Region) values ('Africa')
Go
Create Table Country
(
RegCode int not null,
Country nchar(50) not null,
Code int identity(1,1)
)
/* Now Insert Some Default Countries */
Go
Insert into Country (RegCode,Country) values (2,'Iran')
Insert into Country (RegCode,Country) values (4,'Australia')
Insert into Country (RegCode,Country) values (5,'USA')
Insert into Country (RegCode,Country) values (3,'England')
Insert into Country (RegCode,Country) values (5,'Canada')
Insert into Country (RegCode,Country) values (1,'Afghanistan')
Go
Create Table City
(
CountCode int not null,
City nchar(50) not null,
Code int identity(1,1)
)
/* Now Insert Some Default Cities */
Go
Insert into City (CountCode,City) values (1,'Kerman')
Insert into City (CountCode,City) values (1,'Tehran')
Insert into City (CountCode,City) values (6,'Kabol')
Insert into City (CountCode,City) values (3,'Washington D.C.')
Insert into City (CountCode,City) values (3,'New York')
Insert into City (CountCode,City) values (4,'London')
Insert into City (CountCode,City) values (5,'Torento')
Insert into City (CountCode,City) values (1,'Isfahan')
Go
/* End of Creation of All Tables and Procedure on Supported Regions */ :گیج: