Sedang Disiapkan

Report Generation Interface for .ASP Databse

Query Designer is needed for joined tables in MS SQL database. This project requires the knowledge of Structured Query Language and classic ASP.

If any similar projects done, please send example.

PREVIOUS PROJECT REFERENCES ARE VERY IMPORTANT, IF YOU CAN NOT PROVIDE GOOD PREVIOUS PROJECT REFERENCES (RELATED PROJECTS TO THE SUBJECT), PLEASE DO NOT BOTHER SUBMITTING BID.

1) User should be able to select output columns (SELECT mapping)

2) User should be able to specify column conditions (WHERE clause mapping)

3) User should be able to join tables (with INNER JOIN, sample tables are listed in this document. Join columns would be specified before, not by the user)

4) User should be able to use group by, maximum, minimum, average functions

5) User should be able to save a query he/she built using this query editor for later use

6) User should be able to exclude another query results ( ... NOT IN (saved_query) )

7) User should be able to export the query results to a TAB delimited text file

8) Admin should be able to choose which tables are selectable by users (somewhere within the code, there is no need to setup a seperate admin screen).

For example; somehwere in the code, or in a configuration file I specified that "new_tbl_customers", "new_tbl_order_master", and "new_tbl_order_detail" are the 3 tables that will be used by the query designer.

Then; users will go to query designer and see there are upto 3 tables selectable.

Example 1:

----------

SELECT TABLES: [X]Customers []Orders []Order Detail (User selects the first table. Colums from that table appears below)

SELECT OUTPUT COLUMNS: [X]First Name []Last Name [X]Email []Company []Address []City []State ........ (User selects first name and email)

FILTERS: [First Name ] [Like ] [John%] [REMOVE] (First two are combo box, user selects a column name and a filter, then enters the condition in the text box, then clicks ADD)

[select a column] [filters] [ ] [ADD] (user should be able to add as many filters as they want, or REMOVE the entered filter)

Save query as: [_____________] [SAVE] (User should be able to enter a name such as, "All the Johns" and save this for later use)

Run the Query [TO SCREEN] [TO FILE] (One button outputs to screen, other one outputs to a file)

So, once this form is submitted, the ASP code would generate a query something like this:

SELECT customer_bill_first, customer_bill_email from new_tbl_customers WHERE customer_bill_first LIKE 'John%'

Example 2:

----------

SELECT TABLES: [X]Customers [X]Orders []Order Detail (user selects the first one, colums from that table appears below)

SELECT OUTPUT COLUMNS: [X]First Name []Last Name [X]Email []Company .... []Order# [X]Order Date (User selects first name, email, and the order date)

GROUP BY: [Group]First Name [skip]Last Name [Group]Email [skip]Company .... [skip]Order# [Maximum]Order Date (user groups by name, email; selects the latest order date using Maximum)

FILTERS: [Order Date ] [ > ] [1/1/2008] [REMOVE] (First two are combo box, user selects a column name and a filter, then enters the condition in the text box, then clicks ADD)

[select a column] [filters] [ ] [ADD] (user should be able to add as many filters as they want, or REMOVE the entered filter)

EXCLUDE: [All The Johns] (user wants to exclude all the Johns, so he selects a previously saved query from the combo box to exclude)

Save query as: [_____________] [SAVE] (User should be able to enter a name such as, "Customer Name and Email" and save this for later use)

Run the Query [TO SCREEN] [TO FILE] (One button outputs to screen, other one outputs to a file)

--------------------------------------------------------

TABLES USED IN THIS EXAMPLE

-- PLEASE NOTE --

If you can satisfy the Requirement #8 above,

you do not have to stick with these tables.

These are just examples

--------------------------------------------------------

/*First table is customer table which contains basic customer information such as name, address, and email.*/

/****** Object: Table [dbo].[new_tbl_customers] Script Date: 12/16/2008 11:22:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[new_tbl_customers](

[customer_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_first] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_last] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_mi] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_bill_company] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_bill_email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_bill_street] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_city] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_state_id] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_zip] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_bill_country_id] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[customer_bill_phone] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_bill_fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_subscribed] [bit] NOT NULL,

[customer_ref] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_http_ref] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_from] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_store_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[no_email] [bit] NULL CONSTRAINT [DF_new_tbl_customers_no_email] DEFAULT (0),

[bad_email] [bit] NULL,

[remind_every] [tinyint] NULL,

[added_on] [datetime] NULL CONSTRAINT [DF_new_tbl_customers_added_on] DEFAULT (getdate()),

[rep_1_id] [tinyint] NULL,

[rep_2_id] [tinyint] NULL,

[customer_notes] [varchar](2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[last_printer_model] [int] NULL,

[customer_terms] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_credit] [money] NULL,

[customer_ns_id] [int] NULL,

[customer_level] [tinyint] NULL,

[customer_last_updated_on] [datetime] NULL,

[customer_last_updated_by] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_status] [tinyint] NULL CONSTRAINT [DF_new_tbl_customers_customer_status] DEFAULT (2),

[tax_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[duns_no] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[org_type] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[req_credit_limit] [money] NULL,

[ap_first] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ap_last] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ap_email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[customer_dropships] [bit] NULL,

CONSTRAINT [PK_new_tbl_customers] PRIMARY KEY CLUSTERED

(

[customer_id] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

/* Second table is Orders Table, and it can be joined with the first table using customer_id column*/

/****** Object: Table [dbo].[new_tbl_order_master] Script Date: 12/16/2008 11:23:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[new_tbl_order_master](

[order_id] [int] NOT NULL,

[customer_id] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[order_date] [datetime] NULL,

[order_subtotal] [money] NULL,

[order_tax] [money] NULL,

[order_shipping] [money] NULL,

[order_coupon] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_discount] [money] NULL,

[order_grandtotal] [money] NULL,

[order_ref] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_http_ref] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_avsdata] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_pnref] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_shipping_info_id] [int] NOT NULL,

[order_shipment_date] [smalldatetime] NULL,

[order_shipment_status_id] [tinyint] NULL CONSTRAINT [DF_new_tbl_order_master_order_shipment_status_id] DEFAULT (0),

[order_shipping_method_id] [tinyint] NULL,

[order_tracking_number] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_store_id] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_note] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_comment] [varchar](5000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[is_invoice] [bit] NULL,

[is_changed] [bit] NULL,

[is_dropship] [bit] NULL,

[order_shipment_date_2] [smalldatetime] NULL,

[order_shipping_method_id_2] [tinyint] NULL,

[order_tracking_number_2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_shipment_date_3] [smalldatetime] NULL,

[order_shipping_method_id_3] [tinyint] NULL,

[order_tracking_number_3] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[order_ref_amount] [smallmoney] NULL CONSTRAINT [DF_new_tbl_order_master_order_ref_amount] DEFAULT (0.00),

[order_ref_check] [smallint] NULL,

[order_eta] [smalldatetime] NULL,

[auth_code] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[search_function] [smallint] NULL,

[ajax_fob] [smallint] NULL,

[ajax_mof] [smallint] NULL,

[ajax_oob] [smallint] NULL,

[ajax_mob] [smallint] NULL,

[ref_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ref_4] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [PK_new_tbl_order_master] PRIMARY KEY CLUSTERED

(

[order_id] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

/* Third table is Orders Details table, and it can be joined with the second table using order_id column*/

GO

/****** Object: Table [dbo].[new_tbl_order_detail] Script Date: 12/16/2008 11:24:30 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[new_tbl_order_detail](

[order_id] [int] NOT NULL,

[product_sku] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[order_qty] [tinyint] NULL,

[backorder_qty] [tinyint] NULL CONSTRAINT [DF_new_tbl_order_detail_backorder_qty] DEFAULT (0),

[product_price] [smallmoney] NOT NULL CONSTRAINT [DF_new_tbl_order_detail_product_price] DEFAULT (0),

[hold_qty] [tinyint] NULL,

CONSTRAINT [PK_new_tbl_order_detail] PRIMARY KEY CLUSTERED

(

[order_id] ASC,

[product_sku] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Kemahiran: ASP

Lihat lebih lanjut: sql_latin1_general_cp1_ci_as oracle, report generation asp, asp report sample, requirement document examples, note from the editor example, money on table, mapping document, first bit set, export from ms project, example for project, email address for designer, editor note examples, customer table, code for money, c for int, asc it, asc company, report generation interface, asp table interface, asp collate, smalldatetime classic asp, cast01 1964 smalldatetime, collate con asp, sql_latin1_general_cp1_ci_as example, remove collate sql_latin1_general_cp1_ci_as

Tentang Majikan:
( 75 ulasan ) san diego, United States

ID Projek: #358548

Dianugerahkan kepada:

shmai

have a glance at PM. Regards,

$500 USD dalam 15 hari
(171 Ulasan)
6.8

12 pekerja bebas membida secara purata $311 untuk pekerjaan ini

YashpalSingh123

Hi, Expert Computer Engineer in Microsoft Technology, Can Start Work on Immediately Effect. Thanx

$300 USD dalam 7 hari
(7 Ulasan)
4.2
StanKryloff

Hello!!! My name is Stan Kryloff. I'm a 33-year-old Web Developer working since 1997 in this business. 10-year experience in ASP/MS SQL development, VBScript, JavaScript, HTML, DHTML, T-SQL. 5-year experience in ASP Lagi

$300 USD dalam 7 hari
(2 Ulasan)
3.9
rhussain

Dear Sir, I have 6+ yr exp in developement. I have done the similer job for my clients. I can do this project with full perfaction. thnx n rgrds

$180 USD dalam 5 hari
(1 Ulasan)
1.6
RAKHEEGUPTA

please see PM

$300 USD dalam 7 hari
(0 Ulasan)
0.0
namarketing

Pls see pvt msg

$250 USD dalam 5 hari
(0 Ulasan)
0.0
Softwaresystems

We have a very professional and expert team in dot net /sql and we have done various types of projects. Our website is Bitscrape.com. U can have a look there. Please check the PMB for reference to our work.

$500 USD dalam 5 hari
(0 Ulasan)
0.0
neetupandey

Hi, Thanks for the opportunity to reply in response to your project posting on [url removed, login to view] We provide services for Website designing and development, CMS, Internet Solutions, Software Solutions, Multimedia Solutio Lagi

$500 USD dalam 15 hari
(0 Ulasan)
0.0
SandeshG

Let me know more info.

$200 USD dalam 15 hari
(0 Ulasan)
0.0
rubito

please check pm

$300 USD dalam 0 hari
(0 Ulasan)
0.0
gopalji123

sir, i will do this job..waiting for your response... thanks, Gopalji enerjetics

$200 USD dalam 2 hari
(0 Ulasan)
0.0
DotNetDeveloper9

I am an exprienced VB VB .Net with sql Server, Oracle, Access mySql developer I also built websites using ASP .Net(C#) & MS Sql server. Also I Completed Infosis academic Project 'Protfolio Management(Share trading W Lagi

$200 USD dalam 10 hari
(0 Ulasan)
0.0