CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> Archive -> CodeChargeStudio.Discussion

 How do i get around this !!

Print topic Send  topic

Author Message
Charles
Posted: 11/20/2003, 8:51 PM

Hi Sixto,
Thank you so much for your response.I really appreciate this.
HERE IS THE LAYOUT OF THE DATABASE
CREATE TABLE dbo.patients...Patients Table
(
pat_id INT IDENTITY(1,1) NOT NULL,
pat_acc_no CHAR(15) NOT NULL,
pat_fname CHAR(20) NOT NULL,
pat_sname CHAR(20) NOT NULL,
pat_email CHAR(20),
pat_lphone CHAR(12),
pat_gsm CHAR(15),
pat_addr CHAR(80),
pat_state CHAR(15),
pat_salute CHAR(10),
pat_gender CHAR(10),
pat_yr_birth INT,
pat_mon_birth INT,
pat_day_birth CHAR(10),
pat_age INT,
rheum_fev CHAR(10),
hyper CHAR(10),
heart_dis CHAR(10),
bleed_disorder CHAR(10),
aller_antibiotics CHAR(10),
local_anas CHAR(10),
any_take_drugs CHAR(10),
steroids CHAR(10),
insulin CHAR(10),
transquilisers CHAR(10),
anti_coagulants CHAR(10),
date_regi DATETIME DEFAULT (getdate()),
CONSTRAINT PK_patients PRIMARY KEY CLUSTERED ( pat_id ),
CONSTRAINT IX_patients UNIQUE NONCLUSTERED(pat_acc_no )
)

CREATE TABLE dbo.appoints ....Appointments table
(
app_id INT IDENTITY(1,1) NOT NULL,
app_date DATETIME DEFAULT (getdate()) NOT NULL,
app_time DATETIME DEFAULT (getdate()) NOT NULL,
doc_id INT NOT NULL,
patient_id INT NOT NULL,
pat_acc_no CHAR(15) NOT NULL,
app_type CHAR(10),
app_purpose CHAR(10),
employee_id INT NOT NULL,
CONSTRAINT PK_appoints PRIMARY KEY CLUSTERED ( app_id ),
CONSTRAINT IX_appoints UNIQUE NONCLUSTERED(patient_id )
)

CREATE TABLE dbo.doctors.......Doctors table
(
doc_id INT IDENTITY(1,1) NOT NULL,
doc_fname CHAR(10),
doc_lname CHAR(20) NOT NULL,
doc_lphone CHAR(10),
doc_gsm CHAR(15),
doc_addr TEXT,
doc_status CHAR(15),
CONSTRAINT PK_doctors PRIMARY KEY CLUSTERED ( doc_id )
)

CREATE TABLE dbo.treats.....Treatment table
(
treat_id INT IDENTITY(1,1) NOT NULL,
treat_date DATETIME DEFAULT (getdate()),
treat_time DATETIME DEFAULT (getdate()),
doc_id INT NOT NULL,
pat_id INT NOT NULL,
observation NTEXT,
investigation NTEXT,
diagnosis TEXT,
treat_plan NTEXT,
no_visit_req INT,
last_visit DATETIME,
medical_history NCHAR(39),
age INT,
account_balance MONEY,
purpose_last NCHAR(10),
CONSTRAINT PK_treats PRIMARY KEY CLUSTERED ( treat_id )
)
/*-------- Foreign Keys-----------------*/
ALTER TABLE dbo.treats
ADD CONSTRAINT FK_treats_doctors FOREIGN KEY( doc_id )
REFERENCES [dbo].[doctors] ( doc_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE dbo.treats
ADD CONSTRAINT FK_treats_patients FOREIGN KEY( pat_id )
REFERENCES [dbo].[patients] ( pat_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;

CREATE TABLE dbo.item_orders.....order_items table
(
item_orders_id INT IDENTITY(1,1) NOT NULL,
order_id INT NOT NULL,
item_id INT NOT NULL,
quantity INT,
price FLOAT,
CONSTRAINT PK_item_orders PRIMARY KEY CLUSTERED ( item_orders_id )
)
/*-------- Foreign Keys-----------------*/
ALTER TABLE dbo.item_orders
ADD CONSTRAINT FK_item_orders_items FOREIGN KEY( item_id )
REFERENCES [dbo].[items] ( item_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE dbo.item_orders
ADD CONSTRAINT FK_item_orders_orders FOREIGN KEY( order_id )
REFERENCES [dbo].[orders] ( orderID )
ON UPDATE NO ACTION
ON DELETE NO ACTION;

CREATE TABLE dbo.orders......Orders table
(
orderID INT IDENTITY(34567,66) NOT NULL,
pat_id INT NOT NULL,
employee_id INT,
order_date SMALLDATETIME,
CONSTRAINT PK_orders PRIMARY KEY CLUSTERED ( orderID )
)
/*-------- Foreign Keys-----------------*/
ALTER TABLE dbo.orders
ADD CONSTRAINT FK_orders_patients FOREIGN KEY( pat_id )
REFERENCES [dbo].[patients] ( pat_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
CREATE TABLE dbo.items.......Items Table
(
item_id INT IDENTITY(1,1) NOT NULL,
item_name CHAR(20),
item_desc CHAR(50),
item_price FLOAT,
CONSTRAINT PK_items PRIMARY KEY CLUSTERED ( item_id )
)

CREATE TABLE dbo.payments......Payment Table
(
pay_id INT IDENTITY(1,1) NOT NULL,
pay_date DATETIME DEFAULT (getdate()) NOT NULL,
pat_id INT NOT NULL,
invoice_id INT NOT NULL,
amount FLOAT,
employee_id INT
)
/*-------- Foreign Keys-----------------*/
ALTER TABLE dbo.payments
ADD CONSTRAINT FK_payments_employee FOREIGN KEY( employee_id )
REFERENCES [dbo].[employee] ( employee_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;
ALTER TABLE dbo.payments
ADD CONSTRAINT FK_payments_patients FOREIGN KEY( pat_id )
REFERENCES [dbo].[patients] ( pat_id )
ON UPDATE NO ACTION
ON DELETE NO ACTION;

CREATE TABLE dbo.employee......Employee's table
(
employee_id INT IDENTITY(1,1) NOT NULL,
employee_fname CHAR(35),
employee_lname CHAR(35),
work_start_date DATETIME,
work_end DATETIME,
user_group_id INT,
user_id INT,
user_password CHAR(15),
address CHAR(60),
location CHAR(10),
state CHAR(20),
comments NTEXT,
CONSTRAINT PK_employee PRIMARY KEY CLUSTERED ( employee_id )
)
The workflow is precisely as you described it.Up to stage number 5.my issue
starts from No 6.
"6. Within the same page, but in a separate section, doc is presented with
an
> editable grid where the items can be added to the order.
> -- The editable grid is bounded to the orders_items table.
> -- The grid consists of two fields, items (a select box) and quantity (a
> text box)
> -- The items select box is bounded to the items table.
> -- The orders_items table should only have 3 fields: order_id, item_id,
> quantity (price should be in the items table, and total should be computed
> on the fly)"
I have an editable grid but i do not know how to arrange the
forms(grid,record and editable grid) to go with the workflow of the
application.
How will the doctor be able to fill in several items for a particular
patient,what will the orderID be? Which form comes first?
Looking at my databse design, is it somehow flawed?
What do i need to do?
regards,
Charles

Kevin
Posted: 11/22/2003, 4:14 AM

Your close: Create a page with Order and then create a page with item_order
and use item as the lookup for the item fields, have the submit action send
you to the item_order page with the order_ID and add a delete order function
if they cancel before submitting the item_order page. Or use Iframe and
javascript.

"Charles" <ronicsyscom@ronicsystems.com> wrote in message
news:bpk5kh$74l$1@news.codecharge.com...
> Hi Sixto,
> Thank you so much for your response.I really appreciate this.
> HERE IS THE LAYOUT OF THE DATABASE
> CREATE TABLE dbo.patients...Patients Table
> (
> pat_id INT IDENTITY(1,1) NOT NULL,
> pat_acc_no CHAR(15) NOT NULL,
> pat_fname CHAR(20) NOT NULL,
> pat_sname CHAR(20) NOT NULL,
> pat_email CHAR(20),
> pat_lphone CHAR(12),
> pat_gsm CHAR(15),
> pat_addr CHAR(80),
> pat_state CHAR(15),
> pat_salute CHAR(10),
> pat_gender CHAR(10),
> pat_yr_birth INT,
> pat_mon_birth INT,
> pat_day_birth CHAR(10),
> pat_age INT,
> rheum_fev CHAR(10),
> hyper CHAR(10),
> heart_dis CHAR(10),
> bleed_disorder CHAR(10),
> aller_antibiotics CHAR(10),
> local_anas CHAR(10),
> any_take_drugs CHAR(10),
> steroids CHAR(10),
> insulin CHAR(10),
> transquilisers CHAR(10),
> anti_coagulants CHAR(10),
> date_regi DATETIME DEFAULT (getdate()),
> CONSTRAINT PK_patients PRIMARY KEY CLUSTERED ( pat_id ),
> CONSTRAINT IX_patients UNIQUE NONCLUSTERED(pat_acc_no )
> )
>
> CREATE TABLE dbo.appoints ....Appointments table
> (
> app_id INT IDENTITY(1,1) NOT NULL,
> app_date DATETIME DEFAULT (getdate()) NOT NULL,
> app_time DATETIME DEFAULT (getdate()) NOT NULL,
> doc_id INT NOT NULL,
> patient_id INT NOT NULL,
> pat_acc_no CHAR(15) NOT NULL,
> app_type CHAR(10),
> app_purpose CHAR(10),
> employee_id INT NOT NULL,
> CONSTRAINT PK_appoints PRIMARY KEY CLUSTERED ( app_id ),
> CONSTRAINT IX_appoints UNIQUE NONCLUSTERED(patient_id )
> )
>
> CREATE TABLE dbo.doctors.......Doctors table
> (
> doc_id INT IDENTITY(1,1) NOT NULL,
> doc_fname CHAR(10),
> doc_lname CHAR(20) NOT NULL,
> doc_lphone CHAR(10),
> doc_gsm CHAR(15),
> doc_addr TEXT,
> doc_status CHAR(15),
> CONSTRAINT PK_doctors PRIMARY KEY CLUSTERED ( doc_id )
> )
>
> CREATE TABLE dbo.treats.....Treatment table
> (
> treat_id INT IDENTITY(1,1) NOT NULL,
> treat_date DATETIME DEFAULT (getdate()),
> treat_time DATETIME DEFAULT (getdate()),
> doc_id INT NOT NULL,
> pat_id INT NOT NULL,
> observation NTEXT,
> investigation NTEXT,
> diagnosis TEXT,
> treat_plan NTEXT,
> no_visit_req INT,
> last_visit DATETIME,
> medical_history NCHAR(39),
> age INT,
> account_balance MONEY,
> purpose_last NCHAR(10),
> CONSTRAINT PK_treats PRIMARY KEY CLUSTERED ( treat_id )
> )
> /*-------- Foreign Keys-----------------*/
> ALTER TABLE dbo.treats
> ADD CONSTRAINT FK_treats_doctors FOREIGN KEY( doc_id )
> REFERENCES [dbo].[doctors] ( doc_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
> ALTER TABLE dbo.treats
> ADD CONSTRAINT FK_treats_patients FOREIGN KEY( pat_id )
> REFERENCES [dbo].[patients] ( pat_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
>
> CREATE TABLE dbo.item_orders.....order_items table
> (
> item_orders_id INT IDENTITY(1,1) NOT NULL,
> order_id INT NOT NULL,
> item_id INT NOT NULL,
> quantity INT,
> price FLOAT,
> CONSTRAINT PK_item_orders PRIMARY KEY CLUSTERED ( item_orders_id )
> )
> /*-------- Foreign Keys-----------------*/
> ALTER TABLE dbo.item_orders
> ADD CONSTRAINT FK_item_orders_items FOREIGN KEY( item_id )
> REFERENCES [dbo].[items] ( item_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
> ALTER TABLE dbo.item_orders
> ADD CONSTRAINT FK_item_orders_orders FOREIGN KEY( order_id )
> REFERENCES [dbo].[orders] ( orderID )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
>
> CREATE TABLE dbo.orders......Orders table
> (
> orderID INT IDENTITY(34567,66) NOT NULL,
> pat_id INT NOT NULL,
> employee_id INT,
> order_date SMALLDATETIME,
> CONSTRAINT PK_orders PRIMARY KEY CLUSTERED ( orderID )
> )
> /*-------- Foreign Keys-----------------*/
> ALTER TABLE dbo.orders
> ADD CONSTRAINT FK_orders_patients FOREIGN KEY( pat_id )
> REFERENCES [dbo].[patients] ( pat_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
> CREATE TABLE dbo.items.......Items Table
> (
> item_id INT IDENTITY(1,1) NOT NULL,
> item_name CHAR(20),
> item_desc CHAR(50),
> item_price FLOAT,
> CONSTRAINT PK_items PRIMARY KEY CLUSTERED ( item_id )
> )
>
> CREATE TABLE dbo.payments......Payment Table
> (
> pay_id INT IDENTITY(1,1) NOT NULL,
> pay_date DATETIME DEFAULT (getdate()) NOT NULL,
> pat_id INT NOT NULL,
> invoice_id INT NOT NULL,
> amount FLOAT,
> employee_id INT
> )
> /*-------- Foreign Keys-----------------*/
> ALTER TABLE dbo.payments
> ADD CONSTRAINT FK_payments_employee FOREIGN KEY( employee_id )
> REFERENCES [dbo].[employee] ( employee_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
> ALTER TABLE dbo.payments
> ADD CONSTRAINT FK_payments_patients FOREIGN KEY( pat_id )
> REFERENCES [dbo].[patients] ( pat_id )
> ON UPDATE NO ACTION
> ON DELETE NO ACTION;
>
> CREATE TABLE dbo.employee......Employee's table
> (
> employee_id INT IDENTITY(1,1) NOT NULL,
> employee_fname CHAR(35),
> employee_lname CHAR(35),
> work_start_date DATETIME,
> work_end DATETIME,
> user_group_id INT,
> user_id INT,
> user_password CHAR(15),
> address CHAR(60),
> location CHAR(10),
> state CHAR(20),
> comments NTEXT,
> CONSTRAINT PK_employee PRIMARY KEY CLUSTERED ( employee_id )
> )
> The workflow is precisely as you described it.Up to stage number 5.my
issue
> starts from No 6.
> "6. Within the same page, but in a separate section, doc is presented with
> an
> > editable grid where the items can be added to the order.
> > -- The editable grid is bounded to the orders_items table.
> > -- The grid consists of two fields, items (a select box) and quantity (a
> > text box)
> > -- The items select box is bounded to the items table.
> > -- The orders_items table should only have 3 fields: order_id, item_id,
> > quantity (price should be in the items table, and total should be
computed
> > on the fly)"
> I have an editable grid but i do not know how to arrange the
> forms(grid,record and editable grid) to go with the workflow of the
> application.
> How will the doctor be able to fill in several items for a particular
> patient,what will the orderID be? Which form comes first?
> Looking at my databse design, is it somehow flawed?
> What do i need to do?
> regards,
> Charles
>
>


   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.