public long InserToSQL(SqlConnection pSqlConn, Schet schet, Sluch sluch)
{
if (this.UslID == 0)
{
string sqlExp = @"insert into EXP_UO_EX (EXP_SCHET_EX_ID,EXP_RE_EX_ID,NSH,DSH,RE,G1,KOL_USL,TARIF,SUM_USL,DATE_IN,DATE_OUT,COMMENTU,VID_VME,
V002_ID,K16,K51,DS,V015_CODE,CODE_MD,OT,G13,G14,G16,DENTAL_F,DENT_SURFACE,CODE_USL,P_OTK,NPL,MCOD_LPU)
values(@EXP_SCHET_EX_ID,@EXP_RE_EX_ID,@NSH,@DSH,@RE,@G1,@KOL_USL,@TARIF,@SUM_USL,@DATE_IN,@DATE_OUT,@COMMENTU,@VID_VME,
@V002_ID,@K16,@K51,@DS,@V015_CODE,@CODE_MD,@OT,@G13,@G14,@G16,@DENTAL_F,@DENT_SURFACE,@CODE_USL,@P_OTK,@NPL,@MCOD_LPU)
select @@IDENTITY as uID";
SqlCommand command = new SqlCommand(sqlExp, pSqlConn);
command.Parameters.Add("@EXP_SCHET_EX_ID", SqlDbType.BigInt).Value = schet.SchetID;
command.Parameters.Add("@EXP_RE_EX_ID", SqlDbType.BigInt).Value = sluch.SluchID;
command.Parameters.Add("@NSH", SqlDbType.Char).Value = schet.Nschet;
command.Parameters.Add("@DSH", SqlDbType.DateTime).Value = schet.Dschet;
command.Parameters.Add("@RE", SqlDbType.Int).Value = sluch.N_zap;
command.Parameters.Add("@G1", SqlDbType.Int).Value = sluch.Idcase;
command.Parameters.Add("@KOL_USL", SqlDbType.Decimal).Value = this.KOL_USL;
command.Parameters.Add("@TARIF", SqlDbType.Decimal).Value = this.TARIF;
command.Parameters.Add("@SUM_USL", SqlDbType.Decimal).Value = this.SUMV_USL;
command.Parameters.Add("@DATE_IN", SqlDbType.DateTime).Value = this.DATE_IN;
command.Parameters.Add("@DATE_OUT", SqlDbType.DateTime).Value = this.DATE_OUT;
command.Parameters.Add("@COMMENTU", SqlDbType.NVarChar).Value = this.COMENTU ?? (object)DBNull.Value;
command.Parameters.Add("@VID_VME", SqlDbType.Char).Value = this.VID_VME ?? (object)DBNull.Value;
command.Parameters.Add("@V002_ID", SqlDbType.Int).Value = this.PROFIL == 0 ? (object)DBNull.Value: this.PROFIL;
command.Parameters.Add("@K16", SqlDbType.NChar).Value = this.Mkb1 ?? (object)DBNull.Value;
command.Parameters.Add("@K51", SqlDbType.NChar).Value = this.Mku1 ?? (object)DBNull.Value;
command.Parameters.Add("@DS", SqlDbType.NChar).Value = this.DS1 ?? (object)DBNull.Value;
command.Parameters.Add("@V015_CODE", SqlDbType.Int).Value = this.PRVS;
command.Parameters.Add("@CODE_MD", SqlDbType.Char).Value = this.CODE_MD;
command.Parameters.Add("@OT", SqlDbType.Decimal).Value = this.PODR;
command.Parameters.Add("@G13", SqlDbType.Decimal).Value = this.KOL_USL;
command.Parameters.Add("@G14", SqlDbType.Decimal).Value = this.TARIF;
command.Parameters.Add("@G16", SqlDbType.Decimal).Value = this.SUMV_USL;
command.Parameters.Add("@DENTAL_F", SqlDbType.Decimal).Value = this.DENTAL_F == 0 ? (object)DBNull.Value: this.DENTAL_F;
command.Parameters.Add("@DENT_SURFACE", SqlDbType.NChar).Value = this.DENT_SURFACE ?? (object)DBNull.Value;
command.Parameters.Add("@CODE_USL", SqlDbType.NChar).Value = this.CODE_USL ?? (object)DBNull.Value;
command.Parameters.Add("@P_OTK", SqlDbType.Int).Value = this.P_OTK == 0 ? (object)DBNull.Value : this.P_OTK;
command.Parameters.Add("@NPL", SqlDbType.Int).Value = this.NPL == 0 ? (object)DBNull.Value : this.NPL;
command.Parameters.Add("@MCOD_LPU", SqlDbType.NVarChar).Value = this.LPU ?? (object)DBNull.Value;
this.UslID = Convert.ToInt64(((decimal)command.ExecuteScalar()));
return(this.UslID);
}
else
{
MessageBox.Show($"Услуга {this.UslID} \n уже имеет ID запрос не выполнен");
return(this.UslID);
}
return(UslID);
}
/// <summary>
/// загрузка из xml в базу
/// </summary>
/// <param name="ePath">путь к распакованным файлам</param>
public void LoadToDB(string ePath)
{
string lcfname = Path.GetFileNameWithoutExtension(ePath);
string lcHMfname = $"HM{lcfname}.xml";
string lcLMfname = $"LM{lcfname}.xml";
//MessageBox.Show($"{ePath}\\{lcHMfname}\n{ePath}\\{lcLMfname}");
XDocument HMfname = XDocument.Load($"{ePath}\\{lcHMfname}");
//XmlDocument HMfname = new XmlDocument();
//HMfname.Load($"{ePath}\\{lcHMfname}");
int SluchCount = (from xe in HMfname.Root.Descendants("SLUCH") select xe).Count();
Invoke(new Action(() => MainPBar.Maximum = SluchCount));
XDocument LMfname = XDocument.Load($"{ePath}\\{lcLMfname}");
// получаем счет из файла
Schet schet = new Schet();
Pacient pac = new Pacient();
Person p = new Person();
foreach (XElement SE in HMfname.Element("ZL_LIST").Elements("SCHET"))
{
string ns = ReadXMLElementString(SE, "NSCHET");
DateTime ds = ReadXMLElementDate(SE, "DSCHET");
int ye = ReadXMLElementInt(SE, "YEAR");
int mn = ReadXMLElementInt(SE, "MONTH");
string cm = ReadXMLElementString(SE, "CODE_MO");
string pl = ReadXMLElementString(SE, "PLAT");
double sum = ReadXMLElementDouble(SE, "SUMMAV");
schet = new Schet(0, ns, ds, ye, mn, cm, pl, sum);
schet.SchetID = schet.InsertToSQL(sqlConn);
}
// получаем ZAP из файла
foreach (XElement ZAP in HMfname.Element("ZL_LIST").Elements("ZAP"))
{
int n_zap = ReadXMLElementInt(ZAP, "N_ZAP"); //O
int pr_nov = ReadXMLElementInt(ZAP, "PR_NOV"); //O
string nschet_p = ReadXMLElementString(ZAP, "NSCHET_P");
DateTime dschet_p = ReadXMLElementDate(ZAP, "DSCHET_P");
int n_zap_p = ReadXMLElementInt(ZAP, "N_ZAP_P");
foreach (XElement PACIENT in ZAP.Elements("PACIENT"))
{
// вычитываем инфо пациента
pac.Id_pac = ReadXMLElementString(PACIENT, "ID_PAC");
pac.Vpolis = ReadXMLElementInt(PACIENT, "VPOLIS");
pac.Spolis = ReadXMLElementString(PACIENT, "SPOLIS");
pac.Npolis = ReadXMLElementString(PACIENT, "NPOLIS");
pac.St_okato = ReadXMLElementString(PACIENT, "ST_ОКАТО");
pac.Smo = ReadXMLElementString(PACIENT, "SMO");
pac.Smo_ogrn = ReadXMLElementString(PACIENT, "SMO_OGRN");
pac.Smo_ok = ReadXMLElementString(PACIENT, "SMO_OK");
pac.Smo_nam = ReadXMLElementString(PACIENT, "SMO_NAM");
pac.Inv = ReadXMLElementInt(PACIENT, "INV");
pac.Mse = ReadXMLElementInt(PACIENT, "MSE");
pac.Novor = ReadXMLElementString(PACIENT, "NOVOR");
pac.Vnov_d = ReadXMLElementInt(PACIENT, "VNOV_D");
//выколупываем из L-файла ФИО и тд.
var Pers = from pers in LMfname.Element("PERS_LIST").Elements("PERS")
where (string)pers.Element("ID_PAC") == pac.Id_pac
select pers;
// читае инфо пациента
foreach (XElement xEle in Pers)
{
p.Id_pac = ReadXMLElementString(xEle, "ID_PAC");
p.Fam = ReadXMLElementString(xEle, "FAM");
p.Im = ReadXMLElementString(xEle, "IM");
p.Ot = ReadXMLElementString(xEle, "OT");
p.W = ReadXMLElementInt(xEle, "W");
p.Dr = ReadXMLElementDate(xEle, "DR");
p.Tel = ReadXMLElementString(xEle, "TEL");
p.Fam_p = ReadXMLElementString(xEle, "FAM_P");
p.Im_p = ReadXMLElementString(xEle, "FAM_P");
p.Ot_p = ReadXMLElementString(xEle, "OT_P");
p.W_p = ReadXMLElementInt(xEle, "W_P");
p.Dr_p = ReadXMLElementDate(xEle, "DR_P");
p.Mr = ReadXMLElementString(xEle, "MR");
p.Doctype = ReadXMLElementInt(xEle, "DOCTYPE");
p.Docser = ReadXMLElementString(xEle, "DOCSER");
p.Docnum = ReadXMLElementString(xEle, "DOCNUM");
p.Snils = ReadXMLElementString(xEle, "SNILS");
p.Okatog = ReadXMLElementString(xEle, "OKATOG");
p.Okatop = ReadXMLElementString(xEle, "OKATOP");
p.Fh = ReadXMLElementString(xEle, "FH");
p.Kat_lg = ReadXMLElementInt(xEle, "KAT_LG");
p.Commentp = ReadXMLElementString(xEle, "COMENTP");
p.Adr = ReadXMLElementString(xEle, "ADR");
pac.Pers = p;
}
//var items = from xe in LMfname.Element("PERS_LIST").Elements("PERS")
// where (xe.Element("ID_PAC").Value == id_pac)
// select new Person
// {
// Id_pac = xe.Element("ID_PAC").Value,
// Fam = xe.Element("FAM").Value,
// Im = xe.Element("IM").Value,
// Ot = xe.Element("OT").Value ,
// Dr = DateTime.ParseExact(xe.Element("DR").Value, "yyyy-MM-dd", null)
// };
//foreach (var item in items)
//{
// //MessageBox.Show($"{item.ToString()}");
// //MessageBox.Show($"{item.Fam} {item.Im} {item.Ot}");
//}
}
// вычитываем инфо по законченному случаю
foreach (XElement Z_SL in ZAP.Elements("Z_SL"))
{
Z_sl z = new Z_sl();
z.Pac = pac;
z.Z_sl_id = ReadXMLElementInt(Z_SL, "Z_SL_ID");
z.Istochik_opl = ReadXMLElementInt(Z_SL, "ISTOCHIK_OPL");
z.Usl_ok = ReadXMLElementInt(Z_SL, "USL_OK");
z.Vidpom = ReadXMLElementInt(Z_SL, "VIDPOM");
z.For_pom = ReadXMLElementInt(Z_SL, "FOR_POM");
z.Lpu = ReadXMLElementString(Z_SL, "LPU");
z.Npr_mo = ReadXMLElementString(Z_SL, "NPR_MO");
z.Np = ReadXMLElementString(Z_SL, "NP");
z.Npr_date = ReadXMLElementDate(Z_SL, "NPR_DATE");
z.Np_iddokt = ReadXMLElementString(Z_SL, "NP_IDDOKT");
z.Date_z_1 = ReadXMLElementDate(Z_SL, "DATE_Z_1");
z.Date_z_2 = ReadXMLElementDate(Z_SL, "DATE_Z_2");
z.Kd_z = ReadXMLElementInt(Z_SL, "KD_Z");
z.Vnov_m = ReadXMLElementInt(Z_SL, "VNOV_M");
z.Rslt = ReadXMLElementInt(Z_SL, "RSLT");
z.Ishod = ReadXMLElementInt(Z_SL, "ISHOD");
z.Os_sluch = ReadXMLElementInt(Z_SL, "OS_SLUCH");
z.Vb_p = ReadXMLElementInt(Z_SL, "VB_P");
z.Idsp = ReadXMLElementInt(Z_SL, "IDSP");
z.Sum_z = ReadXMLElementDouble(Z_SL, "SUMV_Z");
z.Oplata = ReadXMLElementInt(Z_SL, "OPLATA");
z.Vbr = ReadXMLElementInt(Z_SL, "VBR");
z.Rslt_d = ReadXMLElementInt(Z_SL, "RSLT_D");
z.P_otk = ReadXMLElementInt(Z_SL, "P_OTK");
z.InsertToSQL(sqlConn, pac, schet);
// перебираем случаи
foreach (XElement SLUCH in Z_SL.Elements("SLUCH"))
{
Sluch s = new Sluch();
// вычитываем случай лечения
s.N_zap = n_zap;
s.Idcase = ReadXMLElementInt(SLUCH, "IDCASE");
s.Caseguid = ReadXMLElementString(SLUCH, "CASEGUID");
s.Vid_hmp = ReadXMLElementString(SLUCH, "VID_HMP");
s.Metod_hmp = ReadXMLElementInt(SLUCH, "METOD_HMP");
s.Disp = ReadXMLElementString(SLUCH, "Disp");
s.Lpu_1 = ReadXMLElementString(SLUCH, "Lpu_1");
s.Podr = ReadXMLElementInt(SLUCH, "Podr");
s.Uchastok = ReadXMLElementInt(SLUCH, "Uchastok");
s.Punkt = ReadXMLElementInt(SLUCH, "Punkt");
s.Profil = ReadXMLElementInt(SLUCH, "Profil");
s.Profil_k = ReadXMLElementInt(SLUCH, "Profil_k");
s.Det = ReadXMLElementInt(SLUCH, "Det");
s.Tal_d = ReadXMLElementDate(SLUCH, "Tal_d");
s.Tal_num = ReadXMLElementString(SLUCH, "Tal_num");
s.Tal_p = ReadXMLElementDate(SLUCH, "Tal_p");
s.Nhistory = ReadXMLElementString(SLUCH, "Nhistory");
s.Obr = ReadXMLElementString(SLUCH, "Obr");
s.P_per = ReadXMLElementInt(SLUCH, "P_per");
s.Date_1 = ReadXMLElementDate(SLUCH, "Date_1");
s.Date_2 = ReadXMLElementDate(SLUCH, "Date_2");
s.Kd = ReadXMLElementInt(SLUCH, "Kd");
s.Ds0 = ReadXMLElementString(SLUCH, "Ds0");
s.Ds1 = ReadXMLElementString(SLUCH, "Ds1");
s.Ds1_pr = ReadXMLElementInt(SLUCH, "Ds1_pr");
s.Ds1_dn = ReadXMLElementInt(SLUCH, "Ds1_dn");
s.C_zab = ReadXMLElementInt(SLUCH, "C_zab");
s.Ds_onk = ReadXMLElementInt(SLUCH, "Ds_onk");
s.Code_mes1 = ReadXMLElementString(SLUCH, "Code_mes1");
s.Code_mes2 = ReadXMLElementString(SLUCH, "Code_mes2");
s.Reab = ReadXMLElementInt(SLUCH, "Reab");
s.Prvs = ReadXMLElementInt(SLUCH, "Prvs");
s.Iddokt = ReadXMLElementString(SLUCH, "Iddokt");
s.Ed_col = ReadXMLElementDouble(SLUCH, "Ed_col");
s.Tarif = ReadXMLElementDouble(SLUCH, "Tarif");
s.Sumv = ReadXMLElementDouble(SLUCH, "Sumv");
s.Sump = ReadXMLElementDouble(SLUCH, "Sump");
s.Idcase_p = ReadXMLElementInt(SLUCH, "Idcase_p");
s.CodeRefusal = ReadXMLElementInt(SLUCH, "CodeRefusal");
s.Comentsl = ReadXMLElementString(SLUCH, "Comentsl");
s.Pr_nov = pr_nov;
switch (z.For_pom)
{
case 1:
s.PR3 = "*";
break;
default:
s.PR3 = null;
break;
}
s.InsertToSQL(sqlConn, pac, schet, z);
// перебираем услуги
foreach (XElement USL in SLUCH.Elements("USL"))
{
Usl u = new Usl();
// вычитываем услуги по случаю
u.IDSERV = ReadXMLElementString(USL, "IDSERV");
u.LPU = ReadXMLElementString(USL, "LPU");
u.LPU_1 = ReadXMLElementString(USL, "LPU_1");
u.PODR = ReadXMLElementInt(USL, "PODR");
u.UCHASTOK = ReadXMLElementInt(USL, "UCHASTOK");
u.PUNKT = ReadXMLElementInt(USL, "PUNKT");
u.PROFIL = ReadXMLElementInt(USL, "PROFIL");
u.VID_VME = ReadXMLElementString(USL, "VID_VME");
u.DET = ReadXMLElementInt(USL, "PROFIL");
u.DATE_IN = ReadXMLElementDate(USL, "DATE_IN");
u.DATE_OUT = ReadXMLElementDate(USL, "DATE_OUT");
u.P_OTK = ReadXMLElementInt(USL, "P_OTK");
u.DS1 = ReadXMLElementString(USL, "DS");
u.CODE_USL = ReadXMLElementString(USL, "CODE_USL");
u.KOL_USL = ReadXMLElementDouble(USL, "KOL_USL");
u.TARIF = ReadXMLElementDouble(USL, "TARIF");
u.SUMV_USL = ReadXMLElementDouble(USL, "SUMV_USL");
u.PRVS = ReadXMLElementInt(USL, "PRVS");
u.CODE_MD = ReadXMLElementString(USL, "CODE_MD");
u.NPL = ReadXMLElementInt(USL, "NPL");
u.DENTAL_F = ReadXMLElementInt(USL, "DENTAL_F");
u.DENT_SURFACE = ReadXMLElementString(USL, "DENT_SURFACE");
u.COMENTU = ReadXMLElementString(USL, "COMENTU");
u.InserToSQL(sqlConn, schet, s);
}
Invoke(new Action(() => MainPBar.Value += 1));
}
}
}
HMfname = null;
LMfname = null;
Invoke(new Action(() => MainPBar.Value = 0));
}