Автоматизированная система управления медицинского страхования
Дипломная работа - Компьютеры, программирование
Другие дипломы по предмету Компьютеры, программирование
est = "";//строка запроса_dbf.CommandText = "select m.code,m.name,(select distinct region from slreg where code=m.okr) as region,m.gd,m.gb,m.sasu,m.tel,m.dogovor, m.dbeg, m.dend from slmso m order by m.code";_dbf = cmd_dbf.ExecuteReader();
//Функция проверки наличия СМО в базе данных_mdf.CommandText = "if object_id(select_smo) is not null drop function select_smo";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_smo(@smoid char(2)) returns int as begin return(select count(smoid) from smo where smoid=@smoid) end";_mdf.ExecuteNonQuery();
//Процедура добавления новой СМО_mdf.CommandText = "if object_id(smo_insert) is not null drop procedure smo_insert";_mdf.ExecuteNonQuery();= "create procedure smo_insert @smoid char(2),@smoname varchar(50),@oiid int,@chief varchar(40),@chiefaccountant varchar(40),@mischief varchar(40), @telephone int,@tfomscontract varchar(8),@startdate datetime,@enddate datetime ";+= "as begin ";+= "if(year(@startdate)=1900) set @startdate=null if(year(@enddate)=1900) set @enddate=null ";+= "if(dbo.select_smo(@smoid)=0) ";+= "insert into smo (smoid,smoname,oiid,chief,chiefaccountant,mischief,telephone,tfomscontract,startdate,enddate) values (@smoid,@smoname,@oiid,@chief,@chiefaccountant,@mischief,@telephone,@tfomscontract,@startdate,@enddate) end";_mdf.CommandText = request;_mdf.ExecuteNonQuery();telephone;start = new DateTime();//дата началаend = new DateTime();//дата окончания(rdr_dbf.Read())
{
{.TryParse(rdr_dbf["tel"].ToString(), out telephone);.TryParse(rdr_dbf["dbeg"].ToString(),out start);(start.Year == 1)= Convert.ToDateTime("1.1.1900");.TryParse(rdr_dbf["dend"].ToString(),out end);(end.Year == 1)= Convert.ToDateTime("1.1.1900");
//вызов хранимой процедуры для добавления записи_mdf.CommandText = string.Format("exec smo_insert {0},{1},{2},{3},{4},{5},{6},{7},{8},{9}", rdr_dbf["code"].ToString(), rdr_dbf["name"].ToString(), rdr_dbf["region"].ToString(), rdr_dbf["gd"].ToString(), rdr_dbf["gb"].ToString(), rdr_dbf["sasu"].ToString(), telephone, rdr_dbf["dogovor"].ToString(), start.ToString("MM.dd.yyyy"), end.ToString("MM.dd.yyyy"));_mdf.ExecuteNonQuery();
}
{ }
}_dbf.Close();
}
//Извлекаем список пунктов регистра застрахованныхextract_prz()
{request="";//строка запроса_dbf.CommandText = "select p.code,p.name,p.q,(select distinct region from slreg where code=p.okr) as region,p.gd,p.gb,p.sasu,p.tel from slprz p order by p.code";_dbf = cmd_dbf.ExecuteReader();
//Функция проверки наличия ПРЗ в базе данных_mdf.CommandText = "if object_id(select_prz) is not null drop function select_prz";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_prz(@przid char(4)) returns int as begin return(select count(przid) from prz where przid=@przid) end";_mdf.ExecuteNonQuery();
//Процедура добавления ПРЗ_mdf.CommandText = "if object_id(prz_insert) is not null drop procedure prz_insert";_mdf.ExecuteNonQuery();= "create procedure prz_insert @przid char(4),@przname varchar(50),@smoid char(2),@oiid int,@chief varchar(20),@matrespon varchar(20),@pcoperator varchar(20),@telephone int ";+= "as begin "; += "if (dbo.select_prz(@przid)=0) ";+= "insert into prz (przid,przname,smoid,oiid,chief,matrespon,pcoperator,telephone) values (@przid,@przname,@smoid,@oiid,@chief,@matrespon,@pcoperator,@telephone) end";_mdf.CommandText = request;_mdf.ExecuteNonQuery();tel = 0;//телефон(rdr_dbf.Read())
{
{.TryParse(rdr_dbf["tel"].ToString(),out tel);
//вызов хранимой процедуры для добавления записи_mdf.CommandText = string.Format("exec prz_insert {0},{1},{2},{3},{4},{5},{6},{7}",rdr_dbf["code"],rdr_dbf["name"],rdr_dbf["q"],rdr_dbf["region"],rdr_dbf["gd"],rdr_dbf["gb"],rdr_dbf["sasu"],tel);_mdf.ExecuteNonQuery();
}
{ }
}_dbf.Close();
}
//Извлечение списка лечебно профилактических учрежденийextract_lpu()
{request = ""; //строка запроса_dbf.CommandText = "select i.code,i.name,(select distinct sl.name from slpod sl where sl.code=i.pod),i.tl1,(select distinct slt.name from sltlpu slt where slt.code=i.tl),i.nom,i.tuzo,i.okpo,i.okogu,i.okato,i.otr,i.okopf,(select distinct s.region from slreg s where s.code=i.okr) as region,i.addr,(select k.name from klpu k where k.code=i.kt) as klpu,i.krr,i.gv,i.gb,dd from infostr i order by i.code";_dbf = cmd_dbf.ExecuteReader();
//Функция поиска ключа подчинённости ЛПУ по наименованию_mdf.CommandText = "if object_id(select_lpu_subord) is not null drop function select_lpu_subord";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_lpu_subord(@lsname varchar(40)) returns int as begin return(select lsid from lpusubord where lsname=@lsname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа возрастного ограничения по наименованию_mdf.CommandText = "if object_id(select_citizens_age) is not null drop function select_citizens_age";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_citizens_age(@caname varchar(30)) returns int as begin return(select caid from citizensage where caname=@caname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа типа ЛПУ по наименованию_mdf.CommandText = "if object_id(select_lpu_type) is not null drop function select_lpu_type";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_lpu_type(@ltname varchar(40)) returns int as begin return(select ltid from lputype where ltname=@ltname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа категорий ЛПУ по наименованию_mdf.CommandText = "if object_id(select_lpu_categories) is not null drop function select_lpu_categories";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_lpu_categories(@lcname varchar(15)) returns int as begin return(select lcid from lpucategories where lcname=@lcname) end";_mdf.ExecuteNonQuery();
//Функция проверки наличия ЛПУ в базе данных_mdf.CommandText = "if object_id(select_lpu) is not null drop function select_lpu";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_lpu(@lpuid int) returns int as begin return(select count(lpuid) from lpulist where lpuid=@lpuid) end";_mdf.ExecuteNonQuery();
//Процедура добавления лечебно профилактического учреждения_mdf.CommandText = "if object_id(lpu_insert) is not null drop procedure lpu_insert";_mdf.ExecuteNonQuery();= "create procedure lpu_insert @lpuid int,@lpuname varchar(50),@lsname varchar(40),@age_id int,@ltname varchar(40),@ltnumber int,@tuzo int,@okpo int,@okogu int,@okato int,@code float,@okopf int,@oiid int,@address varchar(50),@lcname varchar(15),@regtar float,@maindoctor varchar(20),@chiefaccountant varchar(20),@date datetime ";+= "as begin ";+= "declare @age varchar(30) ";+= "if (@age_id=1) set @age=Взрослое население ";+= "if (@age_id=2) set @age=Детское население ";+= "if (@age_id=3) set @age=Взрослое и детское население ";+= "if (year(@date)=1900) set @date=null ";+= "if (dbo.select_lpu(@lpuid)=0) ";+= "insert into lpulist (lpuid,lpuname,lsid,caid,ltid,ltnumber,tuzo,okpo,okogu,okato,code,okopf,oiid,address,lcid,regtar,maindoctor,chiefaccountant,date) values (@lpuid,@lpuname,dbo.select_lpu_subord(@lsname),dbo.select_citizens_age(@age),dbo.select_lpu_type(@ltname),@ltnumber,@tuzo,@okpo,@okogu,@okato,@code,@okopf,@oiid,@address,dbo.select_lpu_categories(@lcname),@regtar,@maindoctor,@chiefaccountant,@date) end";_mdf.CommandText = request;_mdf.ExecuteNonQuery();code_ot = 0;//код отр.kof = 0;//коэффициент регистрационного тарифаdate = new DateTime();//дата регистрации(rdr_dbf.Read())
{
{.TryParse(rdr_dbf["otr"].ToString().Replace(",",decimal_sep).Replace(".",decimal_sep), out code_ot);.TryParse(rdr_dbf["krr"].ToString().Replace(",",decimal_sep).Replace(".",decimal_sep), out kof);.TryParse(rdr_dbf["dd"].ToString(),out date);(date.Year == 1)= Convert.ToDateTime("1.1.1900");
//вызов хранимой процедуры для добавления записи_mdf.CommandText = string.Format("exec lpu_insert {0},{1},{2},{3},{4},{5},&