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

Дипломная работа - Компьютеры, программирование

Другие дипломы по предмету Компьютеры, программирование

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

//Функция поиска ключа улицы по наименованию_mdf.CommandText = "if object_id(select_streets) is not null drop function select_streets";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_streets(@slname varchar(50)) returns int as begin return(select slid from streetslist where slname=@slname) end";_mdf.ExecuteNonQuery();

//Функция поиска ключа категории застрахованного по наименованию_mdf.CommandText = "if object_id(select_categories) is not null drop function select_categories";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_categories(@icname varchar(70)) returns int as begin return(select icid from insuredcategories where icname=@icname) 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 char(7)) returns char(7) as begin return(select lpuid from lpulist where lpuid=@lpuid) end";_mdf.ExecuteNonQuery();

//Функция поиска ключа СМО по наименованию_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 char(2) as begin return(select smoid from smo where smoid=@smoid) end";_mdf.ExecuteNonQuery();

//Функция проверки наличия застрахованного в базе данных_mdf.CommandText = "if object_id(select_insureds) is not null drop function select_insureds";_mdf.ExecuteNonQuery();_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";_mdf.ExecuteNonQuery();

//Процедура добавления застрахованного_mdf.CommandText = "if object_id(insureds_insert) is not null drop procedure insureds_insert";_mdf.ExecuteNonQuery();= "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) ";+= "as begin ";+= "if(year(@outdate)=1900) set @outdate=null ";+= "if(dbo.select_insureds(@clid,@number)=0) ";+= "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";_mdf.CommandText = request;_mdf.ExecuteNonQuery();start_reg=new DateTime();//дата регистрацииend_reg = new DateTime();//дата окончанияbirthdate = new DateTime();//дата рожденияsurrender = new DateTime();//дата сдачиchange = new DateTime();//дата измененияtelephone=0;//телефонos = false;male = false;//пол (муж. - true, жен. - false)ils = 0;house=0, apartment=0;//дом, квартираgroupe=0, oper=0;//группа инвалидности, оператор

records_list = new List();//список не обработанных записей(rdr_dbf.Read())

{

{.TryParse(rdr_dbf["dp"].ToString(),out start_reg);.TryParse(rdr_dbf["dendp"].ToString(),out end_reg);.TryParse(rdr_dbf["tel"].ToString(),out telephone);(rdr_dbf["ft"].ToString().Replace("*", "True") == "True")= true;= false;.TryParse(rdr_dbf["dr"].ToString(),out birthdate);(rdr_dbf["w"].ToString() == "1")= true;= false;.TryParse(rdr_dbf["ils"].ToString(), out ils);.TryParse(rdr_dbf["dom"].ToString(), out house);.TryParse(rdr_dbf["kv"].ToString(), out apartment);.TryParse(rdr_dbf["gr"].ToString(), out groupe);.TryParse(rdr_dbf["sysoper"].ToString(), out oper);.TryParse(rdr_dbf["psdate"].ToString(), out surrender);(surrender.Year == 1)= Convert.ToDateTime("1.1.1900");.TryParse(rdr_dbf["sysdate"].ToString(), out change);

//вызов хранимой процедуры для добавления записи_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"]);_mdf.ExecuteNonQuery();

}

{[] temp_record = new string[33];//текущая запись_string++;(column = 0; column < 33; column++)_record[column] = rdr_dbf[column].ToString();_list.Add(temp_record);

}

}

//создание лог файла(count_string > 0)

{_file = new To_log_File();= workdirectory + "\\log_file_insured_" + DateTime.Now.Date.ToString().Substring(0, 10) + ".xls";_file.create_log_file(records_list, count_string, file,false);(MessageBox.Show("Импорт данных завершён.\nКоличество не обработанных записей: " + count_string + " \nОткыть лог файл?", "Информация", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes).Diagnostics.Process.Start(file);

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

}

#endregion