public getCustomerVehiclesResponse getCustomerVehicles(getCustomerVehiclesRequest request) { Int32 kontonr; Int32 fahrzeugid; string fahrgestellnr = ""; string zulassungskz = ""; string marke = ""; string verkaufstyp = ""; string bezeichnung = ""; string motorseriennr = ""; DateTime datumerstzulassung; string datErstzulassung = ""; Int32 kilometerstand; string farbe = ""; string innenausstattung = ""; Int32 kw; DateTime datumbesuch; string datBesuch = ""; DateTime creationdate; string creationuser = ""; DateTime modifieddate; string modifieduser = ""; DateTime datumzulassungkunde; string datZulassungkunde = ""; DateTime datumhu; string datHU = ""; DateTime datumau; string datAU = ""; string zuendschluesselnr = ""; string tuerschluesselnr = ""; string radiocode = ""; Int32 hubraum; string kbahsnr = ""; string kbatsnr = ""; string motortyp = ""; string kfzbriefnr = ""; string treibstoff = ""; string info = ""; string kfzversicherung = ""; Int32 modelljahr; bool aktiv = false; string strVerkaeufer = ""; string strKW = ""; string strHubraum = ""; string strModelljahr = ""; string strKilometerstand = ""; DateTime dtDefaultDB; dtDefaultDB = Convert.ToDateTime("01.01.1900"); creationdate = dtDefaultDB; modifieddate = dtDefaultDB; datumzulassungkunde = dtDefaultDB; datumhu = dtDefaultDB; datumau = dtDefaultDB; datumbesuch = dtDefaultDB; datumerstzulassung = dtDefaultDB; getCustomerVehiclesResponse res = new getCustomerVehiclesResponse(); interfaceData iData = new interfaceData(); customerVehicleType cV; System.Collections.ArrayList aFahrzeuge = new System.Collections.ArrayList(); salesmanType sT = new salesmanType(); sT.identifier = "fmad"; sT.name = "Fmade"; using (DBConnect myConnect = new DBConnect()) { String sqlCommand; //Limitierung auf 100 Adressdatensätzen pro Abruf sqlCommand = "SELECT TOP 100 START AT " + request.Body.arg0.ToString() + " a.kontonr, b.fahrzeugid, b.fahrgestellnr, b.zulassungskz, b.marke, b.verkaufstyp, b.bezeichnung, "; //sqlCommand = "SELECT a.kontonr, b.fahrzeugid, b.fahrgestellnr, b.zulassungskz, b.marke, b.verkaufstyp, b.bezeichnung, "; sqlCommand += "b.motorseriennr, b.datumerstzulassung, b.kilometerstand, b.farbe, b.innenausstattung, b.kw, b.verkaeufer, b.datumbesuch, b.creationdate, b.creationuser, b.modifieddate, b.modifieduser, "; sqlCommand += "b.datumzulassungkunde, b.datumhu, b.datumau, b.zuendschluesselnr, b.tuerschluesselnr, b.radiocode, b.hubraum, b.kbahsnr, b.motortyp, b.kfzbriefnr, b.treibstoff, b.info, b.kfzversicherung, b.modelljahr, "; sqlCommand += " b.aktiv, b.kbatsnr"; sqlCommand += " FROM bungert.adresse a, bungert.fahrzeug b"; sqlCommand += " WHERE a.adressid = b.kundenadrid"; //sqlCommand += " AND b.zulassungskz = 'B-AR 6700'"; sqlCommand += " ORDER BY b.fahrzeugid"; OdbcCommand cmd = new OdbcCommand(sqlCommand, myConnect.conn); //cmd.Parameters.Add(":fahrgestellnr", OdbcType.VarChar, 17).Value = vin; myConnect.Connect(); OdbcDataReader reader; //DataReader Objekt wird initialisiert using (reader = cmd.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { cV = new customerVehicleType(); kontonr = 0; kontonr = reader.GetInt32(0); fahrzeugid = reader.GetInt32(1); fahrgestellnr = reader.GetValue(2).ToString(); zulassungskz = reader.GetValue(3).ToString(); marke = reader.GetValue(4).ToString(); verkaufstyp = reader.GetValue(5).ToString(); bezeichnung = reader.GetValue(6).ToString(); motorseriennr = reader.GetValue(7).ToString(); datErstzulassung = reader.GetValue(8).ToString(); strKilometerstand = reader.GetValue(9).ToString(); farbe = reader.GetValue(10).ToString(); innenausstattung = reader.GetValue(11).ToString(); strKW = reader.GetValue(12).ToString(); strVerkaeufer = reader.GetValue(13).ToString(); datBesuch = reader.GetValue(14).ToString(); creationdate = reader.GetDateTime(15); creationuser = reader.GetValue(16).ToString(); modifieddate = reader.GetDateTime(17); modifieduser = reader.GetValue(18).ToString(); datZulassungkunde = reader.GetValue(19).ToString(); datHU = reader.GetValue(20).ToString(); datAU = reader.GetValue(21).ToString(); zuendschluesselnr = reader.GetValue(22).ToString(); tuerschluesselnr = reader.GetValue(23).ToString(); radiocode = reader.GetValue(24).ToString(); strHubraum = reader.GetValue(25).ToString(); kbahsnr = reader.GetValue(26).ToString(); motortyp = reader.GetValue(27).ToString(); kfzbriefnr = reader.GetValue(28).ToString(); treibstoff = reader.GetValue(29).ToString(); info = reader.GetValue(30).ToString(); kfzversicherung = reader.GetValue(31).ToString(); strModelljahr = reader.GetValue(32).ToString(); aktiv = reader.GetBoolean(33); kbatsnr = reader.GetValue(34).ToString(); //Datum Erstzulassung if (String.IsNullOrWhiteSpace(datErstzulassung)) { datumerstzulassung = dtDefaultDB; } else { datumerstzulassung = Convert.ToDateTime(datErstzulassung); } //Datum Besuch if (String.IsNullOrWhiteSpace(datBesuch)) { datumbesuch = dtDefaultDB; } else { datumbesuch = Convert.ToDateTime(datBesuch); } //Datum Zulassungkunde if (String.IsNullOrWhiteSpace(datZulassungkunde)) { datumzulassungkunde = dtDefaultDB; } else { datumzulassungkunde = Convert.ToDateTime(datZulassungkunde); } //Datum HU if (String.IsNullOrWhiteSpace(datHU)) { datumhu = dtDefaultDB; } else { datumhu = Convert.ToDateTime(datHU); } //Datum AU if (String.IsNullOrWhiteSpace(datAU)) { datumau = dtDefaultDB; } else { datumau = Convert.ToDateTime(datAU); } customerIdentificationType custID = new customerIdentificationType(); custID.number = kontonr; //US 10.02.2014 //Standort muss zum Verkaeufer passen - standardmäßig "location=1" custID.location = 1; cV.customerNumber = custID; cV.vehicleNumber = fahrzeugid; cV.vin = fahrgestellnr; if (aktiv) { cV.deleted = false; } else { cV.deleted = true; } cV.licensePlate = zulassungskz; cV.brand = marke; cV.typeCode = verkaufstyp; cV.type = bezeichnung; if (String.IsNullOrWhiteSpace(strModelljahr)) { modelljahr = 0; } else { modelljahr = Convert.ToInt32(strModelljahr); } cV.modelYear = Convert.ToDateTime("01.01." + modelljahr.ToString()); cV.modelYearSpecified = true; cV.motorNumber = motorseriennr; cV.firstRegistration = datumerstzulassung; cV.firstRegistrationSpecified = true; if (String.IsNullOrWhiteSpace(strKilometerstand)) { kilometerstand = 0; } else { kilometerstand = Convert.ToInt32(strKilometerstand); } cV.milage = kilometerstand; cV.milageSpecified = true; cV.exteriorColor = farbe; cV.interiorColor = innenausstattung; if (String.IsNullOrWhiteSpace(strKW)) { kw = 0; } else { kw = Convert.ToInt32(strKW); } cV.kw = kw; customerVehicleTypeLastInvoice custLI = new customerVehicleTypeLastInvoice(); custLI.date = datumbesuch; custLI.dateSpecified = true; cV.lastInvoice = custLI; customerVehicleTypeCreation custCreate = new customerVehicleTypeCreation(); custCreate.date = creationdate; custCreate.dateSpecified = true; cV.creation = custCreate; salesmanType custST = new salesmanType(); custST.identifier = creationuser; custST.name = creationuser; custCreate.salesman = custST; customerVehicleTypeLastChange custLS = new customerVehicleTypeLastChange(); custLS.date = modifieddate; custLS.dateSpecified = true; custST = new salesmanType(); custST.identifier = modifieduser; custST.name = modifieduser; custLS.salesman = custST; cV.lastChange = custLS; //Datum Zulassungkunde cV.lastRegistrationSpecified = false; if (datumzulassungkunde.Date > dtDefaultDB.Date) { cV.lastRegistration = datumzulassungkunde; cV.lastRegistrationSpecified = true; } //Datum HU cV.testDate1Specified = false; if (datumhu.Date > dtDefaultDB.Date) { cV.testDate1 = datumhu; cV.testDate1Specified = true; } //Datum AU cV.testDate2Specified = false; if (datumau.Date > dtDefaultDB.Date) { cV.testDate2 = datumau; cV.testDate2Specified = true; } cV.keyCode1 = zuendschluesselnr; cV.keyCode2 = tuerschluesselnr; cV.radioCode = radiocode; if (String.IsNullOrWhiteSpace(strHubraum)) { hubraum = 0; } else { hubraum = Convert.ToInt32(strHubraum); } if (String.IsNullOrWhiteSpace(kbahsnr)) kbahsnr = ""; if (String.IsNullOrWhiteSpace(kbatsnr)) kbatsnr = ""; cV.kbaNumber = kbahsnr + "/" + kbatsnr; cV.capacity = hubraum; cV.capacitySpecified = true; cV.motorCode = motortyp; cV.carsLetter = kfzbriefnr; cV.fuelDescription = treibstoff; cV.remark = info; customerVehicleTypeInsurance custInsurance = new customerVehicleTypeInsurance(); custInsurance.policyNumber = kfzversicherung; cV.insurance = custInsurance; aFahrzeuge.Add(cV); } } reader.Close(); } } customerVehicleType[] aCustVehicle = new customerVehicleType[aFahrzeuge.Count]; //List<customerVehicleType> aCustVehicle = new List<customerVehicleType>(); for (int i = 0; i < aFahrzeuge.Count; i++) { customerVehicleType custV = (customerVehicleType)aFahrzeuge[i]; aCustVehicle[i] = custV; } iData.customerVehicle = aCustVehicle; iData.interfaceVersion = 1; iData.dataProvider = "STANDARD_INTERFACE"; iData.callingUser = sT; iData.transmissionReason = ""; System.IO.StringWriter stringWriter = new System.IO.StringWriter(); var serializer = new XmlSerializer(typeof(interfaceData)); using (var xw = XmlWriter.Create(stringWriter, new XmlWriterSettings { Encoding = new UTF8Encoding() })) { serializer.Serialize(xw, iData); } #region xml encoding //string result; //using (MemoryStream memoryStream = new MemoryStream()) //{ // XmlSerializer xs = new XmlSerializer(typeof(interfaceData)); // XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8); // xs.Serialize(xmlTextWriter, iData); // result = Encoding.UTF8.GetString(memoryStream.ToArray()); //} //if (String.IsNullOrEmpty(result)) // result = ""; #endregion getCustomerVehiclesResponseBody resBody = new getCustomerVehiclesResponseBody(); resBody.@return = stringWriter.ToString(); res.Body = resBody; SmitConfig sc = new SmitConfig(); sc = ReadXML(); sc.changedCustomerVehicleDate = DateTime.Now.ToString("yyyy-MM-dd"); WriteXML(sc); return res; }
public deleteCustomerVehicleResponse deleteCustomerVehicle(deleteCustomerVehicleRequest request) { Int32 kontonr; Int32 rows; Int32 fahrzeugid; string sqlCommand = ""; deleteCustomerVehicleResponse res = new deleteCustomerVehicleResponse(); System.Xml.Serialization.XmlSerializer xmlReader = new System.Xml.Serialization.XmlSerializer(typeof(interfaceData)); TextReader txtReader = new StringReader(request.Body.arg0); interfaceData data = new interfaceData(); data = (interfaceData)xmlReader.Deserialize(txtReader); Bungert.message m = new Bungert.message(); customerVehicleType cV = new customerVehicleType(); cV = data.customerVehicle[0]; customerIdentificationType custID; custID = cV.customerNumber; kontonr = 0; if (custID != null) { kontonr = custID.number; } fahrzeugid = cV.vehicleNumber; sqlCommand += "UPDATE bungert.fahrzeug set aktiv = 0 WHERE fahrzeugid = " + fahrzeugid.ToString(); using (DBConnect myConnect = new DBConnect()) { OdbcCommand cmd = new OdbcCommand(sqlCommand, myConnect.conn); myConnect.Connect(); rows = cmd.ExecuteNonQuery(); } m.dataProvider = "STANDARD_INTERFACE"; Bungert.responseType r = new Bungert.responseType(); r.code = "0"; if (kontonr > 0) { Bungert.customerIdentificationType cIT = new Bungert.customerIdentificationType(); cIT.location = 1; cIT.number = kontonr; r.customerNumber = cIT; } r.message = "Fahrzeug wurde in P2 auf inaktiv gesetzt."; m.success = r; m.interfaceVersion = 1; //UFT-8 Encoding XmlSerializer xmlSerializer = new XmlSerializer(typeof(Bungert.message)); MemoryStream memStrm = new MemoryStream(); UTF8Encoding utf8e = new UTF8Encoding(); XmlTextWriter xmlSink = new XmlTextWriter(memStrm, utf8e); xmlSerializer.Serialize(xmlSink, m); byte[] utf8EncodedData = memStrm.ToArray(); string strMessage = utf8e.GetString(utf8EncodedData); deleteCustomerVehicleResponseBody resBody = new deleteCustomerVehicleResponseBody(); resBody.@return = strMessage; res.Body = resBody; return res; }