Automatisation de la gestion des clients résidentiels des revendeurs d'un operateur telecom

1. account

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[account]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[account] GO

CREATE TABLE [dbo].[account] (

[account] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[pin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[status] [bit] NOT NULL , [no_balancecheck] [bit] NOT NULL ,

[amount] [decimal](10, 2) NULL ,

[type] [int] NULL ,

[tariff] [int] NULL ,

[callback_tariff_id] [int] NULL ,

[callbackflag] [int] NULL , [lcr_id] [int] NULL ,

[calls] [int] NULL ,

[call_date] [smalldatetime] NULL ,

[speed_0] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[batch_nr] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[serial_no] [int] NULL , [reseller_id] [int] NULL , [card_flag] [tinyint] NULL , [valid_days] [int] NULL ,

[valid_date] [smalldatetime] NULL , [first_call] [smalldatetime] NULL , [groupedtariff] [tinyint] NULL , [lang_id] [tinyint] NULL ,

[special_tariff_id] [int] NULL ,

[contract_number] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [currency] [tinyint] NULL ,

[lastduration] [int] NULL , [lastamount] [int] NULL ,

[lastdestination] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [initialamount] [int] NULL ,

[status1] [tinyint] NULL ,

[cardtypeID] [smallint] NULL , [distributorID] [smallint] NULL , [posID] [smallint] NULL ,

[conferencing_enabled] [tinyint] NULL ,

[max_conference_participants] [tinyint] NULL ,

[mf_profile_id] [int] NULL , [auto_topup] [tinyint] NULL ,

[activation_date] [smalldatetime] NULL ,

[auto_topup_threshold] [int] NULL ) ON [PRIMARY]

2.

2010 CAMDEV TELECOM Rapport de Stage présenté par Alban Cédric TABUE TEUKAM 22

trace_team

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trace_team]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[trace_team] GO

CREATE TABLE [dbo].[trace_team] (

[trace_id] [int] IDENTITY (1, 1) NOT NULL ,

[Reseler_Id] [int] NULL ,

[ResellerName] [varchar] (50) COLLATE French_CI_AS NOT NULL ,

[SoldeInitialReseller] [int] NULL , [date_op] [datetime] NOT NULL ,

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

[MontantOperation] [int] NOT NULL ,

[ResCustomerAccount] [char] (14) COLLATE French_CI_AS NOT NULL ,

[ResCustomerCLI] [char] (30) COLLATE French_CI_AS NULL ,

[SoldeInitialCli] [int] NULL , [SoldeFinalReseler] [int] NULL , [SoldeFinalCLI] [int] NULL ,

[heure] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

3. cli_table

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cli_table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[cli_table]

2010 CAMDEV TELECOM Rapport de Stage présenté par Alban Cédric TABUE TEUKAM 23

GO

CREATE TABLE [dbo].[cli_table] (

[account] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[cli] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[resellerid] [int] NULL ,

[cli_order] [tinyint] NULL

) ON [PRIMARY]

GO

4. res_custummer

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[res_customer]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[res_customer]

GO

CREATE TABLE [dbo].[res_customer] (

[customer_id] [int] NULL ,

[account] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [salutation] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [firstname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [surname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [street] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [town] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [postcode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

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

[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[monthly] [int] NULL ,

[reseller_id] [int] NULL ,

[day_phone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [create_date] [smalldatetime] NULL ,

[created_by] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[titel] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

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

[company] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [password] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[city] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [contract_number] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [contract_date] [datetime] NULL ,

[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [auto_topup_threshold] [int] NULL ,

[auto_topup_amount] [int] NULL , [wholesale] [tinyint] NULL ,

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

[regStreet] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [regCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[regCounty] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [regPostCode] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [printedStatement] [tinyint] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2010 CAMDEV TELECOM Rapport de Stage présenté par Alban Cédric TABUE TEUKAM 25

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[reseller]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[reseller]

GO

CREATE TABLE [dbo].[reseller] (
[id] [int] NULL ,

[name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[amount] [decimal](12, 2) NULL ,

[tariffid] [int] NULL ,

[accountstatus] [int] NULL , [accounttyp] [int] NULL , [billingunit] [int] NULL , [callbacktariffid] [int] NULL ,

[batchstartdigits] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[callbackbillingunit] [int] NULL ,

[lcr_id] [int] NULL ,

[mobil_fee] [int] NULL , [fee_0800] [int] NULL , [account_length] [int] NULL ,

[first_digit] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[counter] [int] NULL ,

[creditlimit] [numeric](18, 0) NULL ,

[groupedtariff] [tinyint] NULL ,

[default_currency] [tinyint] NULL ,

[merchantnumber] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[password] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

2010 CAMDEV TELECOM Rapport de Stage présenté par Alban Cédric TABUE TEUKAM 26

sql_databases_cdr

1. massmsout

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[massmsout]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[massmsout]

GO

CREATE TABLE [dbo].[massmsout] (

[outid] [bigint] IDENTITY (1, 1) NOT NULL ,

[text] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [email] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[emailsubject] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [isflash] [bit] NOT NULL ,

[iswapsms] [bit] NOT NULL ,

[isencrypted] [bit] NOT NULL ,

[ismail] [bit] NOT NULL ,

[attach1] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [attach2] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [attach3] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [retrycount] [int] NOT NULL ,

[isdeleted] [bit] NOT NULL ,

[userwhocreate] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [sender] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO