суббота, 18 декабря 2010 г.
MVC модель Repository(Поповнення)
public class PopovnennaRaxynkyRepository
{
private DataClasses1DataContext data()
{
return DataProvider.DataContext();
}
private DataClasses2DataContext data2()
{
return DataProvider.DataContextAcount();
}
private Table<client> Client()
{
return data().client;
}
private Table<popovnennaRaxynky> PopovnennaRaxynku()
{
return data2().popovnennaRaxynky;
}
public decimal Check(string kod)
{
decimal summa=0;
foreach (popovnennaRaxynky kodRax in PopovnennaRaxynku())
{
if (kodRax.numberPopodnenna == kod && kodRax.stan==false )
{
summa=kodRax.summa.Value;
kodRax.stan = true;
data2().SubmitChanges();
}
}
return summa;
}
}
MVC модель Repository(Акаунты)
public class AccountRepository
{
private DataClasses2DataContext data()
{
return DataProvider.DataContextAcount();
}
private Table<Account> Accounts()
{
return data().Account;
}
public bool Check(string name, string pass)
{
bool Check=false;
foreach (Account account in Accounts())
{
if (account.login.ToString().Trim() == name && account.password.ToString().Trim()== pass && account.idPosadu==4)
{
return Check = true;
}
}
return Check;
}
public int IDOper(string name)
{
return (from s in Accounts()
where (s.login == name)
select s.id).Single();
}
public int IDClient(string name)
{
return (from s in Accounts()
where (s.login == name)
select s.idClient).Single().Value;
}
public string Name(int id)
{
return (from s in Accounts()
where (s.id == id)
select s.login).Single().ToString();
}
public string CreateUser(string login, string password, int idclient)
{
Account accounts = new Account();
accounts.login = login;
accounts.password = password;
accounts.idClient = idclient;
accounts.ip = "172.16.25.11";
accounts.idPosadu =5;
Accounts().InsertOnSubmit(accounts);
data().SubmitChanges();
return "Створений коричтувач";
}
}
четверг, 16 декабря 2010 г.
Скрипт создания базы данных
USE [Abonka]
GO
/****** Object: Table [dbo].[znuwku] Script Date: 12/19/2010 18:45:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[znuwku](
[id] [int] IDENTITY(1,1) NOT NULL,
[vidsotok] [int] NULL,
[abonStaw] [int] NULL,
CONSTRAINT [PK_znuwku] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[poslygu] Script Date: 12/19/2010 18:45:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[poslygu](
[id] [int] IDENTITY(1,1) NOT NULL,
[opus] [nvarchar](50) NULL,
[truvalist] [datetime] NULL,
[vartist] [money] NULL,
CONSTRAINT [PK_poslygu] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[taruf] Script Date: 12/19/2010 18:45:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[taruf](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nchar](10) NULL,
[vartist] [money] NULL,
CONSTRAINT [PK_taruf] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[client] Script Date: 12/19/2010 18:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[client](
[id] [int] IDENTITY(1,1) NOT NULL,
[idTaruf] [int] NULL,
[pib] [nvarchar](50) NULL,
[kilkPC] [int] NULL,
[dateConnect] [datetime] NULL CONSTRAINT [DF_client_dateConnect] DEFAULT (getdate()),
[numberZnawku] [int] NULL,
[abonPlata] [money] NULL,
[stanClient] [nvarchar](50) NULL,
[summaNaRaxynky] [money] NULL,
[idAccount] [int] NULL,
[Misve] [varchar](50) NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[poslugeClient] Script Date: 12/19/2010 18:45:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[poslugeClient](
[id] [int] IDENTITY(1,1) NOT NULL,
[idClient] [int] NULL,
[idPoslugu] [int] NULL,
CONSTRAINT [PK_poslugeClient] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[oplata] Script Date: 12/19/2010 18:45:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[oplata](
[id] [int] IDENTITY(1,1) NOT NULL,
[idClient] [int] NOT NULL,
[dateTimeOplatu] [datetime] NOT NULL CONSTRAINT [DF_oplata_dateTimeOplatu] DEFAULT (getdate()),
[oplachenoDo] [datetime] NOT NULL,
[idOperator] [int] NULL,
[numberPopovnenna] [int] NULL,
[summa] [money] NOT NULL,
CONSTRAINT [PK_oplata] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_client_taruf] Script Date: 12/19/2010 18:45:31 ******/
ALTER TABLE [dbo].[client] WITH CHECK ADD CONSTRAINT [FK_client_taruf] FOREIGN KEY([idTaruf])
REFERENCES [dbo].[taruf] ([id])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [FK_client_taruf]
GO
/****** Object: ForeignKey [FK_client_znuwku] Script Date: 12/19/2010 18:45:31 ******/
ALTER TABLE [dbo].[client] WITH CHECK ADD CONSTRAINT [FK_client_znuwku] FOREIGN KEY([numberZnawku])
REFERENCES [dbo].[znuwku] ([id])
GO
ALTER TABLE [dbo].[client] CHECK CONSTRAINT [FK_client_znuwku]
GO
/****** Object: ForeignKey [FK_oplata_client] Script Date: 12/19/2010 18:45:33 ******/
ALTER TABLE [dbo].[oplata] WITH CHECK ADD CONSTRAINT [FK_oplata_client] FOREIGN KEY([idClient])
REFERENCES [dbo].[client] ([id])
GO
ALTER TABLE [dbo].[oplata] CHECK CONSTRAINT [FK_oplata_client]
GO
/****** Object: ForeignKey [FK_poslugeClient_poslygu] Script Date: 12/19/2010 18:45:34 ******/
ALTER TABLE [dbo].[poslugeClient] WITH CHECK ADD CONSTRAINT [FK_poslugeClient_poslygu] FOREIGN KEY([idPoslugu])
REFERENCES [dbo].[poslygu] ([id])
GO
ALTER TABLE [dbo].[poslugeClient] CHECK CONSTRAINT [FK_poslugeClient_poslygu]
GO
--------------------------------------------------------------------------------------------------
USE [Account]
GO
/****** Object: Table [dbo].[posada] Script Date: 12/19/2010 18:46:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[posada](
[id] [int] IDENTITY(1,1) NOT NULL,
[posada] [nchar](10) NULL,
CONSTRAINT [PK_posada] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[popovnennaRaxynky] Script Date: 12/19/2010 18:46:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[popovnennaRaxynky](
[id] [int] IDENTITY(1,1) NOT NULL,
[numberPopodnenna] [nvarchar](50) NULL,
[summa] [money] NULL,
[stan] [bit] NULL,
CONSTRAINT [PK_popovnennaRaxynky] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Account] Script Date: 12/19/2010 18:46:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Account](
[id] [int] IDENTITY(1,1) NOT NULL,
[login] [nvarchar](50) NULL,
[password] [nvarchar](50) NULL,
[ip] [nvarchar](50) NULL,
[idPosadu] [int] NULL,
[idClient] [int] NULL,
CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: ForeignKey [FK_Account_posada1] Script Date: 12/19/2010 18:46:56 ******/
ALTER TABLE [dbo].[Account] WITH CHECK ADD CONSTRAINT [FK_Account_posada1] FOREIGN KEY([idPosadu])
REFERENCES [dbo].[posada] ([id])
GO
ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_posada1]
GO
MVC модель Repository (Клиенты)
public class ClientRepository
{
public int id { get; set; }
private DataClasses1DataContext data()
{
return DataProvider.DataContext();
}
private Table<client> Client()
{
return data().client;
}
public IQueryable<client> GetClients()
{
return data().client.OrderBy(t => t.pib).Cast<client>();
}
public IQueryable<client> GetClients(client clients)
{
return data().client.Select(t => t.id == clients.id).Cast<client>();
}
public client GetClient(int clientId)
{
return data().client.SingleOrDefault(t => t.id == clientId);
}
public IQueryable Search(string name)
{
return from cl in data().client
where cl.pib.StartsWith(name)
select new
{
ID=cl.id,
ПІБ = cl.pib,
Тариф = cl.taruf.name,
Ціна_тарифа = cl.taruf.vartist,
Кількість_ПК = cl.kilkPC,
Дата_підключення = cl.dateConnect,
Абоненський_стаж = cl.znuwku.abonStaw,
Відсоток_знижки = cl.znuwku.vidsotok
};
}
public void DeleteClient(int clientId)
{
data().client.DeleteOnSubmit(GetClient(clientId));
data().SubmitChanges();
}
public void UpdateClient(client Client)
{
client data = GetClient(Client.id);
data.id = Client.id;
data.idTaruf = Client.idTaruf;
data.pib = Client.pib;
data.kilkPC = Client.kilkPC;
data.dateConnect=Client.dateConnect;
data.numberZnawku = Client.numberZnawku;
data.abonPlata = Client.abonPlata;
data.stanClient = Client.stanClient;
data.summaNaRaxynky = Client.summaNaRaxynky;
data.idAccount = Client.idAccount;
DataProvider.DataContext().SubmitChanges();
}
public IQueryable GetClientView()
{
return from cl in data().client
select new
{
ID = cl.id,
ПІБ=cl.pib,
Тариф=cl.taruf.name,
Ціна_тарифа=cl.taruf.vartist,
Кількість_ПК=cl.kilkPC,
Дата_підключення=cl.dateConnect,
Абоненський_стаж=cl.znuwku.abonStaw,
Відсоток_знижки=cl.znuwku.vidsotok
};
}
public int CalculationStaw1()
{
DateTime d1 = DateTime.Parse("06.11.2005"), d2 = DateTime.Parse("05.11.2010");
TimeSpan ts = d2.Subtract(d1);
int p =Convert.ToInt32(ts.TotalDays / 365);
return p;
}
public void CalculationStaw()
{
var qvery=data().client;
int qveryStaw=0;
int client;
int t;
foreach (client p in qvery)
{
client = CalculationStawClient(p.dateConnect.Value);
qveryStaw = (from n in data().znuwku
where (n.abonStaw == client)
select n).Single().id;
p.numberZnawku = qveryStaw;
}
}
public int CalculationStawClient(DateTime date)
{
DateTime d1 = DateTime.Today;
TimeSpan ts = date.Subtract(d1);
int p = Convert.ToInt32(ts.TotalDays / 365);
return p;
}
public void Save()
{
data().SubmitChanges();
}
int len;
public int[] idClient()
{
int[] Qvery = (from p in data().client
select p.id).ToArray();
len = Qvery.Length;
return Qvery;
}
public int[] IdTarufInClient()
{
int[] Qvery = (from p in data().client
select p.idTaruf.Value).ToArray();
return Qvery;
}
public bool IdTarufInClientParam(int r)
{
bool t=true;
foreach (int array in IdTarufInClient())
{
if (r == array) t = false;
}
return t;
}
public bool IsnueClientTaruf(int t1)
{
bool p = true;
var qvery=from cls in data().client
where t1==cls.idTaruf
select cls;
if (qvery.Count()== 0) { p = false; }
return p;
}
public string SetTarufClient(int t1,int t2)
{
var Qvery = from p in data().client
where p.idTaruf == t1
select p;
foreach(client cl in Qvery)
{
cl.idTaruf = t2;
}
data().SubmitChanges();
return "Передеденно клієнтів на інший тариф";
}
public string Name(int id)
{
return (from s in Client()
where (s.id == id)
select s.pib).Single().ToString();
}
public decimal summaRaxynok(int id)
{
return (from s in Client()
where (s.id == id)
select s.summaNaRaxynky).Single().Value;
}
public decimal AddSummaRaxynok(decimal summa,int id)
{
client cl = Client().Single(o=>o.id==id);
cl.summaNaRaxynky += summa;
data().SubmitChanges();
return cl.summaNaRaxynky.Value;
}
public decimal RemoveSummaRaxynok(decimal summa, int id)
{
client cl = Client().Single(o => o.id == id);
cl.summaNaRaxynky -= summa;
data().SubmitChanges();
return cl.summaNaRaxynky.Value;
}
}
MVC модель Repository(Оплата)
public class OplataRepository
{
private DataClasses1DataContext data()
{
return DataProvider.DataContext();
}
private Table<oplata> Oplata()
{
return data().oplata;
}
private Table<client> Client()
{
return data().client;
}
public string AddOplataClietnt(int id, int month,decimal valuta,int IDOper)
{
DateTime t = ZvitOplataLast(id).oplachenoDo;
client c = Client().Single(o => o.id == id);
oplata opp = new oplata();
opp.idOperator = IDOper;
opp.numberPopovnenna = 0;
opp.dateTimeOplatu = DateTime.Now;
opp.oplachenoDo = t.AddMonths(month);
opp.summa = valuta;
c.oplata.Add(opp);
data().SubmitChanges();
return "Оплата внесена";
}
public string AddOplataClietnts(int id, decimal valuta, int IDOper)
{
DateTime t = DateTime.Now;
client c = Client().Single(o => o.id == id);
oplata opp = new oplata();
opp.idOperator = IDOper;
opp.numberPopovnenna = 0;
opp.dateTimeOplatu = DateTime.Now;
opp.oplachenoDo = t.AddMonths(1);
opp.summa = valuta;
c.oplata.Add(opp);
data().SubmitChanges();
return "Оплата внесена";
}
public decimal AddOplataTerminal(int id, int month, decimal valuta)
{
DateTime t = ZvitOplataLast(id).oplachenoDo;
client c = Client().Single(o => o.id == id);
oplata opp = new oplata();
opp.numberPopovnenna = 0;
opp.dateTimeOplatu = DateTime.Now;
opp.oplachenoDo = t.AddMonths(month);
opp.summa = valuta * month;
c.oplata.Add(opp);
data().SubmitChanges();
return opp.summa;
}
public decimal obraxynokSumma(decimal taruf,int kilk,int znuwka,int month)
{
decimal summa = taruf * month * kilk;
decimal summaZnawkouuu = summa - (summa * znuwka / 100);
return summaZnawkouuu;
}
public decimal obraxynokSummaStart(decimal tarufss, int kilk)
{
decimal p = tarufss * kilk;
return p;
}
public IQueryable ZvitOplata(int id)
{
return from opp in Oplata()
where (id == opp.idClient)
select new
{
Індентифікатор = opp.id,
Дата_операції =opp.dateTimeOplatu,
Оплачено_до = opp.oplachenoDo,
Сума = opp.summa
};
}
public oplata ZvitOplataLast(int id)
{
return (from opp in Oplata()
from c in Client()
where (c.id == opp.idClient)
select opp).ToList().Last();
}
}
Подписаться на:
Комментарии (Atom)