Автоматизированная система управления медицинского страхования

extract_policie_movement();

//Функция извлечения списка подчинённости ЛПУ

extract_lpu_subord();

//Функция извлечения списка имён

extract_list_names();

//Функция извлечения списка отчеств

extract_list_second_names();

//Функция извлечения списка уточнений предприятия

extract_list_specifications();

//Функция извлечения классификаторов договора

extract_type_class();

//Функция извлечения списка регионов/районов/населённых пунктов

extract_cadastr();

//Функция установки возрастного положения

insert_age();

//Функция извлечения списка страховых медецинских организаций

extract_smo();

//Функция извлечения списка пунктов регистра застрахованных

extract_prz();

//Функция извлечения списка лечебно профилактических учреждений

extract_lpu();

//Функция извлечения списка договоров

extract_contracts();

//Функция извлечения списка застрахованных

extract_insureds();

}

catch (Exception exp)

{

MessageBox.Show(exp.ToString());

}

}

#endregion

#region функции заполения таблиц

//Извлекаем список улиц

void extract_list_streets()

{

cmd_dbf.CommandText = "(select name from street where name<>'') union (select ul from fizpers where ul<>'' order by ul)";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_street') is not null drop function select_street";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_street(@name varchar(50)) returns int as begin return (select count(slname) from streetslist where slname=@name) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления улицы

cmd_mdf.CommandText = "if object_id('streetslist_insert') is not null drop procedure streetslist_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure streetslist_insert @street varchar(50) as begin if(dbo.select_street(@street)=0) insert into streetslist (slname) values (@street) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec streetslist_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем категории застрахованных

void extract_insured_categories()

{

cmd_dbf.CommandText = "select distinct name from kateg where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_insured_categories') is not null drop function select_insured_categories";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_insured_categories(@name varchar(70)) returns int as begin return (select count(icname) from insuredcategories where icname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('insured_categories_insert') is not null drop procedure insured_categories_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure insured_categories_insert @categ varchar(70) as begin if(dbo.select_insured_categories(@categ)=0) insert into insuredcategories (icname) values (@categ) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText =string.Format("exec insured_categories_insert '{0}'",rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем категории ЛПУ

void extract_lpu_categories()

{

cmd_dbf.CommandText = "select distinct name from klpu where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_lpu_categories') is not null drop function select_lpu_categories";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_categories(@name varchar(15)) returns int as begin return (select count(lcname) from lpucategories where lcname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('lpu_categories_insert') is not null drop procedure lpu_categories_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure lpu_categories_insert @categ varchar(15) as begin if(dbo.select_lpu_categories(@categ)=0) insert into lpucategories (lcname) values (@categ) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec lpu_categories_insert '{0}'",rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем типы документов

void extract_documents()

{

cmd_dbf.CommandText = "select distinct name from tpdoc where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_document') is not null drop function select_document";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_document(@name varchar(50)) returns int as begin return (select count(docname) from document where docname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('documents_insert') is not null drop procedure documents_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure documents_insert @doc varchar(50) as begin if(dbo.select_document(@doc)=0) insert into document (docname) values (@doc) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec documents_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем категории социального положения

void extract_social_status()

{

cmd_dbf.CommandText = "select distinct name from socpol where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_social_status') is not null drop function select_social_status";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_social_status(@name varchar(40)) returns int as begin return (select count(ssname) from socialstatus where ssname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('social_status_insert') is not null drop procedure social_status_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure social_status_insert @social varchar(40),@sscategory varchar(25) as begin if(dbo.select_social_status(@social)=0) insert into socialstatus (ssname,sscategory) values (@social,@sscategory) end";

cmd_mdf.ExecuteNonQuery();

string category="";//категория: работающий/не работающий

while (rdr_dbf.Read())

{

try

{

if ((rdr_dbf["name"].ToString() == "В\\служащий") || (rdr_dbf["name"].ToString() == "Пенсионер (работающий)") || (rdr_dbf["name"].ToString() == "Работающий, служащий"))

category = "Работающее население";

else

category = "Неработающее население";

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec social_status_insert '{0}','{1}'", rdr_dbf["name"].ToString(),category);

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем типы ЛПУ

void extract_lpu_types()

{

cmd_dbf.CommandText = "select distinct name from sltlpu where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_lpu_type') is not null drop function select_lpu_type";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_type(@name varchar(40)) returns int as begin return (select count(ltname) from lputype where ltname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('lpu_type_insert') is not null drop procedure lpu_type_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure lpu_type_insert @type varchar(40) as begin if(dbo.select_lpu_type(@type)=0) insert into lputype (ltname) values (@type) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec lpu_type_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список признака движения полиса

void extract_policie_movement()

{

cmd_dbf.CommandText = "select distinct name from slprp where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_policie_movement') is not null drop function select_policie_movement";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_policie_movement(@name varchar(40)) returns int as begin return (select count(pmname) from policiesmovement where pmname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('policie_movement_insert') is not null drop procedure policie_movement_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure policie_movement_insert @policie varchar(40) as begin if(dbo.select_policie_movement(@policie)=0) insert into policiesmovement (pmname) values (@policie) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec policie_movement_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список подчинённости ЛПУ

void extract_lpu_subord()

{

cmd_dbf.CommandText = "select distinct name from slpod where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_lpu_subord') is not null drop function select_lpu_subord";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_subord(@name varchar(40)) returns int as begin return (select count(lsname) from lpusubord where lsname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('lpu_subord_insert') is not null drop procedure lpu_subord_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure lpu_subord_insert @subord varchar(40) as begin if(dbo.select_lpu_subord(@subord)=0) insert into lpusubord (lsname) values (@subord) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec lpu_subord_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список имён

void extract_list_names()

{

cmd_dbf.CommandText = "(select name from slimx where high<>'' and name<>'') union (select im from fizpers where im<>'' order by im)";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция проверки наличия имени в базе данных

cmd_mdf.CommandText = "if object_id('select_name') is not null drop function select_name";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_name(@name varchar(20)) returns int as begin return(select count(nname) from names where nname=@name) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления имени

cmd_mdf.CommandText = "if object_id('names_insert') is not null drop procedure names_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure names_insert @name varchar(20) as begin if(dbo.select_name(@name)=0) insert into names (nname) values (@name) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec names_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список отчеств

void extract_list_second_names()

{

cmd_dbf.CommandText = "(select name from slotx where high<>'' and name<>'') union (select ot from fizpers where ot<>'' order by ot)";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция проверки наличия отчества в базе данных

cmd_mdf.CommandText = "if object_id('select_second_name') is not null drop function select_second_name";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_second_name(@sname varchar(20)) returns int as begin return(select count(snname) from secondnames where snname=@sname) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления отчества

cmd_mdf.CommandText = "if object_id('second_names_insert') is not null drop procedure second_names_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure second_names_insert @sname varchar(20) as begin if(dbo.select_second_name(@sname)=0) insert into secondnames (snname) values (@sname) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec second_names_insert '{0}'", rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список уточнений предприятия

void extract_list_specifications()

{

cmd_dbf.CommandText = "select distinct code,name from slpred where name<>'' and code<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_list_specifications') is not null drop function select_list_specifications";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_list_specifications(@name varchar(25)) returns int as begin return(select count(slname) from specificationslist where slname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('list_specifications_insert') is not null drop procedure list_specifications_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure list_specifications_insert @id char(1), @specific varchar(25) as begin if(dbo.select_list_specifications(@specific)=0) insert into specificationslist (slid,slname) values (@id,@specific) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec list_specifications_insert '{0}','{1}'", rdr_dbf["code"].ToString().Trim(), rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch { }

}

rdr_dbf.Close();

}

//Извлекаем классификаторы типов договора

void extract_type_class()

{

cmd_dbf.CommandText = "select distinct name from sltipdog where name<>'' order by name";

rdr_dbf = cmd_dbf.ExecuteReader();

cmd_mdf.CommandText = "if object_id('select_type_class') is not null drop function select_type_class";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_type_class(@name varchar(50)) returns int as begin return(select count(ctname) from classtype where ctname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('type_class_insert') is not null drop procedure type_class_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure type_class_insert @type varchar(50) as begin if(dbo.select_type_class(@type)=0) insert into classtype (ctname) values (@type) end";

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec type_class_insert '{0}'",rdr_dbf["name"].ToString());

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список регионов/районов/населённых пунктов

void extract_cadastr()

{

string request = "";//строка запроса

cmd_dbf.CommandText = "select code,name from region order by code";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция проверки наличия региона в базе данных

cmd_mdf.CommandText = "if object_id('select_region') is not null drop function select_region";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_region(@name varchar(50)) returns int as begin return (select count(rname) from regions where rname=@name) end";

cmd_mdf.ExecuteNonQuery();

//Функция проверки наличия района в базе данных

cmd_mdf.CommandText = "if object_id('select_area') is not null drop function select_area";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_area(@code int,@name varchar(50)) returns int as begin return (select count(aname) from areas where aname=@name and aid=@code) end";

cmd_mdf.ExecuteNonQuery();

//Функция проверки наличия населённого пункта в базе данных

cmd_mdf.CommandText = "if object_id('select_occupieditem') is not null drop function select_occupieditem";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_occupieditem(@code int,@name varchar(50)) returns int as begin return (select count(oiname) from occupieditem where oiname=@name and oiid=@code) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления регион/район/населённый пункт

cmd_mdf.CommandText = "if object_id('cadastr_insert') is not null drop procedure cadastr_insert";

cmd_mdf.ExecuteNonQuery();

request = "create procedure cadastr_insert ";

request += "@code varchar(10),@name varchar(50) as ";

request += "begin ";

request += "declare @length int set @length=len(@code) ";

request += "if ((@length=3) and (substring(@code,1,1)='0')) if (dbo.select_region(@name)=0) insert into regions (rid,rname) values (@code,@name) ";

request += "if (@length=6) if (dbo.select_area(@code,@name)=0) insert into areas (aid,aname,rid) values (@code,@name,substring(@code,1,3)) ";

request += "if (@length>6) if (dbo.select_occupieditem(@code,@name)=0) insert into occupieditem (oiid,oiname,aid) values (@code,@name,substring(@code,1,6)) ";

request += "end";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

while (rdr_dbf.Read())

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec cadastr_insert '{0}','{1}'",rdr_dbf["code"].ToString(),rdr_dbf["name"].ToString().Replace("'",""));

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Устанавливаем возрастное положение

void insert_age()

{

string[] age_str ={ "Взрослое население", "Детское население", "Взрослое и детское население" };

cmd_mdf.CommandText = "if object_id('select_age') is not null drop function select_age";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_age(@name varchar(50)) returns int as begin return (select count(caname) from citizensage where caname=@name) end";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "if object_id('age_insert') is not null drop procedure age_insert";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create procedure age_insert @status varchar(50) as begin if(dbo.select_age(@status)=0) insert into citizensage (caname) values (@status) end";

cmd_mdf.ExecuteNonQuery();

for (int i = 0; i < 3; i++)

{

try

{

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec age_insert '{0}'",age_str[i]);

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

}

//Извлекаем список страховых организаций

void extract_smo()

{

string request = "";//строка запроса

cmd_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";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция проверки наличия СМО в базе данных

cmd_mdf.CommandText = "if object_id('select_smo') is not null drop function select_smo";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_smo(@smoid char(2)) returns int as begin return(select count(smoid) from smo where smoid=@smoid) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления новой СМО

cmd_mdf.CommandText = "if object_id('smo_insert') is not null drop procedure smo_insert";

cmd_mdf.ExecuteNonQuery();

request = "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 ";

request += "as begin ";

request += "if(year(@startdate)=1900) set @startdate=null if(year(@enddate)=1900) set @enddate=null ";

request += "if(dbo.select_smo(@smoid)=0) ";

request += "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";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

int telephone;

DateTime start = new DateTime();//дата начала

DateTime end = new DateTime();//дата окончания

while (rdr_dbf.Read())

{

try

{

int.TryParse(rdr_dbf["tel"].ToString(), out telephone);

DateTime.TryParse(rdr_dbf["dbeg"].ToString(),out start);

if (start.Year == 1)

start = Convert.ToDateTime("1.1.1900");

DateTime.TryParse(rdr_dbf["dend"].ToString(),out end);

if (end.Year == 1)

end = Convert.ToDateTime("1.1.1900");

//вызов хранимой процедуры для добавления записи

cmd_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"));

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлекаем список пунктов регистра застрахованных

void extract_prz()

{

string request="";//строка запроса

cmd_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";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция проверки наличия ПРЗ в базе данных

cmd_mdf.CommandText = "if object_id('select_prz') is not null drop function select_prz";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_prz(@przid char(4)) returns int as begin return(select count(przid) from prz where przid=@przid) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления ПРЗ

cmd_mdf.CommandText = "if object_id('prz_insert') is not null drop procedure prz_insert";

cmd_mdf.ExecuteNonQuery();

request = "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 ";

request += "as begin ";

request += "if (dbo.select_prz(@przid)=0) ";

request += "insert into prz (przid,przname,smoid,oiid,chief,matrespon,pcoperator,telephone) values (@przid,@przname,@smoid,@oiid,@chief,@matrespon,@pcoperator,@telephone) end";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

int tel = 0;//телефон

while (rdr_dbf.Read())

{

try

{

int.TryParse(rdr_dbf["tel"].ToString(),out tel);

//вызов хранимой процедуры для добавления записи

cmd_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);

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлечение списка лечебно профилактических учреждений

void extract_lpu()

{

string request = ""; //строка запроса

cmd_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";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция поиска ключа подчинённости ЛПУ по наименованию

cmd_mdf.CommandText = "if object_id('select_lpu_subord') is not null drop function select_lpu_subord";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_subord(@lsname varchar(40)) returns int as begin return(select lsid from lpusubord where lsname=@lsname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа возрастного ограничения по наименованию

cmd_mdf.CommandText = "if object_id('select_citizens_age') is not null drop function select_citizens_age";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_citizens_age(@caname varchar(30)) returns int as begin return(select caid from citizensage where caname=@caname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа типа ЛПУ по наименованию

cmd_mdf.CommandText = "if object_id('select_lpu_type') is not null drop function select_lpu_type";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_type(@ltname varchar(40)) returns int as begin return(select ltid from lputype where ltname=@ltname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа категорий ЛПУ по наименованию

cmd_mdf.CommandText = "if object_id('select_lpu_categories') is not null drop function select_lpu_categories";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu_categories(@lcname varchar(15)) returns int as begin return(select lcid from lpucategories where lcname=@lcname) end";

cmd_mdf.ExecuteNonQuery();

//Функция проверки наличия ЛПУ в базе данных

cmd_mdf.CommandText = "if object_id('select_lpu') is not null drop function select_lpu";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu(@lpuid int) returns int as begin return(select count(lpuid) from lpulist where lpuid=@lpuid) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления лечебно профилактического учреждения

cmd_mdf.CommandText = "if object_id('lpu_insert') is not null drop procedure lpu_insert";

cmd_mdf.ExecuteNonQuery();

request = "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 ";

request += "as begin ";

request += "declare @age varchar(30) ";

request += "if (@age_id=1) set @age='Взрослое население' ";

request += "if (@age_id=2) set @age='Детское население' ";

request += "if (@age_id=3) set @age='Взрослое и детское население' ";

request += "if (year(@date)=1900) set @date=null ";

request += "if (dbo.select_lpu(@lpuid)=0) ";

request += "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";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

float code_ot = 0;//код отр.

float kof = 0;//коэффициент регистрационного тарифа

DateTime date = new DateTime();//дата регистрации

while (rdr_dbf.Read())

{

try

{

float.TryParse(rdr_dbf["otr"].ToString().Replace(",",decimal_sep).Replace(".",decimal_sep), out code_ot);

float.TryParse(rdr_dbf["krr"].ToString().Replace(",",decimal_sep).Replace(".",decimal_sep), out kof);

DateTime.TryParse(rdr_dbf["dd"].ToString(),out date);

if (date.Year == 1)

date = Convert.ToDateTime("1.1.1900");

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec lpu_insert '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}'", rdr_dbf["code"].ToString(), rdr_dbf[1].ToString(), rdr_dbf[2].ToString(), rdr_dbf["tl1"].ToString(), rdr_dbf[4].ToString(), rdr_dbf["nom"].ToString(), rdr_dbf["tuzo"].ToString(), rdr_dbf["okpo"].ToString(), rdr_dbf["okogu"].ToString(), rdr_dbf["okato"].ToString(), code_ot.ToString().Replace(",", "."), rdr_dbf["okopf"].ToString(), rdr_dbf["region"].ToString(), rdr_dbf["addr"].ToString(), rdr_dbf["klpu"].ToString(), kof.ToString().Replace(",", "."), rdr_dbf["gv"].ToString(), rdr_dbf["gb"].ToString(), date.ToString("MM.dd.yyyy"));

cmd_mdf.ExecuteNonQuery();

}

catch

{ }

}

rdr_dbf.Close();

}

//Извлечения списка договоров

void extract_contracts()

{

string request = "";//строка запроса

cmd_dbf.CommandText = "select ndog,rnp,regdat,(select distinct name from sltipdog where code=sltipdog) as ctname,inn,kpp,unemp,name,dbeg,dend,adres,boss,tel,otv,dopinf from dogovor order by ndog";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция поиска ключа типа классификатора по наименованию

cmd_mdf.CommandText = "if object_id('select_type_class') is not null drop function select_type_class";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_type_class(@ctname varchar(50)) returns int as begin return(select ctid from classtype where ctname=@ctname) end";

cmd_mdf.ExecuteNonQuery();

//Функция проверки наличия договора в базе данных

cmd_mdf.CommandText = "if object_id('select_contract') is not null drop function select_contract";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_contract(@clid int) returns int as begin return(select count(clid) from contractlist where clid=@clid) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления договора

cmd_mdf.CommandText = "if object_id('contracts_insert') is not null drop procedure contracts_insert";

cmd_mdf.ExecuteNonQuery();

request = "create procedure contracts_insert @clid int,@regnum varchar(8),@registrationdate smalldatetime,@ctname varchar(50),@inn1 bigint,@inn2 bigint,@kpp bigint,@nw bit,@clname varchar(100),@startdate datetime,@enddate datetime,@address varchar(50),@chief varchar(40),@telephone bigint,@omsresponsible varchar(40),@addinf varchar(100) ";

request += "as begin ";

request += "declare @reg varchar(2) ";

request += "declare @registrationid varchar(5) ";

request += "set @reg=substring(@regnum,1,2) ";

request += "set @registrationid=substring(@regnum,4,5) ";

request += "if (year(@registrationdate)=1900) set @registrationdate=null ";

request += "if (dbo.select_contract(@clid)=0) ";

request += "insert into contractlist (clid,reg,registrationid,registrationdate,ctid,inn1,inn2,kpp,nw,clname,startdate,enddate,address,chief,telephone,omsresponsible,addinf) values (@clid,@reg,ltrim(@registrationid),@registrationdate,isnull(dbo.select_type_class(@ctname),null),@inn1,@inn2,@kpp,@nw,@clname,@startdate,@enddate,@address,@chief,@telephone,@omsresponsible,@addinf) end";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

DateTime regdate = new DateTime();//дата регистрации

DateTime start_reg = new DateTime();//дата начала действия

DateTime end_reg = new DateTime();//дата окончания действия

string inn1, inn2;//ИНН

int pos, kpp;

Int64 tel;//телефон

//переменные для работы c Excel

string file = "";

int count_string = 0;

int column = 0;

List<string[]> records_list = new List<string[]>();//список не обработанных записей

while (rdr_dbf.Read())

{

try

{

DateTime.TryParse(rdr_dbf["regdat"].ToString(),out regdate);

if (regdate.Year==1)

regdate=Convert.ToDateTime("1.1.1900");

//выделяем инн

inn1=rdr_dbf["inn"].ToString();

pos=rdr_dbf["inn"].ToString().IndexOf("/");

if (pos!= -1)

{

inn1 = rdr_dbf["inn"].ToString().Substring(0, pos);

inn2 = rdr_dbf["inn"].ToString().Substring(pos + 1, rdr_dbf["inn"].ToString().Length - pos - 1);

}

else

inn2 = "0";

if (rdr_dbf["kpp"].ToString() == "")

kpp = 0;

else

kpp = Convert.ToInt32(rdr_dbf["kpp"]);

DateTime.TryParse(rdr_dbf["dbeg"].ToString(),out start_reg);

DateTime.TryParse(rdr_dbf["dend"].ToString(),out end_reg);

Int64.TryParse(rdr_dbf["tel"].ToString().Replace("-","").Trim(),out tel);

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec contracts_insert '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}'", rdr_dbf["ndog"], rdr_dbf["rnp"], regdate.ToString("MM.dd.yyyy"), rdr_dbf["ctname"], inn1, inn2, kpp, rdr_dbf["unemp"], rdr_dbf["name"], start_reg.ToString("MM.dd.yyyy"), end_reg.ToString("MM.dd.yyyy"),rdr_dbf["adres"],rdr_dbf["boss"],tel,rdr_dbf["otv"],rdr_dbf["dopinf"]);

cmd_mdf.ExecuteNonQuery();

}

catch

{

string[] temp_record = new string[16];//текущая запись

count_string++;

for (column = 0; column < 16; column++)

temp_record[column] = rdr_dbf[column].ToString();

records_list.Add(temp_record);

}

}

//создание лог файла

if (count_string > 0)

{

to_file = new To_log_File();

file = workdirectory + "\\log_file_contract_" + DateTime.Now.Date.ToString().Substring(0, 10) + ".xls";

to_file.create_log_file(records_list, count_string, file, true);

}

else

MessageBox.Show("Импорт данных завершён.", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);

rdr_dbf.Close();

}

//Извлечение списка застрахованных

void extract_insureds()

{

string request="";//строка запроса

cmd_dbf.CommandText = "select s_pol,n_pol,(select distinct name from slprp where code=jt) as pmid,lprint,n_kms,dp,dendp,tel,namewk,(select name from tpdoc where code=tipd) as docname,sn_pasp,ft,fam,im,ot,dr,w,(select name from socpol where code=sp) as ssname,regp,ils,regs,ul,dom,kor,str,kv,(select distinct name from kateg where code=kt) as icname,gr,mcod,psdate,sysdate,sysoper,old_q from fizpers order by s_pol";

rdr_dbf = cmd_dbf.ExecuteReader();

//Функция поиска ключа признака движения полиса по наименованию

cmd_mdf.CommandText = "if object_id('select_pmid') is not null drop function select_pmid";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_pmid(@pmname varchar(40)) returns int as begin return(select pmid from policiesmovement where pmname=@pmname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа уточнения предприятия по наименованию

cmd_mdf.CommandText = "if object_id('select_specification') is not null drop function select_specification";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_specification(@slname varchar(25)) returns char(1) as begin return(select slid from specificationslist where slname=@slname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа типа документа по наименованию

cmd_mdf.CommandText = "if object_id('select_document') is not null drop function select_document";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_document(@docname varchar(50)) returns int as begin return(select docid from document where docname=@docname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа имени по наименованию

cmd_mdf.CommandText = "if object_id('select_name') is not null drop function select_name";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_name(@name varchar(20)) returns int as begin return(select nid from names where nname=@name) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа отчества по наименованию

cmd_mdf.CommandText = "if object_id('select_sname') is not null drop function select_sname";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_sname(@snname varchar(20)) returns int as begin return(select snid from secondnames where snname=@snname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа социального статуса по наименованию

cmd_mdf.CommandText = "if object_id('select_status') is not null drop function select_status";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_status(@ssname varchar(40)) returns int as begin return(select ssid from socialstatus where ssname=@ssname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа населённого пункта по наименованию

cmd_mdf.CommandText = "if object_id('select_occupieditem') is not null drop function select_occupieditem";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_occupieditem(@oiid int) returns int as begin return(select oiid from occupieditem where oiid=@oiid) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа улицы по наименованию

cmd_mdf.CommandText = "if object_id('select_streets') is not null drop function select_streets";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_streets(@slname varchar(50)) returns int as begin return(select slid from streetslist where slname=@slname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа категории застрахованного по наименованию

cmd_mdf.CommandText = "if object_id('select_categories') is not null drop function select_categories";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_categories(@icname varchar(70)) returns int as begin return(select icid from insuredcategories where icname=@icname) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа ЛПУ по наименованию

cmd_mdf.CommandText = "if object_id('select_lpu') is not null drop function select_lpu";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_lpu(@lpuid char(7)) returns char(7) as begin return(select lpuid from lpulist where lpuid=@lpuid) end";

cmd_mdf.ExecuteNonQuery();

//Функция поиска ключа СМО по наименованию

cmd_mdf.CommandText = "if object_id('select_smo') is not null drop function select_smo";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_smo(@smoid char(2)) returns char(2) as begin return(select smoid from smo where smoid=@smoid) end";

cmd_mdf.ExecuteNonQuery();

//Функция проверки наличия застрахованного в базе данных

cmd_mdf.CommandText = "if object_id('select_insureds') is not null drop function select_insureds";

cmd_mdf.ExecuteNonQuery();

cmd_mdf.CommandText = "create function select_insureds(@clid int,@number int) returns int as begin return(select count(clid) from insuredlist where clid=@clid and number=@number) end";

cmd_mdf.ExecuteNonQuery();

//Процедура добавления застрахованного

cmd_mdf.CommandText = "if object_id('insureds_insert') is not null drop procedure insureds_insert";

cmd_mdf.ExecuteNonQuery();

request = "create procedure insureds_insert @clid int,@number int,@pmname varchar(40),@pr bit,@kms bigint,@startdate datetime,@enddate datetime,@telephone bigint,@slname varchar(25),@docname varchar(50),@docnumber varchar(20),@os bit,@surname varchar(20),@name varchar(20),@second varchar(20),@birthdate datetime,@male bit,@ssname varchar(40),@oiid1 int,@ils int,@oiid2 int,@strname varchar(50),@house int,@cas char(5),@str char(5),@apartment int,@icname varchar(70),@pigroup int,@lpuid char(7),@outdate datetime,@changedate datetime,@operator int,@smoid char(2) ";

request += "as begin ";

request += "if(year(@outdate)=1900) set @outdate=null ";

request += "if(dbo.select_insureds(@clid,@number)=0) ";

request += "insert into insuredlist (clid,number,pmid,pr,kms,startdate,enddate,telephone,slid2,docid,docnumber,os,surname,nid,snid,birthdate,male,ssid,disterr,ils,resterr,slid,house,cas,str,apartment,icid,pigroup,lpuid,outdate,changedate,operator,smoid) values (@clid,@number,dbo.select_pmid(@pmname),@pr,@kms,@startdate,@enddate,@telephone,dbo.select_specification(@slname),dbo.select_document(@docname),@docnumber,@os,@surname,dbo.select_name(@name),dbo.select_sname(@second),@birthdate,@male,dbo.select_status(@ssname),dbo.select_occupieditem(@oiid1),@ils,dbo.select_occupieditem(@oiid2),dbo.select_streets(@strname),@house,@cas,@str,@apartment,dbo.select_categories(@icname),@pigroup,dbo.select_lpu(@lpuid),@outdate,@changedate,@operator,dbo.select_smo(@smoid)) end";

cmd_mdf.CommandText = request;

cmd_mdf.ExecuteNonQuery();

DateTime start_reg=new DateTime();//дата регистрации

DateTime end_reg = new DateTime();//дата окончания

DateTime birthdate = new DateTime();//дата рождения

DateTime surrender = new DateTime();//дата сдачи

DateTime change = new DateTime();//дата изменения

Int64 telephone=0;//телефон

bool os = false;

bool male = false;//пол (муж. - true, жен. - false)

int ils = 0;

int house=0, apartment=0;//дом, квартира

int groupe=0, oper=0;//группа инвалидности, оператор

//переменные для работы c Excel

string file = "";

int count_string = 0;

int column = 0;

List<string[]> records_list = new List< string[]>();//список не обработанных записей

while (rdr_dbf.Read())

{

try

{

DateTime.TryParse(rdr_dbf["dp"].ToString(),out start_reg);

DateTime.TryParse(rdr_dbf["dendp"].ToString(),out end_reg);

Int64.TryParse(rdr_dbf["tel"].ToString(),out telephone);

if (rdr_dbf["ft"].ToString().Replace("*", "True") == "True")

os = true;

else

os = false;

DateTime.TryParse(rdr_dbf["dr"].ToString(),out birthdate);

if (rdr_dbf["w"].ToString() == "1")

male = true;

else

male = false;

int.TryParse(rdr_dbf["ils"].ToString(), out ils);

int.TryParse(rdr_dbf["dom"].ToString(), out house);

int.TryParse(rdr_dbf["kv"].ToString(), out apartment);

int.TryParse(rdr_dbf["gr"].ToString(), out groupe);

int.TryParse(rdr_dbf["sysoper"].ToString(), out oper);

DateTime.TryParse(rdr_dbf["psdate"].ToString(), out surrender);

if (surrender.Year == 1)

surrender = Convert.ToDateTime("1.1.1900");

DateTime.TryParse(rdr_dbf["sysdate"].ToString(), out change);

//вызов хранимой процедуры для добавления записи

cmd_mdf.CommandText = string.Format("exec insureds_insert '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}','{31}','{32}'", rdr_dbf["s_pol"], rdr_dbf["n_pol"], rdr_dbf["pmid"], rdr_dbf["lprint"], Convert.ToInt64(rdr_dbf["n_kms"]), start_reg.ToString("MM.dd.yyyy"), end_reg.ToString("MM.dd.yyyy"), telephone, rdr_dbf["namewk"], rdr_dbf["docname"], rdr_dbf["sn_pasp"], os, rdr_dbf["fam"], rdr_dbf["im"], rdr_dbf["ot"], birthdate.ToString("MM.dd.yyyy"), male, rdr_dbf["ssname"], rdr_dbf["regp"], ils, rdr_dbf["regs"], rdr_dbf["ul"], house, rdr_dbf["kor"], rdr_dbf["str"], apartment, rdr_dbf["icname"], groupe, rdr_dbf["mcod"], surrender.ToString("MM.dd.yyyy"), change.ToString("MM.dd.yyyy"), oper, rdr_dbf["old_q"]);

cmd_mdf.ExecuteNonQuery();

}

catch

{

string[] temp_record = new string[33];//текущая запись

count_string++;

for (column = 0; column < 33; column++)

temp_record[column] = rdr_dbf[column].ToString();

records_list.Add(temp_record);

}

}

//создание лог файла

if (count_string > 0)

{

to_file = new To_log_File();

file = workdirectory + "\\log_file_insured_" + DateTime.Now.Date.ToString().Substring(0, 10) + ".xls";

to_file.create_log_file(records_list, count_string, file,false);

if (MessageBox.Show("Импорт данных завершён.\nКоличество не обработанных записей: " + count_string + " \nОткыть лог файл?", "Информация", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)

System.Diagnostics.Process.Start(file);

}

else

MessageBox.Show("Импорт данных завершён.", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);

rdr_dbf.Close();

}

#endregion

Размещено на Allbest.ru