Exemple #1
0
        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);
        }
Exemple #2
0
        /// <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));
        }