Автоматизированная система управления медицинского страхования
Дипломная работа - Компьютеры, программирование
Другие дипломы по предмету Компьютеры, программирование
#39;{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"));_mdf.ExecuteNonQuery();
}
{ }
}_dbf.Close();
}
//Извлечения списка договоровextract_contracts()
{request = "";//строка запроса_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";_dbf = cmd_dbf.ExecuteReader();
//Функция поиска ключа типа классификатора по наименованию_mdf.CommandText = "if object_id(select_type_class) is not null drop function select_type_class";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_type_class(@ctname varchar(50)) returns int as begin return(select ctid from classtype where ctname=@ctname) end";_mdf.ExecuteNonQuery();
//Функция проверки наличия договора в базе данных_mdf.CommandText = "if object_id(select_contract) is not null drop function select_contract";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_contract(@clid int) returns int as begin return(select count(clid) from contractlist where clid=@clid) end";_mdf.ExecuteNonQuery();
//Процедура добавления договора_mdf.CommandText = "if object_id(contracts_insert) is not null drop procedure contracts_insert";_mdf.ExecuteNonQuery();= "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) ";+= "as begin ";+= "declare @reg varchar(2) ";+= "declare @registrationid varchar(5) ";+= "set @reg=substring(@regnum,1,2) ";+= "set @registrationid=substring(@regnum,4,5) ";+= "if (year(@registrationdate)=1900) set @registrationdate=null ";+= "if (dbo.select_contract(@clid)=0) ";+= "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";_mdf.CommandText = request;_mdf.ExecuteNonQuery();regdate = new DateTime();//дата регистрацииstart_reg = new DateTime();//дата начала действияend_reg = new DateTime();//дата окончания действияinn1, inn2;//ИННpos, kpp;tel;//телефон
records_list = new List();//список не обработанных записей(rdr_dbf.Read())
{
{.TryParse(rdr_dbf["regdat"].ToString(),out regdate);(regdate.Year==1)=Convert.ToDateTime("1.1.1900");
//выделяем инн=rdr_dbf["inn"].ToString();=rdr_dbf["inn"].ToString().IndexOf("/");(pos!= -1)
{= rdr_dbf["inn"].ToString().Substring(0, pos);= rdr_dbf["inn"].ToString().Substring(pos + 1, rdr_dbf["inn"].ToString().Length - pos - 1);
}= "0";(rdr_dbf["kpp"].ToString() == "")= 0;= Convert.ToInt32(rdr_dbf["kpp"]);.TryParse(rdr_dbf["dbeg"].ToString(),out start_reg);.TryParse(rdr_dbf["dend"].ToString(),out end_reg);.TryParse(rdr_dbf["tel"].ToString().Replace("-","").Trim(),out tel);
//вызов хранимой процедуры для добавления записи_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"]);_mdf.ExecuteNonQuery();
}
{[] temp_record = new string[16];//текущая запись_string++;(column = 0; column < 16; column++)_record[column] = rdr_dbf[column].ToString();_list.Add(temp_record);
}
}
//создание лог файла(count_string > 0)
{_file = new To_log_File();= workdirectory + "\\log_file_contract_" + DateTime.Now.Date.ToString().Substring(0, 10) + ".xls";_file.create_log_file(records_list, count_string, file, true);
}.Show("Импорт данных завершён.", "Информация", MessageBoxButtons.OK, MessageBoxIcon.Information);_dbf.Close();
}
//Извлечение списка застрахованныхextract_insureds()
{request="";//строка запроса_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";_dbf = cmd_dbf.ExecuteReader();
//Функция поиска ключа признака движения полиса по наименованию_mdf.CommandText = "if object_id(select_pmid) is not null drop function select_pmid";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_pmid(@pmname varchar(40)) returns int as begin return(select pmid from policiesmovement where pmname=@pmname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа уточнения предприятия по наименованию_mdf.CommandText = "if object_id(select_specification) is not null drop function select_specification";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_specification(@slname varchar(25)) returns char(1) as begin return(select slid from specificationslist where slname=@slname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа типа документа по наименованию_mdf.CommandText = "if object_id(select_document) is not null drop function select_document";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_document(@docname varchar(50)) returns int as begin return(select docid from document where docname=@docname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа имени по наименованию_mdf.CommandText = "if object_id(select_name) is not null drop function select_name";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_name(@name varchar(20)) returns int as begin return(select nid from names where nname=@name) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа отчества по наименованию_mdf.CommandText = "if object_id(select_sname) is not null drop function select_sname";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_sname(@snname varchar(20)) returns int as begin return(select snid from secondnames where snname=@snname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа социального статуса по наименованию_mdf.CommandText = "if object_id(select_status) is not null drop function select_status";_mdf.ExecuteNonQuery();_mdf.CommandText = "create function select_status(@ssname varchar(40)) returns int as begin return(select ssid from socialstatus where ssname=@ssname) end";_mdf.ExecuteNonQuery();
//Функция поиска ключа населённого пункта по наименованию_mdf.CommandText = "if object_id(select_occupieditem) is not null drop function select_occupieditem";_mdf.ExecuteNonQuery()